IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'Ky_NETDashBoard'
      AND SO.[type] = 'P')
BEGIN
  DROP PROCEDURE [dbo].[Ky_NETDashBoard]
END
GO

CREATE PROCEDURE
       [dbo].[Ky_NETDashBoard]
      (@Handler     VARCHAR(10),
       @Fromdate    DATETIME,
       @todate      DATETIME)
AS
/*******************************************************************************************************
 *
 * Ky_NETDashBoard
 * Populates the My Overview page
 *
 * PINO 2014-11-12 - SAM 4 Functionality, improved SQL readability.
 * PINO 2014-11-19 - further readability improvements, fix inefficiency
 *                   and errors in calculation of My Dormant Matters
 * PINO 2015-02-04 - further readability improvements,
 *                   SAM4 functionality
 * PINO 2015-02-11 - time recorded this week and this month were reading from
 *                   daybook instead of time entry
 * PINO 2015-03-06 - incorrect calculation for previous Monday.
 *                   Write Off Time should not be included in counts for
 *                   'Time Recorded This Week' and 'Time Recorded This Month'
 * PINO 2015-03-24 - Fees Current Period, Last Period and YTD need to find
 *                   Transactions for the Fee Earner, not Transaction Headers
 *
 * Magesh 2015-04-02 - Added Matter.closed <> 'Y' condition on case alert
 * Magesh 2015-05-05 - Added matter.closed <> 'y' condition in getting dormant matters
 *
 * Pino 2015-05-26 - SAM4 functionality
 *
 * Pino 2015-06-24 - Outlay Balance
 *
 * Pino 2015-06-24 - Count only clients with open matters for Money Laundering Checks
 *
 * Pino 2015-08-20 - Add Time Recorded This Year
 *
 * Magesh 2015-08-31 - Added condition to the query used to find out @TotalStatuteLimits 
 *                     to exclude min date values '1900-01-01'
 *
 * Pino 2015-09-29 - lockup value should be @OSInvoices + @OutlayBalance + @WIP,
 *                   not @OSInvoices + @DraftInvoice + @WIP
 *						
 *******************************************************************************************************/
BEGIN
DECLARE @NCOMMAND NVARCHAR(MAX)
DECLARE @TotalUndertakings INT
DECLARE @TotalStatuteLimits INT
DECLARE @IsSAM4 bit

SET @IsSAM4 = [dbo].[ISSAM4]()

SELECT @TotalUndertakings = count(1)
  FROM dbo.[Undertakings] UND
 WHERE UND.[AuthorisedByFE] = @Handler
   AND UND.[DISCHARGEDATE] IS NULL

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

