CTimeManReportFull::PrepareSql

static function PrepareSql(&$arFields, $arOrder, &$arFilter, $arSelectFields = false, $arNavStartParams = false)
{
	global $DB;

	$strSqlSelect = "";
	$strSqlFrom = "";
	$strSqlWhere = "";
	$strSqlGroupBy = "";
	$strSqlOrderBy = "";
	$strSqlLimit = "";
	$arGroupByFunct = array();
	$arAlreadyJoined = array();
	// SELECT -->
	$arFieldsKeys = array_keys($arFields);

	if (isset($arSelectFields) && !is_array($arSelectFields) && is_string($arSelectFields) && $arSelectFields <> '' && array_key_exists($arSelectFields, $arFields))
			$arSelectFields = array($arSelectFields);

	if (!isset($arSelectFields)
			|| !is_array($arSelectFields)
			|| count($arSelectFields)<=0
			|| in_array("*", $arSelectFields))
	{
		foreach($arFields as $key => $arField)
			{
				if (isset($arField["WHERE_ONLY"])
						&& $arField["WHERE_ONLY"] == "Y")
				{
						continue;
				}

				if ($strSqlSelect <> '')
						$strSqlSelect .= ", ";

				if (($arField["FIELD_TYPE"] ?? '') == "datetime")
				{
						if (($DB->type == "ORACLE" || $DB->type == "MSSQL") && (array_key_exists($key, $arOrder)))
							$strSqlSelect .= $arField["FIELD_NAME"]." as ".$key."_X1, ";

						$strSqlSelect .= $DB->DateToCharFunction($arField["FIELD_NAME"], "FULL")." as ".$key;
				}
				elseif (($arField["FIELD_TYPE"] ?? '') == "date")
				{
						if (($DB->type == "ORACLE" || $DB->type == "MSSQL") && (array_key_exists($key, $arOrder)))
							$strSqlSelect .= $arField["FIELD_NAME"]." as ".$key."_X1, ";

						$strSqlSelect .= $DB->DateToCharFunction($arField["FIELD_NAME"], "SHORT")." as ".$key;
				}
				else
						$strSqlSelect .= $arField["FIELD_NAME"]." as ".$key;

				if (isset($arField["FROM"])
						&& $arField["FROM"] <> ''
						&& !in_array($arField["FROM"], $arAlreadyJoined))
				{
						if ($strSqlFrom <> '')
							$strSqlFrom .= " ";
						$strSqlFrom .= $arField["FROM"];
						$arAlreadyJoined[] = $arField["FROM"];
				}
			}
	}
	else
	{
			foreach ($arSelectFields as $key => $val)
			{
				$val = mb_strtoupper($val);
				$key = mb_strtoupper($key);
				if (array_key_exists($val, $arFields))
				{
						if ($strSqlSelect <> '')
							$strSqlSelect .= ", ";

						if (in_array($key, $arGroupByFunct))
						{
							$strSqlSelect .= $key."(".$arFields[$val]["FIELD_NAME"].") as ".$val;
						}
						else
						{
							if ($arFields[$val]["FIELD_TYPE"] == "datetime")
							{
									if (($DB->type == "ORACLE" || $DB->type == "MSSQL") && (array_key_exists($val, $arOrder)))
										$strSqlSelect .= $arFields[$val]["FIELD_NAME"]." as ".$val."_X1, ";

									$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL")." as ".$val;
							}
							elseif ($arFields[$val]["FIELD_TYPE"] == "date")
							{
									if (($DB->type == "ORACLE" || $DB->type == "MSSQL") && (array_key_exists($val, $arOrder)))
										$strSqlSelect .= $arFields[$val]["FIELD_NAME"]." as ".$val."_X1, ";

									$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD_NAME"], "SHORT")." as ".$val;
							}
							else
									$strSqlSelect .= $arFields[$val]["FIELD_NAME"]." as ".$val;
						}

						if (isset($arFields[$val]["FROM"])
							&& $arFields[$val]["FROM"] <> ''
							&& !in_array($arFields[$val]["FROM"], $arAlreadyJoined))
						{
							if ($strSqlFrom <> '')
									$strSqlFrom .= " ";
							$strSqlFrom .= $arFields[$val]["FROM"];
							$arAlreadyJoined[] = $arFields[$val]["FROM"];
						}
				}
			}
	}

	// <-- SELECT

	// WHERE -->
	$obWhere = new CSQLWhere;
	$obWhere->SetFields($arFields);
	$strSqlWhere = $obWhere->GetQuery($arFilter);

	// ORDER BY -->
	$arSqlOrder = Array();
	foreach ($arOrder as $by => $order)
	{
		$by = mb_strtoupper($by);
		$order = mb_strtoupper($order);

			if ($order != "ASC")
				$order = "DESC";
			else
				$order = "ASC";

			if (array_key_exists($by, $arFields))
			{
				$arSqlOrder[] = " ".$arFields[$by]["FIELD_NAME"]." ".$order." ";

				if (isset($arFields[$by]["FROM"])
						&& $arFields[$by]["FROM"] <> ''
						&& !in_array($arFields[$by]["FROM"], $arAlreadyJoined))
				{
						if ($strSqlFrom <> '')
							$strSqlFrom .= " ";
						$strSqlFrom .= $arFields[$by]["FROM"];
						$arAlreadyJoined[] = $arFields[$by]["FROM"];
				}
			}
	}

	$strSqlOrderBy = "";
	DelDuplicateSort($arSqlOrder);
	$cnt = count($arSqlOrder);
	for ($i=0; $i<$cnt; $i++)
	{
			if ($strSqlOrderBy <> '')
				$strSqlOrderBy .= ", ";

			if($DB->type == "ORACLE")
			{
				if(mb_substr($arSqlOrder[$i], -3) == "ASC")
						$strSqlOrderBy .= $arSqlOrder[$i]." NULLS FIRST";
				else
						$strSqlOrderBy .= $arSqlOrder[$i]." NULLS LAST";
			}
			else
				$strSqlOrderBy .= $arSqlOrder[$i];
	}
	// <-- ORDER BY

	if (
		is_array($arNavStartParams)
		&& intval($arNavStartParams["nTopCount"] ?? 0) > 0
	)
	{
		$dbType = $DB->type;
		switch ($dbType)
		{
			case "MSSQL":
				$strSqlSelect = "TOP ".$arNavStartParams["nTopCount"]." ".$strSqlSelect;
				break;
			case "ORACLE":
				if($strSqlWhere <> '')
					$strSqlWhere.=" AND ";
				$strSqlWhere.= "ROWNUM<=".$arNavStartParams["nTopCount"];
				break;
			case "MYSQL":
				$strSqlLimit.= "LIMIT ".$arNavStartParams["nTopCount"];
		}
	}

	return array(
			"SELECT" => $strSqlSelect,
			"FROM" => $strSqlFrom,
			"WHERE" => $strSqlWhere,
			"GROUPBY" => $strSqlGroupBy,
			"ORDERBY" => $strSqlOrderBy,
			"LIMIT"=> $strSqlLimit
	);
}