USP_DATALIST_GETSYSTEMROLES

CREATE procedure dbo.USP_DATALIST_GETSYSTEMROLES
    ( @EVENTID uniqueidentifier,
      @SEQUENCEID integer,
    @CLIENTUSERSID int = null
    )WITH EXECUTE AS CALLER
    as

    BEGIN
    SET NOCOUNT ON;

  DECLARE @CONSTITUENTID uniqueidentifier, @IsSponsor bit  
  SELECT @CONSTITUENTID= ID FROM [CONSTITUENT] WHERE SEQUENCEID= @SEQUENCEID

  IF ISNULL(@CLIENTUSERSID,0) > 0
  SET @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)

  IF EXISTS (SELECT * FROM EVENTSPONSOR    WHERE    CONSTITUENTID= @CONSTITUENTID AND EVENTID= @EVENTID AND ISCANCELLED = 0)
      SET @IsSponsor= 1 
  ELSE
      SET @IsSponsor= 0
  ---------------------------


  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, @IsCompanyIndividual bit, @IsIndependentIndividual 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
                  ,@IsCompanyIndividual = @IsCompanyIndividual OUTPUT
                  ,@IsIndependentIndividual = @IsIndependentIndividual OUTPUT
  ---------------------------              


  DECLARE @Group_Type TABLE  ( PRIM_ID int IDENTITY(1,1) primary key, CONSTITUENTID uniqueidentifier, SEQUENCEID int, SPONSOR bit, PARTICIPANT bit, INDIVIDUAL bit, 
                                                       HHMEMBER bit, HHLEADER bit, TEAMMEMBER bit, TEAMLEADER bit, COMPLEADER bit )
  DECLARE @Is_PARTICIPANT bit, @Is_INDIVIDUAL bit, @Is_HHMEMBER bit, @Is_HHLEADER bit, @Is_TEAMMEMBER bit, @Is_TEAMLEADER bit,@Is_COMPLEADER bit
  ----------    

  SELECT  @Is_PARTICIPANT= 1, @Is_INDIVIDUAL= 1, @Is_HHMEMBER= 1, @Is_HHLEADER= 1, @Is_TEAMMEMBER=1, @Is_TEAMLEADER= 1, @Is_COMPLEADER= 1
  ---------------------------


  INSERT INTO        @Group_Type    (CONSTITUENTID, SEQUENCEID, SPONSOR, PARTICIPANT, INDIVIDUAL, HHMEMBER, HHLEADER, TEAMMEMBER, TEAMLEADER, COMPLEADER)
  ----------

  SELECT      @CONSTITUENTID AS CONSTITUENTID, @SEQUENCEID AS SEQUENCEID 
              , @IsSponsor AS SPONSOR 
              , CASE WHEN @Is_PARTICIPANT    IN (@IsIndependentHouseholdMember, @IsIndependentHouseholdLeader, @IsIndependentTeamMember, @IsIndependentTeamLeader, 
                                        @IsCompanyHouseholdMember, @IsCompanyHouseholdLeader, @IsCompanyTeamHouseholdLeader, @IsCompanyTeamMember, @IsCompanyTeamLeader, 
                                        @IsCompanyLeader, @IsCompanyTeamHouseholdMember,@IsTeamHouseholdMember, @IsTeamHouseholdLeader, @IsCompanyIndividual, 
                                        @IsIndependentIndividual)                                      THEN 1 ELSE 0 END AS  PARTICIPANT 

              , CASE WHEN @Is_INDIVIDUAL    IN (@IsIndependentTeamMember, @IsIndependentTeamLeader,  @IsCompanyTeamMember, @IsCompanyTeamLeader, @IsCompanyLeader, 
  @IsCompanyIndividual, @IsIndependentIndividual) THEN 1 ELSE 0 END AS  INDIVIDUAL             

              , CASE WHEN @Is_HHMEMBER        IN (@IsIndependentHouseholdMember, @IsCompanyHouseholdMember, @IsCompanyTeamHouseholdMember, @IsTeamHouseholdMember)
                                      --,@IsTeamHouseholdLeader, @IsIndependentHouseholdLeader, @IsCompanyHouseholdLeader, @IsCompanyTeamHouseholdLeader)                

                                                                                        THEN 1 ELSE 0 END AS  HHMEMBER 

              , CASE WHEN @Is_HHLEADER        IN (@IsIndependentHouseholdLeader, @IsCompanyHouseholdLeader, @IsCompanyTeamHouseholdLeader,  @IsTeamHouseholdLeader)    
                                                                                                                                                                                THEN 1 ELSE 0 END AS  HHLEADER     

              , CASE WHEN @Is_TEAMMEMBER    IN (@IsIndependentTeamMember, @IsCompanyTeamMember, @IsCompanyTeamHouseholdLeader, @IsTeamHouseholdLeader)    THEN 1 ELSE 0 END AS  TEAMMEMBER     

              , CASE WHEN @Is_TEAMLEADER    IN (@IsIndependentTeamLeader, @IsCompanyTeamLeader)    THEN 1 ELSE 0 END AS  TEAMLEADER     

              , CASE WHEN @Is_COMPLEADER    IN (@IsCompanyLeader)                                                                THEN 1 ELSE 0 END AS    COMPLEADER                            
  -----------------------------------------------------------------------


  DECLARE @FAF_ClientRoles TABLE  ( PRIM_ID int IDENTITY(1,1) primary key, ROLEID int, ROLEGUID uniqueidentifier, ROLENAME nvarchar(100) )
  -----    

  INSERT INTO        @FAF_ClientRoles  ( ROLEID, ROLEGUID, ROLENAME ) 
  ----------

  SELECT    ID, Guid, Name                
  FROM    ClientRoles 
  WHERE    Guid IN ('61CBFC50-B232-4267-A20F-1F2E88879293', 'FD5734D1-9E84-4B82-8B4A-C6322EB0F9BF', 'BC6B6D42-C7E6-4983-9A99-09ABAE79F452', '3182787B-18A8-4B5E-BCBE-B56D97A6F88B', 
                             'BDA181EC-4575-4BBD-9C6F-57CBFF264B7D', 'B482AB69-4E63-4F76-B872-C6A50C94619C', 'A4EF01E5-057B-44CE-AB02-47B1993D44AA', 'FE9F294E-324D-4237-8761-F6FA0D509F77')
  --------------------


  SELECT    ROLEID, ROLEGUID
                , CASE  WHEN ROLEGUID= 'B482AB69-4E63-4F76-B872-C6A50C94619C'          THEN 'FAF Sponsor' 
                              WHEN ROLEGUID= 'A4EF01E5-057B-44CE-AB02-47B1993D44AA'       THEN 'FAF Participant'
                              WHEN ROLEGUID= 'BDA181EC-4575-4BBD-9C6F-57CBFF264B7D'         THEN 'FAF Individual'
                              WHEN ROLEGUID= '3182787B-18A8-4B5E-BCBE-B56D97A6F88B'          THEN 'FAF Household Member'
                              WHEN ROLEGUID= 'BC6B6D42-C7E6-4983-9A99-09ABAE79F452'          THEN 'FAF Household Leader'
                  WHEN ROLEGUID= 'FE9F294E-324D-4237-8761-F6FA0D509F77'       THEN 'FAF Team Member'
                              WHEN ROLEGUID= 'FD5734D1-9E84-4B82-8B4A-C6322EB0F9BF'        THEN 'FAF Team Leader'
                              WHEN ROLEGUID= '61CBFC50-B232-4267-A20F-1F2E88879293'       THEN 'FAF Company Leader' END AS ROLENAME
                , CASE  WHEN ROLEGUID= 'B482AB69-4E63-4F76-B872-C6A50C94619C'    AND (SELECT SPONSOR FROM @Group_Type)= 1          THEN 1 
                              WHEN ROLEGUID= 'B482AB69-4E63-4F76-B872-C6A50C94619C'    AND (SELECT SPONSOR FROM @Group_Type)= 0            THEN 0 
                              WHEN ROLEGUID= 'A4EF01E5-057B-44CE-AB02-47B1993D44AA'    AND (SELECT PARTICIPANT FROM @Group_Type)= 1    THEN 1
                              WHEN ROLEGUID= 'A4EF01E5-057B-44CE-AB02-47B1993D44AA'    AND (SELECT PARTICIPANT FROM @Group_Type)= 0    THEN 0
                              WHEN ROLEGUID= 'BDA181EC-4575-4BBD-9C6F-57CBFF264B7D'    AND (SELECT INDIVIDUAL FROM @Group_Type)= 1        THEN 1
                              WHEN ROLEGUID= 'BDA181EC-4575-4BBD-9C6F-57CBFF264B7D'    AND (SELECT INDIVIDUAL FROM @Group_Type)= 0        THEN 0
                              WHEN ROLEGUID= '3182787B-18A8-4B5E-BCBE-B56D97A6F88B'    AND (SELECT HHMEMBER FROM @Group_Type)= 1          THEN 1
                              WHEN ROLEGUID= '3182787B-18A8-4B5E-BCBE-B56D97A6F88B'    AND (SELECT HHMEMBER FROM @Group_Type)= 0          THEN 0
                              WHEN ROLEGUID= 'BC6B6D42-C7E6-4983-9A99-09ABAE79F452'    AND (SELECT HHLEADER FROM @Group_Type)= 1          THEN 1
                              WHEN ROLEGUID= 'BC6B6D42-C7E6-4983-9A99-09ABAE79F452'    AND (SELECT HHLEADER FROM @Group_Type)= 0          THEN 0
                  WHEN ROLEGUID= 'FE9F294E-324D-4237-8761-F6FA0D509F77'    AND (SELECT TEAMMEMBER FROM @Group_Type)= 1      THEN 1
                              WHEN ROLEGUID= 'FE9F294E-324D-4237-8761-F6FA0D509F77'    AND (SELECT TEAMMEMBER FROM @Group_Type)= 0      THEN 0
                              WHEN ROLEGUID= 'FD5734D1-9E84-4B82-8B4A-C6322EB0F9BF'    AND (SELECT TEAMLEADER FROM @Group_Type)= 1      THEN 1
                              WHEN ROLEGUID= 'FD5734D1-9E84-4B82-8B4A-C6322EB0F9BF'    AND (SELECT TEAMLEADER FROM @Group_Type)= 0      THEN 0
                              WHEN ROLEGUID= '61CBFC50-B232-4267-A20F-1F2E88879293'    AND (SELECT COMPLEADER FROM @Group_Type)= 1      THEN 1
                              WHEN ROLEGUID= '61CBFC50-B232-4267-A20F-1F2E88879293'    AND (SELECT COMPLEADER FROM @Group_Type)= 0      THEN 0 ELSE 0  END AS HASROLE
   FROM      @FAF_ClientRoles            
   ORDER BY  ROLEID
    END