Update UITemplates set TemplateQuery= ' DECLARE @BitWiseID BIGINT DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT @BitWiseID = HNG.[BitWiseID] FROM HandlerNETGroupMembership HNG WHERE HNG.[HANDLER] = CONVERT(VARCHAR(10), @handler) SET @BitWiseID = ISNULL(@BitWiseID, [dbo].[KAAS_FN_GetOldPermissions](RTRIM(ISNULL(@handler, '''')))) IF (@handler = ''ADM'') BEGIN SET @BitWiseID = CONVERT(BIGINT, -1) END /*Today Over due Task*/ SELECT * INTO #Table1 FROM( SELECT ISNULL(ADT.[Overdue Task], 0) AS [HeaderCount] , ''Over due task'' as HeaderLabel FROM ( SELECT SUM(CASE WHEN DEL.[DATE] < ''18010101'' THEN 0 WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR, @GETDATE, 112))) THEN 0 WHEN [DEL].[STATUS] = 0 THEN 1 ELSE 0 END) AS [Overdue Task] FROM [dbo].[DiaryDelegations] DEL INNER JOIN [dbo].[Diary] DIA INNER JOIN [dbo].[matters] MAT INNER JOIN [dbo].[Contacts] CON ON CON.[CODE] = MAT.[CLIENTCODE] LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP ON MNP.[MATTER] = MAT.[Code] ON MAT.[CODE] = DIA.[CASECODE] AND ISNULL(MAT.[Closed], ''N'') <> ''Y'' ON DIA.[ACTIONID] = DEL.[ACTIONID] WHERE DEL.[HANDLER] = @Handler AND DEL.[STATUS] = 0 AND DEL.[DelType] <> ''Completed'' AND DEL.[DelType] <> ''Processed'' AND DEL.[DelType] <> ''Returned'' AND (ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0 OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT) AS TBL /*yesterdays Over due task*/ SET @GETDATE=(SELECT DATEADD(DAY, CASE DATENAME(WEEKDAY, @GETDATE) WHEN ''Sunday'' THEN -2 WHEN ''Monday'' THEN -3 ELSE -1 END, DATEDIFF(DAY, 0, @GETDATE))) SELECT * INTO #Table2 FROM( SELECT ISNULL(ADT.[Overdue Task], 0) AS [FooterCount] , ''Yesterday''''s Over due task'' as FooterLabel FROM ( SELECT SUM(CASE WHEN DEL.[DATE] < ''18010101'' THEN 0 WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR, @GETDATE, 112))) THEN 0 WHEN [DEL].[STATUS] = 0 THEN 1 ELSE 0 END) AS [Overdue Task] FROM [dbo].[DiaryDelegations] DEL INNER JOIN [dbo].[Diary] DIA INNER JOIN [dbo].[matters] MAT INNER JOIN [dbo].[Contacts] CON ON CON.[CODE] = MAT.[CLIENTCODE] LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP ON MNP.[MATTER] = MAT.[Code] ON MAT.[CODE] = DIA.[CASECODE] AND ISNULL(MAT.[Closed], ''N'') <> ''Y'' ON DIA.[ACTIONID] = DEL.[ACTIONID] WHERE DEL.[HANDLER] = @Handler AND DEL.[STATUS] = 0 AND DEL.[DelType] <> ''Completed'' AND DEL.[DelType] <> ''Processed'' AND DEL.[DelType] <> ''Returned'' AND (ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0 OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT ) AS TBL SELECT ( SELECT * FROM #Table1,#Table2 FOR XML PATH(''Result'')) as [Result] DROP TABLE #Table1,#Table2 ' where templateid=1000 Go Update UITemplates set TemplateQuery= 'DECLARE @BitWiseID BIGINT DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @todate=''21990101'' SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT @BitWiseID = HNG.[BitWiseID] FROM HandlerNETGroupMembership HNG WHERE HNG.[HANDLER] = CONVERT(VARCHAR(10), @handler) SET @BitWiseID = ISNULL(@BitWiseID, [dbo].[KAAS_FN_GetOldPermissions](RTRIM(ISNULL(@handler, '''')))) IF (@handler = ''ADM'') BEGIN SET @BitWiseID = CONVERT(BIGINT, -1) END /*Today Outstanding Task*/ SELECT * INTO #Table1 FROM( SELECT ISNULL(ADT.[Outstanding Task], 0) AS [HeaderCount], ''Outstanding task'' as HeaderLabel FROM ( SELECT SUM(CASE WHEN DEL.[DATE] < ''18010101'' THEN 0 WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR,@todate, 112))) THEN 0 WHEN [DEL].[STATUS] = 0 THEN 1 ELSE 0 END) AS [Outstanding Task] FROM [dbo].[DiaryDelegations] DEL INNER JOIN [dbo].[Diary] DIA INNER JOIN [dbo].[matters] MAT INNER JOIN [dbo].[Contacts] CON ON CON.[CODE] = MAT.[CLIENTCODE] LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP ON MNP.[MATTER] = MAT.[Code] ON MAT.[CODE] = DIA.[CASECODE] AND ISNULL(MAT.[Closed], ''N'') <> ''Y'' ON DIA.[ACTIONID] = DEL.[ACTIONID] WHERE DEL.[HANDLER] = @Handler AND DEL.[STATUS] = 0 AND DEL.[DelType] <> ''Completed'' AND DEL.[DelType] <> ''Processed'' AND DEL.[DelType] <> ''Returned'' AND (ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0 OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT ) AS TBL /*Yesterday Outstanding Task*/ Set @GETDATE=(SELECT DATEADD(DAY, CASE DATENAME(WEEKDAY, @GETDATE) WHEN ''Sunday'' THEN -2 WHEN ''Monday'' THEN -3 ELSE -1 END, DATEDIFF(DAY, 0, @GETDATE))) SELECT * INTO #Table2 FROM( SELECT ISNULL(ADT.[Outstanding Task], 0) AS [FooterCount], ''Yesterday''''s Outstanding task'' as FooterLabel FROM ( SELECT SUM(CASE WHEN DEL.[DATE] < ''18010101'' THEN 0 WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR, @todate, 112))) THEN 0 WHEN [DEL].[STATUS] = 0 THEN 1 ELSE 0 END) AS [Outstanding Task] FROM [dbo].[DiaryDelegations] DEL INNER JOIN [dbo].[Diary] DIA INNER JOIN [dbo].[matters] MAT INNER JOIN [dbo].[Contacts] CON ON CON.[CODE] = MAT.[CLIENTCODE] LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP ON MNP.[MATTER] = MAT.[Code] ON MAT.[CODE] = DIA.[CASECODE] AND ISNULL(MAT.[Closed], ''N'') <> ''Y'' ON DIA.[ACTIONID] = DEL.[ACTIONID] WHERE DEL.[HANDLER] = @Handler AND DEL.[STATUS] = 0 AND DEL.[DelType] <> ''Completed'' AND DEL.[DelType] <> ''Processed'' AND DEL.[DelType] <> ''Returned'' AND (ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0 OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT ) AS TBL SELECT( SELECT * FROM #Table1,#Table2 for xml path(''Result'')) AS Result drop table #Table1,#Table2 ' where templateid=1001 Go Update UITemplates set TemplateQuery='DECLARE @NCOMMAND NVARCHAR(MAX) DECLARE @IsSAM4 BIT DECLARE @GETDATE DATETIME SET @GETDATE = GETDATE() SET @IsSAM4 = [dbo].[ISSAM4]() DECLARE @FeesYTD DECIMAL(18, 2) DECLARE @FeesLastYTD DECIMAL(18, 2) /*Target*/ DECLARE @FEESISSUEDYTD DECIMAL(18, 2) DECLARE @YEAR INT DECLARE @PERIOD INT /*Get the financial year and period from the KPI, if not there then control, if not there then use now.*/ IF [dbo].[ISSAM4]() = 0 BEGIN SELECT @YEAR = ISNULL(KPI.[Year], ISNULL(CON.[YEAR], YEAR(GETUTCDATE()))) FROM [dbo].[KPIParameters] AS KPI OUTER APPLY [dbo].[control] AS CON SELECT @PERIOD = ISNULL(KPI.[Period], ISNULL(CON.[CURPER], MONTH(GETUTCDATE()))) FROM [dbo].[KPIParameters] AS KPI OUTER APPLY [dbo].[control] AS CON END ELSE BEGIN SELECT @YEAR = ISNULL(KPI.[Year], ISNULL(CON.[NumValue], YEAR(GETUTCDATE()))) FROM [dbo].[KPIParameters] AS KPI OUTER APPLY (SELECT TOP 1 NUM.[NumValue] FROM [dbo].[CtrlNum] AS NUM WHERE NUM.[Code] = ''CurYear'') AS CON SELECT @PERIOD = ISNULL(KPI.[Period], ISNULL(CON.[NumValue], MONTH(GETUTCDATE()))) FROM [dbo].[KPIParameters] AS KPI OUTER APPLY (SELECT TOP 1 NUM.[NumValue] FROM [dbo].[CtrlNum] AS NUM WHERE NUM.[Code] = ''CurPeriod'') AS CON END /* This is very unwieldy but not much choice when converting time values stored as strings into time formats (7.5 -> 07:30)*/ SELECT @FEESISSUEDYTD = FLOOR(SUM(BDG.[FeesBudget])) FROM [dbo].[HandlerBudgets] AS BDG WHERE BDG.[Handler] = @handler AND BDG.[Month] <= @PERIOD AND BDG.[Year] = @YEAR /*FeesYearToDate*/ IF @IsSAM4 = 0 BEGIN SET @NCOMMAND = '' SELECT @FeesYTD = ISNULL(SUM(CASE WHEN BH.[INVCR] = ''''I'''' THEN BD.[VALUE] ELSE 0 END - CASE WHEN BH.[INVCR] = ''''C'''' THEN BD.[VALUE] ELSE 0 END), 0) FROM dbo.[BatchDetails] BD INNER JOIN dbo.[BatchH] BH ON BD.[BATCHNO] = BH.[BATCHNO] AND BH.[POSTED] = ''''Y'''' INNER JOIN [dbo].[KPIParameters] KPI ON KPI.[Year] = BD.[YEAR] AND KPI.[Period] >= BD.[PERNO] WHERE BD.[FEE] = @handler AND BD.[TYPE] = ''''I'''' AND BD.[OUTLAY] = ''''F'''''' END ELSE BEGIN SET @NCOMMAND = '' SELECT @FeesYTD = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0) FROM [dbo].[Transactions] HED INNER JOIN [dbo].[Transactions] TRN ON TRN.[BatchNo] = HED.[BatchNo] AND TRN.[RecType] = ''''D'''' AND TRN.[LineType] = ''''F'''' AND TRN.[XnType] = ''''I'''' AND TRN.[Posted] = ''''Y'''' AND ( TRN.[CorrectionInd] = ''''L'''' OR TRN.[CorrectionInd] = ''''X'''') AND TRN.[FeeEarner] = @handler INNER JOIN [dbo].[KPIParameters] KPI ON KPI.[Year] = HED.[PostingYear] AND KPI.[Period] >= HED.[PostingPeriod] WHERE HED.[RecType] = ''''H'''''' END EXECUTE sp_executesql @NCOMMAND, N''@handler VARCHAR(10),@FeesYTD DECIMAL(18, 2) OUTPUT'', @handler = @handler, @FeesYTD=@FeesYTD OUTPUT /*FeesLastYearToDate*/ IF @IsSAM4 = 0 BEGIN SET @NCOMMAND = ''SELECT @FeesLastYTD = ISNULL(SUM(CASE WHEN BH.[INVCR] = ''''I'''' THEN BD.[VALUE] ELSE 0 END - CASE WHEN BH.[INVCR] = ''''C'''' THEN BD.[VALUE] ELSE 0 END), 0) FROM dbo.[BatchDetails] BD INNER JOIN dbo.[BatchH] BH ON BD.[BATCHNO] = BH.[BATCHNO] AND BH.[POSTED] = ''''Y'''' INNER JOIN [dbo].[KPIParameters] KPI ON KPI.[Year]-1 = BD.[YEAR] AND KPI.[Period] >= BD.[PERNO] WHERE BD.[FEE] = @handler AND BD.[TYPE] = ''''I'''' AND BD.[OUTLAY] = ''''F'''''' END ELSE BEGIN SET @NCOMMAND = ''SELECT @FeesLastYTD = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0) FROM [dbo].[Transactions] HED INNER JOIN [dbo].[Transactions] TRN ON TRN.[BatchNo] = HED.[BatchNo] AND TRN.[RecType] = ''''D'''' AND TRN.[LineType] = ''''F'''' AND TRN.[XnType] = ''''I'''' AND TRN.[Posted] = ''''Y'''' AND TRN.[CorrectionInd] IN (''''L'''', ''''X'''') AND TRN.[FeeEarner] = @handler INNER JOIN [dbo].[KPIParameters] KPI ON KPI.[Year]-1 = HED.[PostingYear] AND KPI.[Period] >= HED.[PostingPeriod] WHERE HED.[RecType] = ''''H'''''' END EXECUTE sp_executesql @NCOMMAND, N''@handler VARCHAR(10),@FeesLastYTD DECIMAL(18, 2) OUTPUT'', @handler = @handler, @FeesLastYTD=@FeesLastYTD OUTPUT SELECT ( SELECT REPLACE(CONVERT(VARCHAR, ROUND(ISNULL(@FEESISSUEDYTD, 0), 0), 1), ''.00'', '''') AS [TargetYearToDate], REPLACE(CONVERT(VARCHAR, CAST(ROUND(ISNULL(@FeesYTD, 0), 0) AS MONEY), 1), ''.00'', '''') AS [FeesIssuedYearToDate], REPLACE(CONVERT(VARCHAR, CAST(ROUND(ISNULL(@FeesLastYTD, 0), 0) AS MONEY), 1), ''.00'', '''') AS [FeesIssuedLastYearToDate] FOR XML PATH(''Result'')) AS [Result]' where templateid=1002 GO Update UITemplates set TemplateQuery= 'DECLARE @NCOMMAND NVARCHAR(MAX) DECLARE @IsSAM4 BIT DECLARE @GETDATE DATETIME SET @GETDATE = GETDATE() SET @IsSAM4 = [dbo].[ISSAM4]() DECLARE @FeesYTD DECIMAL(18, 2) DECLARE @FeesLastYTD DECIMAL(18, 2) /*Target*/ DECLARE @FEESISSUEDYTD DECIMAL(18, 2) DECLARE @YEAR INT DECLARE @PERIOD INT /*Get the financial year and period from the KPI, if not there then control, if not there then use now.*/ IF [dbo].[ISSAM4]() = 0 BEGIN SELECT @YEAR = ISNULL(KPI.[Year], ISNULL(CON.[YEAR], YEAR(GETUTCDATE()))) FROM [dbo].[KPIParameters] AS KPI OUTER APPLY [dbo].[control] AS CON SELECT @PERIOD = ISNULL(KPI.[Period], ISNULL(CON.[CURPER], MONTH(GETUTCDATE()))) FROM [dbo].[KPIParameters] AS KPI OUTER APPLY [dbo].[control] AS CON END ELSE BEGIN SELECT @YEAR = ISNULL(KPI.[Year], ISNULL(CON.[NumValue], YEAR(GETUTCDATE()))) FROM [dbo].[KPIParameters] AS KPI OUTER APPLY (SELECT TOP 1 NUM.[NumValue] FROM [dbo].[CtrlNum] AS NUM WHERE NUM.[Code] = ''CurYear'') AS CON SELECT @PERIOD = ISNULL(KPI.[Period], ISNULL(CON.[NumValue], MONTH(GETUTCDATE()))) FROM [dbo].[KPIParameters] AS KPI OUTER APPLY (SELECT TOP 1 NUM.[NumValue] FROM [dbo].[CtrlNum] AS NUM WHERE NUM.[Code] = ''CurPeriod'') AS CON END /* This is very unwieldy but not much choice when converting time values stored as strings into time formats (7.5 -> 07:30)*/ SELECT @FEESISSUEDYTD = FLOOR(SUM(BDG.[FeesBudget])) FROM [dbo].[HandlerBudgets] AS BDG WHERE BDG.[Handler] = @handler AND BDG.[Month] <= @PERIOD AND BDG.[Year] = @YEAR /*FeesYearToDate*/ IF @IsSAM4 = 0 BEGIN SET @NCOMMAND = '' SELECT @FeesYTD = ISNULL(SUM(CASE WHEN BH.[INVCR] = ''''I'''' THEN BD.[VALUE] ELSE 0 END - CASE WHEN BH.[INVCR] = ''''C'''' THEN BD.[VALUE] ELSE 0 END), 0) FROM dbo.[BatchDetails] BD INNER JOIN dbo.[BatchH] BH ON BD.[BATCHNO] = BH.[BATCHNO] AND BH.[POSTED] = ''''Y'''' INNER JOIN [dbo].[KPIParameters] KPI ON KPI.[Year] = BD.[YEAR] AND KPI.[Period] >= BD.[PERNO] WHERE BD.[FEE] = @handler AND BD.[TYPE] = ''''I'''' AND BD.[OUTLAY] = ''''F'''''' END ELSE BEGIN SET @NCOMMAND = '' SELECT @FeesYTD = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0) FROM [dbo].[Transactions] HED INNER JOIN [dbo].[Transactions] TRN ON TRN.[BatchNo] = HED.[BatchNo] AND TRN.[RecType] = ''''D'''' AND TRN.[LineType] = ''''F'''' AND TRN.[XnType] = ''''I'''' AND TRN.[Posted] = ''''Y'''' AND ( TRN.[CorrectionInd] = ''''L'''' OR TRN.[CorrectionInd] = ''''X'''') AND TRN.[FeeEarner] = @handler INNER JOIN [dbo].[KPIParameters] KPI ON KPI.[Year] = HED.[PostingYear] AND KPI.[Period] >= HED.[PostingPeriod] WHERE HED.[RecType] = ''''H'''''' END EXECUTE sp_executesql @NCOMMAND, N''@handler VARCHAR(10),@FeesYTD DECIMAL(18, 2) OUTPUT'', @handler = @handler, @FeesYTD=@FeesYTD OUTPUT /*FeesLastYearToDate*/ IF @IsSAM4 = 0 BEGIN SET @NCOMMAND = ''SELECT @FeesLastYTD = ISNULL(SUM(CASE WHEN BH.[INVCR] = ''''I'''' THEN BD.[VALUE] ELSE 0 END - CASE WHEN BH.[INVCR] = ''''C'''' THEN BD.[VALUE] ELSE 0 END), 0) FROM dbo.[BatchDetails] BD INNER JOIN dbo.[BatchH] BH ON BD.[BATCHNO] = BH.[BATCHNO] AND BH.[POSTED] = ''''Y'''' INNER JOIN [dbo].[KPIParameters] KPI ON KPI.[Year]-1 = BD.[YEAR] AND KPI.[Period] >= BD.[PERNO] WHERE BD.[FEE] = @handler AND BD.[TYPE] = ''''I'''' AND BD.[OUTLAY] = ''''F'''''' END ELSE BEGIN SET @NCOMMAND = ''SELECT @FeesLastYTD = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0) FROM [dbo].[Transactions] HED INNER JOIN [dbo].[Transactions] TRN ON TRN.[BatchNo] = HED.[BatchNo] AND TRN.[RecType] = ''''D'''' AND TRN.[LineType] = ''''F'''' AND TRN.[XnType] = ''''I'''' AND TRN.[Posted] = ''''Y'''' AND TRN.[CorrectionInd] IN (''''L'''', ''''X'''') AND TRN.[FeeEarner] = @handler INNER JOIN [dbo].[KPIParameters] KPI ON KPI.[Year]-1 = HED.[PostingYear] AND KPI.[Period] >= HED.[PostingPeriod] WHERE HED.[RecType] = ''''H'''''' END EXECUTE sp_executesql @NCOMMAND, N''@handler VARCHAR(10),@FeesLastYTD DECIMAL(18, 2) OUTPUT'', @handler = @handler, @FeesLastYTD=@FeesLastYTD OUTPUT SELECT ( SELECT REPLACE(CONVERT(VARCHAR, ROUND(ISNULL(@FEESISSUEDYTD, 0), 0), 1), ''.00'', '''') AS [TargetYearToDate], REPLACE(CONVERT(VARCHAR, CAST(ROUND(ISNULL(@FeesYTD, 0), 0) AS MONEY), 1), ''.00'', '''') AS [FeesIssuedYearToDate], REPLACE(CONVERT(VARCHAR, CAST(ROUND(ISNULL(@FeesLastYTD, 0), 0) AS MONEY), 1), ''.00'', '''') AS [FeesIssuedLastYearToDate] FOR XML PATH(''Result'')) AS [Result] ' where templateid=1002 GO UPDATE UITemplates SET TemplateQuery= ' DECLARE @BitWiseID BIGINT DECLARE @GETDATE DATETIME SET @fromdate=''18010101'' SET @todate=''21990101''; SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT @BitWiseID = HNG.[BitWiseID] FROM HandlerNETGroupMembership HNG WHERE HNG.[HANDLER] = CONVERT(VARCHAR(10), @handler) SET @BitWiseID = ISNULL(@BitWiseID, [dbo].[KAAS_FN_GetOldPermissions](RTRIM(ISNULL(@handler, '''')))) IF (@handler = ''ADM'') BEGIN SET @BitWiseID = CONVERT(BIGINT, -1) END /*Today Critical Task*/ SELECT ( SELECT ISNULL(ADT.[Critical Tasks], 0) AS [HeaderCount], ''CriticalTasks'' as HeaderLabel FROM ( SELECT SUM(CASE WHEN DEL.[DATE] < @fromdate THEN 0 WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR, @todate, 112))) THEN 0 WHEN [DIA].[ActionType] = ''C'' THEN 1 ELSE 0 END) AS [Critical Tasks] FROM [dbo].[DiaryDelegations] DEL INNER JOIN [dbo].[Diary] DIA INNER JOIN [dbo].[matters] MAT INNER JOIN [dbo].[Contacts] CON ON CON.[CODE] = MAT.[CLIENTCODE] LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP ON MNP.[MATTER] = MAT.[Code] ON MAT.[CODE] = DIA.[CASECODE] AND ISNULL(MAT.[Closed], ''N'') <> ''Y'' ON DIA.[ACTIONID] = DEL.[ACTIONID] WHERE DEL.[HANDLER] = @Handler AND DEL.[STATUS] = 0 AND DEL.[DelType] <> ''Completed'' AND DEL.[DelType] <> ''Processed'' AND DEL.[DelType] <> ''Returned'' AND ( ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0 OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT FOR XML PATH(''Result'')) AS Result ' where TemplateId=1006 GO Update UITemplates set TemplateQuery=' DECLARE @BitWiseID BIGINT DECLARE @GETDATE DATETIME SET @fromdate=''18010101'' SET @todate=''21990101'' SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT @BitWiseID = HNG.[BitWiseID] FROM HandlerNETGroupMembership HNG WHERE HNG.[HANDLER] = CONVERT(VARCHAR(10), @handler) SET @BitWiseID = ISNULL(@BitWiseID, [dbo].[KAAS_FN_GetOldPermissions](RTRIM(ISNULL(@handler, '''')))) IF (@handler = ''ADM'') BEGIN SET @BitWiseID = CONVERT(BIGINT, -1) END /*Today Court Dates*/ SELECT ( SELECT ISNULL(ADT.[Court Dates], 0) AS [HeaderCount],''CourtDates'' as HeaderLabel FROM ( SELECT SUM(CASE WHEN DEL.[DATE] < @fromdate THEN 0 WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR, @todate, 112))) THEN 0 WHEN [DIA].[ActionType] = ''O'' THEN 1 ELSE 0 END) AS [Court Dates] FROM [dbo].[DiaryDelegations] DEL INNER JOIN [dbo].[Diary] DIA INNER JOIN [dbo].[matters] MAT INNER JOIN [dbo].[Contacts] CON ON CON.[CODE] = MAT.[CLIENTCODE] LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP ON MNP.[MATTER] = MAT.[Code] ON MAT.[CODE] = DIA.[CASECODE] AND ISNULL(MAT.[Closed], ''N'') <> ''Y'' ON DIA.[ACTIONID] = DEL.[ACTIONID] WHERE DEL.[HANDLER] = @Handler AND DEL.[STATUS] = 0 AND DEL.[DelType] <> ''Completed'' AND DEL.[DelType] <> ''Processed'' AND DEL.[DelType] <> ''Returned'' AND ( ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0 OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT FOR XML PATH(''Result''))as [Result] ' where TemplateId=1007 GO Update UITemplates set TemplateQuery='DECLARE @BitWiseID BIGINT DECLARE @GETDATE DATETIME SET @Fromdate=''18010101'' SET @todate=''21990101'' SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT @BitWiseID = HNG.[BitWiseID] FROM HandlerNETGroupMembership HNG WHERE HNG.[HANDLER] = CONVERT(VARCHAR(10), @handler) SET @BitWiseID = ISNULL(@BitWiseID, [dbo].[KAAS_FN_GetOldPermissions](RTRIM(ISNULL(@handler, '''')))) IF (@handler = ''ADM'') BEGIN SET @BitWiseID = CONVERT(BIGINT, -1) END SELECT ( SELECT ISNULL(ADT.[High Priority], 0) AS HeaderCount, ''High Priority''as HeaderLabel FROM ( SELECT SUM(CASE WHEN DEL.[DATE] < @fromdate THEN 0 WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR, @todate, 112))) THEN 0 WHEN [DIA].[PRIORITY] = ''H'' THEN 1 ELSE 0 END) AS [High Priority] FROM [dbo].[DiaryDelegations] DEL INNER JOIN [dbo].[Diary] DIA INNER JOIN [dbo].[matters] MAT INNER JOIN [dbo].[Contacts] CON ON CON.[CODE] = MAT.[CLIENTCODE] LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP ON MNP.[MATTER] = MAT.[Code] ON MAT.[CODE] = DIA.[CASECODE] AND ISNULL(MAT.[Closed], ''N'') <> ''Y'' ON DIA.[ACTIONID] = DEL.[ACTIONID] WHERE DEL.[HANDLER] = @Handler AND DEL.[STATUS] = 0 AND DEL.[DelType] <> ''Completed'' AND DEL.[DelType] <> ''Processed'' AND DEL.[DelType] <> ''Returned'' AND ( ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0 OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT for xml path(''Result'')) as [Result] ' where TemplateId=1008 GO UPDATE UITemplates SET TemplateQuery=' DECLARE @BitWiseID BIGINT DECLARE @GETDATE DATETIME SET @Fromdate=''18010101'' SET @todate=''21990101'' SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT @BitWiseID = HNG.[BitWiseID] FROM HandlerNETGroupMembership HNG WHERE HNG.[HANDLER] = CONVERT(VARCHAR(10), @handler) SET @BitWiseID = ISNULL(@BitWiseID, [dbo].[KAAS_FN_GetOldPermissions](RTRIM(ISNULL(@handler, '''')))) IF (@handler = ''ADM'') BEGIN SET @BitWiseID = CONVERT(BIGINT, -1) END SELECT (SELECT ISNULL(ADT.[Phone Message], 0) AS HeaderCount, ''Phone Message''as HeaderLabel FROM ( SELECT SUM(CASE WHEN DEL.[DATE] < @fromdate THEN 0 WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR, @todate, 112))) THEN 0 WHEN [DIA].[ActionType] = ''T'' THEN 1 ELSE 0 END) AS [Phone Message] FROM [dbo].[DiaryDelegations] DEL INNER JOIN [dbo].[Diary] DIA INNER JOIN [dbo].[matters] MAT INNER JOIN [dbo].[Contacts] CON ON CON.[CODE] = MAT.[CLIENTCODE] LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP ON MNP.[MATTER] = MAT.[Code] ON MAT.[CODE] = DIA.[CASECODE] AND ISNULL(MAT.[Closed], ''N'') <> ''Y'' ON DIA.[ACTIONID] = DEL.[ACTIONID] WHERE DEL.[HANDLER] = @Handler AND DEL.[STATUS] = 0 AND DEL.[DelType] <> ''Completed'' AND DEL.[DelType] <> ''Processed'' AND DEL.[DelType] <> ''Returned'' AND ( ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0 OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT for xml path(''Result'')) as [Result] ' WHERE TemplateId=1009 GO UPDATE UITemplates SET TemplateQuery= ' DECLARE @TotalUndertakings INT DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT @TotalUndertakings = count(1) FROM dbo.[Undertakings] UND INNER JOIN [dbo].[matters] MAT ON MAT.[Code] = UND.[MATTER] WHERE UND.[AuthorisedByFE] = @Handler AND MAT.[Closed] <> ''Y'' AND UND.[DISCHARGEDATE] IS NULL SELECT ( SELECT @TotalUndertakings AS HeaderCount, ''Total Undertakings'' AS HeaderLabel for xml path(''Result'')) AS [Result] ' WHERE TemplateId=1010 GO Update UITemplates set TemplateQuery= 'DECLARE @TotalStatuteLimits INT DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT @TotalStatuteLimits = count(1) FROM dbo.[matters] MAT WHERE MAT.[FECode] = @Handler AND DATEDIFF(DD, @GETDATE, CASE WHEN ISNULL(MAT.[StatuteLimits], ''21000101'')=''19000101'' THEN ''21000101'' ELSE MAT.[StatuteLimits] END) <= 60 AND MAT.[Closed] <> ''Y'' SELECT ( SELECT @TotalStatuteLimits AS HeaderCount, ''Statute Date Approaching'' as HeaderLabel FOR XML PATH(''Result'')) AS [Result] ' where TemplateId=1011 GO Update UITemplates set TemplateQuery= 'DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END DECLARE @Open INT SELECT @Open = ISNULL(SUM(CASE WHEN MAT.[Closed] = ''Y'' THEN 0 WHEN MAT.[ClientCode] = ''ZZZZZZ'' THEN 0 WHEN isnumeric(MAT.[matter]) = 0 /*system matters like ZZZZZZ/ZZZ2*/ THEN 0 WHEN convert(int, MAT.[matter]) = 0 /*ignore "0" matter*/ THEN 0 ELSE 1 END), 0) FROM [dbo].[matters] MAT WHERE MAT.[FECode] = @Handler AND MAT.Closed <> ''Y'' SELECT ( SELECT ISNULL(@Open, 0) AS HeaderCount, ''Open Matters'' as HeaderLabel FOR XML PATH(''Result'')) AS [Result] ' where TemplateId=1012 GO Update UITemplates set TemplateQuery= 'DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END DECLARE @Dormant INT DECLARE @MATTERS TABLE ([Code] VARCHAR(20), [LastActionDate] DATETIME) INSERT INTO @MATTERS ([Code], [LastActionDate]) EXEC @Dormant = [dbo].[KAAS_GetDormantMatters] @Handler SELECT ( SELECT ISNULL(@Dormant, 0) AS HeaderCount, ''My dormant Matters'' as HeaderLabel for xml path(''Result'')) AS [Result] ' where TemplateId=1013 GO Update UITemplates set TemplateQuery= 'SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END DECLARE @EffectivelyCompleted INT SELECT @EffectivelyCompleted=ISNULL(SUM(CASE WHEN isnumeric(MAT.[matter]) = 0 /*system matters like ZZZZZZ/ZZZ2*/ THEN 0 WHEN convert(int, MAT.[matter]) = 0 /*ignore "0" matter*/ THEN 0 WHEN ISNULL(MAT.PCODE, '''') = '''' AND ISNULL(PFECODE, '''') = '''' THEN 1 ELSE 0 END),0) FROM [dbo].[matters] MAT WHERE MAT.[FECode] = @Handler AND MAT.Closed <> ''Y'' SELECT ( SELECT ISNULL(@EffectivelyCompleted, 0) AS HeaderCount, ''No of Cases Effectively Complete'' as HeaderLabel for xml path(''Result'')) AS Result ' where TemplateId=1014 GO Update UITemplates set TemplateQuery= 'DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END DECLARE @Expectinvoice INT SELECT @Expectinvoice = ISNULL(SUM(CASE WHEN MAT.[ExpBillD] <= @GETDATE THEN 1 ELSE 0 END), 0) FROM [dbo].[matters] MAT WHERE MAT.[FECode] = @Handler AND MAT.Closed <> ''Y'' SELECT ( SELECT ISNULL(@Expectinvoice, 0) AS HeaderCount, ''No. of Cases where expected invoice date has passed'' as HeaderLabel FOR XML PATH(''Result'')) AS [Result] ' where TemplateId=1015 GO Update UITemplates set TemplateQuery= 'DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END DECLARE @Approachingestimatedfee INT DECLARE @EstFeeWarningLimit DECIMAL(19, 2) SELECT @EstFeeWarningLimit = CONVERT(DECIMAL(19, 2), ISNULL([SET].[KeyValue], ''0'')) FROM [dbo].[Settings] [SET] WHERE [SET].[KeyName] = ''EstFeeWarningLimit'' SET @EstFeeWarningLimit = ISNULL(@EstFeeWarningLimit, 0) SELECT @Approachingestimatedfee = ISNULL(SUM(CASE WHEN (COALESCE ((ISNULL(MAT.[ChargeBal], 0) / NULLIF (MAT.[EstFee], 0) * 100), 0)) > @EstFeeWarningLimit THEN 1 ELSE 0 END), 0) FROM [dbo].[matters] MAT WHERE MAT.[FECode] = @Handler AND MAT.Closed <> ''Y'' SELECT ( SELECT ISNULL(@Approachingestimatedfee, 0) AS HeaderCount, ''No. of Cases with approaching estimate fee'' as HeaderLabel for xml path(''Result'')) AS [Result] ' where TemplateId=1016 GO Update UITemplates set TemplateQuery= 'DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END DECLARE @Approachingestimatedfee INT DECLARE @EstFeeWarningLimit DECIMAL(19, 2) SELECT @EstFeeWarningLimit = CONVERT(DECIMAL(19, 2), ISNULL([SET].[KeyValue], ''0'')) FROM [dbo].[Settings] [SET] WHERE [SET].[KeyName] = ''EstFeeWarningLimit'' SET @EstFeeWarningLimit = ISNULL(@EstFeeWarningLimit, 0) SELECT @Approachingestimatedfee = ISNULL(SUM(CASE WHEN (COALESCE ((ISNULL(MAT.[ChargeBal], 0) / NULLIF (MAT.[EstFee], 0) * 100), 0)) > @EstFeeWarningLimit THEN 1 ELSE 0 END), 0) FROM [dbo].[matters] MAT WHERE MAT.[FECode] = @Handler AND MAT.Closed <> ''Y'' SELECT ( SELECT ISNULL(@Approachingestimatedfee, 0) AS HeaderCount, ''No. of Cases with approaching estimate fee'' as HeaderLabel FOR XML PATH(''Result'')) AS [Result] ' where TemplateId=1017 GO Update UITemplates set TemplateQuery= 'DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END DECLARE @MoneyLaunderingchecks INT SELECT @MoneyLaunderingchecks = COUNT(DISTINCT CON.[CODE]) FROM [dbo].[Contacts] CON INNER JOIN [dbo].[matters] MAT ON MAT.[ClientCode] = CON.[Code] AND MAT.[Closed] <> ''Y'' WHERE CON.[FE] = @Handler AND ISNULL(CON.[Approved], 0) <> 1 SELECT ( SELECT ISNULL(@MoneyLaunderingchecks, 0) AS HeaderCount, ''No. of Clients that require money laundering checks'' as HeaderLabel FOR XML PATH(''Result'')) AS Result ' where TemplateId=1018 GO Update UITemplates set TemplateQuery= ' SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END DECLARE @Noestimategiven INT SELECT @Noestimategiven = ISNULL(SUM(CASE WHEN isnumeric(MAT.[matter]) = 0 /*system matters like ZZZZZZ/ZZZ2*/ THEN 0 WHEN convert(int, MAT.[matter]) = 0 /*ignore "0" matter*/ THEN 0 WHEN MAT.[Section68] = ''Y'' THEN 0 ELSE 1 END), 0) FROM [dbo].[matters] MAT WHERE MAT.[FECode] = @Handler AND MAT.Closed <> ''Y'' SELECT ( SELECT ISNULL(@Noestimategiven, 0) AS HeaderCount, ''No. of Cases with no estimate given'' as HeaderLabel FOR XML PATH(''Result'') ) AS Result ' where TemplateId=1019 GO Update UITemplates set TemplateQuery= 'DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END DECLARE @Noestimatefee INT SELECT @Noestimatefee = ISNULL(SUM(CASE WHEN isnumeric(MAT.[matter]) = 0 /*system matters like ZZZZZZ/ZZZ2*/ THEN 0 WHEN convert(int, MAT.[matter]) = 0 /*ignore "0" matter*/ THEN 0 WHEN MAT.[EstFee] = 0 THEN 1 ELSE 0 END), 0) FROM [dbo].[matters] MAT WHERE MAT.[FECode] = @Handler AND MAT.Closed <> ''Y'' SELECT ( SELECT ISNULL(@Noestimatefee, 0) AS HeaderCount, ''No. of Cases with no estimate fee'' as HeaderLabel FOR XML PATH(''Result'')) AS Result ' where TemplateId=1020 GO UPDATE UITemplates SET TemplateQuery= 'DECLARE @NCOMMAND nvarchar(MAX) DECLARE @DraftBillValue DECIMAL(18, 2) DECLARE @IsSAM4 bit SET @IsSAM4 = [dbo].[ISSAM4]() IF @IsSAM4 = 0 BEGIN SET @NCOMMAND = N'' SELECT @DraftBillValue = ISNULL(SUM(ISNULL(BD.[NET], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VATVALUE], 0)), 0) FROM [dbo].[BillHeader] HED INNER JOIN [dbo].[BillDetails] BD ON BD.[DRAFTNO] = HED.[DRAFTNO] WHERE HED.[MATTER] = @CaseCode AND HED.[BILLNO] = 0'' END ELSE BEGIN SET @NCOMMAND = N'' SELECT @DraftBillValue = ISNULL(SUM(ISNULL(BD.[NetValue], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VatValue], 0)), 0) FROM [dbo].[BillHead] HED INNER JOIN [dbo].[BillDetail] BD ON BD.[BillID] = HED.[BillingID] WHERE HED.[MatterCode] = @CaseCode AND HED.[BillStatus] <> ''''Approved'''' AND ISNULL(HED.[ProForma], '''''''') = ''''Y'''' '' END EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20),@DraftBillValue DECIMAL(18, 2) OUTPUT'', @CaseCode = @CaseCode, @DraftBillValue=@DraftBillValue OUTPUT DECLARE @DebtBal DECIMAL(19, 2) DECLARE @DebtBalDate DATETIME SET @DebtBal = 0 IF @IsSAM4 = 1 BEGIN SET @NCOMMAND = N'' SELECT @DebtBal = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[DebtorsValue], 0)), 0)) , @DebtBalDate = MAX(CASE WHEN ISNULL(TRN.[DebtorsValue], 0) = 0 THEN NULL ELSE TRN.[TransDate] END) FROM [dbo].[Transactions] TRN WHERE TRN.[MatterCode] = @CaseCode AND TRN.[Posted] = ''''Y'''' AND TRN.[RecType] <> ''''V'''' AND TRN.[CorrectionInd] = ''''L'''' '' EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @DebtBal DECIMAL(19, 2) OUTPUT, @DebtBalDate DATETIME OUTPUT'', @CaseCode = @CaseCode, @DebtBal = @DebtBal OUTPUT, @DebtBalDate = @DebtBalDate OUTPUT END /*Result Set 0 Matter Data */ SELECT * INTO #Table1 FROM( SELECT CONVERT(DECIMAL(19, 2), CASE WHEN @DebtBal <> 0 THEN @DebtBal ELSE ISNULL(MAT.[DebtBal], 0) END) AS [AccountSummaryValue], ''Debtors A/c'' AS [AccountSummaryLabel] FROM dbo.[matters] MAT WHERE MAT.[Code] = @CaseCode ) AS TBL /*-Result Set 8 Date for Debtor Ledger */ CREATE TABLE #Table2([AccountSummaryProgress] VARCHAR(200),[LastDebtLedgerDate] VARCHAR(200)) IF @IsSAM4 = 0 BEGIN INSERT INTO #Table2 SELECT [dbo].[KAAS_FN_DateDiffVerbose](LDL.[LastDebtLedgerDate], LDL.[TODAY]) AS [AccountSummaryProgress], CASE WHEN DATEDIFF(DAY, LDL.[LastDebtLedgerDate], LDL.[TODAY]) > 0 THEN DATEDIFF(DAY, LDL.[LastDebtLedgerDate], LDL.[TODAY]) ELSE 0 END AS [DebtLedgerOSDays] FROM ( SELECT GetDate() AS [TODAY], MAX(DTL.[DATE]) AS [LastDebtLedgerDate] FROM [dbo].[DebtorsLedger] DTL WHERE DTL.[MATTER] = @CaseCode) LDL END ELSE BEGIN INSERT INTO #Table2 SELECT [dbo].[KAAS_FN_DateDiffVerbose](LDL.[LastDebtLedgerDate], LDL.[TODAY]) AS [AccountSummaryProgress], CASE WHEN DATEDIFF(DAY, LDL.[LastDebtLedgerDate], LDL.[TODAY]) > 0 THEN DATEDIFF(DAY, LDL.[LastDebtLedgerDate], LDL.[TODAY]) ELSE 0 END AS [DebtLedgerOSDays] FROM ( SELECT GetDate() AS [TODAY], @DebtBalDate AS [LastDebtLedgerDate]) LDL END SELECT ( SELECT * FROM #Table1,#Table2 FOR XML PATH(''Result'')) AS [Result] DROP TABLE #Table1,#Table2 ' WHERE TemplateId=1021 GO UPDATE UITemplates SET TemplateQuery= 'DECLARE @NCOMMAND nvarchar(MAX) DECLARE @DraftBillValue DECIMAL(18, 2) DECLARE @IsSAM4 bit SET @IsSAM4 = [dbo].[ISSAM4]() IF @IsSAM4 = 0 BEGIN SET @NCOMMAND = N'' SELECT @DraftBillValue = ISNULL(SUM(ISNULL(BD.[NET], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VATVALUE], 0)), 0) FROM [dbo].[BillHeader] HED INNER JOIN [dbo].[BillDetails] BD ON BD.[DRAFTNO] = HED.[DRAFTNO] WHERE HED.[MATTER] = @CaseCode AND HED.[BILLNO] = 0'' END ELSE BEGIN SET @NCOMMAND = N'' SELECT @DraftBillValue = ISNULL(SUM(ISNULL(BD.[NetValue], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VatValue], 0)), 0) FROM [dbo].[BillHead] HED INNER JOIN [dbo].[BillDetail] BD ON BD.[BillID] = HED.[BillingID] WHERE HED.[MatterCode] = @CaseCode AND HED.[BillStatus] <> ''''Approved'''' AND ISNULL(HED.[ProForma], '''''''') = ''''Y'''''' END EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20),@DraftBillValue DECIMAL(18, 2) OUTPUT'', @CaseCode = @CaseCode, @DraftBillValue=@DraftBillValue OUTPUT DECLARE @OutlayBal DECIMAL(19, 2) DECLARE @OutlayBalDate DATETIME SET @OutlayBal = 0 IF @IsSAM4 = 1 BEGIN SET @NCOMMAND = N'' SELECT @OutlayBal = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[OutlayValue], 0)), 0)), @OutlayBalDate = MAX(CASE WHEN ISNULL(TRN.[OutlayValue], 0) = 0 THEN NULL ELSE TRN.[TransDate] END) FROM [dbo].[Transactions] TRN WHERE TRN.[MatterCode] = @CaseCode AND TRN.[Posted] = ''''Y'''' AND TRN.[RecType] <> ''''V'''' AND TRN.[CorrectionInd] = ''''L'''''' EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @OutlayBal DECIMAL(19, 2) OUTPUT, @OutlayBalDate DATETIME OUTPUT'', @CaseCode = @CaseCode, @OutlayBal = @OutlayBal OUTPUT, @OutlayBalDate = @OutlayBalDate OUTPUT END /*Result Set 0 Matter Data */ SELECT * INTO #Table1 FROM( SELECT CONVERT(DECIMAL(19, 2), CASE WHEN @OutlayBal <> 0 THEN @OutlayBal ELSE ISNULL(MAT.[OutlayBal], 0) END) AS [AccountSummaryValue],''Outlay A/C'' AS [AccountSummaryLabel] FROM dbo.[matters] MAT WHERE MAT.[Code] = @CaseCode ) AS TBL /*Result Set 9 Date for Matter Ledger (Outstanding) */ CREATE TABLE #Table2([AccountSummaryProgress] VARCHAR(200),[LastMatLedgerDate] VARCHAR(200)) IF @IsSAM4 = 0 BEGIN INSERT INTO #Table2 SELECT [dbo].[KAAS_FN_DateDiffVerbose](LDL.[LastMatLedgerDate], LDL.[TODAY]) AS [AccountSummaryProgress], CASE WHEN DATEDIFF(DAY, LDL.[LastMatLedgerDate], LDL.[TODAY]) > 0 THEN DATEDIFF(DAY, LDL.[LastMatLedgerDate], LDL.[TODAY]) ELSE 0 END AS [MatLedgerOSDays] FROM ( SELECT GetDate() AS [TODAY], MAX(MAL.[DATE]) AS [LastMatLedgerDate] FROM [dbo].[MatterLedger] MAL WHERE MAL.[MATTER] = @CaseCode AND ISNULL(MAL.[VALUEO], 0) <> 0) LDL END ELSE BEGIN INSERT INTO #Table2 SELECT [dbo].[KAAS_FN_DateDiffVerbose](LDL.[LastMatLedgerDate], LDL.[TODAY]) AS [MatLedgerOSVerbose], CASE WHEN DATEDIFF(DAY, LDL.[LastMatLedgerDate], LDL.[TODAY]) > 0 THEN DATEDIFF(DAY, LDL.[LastMatLedgerDate], LDL.[TODAY]) ELSE 0 END AS [MatLedgerOSDays] FROM ( SELECT GetDate() AS [TODAY], @OutlayBalDate AS [LastMatLedgerDate]) LDL END SELECT ( SELECT * FROM #Table1,#Table2 FOR XML PATH(''Result'')) AS [Result] DROP TABLE #Table1,#Table2 ' WHERE TemplateId=1022 GO UPDATE UITemplates SET TemplateQuery= 'DECLARE @NCOMMAND nvarchar(MAX) DECLARE @IsSAM4 bit SET @IsSAM4 = [dbo].[ISSAM4]() DECLARE @OutlayBal DECIMAL(19, 2) SET @OutlayBal = 0 IF @IsSAM4 = 1 BEGIN SET @NCOMMAND = N'' SELECT @OutlayBal = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[OutlayValue], 0)), 0)) FROM [dbo].[Transactions] TRN WHERE TRN.[MatterCode] = @CaseCode AND TRN.[Posted] = ''''Y'''' AND TRN.[RecType] <> ''''V'''' AND TRN.[CorrectionInd] = ''''L'''''' EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @OutlayBal DECIMAL(19, 2) OUTPUT'', @CaseCode = @CaseCode, @OutlayBal = @OutlayBal OUTPUT END SELECT ( SELECT MAT.[OutlayBud] AS [OutlayBud], ''Outlay Budget''AS [AccountSummaryLabel] , CASE WHEN MAT.[OutlayBud] = 0 THEN NULL ELSE CONVERT(DECIMAL (19,2),(CONVERT(DECIMAL(19, 2), CASE WHEN @OutlayBal <> 0 THEN @OutlayBal ELSE ISNULL(MAT.[OutlayBal], 0) END) *100)/MAT.[OutlayBud] ) END AS [AccountSummaryProgress] , CONVERT(DECIMAL(19, 2), CASE WHEN @OutlayBal <> 0 THEN @OutlayBal ELSE ISNULL(MAT.[OutlayBal], 0) END) AS [OutlayBal] FROM dbo.[matters] MAT WHERE MAT.[Code] = @CaseCode FOR XML PATH(''Result'') ) as [Result] ' WHERE TemplateId=1023 GO UPDATE UITemplates SET TemplateQuery= 'DECLARE @NCOMMAND nvarchar(MAX) DECLARE @DraftBillValue DECIMAL(18, 2) DECLARE @OutstandingFee DECIMAL(18, 2) DECLARE @IsSAM4 bit SET @IsSAM4 = [dbo].[ISSAM4]() /* Result Set 11 Current outstanding fees */ IF @IsSAM4 = 0 BEGIN SET @NCOMMAND = N'' SELECT @OutstandingFee = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(DTL.[OSFees], 0)), 0)) FROM [dbo].[DebtorsLedger] DTL WHERE DTL.[matter] = @CaseCode'' END ELSE BEGIN SET @NCOMMAND = N'' SELECT @OutstandingFee = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[FeesOS], 0)), 0)) FROM [dbo].[Transactions] TRN WHERE TRN.[MatterCode] = @CaseCode AND TRN.[Posted] = ''''Y'''' AND TRN.[RecType] <> ''''V'''' AND TRN.[CorrectionInd] = ''''L'''''' END EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @OutstandingFee DECIMAL(18,2) OUTPUT'', @CaseCode = @CaseCode, @OutstandingFee = @OutstandingFee OUTPUT SELECT ( SELECT ''Current Outstanding Fees'' AS [AccountSummaryLabel] , @OutstandingFee as OutstandingFee FOR XML PATH(''Result'')) as [Result] ' WHERE TemplateId=1024 GO UPDATE UITemplates SET TemplateQuery= 'DECLARE @NCOMMAND nvarchar(MAX) DECLARE @DraftBillValue DECIMAL(18, 2) DECLARE @IsSAM4 bit SET @IsSAM4 = [dbo].[ISSAM4]() DECLARE @ClientCurrent DECIMAL(19, 2) DECLARE @ClientDeposit DECIMAL(19, 2) SET @ClientCurrent = 0 SET @ClientDeposit = 0 IF @IsSAM4 = 1 BEGIN SET @NCOMMAND = N'' SELECT @ClientCurrent = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[ClientCurrent], 0)), 0)), @ClientDeposit = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[ClientDeposit], 0)), 0)) FROM [dbo].[Transactions] TRN WHERE TRN.[MatterCode] = @CaseCode AND TRN.[Posted] = ''''Y'''' AND TRN.[RecType] <> ''''V'''' AND TRN.[CorrectionInd] = ''''L'''''' EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @ClientCurrent DECIMAL(19, 2) OUTPUT, @ClientDeposit DECIMAL(19, 2) OUTPUT'', @CaseCode = @CaseCode, @ClientCurrent = @ClientCurrent OUTPUT, @ClientDeposit = @ClientDeposit OUTPUT END SELECT ( SELECT CONVERT(DECIMAL(19, 2), CASE WHEN @ClientCurrent <> 0 THEN @ClientCurrent + @ClientDeposit WHEN @ClientDeposit <> 0 THEN @ClientCurrent + @ClientDeposit ELSE ISNULL(MAT.[ClientBal], 0) END) AS [AccountSummaryValue], ''Client A/C'' AS [AccountSummaryLabel] FROM dbo.[matters] MAT WHERE MAT.[Code] = @CaseCode FOR XML PATH(''Result'')) AS [Result] ' WHERE TemplateId=1025 GO UPDATE UITemplates SET TemplateQuery= 'DECLARE @NCOMMAND nvarchar(MAX) DECLARE @DraftBillValue DECIMAL(18, 2) DECLARE @IsSAM4 bit SET @IsSAM4 = [dbo].[ISSAM4]() DECLARE @ClientCurrent DECIMAL(19, 2) SET @ClientCurrent = 0 IF @IsSAM4 = 1 BEGIN SET @NCOMMAND = N'' SELECT @ClientCurrent = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[ClientCurrent], 0)), 0)) FROM [dbo].[Transactions] TRN WHERE TRN.[MatterCode] = @CaseCode AND TRN.[Posted] = ''''Y'''' AND TRN.[RecType] <> ''''V'''' AND TRN.[CorrectionInd] = ''''L'''''' EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @ClientCurrent DECIMAL(19, 2) OUTPUT'', @CaseCode = @CaseCode, @ClientCurrent = @ClientCurrent OUTPUT END SELECT ( SELECT CONVERT(DECIMAL(19, 2), CASE WHEN @ClientCurrent <> 0 THEN @ClientCurrent ELSE ISNULL(MAT.[ClientCurBal], 0) END) AS [AccountSummaryValue], ''Client Current'' AS [AccountSummaryLabel] FROM dbo.[matters] MAT WHERE MAT.[Code] = @CaseCode FOR XML PATH(''Result'')) AS [Result] ' WHERE TemplateId=1026 GO UPDATE UITemplates SET TemplateQuery= 'DECLARE @NCOMMAND nvarchar(MAX) DECLARE @DraftBillValue DECIMAL(18, 2) DECLARE @IsSAM4 bit SET @IsSAM4 = [dbo].[ISSAM4]() DECLARE @ClientDeposit DECIMAL(19, 2) SET @ClientDeposit = 0 IF @IsSAM4 = 1 BEGIN SET @NCOMMAND = N'' SELECT @ClientDeposit = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[ClientDeposit], 0)), 0)) FROM [dbo].[Transactions] TRN WHERE TRN.[MatterCode] = @CaseCode AND TRN.[Posted] = ''''Y'''' AND TRN.[RecType] <> ''''V'''' AND TRN.[CorrectionInd] = ''''L'''''' EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @ClientDeposit DECIMAL(19, 2) OUTPUT'', @CaseCode = @CaseCode, @ClientDeposit = @ClientDeposit OUTPUT END /*Result Set 0 Matter Data*/ select ( SELECT CONVERT(DECIMAL(19, 2), CASE WHEN @ClientDeposit <> 0 THEN @ClientDeposit ELSE ISNULL(MAT.[ClientDepBal], 0) END) AS [AccountSummaryValue], ''Client Deposit'' AS [AccountSummaryLabel] FROM dbo.[matters] MAT WHERE MAT.[Code] = @CaseCode FOR XML PATH(''Result'')) as [Result] ' WHERE TemplateId=1027 GO UPDATE UITemplates SET TemplateQuery= 'DECLARE @NCOMMAND nvarchar(MAX) DECLARE @DraftBillValue DECIMAL(18, 2) DECLARE @IsSAM4 bit SET @IsSAM4 = [dbo].[ISSAM4]() DECLARE @ChargeBal DECIMAL(19, 2) SELECT @ChargeBal = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N'' THEN 0 WHEN @IsSAM4 = 1 AND ISNULL(TIE.[InvoiceNo], 0) <> 0 THEN 0 WHEN @IsSAM4 = 1 THEN ISNULL(TIE.[Charge], 0) ELSE ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0) END), 0) FROM [dbo].[TimeEntry] TIE WHERE TIE.[Matter] = @CaseCode AND ( (TIE.[TimeOrCharge] = ''T'') OR (TIE.[TimeOrCharge] = ''C'')) SELECT ( SELECT @ChargeBal AS [AccountSummaryValue], ''Total Work In Progress'' AS [AccountSummaryLabel] , CASE WHEN MAT.[EstFee]=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),(@ChargeBal*100)/MAT.[EstFee]) END AS [AccountSummaryProgress], MAT.[EstFee] AS [EstimateFee] FROM dbo.[matters] MAT WHERE MAT.[Code] = @CaseCode FOR XML PATH(''Result'')) as [Result] ' WHERE TemplateId=1028 GO UPDATE UITemplates SET TemplateQuery= '/*Write off time*/ SELECT ( SELECT SUM(CONVERT(DECIMAL(18, 2), ISNULL(TIE.[Charge], 0))) AS [AccountSummaryValue] , ''Write Off Time'' AS [AccountSummaryLabel] FROM [dbo].[matters] MAT INNER JOIN [dbo].[TimeEntry] TIE ON TIE.[MATTER] = MAT.[Code] WHERE TIE.[MATTER] = @CaseCode AND ( (TIE.[TimeOrCharge] = ''T'') OR (TIE.[TimeOrCharge] = ''C'')) AND TIE.[Task] = ''WRI'' for xml path(''Result'')) as Result ' WHERE TemplateId=1029 GO UPDATE UITemplates SET TemplateQuery= 'DECLARE @NCOMMAND nvarchar(MAX) DECLARE @IsSAM4 bit SET @IsSAM4 = [dbo].[ISSAM4]() DECLARE @EstimateFee DECIMAL(18,2) DECLARE @TotalFeesBilled DECIMAL(18,2) DECLARE @AccountSummaryProgress DECIMAL(18,2) SET @EstimateFee=( SELECT MAT.[EstFee] FROM dbo.[matters] MAT WHERE MAT.[Code] = @CaseCode) /*Result Set 10 The fees issued to date */ IF @IsSAM4 = 0 BEGIN SET @NCOMMAND = N'' SELECT @TotalFeesBilled = (ISNULL(SUM((CASE WHEN HED.[INVCR] = ''''I'''' THEN TRN.[VALUE] ELSE TRN.[VALUE] * - 1 END)), 0) ), @AccountSummaryProgress = ( CASE WHEN @EstimateFee = 0 THEN NULL ELSE CONVERT(DECIMAL (19,2),(ISNULL(SUM((CASE WHEN HED.[INVCR] = ''''I'''' THEN TRN.[VALUE] ELSE TRN.[VALUE] * - 1 END)), 0)*100)/ @EstimateFee) END ) FROM [dbo].[BatchH] HED INNER JOIN [dbo].[BatchDetails] TRN ON TRN.[BATCHNO] = HED.[BATCHNO] WHERE HED.[MATTER] = @CaseCode AND HED.[POSTED] = ''''Y'''' AND TRN.[TYPE] = ''''I'''' AND TRN.[OUTLAY] = ''''F'''''' END ELSE BEGIN SET @NCOMMAND = N'' DECLARE @TotalFeesBilled DECIMAL(22, 2) SELECT @TotalFeesBilled = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0) FROM [dbo].[TransactionsHeaders] HED INNER JOIN [dbo].[Transactions] TRN ON TRN.[BatchNo] = HED.[BatchNo] AND TRN.[RecType] = ''''D'''' AND TRN.[LineType] = ''''F'''' AND TRN.[XnType] = ''''I'''' AND TRN.[Posted] = ''''Y'''' AND ( TRN.[CorrectionInd] = ''''L'''' OR TRN.[CorrectionInd] = ''''X'''') WHERE HED.[MatterCode] = @CaseCode /*values imported from Axxia*/ SELECT @TotalFeesBilled = @TotalFeesBilled + ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0) FROM [dbo].[Transactions] TRN LEFT OUTER JOIN [dbo].[TransactionsHeaders] HED ON HED.[BatchNo] = TRN.[BatchNo] WHERE TRN.[MatterCode] = @CaseCode AND TRN.[RecType] = ''''D'''' AND TRN.[LineType] = ''''F'''' AND TRN.[XnType] = ''''I'''' AND TRN.[Posted] = ''''Y'''' AND ( TRN.[CorrectionInd] = ''''L'''' OR TRN.[CorrectionInd] = ''''X'''') AND HED.[BatchNo] IS NULL SELECT @AccountSummaryProgress = CONVERT(DECIMAL (19,2),(@TotalFeesBilled*100)/@EstimateFee) '' END EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20) , @EstimateFee INT, @TotalFeesBilled DECIMAL(18,2) OUTPUT, @AccountSummaryProgress DECIMAL(18,2) OUTPUT'', @CaseCode = @CaseCode ,@EstimateFee=@EstimateFee, @TotalFeesBilled = @TotalFeesBilled OUTPUT, @AccountSummaryProgress = @AccountSummaryProgress OUTPUT SELECT ( SELECT ''Fees Issued To Date'' AS [AccountSummaryLabel], @EstimateFee as [EstimateFee], @TotalFeesBilled as TotalFeesBilled, @AccountSummaryProgress as AccountSummaryProgress FOR XML PATH(''Result'')) as [Result] ' WHERE TemplateId=1030 GO UPDATE UITemplates SET TemplateQuery= ' DECLARE @NCOMMAND nvarchar(MAX) DECLARE @IsSAM4 bit SET @IsSAM4 = [dbo].[ISSAM4]() declare @chargeBal decimal(18,2) declare @FeesIssued decimal(18,2) declare @EstimateFee decimal(18,2) select @EstimateFee= ISNULL(EstFee,0) from matters where code = @CaseCode select @chargeBal = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N'' THEN 0 WHEN @IsSAM4 = 1 AND ISNULL(TIE.[InvoiceNo], 0) <> 0 THEN 0 WHEN @IsSAM4 = 1 THEN ISNULL(TIE.[Charge], 0) ELSE ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0) END), 0) from TimeEntry TIE where Matter = @CaseCode AND ( (TIE.[TimeOrCharge] = ''T'') OR (TIE.[TimeOrCharge] = ''C'')) IF @IsSAM4 = 0 BEGIN SET @NCOMMAND = N'' SELECT @FeesIssued = ISNULL(SUM((CASE WHEN HED.[INVCR] = ''''I'''' THEN TRN.[VALUE] ELSE TRN.[VALUE] * - 1 END)), 0) FROM [dbo].[BatchH] HED INNER JOIN [dbo].[BatchDetails] TRN ON TRN.[BATCHNO] = HED.[BATCHNO] WHERE HED.[MATTER] = @CaseCode AND HED.[POSTED] = ''''Y'''' AND TRN.[TYPE] = ''''I'''' AND TRN.[OUTLAY] = ''''F'''' '' END ELSE BEGIN SET @NCOMMAND = N'' DECLARE @TotalFeesBilled DECIMAL(22, 2) SELECT @TotalFeesBilled = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0) FROM [dbo].[TransactionsHeaders] HED INNER JOIN [dbo].[Transactions] TRN ON TRN.[BatchNo] = HED.[BatchNo] AND TRN.[RecType] = ''''D'''' AND TRN.[LineType] = ''''F'''' AND TRN.[XnType] = ''''I'''' AND TRN.[Posted] = ''''Y'''' AND ( TRN.[CorrectionInd] = ''''L'''' OR TRN.[CorrectionInd] = ''''X'''') WHERE HED.[MatterCode] = @CaseCode --values imported from Axxia SELECT @FeesIssued = @TotalFeesBilled + ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0) FROM [dbo].[Transactions] TRN LEFT OUTER JOIN [dbo].[TransactionsHeaders] HED ON HED.[BatchNo] = TRN.[BatchNo] WHERE TRN.[MatterCode] = @CaseCode AND TRN.[RecType] = ''''D'''' AND TRN.[LineType] = ''''F'''' AND TRN.[XnType] = ''''I'''' AND TRN.[Posted] = ''''Y'''' AND ( TRN.[CorrectionInd] = ''''L'''' OR TRN.[CorrectionInd] = ''''X'''') AND HED.[BatchNo] IS NULL '' END EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @chargeBal DECIMAL(18,2) OUTPUT, @FeesIssued DECIMAL(18,2) OUTPUT, @EstimateFee DECIMAL(18,2) OUTPUT'', @CaseCode = @CaseCode, @chargeBal = @chargeBal OUTPUT, @FeesIssued = @FeesIssued OUTPUT, @EstimateFee = @EstimateFee OUTPUT SELECT ( SELECT ''Fees To Date + WIP'' AS WidgetName, @FeesIssued AS [FeesIssued], @chargeBal AS [ChargeBalance] , @EstimateFee as [EstimateFee] FOR XML PATH(''Result'')) AS [Result] ' WHERE TemplateId=1031 GO UPDATE UITemplates SET TemplateQuery= ' DECLARE @NCOMMAND nvarchar(MAX) DECLARE @IsSAM4 bit SET @IsSAM4 = [dbo].[ISSAM4]() DECLARE @ChargeBal decimal(18,2) DECLARE @EstimateFee decimal(18,2) DECLARE @FeesIssued decimal(18,2) DECLARE @Section68 VARCHAR(1) SELECT @EstimateFee = Mat.EstFee, @Section68 = Mat.Section68 FROM [dbo].[matters] MAT WHERE MAT.Code = @CaseCode select @ChargeBal = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N'' THEN 0 WHEN @IsSAM4 = 1 AND ISNULL(TIE.[InvoiceNo], 0) <> 0 THEN 0 WHEN @IsSAM4 = 1 THEN ISNULL(TIE.[Charge], 0) ELSE ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0) END), 0) from TimeEntry TIE WHERE TIE.[Matter] = @CaseCode AND ( (TIE.[TimeOrCharge] = ''T'') OR (TIE.[TimeOrCharge] = ''C'')) IF @IsSAM4 = 0 BEGIN SET @NCOMMAND = N'' SELECT @FeesIssued = ISNULL(SUM((CASE WHEN HED.[INVCR] = ''''I'''' THEN TRN.[VALUE] ELSE TRN.[VALUE] * - 1 END)), 0) FROM [dbo].[BatchH] HED INNER JOIN [dbo].[BatchDetails] TRN ON TRN.[BATCHNO] = HED.[BATCHNO] WHERE HED.[MATTER] = @CaseCode AND HED.[POSTED] = ''''Y'''' AND TRN.[TYPE] = ''''I'''' AND TRN.[OUTLAY] = ''''F'''' '' END ELSE BEGIN SET @NCOMMAND = N'' SELECT @FeesIssued = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0) FROM [dbo].[TransactionsHeaders] HED INNER JOIN [dbo].[Transactions] TRN ON TRN.[BatchNo] = HED.[BatchNo] AND TRN.[RecType] = ''''D'''' AND TRN.[LineType] = ''''F'''' AND TRN.[XnType] = ''''I'''' AND TRN.[Posted] = ''''Y'''' AND ( TRN.[CorrectionInd] = ''''L'''' OR TRN.[CorrectionInd] = ''''X'''') WHERE HED.[MatterCode] = @CaseCode --values imported from Axxia SELECT @FeesIssued = @FeesIssued + ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0) FROM [dbo].[Transactions] TRN LEFT OUTER JOIN [dbo].[TransactionsHeaders] HED ON HED.[BatchNo] = TRN.[BatchNo] WHERE TRN.[MatterCode] = @CaseCode AND TRN.[RecType] = ''''D'''' AND TRN.[LineType] = ''''F'''' AND TRN.[XnType] = ''''I'''' AND TRN.[Posted] = ''''Y'''' AND ( TRN.[CorrectionInd] = ''''L'''' OR TRN.[CorrectionInd] = ''''X'''') AND HED.[BatchNo] IS NULL '' END EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @ChargeBal DECIMAL(18,2) OUTPUT, @EstimateFee DECIMAL(18,2) OUTPUT , @FeesIssued DECIMAL(18,2) OUTPUT, @Section68 VARCHAR(1) OUTPUT'', @CaseCode = @CaseCode,@ChargeBal = @ChargeBal OUTPUT, @EstimateFee = @EstimateFee OUTPUT, @FeesIssued = @FeesIssued OUTPUT,@Section68 = @Section68 OUTPUT SELECT ( SELECT ''Estimate Fees'' as WidgetName, @FeesIssued AS [FeesIssued], @ChargeBal as [ChargeBal],@EstimateFee as [EstimateFee], @Section68 as [Section68] FOR XML PATH(''Result'')) as [Result] ' WHERE TemplateId=1032 GO UPDATE UITemplates SET TemplateQuery= 'DECLARE @NCOMMAND nvarchar(MAX) DECLARE @DraftBillValue DECIMAL(18, 2) DECLARE @IsSAM4 bit SET @IsSAM4 = [dbo].[ISSAM4]() IF @IsSAM4 = 0 BEGIN SET @NCOMMAND = N'' SELECT @DraftBillValue = ISNULL(SUM(ISNULL(BD.[NET], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VATVALUE], 0)), 0) FROM [dbo].[BillHeader] HED INNER JOIN [dbo].[BillDetails] BD ON BD.[DRAFTNO] = HED.[DRAFTNO] WHERE HED.[MATTER] = @CaseCode AND HED.[BILLNO] = 0'' END ELSE BEGIN SET @NCOMMAND = N'' SELECT @DraftBillValue = ISNULL(SUM(ISNULL(BD.[NetValue], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VatValue], 0)), 0) FROM [dbo].[BillHead] HED INNER JOIN [dbo].[BillDetail] BD ON BD.[BillID] = HED.[BillingID] WHERE HED.[MatterCode] = @CaseCode AND HED.[BillStatus] <> ''''Approved'''' AND ISNULL(HED.[ProForma], '''''''') = ''''Y'''''' END EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20),@DraftBillValue DECIMAL(18, 2) OUTPUT'', @CaseCode = @CaseCode, @DraftBillValue=@DraftBillValue OUTPUT SELECT ( SELECT @DraftBillValue AS [AccountSummaryValue], ''Draft Invoices'' AS [AccountSummaryLabel] FOR XML PATH(''Result'')) as [Result] ' WHERE TemplateId=1033 GO UPDATE UITemplates SET TemplateQuery= 'DECLARE @NCOMMAND nvarchar(MAX) DECLARE @DraftBillValue DECIMAL(18, 2) DECLARE @IsSAM4 bit SET @IsSAM4 = [dbo].[ISSAM4]() DECLARE @TIEDATE DATETIME IF [dbo].[ISSAM4]() = 0 BEGIN SET @NCOMMAND = N'' SELECT @TIEDATE = MAX(CASE WHEN DLG.[DATE] IS NULL THEN CONVERT(DATETIME, NULL) WHEN DLG.[DATE] > GETDATE() THEN CONVERT(DATETIME, NULL) ELSE DLG.[DATE] END) FROM [dbo].[DebtorsLedger] DLG WHERE DLG.[MATTER] = @CaseCode'' END ELSE BEGIN SET @NCOMMAND = N'' SELECT @TIEDATE = MAX(CASE WHEN TRN.[TransDate] IS NULL THEN CONVERT(DATETIME, NULL) WHEN TRN.[TransDate] > GETDATE() THEN CONVERT(DATETIME, NULL) ELSE TRN.[TransDate] END) FROM [dbo].[Transactions] TRN WHERE TRN.[Posted] = ''''Y'''' AND TRN.[MatterCode] = @CaseCode AND TRN.[DebtorsValue] <> 0 AND TRN.[CorrectionInd] = ''''L'''' AND ( ( TRN.[XnType] = ''''I'''' AND TRN.[RecType] = ''''H'''') OR ( TRN.[XnType] = ''''J'''' AND TRN.[RecType] = ''''D''''))'' END EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @TIEDATE DATETIME OUTPUT'', @CaseCode = @CaseCode, @TIEDATE = @TIEDATE OUTPUT SELECT ( SELECT @TIEDATE AS [AccountSummaryValue], ''Last Bill Date'' AS [AccountSummaryLabel] FROM dbo.[matters] MAT WHERE MAT.[Code] = @CaseCode FOR XML PATH(''Result'') ) AS [Result] ' WHERE TemplateId=1034 GO UPDATE UITemplates SET TemplateQuery= ' SELECT ( SELECT MAT.[ExpBillD] AS [AccountSummaryValue], ''Expected Bill Date'' AS [AccountSummaryLabel] FROM dbo.[matters] MAT WHERE MAT.[Code] = @CaseCode FOR XML PATH(''Result'')) as [Result] ' WHERE TemplateId=1035 GO UPDATE UITemplates SET TemplateQuery= ' DECLARE @NCOMMAND nvarchar(MAX) DECLARE @IsSAM4 bit SET @IsSAM4 = [dbo].[ISSAM4]() declare @activityTotal decimal(18,2) declare @TotalFeesBilled decimal(18,2) SELECT @activityTotal = SUM(CONVERT(DECIMAL(18, 2), ISNULL(TIE.[Charge], 0))) FROM [dbo].[matters] MAT INNER JOIN [dbo].[TimeEntry] TIE ON TIE.[MATTER] = MAT.[Code] WHERE TIE.[MATTER] = @CaseCode AND ( (TIE.[TimeOrCharge] = ''T'') OR (TIE.[TimeOrCharge] = ''C'')) AND TIE.[TASK] <> ''WRI'' IF @IsSAM4 = 0 BEGIN SET @NCOMMAND = N'' SELECT @TotalFeesBilled = ISNULL(SUM((CASE WHEN HED.[INVCR] = ''''I'''' THEN TRN.[VALUE] ELSE TRN.[VALUE] * - 1 END)), 0) FROM [dbo].[BatchH] HED INNER JOIN [dbo].[BatchDetails] TRN ON TRN.[BATCHNO] = HED.[BATCHNO] WHERE HED.[MATTER] = @CaseCode AND HED.[POSTED] = ''''Y'''' AND TRN.[TYPE] = ''''I'''' AND TRN.[OUTLAY] = ''''F'''' '' END ELSE BEGIN SET @NCOMMAND = N'' DECLARE @TotalFeesBilled DECIMAL(22, 2) SELECT @TotalFeesBilled = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0) FROM [dbo].[TransactionsHeaders] HED INNER JOIN [dbo].[Transactions] TRN ON TRN.[BatchNo] = HED.[BatchNo] AND TRN.[RecType] = ''''D'''' AND TRN.[LineType] = ''''F'''' AND TRN.[XnType] = ''''I'''' AND TRN.[Posted] = ''''Y'''' AND ( TRN.[CorrectionInd] = ''''L'''' OR TRN.[CorrectionInd] = ''''X'''') WHERE HED.[MatterCode] = @CaseCode --values imported from Axxia SELECT @TotalFeesBilled = @TotalFeesBilled + ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0) FROM [dbo].[Transactions] TRN LEFT OUTER JOIN [dbo].[TransactionsHeaders] HED ON HED.[BatchNo] = TRN.[BatchNo] WHERE TRN.[MatterCode] = @CaseCode AND TRN.[RecType] = ''''D'''' AND TRN.[LineType] = ''''F'''' AND TRN.[XnType] = ''''I'''' AND TRN.[Posted] = ''''Y'''' AND ( TRN.[CorrectionInd] = ''''L'''' OR TRN.[CorrectionInd] = ''''X'''') AND HED.[BatchNo] IS NULL '' END EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @activityTotal DECIMAL(18,2) OUTPUT, @TotalFeesBilled DECIMAL(18,2) OUTPUT'', @CaseCode = @CaseCode, @activityTotal = @activityTotal OUTPUT , @TotalFeesBilled = @TotalFeesBilled OUTPUT SELECT ( SELECT @TotalFeesBilled AS [FeesIssued], @activityTotal AS [ActivityTotal] FOR XML PATH(''Result'')) as [Result] ' WHERE TemplateId=1036 GO UPDATE UITemplates SET TemplateQuery= ' SELECT ( SELECT MAT.[Value] AS [AccountSummaryValue], ''Possible Value To The Client'' AS [AccountSummaryLabel] FROM dbo.[matters] MAT WHERE MAT.[Code] = @CaseCode FOR XML PATH(''Result'')) AS ''Result'' ' WHERE TemplateId=1037 GO UPDATE UITemplates SET TemplateQuery= 'SELECT ( SELECT ''Anti Money Laundering Checked'' as WidgetName, Approved, ApprovedDate FROM Contacts WHERE CODE = SUBSTRING(@casecode,0,CHARINDEX(''/'',@casecode,0)) FOR XML PATH(''Result'')) AS ''Result'' ' WHERE TemplateId=1038 GO UPDATE UITemplates SET TemplateQuery= ' DECLARE @IsSAM4 bit DECLARE @ChargeBal DECIMAL(18,2) SET @IsSAM4 = [dbo].[ISSAM4]() SELECT @ChargeBal = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N'' THEN 0 WHEN @IsSAM4 = 1 AND ISNULL(TIE.[InvoiceNo], 0) <> 0 THEN 0 WHEN @IsSAM4 = 1 THEN ISNULL(TIE.[Charge], 0) ELSE ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0) END), 0) FROM TimeEntry TIE WHERE TIE.[Matter] = @casecode AND ( (TIE.[TimeOrCharge] = ''T'') OR (TIE.[TimeOrCharge] = ''C'')) SELECT ( SELECT ''Current Locked Up Value'' as WidgetName, @ChargeBal as ChargeBal, OutlayBal, DebtBal FROM matters WHERE Code = @casecode FOR XML PATH(''Result'') )as ''Result'' ' WHERE TemplateId=1039 GO -------------------------------------------Panels Insert Start--------------------------------------------------------- SET IDENTITY_INSERT [dbo].[Panels] ON GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 1) BEGIN INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (1, N'Tasks', N'Panel to display all the tasks', 'tasks', 100) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 2) BEGIN INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (2, N'Fees Issued', N'Fees Issued Information Panel', 'tasks', 50) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 3) BEGIN INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (3, N'Account Summary', N'Account Summary', 'caseDiary', 100) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 4) BEGIN INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (4, N'Fees Issued', N'Fees Issued Information Panel', null, 0) END GO SET IDENTITY_INSERT [dbo].[Panels] OFF GO ---------------------------------------------Panels Insert End----------------------------------------------------------- ------------------------------------------UI Templates Insert Start------------------------------------------------------- SET IDENTITY_INSERT [dbo].[UITemplates] ON GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1000) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1000, N'Over due Tasks', N'Template for over due tasks', N'', N'overDueTasksQueryComponent', N'overDueTasksDisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1001) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1001, N'Outstanding Tasks', N'Template for Outstanding Tasks', N'', N'Outstanding Tasks QueryComponent', N'Outstanding Tasks DisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1002) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1002, N'Pending Tasks', N'Template for Pending tasks', N'', N'pendingTasksQueryComponent', N'pendingTasksDisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1003) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1003, N'My Status', N'Template for current user status', N'', N'myStatusQueryComponent', N'myStatusDisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1004) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1004, N'Users in Project', N'Template for users based on project ', N'', N'projectUserQueryComponent', N'projectUserDisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1005) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1005, N'User List', N'All Users template', N'', N'userListQueryComponent', N'userListDisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1006) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1006, N'Critical Tasks', N'Template for Critical tasks', N'', N'CriticalTasks Query component', N'CriticalTasks Display component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1007) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1007, N'Court Dates', N'Template for Court Dates', N'', N'Court Dates QueryComponent', N'Court Dates DisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1008) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1008, N'High priority Tasks', N'Template for High priority Tasks', N'', N'High priority Tasks QueryComponent', N'High priority Tasks DisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1009) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1009, N'Phone Message', N'Template for Phone Message', N'', N'Phone Message QueryComponent', N'Phone Message DisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1010) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1010, N'Undertakings', N'Template for Undertakings', N'', N'Undertakings QueryComponent', N'Undertakings DisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1011) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1011, N'Statute date approaching', N'Template for Statute date approaching', N'', N'Statute date approaching QueryComponent', N'Statute date approaching DisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1012) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1012, N'Open Matters', N'Template for Open Matters', N'', N'Open Matters QueryComponent', N'Open Matters DisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1013) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1013, N'My dormant Matters', N'Template for My dormant Matters', N'', N'My dormant Matters QueryComponent', N'My dormant Matters DisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1014) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1014, N'No. of Effectively complete Matters', N'Template for No. of Effectively complete Matters', N'', N'No. of Effectively complete Matters QueryComponent', N'No. of Effectively complete Matters DisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1015) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1015, N'No. of Cases where expected invoice date has passed', N'Template for No. of Cases where expected invoice date has passed', N'', N'No. of Cases where expected invoice date has passed QueryComponent', N'No. of Cases where expected invoice date has passed DisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1016) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1016, N'No. of Cases approaching estimate fee', N'Template for No. of Cases approaching estimate fee', N'', N'No. of Cases approaching estimate fee QueryComponent', N'No. of Cases approaching estimate fee DisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1017) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1017, N'No. of Cases approaching lockup value limit', N'Template for No. of Cases approaching lockup value limit', N'', N'No. of Cases approaching lockup value limit QueryComponent', N'No. of Cases approaching lockup value limit DisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1018) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1018, N'No. of Clients that require money laundering checks', N'Template for No. of Clients that require money laundering checks', N'', N'No. of Clients that require money laundering checks QueryComponent', N'No. of Clients that require money laundering checks DisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1019) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1019, N'No. of Cases with no estimate given', N'Template for No. of Cases with no estimate given', N'', N'No. of Cases with no estimate given QueryComponent', N'No. of Cases with no estimate given DisplayComponent') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1020) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1020, N'No. of Cases with no estimated fee', N'Template for No of Cases with no estimated fee', N'', N'No of Cases with no estimated fee Query Component', N'No of Cases with no estimated fee Display Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1021) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1021, N'Debtors A/C', N'Debtors Account information', N'', N'Debtors Account Query Component', N'Debtors Account Query Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1022) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1022, N'Outlay A/C', N'Outlay Account information', N'', N'Outlay Account Query Component', N'Outlay Account Query Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1023) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1023, N'Outlay Budget', N'Outlay Budget information', N'', N'Outlay Budget Query Component', N'Outlay Budget Query Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1024) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1024, N'Current Outstanding Fees', N'Current Outstanding Fees information', N'', N'Current Outstanding Fees Query Component', N'Current Outstanding Fees Query Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1025) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1025, N'Client A/C', N'Client Account information', N'', N'Client Account Query Component', N'Client Account Query Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1026) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1026, N'Client Current', N'Client Current information', N'', N'Client Current Query Component', N'Client Current Query Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1027) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1027, N'Client Deposit', N'Client Deposit information', N'', N'Client Deposit Query Component', N'Client Deposit Query Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1028) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1028, N'Total Work In Progress', N'Total Work In Progress information', N'', N'Total Work In Progress Query Component', N'Total Work In Progress Query Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1029) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1029, N'Write Off Time', N'Write Off Time information', N'', N'Write Off Time Query Component', N'Write Off Time Display Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1030) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1030, N'Fees Issued To Date', N'Fees Issued To Date information', N'', N'Fees Issued To Date Query Component', N'Fees Issued To Date Query Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1031) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1031, N'Fees To Date + WIP', N'Fees To Date + WIP information', N'', N'Fees To Date + WIP Query Component', N'Fees To Date + WIP Display Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1032) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1032, N'Estimate Fees', N'Estimate Fees information', N'', N'Estimate Fees Query Component', N'Estimate Fees Query Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1033) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1033, N'Draft Invoices', N'Draft Invoices information', N'', N'Draft Invoices Query Component', N'Draft Invoices Query Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1034) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1034, N'Last Bill Date', N'Last Bill Date information', N'', N'Last Bill Date Query Component', N'Last Bill Date Query Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1035) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1035, N'Expected Bill Date', N'Expected Bill Date information', N'', N'Expected Bill Date Query Component', N'Expected Bill Date Query Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1036) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1036, N'Current Profit/Loss', N'Current Profit/Loss information', N'', N'Current Profit/Loss Query Component', N'Current Profit/Loss Query Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1037) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1037, N'Possible Value To The Client ', N'Possible Value To The Client information', N'', N'Possible Value To The Client Query Component', N'Possible Value To The Client Query Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1038) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1038, N'Anti Money Laundering Checked', N'Anti Money Laundering Checked information', N'', N'Anti Money Laundering Checked Query Component', N'Anti Money Laundering Checked Display Component') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1039) BEGIN INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) VALUES (1039, N'Current Loked Up Value', N'Current Loked Up Value', N'', N'Current Loked Up Value', N'Current Loked Up Value') END GO SET IDENTITY_INSERT [dbo].[UITemplates] OFF GO -------------------------------------UI Templates Insert End------------------------------------------------------- ------------------------------------------Widget Insert Start----------------------------------------------------- SET IDENTITY_INSERT [dbo].[Widgets] ON GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 100) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (100, N'Over due Tasks', N'Shows all Over due Tasks', 1000, N' {"color":"warn-fg", "title":"Overdue", "icon":"receipt"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 101) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (101, N'Outstanding Tasks', N'Shows all Outstanding Tasks', 1001, N' {"color":"orange-fg", "title":"Outstanding", "icon":"list_alt"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 102) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (102, N'Fees Issued YTD', N'Shows all Fees Issued Year to Date', 1002, NULL) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 103) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (103, N'Fees Issued This Month', N'Shows all Fees Issued This Month', 1003, NULL) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 104) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (104, N'Chargeable Time YTD', N'Shows all Chargeable Time YTD', 1004, NULL) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 105) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (105, N'Chargeable Time This Month', N'Shows all Chargeable Time This Month', 1005, NULL) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 106) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (106, N'Critical Tasks', N'Shows all Critical Tasks', 1006, N' {"color":"red-fg", "title":"Critical", "icon":"priority_high"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 107) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (107, N'Court Dates', N'Shows all Court Dates', 1007, N' {"color":"blue-fg", "title":"Court Dates", "icon":"event_available"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 108) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (108, N'High priority Tasks', N'Shows all High priority Tasks', 1008, N' {"color":"deep-purple-fg", "title":"Priority", "icon":"format_line_spacing"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 109) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (109, N'Phone message', N'Shows all Phone message', 1009, N' {"color":"amber-fg", "title":"Messages", "icon":"local_phone"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 110) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (110, N'Undertakings', N'Shows all Undertakings', 1010, N' {"color":"yellow-fg", "title":"Undertakings", "icon":"child_friendly"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 111) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (111, N'Statue date approaching', N'Shows count of Statue date approaching', 1011, N' {"color":"green-fg", "title":"Statute", "icon":"card_membership"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 112) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (112, N'Open Matters', N'Shows count of Open Matters', 1012, N' {"color":"light-green-fg", "title":"Matters", "icon":"local_library"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 113) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (113, N'My dormant Matters', N'Shows count of My dormant Matters', 1013, N' {"color":"lime-fg", "title":"Dormant", "icon":"book"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 114) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (114, N'No of Effectively Complete Matters', N'Shows count of No of Effectively Complete Matters', 1014, N' {"color":"brown-fg", "title":"Complete Matters", "icon":"calendar_today"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 115) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (115, N'No. of Cases where expected invoice date has passed', N'Shows count of No. of Cases where expected invoice date has passed', 1015, N' {"color":"fuse-navy-fg", "title":"Date Passed Cases", "icon":"event"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 116) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (116, N'No. of Cases approaching estimate fee', N'Shows count of No. of Cases approaching estimate fee', 1016, N' {"color":"teal-fg", "title":"Estimate Fee", "icon":"confirmation_number"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 117) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (117, N'No. of Cases approaching lockup value limit', N'Shows count of No. of Cases approaching lockup value limit', 1017, N' {"color":"indigo-fg", "title":"Lookup Value", "icon":"vertical_align_top"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 118) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (118, N'No. of Clients that require money laundering checks', N'Shows count of No. of Clients that require money laundering checks', 1018, N' {"color":"purple-fg", "title":"Money Laundering", "icon":"assignment_ind"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 119) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (119, N'No. of Cases with no estimate given', N'Shows count of No. of Cases with no estimate given', 1019, N' {"color":"pink-fg", "title":"No Estimate Time", "icon":"event_note"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 120) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (120, N'No. of Cases with no estimated fee', N'Shows count of No of Cases with no estimated fee', 1020, N' {"color":"accent-fg", "title":"No Estimate Fee", "icon":"account_balance_wallet"}') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 121) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (121, N'Debtors A/C', N'Debtors Account information', 1021, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 122) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (122, N'Outlay A/C', N'Outlay Account information', 1022, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 123) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (123, N'Outlay Budget', N'Outlay Budget information', 1023, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 124) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (124, N'Current Outstanding Fees', N'Current Outstanding Fees information', 1024, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 125) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (125, N'Client A/C', N'Client Account information', 1025, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 126) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (126, N'Client Current', N'Client Current information', 1026, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 127) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (127, N'Client Deposit', N'Client Deposit information', 1027, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 128) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (128, N'Total Work In Progress', N'Total Work In Progress information', 1028, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 129) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (129, N'Write Off Time', N'Write Off Time information', 1029, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 130) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (130, N'Fees Issued To Date', N'Fees Issued To Date information', 1030, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 131) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (131, N'Fees To Date + WIP', N'Fees To Date + WIP information', 1031, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 132) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (132, N'Estimate Fees', N'Estimate Fees information', 1032, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 133) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (133, N'Draft Invoices', N'Draft Invoices information', 1033, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 134) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (134, N'Last Bill Date', N'Last Bill Date information', 1034, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 135) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (135, N'Expected Bill Date', N'Expected Bill Date information', 1035, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 136) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (136, N'Current Profit/Loss', N'Current Profit/Loss information', 1036, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 137) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (137, N'Possible Value To The Client ', N'Possible Value To The Client information', 1037, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 138) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (138, N'Anti Money Laundering Checked', N'Anti Money Laundering Checked information', 1038, N'') END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 139) BEGIN INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (139, N'Current Loked Up Value', N'Current Loked Up Value', 1039, N'') END GO SET IDENTITY_INSERT [dbo].[Widgets] OFF GO ---------------------------------------------------Widget Insert End------------------------------------- ---------------------------------------[PanelWidgetAssociation] Insert Start----------------------------- SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 1) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (1, 1, 100) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 2) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (2, 1, 101) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 3) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (3, 2, 102) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 4) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (4, 4, 103) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 5) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (5, 4, 104) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 6) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (6, 4, 105) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 7) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (7, 1, 106) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 8) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (8, 1, 107) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 9) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (9, 1, 108) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 10) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (10, 1, 109) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 11) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (11, 1, 110) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 12) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (12, 1, 111) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 13) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (13, 1, 112) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 14) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (14, 1, 113) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 15) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (15, 1, 114) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 16) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (16, 1, 115) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 17) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (17, 1, 116) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 18) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (18, 1, 117) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 19) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (19, 1, 118) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 20) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (20, 1, 119) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 21) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (21, 1, 120) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 22) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (22, 3, 121) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 23) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (23, 3, 122) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 24) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (24, 3, 123) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 25) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (25, 3, 124) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 26) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (26, 3, 125) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 27) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (27, 3, 126) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 28) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (28, 3, 127) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 29) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (29, 3, 128) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 30) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (30, 3, 129) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 31) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (31, 3, 130) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 32) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (32, 3, 131) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 33) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (33, 3, 132) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 34) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (34, 3, 133) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 35) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (35, 3, 134) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 36) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (36, 3, 135) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 37) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (37, 3, 136) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 38) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (38, 3, 137) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 39) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (39, 3, 138) END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 40) BEGIN INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (40, 3, 139) END GO SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF GO