USP_SIMPLEDATALIST_FAFFUNDRAISINGGROUP

CREATE procedure dbo.USP_SIMPLEDATALIST_FAFFUNDRAISINGGROUP(
    @EVENTID uniqueidentifier, 
    @NAME varchar(200) = null, 
    @CEVENTID uniqueidentifier = NULL,
    /*
      0: Company Leader
      1: Team Leader Under Company
      2: Team Leader Independent
      3: Team Member
      4: Household Header Under Company
      5: Household Header Under Team
      6: Household Header Independent
      7: Individual Under Company
      8: Individual Independent
      9: Household Member

      TeamExtension.TypeCode 1 team 2 company 3 household
    */
    @REGISTRANTROLE smallint,
    @DISPLAYONLYACTIVE bit = 1, -- 1: YES, 0: NO,

    @ID uniqueidentifier = NULL

) with execute as owner
as
    set nocount on;

    DECLARE @STATUSCODE tinyint
    DECLARE @TEAMTYPE tinyint
    SELECT @TEAMTYPE = CASE @REGISTRANTROLE
      when 0 then 0
      when 1 then 2 
      when 2 then 0 
      when 3 then 1 
      when 4 then 2 
        when 5 then 1 
      when 6 then 0 
      when 7 then 2 
      when 8 then 0 
      when 9 then 3
  END

  DECLARE @TEAMS table
    (
      ID uniqueidentifier null,
      NAME nvarchar(200) null,
      TEAMCAPTAINLIST varchar(800) null,
    ISEXISTS bit null,
      STATUS nvarchar(16) null,
    MEMBERS integer null,
    TEAMS integer null,
      HASPARENT bit null
      )

    IF @DISPLAYONLYACTIVE = 1
    SET @STATUSCODE = 0

      INSERT INTO @TEAMS(ID, NAME, TEAMCAPTAINLIST, ISEXISTS,STATUS,HASPARENT)
                SELECT 
                T.ID,
                T.NAME,
                dbo.UDA_BUILDLIST(C.NAME) TEAMCAPTAINLIST,
                dbo.UFN_ISTEAMNAMEEXISTS(@CEVENTID, T.NAME) as ISEXISTS,
                TE.STATUS,
                CASE
                  WHEN T.PARENTTEAMID IS NOT NULL THEN 'true'
                  ELSE 'false'
                END
                FROM dbo.TEAMFUNDRAISINGTEAM T WITH (NOLOCK)
                INNER JOIN dbo.EVENT E WITH (NOLOCK)
                ON T.APPEALID = E.APPEALID
                INNER JOIN dbo.TEAMEXTENSION TE (NOLOCK)
                ON TE.TEAMFUNDRAISINGTEAMID = T.ID
                AND TE.TYPECODE = @TEAMTYPE
                LEFT OUTER JOIN dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC WITH (NOLOCK)
                ON TC.TEAMFUNDRAISINGTEAMID = T.ID
                LEFT OUTER JOIN dbo.CONSTITUENT C WITH (NOLOCK)
                ON C.ID = TC.CONSTITUENTID
                WHERE E.ID = @EVENTID and T.NAME = ISNULL(@NAME,T.NAME)
                AND TE.STATUSCODE = ISNULL(@STATUSCODE,TE.STATUSCODE)
                AND T.ID = ISNULL(@ID,T.ID)
                AND dbo.UFN_FAFGROUP_HASREACHEDLIMIT(@EVENTID, T.ID, @REGISTRANTROLE) = 0
                GROUP BY T.ID, T.NAME, TE.STATUS, T.PARENTTEAMID
                ORDER BY T.NAME

        IF @TEAMTYPE = 1
            UPDATE @TEAMS
            SET MEMBERS = (SELECT COUNT(*) FROM dbo.UFN_FAF_TEAMMEMBERLIST_BY_TEAMID(ID))

          IF @TEAMTYPE = 3
              UPDATE @TEAMS
              SET MEMBERS = (SELECT COUNT(*) FROM dbo.UFN_FAF_HOUSEHOLDMEMBERS_BY_HHTEAMID(ID))

          IF @TEAMTYPE = 2
          BEGIN
               UPDATE @TEAMS
              SET MEMBERS = (SELECT COUNT(*) FROM dbo.UFN_FAF_COMPANYINDIVIDUALLIST_BY_COMPANYID(ID))

              UPDATE @TEAMS
              SET TEAMS = (SELECT COUNT(*) FROM dbo.UFN_FAF_GETTEAMHIEARACHY(ID) WHERE [level] > 0 and TEAMID IN 
                        (SELECT TEAMFUNDRAISINGTEAMID FROM dbo.TEAMEXTENSION where EVENTID=@EVENTID and TYPECODE = 1)
                      )
          END

        SELECT 
          ID AS VALUE,
          NAME AS LABEL
        FROM @TEAMS
        ORDER BY NAME