USP_DATALIST_RECIPIENTROLE

CREATE procedure dbo.USP_DATALIST_RECIPIENTROLE
(
  @EVENTID uniqueidentifier,
  @GROUPID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier = null,
  @REGISTRANTID uniqueidentifier = null
)
  as
      set nocount on;
      declare @IsTeamLeader int,
              @IsCompanyLeader int,
              @IsHouseHoldHead int,
              @IsFundraisingCoach int

if @CONSTITUENTID is null
  select @CONSTITUENTID = CONSTITUENTID FROM dbo.REGISTRANT WHERE ID = @REGISTRANTID

select @IsTeamLeader = 1
  from TEAMFUNDRAISINGTEAMCAPTAIN TC
  inner join TEAMFUNDRAISINGTEAM T on T.ID = TC.TEAMFUNDRAISINGTEAMID 
  inner join TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = T.ID 
  inner join CONSTITUENT C on C.ID = TC.CONSTITUENTID 
  inner join EVENT E on E.ID = TX.EVENTID 
 where E.ID = @EVENTID
   and C.ID = @CONSTITUENTID
   and T.ID = @GROUPID
   and TX.TYPECODE = 1

select @IsCompanyLeader = 1
  from TEAMFUNDRAISINGTEAMCAPTAIN TC
  inner join TEAMFUNDRAISINGTEAM T on T.ID = TC.TEAMFUNDRAISINGTEAMID 
  inner join TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = T.ID 
  inner join CONSTITUENT C on C.ID = TC.CONSTITUENTID 
  inner join EVENT E on E.ID = TX.EVENTID 
 where E.ID = @EVENTID
   and C.ID = @CONSTITUENTID
   and T.ID = @GROUPID
   and TX.TYPECODE = 2

select @IsHouseHoldHead = 1
  from TEAMFUNDRAISINGTEAMCAPTAIN TC
  inner join TEAMFUNDRAISINGTEAM T on T.ID = TC.TEAMFUNDRAISINGTEAMID 
  inner join TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = T.ID 
  inner join CONSTITUENT C on C.ID = TC.CONSTITUENTID 
  inner join EVENT E on E.ID = TX.EVENTID 
 where E.ID = @EVENTID
   and C.ID = @CONSTITUENTID
   and T.ID = @GROUPID
   and TX.TYPECODE = 3

set @IsFundraisingCoach = 0

select isnull(@IsTeamLeader, 0) as ISTEAMLEADER
     , isnull(@IsCompanyLeader, 0) as ISCOMPANYLEADER
     , isnull(@IsHouseHoldHead, 0) as ISHOUSEHOLDHEAD
     , isnull(@IsFundraisingCoach, 0) as ISFUNDRAISINGCOACH