USP_FAF_GROUP_PROMOTION_DEMOTION

CREATE procedure dbo.USP_FAF_GROUP_PROMOTION_DEMOTION
(
      @GROUPID        uniqueidentifier,
      @OLDTYPECODE    tinyint,
      @NEWTYPECODE    tinyint,
      @EVENTID        uniqueidentifier
)
as
begin
    -- do work

  Declare @PARENTGROUPID        uniqueidentifier,
          @GRANDPARENTGROUPID   uniqueidentifier,
          @ISPROMOTION          bit,
          @APPEALID             uniqueidentifier,
          @PARENTGROUPTYPECODE  tinyint

  Set @ISPROMOTION = 1
  Set @PARENTGROUPID = null
  Set @GRANDPARENTGROUPID = null
  Set @PARENTGROUPTYPECODE = 0

  Select @PARENTGROUPID = PARENTTEAMID from dbo.TEAMFUNDRAISINGTEAM where ID = @GROUPID

  select @APPEALID = APPEALID from dbo.Event where ID = @EVENTID

  if @PARENTGROUPID is not null
    begin
       Select @GRANDPARENTGROUPID = PARENTTEAMID from dbo.TEAMFUNDRAISINGTEAM where ID = @PARENTGROUPID
       Select @PARENTGROUPTYPECODE = TypeCode from dbo.TeamExtension where TeamFundraisingTeamID = @PARENTGROUPID
    end

  -- check promotion or demotion

  if @OLDTYPECODE = 2  -- company

     Set @ISPROMOTION = 0
  else if @OLDTYPECODE = 3  -- household

     Set @ISPROMOTION = 1
  else if @OLDTYPECODE = 1 and @NEWTYPECODE =2  -- team

     Set @ISPROMOTION = 1
  else
     Set @ISPROMOTION = 0

   Declare @REGISTRANTCONSTITUENTID  uniqueidentifier
   Select @REGISTRANTCONSTITUENTID = CONSTITUENTID from dbo.TEAMFUNDRAISINGTEAMCAPTAIN (nolock)
                   where TEAMFUNDRAISINGTEAMID = @GROUPID

  -- execute the logic

  if @ISPROMOTION = 0    -- demotion

    begin
        -- child group becomes independent

        if @OLDTYPECODE = 1  -- team to household

          begin
             Update dbo.TEAMFUNDRAISINGTEAM set PARENTTEAMID = null where PARENTTEAMID = @GROUPID and APPEALID = @APPEALID
          end
        else if @NEWTYPECODE = 1 -- company to team

          begin

             --addressbook

             Exec dbo.USP_FAF_ADDRESSBOOK_DEMOTION_INSERT @GROUPID, @EVENTID, @REGISTRANTCONSTITUENTID, 0

             Update tft set tft.PARENTTEAMID = null
                from dbo.TEAMFUNDRAISINGTEAM tft
                    inner join dbo.TeamExtension te on tft.ID = te.TEAMFUNDRAISINGTEAMID
                where PARENTTEAMID = @GROUPID and APPEALID = @APPEALID and te.TYPECODE != 3
          end
        else  -- company to household

          begin
             --addressbook

             Exec dbo.USP_FAF_ADDRESSBOOK_DEMOTION_INSERT @GROUPID, @EVENTID, @REGISTRANTCONSTITUENTID, 1

             Update dbo.TEAMFUNDRAISINGTEAM set PARENTTEAMID = null where PARENTTEAMID = @GROUPID and APPEALID = @APPEALID
          end

    end
  else   -- promotion

    Begin
       if @NEWTYPECODE = 2  -- team or household promoted to company

         begin
           Update dbo.TEAMFUNDRAISINGTEAM set PARENTTEAMID = null where ID = @GROUPID and APPEALID = @APPEALID
         end

      if @NEWTYPECODE = 1 and @PARENTGROUPTYPECODE =1 -- household promoted to team

         begin
            Update dbo.TEAMFUNDRAISINGTEAM set PARENTTEAMID = null where ID = @GROUPID and APPEALID = @APPEALID
         end

       if @GRANDPARENTGROUPID is not null and @NEWTYPECODE =1  -- household to team that has parent

         begin
            Update dbo.TEAMFUNDRAISINGTEAM set PARENTTEAMID = @GRANDPARENTGROUPID where ID = @GROUPID and APPEALID = @APPEALID

            -- add to company leader's addressbook


            Exec dbo.USP_FAFADDRESSBOOK_REGISTRANT_ADD @GRANDPARENTGROUPID, @REGISTRANTCONSTITUENTID
         end
    End
end