USP_REPORT_DASHBOARD_SALES

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

    declare @MINDATE datetime                

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

    declare @TOTALSALES money
    declare @TOTALREFUNDS money
    declare @ORDERDISCOUNTS money
    declare @ITEMDISCOUNTS money

    --total sales

    select 
        @TOTALSALES = isnull(sum(SALESORDERITEM.TOTAL), 0)
    from 
        dbo.SALESORDER
    inner join
        dbo.SALESORDERITEM on SALESORDER.ID = SALESORDERITEM.SALESORDERID
    where
        SALESORDER.TRANSACTIONDATE between @STARTDATE and @ENDDATE and
        SALESORDER.STATUSCODE = 1 and  -- Complete

        SALESORDERITEM.TYPECODE not in (4,5)  -- Tax, Discount


    --total order discounts

    select 
        @ORDERDISCOUNTS = isnull(sum(SALESORDERITEM.TOTAL), 0)
    from 
        dbo.SALESORDER
    inner join
        dbo.SALESORDERITEM on SALESORDER.ID = SALESORDERITEM.SALESORDERID
    where
        SALESORDER.TRANSACTIONDATE between @STARTDATE and @ENDDATE and
        SALESORDER.STATUSCODE = 1 and  -- Complete

        SALESORDERITEM.TYPECODE = 5  -- Discount


    --total item discounts

    select 
        @ITEMDISCOUNTS = isnull(sum(SALESORDERITEMITEMDISCOUNT.AMOUNT), 0)
    from 
        dbo.SALESORDER
    inner join
        dbo.SALESORDERITEM on SALESORDER.ID = SALESORDERITEM.SALESORDERID
    inner join
        dbo.SALESORDERITEMITEMDISCOUNT on SALESORDERITEM.ID = SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID
    where
        SALESORDER.TRANSACTIONDATE between @STARTDATE and @ENDDATE and
        SALESORDER.STATUSCODE = 1  -- Complete


    --total refunds    

    select 
        @TOTALREFUNDS = isnull(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS), 0)
    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


    select @TOTALSALES - @TOTALREFUNDS - @ORDERDISCOUNTS - @ITEMDISCOUNTS amount;

    return 0;