USP_DATALIST_CheckHouseholdPrivacy

CREATE procedure dbo.USP_DATALIST_CheckHouseholdPrivacy(
    @EVENTID       uniqueidentifier,
    @TEAMID        uniqueidentifier,
    @REGISTRANTID  uniqueidentifier)
as
    set nocount on;

  Declare @LOGINUSERPARENTTEAMID uniqueidentifier
  Declare @LOGINUSERTEAMID uniqueidentifier
  Declare @HouseholdPARENTTEAMID uniqueidentifier
  Declare @IsHouseholdPrivate bit

    --check if team is household team

If Not Exists(Select TFT.ID from TEAMFUNDRAISINGTEAM TFT
            Inner Join TEAMEXTENSION TT on TFT.ID = TT.TEAMFUNDRAISINGTEAMID
            where TFT.ID = @TeamID and TT.TYPECODE = 3 and TT.EventID = @EVENTID)
   Begin
      Select @IsHouseholdPrivate = 0
   End
Else

   Begin
       --check household leader's privacy

       if exists (Select trt.ID
                  From REGISTRANT rg
                        inner join CONSTITUENT c on c.ID = rg.CONSTITUENTID 
                        inner join TEAMFUNDRAISER tr on c.ID = tr.CONSTITUENTID
                        inner join TEAMFUNDRAISINGTEAMMEMBER tra on tra.TEAMFUNDRAISERID = tr.ID
                        inner join TEAMFUNDRAISINGTEAM trt on trt.ID = tra.TEAMFUNDRAISINGTEAMID
                        inner join TEAMFUNDRAISINGTEAMCAPTAIN tftc on tftc.TEAMFUNDRAISINGTEAMID = trt.ID
                        inner join REGISTRANTEXTENSION rt on rt.REGISTRANTID = rg.ID
                      Where trt.ID = @TeamID and rt.ISPRIVATE = 1 and rg.EVENTID = @EVENTID)
          Begin
               Select @LOGINUSERPARENTTEAMID = trt.PARENTTEAMID, @LOGINUSERTEAMID = trt.ID from REGISTRANT rg
                          inner join CONSTITUENT c on c.ID = rg.CONSTITUENTID 
                          inner join TEAMFUNDRAISER tr on c.ID = tr.CONSTITUENTID
                          inner join TEAMFUNDRAISINGTEAMMEMBER tra on tra.TEAMFUNDRAISERID = tr.ID
                          inner join TEAMFUNDRAISINGTEAM trt on trt.ID = tra.TEAMFUNDRAISINGTEAMID
                     Where rg.ID = @RegistrantID 

                  Select @HouseholdPARENTTEAMID = PARENTTEAMID from TEAMFUNDRAISINGTEAM where ID = @TeamID

                  if @LOGINUSERPARENTTEAMID is null  --company leader or single user

                   Select @IsHouseholdPrivate = 1
            else if @LOGINUSERTEAMID = @HouseholdPARENTTEAMID or @LOGINUSERTEAMID = @TeamID
                 Select @IsHouseholdPrivate = 0
              else
                   Select @IsHouseholdPrivate = 1

          End
       else
          Select @IsHouseholdPrivate = 0

   End

   Select @IsHouseholdPrivate As IsHouseholdPrivate, @LOGINUSERPARENTTEAMID As LOGINUSERPARENTTEAMID,
           @HouseholdPARENTTEAMID As HouseholdPARENTTEAMID