USP_REPORT_CREDITCARDPROCESSING_SUMMARY

CREATE procedure dbo.USP_REPORT_CREDITCARDPROCESSING_SUMMARY
(
    @BUSINESSPROCESSSTATUSID nvarchar(36),
    @REPORTUSERID nvarchar(128) = null,
    @CURRENCYCODE tinyint = 3,
    @ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as begin

    set nocount on;

    declare @TABLENAME nvarchar(255);
    declare @SQL nvarchar(4000);

    declare @CURRENCYID uniqueidentifier;
    declare @CURRENTAPPUSERID uniqueidentifier;

    set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);

    select @CURRENCYID = case @CURRENCYCODE
        when 1 then dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
        else dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID)
    end;

    begin try

        -- Calculate total amounts and get the process date.


        declare @AUTHORIZEDTOTAL money = 0;
        declare @PROVISIONALTOTAL money = 0;
        declare @PERMANENTTOTAL money = 0;
        declare @PROCESSDATE datetime;

        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 = '
                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

                declare @RATES table
                (
                    FROMCURRENCYID uniqueidentifier,
                    RATEID uniqueidentifier
                );

                insert into @RATES (FROMCURRENCYID, RATEID)
                select CURRENCYEXCHANGERATE.FROMCURRENCYID, CURRENCYEXCHANGERATE.ID
                from dbo.CURRENCYEXCHANGERATE
                where
                    CURRENCYEXCHANGERATE.TOCURRENCYID = @CURRENCYID
                    and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                    and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                    and @CURRENTDATE >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
                    and @CURRENTDATE <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ;

                select
                    @AUTHORIZEDTOTAL = sum(
                        case when T.TRANSACTIONCURRENCYID = @CURRENCYID then T.TRANSACTIONAMOUNTAPPLIED
                        else dbo.UFN_CURRENCY_CONVERT(T.TRANSACTIONAMOUNTAPPLIED, RATE.RATEID)
                        end
                    )
                from ' + @TABLENAME + ' as T
                outer apply (
                    select top 1 R.RATEID from @RATES as R
                    where R.FROMCURRENCYID = T.TRANSACTIONCURRENCYID
                ) as RATE
                where T.ISSUCCESSFUL = 1;

                select
                    @PROVISIONALTOTAL = sum(
                        case when T.TRANSACTIONCURRENCYID = @CURRENCYID then T.TRANSACTIONAMOUNTAPPLIED
                        else dbo.UFN_CURRENCY_CONVERT(T.TRANSACTIONAMOUNTAPPLIED, RATE.RATEID)
                        end
                    )
                from ' + @TABLENAME + ' as T
                outer apply (
                    select top 1 R.RATEID from @RATES as R
                    where R.FROMCURRENCYID = T.TRANSACTIONCURRENCYID
                ) as RATE
                where T.ISSUCCESSFUL = 0 and T.ISPERMANENTREJECTION = 0;

                select
                    @PERMANENTTOTAL = sum(
                        case when T.TRANSACTIONCURRENCYID = @CURRENCYID then T.TRANSACTIONAMOUNTAPPLIED
                        else dbo.UFN_CURRENCY_CONVERT(T.TRANSACTIONAMOUNTAPPLIED, RATE.RATEID)
                        end
                    )
                from ' + @TABLENAME + ' as T
                outer apply (
                    select top 1 R.RATEID from @RATES as R
                    where R.FROMCURRENCYID = T.TRANSACTIONCURRENCYID
                ) as RATE
                where T.ISSUCCESSFUL = 0 and T.ISPERMANENTREJECTION = 1;

                select top 1
                    @PROCESSDATE = T.PROCESSDATE
                from ' + @TABLENAME + ' as T;
                ';
        end

        exec sp_executesql @SQL, N'@CURRENCYID uniqueidentifier, @AUTHORIZEDTOTAL money output, @PROVISIONALTOTAL money output, @PERMANENTTOTAL money output, @PROCESSDATE datetime output', @CURRENCYID=@CURRENCYID, @AUTHORIZEDTOTAL=@AUTHORIZEDTOTAL output, @PROVISIONALTOTAL=@PROVISIONALTOTAL output, @PERMANENTTOTAL=@PERMANENTTOTAL output, @PROCESSDATE=@PROCESSDATE output;

        select top 1
            CREDITCARDPROCESSINGTRANSACTIONOUTCOME.ORIGINALBATCHNUMBER,
            CREDITCARDPROCESSINGTRANSACTIONOUTCOME.RETRYBATCHNUMBER,
            (BUSINESSPROCESSSTATUS.NUMBEROFEXCEPTIONS + BUSINESSPROCESSSTATUS.NUMBERPROCESSED) as TOTALCOUNT,
            BUSINESSPROCESSSTATUS.NUMBEROFEXCEPTIONS as EXCEPTIONCOUNT,
            BUSINESSPROCESSSTATUS.NUMBERPROCESSED as SUCCESSCOUNT,
            CREDITCARDPROCESSINGTRANSACTIONOUTCOME.AUTHORIZEDCOUNT,
            CREDITCARDPROCESSINGTRANSACTIONOUTCOME.PROVISIONALCOUNT,
            CREDITCARDPROCESSINGTRANSACTIONOUTCOME.PERMANENTCOUNT,
            @PROCESSDATE as PROCESSDATE,
            @AUTHORIZEDTOTAL as AUTHORIZEDTRANSACTIONTOTAL,
            @PROVISIONALTOTAL as PROVISIONALREJECTIONTOTAL,
            @PERMANENTTOTAL as PERMANENTREJECTIONTOTAL,
            CREDITCARDPROCESSINGTRANSACTIONOUTCOME.ATTEMPTCOUNT,
            CREDITCARDPROCESSINGTRANSACTIONOUTCOME.ATTEMPTMAX,
            CURRENCY.CURRENCYSYMBOL,
            CURRENCY.ISO4217 as ISOCURRENCYCODE,
            CURRENCY.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
            CURRENCY.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
            CREDITCARDPROCESSINGTRANSACTIONOUTCOME.NEWBATCHCREATED as ISNEWBATCH
        from dbo.CREDITCARDPROCESSINGTRANSACTIONOUTCOME
        inner join dbo.BUSINESSPROCESSSTATUS on BUSINESSPROCESSSTATUS.ID = CREDITCARDPROCESSINGTRANSACTIONOUTCOME.ID
        cross join dbo.CURRENCY
        where
            CREDITCARDPROCESSINGTRANSACTIONOUTCOME.ID = cast(@BUSINESSPROCESSSTATUSID as uniqueidentifier)
            and CURRENCY.ID = @CURRENCYID

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