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 [AccountSummaryValue], 
	''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]    
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]
  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],
	@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)
select  @chargeBal = chargebal from matters where code =  @CaseCode
  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'', @CaseCode = @CaseCode, @chargeBal = @chargeBal OUTPUT, @FeesIssued = @FeesIssued OUTPUT
SELECT (
SELECT ''Fees To Date + WIP'' AS WidgetName, @FeesIssued AS [FeesIssued], @chargeBal AS [ChargeBalance]   
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)


SELECT @ChargeBal  = mat.ChargeBal, @EstimateFee = Mat.EstFee  
            FROM [dbo].[matters] MAT
           WHERE MAT.Code = @CaseCode
		 
  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'', @CaseCode = @CaseCode,@ChargeBal = @ChargeBal OUTPUT, @EstimateFee = @EstimateFee OUTPUT, @FeesIssued = @FeesIssued OUTPUT
	 
	 SELECT (
	  SELECT ''Estimate Fees'' as WidgetName, @FeesIssued AS [FeesIssued], @ChargeBal as [ChargeBal],@EstimateFee as [EstimateFee]
	  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=
'SELECT (
	SELECT  
		''Current Locked Up Value'' as WidgetName, 
		ChargeBal, 
		OutlayBal, 
		DebtBal 
	FROM 
		matters
	WHERE 
		Code = @casecode
	FOR XML PATH(''Result'')
	)as ''Result''
'
WHERE TemplateId=1039
GO