CAllSocNetGroup::PrepareSql

static function PrepareSql(&$arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields, $arUF = array()): array
{
	global $DB;

	$obUserFieldsSql = false;

	if (is_array($arUF) && array_key_exists("ENTITY_ID", $arUF))
	{
		$obUserFieldsSql = new CUserTypeSQL;
		$obUserFieldsSql->SetEntity($arUF["ENTITY_ID"], $arFields["ID"]["FIELD"]);
		$obUserFieldsSql->SetSelect($arSelectFields);
		$obUserFieldsSql->SetFilter($arFilter);
		$obUserFieldsSql->SetOrder($arOrder);
	}

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

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

	$arAlreadyJoined = array();

	// 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 (isset($arFields[$val]["FROM"])
					&& $arFields[$val]["FROM"] <> ''
					&& !in_array($arFields[$val]["FROM"], $arAlreadyJoined))
				{
					if ($strSqlFrom <> '')
						$strSqlFrom .= " ";
					$strSqlFrom .= $arFields[$val]["FROM"];
					$arAlreadyJoined[] = $arFields[$val]["FROM"];
				}
			}
		}
	}
	// <-- GROUP BY

	// WHERE -->
	$arAlreadyJoinedOld = $arAlreadyJoined;

	if (
		isset($arFilter['ID'])
		&& is_array($arFilter['ID'])
	)
	{
		$arFilter['@ID'] = $arFilter['ID'];
		unset($arFilter['ID']);
	}
	if (
		isset($arFilter['!ID'])
		&& is_array($arFilter['!ID'])
	)
	{
		$arFilter['!@ID'] = $arFilter['!ID'];
		unset($arFilter['!ID']);
	}

	$strSqlWhere .= CSqlUtil::PrepareWhere($arFields, $arFilter, $arAlreadyJoined);
	$arAlreadyJoinedDiff = array_diff($arAlreadyJoined, $arAlreadyJoinedOld);

	foreach($arAlreadyJoinedDiff as $from_tmp)
	{
		if ($strSqlFrom <> '')
			$strSqlFrom .= " ";
		$strSqlFrom .= $from_tmp;
	}

	if ($obUserFieldsSql)
	{
		$r = $obUserFieldsSql->GetFilter();
		if($r <> '')
			$strSqlWhere .= ($strSqlWhere <> '' ? " AND" : "")." (".$r.") ";
	}
	// <-- WHERE

	// 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))
		{
			if ($arFields[$by]["TYPE"] == "datetime" || $arFields[$by]["TYPE"] == "date")
			{
				$arSqlOrder[] = " ".$by."_X1 ".$order." ";
				if (!is_array($arSelectFields) || !in_array($by, $arSelectFields))
					$arSelectFields[] = $by;
			}
			else
				$arSqlOrder[] = " ".$arFields[$by]["FIELD"]." ".$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"];
			}
		}
		elseif($obUserFieldsSql && $s = $obUserFieldsSql->GetOrder($by))
			$arSqlOrder[$by] = " ".$s." ".$order." ";
	}

	$strSqlOrderBy = "";
	DelDuplicateSort($arSqlOrder);
	$tmp_count = count($arSqlOrder);
	for ($i=0; $i < $tmp_count; $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

	// 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))
		{
			$tmp_count = count($arFieldsKeys);
			for ($i = 0; $i < $tmp_count; $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"])." 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 (isset($arFields[$arFieldsKeys[$i]]["FROM"])
					&& $arFields[$arFieldsKeys[$i]]["FROM"] <> ''
					&& !in_array($arFields[$arFieldsKeys[$i]]["FROM"], $arAlreadyJoined))
				{
					if ($strSqlFrom <> '')
						$strSqlFrom .= " ";
					$strSqlFrom .= $arFields[$arFieldsKeys[$i]]["FROM"];
					$arAlreadyJoined[] = $arFields[$arFieldsKeys[$i]]["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"].") as ".$val;
					else
					{
						if (($arFields[$val]["TYPE"] ?? null) === "datetime")
						{
							if (array_key_exists($val, $arOrder))
								$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val."_X1, ";

							$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"])." as ".$val;
						}
						elseif (($arFields[$val]["TYPE"] ?? null) === "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 (isset($arFields[$val]["FROM"])
						&& $arFields[$val]["FROM"] <> ''
						&& !in_array($arFields[$val]["FROM"], $arAlreadyJoined))
					{
						if ($strSqlFrom <> '')
							$strSqlFrom .= " ";
						$strSqlFrom .= $arFields[$val]["FROM"];
						$arAlreadyJoined[] = $arFields[$val]["FROM"];
					}
				}
			}
		}

		if ($obUserFieldsSql)
			$strSqlSelect .= ($strSqlSelect == '' ? $arFields["ID"]["FIELD"] : "").$obUserFieldsSql->GetSelect();

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

	if ($obUserFieldsSql)
	{
		$strSqlFrom .= " ".$obUserFieldsSql->GetJoin($arFields["ID"]["FIELD"]);
	}

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