USP_FAFGROUPMEMBERUPDATE_DELETE

CREATE procedure dbo.USP_FAFGROUPMEMBERUPDATE_DELETE
(
          @ID uniqueidentifier, -- group to delete

          @EVENTID uniqueidentifier,
             @CHANGEAGENTID uniqueidentifier = null,
          @NEWROLE tinyint,
          @REGISTRATIONOPTIONID uniqueidentifier
        )
        as
        begin try
          declare @PARENTGROUPID uniqueidentifier = null,
                  @AMOUNT money,
                  @RECEIPTAMOUNT money,
                  @ORGANIZATIONAMOUNT money,
                  @CURRENTDATE date,
                  @MAXQUANTITY int,
                  @EVENTPRICEID uniqueidentifier = null,
                  @EVENTPRICECOUNT int

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

          set @CURRENTDATE = getdate()

          if exists(select ID from dbo.EVENTPRICE where ID = @REGISTRATIONOPTIONID and EVENTID = @EVENTID)
          begin
            select @EVENTPRICECOUNT = COUNT(*) from dbo.REGISTRANTREGISTRATION where EVENTPRICEID = @REGISTRATIONOPTIONID 
                  select @MAXQUANTITY = MAXQUANTITY from dbo.FAFREGISTRATIONTYPE where EVENTPRICEID = @REGISTRATIONOPTIONID 
                  if (@MAXQUANTITY = 0 or @EVENTPRICECOUNT < @MAXQUANTITY) 
                    begin
              select @AMOUNT=AMOUNT, @RECEIPTAMOUNT=RECEIPTAMOUNT, @ORGANIZATIONAMOUNT=ORGANIZATIONAMOUNT from dbo.EVENTPRICE where ID = @REGISTRATIONOPTIONID

              -- get parent group of this group if exists else parent group id is null

              select @PARENTGROUPID=PARENTTEAMID from dbo.TEAMFUNDRAISINGTEAM where ID = @ID  

              -- updating child teams to reference to parent team of the current team whether current team has parent or not

              update dbo.TEAMFUNDRAISINGTEAM
              set   PARENTTEAMID = @PARENTGROUPID
              where PARENTTEAMID = @ID

              select TF.CONSTITUENTID, TFTM.TEAMFUNDRAISINGTEAMID, R.ID as REGISTRANTID, TF.ID as TEAMFUNDRAISERID into #tmpTeamMembers
              from dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM
              join dbo.TEAMFUNDRAISER TF on TFTM.TEAMFUNDRAISERID = TF.ID
              join dbo.REGISTRANT R on R.CONSTITUENTID = TF.CONSTITUENTID and R.EVENTID = @EVENTID
              where TFTM.TEAMFUNDRAISINGTEAMID = @ID

              update dbo.REGISTRANTREGISTRATION
                set EVENTPRICEID = @REGISTRATIONOPTIONID,
                    AMOUNT = @AMOUNT,
                    RECEIPTAMOUNT=@RECEIPTAMOUNT,
                    ORGANIZATIONAMOUNT=@ORGANIZATIONAMOUNT,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                where REGISTRANTID in (select REGISTRANTID from #tmpTeamMembers)

              delete from dbo.TEAMFUNDRAISINGTEAMCAPTAIN where TEAMFUNDRAISINGTEAMID = @ID

              -- update group member if group has parent group

              if @PARENTGROUPID is not null
              begin                          
                update dbo.TEAMFUNDRAISINGTEAMMEMBER 
                set TEAMFUNDRAISINGTEAMID = @PARENTGROUPID
                where TEAMFUNDRAISINGTEAMID = @ID   

                declare @REGISTRANTCONSTITUENTID uniqueidentifier
                while exists(select top 1 CONSTITUENTID from #tmpTeamMembers)
                begin
                    select top 1 @REGISTRANTCONSTITUENTID = CONSTITUENTID from #tmpTeamMembers

                    exec dbo.USP_FAFADDRESSBOOK_REGISTRANT_ADD 
                               @TEAMFUNDRAISINGTEAMID=@PARENTGROUPID, 
                               @REGISTRANTCONSTITUENTID=@REGISTRANTCONSTITUENTID,
                               @CHANGEAGENTID=@CHANGEAGENTID

                    delete from #tmpTeamMembers where CONSTITUENTID = @REGISTRANTCONSTITUENTID

                    continue
                end

              end
              else
              begin
                -- remove member record since we want these registrant to become individual

                delete dbo.TEAMFUNDRAISINGTEAMMEMBER where TEAMFUNDRAISINGTEAMID = @ID
                delete dbo.TEAMFUNDRAISER where ID in (select TEAMFUNDRAISERID from #tmpTeamMembers)
              end

              if @PARENTGROUPID is null 
                  update dbo.REGISTRANTBATCH
                  set TEAMFUNDRAISINGTEAMID = @PARENTGROUPID,
                      REGISTRANTROLECODE = 8,
                      FUNDRAISINGGROUPNAME = '',
                      CHANGEDBYID=@CHANGEAGENTID,
                      DATECHANGED=@CURRENTDATE
                  where TEAMFUNDRAISINGTEAMID = @ID
              else
              begin
                  declare @PTYPECODE tinyint,
                              @REGROLECODE tinyint

                  select @PTYPECODE=TYPECODE from dbo.TEAMEXTENSION (nolock) where TEAMFUNDRAISINGTEAMID = @PARENTGROUPID 

                  /* 
                    if this registrant batch record has not been committed (not committed batch), get role code so we can update the role code to match group type                  
                    reason - update role code and group to maintain data accuracy when the batch do get committed to the system.
                  */
                  select @REGROLECODE=case when @PTYPECODE=2 and REGISTRANTROLECODE=3 then 7 when @PTYPECODE=1 and REGISTRANTROLECODE = 9 then 3 else REGISTRANTROLECODE end
                  from dbo.REGISTRANTBATCH RB (nolock)
                  join dbo.BATCH B (nolock) on B.ID = RB.BATCHID and B.STATUSCODE = 0
                  where RB.TEAMFUNDRAISINGTEAMID = @ID

                  update dbo.REGISTRANTBATCH
                  set TEAMFUNDRAISINGTEAMID = @PARENTGROUPID,
                      REGISTRANTROLECODE = isnull(@REGROLECODE, REGISTRANTROLECODE),
                      CHANGEDBYID=@CHANGEAGENTID,
                      DATECHANGED=@CURRENTDATE
                  where TEAMFUNDRAISINGTEAMID = @ID
              end

              exec dbo.USP_TEAMFUNDRAISINGTEAM_DELETE @ID=@ID, @CHANGEAGENTID=@CHANGEAGENTID

              drop table #tmpTeamMembers

            end
            else
              raiserror('REGISTRATIONOPTIONREACHLIMIT', 13,1);
              return 1;
                end

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

        return 0;