--Statement to get No. of Overdue and No. of Outstandings
SELECT ISNULL(ADT.[Overdue Task], 0) AS [Overdue Task],
       ISNULL(ADT.[Outstanding Task], 0) AS [Outstanding Task],
       ISNULL(ADT.[Critical Tasks], 0) AS [Critical Tasks],
       ISNULL(ADT.[Court Dates], 0) AS [Court Dates],
       ISNULL(ADT.[High Priority], 0) AS [High Priority],
       ISNULL(ADT.[Phone Message], 0) AS [Phone Message],
       @TotalUndertakings AS [UnderTaking],
       @TotalStatuteLimits AS [Statute Date Approaching]
  FROM (  SELECT SUM(CASE WHEN DEL.[DATE] < @fromdate
                               THEN 0
                          WHEN DEL.[DATE] >  DATEADD(s, -1, DATEADD(DD, 1, CONVERT (DATETIME, GETDATE())))
                               THEN 0
                          WHEN [DEL].[STATUS] = 0
                               THEN 1
                          ELSE 0 END) AS [Overdue Task],
                 SUM(CASE WHEN DEL.[DATE] < @fromdate
                               THEN 0
                          WHEN DEL.[DATE] >  DATEADD(s, -1, DATEADD(DD, 1, CONVERT (DATETIME, @todate)))
                               THEN 0
                          WHEN [DEL].[STATUS] = 0
                               THEN 1
                          ELSE 0 END) AS [Outstanding Task],
                 SUM(CASE WHEN DEL.[DATE] < @fromdate
                               THEN 0
                          WHEN DEL.[DATE] >  DATEADD(s, -1, DATEADD(DD, 1, CONVERT (DATETIME, @todate)))
                               THEN 0
                          WHEN [DIA].[ActionType] = 'C'
                               THEN 1
                          ELSE 0 END) AS [Critical Tasks],
                 SUM(CASE WHEN DEL.[DATE] < @fromdate
                               THEN 0
                          WHEN DEL.[DATE] >  DATEADD(s, -1, DATEADD(DD, 1, CONVERT (DATETIME, @todate)))
                               THEN 0
                          WHEN [DIA].[ActionType] = 'O'
                               THEN 1
                          ELSE 0 END) AS [Court Dates],
                 SUM(CASE WHEN DEL.[DATE] < @fromdate
                               THEN 0
                          WHEN DEL.[DATE] >  DATEADD(s, -1, DATEADD(DD, 1, CONVERT (DATETIME, @todate)))
                               THEN 0
                          WHEN [DIA].[PRIORITY] = 'H'
                               THEN 1
                          ELSE 0 END) AS [High Priority],
                 SUM(CASE WHEN DEL.[DATE] < @fromdate
                               THEN 0
                          WHEN DEL.[DATE] >  DATEADD(s, -1, DATEADD(DD, 1, CONVERT (DATETIME, @todate)))
                               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]
                         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)) & CONVERT (BIGINT, CONVERT (VARCHAR (20), 1)) <> 0
                  OR MNP.[Privileges] IS NULL)) ADT

SELECT 0 AS [Zero]

DECLARE @MyTimeDayBook INT
DECLARE @MyTimeDayBookHr VARCHAR(20)
DECLARE @MyTimeDayBookWeek INT
DECLARE @MyTimeDayBookWeekHr VARCHAR(20)
DECLARE @MyTimeDayBookMonth INT
DECLARE @MyTimeDayBookMonthHr VARCHAR(20)
DECLARE @MyTimeDayBookYear INT
DECLARE @MyTimeDayBookYearHr VARCHAR(20)
DECLARE @TODAYNUM INT
DECLARE @MONDAY DATETIME
DECLARE @STARTOFMONTH DATETIME
DECLARE @STARTOFYEAR DATETIME

DECLARE @GETDATE DATETIME

SET @GETDATE = GetDate()

SELECT @TODAYNUM = DATEPART(WEEKDAY, @GETDATE) - DATEPART(WEEKDAY, '20000102') --the 2nd of January, 2000 was a Monday

IF @TODAYNUM <= 0
  SET @TODAYNUM = @TODAYNUM + 7

SET @MONDAY = CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY, 1 - @TODAYNUM, @GETDATE), 112))

SELECT @TODAYNUM = DATEPART(DAY, @GETDATE)

SET @STARTOFMONTH = CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY, 1 - @TODAYNUM, @GETDATE), 112))

SELECT @STARTOFYEAR = KPP.[StartOfYear]
  FROM [dbo].[KPIParameters] KPP

SET @STARTOFYEAR = ISNULL(@STARTOFYEAR, CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GetDate())) + '0101'))

SELECT @MyTimeDayBook = ISNULL(SUM(ISNULL(TDB.[TIME], 0)), 0)
  FROM [dbo].[TimeDayBook] TDB
 WHERE TDB.[FEEEARN] = @Handler
   AND TDB.[TIMEORCHARGE] = 'T'

SELECT @MyTimeDayBookWeek = ISNULL(SUM(ISNULL(TIE.[TIME], 0)), 0)
  FROM [dbo].[TimeEntry] TIE
 WHERE TIE.[FEEEARN] = @Handler
   AND TIE.[TIMEORCHARGE] = 'T'
   AND TIE.[Date] >= @MONDAY
   AND TIE.[DATE] <= GETDATE()
   AND ISNULL(TIE.[TASK], '') <> 'WRI'

