USP_DATALIST_FAFPREVIOUSGROUPS

CREATE procedure dbo.USP_DATALIST_FAFPREVIOUSGROUPS
(
  @CONSTITUENTID uniqueidentifier,
  @CURRENTEVENTID uniqueidentifier,
  @GROUPTYPE tinyint, --1: team; 2: company; 3: household

  @RESTARTTYPE tinyint,
  @TOPROWS int
)
as
  set nocount on;

  declare @previousEvent as uniqueidentifier;

  declare @previousGroups as table
  (
    ID uniqueidentifier,
    NAME nvarchar(100),
    EVENTNAME nvarchar(100),
    EVENTID uniqueidentifier,
    TYPE nvarchar(9),
    TYPECODE tinyint,
    ISLEADER bit
  )

  select @previousEvent = PRIORYEAREVENTID from dbo.EVENTEXTENSION where EVENTID = @CURRENTEVENTID

  insert into @previousGroups
    select 
      case when tx.TYPECODE = @GROUPTYPE then tft.ID when tx1.TYPECODE=@GROUPTYPE then tft1.ID when tx2.TYPECODE=@GROUPTYPE then tft2.ID end ID,
      case when tx.TYPECODE = @GROUPTYPE then tft.NAME when tx1.TYPECODE=@GROUPTYPE then tft1.NAME when tx2.TYPECODE=@GROUPTYPE then tft2.NAME end Name
      ,e.NAME as EVENTNAME
      ,e.ID as EVENTID
      ,tx.TYPE
      ,tx.TYPECODE
      ,case when tc.ID is null then 0 else 1 end as ISLEADER
    from TEAMFUNDRAISINGTEAM tft (nolock)
      inner join TEAMEXTENSION tx (nolock) on tx.TEAMFUNDRAISINGTEAMID = tft.ID
      left join EVENT e on e.ID = tx.EVENTID
      left join TEAMFUNDRAISINGTEAMMEMBER tm (nolock) on tm.TEAMFUNDRAISINGTEAMID = tft.ID
      inner join TEAMFUNDRAISER tf (nolock) on tf.ID = tm.TEAMFUNDRAISERID
      left join TEAMFUNDRAISINGTEAMCAPTAIN tc on tc.TEAMFUNDRAISINGTEAMID = tft.ID and tc.CONSTITUENTID = tf.CONSTITUENTID
      left join TEAMFUNDRAISINGTEAM tft1 (nolock) on tft1.ID = tft.PARENTTEAMID
      left join TEAMEXTENSION tx1 (nolock) on tx1.TEAMFUNDRAISINGTEAMID = tft1.ID
      left join TEAMFUNDRAISINGTEAM tft2 (nolock) on tft2.ID = tft1.PARENTTEAMID
      left join TEAMEXTENSION tx2 (nolock) on tx2.TEAMFUNDRAISINGTEAMID = tft2.ID
    where 
      tf.CONSTITUENTID=@CONSTITUENTID
      and (tx.TYPECODE = @GROUPTYPE or tx1.TYPECODE = @GROUPTYPE or tx2.TYPECODE = @GROUPTYPE)
      and e.ID <> @CURRENTEVENTID

  if @GROUPTYPE = 1
  begin
    if @RESTARTTYPE = 0                           --team leaders

      delete from @previousGroups where TYPECODE <> 1 or ISLEADER <> 1
    else if @RESTARTTYPE = 1                      --team leaders/members

      delete from @previousGroups where TYPECODE <> 1
    else                                          --none

      delete from @previousGroups
  end
  else if @GROUPTYPE = 2
  begin
    if @RESTARTTYPE = 0                           --company leaders

      delete from @previousGroups where TYPECODE <> 2 or ISLEADER <> 1
    else if @RESTARTTYPE = 1 or @RESTARTTYPE = 2  --company leaders/members

      delete from @previousGroups where TYPECODE <> 2
    else                                          --none

      delete from @previousGroups
  end
  else if @GROUPTYPE = 3
  begin
    if @RESTARTTYPE = 0                           --household leaders

      delete from @previousGroups where TYPECODE <> 3 or ISLEADER <> 1
    else if @RESTARTTYPE = 1                      --household leaders/members

      delete from @previousGroups where TYPECODE <> 3
    else                                          --none

      delete from @previousGroups
  end

  if @previousEvent is not null and exists(select 1 from @previousGroups where EVENTID = @previousEvent)
    delete from @previousGroups where EventID <> @previousEvent   --just return the previous event's group

  else
    delete from @previousGroups where ID in                       --filter the restarted groups

    (
      select tt.ID from @previousGroups tt
      inner join TEAMFUNDRAISINGTEAM tft on tft.ID = tt.ID
      inner join TEAMEXTENSION tx on tx.TEAMFUNDRAISINGTEAMID = tft.ID
      left join TEAMEXTENSION txc on txc.TEAMCONSTITUENTID = tx.TEAMCONSTITUENTID and txc.EVENTID = @CURRENTEVENTID
      where txc.ID is not null
    )

  select top(@TOPROWS)
    tft.ID,
    tft.NAME as GROUPNAME,
    tt.EVENTID,
    tt.EVENTNAME,
    tft1.ID as PARENTGROUPID,
    tft1.NAME as PARENTGROUPNAME,
    tftc.ID as EXISTINGGROUPID,
    tftc.NAME as EXISTINGGROUPNAME,
    nc.ID as NFGID,
    nc.NAME as NFGNAME
  from @previousGroups tt
    inner join TEAMFUNDRAISINGTEAM tft on tft.ID = tt.ID
    inner join TEAMEXTENSION tx on tx.TEAMFUNDRAISINGTEAMID = tt.ID
    left join TEAMFUNDRAISINGTEAM tft1 on tft1.ID = tft.PARENTTEAMID
    left join TEAMEXTENSION txc on txc.TEAMCONSTITUENTID = tx.TEAMCONSTITUENTID and txc.EVENTID = @CURRENTEVENTID
    left join TEAMFUNDRAISINGTEAM tftc on tftc.ID = txc.TEAMFUNDRAISINGTEAMID
    left join dbo.FAFNFGCAMPAIGNLEVEL ncl on ncl.ID = tx.NFGCAMPAIGNLEVELID
    left join dbo.FAFNFGCAMPAIGN nc on nc.ID = ncl.NFGCAMPAIGNID
  order by tft.DATEADDED desc