USP_DATALIST_PREVIOUSEVENTGROUPBYCONSTITUENT

CREATE procedure dbo.USP_DATALIST_PREVIOUSEVENTGROUPBYCONSTITUENT
(
  @EVENTID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier,
  @ROLETYPE tinyint
)
as
    set nocount on;

        declare 
            @PREVIOUSEVENTID uniqueidentifier,
            @GROUPNAME nvarchar(200),
            @GROUPCONSTITUENTID uniqueidentifier,
            @GROUPID uniqueidentifier,
            @GROUPTYPECODE tinyint = 2,
            @PARENTGROUPNAME nvarchar(200),
            @PARENTGROUPID uniqueidentifier,
            @PARENTGROUPCODE tinyint = 0,
            @ISLEADER bit = 0,
            @GROUPHASSTARTED bit = 0,
            @PREVIOUSGROUPID uniqueidentifier,
            @PARENTGROUPCONSTITUENTID uniqueidentifier,
            @PARENTGROUPHASSTARTED bit = 0,
      @TOTALMEMBERS int = 0,
      @TOTALCHILDGROUPS int = 0,
      @TEAMMEMBERSLIMIT int, 
      @COMPANYTEAMSLIMIT int, 
      @COMPANYTEAMMEMBERSLIMIT int


select @PREVIOUSEVENTID=PRIORYEAREVENTID FROM dbo.EVENTEXTENSION EX where EX.EVENTID = @EVENTID