SELECT @MyTimeDayBookMonth = ISNULL(SUM(ISNULL(TIE.[TIME], 0)), 0)
  FROM [dbo].[TimeEntry] TIE
 WHERE TIE.[FEEEARN] = @Handler
   AND TIE.[TIMEORCHARGE] = 'T'
   AND TIE.[Date] >= @STARTOFMONTH
   AND TIE.[DATE] <= GETDATE()
   AND ISNULL(TIE.[TASK], '') <> 'WRI'

SELECT @MyTimeDayBookYear = ISNULL(SUM(ISNULL(TIE.[TIME], 0)), 0)
  FROM [dbo].[TimeEntry] TIE
 WHERE TIE.[FEEEARN] = @Handler
   AND TIE.[TIMEORCHARGE] = 'T'
   AND TIE.[Date] >= @STARTOFYEAR
   AND TIE.[DATE] <= GETDATE()
   AND ISNULL(TIE.[TASK], '') <> 'WRI'

SET @MyTimeDayBook = CASE WHEN @MyTimeDayBook < 0 THEN 0 ELSE @MyTimeDayBook END
SET @MyTimeDayBookHr = CONVERT(VARCHAR(20), @MyTimeDayBook / 60)
                     + ':'
                     + SUBSTRING(CONVERT(VARCHAR(3), 100 + (@MyTimeDayBook % 60)), 2, 2)

SET @MyTimeDayBookWeek = CASE WHEN @MyTimeDayBookWeek < 0 THEN 0 ELSE @MyTimeDayBookWeek END
SET @MyTimeDayBookWeekHr = CONVERT(VARCHAR(20), @MyTimeDayBookWeek / 60)
                         + ':'
                         + SUBSTRING(CONVERT(VARCHAR(3), 100 + (@MyTimeDayBookWeek % 60)), 2, 2)

SET @MyTimeDayBookMonth = CASE WHEN @MyTimeDayBookMonth < 0 THEN 0 ELSE @MyTimeDayBookMonth END
SET @MyTimeDayBookMonthHr = CONVERT(VARCHAR(20), @MyTimeDayBookMonth / 60)
                          + ':'
                          + SUBSTRING(CONVERT(VARCHAR(3), 100 + (@MyTimeDayBookMonth % 60)), 2, 2)

SET @MyTimeDayBookYear = CASE WHEN @MyTimeDayBookYear < 0 THEN 0 ELSE @MyTimeDayBookYear END
SET @MyTimeDayBookYearHr = CONVERT(VARCHAR(20), @MyTimeDayBookYear / 60)
                         + ':'
                         + SUBSTRING(CONVERT(VARCHAR(3), 100 + (@MyTimeDayBookYear % 60)), 2, 2)

-- Statement to get Time recorder value
SELECT @MyTimeDayBookHr AS [My Timeday Book Hr],
       @MyTimeDayBookWeekHr AS [Time Recorded this Week Hr],
       @MyTimeDayBookMonthHr AS [Time Recorded this Month Hr],
       @MyTimeDayBookYearHr AS [Time Recorded this Year Hr],
       @MyTimeDayBook AS [My Timeday Book],
       @MyTimeDayBookWeek AS [Time Recorded this Week],
       @MyTimeDayBookMonth AS [Time Recorded this Month],
       @MyTimeDayBookYear AS [Time Recorded this Year]

--Statement to get fees details
DECLARE @FeesCurrent DECIMAL(18, 2)
DECLARE @FeesLastPer DECIMAL(18, 2)
DECLARE @FeesYTD     DECIMAL(18, 2)

IF @IsSAM4 = 0
  BEGIN
    SET @NCOMMAND = '
  SELECT @FeesCurrent = 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]
         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 @FeesCurrent = 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),@FeesCurrent DECIMAL(18, 2) OUTPUT', @Handler = @Handler, @FeesCurrent=@FeesCurrent OUTPUT

