USP_DATALIST_GROUPSALESORDERREPORT

CREATE procedure dbo.USP_DATALIST_GROUPSALESORDERREPORT(
    @RESERVATIONID uniqueidentifier,
    @RESOURCEGROUPING tinyint = 0   -- when 0, this allows for grouping by resource category (i.e, Equipment), when 1, by resource name (i.e., Projector)

)
as
    set nocount on;

    declare @TABLE table(
        QUANTITY decimal,
        DESCRIPTION nvarchar(255),
        ISFACILITYRENTAL tinyint,
        ITEMTYPECODE tinyint
    );

    insert into @TABLE
    -- Tickets

    select
        QUANTITY,
        [DESCRIPTION],
        0 as ISFACILITYRENTAL,
        0 as ITEMTYPECODE
    from dbo.SALESORDERITEM as SOI
    where SOI.SALESORDERID = @RESERVATIONID and SOI.TYPECODE = 0  -- Ticket


    union all
    -- Resources

    -- Itinerary Resources

    select
        case RES.ISPERTICKETITEM
            when 0 then
                IR.QUANTITYNEEDED
            else
                dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(dbo.UFN_ITINERARY_TOTALVISITORCOUNT(IR.ITINERARYID) * IR.PERTICKETQUANTITY, IR.PERTICKETDIVISOR)
        end as QUANTITY,
        case @RESOURCEGROUPING
            when 1 then
                [RES].[NAME]
            else 
                dbo.UFN_RESOURCE_GETCATEGORY([RES].[ID])
        end as DESCRIPTION,
        0 as ISFACILITYRENTAL,
        1 as ITEMTYPECODE
    from dbo.ITINERARYRESOURCE as IR
    inner join dbo.SALESORDERITEMITINERARYRESOURCE as SOIIR on SOIIR.ITINERARYRESOURCEID = IR.ID
    inner join dbo.SALESORDERITEM as SOI on SOI.ID = SOIIR.SALESORDERITEMID
    inner join dbo.RESOURCE as RES on RES.ID = IR.RESOURCEID
    where SOI.SALESORDERID = @RESERVATIONID and SOI.PRICE > 0.00

    union all 
    -- Itinerary Item Resources

    select
        case RES.ISPERTICKETITEM
                when 0 then
                    IIR.QUANTITYNEEDED
                else
                    dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(dbo.UFN_ITINERARY_TOTALVISITORCOUNT(II.ITINERARYID) * IIR.PERTICKETQUANTITY, IIR.PERTICKETDIVISOR)
            end as QUANTITY,
        case @RESOURCEGROUPING
            when 1 then
                [RES].[NAME]
            else 
                dbo.UFN_RESOURCE_GETCATEGORY([RES].[ID])
        end as DESCRIPTION,
        0 as ISFACILITYRENTAL,
        1 as ITEMTYPECODE
    from dbo.ITINERARYITEM as II
    inner join dbo.ITINERARYITEMRESOURCE as IIR on II.ID = IIR.ITINERARYITEMID
    inner join dbo.SALESORDERITEMITINERARYITEMRESOURCE as SOIIIR on SOIIIR.ITINERARYITEMRESOURCEID = IIR.ID
    inner join dbo.SALESORDERITEM as SOI on SOI.ID = SOIIIR.SALESORDERITEMID
    inner join dbo.RESOURCE as RES on RES.ID = IIR.RESOURCEID
    where SOI.SALESORDERID = @RESERVATIONID and SOI.PRICE > 0.00

    union all
    -- Itinerary Staff Resources 

    select
        QUANTITYNEEDED QUANTITY,
        case @RESOURCEGROUPING
            when 1 then
                isnull(VOLUNTEERTYPE.NAME, 'Other Staff')
            else
                'Staff'
        end as DESCRIPTION,
        0 as ISFACILITYRENTAL,
        2 as ITEMTYPECODE
    from dbo.ITINERARYSTAFFRESOURCE as ISR
    inner join dbo.SALESORDERITEMITINERARYSTAFFRESOURCE as SOIISR on SOIISR.ITINERARYSTAFFRESOURCEID = ISR.ID
    inner join dbo.SALESORDERITEM as SOI on SOI.ID = SOIISR.SALESORDERITEMID
    left join dbo.VOLUNTEERTYPE on VOLUNTEERTYPE.ID = ISR.VOLUNTEERTYPEID
    where SOI.SALESORDERID = @RESERVATIONID and SOI.PRICE > 0.00

    union all
    -- Itinerary Item Staff Resources

    select
        QUANTITYNEEDED QUANTITY,
        case @RESOURCEGROUPING
            when 1 then
                isnull(VOLUNTEERTYPE.NAME, 'Other Staff')
            else
                'Staff'
        end as DESCRIPTION,
        0 as ISFACILITYRENTAL,
        2 as ITEMTYPECODE
    from dbo.SALESORDERITEM as SOI
    inner join dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE as SOIIISR on SOIIISR.SALESORDERITEMID = SOI.ID
    inner join dbo.ITINERARYITEMSTAFFRESOURCE as IISR on IISR.ID = SOIIISR.ITINERARYITEMSTAFFRESOURCEID
    left join dbo.VOLUNTEERTYPE on VOLUNTEERTYPE.ID = IISR.VOLUNTEERTYPEID
    where SOI.SALESORDERID = @RESERVATIONID and SOI.PRICE > 0.00

    union all
    select
        1,
        'Facility - ' + dbo.UFN_EVENTLOCATION_GETNAME(II.EVENTLOCATIONID) [DESCRIPTION],
        1 as ISFACILITYRENTAL,
        3 as ITEMTYPECODE
    from dbo.ITINERARY as I
    inner join dbo.ITINERARYITEM as II on II.ITINERARYID = I.ID
    inner join dbo.ITINERARYITEMLOCATION as IIL on IIL.ID = II.ID
    where I.RESERVATIONID = @RESERVATIONID

    union all
    select
        1,
        SALESORDERITEMITEMDISCOUNT.DISCOUNTNAME,
        0 as ISFACILITYRENTAL,
        4 as ITEMTYPECODE
    from dbo.SALESORDERITEM
    inner join dbo.SALESORDERITEMITEMDISCOUNT on SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
    where SALESORDERITEM.SALESORDERID = @RESERVATIONID

    union all
    select
        1,
        SALESORDERITEMORDERDISCOUNT.DISCOUNTNAME,
        0 as ISFACILITYRENTAL,
        4 as ITEMTYPECODE
    from dbo.SALESORDERITEM
    inner join dbo.SALESORDERITEMORDERDISCOUNT on SALESORDERITEMORDERDISCOUNT.ID = SALESORDERITEM.ID
    where SALESORDERITEM.SALESORDERID = @RESERVATIONID;

    declare @TOTALVISITORCOUNT integer = coalesce([dbo].UFN_RESERVATION_TOTALVISITORCOUNT(@RESERVATIONID), 0);
    declare @CONTACTNAME nvarchar(154);
    declare @CONSTITUENTNAME nvarchar(154);
    declare @ORDERTOTALPREDISCOUNT money;
    declare @ORDERAMOUNT money;
    declare @TOTALDISCOUNTS money;
    declare @OVERAGEKEPT money;

    select @ORDERTOTALPREDISCOUNT = coalesce(sum(SALESORDERITEM.TOTAL), 0)
    from dbo.SALESORDERITEM
    where SALESORDERITEM.SALESORDERID = @RESERVATIONID and
            SALESORDERITEM.TYPECODE not in (5, 13)  -- Discount, Membership Promotion


    select
        @CONTACTNAME = dbo.UFN_CONSTITUENT_BUILDNAME(SO.RECIPIENTID),
        @CONSTITUENTNAME = dbo.UFN_CONSTITUENT_BUILDNAME(SO.CONSTITUENTID),
        @ORDERAMOUNT = SO.AMOUNT
    from dbo.SALESORDER SO
    where ID = @RESERVATIONID;

    set @TOTALDISCOUNTS = @ORDERTOTALPREDISCOUNT - @ORDERAMOUNT
    if @TOTALDISCOUNTS < 0
        set @TOTALDISCOUNTS = @ORDERAMOUNT;

    declare @SECURITYDEPOSITBALANCE money;
    set @SECURITYDEPOSITBALANCE = dbo.UFN_RESERVATION_GETSECURITYDEPOSITAMOUNTDUE(@RESERVATIONID);

    declare @LATESTDEPOSITPAYMENT date;
    select top(1) @LATESTDEPOSITPAYMENT = PAYMENTDATEWITHTIMEOFFSET 
    from dbo.RESERVATIONSECURITYDEPOSITPAYMENT
    where RESERVATIONID = @RESERVATIONID
    order by PAYMENTDATEWITHTIMEOFFSET desc;

    -- Security deposit info

    declare @SECURITYDEPOSITREFUNDEDAMOUNT money;
    declare @SECURITYDEPOSITREFUNDEDDATE date;
    declare @SECURITYDEPOSITWITHHELDREASON nvarchar(100);
    declare @SECURITYDEPOSITSTATUSCODE tinyint;

    select
        @SECURITYDEPOSITWITHHELDREASON = CODE.DESCRIPTION,
        @SECURITYDEPOSITSTATUSCODE = RES.SECURITYDEPOSITSTATUSCODE
    from 
        dbo.RESERVATION RES
    left outer join
        dbo.RESERVATIONSECURITYDEPOSITWITHHOLDREASONCODE CODE on RES.SECURITYDEPOSITWITHHOLDREASONCODEID = CODE.ID
    where
        RES.ID = @RESERVATIONID;

    set @SECURITYDEPOSITREFUNDEDAMOUNT = 
        case  
            when @SECURITYDEPOSITSTATUSCODE = 2 or @SECURITYDEPOSITSTATUSCODE = 4 then (
                select sum(CREDITPAYMENT.AMOUNT)
                from dbo.UFN_SALESORDER_REFUNDS(@RESERVATIONID) as REFUNDS
                inner join dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = REFUNDS.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as REFUNDEDLI on REFUNDEDLI.ID = CREDITPAYMENT.REVENUESPLITID
                inner join dbo.REVENUESPLIT_EXT as EXT on EXT.ID = REFUNDEDLI.ID
                where EXT.TYPECODE = 13  -- Security deposit

            )
            else 0
        end;

    select top(1)
        @SECURITYDEPOSITREFUNDEDDATE = cast(FT.DATE as datetime)
    from
        dbo.UFN_SALESORDER_REFUNDS(@RESERVATIONID) as REFUNDS
    inner join
        dbo.FINANCIALTRANSACTION as FT on FT.ID = REFUNDS.ID
    inner join
        dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = REFUNDS.ID
    inner join 
        dbo.FINANCIALTRANSACTIONLINEITEM as REFUNDEDLI on REFUNDEDLI.ID = CREDITPAYMENT.REVENUESPLITID
    inner join
        dbo.REVENUESPLIT_EXT as EXT on EXT.ID = REFUNDEDLI.ID
    where 
        EXT.TYPECODE = 13  -- Security deposit

    order by
        FT.DATE desc;

    select @OVERAGEKEPT = sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT)
    from dbo.SALESORDER
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on SALESORDER.REVENUEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
    where SALESORDER.ID = @RESERVATIONID
    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
    and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0
    and REVENUESPLIT_EXT.TYPECODE = 20;

    with TABLE_CTE as (
        select sum(QUANTITY) QUANTITY,
            DESCRIPTION,
            ISFACILITYRENTAL,
            ITEMTYPECODE
        from @TABLE
        group by DESCRIPTION, ISFACILITYRENTAL, ITEMTYPECODE
    ),
    REFUNDS_CTE as (
        select 
            CREDITPAYMENT.CREDITID as CREDITID,
            CONVERT(date, CREDITPAYMENT.CREDITPAYMENTDATEWITHTIMEOFFSET) as CREDITDATE,
            case CREDITPAYMENT.PAYMENTMETHODCODE
                when 10 then -- 'Other' refunds need information from a different table

                    OTHERPAYMENTMETHODCODE.DESCRIPTION
                else
                    CREDITPAYMENT.PAYMENTMETHOD
            end as CREDITMETHOD,
            CREDITPAYMENT.AMOUNT as CREDITAMOUNT,
            CREDITREASONCODE.DESCRIPTION as CREDITREASON,
            CREDITPAYMENT.REVENUEID as REVENUEID
        from
            dbo.CREDITPAYMENT
        left join
            dbo.OTHERPAYMENTMETHODCODE on OTHERPAYMENTMETHODCODE.ID = CREDITPAYMENT.OTHERPAYMENTMETHODCODEID
        inner join
            dbo.CREDIT_EXT on CREDIT_EXT.ID = CREDITPAYMENT.CREDITID
        left join
            dbo.CREDITREASONCODE on CREDITREASONCODE.ID = CREDIT_EXT.CREDITREASONCODEID
    )
    select 
        [SO].[SEQUENCEID] ORDERNUMBER,
        @CONTACTNAME CONTACTNAME,
        @TOTALVISITORCOUNT VISITORCOUNT,
        [R].[ARRIVALDATE] ARRIVALDATE,
        @ORDERTOTALPREDISCOUNT TOTALPRICE,
        [SOP].[ID] SALESORDERPAYMENTID,
        [SOP].[AMOUNT] PAYMENT,
        [SOP].[DATEADDED] PAYMENTDATE,
        [SOP].[ID] PAYMENTID,
        @CONSTITUENTNAME CONSTITUENTNAME,
        [R].[FINALDUEDATE] DUEDATE,
        (
            select sum(AMOUNT)
            from dbo.SALESORDERPAYMENT
            where SALESORDERID = @RESERVATIONID
        ) as TOTALPAYMENTAMOUNT,
        [RPM].[PAYMENTMETHOD] PAYMENTMETHOD,
        case
            when [RPM].[PAYMENTMETHODCODE] = 0 then
                ''
            when [RPM].[PAYMENTMETHODCODE] = 1 then (
                select 'Check #' + CHECKNUMBER + ', ' + convert(nvarchar(20), dbo.UFN_DATE_FROMFUZZYDATE(CHECKDATE), 101)
                from dbo.CHECKPAYMENTMETHODDETAIL
                where ID = [RPM].[ID]
            )
            when [RPM].[PAYMENTMETHODCODE] = 2 then (
                select dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITTYPECODEID) + ' - ' + CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER
                from dbo.CREDITCARDPAYMENTMETHODDETAIL 
                where ID = [RPM].[ID]
            )
            when [RPM].[PAYMENTMETHODCODE] = 10 then (
                select dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODCODEID)
                from dbo.OTHERPAYMENTMETHODDETAIL
                where ID = [RPM].[ID]
            )
        end as PAYMENTDETAILS,
        [T].[QUANTITY] QUANTITY,
        [T].[DESCRIPTION] [DESCRIPTION],
        coalesce([OI].[NAME],'') ORGANIZATIONNAME,
        coalesce([OI].[ADDRESSBLOCK],'') ADDRESSBLOCK,
        coalesce([OI].[CITY],'') [CITY],
        coalesce([ST].[ABBREVIATION],'') [STATE],
        coalesce([OI].[POSTCODE],'') POSTCODE,
        coalesce([OI].[PHONENUMBER],'') PHONENUMBER,
        [T].[ISFACILITYRENTAL] ISFACILITYRENTAL,
        @TOTALDISCOUNTS DISCOUNTTOTAL,
        [T].[ITEMTYPECODE] ITEMTYPECODE,
        [R].[SECURITYDEPOSITREQUIRED] SECURITYDEPOSITREQUIRED,
        @SECURITYDEPOSITSTATUSCODE SECURITYDEPOSITSTATUSCODE,
        @SECURITYDEPOSITBALANCE SECURITYDEPOSITBALANCE,
        [R].[SECURITYDEPOSITAMOUNT] SECURITYDEPOSITDUE,
        [R].[SECURITYDEPOSITDUEDATE] SECURITYDEPOSITDUEDATE,
        @LATESTDEPOSITPAYMENT SECURITYDEPOSITPAIDDATE,
        @SECURITYDEPOSITREFUNDEDDATE SECURITYDEPOSITREFUNDEDDATE,
        @SECURITYDEPOSITREFUNDEDAMOUNT SECURITYDEPOSITREFUNDEDAMOUNT,
        @SECURITYDEPOSITWITHHELDREASON SECURITYDEPOSITWITHHELDREASON,
        SALESORDERTOTALS.REFUNDS as TOTALCREDITAMOUNT,
        REFUNDS_CTE.CREDITID,
        REFUNDS_CTE.CREDITDATE,
        REFUNDS_CTE.CREDITMETHOD,
        REFUNDS_CTE.CREDITAMOUNT,
        REFUNDS_CTE.CREDITREASON,
        SALESORDERTOTALS.BALANCE as BALANCE,
        @OVERAGEKEPT as OVERAGEKEPT,
        convert(varchar,convert(time,substring(nullif([R].[ARRIVALTIME],''),1,2)+':'+substring(nullif([R].[ARRIVALTIME],''),3,2)),0) ARRIVALTIME, 
        [R].[NAME] RESERVATIONNAME
    from [dbo].[SALESORDER] as SO
    inner join [dbo].[RESERVATION] as R on R.ID = [SO].[ID]
    left outer join [dbo].[CONSTITUENT] as C on [C].[ID] = [SO].[CONSTITUENTID]
    left outer join [dbo].[SALESORDERPAYMENT] as SOP on [SO].[ID] = [SOP].[SALESORDERID]
    left outer join [dbo].[ITINERARY] as I on [I].[RESERVATIONID] = [R].[ID]
    left outer join [dbo].[REVENUEPAYMENTMETHOD] as RPM on [RPM].[REVENUEID] = [SOP].[PAYMENTID]
    full outer join [dbo].[ORGANIZATIONINFORMATION] as OI on [R].ID = @RESERVATIONID
    full outer join TABLE_CTE as T on [R].ID = @RESERVATIONID
    left outer join [dbo].[STATE] as ST on ST.ID = OI.STATEID
    left outer join REFUNDS_CTE on REFUNDS_CTE.REVENUEID = [SOP].PAYMENTID
    outer apply dbo.UFN_SALESORDER_TOTALS(@RESERVATIONID) as SALESORDERTOTALS
    where [R].[ID] = @RESERVATIONID
    group by [SOP].[ID],[SOP].[AMOUNT],[SO].[AMOUNT],[R].[ARRIVALDATE],[R].[NAME],[SO].[SEQUENCEID],[I].[LEADERID],[SOP].[ID],
        [SOP].[DATEADDED],[C].[ID],[R].[FINALDUEDATE],[RPM].[PAYMENTMETHOD],[RPM].[PAYMENTMETHODCODE],[RPM].[ID],[T].[QUANTITY],
        [T].[DESCRIPTION],[OI].[NAME],[OI].[ADDRESSBLOCK],[OI].[CITY],[ST].[ABBREVIATION],[OI].[POSTCODE],[OI].[PHONENUMBER],
        [T].[ISFACILITYRENTAL], [T].[ITEMTYPECODE], [R].[SECURITYDEPOSITREQUIRED], [R].[SECURITYDEPOSITAMOUNT], [R].[SECURITYDEPOSITDUEDATE],
        REFUNDS_CTE.CREDITID, REFUNDS_CTE.CREDITDATE, REFUNDS_CTE.CREDITAMOUNT,
        REFUNDS_CTE.CREDITMETHOD, REFUNDS_CTE.CREDITREASON, SALESORDERTOTALS.REFUNDS,
        SALESORDERTOTALS.BALANCE, [R].[ARRIVALTIME], [R].[NAME]

    return 0;