USP_FAFREGISTRANTREGISTRATION_DELETE

CREATE procedure dbo.USP_FAFREGISTRANTREGISTRATION_DELETE
(
    @ID uniqueidentifier, -- registrant ID

    @CHANGEAGENTID uniqueidentifier,
  @HASREFUND bit = 0,
  @CONSTITUENTID uniqueidentifier = null, -- constituent id to promote if promotion is required

  @REGISTRATIONOPTIONID uniqueidentifier = null 
)
as begin
    --check deletion rules, if any

  declare @ORIGINALCONSTITUENTID uniqueidentifier,
          @ORIGINALCONSTITUENTREGISTRATIONOPTIONID uniqueidentifier,
          @GROUPCONSTITUENTID uniqueidentifier,
          @PARENTGROUPCONSTITUENTID uniqueidentifier,
          @ORGANIZATIONCONSTITUENTID uniqueidentifier,
          @PARENTGROUPID uniqueidentifier,
          @PROMOTEREQUIRED bit ,
          @CANPROMOTE bit,
          @FRIENDLYPARAMID uniqueidentifier,
          @VANITYURLID int, 
          @FRIENDLYGROUPPARAMID uniqueidentifier,          
          @GROUPVANITYURLID int, 
          @CURRENTDATE datetime = getdate(),
          @ROLECODE tinyint,
          @TEAMFUNDRAISINGTEAMID uniqueidentifier,
          @EVENTID uniqueidentifier,
          @REGOPTIONFEE decimal

  declare @FINANCIALTRANSACTIONID uniqueidentifier,
          @FINANCIALTRANSACTIONLINEITEMID uniqueidentifier, 
          @BASECURRENCYID uniqueidentifier,  /* will use currency later */
          @TRANSACTIONCURRENCYID uniqueidentifier,  
          @BASEEXCHANGERATEID uniqueidentifier,  
          @EXCHANGERATE decimal(20,8),
          @REGAMOUNT decimal


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

  select  @ORIGINALCONSTITUENTID=R.CONSTITUENTID, 
          @ORIGINALCONSTITUENTREGISTRATIONOPTIONID = RR.EVENTPRICEID,
          @EVENTID=R.EVENTID, 
          @ORGANIZATIONCONSTITUENTID=RX.ORGANIZATIONCONSTITUENTID,
          @FRIENDLYPARAMID = FRURL.ID,
          @VANITYURLID = VURL.ID,
          @REGAMOUNT = RR.AMOUNT
  from dbo.REGISTRANT R (nolock)
  join dbo.REGISTRANTREGISTRATION RR (nolock) on R.ID = RR.REGISTRANTID
  join dbo.REGISTRANTEXTENSION RX (nolock) on R.ID = RX.REGISTRANTID  
  -- get vanity url if exists

  left join dbo.FAFFRIENDLYURLPARAMS FRURL on R.ID = FRURL.PARTICIPANTID and FRURL.PAGETYPE = 0 -- participant page URL

  left join dbo.VanityURL VURL on FRURL.SEQUENCEID = VURL.RealmID and VURL.RealmTypeID = 7
  where R.ID = @ID

  ;WITH myGroup AS 
  (
        select count(TFTM2.TEAMFUNDRAISINGTEAMID) as MEMBERSCNT, 
                TX.TEAMFUNDRAISINGTEAMID AS GROUPID, TX.TEAMCONSTITUENTID as GROUPCONSTITUENTID,  TX.TYPECODE, 
                PTFT.ID as PARENTGROUPID, PTX.TEAMCONSTITUENTID AS PARENTGROUPCONSTITUENTID,
          FRURL.ID as FRIENDLYGROUPPARAMID,
          VURL.ID as GROUPVANITYURLID,
          case when TFTC.ID is not null then 1 else 0 end as ISCAPTAIN
        from dbo.REGISTRANT R (nolock)
        join dbo.TEAMFUNDRAISER TF (nolock) on R.CONSTITUENTID = TF.CONSTITUENTID
        join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (nolock) on TF.ID = TFTM.TEAMFUNDRAISERID
        join dbo.TEAMEXTENSION TX (nolock) on TX.TEAMFUNDRAISINGTEAMID = TFTM.TEAMFUNDRAISINGTEAMID and TX.EVENTID = R.EVENTID
        join dbo.TEAMFUNDRAISINGTEAM TFT (nolock) on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
      left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TFTC (nolock) on TFTC.CONSTITUENTID = R.CONSTITUENTID and TFTC.TEAMFUNDRAISINGTEAMID = TX.TEAMFUNDRAISINGTEAMID
        -- get group parent group info

        left join dbo.TEAMFUNDRAISINGTEAM PTFT (nolock) on PTFT.ID = TFT.PARENTTEAMID
        left join dbo.TEAMEXTENSION PTX (nolock) on PTX.TEAMFUNDRAISINGTEAMID = PTFT.ID
        -- get members of current group

        left join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM2 (nolock) on TFTM2.TEAMFUNDRAISINGTEAMID = TFTM.TEAMFUNDRAISINGTEAMID   
      -- get vanity url if exists

      left join dbo.FAFFRIENDLYURLPARAMS FRURL on TX.TEAMFUNDRAISINGTEAMID = FRURL.PARTICIPANTID and FRURL.PAGETYPE = TX.TYPECODE 
      left join dbo.VanityURL VURL on FRURL.SEQUENCEID = VURL.RealmID and VURL.RealmTypeID = 7

        where R.ID = @ID
        group by TX.TEAMFUNDRAISINGTEAMID, TX.TEAMCONSTITUENTID, TX.TYPECODE, PTFT.ID, PTX.TEAMCONSTITUENTID, FRURL.ID,  VURL.ID, TFTC.ID
  )
  select @CANPROMOTE = case when MEMBERSCNT > 1 then 1 else 0 end, 
        @TEAMFUNDRAISINGTEAMID = isnull(@TEAMFUNDRAISINGTEAMID, GROUPID), -- take GroupID if @TEAMFUNDRAISINGTEAMID is not passed in 

        @PROMOTEREQUIRED = case when ISCAPTAIN = 1 and (select COUNT(TFTC.ID) from dbo.TEAMFUNDRAISINGTEAMCAPTAIN TFTC (nolock) where TFTC.TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID) < 2 then 1 else 0 end,
        @GROUPCONSTITUENTID=GROUPCONSTITUENTID, 
        @PARENTGROUPID=PARENTGROUPID, 
        @PARENTGROUPCONSTITUENTID=PARENTGROUPCONSTITUENTID,
        @FRIENDLYGROUPPARAMID = FRIENDLYGROUPPARAMID,
        @GROUPVANITYURLID = GROUPVANITYURLID,
        @ROLECODE = case when TYPECODE in (1,3) then TYPECODE when TYPECODE = 2 then 0 end -- rolecode = 1:team leader; 3: householdleader; 0: company leader

  from myGroup  

  -- /* promote this constituent */  

  if @PROMOTEREQUIRED = 1
  begin  
    if @CANPROMOTE = 1 and @CONSTITUENTID is not null
    begin
      update dbo.TEAMFUNDRAISINGTEAMCAPTAIN set 
        CONSTITUENTID = @CONSTITUENTID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED   = @CURRENTDATE
      where CONSTITUENTID = @ORIGINALCONSTITUENTID and TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID

      --- reassign org const to newly selected registrant

      update RX set
          ORGANIZATIONCONSTITUENTID = isnull(ORGANIZATIONCONSTITUENTID, @ORGANIZATIONCONSTITUENTID),
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED   = @CURRENTDATE
      from dbo.REGISTRANTEXTENSION RX
      join dbo.REGISTRANT R on RX.REGISTRANTID = R.ID
      where R.CONSTITUENTID=@CONSTITUENTID and R.EVENTID=@EVENTID

      select @REGOPTIONFEE=AMOUNT from dbo.EVENTPRICE where ID=@REGISTRATIONOPTIONID

      -- update registration option

      merge into dbo.REGISTRANTREGISTRATION as Target
      using 
      (
        select RR.ID from dbo.REGISTRANTREGISTRATION RR (nolock)
        join dbo.REGISTRANT R (nolock) on RR.REGISTRANTID = R.ID 
        where R.CONSTITUENTID = @CONSTITUENTID
                and R.EVENTID = @EVENTID
      ) as source on source.ID = Target.ID
      when matched then
          update set
              EVENTPRICEID = isnull(@REGISTRATIONOPTIONID,EVENTPRICEID),
          AMOUNT = case when @REGOPTIONFEE = 0 then AMOUNT else @REGOPTIONFEE end,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED   = @CURRENTDATE;

    end 
    else if @CANPROMOTE = 0 -- no promotion but check if this group is a parent of any group

    begin    
      -- no need to delete captain/member/fundraiser since registrant delete will take care of it


      -- /* move group credit to parent or event if this group will be deleted */

      merge into dbo.REVENUERECOGNITION as Target
      using
      (
          select RR.ID 
          from dbo.REVENUE_EXT R 
          join dbo.EVENT E on R.APPEALID = E.APPEALID
          join dbo.EVENTEXTENSION EX on EX.EVENTID = E.ID
          join dbo.FINANCIALTRANSACTIONLINEITEM RS on R.ID = RS.FINANCIALTRANSACTIONID
          join dbo.REVENUERECOGNITION RR on RS.ID = RR.REVENUESPLITID
          where RR.CONSTITUENTID =@GROUPCONSTITUENTID    and EX.EVENTID = @EVENTID
      ) as source on Target.ID = source.ID 
      when matched and (@PARENTGROUPCONSTITUENTID is not null) then
          update set 
              CONSTITUENTID = @PARENTGROUPCONSTITUENTID,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CURRENTDATE
      when matched then
            delete;

      if exists (select ID from dbo.TEAMFUNDRAISINGTEAM where PARENTTEAMID = @TEAMFUNDRAISINGTEAMID and @PARENTGROUPID is not null )
      begin
        declare @groupidtomerge uniqueidentifier

        declare grpCursor cursor local fast_forward for
          select ID from TEAMFUNDRAISINGTEAM where PARENTTEAMID = @TEAMFUNDRAISINGTEAMID 

        open grpCursor fetch next from grpCursor into @groupidtomerge
        while @@fetch_status = 0
        begin
          exec dbo.USP_DATAFORMTEMPLATE_EDIT_FAFGROUPMERGE 
                        @ID=@groupidtomerge, 
                        @CHANGEAGENTID=@CHANGEAGENTID, 
                        @GROUPTOMERGEID=@groupidtomerge,                         
                        @GROUPTOKEEPID=@PARENTGROUPID

          fetch next from grpCursor into @groupidtomerge
        end        
        close grpCursor
        deallocate grpCursor   

      end
      else
      begin
        update dbo.TEAMFUNDRAISINGTEAM set
          PARENTTEAMID = null,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED   = @CURRENTDATE
        where PARENTTEAMID = @TEAMFUNDRAISINGTEAMID
      end

      -- /* release friendly URL used by this group */

       if @FRIENDLYGROUPPARAMID is not null
       begin
          delete from dbo.FAFFRIENDLYURLPARAMS where ID = @FRIENDLYGROUPPARAMID
          delete from dbo.VanityURL where ID = @GROUPVANITYURLID
       end

      exec dbo.USP_TEAMFUNDRAISINGTEAM_DELETE @ID=@TEAMFUNDRAISINGTEAMID, @CHANGEAGENTID=@CHANGEAGENTID
      set @GROUPCONSTITUENTID = null -- reset so recognition will go to parent group or to event.


    end
  end  


  --/* remove registration payment */

  if @HASREFUND = 1
  begin

    select @FINANCIALTRANSACTIONID=FTLI.FINANCIALTRANSACTIONID, @FINANCIALTRANSACTIONLINEITEMID=FTLI.ID, @REGAMOUNT = FTLI.TRANSACTIONAMOUNT,
           @BASECURRENCYID = R.BASECURRENCYID,
           @TRANSACTIONCURRENCYID = R.TRANSACTIONCURRENCYID,
           @BASEEXCHANGERATEID = R.BASEEXCHANGERATEID, 
           @EXCHANGERATE = 
                        case
                            when R.BASEEXCHANGERATEID is not null
                                then CE.RATE
                            when R.TRANSACTIONCURRENCYID = R.BASECURRENCYID
                                then 1
                            else 0
                        end
    from dbo.EVENTREGISTRANTPAYMENT ERP (nolock)
      join dbo.FINANCIALTRANSACTIONLINEITEM FTLI  (nolock) on ERP.PAYMENTID = FTLI.ID
    join dbo.REVENUE R (nolock) on FTLI.FINANCIALTRANSACTIONID = R.ID
    left join dbo.CURRENCYEXCHANGERATE CE (nolock) on CE.ID = R.BASEEXCHANGERATEID
      where ERP.REGISTRANTID = @ID              

    delete dbo.EVENTREGISTRANTPAYMENT where REGISTRANTID = @ID and PAYMENTID = @FINANCIALTRANSACTIONLINEITEMID

    if (select Count(FT.ID) from dbo.FINANCIALTRANSACTIONLINEITEM FT where FT.FINANCIALTRANSACTIONID = @FINANCIALTRANSACTIONID ) > 1
    begin
        delete from dbo.FINANCIALTRANSACTIONLINEITEM where ID = @FINANCIALTRANSACTIONLINEITEMID

        update dbo.FINANCIALTRANSACTION set
          TRANSACTIONAMOUNT = TRANSACTIONAMOUNT - @REGAMOUNT,  
          BASEAMOUNT = BASEAMOUNT - @REGAMOUNT,  
          ORGAMOUNT = ORGAMOUNT - @REGAMOUNT, 
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED  = @CURRENTDATE   
        where ID = @FINANCIALTRANSACTIONID

    end
    else if exists(select ID from dbo.REVENUEPOSTED where ID=@FINANCIALTRANSACTIONID)
        exec dbo.USP_REVENUETRANSACTION_DELETEPOSTED @FINANCIALTRANSACTIONID, @CHANGEAGENTID
    else
        exec dbo.USP_REVENUETRANSACTION_DELETE @FINANCIALTRANSACTIONID, @CHANGEAGENTID

  end
  else
    delete dbo.EVENTREGISTRANTPAYMENT where REGISTRANTID = @ID

  --/* free up friendly URL */

  if @FRIENDLYPARAMID is not null
  begin
    delete from dbo.FAFFRIENDLYURLPARAMS where ID = @FRIENDLYPARAMID
    delete from dbo.VanityURL where ID = @VANITYURLID
  end

  --/* finally move credits */  

  merge into dbo.REVENUERECOGNITION as Target
  using
  (
      select RR.ID 
      from dbo.REVENUE_EXT R 
      join dbo.EVENT E on R.APPEALID = E.APPEALID
      join dbo.EVENTEXTENSION EX on EX.EVENTID = E.ID
      join dbo.FINANCIALTRANSACTIONLINEITEM RS on R.ID = RS.FINANCIALTRANSACTIONID
      join dbo.REVENUERECOGNITION RR on RS.ID = RR.REVENUESPLITID
      where RR.CONSTITUENTID = @ORIGINALCONSTITUENTID    and EX.EVENTID = @EVENTID
  ) as source on Target.ID = source.ID 
  when matched and (@GROUPCONSTITUENTID is not null or @PARENTGROUPCONSTITUENTID is not null) then
      update set 
          CONSTITUENTID = isnull(@GROUPCONSTITUENTID,@PARENTGROUPCONSTITUENTID),
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
  when matched then
        delete;

  -- update this new leader's address book (members and general donations donors)

  -- or update other leaders' address book to include the new general donors (generated by this registration delete)

  --if @PROMOTEREQUIRED = 1 and @CANPROMOTE = 1 and @CONSTITUENTID is not null  

  --    exec dbo.USP_ADDRESSBOOKFAF_BULKADD_CONTACTBYGROUP @GROUPID=@TEAMFUNDRAISINGTEAMID, @CHANGEAGENTID=@CHANGEAGENTID, @REGISTRANTCONSTITUENTID=@CONSTITUENTID, @ROLECODE=@ROLECODE

  --else   

  if (select COUNT(ID) from dbo.TEAMFUNDRAISINGTEAMCAPTAIN (nolock) where TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID ) > 0
  begin
    declare @leaderConstituentID uniqueidentifier

    declare leaderCursor cursor local fast_forward for
      select CONSTITUENTID from dbo.TEAMFUNDRAISINGTEAMCAPTAIN where TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID

    open leaderCursor fetch next from leaderCursor into @leaderConstituentID
    while @@fetch_status = 0
    begin
      exec dbo.USP_ADDRESSBOOKFAF_BULKADD_CONTACTBYGROUP 
                    @GROUPID=@TEAMFUNDRAISINGTEAMID, 
                    @CHANGEAGENTID=@CHANGEAGENTID, 
                    @REGISTRANTCONSTITUENTID=@leaderConstituentID, 
                    @ROLECODE=@ROLECODE

      fetch next from leaderCursor into @leaderConstituentID
    end        
    close leaderCursor
    deallocate leaderCursor           
  end


  --/* free up a registration slot */

  merge into dbo.FAFREGISTRATIONTYPE as Target
  using
  (
      select FRT.ID, FRT.USEDQUANTITY
      from dbo.REGISTRANTREGISTRATION RR (nolock)
      join dbo.EVENTPRICE EP (nolock) on EP.ID = RR.EVENTPRICEID
      join dbo.FAFREGISTRATIONTYPE FRT (nolock) on EP.ID = FRT.EVENTPRICEID
      where RR.REGISTRANTID = @ID
  ) as Source on Source.ID = Target.ID
  when matched then
      update set USEDQUANTITY = source.USEDQUANTITY - 1,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE;

  /* change subscription */  
  delete from dbo.NotificationSubscription 
  where UserID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@ORIGINALCONSTITUENTID)) 
        and NotificationID in (select NCNOTIFICATIONID from dbo.EVENTEMAILTEMPLATE where EVENTID = @EVENTID)

  exec dbo.USP_FAFNOTIFICATION_SUBSCRIPTION_ADDUPDATE @CONSTITUENTID=@CONSTITUENTID, @EVENTID=@EVENTID, @ROLECODE=@ROLECODE

  insert into dbo.REGISTRATIONDELETEHISTORY(REGISTRANTID, REGISTRATIONAMOUNT, CONSTITUENTID, EVENTID, EVENTPRICEID, REVENUESPLITID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  values (@ID, isnull(@REGAMOUNT, 0), @ORIGINALCONSTITUENTID, @EVENTID, @ORIGINALCONSTITUENTREGISTRATIONOPTIONID, @FINANCIALTRANSACTIONLINEITEMID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE)

  /*finally, delete this registrant */
  exec dbo.USP_REGISTRANT_DELETE @ID, @CHANGEAGENTID

    return 0;

end