IF @IsSAM4 = 0
  BEGIN
    SET @NCOMMAND = '
  SELECT @FeesLastPer = 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]
         INNER JOIN [dbo].[KPIParameters] KPI
                 ON (    KPI.[Year] = BD.[YEAR]
                     AND KPI.[Period] <> 1
                     AND KPI.[Period] - 1 = BD.[PERNO])
                 OR (    KPI.[Year] = BD.[YEAR] - 1
                     AND KPI.[Period] = 1
                     AND BD.[PERNO] = 12)
   WHERE BD.[FEE] = @Handler
     AND BD.[TYPE] = ''I''
     AND BD.[OUTLAY] = ''F'''
  END
ELSE
  BEGIN
    SET @NCOMMAND = '
  SELECT @FeesLastPer = 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] <> 1
                    AND KPI.[Period] - 1 = HED.[PostingPeriod])
                OR (    KPI.[Year] = HED.[PostingYear] - 1
                    AND KPI.[Period] = 1
                    AND HED.[PostingPeriod] = 12)
  WHERE HED.[RecType] = ''H'''
  END

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

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]
         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

SELECT REPLACE(CONVERT(VARCHAR, CAST(ROUND(@FeesCurrent, 0) AS MONEY), 1), '.00', '') AS [Fees Issued Current Month],
       REPLACE(CONVERT(VARCHAR, CAST(ROUND(@FeesLastPer, 0) AS MONEY), 1), '.00', '') AS [Fees Issued Last Month],
       REPLACE(CONVERT(VARCHAR, CAST(ROUND(@FeesYTD, 0) AS MONEY), 1), '.00', '') AS [Fees Issued Year to Date]

DECLARE @DraftInvoice DECIMAL(18, 2)

IF @IsSAM4 = 0
  BEGIN
    SET @NCOMMAND = '
SELECT @DraftInvoice = ISNULL(SUM(ISNULL(BD.[NET], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VATVALUE], 0)), 0)
  FROM [dbo].[matters] MAT
       INNER JOIN [dbo].[BillHeader] BH
                  INNER JOIN [dbo].[BillDetails] BD
                          ON BD.[DRAFTNO] = BH.[DRAFTNO]
               ON BH.[MATTER] = MAT.[Code]
              AND BH.[BILLNO] = 0
 WHERE MAT.[FECode] = @Handler'
  END
ELSE
  BEGIN
    SET @NCOMMAND = '