if @PREVIOUSEVENTID is not null
begin

    if @ROLETYPE = 1
    begin
          select 
            @GROUPID = T.ID,
            @GROUPNAME = T.NAME,
            @GROUPTYPECODE = TE.TYPECODE,
            @GROUPCONSTITUENTID = TE.TEAMCONSTITUENTID,
            @PARENTGROUPNAME = T2.NAME, 
            @PARENTGROUPCODE = TE2.TYPECODE,
            @PARENTGROUPID = T.PARENTTEAMID ,
            @PARENTGROUPCONSTITUENTID = TE2.TEAMCONSTITUENTID,
            @ISLEADER = (CASE WHEN TC.ID IS NOT NULL THEN 1 ELSE 0 END)
         from  dbo.TEAMEXTENSION TE WITH (nolock)  
         inner join dbo.TEAMFUNDRAISINGTEAM T WITH (nolock) on T.ID = TE.TEAMFUNDRAISINGTEAMID  
         join dbo.TEAMFUNDRAISINGTEAMMEMBER TM WITH (nolock)  ON T.ID = TM.TEAMFUNDRAISINGTEAMID  
                  and TM.TEAMFUNDRAISERID IN (SELECT ID from dbo.TEAMFUNDRAISER (nolock) where CONSTITUENTID = @CONSTITUENTID)  
         left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC WITH (NOLOCK) ON TC.TEAMFUNDRAISINGTEAMID = T.ID AND TC.CONSTITUENTID = @CONSTITUENTID  
         left join dbo.TEAMFUNDRAISINGTEAM T2 (nolock) ON T.PARENTTEAMID = T2.ID
         left join dbo.TEAMEXTENSION TE2 (nolock) ON T2.id = TE2.TEAMFUNDRAISINGTEAMID
         WHERE TE.EVENTID = @PREVIOUSEVENTID
    end
    else if @ROLETYPE in (2,3,4)
    begin
          select @PREVIOUSGROUPID=TX.TEAMFUNDRAISINGTEAMID, @ISLEADER = (CASE WHEN TFTC.ID IS NOT NULL THEN 1 ELSE 0 END) from dbo.TEAMEXTENSION TX (nolock)    
          join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (nolock) on TX.TEAMFUNDRAISINGTEAMID = TFTM.TEAMFUNDRAISINGTEAMID         
          join dbo.TEAMFUNDRAISER TF (nolock) on TF.ID = TFTM.TEAMFUNDRAISERID 
          left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TFTC (nolock) on TFTC.TEAMFUNDRAISINGTEAMID = TX.TEAMFUNDRAISINGTEAMID and TFTC.CONSTITUENTID = @CONSTITUENTID
          where TF.CONSTITUENTID = @CONSTITUENTID and TX.EVENTID = @PREVIOUSEVENTID

          select 
              @GROUPID = case when TFT.ID is not null then TFT.ID when NFGL1.ID is not null then NFGL1.ID else null end,
              @GROUPNAME = case when TFT.ID is not null then TFT.NAME when NFGL1.ID is not null then NFG1.NAME else null end, 
              @GROUPCONSTITUENTID = case when TFT.ID is not null then TX.TEAMCONSTITUENTID when NFGL1.ID is not null then NFG1.GROUPCONSTITUENTID else null end,
              @GROUPTYPECODE=case when TFT.ID is not null then TX.TYPECODE when NFGL1.ID is not null then 5 else null end,  
              @PARENTGROUPNAME = case when pTFT.ID is not null then pTFT.NAME when NFG.NAME is not null then NFG.NAME else null end, 
              @PARENTGROUPCODE = case when pTFT.ID is not null then pTX.TYPECODE when NFGL.ID is not null then 5 else null end, -- use 5 here so it can be consistently an integer 5 = national

              @PARENTGROUPID = case when pTFT.ID is not null then pTFT.PARENTTEAMID when NFG.ID is not null then NFGL.ID else null end,
              @PARENTGROUPCONSTITUENTID = case when pTFT.ID is not null then pTX.TEAMCONSTITUENTID when NFGL.ID is not null then NFG.GROUPCONSTITUENTID else null end
          from dbo.TEAMFUNDRAISINGTEAM cTFT 
          join dbo.TEAMEXTENSION cTX (nolock) on cTFT.ID = cTX.TEAMFUNDRAISINGTEAMID
          left join dbo.TEAMFUNDRAISINGTEAM TFT (nolock) on TFT.ID = cTFT.PARENTTEAMID    -- direct parent (1)

          left join dbo.TEAMEXTENSION TX (nolock) on TX.TEAMFUNDRAISINGTEAMID = TFT.ID        
          left join dbo.FAFNFGCAMPAIGNLEVEL NFGL1 (nolock) on NFGL1.ID = cTX.NFGCAMPAIGNLEVELID -- direct parent (1) in case previous group has national as parent rather than local

          left join dbo.FAFNFGCAMPAIGN NFG1 (nolock) on NFG1.ID = NFGL1.NFGCAMPAIGNID

          left join dbo.TEAMFUNDRAISINGTEAM pTFT (nolock) on pTFT.ID = TFT.PARENTTEAMID    -- next direct parent (2)

          left join dbo.TEAMEXTENSION pTX (nolock) on pTX.TEAMFUNDRAISINGTEAMID = pTFT.ID
          left join dbo.FAFNFGCAMPAIGNLEVEL NFGL (nolock) on NFGL.ID = TX.NFGCAMPAIGNLEVELID -- next direct parent (2) in case (1) has national as parent rather than local

          left join dbo.FAFNFGCAMPAIGN NFG (nolock) on NFG.ID = NFGL.NFGCAMPAIGNID
          where cTFT.ID = @PREVIOUSGROUPID    

    end

    -- get the started group of the current event


    if @GROUPTYPECODE <> 5 
      select @GROUPID = TX.TEAMFUNDRAISINGTEAMID, @GROUPNAME = TFT.NAME, @GROUPTYPECODE=TX.TYPECODE
      from dbo.TEAMEXTENSION TX (nolock)
      join dbo.TEAMFUNDRAISINGTEAM TFT (nolock) on TX.TEAMFUNDRAISINGTEAMID = TFT.ID
      where TEAMCONSTITUENTID = @GROUPCONSTITUENTID and EVENTID = @EVENTID   
    else
      select @GROUPID = NFGL.ID, @GROUPNAME = NFG.NAME
      from dbo.EVENTEXTENSION EX (nolock)
      join dbo.FAFNFGCAMPAIGN NFG (nolock) on EX.FAFPROGRAMID = NFG.CAMPAIGNID
      join dbo.FAFNFGCAMPAIGNLEVEL NFGL (nolock) on NFG.ID = NFGL.NFGCAMPAIGNID and NFGL.HIERARCHYPATH.GetAncestor(1) = '/'
      where NFG.GROUPCONSTITUENTID = @GROUPCONSTITUENTID and EX.EVENTID = @EVENTID   

    if @PARENTGROUPCODE <> 5 
      select @PARENTGROUPID = TX.TEAMFUNDRAISINGTEAMID, @PARENTGROUPNAME = TFT.NAME, @PARENTGROUPCODE = TX.TYPECODE
      from dbo.TEAMEXTENSION TX (nolock)
      join dbo.TEAMFUNDRAISINGTEAM TFT (nolock) on TX.TEAMFUNDRAISINGTEAMID = TFT.ID
      where TEAMCONSTITUENTID = @PARENTGROUPCONSTITUENTID and EVENTID = @EVENTID   
    else
      select @PARENTGROUPID = NFGL.ID, @PARENTGROUPNAME = NFG.NAME
      from dbo.EVENTEXTENSION EX (nolock)
      join dbo.FAFNFGCAMPAIGN NFG (nolock) on EX.FAFPROGRAMID = NFG.CAMPAIGNID
      join dbo.FAFNFGCAMPAIGNLEVEL NFGL (nolock) on NFG.ID = NFGL.NFGCAMPAIGNID and NFGL.HIERARCHYPATH.GetAncestor(1) = '/'
      where NFG.GROUPCONSTITUENTID = @PARENTGROUPCONSTITUENTID and EX.EVENTID = @EVENTID  

    -- end


    select @GROUPHASSTARTED=1 from dbo.TEAMEXTENSION (nolock) where TEAMCONSTITUENTID = @GROUPCONSTITUENTID and EVENTID = @EVENTID
    if @GROUPHASSTARTED = 0
    begin        
        select @GROUPHASSTARTED=1 from dbo.FAFNFGCAMPAIGN (nolock) where GROUPCONSTITUENTID = @GROUPCONSTITUENTID and CAMPAIGNID in (select FAFPROGRAMID from dbo.EVENTEXTENSION where EVENTID = @EVENTID)
        select @PARENTGROUPHASSTARTED=1 from dbo.TEAMEXTENSION (nolock) where TEAMCONSTITUENTID = @PARENTGROUPCONSTITUENTID and EVENTID = @EVENTID

        if @PARENTGROUPHASSTARTED=0      
            select @PARENTGROUPHASSTARTED=1 from dbo.FAFNFGCAMPAIGN (nolock) where GROUPCONSTITUENTID = @PARENTGROUPCONSTITUENTID and CAMPAIGNID in (select FAFPROGRAMID from dbo.EVENTEXTENSION where EVENTID = @EVENTID)
    end 

  select @GROUPID = case when @GROUPHASSTARTED = 1 then @GROUPID when @PARENTGROUPHASSTARTED = 1 then @PARENTGROUPID else null end,
         @GROUPNAME = case when @GROUPHASSTARTED = 1 then @GROUPNAME when @PARENTGROUPHASSTARTED = 1 then @PARENTGROUPNAME else null end,
         @GROUPTYPECODE = case when @GROUPHASSTARTED = 1 then @GROUPTYPECODE when @PARENTGROUPHASSTARTED = 1 then @PARENTGROUPCODE else null end

  select  @TOTALMEMBERS=TOTALPARTICIPANTS, 
          @TOTALCHILDGROUPS=TOTALGROUPS 
  from dbo.FAFGROUPSUMMARYINFORMATION (nolock) where ID = @GROUPID

  select         
      @TEAMMEMBERSLIMIT = TEAMMEMBERSLIMIT, 
      @COMPANYTEAMSLIMIT = COMPANYTEAMSLIMIT, 
      @COMPANYTEAMMEMBERSLIMIT = COMPANYTEAMMEMBERSLIMIT
    from dbo.EVENTTEAMFAFCONFIG (nolock) where EVENTID = @EVENTID

  -- clear out selection if this group has reached the limit set by the event

  if (@ROLETYPE = 1 and @GROUPTYPECODE = 1 and @TOTALMEMBERS >= @TEAMMEMBERSLIMIT) 
     or 
     (@ROLETYPE in (2,3) and @GROUPTYPECODE = 2 and @TOTALCHILDGROUPS >= @COMPANYTEAMSLIMIT)
  begin
    set @GROUPID = null
    set @GROUPNAME = null
    set @GROUPTYPECODE = null
  end    

  -- clear out selection if this group is national group and is not set to allow groups to join

  if (@ROLETYPE IN (2,4) AND @GROUPTYPECODE = 5 and 
        not exists(select NFG.ID from dbo.FAFNFGCAMPAIGN NFG (nolock) 
                    join dbo.FAFNFGCAMPAIGNLEVEL NFGL (nolock) on NFG.ID = NFGL.NFGCAMPAIGNID 
                    where NFGL.ID = @GROUPID and NFG.ALLOWOTHERSJOINFROMGROUPPAGE = 1))
     Or
     (@ROLETYPE = 3 and @GROUPTYPECODE = 5 and 
        not exists(select NFG.ID from dbo.FAFNFGCAMPAIGN NFG (nolock) 
                    join dbo.FAFNFGCAMPAIGNLEVEL NFGL (nolock) on NFG.ID = NFGL.NFGCAMPAIGNID 
                    where NFGL.ID = @GROUPID and NFG.ALLOWCOMPANIESJOINFROMGROUPPAGE = 1))
  begin                
    set @GROUPID = null
    set @GROUPNAME = null
    set @GROUPTYPECODE = null                    
  end

    select   
      E.ID as PRIORYEAREVENTID,  --0

      E.NAME as PRIORYEAREVENTNAME,    --1

      @ISLEADER AS ISLEADER  ,        --2    

      @GROUPID as PARENTGROUPID,            --3

      @GROUPNAME AS PARENTGROUPNAME,  --4 

      @GROUPTYPECODE as PARENTGROUPCODE    --5

     from dbo.EVENT E (NOLOCK)
     where E.ID = @PREVIOUSEVENTID 


end