Exemplo n.º 1
0
 public 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) && count($arGroupBy) > 0) {
         $arSelectFields = $arGroupBy;
         foreach ($arGroupBy as $key => $val) {
             $val = strtoupper($val);
             $key = strtoupper($key);
             if (array_key_exists($val, $arFields) && !in_array($key, $arGroupByFunct)) {
                 if (strlen($strSqlGroupBy) > 0) {
                     $strSqlGroupBy .= ", ";
                 }
                 $strSqlGroupBy .= $arFields[$val]["FIELD"];
                 if (isset($arFields[$val]["FROM"]) && strlen($arFields[$val]["FROM"]) > 0 && !in_array($arFields[$val]["FROM"], $arAlreadyJoined)) {
                     if (strlen($strSqlFrom) > 0) {
                         $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) && strlen($arSelectFields) > 0 && array_key_exists($arSelectFields, $arFields)) {
             $arSelectFields = array($arSelectFields);
         }
         if (!isset($arSelectFields) || !is_array($arSelectFields) || count($arSelectFields) <= 0 || in_array("*", $arSelectFields)) {
             foreach ($arFields as $FIELD_ID => $arField) {
                 if (isset($arField["WHERE_ONLY"]) && $arField["WHERE_ONLY"] == "Y") {
                     continue;
                 }
                 if (strlen($strSqlSelect) > 0) {
                     $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"]) && strlen($arField["FROM"]) > 0 && !in_array($arField["FROM"], $arAlreadyJoined)) {
                     if (strlen($strSqlFrom) > 0) {
                         $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 (strlen($strSqlSelect) > 0) {
                         $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"]) && strlen($arFields[$val]["FROM"]) > 0 && !in_array($arFields[$val]["FROM"], $arAlreadyJoined)) {
                         if (strlen($strSqlFrom) > 0) {
                             $strSqlFrom .= " ";
                         }
                         $strSqlFrom .= $arFields[$val]["FROM"];
                         $arAlreadyJoined[] = $arFields[$val]["FROM"];
                     }
                 }
             }
         }
         if (strlen($strSqlGroupBy) > 0) {
             if (strlen($strSqlSelect) > 0) {
                 $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 = CGroup::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 (strlen($val) <= 0) {
                                     $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 (strlen($val) <= 0) {
                                 $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 (strlen($val) <= 0) {
                                 $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"]) && strlen($arFields[$key]["FROM"]) > 0 && !in_array($arFields[$key]["FROM"], $arAlreadyJoined)) {
                     if (strlen($strSqlFrom) > 0) {
                         $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"]) && strlen($arFields[$by]["FROM"]) > 0 && !in_array($arFields[$by]["FROM"], $arAlreadyJoined)) {
                 if (strlen($strSqlFrom) > 0) {
                     $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);
 }
Exemplo n.º 2
0
 public static function GetFilterOperation($key)
 {
     return CGroup::GetFilterOperation($key);
 }