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

-------------------------------------------Panels Insert Start---------------------------------------------------------

SET IDENTITY_INSERT [dbo].[Panels] ON 

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 1)
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (1, N'Tasks', N'Panel to display all the tasks', 'tasks', 100)
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 2)
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (2, N'Fees Issued', N'Fees Issued Information Panel', 'tasks', 50)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 3)
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (3, N'Account Summary', N'Account Summary', 'caseDiary', 100)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 4)
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (4, N'Fees Issued', N'Fees Issued Information Panel', null, 0)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE PanelId = 5)
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (5, N'My Day Books', N'My day book panel ', 'timeCosting', 100)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE [PanelName] = 'Current Matter Details' AND DashboardType = 'caseDiary')
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (6, N'Current Matter Details', N'Current Matter Details', 'caseDiary', 50)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE [PanelName] = 'Case Status' AND DashboardType = 'caseDiary')
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (7, N'Case Status', N'Case Status', 'caseDiary', 50)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE [PanelName] = 'Time By FE' AND DashboardType = 'caseDiary')
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (8, N'Time By FE', N'Time By FE', 'caseDiary', 50)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE [PanelName] = 'Activity' AND DashboardType = 'caseDiary')
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (9, N'Activity', N'Activity', 'caseDiary', 100)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE [PanelName] = 'Court Dates' AND DashboardType = 'caseDiary')
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (10, N'Court Dates', N'Court Dates', 'caseDiary', 50)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE [PanelName] = 'Outstanding Critical Dates' AND DashboardType = 'caseDiary')
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (11, N'Outstanding Critical Dates', N'Outstanding Critical Dates', 'caseDiary', 50)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Panels] WHERE [PanelName] = 'Undertakings' AND DashboardType = 'caseDiary')
BEGIN
	INSERT [dbo].[Panels] ([PanelId], [PanelName], [Description], [DashboardType], [Width]) VALUES (12, N'Undertakings', N'Undertakings', 'caseDiary', 50)
END
GO

SET IDENTITY_INSERT [dbo].[Panels] OFF
GO

---------------------------------------------Panels Insert End-----------------------------------------------------------



------------------------------------------UI Templates Insert Start-------------------------------------------------------