SELECT @DraftInvoice = ISNULL(SUM(ISNULL(BD.[NetValue], 0)), 0) + ISNULL(SUM(ISNULL(BD.[VatValue], 0)), 0)
  FROM [dbo].[Billhead] BH
       INNER JOIN [dbo].[BillDetail] BD
               ON BD.[BillID] = BH.[BillingID]
              AND BD.[RecordType] = ''F''
              AND BD.[FeCode] = @Handler
 WHERE BH.[FECode] = @Handler
   AND BH.[BillStatus] <> ''Approved'''
  END

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

DECLARE @OSFees DECIMAL(19, 2)
DECLARE @OSInvoices DECIMAL(19, 2)
DECLARE @AVGDDays INT

-- Pino 2015-05-26 - Please make sure that this calculation exactly mirrors the corresponding one
--                   in [dbo].[ky_NETDashboardCaseAlert] - if either is changed, change the other one
--                   accordingly
IF @IsSAM4 = 0
  BEGIN
    SET @NCOMMAND = N'
SELECT @OSFees = DT.[OSFees],
       @OSInvoices = DT.[OSInvoices],
       @AVGDDays = CASE WHEN DT.[DebtorEntries] = 0
                             THEN 0
                        ELSE DT.[DebtorDays] / DT.[DebtorEntries] END
  FROM (SELECT CONVERT(DECIMAL(19, 2), ISNULL(SUM(CONVERT(DECIMAL(19, 2), ISNULL(DTL.[OSFees], 0))), 0)) AS [OSFees],
               CONVERT(DECIMAL(19, 2), ISNULL(SUM(CONVERT(DECIMAL(19, 2), ISNULL(DTL.[OSVALUE], 0))), 0)) AS [OSInvoices],
               ISNULL(SUM(CASE WHEN DTL.[DATE] IS NULL
                                    THEN 0
                               WHEN DTL.[DATE] > GETDATE()
                                    THEN 0
                               WHEN DTL.[OSVALUE] = 0
                                    THEN 0
                               ELSE DATEDIFF(Day, DTL.[DATE], GetDate()) END), 0) AS [DebtorDays],
               ISNULL(SUM(CASE WHEN DTL.[DATE] IS NULL
                                    THEN 0
                               WHEN DTL.[DATE] > GETDATE()
                                    THEN 0
                               WHEN DTL.[OSVALUE] = 0
                                    THEN 0
                               ELSE 1 END), 0) AS [DebtorEntries]
          FROM [dbo].[matters] MAT
               INNER JOIN [dbo].[DebtorsLedger] DTL
                       ON DTL.[MATTER] = MAT.[Code]
                      AND DTL.[OSValue] > 0
          WHERE MAT.[FECode] = @Handler) DT'

    EXECUTE sp_executesql @NCOMMAND, N'@Handler VARCHAR(10), @OSFees DECIMAL(19, 2) OUTPUT, @OSInvoices DECIMAL(19, 2) OUTPUT, @AVGDDays INT OUTPUT', @Handler = @Handler, @OSFees = @OSFees OUTPUT, @OSInvoices = @OSInvoices OUTPUT, @AVGDDays = @AVGDDays OUTPUT

  END
ELSE
  BEGIN
    SET @NCOMMAND = N'
SELECT @OSFees = DT.[OSFees],
       @OSInvoices = DT.[OSInvoices],
       @AVGDDays = CASE WHEN DT.[DebtorEntries] = 0
                             THEN 0
                        ELSE DT.[DebtorDays] / DT.[DebtorEntries] END
  FROM (SELECT CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[DebtorsValueOS], 0)), 0)) AS [OSInvoices],
               CONVERT(DECIMAL(19, 2), ISNULL(SUM(ISNULL(TRN.[FeesOS], 0)), 0)) AS [OSFees],
               ISNULL(SUM(CASE WHEN TRN.[TransDate] IS NULL
                                    THEN 0
                               WHEN TRN.[TransDate] > GETDATE()
                                    THEN 0
                               WHEN TRN.[DebtorsValueOS] = 0
                                    THEN 0
                               ELSE DATEDIFF(Day, TRN.[TransDate], GetDate()) END), 0) AS [DebtorDays],
               ISNULL(SUM(CASE WHEN TRN.[TransDate] IS NULL
                                    THEN 0
                               WHEN TRN.[TransDate] > GETDATE()
                                    THEN 0
                               WHEN TRN.[DebtorsValueOS] = 0
                                    THEN 0
                               ELSE 1 END), 0) AS [DebtorEntries]
          FROM [dbo].[Transactions] TRN
         WHERE TRN.[Posted] = ''Y''
           AND TRN.[FeeEarner] = @Handler
           AND TRN.[CorrectionInd] = ''L''
           AND (   (    TRN.[XnType] = ''I''
                    AND TRN.[RecType] = ''H'')
                OR (    TRN.[XnType] = ''J''
                    AND TRN.[RecType] = ''D''))
           AND TRN.[DebtorsValueOS] > 0) DT'

    EXECUTE sp_executesql @NCOMMAND, N'@Handler VARCHAR(10), @OSInvoices DECIMAL(19, 2) OUTPUT, @OSFees DECIMAL(19, 2) OUTPUT, @AVGDDays INT OUTPUT', @Handler = @Handler, @OSInvoices = @OSInvoices OUTPUT, @OSFees = @OSFees OUTPUT, @AVGDDays = @AVGDDays OUTPUT
  END

DECLARE @WIP DECIMAL(19, 2)

-- Pino 2015-05-26 - Please make sure that this calculation exactly mirrors the corresponding one
--                   in [dbo].[ky_NETDashboardCaseAlert] - if either is changed, change the other one
--                   accordingly
IF @IsSAM4 = 0
  BEGIN
    SELECT @WIP = ISNULL(SUM(ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0)), 0)
      FROM [dbo].[TimeEntry] TIE
           INNER JOIN [dbo].[matters] MAT
                   ON MAT.[Code] = TIE.[Matter]
                  AND MAT.[Closed] = 'N'
     WHERE TIE.[FeeEarn] = @Handler
       AND TIE.[Rec_Irr] <> 'N'
       AND TIE.[Matter] <> '~'
       AND (   TIE.[TimeOrCharge] = 'T'
            OR TIE.[TimeOrCharge] = 'C')
  END
ELSE
  BEGIN
    SELECT @WIP = ISNULL(SUM(CASE WHEN TIE.[InvoiceNo] = 0
                                       THEN ISNULL(TIE.[Charge], 0)
                                  ELSE 0 END), 0)
      FROM [dbo].[TimeEntry] TIE
           INNER JOIN [dbo].[matters] MAT
                   ON MAT.[Code] = TIE.[Matter]
                  AND MAT.[Closed] = 'N'
     WHERE TIE.[FeeEarn] = @Handler
       AND TIE.[Rec_Irr] <> 'N'
       AND TIE.[Matter] <> '~'
       AND (   TIE.[TimeOrCharge] = 'T'
            OR TIE.[TimeOrCharge] = 'C')
  END

-- Pino 2015-06-24 Start
DECLARE @OutlayBalance DECIMAL(19, 2)

IF @IsSAM4 = 0
  BEGIN
    SET @NCOMMAND = N'
SELECT @OutlayBalance = ISNULL(SUM(ISNULL(MAT.[OutlayBal], 0)), 0)
  FROM [dbo].[matters] MAT
 WHERE MAT.[FECode] = @Handler
   AND MAT.[Closed] <> ''Y'''
  END
