function FilterCreateEx($fname, $vals, $type, &$bFullJoin, $cOperationType=false, $bSkipEmpty = true)
{
global $DB;
if(!is_array($vals))
$vals=Array($vals);
if(count($vals)<1)
return "";
if(is_bool($cOperationType))
{
if($cOperationType===true)
$cOperationType = "N";
else
$cOperationType = "E";
}
if($cOperationType=="G")
$strOperation = ">";
elseif($cOperationType=="GE")
$strOperation = ">=";
elseif($cOperationType=="LE")
$strOperation = "<=";
elseif($cOperationType=="L")
$strOperation = "<";
else
$strOperation = "=";
$bFullJoin = false;
$bWasLeftJoin = false;
$res = Array();
for($i = 0, $n = count($vals); $i < $n; $i++)
{
$val = $vals[$i];
if(!$bSkipEmpty || strlen($val)>0 || (is_bool($val) && $val===false))
{
switch ($type)
{
case "string_equal":
if(strlen($val)<=0)
$res[] = ($cOperationType=="N"?"NOT":"")."(".$fname." IS NULL OR ".$DB->Length($fname)."<=0)";
else
$res[] = ($cOperationType=="N"?" ".$fname." IS NULL OR NOT ":"")."(".CIBlock::_Upper($fname).$strOperation.CIBlock::_Upper("'".$DB->ForSql($val)."'").")";
break;
case "string":
if(strlen($val)<=0)
$res[] = ($cOperationType=="N"?"NOT":"")."(".$fname." IS NULL OR ".$DB->Length($fname)."<=0)";
else
if($strOperation=="=")
$res[] = ($cOperationType=="N"?" ".$fname." IS NULL OR NOT ":"")."(".(strtoupper($DB->type)=="ORACLE"?CIBlock::_Upper($fname)." LIKE ".CIBlock::_Upper("'".$DB->ForSqlLike($val)."'")." ESCAPE '\\'" : $fname." ".($strOperation=="="?"LIKE":$strOperation)." '".$DB->ForSqlLike($val)."'").")";
else
$res[] = ($cOperationType=="N"?" ".$fname." IS NULL OR NOT ":"")."(".(strtoupper($DB->type)=="ORACLE"?CIBlock::_Upper($fname)." ".$strOperation." ".CIBlock::_Upper("'".$DB->ForSql($val)."'")." " : $fname." ".$strOperation." '".$DB->ForSql($val)."'").")";
break;
case "date":
if(strlen($val)<=0)
$res[] = ($cOperationType=="N"?"NOT":"")."(".$fname." IS NULL)";
else
$res[] = ($cOperationType=="N"?" ".$fname." IS NULL OR NOT ":"")."(".$fname." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "FULL").")";
break;
case "number":
if(strlen($val)<=0)
$res[] = ($cOperationType=="N"?"NOT":"")."(".$fname." IS NULL)";
else
$res[] = ($cOperationType=="N"?" ".$fname." IS NULL OR NOT ":"")."(".$fname." ".$strOperation." '".DoubleVal($val)."')";
break;
case "number_above":
if(strlen($val)<=0)
$res[] = ($cOperationType=="N"?"NOT":"")."(".$fname." IS NULL)";
else
$res[] = ($cOperationType=="N"?" ".$fname." IS NULL OR NOT ":"")."(".$fname." ".$strOperation." '".$DB->ForSql($val)."')";
break;
}
// INNER JOIN on such conditions
if(strlen($val)>0 && $cOperationType!="N")
$bFullJoin = true;
else
$bWasLeftJoin = true;
}
}
$strResult = "";
for($i = 0, $n = count($res); $i < $n; $i++)
{
if($i>0)
$strResult .= ($cOperationType=="N"?" AND ":" OR ");
$strResult .= "(".$res[$i].")";
}
if($strResult!="")
$strResult = "(".$strResult.")";
if($bFullJoin && $bWasLeftJoin && $cOperationType!="N")
$bFullJoin = false;
return $strResult;
}
public static function GetProperty($IBLOCK_ID, $ELEMENT_ID, $by = "sort", $order = "asc", $arFilter = array())
{
global $DB;
if (is_array($by)) {
if ($order != "asc") {
$arFilter = $order;
}
$arOrder = $by;
} else {
$arOrder = false;
}
$IBLOCK_ID = intval($IBLOCK_ID);
$ELEMENT_ID = intval($ELEMENT_ID);
$VERSION = CIBlockElement::GetIBVersion($IBLOCK_ID);
$strSqlSearch = "";
foreach ($arFilter as $key => $val) {
switch (strtoupper($key)) {
case "ACTIVE":
if ($val == "Y" || $val == "N") {
$strSqlSearch .= "AND BP.ACTIVE='" . $val . "'\n";
}
break;
case "SEARCHABLE":
if ($val == "Y" || $val == "N") {
$strSqlSearch .= "AND BP.SEARCHABLE='" . $val . "'\n";
}
break;
case "NAME":
if (strlen($val) > 0) {
$strSqlSearch .= "AND " . CIBLock::_Upper("BP.NAME") . " LIKE " . CIBlock::_Upper("'" . $DB->ForSql($val) . "'") . "\n";
}
break;
case "ID":
if (is_array($val)) {
if (!empty($val)) {
$strSqlSearch .= "AND BP.ID in (" . implode(", ", array_map("intval", $val)) . ")\n";
}
} elseif (strlen($val) > 0) {
$strSqlSearch .= "AND BP.ID=" . IntVal($val) . "\n";
}
break;
case "PROPERTY_TYPE":
if (strlen($val) > 0) {
$strSqlSearch .= "AND BP.PROPERTY_TYPE='" . $DB->ForSql($val) . "'\n";
}
break;
case "CODE":
if (strlen($val) > 0) {
$strSqlSearch .= "AND " . CIBLock::_Upper("BP.CODE") . " LIKE " . CIBLock::_Upper("'" . $DB->ForSql($val) . "'") . "\n";
}
break;
case "EMPTY":
if (strlen($val) > 0) {
if ($val == "Y") {
$strSqlSearch .= "AND BEP.ID IS NULL\n";
} elseif ($VERSION != 2) {
$strSqlSearch .= "AND BEP.ID IS NOT NULL\n";
}
}
break;
}
}
$arSqlOrder = array();
if ($arOrder) {
foreach ($arOrder as $by => $order) {
$order = strtolower($order);
if ($order != "desc") {
$order = "asc";
}
$by = strtolower($by);
if ($by == "sort") {
$arSqlOrder["BP.SORT"] = $order;
} elseif ($by == "id") {
$arSqlOrder["BP.ID"] = $order;
} elseif ($by == "name") {
$arSqlOrder["BP.NAME"] = $order;
} elseif ($by == "active") {
$arSqlOrder["BP.ACTIVE"] = $order;
} elseif ($by == "value_id") {
$arSqlOrder["BEP.ID"] = $order;
} elseif ($by == "enum_sort") {
$arSqlOrder["BEPE.SORT"] = $order;
} else {
$arSqlOrder["BP.SORT"] = $order;
}
}
} else {
if ($by == "id") {
$arSqlOrder["BP.ID"] = "asc";
} elseif ($by == "name") {
$arSqlOrder["BP.NAME"] = "asc";
} elseif ($by == "active") {
$arSqlOrder["BP.ACTIVE"] = "asc";
} elseif ($by == "value_id") {
$arSqlOrder["BEP.ID"] = $order;
} elseif ($by == "enum_sort") {
$arSqlOrder["BEPE.SORT"] = $order;
} else {
$arSqlOrder["BP.SORT"] = "asc";
$by = "sort";
}
if ($order != "desc") {
$arSqlOrder["BP.SORT"] = "asc";
$arSqlOrder["BP.ID"] = "asc";
$arSqlOrder["BEPE.SORT"] = "asc";
$arSqlOrder["BEP.ID"] = "asc";
$order = "asc";
} else {
$arSqlOrder["BP.SORT"] = "desc";
$arSqlOrder["BP.ID"] = "desc";
$arSqlOrder["BEPE.SORT"] = "desc";
$arSqlOrder["BEP.ID"] = "desc";
}
}
$strSqlOrder = "";
foreach ($arSqlOrder as $key => $val) {
$strSqlOrder .= ", " . $key . " " . $val;
}
if ($strSqlOrder != "") {
$strSqlOrder = ' ORDER BY ' . substr($strSqlOrder, 1);
}
if ($VERSION == 2) {
$strTable = "b_iblock_element_prop_m" . $IBLOCK_ID;
} else {
$strTable = "b_iblock_element_property";
}
$strSql = "\n\t\t\tSELECT BP.*, BEP.ID as PROPERTY_VALUE_ID, BEP.VALUE, BEP.DESCRIPTION, BEPE.VALUE VALUE_ENUM, BEPE.XML_ID VALUE_XML_ID, BEPE.SORT VALUE_SORT\n\t\t\tFROM b_iblock B\n\t\t\t\tINNER JOIN b_iblock_property BP ON B.ID=BP.IBLOCK_ID\n\t\t\t\tLEFT JOIN " . $strTable . " BEP ON (BP.ID = BEP.IBLOCK_PROPERTY_ID AND BEP.IBLOCK_ELEMENT_ID = " . $ELEMENT_ID . ")\n\t\t\t\tLEFT JOIN b_iblock_property_enum BEPE ON (BP.PROPERTY_TYPE = 'L' AND BEPE.ID=BEP.VALUE_ENUM AND BEPE.PROPERTY_ID=BP.ID)\n\t\t\tWHERE B.ID = " . $IBLOCK_ID . "\n\t\t\t\t" . $strSqlSearch . "\n\t\t\t" . $strSqlOrder;
if ($VERSION == 2) {
$result = array();
$arElements = array();
$rs = $DB->Query($strSql);
while ($ar = $rs->Fetch()) {
if ($ar["VERSION"] == 2 && $ar["MULTIPLE"] == "N") {
if (!array_key_exists($ELEMENT_ID, $arElements)) {
$strSql = "\n\t\t\t\t\t\t\tSELECT *\n\t\t\t\t\t\t\tFROM b_iblock_element_prop_s" . $ar["IBLOCK_ID"] . "\n\t\t\t\t\t\t\tWHERE IBLOCK_ELEMENT_ID = " . $ELEMENT_ID . "\n\t\t\t\t\t\t";
$rs2 = $DB->Query($strSql);
$arElements[$ELEMENT_ID] = $rs2->Fetch();
}
if (!isset($arFilter["EMPTY"]) || $arFilter["EMPTY"] == "Y" || strlen($arElements[$ELEMENT_ID]["PROPERTY_" . $ar["ID"]]) > 0) {
$val = $arElements[$ELEMENT_ID]["PROPERTY_" . $ar["ID"]];
$ar["PROPERTY_VALUE_ID"] = $ELEMENT_ID . ":" . $ar["ID"];
if ($ar["PROPERTY_TYPE"] == "L" && intval($val) > 0) {
$arEnum = CIBlockPropertyEnum::GetByID($val);
if ($arEnum !== false) {
$ar["VALUE_ENUM"] = $arEnum["VALUE"];
$ar["VALUE_XML_ID"] = $arEnum["XML_ID"];
$ar["VALUE_SORT"] = $arEnum["SORT"];
}
} else {
$ar["VALUE_ENUM"] = "";
}
if ($ar["PROPERTY_TYPE"] == "N" && strlen($val) > 0) {
$val = CIBlock::NumberFormat($val);
}
$ar["DESCRIPTION"] = $arElements[$ELEMENT_ID]["DESCRIPTION_" . $ar["ID"]];
$ar["VALUE"] = $val;
} else {
continue;
}
}
if ($arFilter["EMPTY"] == "N" && $ar["PROPERTY_VALUE_ID"] == "") {
continue;
}
$result[] = $ar;
}
$rs = new CIBlockPropertyResult();
$rs->InitFromArray($result);
} else {
$rs = new CIBlockPropertyResult($DB->Query($strSql));
}
return $rs;
}
public static function FilterCreateEx($fname, $vals, $type, &$bFullJoin, $cOperationType = false, $bSkipEmpty = true)
{
/** @global CDatabase $DB */
global $DB;
if (!is_array($vals)) {
$vals = array($vals);
}
if (count($vals) < 1) {
return "";
}
if (is_bool($cOperationType)) {
if ($cOperationType === true) {
$cOperationType = "N";
} else {
$cOperationType = "E";
}
}
if ($cOperationType == "E") {
// most req operation
$strOperation = "=";
} elseif ($cOperationType == "G") {
$strOperation = ">";
} elseif ($cOperationType == "GE") {
$strOperation = ">=";
} elseif ($cOperationType == "LE") {
$strOperation = "<=";
} elseif ($cOperationType == "L") {
$strOperation = "<";
} elseif ($cOperationType == 'B') {
$strOperation = array('BETWEEN', 'AND');
} elseif ($cOperationType == 'NB') {
$strOperation = array('BETWEEN', 'AND');
} else {
$strOperation = "=";
}
if ($cOperationType == 'B' || $cOperationType == 'NB') {
if (count($vals) == 2 && !is_array($vals[0])) {
$vals = array($vals);
}
}
$bNegative = substr($cOperationType, 0, 1) == "N";
$bFullJoin = false;
$bWasLeftJoin = false;
$arIn = array();
//This will gather equality number conditions
$bWasNull = false;
$res = array();
foreach ($vals as $val) {
if (!$bSkipEmpty || is_array($strOperation) && is_array($val) || is_bool($val) && $val === false || strlen($val) > 0) {
switch ($type) {
case "string_equal":
if ($cOperationType == "?") {
if (strlen($val) > 0) {
$res[] = GetFilterQuery($fname, $val, "N");
}
} elseif ($cOperationType == "S" || $cOperationType == "NS") {
$res[] = ($cOperationType == "NS" ? " " . $fname . " IS NULL OR NOT " : "") . "(" . CIBlock::_Upper($fname) . " LIKE " . CIBlock::_Upper("'%" . CIBlock::ForLIKE($val) . "%'") . ")";
} elseif (($cOperationType == "B" || $cOperationType == "NB") && is_array($val) && count($val) == 2) {
$res[] = ($cOperationType == "NB" ? " " . $fname . " IS NULL OR NOT " : "") . "(" . CIBlock::_Upper($fname) . " " . $strOperation[0] . " '" . CIBlock::_Upper($DB->ForSql($val[0])) . "' " . $strOperation[1] . " '" . CIBlock::_Upper($DB->ForSql($val[1])) . "')";
} else {
if (strlen($val) <= 0) {
$res[] = ($cOperationType == "N" ? "NOT" : "") . "(" . $fname . " IS NULL OR " . $DB->Length($fname) . "<=0)";
} else {
$res[] = ($cOperationType == "N" ? " " . $fname . " IS NULL OR NOT " : "") . "(" . CIBlock::_Upper($fname) . $strOperation . CIBlock::_Upper("'" . $DB->ForSql($val) . "'") . ")";
}
}
break;
case "string":
if ($cOperationType == "?") {
if (strlen($val) > 0) {
$sr = GetFilterQuery($fname, $val, "Y", array(), $fname == "BE.SEARCHABLE_CONTENT" || $fname == "BE.DETAIL_TEXT" ? "Y" : "N");
if ($sr != "0") {
$res[] = $sr;
}
}
} elseif (($cOperationType == "B" || $cOperationType == "NB") && is_array($val) && count($val) == 2) {
$res[] = ($cOperationType == "NB" ? " " . $fname . " IS NULL OR NOT " : "") . "(" . CIBlock::_Upper($fname) . " " . $strOperation[0] . " '" . CIBlock::_Upper($DB->ForSql($val[0])) . "' " . $strOperation[1] . " '" . CIBlock::_Upper($DB->ForSql($val[1])) . "')";
} elseif ($cOperationType == "S" || $cOperationType == "NS") {
$res[] = ($cOperationType == "NS" ? " " . $fname . " IS NULL OR NOT " : "") . "(" . CIBlock::_Upper($fname) . " LIKE " . CIBlock::_Upper("'%" . CIBlock::ForLIKE($val) . "%'") . ")";
} else {
if (strlen($val) <= 0) {
$res[] = ($bNegative ? "NOT" : "") . "(" . $fname . " IS NULL OR " . $DB->Length($fname) . "<=0)";
} else {
if ($strOperation == "=" && $cOperationType != "I" && $cOperationType != "NI") {
$res[] = ($cOperationType == "N" ? " " . $fname . " IS NULL OR NOT " : "") . "(" . ($DB->type == "ORACLE" ? CIBlock::_Upper($fname) . " LIKE " . CIBlock::_Upper("'" . $DB->ForSqlLike($val) . "'") . " ESCAPE '\\'" : $fname . " LIKE '" . $DB->ForSqlLike($val) . "'") . ")";
} else {
$res[] = ($bNegative ? " " . $fname . " IS NULL OR NOT " : "") . "(" . ($DB->type == "ORACLE" ? CIBlock::_Upper($fname) . " " . $strOperation . " " . CIBlock::_Upper("'" . $DB->ForSql($val) . "'") . " " : $fname . " " . $strOperation . " '" . $DB->ForSql($val) . "'") . ")";
}
}
}
break;
case "date":
if (!is_array($val) && strlen($val) <= 0) {
$res[] = ($cOperationType == "N" ? "NOT" : "") . "(" . $fname . " IS NULL)";
} elseif (($cOperationType == "B" || $cOperationType == "NB") && is_array($val) && count($val) == 2) {
$res[] = ($cOperationType == 'NB' ? ' ' . $fname . ' IS NULL OR NOT ' : '') . '(' . $fname . ' ' . $strOperation[0] . ' ' . $DB->CharToDateFunction($DB->ForSql($val[0]), "FULL") . ' ' . $strOperation[1] . ' ' . $DB->CharToDateFunction($DB->ForSql($val[1]), "FULL") . ')';
} else {
$res[] = ($bNegative ? " " . $fname . " IS NULL OR NOT " : "") . "(" . $fname . " " . $strOperation . " " . $DB->CharToDateFunction($DB->ForSql($val), "FULL") . ")";
}
break;
case "number":
if (!is_array($val) && strlen($val) <= 0) {
$res[] = $fname . " IS " . ($bNegative ? "NOT NULL" : " NULL");
$bWasNull = true;
} elseif ($cOperationType == "B" || $cOperationType == "NB") {
if (is_array($val)) {
if (count($val) == 2) {
$res[] = ($cOperationType == 'NB' ? ' ' . $fname . ' IS NULL OR NOT ' : '') . '(' . $fname . ' ' . $strOperation[0] . ' \'' . DoubleVal($val[0]) . '\' ' . $strOperation[1] . ' \'' . DoubleVal($val[1]) . '\')';
} else {
$res[] = ($cOperationType == 'NB' ? ' ' . $fname . ' IS NULL OR NOT ' : '') . '(' . $fname . ' = \'' . DoubleVal(array_pop($val[0])) . '\')';
}
} else {
$res[] = ($cOperationType == 'NB' ? ' ' . $fname . ' IS NULL OR NOT ' : '') . '(' . $fname . ' = \'' . DoubleVal($val) . '\')';
}
} elseif ($bNegative) {
$res[] = " " . $fname . " IS NULL OR NOT (" . $fname . " " . $strOperation . " '" . DoubleVal($val) . "')";
if ($strOperation == '=') {
$arIn[] = DoubleVal($val);
}
} else {
$res[] = "(" . $fname . " " . $strOperation . " '" . DoubleVal($val) . "')";
if ($strOperation == '=') {
$arIn[] = DoubleVal($val);
}
}
break;
case "number_above":
if (strlen($val) <= 0) {
$res[] = ($cOperationType == "N" ? "NOT" : "") . "(" . $fname . " IS NULL)";
} else {
$res[] = ($cOperationType == "N" ? " " . $fname . " IS NULL OR NOT " : "") . "(" . $fname . " " . $strOperation . " '" . $DB->ForSql($val) . "')";
}
break;
}
if ((is_array($val) || strlen($val) > 0) && !$bNegative) {
$bFullJoin = true;
} else {
$bWasLeftJoin = true;
}
}
}
$strResult = "";
$cntIn = count($arIn);
if (!$bWasNull && $cntIn > 1 && ($cntIn < 2000 || $DB->type == "MYSQL")) {
if ($bNegative) {
$res = array($fname . " IS NULL OR NOT (" . $fname . " IN ('" . implode("', '", $arIn) . "'))");
} else {
$res = array($fname . " IN ('" . implode("', '", $arIn) . "')");
}
}
foreach ($res as $i => $val) {
if ($i > 0) {
$strResult .= $bNegative ? " AND " : " OR ";
}
$strResult .= "(" . $val . ")";
}
if ($strResult != "") {
$strResult = "(" . $strResult . ")";
}
if ($bFullJoin && $bWasLeftJoin && !$bNegative) {
$bFullJoin = false;
}
return $strResult;
}
