USP_REPORT_CREDITCARDPROCESSING

CREATE procedure dbo.USP_REPORT_CREDITCARDPROCESSING
(
    @BUSINESSPROCESSSTATUSID nvarchar(36),
    @TRANSACTIONCODE tinyint = 0,
    @APPLICATIONCODE tinyint = 0
)
with execute as owner
as begin

    set nocount on;

    declare @TABLENAME nvarchar(255);
    declare @SQL nvarchar(4000);
    declare @TRANSACTIONCLAUSE nvarchar(4000);
    declare @APPLICATIONCLAUSE nvarchar(4000);
    set @SQL = '';
    set @TRANSACTIONCLAUSE = '';
    set @APPLICATIONCLAUSE = '';

    begin try
        select @TABLENAME = BUSINESSPROCESSOUTPUT.TABLENAME
            from dbo.BUSINESSPROCESSSTATUS
            inner join dbo.BUSINESSPROCESSOUTPUT on BUSINESSPROCESSSTATUS.ID = BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID 
            where BUSINESSPROCESSSTATUS.ID = @BUSINESSPROCESSSTATUSID and BUSINESSPROCESSOUTPUT.TABLEKEY = 'OUTPUT';

        if not @TABLENAME is null and not OBJECT_ID(@TABLENAME) is null
            begin
                set @SQL = '
                    select
                        T.BUSINESSPROCESSOUTPUT_PKID as ROWNUMBER,
                        T.TRANSACTIONNUMBER,
                        T.COMMITMENTID,
                        CONSTITUENT_NF.NAME, 
                        T.PARTIALCREDITCARDNUMBER,
                        T.TRANSACTIONAMOUNTAPPLIED,
                        T.MESSAGE,
                        T.CREDITTYPE,
                        T.EXPIRESON,
                        T.ISSUCCESSFUL,
                        T.ISPERMANENTREJECTION,
                        T.INCLUDEINEXPORT,
                        CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
                        coalesce(FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID, REGISTRANT.LOOKUPID, MEMBERSHIP.LOOKUPID) as REVENUELOOKUPID,
                        coalesce(FINANCIALTRANSACTION.TYPE, nullif(T.APPLICATION, '''')) as TRANSACTIONTYPE,
                        coalesce(FINANCIALTRANSACTION.DATE, EVENT.STARTDATE, cast(MEMBERSHIP.JOINDATE as date)) as REVENUEDATE,
                        case
                            when FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID is not null then ''http://www.blackbaud.com/REVENUELINK?REVENUEID='' + cast(T.COMMITMENTID as nvarchar(max))
                            else ''''
                            end as REVENUELINK,
                        ''http://www.blackbaud.com/CONSTITUENTLINK?CONSTITUENTID='' + cast(T.CONSTITUENTID as nvarchar(max)) as CONSTITUENTLINK,
                        CURRENCY.ISO4217 as ISOCURRENCYCODE,
                        CURRENCY.DECIMALDIGITS,
                        CURRENCY.CURRENCYSYMBOL,
                        CURRENCY.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                        case
                            when REGISTRANT.LOOKUPID is not null then ''http://www.blackbaud.com/REGISTRANTLINK?REGISTRANTID='' + cast(T.COMMITMENTID as nvarchar(max))
                            else ''''
                            end as REGISTRANTLINK,
                        case
                            when MEMBERSHIP.ID is not null then ''http://www.blackbaud.com/MEMBERSHIPLINK?MEMBERSHIPID='' + cast(T.COMMITMENTID as nvarchar(max))
                            else ''''
                            end as MEMBERSHIPLINK,
                            MEMBERSHIP.ID as MEMBERSHIPID
                            from ' + @TABLENAME + ' as T 
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(T.CONSTITUENTID) CONSTITUENT_NF
                    left join dbo.CONSTITUENT on CONSTITUENT.ID = T.CONSTITUENTID
                    left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = T.COMMITMENTID
                    left join dbo.CURRENCY on T.TRANSACTIONCURRENCYID = CURRENCY.ID
                    left join dbo.REGISTRANT on REGISTRANT.ID = T.COMMITMENTID
                    left join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
                    left join dbo.MEMBERSHIP on MEMBERSHIP.ID = T.COMMITMENTID
                    ';

                --@TRANSACTIONCODE = 0 --All transactions

                if @TRANSACTIONCODE = 1 --Authorized transactions

                    set @TRANSACTIONCLAUSE = 'T.ISSUCCESSFUL = 1';
                else if @TRANSACTIONCODE = 2 --All rejected transactions

                    set @TRANSACTIONCLAUSE = 'T.ISSUCCESSFUL = 0';
                else if @TRANSACTIONCODE = 3 --Provisional rejections

                    set @TRANSACTIONCLAUSE = 'T.ISSUCCESSFUL = 0 and T.ISPERMANENTREJECTION = 0';
                else if @TRANSACTIONCODE = 4 --Permanent rejections

                    set @TRANSACTIONCLAUSE = 'T.ISSUCCESSFUL = 0 and T.ISPERMANENTREJECTION = 1';

                --@APPLICATIONCODE = 0 --All applications

                if @APPLICATIONCODE = 1 --Pledges

                    set @APPLICATIONCLAUSE = 'FINANCIALTRANSACTION.TYPECODE = 1'
                if @APPLICATIONCODE = 2 --Recurring gifts

                    set @APPLICATIONCLAUSE = 'FINANCIALTRANSACTION.TYPECODE = 2'
                if @APPLICATIONCODE = 3 --Donations

                    set @APPLICATIONCLAUSE = 'T.APPLICATION = ''Donation'''
                if @APPLICATIONCODE = 4 --Event registrations

                    set @APPLICATIONCLAUSE = 'REGISTRANT.ID is not null'
                if @APPLICATIONCODE = 5 --Memberships

                    set @APPLICATIONCLAUSE = 'T.APPLICATION = ''Membership'''

                if len(@TRANSACTIONCLAUSE) > 0 and len(@APPLICATIONCLAUSE) > 0
                    set @TRANSACTIONCLAUSE = @TRANSACTIONCLAUSE + ' and ';

                if len(@TRANSACTIONCLAUSE) > 0 or len(@APPLICATIONCLAUSE) > 0
                    set @SQL = @SQL + ' where ';

                set @SQL = @SQL + @TRANSACTIONCLAUSE + @APPLICATIONCLAUSE;

            end    
        else
            set @SQL = '
                select
                    0 as ROWNUMBER,
                    0 as TRANSACTIONNUMBER,
                    ID as [COMMITMENTID],
                    null as NAME,
                    null as PARTIALCREDITCARDNUMBER,
                    null as TRANSACTIONAMOUNTAPPLIED,
                    null as MESSAGE,
                    null as CREDITTYPE,
                    null as EXPIRESON,
                    null as ISSUCCESSFUL,
                    null as ISPERMANENTREJECTION,
                    null as CONSTITUENTLOOKUPID,
                    null as REVENUELOOKUPID,
                    null as TRANSACTIONTYPE,
                    null as REVENUEDATE,
                    null as REVENUELINK,
                    null as CONSTITUENTLINK,
                    null as ISOCURRENCYCODE,
                    null as DECIMALDIGITS,
                    null as CURRENCYSYMBOL,
                    null as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                    null as REGISTRANTLINK
                    null as MEMBERSHIPLINK,
                    null as MEMBERSHIPID
                    from BUSINESSPROCESSSTATUS 
                where 1 = 0';


        exec sp_executesql @SQL;
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch
    return 0;
end