USP_BATCHMEMBERSHIPDUES_VALIDATIONREPORT_CURRENCYSUMMARY

CREATE procedure dbo.USP_BATCHMEMBERSHIPDUES_VALIDATIONREPORT_CURRENCYSUMMARY
(
    @BATCHID uniqueidentifier
)
as
begin

    set nocount on;

    --Get batch revenue records we care about

    declare @BATCHMEMBERSHIPDUES table(
        ID uniqueidentifier
        , TOTALAMOUNT money
        , DONATIONAMOUNT money
        , MEMBERSHIPPROGRAMID uniqueidentifier
        , TRANSACTIONCURRENCYID uniqueidentifier
        , BASECURRENCYID uniqueidentifier
        , REVENUETYPECODE tinyint
        , MEMBERSHIPTRANSACTIONTYPECODE tinyint
        , EXCHANGERATEID uniqueidentifier
    );

    insert into @BATCHMEMBERSHIPDUES( 
        ID
        , TOTALAMOUNT
        , DONATIONAMOUNT
        , MEMBERSHIPPROGRAMID
        , TRANSACTIONCURRENCYID
        , BASECURRENCYID
        , REVENUETYPECODE
        , MEMBERSHIPTRANSACTIONTYPECODE
        , EXCHANGERATEID
    )
    select
        BATCHMEMBERSHIPDUES.ID
        , BATCHMEMBERSHIPDUES.TOTALAMOUNT
        , BATCHMEMBERSHIPDUES.DONATIONAMOUNT
        , BATCHMEMBERSHIPDUES.MEMBERSHIPPROGRAMID
        , BATCHMEMBERSHIPDUES.TRANSACTIONCURRENCYID
        , BATCHMEMBERSHIPDUES.BASECURRENCYID
        , BATCHMEMBERSHIPDUES.REVENUETYPECODE
        , BATCHMEMBERSHIPDUES.MEMBERSHIPTRANSACTIONTYPECODE
        , case 
            when BATCHMEMBERSHIPDUES.TRANSACTIONCURRENCYID = BATCHMEMBERSHIPDUES.BASECURRENCYID then null
            else dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(BATCHMEMBERSHIPDUES.BASECURRENCYID, BATCHMEMBERSHIPDUES.TRANSACTIONCURRENCYID, null, 1, null)
          end
    from dbo.BATCHMEMBERSHIPDUES
    where BATCHMEMBERSHIPDUES.BATCHID = @BATCHID;

    --Get currencies used by batch revenue records we care about.

    declare @CURRENCY table(
        ID uniqueidentifier,
        NAME nvarchar(100),
        ISO4217 nvarchar(3),
        DECIMALDIGITS tinyint,
        CURRENCYSYMBOL nvarchar(5),
        SYMBOLDISPLAYSETTINGCODE tinyint
    );

    insert into @CURRENCY(
        ID,
        NAME,
        ISO4217,
        DECIMALDIGITS,
        CURRENCYSYMBOL,
        SYMBOLDISPLAYSETTINGCODE
    )
    select distinct
        ID,
        NAME,
        ISO4217,
        DECIMALDIGITS,
        CURRENCYSYMBOL,
        SYMBOLDISPLAYSETTINGCODE
    from dbo.CURRENCY
    where ID in(
        select
            TRANSACTIONCURRENCYID
        from @BATCHMEMBERSHIPDUES
    );

    if @@ROWCOUNT > 1
    begin            
        --If there is more than one currency, do the aggregation.

        select
            CURRENCY.ID CURRENCYID
            , CURRENCY.NAME CURRENCYNAME
            , CURRENCY.ISO4217
            , CURRENCY.DECIMALDIGITS
            , CURRENCY.CURRENCYSYMBOL
            , CURRENCY.SYMBOLDISPLAYSETTINGCODE
            , coalesce((select sum(TOTALAMOUNT) from @BATCHMEMBERSHIPDUES BMD where BMD.TRANSACTIONCURRENCYID = CURRENCY.ID), 0) as CURRENTTOTALAMOUNT
            , coalesce((select count(ID) from @BATCHMEMBERSHIPDUES BMD where BMD.TRANSACTIONCURRENCYID = CURRENCY.ID), 0) as CURRENTNUMBEROFRECORDS
            , coalesce((select 
                            case 
                                when BMD.EXCHANGERATEID is null then
                                    SUM(MPA.PRICE * BMDA.NUMBEROFADDONS)
                                else 
                                    dbo.UFN_CURRENCY_CONVERT(SUM(MPA.PRICE * BMDA.NUMBEROFADDONS), BMD.EXCHANGERATEID)
                            end
                    from @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
                    where BMD.TRANSACTIONCURRENCYID = CURRENCY.ID
                    group by BMD.EXCHANGERATEID), 0) as TOTALADDONAMOUNT
            , coalesce((select SUM(DONATIONAMOUNT) from dbo.BATCHMEMBERSHIPDUES where BATCHID = @BATCHID), 0) as TOTALDONATIONAMOUNT
        from @CURRENCY CURRENCY;
    end
end