Update UITemplates set TemplateQuery=
'
DECLARE @BitWiseID          BIGINT  
DECLARE @GETDATE            DATETIME  

SET @GETDATE = GETUTCDATE()                        
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END    
SELECT @BitWiseID = HNG.[BitWiseID]    FROM HandlerNETGroupMembership HNG   WHERE HNG.[HANDLER] = CONVERT(VARCHAR(10), @handler)    
SET @BitWiseID = ISNULL(@BitWiseID, [dbo].[KAAS_FN_GetOldPermissions](RTRIM(ISNULL(@handler, ''''))))    IF (@handler = ''ADM'')    
BEGIN  SET @BitWiseID = CONVERT(BIGINT, -1)    END    

/*Today Over due Task*/
SELECT * INTO #Table1 FROM(
SELECT ISNULL(ADT.[Overdue Task], 0) AS [HeaderCount] , ''Over due task'' as HeaderLabel   
FROM (  SELECT SUM(CASE WHEN DEL.[DATE] < ''18010101'' THEN 0                            
WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR, @GETDATE, 112))) THEN 0                            
WHEN [DEL].[STATUS] = 0 THEN 1 ELSE 0 END) AS [Overdue Task] FROM [dbo].[DiaryDelegations] DEL                   
INNER JOIN [dbo].[Diary] DIA       
INNER JOIN [dbo].[matters] MAT        
INNER JOIN [dbo].[Contacts] CON          
ON CON.[CODE] = MAT.[CLIENTCODE]        
LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP          
ON MNP.[MATTER] = MAT.[Code]          
ON MAT.[CODE] = DIA.[CASECODE]          
AND ISNULL(MAT.[Closed], ''N'') <> ''Y''          
ON DIA.[ACTIONID] = DEL.[ACTIONID]             
WHERE DEL.[HANDLER] = @Handler               
AND DEL.[STATUS] = 0               
AND DEL.[DelType] <> ''Completed''               
AND DEL.[DelType] <> ''Processed''               
AND DEL.[DelType] <> ''Returned''               
AND (ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0                    
OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT) AS TBL 

/*yesterdays Over due task*/  
SET @GETDATE=(SELECT  DATEADD(DAY, CASE DATENAME(WEEKDAY, @GETDATE)  
				WHEN ''Sunday'' THEN -2   
				WHEN ''Monday'' THEN -3                           
				ELSE -1 END, DATEDIFF(DAY, 0, @GETDATE)))    
	

SELECT * INTO #Table2 FROM(
SELECT ISNULL(ADT.[Overdue Task], 0) AS [FooterCount] , 
	''Yesterday''''s Over due task'' as FooterLabel     
 FROM (  SELECT SUM(CASE WHEN DEL.[DATE] < ''18010101'' THEN 0                                    
			WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR, @GETDATE, 112))) 
			THEN 0                                    
			WHEN [DEL].[STATUS] = 0  
			THEN 1         
			ELSE 0 
		END) AS [Overdue Task]                  
	FROM [dbo].[DiaryDelegations] DEL                       
	INNER JOIN [dbo].[Diary] DIA           
	INNER JOIN [dbo].[matters] MAT            
	INNER JOIN [dbo].[Contacts] CON  ON CON.[CODE] = MAT.[CLIENTCODE]            
	LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP ON MNP.[MATTER] = MAT.[Code]  ON MAT.[CODE] = DIA.[CASECODE]              AND ISNULL(MAT.[Closed], ''N'') <> ''Y''   ON DIA.[ACTIONID] = DEL.[ACTIONID]                 WHERE DEL.[HANDLER] = @Handler      AND DEL.[STATUS] = 0      AND DEL.[DelType] <> ''Completed''                    AND DEL.[DelType] <> ''Processed''                    AND DEL.[DelType] <> ''Returned''                    AND (ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0                         OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT      

) AS TBL

SELECT (
SELECT * FROM #Table1,#Table2
FOR XML  PATH(''Result'')) as [Result]

DROP TABLE #Table1,#Table2
'

where templateid=1000
Go

Update UITemplates set TemplateQuery=
'DECLARE @BitWiseID          BIGINT    
DECLARE @GETDATE            DATETIME    
SET @GETDATE = GETUTCDATE()  
SET @todate=''21990101''   
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END      
SELECT @BitWiseID = HNG.[BitWiseID]      FROM HandlerNETGroupMembership HNG     WHERE HNG.[HANDLER] = CONVERT(VARCHAR(10), @handler)      
SET @BitWiseID = ISNULL(@BitWiseID, [dbo].[KAAS_FN_GetOldPermissions](RTRIM(ISNULL(@handler, ''''))))    IF (@handler = ''ADM'')      
BEGIN      SET @BitWiseID = CONVERT(BIGINT, -1)    END      

/*Today Outstanding Task*/ 
 SELECT * INTO #Table1 FROM(    
SELECT ISNULL(ADT.[Outstanding Task], 0) AS [HeaderCount], ''Outstanding task'' as HeaderLabel  
FROM (  SELECT SUM(CASE WHEN DEL.[DATE] < ''18010101'' THEN 0
WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR,@todate, 112))) THEN 0 
WHEN [DEL].[STATUS] = 0  THEN 1  ELSE 0 END) AS [Outstanding Task]   FROM [dbo].[DiaryDelegations] DEL 
INNER JOIN [dbo].[Diary] DIA   
INNER JOIN [dbo].[matters] MAT     
INNER JOIN [dbo].[Contacts] CON     
ON CON.[CODE] = MAT.[CLIENTCODE]    
LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP   
ON MNP.[MATTER] = MAT.[Code]            
ON MAT.[CODE] = DIA.[CASECODE]            
AND ISNULL(MAT.[Closed], ''N'') <> ''Y''       
ON DIA.[ACTIONID] = DEL.[ACTIONID]             
WHERE DEL.[HANDLER] = @Handler    
AND DEL.[STATUS] = 0     
AND DEL.[DelType] <> ''Completed''     
AND DEL.[DelType] <> ''Processed''     
AND DEL.[DelType] <> ''Returned''         
AND (ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0  
OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT ) AS TBL  
 
/*Yesterday Outstanding Task*/  
Set @GETDATE=(SELECT  DATEADD(DAY, CASE DATENAME(WEEKDAY, @GETDATE)                           
				WHEN ''Sunday'' THEN -2                           
				WHEN ''Monday'' THEN -3                           
				ELSE -1 END, DATEDIFF(DAY, 0, @GETDATE)))  
				
SELECT * INTO #Table2 FROM(				
SELECT ISNULL(ADT.[Outstanding Task], 0) AS [FooterCount], 
	''Yesterday''''s Outstanding task'' as FooterLabel      
FROM (  SELECT SUM(CASE WHEN DEL.[DATE] < ''18010101'' THEN 0            
			WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR, @todate, 112)))                                 
			THEN 0         
			WHEN [DEL].[STATUS] = 0           
			THEN 1          
			ELSE 0        
		END) AS [Outstanding Task]     
	FROM [dbo].[DiaryDelegations] DEL                       
	INNER JOIN [dbo].[Diary] DIA           
	INNER JOIN [dbo].[matters] MAT            
	INNER JOIN [dbo].[Contacts] CON               
	ON CON.[CODE] = MAT.[CLIENTCODE]            
	LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP               
	ON MNP.[MATTER] = MAT.[Code]               
	ON MAT.[CODE] = DIA.[CASECODE]              
	AND ISNULL(MAT.[Closed], ''N'') <> ''Y''               
	ON DIA.[ACTIONID] = DEL.[ACTIONID]                 
	WHERE DEL.[HANDLER] = @Handler      
	AND DEL.[STATUS] = 0       
	AND DEL.[DelType] <> ''Completed''       
	AND DEL.[DelType] <> ''Processed''       
	AND DEL.[DelType] <> ''Returned''                    
	AND (ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0                         
	OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT    ) AS TBL

SELECT(
SELECT * FROM #Table1,#Table2
for xml path(''Result'')) AS Result

drop table #Table1,#Table2

'

where templateid=1001
Go

Update UITemplates set TemplateQuery='DECLARE @NCOMMAND NVARCHAR(MAX)
DECLARE @IsSAM4 BIT
DECLARE @GETDATE DATETIME
SET @GETDATE = GETDATE()
SET @IsSAM4 = [dbo].[ISSAM4]()


DECLARE @FeesYTD      DECIMAL(18, 2)
DECLARE @FeesLastYTD  DECIMAL(18, 2)

/*Target*/
DECLARE @FEESISSUEDYTD DECIMAL(18, 2)
    DECLARE @YEAR INT
    DECLARE @PERIOD INT

    /*Get the financial year and period from the KPI, if not there then control, if not there then use now.*/
    IF [dbo].[ISSAM4]() = 0
    BEGIN
        SELECT @YEAR = ISNULL(KPI.[Year], ISNULL(CON.[YEAR], YEAR(GETUTCDATE())))
          FROM [dbo].[KPIParameters] AS KPI
               OUTER APPLY [dbo].[control] AS CON

        SELECT @PERIOD = ISNULL(KPI.[Period], ISNULL(CON.[CURPER], MONTH(GETUTCDATE())))
          FROM [dbo].[KPIParameters] AS KPI
               OUTER APPLY [dbo].[control] AS CON
    END
    ELSE
    BEGIN
        SELECT @YEAR = ISNULL(KPI.[Year], ISNULL(CON.[NumValue], YEAR(GETUTCDATE())))
          FROM [dbo].[KPIParameters] AS KPI
               OUTER APPLY (SELECT TOP 1 NUM.[NumValue]
                              FROM [dbo].[CtrlNum] AS NUM
                             WHERE NUM.[Code] = ''CurYear'') AS CON

        SELECT @PERIOD = ISNULL(KPI.[Period], ISNULL(CON.[NumValue], MONTH(GETUTCDATE())))
          FROM [dbo].[KPIParameters] AS KPI
               OUTER APPLY (SELECT TOP 1 NUM.[NumValue]
                              FROM [dbo].[CtrlNum] AS NUM
                             WHERE NUM.[Code] = ''CurPeriod'') AS CON
    END

    /* This is very unwieldy but not much choice when converting time values stored as strings into time formats (7.5 -> 07:30)*/
   
    SELECT @FEESISSUEDYTD = FLOOR(SUM(BDG.[FeesBudget]))         
      FROM [dbo].[HandlerBudgets] AS BDG
     WHERE BDG.[Handler] = @handler
       AND BDG.[Month]  <= @PERIOD
       AND BDG.[Year]    = @YEAR



/*FeesYearToDate*/
IF @IsSAM4 = 0
  BEGIN
    SET @NCOMMAND = ''
  SELECT @FeesYTD = ISNULL(SUM(CASE WHEN BH.[INVCR] = ''''I'''' THEN BD.[VALUE] ELSE 0 END - CASE WHEN BH.[INVCR] = ''''C'''' THEN BD.[VALUE] ELSE 0 END), 0)
    FROM dbo.[BatchDetails] BD
         INNER JOIN dbo.[BatchH] BH
                 ON BD.[BATCHNO] = BH.[BATCHNO]
                AND BH.[POSTED] = ''''Y''''
         INNER JOIN [dbo].[KPIParameters] KPI
                 ON KPI.[Year] = BD.[YEAR]
                AND KPI.[Period] >= BD.[PERNO]
   WHERE BD.[FEE] = @handler
     AND BD.[TYPE] = ''''I''''
     AND BD.[OUTLAY] = ''''F''''''
  END
ELSE
  BEGIN
    SET @NCOMMAND = ''
SELECT @FeesYTD = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0)
  FROM [dbo].[Transactions] HED
       INNER JOIN [dbo].[Transactions] TRN
               ON TRN.[BatchNo] = HED.[BatchNo]
              AND TRN.[RecType] = ''''D''''
              AND TRN.[LineType] = ''''F''''
              AND TRN.[XnType] = ''''I''''
              AND TRN.[Posted] = ''''Y''''
              AND (   TRN.[CorrectionInd] = ''''L''''
                   OR TRN.[CorrectionInd] = ''''X'''')
              AND TRN.[FeeEarner] = @handler
       INNER JOIN [dbo].[KPIParameters] KPI
               ON KPI.[Year] = HED.[PostingYear]
              AND KPI.[Period] >= HED.[PostingPeriod]
 WHERE HED.[RecType] = ''''H''''''
  END

EXECUTE sp_executesql @NCOMMAND, N''@handler VARCHAR(10),@FeesYTD DECIMAL(18, 2) OUTPUT'', @handler = @handler, @FeesYTD=@FeesYTD OUTPUT

/*FeesLastYearToDate*/
IF @IsSAM4 = 0
BEGIN
    SET @NCOMMAND = ''SELECT @FeesLastYTD = ISNULL(SUM(CASE WHEN BH.[INVCR] = ''''I'''' THEN BD.[VALUE] ELSE 0 END - CASE WHEN BH.[INVCR] = ''''C'''' THEN BD.[VALUE] ELSE 0 END), 0)
                       FROM dbo.[BatchDetails] BD
                            INNER JOIN dbo.[BatchH] BH
                                    ON BD.[BATCHNO] = BH.[BATCHNO]
                                   AND BH.[POSTED] = ''''Y''''
                            INNER JOIN [dbo].[KPIParameters] KPI
                                    ON KPI.[Year]-1 = BD.[YEAR]
                                   AND KPI.[Period] >= BD.[PERNO]
                      WHERE BD.[FEE] = @handler
                        AND BD.[TYPE] = ''''I''''
                        AND BD.[OUTLAY] = ''''F''''''
END
ELSE
BEGIN
    SET @NCOMMAND = ''SELECT @FeesLastYTD = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0)
                       FROM [dbo].[Transactions] HED
                            INNER JOIN [dbo].[Transactions] TRN
                                    ON TRN.[BatchNo] = HED.[BatchNo]
                                   AND TRN.[RecType] = ''''D''''
                                   AND TRN.[LineType] = ''''F''''
                                   AND TRN.[XnType] = ''''I''''
                                   AND TRN.[Posted] = ''''Y''''
                                   AND TRN.[CorrectionInd] IN (''''L'''', ''''X'''')
                                   AND TRN.[FeeEarner] = @handler
                            INNER JOIN [dbo].[KPIParameters] KPI
                                    ON KPI.[Year]-1 = HED.[PostingYear]
                                   AND KPI.[Period] >= HED.[PostingPeriod]
                      WHERE HED.[RecType] = ''''H''''''
END

EXECUTE sp_executesql @NCOMMAND, N''@handler VARCHAR(10),@FeesLastYTD DECIMAL(18, 2) OUTPUT'', @handler = @handler, @FeesLastYTD=@FeesLastYTD OUTPUT

SELECT (
SELECT REPLACE(CONVERT(VARCHAR, ROUND(ISNULL(@FEESISSUEDYTD, 0), 0), 1), ''.00'', '''') AS [TargetYearToDate],
	   REPLACE(CONVERT(VARCHAR, CAST(ROUND(ISNULL(@FeesYTD, 0), 0) AS MONEY), 1), ''.00'', '''') AS [FeesIssuedYearToDate],
       REPLACE(CONVERT(VARCHAR, CAST(ROUND(ISNULL(@FeesLastYTD, 0), 0) AS MONEY), 1), ''.00'', '''') AS [FeesIssuedLastYearToDate]
	   FOR XML PATH(''Result'')) AS [Result]'


where templateid=1002
GO

Update UITemplates set TemplateQuery=
'DECLARE @NCOMMAND NVARCHAR(MAX)
DECLARE @IsSAM4 BIT
DECLARE @GETDATE DATETIME
SET @GETDATE = GETDATE()
SET @IsSAM4 = [dbo].[ISSAM4]()


DECLARE @FeesYTD      DECIMAL(18, 2)
DECLARE @FeesLastYTD  DECIMAL(18, 2)

/*Target*/
DECLARE @FEESISSUEDYTD DECIMAL(18, 2)
    DECLARE @YEAR INT
    DECLARE @PERIOD INT

    /*Get the financial year and period from the KPI, if not there then control, if not there then use now.*/
    IF [dbo].[ISSAM4]() = 0
    BEGIN
        SELECT @YEAR = ISNULL(KPI.[Year], ISNULL(CON.[YEAR], YEAR(GETUTCDATE())))
          FROM [dbo].[KPIParameters] AS KPI
               OUTER APPLY [dbo].[control] AS CON

        SELECT @PERIOD = ISNULL(KPI.[Period], ISNULL(CON.[CURPER], MONTH(GETUTCDATE())))
          FROM [dbo].[KPIParameters] AS KPI
               OUTER APPLY [dbo].[control] AS CON
    END
    ELSE
    BEGIN
        SELECT @YEAR = ISNULL(KPI.[Year], ISNULL(CON.[NumValue], YEAR(GETUTCDATE())))
          FROM [dbo].[KPIParameters] AS KPI
               OUTER APPLY (SELECT TOP 1 NUM.[NumValue]
                              FROM [dbo].[CtrlNum] AS NUM
                             WHERE NUM.[Code] = ''CurYear'') AS CON

        SELECT @PERIOD = ISNULL(KPI.[Period], ISNULL(CON.[NumValue], MONTH(GETUTCDATE())))
          FROM [dbo].[KPIParameters] AS KPI
               OUTER APPLY (SELECT TOP 1 NUM.[NumValue]
                              FROM [dbo].[CtrlNum] AS NUM
                             WHERE NUM.[Code] = ''CurPeriod'') AS CON
    END

    /* This is very unwieldy but not much choice when converting time values stored as strings into time formats (7.5 -> 07:30)*/
   
    SELECT @FEESISSUEDYTD = FLOOR(SUM(BDG.[FeesBudget]))         
      FROM [dbo].[HandlerBudgets] AS BDG
     WHERE BDG.[Handler] = @handler
       AND BDG.[Month]  <= @PERIOD
       AND BDG.[Year]    = @YEAR



/*FeesYearToDate*/
IF @IsSAM4 = 0
  BEGIN
    SET @NCOMMAND = ''
  SELECT @FeesYTD = ISNULL(SUM(CASE WHEN BH.[INVCR] = ''''I'''' THEN BD.[VALUE] ELSE 0 END - CASE WHEN BH.[INVCR] = ''''C'''' THEN BD.[VALUE] ELSE 0 END), 0)
    FROM dbo.[BatchDetails] BD
         INNER JOIN dbo.[BatchH] BH
                 ON BD.[BATCHNO] = BH.[BATCHNO]
                AND BH.[POSTED] = ''''Y''''
         INNER JOIN [dbo].[KPIParameters] KPI
                 ON KPI.[Year] = BD.[YEAR]
                AND KPI.[Period] >= BD.[PERNO]
   WHERE BD.[FEE] = @handler
     AND BD.[TYPE] = ''''I''''
     AND BD.[OUTLAY] = ''''F''''''
  END
ELSE
  BEGIN
    SET @NCOMMAND = ''
SELECT @FeesYTD = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0)
  FROM [dbo].[Transactions] HED
       INNER JOIN [dbo].[Transactions] TRN
               ON TRN.[BatchNo] = HED.[BatchNo]
              AND TRN.[RecType] = ''''D''''
              AND TRN.[LineType] = ''''F''''
              AND TRN.[XnType] = ''''I''''
              AND TRN.[Posted] = ''''Y''''
              AND (   TRN.[CorrectionInd] = ''''L''''
                   OR TRN.[CorrectionInd] = ''''X'''')
              AND TRN.[FeeEarner] = @handler
       INNER JOIN [dbo].[KPIParameters] KPI
               ON KPI.[Year] = HED.[PostingYear]
              AND KPI.[Period] >= HED.[PostingPeriod]
 WHERE HED.[RecType] = ''''H''''''
  END

EXECUTE sp_executesql @NCOMMAND, N''@handler VARCHAR(10),@FeesYTD DECIMAL(18, 2) OUTPUT'', @handler = @handler, @FeesYTD=@FeesYTD OUTPUT

/*FeesLastYearToDate*/
IF @IsSAM4 = 0
BEGIN
    SET @NCOMMAND = ''SELECT @FeesLastYTD = ISNULL(SUM(CASE WHEN BH.[INVCR] = ''''I'''' THEN BD.[VALUE] ELSE 0 END - CASE WHEN BH.[INVCR] = ''''C'''' THEN BD.[VALUE] ELSE 0 END), 0)
                       FROM dbo.[BatchDetails] BD
                            INNER JOIN dbo.[BatchH] BH
                                    ON BD.[BATCHNO] = BH.[BATCHNO]
                                   AND BH.[POSTED] = ''''Y''''
                            INNER JOIN [dbo].[KPIParameters] KPI
                                    ON KPI.[Year]-1 = BD.[YEAR]
                                   AND KPI.[Period] >= BD.[PERNO]
                      WHERE BD.[FEE] = @handler
                        AND BD.[TYPE] = ''''I''''
                        AND BD.[OUTLAY] = ''''F''''''
END
ELSE
BEGIN
    SET @NCOMMAND = ''SELECT @FeesLastYTD = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0)
                       FROM [dbo].[Transactions] HED
                            INNER JOIN [dbo].[Transactions] TRN
                                    ON TRN.[BatchNo] = HED.[BatchNo]
                                   AND TRN.[RecType] = ''''D''''
                                   AND TRN.[LineType] = ''''F''''
                                   AND TRN.[XnType] = ''''I''''
                                   AND TRN.[Posted] = ''''Y''''
                                   AND TRN.[CorrectionInd] IN (''''L'''', ''''X'''')
                                   AND TRN.[FeeEarner] = @handler
                            INNER JOIN [dbo].[KPIParameters] KPI
                                    ON KPI.[Year]-1 = HED.[PostingYear]
                                   AND KPI.[Period] >= HED.[PostingPeriod]
                      WHERE HED.[RecType] = ''''H''''''
END

EXECUTE sp_executesql @NCOMMAND, N''@handler VARCHAR(10),@FeesLastYTD DECIMAL(18, 2) OUTPUT'', @handler = @handler, @FeesLastYTD=@FeesLastYTD OUTPUT

SELECT (
SELECT REPLACE(CONVERT(VARCHAR, ROUND(ISNULL(@FEESISSUEDYTD, 0), 0), 1), ''.00'', '''') AS [TargetYearToDate],
	   REPLACE(CONVERT(VARCHAR, CAST(ROUND(ISNULL(@FeesYTD, 0), 0) AS MONEY), 1), ''.00'', '''') AS [FeesIssuedYearToDate],
       REPLACE(CONVERT(VARCHAR, CAST(ROUND(ISNULL(@FeesLastYTD, 0), 0) AS MONEY), 1), ''.00'', '''') AS [FeesIssuedLastYearToDate]
	   FOR XML PATH(''Result'')) AS [Result]
'

where templateid=1002
GO

UPDATE UITemplates 
SET TemplateQuery=
'
DECLARE @BitWiseID          BIGINT
DECLARE @GETDATE            DATETIME

SET @fromdate=''18010101''
SET @todate=''21990101'';
SET @GETDATE = GETUTCDATE()
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END

SELECT @BitWiseID = HNG.[BitWiseID]
  FROM HandlerNETGroupMembership HNG
 WHERE HNG.[HANDLER] = CONVERT(VARCHAR(10), @handler)

SET @BitWiseID = ISNULL(@BitWiseID, [dbo].[KAAS_FN_GetOldPermissions](RTRIM(ISNULL(@handler, ''''))))

IF (@handler = ''ADM'')
  BEGIN
    SET @BitWiseID = CONVERT(BIGINT, -1)
  END


/*Today Critical Task*/
SELECT (
SELECT ISNULL(ADT.[Critical Tasks], 0) AS [HeaderCount], ''CriticalTasks'' as HeaderLabel
  FROM (  SELECT SUM(CASE WHEN DEL.[DATE] < @fromdate
                               THEN 0
                          WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR, @todate, 112)))
                               THEN 0
                          WHEN [DIA].[ActionType] = ''C''
                               THEN 1
                          ELSE 0 END) AS [Critical Tasks]
            FROM [dbo].[DiaryDelegations] DEL
                 INNER JOIN [dbo].[Diary] DIA
                            INNER JOIN [dbo].[matters] MAT
                                       INNER JOIN [dbo].[Contacts] CON
                                               ON CON.[CODE] = MAT.[CLIENTCODE]
                                       LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP
                                                    ON MNP.[MATTER] = MAT.[Code]
                                    ON MAT.[CODE] = DIA.[CASECODE]
                                   AND ISNULL(MAT.[Closed], ''N'') <> ''Y''
                         ON DIA.[ACTIONID] = DEL.[ACTIONID]
           WHERE DEL.[HANDLER] = @Handler
             AND DEL.[STATUS] = 0
             AND DEL.[DelType] <> ''Completed''
             AND DEL.[DelType] <> ''Processed''
             AND DEL.[DelType] <> ''Returned''
             AND (   ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0
                  OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT
			FOR XML PATH(''Result'')) AS Result

'
where TemplateId=1006
GO



Update UITemplates set TemplateQuery='
DECLARE @BitWiseID          BIGINT
DECLARE @GETDATE            DATETIME

SET @fromdate=''18010101''
SET @todate=''21990101''
SET @GETDATE = GETUTCDATE()



SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END

SELECT @BitWiseID = HNG.[BitWiseID]
  FROM HandlerNETGroupMembership HNG
 WHERE HNG.[HANDLER] = CONVERT(VARCHAR(10), @handler)

SET @BitWiseID = ISNULL(@BitWiseID, [dbo].[KAAS_FN_GetOldPermissions](RTRIM(ISNULL(@handler, ''''))))

IF (@handler = ''ADM'')
  BEGIN
    SET @BitWiseID = CONVERT(BIGINT, -1)
  END


/*Today Court Dates*/

SELECT (
SELECT ISNULL(ADT.[Court Dates], 0) AS [HeaderCount],''CourtDates'' as HeaderLabel
       
  FROM (  SELECT 
                 SUM(CASE WHEN DEL.[DATE] < @fromdate
                               THEN 0
                          WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR, @todate, 112)))
                               THEN 0
                          WHEN [DIA].[ActionType] = ''O''
                               THEN 1
                          ELSE 0 END) AS [Court Dates]
            FROM [dbo].[DiaryDelegations] DEL
                 INNER JOIN [dbo].[Diary] DIA
                            INNER JOIN [dbo].[matters] MAT
                                       INNER JOIN [dbo].[Contacts] CON
                                               ON CON.[CODE] = MAT.[CLIENTCODE]
                                       LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP
                                                    ON MNP.[MATTER] = MAT.[Code]
                                    ON MAT.[CODE] = DIA.[CASECODE]
                                   AND ISNULL(MAT.[Closed], ''N'') <> ''Y''
                         ON DIA.[ACTIONID] = DEL.[ACTIONID]
           WHERE DEL.[HANDLER] = @Handler
             AND DEL.[STATUS] = 0
             AND DEL.[DelType] <> ''Completed''
             AND DEL.[DelType] <> ''Processed''
             AND DEL.[DelType] <> ''Returned''
             AND (   ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0
                  OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT
				  FOR XML PATH(''Result''))as [Result]


'
where TemplateId=1007
GO



Update UITemplates set TemplateQuery='DECLARE @BitWiseID          BIGINT
DECLARE @GETDATE            DATETIME
SET @Fromdate=''18010101''
SET @todate=''21990101''
SET @GETDATE = GETUTCDATE()
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END
SELECT @BitWiseID = HNG.[BitWiseID]
  FROM HandlerNETGroupMembership HNG
 WHERE HNG.[HANDLER] = CONVERT(VARCHAR(10), @handler)

SET @BitWiseID = ISNULL(@BitWiseID, [dbo].[KAAS_FN_GetOldPermissions](RTRIM(ISNULL(@handler, ''''))))

IF (@handler = ''ADM'')
  BEGIN
    SET @BitWiseID = CONVERT(BIGINT, -1)
  END
  SELECT (  
SELECT ISNULL(ADT.[High Priority], 0) AS Count, ''High Priority''as HeaderLabel
  
  FROM (  SELECT SUM(CASE WHEN DEL.[DATE] < @fromdate
                               THEN 0
                          WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR, @todate, 112)))
                               THEN 0
                          WHEN [DIA].[PRIORITY] = ''H''
                               THEN 1
                          ELSE 0 END) AS [High Priority]
            FROM [dbo].[DiaryDelegations] DEL
                 INNER JOIN [dbo].[Diary] DIA
                            INNER JOIN [dbo].[matters] MAT
                                       INNER JOIN [dbo].[Contacts] CON
                                               ON CON.[CODE] = MAT.[CLIENTCODE]
                                       LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP
                                                    ON MNP.[MATTER] = MAT.[Code]
                                    ON MAT.[CODE] = DIA.[CASECODE]
                                   AND ISNULL(MAT.[Closed], ''N'') <> ''Y''
                         ON DIA.[ACTIONID] = DEL.[ACTIONID]
           WHERE DEL.[HANDLER] = @Handler
             AND DEL.[STATUS] = 0
             AND DEL.[DelType] <> ''Completed''
             AND DEL.[DelType] <> ''Processed''
             AND DEL.[DelType] <> ''Returned''
             AND (   ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0
                  OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT
				  for xml path(''Result'')) as [Result]


'
where TemplateId=1008
GO

UPDATE UITemplates SET TemplateQuery='
DECLARE @BitWiseID          BIGINT  
DECLARE @GETDATE            DATETIME  
SET @Fromdate=''18010101''
SET @todate=''21990101''  
SET @GETDATE = GETUTCDATE()  
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' 
					THEN ''ADM'' 
					ELSE RTRIM(@handler) 
				END  
SELECT @BitWiseID = HNG.[BitWiseID]    
  FROM HandlerNETGroupMembership HNG   
 WHERE HNG.[HANDLER] = CONVERT(VARCHAR(10), @handler)    
 
 SET @BitWiseID = ISNULL(@BitWiseID, [dbo].[KAAS_FN_GetOldPermissions](RTRIM(ISNULL(@handler, ''''))))    IF (@handler = ''ADM'')    BEGIN      SET @BitWiseID = CONVERT(BIGINT, -1)    END    
 
 SELECT (SELECT ISNULL(ADT.[Phone Message], 0) AS Count, ''Phone Message''as HeaderLabel        
							  FROM (  SELECT SUM(CASE WHEN DEL.[DATE] < @fromdate                                 
													  THEN 0                            
													  WHEN DEL.[DATE] >= DATEADD(DD, 1, CONVERT (DATETIME, CONVERT(VARCHAR, @todate, 112)))                                 
													  THEN 0                            
													  WHEN [DIA].[ActionType] = ''T''                                 
													  THEN 1                            
													  ELSE 0 
													END) AS [Phone Message]              
										FROM [dbo].[DiaryDelegations] DEL                   
											INNER JOIN [dbo].[Diary] DIA                              
											INNER JOIN [dbo].[matters] MAT                                         
											INNER JOIN [dbo].[Contacts] CON                                                 
											ON CON.[CODE] = MAT.[CLIENTCODE]                                         
											LEFT OUTER JOIN [dbo].[MatterNetPrivileges] MNP                                                      
											ON MNP.[MATTER] = MAT.[Code]                                      
											ON MAT.[CODE] = DIA.[CASECODE]                                     
											AND ISNULL(MAT.[Closed], ''N'') <> ''Y''                           
											ON DIA.[ACTIONID] = DEL.[ACTIONID]             
										WHERE DEL.[HANDLER] = @Handler               
												AND DEL.[STATUS] = 0               
												AND DEL.[DelType] <> ''Completed''               
												AND DEL.[DelType] <> ''Processed''               
												AND DEL.[DelType] <> ''Returned''               
												AND (   ISNULL(MNP.[Privileges], CONVERT (BIGINT, -1)) & @BitWiseID <> 0                    
												OR ISNULL(MNP.[Privileges], CONVERT (BIGINT, 0)) = 0)) ADT
												for xml path(''Result'')) as [Result]
  

'

WHERE TemplateId=1009
GO

UPDATE UITemplates 
SET TemplateQuery=
'
DECLARE @TotalUndertakings INT
DECLARE @GETDATE DATETIME

SET @GETDATE = GETUTCDATE()
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END

 SELECT @TotalUndertakings = count(1)
  FROM dbo.[Undertakings] UND
 INNER JOIN [dbo].[matters] MAT
    ON MAT.[Code] = UND.[MATTER]
 WHERE UND.[AuthorisedByFE] = @Handler
   AND MAT.[Closed] <> ''Y''
   AND UND.[DISCHARGEDATE] IS NULL


SELECT ( 
SELECT  @TotalUndertakings AS HeaderCount, ''Total Undertakings'' AS HeaderLabel
for xml path(''Result'')) AS [Result]

'
WHERE TemplateId=1010
GO



Update UITemplates 
set TemplateQuery=
'DECLARE @TotalStatuteLimits INT
DECLARE @GETDATE DATETIME

SET @GETDATE = GETUTCDATE()
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END

 
SELECT @TotalStatuteLimits = count(1)
  FROM dbo.[matters] MAT
 WHERE MAT.[FECode] = @Handler
		   AND DATEDIFF(DD, @GETDATE, CASE 
										WHEN ISNULL(MAT.[StatuteLimits], ''21000101'')=''19000101'' 
										THEN ''21000101'' 
										ELSE MAT.[StatuteLimits] 
									 END) <= 60
		   AND MAT.[Closed] <> ''Y''
SELECT ( 
SELECT  @TotalStatuteLimits AS HeaderCount, ''Statute Date Approaching'' as HeaderLabel
FOR XML PATH(''Result'')) AS [Result]
'
where TemplateId=1011
GO









Update UITemplates 
set TemplateQuery=
'DECLARE @GETDATE DATETIME
SET @GETDATE = GETUTCDATE()
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END
DECLARE @Open INT
SELECT @Open = ISNULL(SUM(CASE WHEN MAT.[Closed] = ''Y''
                                    THEN 0
                               WHEN MAT.[ClientCode] = ''ZZZZZZ''
                                    THEN 0
                               WHEN isnumeric(MAT.[matter]) = 0 /*system matters like ZZZZZZ/ZZZ2*/
                                    THEN 0
                               WHEN convert(int, MAT.[matter]) = 0 /*ignore "0" matter*/
                                    THEN 0
                               ELSE 1 END), 0)
  FROM [dbo].[matters] MAT
 WHERE MAT.[FECode] = @Handler
   AND MAT.Closed <> ''Y''


SELECT ( 
SELECT ISNULL(@Open, 0) AS  HeaderCount, ''Open Matters'' as HeaderLabel
FOR XML PATH(''Result'')) AS [Result]

'
where TemplateId=1012
GO



Update UITemplates 
set TemplateQuery=
'DECLARE @GETDATE DATETIME
SET @GETDATE = GETUTCDATE()
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END
DECLARE @Dormant INT
DECLARE @MATTERS TABLE ([Code] VARCHAR(20), [LastActionDate] DATETIME)

INSERT INTO @MATTERS ([Code], [LastActionDate])
EXEC @Dormant = [dbo].[KAAS_GetDormantMatters] @Handler


SELECT ( 
SELECT ISNULL(@Dormant, 0) AS  HeaderCount, ''My dormant Matters'' as HeaderLabel
for xml path(''Result'')) AS [Result]

'
where TemplateId=1013
GO

Update UITemplates 
set TemplateQuery=
'SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END
DECLARE @EffectivelyCompleted INT
SELECT @EffectivelyCompleted=ISNULL(SUM(CASE WHEN isnumeric(MAT.[matter]) = 0 /*system matters like ZZZZZZ/ZZZ2*/
                                                  THEN 0
                                             WHEN convert(int, MAT.[matter]) = 0 /*ignore "0" matter*/
                                                  THEN 0
                                             WHEN ISNULL(MAT.PCODE, '''') = '''' AND ISNULL(PFECODE, '''') = ''''
                                                  THEN 1
                                             ELSE 0 END),0)
  FROM [dbo].[matters] MAT
 WHERE MAT.[FECode] = @Handler
   AND MAT.Closed <> ''Y''

SELECT ( 
SELECT ISNULL(@EffectivelyCompleted, 0) AS  HeaderCount, ''No of Cases Effectively Complete'' as HeaderLabel
for xml path(''Result'')) AS Result
'
where TemplateId=1014
GO



Update UITemplates 
set TemplateQuery=
'DECLARE @GETDATE DATETIME
SET @GETDATE = GETUTCDATE()
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END
DECLARE @Expectinvoice INT
SELECT  @Expectinvoice = ISNULL(SUM(CASE WHEN MAT.[ExpBillD] <= @GETDATE
                                             THEN 1
                                        ELSE 0 END), 0)
  FROM [dbo].[matters] MAT
 WHERE MAT.[FECode] = @Handler
   AND MAT.Closed <> ''Y''

SELECT (
SELECT ISNULL(@Expectinvoice, 0) AS  HeaderCount, ''No. of Cases where expected invoice date has passed'' as HeaderLabel
FOR XML PATH(''Result'')) AS [Result]

'
where TemplateId=1015
GO

Update UITemplates 
set TemplateQuery=
'DECLARE @GETDATE DATETIME
SET @GETDATE = GETUTCDATE()
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END
DECLARE @Approachingestimatedfee INT
DECLARE @EstFeeWarningLimit DECIMAL(19, 2)
SELECT @EstFeeWarningLimit = CONVERT(DECIMAL(19, 2), ISNULL([SET].[KeyValue], ''0''))
  FROM [dbo].[Settings] [SET]
 WHERE [SET].[KeyName] = ''EstFeeWarningLimit''

SET @EstFeeWarningLimit = ISNULL(@EstFeeWarningLimit, 0)
SELECT   @Approachingestimatedfee = ISNULL(SUM(CASE WHEN (COALESCE ((ISNULL(MAT.[ChargeBal], 0) / NULLIF (MAT.[EstFee], 0) * 100), 0)) > @EstFeeWarningLimit
                                                       THEN 1
                                                  ELSE 0 END), 0)
  FROM [dbo].[matters] MAT
 WHERE MAT.[FECode] = @Handler
   AND MAT.Closed <> ''Y''

SELECT ( 
SELECT ISNULL(@Approachingestimatedfee, 0) AS  HeaderCount, ''No. of Cases with approaching estimate fee'' as HeaderLabel
for xml path(''Result'')) AS [Result]

'
where TemplateId=1016
GO

Update UITemplates 
set TemplateQuery=
'DECLARE @GETDATE DATETIME
SET @GETDATE = GETUTCDATE()
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END
DECLARE @Approachingestimatedfee INT
DECLARE @EstFeeWarningLimit DECIMAL(19, 2)
SELECT @EstFeeWarningLimit = CONVERT(DECIMAL(19, 2), ISNULL([SET].[KeyValue], ''0''))
  FROM [dbo].[Settings] [SET]
 WHERE [SET].[KeyName] = ''EstFeeWarningLimit''

SET @EstFeeWarningLimit = ISNULL(@EstFeeWarningLimit, 0)
SELECT   @Approachingestimatedfee = ISNULL(SUM(CASE WHEN (COALESCE ((ISNULL(MAT.[ChargeBal], 0) / NULLIF (MAT.[EstFee], 0) * 100), 0)) > @EstFeeWarningLimit
                                                       THEN 1
                                                  ELSE 0 END), 0)
  FROM [dbo].[matters] MAT
 WHERE MAT.[FECode] = @Handler
   AND MAT.Closed <> ''Y''

SELECT ( 
SELECT ISNULL(@Approachingestimatedfee, 0) AS  HeaderCount, ''No. of Cases with approaching estimate fee'' as HeaderLabel
FOR XML PATH(''Result'')) AS [Result]

'
where TemplateId=1017
GO

Update UITemplates 
set TemplateQuery=
'DECLARE @GETDATE DATETIME
SET @GETDATE = GETUTCDATE()
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END
DECLARE @MoneyLaunderingchecks INT
SELECT @MoneyLaunderingchecks = COUNT(DISTINCT CON.[CODE])
  FROM [dbo].[Contacts] CON
       INNER JOIN [dbo].[matters] MAT
               ON MAT.[ClientCode] = CON.[Code]
              AND MAT.[Closed] <> ''Y''
 WHERE CON.[FE] = @Handler
   AND ISNULL(CON.[Approved], 0) <> 1

SELECT ( 
SELECT ISNULL(@MoneyLaunderingchecks, 0) AS  HeaderCount, ''No. of Clients that require money laundering checks'' as HeaderLabel
FOR XML PATH(''Result'')) AS Result
'
where TemplateId=1018
GO

Update UITemplates 
set TemplateQuery=
'
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END
DECLARE @Noestimategiven INT
SELECT @Noestimategiven = ISNULL(SUM(CASE WHEN isnumeric(MAT.[matter]) = 0 /*system matters like ZZZZZZ/ZZZ2*/
                                               THEN 0
                                          WHEN convert(int, MAT.[matter]) = 0 /*ignore "0" matter*/
                                               THEN 0
                                          WHEN MAT.[Section68] = ''Y''
                                               THEN 0
                                          ELSE 1 END), 0)
										    FROM [dbo].[matters] MAT
 WHERE MAT.[FECode] = @Handler
   AND MAT.Closed <> ''Y''

SELECT ( 
SELECT ISNULL(@Noestimategiven, 0) AS  HeaderCount, ''No. of Cases with no estimate given'' as HeaderLabel
FOR XML PATH(''Result'') ) AS Result

'
where TemplateId=1019
GO

Update UITemplates 
set TemplateQuery=
'DECLARE @GETDATE DATETIME
SET @GETDATE = GETUTCDATE()
SET @handler = CASE WHEN RTRIM(ISNULL(@handler, '''')) = '''' THEN ''ADM'' ELSE RTRIM(@handler) END
DECLARE @Noestimatefee INT
SELECT  @Noestimatefee = ISNULL(SUM(CASE WHEN isnumeric(MAT.[matter]) = 0 /*system matters like ZZZZZZ/ZZZ2*/
                                             THEN 0
                                        WHEN convert(int, MAT.[matter]) = 0 /*ignore "0" matter*/
                                             THEN 0
                                        WHEN MAT.[EstFee] = 0
                                             THEN 1
                                        ELSE 0 END), 0)
  FROM [dbo].[matters] MAT
 WHERE MAT.[FECode] = @Handler
   AND MAT.Closed <> ''Y''

SELECT ( 
SELECT ISNULL(@Noestimatefee, 0) AS  HeaderCount, ''No. of Cases with no estimate fee'' as HeaderLabel
FOR XML PATH(''Result'')) AS Result
	
'
where TemplateId=1020
GO

UPDATE UITemplates 
SET TemplateQuery=
'DECLARE @NCOMMAND nvarchar(MAX)    
DECLARE @DraftBillValue DECIMAL(18, 2)     
DECLARE @IsSAM4 bit            
SET @IsSAM4 = [dbo].[ISSAM4]()           
IF @IsSAM4 = 0      
  BEGIN      
    SET @NCOMMAND = N''      
	SELECT @DraftBillValue = ISNULL(SUM(ISNULL(BD.[NET], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VATVALUE], 0)), 0)      
	  FROM [dbo].[BillHeader] HED      
         INNER JOIN [dbo].[BillDetails] BD      
                 ON BD.[DRAFTNO] = HED.[DRAFTNO]      
	 WHERE HED.[MATTER] = @CaseCode      
      AND HED.[BILLNO] = 0''      
  END      
ELSE      
  BEGIN      
    SET @NCOMMAND = N''      
	SELECT @DraftBillValue = ISNULL(SUM(ISNULL(BD.[NetValue], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VatValue], 0)), 0)      
    FROM [dbo].[BillHead] HED      
         INNER JOIN [dbo].[BillDetail] BD      
                 ON BD.[BillID] = HED.[BillingID]      
	WHERE HED.[MatterCode] = @CaseCode      
    AND HED.[BillStatus] <> ''''Approved''''      
    AND ISNULL(HED.[ProForma], '''''''') = ''''Y'''' ''      
  END      
EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20),@DraftBillValue DECIMAL(18, 2) OUTPUT'', @CaseCode = @CaseCode, @DraftBillValue=@DraftBillValue OUTPUT    
      
DECLARE @DebtBal        DECIMAL(19, 2)   
DECLARE @DebtBalDate    DATETIME          
  
SET @DebtBal = 0    
IF @IsSAM4 = 1      
  BEGIN      
    SET @NCOMMAND = N''      
				SELECT @DebtBal = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[DebtorsValue], 0)), 0)) ,      
					   @DebtBalDate = MAX(CASE WHEN ISNULL(TRN.[DebtorsValue], 0) = 0 THEN NULL ELSE TRN.[TransDate] END)    
					FROM [dbo].[Transactions] TRN      
					WHERE TRN.[MatterCode] = @CaseCode      
					AND TRN.[Posted] = ''''Y''''      
					AND TRN.[RecType] <> ''''V''''      
					AND TRN.[CorrectionInd] = ''''L'''' ''      
EXECUTE sp_executesql @NCOMMAND,      
                      N''@CaseCode VARCHAR(20),      
                         @DebtBal DECIMAL(19, 2) OUTPUT,
			 @DebtBalDate DATETIME OUTPUT'',      
                         @CaseCode      = @CaseCode,      
                         @DebtBal       = @DebtBal OUTPUT,
			 @DebtBalDate   = @DebtBalDate OUTPUT     
  END      
      
/*Result Set 0      
Matter Data  */
SELECT * INTO #Table1 FROM( 
SELECT CONVERT(DECIMAL(19, 2), CASE WHEN @DebtBal <> 0      
                                         THEN @DebtBal      
                                    ELSE ISNULL(MAT.[DebtBal], 0) END)  AS [AccountSummaryValue],  ''Debtors A/c'' AS [AccountSummaryLabel]  
  FROM dbo.[matters] MAT      
		WHERE MAT.[Code] = @CaseCode      
      ) AS TBL

/*-Result Set 8      
Date for Debtor Ledger  */  
CREATE TABLE #Table2([AccountSummaryProgress] VARCHAR(200),[LastDebtLedgerDate] VARCHAR(200))  
IF @IsSAM4 = 0      
BEGIN    
INSERT INTO #Table2
SELECT [dbo].[KAAS_FN_DateDiffVerbose](LDL.[LastDebtLedgerDate],      
                                       LDL.[TODAY]) AS [AccountSummaryProgress],      
       CASE WHEN DATEDIFF(DAY, LDL.[LastDebtLedgerDate], LDL.[TODAY]) > 0      
                 THEN DATEDIFF(DAY, LDL.[LastDebtLedgerDate], LDL.[TODAY])      
            ELSE 0 END AS [DebtLedgerOSDays]      
  FROM (  SELECT GetDate() AS [TODAY],      
                 MAX(DTL.[DATE]) AS [LastDebtLedgerDate]      
            FROM [dbo].[DebtorsLedger] DTL      
           WHERE DTL.[MATTER] = @CaseCode) LDL  
  END      
ELSE      
  BEGIN      
  INSERT INTO #Table2
SELECT [dbo].[KAAS_FN_DateDiffVerbose](LDL.[LastDebtLedgerDate],      
                                       LDL.[TODAY]) AS [AccountSummaryProgress],      
       CASE WHEN DATEDIFF(DAY, LDL.[LastDebtLedgerDate], LDL.[TODAY]) > 0      
                 THEN DATEDIFF(DAY, LDL.[LastDebtLedgerDate], LDL.[TODAY])      
            ELSE 0 END AS  [DebtLedgerOSDays]      
  FROM (  SELECT GetDate() AS [TODAY],      
                 @DebtBalDate AS [LastDebtLedgerDate]) LDL 
    
  END      
      SELECT (
	  SELECT * FROM #Table1,#Table2
	  FOR XML PATH(''Result'')) AS [Result]

	  DROP TABLE #Table1,#Table2
' 
WHERE TemplateId=1021
GO

UPDATE UITemplates
SET TemplateQuery=
'DECLARE @NCOMMAND nvarchar(MAX)      
DECLARE @DraftBillValue DECIMAL(18, 2)      
DECLARE @IsSAM4 bit            
SET @IsSAM4 = [dbo].[ISSAM4]()            
IF @IsSAM4 = 0      
  BEGIN      
    SET @NCOMMAND = N''      
	SELECT @DraftBillValue = ISNULL(SUM(ISNULL(BD.[NET], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VATVALUE], 0)), 0)      
	  FROM [dbo].[BillHeader] HED      
         INNER JOIN [dbo].[BillDetails] BD      
                 ON BD.[DRAFTNO] = HED.[DRAFTNO]      
	 WHERE HED.[MATTER] = @CaseCode      
      AND HED.[BILLNO] = 0''     
  END      
ELSE      
  BEGIN      
    SET @NCOMMAND = N''      
	SELECT @DraftBillValue = ISNULL(SUM(ISNULL(BD.[NetValue], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VatValue], 0)), 0)      
    FROM [dbo].[BillHead] HED      
         INNER JOIN [dbo].[BillDetail] BD      
                 ON BD.[BillID] = HED.[BillingID]      
	WHERE HED.[MatterCode] = @CaseCode      
    AND HED.[BillStatus] <> ''''Approved''''      
    AND ISNULL(HED.[ProForma], '''''''') = ''''Y''''''      
  END      
EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20),@DraftBillValue DECIMAL(18, 2) OUTPUT'', @CaseCode = @CaseCode, @DraftBillValue=@DraftBillValue OUTPUT      

DECLARE @OutlayBal      DECIMAL(19, 2)      
DECLARE @OutlayBalDate  DATETIME      

SET @OutlayBal = 0  
IF @IsSAM4 = 1      
  BEGIN      
    SET @NCOMMAND = N''      
				SELECT  @OutlayBal = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[OutlayValue], 0)), 0)),      
						@OutlayBalDate = MAX(CASE WHEN ISNULL(TRN.[OutlayValue], 0) = 0 THEN NULL ELSE TRN.[TransDate] END)    
					FROM [dbo].[Transactions] TRN      
					WHERE TRN.[MatterCode] = @CaseCode      
					AND TRN.[Posted] = ''''Y''''      
					AND TRN.[RecType] <> ''''V''''      
					AND TRN.[CorrectionInd] = ''''L''''''     
EXECUTE sp_executesql @NCOMMAND,      
                      N''@CaseCode VARCHAR(20),     
                        @OutlayBal DECIMAL(19, 2) OUTPUT,      
                        @OutlayBalDate DATETIME OUTPUT'',      
                      @CaseCode      = @CaseCode,                                 
                      @OutlayBal     = @OutlayBal OUTPUT,      
                      @OutlayBalDate = @OutlayBalDate OUTPUT    
  END      
      
/*Result Set 0      
Matter Data */
SELECT * INTO #Table1 FROM( 
SELECT CONVERT(DECIMAL(19, 2), CASE WHEN @OutlayBal <> 0      
                                         THEN @OutlayBal      
                                    ELSE ISNULL(MAT.[OutlayBal], 0) END)  AS [AccountSummaryValue],''Outlay A/C'' AS [AccountSummaryLabel]    
  FROM dbo.[matters] MAT      
		WHERE MAT.[Code] = @CaseCode      
   ) AS TBL
      
/*Result Set 9      
Date for Matter Ledger (Outstanding)    */
CREATE TABLE #Table2([AccountSummaryProgress] VARCHAR(200),[LastMatLedgerDate] VARCHAR(200))  
IF @IsSAM4 = 0      
  BEGIN      
INSERT INTO #Table2
SELECT [dbo].[KAAS_FN_DateDiffVerbose](LDL.[LastMatLedgerDate],      
                                       LDL.[TODAY]) AS [AccountSummaryProgress],      
       CASE WHEN DATEDIFF(DAY, LDL.[LastMatLedgerDate], LDL.[TODAY]) > 0      
                 THEN DATEDIFF(DAY, LDL.[LastMatLedgerDate], LDL.[TODAY])      
            ELSE 0 END AS [MatLedgerOSDays]      
  FROM (  SELECT GetDate() AS [TODAY],      
                 MAX(MAL.[DATE]) AS [LastMatLedgerDate]      
            FROM [dbo].[MatterLedger] MAL     
           WHERE MAL.[MATTER] = @CaseCode      
             AND ISNULL(MAL.[VALUEO], 0) <> 0) LDL      
      
  END      
ELSE      
  BEGIN      
    INSERT INTO #Table2   
SELECT [dbo].[KAAS_FN_DateDiffVerbose](LDL.[LastMatLedgerDate],      
                                       LDL.[TODAY]) AS [MatLedgerOSVerbose],      
       CASE WHEN DATEDIFF(DAY, LDL.[LastMatLedgerDate], LDL.[TODAY]) > 0      
                 THEN DATEDIFF(DAY, LDL.[LastMatLedgerDate], LDL.[TODAY])      
            ELSE 0 END AS [MatLedgerOSDays]      
  FROM (  SELECT GetDate() AS [TODAY],      
                 @OutlayBalDate AS [LastMatLedgerDate]) LDL      
          
  END 
  SELECT (
  SELECT * FROM #Table1,#Table2
  FOR XML PATH(''Result'')) AS [Result]
	  DROP TABLE #Table1,#Table2
  '
  WHERE TemplateId=1022
  GO

UPDATE UITemplates
SET TemplateQuery=
'DECLARE @NCOMMAND nvarchar(MAX)      
DECLARE @IsSAM4 bit            
SET @IsSAM4 = [dbo].[ISSAM4]()        
DECLARE @OutlayBal      DECIMAL(19, 2)         
SET @OutlayBal = 0      
IF @IsSAM4 = 1      
  BEGIN      
    SET @NCOMMAND = N''      
				SELECT @OutlayBal = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[OutlayValue], 0)), 0))
					FROM [dbo].[Transactions] TRN      
					WHERE TRN.[MatterCode] = @CaseCode      
					AND TRN.[Posted] = ''''Y''''      
					AND TRN.[RecType] <> ''''V''''      
					AND TRN.[CorrectionInd] = ''''L''''''      
EXECUTE sp_executesql @NCOMMAND,      
                      N''@CaseCode VARCHAR(20), 
                        @OutlayBal DECIMAL(19, 2) OUTPUT'',      
                      @CaseCode      = @CaseCode,                                  
                      @OutlayBal     = @OutlayBal OUTPUT     
  END      


SELECT (
SELECT 
	MAT.[OutlayBud]     AS [OutlayBud], 
	''Outlay Budget''AS [AccountSummaryLabel] ,
	CASE WHEN MAT.[OutlayBud] = 0
	 	 THEN NULL
	ELSE			
		CONVERT(DECIMAL (19,2),(CONVERT(DECIMAL(19, 2), CASE WHEN @OutlayBal <> 0      
																THEN @OutlayBal      
															 ELSE ISNULL(MAT.[OutlayBal], 0) END) *100)/MAT.[OutlayBud]  )  
														END         AS [AccountSummaryProgress]  ,
	   CONVERT(DECIMAL(19, 2), CASE WHEN @OutlayBal <> 0
                                         THEN @OutlayBal
                                    ELSE ISNULL(MAT.[OutlayBal], 0) END)              AS [OutlayBal]
FROM 
	dbo.[matters] MAT      
WHERE 
	MAT.[Code] = @CaseCode    
FOR XML PATH(''Result'') ) as [Result]
	
	  '
WHERE TemplateId=1023
GO

UPDATE UITemplates
SET TemplateQuery=
'DECLARE @NCOMMAND nvarchar(MAX)      
DECLARE @DraftBillValue DECIMAL(18, 2)    
DECLARE @OutstandingFee DECIMAL(18, 2)
  
DECLARE @IsSAM4 bit            
SET @IsSAM4 = [dbo].[ISSAM4]()      
/* Result Set 11      
Current outstanding fees */
IF @IsSAM4 = 0      
  BEGIN      
    SET @NCOMMAND = N''      
SELECT @OutstandingFee = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(DTL.[OSFees], 0)), 0))
  FROM [dbo].[DebtorsLedger] DTL      
 WHERE DTL.[matter] = @CaseCode''      
  END      
ELSE      
  BEGIN      
    SET @NCOMMAND = N''      
SELECT @OutstandingFee = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[FeesOS], 0)), 0))     
  FROM [dbo].[Transactions] TRN      
 WHERE TRN.[MatterCode] = @CaseCode      
   AND TRN.[Posted] = ''''Y''''      
   AND TRN.[RecType] <> ''''V''''      
   AND TRN.[CorrectionInd] = ''''L''''''      
  END      
      
EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @OutstandingFee DECIMAL(18,2) OUTPUT'', @CaseCode = @CaseCode, @OutstandingFee = @OutstandingFee OUTPUT

SELECT (
SELECT ''Current Outstanding Fees'' AS [AccountSummaryLabel] , @OutstandingFee as OutstandingFee
FOR XML PATH(''Result'')) as [Result]

'


WHERE TemplateId=1024
GO

UPDATE UITemplates
SET TemplateQuery=
'DECLARE @NCOMMAND nvarchar(MAX)      
DECLARE @DraftBillValue DECIMAL(18, 2)      
DECLARE @IsSAM4 bit            
SET @IsSAM4 = [dbo].[ISSAM4]()      
      
   
DECLARE @ClientCurrent  DECIMAL(19, 2)      
DECLARE @ClientDeposit  DECIMAL(19, 2)      
SET @ClientCurrent = 0      
SET @ClientDeposit = 0      
IF @IsSAM4 = 1      
  BEGIN      
    SET @NCOMMAND = N''      
				SELECT @ClientCurrent = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[ClientCurrent], 0)), 0)),      
						@ClientDeposit = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[ClientDeposit], 0)), 0))      
					FROM [dbo].[Transactions] TRN      
					WHERE TRN.[MatterCode] = @CaseCode      
					AND TRN.[Posted] = ''''Y''''      
					AND TRN.[RecType] <> ''''V''''      
					AND TRN.[CorrectionInd] = ''''L''''''      
EXECUTE sp_executesql @NCOMMAND,      
                      N''@CaseCode VARCHAR(20),  
                        @ClientCurrent DECIMAL(19, 2) OUTPUT,      
                        @ClientDeposit DECIMAL(19, 2) OUTPUT'',      
                      @CaseCode      = @CaseCode,                               
                      @ClientCurrent = @ClientCurrent OUTPUT,      
                      @ClientDeposit = @ClientDeposit OUTPUT      
  END      

SELECT (  
	SELECT 
		CONVERT(DECIMAL(19, 2), CASE WHEN @ClientCurrent <> 0      
											 THEN @ClientCurrent + @ClientDeposit      
									 WHEN @ClientDeposit <> 0      
											 THEN @ClientCurrent + @ClientDeposit      
									 ELSE ISNULL(MAT.[ClientBal], 0) END)              AS [AccountSummaryValue],
		 ''Client A/C'' AS [AccountSummaryLabel]    
	FROM 
		dbo.[matters] MAT      
	WHERE 
		MAT.[Code] = @CaseCode  
	FOR XML PATH(''Result'')) AS [Result]
		
'
WHERE TemplateId=1025
GO

UPDATE UITemplates
SET TemplateQuery=
'DECLARE @NCOMMAND nvarchar(MAX)      
DECLARE @DraftBillValue DECIMAL(18, 2)      
DECLARE @IsSAM4 bit            
SET @IsSAM4 = [dbo].[ISSAM4]()     
DECLARE @ClientCurrent  DECIMAL(19, 2)      
SET @ClientCurrent = 0      
IF @IsSAM4 = 1      
  BEGIN      
    SET @NCOMMAND = N''      
				SELECT @ClientCurrent = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[ClientCurrent], 0)), 0))      
					FROM [dbo].[Transactions] TRN      
					WHERE TRN.[MatterCode] = @CaseCode      
					AND TRN.[Posted] = ''''Y''''      
					AND TRN.[RecType] <> ''''V''''      
					AND TRN.[CorrectionInd] = ''''L''''''      
EXECUTE sp_executesql @NCOMMAND,      
                      N''@CaseCode VARCHAR(20),  
                        @ClientCurrent DECIMAL(19, 2) OUTPUT'',      
                      @CaseCode      = @CaseCode,                               
                      @ClientCurrent = @ClientCurrent OUTPUT
                           
  END      

SELECT (
SELECT 
	CONVERT(DECIMAL(19, 2), CASE WHEN @ClientCurrent <> 0      
                                         THEN @ClientCurrent      
                                 ELSE ISNULL(MAT.[ClientCurBal], 0) END)           AS [AccountSummaryValue],
	''Client Current'' AS [AccountSummaryLabel]    
FROM 
	dbo.[matters] MAT
WHERE 
	MAT.[Code] = @CaseCode      
FOR XML PATH(''Result'')) AS [Result]
      
  '
  WHERE TemplateId=1026
  GO

UPDATE UITemplates
SET TemplateQuery=
'DECLARE @NCOMMAND nvarchar(MAX)      
DECLARE @DraftBillValue DECIMAL(18, 2)      
DECLARE @IsSAM4 bit            
SET @IsSAM4 = [dbo].[ISSAM4]()  
DECLARE @ClientDeposit  DECIMAL(19, 2)      
SET @ClientDeposit = 0      
IF @IsSAM4 = 1      
  BEGIN      
    SET @NCOMMAND = N''      
				SELECT @ClientDeposit = CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[ClientDeposit], 0)), 0))      
					FROM [dbo].[Transactions] TRN      
					WHERE TRN.[MatterCode] = @CaseCode      
					AND TRN.[Posted] = ''''Y''''      
					AND TRN.[RecType] <> ''''V''''      
					AND TRN.[CorrectionInd] = ''''L''''''      
EXECUTE sp_executesql @NCOMMAND,      
                      N''@CaseCode VARCHAR(20),      
                        @ClientDeposit DECIMAL(19, 2) OUTPUT'',      
                      @CaseCode      = @CaseCode,                                                         
                      @ClientDeposit = @ClientDeposit OUTPUT      
  END      
      
/*Result Set 0      
Matter Data*/   
select (   
SELECT  CONVERT(DECIMAL(19, 2), CASE WHEN @ClientDeposit <> 0      
                                         THEN @ClientDeposit      
                                    ELSE ISNULL(MAT.[ClientDepBal], 0) END)           AS [AccountSummaryValue], 
									''Client Deposit'' AS [AccountSummaryLabel]    
  FROM dbo.[matters] MAT      
		WHERE MAT.[Code] = @CaseCode            
		FOR XML PATH(''Result'')) as [Result]
      
  '
  WHERE TemplateId=1027
  GO

UPDATE UITemplates
SET TemplateQuery=
'DECLARE @NCOMMAND nvarchar(MAX)      
DECLARE @DraftBillValue DECIMAL(18, 2)      
DECLARE @IsSAM4 bit 
SET @IsSAM4 = [dbo].[ISSAM4]()           
DECLARE @ChargeBal    DECIMAL(19, 2)
SELECT @ChargeBal = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N''     
                                         THEN 0      
       WHEN @IsSAM4 = 1      
                                     AND ISNULL(TIE.[InvoiceNo], 0) <> 0      
                                         THEN 0      
                                    WHEN @IsSAM4 = 1      
                                         THEN ISNULL(TIE.[Charge], 0)      
                                    ELSE ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0) END), 0)     
  FROM [dbo].[TimeEntry] TIE      
 WHERE TIE.[Matter] = @CaseCode      
		AND (   (TIE.[TimeOrCharge] = ''T'')      
        OR (TIE.[TimeOrCharge] = ''C''))  
      
SELECT (
SELECT @ChargeBal											 AS [AccountSummaryValue], 
	   ''Total Work In Progress''							 AS [AccountSummaryLabel] ,
	   CASE WHEN MAT.[EstFee]=0
				THEN NULL
			ELSE
				CONVERT(DECIMAL(10,2),(@ChargeBal*100)/MAT.[EstFee])  
		END										AS [AccountSummaryProgress],
		MAT.[EstFee]							AS [EstimateFee]
  FROM dbo.[matters] MAT      
		WHERE MAT.[Code] = @CaseCode
		FOR XML PATH(''Result'')) as [Result]
'
WHERE TemplateId=1028
GO

UPDATE UITemplates
SET TemplateQuery=
'/*Write off time*/      
SELECT (  SELECT SUM(CONVERT(DECIMAL(18, 2), ISNULL(TIE.[Charge], 0))) AS [AccountSummaryValue]      ,
  ''Write Off Time'' AS [AccountSummaryLabel]  
            FROM [dbo].[matters] MAT      
                 INNER JOIN [dbo].[TimeEntry] TIE      
                         ON TIE.[MATTER] = MAT.[Code]      
           WHERE TIE.[MATTER] = @CaseCode      
				 AND (   (TIE.[TimeOrCharge] = ''T'')      
                 OR (TIE.[TimeOrCharge] = ''C''))      
				 AND TIE.[Task] = ''WRI''
				 for xml path(''Result'')) as Result
'
WHERE TemplateId=1029
GO

UPDATE UITemplates
SET TemplateQuery=
'DECLARE @NCOMMAND nvarchar(MAX)      
DECLARE @IsSAM4 bit  
SET @IsSAM4 = [dbo].[ISSAM4]() 
DECLARE @EstimateFee DECIMAL(18,2) 
DECLARE @TotalFeesBilled DECIMAL(18,2)
DECLARE @AccountSummaryProgress DECIMAL(18,2)

SET @EstimateFee=(

SELECT MAT.[EstFee]
 FROM dbo.[matters] MAT      
		WHERE MAT.[Code] = @CaseCode)

   
/*Result Set 10      
 The fees issued to date   */   
    IF @IsSAM4 = 0      
      BEGIN      
        SET @NCOMMAND = N''      
		SELECT @TotalFeesBilled = (ISNULL(SUM((CASE WHEN HED.[INVCR] = ''''I''''      
                             THEN TRN.[VALUE]      
                        ELSE TRN.[VALUE] * - 1 END)), 0) ),
		@AccountSummaryProgress = (
		CASE WHEN @EstimateFee = 0
				THEN NULL
			ELSE
				 CONVERT(DECIMAL (19,2),(ISNULL(SUM((CASE WHEN HED.[INVCR] = ''''I''''      
                             THEN TRN.[VALUE]      
                        ELSE TRN.[VALUE] * - 1 END)), 0)*100)/	@EstimateFee) 
		END 	)      
  FROM [dbo].[BatchH] HED      
       INNER JOIN [dbo].[BatchDetails] TRN      
               ON TRN.[BATCHNO] = HED.[BATCHNO]      
 WHERE HED.[MATTER] = @CaseCode      
   AND HED.[POSTED] = ''''Y''''      
   AND TRN.[TYPE] = ''''I''''      
   AND TRN.[OUTLAY] = ''''F''''''      
      END      
    ELSE      
      BEGIN      
        SET @NCOMMAND = N''      
DECLARE @TotalFeesBilled DECIMAL(22, 2)      
      
SELECT @TotalFeesBilled = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0)      
  FROM [dbo].[TransactionsHeaders] HED      
       INNER JOIN [dbo].[Transactions] TRN      
               ON TRN.[BatchNo] = HED.[BatchNo]      
              AND TRN.[RecType] = ''''D''''      
              AND TRN.[LineType] = ''''F''''      
              AND TRN.[XnType] = ''''I''''      
              AND TRN.[Posted] = ''''Y''''      
              AND (   TRN.[CorrectionInd] = ''''L''''      
                   OR TRN.[CorrectionInd] = ''''X'''')      
 WHERE HED.[MatterCode] = @CaseCode      
      
/*values imported from Axxia*/      
SELECT @TotalFeesBilled = @TotalFeesBilled + ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0)      
  FROM [dbo].[Transactions] TRN      
       LEFT OUTER JOIN [dbo].[TransactionsHeaders] HED      
                    ON HED.[BatchNo] = TRN.[BatchNo]      
 WHERE TRN.[MatterCode] = @CaseCode      
   AND TRN.[RecType] = ''''D''''      
   AND TRN.[LineType] = ''''F''''      
   AND TRN.[XnType] = ''''I''''      
   AND TRN.[Posted] = ''''Y''''      
   AND (   TRN.[CorrectionInd] = ''''L''''      
        OR TRN.[CorrectionInd] = ''''X'''')      
   AND HED.[BatchNo] IS NULL      
      
SELECT  

	    @AccountSummaryProgress = CONVERT(DECIMAL (19,2),(@TotalFeesBilled*100)/@EstimateFee) 
	     '' 
     
      END      
      
EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20) , @EstimateFee INT, @TotalFeesBilled DECIMAL(18,2) OUTPUT, @AccountSummaryProgress DECIMAL(18,2) OUTPUT'', @CaseCode = @CaseCode ,@EstimateFee=@EstimateFee, @TotalFeesBilled = @TotalFeesBilled OUTPUT, 	@AccountSummaryProgress   = @AccountSummaryProgress OUTPUT  

SELECT (
SELECT 	  
	''Fees Issued To Date'' AS [AccountSummaryLabel],
	@EstimateFee as [EstimateFee],
	@TotalFeesBilled as TotalFeesBilled, 
	@AccountSummaryProgress as AccountSummaryProgress
	FOR XML PATH(''Result'')) as [Result]

'
WHERE TemplateId=1030
GO

UPDATE UITemplates
SET TemplateQuery=
'
DECLARE @NCOMMAND nvarchar(MAX) 
DECLARE @IsSAM4 bit              
SET @IsSAM4 = [dbo].[ISSAM4]()  
declare @chargeBal decimal(18,2)
declare @FeesIssued decimal(18,2)
declare @EstimateFee decimal(18,2)
select @EstimateFee= ISNULL(EstFee,0) from matters where code =  @CaseCode


 select  @chargeBal = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N''      
                                         THEN 0      
       WHEN @IsSAM4 = 1      
                                     AND ISNULL(TIE.[InvoiceNo], 0) <> 0      
                                         THEN 0      
                                    WHEN @IsSAM4 = 1      
                                         THEN ISNULL(TIE.[Charge], 0)      
                                    ELSE ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0) END), 0)

									from TimeEntry TIE
									where Matter = @CaseCode
									AND (   (TIE.[TimeOrCharge] = ''T'')      
        OR (TIE.[TimeOrCharge] = ''C''))  



  IF @IsSAM4 = 0
      BEGIN
        SET @NCOMMAND = N''
		SELECT @FeesIssued = ISNULL(SUM((CASE WHEN HED.[INVCR] = ''''I''''
									THEN TRN.[VALUE]
							ELSE TRN.[VALUE] * - 1 END)), 0) 
		FROM [dbo].[BatchH] HED
			INNER JOIN [dbo].[BatchDetails] TRN
					ON TRN.[BATCHNO] = HED.[BATCHNO]
		WHERE HED.[MATTER] = @CaseCode
		AND HED.[POSTED] = ''''Y''''
		AND TRN.[TYPE] = ''''I''''
		AND TRN.[OUTLAY] = ''''F''''
  ''
      END
    ELSE
      BEGIN
        SET @NCOMMAND = N''
DECLARE @TotalFeesBilled DECIMAL(22, 2)

SELECT @TotalFeesBilled = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0)
  FROM [dbo].[TransactionsHeaders] HED
       INNER JOIN [dbo].[Transactions] TRN
               ON TRN.[BatchNo] = HED.[BatchNo]
              AND TRN.[RecType] = ''''D''''
              AND TRN.[LineType] = ''''F''''
              AND TRN.[XnType] = ''''I''''
              AND TRN.[Posted] = ''''Y''''
              AND (   TRN.[CorrectionInd] = ''''L''''
                   OR TRN.[CorrectionInd] = ''''X'''')
 WHERE HED.[MatterCode] = @CaseCode

--values imported from Axxia
SELECT @FeesIssued = @TotalFeesBilled + ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0)
  FROM [dbo].[Transactions] TRN
       LEFT OUTER JOIN [dbo].[TransactionsHeaders] HED
                    ON HED.[BatchNo] = TRN.[BatchNo]
 WHERE TRN.[MatterCode] = @CaseCode
   AND TRN.[RecType] = ''''D''''
   AND TRN.[LineType] = ''''F''''
   AND TRN.[XnType] = ''''I''''
   AND TRN.[Posted] = ''''Y''''
   AND (   TRN.[CorrectionInd] = ''''L''''
        OR TRN.[CorrectionInd] = ''''X'''')
   AND HED.[BatchNo] IS NULL ''


      END

EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @chargeBal DECIMAL(18,2) OUTPUT, @FeesIssued DECIMAL(18,2) OUTPUT, @EstimateFee DECIMAL(18,2) OUTPUT'', @CaseCode = @CaseCode, @chargeBal = @chargeBal OUTPUT, @FeesIssued = @FeesIssued OUTPUT, @EstimateFee = @EstimateFee OUTPUT
SELECT (
SELECT ''Fees To Date + WIP'' AS WidgetName, @FeesIssued AS [FeesIssued], @chargeBal AS [ChargeBalance] , @EstimateFee as [EstimateFee]
FOR XML PATH(''Result'')) AS [Result]
'
WHERE TemplateId=1031
GO

UPDATE UITemplates
SET TemplateQuery=
'
DECLARE @NCOMMAND nvarchar(MAX) 
DECLARE @IsSAM4 bit              
SET @IsSAM4 = [dbo].[ISSAM4]()  
DECLARE @ChargeBal decimal(18,2)
DECLARE @EstimateFee decimal(18,2)
DECLARE @FeesIssued decimal(18,2)
DECLARE @Section68 VARCHAR(1)


SELECT  @EstimateFee = Mat.EstFee, @Section68 = Mat.Section68
            FROM [dbo].[matters] MAT
           WHERE MAT.Code = @CaseCode


 select  @ChargeBal = ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N''
                                         THEN 0
                                    WHEN @IsSAM4 = 1
                                     AND ISNULL(TIE.[InvoiceNo], 0) <> 0
                                         THEN 0
                                    WHEN @IsSAM4 = 1
                                         THEN ISNULL(TIE.[Charge], 0)
                                    ELSE ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0) END), 0)
									from TimeEntry TIE
									WHERE 
									TIE.[Matter] = @CaseCode
   AND (   (TIE.[TimeOrCharge] = ''T'')
        OR (TIE.[TimeOrCharge] = ''C''))


		 
  IF @IsSAM4 = 0
      BEGIN
		SET @NCOMMAND = N''
		SELECT @FeesIssued = ISNULL(SUM((CASE WHEN HED.[INVCR] = ''''I''''
			THEN TRN.[VALUE]
		ELSE TRN.[VALUE] * - 1 END)), 0) 
			FROM [dbo].[BatchH] HED
		INNER JOIN [dbo].[BatchDetails] TRN
		ON TRN.[BATCHNO] = HED.[BATCHNO]
		WHERE HED.[MATTER] = @CaseCode
			AND HED.[POSTED] = ''''Y''''
			AND TRN.[TYPE] = ''''I''''
			AND TRN.[OUTLAY] = ''''F'''' ''
      END
    ELSE
      BEGIN
        SET @NCOMMAND = N''

SELECT @FeesIssued = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0)
  FROM [dbo].[TransactionsHeaders] HED
       INNER JOIN [dbo].[Transactions] TRN
               ON TRN.[BatchNo] = HED.[BatchNo]
              AND TRN.[RecType] = ''''D''''
              AND TRN.[LineType] = ''''F''''
              AND TRN.[XnType] = ''''I''''
              AND TRN.[Posted] = ''''Y''''
              AND (   TRN.[CorrectionInd] = ''''L''''
                   OR TRN.[CorrectionInd] = ''''X'''')
 WHERE HED.[MatterCode] = @CaseCode

--values imported from Axxia
SELECT @FeesIssued = @FeesIssued + ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0)
  FROM [dbo].[Transactions] TRN
       LEFT OUTER JOIN [dbo].[TransactionsHeaders] HED
                    ON HED.[BatchNo] = TRN.[BatchNo]
 WHERE TRN.[MatterCode] = @CaseCode
   AND TRN.[RecType] = ''''D''''
   AND TRN.[LineType] = ''''F''''
   AND TRN.[XnType] = ''''I''''
   AND TRN.[Posted] = ''''Y''''
   AND (   TRN.[CorrectionInd] = ''''L''''
        OR TRN.[CorrectionInd] = ''''X'''')
   AND HED.[BatchNo] IS NULL

''
      END

	  EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @ChargeBal DECIMAL(18,2) OUTPUT, @EstimateFee DECIMAL(18,2) OUTPUT , @FeesIssued DECIMAL(18,2) OUTPUT, @Section68 VARCHAR(1) OUTPUT'', 
	  @CaseCode = @CaseCode,@ChargeBal = @ChargeBal OUTPUT, @EstimateFee = @EstimateFee OUTPUT, @FeesIssued = @FeesIssued OUTPUT,@Section68 = @Section68 OUTPUT
	 
	 SELECT (
	  SELECT ''Estimate Fees'' as WidgetName, @FeesIssued AS [FeesIssued], @ChargeBal as [ChargeBal],@EstimateFee as [EstimateFee], @Section68 as [Section68]
	  FOR XML PATH(''Result'')) as [Result]

'
WHERE TemplateId=1032
GO

UPDATE UITemplates
SET TemplateQuery=
'DECLARE @NCOMMAND nvarchar(MAX)      
DECLARE @DraftBillValue DECIMAL(18, 2)      
DECLARE @IsSAM4 bit    
      
SET @IsSAM4 = [dbo].[ISSAM4]()      
      
IF @IsSAM4 = 0      
  BEGIN      
    SET @NCOMMAND = N''      
	SELECT @DraftBillValue = ISNULL(SUM(ISNULL(BD.[NET], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VATVALUE], 0)), 0)      
	  FROM [dbo].[BillHeader] HED      
         INNER JOIN [dbo].[BillDetails] BD      
                 ON BD.[DRAFTNO] = HED.[DRAFTNO]      
	 WHERE HED.[MATTER] = @CaseCode      
      AND HED.[BILLNO] = 0''      
  END      
ELSE      
  BEGIN      
    SET @NCOMMAND = N''      
	SELECT @DraftBillValue = ISNULL(SUM(ISNULL(BD.[NetValue], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VatValue], 0)), 0)      
    FROM [dbo].[BillHead] HED      
         INNER JOIN [dbo].[BillDetail] BD      
                 ON BD.[BillID] = HED.[BillingID]      
	WHERE HED.[MatterCode] = @CaseCode      
    AND HED.[BillStatus] <> ''''Approved''''      
    AND ISNULL(HED.[ProForma], '''''''') = ''''Y''''''      
  END      
EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20),@DraftBillValue DECIMAL(18, 2) OUTPUT'', @CaseCode = @CaseCode, @DraftBillValue=@DraftBillValue OUTPUT      

      
SELECT (
	SELECT 
		@DraftBillValue AS [AccountSummaryValue],  
		''Draft Invoices'' AS [AccountSummaryLabel] 
		FOR XML PATH(''Result'')) as [Result]
          
'
WHERE TemplateId=1033
GO

UPDATE UITemplates
SET TemplateQuery=
'DECLARE @NCOMMAND nvarchar(MAX)      
DECLARE @DraftBillValue DECIMAL(18, 2)      
DECLARE @IsSAM4 bit            
SET @IsSAM4 = [dbo].[ISSAM4]()    
      
DECLARE @TIEDATE DATETIME            
     
IF [dbo].[ISSAM4]() = 0      
  BEGIN      
    SET @NCOMMAND = N''      
        SELECT @TIEDATE = MAX(CASE WHEN DLG.[DATE] IS NULL      
                                        THEN CONVERT(DATETIME, NULL)      
                                   WHEN DLG.[DATE] > GETDATE()      
                                        THEN CONVERT(DATETIME, NULL)      
                                   ELSE DLG.[DATE] END)      
          FROM [dbo].[DebtorsLedger] DLG      
         WHERE DLG.[MATTER] = @CaseCode''      
  END      
ELSE      
  BEGIN      
    SET @NCOMMAND = N''      
        SELECT @TIEDATE = MAX(CASE WHEN TRN.[TransDate] IS NULL      
                                        THEN CONVERT(DATETIME, NULL)      
                                   WHEN TRN.[TransDate] > GETDATE()      
                                        THEN CONVERT(DATETIME, NULL)      
                                   ELSE TRN.[TransDate] END)      
          FROM [dbo].[Transactions] TRN      
         WHERE TRN.[Posted] = ''''Y''''      
           AND TRN.[MatterCode] = @CaseCode      
           AND TRN.[DebtorsValue] <> 0      
           AND TRN.[CorrectionInd] = ''''L''''      
           AND (   (    TRN.[XnType] = ''''I''''      
                    AND TRN.[RecType] = ''''H'''')      
                OR (    TRN.[XnType] = ''''J''''      
                    AND TRN.[RecType] = ''''D''''))''      
  END      
      
EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @TIEDATE DATETIME OUTPUT'', @CaseCode = @CaseCode, @TIEDATE = @TIEDATE OUTPUT      
   
SELECT (
	SELECT 
		@TIEDATE AS [AccountSummaryValue],  
		''Last Bill Date'' AS [AccountSummaryLabel]     
	FROM 
		dbo.[matters] MAT      
	WHERE 
		MAT.[Code] = @CaseCode    
	FOR XML PATH(''Result'')
) AS [Result] 
'
WHERE TemplateId=1034
GO

UPDATE UITemplates
SET TemplateQuery=
'
SELECT (
SELECT 
	MAT.[ExpBillD] AS [AccountSummaryValue],  
	''Expected Bill Date'' AS [AccountSummaryLabel]     
FROM 
	dbo.[matters] MAT      
WHERE 
	MAT.[Code] = @CaseCode 
FOR XML PATH(''Result'')) as [Result]    
'
WHERE TemplateId=1035
GO

UPDATE UITemplates
SET TemplateQuery=
'
DECLARE @NCOMMAND nvarchar(MAX) 
DECLARE @IsSAM4 bit              
SET @IsSAM4 = [dbo].[ISSAM4]()  

declare @activityTotal decimal(18,2)
declare @TotalFeesBilled decimal(18,2)


SELECT @activityTotal = SUM(CONVERT(DECIMAL(18, 2), ISNULL(TIE.[Charge], 0)))  
            FROM [dbo].[matters] MAT
                 INNER JOIN [dbo].[TimeEntry] TIE
                         ON TIE.[MATTER] = MAT.[Code]
           WHERE TIE.[MATTER] = @CaseCode
             AND (   (TIE.[TimeOrCharge] = ''T'')
                  OR (TIE.[TimeOrCharge] = ''C''))
             AND TIE.[TASK] <> ''WRI''


  IF @IsSAM4 = 0
      BEGIN
        SET @NCOMMAND = N''
		
			SELECT 
				@TotalFeesBilled = ISNULL(SUM((CASE WHEN HED.[INVCR] = ''''I''''
								 THEN TRN.[VALUE]
								 ELSE TRN.[VALUE] * - 1 
							END)), 0)
			FROM 
				[dbo].[BatchH] HED
			INNER JOIN [dbo].[BatchDetails] TRN
						ON TRN.[BATCHNO] = HED.[BATCHNO]
			WHERE 
				HED.[MATTER] = @CaseCode
				AND HED.[POSTED] = ''''Y''''
				AND TRN.[TYPE] = ''''I''''
				AND TRN.[OUTLAY] = ''''F'''' 
			 ''
      END
    ELSE
      BEGIN
        SET @NCOMMAND = N''
DECLARE @TotalFeesBilled DECIMAL(22, 2)

SELECT @TotalFeesBilled = ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0)
  FROM [dbo].[TransactionsHeaders] HED
       INNER JOIN [dbo].[Transactions] TRN
               ON TRN.[BatchNo] = HED.[BatchNo]
              AND TRN.[RecType] = ''''D''''
              AND TRN.[LineType] = ''''F''''
              AND TRN.[XnType] = ''''I''''
              AND TRN.[Posted] = ''''Y''''
              AND (   TRN.[CorrectionInd] = ''''L''''
                   OR TRN.[CorrectionInd] = ''''X'''')
 WHERE HED.[MatterCode] = @CaseCode

--values imported from Axxia
SELECT @TotalFeesBilled = @TotalFeesBilled + ISNULL(SUM(ISNULL(TRN.[FeesBilled], 0) * ISNULL(TRN.[DrCr], 1) * (-1)), 0)
  FROM [dbo].[Transactions] TRN
       LEFT OUTER JOIN [dbo].[TransactionsHeaders] HED
                    ON HED.[BatchNo] = TRN.[BatchNo]
 WHERE TRN.[MatterCode] = @CaseCode
   AND TRN.[RecType] = ''''D''''
   AND TRN.[LineType] = ''''F''''
   AND TRN.[XnType] = ''''I''''
   AND TRN.[Posted] = ''''Y''''
   AND (   TRN.[CorrectionInd] = ''''L''''
        OR TRN.[CorrectionInd] = ''''X'''')
   AND HED.[BatchNo] IS NULL	''
      END
 EXECUTE sp_executesql @NCOMMAND, N''@CaseCode VARCHAR(20), @activityTotal DECIMAL(18,2) OUTPUT, @TotalFeesBilled DECIMAL(18,2) OUTPUT'', @CaseCode = @CaseCode, @activityTotal = @activityTotal OUTPUT , @TotalFeesBilled = @TotalFeesBilled OUTPUT

SELECT (
SELECT 
		@TotalFeesBilled AS [FeesIssued], @activityTotal AS [ActivityTotal] 
		FOR XML PATH(''Result'')) as [Result]

'
WHERE TemplateId=1036

GO

UPDATE UITemplates
SET TemplateQuery=
'
SELECT (
	SELECT 
		MAT.[Value] AS [AccountSummaryValue],  
		''Possible Value To The Client'' AS [AccountSummaryLabel]
	FROM 
		dbo.[matters] MAT
	WHERE 
		MAT.[Code] = @CaseCode     
	FOR XML PATH(''Result'')) AS ''Result''
'
WHERE TemplateId=1037
GO

UPDATE UITemplates
SET TemplateQuery=
'SELECT (
	SELECT 
		''Anti Money Laundering Checked'' as WidgetName, 
		Approved, 
		ApprovedDate 
	FROM 
		Contacts
	WHERE 
		CODE =  SUBSTRING(@casecode,0,CHARINDEX(''/'',@casecode,0)) 
	FOR XML PATH(''Result'')) AS ''Result''
'
WHERE TemplateId=1038
GO

UPDATE UITemplates
SET TemplateQuery=
'
	
DECLARE @IsSAM4 bit
DECLARE @ChargeBal DECIMAL(18,2)

SET @IsSAM4 = [dbo].[ISSAM4]()
SELECT @ChargeBal = 
	ISNULL(SUM(CASE WHEN ISNULL(TIE.[Rec_Irr], '''') = ''N''
                            THEN 0
                    WHEN @IsSAM4 = 1
                        AND ISNULL(TIE.[InvoiceNo], 0) <> 0
                            THEN 0
                    WHEN @IsSAM4 = 1
                            THEN ISNULL(TIE.[Charge], 0)
                    ELSE ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0) END), 0)
FROM TimeEntry TIE
WHERE 
	TIE.[Matter] =  @casecode
AND (   (TIE.[TimeOrCharge] = ''T'')
OR (TIE.[TimeOrCharge] = ''C'')) 

SELECT (				
	SELECT  
		''Current Locked Up Value'' as WidgetName, 
		@ChargeBal as ChargeBal, 
		OutlayBal, 
		DebtBal 
	FROM 
		matters
	WHERE 
		Code = @casecode

	FOR XML PATH(''Result'')
	)as ''Result''
'
WHERE TemplateId=1039
GO

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

SET IDENTITY_INSERT [dbo].[Panels] ON 

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

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

GO

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

GO

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

GO

SET IDENTITY_INSERT [dbo].[Panels] OFF
GO
---------------------------------------------Panels Insert End-----------------------------------------------------------



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

SET IDENTITY_INSERT [dbo].[UITemplates] ON 

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


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


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


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


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


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

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


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

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

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

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

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

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

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

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

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1015)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1015, N'No. of Cases where expected invoice date has passed', N'Template for No. of Cases where expected invoice date has passed', N'', N'No. of Cases where expected invoice date has passed QueryComponent', N'No. of Cases where expected invoice date has passed DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1016)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1016, N'No. of Cases approaching estimate fee', N'Template for No. of Cases approaching estimate fee', N'', N'No. of Cases approaching estimate fee QueryComponent', N'No. of Cases approaching estimate fee DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1017)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1017, N'No. of Cases approaching lockup value limit', N'Template for No. of Cases approaching lockup value limit', N'', N'No. of Cases approaching lockup value limit QueryComponent', N'No. of Cases approaching lockup value limit DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1018)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1018, N'No. of Clients that require money laundering checks', N'Template for No. of Clients that require money laundering checks', N'', N'No. of Clients that require money laundering checks QueryComponent', N'No. of Clients that require money laundering checks DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1019)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1019, N'No. of Cases with no estimate given', N'Template for No. of Cases with no estimate given', N'', N'No. of Cases with no estimate given QueryComponent', N'No. of Cases with no estimate given DisplayComponent')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1020)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1020, N'No. of Cases with no estimated fee', N'Template for No of Cases with no estimated fee', N'', N'No of Cases with no estimated fee Query Component', N'No of Cases with no estimated fee Display Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1021)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1021, N'Debtors A/C', N'Debtors Account information', N'', N'Debtors Account Query Component', N'Debtors Account Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1022)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1022, N'Outlay A/C', N'Outlay Account information', N'', N'Outlay Account Query Component', N'Outlay Account Query Component')
END
GO
IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1023)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1023, N'Outlay Budget', N'Outlay Budget information', N'', N'Outlay Budget Query Component', N'Outlay Budget Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1024)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1024, N'Current Outstanding Fees', N'Current Outstanding Fees information', N'', N'Current Outstanding Fees Query Component', N'Current Outstanding Fees Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1025)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1025, N'Client A/C', N'Client Account information', N'', N'Client Account Query Component', N'Client Account Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1026)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1026, N'Client Current', N'Client Current information', N'', N'Client Current Query Component', N'Client Current Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1027)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1027, N'Client Deposit', N'Client Deposit information', N'', N'Client Deposit Query Component', N'Client Deposit Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1028)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1028, N'Total Work In Progress', N'Total Work In Progress information', N'', N'Total Work In Progress Query Component', N'Total Work In Progress Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1029)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1029, N'Write Off Time', N'Write Off Time information', N'', N'Write Off Time Query Component', N'Write Off Time Display Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1030)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1030, N'Fees Issued To Date', N'Fees Issued To Date information', N'', N'Fees Issued To Date Query Component', N'Fees Issued To Date Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1031)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1031, N'Fees To Date + WIP', N'Fees To Date + WIP information', N'', N'Fees To Date + WIP Query Component', N'Fees To Date + WIP Display Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1032)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1032, N'Estimate Fees', N'Estimate Fees information', N'', N'Estimate Fees Query Component', N'Estimate Fees Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1033)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1033, N'Draft Invoices', N'Draft Invoices information', N'', N'Draft Invoices Query Component', N'Draft Invoices Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1034)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1034, N'Last Bill Date', N'Last Bill Date information', N'', N'Last Bill Date Query Component', N'Last Bill Date Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1035)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1035, N'Expected Bill Date', N'Expected Bill Date information', N'', N'Expected Bill Date Query Component', N'Expected Bill Date Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1036)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1036, N'Current Profit/Loss', N'Current Profit/Loss information', N'', N'Current Profit/Loss Query Component', N'Current Profit/Loss Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1037)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1037, N'Possible Value To The Client ', N'Possible Value To The Client  information', N'', N'Possible Value To The Client  Query Component', N'Possible Value To The Client  Query Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1038)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1038, N'Anti Money Laundering Checked', N'Anti Money Laundering Checked information', N'', N'Anti Money Laundering Checked Query Component', N'Anti Money Laundering Checked Display Component')
END
GO

IF NOT EXISTS(SELECT TOP 1 1 FROM [UITemplates] WHERE TemplateId = 1039)
BEGIN
	INSERT 
		[dbo].[UITemplates] ([TemplateId], [TemplateName], [Description], [TemplateQuery], [QueryComponent], [DisplayComponent]) 
	VALUES 
		(1039, N'Current Loked Up Value', N'Current Loked Up Value', N'', N'Current Loked Up Value', N'Current Loked Up Value')
END
GO

SET IDENTITY_INSERT [dbo].[UITemplates] OFF
GO


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


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

SET IDENTITY_INSERT [dbo].[Widgets] ON 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

---------------------------------------------------Widget Insert End-------------------------------------


---------------------------------------[PanelWidgetAssociation] Insert Start-----------------------------

SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] ON 

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


SET IDENTITY_INSERT [dbo].[PanelWidgetAssociation] OFF
GO
