USP_BBNC_GETOFFLINEGIFTS

            CREATE procedure dbo.USP_BBNC_GETOFFLINEGIFTS
            (
                @APPEALMAPID int,
                @SOLICITORCONSTITUENTMAPID int,
                @COMMENTNOTETITLE nvarchar(100) = null
            )
            as
                set nocount on;

                declare @REVENUENOTETYPECODEID uniqueidentifier;
                select top 1 @REVENUENOTETYPECODEID = REVENUENOTETYPECODEID from dbo.NETCOMMUNITYDEFAULTCODEMAP;

                --JamesWill 2008-03-07 Broke most of this out into a CTE so it would be easier to add [PAYSID], [HASPAYMENTS], and [ONLINEGIFT]. This, in turn, makes it easier

                --to filter out payments to online pledges and offline pledges with payments. Also now joining on REVENUESOLICITOR as part of a subexpression in the CTE to elimate

                --most of the group by clauses (which were getting in the way of the [PAYSID], [HASPAYMENTS], and [ONLINEGIFT]). I think it should also be a little easier to read 

                --and work with now. 

                with [CTE] as
                (
                    select
                        BBNCREVENUESPLITIDMAP.ID as [REVENUESPLITBBNCID],
                        REVENUEBBNC.NETCOMMUNITYTRANSACTIONID,
                        [SOLICITOR].AMOUNT,
                        REVENUE.ID,
                        REVENUE.TRANSACTIONTYPECODE,
                        REVENUE.DATE,
                        REVENUE.GIVENANONYMOUSLY,
                        REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                        REVENUE.CONSTITUENTID as [DONORID],
                        (select top 1 PLEDGEID from dbo.INSTALLMENTPAYMENT where PAYMENTID = REVENUE.ID) as [PAYSID],
                        case when (select count(INSTALLMENTPAYMENT.PAYMENTID) from dbo.INSTALLMENTPAYMENT where PLEDGEID = REVENUE.ID) > 0 then 1 else 0 end as [HASPAYMENTS],
                        case when REVENUEBBNC.ID is null then 0 else 1 end as [ONLINEGIFT]
                    from dbo.REVENUE
                    inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
                    inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                    inner join dbo.BBNCAPPEALIDMAP on REVENUE.APPEALID = BBNCAPPEALIDMAP.APPEALID and BBNCAPPEALIDMAP.ID = @APPEALMAPID
                    inner join 
                    (
                        select 
                            REVENUESOLICITOR.REVENUESPLITID,
                            sum(REVENUESOLICITOR.AMOUNT) as [AMOUNT]            
                        from dbo.REVENUESOLICITOR
                        inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUESOLICITOR.CONSTITUENTID
                        where CONSTITUENT.SEQUENCEID = @SOLICITORCONSTITUENTMAPID
                        group by
                            REVENUESOLICITOR.REVENUESPLITID
                    ) as [SOLICITOR] on [SOLICITOR].REVENUESPLITID = REVENUESPLIT.ID

                    left join dbo.REVENUEBBNC on REVENUEBBNC.ID = REVENUE.ID
                    left join dbo.BBNCREVENUESPLITIDMAP on BBNCREVENUESPLITIDMAP.REVENUESPLITID = REVENUESPLIT.ID 
                    where 
                        REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE in (0, 1, 2, 3, 9) --TODO Verify these are the right payment methods to include

                        and
                        (
                            --TODO Verify these are the right revenue types to include

                            REVENUE.TRANSACTIONTYPECODE <> 2 -- Exclude recurring gifts

                            and
                            REVENUESPLIT.APPLICATIONCODE not in (1, 5) -- Exclude revenue applied to event registrations and memberships

                        )
                )
                select
                    [CTE].REVENUESPLITBBNCID,
                    [CTE].AMOUNT,
                    [CTE].DATE,
                    [CTE].GIVENANONYMOUSLY,
                    [CTE].PAYMENTMETHODCODE,
                    (
                        select top 1 
                            REVENUENOTE.TEXTNOTE
                        from dbo.REVENUENOTE
                        where REVENUENOTE.REVENUEID = [CTE].ID
                            and REVENUENOTE.REVENUENOTETYPECODEID = @REVENUENOTETYPECODEID
                        order by REVENUENOTE.DATEADDED
                    ) [COMMENT],
                    CONSTITUENT.FIRSTNAME as [DONORFIRSTNAME],
                    CONSTITUENT.KEYNAME as [DONORLASTNAME],
                    (select top 1 DESCRIPTION from dbo.TITLECODE where ID = CONSTITUENT.TITLECODEID) as [DONORTITLECODE],
                    CONSTITUENT.ISORGANIZATION as [DONORISORGANIZATION],
                    CONSTITUENT.NAME as [DONORNAME],
                    (select top 1 EMAILADDRESS from dbo.EMAILADDRESS where CONSTITUENTID = CONSTITUENT.ID and ISPRIMARY = 1) as [EMAILADDRESS],
                    [CTE].NETCOMMUNITYTRANSACTIONID
                from [CTE]
                inner join dbo.CONSTITUENT on CONSTITUENT.ID = [CTE].DONORID
                left join dbo.TITLECODE on CONSTITUENT.TITLECODEID = TITLECODE.ID
                where 
                    ([CTE].[PAYSID] is null or (select top 1 ID from dbo.REVENUEBBNC where ID = [PAYSID]) is null) --Exclude payments for pledges that originated online (See RE7XData/FundDataFetch.vb)

                    and ([CTE].TRANSACTIONTYPECODE <> 1 or [CTE].ONLINEGIFT = 1 or [CTE].HASPAYMENTS = 0) --Exclude payments for pledges that originated in the backoffice and have payments (See RE7XData/FundDataFetch.vb)

                order by [CTE].REVENUESPLITBBNCID