USP_BATCHMEMBERSHIPDUES_VALIDATIONREPORT_SUMMARY

CREATE procedure dbo.USP_BATCHMEMBERSHIPDUES_VALIDATIONREPORT_SUMMARY
(
    @BATCHID uniqueidentifier
)
as
begin
    set nocount on;

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

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

    select
        BATCH.BATCHNUMBER
        , BATCH.STATUS
        , APPUSER.USERNAME as [OWNER]
        , BATCH.PROJECTEDNUMBEROFRECORDS
        , BATCH.PROJECTEDTOTALAMOUNT
        , coalesce((select count(ID) from dbo.BATCHMEMBERSHIPDUES where BATCHID = @BATCHID),0) as [CURRENTNUMBEROFRECORDS]
        , coalesce((select sum(TOTALAMOUNT) from dbo.BATCHMEMBERSHIPDUES where BATCHID = @BATCHID),0) as [CURRENTTOTALAMOUNT]
        , coalesce((select count(ID) from dbo.BATCHMEMBERSHIPDUES where BATCHID = @BATCHID and REVENUETYPECODE <> 2), 0) AS TOTALPAYMENTCOUNT
        , coalesce((select 
                        count(BMD.ID) 
                    from dbo.BATCHMEMBERSHIPDUES BMD
                        inner join dbo.MEMBERSHIPPROGRAM MP on BMD.MEMBERSHIPPROGRAMID = MP.ID
                    where 
                        BMD.BATCHID = @BATCHID 
                        and MP.PROGRAMTYPECODE <> 1 
                        and BMD.MEMBERSHIPTRANSACTIONTYPECODE <> 2
                        and BMD.REVENUETYPECODE <> 0), 0) AS TOTALPLEDGECOUNT
        , coalesce((select 
                        count(BMD.ID) 
                    from dbo.BATCHMEMBERSHIPDUES BMD
                        inner join dbo.MEMBERSHIPPROGRAM MP on BMD.MEMBERSHIPPROGRAMID = MP.ID
                    where 
                        BMD.BATCHID = @BATCHID 
                        and MP.PROGRAMTYPECODE = 1 
                        and BMD.MEMBERSHIPTRANSACTIONTYPECODE <> 2
                    ), 0) AS TOTALRECURRINGGIFTCOUNT
        , coalesce((
            select sum(S.ADDONAMOUNT / S.INSTALLMENTS)
            from (
                select 
                    BMD.ID
                    , case
                        when BMD.BASECURRENCYID = BMD.TRANSACTIONCURRENCYID then 
                            sum(MPA.PRICE * BMDA.NUMBEROFADDONS)
                        else
                            -- get the exchange rate and then do an immediate conversion. We never look at a rate from the row, even if there is one.

                            dbo.UFN_CURRENCY_CONVERT(sum(MPA.PRICE * BMDA.NUMBEROFADDONS), dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(BMD.BASECURRENCYID, BMD.TRANSACTIONCURRENCYID, null, 1, null))
                    end as ADDONAMOUNT
                    , case BMD.REVENUETYPECODE
                        when 0 then 1
                        else I.INSTALLMENTCOUNT
                    end as INSTALLMENTS
                from dbo.BATCHMEMBERSHIPDUES BMD
                    inner join dbo.BATCHMEMBERSHIPDUESMEMBERSHIPROGRAMADDON BMDA on BMD.ID = BMDA.BATCHMEMBERSHIPDUESID
                    inner join dbo.MEMBERSHIPPROGRAMADDON MPA on MPA.ADDONID = BMDA.ADDONID and BMD.MEMBERSHIPPROGRAMID = MPA.MEMBERSHIPPROGRAMID
                    left join (
                        select
                            BMD.ID
                            , count(BPI.ID) as INSTALLMENTCOUNT
                        from dbo.BATCHMEMBERSHIPDUES BMD
                            inner join dbo.BATCHMEMBERSHIPDUESPLEDGEINSTALLMENT BPI on BMD.ID = BPI.BATCHMEMBERSHIPDUESID
                        where BMD.BATCHID = @BATCHID
                        group by BMD.ID
                    ) I on I.ID = BMD.ID
                where 
                    BMD.BATCHID = @BATCHID
                    and BMD.REVENUETYPECODE <> 2 -- no pledge only

                group by 
                    BMD.BASECURRENCYID
                    , BMD.TRANSACTIONCURRENCYID
                    , BMD.ID
                    , BMD.REVENUETYPECODE
                    , I.INSTALLMENTCOUNT) S), 0) as TOTALADDONAMOUNT
        , coalesce((select SUM(DONATIONAMOUNT) from dbo.BATCHMEMBERSHIPDUES where BATCHID = @BATCHID), 0) as TOTALDONATIONAMOUNT
        , @MULTICURRENCYENABLED MULTICURRENCYENABLED
        , @ORGANIZATIONISO4217 ORGANIZATIONISO4217
        , @ORGANIZATIONDECIMALDIGITS ORGANIZATIONDECIMALDIGITS
        , @ORGANIZATIONCURRENCYSYMBOL ORGANIZATIONCURRENCYSYMBOL
        , @ORGANIZATIONSYMBOLDISPLAYSETTINGCODE ORGANIZATIONSYMBOLDISPLAYSETTINGCODE
    from dbo.BATCH
        inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.ID = BATCH.BATCHTEMPLATEID 
        inner join dbo.APPUSER on BATCH.APPUSERID = APPUSER.ID
    where BATCH.ID = @BATCHID

end