USP_REPORT_SALESBYPROGRAM

create procedure dbo.USP_REPORT_SALESBYPROGRAM
(
    @FROMDATE datetime = null,
    @TODATE datetime = null,
    @PROGRAMID uniqueidentifier = null,
    @PROGRAMQUERY uniqueidentifier = null,
    @SALESMETHODID uniqueidentifier = null,
    @PROGRAMCATEGORYID uniqueidentifier = null
)
as
    set nocount on;

    declare @SALESMETHODTYPECODE tinyint = null;
    select @SALESMETHODTYPECODE = [TYPECODE]
    from dbo.[SALESMETHOD]
    where [ID] = @SALESMETHODID;

    declare @PROGRAMFILTER bit = 1;

    declare @PROGRAMS table (
        ID uniqueidentifier not null
    )

    if @PROGRAMID is not null
        insert into @PROGRAMS (ID)
        values (@PROGRAMID);
    else if @PROGRAMQUERY is not null
        insert into @PROGRAMS (ID)
        select [ID] from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@PROGRAMQUERY);
    else if @PROGRAMCATEGORYID is not null
        insert into @PROGRAMS (ID)
        select ID
        from dbo.PROGRAM
        where [PROGRAM].[PROGRAMCATEGORYCODEID] = @PROGRAMCATEGORYID;
    else 
        --I'd rather not place all the programs in a temp table--a potentially large amount

        set @PROGRAMFILTER = 0;

    with REPORTPARAMETERS_CTE as (
        select
            [SALESORDERITEMTICKET].[PROGRAMNAME],
            isnull(sum([SALESORDERITEM].[TOTAL]), 0) as [GROSS],
            isnull(sum([ITEMDISCOUNTS].[AMOUNT]), 0) + isnull(sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT), 0) as [DISCOUNTS],
            0 as [REFUNDS],
            isnull(sum(FEES.TOTAL), 0) as [FEES],
            0 as [NET],
            0 as [PERCENTOFNET],
            SALESORDERS.SALESMETHODTYPECODE,
            0 as [FEESREFUNDED]
        from
            dbo.[SALESORDERITEMTICKET]
        inner join
            dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
        inner join
            dbo.PROGRAM on [PROGRAM].[ID] = [SALESORDERITEMTICKET].[PROGRAMID]
        inner join (
            select SALESORDER.ID, SALESORDER.STATUSCODE, SALESORDER.SALESMETHODTYPECODE, FT.CALCULATEDDATE as DATE
            from dbo.SALESORDER
            inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = SALESORDER.REVENUEID

            union all
            select ID, STATUSCODE, SALESMETHODTYPECODE, TRANSACTIONDATE as DATE
            from dbo.SALESORDER
            where REVENUEID is null
        ) as SALESORDERS on SALESORDERS.ID = SALESORDERITEM.SALESORDERID
        outer apply (
            select sum(SALESORDERITEMITEMDISCOUNT.AMOUNT) as AMOUNT
            from dbo.SALESORDERITEMITEMDISCOUNT
            where SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
        ) as ITEMDISCOUNTS
        outer apply (
            select sum(FEEORDERITEM.TOTAL) as TOTAL
            from dbo.SALESORDERITEMFEE
            inner join dbo.SALESORDERITEM as FEEORDERITEM on FEEORDERITEM.ID = SALESORDERITEMFEE.ID
            where SALESORDERITEMFEE.SALESORDERITEMID = SALESORDERITEM.ID
        ) as FEES
        outer apply (
            select sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT) as AMOUNT
            from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
            where SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = SALESORDERITEM.ID
        ) as SALESORDERITEMORDERDISCOUNTDETAIL
        where
            SALESORDERS.DATE between @FROMDATE and @TODATE and
            SALESORDERS.STATUSCODE = 1 and
            (@SALESMETHODTYPECODE is null or SALESORDERS.SALESMETHODTYPECODE = @SALESMETHODTYPECODE)
            and (@PROGRAMFILTER = 0 or PROGRAM.ID in (select ID from @PROGRAMS))
        group by
            [PROGRAMNAME], SALESORDERS.SALESMETHODTYPECODE

        union all
        select
            [SALESORDERITEMTICKET].[PROGRAMNAME],
            0 as [GROSS],
            0 as [DISCOUNTS],
            isnull(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS), 0) - isnull(sum([SALESORDERITEMORDERDISCOUNTDETAIL].[AMOUNT]), 0) as [REFUNDS],
            0 as [FEES],
            0 as [NET],
            0 as [PERCENTOFNET],
            [SALESORDER].[SALESMETHODTYPECODE],
            isnull(sum(EXT.FEES), 0) as [FEESREFUNDED]
        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
        inner join
            dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
        inner join
            dbo.[SALESORDERITEMTICKET] on [SALESORDERITEMTICKET].[ID] = EXT.SALESORDERITEMID
        inner join
            dbo.[SALESORDER] on [SALESORDER].[ID] = [SALESORDERITEM].[SALESORDERID]
        inner join
            dbo.PROGRAM on PROGRAM.ID = SALESORDERITEMTICKET.PROGRAMID
        outer apply (
            select sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT) as AMOUNT
            from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
            where SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = SALESORDERITEM.ID
        ) as SALESORDERITEMORDERDISCOUNTDETAIL
        where
            FT.CALCULATEDDATE between @FROMDATE and @TODATE and
            [SALESORDER].[STATUSCODE] = 1 and
            (@SALESMETHODTYPECODE is null or [SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE) and
            (@PROGRAMFILTER = 0 or PROGRAM.ID in (select ID from @PROGRAMS))
        group by [PROGRAMNAME], [SALESORDER].[SALESMETHODTYPECODE]
    )
    select 
        [PROGRAMNAME],
        sum([GROSS]) as GROSS,
        sum([DISCOUNTS]) as DISCOUNTS,
        sum([REFUNDS] + [FEESREFUNDED]) as [REFUNDS],
        sum([FEES]) as FEES,
        0 as TAXES,
        sum([GROSS] - [DISCOUNTS] - [REFUNDS] - [FEESREFUNDED] + [FEES]) as [NET],
        SALESMETHOD.TYPE as [SALESMETHODFIELDTEXT]
    from REPORTPARAMETERS_CTE
    inner join dbo.SALESMETHOD on REPORTPARAMETERS_CTE.SALESMETHODTYPECODE = SALESMETHOD.TYPECODE
    group by [PROGRAMNAME], SALESMETHOD.TYPE
    order by PROGRAMNAME, SALESMETHOD.TYPE
    option (recompile)

    return 0;