USP_REGISTRANTREGISTRATION_CHANGEREGISTRATIONOPTION

CREATE procedure dbo.USP_REGISTRANTREGISTRATION_CHANGEREGISTRATIONOPTION
(
  @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
  @REGISTRATIONOPTIONID uniqueidentifier,   
  @REGISTRATIONOPTIONIDORIGINAL uniqueidentifier,
  @ROLECODE tinyint,
  @ROLECODEORIGINAL tinyint
)
as
begin try

  declare @IsAvailableToIndividual bit,
          @IsAvailableToTeamMember bit,
          @IsAvailableToTeamLeader bit,
          @IsAvailableToHouseholdMember bit,
          @IsAvailableToHeadHousehold bit,
          @IsAvailableToCompanyLeader bit,
          @amount money,
          @receiptamount money,
          @organizationamount money,
          @MAXQUANTITY int,
          @USEDQUANTITY int,
          @USEDQUANTITYORIGINAL int

  select @IsAvailableToCompanyLeader=FAFRT.ISREGTYPEAVAILABLECOMPANYLEADER,
         @IsAvailableToHeadHousehold=FAFRT.ISREGTYPEAVAILABLEHEADHOUSEHOLD,
         @IsAvailableToHouseholdMember=FAFRT.ISREGTYPEAVAILABLEFAMILYMEMBER,
         @IsAvailableToTeamLeader=FAFRT.ISREGTYPEAVAILABLETEAMLEADER,
         @IsAvailableToTeamMember=FAFRT.ISREGTYPEAVAILABLETEAMMEMBER,
         @IsAvailableToIndividual=FAFRT.ISREGTYPEAVAILABLEINDIVIDUAL,
     @amount=EP.amount, 
     @receiptamount=EP.receiptamount, 
     @organizationamount=EP.organizationamount 
  from dbo.EVENTPRICE EP
  join dbo.FAFREGISTRATIONTYPE FAFRT on EP.ID = FAFRT.EVENTPRICEID
  where FAFRT.EVENTPRICEID = @REGISTRATIONOPTIONID    

  SELECT @MAXQUANTITY = MAXQUANTITY from dbo.FAFREGISTRATIONTYPE where EVENTPRICEID=@REGISTRATIONOPTIONID
  SELECT @USEDQUANTITY = COUNT(*) from dbo.REGISTRANTREGISTRATION where EVENTPRICEID=@REGISTRATIONOPTIONID
  SELECT @USEDQUANTITYORIGINAL = COUNT(*) from dbo.REGISTRANTREGISTRATION where EVENTPRICEID=@REGISTRATIONOPTIONIDORIGINAL


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

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()

  if @REGISTRATIONOPTIONID <> @REGISTRATIONOPTIONIDORIGINAL
  begin
    if @MAXQUANTITY > 0 and @USEDQUANTITY >= @MAXQUANTITY
      RAISERROR('MAXQUANTITYERROR',13,1)

    if ( (@ROLECODE = 0 and @IsAvailableToCompanyLeader = 1) 
          or (@ROLECODE = 1 and @IsAvailableToTeamLeader = 1) 
          or (@ROLECODE = 2 and @IsAvailableToTeamMember = 1) 
          or (@ROLECODE = 3 and @IsAvailableToHeadHousehold = 1) 
          or (@ROLECODE = 4 and @IsAvailableToHouseholdMember = 1) 
          or (@ROLECODE = 5 and @IsAvailableToIndividual = 1) )
    begin
      update dbo.REGISTRANTREGISTRATION
        set EVENTPRICEID = @REGISTRATIONOPTIONID,
            AMOUNT = @amount,
            RECEIPTAMOUNT=@receiptamount,
            ORGANIZATIONAMOUNT=@organizationamount,
            DATECHANGED = @CURRENTDATE,
            CHANGEDBYID = @CHANGEAGENTID
        where REGISTRANTID = @ID   

      /* update registration type */
      update FAFREGISTRATIONTYPE set USEDQUANTITY = @USEDQUANTITY + 1 where EVENTPRICEID=@REGISTRATIONOPTIONID
      update FAFREGISTRATIONTYPE set USEDQUANTITY = @USEDQUANTITYORIGINAL - 1 where EVENTPRICEID=@REGISTRATIONOPTIONIDORIGINAL

      /* update registrant benefits */
      delete from dbo.REGISTRANTBENEFIT
      where REGISTRANTID = @ID 

      delete from dbo.REGISTRANTBENEFITEXTENSION
      where REGISTRANTID = @ID and BENEFITTYPECODE = 0 

      insert into dbo.REGISTRANTBENEFIT
      (ID, REGISTRANTID, BENEFITID, UNITVALUE, QUANTITY, TOTALVALUE, DETAILS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BASECURRENCYID, EVENTBASECURRENCYTOTALVALUE, ORGANIZATIONTOTALVALUE)
      select NEWID(), @ID, BENEFITID, UNITVALUE, QUANTITY, TOTALVALUE, DETAILS,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE, BASECURRENCYID, EVENTBASECURRENCYTOTALVALUE, ORGANIZATIONTOTALVALUE
      from dbo.EVENTPRICEBENEFIT  
      where EVENTPRICEID = @REGISTRATIONOPTIONID

      insert into dbo.REGISTRANTBENEFITEXTENSION
      (ID, REGISTRANTID, BENEFITID, BENEFITOPTIONID, BENEFITPREFERENCE,  ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BENEFITTYPECODE)
      select NEWID(), @ID, BENEFITID, null, '', @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE, 0
      from (select distinct BENEFITID, UNITVALUE, QUANTITY, TOTALVALUE, DETAILS, BASECURRENCYID, EVENTBASECURRENCYTOTALVALUE, ORGANIZATIONTOTALVALUE
              from EVENTPRICEBENEFIT  
              where EVENTPRICEID = @REGISTRATIONOPTIONID
            ) B -- use case when there are more than same benefit in an option, this table has restriction on this unique


    end 
    else
      raiserror('MISMATCHROLEANDOPTION', 13,1)
  end

end try
begin catch
  exec USP_RAISE_ERROR
  return 1;
end catch

return 0;