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; }