USP_REPORT_DISCOUNTREPORT

create procedure dbo.USP_REPORT_DISCOUNTREPORT (
    @FROMDATE date = null,
    @TODATE date = null,
    @APPUSERID uniqueidentifier = null,
    @DISCOUNTID uniqueidentifier = null,
    @APPLICATIONTYPECODE tinyint = null,
    @INCLUDEREFUNDED bit = 0,
    @GROUPING tinyint = null,
    @SALESMETHODID uniqueidentifier = null,
    @DISCOUNTQUERY uniqueidentifier = null,
    @USERQUERY uniqueidentifier = null,
    @ADJUSTABLEDISCOUNTSONLY bit = 0,
    @APPLIEDTICKETSONLY bit = 0,
    @MEMBERSHIPPROMOID uniqueidentifier = null
)
as
    set nocount on;

    declare @SALESMETHODTYPECODE tinyint = (
        select TYPECODE
        from dbo.SALESMETHOD
        where ID = @SALESMETHODID
    );

    declare @ADJUSTABLEDISCOUNTID uniqueidentifier = newid();
    declare @APPLIEDTICKETSTOMEMBERSHIPPROMOTIONID uniqueidentifier = newid();

    declare @USERQUERYRESULTS dbo.UDT_GENERICID;
    declare @DISCOUNTQUERYRESULTS dbo.UDT_GENERICID;

    if @USERQUERY is not null begin
        insert into @USERQUERYRESULTS (ID)
        select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@USERQUERY);
    end

    if @DISCOUNTID is not null begin
        insert into @DISCOUNTQUERYRESULTS (ID)
        select @DISCOUNTID
    end
    if @DISCOUNTQUERY is not null begin
        insert into @DISCOUNTQUERYRESULTS (ID)
        select ID
        from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@DISCOUNTQUERY) IDSET
    end;

    with ORDERS_CTE as (
        select ID from dbo.UFN_SALESORDER_BETWEENDATES(@FROMDATE, @TODATE)
    ),
    DISCOUNTS_CTE as (

        -- Order-level discounts

        select
            SALESORDER.ID SALESORDERID,
            SALESORDER.APPUSERID,
            DISCOUNTITEM.PRICE AMOUNT,
            case when EXT.ID is null then 0 else DISCOUNTITEM.PRICE end REFUNDEDAMOUNT,
            case when EXT.ID is null then 0 else 1 end TIMESREFUNDED,
            coalesce(ORDERDISCOUNT.DISCOUNTID, @ADJUSTABLEDISCOUNTID) DISCOUNTID,
            ORDERDISCOUNT.DISCOUNTNAME,
            SALESORDER.ID as DISCOUNTEDITEMID,  -- this is actually an order ID here

            1 as QUANTITY,
            SALESORDER.SALESMETHODTYPECODE,
            case when MANUALDISCOUNT.ID is not null then ORDERDISCOUNT.ISADJUSTABLEDISCOUNT else 0 end ISMANUALLYAPPLIED,  -- If it's Adjustable, then it's definitely manual.

            MANUALDISCOUNT.PROMOTIONALCODE
        from ORDERS_CTE
        inner join dbo.SALESORDER on SALESORDER.ID = ORDERS_CTE.ID
        inner join dbo.SALESORDERITEM DISCOUNTITEM on DISCOUNTITEM.SALESORDERID = SALESORDER.ID
        inner join dbo.SALESORDERITEMORDERDISCOUNT ORDERDISCOUNT on ORDERDISCOUNT.ID = DISCOUNTITEM.ID
        left join dbo.SALESORDERMANUALDISCOUNT MANUALDISCOUNT on (MANUALDISCOUNT.SALESORDERID = SALESORDER.ID and MANUALDISCOUNT.DISCOUNTID = ORDERDISCOUNT.DISCOUNTID)
        left join dbo.CREDITITEM_EXT EXT on EXT.SALESORDERITEMID = DISCOUNTITEM.ID
        where SALESORDER.STATUSCODE in (1, 3, 4)

        union all

        -- Item-level discounts

        select
            SALESORDER.ID SALESORDERID,
            SALESORDER.APPUSERID,
            ITEMDISCOUNT.AMOUNT,
            REFUNDS.REFUNDEDDISCOUNTS REFUNDEDAMOUNT,
            REFUNDS.TIMESREFUNDED,
            ITEMDISCOUNT.DISCOUNTID,
            ITEMDISCOUNT.DISCOUNTNAME,
            DISCOUNTEDITEM.ID as DISCOUNTEDITEMID,
            ITEMDISCOUNT.NUMBEROFDISCOUNTEDITEMS QUANTITY,
            SALESORDER.SALESMETHODTYPECODE,
            case when MANUALDISCOUNT.ID is not null then 1 else 0 end ISMANUALLYAPPLIED,
            MANUALDISCOUNT.PROMOTIONALCODE
        from ORDERS_CTE
        inner join dbo.SALESORDER on SALESORDER.ID = ORDERS_CTE.ID
        inner join dbo.SALESORDERITEM DISCOUNTEDITEM on DISCOUNTEDITEM.SALESORDERID = SALESORDER.ID
        inner join dbo.SALESORDERITEMITEMDISCOUNT ITEMDISCOUNT on ITEMDISCOUNT.SALESORDERITEMID = DISCOUNTEDITEM.ID  -- Multiple applications in the same order show multiple times (that's what we want).

        left join dbo.SALESORDERMANUALDISCOUNT MANUALDISCOUNT on (MANUALDISCOUNT.SALESORDERID = SALESORDER.ID and MANUALDISCOUNT.DISCOUNTID = ITEMDISCOUNT.DISCOUNTID)
        outer apply (
            -- the sum here should prevent duplication of ITEMDISCOUNT.AMOUNT for new-style refunds (multiple credit items can link to a single discounted item)

            select
                coalesce(sum(EXT.DISCOUNTS),0) REFUNDEDDISCOUNTS,
                coalesce(case
                    when sum(case when EXT.DISCOUNTS > 0 then FTLI.QUANTITY else 0 end) > ITEMDISCOUNT.NUMBEROFDISCOUNTEDITEMS then ITEMDISCOUNT.NUMBEROFDISCOUNTEDITEMS  -- For old-style refunds: if |refunded| > |discounted|, then all discounted items were returned.

                    else sum(case when EXT.DISCOUNTS > 0 then FTLI.QUANTITY else 0 end)
                end,0) TIMESREFUNDED
            from dbo.CREDITITEM_EXT EXT
            inner join dbo.FINANCIALTRANSACTIONLINEITEM    FTLI on FTLI.ID = EXT.ID
            where EXT.SALESORDERITEMID = DISCOUNTEDITEM.ID
        ) REFUNDS
        where SALESORDER.STATUSCODE in (1, 3, 4)

        union all

        -- Membership promotions (including applied tickets)

        select
            SALESORDER.ID SALESORDERID,
            SALESORDER.APPUSERID,
            PROMO.AMOUNT,
            coalesce(CREDITPROMO.AMOUNT,0) REFUNDEDAMOUNT,
            case when CREDITPROMO.AMOUNT is null then 0 else 1 end TIMESREFUNDED,
            coalesce(PROMO.MEMBERSHIPPROMOID, @APPLIEDTICKETSTOMEMBERSHIPPROMOTIONID) DISCOUNTID,
            PROMO.PROMOTIONNAME DISCOUNTNAME,
            DISCOUNTEDITEM.ID as DISCOUNTEDITEMID,
            1 as QUANTITY,
            SALESORDER.SALESMETHODTYPECODE,
            case when MEMBERSHIPPROMO.ID is null or MEMBERSHIPPROMO.APPLICATIONTYPECODE in (0,1) then 1 else 0 end ISMANUALLYAPPLIED,
            MANUALDISCOUNT.PROMOTIONALCODE
        from ORDERS_CTE
        inner join dbo.SALESORDER on SALESORDER.ID = ORDERS_CTE.ID
        inner join dbo.SALESORDERITEM DISCOUNTEDITEM on DISCOUNTEDITEM.SALESORDERID = SALESORDER.ID
        inner join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION PROMO on PROMO.SALESORDERITEMID = DISCOUNTEDITEM.ID
        left join dbo.MEMBERSHIPPROMO with (nolock) on MEMBERSHIPPROMO.ID = PROMO.MEMBERSHIPPROMOID
        left join dbo.SALESORDERMANUALDISCOUNT MANUALDISCOUNT on (MANUALDISCOUNT.SALESORDERID = SALESORDER.ID and MANUALDISCOUNT.DISCOUNTID = MEMBERSHIPPROMO.ID)
        left join dbo.CREDITITEM_EXT MEMBERSHIPREFUNDEXT on MEMBERSHIPREFUNDEXT.SALESORDERITEMID = DISCOUNTEDITEM.ID
        left join dbo.CREDITITEMMEMBERSHIPITEMPROMOTION CREDITPROMO on CREDITPROMO.CREDITITEMID = MEMBERSHIPREFUNDEXT.ID
        where SALESORDER.STATUSCODE in (1, 3, 4)
    ),
    FILTEREDDISCOUNTS_CTE as (
        select
            APPUSERID,
            case when @INCLUDEREFUNDED = 1 then AMOUNT else AMOUNT - REFUNDEDAMOUNT end AMOUNTAPPLIED,
            case when @INCLUDEREFUNDED = 1 then QUANTITY else QUANTITY - TIMESREFUNDED end TIMESAPPLIED,
            DISCOUNTID,
            DISCOUNTNAME,
            DISCOUNTEDITEMID,
            SALESMETHODTYPECODE
        from DISCOUNTS_CTE
        where
            ((@DISCOUNTID is null and @DISCOUNTQUERY is null) or DISCOUNTID in (select ID from @DISCOUNTQUERYRESULTS))
            and (@ADJUSTABLEDISCOUNTSONLY = 0 or DISCOUNTID = @ADJUSTABLEDISCOUNTID)
            and (@APPLIEDTICKETSONLY = 0 or DISCOUNTID = @APPLIEDTICKETSTOMEMBERSHIPPROMOTIONID)
            and (@MEMBERSHIPPROMOID is null or DISCOUNTID = @MEMBERSHIPPROMOID)
            and (@SALESMETHODTYPECODE is null or SALESMETHODTYPECODE = @SALESMETHODTYPECODE)
            and (
                @APPLICATIONTYPECODE is null  -- Anything

                or (@APPLICATIONTYPECODE = ISMANUALLYAPPLIED) -- 0 = 0 for automatic or 1 = 1 for manual

                or (@APPLICATIONTYPECODE = 2 and len(PROMOTIONALCODE) > 0)  -- 2 is promo code

            )
            and (
                @APPUSERID is null
                or @APPUSERID in (select SALESORDERPAYMENT.APPUSERID from dbo.SALESORDERPAYMENT with (nolock) where SALESORDERPAYMENT.ID = DISCOUNTS_CTE.SALESORDERID)
                or DISCOUNTS_CTE.APPUSERID = @APPUSERID
            )
            and (
                @USERQUERY is null
                or exists (
                    select *
                    from dbo.SALESORDERPAYMENT with (nolock)
                    inner join @USERQUERYRESULTS as RESULTS on RESULTS.ID = SALESORDERPAYMENT.APPUSERID
                    where SALESORDERPAYMENT.SALESORDERID = DISCOUNTS_CTE.SALESORDERID
                )
                or DISCOUNTS_CTE.APPUSERID in (select ID from @USERQUERYRESULTS)
            )
    )
    select
        DISCOUNTSUSED.APPUSERID USERID,
        case
            when SALESMETHODTYPECODE = 2 then 'Online'
            when APPUSERID is null then 'Deleted user'
            else dbo.UFN_APPUSER_GETNAME(DISCOUNTSUSED.APPUSERID)
        end USERNAME,
        coalesce(LATESTDISCOUNT.ID, DISCOUNTSUSED.DISCOUNTID) DISCTID, -- Membership promos do not exist in the DISCOUNT table.

        DISCOUNTSUSED.DISCOUNTNAME,
        sum(DISCOUNTSUSED.AMOUNTAPPLIED) TOTALVALUEFORUSER,
        convert(int, sum(DISCOUNTSUSED.TIMESAPPLIED)) TIMESUSERAPPLIED
    from FILTEREDDISCOUNTS_CTE DISCOUNTSUSED
    left join dbo.DISCOUNT on DISCOUNT.ID = DISCOUNTSUSED.DISCOUNTID
    left join dbo.DISCOUNT LATESTDISCOUNT on (LATESTDISCOUNT.ORIGINALDISCOUNTID = DISCOUNT.ORIGINALDISCOUNTID and LATESTDISCOUNT.SUPERSEDEDBYID is null)  -- this doesn't really do anything because historical discounts are not stored.

    where DISCOUNTSUSED.TIMESAPPLIED > 0
    group by SALESMETHODTYPECODE, APPUSERID, DISCOUNTSUSED.DISCOUNTID, DISCOUNTSUSED.DISCOUNTNAME, LATESTDISCOUNT.ID
    option (recompile);