USP_DATAFORMTEMPLATE_EDIT_EVENTBASECURRENCY

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_EVENTBASECURRENCY
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @BASECURRENCYID uniqueidentifier
)
as

  set nocount on;

  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  declare @CURRENTDATE datetime;
  set @CURRENTDATE = getdate();

  if exists (select 1 from dbo.EVENTPRICE where EVENTID = @ID)
    raiserror('ERR_EVENTBASECURRENCYEDIT_EVENTPRICESEXIST',13,1);

  if exists (select top 1 1 from dbo.JOBOCCURRENCE where EVENTID = @ID)
    raiserror('ERR_EVENTBASECURRENCYEDIT_JOBOCCURRENCESEXIST',13,1);

  if exists (select top 1 1 from dbo.AUCTIONITEM where AUCTIONITEM.EVENTAUCTIONID = @ID)
    raiserror('BBERR_AUCTIONITEM_EVENT_BASECURRENCY', 13, 1);

  if exists (select top 1 1 from dbo.EVENTAPPEAL where EVENTAPPEAL.EVENTID = @ID)
      raiserror('ERR_EVENTBASECURRENCYEDIT_EVENTAPPEALEXIST', 13, 1);

  if exists (select top 1 1 from dbo.EVENT where EVENT.ID = @ID and EVENT.APPEALID is not null)
    raiserror('ERR_EVENTBASECURRENCYEDIT_TEAMFUNDRAISINGAPPEALEXIST', 13, 1);

  begin try
    declare @OLDBASECURRENCYID uniqueidentifier;
    select 
      @OLDBASECURRENCYID = EVENT.BASECURRENCYID
    from 
      dbo.EVENT
    where
      EVENT.ID = @ID;

    update dbo.EVENT set
      EVENT.BASECURRENCYID = @BASECURRENCYID,
      EVENT.CHANGEDBYID = @CHANGEAGENTID,
      EVENT.DATECHANGED = @CURRENTDATE
    where
      EVENT.ID = @ID;

    --If the user saves without changing the base currency, don't update org amounts.

    if @OLDBASECURRENCYID <> @BASECURRENCYID
    begin
      declare @ORGCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
      declare @DATEADDED datetime;

      select @DATEADDED = EVENT.DATEADDED from dbo.EVENT where EVENT.ID = @ID;

      --If the new base currency is the org currency, org amount equals amount, so don't bother getting

      --  an exchange rate or doing a conversion.

      if @BASECURRENCYID = @ORGCURRENCYID
      begin

        update dbo.EVENTPRICE
        set
          EVENTPRICE.ORGANIZATIONAMOUNT = EVENTPRICE.AMOUNT,
          EVENTPRICE.ORGANIZATIONCOST = EVENTPRICE.COST,
          EVENTPRICE.ORGANIZATIONEXCHANGERATEID = null,
          EVENTPRICE.CHANGEDBYID = @CHANGEAGENTID,
          EVENTPRICE.DATECHANGED = @CURRENTDATE
        where
          EVENTPRICE.EVENTID = @ID;

        update dbo.REGISTRANTREGISTRATION
        set
          REGISTRANTREGISTRATION.ORGANIZATIONAMOUNT = REGISTRANTREGISTRATION.AMOUNT,
          REGISTRANTREGISTRATION.ORGANIZATIONEXCHANGERATEID = null,
          REGISTRANTREGISTRATION.CHANGEDBYID = @CHANGEAGENTID,
          REGISTRANTREGISTRATION.DATECHANGED = @CURRENTDATE
        from
          dbo.REGISTRANTREGISTRATION
          inner join dbo.REGISTRANT on REGISTRANTREGISTRATION.REGISTRANTID = REGISTRANT.ID
        where
          REGISTRANT.EVENTID = @ID;

        update dbo.EVENTEXPENSE
        set 
            EVENTEXPENSE.ORGANIZATIONBUDGETEDAMOUNT = EVENTEXPENSE.BUDGETEDAMOUNT,
            EVENTEXPENSE.ORGANIZATIONACTUALAMOUNT = EVENTEXPENSE.ACTUALAMOUNT,
            EVENTEXPENSE.ORGANIZATIONAMOUNTPAID = EVENTEXPENSE.AMOUNTPAID,
            EVENTEXPENSE.ORGANIZATIONEXCHANGERATEID = null,
            EVENTEXPENSE.BASECURRENCYID = @BASECURRENCYID,
            EVENTEXPENSE.CHANGEDBYID = @CHANGEAGENTID,
            EVENTEXPENSE.DATECHANGED = @CURRENTDATE
        where EVENTEXPENSE.EVENTID = @ID;

      end
      else --Base currency has changed and isn't the org currency

      begin

        declare @CURRENCYEXCHANGERATEID uniqueidentifier = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, @DATEADDED, 0, null);

        update dbo.EVENTPRICE
        set
          EVENTPRICE.ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(EVENTPRICE.AMOUNT, @CURRENCYEXCHANGERATEID),
          EVENTPRICE.ORGANIZATIONCOST = dbo.UFN_CURRENCY_CONVERT(EVENTPRICE.COST, @CURRENCYEXCHANGERATEID),
          EVENTPRICE.ORGANIZATIONEXCHANGERATEID = @CURRENCYEXCHANGERATEID,
          EVENTPRICE.CHANGEDBYID = @CHANGEAGENTID,
          EVENTPRICE.DATECHANGED = @CURRENTDATE
        where
          EVENTPRICE.EVENTID = @ID;

        update dbo.REGISTRANTREGISTRATION
        set
          REGISTRANTREGISTRATION.ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(REGISTRANTREGISTRATION.AMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, REGISTRANTREGISTRATION.DATEPURCHASED, 0, null)),
          REGISTRANTREGISTRATION.ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, REGISTRANTREGISTRATION.DATEPURCHASED, 0, null),
          REGISTRANTREGISTRATION.CHANGEDBYID = @CHANGEAGENTID,
          REGISTRANTREGISTRATION.DATECHANGED = @CURRENTDATE
        from
          dbo.REGISTRANTREGISTRATION
          inner join dbo.REGISTRANT on REGISTRANTREGISTRATION.REGISTRANTID = REGISTRANT.ID
        where
          REGISTRANT.EVENTID = @ID;

        update dbo.EVENTEXPENSE
        set 
            EVENTEXPENSE.ORGANIZATIONBUDGETEDAMOUNT = dbo.UFN_CURRENCY_CONVERT(EVENTEXPENSE.BUDGETEDAMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, EVENTEXPENSE.DATEADDED, 0, null)),
            EVENTEXPENSE.ORGANIZATIONACTUALAMOUNT = dbo.UFN_CURRENCY_CONVERT(EVENTEXPENSE.ACTUALAMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, EVENTEXPENSE.DATEADDED, 0, null)),
            EVENTEXPENSE.ORGANIZATIONAMOUNTPAID = dbo.UFN_CURRENCY_CONVERT(EVENTEXPENSE.AMOUNTPAID, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, EVENTEXPENSE.DATEADDED, 0, null)),
            EVENTEXPENSE.ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, EVENTEXPENSE.DATEADDED, 0, null),
            EVENTEXPENSE.BASECURRENCYID = @BASECURRENCYID,
            EVENTEXPENSE.CHANGEDBYID = @CHANGEAGENTID,
            EVENTEXPENSE.DATECHANGED = @CURRENTDATE
        where EVENTEXPENSE.EVENTID = @ID;
      end

      -- Update the base exchange rate and event base currency total value on event price benefits

      update dbo.EVENTPRICEBENEFIT
      set
        EVENTPRICEBENEFIT.BASEEXCHANGERATEID = 
          case when @BASECURRENCYID = EVENTPRICEBENEFIT.BASECURRENCYID
            then null
            else dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENTPRICEBENEFIT.BASECURRENCYID, @BASECURRENCYID, @DATEADDED, 1, null)
          end,
        EVENTPRICEBENEFIT.EVENTBASECURRENCYTOTALVALUE = 
          case when @BASECURRENCYID = EVENTPRICEBENEFIT.BASECURRENCYID
            then EVENTPRICEBENEFIT.TOTALVALUE
            else dbo.UFN_CURRENCY_CONVERT(EVENTPRICEBENEFIT.TOTALVALUE,dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EVENTPRICEBENEFIT.BASECURRENCYID, @BASECURRENCYID, @DATEADDED, 1, null))
          end,
        EVENTPRICEBENEFIT.CHANGEDBYID = @CHANGEAGENTID,
        EVENTPRICEBENEFIT.DATECHANGED = @CURRENTDATE
      from
        dbo.EVENTPRICEBENEFIT
        inner join dbo.EVENTPRICE on EVENTPRICEBENEFIT.EVENTPRICEID = EVENTPRICE.ID
      where
        EVENTPRICE.EVENTID = @ID;


      -- Update the base exchange rate and event base currency total value on registrant benefits

      update dbo.REGISTRANTBENEFIT
      set
        REGISTRANTBENEFIT.BASEEXCHANGERATEID = 
          case when @BASECURRENCYID = REGISTRANTBENEFIT.BASECURRENCYID
            then null
            else dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(REGISTRANTBENEFIT.BASECURRENCYID, @BASECURRENCYID, @DATEADDED, 1, null)
          end,
        REGISTRANTBENEFIT.EVENTBASECURRENCYTOTALVALUE = 
          case when @BASECURRENCYID = REGISTRANTBENEFIT.BASECURRENCYID
            then REGISTRANTBENEFIT.TOTALVALUE
            else dbo.UFN_CURRENCY_CONVERT(REGISTRANTBENEFIT.TOTALVALUE,dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(REGISTRANTBENEFIT.BASECURRENCYID, @BASECURRENCYID, @DATEADDED, 1, null))
          end,
        REGISTRANTBENEFIT.CHANGEDBYID = @CHANGEAGENTID,
        REGISTRANTBENEFIT.DATECHANGED = @CURRENTDATE
      from
        dbo.REGISTRANTBENEFIT
        inner join dbo.REGISTRANT on REGISTRANTBENEFIT.REGISTRANTID = REGISTRANT.ID
      where
        REGISTRANT.EVENTID = @ID;

      --The receipt amount needs to take into account the benefits in the new currency of the event.

      update dbo.EVENTPRICE
      set
        RECEIPTAMOUNT = (EVENTPRICE.AMOUNT - (EVENTPRICE.COST + (select coalesce(sum(EVENTPRICEBENEFIT.EVENTBASECURRENCYTOTALVALUE),0) from dbo.EVENTPRICEBENEFIT where EVENTPRICEID = EVENTPRICE.ID))),
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      from 
        dbo.EVENTPRICE
      where
        EVENTPRICE.EVENTID = @ID;

      --The receipt amount needs to take into account the benefits in the new currency of the event.

      update dbo.REGISTRANTREGISTRATION
      set
        REGISTRANTREGISTRATION.RECEIPTAMOUNT =
          case when REGISTRANT.BENEFITSWAIVED = 1
            then (EVENTPRICE.AMOUNT - EVENTPRICE.COST)
            else EVENTPRICE.RECEIPTAMOUNT
          end,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      from 
        dbo.REGISTRANTREGISTRATION
        inner join dbo.EVENTPRICE on REGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
        left join dbo.REGISTRANTREGISTRATIONMAP on REGISTRANTREGISTRATION.ID = REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID
        left join dbo.REGISTRANT on REGISTRANTREGISTRATIONMAP.REGISTRANTID = REGISTRANT.ID
      where
        EVENTPRICE.EVENTID = @ID
        and REGISTRANTREGISTRATION.AMOUNT <> 0; --Registration fee waived.

    end
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
  end catch

  return 0;