ELSE
  BEGIN
    SET @NCOMMAND = N'
SELECT @OutlayBalance = ISNULL(SUM(ISNULL(OBL.[OutlayBalance], 0)), 0)
  FROM (  SELECT MAT.[Code],
                 ISNULL(SUM(ISNULL(TRN.[OutlayValue], 0)), 0) AS [OutlayBalance]
            FROM [dbo].[matters] MAT
                 INNER JOIN [dbo].[Transactions] TRN
                         ON TRN.[MatterCode] = MAT.[Code]
                        AND (   (    (TRN.[Posted] = ''Y'')
                                 AND (TRN.[RecType] <> ''V'')
                                 AND (TRN.[CorrectionInd] = ''L''))
                             OR (    (TRN.[Posted] IS NULL)
                                 AND (TRN.[RecType] IS NULL)
                                 AND (TRN.[CorrectionInd] IS NULL)))
           WHERE MAT.[FECode] = @Handler
             AND MAT.[Closed] <> ''Y''
        GROUP BY MAT.[Code]) OBL'
  END

EXECUTE sp_executesql @NCOMMAND, N'@Handler VARCHAR(10), @OutlayBalance DECIMAL(19, 2) OUTPUT', @Handler = @Handler, @OutlayBalance = @OutlayBalance OUTPUT
-- Pino 2015-06-24 END

--Statement to get OS fee and invoice
SELECT REPLACE(CONVERT(VARCHAR,CAST(ROUND(@OSInvoices,0) AS MONEY),1),'.00','')  AS [OS Invoice],
       REPLACE(CONVERT(VARCHAR,CAST(ROUND(@OSFees,0) AS MONEY),1),'.00','') AS [OS Fees],
       REPLACE(CONVERT(VARCHAR,CAST(ROUND(@AVGDDays,0) AS MONEY),1),'.00','') AS [Average Debtors Days],
       REPLACE(CONVERT(VARCHAR,CAST(ROUND(@WIP,0) AS MONEY),1),'.00','') AS [Current WIP],
       REPLACE(CONVERT(VARCHAR,CAST(ROUND(@DraftInvoice,0) AS MONEY),1),'.00','') AS [Draft Invoice],
       REPLACE(CONVERT(VARCHAR,CAST(ROUND((@OSInvoices + @OutlayBalance + @WIP),0) AS MONEY),1),'.00','') AS [Current lookup value], --Pino 2015-09-29
       @OutlayBalance AS [Outlay Balance] --Added Pino 2015-06-24