SET IDENTITY_INSERT [dbo].[UITemplates] ON 

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1000)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1000, N'Over due Tasks', N'Template for over due tasks', N'overDueTasksQueryComponent', N'overDueTasksDisplayComponent')
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1001)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1001, N'Outstanding Tasks', N'Template for Outstanding Tasks', N'Outstanding Tasks QueryComponent', N'Outstanding Tasks DisplayComponent')
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1002)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1002, N'Pending Tasks', N'Template for Pending tasks', N'pendingTasksQueryComponent', N'pendingTasksDisplayComponent')
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1003)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1003, N'My Status', N'Template for current user status', N'myStatusQueryComponent', N'myStatusDisplayComponent')
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1004)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1004, N'Users in Project', N'Template for users based on project ', N'projectUserQueryComponent', N'projectUserDisplayComponent')
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1005)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1005, N'User List', N'All Users template', N'userListQueryComponent', N'userListDisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1006)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1006, N'Critical Tasks', N'Template for Critical tasks', N'CriticalTasks Query component', N'CriticalTasks Display component')
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1007)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1007, N'Court Dates', N'Template for Court Dates', N'Court Dates QueryComponent', N'Court Dates DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1008)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1008, N'High priority Tasks', N'Template for High priority Tasks', N'High priority Tasks QueryComponent', N'High priority Tasks DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1009)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1009, N'Phone Message', N'Template for Phone Message', N'Phone Message QueryComponent', N'Phone Message DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1010)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1010, N'Undertakings', N'Template for Undertakings', N'Undertakings QueryComponent', N'Undertakings DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1011)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1011, N'Statute date approaching', N'Template for Statute date approaching', N'Statute date approaching QueryComponent', N'Statute date approaching DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1012)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1012, N'Open Matters', N'Template for Open Matters', N'Open Matters QueryComponent', N'Open Matters DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1013)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent])
	VALUES 
		(1013, N'My dormant Matters', N'Template for My dormant Matters', N'My dormant Matters QueryComponent', N'My dormant Matters DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1014)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1015)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1016)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1017)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1018)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1019)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1020)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1021)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1022)
BEGIN
	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')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1023)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1024)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1025)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1026)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1027)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1028)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1029)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1030)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1031)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1032)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1033)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1034)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1035)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1036)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1037)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1038)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1039)
BEGIN
	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')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1040)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1040, N'Gauge Template', N'Gauge Template used in Timecosting', NULL, NULL)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1041)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1041, N'Time Display', N'Time Display', NULL, NULL)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1042)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1042, N'Table value', N'Table value', N'Table value', N'Table value')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1043)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1043, N'Pie Chart', N'Pie Chart', N'Pie Chart', N'Pie Chart')
END
GO

SET IDENTITY_INSERT [dbo].[UITemplates] OFF
GO

-------------------------------------UI Templates Insert End-------------------------------------------------------


------------------------------------------Widget Insert Start-----------------------------------------------------

SET IDENTITY_INSERT [dbo].[Widgets] ON 

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 100)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(100, N'Over due Tasks', N'Shows all Over due Tasks', 1000, N' {"color":"warn-fg", "title":"Overdue", "icon":"receipt"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 101)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(101, N'Outstanding Tasks', N'Shows all Outstanding Tasks', 1001, N' {"color":"orange-fg", "title":"Outstanding", "icon":"list_alt"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 102)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(102, N'Fees Issued YTD', N'Shows all Fees Issued Year to Date', 1002, NULL)
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 103)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(103, N'Fees Issued This Month', N'Shows all Fees Issued This Month', 1003, NULL)
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 104)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(104, N'Chargeable Time YTD', N'Shows all Chargeable Time YTD', 1004, NULL)
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 105)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(105, N'Chargeable Time This Month', N'Shows all Chargeable Time This Month', 1005, NULL)
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 106)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData])
	VALUES 
		(106, N'Critical Tasks', N'Shows all Critical Tasks', 1006, N' {"color":"red-fg", "title":"Critical", "icon":"priority_high"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 107)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(107, N'Court Dates', N'Shows all Court Dates', 1007, N' {"color":"blue-fg", "title":"Court Dates", "icon":"event_available"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 108)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(108, N'High priority Tasks', N'Shows all High priority Tasks', 1008, N' {"color":"deep-purple-fg", "title":"Priority", "icon":"format_line_spacing"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 109)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(109, N'Phone message', N'Shows all Phone message', 1009, N' {"color":"amber-fg", "title":"Messages", "icon":"local_phone"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 110)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(110, N'Undertakings', N'Shows all Undertakings', 1010, N' {"color":"yellow-fg", "title":"Undertakings", "icon":"child_friendly"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 111)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(111, N'Statue date approaching', N'Shows count of Statue date approaching', 1011, N' {"color":"green-fg", "title":"Statute", "icon":"card_membership"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 112)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(112, N'Open Matters', N'Shows count of Open Matters', 1012, N' {"color":"light-green-fg", "title":"Matters", "icon":"local_library"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 113)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(113, N'My dormant Matters', N'Shows count of My dormant Matters', 1013, N' {"color":"lime-fg", "title":"Dormant", "icon":"book"}')
END

GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 114)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(114, N'No of Effectively Complete Matters', N'Shows count of No of Effectively Complete Matters', 1014, N' {"color":"brown-fg", "title":"Complete Matters", "icon":"calendar_today"}')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 115)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(115, N'No. of Cases where expected invoice date has passed', N'Shows count of No. of Cases where expected invoice date has passed', 1015, N' {"color":"fuse-navy-fg", "title":"Date Passed Cases", "icon":"event"}')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 116)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(116, N'No. of Cases approaching estimate fee', N'Shows count of No. of Cases approaching estimate fee', 1016, N' {"color":"teal-fg", "title":"Estimate Fee", "icon":"confirmation_number"}')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 117)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(117, N'No. of Cases approaching lockup value limit', N'Shows count of No. of Cases approaching lockup value limit', 1017, N' {"color":"indigo-fg", "title":"Lookup Value", "icon":"vertical_align_top"}')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 118)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(118, N'No. of Clients that require money laundering checks', N'Shows count of No. of Clients that require money laundering checks', 1018, N' {"color":"purple-fg", "title":"Money Laundering", "icon":"assignment_ind"}')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 119)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(119, N'No. of Cases with no estimate given', N'Shows count of No. of Cases with no estimate given', 1019, N' {"color":"pink-fg", "title":"No Estimate Time", "icon":"event_note"}')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 120)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(120, N'No. of Cases with no estimated fee', N'Shows count of No of Cases with no estimated fee', 1020, N' {"color":"accent-fg", "title":"No Estimate Fee", "icon":"account_balance_wallet"}')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 121)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(121, N'Debtors A/C', N'Debtors Account information', 1021, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 122)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(122, N'Outlay A/C', N'Outlay Account information', 1022, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 123)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(123, N'Outlay Budget', N'Outlay Budget information', 1023, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 124)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(124, N'Current Outstanding Fees', N'Current Outstanding Fees information', 1024, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 125)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(125, N'Client A/C', N'Client Account information', 1025, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 126)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(126, N'Client Current', N'Client Current information', 1026, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 127)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(127, N'Client Deposit', N'Client Deposit information', 1027, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 128)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(128, N'Total Work In Progress', N'Total Work In Progress information', 1028, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 129)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(129, N'Write Off Time', N'Write Off Time information', 1029, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 130)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(130, N'Fees Issued To Date', N'Fees Issued To Date information', 1030, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 131)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(131, N'Fees To Date + WIP', N'Fees To Date + WIP information', 1031, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 132)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(132, N'Estimate Fees', N'Estimate Fees information', 1032, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 133)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(133, N'Draft Invoices', N'Draft Invoices information', 1033, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 134)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(134, N'Last Bill Date', N'Last Bill Date information', 1034, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 135)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(135, N'Expected Bill Date', N'Expected Bill Date information', 1035, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 136)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(136, N'Current Profit/Loss', N'Current Profit/Loss information', 1036, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 137)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(137, N'Possible Value To The Client ', N'Possible Value To The Client  information', 1037, N'')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 138)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(138, N'Anti Money Laundering Checked', N'Anti Money Laundering Checked information', 1038, N'')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 139)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(139, N'Current Loked Up Value', N'Current Loked Up Value', 1039, N'')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 140)
BEGIN
	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}}')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 141)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(141, N'Chargeable', N'Chargeable Time', 1041, N'{"key":"ChargeableTime","color":"#33A5EB","time":"00:07"}')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 142)
