USP_REPORT_TOTALREVENUE_PREVIOUSLYUNEARNED

create procedure dbo.USP_REPORT_TOTALREVENUE_PREVIOUSLYUNEARNED
(
    @FROMDATE date = null,
    @TODATE date = null,
    @INCLUDETAXES bit = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @APPUSERID uniqueidentifier = null,
    @APPUSERQUERYID uniqueidentifier = null,
    @SHOWUNEARNEDTOEARNED bit = null
)
as
    set nocount on;

    if isnull(@SHOWUNEARNEDTOEARNED, 0) = 0
        return;

    declare @USERGRANTEDTRANSACTIONPAGE bit = 0,
            @USERGRANTEDORDERPAGE bit = 0,
            @USERGRANTEDRESERVATIONPAGE bit = 0,
            @USERGRANTEDCONSTITUENTPAGE bit = 0;

    if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
    begin
        select
            @USERGRANTEDTRANSACTIONPAGE = 1,
            @USERGRANTEDORDERPAGE = 1,
            @USERGRANTEDRESERVATIONPAGE = 1,
            @USERGRANTEDCONSTITUENTPAGE = 1;
    end
    else
    begin
        select
            @USERGRANTEDTRANSACTIONPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'D00E6C42-2434-4D85-8A04-2323CA6BB2E7'),
            @USERGRANTEDORDERPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, '9C4D9D19-BB7E-4656-9B78-7EE1930C009A'),
            @USERGRANTEDRESERVATIONPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'C8E970CA-858A-4066-AD34-DC049A2A2DE7'),
            @USERGRANTEDCONSTITUENTPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, '0C836902-A398-47a0-91EB-8B66E434148E');
    end;

    declare @APPUSERQUERYRESULTS dbo.UDT_GENERICID;

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

    with RESINFO as (
        select
            RESERVATION.ID,
            dbo.UDA_BUILDLIST(GT.DESCRIPTION) GROUPTYPE,
            RESERVATION.NAME RESERVATIONNAME
        from dbo.RESERVATION
            left join dbo.ITINERARY on ITINERARY.RESERVATIONID = RESERVATION.ID
            left join dbo.GROUPSALESGROUPTYPECODE GT on GT.ID = ITINERARY.GROUPSALESGROUPTYPECODEID
        group by RESERVATION.ID, RESERVATION.ARRIVALDATE, RESERVATION.NAME
    )
    select ORDERREVENUE.ID ORDERREVENUEID,
        case
            when ORDERLI_EXT.TYPECODE = 20 then ORDERLI.POSTDATE
            else cast(ORDERREVENUE.DATE as datetime)
        end ORDERDATE,
        cast(SOP.PAYMENTDATEWITHTIMEOFFSET as date) PAYMENTDATE,
        C.NAME CONSTITUENTNAME,
        ORDERREVENUE_EXT.REFERENCE,
        REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
        PAYMENTLI.BASEAMOUNT AMOUNT,
        RESINFO.GROUPTYPE,
        RESINFO.RESERVATIONNAME,
        ORDERLI_EXT.APPLICATIONCODE,
        ORDERLI_EXT.TYPECODE REVENUETYPECODE,
        PROGRAM.NAME PROGRAMNAME,
        TAX.NAME TAXNAME,
        FEE.NAME FEENAME,
        EVENTLOCATION.NAME LOCATIONNAME,
        [RESOURCE].NAME RESOURCENAME,
        VOLUNTEERTYPE.NAME STAFFRESOURCENAME,
        case when @USERGRANTEDCONSTITUENTPAGE = 1 then 'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36), C.ID) else null end as CONSTITUENTLINK,
        case when @USERGRANTEDORDERPAGE = 1 then 'http://www.blackbaud.com/SALESORDERID?SALESORDERID=' + CONVERT(nvarchar(36), SO.ID) else null end as ORDERLINK,
        case when @USERGRANTEDTRANSACTIONPAGE = 1 then 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36), PAYMENT.ID) else null end as PAYMENTLINK,
        case when @USERGRANTEDRESERVATIONPAGE = 1 then 'http://www.blackbaud.com/GROUPSALESORDERID?GROUPSALESORDERID=' + CONVERT(nvarchar(36), SO.ID) else null end as RESERVATIONLINK,
        case when C.ISORGANIZATION = 1 then
            case C.KEYNAMEPREFIX
                when '' then C.KEYNAME
                else C.KEYNAME + ', ' + C.KEYNAMEPREFIX
            end
        else dbo.UFN_NAMEFORMAT_08(C.ID, C.KEYNAME, C.FIRSTNAME, C.MIDDLENAME, null, null, null, null, null, null, null)
        end as SORTCONSTITUENTNAME
    from dbo.FINANCIALTRANSACTION ORDERREVENUE
    inner join dbo.REVENUE_EXT ORDERREVENUE_EXT on ORDERREVENUE.ID = ORDERREVENUE_EXT.ID
    inner join dbo.SALESORDER SO on SO.REVENUEID = ORDERREVENUE.ID
    inner join RESINFO on RESINFO.ID = SO.ID
    inner join dbo.SALESORDERPAYMENT SOP on SOP.SALESORDERID = SO.ID
    inner join dbo.FINANCIALTRANSACTION PAYMENT on PAYMENT.ID = SOP.PAYMENTID
    inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = PAYMENT.ID
    inner join dbo.CONSTITUENT C on C.ID = SO.CONSTITUENTID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM ORDERLI on ORDERLI.FINANCIALTRANSACTIONID = ORDERREVENUE.ID
    inner join dbo.REVENUESPLIT_EXT ORDERLI_EXT on ORDERLI_EXT.ID = ORDERLI.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTLI on (PAYMENTLI.FINANCIALTRANSACTIONID = PAYMENT.ID and PAYMENTLI.SOURCELINEITEMID = ORDERLI.ID and PAYMENTLI.VISIBLE = 0)
    left join dbo.REVENUESPLITORDER RSO on RSO.ID = ORDERLI.ID
    left join dbo.PROGRAM on RSO.PROGRAMID = PROGRAM.ID
    left join dbo.TAX on RSO.TAXID = TAX.ID
    left join dbo.FEE on RSO.FEEID = FEE.ID
    left join dbo.VOLUNTEERTYPE on RSO.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
    left join dbo.EVENTLOCATION on RSO.EVENTLOCATIONID = EVENTLOCATION.ID
    left join dbo.[RESOURCE] on RSO.RESOURCEID = [RESOURCE].ID
    where ORDERREVENUE.TYPECODE = 5
        and SO.SALESMETHODTYPECODE = 3
        and (@INCLUDETAXES = 1 or ORDERLI_EXT.TYPECODE <> 7)
        and (
            (ORDERLI_EXT.TYPECODE = 20 and ORDERLI.POSTDATE between @FROMDATE and @TODATE)
            or (ORDERLI_EXT.TYPECODE <> 20 and ORDERREVENUE.CALCULATEDDATE between @FROMDATE and @TODATE)        -- Earned date was during time period

        )
        and PAYMENT.CALCULATEDDATE < @FROMDATE         -- Payment was made earlier (as unearned)

        and (@APPUSERID is null or SO.APPUSERID = @APPUSERID)
        and (@APPUSERQUERYID is null or SO.APPUSERID in (select ID from @APPUSERQUERYRESULTS));


    return 0;