USP_BATCHMEMBERSHIPDUES_CONTROLREPORT_SUMMARY

CREATE procedure dbo.USP_BATCHMEMBERSHIPDUES_CONTROLREPORT_SUMMARY
(
    @BATCHID uniqueidentifier
)
with execute as owner
as
begin
    set nocount on;

    declare @MULTICURRENCYENABLED bit = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency');
    declare 
        @ORGANIZATIONISO4217 nvarchar(3),
        @ORGANIZATIONDECIMALDIGITS tinyint,
        @ORGANIZATIONCURRENCYSYMBOL nvarchar(5),
        @ORGANIZATIONSYMBOLDISPLAYSETTINGCODE tinyint;

    declare @BATCHNUMBER nvarchar(100);

    select @BATCHNUMBER = BATCH.BATCHNUMBER
    from dbo.BATCH
    where BATCH.ID = @BATCHID;

    select
        @ORGANIZATIONISO4217 = ISO4217,
        @ORGANIZATIONDECIMALDIGITS = DECIMALDIGITS,
        @ORGANIZATIONCURRENCYSYMBOL  = CURRENCYSYMBOL,
        @ORGANIZATIONSYMBOLDISPLAYSETTINGCODE = SYMBOLDISPLAYSETTINGCODE
    from dbo.CURRENCY
    where ISORGANIZATIONCURRENCY = 1;

    declare @BATCHTABLE nvarchar(128)
    set @BATCHTABLE = dbo.UFN_BATCH_GETREPORTTABLENAME(@BATCHID, 0);

    declare @COUNT as integer = 0;
    declare @SQL as nvarchar(max) ='
        set nocount on;
        select @COUNT = COUNT(ID)
        from ' + @BATCHTABLE

    exec sp_executesql @SQL, N'@COUNT integer output', @COUNT = @COUNT output;

    declare @SQLTOEXEC nvarchar(max) = N'set nocount on;
        with FT_CTE as (
        select 
            FT.ID
            , S.ISPLEDGE
            , S.ISRECURRINGGIFT
            , S.ISPAYMENT
        from dbo.FINANCIALTRANSACTION FT
            inner join (
                select 
                    O.ID as ID
                    , case O.TYPECODE when 15 then 1 else 0 end as ISPLEDGE
                    , case O.TYPECODE when 2 then 1 else 0 end as ISRECURRINGGIFT
                    , case O.TYPECODE when 0 then 1 else 0 end as ISPAYMENT
                from ' + @BATCHTABLE + ' O
                union
                select 
                    o.PARENTID as ID
                    , case O.PARENTTYPECODE when 15 then 1 else 0 end as ISPLEDGE
                    , case O.PARENTTYPECODE when 2 then 1 else 0 end as ISRECURRINGGIFT
                    , 0 ISPAYMENT
                from ' + @BATCHTABLE + ' O
                inner join dbo.REVENUE_EXT on O.PARENTID = REVENUE_EXT.ID
                where o.PARENTID is not null and REVENUE_EXT.BATCHNUMBER = @BATCHNUMBER
            ) S on FT.ID = S.ID
        )
        select
            b.BATCHNUMBER
            , b.STATUS
            , EXCEPTIONBATCH.BATCHNUMBER EXCEPTIONBATCHNAME
            , a.USERNAME as OWNER
            , B.PROJECTEDNUMBEROFRECORDS
            , B.PROJECTEDTOTALAMOUNT
            , @COUNT as CURRENTNUMBEROFRECORDS
            , sum(T.ISPAYMENT) as TOTALPAYMENTCOUNT
            , sum(T.ISPLEDGE) as TOTALPLEDGECOUNT
            , sum(T.ISRECURRINGGIFT) as TOTALRECURRINGGIFTCOUNT
            , @MULTICURRENCYENABLED MULTICURRENCYENABLED
            , @ORGANIZATIONISO4217 ORGANIZATIONISO4217
            , @ORGANIZATIONDECIMALDIGITS ORGANIZATIONDECIMALDIGITS
            , @ORGANIZATIONCURRENCYSYMBOL ORGANIZATIONCURRENCYSYMBOL
            , @ORGANIZATIONSYMBOLDISPLAYSETTINGCODE ORGANIZATIONSYMBOLDISPLAYSETTINGCODE
        from FT_CTE T
            inner join dbo.FINANCIALTRANSACTION FT on T.ID = FT.ID
            inner join dbo.BATCH B on B.ID = @BATCHID
            inner join dbo.APPUSER A on B.APPUSERID = A.ID
            /*    JamesWill WI200374 2012-07-20 The BATCH.EXCEPTIONBATCHNAME field is only used when overriding the default exception batch number. 
                So we need to actually look up the exception bathc using the exception batch change data. */
            left join 
            (
                select top 1 
                    EXCEPTIONBATCH.ID,
                    EXCEPTIONBATCH.ORIGINATINGBATCHID,
                    EXCEPTIONBATCH.BATCHNUMBER
                from dbo.BATCH EXCEPTIONBATCH 
                where EXCEPTIONBATCH.ORIGINATINGBATCHID = @BATCHID
            ) EXCEPTIONBATCH on EXCEPTIONBATCH.ORIGINATINGBATCHID = B.ID
        group by
            b.BATCHNUMBER
            , b.STATUS
            , EXCEPTIONBATCH.BATCHNUMBER
            , a.USERNAME
            , B.PROJECTEDNUMBEROFRECORDS
            , B.PROJECTEDTOTALAMOUNT';

    exec sp_executesql @SQLTOEXEC, 
        N'@COUNT integer,
        @BATCHID uniqueidentifier,
        @MULTICURRENCYENABLED bit, 
        @ORGANIZATIONISO4217 nvarchar(3), 
        @ORGANIZATIONDECIMALDIGITS tinyint,
        @ORGANIZATIONCURRENCYSYMBOL nvarchar(5),
        @ORGANIZATIONSYMBOLDISPLAYSETTINGCODE tinyint,
        @BATCHNUMBER nvarchar(100)',
        @COUNT = @COUNT,
        @BATCHID = @BATCHID,
        @MULTICURRENCYENABLED = @MULTICURRENCYENABLED, 
        @ORGANIZATIONISO4217 = @ORGANIZATIONISO4217, 
        @ORGANIZATIONDECIMALDIGITS = @ORGANIZATIONDECIMALDIGITS,
        @ORGANIZATIONCURRENCYSYMBOL = @ORGANIZATIONCURRENCYSYMBOL,
        @ORGANIZATIONSYMBOLDISPLAYSETTINGCODE = @ORGANIZATIONSYMBOLDISPLAYSETTINGCODE,
        @BATCHNUMBER = @BATCHNUMBER
        ;
end