static function PrepareWhere(&$arFields, &$arFilter, &$arJoins)
{
global $DB;
$arSqlSearch = Array();
if (!is_array($arFilter))
$filter_keys = Array();
else
$filter_keys = array_keys($arFilter);
$keyQty = count($filter_keys);
for ($i = 0; $i < $keyQty; $i++)
{
$vals = $arFilter[$filter_keys[$i]];
if (!is_array($vals))
$vals = array($vals);
$filterKey = $filter_keys[$i];
if(mb_strpos($filterKey, '__INNER_FILTER') === 0)
{
$innerFilterSql = self::PrepareWhere($arFields, $vals, $arJoins);
if(is_string($innerFilterSql) && $innerFilterSql !== '')
{
$arSqlSearch[] = '('.$innerFilterSql.')';
}
continue;
}
$key_res = self::GetFilterOperation($filterKey);
$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();
if (!empty($vals))
{
if ($strOperation == "IN")
{
if (isset($arFields[$key]["WHERE"]))
{
$arSqlSearch_tmp1 = call_user_func_array(
$arFields[$key]["WHERE"],
array($vals, $key, $strOperation, $strNegative, $arFields[$key]["FIELD"], &$arFields, &$arFilter)
);
if ($arSqlSearch_tmp1 !== false)
$arSqlSearch_tmp[] = $arSqlSearch_tmp1;
}
else
{
if ($arFields[$key]["TYPE"] == "int")
{
array_walk(
$vals,
function (&$item) {
$item = (int)$item;
}
);
$vals = array_unique($vals);
$val = implode(",", $vals);
if (empty($vals))
$arSqlSearch_tmp[] = "(1 = 2)";
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " NOT " : "")."(".$arFields[$key]["FIELD"]." IN (".$val."))";
}
elseif ($arFields[$key]["TYPE"] == "double")
{
array_walk(
$vals,
function (&$item) {
$item = (float)$item;
}
);
$vals = array_unique($vals);
$val = implode(",", $vals);
if (empty($vals))
$arSqlSearch_tmp[] = "(1 = 2)";
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." (".$val."))";
}
elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char")
{
array_walk(
$vals,
function (&$item) {
$item = "'".$GLOBALS["DB"]->ForSql($item)."'";
}
);
$vals = array_unique($vals);
$val = implode(",", $vals);
if (empty($vals))
$arSqlSearch_tmp[] = "(1 = 2)";
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." (".$val."))";
}
elseif ($arFields[$key]["TYPE"] == "datetime")
{
array_walk(
$vals,
function (&$item) {
$item = $GLOBALS["DB"]->CharToDateFunction($item, "FULL");
}
);
$vals = array_unique($vals);
$val = implode(",", $vals);
if (empty($vals))
$arSqlSearch_tmp[] = "1 = 2";
else
$arSqlSearch_tmp[] = ($strNegative=="Y"?" NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." (".$val."))";
}
elseif ($arFields[$key]["TYPE"] == "date")
{
array_walk(
$vals,
function (&$item) {
$item = $GLOBALS["DB"]->CharToDateFunction($item, "SHORT");
}
);
$vals = array_unique($vals);
$val = implode(",", $vals);
if (empty($vals))
$arSqlSearch_tmp[] = "1 = 2";
else
$arSqlSearch_tmp[] = ($strNegative=="Y"?" NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." (".$val."))";
}
}
}
else
{
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
{
$fieldType = $arFields[$key]["TYPE"];
$fieldName = $arFields[$key]["FIELD"];
if ($strOperation === "QUERY" && $fieldType !== "string" && $fieldType !== "char")
{
// Ignore QUERY operation for not character types - QUERY is supported only for character types.
$strOperation = '=';
}
if (($strOperation === "LIKE" || $strOperation === "RLIKE" || $strOperation === "LLIKE")
&& ($fieldType === "int" || $fieldType === "double"))
{
// Ignore LIKE operation for numeric types.
$strOperation = '=';
}
if ($fieldType === "int")
{
if ((intval($val) === 0) && (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 ($fieldType === "double")
{
$val = str_replace(",", ".", $val);
if ((doubleval($val) === 0) && (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 ($fieldType === "string" || $fieldType === "char")
{
if ($strOperation === "QUERY")
{
$arSqlSearch_tmp[] = GetFilterQuery($fieldName, $val, "Y");
}
else
{
if (($val == '') && (strpos($strOperation, "=") !== false))
$arSqlSearch_tmp[] = "(".$fieldName." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND NOT" : "OR")." (".$DB->Length($fieldName)." <= 0) ".(($strNegative == "Y") ? "AND NOT" : "OR")." (".$fieldName." ".$strOperation." '".$DB->ForSql($val)."' )";
else
{
if($strOperation === "LIKE")
{
if(is_array($val))
$arSqlSearch_tmp[] = "(".$fieldName." LIKE '%".implode("%' ESCAPE '!' OR ".$fieldName." LIKE '%", self::ForLike($val))."%' ESCAPE '!')";
elseif($val == '')
$arSqlSearch_tmp[] = $fieldName;
else
$arSqlSearch_tmp[] = $fieldName." LIKE '%".self::ForLike($val)."%' ESCAPE '!'";
}
elseif($strOperation === "RLIKE" || $strOperation === "LLIKE")
{
if(is_array($val))
$arSqlSearch_tmp[] = "(".$fieldName." LIKE '".implode("' OR ". $fieldName." LIKE '", $DB->ForSql($val))."')";
elseif($val == '')
$arSqlSearch_tmp[] = $fieldName;
else
$arSqlSearch_tmp[] = $fieldName." LIKE '".$DB->ForSql($val)."'";
}
elseif($strOperation === "FT" || $strOperation === "FTI" || $strOperation === "FTL")
{
$queryWhere = new CSQLWhere();
$queryWhere->SetFields(
array(
$key => array(
'FIELD_NAME' => $fieldName,
'FIELD_TYPE' => 'string',
'JOIN' => false
)
)
);
$query = $queryWhere->GetQuery(array($filterKey => $val));
if($query !== '')
{
$arSqlSearch_tmp[] = $query;
}
}
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$fieldName." IS NULL OR NOT " : "")."(".$fieldName." ".$strOperation." '".$DB->ForSql($val)."' )";
}
}
}
elseif ($fieldType === "datetime")
{
if(!in_array($strOperation, array('=', '<', '>', '<=', '>='), true))
{
$strOperation = '=';
}
if ($val == '')
$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL)";
elseif (mb_strtoupper($val) === "NOW")
$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->GetNowFunction().")";
else
$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "FULL").")";
}
elseif ($fieldType === "date")
{
if(!in_array($strOperation, array('=', '<', '>', '<=', '>='), true))
{
$strOperation = '=';
}
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"], $arJoins))
{
$arJoins[] = $arFields[$key]["FROM"];
}
$strSqlSearch_tmp = "";
$sqlSearchQty = count($arSqlSearch_tmp);
for ($j = 0; $j < $sqlSearchQty; $j++)
{
if ($j > 0)
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
$strSqlSearch_tmp .= self::AddBrackets($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 ($arFields[$key]["TYPE"] == "int" || $arFields[$key]["TYPE"] == "double")
{
if ($strSqlSearch_tmp <> '')
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." ".($strNegative=="Y" ? "<>" : "=")." 0)";
}
elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char")
{
if ($strSqlSearch_tmp <> '')
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." ".($strNegative=="Y" ? "<>" : "=")." '')";
}
}
if ($strSqlSearch_tmp != "")
{
$arSqlSearch[] = $strSqlSearch_tmp;
}
}
}
$logic = 'AND';
if(isset($arFilter['LOGIC']) && $arFilter['LOGIC'] !== '')
{
$logic = mb_strtoupper($arFilter['LOGIC']);
if($logic !== 'AND' && $logic !== 'OR')
{
$logic = 'AND';
}
}
$strSqlWhere = '';
$logic = " $logic ";
$sqlSearchQty = count($arSqlSearch);
for ($i = 0; $i < $sqlSearchQty; $i++)
{
$searchItem = $arSqlSearch[$i];
if($searchItem === '')
{
continue;
}
if ($strSqlWhere !== '')
$strSqlWhere .= $logic;
$strSqlWhere .= "($searchItem)";
}
return $strSqlWhere;
}