UFN_FAFGROUP_HASREACHEDLIMIT

CREATE function dbo.UFN_FAFGROUP_HASREACHEDLIMIT(@EVENTID uniqueidentifier, @TEAMID uniqueidentifier, @REGISTRANTROLE tinyint)
returns bit with execute as caller
as begin
  DECLARE @TEAMTYPE tinyint
  DECLARE @FORCOMPANYMEMBER bit
  DECLARE @TEAMMEMBERSLIMIT int  
    DECLARE @HOUSEHOLDMEMBERSLIMIT int  
    DECLARE @COMPANYTEAMMEMBERSLIMIT int  
    DECLARE @COMPANYTEAMSLIMIT int

    DECLARE @MEMBERS int
    DECLARE @TEAMS int

    DECLARE @HASREACHEDLIMIT bit  
    SET @HASREACHEDLIMIT = 1

    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

  IF @TEAMTYPE = 0 OR @TEAMID IS NULL
    return 0

  IF @REGISTRANTROLE = 7
    SET @FORCOMPANYMEMBER = 1
  ELSE
    SET @FORCOMPANYMEMBER = 0

  SELECT @TEAMMEMBERSLIMIT = ISNULL(TEAMMEMBERSLIMIT,0), @HOUSEHOLDMEMBERSLIMIT = ISNULL(HOUSEHOLDMEMBERSLIMIT, 0), 
        @COMPANYTEAMSLIMIT = ISNULL(COMPANYTEAMSLIMIT, 0), @COMPANYTEAMMEMBERSLIMIT = ISNULL(COMPANYTEAMMEMBERSLIMIT, 0)
    from dbo.EVENTTEAMFAFCONFIG EC
    where EVENTID = @EVENTID

    IF @TEAMTYPE = 1
    BEGIN
        SET @MEMBERS = (SELECT COUNT(*) FROM dbo.UFN_FAF_TEAMMEMBERLIST_BY_TEAMID(@TEAMID))
        IF @TEAMMEMBERSLIMIT = 0 OR @MEMBERS < @TEAMMEMBERSLIMIT
            SET @HASREACHEDLIMIT = 0
    END
    ELSE IF @TEAMTYPE = 3
    BEGIN
        SET @MEMBERS = (SELECT COUNT(*) FROM dbo.UFN_FAF_HOUSEHOLDMEMBERS_BY_HHTEAMID(@TEAMID))
        IF @HOUSEHOLDMEMBERSLIMIT = 0 OR @MEMBERS < @HOUSEHOLDMEMBERSLIMIT
            SET @HASREACHEDLIMIT = 0
    END
    ELSE IF @TEAMTYPE = 2
    BEGIN
        SET @MEMBERS = (SELECT COUNT(*) FROM dbo.UFN_FAF_COMPANYINDIVIDUALLIST_BY_COMPANYID(@TEAMID))

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

        IF @FORCOMPANYMEMBER = 1
      BEGIN
              IF @COMPANYTEAMMEMBERSLIMIT = 0 OR @MEMBERS < @COMPANYTEAMMEMBERSLIMIT
                  SET @HASREACHEDLIMIT = 0
      END
        ELSE
      BEGIN
              IF @COMPANYTEAMSLIMIT = 0 OR @TEAMS < @COMPANYTEAMSLIMIT
                  SET @HASREACHEDLIMIT = 0
      END
    END

    return @HASREACHEDLIMIT     
end