CBPHelper::prepareSql

static function prepareSql(&$arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields)
{
	global $DB;

	$strSqlSelect = "";
	$strSqlFrom = "";
	$strSqlWhere = "";
	$strSqlGroupBy = "";
	$strSqlOrderBy = "";

	$arOrder = array_change_key_case($arOrder, CASE_UPPER);

	$arGroupByFunct = array("COUNT", "AVG", "MIN", "MAX", "SUM");

	$arAlreadyJoined = [];

	// GROUP BY -->
	if (is_array($arGroupBy) && count($arGroupBy)>0)
	{
		$arSelectFields = $arGroupBy;
		foreach ($arGroupBy as $key => $val)
		{
			$val = mb_strtoupper($val);
			$key = mb_strtoupper($key);
			if (array_key_exists($val, $arFields) && !in_array($key, $arGroupByFunct))
			{
				if ($strSqlGroupBy <> '')
					$strSqlGroupBy .= ", ";
				$strSqlGroupBy .= $arFields[$val]["FIELD"];

				if (!empty($arFields[$val]["FROM"]))
				{
					$toJoin = (array)$arFields[$val]["FROM"];
					foreach ($toJoin as $join)
					{
						if (in_array($join, $arAlreadyJoined))
						{
							continue;
						}
						if ($strSqlFrom <> '')
						{
							$strSqlFrom .= " ";
						}
						$strSqlFrom .= $join;
						$arAlreadyJoined[] = $join;
					}
				}
			}
		}
	}
	// <-- GROUP BY

	// SELECT -->
	$arFieldsKeys = array_keys($arFields);

	if (is_array($arGroupBy) && count($arGroupBy)==0)
	{
		$strSqlSelect = "COUNT(%%_DISTINCT_%% ".$arFields[$arFieldsKeys[0]]["FIELD"].") as CNT ";
	}
	else
	{
		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))
		{
			for ($i = 0, $cnt = count($arFieldsKeys); $i < $cnt; $i++)
			{
				if (isset($arFields[$arFieldsKeys[$i]]["WHERE_ONLY"])
					&& $arFields[$arFieldsKeys[$i]]["WHERE_ONLY"] == "Y")
				{
					continue;
				}

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

				if ($arFields[$arFieldsKeys[$i]]["TYPE"] == "datetime")
				{
					if (array_key_exists($arFieldsKeys[$i], $arOrder))
						$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i]."_X1, ";

					$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "FULL")." as ".$arFieldsKeys[$i];
				}
				elseif ($arFields[$arFieldsKeys[$i]]["TYPE"] == "date")
				{
					if (array_key_exists($arFieldsKeys[$i], $arOrder))
						$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i]."_X1, ";

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

				if (!empty($arFields[$arFieldsKeys[$i]]["FROM"]))
				{
					$toJoin = (array)$arFields[$arFieldsKeys[$i]]["FROM"];
					foreach ($toJoin as $join)
					{
						if (in_array($join, $arAlreadyJoined))
							continue;
						if ($strSqlFrom <> '')
							$strSqlFrom .= " ";
						$strSqlFrom .= $join;
						$arAlreadyJoined[] = $join;
					}
				}
			}
		}
		else
		{
			foreach ($arOrder as $by => $order)
			{
				if (
					isset($arFields[$by])
					&& !in_array($by, $arSelectFields)
					&& ($arFields[$by]["TYPE"] == "date" || $arFields[$by]["TYPE"] == "datetime")
				)
					$arSelectFields[] = $by;
			}

			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"].") as ".$val;
					}
					else
					{
						if ($arFields[$val]["TYPE"] == "datetime")
						{
							if (array_key_exists($val, $arOrder))
								$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val."_X1, ";

							$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL")." as ".$val;
						}
						elseif ($arFields[$val]["TYPE"] == "date")
						{
							if (array_key_exists($val, $arOrder))
								$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val."_X1, ";

							$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "SHORT")." as ".$val;
						}
						else
							$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val;
					}
					if (!empty($arFields[$val]["FROM"]))
					{
						$toJoin = (array)$arFields[$val]["FROM"];
						foreach ($toJoin as $join)
						{
							if (in_array($join, $arAlreadyJoined))
								continue;
							if ($strSqlFrom <> '')
								$strSqlFrom .= " ";
							$strSqlFrom .= $join;
							$arAlreadyJoined[] = $join;
						}
					}
				}
			}
		}

		if ($strSqlGroupBy <> '')
		{
			if ($strSqlSelect <> '')
				$strSqlSelect .= ", ";
			$strSqlSelect .= "COUNT(%%_DISTINCT_%% ".$arFields[$arFieldsKeys[0]]["FIELD"].") as CNT";
		}
		else
			$strSqlSelect = "%%_DISTINCT_%% ".$strSqlSelect;
	}
	// <-- SELECT

	// WHERE -->
	$arSqlSearch = [];

	if (!is_array($arFilter))
		$filter_keys = [];
	else
		$filter_keys = array_keys($arFilter);

	for ($i = 0, $cnt = count($filter_keys); $i < $cnt; $i++)
	{
		$vals = $arFilter[$filter_keys[$i]];
		if (!is_array($vals))
			$vals = array($vals);

		$key = $filter_keys[$i];
		$key_res = CBPHelper::GetFilterOperation($key);
		$key = $key_res["FIELD"];
		$strNegative = $key_res["NEGATIVE"];
		$strOperation = $key_res["OPERATION"];
		$strOrNull = $key_res["OR_NULL"];

		if (array_key_exists($key, $arFields))
		{
			$arSqlSearch_tmp = array();
			for ($j = 0, $cntj = count($vals); $j < $cntj; $j++)
			{
				$val = $vals[$j];

				if (isset($arFields[$key]["WHERE"]))
				{
					$arSqlSearch_tmp1 = call_user_func_array(
							$arFields[$key]["WHERE"],
							array($val, $key, $strOperation, $strNegative, $arFields[$key]["FIELD"], $arFields, $arFilter)
						);
					if ($arSqlSearch_tmp1 !== false)
						$arSqlSearch_tmp[] = $arSqlSearch_tmp1;
				}
				else
				{
					if ($arFields[$key]["TYPE"] == "int")
					{
						if ((intval($val) == 0) && (mb_strpos($strOperation, "=") !== False))
							$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND" : "OR")." ".(($strNegative == "Y") ? "NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." 0)";
						else
							$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".intval($val)." )";
					}
					elseif ($arFields[$key]["TYPE"] == "double")
					{
						$val = str_replace(",", ".", $val);

						if ((DoubleVal($val) == 0) && (mb_strpos($strOperation, "=") !== False))
							$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND" : "OR")." ".(($strNegative == "Y") ? "NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." 0)";
						else
							$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".DoubleVal($val)." )";
					}
					elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char")
					{
						if ($strOperation == "QUERY")
						{
							$arSqlSearch_tmp[] = GetFilterQuery($arFields[$key]["FIELD"], $val, "Y");
						}
						else
						{
							if (($val == '') && (mb_strpos($strOperation, "=") !== False))
								$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND NOT" : "OR")." (".$DB->Length($arFields[$key]["FIELD"])." <= 0) ".(($strNegative == "Y") ? "AND NOT" : "OR")." (".$arFields[$key]["FIELD"]." ".$strOperation." '".$DB->ForSql($val)."' )";
							else
								$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." '".$DB->ForSql($val)."' )";
						}
					}
					elseif ($arFields[$key]["TYPE"] == "datetime")
					{
						if ($val == '')
							$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL)";
						else
							$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "FULL").")";
					}
					elseif ($arFields[$key]["TYPE"] == "date")
					{
						if ($val == '')
							$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL)";
						else
							$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "SHORT").")";
					}
				}
			}

			if (!empty($arFields[$key]["FROM"]))
			{
				$toJoin = (array)$arFields[$key]["FROM"];
				foreach ($toJoin as $join)
				{
					if (in_array($join, $arAlreadyJoined))
						continue;
					if ($strSqlFrom <> '')
						$strSqlFrom .= " ";
					$strSqlFrom .= $join;
					$arAlreadyJoined[] = $join;
				}
			}

			$strSqlSearch_tmp = "";
			for ($j = 0, $cntj = count($arSqlSearch_tmp); $j < $cntj; $j++)
			{
				if ($j > 0)
					$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
				$strSqlSearch_tmp .= "(".$arSqlSearch_tmp[$j].")";
			}
			if ($strOrNull == "Y")
			{
				if ($strSqlSearch_tmp <> '')
					$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
				$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." IS ".($strNegative=="Y" ? "NOT " : "")."NULL)";

				if ($strSqlSearch_tmp <> '')
					$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
				if ($arFields[$key]["TYPE"] == "int" || $arFields[$key]["TYPE"] == "double")
					$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." ".($strNegative=="Y" ? "<>" : "=")." 0)";
				elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char")
					$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." ".($strNegative=="Y" ? "<>" : "=")." '')";
			}

			if ($strSqlSearch_tmp != "")
				$arSqlSearch[] = "(".$strSqlSearch_tmp.")";
		}
	}

	for ($i = 0, $cnt = count($arSqlSearch); $i < $cnt; $i++)
	{
		if ($strSqlWhere <> '')
			$strSqlWhere .= " AND ";
		$strSqlWhere .= "(".$arSqlSearch[$i].")";
	}
	// <-- WHERE

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

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

		if (array_key_exists($by, $arFields))
		{
			if ($arFields[$by]["TYPE"] == "datetime" || $arFields[$by]["TYPE"] == "date")
				$arSqlOrder[] = " ".$by."_X1 ".$order." ";
			else
				$arSqlOrder[] = " ".$arFields[$by]["FIELD"]." ".$order." ";

			if (!empty($arFields[$by]["FROM"]))
			{
				$toJoin = (array)$arFields[$by]["FROM"];
				foreach ($toJoin as $join)
				{
					if (in_array($join, $arAlreadyJoined))
						continue;
					if ($strSqlFrom <> '')
						$strSqlFrom .= " ";
					$strSqlFrom .= $join;
					$arAlreadyJoined[] = $join;
				}
			}
		}
	}

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

		$strSqlOrderBy .= $arSqlOrder[$i];
	}
	// <-- ORDER BY

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