USP_DATALIST_ORDERDETAILS

CREATE procedure dbo.USP_DATALIST_ORDERDETAILS(@CONTEXTID uniqueidentifier)
as
    set nocount on;

    declare @INDENT char(4)
    set @INDENT = '    '

    declare @ORDERSUBTOTAL money;
    declare @ORDERTOTAL money;
    declare @TOTALTAXES money;
    declare @AMOUNTTENDERED money;
    declare @CHANGEDUE money;

    select
        @ORDERSUBTOTAL = SUBTOTAL,
        @ORDERTOTAL = TOTAL,
        @TOTALTAXES = TAXES,
        @AMOUNTTENDERED = AMOUNTTENDERED,
        @CHANGEDUE = CHANGEDUE
    from
        dbo.UFN_SALESORDER_TOTALS(@CONTEXTID);

    select ITEMID, ID, DESCRIPTION, QUANTITY, TOTAL, ITEMTYPE, ISPARENT, ISBOLD
    from

    (
        -- Daily admission tickets

        select
            distinct [PROGRAM].[ID] as [ITEMID],
            (select top 1 [SALESORDERITEM].[ID]
                from dbo.[SALESORDERITEM] 
                    inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
                where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
            ) as [ID],
            [PROGRAM].[NAME] as [DESCRIPTION],
            null as [QUANTITY],
            null as [TOTAL],
            0 as [ITEMTYPE],
            1 as [ISPARENT],
            0 AS [ISBOLD]
        from dbo.[PROGRAM]
            inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
            inner join dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
        where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and 
                [SALESORDERITEM].[QUANTITY] > 0 and 
                [PROGRAM].[ISDAILYADMISSION] = 1

        union all

        select
            [PROGRAM].[ID] as [ITEMID],
            [SALESORDERITEM].[ID],
            (@INDENT + [PRICETYPECODE].[DESCRIPTION]) as [DESCRIPTION],
            [SALESORDERITEM].[QUANTITY] as [QUANTITY],
            [SALESORDERITEM].[PRICE] * [SALESORDERITEM].[QUANTITY] as [TOTAL],
            0 as [ITEMTYPE],
            0 as [ISPARENT],
            0 AS [ISBOLD]
        from dbo.[SALESORDERITEM]
            inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
            inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
            inner join dbo.[PROGRAM] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
        where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and 
                [SALESORDERITEM].[QUANTITY] > 0 and 
                [PROGRAM].[ISDAILYADMISSION] = 1

        -- Scheduled program tickets

        union all
        select
            distinct [EVENT].[ID] as [ITEMID],
            (select top 1 [SALESORDERITEM].[ID] 
                from dbo.[SALESORDERITEM] 
                    inner join dbo.[SALESORDERITEMTICKET] 
                on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
                where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and [SALESORDERITEMTICKET].[EVENTID] = [EVENT].ID
            ) as [ID],
            [EVENT].[NAME] + ' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')' as [DESCRIPTION],
            null as [QUANTITY],
            null as [TOTAL],
            2 as [ITEMTYPE],
            1 as [ISPARENT],
            0 AS [ISBOLD]
        from dbo.[EVENT]
            inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
            inner join dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
        where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and 
            [SALESORDERITEM].[QUANTITY] > 0

        union all

        select
            [EVENT].[ID] as [ITEMID],
            [SALESORDERITEM].[ID],
            (@INDENT + [PRICETYPECODE].[DESCRIPTION]) as [DESCRIPTION],
            [SALESORDERITEM].[QUANTITY] as [QUANTITY],
            [SALESORDERITEM].[PRICE] * [SALESORDERITEM].[QUANTITY] as [TOTAL],
            2 as [ITEMTYPE],
            0 as [ISPARENT],
            0 AS [ISBOLD]
        from dbo.[SALESORDERITEM]
            inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
            inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
            inner join dbo.[EVENT] on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
        where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and 
            [SALESORDERITEM].[QUANTITY] > 0

        -- Memberships

        union all
        select
            [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPID] as [ITEMID],
            [SALESORDERITEM].[ID] as [ID],
            [MEMBERSHIPPROGRAM].[NAME] as [DESCRIPTION],
            null as [QUANTITY],
            null as [TOTAL],
            3 as [ITEMTYPE],
            1 as [ISPARENT],
            0 AS [ISBOLD]
        from dbo.[SALESORDER]
            inner join dbo.[SALESORDERITEM] on [SALESORDER].[ID] = [SALESORDERITEM].[SALESORDERID]
            inner join dbo.[SALESORDERITEMMEMBERSHIP] on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]
            inner join dbo.[MEMBERSHIPPROGRAM] on [MEMBERSHIPPROGRAM].[ID] = [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMID]
            left join dbo.[MEMBERSHIP] on [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
        where [SALESORDER].[ID] = @CONTEXTID      

        union all

        select
            [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPID] as [ITEMID],
            [SALESORDERITEM].[ID] as [ID],
            (@INDENT + right([SALESORDERITEM].[DESCRIPTION], len([SALESORDERITEM].[DESCRIPTION]) - charindex('-', [SALESORDERITEM].[DESCRIPTION]) - 1)) as [DESCRIPTION],
            null as [QUANTITY],
            [SALESORDERITEM].[TOTAL],
            3 as [ITEMTYPE],
            0 as [ISPARENT],
            0 AS [ISBOLD]
        from dbo.[SALESORDER]
            inner join dbo.[SALESORDERITEM] on [SALESORDER].[ID] = [SALESORDERITEM].[SALESORDERID]
            inner join dbo.[SALESORDERITEMMEMBERSHIP] on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]
            left join dbo.[MEMBERSHIP] on [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
        where [SALESORDER].[ID] = @CONTEXTID

        -- Event registrant

        union all
        select 
            [SALESORDERITEM].[ID] as [ITEMID],
            [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] as [ID],
            [EVENT].[NAME] + ' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')' as [DESCRIPTION],
            null as [QUANTITY],
            null as [TOTAL],
            4 as [ITEMTYPE],
            1 as [ISPARENT],
            0 AS [ISBOLD]
        from dbo.[SALESORDERITEM]
            inner join dbo.[SALESORDERITEMEVENTREGISTRATION] on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
            inner join dbo.[REGISTRANT] on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
            inner join dbo.[EVENT] on [REGISTRANT].[EVENTID] = [EVENT].[ID]
        where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID

        union all

        select 
            [SALESORDERITEM].[ID] as [ITEMID],
            [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] as [ID],
            (@INDENT + [EVENTPRICE].[NAME]) as [DESCRIPTION],
            [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[QUANTITY],
            [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[AMOUNT] as [TOTAL],
            4 as [ITEMTYPE],
            0 as [ISPARENT],
            0 AS [ISBOLD]
        from dbo.[SALESORDERITEM]
            inner join dbo.[SALESORDERITEMEVENTREGISTRATION] on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
            inner join dbo.[SALESORDERITEMEVENTREGISTRANTREGISTRATION] on [SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[SALESORDERITEMEVENTREGISTRATIONID]
            inner join dbo.[REGISTRANT] on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
            inner join dbo.[EVENT] on [REGISTRANT].[EVENTID] = [EVENT].[ID]
            inner join dbo.[EVENTPRICE] on [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[EVENTPRICEID] = [EVENTPRICE].[ID]
        where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID

        -- Donations

        union all
        select
            [SALESORDERITEM].[ID] as [ITEMID],
            [SALESORDERITEM].[ID],
            [SALESORDERITEM].[DESCRIPTION],
            [SALESORDERITEM].[QUANTITY],
            [SALESORDERITEM].[TOTAL],
            5 as [ITEMTYPE],
            1 as [ISPARENT],
            0 AS [ISBOLD]
        from dbo.[SALESORDERITEM]
            inner join dbo.[SALESORDERITEMDONATION] on [SALESORDERITEM].[ID] = [SALESORDERITEMDONATION].[ID]
        where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID

        -- Delivery method and fees

        union all
        select
            [SALESORDER].[DELIVERYMETHODID] as [ITEMID],
            [SALESORDER].[DELIVERYMETHODID] as [ID],
            'Delivery method - ' + dbo.UFN_DELIVERYMETHOD_GETNAME([SALESORDER].[DELIVERYMETHODID]) as [DESCRIPTION],
            null as [QUANTITY],
            null as [TOTAL],
            6 as [ITEMTYPE],
            1 as [ISPARENT],
            0 AS [ISBOLD]
        from dbo.[SALESORDER]
        where [SALESORDER].[ID] = @CONTEXTID

        union all

        select
            [SALESORDER].[DELIVERYMETHODID] as [ITEMID],
            [SALESORDERITEM].[ID],
            (@INDENT + [SALESORDERITEM].[DESCRIPTION]) as [DESCRIPTION],
            null as [QUANTITY],
            [SALESORDERITEM].[TOTAL] as [TOTAL],
            6 as [ITEMTYPE],
            0 as [ISPARENT],
            0 AS [ISBOLD]
        from dbo.[SALESORDERITEM]
            inner join dbo.[SALESORDERITEMFEE] on [SALESORDERITEM].[ID] = [SALESORDERITEMFEE].[ID]
            inner join dbo.[SALESORDER] on [SALESORDER].[ID] = [SALESORDERITEM].[SALESORDERID]
        where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and
            [SALESORDERITEM].[TYPECODE] = 3 and
            [SALESORDERITEMFEE].[APPLIESTOCODE] = 0 and
            [SALESORDERITEMFEE].[TYPECODE] = 0

        -- Fees

        union all
        select
            [SALESORDERITEM].[ID] as [ITEMID],
            [SALESORDERITEM].[ID],
            [SALESORDERITEM].[DESCRIPTION] as [DESCRIPTION],
            null as [QUANTITY],
            [SALESORDERITEM].[TOTAL] as [TOTAL],
            7 as [ITEMTYPE],
            0 as [ISPARENT],
            0 AS [ISBOLD]
        from dbo.[SALESORDERITEM]
            inner join dbo.[SALESORDERITEMFEE] on [SALESORDERITEM].[ID] = [SALESORDERITEMFEE].[ID]
        where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and
            [SALESORDERITEM].[TYPECODE] = 3 and
            [SALESORDERITEMFEE].[APPLIESTOCODE] = 0 and
            [SALESORDERITEMFEE].[TYPECODE] <> 0

        -- Discounts

        union all
        select
            [SALESORDERITEM].[ID] as [ITEMID],
            [SALESORDERITEM].[ID],
            [SALESORDERITEM].[DESCRIPTION],
            null as [QUANTITY],
            (-1 * [SALESORDERITEM].[TOTAL]) as [TOTAL],
            8 as [ITEMTYPE],
            1 as [ISPARENT],
            0 AS [ISBOLD]
        from dbo.[SALESORDERITEM]
        where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and
            [SALESORDERITEM].[TYPECODE] = 5

        -- Subtotal

        union all
        select 
            null as [ITEMID],
            @CONTEXTID as [ID],
            'Subtotal' as [DESCRIPTION],
            null as [QUANTITY],
            @ORDERSUBTOTAL as [TOTAL],
            9 as [ITEMTYPE],
            1 as [ISPARENT],
            1 AS [ISBOLD]
      where @ORDERSUBTOTAL > 0

        -- Taxes

        union all
        select
            [SALESORDERITEM].[ID] as [ITEMID],
            [SALESORDERITEM].[ID],
            [SALESORDERITEM].[DESCRIPTION],
            null as [QUANTITY],
            [SALESORDERITEM].[TOTAL],
            10 as [ITEMTYPE],
            1 as [ISPARENT],
            0 AS [ISBOLD]
        from dbo.[SALESORDERITEM]
        where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and
            [SALESORDERITEM].[TYPECODE] = 4 and 
            [SALESORDERITEM].[TOTAL] > 0

        -- Total

        union all
        select 
            null as [ITEMID],
            @CONTEXTID as [ID],
            'Total' as [DESCRIPTION],
            null as [QUANTITY],
            @ORDERTOTAL as [TOTAL],
            11 as [ITEMTYPE],
            1 as [ISPARENT],
            1 AS [ISBOLD]
      where @ORDERTOTAL > 0

        -- White space

        union all 
        select
            null as [ITEMID],
            @CONTEXTID as [ID],
            null as [DESCRIPTION],
            null as [QUANTITY],
            null as [TOTAL],
            12 as [ITEMTYPE],
            1 as [ISPARENT],
            0 AS [ISBOLD]
        union all 
        select
            null as [ITEMID],
            @CONTEXTID as [ID],
            null as [DESCRIPTION],
            null as [QUANTITY],
            null as [TOTAL],
            12 as [ITEMTYPE],
            1 as [ISPARENT],
            0 AS [ISBOLD]

        -- Payment label

        union all
        select
            null as [ITEMID],
            @CONTEXTID as [ID],
            'Paid' as [DESCRIPTION],
            null as [QUANTITY],
            null as [TOTAL],
            13 as [ITEMTYPE],
            1 as [ISPARENT],
            1 AS [ISBOLD]

        -- Payments

        union all
        select 
            SALESORDERPAYMENT.ID as [ITEMID],
            SALESORDERPAYMENT.ID as [ID],
            --SALESORDERPAYMENT.PAYMENTID,

            (@INDENT + case when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 0
                          then REVENUEPAYMENTMETHOD.PAYMENTMETHOD
                        when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 1 
                          then (select 'Check: #' + CHECKNUMBER + ', ' + convert(nvarchar(20), dbo.UFN_DATE_FROMFUZZYDATE(CHECKDATE), 101)
                                        from dbo.CHECKPAYMENTMETHODDETAIL
                                        where ID = REVENUEPAYMENTMETHOD.ID)
                              when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 
                          then (select dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITTYPECODEID) + ': ' + CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER
                                        from dbo.CREDITCARDPAYMENTMETHODDETAIL 
                                        where ID = REVENUEPAYMENTMETHOD.ID)
                              when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10 
                          then REVENUEPAYMENTMETHOD.PAYMENTMETHOD + ': ' + 
                              (select dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODCODEID)
                                        from dbo.OTHERPAYMENTMETHODDETAIL
                                        where ID = REVENUEPAYMENTMETHOD.ID)
            end) as [DESCRIPTION],
            null as [QUANTITY],
            SALESORDERPAYMENT.AMOUNTTENDERED as [TOTAL],
            14 as [ITEMTYPE],
            0 as [ISPARENT],
            0 AS [ISBOLD]
        from dbo.REVENUE
            inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
            inner join dbo.SALESORDERPAYMENT on REVENUE.ID = SALESORDERPAYMENT.PAYMENTID
            left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
        where SALESORDERPAYMENT.SALESORDERID = @CONTEXTID

        -- Payment total label

        union all
        select 
            null as [ITEMID],
            @CONTEXTID as [ID],
            'Total Paid' as [DESCRIPTION],
            null as [QUANTITY],
            @AMOUNTTENDERED as [TOTAL],
            15 as [ITEMTYPE],
            1 as [ISPARENT],
            1 AS [ISBOLD]

        -- Change due label

        union all
        select 
            null as [ITEMID],
            @CONTEXTID as [ID],
            'Change Due' as [DESCRIPTION],
            null as [QUANTITY],
            @CHANGEDUE as [TOTAL],
            16 as [ITEMTYPE],
            1 as [ISPARENT],
            0 AS [ISBOLD]
        where @CHANGEDUE > 0


    ) as SALESORDERITEMS

    order by ITEMTYPE asc, ITEMID asc, ISPARENT desc