DECLARE @Open INT
DECLARE @Dormant INT
DECLARE @Expectinvoice INT
DECLARE @Noestimatefee INT
DECLARE @Approachingestimatedfee INT
DECLARE @Lockedupvalue INT
DECLARE @Noestimategiven INT
DECLARE @MoneyLaunderingchecks INT

DECLARE @EstFeeWarningLimit DECIMAL(19, 2)
DECLARE @LockupUpValue      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 @LockupUpValue = CONVERT(DECIMAL(19, 2), ISNULL([SET].[KeyValue], '0'))
  FROM [dbo].[Settings] [SET]
 WHERE [SET].[KeyName] = 'LockupUpValue'

SET @LockupUpValue = ISNULL(@LockupUpValue, 0)

SELECT @Open = ISNULL(SUM(CASE WHEN MAT.[Closed] = 'Y'
                                    THEN 0
                               WHEN MAT.[ClientCode] = 'ZZZZZZ'
                                    THEN 0
                               ELSE 1 END), 0),
       @Expectinvoice = ISNULL(SUM(CASE WHEN MAT.[ExpBillD] <= GETDATE()
                                             THEN 1
                                        ELSE 0 END), 0),
       @Noestimatefee = ISNULL(SUM(CASE WHEN MAT.[EstFee] = 0
                                             THEN 1
                                        ELSE 0 END), 0),
       @Approachingestimatedfee = ISNULL(SUM(CASE WHEN (COALESCE ((ISNULL(MAT.[ChargeBal], 0) / NULLIF (MAT.[EstFee], 0) * 100), 0)) > @EstFeeWarningLimit
                                                       THEN 1
                                                  ELSE 0 END), 0),
       @Lockedupvalue = ISNULL(SUM(CASE WHEN (MAT.[ChargeBal] + MAT.[DebtBal] + MAT.[OutlayBal]) > @LockupUpValue
                                             THEN 1
                                        ELSE 0 END), 0),
       @Noestimategiven = ISNULL(SUM(CASE WHEN MAT.[Section68] = 'Y'
                                               THEN 0
                                          ELSE 1 END), 0)
  FROM [dbo].[matters] MAT
 WHERE MAT.[FECode] = @Handler
	AND MAT.Closed <> 'Y'

SELECT @Dormant = COUNT(1)
  FROM [dbo].[matters] MAT
       CROSS APPLY (SELECT CASE WHEN MAX(DIA.[DATE]) IS NULL
                                     THEN 1
                                ELSE CASE WHEN DATEDIFF(DD, MAX(DIA.[DATE]), GetDate()) < 180
                                               THEN 0
                                          ELSE 1 END END AS [Dormant],
                           MAX(DIA.[DATE]) AS [LastActionDate]
                      FROM [dbo].[diary] DIA
                     WHERE DIA.[CASECODE] = MAT.[Code]) LAC
 WHERE MAT.[FECode] = @Handler
   AND LAC.[Dormant] = 1
   AND MAT.Closed <> 'Y'

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 @Open AS 'Open Matters',
       @Dormant AS 'My Dormant Matters',
       @Expectinvoice  AS [No Case where expect invoice date has passed],
       @Noestimatefee  AS [No Case with no estimate fee],
       @Approachingestimatedfee AS [No Case Approaching estimated fee],
       @Lockedupvalue As [No Case Approaching Locked up value],
       @MoneyLaunderingchecks AS [No Clients that require Money Laundering checks],
       @Noestimategiven AS [No Cases with no estimate given]  ;
END
GO