static function PrepareSql(&$arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields)
{
global $DB;
$strSqlSelect = "";
$strSqlFrom = "";
$strSqlWhere = "";
$strSqlGroupBy = "";
$arGroupByFunct = array("COUNT", "AVG", "MIN", "MAX", "SUM");
$arAlreadyJoined = array();
// GROUP BY -->
if (is_array($arGroupBy) && !empty($arGroupBy))
{
$arSelectFields = $arGroupBy;
foreach ($arGroupBy as $key => $val)
{
$val = strtoupper($val);
$key = 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) && empty($arGroupBy))
{
$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)
|| empty($arSelectFields)
|| in_array("*", $arSelectFields))
{
foreach ($arFields as $FIELD_ID => $arField)
{
if (isset($arField["WHERE_ONLY"])
&& $arField["WHERE_ONLY"] == "Y")
{
continue;
}
if ($strSqlSelect <> '')
$strSqlSelect .= ", ";
if ($arField["TYPE"] == "datetime")
$strSqlSelect .= $DB->DateToCharFunction($arField["FIELD"], "FULL")." as ".$FIELD_ID;
elseif ($arField["TYPE"] == "date")
$strSqlSelect .= $DB->DateToCharFunction($arField["FIELD"], "SHORT")." as ".$FIELD_ID;
else
$strSqlSelect .= $arField["FIELD"]." as ".$FIELD_ID;
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 = strtoupper($val);
$key = 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")
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL")." as ".$val;
elseif ($arFields[$val]["TYPE"] == "date")
$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 = Array();
if (is_array($arFilter))
{
foreach ($arFilter as $key => $vals)
{
if (!is_array($vals))
$vals = array($vals);
$key_res = static::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();
foreach($vals as $val)
{
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)
$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL OR ".$arFields[$key]["FIELD"]." <= 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)
$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL OR ".$arFields[$key]["FIELD"]." <= 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 ((string)$val == '')
$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL OR LENGTH(".$arFields[$key]["FIELD"].")<=0)";
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 ((string)$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 ((string)$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 = "";
foreach ($arSqlSearch_tmp as $condition)
{
if ($strSqlSearch_tmp != "")
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
$strSqlSearch_tmp .= "(".$condition.")";
}
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 != "")
$arSqlSearch[] = "(".$strSqlSearch_tmp.")";
}
}
}
foreach ($arSqlSearch as $condition)
{
if ($strSqlWhere != "")
$strSqlWhere .= " AND ";
$strSqlWhere .= "(".$condition.")";
}
// <-- WHERE
// ORDER BY -->
$arSqlOrder = Array();
foreach ($arOrder as $by => $order)
{
$by = strtoupper($by);
$order = strtoupper($order);
if ($order != "ASC")
$order = "DESC";
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"];
}
}
}
$strSqlOrderBy = implode(", ", $arSqlOrder);
// <-- ORDER BY
return array(
"SELECT" => $strSqlSelect,
"FROM" => $strSqlFrom,
"WHERE" => $strSqlWhere,
"GROUPBY" => $strSqlGroupBy,
"ORDERBY" => $strSqlOrderBy
);
}