Update Widgets set ActionQuery = ' 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 SELECT( SELECT * FROM ( SELECT DIA.CASECODE AS [CaseCode], DIA.ACTIONID AS [ActionId],DIA.ACTIONCODE AS [ActionCode] ,DIA.ACTIONSTATUS AS [ActionStatus], DIA.ACTIONTYPE AS [ActionType], DIA.[SUBJECT] As [Subject] 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 = 1000 GO UPDATE Widgets SET ActionQuery = '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 SELECT( SELECT * FROM ( SELECT DIA.CASECODE AS [CaseCode], DIA.ACTIONID AS [ActionId],DIA.ACTIONCODE AS [ActionCode] ,DIA.ACTIONSTATUS AS [ActionStatus], DIA.ACTIONTYPE AS [ActionType], DIA.[SUBJECT] As [Subject] 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 = 1001 Go UPDATE Widgets SET ActionQuery = ' 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 * FROM( SELECT DIA.CASECODE AS [CaseCode], DIA.ACTIONID AS [ActionId],DIA.ACTIONCODE AS [ActionCode] ,DIA.ACTIONSTATUS AS [ActionStatus], DIA.ACTIONTYPE AS [ActionType], DIA.[SUBJECT] As [Subject] 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) AND [DIA].[ActionType] = ''C'' )ADT FOR XML PATH(''Result'')) AS Result ' WHERE TemplateId = 1006 GO UPDATE Widgets SET ActionQuery=' 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 * FROM ( SELECT DIA.CASECODE AS [CaseCode], DIA.ACTIONID AS [ActionId],DIA.ACTIONCODE AS [ActionCode] ,DIA.ACTIONSTATUS AS [ActionStatus], DIA.ACTIONTYPE AS [ActionType], DIA.[SUBJECT] As [Subject] 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) AND [DIA].[ActionType] = ''O'' ) ADT FOR XML PATH(''Result''))as [Result] ' WHERE WidgetId = 107 GO UPDATE Widgets SET ActionQuery='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 * FROM ( SELECT DIA.CASECODE AS [CaseCode], DIA.ACTIONID AS [ActionId],DIA.ACTIONCODE AS [ActionCode] ,DIA.ACTIONSTATUS AS [ActionStatus], DIA.ACTIONTYPE AS [ActionType], DIA.[SUBJECT] As [Subject] 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) AND DIA.[PRIORITY] = ''H'' ) ADT FOR XML PATH(''Result'') ) AS [Result] ' WHERE TemplateId = 1008 GO UPDATE Widgets SET ActionQuery = ' 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 * FROM ( SELECT DIA.CASECODE AS [CaseCode], DIA.ACTIONID AS [ActionId],DIA.ACTIONCODE AS [ActionCode] ,DIA.ACTIONSTATUS AS [ActionStatus], DIA.ACTIONTYPE AS [ActionType], DIA.[SUBJECT] As [Subject] 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) AND DIA.[ActionType] = ''T'' ) ADT FOR XML PATH(''Result'') ) AS [Result] ' WHERE TemplateId = 1009 GO UPDATE Widgets SET ActionQuery = ' DECLARE @TotalUndertakings INT DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT ( SELECT * FROM ( SELECT DIA.CASECODE AS [CaseCode], DIA.ACTIONID AS [ActionId],DIA.ACTIONCODE AS [ActionCode] ,DIA.ACTIONSTATUS AS [ActionStatus], DIA.ACTIONTYPE AS [ActionType], DIA.[SUBJECT] As [Subject] FROM dbo.[Undertakings] UND INNER JOIN [dbo].[matters] MAT ON MAT.[Code] = UND.[MATTER] INNER JOIN [dbo].[Diary] DIA ON DIA.[ACTIONID] = UND.[ACTIONID] WHERE UND.[AuthorisedByFE] = @Handler AND MAT.[Closed] <> ''Y'' AND UND.[DISCHARGEDATE] IS NULL ) ADT FOR XML PATH(''Result'') ) AS [Result] ' WHERE TemplateId = 1010 GO UPDATE Widgets SET ActionQuery ='DECLARE @TotalStatuteLimits INT DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT ( SELECT * FROM ( SELECT MAT.CODE AS [CaseCode], MAT.[Description] AS [Description], CASE WHEN MAt.Closed = ''Y'' THEN ''Closed'' ELSE ''Open'' END AS [Status] 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'' ) ADT FOR XML PATH(''Result'') ) AS [Result] ' WHERE TemplateId = 1011 GO UPDATE Widgets SET ActionQuery= 'DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END DECLARE @Open INT SELECT ( SELECT * FROM ( SELECT MAT.CODE AS [CaseCode], MAT.[Description] AS [Description], CASE WHEN MAt.Closed = ''Y'' THEN ''Closed'' ELSE ''Open'' END AS [Status] FROM [dbo].[matters] MAT WHERE MAT.[FECode] = @Handler AND MAT.Closed <> ''Y'' AND MAT.[ClientCode] <> ''ZZZZZZ'' AND ISNUMERIC(MAT.[matter]) != 0 AND CONVERT(INT, MAT.[matter]) != 0 ) ADT FOR XML PATH(''Result'') ) AS [Result] ' WHERE TemplateId = 1012 GO UPDATE Widgets SET ActionQuery = '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 * FROM ( SELECT MAT.CODE AS [CaseCode], MAT.[Description] AS [Description] --, M.LastActionDate FROM @MATTERS M LEFT JOIN matters MAT ON M.Code = MAT.[Code] ) ADT FOR XML PATH(''Result'') ) AS [Result] ' WHERE TemplateId=1013 GO UPDATE Widgets SET ActionQuery = 'SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT( SELECT * FROM( SELECT MAT.Code AS [CaseCode], MAT.[Description] FROM [dbo].[matters] MAT WHERE MAT.[FECode] = @Handler AND (MAT.Closed <> ''Y'' AND ISNUMERIC(MAT.[matter]) != 0 AND CONVERT(INT, MAT.[matter]) != 0) AND ISNULL(MAT.PCODE, '''') = '''' AND ISNULL(PFECODE, '''') = '''' ) ADT FOR XML PATH(''Result'') ) AS [Result] ' WHERE TemplateId = 1014 GO UPDATE Widgets SET ActionQuery = 'DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT( SELECT * FROM( SELECT MAT.Code AS [CaseCode], Mat.[Description] FROM [dbo].[matters] MAT WHERE MAT.[FECode] = @Handler AND MAT.Closed <> ''Y'' AND MAT.[ExpBillD] <= @GETDATE ) ADT FOR XML PATH(''Result'') ) AS [Result] ' WHERE TemplateId = 1015 GO UPDATE Widgets SET ActionQuery ='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( SELECT * FROM( SELECT MAT.Code AS [CaseCode], Mat.[Description], CASE WHEN (COALESCE ((ISNULL(MAT.[ChargeBal], 0) / NULLIF (MAT.[EstFee], 0) * 100), 0)) > @EstFeeWarningLimit THEN 1 ELSE 0 END AS EstFeeWarning FROM [dbo].[matters] MAT WHERE MAT.[FECode] = @Handler AND MAT.Closed <> ''Y'' ) ADT WHERE ADT.EstFeeWarning > 0 FOR XML PATH(''Result'') ) AS [Result] ' WHERE TemplateId = 1016 GO UPDATE Widgets SET ActionQuery ='DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END DECLARE @LockupUpValue DECIMAL(19, 2); SELECT @LockupUpValue = CONVERT(DECIMAL(19, 2), ISNULL([SET].[KeyValue], ''0'')) FROM [dbo].[Settings] [SET] WHERE [SET].[KeyName] = ''LockupUpValue'' SET @LockupUpValue = ISNULL(@LockupUpValue, 0) SELECT( SELECT * FROM( SELECT MAT.Code AS CaseCode, MAT.[Description], CASE WHEN (MAT.[ChargeBal] + MAT.[DebtBal] + MAT.[OutlayBal]) > @LockupUpValue THEN 1 ELSE 0 END AS IsLockUpValue FROM [dbo].[matters] MAT WHERE MAT.[FECode] = @Handler AND MAT.Closed <> ''Y'' ) ADT WHERE ADT.IsLockUpValue = 1 FOR XML PATH(''Result'') ) AS [Result] ' WHERE TemplateId = 1017 GO UPDATE Widgets SET ActionQuery = 'DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT( SELECT * FROM( SELECT MAX(MAT.Code) AS CaseCode, MAX(MAT.[Description]) AS [Description], Mat.ClientCode 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 GROUP BY ClientCode ) ADT FOR XML PATH(''Result'') ) AS [Result] ' WHERE TemplateId = 1018 GO UPDATE Widgets SET ActionQuery ='SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT( SELECT * FROM( SELECT MAT.Code AS [CaseCode], MAT.[Description], (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) AS IsNotEstimated FROM [dbo].[matters] MAT WHERE MAT.[FECode] = @Handler AND MAT.Closed <> ''Y'' ) ADT WHERE ADT.IsNotEstimated = 1 FOR XML PATH(''Result'') ) AS [Result] ' WHERE TemplateId = 1019 GO UPDATE Widgets SET ActionQuery = 'DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT( SELECT * FROM( SELECT MAT.Code AS [CaseCode], MAT.[Description], 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 AS IsEstimatedFee FROM [dbo].[matters] MAT WHERE MAT.[FECode] = @Handler AND MAT.Closed <> ''Y'' ) ADT WHERE ADT.IsEstimatedFee = 1 FOR XML PATH(''Result'') ) AS [Result] ' WHERE TemplateId = 1020 GO Update Widgets set WidgetQuery = ' 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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery ='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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = ' 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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery ='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 Widgets SET WidgetQuery =' 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 Widgets SET WidgetQuery = ' 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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = ' 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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '/*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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = ' 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 Widgets SET WidgetQuery = ' 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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = ' 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 Widgets SET WidgetQuery = ' 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 Widgets SET WidgetQuery = ' 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 Widgets SET WidgetQuery = '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 Widgets SET WidgetQuery = ' 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 GO -------------------------------------------Panels Insert Start--------------------------------------------------------- GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 1) BEGIN SET IDENTITY_INSERT [dbo].[Panels] ON INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (1, N'Tasks', N'Panel to display all the tasks', 'tasks', 100) SET IDENTITY_INSERT [dbo].[Panels] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 2) BEGIN SET IDENTITY_INSERT [dbo].[Panels] ON INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (2, N'Fees Issued', N'Fees Issued Information Panel', 'tasks', 50) SET IDENTITY_INSERT [dbo].[Panels] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 3) BEGIN SET IDENTITY_INSERT [dbo].[Panels] ON INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (3, N'Account Summary', N'Account Summary', 'caseDiary', 100) SET IDENTITY_INSERT [dbo].[Panels] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 4) BEGIN SET IDENTITY_INSERT [dbo].[Panels] ON INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (4, N'Fees Issued', N'Fees Issued Information Panel', null, 0) SET IDENTITY_INSERT [dbo].[Panels] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 5) BEGIN SET IDENTITY_INSERT [dbo].[Panels] ON INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (5, N'My Day Books', N'My day book panel ', 'timeCosting', 100) SET IDENTITY_INSERT [dbo].[Panels] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE [PanelName] = 'Current Matter Details' AND DashboardType = 'caseDiary') BEGIN SET IDENTITY_INSERT [dbo].[Panels] ON INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (6, N'Current Matter Details', N'Current Matter Details', 'caseDiary', 50) SET IDENTITY_INSERT [dbo].[Panels] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE [PanelName] = 'Case Status' AND DashboardType = 'caseDiary') BEGIN SET IDENTITY_INSERT [dbo].[Panels] ON INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (7, N'Case Status', N'Case Status', 'caseDiary', 50) SET IDENTITY_INSERT [dbo].[Panels] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE [PanelName] = 'Time By FE' AND DashboardType = 'caseDiary') BEGIN SET IDENTITY_INSERT [dbo].[Panels] ON INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (8, N'Time By FE', N'Time By FE', 'caseDiary', 50) SET IDENTITY_INSERT [dbo].[Panels] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE [PanelName] = 'Activity' AND DashboardType = 'caseDiary') BEGIN SET IDENTITY_INSERT [dbo].[Panels] ON INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (9, N'Activity', N'Activity', 'caseDiary', 100) SET IDENTITY_INSERT [dbo].[Panels] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE [PanelName] = 'Court Dates' AND DashboardType = 'caseDiary') BEGIN SET IDENTITY_INSERT [dbo].[Panels] ON INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (10, N'Court Dates', N'Court Dates', 'caseDiary', 50) SET IDENTITY_INSERT [dbo].[Panels] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE [PanelName] = 'Outstanding Critical Dates' AND DashboardType = 'caseDiary') BEGIN SET IDENTITY_INSERT [dbo].[Panels] ON INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (11, N'Outstanding Critical Dates', N'Outstanding Critical Dates', 'caseDiary', 50) SET IDENTITY_INSERT [dbo].[Panels] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE [PanelName] = 'Undertakings' AND DashboardType = 'caseDiary') BEGIN SET IDENTITY_INSERT [dbo].[Panels] ON INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (12, N'Undertakings', N'Undertakings', 'caseDiary', 50) SET IDENTITY_INSERT [dbo].[Panels] OFF END GO GO ---------------------------------------------Panels Insert End----------------------------------------------------------- ------------------------------------------UI Templates Insert Start------------------------------------------------------- GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1000) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1000, N'Over due Tasks', N'Template for over due tasks', N'overDueTasksQueryComponent', N'overDueTasksDisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1001) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1001, N'Outstanding Tasks', N'Template for Outstanding Tasks', N'Outstanding Tasks QueryComponent', N'Outstanding Tasks DisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1002) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1002, N'Pending Tasks', N'Template for Pending tasks', N'pendingTasksQueryComponent', N'pendingTasksDisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1003) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1003, N'My Status', N'Template for current user status', N'myStatusQueryComponent', N'myStatusDisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1004) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1004, N'Users in Project', N'Template for users based on project ', N'projectUserQueryComponent', N'projectUserDisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1005) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1005, N'User List', N'All Users template', N'userListQueryComponent', N'userListDisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1006) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1006, N'Critical Tasks', N'Template for Critical tasks', N'CriticalTasks Query component', N'CriticalTasks Display component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1007) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1007, N'Court Dates', N'Template for Court Dates', N'Court Dates QueryComponent', N'Court Dates DisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1008) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1008, N'High priority Tasks', N'Template for High priority Tasks', N'High priority Tasks QueryComponent', N'High priority Tasks DisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1009) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1009, N'Phone Message', N'Template for Phone Message', N'Phone Message QueryComponent', N'Phone Message DisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1010) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1010, N'Undertakings', N'Template for Undertakings', N'Undertakings QueryComponent', N'Undertakings DisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1011) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1011, N'Statute date approaching', N'Template for Statute date approaching', N'Statute date approaching QueryComponent', N'Statute date approaching DisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1012) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1012, N'Open Matters', N'Template for Open Matters', N'Open Matters QueryComponent', N'Open Matters DisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1013) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1013, N'My dormant Matters', N'Template for My dormant Matters', N'My dormant Matters QueryComponent', N'My dormant Matters DisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1014) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1014, N'No. of Effectively complete Matters', N'Template for No. of Effectively complete Matters', N'No. of Effectively complete Matters QueryComponent', N'No. of Effectively complete Matters DisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1015) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [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'No. of Cases where expected invoice date has passed QueryComponent', N'No. of Cases where expected invoice date has passed DisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1016) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1016, N'No. of Cases approaching estimate fee', N'Template for No. of Cases approaching estimate fee', N'No. of Cases approaching estimate fee QueryComponent', N'No. of Cases approaching estimate fee DisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1017) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1017, N'No. of Cases approaching lockup value limit', N'Template for No. of Cases approaching lockup value limit', N'No. of Cases approaching lockup value limit QueryComponent', N'No. of Cases approaching lockup value limit DisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1018) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [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'No. of Clients that require money laundering checks QueryComponent', N'No. of Clients that require money laundering checks DisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1019) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1019, N'No. of Cases with no estimate given', N'Template for No. of Cases with no estimate given', N'No. of Cases with no estimate given QueryComponent', N'No. of Cases with no estimate given DisplayComponent') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1020) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1020, N'No. of Cases with no estimated fee', N'Template for No of Cases with no estimated fee', N'No of Cases with no estimated fee Query Component', N'No of Cases with no estimated fee Display Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1021) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1021, N'Debtors A/C', N'Debtors Account information', N'Debtors Account Query Component', N'Debtors Account Query Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1022) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1022, N'Outlay A/C', N'Outlay Account information', N'Outlay Account Query Component', N'Outlay Account Query Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1023) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1023, N'Outlay Budget', N'Outlay Budget information', N'Outlay Budget Query Component', N'Outlay Budget Query Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1024) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1024, N'Current Outstanding Fees', N'Current Outstanding Fees information', N'Current Outstanding Fees Query Component', N'Current Outstanding Fees Query Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1025) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1025, N'Client A/C', N'Client Account information', N'Client Account Query Component', N'Client Account Query Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1026) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1026, N'Client Current', N'Client Current information', N'Client Current Query Component', N'Client Current Query Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1027) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1027, N'Client Deposit', N'Client Deposit information', N'Client Deposit Query Component', N'Client Deposit Query Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1028) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1028, N'Total Work In Progress', N'Total Work In Progress information', N'Total Work In Progress Query Component', N'Total Work In Progress Query Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1029) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1029, N'Write Off Time', N'Write Off Time information', N'Write Off Time Query Component', N'Write Off Time Display Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1030) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1030, N'Fees Issued To Date', N'Fees Issued To Date information', N'Fees Issued To Date Query Component', N'Fees Issued To Date Query Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1031) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1031, N'Fees To Date + WIP', N'Fees To Date + WIP information', N'Fees To Date + WIP Query Component', N'Fees To Date + WIP Display Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1032) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1032, N'Estimate Fees', N'Estimate Fees information', N'Estimate Fees Query Component', N'Estimate Fees Query Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1033) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1033, N'Draft Invoices', N'Draft Invoices information', N'Draft Invoices Query Component', N'Draft Invoices Query Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1034) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1034, N'Last Bill Date', N'Last Bill Date information', N'Last Bill Date Query Component', N'Last Bill Date Query Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1035) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1035, N'Expected Bill Date', N'Expected Bill Date information', N'Expected Bill Date Query Component', N'Expected Bill Date Query Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1036) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1036, N'Current Profit/Loss', N'Current Profit/Loss information', N'Current Profit/Loss Query Component', N'Current Profit/Loss Query Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1037) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1037, N'Possible Value To The Client ', N'Possible Value To The Client information', N'Possible Value To The Client Query Component', N'Possible Value To The Client Query Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1038) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1038, N'Anti Money Laundering Checked', N'Anti Money Laundering Checked information', N'Anti Money Laundering Checked Query Component', N'Anti Money Laundering Checked Display Component') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1039) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1039, N'Current Locked Up Value', N'Current Locked Up Value', N'Current Locked Up Value', N'Current Locked Up Value') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1040) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1040, N'Gauge Template', N'Gauge Template used in Timecosting', NULL, NULL) SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1041) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1041, N'Time Display', N'Time Display', NULL, NULL) SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1042) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1042, N'Table value', N'Table value', N'Table value', N'Table value') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1043) BEGIN SET IDENTITY_INSERT [dbo].[UITemplates] ON INSERT [dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) VALUES (1043, N'Pie Chart', N'Pie Chart', N'Pie Chart', N'Pie Chart') SET IDENTITY_INSERT [dbo].[UITemplates] OFF END GO GO -------------------------------------UI Templates Insert End------------------------------------------------------- ------------------------------------------Widget Insert Start----------------------------------------------------- GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 100) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 101) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 102) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (102, N'Fees Issued YTD', N'Shows all Fees Issued Year to Date', 1002, NULL) SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 103) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (103, N'Fees Issued This Month', N'Shows all Fees Issued This Month', 1003, NULL) SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 104) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (104, N'Chargeable Time YTD', N'Shows all Chargeable Time YTD', 1004, NULL) SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 105) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (105, N'Chargeable Time This Month', N'Shows all Chargeable Time This Month', 1005, NULL) SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 106) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 107) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 108) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 109) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 110) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 111) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 112) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 113) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 114) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 115) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 116) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 117) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 118) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 119) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 120) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 121) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (121, N'Debtors A/C', N'Debtors Account information', 1021, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 122) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (122, N'Outlay A/C', N'Outlay Account information', 1022, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 123) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (123, N'Outlay Budget', N'Outlay Budget information', 1023, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 124) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (124, N'Current Outstanding Fees', N'Current Outstanding Fees information', 1024, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 125) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (125, N'Client A/C', N'Client Account information', 1025, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 126) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (126, N'Client Current', N'Client Current information', 1026, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 127) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (127, N'Client Deposit', N'Client Deposit information', 1027, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 128) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (128, N'Total Work In Progress', N'Total Work In Progress information', 1028, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 129) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (129, N'Write Off Time', N'Write Off Time information', 1029, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 130) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (130, N'Fees Issued To Date', N'Fees Issued To Date information', 1030, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 131) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (131, N'Fees To Date + WIP', N'Fees To Date + WIP information', 1031, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 132) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (132, N'Estimate Fees', N'Estimate Fees information', 1032, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 133) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (133, N'Draft Invoices', N'Draft Invoices information', 1033, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 134) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (134, N'Last Bill Date', N'Last Bill Date information', 1034, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 135) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (135, N'Expected Bill Date', N'Expected Bill Date information', 1035, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 136) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (136, N'Current Profit/Loss', N'Current Profit/Loss information', 1036, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 137) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON 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'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 138) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (138, N'Anti Money Laundering Checked', N'Anti Money Laundering Checked information', 1038, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 139) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (139, N'Current Loked Up Value', N'Current Loked Up Value', 1039, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 140) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (140, N'MyDay Book Summary', N'Shows summary percentage value in a gauge', 1040, N'{"canvasWidth":300,"needleValue":65,"centralLabel":"10%","name":"Gauge chart","bottomLabel":"65","options":{"hasNeedle":false,"needleColor":"#ededed","needleUpdateSpeed":1000,"arcColors":["#33A5EB","#FD6682"],"arcDelimiters":[30],"rangeLabel":["0","100"],"needleStartValue":50}}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 141) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (141, N'Chargeable', N'Chargeable Time', 1041, N'{"key":"ChargeableTime","color":"#33A5EB","time":"00:07"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 142) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (142, N'Non-Chargeable', N'Non Chargeable Time', 1041, N'{"key":"NonChargeableTime","color":"#FF3A2A","time":"00:07"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 143) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (143, N'Total', N'Total Time', 1041, N'{"key":"TotalTime","color":"#FF9400","time":"00:07"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 144) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (144, N'Remaining', N'Remaining Time', 1041, N'{"key":"RemainingTime","color":"#597C88","time":"00:07"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 145 AND TemplateId = 1042) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (145, N'Current Matter Details', N'Current Matter Details', 1042, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 146 AND TemplateId = 1042) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (146, N'Case Status', N'Case Status', 1042, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 147 AND TemplateId = 1042) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (147, N'Court Dates', N'Court Dates', 1042, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 148 AND TemplateId = 1042) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (148, N'Undertakings', N'Undertakings', 1042, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 149 AND TemplateId = 1042) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (149, N'Outstanding Critical Dates', N'Outstanding Critical Dates', 1042, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 150 AND TemplateId = 1042) BEGIN UPDATE [dbo].[Widgets] SET TemplateId = 1043 WHERE WidgetId = 150 END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 150 AND TemplateId = 1043) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (150, N'Time By Fee Earner', N'Time By Fee Earner', 1043, N' {"color":"accent-fg", "title":[{"name":"Total Time","name":"Work In Progress"] "icon":"account_balance_wallet"}') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 151 AND TemplateId = 1043) BEGIN SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT [dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) VALUES (151, N'Activity', N'Activity', 1043, N'') SET IDENTITY_INSERT [dbo].[Widgets] OFF END GO GO ---------------------------------------------------Widget Insert End------------------------------------- ---------------------------------------[PanelWidgetAssociation] Insert Start----------------------------- IF EXISTS(SELECT TOP 1 1 FROM SYS.OBJECTS WHERE [NAME] = 'PanelWidgetAssociation') BEGIN TRUNCATE TABLE [dbo].[PanelWidgetAssociation] END GO GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 1) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (1, 1, 100) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 2) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (2, 1, 101) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 3) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (3, 2, 102) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 4) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (4, 4, 103) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 5) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (5, 4, 104) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 6) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (6, 4, 105) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 7) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (7, 1, 106) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 8) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (8, 1, 107) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 9) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (9, 1, 108) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 10) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (10, 1, 109) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 11) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (11, 1, 110) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 12) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (12, 1, 111) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 13) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (13, 1, 112) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 14) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (14, 1, 113) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 15) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (15, 1, 114) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 16) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (16, 1, 115) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 17) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (17, 1, 116) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 18) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (18, 1, 117) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 19) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (19, 1, 118) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 20) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (20, 1, 119) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 21) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (21, 1, 120) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 22) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (22, 3, 121) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 23) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (23, 3, 122) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 24) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (24, 3, 123) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 25) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (25, 3, 124) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 26) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (26, 3, 125) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 27) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (27, 3, 126) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 28) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (28, 3, 127) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 29) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (29, 3, 128) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 30) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (30, 3, 129) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 31) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (31, 3, 130) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 32) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (32, 3, 131) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 33) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (33, 3, 132) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 34) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (34, 3, 133) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 35) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (35, 3, 134) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 36) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (36, 3, 135) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 37) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON 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 SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (38, 3, 137) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 39) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON 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 SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (40, 3, 139) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 41) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (41, 5, 140) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 5 AND WidgetId = 141) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (42, 5, 141) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 5 AND WidgetId = 142) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (43, 5, 142) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 5 AND WidgetId = 143) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (44, 5, 143) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 5 AND WidgetId = 144) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (45, 5, 144) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 6 AND WidgetId = 145) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (46, 6, 145) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 7 AND WidgetId = 146) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (47, 7, 146) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 10 AND WidgetId = 147) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (48, 10, 147) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 12 AND WidgetId = 148) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (49, 12, 148) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 11 AND WidgetId = 149) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (50, 11, 149) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 8 AND WidgetId = 150) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (51, 8, 150) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 9 AND WidgetId = 151) BEGIN SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (52, 9, 151) SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF END GO UPDATE [Widgets] SET WidgetData = '{"color":"warn-fg", "title":"Overdue Tasks", "icon":"receipt", "redirect":"/tasks/main/overdue-tasks", "filter":""}' WHERE WidgetId = 100 GO UPDATE [Widgets] SET WidgetData = '{"color":"orange-fg", "title":"Outstanding Tasks", "icon":"list_alt"}' WHERE WidgetId = 101 GO UPDATE [Widgets] SET WidgetData = '{"color":"red-fg", "title":"Critical Tasks", "icon":"priority_high"}' WHERE WidgetId = 106 GO UPDATE [Widgets] SET WidgetData = '{"color":"deep-purple-fg", "title":"High Priority Tasks", "icon":"format_line_spacing"}' WHERE WidgetId = 108 GO UPDATE [Widgets] SET WidgetData = '{"color":"amber-fg", "title":"Phone Message", "icon":"local_phone"}' WHERE WidgetId = 109 GO UPDATE [Widgets] SET WidgetData = '{"color":"green-fg", "title":"Approaching Statute Date", "icon":"card_membership"}' WHERE WidgetId = 111 GO UPDATE [Widgets] SET WidgetData = '{"color":"light-green-fg", "title":"Open Matters", "icon":"local_library"}' WHERE WidgetId = 112 GO UPDATE [Widgets] SET WidgetData = '{"color":"lime-fg", "title":"My Dormant Matters", "icon":"book"}' WHERE WidgetId = 113 GO UPDATE [Widgets] SET WidgetData = '{"color":"brown-fg", "title":"No.of effectively complete matters", "icon":"calendar_today"}' WHERE WidgetId = 114 GO UPDATE [Widgets] SET WidgetData = '{"color":"fuse-navy-fg", "title":"No.of Cases where expected invoice date passed", "icon":"event"}' WHERE WidgetId = 115 GO UPDATE [Widgets] SET WidgetData = '{"color":"teal-fg", "title":"No.of Cases approaching Estimate Fee", "icon":"confirmation_number"}' WHERE WidgetId = 116 GO UPDATE [Widgets] SET WidgetData = '{"color":"indigo-fg", "title":"No.of Cases approaching lockup value limit", "icon":"vertical_align_top"}' WHERE WidgetId = 117 GO UPDATE [Widgets] SET WidgetData = '{"color":"purple-fg", "title":"No.of Clients that require money laundering checks", "icon":"assignment_ind"}' WHERE WidgetId = 118 GO UPDATE [Widgets] SET WidgetData = '{"color":"pink-fg", "title":"No.of Cases with no estimate given", "icon":"event_note"}' WHERE WidgetId = 119 GO UPDATE [Widgets] SET WidgetData = '{"color":"accent-fg", "title":"No.of Cases with no estimated fee", "icon":"account_balance_wallet"}' WHERE WidgetId = 120 GO IF EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 150 AND TemplateId = 1043) BEGIN UPDATE [Widgets] SET WidgetData = ' {"color":"accent-fg", "title":[{"name":"Total Time","scheme" :{"domain": ["#EE0000", "#00EE00", "#0000EE"]}},{"name":"Work In Progress","scheme" :{"domain": ["#EE0000", "#00EE00", "#0000EE"]}}], "icon":"account_balance_wallet"}' WHERE WidgetId = 150 AND TemplateId = 1043 END GO IF EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 151 AND TemplateId = 1043) BEGIN UPDATE [Widgets] SET WidgetData = ' {"color":"accent-fg", "title":[{"name":"Activity","scheme" :{"domain": ["#EE0000", "#00EE00", "#0000EE"]}}], "icon":"account_balance_wallet"}' WHERE WidgetId = 151 AND TemplateId = 1043 END GO Update Widgets SET WidgetQuery = 'DECLARE @GETDATE DATETIME SET @GETDATE = GETUTCDATE() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END DECLARE @Lockedupvalue INT DECLARE @LockupUpValue DECIMAL(19, 2) SELECT @LockupUpValue = CONVERT(DECIMAL(19, 2), ISNULL([SET].[KeyValue], ''0'')) FROM [dbo].[Settings] [SET] WHERE [SET].[KeyName] = ''LockupUpValue'' SET @LockupUpValue = ISNULL(@LockupUpValue, 0) SELECT @Lockedupvalue = ISNULL(SUM(CASE WHEN (MAT.[ChargeBal] + MAT.[DebtBal] + MAT.[OutlayBal]) > @LockupUpValue THEN 1 ELSE 0 END), 0) FROM [dbo].[matters] MAT WHERE MAT.[FECode] = @Handler AND MAT.Closed <> ''Y'' SELECT ( SELECT ISNULL(@Lockedupvalue, 0) AS HeaderCount, ''No. of Cases approaching lockup value limit'' as HeaderLabel FOR XML PATH(''Result'')) AS [Result] ' WHERE TemplateId = 1017 GO UPDATE Widgets SET WidgetQuery = N' DECLARE @Post VARCHAR(100) = ''TimeDayBookLimit'' SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END DECLARE @DayBookValue INT SELECT @DayBookValue = ISNULL(SUM([TDB].[Time]), 0) FROM [dbo].[Timedaybook] [TDB] WITH (NOLOCK) WHERE [TDB].[FeeEarn] = @handler DECLARE @MaxDayBookValue DECIMAL(10,2) = 7.5 IF EXISTS(SELECT TOP 1 1 FROM [dbo].[UserSettings] WHERE [Handler] = @handler AND [Key] = @Post) BEGIN SELECT @MaxDayBookValue = Value FROM [dbo].[UserSettings] WHERE [Handler] = @handler AND [Key] = @Post END SELECT @MaxDayBookValue = CASE WHEN (ISNULL(@MaxDayBookValue,0) = 0) THEN 7.5 * 60 ELSE @MaxDayBookValue * 60 END SELECT ( SELECT CAST(CAST(timeVal AS VARCHAR) AS VARCHAR(6)) AS timeVal FROM( SELECT CASE WHEN (NULLIF(@DayBookValue, 0)/NULLIF(@MaxDayBookValue, 0))*100 > 100 THEN 100 ELSE (NULLIF(@DayBookValue, 0)/NULLIF(@MaxDayBookValue, 0))*100 END AS timeVal ) ADT FOR XML PATH(''Result'') )AS [Result] ' WHERE WidgetId = 140 GO UPDATE Widgets SET WidgetQuery = N' DECLARE @Post VARCHAR(100) = ''TimeDayBookLimit'' SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT ( SELECT ISNULL([CaseTime],''00:00'')AS timeVal FROM ( SELECT [TDB].[Rec_Irr] AS [ChargeType], CASE WHEN SUM([TDB].[Time]) < 0 THEN ''-'' ELSE '''' END + CASE WHEN ABS(FLOOR(SUM([TDB].[Time]) / 60.0)) < 10 THEN ''0'' ELSE '''' END + CONVERT(VARCHAR, ABS(FLOOR(SUM([TDB].[Time]) / 60))) + '':'' + RIGHT(''00'' + CONVERT(VARCHAR, ABS(FLOOR(SUM([TDB].[Time]))) % 60), 2) AS [CaseTime] FROM [dbo].[TimeDayBook] [TDB] WITH (NOLOCK) WHERE [TDB].[FeeEarn] = @handler AND ISNULL([TDB].[Post], ''Y'') IN (''Y'', @Post) GROUP BY [TDB].[Rec_Irr] ) ADT WHERE ADT.[ChargeType]= '' '' ORDER BY ADT.[ChargeType] FOR XML PATH(''Result'') )AS [Result] ' WHERE WidgetId = 141 GO UPDATE Widgets SET WidgetQuery = N' DECLARE @Post VARCHAR(100) = ''TimeDayBookLimit'' SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT ( SELECT ISNULL([CaseTime],''00:00'')AS timeVal FROM ( SELECT [TDB].[Rec_Irr] AS [ChargeType], CASE WHEN SUM([TDB].[Time]) < 0 THEN ''-'' ELSE '''' END + CASE WHEN ABS(FLOOR(SUM([TDB].[Time]) / 60.0)) < 10 THEN ''0'' ELSE '''' END + CONVERT(VARCHAR, ABS(FLOOR(SUM([TDB].[Time]) / 60))) + '':'' + RIGHT(''00'' + CONVERT(VARCHAR, ABS(FLOOR(SUM([TDB].[Time]))) % 60), 2) AS [CaseTime] FROM [dbo].[TimeDayBook] [TDB] WITH (NOLOCK) WHERE [TDB].[FeeEarn] = @handler AND ISNULL([TDB].[Post], ''Y'') IN (''Y'', @Post) GROUP BY [TDB].[Rec_Irr] ) ADT WHERE ADT.[ChargeType]= ''C'' ORDER BY ADT.[ChargeType] FOR XML PATH(''Result'') )AS [Result] ' WHERE WidgetId = 142 GO UPDATE Widgets SET WidgetQuery = N' DECLARE @Post VARCHAR(100) = ''TimeDayBookLimit'' SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END DECLARE @CHARGETIME TIME DECLARE @NONCHARGETIME TIME SELECT @CHARGETIME = ISNULL([CaseTime],''00:00'') FROM ( SELECT [TDB].[Rec_Irr] AS [ChargeType], CASE WHEN SUM([TDB].[Time]) < 0 THEN ''-'' ELSE '''' END + CASE WHEN ABS(FLOOR(SUM([TDB].[Time]) / 60.0)) < 10 THEN ''0'' ELSE '''' END + CONVERT(VARCHAR, ABS(FLOOR(SUM([TDB].[Time]) / 60))) + '':'' + RIGHT(''00'' + CONVERT(VARCHAR, ABS(FLOOR(SUM([TDB].[Time]))) % 60), 2) AS [CaseTime] FROM [dbo].[TimeDayBook] [TDB] WITH (NOLOCK) WHERE [TDB].[FeeEarn] = @handler AND ISNULL([TDB].[Post], ''Y'') IN (''Y'', @Post) GROUP BY [TDB].[Rec_Irr] ) ADT WHERE ADT.[ChargeType]= '' '' ORDER BY ADT.[ChargeType] SELECT @NONCHARGETIME = ISNULL([CaseTime],''00:00'') FROM ( SELECT [TDB].[Rec_Irr] AS [ChargeType], CASE WHEN SUM([TDB].[Time]) < 0 THEN ''-'' ELSE '''' END + CASE WHEN ABS(FLOOR(SUM([TDB].[Time]) / 60.0)) < 10 THEN ''0'' ELSE '''' END + CONVERT(VARCHAR, ABS(FLOOR(SUM([TDB].[Time]) / 60))) + '':'' + RIGHT(''00'' + CONVERT(VARCHAR, ABS(FLOOR(SUM([TDB].[Time]))) % 60), 2) AS [CaseTime] FROM [dbo].[TimeDayBook] [TDB] WITH (NOLOCK) WHERE [TDB].[FeeEarn] = @handler AND ISNULL([TDB].[Post], ''Y'') IN (''Y'', @Post) GROUP BY [TDB].[Rec_Irr] ) ADT WHERE ADT.[ChargeType]= ''C'' ORDER BY ADT.[ChargeType] SELECT ( SELECT CAST( CAST( DATEADD(MINUTE,SUM(DATEDIFF(MINUTE, ''00:00:00'', CAST(@CHARGETIME AS TIME))),@NONCHARGETIME) AS TIME ) AS VARCHAR(5) ) AS timeVal FOR XML PATH(''Result'') )AS [Result] ' WHERE WidgetId = 143 GO UPDATE Widgets SET WidgetQuery = N' DECLARE @Post VARCHAR(100) = ''TimeDayBookLimit'' SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END DECLARE @CHARGETIME TIME DECLARE @NONCHARGETIME TIME SELECT @CHARGETIME = ISNULL([CaseTime],''00:00'') FROM ( SELECT [TDB].[Rec_Irr] AS [ChargeType], CASE WHEN SUM([TDB].[Time]) < 0 THEN ''-'' ELSE '''' END + CASE WHEN ABS(FLOOR(SUM([TDB].[Time]) / 60.0)) < 10 THEN ''0'' ELSE '''' END + CONVERT(VARCHAR, ABS(FLOOR(SUM([TDB].[Time]) / 60))) + '':'' + RIGHT(''00'' + CONVERT(VARCHAR, ABS(FLOOR(SUM([TDB].[Time]))) % 60), 2) AS [CaseTime] FROM [dbo].[TimeDayBook] [TDB] WITH (NOLOCK) WHERE [TDB].[FeeEarn] = @handler AND ISNULL([TDB].[Post], ''Y'') IN (''Y'', @Post) GROUP BY [TDB].[Rec_Irr] ) ADT WHERE ADT.[ChargeType]= '' '' ORDER BY ADT.[ChargeType] SELECT @NONCHARGETIME = ISNULL([CaseTime],''00:00'') FROM ( SELECT [TDB].[Rec_Irr] AS [ChargeType], CASE WHEN SUM([TDB].[Time]) < 0 THEN ''-'' ELSE '''' END + CASE WHEN ABS(FLOOR(SUM([TDB].[Time]) / 60.0)) < 10 THEN ''0'' ELSE '''' END + CONVERT(VARCHAR, ABS(FLOOR(SUM([TDB].[Time]) / 60))) + '':'' + RIGHT(''00'' + CONVERT(VARCHAR, ABS(FLOOR(SUM([TDB].[Time]))) % 60), 2) AS [CaseTime] FROM [dbo].[TimeDayBook] [TDB] WITH (NOLOCK) WHERE [TDB].[FeeEarn] = @handler AND ISNULL([TDB].[Post], ''Y'') IN (''Y'', @Post) GROUP BY [TDB].[Rec_Irr] ) ADT WHERE ADT.[ChargeType]= ''C'' ORDER BY ADT.[ChargeType] SELECT ( SELECT CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,CAST(DATEADD(MINUTE,SUM(DATEDIFF(MINUTE, ''00:00:00'', cast(@NONCHARGETIME AS TIME))),@CHARGETIME)AS TIME),CAST(''07:30:00'' AS TIME)),CAST(''00:00:00'' AS TIME)) AS VARCHAR(5) ) AS timeVal FOR XML PATH(''Result'') )AS [Result] ' WHERE WidgetId = 144 GO UPDATE Widgets SET WidgetQuery = N' SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT ( SELECT * FROM ( SELECT RTRIM(ISNULL(HAN.[NAME], '''')) AS [FeeEarner], ISNULL(CONVERT(VARCHAR,dbo.KAAS_FNConvertUTCDateToTargetTimeZone(MAT.[Started], @handler),106), '''') AS [StartDate], ISNULL(CONVERT(VARCHAR,dbo.KAAS_FNConvertUTCDateToTargetTimeZone(MAT.[StatuteLimits],@handler),106), '''') AS [StatuteDate], MAT.[FileColour] AS [FileColour] FROM dbo.[matters] MAT LEFT JOIN dbo.[WorkTypes] WKT ON WKT.[CODE] = MAT.[WType] LEFT OUTER JOIN dbo.[Departments] DPT ON DPT.[CODE] = MAT.[Dept] LEFT OUTER JOIN dbo.[Handlers] HAN ON HAN.[CODE] = MAT.[FECode] LEFT OUTER JOIN dbo.[client] CLT ON CLT.[CLCODE] = MAT.[ClientCode] LEFT OUTER JOIN dbo.[StatusCodes] STC ON STC.[CODE] = MAT.[Status] LEFT OUTER JOIN dbo.[CaseMaster] CSM LEFT OUTER JOIN dbo.[Templates] TMP ON TMP.[WKCODE] = CSM.[CSWKTCODE] ON CSM.[CSCODE] = MAT.[Code] LEFT OUTER JOIN [dbo].[FileColours] FCL ON FCL.[COLOURCODE] = MAT.[FileColour] WHERE MAT.[Code] = @CaseCode ) ADT FOR XML PATH(''Result''))as [Result] ' WHERE WidgetId = 145 GO UPDATE Widgets SET WidgetQuery = N' SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT ( SELECT * FROM( SELECT * FROM( SELECT TOP 1 ''Last Action'' AS [CaseStatus], LDIA.[FNCODE] AS [Who], ISNULL(CONVERT(VARCHAR,dbo.KAAS_FNConvertUTCDateToTargetTimeZone(LDIA.[DATE],@handler),106),'''') AS [Date], LDIA.[TEXT1] AS [Description] FROM [dbo].[diary] LDIA WHERE LDIA.[CASECODE] = @CaseCode AND LDIA.[STATUS] = 1 ORDER BY LDIA.[DATE] DESC )V UNION SELECT * FROM ( SELECT TOP 1 ''Last Milestone Action'' AS [CaseStatus], MDIA.[FNCODE] AS [Who], ISNULL(CONVERT(VARCHAR,dbo.KAAS_FNConvertUTCDateToTargetTimeZone(MDIA.[DATE],@handler),106),'''') AS [Date], MDIA.[TEXT1] AS [Description] FROM [dbo].[diary] MDIA WHERE MDIA.[CASECODE] = @CaseCode AND MDIA.[HIGHLIGHTED] = ''Y'' ORDER BY MDIA.[DATE] DESC )V1 UNION SELECT * FROM( SELECT TOP 1 ''Next Action'' AS [CaseStatus], NDIA.[FNCODE] AS [Who], ISNULL(CONVERT(VARCHAR,dbo.KAAS_FNConvertUTCDateToTargetTimeZone(NDIA.[DATE],@handler),106), '''') AS [Date], NDIA.[TEXT1] AS [Description] FROM dbo.[diary] NDIA WHERE NDIA.[CASECODE] = @CaseCode AND NDIA.[STATUS] = 0 ORDER BY NDIA.[DATE] ASC )V2 UNION SELECT * FROM( SELECT TOP (1) ''Last RecordTime'' AS [CaseStatus], TIE.[FeeEarn] AS [Who], ISNULL(CONVERT(VARCHAR,dbo.KAAS_FNConvertUTCDateToTargetTimeZone(TIE.[DATE],@handler),106), '''') AS [Date], TIE.[Comment] AS [Description] FROM [dbo].[TimeEntry] TIE WHERE TIE.[Matter] = @CaseCode AND ( (TIE.[TimeOrCharge] = ''T'') OR (TIE.[TimeOrCharge] = ''C'')) ORDER BY TIE.[Date] DESC, TIE.[Time] DESC )V3 ) ADT FOR XML PATH(''Result''))as [Result] ' WHERE WidgetId = 146 GO UPDATE Widgets SET WidgetQuery = N' SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT ( SELECT * FROM( SELECT ISNULL(CONVERT(VARCHAR, DIA.[DATE], 106), '''') AS [DATE], DIA.[FNCODE] AS [Who], RTRIM(LTRIM(DIA.[TEXT1])) AS [Description] FROM [dbo].[diary] DIA WHERE DIA.[ACTIONTYPE] = ''o'' AND DIA.[CASECODE] = @CaseCode ) ADT FOR XML PATH(''Result''))as [Result] ' WHERE WidgetId = 147 GO UPDATE Widgets SET WidgetQuery = N' SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT ( SELECT * FROM( SELECT UND.[DESCRIPTION] As [Description], UND.[VALUE] AS [Value], UND.[WHO] AS [Who], CASE [GIVENORRECEIVED] WHEN ''G'' THEN ''Given'' WHEN ''R'' THEN ''Received'' ELSE '''' END AS [Status] FROM [dbo].[UNDERTAKINGS] UND WHERE UND.[MATTER] = @CaseCode ) ADT FOR XML PATH(''Result''))as [Result] ' WHERE WidgetId = 148 GO UPDATE Widgets SET WidgetQuery = N' SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT ( SELECT * FROM( SELECT ISNULL(CONVERT(VARCHAR, DIA.[DATE], 106), '''') AS [Date], RTRIM(ISNULL(DIA.[FNCODE],'''')) AS [Who], CASE RTRIM(ISNULL([Status], '''')) WHEN ''1'' THEN ''True'' ELSE ''False'' END AS [Status], RTRIM(ISNULL(DIA.[TEXT1],'''')) AS [Description] FROM [dbo].[diary] DIA WHERE DIA.[ACTIONTYPE] = ''c'' AND DIA.[CASECODE] = @CaseCode --ORDER BY DIA.[date] ) ADT FOR XML PATH(''Result''))as [Result] ' WHERE WidgetId = 149 GO UPDATE Widgets SET WidgetQuery = N' DECLARE @IsSAM4 BIT DECLARE @ChargeBal DECIMAL(19, 2) DECLARE @TotalCharge Decimal(19, 2) SET @IsSAM4 = [dbo].[ISSAM4]() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT @TotalCharge = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N'' THEN 0 ELSE ISNULL(TIE.[Charge], 0) END), 0), @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 TIES.[NAME] AS [name], CONVERT(VARCHAR(20), TIES.[TOTALTIME] / 60) + '':'' + SUBSTRING(CONVERT(VARCHAR(3), 100 + (TIES.[TOTALTIME] % 60)), 2, 2) AS [Param1], TIES.[Charge] AS [Param2], CONVERT(DECIMAL(18,2),ISNULL(TIES.[Charge]/NULLIF(@TotalCharge,0),0)*100) AS [value] FROM ( SELECT TIE.[FeeEarn] AS [FeeEarn], HAN.[NAME] AS [NAME], ISNULL(SUM(ISNULL(TIE.[TIME], 0)), 0) AS [TOTALTIME], SUM(CONVERT(DECIMAL(18, 2), CASE WHEN @IsSAM4 = 1 AND TIE.[InvoiceNo] <> 0 THEN 0 WHEN @IsSAM4 = 0 THEN ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0) ELSE ISNULL(TIE.[Charge], 0) END)) AS [ChargeBal], SUM(CONVERT(DECIMAL(18, 2), ISNULL(TIE.[Charge], 0))) AS [Charge] FROM [dbo].[matters] MAT INNER JOIN [dbo].[TimeEntry] TIE ON TIE.[MATTER] = MAT.[Code] INNER JOIN [dbo].[Handlers] HAN ON HAN.[CODE] = TIE.[FeeEarn] WHERE TIE.[MATTER] = @CaseCode AND TIE.[DATE] < GETDATE() AND ( (TIE.[TimeOrCharge] = ''T'') OR (TIE.[TimeOrCharge] = ''C'')) AND TIE.[REC_IRR] <> ''N'' GROUP BY TIE.[FEEEARN], HAN.[NAME] ) TIES ORDER BY TIES.[ChargeBal] DESC, TIES.[TOTALTIME] DESC FOR XML PATH(''Widget1'') )AS [Result] UNION ALL SELECT ( SELECT TIES.[NAME] AS [name], CONVERT(VARCHAR(20), TIES.[WIPTOTALTIME] / 60) + '':'' + SUBSTRING(CONVERT(VARCHAR(3), 100 + (TIES.[WIPTOTALTIME] % 60)), 2, 2) AS [Param1], TIES.[ChargeBal] AS [Param2], CONVERT(DECIMAL(18,2), ISNULL(TIES.[ChargeBal]/NULLIF(@ChargeBal,0),0)*100) AS [value] FROM ( SELECT TIE.[FeeEarn] AS [FeeEarn], HAN.[NAME] AS [NAME], ISNULL(SUM(ISNULL(TIE.[TIME], 0)), 0) AS [TOTALTIME], SUM(CONVERT(DECIMAL(18, 2), CASE WHEN @IsSAM4 = 1 AND TIE.[InvoiceNo] <> 0 THEN 0 WHEN @IsSAM4 = 0 THEN ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0) ELSE ISNULL(TIE.[Charge], 0) END)) AS [ChargeBal], CONVERT(INT, FLOOR(CONVERT(DECIMAL(18, 2), ISNULL(SUM(CASE WHEN @IsSAM4 = 1 AND [TIE].[InvoiceNo] <> 0 THEN 0 WHEN [TIE].[Charge] - [TIE].[BilledAmount] = 0 THEN 0 ELSE TIE.[TIME] END) , 0)) * CASE WHEN SUM(CONVERT(DECIMAL(18, 2), CASE WHEN @IsSAM4 = 1 AND [TIE].[InvoiceNo] <> 0 THEN 0 WHEN [TIE].[Charge] - [TIE].[BilledAmount] = 0 THEN 0 ELSE TIE.[Charge] END)) = 0 THEN 0 ELSE SUM(CONVERT(DECIMAL(18, 2), CASE WHEN @IsSAM4 = 1 AND TIE.[InvoiceNo] <> 0 THEN 0 WHEN @IsSAM4 = 0 THEN ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0) ELSE ISNULL(TIE.[Charge], 0) END)) / SUM(CONVERT(DECIMAL(18, 2), CASE WHEN @IsSAM4 = 1 AND [TIE].[InvoiceNo] <> 0 THEN 0 WHEN [TIE].[Charge] - [TIE].[BilledAmount] = 0 THEN 0 ELSE TIE.[Charge] END)) END)) AS [WIPTOTALTIME] FROM [dbo].[matters] MAT INNER JOIN [dbo].[TimeEntry] TIE ON TIE.[MATTER] = MAT.[Code] INNER JOIN [dbo].[Handlers] HAN ON HAN.[CODE] = TIE.[FeeEarn] WHERE TIE.[MATTER] = @CaseCode AND TIE.[DATE] < GETDATE() AND ( (TIE.[TimeOrCharge] = ''T'') OR (TIE.[TimeOrCharge] = ''C'')) AND TIE.[REC_IRR] <> ''N'' GROUP BY TIE.[FEEEARN], HAN.[NAME] ) TIES ORDER BY TIES.[ChargeBal] DESC, TIES.[TOTALTIME] DESC FOR XML PATH(''Widget2''))as [Result] ' WHERE WidgetId = 150 GO UPDATE Widgets SET WidgetQuery = N' DECLARE @IsSAM4 BIT DECLARE @ChargeBal DECIMAL(19, 2) DECLARE @TotalCharge Decimal(19, 2) DECLARE @ActivityTotalTime VARCHAR(100) DECLARE @ActivityTotalValue DECIMAL(18,2) SET @IsSAM4 = [dbo].[ISSAM4]() SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END SELECT @ActivityTotalTime = CONVERT(VARCHAR(20), TIES.[TOTALTIME] / 60) + '':'' + SUBSTRING(CONVERT(VARCHAR(3), 100 + (TIES.[TOTALTIME] % 60)), 2, 2), @ActivityTotalValue = TIES.[Value] FROM ( SELECT ISNULL(SUM(ISNULL(TIE.[TIME], 0)), 0) AS [TOTALTIME], SUM(CONVERT(DECIMAL(18, 2), NULLIF(TIE.[Charge], 0))) AS [Value] 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'') TIES SELECT ( SELECT TIES.[Activity] AS [name], CASE WHEN TIES.[TOTALTIME] < 0 THEN ''-'' ELSE '''' END + CONVERT(VARCHAR(20), ABS(TIES.[TOTALTIME]) / 60) + '':'' + SUBSTRING(CONVERT(VARCHAR(3), 100 + (ABS(TIES.[TOTALTIME]) % 60)), 2, 2) AS [Param1], TIES.[Value] AS [Param2], CONVERT(DECIMAL(18, 2),ISNULL(TIES.[Value]/CONVERT(DECIMAL(18, 2), NULLIF(@ActivityTotalValue, 0)),0)*100) AS [value] FROM ( SELECT TIE.[TASK] AS [Task], CASE WHEN TSC.[DESC] IS NULL THEN ''Task Code: '' + ISNULL(TIE.[TASK], '''') ELSE RTRIM(ISNULL(TSC.[DESC],'''')) END AS [Activity], ISNULL(SUM(ISNULL(TIE.[TIME], 0)), 0) AS [TOTALTIME], SUM( CONVERT(DECIMAL(18, 2), ISNULL(TIE.[Charge], 0))) AS [Value] FROM [dbo].[matters] MAT INNER JOIN [dbo].[TimeEntry] TIE LEFT OUTER JOIN [dbo].[TasksCodes] TSC ON TSC.[CODE] = TIE.[Task] ON TIE.[MATTER] = MAT.[Code] WHERE TIE.[MATTER] = @CaseCode AND TIE.[DATE] < GETDATE() AND ( (TIE.[TimeOrCharge] = ''T'') OR (TIE.[TimeOrCharge] = ''C'')) AND TIE.[REC_IRR] <> ''N'' AND TIE.[Task] <> ''WRI'' GROUP BY TIE.[TASK], CASE WHEN TSC.[DESC] IS NULL THEN ''Task Code: '' + ISNULL(TIE.[TASK], '''') ELSE RTRIM(ISNULL(TSC.[DESC],'''')) END) TIES ORDER BY TIES.[Value] DESC, TIES.[TOTALTIME] DESC FOR XML PATH(''Widget1''))as [Result] ' WHERE WidgetId = 151 GO