static function PrepareSql(&$arFields, $arOrder, &$arFilter, $arGroupBy, $arSelectFields, $obUserFieldsSql = false)
{
global $DB;
$strSqlSelect = "";
$strSqlFrom = "";
$strSqlWhere = "";
$strSqlGroupBy = "";
$strSqlOrderBy = "";
$arGroupByFunct = ["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 (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
// 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 = [$arSelectFields];
}
if (!isset($arSelectFields)
|| !is_array($arSelectFields)
|| count($arSelectFields) <= 0
|| in_array("*", $arSelectFields))
{
for ($i = 0; $i < count($arFieldsKeys); $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 (($DB->type == "ORACLE" || $DB->type == "MSSQL") && (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 (($DB->type == "ORACLE" || $DB->type == "MSSQL") && (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"] == "datetime")
{
if (($DB->type == "ORACLE" || $DB->type == "MSSQL") && (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 (($DB->type == "ORACLE" || $DB->type == "MSSQL") && (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 ($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; $i < count($filter_keys); $i++)
{
$vals = $arFilter[$filter_keys[$i]];
if (!is_array($vals))
{
$vals = [$vals];
}
else
{
$vals = array_values($vals);
}
$key = $filter_keys[$i];
$key_res = CTimeManEntry::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 = [];
for ($j = 0; $j < count($vals); $j++)
{
$val = $vals[$j];
if (isset($arFields[$key]["WHERE"]))
{
$arSqlSearch_tmp1 = call_user_func_array(
$arFields[$key]["WHERE"],
[$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 (isset($arFields[$key]["FROM"])
&& $arFields[$key]["FROM"] <> ''
&& !in_array($arFields[$key]["FROM"], $arAlreadyJoined))
{
if ($strSqlFrom <> '')
{
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$key]["FROM"];
$arAlreadyJoined[] = $arFields[$key]["FROM"];
}
$strSqlSearch_tmp = "";
for ($j = 0; $j < count($arSqlSearch_tmp); $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" ? "<>" : "=") . " '')";
}
else
{
$strSqlSearch_tmp .= ($strNegative == "Y" ? " (1=1) " : " (1=0) ");
}
}
if ($strSqlSearch_tmp != "")
{
$arSqlSearch[] = "(" . $strSqlSearch_tmp . ")";
}
}
}
for ($i = 0; $i < count($arSqlSearch); $i++)
{
if ($strSqlWhere <> '')
{
$strSqlWhere .= " AND ";
}
$strSqlWhere .= "(" . $arSqlSearch[$i] . ")";
}
// <-- WHERE
// ORDER BY -->
$arSqlOrder = [];
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"] . " " . $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)
{
$arSqlOrder[] = " " . $obUserFieldsSql->GetOrder($by) . " " . $order . " ";
}
}
$strSqlOrderBy = "";
DelDuplicateSort($arSqlOrder);
for ($i = 0; $i < count($arSqlOrder); $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
return [
"SELECT" => $strSqlSelect,
"FROM" => $strSqlFrom,
"WHERE" => $strSqlWhere,
"GROUPBY" => $strSqlGroupBy,
"ORDERBY" => $strSqlOrderBy,
];
}