UFN_ADDRESSBOOKFAF_GETEXPECTEDGROUPMEMBER

CREATE function dbo.UFN_ADDRESSBOOKFAF_GETEXPECTEDGROUPMEMBER
(
  @GROUPID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier,
  @CLIENTUSERSID int,
  @ROLECODE tinyint -- 0: company leader, 1: team leader, 2: team member, 3: head of household, 4: household member

)
returns table
as return 

With CurrentGroupMembers
as 
(
    select 
        dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0) as USERID,
        C.ID
    from dbo.TEAMEXTENSION TX 
    join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM on TFTM.TEAMFUNDRAISINGTEAMID = TX.TEAMFUNDRAISINGTEAMID
    join dbo.TEAMFUNDRAISER TF on TF.ID = TFTM.TEAMFUNDRAISERID
    join dbo.CONSTITUENT C on TF.CONSTITUENTID = C.ID 
    where (TX.TEAMFUNDRAISINGTEAMID = @GROUPID
    --and C.ID not in (select CONSTITUENTID from dbo.ADDRESSBOOKFAF where CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCURRENTCLIENTUSERID(@CLIENTUSERSID)) and CONSTITUENTID is not null)

    and C.ID <> @CONSTITUENTID) 
    and @ROLECODE between 0 and 4    
)
, CurrentGroupChildGroupLeaders
as 
(        
    select USERID ,ID from CurrentGroupMembers

    union

    /* child group leaders */
    select 
        dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0) as USERID,
        C.ID
    from dbo.TEAMFUNDRAISINGTEAM TFT
    join dbo.TEAMEXTENSION TX on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
    join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TFTC on TFTC.TEAMFUNDRAISINGTEAMID = TX.TEAMFUNDRAISINGTEAMID
    join dbo.CONSTITUENT C on TFTC.CONSTITUENTID = C.ID 
    where (TFT.PARENTTEAMID = @GROUPID
    --and C.ID not in (select CONSTITUENTID from dbo.ADDRESSBOOKFAF where CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCURRENTCLIENTUSERID(@CLIENTUSERSID)) and CONSTITUENTID is not null)

    and C.ID <> @CONSTITUENTID ) 
    and (@ROLECODE = 0 or (@ROLECODE in(1,2) and TX.TYPECODE = 3) ) 
)
, HouseholdParentGroupMembers
as 
(            

    select USERID ,ID from [CurrentGroupMembers]

    union
    /* household's parent group members */
    select 
        dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0) as USERID,
        C.ID
    from dbo.TEAMEXTENSION TX 
    join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM on TFTM.TEAMFUNDRAISINGTEAMID = TX.TEAMFUNDRAISINGTEAMID
    join dbo.TEAMFUNDRAISER TF on TF.ID = TFTM.TEAMFUNDRAISERID
    join dbo.CONSTITUENT C on TF.CONSTITUENTID = C.ID 
    where (TX.TEAMFUNDRAISINGTEAMID in (select PARENTTEAMID from dbo.TEAMFUNDRAISINGTEAM where ID = @GROUPID) and TX.TYPECODE <> 2
    --and C.ID not in (select CONSTITUENTID from dbo.ADDRESSBOOKFAF where CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCURRENTCLIENTUSERID(@CLIENTUSERSID)) and CONSTITUENTID is not null)

    and C.ID <> @CONSTITUENTID) 
    and @ROLECODE = 3 
)
select USERID, ID  from CurrentGroupChildGroupLeaders CGCGL where @rolecode in (0,1,2)
union all
select USERID, ID from HouseholdParentGroupMembers HPGM where @rolecode in (3,4)