BEGIN
	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"}')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 143)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(143, N'Total', N'Total Time', 1041, N'{"key":"TotalTime","color":"#FF9400","time":"00:07"}')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 144)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(144, N'Remaining', N'Remaining Time', 1041, N'{"key":"RemainingTime","color":"#597C88","time":"00:07"}')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 145 AND TemplateId = 1042)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(145, N'Current Matter Details', N'Current Matter Details', 1042, N'')
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 146 AND TemplateId = 1042)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(146, N'Case Status', N'Case Status', 1042, N'')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 147 AND TemplateId = 1042)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(147, N'Court Dates', N'Court Dates', 1042, N'')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 148 AND TemplateId = 1042)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(148, N'Undertakings', N'Undertakings', 1042, N'')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 149 AND TemplateId = 1042)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(149, N'Outstanding Critical Dates', N'Outstanding Critical Dates', 1042, N'')
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
	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"}')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [Widgets] WHERE WidgetId = 151 AND TemplateId = 1043)
BEGIN
	INSERT 
		[dbo].[Widgets] ([WidgetId], [WidgetName], [Description], [TemplateId], [WidgetData]) 
	VALUES 
		(151,  N'Activity', N'Activity', 1043, N'')
END
GO

SET IDENTITY_INSERT [dbo].[Widgets] OFF
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

SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON 
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 1)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (1, 1, 100)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 2)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (2, 1, 101)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 3)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (3, 2, 102)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 4)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (4, 4, 103)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 5)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (5, 4, 104)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 6)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (6, 4, 105)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 7)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (7, 1, 106)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 8)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (8, 1, 107)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 9)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (9, 1, 108)
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 10)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (10, 1, 109)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 11)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (11, 1, 110)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 12)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (12, 1, 111)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 13)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (13, 1, 112)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 14)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (14, 1, 113)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 15)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (15, 1, 114)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 16)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (16, 1, 115)
END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 17)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (17, 1, 116)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 18)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (18, 1, 117)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 19)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (19, 1, 118)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 20)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (20, 1, 119)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 21)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (21, 1, 120)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 22)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (22, 3, 121)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 23)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (23, 3, 122)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 24)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (24, 3, 123)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 25)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (25, 3, 124)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 26)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (26, 3, 125)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 27)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (27, 3, 126)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 28)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (28, 3, 127)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 29)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (29, 3, 128)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 30)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (30, 3, 129)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 31)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (31, 3, 130)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 32)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (32, 3, 131)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 33)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (33, 3, 132)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 34)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (34, 3, 133)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 35)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (35, 3, 134)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 36)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (36, 3, 135)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 37)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (37, 3, 136)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 38)
BEGIN
INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (38, 3, 137)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 39)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (39, 3, 138)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 40)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (40, 3, 139)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE AssociationID = 41)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (41, 5, 140)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 5 AND WidgetId = 141)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (42, 5, 141)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 5 AND WidgetId = 142)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (43, 5, 142)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 5 AND WidgetId = 143)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (44, 5, 143)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 5 AND WidgetId = 144)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (45, 5, 144)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 6 AND WidgetId = 145)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (46, 6, 145)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 7 AND WidgetId = 146)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (47, 7, 146)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 10 AND WidgetId = 147)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (48, 10, 147)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 12 AND WidgetId = 148)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (49, 12, 148)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 11 AND WidgetId = 149)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (50, 11, 149)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 8 AND WidgetId = 150)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (51, 8, 150)
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [PanelWidgetAssociation] WHERE PanelId = 9 AND WidgetId = 151)
BEGIN
	INSERT [dbo].[PanelWidgetAssociation] ([AssociationID], [PanelId], [WidgetId]) VALUES (52, 9, 151)
END
GO

SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF
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
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