UFN_REVENUEBATCH_EVENTREGISTRANT_GETPAYMENTBENEFITS

create function dbo.UFN_REVENUEBATCH_EVENTREGISTRANT_GETPAYMENTBENEFITS
(
  @REGISTRANTID uniqueidentifier,
  @AMOUNT money = null,
  @TRANSACTIONCURRENCYID uniqueidentifier = null,
  @BASECURRENCYID uniqueidentifier = null,
  @EXCHANGERATE decimal(20, 8) = null,
  @BASEEXCHANGERATEID uniqueidentifier = null,
  @MUSTPAYFULLREGISTRATIONAMOUNT bit = 0
)
returns @BENEFITS table
(
  BENEFITID uniqueidentifier,
  QUANTITY int,
  UNITVALUE money,
  DETAILS nvarchar(255),
  BENEFITNAME nvarchar(100),
  TRANSACTIONTOTALVALUE money
)
as
begin

  if @MUSTPAYFULLREGISTRATIONAMOUNT = 1
  begin

    declare @REGISTRATIONAMOUNT money;
    declare @REGISTRATIONCURRENCYID uniqueidentifier;

      select
          @REGISTRATIONAMOUNT = sum(REGISTRANTREGISTRATION.AMOUNT),
          @REGISTRATIONCURRENCYID = EVENT.BASECURRENCYID
      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 and
          -- Exclude registrations that had their registration fees waived

          REGISTRANTREGISTRATION.AMOUNT > 0
      group by EVENT.BASECURRENCYID;


    declare @AMOUNTINCURRENCY money;
    declare @REGISTRATIONAMOUNTINCURRENCY money;

    if @TRANSACTIONCURRENCYID = @REGISTRATIONCURRENCYID
    begin
      set @AMOUNTINCURRENCY = @AMOUNT;
      set @REGISTRATIONAMOUNTINCURRENCY = @REGISTRATIONAMOUNT;
    end
    else
    begin

      if @BASEEXCHANGERATEID is not null or coalesce(@EXCHANGERATE, 0) <> 0
      begin
        if coalesce(@EXCHANGERATE, 0) <> 0
          set @AMOUNTINCURRENCY = dbo.UFN_CURRENCY_APPLYRATE(@AMOUNT, @EXCHANGERATE);
        else
          set @AMOUNTINCURRENCY = dbo.UFN_CURRENCY_CONVERT(@AMOUNT, @BASEEXCHANGERATEID);

        if @BASECURRENCYID = @REGISTRATIONCURRENCYID
        begin
          set @REGISTRATIONAMOUNTINCURRENCY = @REGISTRATIONAMOUNT;
        end
        else
        begin
          declare @TODAY date = getdate();
          declare @CORPORATERATETYPECODE tinyint = 0;
          declare @REGISTRATIONEXCHANGERATEID uniqueidentifier = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@REGISTRATIONCURRENCYID, @BASECURRENCYID, @TODAY, 
            @CORPORATERATETYPECODE, default); 

          if @REGISTRATIONEXCHANGERATEID is not null
            set @REGISTRATIONAMOUNTINCURRENCY = dbo.UFN_CURRENCY_CONVERT(@REGISTRATIONAMOUNT, @REGISTRATIONEXCHANGERATEID);

        end
      end
    end

    if @AMOUNTINCURRENCY is null or @REGISTRATIONAMOUNTINCURRENCY is null or @AMOUNTINCURRENCY <> @REGISTRATIONAMOUNTINCURRENCY
      return;
  end

  insert into
    @BENEFITS
    select
        REGISTRANTBENEFIT.BENEFITID,
        sum(REGISTRANTBENEFIT.QUANTITY) as QUANTITY,
        -- When the registration fee is waived, set the unit value to 0

        case
            when REGISTRANTREGISTRATION.AMOUNT = 0 then cast (0 as money)
            else REGISTRANTBENEFIT.UNITVALUE
        end as UNITVALUE,
        REGISTRANTBENEFIT.DETAILS,
        BENEFIT.NAME as BENEFITNAME,
        case
            when REGISTRANTREGISTRATION.AMOUNT = 0 then
        cast (0 as money)
            when REGISTRANTBENEFIT.BASECURRENCYID = @TRANSACTIONCURRENCYID then
        REGISTRANTBENEFIT.UNITVALUE * sum(REGISTRANTBENEFIT.QUANTITY)
            else
        dbo.UFN_CURRENCY_CONVERT
        (
                    REGISTRANTBENEFIT.UNITVALUE * sum(REGISTRANTBENEFIT.QUANTITY),
                    dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST
          (
                        REGISTRANTBENEFIT.BASECURRENCYID,
                        @TRANSACTIONCURRENCYID,
                        getdate(),
                        1,
                        default
                    )
                )
        end as TRANSACTIONTOTALVALUE
    from
    dbo.REGISTRANT
    inner join
    dbo.REGISTRANTBENEFIT on REGISTRANTBENEFIT.REGISTRANTID = REGISTRANT.ID
    inner join
    dbo.BENEFIT on BENEFIT.ID = REGISTRANTBENEFIT.BENEFITID
    left join
    dbo.REGISTRANTREGISTRATIONMAP on REGISTRANTREGISTRATIONMAP.REGISTRANTID = REGISTRANT.ID
    left join
    dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATION.ID = REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID
    where
        (
            REGISTRANT.ID = @REGISTRANTID
      or
            REGISTRANT.GUESTOFREGISTRANTID = @REGISTRANTID
        )
    and
        BENEFITSWAIVED = 0
    and
        REGISTRANTBENEFIT.BASECURRENCYID = @BASECURRENCYID
    group by
        REGISTRANTBENEFIT.BENEFITID,
        REGISTRANTBENEFIT.DETAILS,
        BENEFIT.NAME,
        REGISTRANTBENEFIT.UNITVALUE,
        REGISTRANTREGISTRATION.AMOUNT,
        REGISTRANTBENEFIT.BASECURRENCYID;

  return;
end