USP_REPORT_DASHBOARD_SALES_BYDATE

create procedure dbo.USP_REPORT_DASHBOARD_SALES_BYDATE
(
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @ALLDATES bit
)
as
    set nocount on;

    declare @MINDATE datetime                
    declare @SALESDATETABLE table (
        TransactionDate date,
        TotalSales money
    )

    -- Checking TRANSACTIONDATE for null so that the index will perform a seek.

    -- Removed check for STATUSCODE since the earliest TRANSACTIONDATE should be a complete order

    -- and including STATUSCODE would require a key lookup.

    select @MINDATE = min(SALESORDER.TRANSACTIONDATE) from dbo.SALESORDER where SALESORDER.TRANSACTIONDATE is not null;

    set @MINDATE = coalesce(@MINDATE, getdate())

    if @ALLDATES = 1 begin                                
        set @STARTDATE = @MINDATE
        set @ENDDATE = getdate()
    end

    set @STARTDATE = coalesce(@STARTDATE, @MINDATE)
    set @ENDDATE = coalesce(@ENDDATE, getdate())

    if @STARTDATE > @ENDDATE
        set @STARTDATE = @ENDDATE                            

    set @STARTDATE = cast(@STARTDATE as date)
    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)

    insert into @SALESDATETABLE 
    select
        DATES.[DATE] as TransactionDate,
        0 TotalSales
    from
        dbo.UFN_CALENDARDATES(@STARTDATE, @ENDDATE, 1) DATES

    update @SALESDATETABLE set
        TotalSales = SALES.Amount
    from 
        @SALESDATETABLE SALESDATETABLE
    inner join (
        --total sales                

        select 
            cast(SALES.TRANSACTIONDATE as date) as TransactionDate,
            sum(SALES.AMOUNT) - sum(coalesce(SALESORDERITEMITEMDISCOUNT.AMOUNT, 0)) as Amount
        from (
            select 
                SALESORDER.TRANSACTIONDATE,
                case
                    when SALESORDERITEM.TYPECODE = 5 then
                        -SALESORDERITEM.TOTAL
                    else
                        SALESORDERITEM.TOTAL 
                end as AMOUNT,
                SALESORDERITEM.ID
            from
                dbo.SALESORDER
            inner join
                dbo.SALESORDERITEM on SALESORDER.ID = SALESORDERITEM.SALESORDERID
            where
                SALESORDER.TRANSACTIONDATE between @STARTDATE and @ENDDATE and
                SALESORDERITEM.TYPECODE <> 4 and  -- Tax

                SALESORDER.STATUSCODE = 1  -- Complete

        ) as SALES
        left outer join
            dbo.SALESORDERITEMITEMDISCOUNT on SALES.ID = SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID                                    
        group by
            cast(SALES.TRANSACTIONDATE as date)
    ) as SALES on SALESDATETABLE.TransactionDate = SALES.TransactionDate;

    --total refunds

    update @SALESDATETABLE set
        TotalSales = TotalSales - REFUND.Amount
    from 
        @SALESDATETABLE SALESDATETABLE
    inner join (
        select
            REFUND.TRANSACTIONDATE as TransactionDate, 
            sum(REFUND.AMOUNT) as Amount
        from (
            select
                FT.CALCULATEDDATE as TransactionDate,
                ((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS) as AMOUNT                            
            from
                dbo.FINANCIALTRANSACTION as FT
            inner join
                dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
            inner join
                dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
            where
                FT.CALCULATEDDATE between @STARTDATE and @ENDDATE and
                EXT.TYPECODE <> 4  -- Tax

                and FT.TYPECODE = 23  -- Refund

        ) as REFUND
        group by
            REFUND.TRANSACTIONDATE
    ) as REFUND on SALESDATETABLE.TransactionDate = REFUND.TransactionDate

    select 
        TransactionDate, 
        TotalSales Amount
    from @SALESDATETABLE
    order by            
        TransactionDate

    return 0;