---------------------------
--***********************
--
--  SUPPORT NOTES
--
--***********************
-- There are known issues with this SP
-- It regulary finds itself with a "bad" query plan.
-- The current method to resolve is:
--  1. run sp_recompile qryMMSSalesSummary_ListByDateRange
--  2. run the sp with a small date range (less than or equal to 1 week) e.g. qryMMSSalesSummary_ListByDateRange 184, '1-jan-10', '7-jan-10'
--  3. Advise the customer that this report should only be run for 1 week date ranges.
-- This should get the sp back into a resonable state  
-- Work needs to be done to completely re-write this query. 
-- Rich Lees, Rich Miller and Wayne Keyser have all spent significant time trying to debug current issues with no luck.
-- 
--------------------------
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qryMMSSalesSummary_ListByDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[qryMMSSalesSummary_ListByDateRange]
GO
CREATE Procedure [dbo].[qryMMSSalesSummary_ListByDateRange]
    @EntityID as bigint,    
    @StartDate as datetime, 
    @EndDate as datetime
    --WITH recompile
AS
    declare @MyEntityID as bigint = @EntityID
    declare @MyStartDate as datetime =  @StartDate
    declare @MyEndDate as datetime = @EndDate
    -- These settings have been added because without them precision errors were being generated. This was causing the 
    -- incorrect figure of NULL to be returned for some fields which was being converted to 0.  
    --set arithabort off  
    --set numeric_roundabort off  
    --set ansi_warnings off     
    SET NOCOUNT ON      
    CREATE TABLE #SalesSummaryDynamic(    
        EntityID INT,  
        Entity varchar(50), 
        Adjustment varchar(50),  
        WeekDay varchar(10),  
        SalesSummaryDate datetime,  
        DayStart datetime,  
        DayEnd datetime,  
        GrossSales Money,  
        Tax Money,  
        NetSales Money,  
        CashDrops Money,  
        Cards Money,  
        Vouchers Money,  
        VoucherCheques Money,  
        PettyCash Money,  
        CashOut Money,  
        [Difference] Money,  
        Rebank Money,  
        BankingID BIGINT,  
        SpecialDeposits Money,  
        TotalBanked Money,  
        CustomerCount INT,  
        CustomerCount_2 INT,  
        PMBanking Money,  
        AMBanking Money,  
        Banking Money,
        NonSales MONEY,
        NetSalesWithTax MONEY,
        GrossSalesWithTax MONEY,
        NetSalesExcludingTax MONEY,
        GrossSalesExcludingTax MONEY
    )   
    
    CREATE TABLE #Summary  (    
        EntityID int,  
        DayStart datetime,  
        DayEnd datetime,  
        GrossSales FLOAT,  
        NetSales FLOAT,  
        Tax FLOAT,  
        Rebank FLOAT,  
        CashDrops FLOAT,  
        BankingID BIGINT,  
        PettyCash FLOAT,  
        CashOut FLOAT,  
        [Difference] FLOAT,  
        SpecialDeposits FLOAT,  
        Cards FLOAT,  
        Vouchers FLOAT,  
        VoucherCheques FLOAT,  
        CustomerCount INT,  
        CustomerCount_2 INT,  
        Banking FLOAT,
        NonSales MONEY,
        NetSalesWithTax MONEY,
        GrossSalesWithTax MONEY,
        NetSalesExcludingTax MONEY,
        GrossSalesExcludingTax MONEY
    )  
                    
     -- TradingDays to be use to check Adjustments  
     DECLARE @TradingDateOpen as datetime 
     DECLARE @TradingDateClose as datetime  
 
     SELECT @TradingDateOpen = DayOpen,   
        @TradingDateClose = DayClose   
     FROM fn_GetTradingForCalendarDayRange(@MyEntityID,@MyStartDate,@MyEndDate)  
     
    -- Insert Dates
    INSERT INTO #SalesSummaryDynamic (  
        EntityID,  
        [weekDay],  
        SalesSummaryDate)  
    SELECT @MyEntityID,  
    CASE   
        WHEN DATEPART(dw,t1.dtime) = 1 then 'Sun'  
        WHEN DATEPART(dw,t1.dtime) = 2 then 'Mon'  
        WHEN DATEPART(dw,t1.dtime) = 3 then 'Tue'  
        WHEN DATEPART(dw,t1.dtime) = 4 then 'Wed'  
        WHEN DATEPART(dw,t1.dtime) = 5 then 'Thu'  
        WHEN DATEPART(dw,t1.dtime) = 6 then 'Fri'  
        WHEN DATEPART(dw,t1.dtime) = 7 then 'Sat'  
    END,  
    t1.dtime  
    FROM fn_DateTimes(@MyStartDate, @MyEndDate,1,'day') AS t1  
    
    
    UPDATE ssd
    SET 
        ssd.DayStart = t.DayOpen,
        ssd.DayEnd = t.DayClose
    FROM #SalesSummaryDynamic ssd
    CROSS APPLY fn_GetTradingForCalendarDay(@MyEntityID,ssd.SalesSummaryDate) t 
    
    -------------------  
    -- CustomerCount and NonSales       
    INSERT INTO #Summary (    
       EntityID,    
       CustomerCount,    
       CustomerCount_2,    
       DayStart,    
       DayEnd,
       NonSales)    
    SELECT    
       S.EntityID,    
       ISNULL(SUM(S.TransactionCount),0),    
       ISNULL(SUM(S.NoOfCovers),0),    
       t2.DayStart,    
       t2.DayEnd,
       ISNULL(SUM(S.NonSales),0)
    FROM tbTransactionSummary S with (NoLock)  
    join #SalesSummaryDynamic t2 on S.TransactionDate >= t2.DayStart and S.TransactionDate  < t2.DayEnd and s.EntityID = t2.EntityID  
    Where S.EntityID = @MyEntityID    
    GROUP BY  t2.DayStart, t2.DayEnd, S.EntityID
    
    INSERT INTO #Summary (  
        EntityID,  
        GrossSales,  
        NetSales,  
        Tax,  
        PettyCash,  
        SpecialDeposits,  
        CashOut,  
        Cards,  
        Vouchers,  
        VoucherCheques,  
        Rebank,  
        CashDrops,  
        [Difference],  
        DayStart,  
        DayEnd,
        NetSalesWithTax,
        GrossSalesWithTax,
        NetSalesExcludingTax,
        GrossSalesExcludingTax)  
    SELECT  
        S.EntityID,  
        SUM(ISNULL(S.NetSalesWithTax,0)) GrossSales,  
        SUM(ISNULL(S.NetSalesExcludingTax,0)) NetSales,   
        SUM(ISNULL(S.GST,0)) Tax,  
        SUM(ISNULL(S.PettyCash,0)) PettyCash,  
        SUM(ISNULL(S.SpecialDeposits,0)) SpecialDeposits,  
        SUM(ISNULL(S.CashOut,0)) CashOut,  
        SUM(ISNULL(S.CardsCredit,0)+ISNULL(S.CardsDebit,0)) Cards,  
        SUM(ISNULL(S.Vouchers,0)) Vouchers,  
        SUM(ISNULL(S.VoucherCheques,0)) VoucherCheques,  
        SUM(ISNULL(S.Rebank,0)) Rebank,  
        SUM(ISNULL(S.Cash,0)) CashDrops,  
        SUM(ISNULL(S.CashOverShort,0)) [Difference],  
        t2.DayStart,  
        t2.DayEnd,
        SUM(ISNULL(S.NetSalesWithTax, 0)) NetSalesWithTax,
        SUM(ISNULL(S.GrossSalesWithTax, 0)) GrossSalesWithTax,
        SUM(ISNULL(S.NetSalesExcludingTax, 0)) NetSalesExcludingTax,
        SUM(ISNULL(S.GrossSalesExcludingTax, 0)) GrossSalesExcludingTax  
    FROM fn_GetSalesAndBankingRows(@MyEntityID, @MyStartDate, @MyEndDate,1) S 
    JOIN #SalesSummaryDynamic t2 on (S.TradingDate  = t2.SalesSummaryDate) and (S.EntityID = t2.EntityID)  
    WHERE RecordType = 'DayOfWeek'  
    GROUP BY t2.DayStart, t2.DayEnd, S.EntityID  
    --------------------  
    -- Banking  
    
    UPDATE #SalesSummaryDynamic  
        SET PMBanking=ISNULL(t1.actualDepositTotal,0)  
    FROM tbBanking t1 WITH (NOLOCK)   
    INNER JOIN #SalesSummaryDynamic t2 ON t1.CreateDate = t2.SalesSummaryDate AND t1.EntityID = t2.EntityID   
    WHERE t1.BankingtypeID = 2  
    
    UPDATE #SalesSummaryDynamic  
        SET AMBanking=ISNULL(t1.actualDepositTotal,0)  
    FROM tbBanking t1 with (nolock)   
    INNER JOIN #SalesSummaryDynamic t2 ON t1.CreateDate = DATEADD(day, 1, t2.SalesSummaryDate) AND t1.EntityID = t2.EntityID   
    WHERE t1.BankingtypeID = 1  
    
    UPDATE #SalesSummaryDynamic  
        SET Banking = (ISNULL(PMBanking,0) + ISNULL(AMBanking,0))  
    -------  
    UPDATE #SalesSummaryDynamic  
        SET   
            GrossSales = ISNULL(t2.GrossSales,0),  
            Tax = ISNULL(t2.Tax,0),   
            NetSales = ISNULL(t2.NetSales,0),   
            Cards = ISNULL(t2.Cards,0),   
            Vouchers = ISNULL(t2.Vouchers,0),   
            VoucherCheques = ISNULL(t2.VoucherCheques,0),   
            PettyCash = ISNULL(t2.PettyCash,0),   
            CashOut = ISNULL(t2.CashOut,0),   
            Rebank = ISNULL(t2.Rebank,0),  
            CashDrops = ISNULL(t2.CashDrops,0),   
            [Difference] = ISNULL(t2.[Difference],0),   
            SpecialDeposits = ISNULL(t2.SpecialDeposits,0),   
            TotalBanked = (ISNULL(t2.CashDrops,0) + ISNULL(t2.Cards,0)),   
            CustomerCount = ISNULL(t2.CustomerCount,0),  
            CustomerCount_2 = ISNULL(t2.CustomerCount_2,0),
            NonSales = ISNULL(t2.NonSales,0),
            NetSalesWithTax = ISNULL(t2.NetSalesWithTax, 0),
            GrossSalesWithTax = ISNULL(t2.GrossSalesWithTax, 0),
            NetSalesExcludingTax = ISNULL(t2.NetSalesExcludingTax, 0),
            GrossSalesExcludingTax = ISNULL(t2.GrossSalesExcludingTax, 0)
    FROM #SalesSummaryDynamic t1 
    INNER JOIN
    (
    SELECT 
        EntityID, 
        DayStart, 
        DayEnd, 
        SUM(ISNULL(GrossSales,0)) AS GrossSales,
        SUM(ISNULL(NetSales,0)) AS NetSales,
        SUM(ISNULL(Tax,0)) AS Tax,
        SUM(ISNULL(Rebank,0)) AS Rebank,
        SUM(ISNULL(CashDrops,0)) AS CashDrops,
        BankingID,
        SUM(ISNULL(PettyCash,0)) AS PettyCash,
        SUM(ISNULL(CashOut,0)) AS CashOut,
        SUM(ISNULL([Difference],0)) AS [Difference],
        SUM(ISNULL(SpecialDeposits,0)) AS SpecialDeposits,
        SUM(ISNULL(Cards,0)) AS Cards,
        SUM(ISNULL(Vouchers,0)) AS Vouchers,
        SUM(ISNULL(VoucherCheques,0)) AS VoucherCheques,
        SUM(ISNULL(CustomerCount,0)) AS CustomerCount,
        SUM(ISNULL(CustomerCount_2,0)) AS CustomerCount_2,
        Banking,
        SUM(ISNULL(NonSales,0)) AS NonSales,
        SUM(ISNULL(NetSalesWithTax,0)) AS NetSalesWithTax,
        SUM(ISNULL(GrossSalesWithTax,0)) AS GrossSalesWithTax,
        SUM(ISNULL(NetSalesExcludingTax,0)) AS NetSalesExcludingTax,
        SUM(ISNULL(GrossSalesExcludingTax,0)) AS GrossSalesExcludingTax
    FROM #Summary 
    GROUP BY 
        EntityID, 
        DayStart, 
        DayEnd, 
        BankingID,
        Banking) t2 ON (t1.DayStart = t2.DayStart and t1.DayEnd =  t2.DayEnd) and t1.EntityID = t2.EntityID  
    ----  
    UPDATE #SalesSummaryDynamic  
        SET Entity = tbEntity.Entity  
    FROM tbEntity   
    WHERE tbEntity.EntityID = @MyEntityID  
    UPDATE #SalesSummaryDynamic  
        SET Adjustment = '<font color=navy>*</font>'  
    WHERE dbo.fn_DateOnly(SalesSummaryDate) IN 
        ( SELECT DISTINCT dbo.fn_DateOnly(TransactionDate) 
                 from tbTransactionSummary  with (NoLock)   
         WHERE TransactionDate >= @TradingDateOpen 
            AND TransactionDate <= @TradingDateClose 
            AND EntityID = @MyEntityID 
            AND PosTransactionID IN (-1,-3) )   
    -- 
    
    --Update PettyCash from Transactional tables
    UPDATE #SalesSummaryDynamic  
        SET PettyCash = (  
            SELECT SUM(d.TotalAmount)  
                FROM tbTransactionPettyCash d WITH (NOLOCK)    
            WHERE d.TransactionDate >= s.DayStart  
            AND d.TransactionDate < s.DayEnd     
            AND d.EntityID = @EntityID
            AND d.PettyCashTypeID = 1)  
        FROM #SalesSummaryDynamic s
   
    --Update Cards from Transactional tables      
     DECLARE @ManualCards INT  
   
        SELECT TOP 1 @ManualCards = s.ShowCards  
     FROM tbEntityCashupSettings es WITH (NOLOCK)   
      JOIN tbCashUpSettings s WITH (NOLOCK) ON es.CashupSettingsID = s.CashupSettingsID     
     WHERE es.EntityID IN (@EntityID,0)    
     ORDER BY es.EntityID DESC    
  
     IF @ManualCards = 1  
      UPDATE #SalesSummaryDynamic  
      SET Cards = (  
       SELECT SUM(d.EFTPOS)  
       FROM tbTransactionCashDeposit d WITH (NOLOCK)    
       WHERE d.CloseDate >= s.DayStart     
        AND d.CloseDate < s.DayEnd     
        AND d.EntityID = @EntityID)  
      FROM #SalesSummaryDynamic s  
     ELSE  
      UPDATE #SalesSummaryDynamic  
      SET Cards = (  
       SELECT SUM(tci.ControlInfoAmount)  
       FROM tbTransactionControlInfo tci WITH (NOLOCK)     
        JOIN tbControlInfo ci WITH (NOLOCK) ON tci.ControlInfoID = ci.ControlInfoID    
       WHERE ci.ControlInfo IN ('EFTPOS','EFTTenderKey','American Express','MasterCard','VISA','PIN','CHIP','Diners', 'Other Card')     
        AND tci.TransactionDate >= s.DayStart     
        AND tci.TransactionDate < s.DayEnd  
        AND tci.EntityID = @EntityID)  
      FROM #SalesSummaryDynamic s  
  
    SELECT   
        Entity,  
        Adjustment,  
        [WeekDay],  
        SalesSummaryDate SalesSummaryDate,  
        ISNULL(GrossSales,0) GrossSales,  
        ISNULL(Tax,0) Tax,  
        ISNULL(NetSales,0) NetSales,  
        ISNULL(CashDrops,0) CashDrops,  
        ISNULL(Cards,0) Cards,  
        ISNULL(Vouchers,0) Vouchers,  
        ISNULL(VoucherCheques,0) VoucherCheques,  
        ISNULL(PettyCash,0) PettyCash,  
        ISNULL(CashOut,0) CashOut,  
        ISNULL([Difference],0) [Difference],  
        ISNULL(Rebank,0) Rebank,  
        ISNULL(SpecialDeposits,0) SpecialDeposits,  
        ISNULL(TotalBanked,0) TotalBanked,  
        ISNULL(CustomerCount,0) CustomerCount,  
        ISNULL(CustomerCount_2,0) CustomerCount_2,  
        ISNULL(PMBanking,0) PMBanking,  
        ISNULL(AMBanking,0) AMBanking,  
        ISNULL(Banking,0) Banking,  
        CAST(ISNULL(NetSales + Tax - Banking - Cards ,0) AS MONEY)  AS BankingDifference,
        ISNULL(NonSales,0) NonSales,
        ISNULL(NetSalesWithTax, 0) NetSalesWithTax,
        ISNULL(GrossSalesWithTax, 0) GrossSalesWithTax,
        ISNULL(NetSalesExcludingTax, 0) NetSalesExcludingTax,
        ISNULL(GrossSalesExcludingTax, 0) GrossSalesExcludingTax  
    FROM #SalesSummaryDynamic
    ORDER BY SalesSummaryDate  
DROP TABLE #SalesSummaryDynamic
DROP TABLE #Summary
GO