CAllGroup::GetList

static function GetList($by = 'c_sort', $order = 'asc', $arFilter = [], $SHOW_USERS_AMOUNT = "N")
{
	global $DB;
	$connection = BitrixMainApplication::getConnection();
	$helper = $connection->getSqlHelper();

	$err_mess = (static::err_mess())."
Function: GetList
Line: ";
	$arSqlSearch = $arSqlSearch_h = array();
	$strSqlSearch_h = "";
	if(is_array($arFilter))
	{
		foreach($arFilter as $key => $val)
		{
			if(is_array($val))
			{
				if(empty($val))
					continue;
			}
			else
			{
				if ((string)$val == '' || $val == "NOT_REF")
					continue;
			}
			$key = strtoupper($key);
			$match_value_set = array_key_exists($key."_EXACT_MATCH", $arFilter);
			switch($key)
			{
				case "ID":
					$match = ($match_value_set && $arFilter[$key."_EXACT_MATCH"]=="N") ? "Y" : "N";
					$arSqlSearch[] = GetFilterQuery("G.ID", $val, $match);
					break;
				case "TIMESTAMP_1":
					$arSqlSearch[] = "G.TIMESTAMP_X >= FROM_UNIXTIME('".MkDateTime(FmtDate($val,"D.M.Y"),"d.m.Y")."')";
					break;
				case "TIMESTAMP_2":
					$arSqlSearch[] = "G.TIMESTAMP_X <= FROM_UNIXTIME('".MkDateTime(FmtDate($val,"D.M.Y")." 23:59:59","d.m.Y")."')";
					break;
				case "ACTIVE":
					$arSqlSearch[] = ($val=="Y") ? "G.ACTIVE='Y'" : "G.ACTIVE='N'";
					break;
				case "ADMIN":
					if(COption::GetOptionString("main", "controller_member", "N") == "Y" && COption::GetOptionString("main", "~controller_limited_admin", "N") == "Y")
					{
						if($val=="Y")
							$arSqlSearch[] =  "G.ID=0";
						break;
					}
					else
						$arSqlSearch[] = ($val=="Y") ? "G.ID=1" : "G.ID>1";
					break;
				case "NAME":
					$match = ($match_value_set && $arFilter[$key."_EXACT_MATCH"]=="Y") ? "N" : "Y";
					$arSqlSearch[] = GetFilterQuery("G.NAME", $val, $match);
					break;
				case "STRING_ID":
					$match = ($match_value_set && $arFilter[$key."_EXACT_MATCH"]=="N") ? "Y" : "N";
					$arSqlSearch[] = GetFilterQuery("G.STRING_ID", $val, $match);
					break;
				case "DESCRIPTION":
					$match = ($match_value_set && $arFilter[$key."_EXACT_MATCH"]=="Y") ? "N" : "Y";
					$arSqlSearch[] = GetFilterQuery("G.DESCRIPTION", $val, $match);
					break;
				case "USERS_1":
					$SHOW_USERS_AMOUNT="Y";
					$arSqlSearch_h[] = "USERS>=".intval($val);
					break;
				case "USERS_2":
					$SHOW_USERS_AMOUNT="Y";
					$arSqlSearch_h[] = "USERS<=".intval($val);
					break;
				case "ANONYMOUS":
					if($val == 'Y' || $val == 'N')
						$arSqlSearch[] = "G.ANONYMOUS='".$val."'";
					break;
			}
		}
		foreach($arSqlSearch_h as $condition)
			$strSqlSearch_h .= " and (".$condition.") ";
	}

	$by = strtolower($by);

	if($by == "id")				$strSqlOrder = " ORDER BY G.ID ";
	elseif($by == "active")		$strSqlOrder = " ORDER BY G.ACTIVE ";
	elseif($by == "timestamp_x")	$strSqlOrder = " ORDER BY G.TIMESTAMP_X ";
	elseif($by == "c_sort")		$strSqlOrder = " ORDER BY G.C_SORT ";
	elseif($by == "sort")			$strSqlOrder = " ORDER BY G.C_SORT, G.NAME, G.ID ";
	elseif($by == "name")			$strSqlOrder = " ORDER BY G.NAME ";
	elseif($by == "string_id")		$strSqlOrder = " ORDER BY G.STRING_ID ";
	elseif($by == "description")		$strSqlOrder = " ORDER BY G.DESCRIPTION ";
	elseif($by == "anonymous")		$strSqlOrder = " ORDER BY G.ANONYMOUS ";
	elseif($by == "dropdown")		$strSqlOrder = " ORDER BY C_SORT, NAME ";
	elseif($by == "users")
	{
		$strSqlOrder = " ORDER BY USERS ";
		$SHOW_USERS_AMOUNT = "Y";
	}
	else
	{
		$strSqlOrder = " ORDER BY G.C_SORT ";
	}

	if(strtolower($order) == "desc")
	{
		$strSqlOrder .= " desc ";
	}
	else
	{
		$strSqlOrder .= " asc ";
	}

	$str_USERS = $str_TABLE = "";
	if($SHOW_USERS_AMOUNT=="Y")
	{
		$str_USERS = "count(distinct U.USER_ID)						USERS,";
		$str_TABLE = "LEFT JOIN b_user_group U ON (U.GROUP_ID=G.ID AND ((U.DATE_ACTIVE_FROM IS NULL) OR (U.DATE_ACTIVE_FROM <= ".$DB->CurrentTimeFunction().")) AND ((U.DATE_ACTIVE_TO IS NULL) OR (U.DATE_ACTIVE_TO >= ".$DB->CurrentTimeFunction().")))";
	}
	$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
	$strSql = "
		SELECT
			G.ID, G.ACTIVE, G.C_SORT, G.ANONYMOUS, G.NAME, G.DESCRIPTION, G.STRING_ID,
			".$str_USERS."
			G.ID REFERENCE_ID,
			" . $helper->getConcatFunction("G.NAME", "' ['", "G.ID", "']'") . " REFERENCE,
			" . $DB->DateToCharFunction("G.TIMESTAMP_X") . " TIMESTAMP_X
		FROM
			b_group G
		".$str_TABLE."
		WHERE
		".$strSqlSearch."
		GROUP BY
			G.ID, G.ACTIVE, G.C_SORT, G.TIMESTAMP_X, G.ANONYMOUS, G.NAME, G.STRING_ID, G.DESCRIPTION
		HAVING
			1=1
			".$strSqlSearch_h."
		".$strSqlOrder;

	$res = $DB->Query($strSql, false, $err_mess.__LINE__);
	$res->is_filtered = (IsFiltered($strSqlSearch) || $strSqlSearch_h <> '');
	return $res;
}