UFN_EVENTREGISTRANT_GETRECEIPTAMOUNT_NOBENEFITS

CREATE function dbo.UFN_EVENTREGISTRANT_GETRECEIPTAMOUNT_NOBENEFITS
(
    @REGISTRANTID uniqueidentifier,
    @REVENUEDATE datetime,
    @TRANSACTIONAMOUNT money,
    @TRANSACTIONCURRENCYID uniqueidentifier,
    @BASECURRENCYID uniqueidentifier,
    @BASEEXCHANGERATEID uniqueidentifier,
    @SPOTRATE decimal(20,8) = 0,
    @REGISTRATIONEXISTSINBATCH bit = 0,
    @EXISTINGREVENUEID uniqueidentifier = null
)
returns money
as
begin
    --Get the event's currency.

    declare @APPLICATIONCURRENCYID uniqueidentifier;
    if @REGISTRATIONEXISTSINBATCH = 0
    begin
        select
            @APPLICATIONCURRENCYID = EVENT.BASECURRENCYID
        from
            dbo.REGISTRANT
            inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
        where
            REGISTRANT.ID = @REGISTRANTID;
    end
    else
    begin
        select
            @APPLICATIONCURRENCYID = EVENT.BASECURRENCYID
        from
            dbo.BATCHREVENUEREGISTRANT
            inner join dbo.EVENT on EVENT.ID = BATCHREVENUEREGISTRANT.EVENTID
        where
            BATCHREVENUEREGISTRANT.ID = @REGISTRANTID
    end

    declare @APPLICATIONCURRENCYDECIMALDIGITS int;
    declare @APPLICATIONCURRENCYROUNDINGTYPE int = 0;
    select
        @APPLICATIONCURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
        @APPLICATIONCURRENCYROUNDINGTYPE = CURRENCY.ROUNDINGTYPECODE
    from dbo.CURRENCY
    where CURRENCY.ID = @APPLICATIONCURRENCYID;

    declare @TRANSACTIONCURRENCYDECIMALDIGITS int;
    declare @TRANSACTIONCURRENCYROUNDINGTYPE int = 0;
    select
        @TRANSACTIONCURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
        @TRANSACTIONCURRENCYROUNDINGTYPE = CURRENCY.ROUNDINGTYPECODE
    from dbo.CURRENCY
    where CURRENCY.ID = @TRANSACTIONCURRENCYID;

    --Calculate the application amount, getting an exchange rate if need be.

    declare @APPLICATIONEXCHANGERATE decimal(20,8);
    declare @APPLICATIONEXCHANGERATEID uniqueidentifier;
    declare @APPLICATIONAMOUNT money;
    declare @EVENTPRICE MONEY;

    if @APPLICATIONCURRENCYID = @TRANSACTIONCURRENCYID
    begin
        set @APPLICATIONAMOUNT = @TRANSACTIONAMOUNT;
    end
    else
    begin
        if @APPLICATIONCURRENCYID = @BASECURRENCYID
        begin
            if @BASEEXCHANGERATEID <> '00000000-0000-0000-0000-000000000001'
            begin
                select
                    @APPLICATIONEXCHANGERATE = RATE,
                    @APPLICATIONEXCHANGERATEID = ID
                from dbo.CURRENCYEXCHANGERATE
                where ID = @BASEEXCHANGERATEID;
            end
            else
            begin
                set @APPLICATIONEXCHANGERATE = @SPOTRATE;
            end
        end
        else
        begin
            set @APPLICATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID, @APPLICATIONCURRENCYID, @REVENUEDATE, 1, null);
            if @APPLICATIONEXCHANGERATEID is null
            begin
                --If we have no exchange rate, we can't calculate the receipt amount

                return 0;
            end

            select @APPLICATIONEXCHANGERATE = RATE
            from dbo.CURRENCYEXCHANGERATE
            where ID = @APPLICATIONEXCHANGERATEID;
        end

        --Convert the transaction amount to the event's base currency

        set @APPLICATIONAMOUNT = dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(@TRANSACTIONAMOUNT, @APPLICATIONEXCHANGERATE),@APPLICATIONCURRENCYDECIMALDIGITS,@APPLICATIONCURRENCYROUNDINGTYPE);
    end


    declare @RECEIPTAMOUNT money;


            --Get total registration price and the info needed to calculate total registration cost

            declare @APPLICATIONTOTALPRICE money;
            declare @APPLICATIONTOTALCOST money;
            if @REGISTRATIONEXISTSINBATCH = 0
            begin
                select
                    @APPLICATIONTOTALPRICE = sum(REGISTRANTREGISTRATION.AMOUNT), --The amount of the registration

                    @APPLICATIONTOTALCOST = sum(EVENTPRICE.COST * REGISTRANTREGISTRATION.QUANTITY) --The cost of the registration option * the number of options selected

                from
                    dbo.REGISTRANTREGISTRATION
                    inner join dbo.EVENTPRICE on EVENTPRICE.ID = REGISTRANTREGISTRATION.EVENTPRICEID
                where
                    REGISTRANTREGISTRATION.REGISTRANTID = @REGISTRANTID 
                    and (
                        REGISTRANTREGISTRATION.AMOUNT > 0
                        or EVENTPRICE.AMOUNT = 0
                    );
            end
            else
            begin
                select
                    @APPLICATIONTOTALPRICE = sum(BATCHREVENUEREGISTRANTREGISTRATION.AMOUNT), --The amount of the registration

                    @APPLICATIONTOTALCOST = sum(EVENTPRICE.COST * BATCHREVENUEREGISTRANTREGISTRATION.QUANTITY) --The cost of the registration option * the number of options selected

                from
                    dbo.BATCHREVENUEREGISTRANTREGISTRATION
                    inner join dbo.EVENTPRICE on EVENTPRICE.ID = BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID
                where
                    BATCHREVENUEREGISTRANTREGISTRATION.REGISTRANTID = @REGISTRANTID 
                    and
                    (
                        BATCHREVENUEREGISTRANTREGISTRATION.AMOUNT > 0
                        or EVENTPRICE.AMOUNT = 0
                    );
            end

            --Get the amount previously applied to this registration

            declare @APPLICATIONPREVIOUSAMOUNT money;
            declare @APPLICATIONPREVIOUSRECEIPTAMOUNT money;
            declare @APPLICATIONPREVIOUSCOST money = 0;
            declare @APPLICATIONPREVIOUSBENEFITS money;

            select
                @APPLICATIONPREVIOUSAMOUNT = coalesce(sum(EVENTREGISTRANTPAYMENT.AMOUNT), 0),
                @APPLICATIONPREVIOUSRECEIPTAMOUNT = coalesce(sum(EVENTREGISTRANTPAYMENT.RECEIPTAMOUNT), 0)
             from
                dbo.EVENTREGISTRANTPAYMENT
                inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
                where
                EVENTREGISTRANTPAYMENT.REGISTRANTID = @REGISTRANTID
                and (@EXISTINGREVENUEID is null or FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID <> @EXISTINGREVENUEID)
                and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

            -- Fetch benefits amount applied in previous payment

            select @APPLICATIONPREVIOUSBENEFITS = isnull(sum(REVENUEBENEFIT.TOTALVALUE),0)
            from dbo.REVENUEBENEFIT
            inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUEBENEFIT.REVENUEID 
            inner join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = REVENUESPLIT.ID
            where REGISTRANTID =  @REGISTRANTID
            and REVENUEBENEFIT.UNITVALUE > 0;

            declare @TRANSACTIONTOTALPRICE money;
            declare @TRANSACTIONTOTALRECEIPT money;
            declare @TRANSACTIONPREVIOUSLYPAID money;
            declare @TRANSACTIONPREVIOUSLYRECEIPTED money;

            --Calculate the total expected receipts in transaction currency.

            if @APPLICATIONEXCHANGERATE <> 0
            begin
                -- Do an inverse conversion because we want to ensure that the transaction receipt amounts, when converted to the event's 

                -- currency, sum up to the registration's price minus cost

                set @TRANSACTIONTOTALPRICE = dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(@APPLICATIONTOTALPRICE, 1/@APPLICATIONEXCHANGERATE),@TRANSACTIONCURRENCYDECIMALDIGITS,@TRANSACTIONCURRENCYROUNDINGTYPE);
                set @TRANSACTIONTOTALRECEIPT = dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(@APPLICATIONTOTALPRICE - @APPLICATIONTOTALCOST, 1/@APPLICATIONEXCHANGERATE),@TRANSACTIONCURRENCYDECIMALDIGITS,@TRANSACTIONCURRENCYROUNDINGTYPE);
                set @TRANSACTIONPREVIOUSLYPAID = dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(@APPLICATIONPREVIOUSAMOUNT, 1/@APPLICATIONEXCHANGERATE),@TRANSACTIONCURRENCYDECIMALDIGITS,@TRANSACTIONCURRENCYROUNDINGTYPE);
                set @TRANSACTIONPREVIOUSLYRECEIPTED = dbo.UFN_CURRENCY_CONVERT(@APPLICATIONPREVIOUSRECEIPTAMOUNT, @APPLICATIONEXCHANGERATEID);
            end
            else
            begin
                set @TRANSACTIONTOTALPRICE = @APPLICATIONTOTALPRICE;
                set @TRANSACTIONTOTALRECEIPT = @APPLICATIONTOTALPRICE - @APPLICATIONTOTALCOST;
                set @TRANSACTIONPREVIOUSLYPAID = @APPLICATIONPREVIOUSAMOUNT;
                set @TRANSACTIONPREVIOUSLYRECEIPTED = @APPLICATIONPREVIOUSRECEIPTAMOUNT;
                set @APPLICATIONPREVIOUSCOST = @APPLICATIONPREVIOUSAMOUNT - @APPLICATIONPREVIOUSRECEIPTAMOUNT - @APPLICATIONPREVIOUSBENEFITS;
            end

            -- This percentage-based algorithm is meant to mimic logic found in USP_EVENT_ADDPAYMENT

            declare @PERCENTOFREMAINING float = 1;
            declare @TRANSACTIONAMOUNTREMAINING money = @TRANSACTIONTOTALPRICE - @TRANSACTIONPREVIOUSLYPAID;
                -- Handles when event cost is zero and Total Receipt amount is equal to registration fee only

            if @TRANSACTIONAMOUNTREMAINING =  @TRANSACTIONTOTALRECEIPT - @TRANSACTIONPREVIOUSLYPAID
                set @RECEIPTAMOUNT = @TRANSACTIONAMOUNT;
            else    
            begin
            if @TRANSACTIONAMOUNTREMAINING > 0 and @TRANSACTIONAMOUNT <= @TRANSACTIONAMOUNTREMAINING
            begin
                set @PERCENTOFREMAINING = @TRANSACTIONAMOUNT / @TRANSACTIONAMOUNTREMAINING;
            end

            set @RECEIPTAMOUNT = round((@TRANSACTIONTOTALRECEIPT - @TRANSACTIONPREVIOUSLYPAID + @APPLICATIONPREVIOUSCOST) * @PERCENTOFREMAINING, 2);
            end
                --Check if this is an event with designations, receipt amount calculation is different.

        if exists(
            select 1 from dbo.REGISTRANT 
                inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
                 inner join dbo.REGISTRANTDESIGNATION on REGISTRANTDESIGNATION.REGISTRANTID = REGISTRANT.ID 
            where 
                REGISTRANT.ID = @REGISTRANTID
                and EVENT.DESIGNATIONSONFEES = 1
                and REGISTRANTDESIGNATION.AMOUNT > 0)
            and exists(select 1 from dbo.REGISTRANTDESIGNATION where REGISTRANTID = @REGISTRANTID and AMOUNT > 0)
        begin
    if @REGISTRATIONEXISTSINBATCH = 0 
    begin
    select
      @EVENTPRICE = EVENTPRICE.AMOUNT
    from
      dbo.REGISTRANTREGISTRATION
      inner join dbo.EVENTPRICE on REGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
      inner join dbo.EVENT on EVENTPRICE.EVENTID = EVENT.ID
    where
      REGISTRANTREGISTRATION.REGISTRANTID = @REGISTRANTID;
      end 
  else 
     begin
    select
      @EVENTPRICE = EVENTPRICE.AMOUNT
    from
      dbo.BATCHREVENUEREGISTRANTREGISTRATION
      inner join dbo.EVENTPRICE on BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
      inner join dbo.EVENT on EVENTPRICE.EVENTID = EVENT.ID
    where
      REGISTRANTID = @REGISTRANTID;
     end

            --If this event registration is being paid in full, the benefits are also applied to the payment so just use the non-designation calculation result.

            if  @APPLICATIONTOTALPRICE <> @APPLICATIONAMOUNT and (@EVENTPRICE = 0 or @APPLICATIONAMOUNT % @EVENTPRICE <> 0)
            begin
                    declare @EVENTPORTIONBALANCE money = dbo.UFN_EVENTREGISTRANT_GETBALANCEEVENTPORTION(@REGISTRANTID);
                    declare @EVENTPORTIONAMOUNTAPPLIED money;
                    declare @REMAININGAMOUNTTOAPPLY money;

                    --Apply as much as possible towards the event portion before applying towards designations.

                    if @APPLICATIONAMOUNT >= @EVENTPORTIONBALANCE
                    begin
                     set @EVENTPORTIONAMOUNTAPPLIED = @EVENTPORTIONBALANCE;
                     set @REMAININGAMOUNTTOAPPLY = (@APPLICATIONAMOUNT - @EVENTPORTIONBALANCE);
                    end
                    else
                    begin
                     set @EVENTPORTIONAMOUNTAPPLIED =  @APPLICATIONAMOUNT;
                     set @REMAININGAMOUNTTOAPPLY = 0;
                    end

                        set @RECEIPTAMOUNT = @REMAININGAMOUNTTOAPPLY
            end

        end


    if @RECEIPTAMOUNT > @TRANSACTIONAMOUNT or @RECEIPTAMOUNT < 0
        set @RECEIPTAMOUNT = 0.00;

    return @RECEIPTAMOUNT;
end