USP_REPORT_GROUPSALESREVENUE

create procedure dbo.USP_REPORT_GROUPSALESREVENUE
(
    @STARTDATE date = null,
    @ENDDATE date = null,
    @ONLYOUTSTANDINGBALANCE bit = 0,
    @ONLYNOPAYMENT bit = 0,
    @INCLUDECANCELLEDRESERVATIONS bit = 0,
    @GROUPTYPEFILTEROPTION tinyint = 0,
    @INCLUDERESERVATIONSWITHNOGROUPTYPE bit = 0,
    @GROUPTYPES xml = null,
    @CURRENTAPPUSERID uniqueidentifier = null
)
as
    set nocount on;

    declare @GRANTEDRESERVATIONPAGE bit = 1;

    if not @CURRENTAPPUSERID is null and not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 begin
        set @GRANTEDRESERVATIONPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'C8E970CA-858A-4066-AD34-DC049A2A2DE7');
    end

    declare @FILTEREDGROUPTYPES dbo.UDT_GENERICID;

    if @GROUPTYPEFILTEROPTION = 1 begin  -- Specific group types

        insert into @FILTEREDGROUPTYPES
        select GT.c.value('(@ID)[1]', 'uniqueidentifier')
        from @GROUPTYPES.nodes('/GROUPTYPES/ITEM') as GT(c);
    end

    select
        RESERVATION.NAME,
        case
            when @GRANTEDRESERVATIONPAGE = 1 then 'http://www.blackbaud.com/RESERVATIONID?RESERVATIONID=' + CONVERT(nvarchar(36), [RESERVATION].ID)
            else null
        end RESERVATIONURL,
        SALESORDER.STATUSCODE,
        dbo.UFN_RESERVATION_TOTALVISITORCOUNT(RESERVATION.ID) TOTALVISITORCOUNT,
        RESERVATION.ARRIVALDATE,
        TOTALS.REFUNDS,
        TOTALS.OVERAGEKEPT,
        TOTALS.TOTALDISCOUNTS,
        TOTALS.TOTAL,
        TOTALS.BALANCE,
        TOTALS.AMOUNTPAID,
        case when TOTALS.BALANCE < 0 then TOTALS.BALANCE else 0 end as BALANCEYOUOWE,
        case when TOTALS.BALANCE > 0 then TOTALS.BALANCE else 0 end as BALANCETHEYOWE,
        CONTACTCONSTITUENT.NAME as CONTACTNAME,
        EMAILADDRESS.EMAILADDRESS as CONTACTEMAIL,
        EMAILADDRESS.DONOTEMAIL as CONTACTDONOTEMAIL,
        PHONE.NUMBER as CONTACTNUMBER,
        PHONE.DONOTCALL as CONTACTDONOTCALL,
        PHONE.ISCONFIDENTIAL as CONTACTCONFIDENTIALPHONE,
        dbo.UFN_RESERVATION_GETGROUPTYPELIST(RESERVATION.ID, N', ') as GROUPTYPELIST
    from
        dbo.RESERVATION
    inner join
        dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
    outer apply
        dbo.UFN_SALESORDER_CONTACTRECORDS(RESERVATION.ID) as CONTACTRECORDS
    left outer join
        dbo.EMAILADDRESS on EMAILADDRESS.ID = CONTACTRECORDS.EMAILADDRESSID
    left outer join
        dbo.PHONE on PHONE.ID = CONTACTRECORDS.PHONEID
    outer apply
        dbo.UFN_SALESORDER_TOTALS(SALESORDER.ID) TOTALS
    outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(SALESORDER.RECIPIENTID) as CONTACTCONSTITUENT
    where
        (
            @STARTDATE is null
            or RESERVATION.ARRIVALDATE >= @STARTDATE
        )
        and (
            @ENDDATE is null
            or RESERVATION.ARRIVALDATE <= @ENDDATE
        )
        and (
            @ONLYNOPAYMENT = 0
            or TOTALS.NUMBEROFPAYMENTS = 0
        )
        and (
            @ONLYOUTSTANDINGBALANCE = 0
            or TOTALS.BALANCE <> 0
        )
        and (
            @INCLUDECANCELLEDRESERVATIONS = 1
            or SALESORDER.STATUSCODE <> 5  -- Cancelled

        )
        and (
            @GROUPTYPEFILTEROPTION = 0  -- All

            or exists (
                select
                    *
                from
                    dbo.ITINERARY
                where
                    ITINERARY.RESERVATIONID = RESERVATION.ID
                    and (
                        ITINERARY.GROUPSALESGROUPTYPECODEID in (
                            select ID from @FILTEREDGROUPTYPES
                        )
                        or (@INCLUDERESERVATIONSWITHNOGROUPTYPE = 1 and ITINERARY.GROUPSALESGROUPTYPECODEID is null)
                    )
            )
            or (
                @INCLUDERESERVATIONSWITHNOGROUPTYPE = 1
                and not exists (
                    select *
                    from dbo.ITINERARY
                    where ITINERARY.RESERVATIONID = RESERVATION.ID
                ) 
            )
        )
    order by
        RESERVATION.ARRIVALDATE, RESERVATION.NAME