USP_DATALIST_PREVIOUSPARTICIPANTS

CREATE procedure dbo.USP_DATALIST_PREVIOUSPARTICIPANTS
  (
    @CLIENTUSERSID int,
      @EVENTID uniqueidentifier
  )
as
    set nocount on;

  DECLARE @PREVIOUSEVENTID uniqueidentifier

  DECLARE @CONSTITUENTID uniqueidentifier
    SET @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)

      DECLARE 
        @IsIndependentHouseholdMember bit 
        ,@IsIndependentHouseholdLeader bit  
        ,@IsIndependentTeamMember bit  
        ,@IsIndependentTeamLeader bit   
        ,@IsCompanyHouseholdMember bit   
        ,@IsCompanyHouseholdLeader bit   
        ,@IsCompanyTeamHouseholdLeader bit   
        ,@IsCompanyTeamMember bit   
        ,@IsCompanyTeamLeader bit   
        ,@IsCompanyLeader bit   
    ,@IsCompanyTeamHouseholdMember bit
    ,@IsTeamHouseholdMember bit
    ,@IsTeamHouseholdLeader bit

    EXEC dbo.USP_FAF_REGISTRANT_ROLE
        @CONSTITUENTID = @CONSTITUENTID
        ,@EVENTID =@EVENTID
        ,@IsIndependentHouseholdMember  = @IsIndependentHouseholdMember OUTPUT
        ,@IsIndependentHouseholdLeader  = @IsIndependentHouseholdLeader OUTPUT
        ,@IsIndependentTeamMember  = @IsIndependentTeamMember OUTPUT
        ,@IsIndependentTeamLeader   = @IsIndependentTeamLeader OUTPUT
        ,@IsCompanyHouseholdMember   = @IsCompanyHouseholdMember OUTPUT
        ,@IsCompanyHouseholdLeader   = @IsCompanyHouseholdLeader OUTPUT
        ,@IsCompanyTeamHouseholdLeader   = @IsCompanyTeamHouseholdLeader OUTPUT
        ,@IsCompanyTeamMember   = @IsCompanyTeamMember OUTPUT
        ,@IsCompanyTeamLeader   = @IsCompanyTeamLeader OUTPUT
        ,@IsCompanyLeader   = @IsCompanyLeader OUTPUT
    ,@IsCompanyTeamHouseholdMember = @IsCompanyTeamHouseholdMember OUTPUT
    ,@IsTeamHouseholdMember = @IsTeamHouseholdMember OUTPUT
    ,@IsTeamHouseholdLeader = @IsTeamHouseholdLeader OUTPUT

    DECLARE @MYADDRESSBOOK table (
                      GROUPNAME nvarchar(100),
                      NAME varchar(256) null,
                      CONSTITUENTID uniqueidentifier null
                      )

    select @PREVIOUSEVENTID = prioryeareventid from dbo.EVENTEXTENSION (NOLOCK) WHERE EVENTID = @EVENTID

    IF @PREVIOUSEVENTID IS NOT NULL
    Begin

      IF (@IsIndependentTeamLeader = 1
          OR @IsIndependentTeamMember = 1
          OR @IsCompanyTeamMember = 1
          OR @IsCompanyTeamLeader = 1)  
        BEGIN
          INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)
          SELECT 'Team members - Previous', Name, ID
          FROM dbo.UFN_FAF_TEAMMEMBERLIST(@CONSTITUENTID, @PREVIOUSEVENTID)
        where ID not in (Select constituentid from dbo.REGISTRANT (NOLOCK) where EVENTID = @EVENTID)
        END

      IF (@IsCompanyHouseholdLeader = 1
          OR @IsCompanyHouseholdMember = 1
          OR @IsCompanyTeamHouseholdLeader = 1
        OR @IsCompanyTeamHouseholdMember = 1
          OR @IsTeamHouseholdMember = 1
        OR @IsTeamHouseholdLeader = 1
        OR @IsIndependentHouseholdMember = 1
          OR @IsIndependentHouseholdLeader = 1
        )
        BEGIN
          INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)
          SELECT 'Household members - Previous', Name, ID
          FROM dbo.UFN_FAF_HOUSEHOLDMEMBERLIST(@CONSTITUENTID, @PREVIOUSEVENTID)
        WHERE ID not in (Select constituentid from dbo.REGISTRANT (NOLOCK) where EVENTID = @EVENTID)
        END

      IF (@IsCompanyLeader = 1)
        BEGIN
          INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)

          SELECT 
          'Individuals - Previous', Name, ID
          FROM dbo.UFN_FAF_COMPANYINDIVIDUALLIST(@CONSTITUENTID, @PREVIOUSEVENTID)
        WHERE ID not in (Select constituentid from dbo.REGISTRANT (NOLOCK) where EVENTID = @EVENTID)

          UNION 

          SELECT -- team leaders, household leaders under this company

          groupname+' - Previous', Name, ID
          FROM dbo.UFN_FAF_COMPANYTEAMLEADERLIST(@CONSTITUENTID, @PREVIOUSEVENTID)
        WHERE ID not in (Select constituentid from dbo.REGISTRANT (NOLOCK) where EVENTID = @EVENTID)

        END

   End

        select A.CONSTITUENTID,
               A.NAME,
               GROUPNAME,
           E.EMAILADDRESS
        from @MYADDRESSBOOK A
      LEFT OUTER JOIN dbo.EMAILADDRESS E
      ON A.CONSTITUENTID = E.CONSTITUENTID