CStatRegion::GetList

function GetList($arOrder = /*.(array[string]string).*/array(), $arFilter = /*.(array[string]string).*/array())
{
	$DB = CDatabase::GetModuleConnection('statistic');

	$arQueryOrder = array();
	if(is_array($arOrder))
	{
		foreach($arOrder as $strColumn => $strDirection)
		{
			$strColumn = mb_strtoupper($strColumn);
			$strDirection = mb_strtoupper($strDirection) === "ASC"? "ASC": "DESC";
			switch($strColumn)
			{
				case "COUNTRY_ID":
				case "COUNTRY_SHORT_NAME":
				case "COUNTRY_NAME":
				case "REGION_NAME":
					$arQueryOrder[$strColumn] = $strColumn." ".$strDirection;
					break;
				case "REGION":
					$arQueryOrder["COUNTRY_ID"] = "COUNTRY_ID ".$strDirection;
					$arQueryOrder["REGION_NAME"] = "REGION_NAME ".$strDirection;
					break;
				default:
					break;
			}
		}
	}

	$obQueryWhere = new CSQLWhere;
	$obQueryWhere->SetFields(array(
		"COUNTRY_ID" => array(
			"TABLE_ALIAS" => "R",
			"FIELD_NAME" => "R.COUNTRY_ID",
			"FIELD_TYPE" => "string",
			"JOIN" => "",
		),
		"COUNTRY_SHORT_NAME" => array(
			"TABLE_ALIAS" => "C",
			"FIELD_NAME" => "C.SHORT_NAME",
			"FIELD_TYPE" => "string",
			"JOIN" => "",
		),
		"COUNTRY_NAME" => array(
			"TABLE_ALIAS" => "C",
			"FIELD_NAME" => "C.NAME",
			"FIELD_TYPE" => "string",
			"JOIN" => "",
		),
		"REGION_NAME" => array(
			"TABLE_ALIAS" => "R",
			"FIELD_NAME" => "R.REGION",
			"FIELD_TYPE" => "string",
			"JOIN" => "",
		),
	));

	$strSql = "
		SELECT
			R.COUNTRY_ID
			,C.SHORT_NAME COUNTRY_SHORT_NAME
			,C.NAME COUNTRY_NAME
			,R.REGION REGION_NAME
		FROM
			b_stat_city R
			INNER JOIN b_stat_country C on C.ID = R.COUNTRY_ID
	";

	$strQueryWhere = $obQueryWhere->GetQuery($arFilter);

	if($strQueryWhere <> '')
	{
		$strSql .= "
			WHERE
			".$strQueryWhere."
		";
	}
	$strSql .= "
		GROUP BY
			R.COUNTRY_ID, R.REGION, C.SHORT_NAME, C.NAME
		";

	if(count($arQueryOrder) > 0)
	{
		$strSql .= "
			ORDER BY
			".implode(", ", $arQueryOrder)."
		";
	}

	return $DB->Query($strSql, false, "File: ".__FILE__."
Line: ".__LINE__);
}