/** * @param boolean|array[] $arOrder * @param boolean|array[] $arFilter * @param boolean|array[] $arSelect * * @return boolean|CDBResult */ public static function GetList($arOrder = false, $arFilter = false, $arSelect = false) { global $DB; if (!is_array($arSelect)) { $arSelect = array(); } if (count($arSelect) < 1) { $arSelect = array("ID", "TIMESTAMP_X", "THREADS", "HITS", "ERRORS", "PAGES_PER_SECOND", "PAGE_EXEC_TIME", "PAGE_RESP_TIME"); } if (!is_array($arOrder)) { $arOrder = array(); } $arQueryOrder = array(); foreach ($arOrder as $strColumn => $strDirection) { $strColumn = strtoupper($strColumn); $strDirection = strtoupper($strDirection) == "ASC" ? "ASC" : "DESC"; switch ($strColumn) { case "ID": $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; break; } } $arQuerySelect = array(); foreach ($arSelect as $strColumn) { $strColumn = strtoupper($strColumn); switch ($strColumn) { case "ID": case "TIMESTAMP_X": case "THREADS": case "HITS": case "ERRORS": case "PAGES_PER_SECOND": case "PAGE_EXEC_TIME": case "PAGE_RESP_TIME": $arQuerySelect[$strColumn] = "p." . $strColumn; break; } } if (count($arQuerySelect) < 1) { $arQuerySelect = array("ID" => "p.ID"); } $obQueryWhere = new CSQLWhere(); $arFields = array("ID" => array("TABLE_ALIAS" => "p", "FIELD_NAME" => "p.ID", "FIELD_TYPE" => "int", "JOIN" => false)); $obQueryWhere->SetFields($arFields); if (!is_array($arFilter)) { $arFilter = array(); } $strQueryWhere = $obQueryWhere->GetQuery($arFilter); $bDistinct = $obQueryWhere->bDistinctReqired; $strSql = "\n\t\t\tSELECT " . ($bDistinct ? "DISTINCT" : "") . "\n\t\t\t" . implode(", ", $arQuerySelect) . "\n\t\t\tFROM\n\t\t\t\tb_perf_cluster p\n\t\t\t" . $obQueryWhere->GetJoins() . "\n\t\t"; if ($strQueryWhere) { $strSql .= "\n\t\t\t\tWHERE\n\t\t\t\t" . $strQueryWhere . "\n\t\t\t"; } if (count($arQueryOrder) > 0) { $strSql .= "\n\t\t\t\tORDER BY\n\t\t\t\t" . implode(", ", $arQueryOrder) . "\n\t\t\t"; } return $DB->Query($strSql, false, '', array('fixed_connection' => true)); }
function GetJoin($ID) { $result = $this->obWhere->GetJoins(); $table = " b_uts_".$this->entity_id." ".$this->table_alias." "; if((count($this->select)>0 || count($this->order)>0) && strpos($result, $table)===false) $result .= "\nLEFT JOIN".$table."ON ".$this->table_alias.".VALUE_ID = ".$ID; return $result; }
public static function GetList($arSelect, $arFilter, $arOrder) { global $DB; if (!is_array($arSelect)) { $arSelect = array(); } if (count($arSelect) < 1) { $arSelect = array("ID"); } if (!is_array($arOrder)) { $arOrder = array(); } if (count($arOrder) < 1) { $arOrder = array("TABLE_NAME" => "ASC"); } $arQueryOrder = array(); foreach ($arOrder as $strColumn => $strDirection) { $strColumn = strtoupper($strColumn); $strDirection = strtoupper($strDirection) == "ASC" ? "ASC" : "DESC"; switch ($strColumn) { case "ID": case "TABLE_NAME": case "SQL_COUNT": case "SQL_TIME": $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; break; } } $bJoin = false; $arQuerySelect = array(); foreach ($arSelect as $strColumn) { $strColumn = strtoupper($strColumn); switch ($strColumn) { case "ID": case "TABLE_NAME": case "TABLE_ALIAS": case "COLUMN_NAMES": case "SQL_MD5": case "SQL_TEXT": case "SQL_COUNT": case "SQL_TIME": case "SQL_EXPLAIN": $arQuerySelect[$strColumn] = "s." . $strColumn; break; case "BANNED": $arQuerySelect[$strColumn] = "c." . $strColumn; $bJoin = true; break; } } $obQueryWhere = new CSQLWhere(); $obQueryWhere->SetFields(array("ID" => array("TABLE_ALIAS" => "s", "FIELD_NAME" => "ID", "FIELD_TYPE" => "int", "JOIN" => false), "SQL_MD5" => array("TABLE_ALIAS" => "s", "FIELD_NAME" => "s.SQL_MD5", "FIELD_TYPE" => "string", "JOIN" => false), "TABLE_NAME" => array("TABLE_ALIAS" => "s", "FIELD_NAME" => "s.TABLE_NAME", "FIELD_TYPE" => "string", "JOIN" => false), "COLUMN_NAMES" => array("TABLE_ALIAS" => "s", "FIELD_NAME" => "s.COLUMN_NAMES", "FIELD_TYPE" => "string", "JOIN" => false), "BANNED" => array("TABLE_ALIAS" => "c1", "FIELD_NAME" => "c1.BANNED", "FIELD_TYPE" => "string", "JOIN" => "LEFT JOIN b_perf_index_complete c1 on c1.TABLE_NAME = s.TABLE_NAME and c1.COLUMN_NAMES = s.COLUMN_NAMES"))); if (count($arQuerySelect) < 1) { $arQuerySelect = array("ID" => "s.ID"); } if (!is_array($arFilter)) { $arFilter = array(); } $strQueryWhere = $obQueryWhere->GetQuery($arFilter); $strSql = "\n\t\t\tSELECT " . implode(", ", $arQuerySelect) . "\n\t\t\tFROM b_perf_index_suggest s\n\t\t\t" . $obQueryWhere->GetJoins() . "\n\t\t\t" . ($bJoin ? "LEFT JOIN b_perf_index_complete c on c.TABLE_NAME = s.TABLE_NAME and c.COLUMN_NAMES = s.COLUMN_NAMES" : "") . "\n\t\t\t" . ($strQueryWhere ? "WHERE " . $strQueryWhere : "") . "\n\t\t\t" . (count($arQueryOrder) ? "ORDER BY " . implode(", ", $arQueryOrder) : "") . "\n\t\t"; $res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); return $res; }
public static function GetList($arSelect, $arFilter, $arOrder, $bGroup, $arNavStartParams = false) { global $DB; if(!is_array($arSelect)) $arSelect = array(); if(count($arSelect) < 1) $arSelect = array( "ID", ); if(!is_array($arOrder)) $arOrder = array(); if(count($arOrder) < 1) $arOrder = array( "HIT_ID" => "DESC", "NN" => "ASC", ); $arQueryOrder = array(); foreach($arOrder as $strColumn => $strDirection) { $strColumn = strtoupper($strColumn); $strDirection = strtoupper($strDirection)=="ASC"? "ASC": "DESC"; switch($strColumn) { case "ID": case "HIT_ID": case "NN": case "MODULE_NAME": case "COMPONENT_NAME": $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn." ".$strDirection; break; case "SQL_TEXT": case "QUERY_TIME": if(!$bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn." ".$strDirection; } break; case "MAX_QUERY_TIME": case "MIN_QUERY_TIME": case "AVG_QUERY_TIME": case "SUM_QUERY_TIME": if($bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn." ".$strDirection; } break; case "COUNT": if($bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn." ".$strDirection; } break; } } $arQueryGroup = array(); $arQuerySelect = array(); foreach($arSelect as $strColumn) { $strColumn = strtoupper($strColumn); switch($strColumn) { case "ID": case "HIT_ID": case "NN": case "MODULE_NAME": case "COMPONENT_NAME": if($bGroup) $arQueryGroup[$strColumn] = "s.".$strColumn; $arQuerySelect[$strColumn] = "s.".$strColumn; break; case "SQL_TEXT": case "QUERY_TIME": if(!$bGroup) $arQuerySelect[$strColumn] = "s.".$strColumn; break; case "MAX_QUERY_TIME": case "MIN_QUERY_TIME": case "AVG_QUERY_TIME": case "SUM_QUERY_TIME": if($bGroup) { $arQuerySelect[$strColumn] = substr($strColumn, 0, 3)."(s.".substr($strColumn, 4).") ".$strColumn; } break; case "COUNT": if($bGroup) { $arQuerySelect[$strColumn] = "COUNT(s.ID) ".$strColumn; } break; } } $obQueryWhere = new CSQLWhere; $obQueryWhere->SetFields(array( "HIT_ID" => array( "TABLE_ALIAS" => "s", "FIELD_NAME" => "s.HIT_ID", "FIELD_TYPE" => "int", //int, double, file, enum, int, string, date, datetime "JOIN" => false, //"LEFT_JOIN" => "lt", ), "COMPONENT_ID" => array( "TABLE_ALIAS" => "s", "FIELD_NAME" => "s.COMPONENT_ID", "FIELD_TYPE" => "int", "JOIN" => false, ), "ID" => array( "TABLE_ALIAS" => "s", "FIELD_NAME" => "s.ID", "FIELD_TYPE" => "int", "JOIN" => false, ), "QUERY_TIME" => array( "TABLE_ALIAS" => "s", "FIELD_NAME" => "s.QUERY_TIME", "FIELD_TYPE" => "double", "JOIN" => false, ), "SUGGEST_ID" => array( "TABLE_ALIAS" => "iss", "FIELD_NAME" => "iss.SUGGEST_ID", "FIELD_TYPE" => "int", "JOIN" => "INNER JOIN b_perf_index_suggest_sql iss on iss.SQL_ID = s.ID", "LEFT_JOIN" => "LEFT JOIN b_perf_index_suggest_sql is on is.SQL_ID = s.ID", ), )); if(count($arQuerySelect) < 1) $arQuerySelect = array("ID"=>"s.ID"); if(!is_array($arFilter)) $arFilter = array(); $strQueryWhere = $obQueryWhere->GetQuery($arFilter); if(is_array($arNavStartParams) && $arNavStartParams["nTopCount"] > 0) { $strSql = $DB->TopSQL(" SELECT ".implode(", ", $arQuerySelect)." FROM b_perf_sql s ".$obQueryWhere->GetJoins()." ".($strQueryWhere? "WHERE ".$strQueryWhere: "")." ".($bGroup? "GROUP BY ".implode(", ", $arQueryGroup): "")." ".(count($arQueryOrder)? "ORDER BY ".implode(", ", $arQueryOrder): "")." ", $arNavStartParams["nTopCount"] ); $res = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__); } elseif(is_array($arNavStartParams)) { $strSql = " SELECT count('x') CNT FROM b_perf_sql s ".$obQueryWhere->GetJoins()." ".($strQueryWhere? "WHERE ".$strQueryWhere: "")." ".($bGroup? "GROUP BY ".implode(", ", $arQueryGroup): "")." "; $res_cnt = $DB->Query($strSql); $ar_cnt = $res_cnt->Fetch(); $strSql = " SELECT ".implode(", ", $arQuerySelect)." FROM b_perf_sql s ".$obQueryWhere->GetJoins()." ".($strQueryWhere? "WHERE ".$strQueryWhere: "")." ".($bGroup? "GROUP BY ".implode(", ", $arQueryGroup): "")." ".(count($arQueryOrder)? "ORDER BY ".implode(", ", $arQueryOrder): "")." "; $res = new CDBResult(); $res->NavQuery($strSql, $ar_cnt["CNT"], $arNavStartParams); } else { $strSql = " SELECT ".implode(", ", $arQuerySelect)." FROM b_perf_sql s ".$obQueryWhere->GetJoins()." ".($strQueryWhere? "WHERE ".$strQueryWhere: "")." ".($bGroup? "GROUP BY ".implode(", ", $arQueryGroup): "")." ".(count($arQueryOrder)? "ORDER BY ".implode(", ", $arQueryOrder): "")." "; $res = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__); } return $res; }
function __PrepareFilter($arFilter, &$bIncSites, $strSearchContentAlias="sc.") { $DB = CDatabase::GetModuleConnection('search'); $arSql = array(); $arNewFilter = array(); static $arFilterEvents = false; if(!is_array($arFilter)) $arFilter = array(); foreach($arFilter as $field=>$val) { $field = strtoupper($field); if( is_array($val) && count($val) == 1 && $field !== "URL" && $field !== "PARAMS" ) $val = $val[0]; switch($field) { case "=MODULE_ID": if($val !== false && $val !== "no") $arNewFilter[$field] = $val; break; case "MODULE_ID": if($val !== false && $val !== "no") $arNewFilter["=".$field] = $val; break; case "ITEM_ID": case "PARAM1": case "PARAM2": if($val !== false) $arNewFilter["=".$field] = $val; break; case "CHECK_DATES": if($val == "Y") { $time = ConvertTimeStamp(time()+CTimeZone::GetOffset(), "FULL"); $arNewFilter[] = array( "LOGIC" => "AND", array( "LOGIC" => "OR", "=DATE_FROM" => false, "<=DATE_FROM" => $time, ), array( "LOGIC" => "OR", "=DATE_TO" => false, ">=DATE_TO" => $time, ), ); } break; case "DATE_CHANGE": if(strlen($val) > 0) $arNewFilter[">=".$field] = $val; break; case "SITE_ID": if($val !== false) $arNewFilter["=".$field] = $val; break; default: if(!is_array($arFilterEvents)) { $arFilterEvents = array(); foreach(GetModuleEvents("search", "OnSearchPrepareFilter", true) as $arEvent) $arFilterEvents[] = $arEvent; } //Try to get someone to make the filter sql $sql = ""; foreach($arFilterEvents as $arEvent) { $sql = ExecuteModuleEventEx($arEvent, array($strSearchContentAlias, $field, $val)); if(strlen($sql)) { $arSql[] = "(".$sql.")"; break; } } if(!$sql) $arNewFilter[$field] = $val; } } $strSearchContentAlias = rtrim($strSearchContentAlias, "."); $obWhereHelp = new CSearchSQLHelper($strSearchContentAlias); $obQueryWhere = new CSQLWhere; $obQueryWhere->SetFields(array( "MODULE_ID" => array( "TABLE_ALIAS" => $strSearchContentAlias, "FIELD_NAME" => $strSearchContentAlias.".MODULE_ID", "MULTIPLE" => "N", "FIELD_TYPE" => "string", "JOIN" => false, ), "ITEM_ID" => array( "TABLE_ALIAS" => $strSearchContentAlias, "FIELD_NAME" => $strSearchContentAlias.".ITEM_ID", "MULTIPLE" => "N", "FIELD_TYPE" => "string", "JOIN" => false, ), "PARAM1" => array( "TABLE_ALIAS" => $strSearchContentAlias, "FIELD_NAME" => $strSearchContentAlias.".PARAM1", "MULTIPLE" => "N", "FIELD_TYPE" => "string", "JOIN" => false, ), "PARAM2" => array( "TABLE_ALIAS" => $strSearchContentAlias, "FIELD_NAME" => $strSearchContentAlias.".PARAM2", "MULTIPLE" => "N", "FIELD_TYPE" => "string", "JOIN" => false, ), "DATE_FROM" => array( "TABLE_ALIAS" => $strSearchContentAlias, "FIELD_NAME" => $strSearchContentAlias.".DATE_FROM", "MULTIPLE" => "N", "FIELD_TYPE" => "datetime", "JOIN" => false, ), "DATE_TO" => array( "TABLE_ALIAS" => $strSearchContentAlias, "FIELD_NAME" => $strSearchContentAlias.".DATE_TO", "MULTIPLE" => "N", "FIELD_TYPE" => "datetime", "JOIN" => false, ), "DATE_CHANGE" => array( "TABLE_ALIAS" => $strSearchContentAlias, "FIELD_NAME" => $strSearchContentAlias.".DATE_CHANGE", "MULTIPLE" => "N", "FIELD_TYPE" => "datetime", "JOIN" => false, ), "SITE_ID" => array( "TABLE_ALIAS" => "scsite", "FIELD_NAME" => "scsite.SITE_ID", "MULTIPLE" => "N", "FIELD_TYPE" => "string", "JOIN" => true, ), "SITE_URL" => array( "TABLE_ALIAS" => "scsite", "FIELD_NAME" => "scsite.URL", "MULTIPLE" => "N", "FIELD_TYPE" => "string", "JOIN" => true, ), "URL" => array( "TABLE_ALIAS" => $strSearchContentAlias, "FIELD_NAME" => $strSearchContentAlias.".URL", "MULTIPLE" => "N", "FIELD_TYPE" => "callback", "CALLBACK" => array($obWhereHelp, "_CallbackURL"), "JOIN" => true, ), "PARAMS" => array( "TABLE_ALIAS" => $strSearchContentAlias, "FIELD_NAME" => $strSearchContentAlias.".ID", "MULTIPLE" => "N", "FIELD_TYPE" => "callback", "CALLBACK" => array($obWhereHelp, "_CallbackPARAMS"), "JOIN" => false, ), )); $strWhere = $obQueryWhere->GetQuery($arNewFilter); if(count($arSql) > 0) { if($strWhere) $strWhere .= "\nAND (".implode(" AND ", $arSql).")"; else $strWhere = implode("\nAND ", $arSql); } $bIncSites = $bIncSites || strlen($obQueryWhere->GetJoins()) > 0; return $strWhere; }
public static function GetList($arSelect, $arFilter, $arOrder) { global $DB; if (!is_array($arSelect)) { $arSelect = array(); } if (count($arSelect) < 1) { $arSelect = array("ID", "RULE_TYPE", "ACTIVE", "ADMIN_SECTION", "SITE_ID", "SORT", "NAME", "ACTIVE_FROM", "ACTIVE_TO"); } if (!is_array($arOrder)) { $arOrder = array(); } $arQueryOrder = array(); foreach ($arOrder as $strColumn => $strDirection) { $strColumn = strtoupper($strColumn); $strDirection = strtoupper($strDirection) == "ASC" ? "ASC" : "DESC"; switch ($strColumn) { case "ID": case "RULE_TYPE": case "ACTIVE": case "ADMIN_SECTION": case "SITE_ID": case "SORT": case "NAME": case "ACTIVE_FROM": case "ACTIVE_TO": $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; break; } } $arQuerySelect = array(); foreach ($arSelect as $strColumn) { $strColumn = strtoupper($strColumn); switch ($strColumn) { case "ID": case "RULE_TYPE": case "ACTIVE": case "ADMIN_SECTION": case "SITE_ID": case "SORT": case "NAME": case "ACTIVE_FROM_TIMESTAMP": case "ACTIVE_TO_TIMESTAMP": $arQuerySelect[$strColumn] = "r." . $strColumn; break; case "ACTIVE_FROM": case "ACTIVE_TO": $arQuerySelect[$strColumn] = $DB->DateToCharFunction("r." . $strColumn, "FULL") . " AS " . $strColumn; break; } } if (count($arQuerySelect) < 1) { $arQuerySelect = array("ID" => "r.ID"); } $obQueryWhere = new CSQLWhere(); $arFields = array("ID" => array("TABLE_ALIAS" => "r", "FIELD_NAME" => "r.ID", "FIELD_TYPE" => "int", "JOIN" => false), "RULE_TYPE" => array("TABLE_ALIAS" => "r", "FIELD_NAME" => "r.RULE_TYPE", "FIELD_TYPE" => "string", "JOIN" => false), "ACTIVE" => array("TABLE_ALIAS" => "r", "FIELD_NAME" => "r.ACTIVE", "FIELD_TYPE" => "string", "JOIN" => false), "ADMIN_SECTION" => array("TABLE_ALIAS" => "r", "FIELD_NAME" => "r.ADMIN_SECTION", "FIELD_TYPE" => "string", "JOIN" => false), "SITE_ID" => array("TABLE_ALIAS" => "r", "FIELD_NAME" => "r.SITE_ID", "FIELD_TYPE" => "string", "JOIN" => false), "SORT" => array("TABLE_ALIAS" => "r", "FIELD_NAME" => "r.SORT", "FIELD_TYPE" => "int", "JOIN" => false), "NAME" => array("TABLE_ALIAS" => "r", "FIELD_NAME" => "r.NAME", "FIELD_TYPE" => "string", "JOIN" => false), "ACTIVE_FROM" => array("TABLE_ALIAS" => "r", "FIELD_NAME" => "r.ACTIVE_FROM", "FIELD_TYPE" => "datetime", "JOIN" => false), "ACTIVE_TO" => array("TABLE_ALIAS" => "r", "FIELD_NAME" => "r.ACTIVE_TO", "FIELD_TYPE" => "datetime", "JOIN" => false)); $obQueryWhere->SetFields($arFields); if (!is_array($arFilter)) { $arFilter = array(); } $strQueryWhere = $obQueryWhere->GetQuery($arFilter); $bDistinct = $obQueryWhere->bDistinctReqired; $strMaskJoin = ""; if (array_key_exists("PATH", $arFilter)) { $path = trim($arFilter["PATH"]); if ($path) { $bDistinct = true; $strMaskJoin = "\n\t\t\t\t\tINNER JOIN b_sec_iprule_incl_mask im on im.IPRULE_ID = r.ID\n\t\t\t\t\tLEFT JOIN b_sec_iprule_excl_mask em on em.IPRULE_ID = r.ID AND '" . $DB->ForSQL($path) . "' like em.LIKE_MASK\n\t\t\t\t"; $strMaskWhere = "('" . $DB->ForSQL($path) . "' like im.LIKE_MASK AND em.IPRULE_ID is null)"; if ($strQueryWhere) { $strQueryWhere = "(" . $strQueryWhere . ") AND " . $strMaskWhere; } else { $strQueryWhere = $strMaskWhere; } } } $strIPJoin = ""; if (array_key_exists("IP", $arFilter)) { $ip = self::ip2number($arFilter["IP"]); if ($ip > 0) { $bDistinct = true; $strIPJoin = "\n\t\t\t\t\tINNER JOIN b_sec_iprule_incl_ip ii on ii.IPRULE_ID = r.ID\n\t\t\t\t\tLEFT JOIN b_sec_iprule_excl_ip ei on ei.IPRULE_ID = r.ID AND " . $ip . " between ei.IP_START AND ei.IP_END\n\t\t\t\t"; $strIPWhere = "(" . $ip . " between ii.IP_START AND ii.IP_END AND ei.IPRULE_ID is null)"; if ($strQueryWhere) { $strQueryWhere = "(" . $strQueryWhere . ") AND " . $strIPWhere; } else { $strQueryWhere = $strIPWhere; } } } $strSql = "\n\t\t\tSELECT " . ($bDistinct ? "DISTINCT" : "") . "\n\t\t\t" . implode(", ", $arQuerySelect) . "\n\t\t\tFROM\n\t\t\t\tb_sec_iprule r\n\t\t\t\t" . $strMaskJoin . "\n\t\t\t\t" . $strIPJoin . "\n\t\t\t" . $obQueryWhere->GetJoins() . "\n\t\t"; if ($strQueryWhere) { $strSql .= "\n\t\t\t\tWHERE\n\t\t\t\t" . $strQueryWhere . "\n\t\t\t"; } if (count($arQueryOrder) > 0) { $strSql .= "\n\t\t\t\tORDER BY\n\t\t\t\t" . implode(", ", $arQueryOrder) . "\n\t\t\t"; } //echo "<pre>",htmlspecialcharsbx($strSql),"</pre><hr>"; return $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); }
function GetList($arOrder, $arFilter, $bGroup, $arNavStartParams, $arSelect) { global $DB; if (!is_array($arSelect)) { $arSelect = array(); } if (count($arSelect) < 1) { $arSelect = array("ID"); } if (!is_array($arOrder)) { $arOrder = array(); } if (count($arOrder) < 1) { $arOrder = array("HIT_ID" => "DESC", "NN" => "ASC"); } $arQueryOrder = array(); foreach ($arOrder as $strColumn => $strDirection) { $strColumn = strtoupper($strColumn); if (preg_match("/^(MIN|MAX|AVG|SUM)_(.*)\$/", $strColumn, $arMatch)) { $strGroupFunc = $arMatch[1]; $strColumn = $arMatch[2]; } else { $strGroupFunc = ""; } $strDirection = strtoupper($strDirection) == "ASC" ? "ASC" : "DESC"; switch ($strColumn) { case "ID": case "HIT_ID": case "NN": case "CACHE_TYPE": case "COMPONENT_NAME": if ($strGroupFunc == "") { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; } break; case "CACHE_SIZE": case "COMPONENT_TIME": case "QUERIES": case "QUERIES_TIME": if ($strGroupFunc == "") { if (!$bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; } } else { if ($bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strGroupFunc . "_" . $strColumn] = $strGroupFunc . "_" . $strColumn . " " . $strDirection; } } break; case "COUNT": if ($bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; } break; } } $arQueryGroup = array(); $arQuerySelect = array(); foreach ($arSelect as $strColumn) { $strColumn = strtoupper($strColumn); if (preg_match("/^(MIN|MAX|AVG|SUM)_(.*)\$/", $strColumn, $arMatch)) { $strGroupFunc = $arMatch[1]; $strColumn = $arMatch[2]; } else { $strGroupFunc = ""; } switch ($strColumn) { case "ID": case "HIT_ID": case "NN": case "CACHE_TYPE": case "COMPONENT_NAME": if ($strGroupFunc == "") { if ($bGroup) { $arQueryGroup[$strColumn] = "c." . $strColumn; } $arQuerySelect[$strColumn] = "c." . $strColumn; } break; case "CACHE_SIZE": case "COMPONENT_TIME": case "QUERIES": case "QUERIES_TIME": if ($strGroupFunc == "") { if (!$bGroup) { $arQuerySelect[$strColumn] = "c." . $strColumn; } } else { if ($bGroup) { $arQuerySelect[$strGroupFunc . "_" . $strColumn] = $strGroupFunc . "(c." . $strColumn . ") " . $strGroupFunc . "_" . $strColumn; } } break; case "COUNT": if ($strGroupFunc == "" && $bGroup) { $arQuerySelect[$strColumn] = "COUNT(c.ID) " . $strColumn; } break; } } $obQueryWhere = new CSQLWhere(); static $arWhereFields = array("HIT_ID" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.HIT_ID", "FIELD_TYPE" => "int", "JOIN" => false), "COMPONENT_NAME" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.COMPONENT_NAME", "FIELD_TYPE" => "string", "JOIN" => false), "ID" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.ID", "FIELD_TYPE" => "int", "JOIN" => false), "CACHE_TYPE" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.CACHE_TYPE", "FIELD_TYPE" => "string", "JOIN" => false), "CACHE_SIZE" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.CACHE_SIZE", "FIELD_TYPE" => "int", "JOIN" => false), "QUERIES" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.QUERIES", "FIELD_TYPE" => "int", "JOIN" => false), "HIT_SCRIPT_NAME" => array("TABLE_ALIAS" => "h", "FIELD_NAME" => "h.SCRIPT_NAME", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_perf_hit h on h.ID = c.HIT_ID"), "HIT_IS_ADMIN" => array("TABLE_ALIAS" => "h", "FIELD_NAME" => "h.IS_ADMIN", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_perf_hit h on h.ID = c.HIT_ID"), "HIT_CACHE_TYPE" => array("TABLE_ALIAS" => "h", "FIELD_NAME" => "h.CACHE_TYPE", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_perf_hit h on h.ID = c.HIT_ID"), "HIT_MENU_RECALC" => array("TABLE_ALIAS" => "h", "FIELD_NAME" => "h.MENU_RECALC", "FIELD_TYPE" => "int", "JOIN" => "INNER JOIN b_perf_hit h on h.ID = c.HIT_ID")); $obQueryWhere->SetFields($arWhereFields); if (count($arQuerySelect) < 1) { $arQuerySelect = array("ID" => "c.ID"); } $strSql = "\n\t\t\tSELECT\n\t\t\t" . implode(", ", $arQuerySelect) . "\n\t\t\tFROM\n\t\t\t\tb_perf_component c\n\t\t"; if (!is_array($arFilter)) { $arFilter = array(); } if ($strQueryWhere = $obQueryWhere->GetQuery($arFilter)) { $strSql .= $obQueryWhere->GetJoins() . "\n\t\t\t\tWHERE\n\t\t\t\t" . $strQueryWhere . "\n\t\t\t"; } if ($bGroup && count($arQueryGroup) > 0) { $strSql .= "\n\t\t\t\tGROUP BY\n\t\t\t\t" . implode(", ", $arQueryGroup) . "\n\t\t\t"; if (array_key_exists(">COUNT", $arFilter)) { $strSql .= "\n\t\t\t\t\tHAVING\n\t\t\t\t\tCOUNT(*) > " . intval($arFilter["COUNT"]) . "\n\t\t\t\t"; } } if (count($arQueryOrder) > 0) { $strSql .= "\n\t\t\t\tORDER BY\n\t\t\t\t" . implode(", ", $arQueryOrder) . "\n\t\t\t"; } //echo "<pre>",htmlspecialcharsbx($strSql),"</pre><hr>"; return $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); }
public static function GetList($arOrder=false, $arFilter=false, $arSelect=false) { global $DB; if(!is_array($arSelect)) $arSelect = array(); if(count($arSelect) < 1) $arSelect = array( "ID", "NAME", "DESCRIPTION", "HOST", "PORT", "STATUS_URL", ); if(!is_array($arOrder)) $arOrder = array(); $arQueryOrder = array(); foreach($arOrder as $strColumn => $strDirection) { $strColumn = strtoupper($strColumn); $strDirection = strtoupper($strDirection)=="ASC"? "ASC": "DESC"; switch($strColumn) { case "ID": case "NAME": $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn." ".$strDirection; break; } } $arQuerySelect = array(); foreach($arSelect as $strColumn) { $strColumn = strtoupper($strColumn); switch($strColumn) { case "ID": case "NAME": case "DESCRIPTION": case "HOST": case "PORT": case "STATUS_URL": $arQuerySelect[$strColumn] = "w.".$strColumn; break; } } if(count($arQuerySelect) < 1) $arQuerySelect = array("ID"=>"w.ID"); $obQueryWhere = new CSQLWhere; $arFields = array( "ID" => array( "TABLE_ALIAS" => "w", "FIELD_NAME" => "w.ID", "FIELD_TYPE" => "int", "JOIN" => false, ), "GROUP_ID" => array( "TABLE_ALIAS" => "w", "FIELD_NAME" => "w.GROUP_ID", "FIELD_TYPE" => "int", "JOIN" => false, ), ); $obQueryWhere->SetFields($arFields); if(!is_array($arFilter)) $arFilter = array(); $strQueryWhere = $obQueryWhere->GetQuery($arFilter); $bDistinct = $obQueryWhere->bDistinctReqired; $strSql = " SELECT ".($bDistinct? "DISTINCT": "")." ".implode(", ", $arQuerySelect)." FROM b_cluster_webnode w ".$obQueryWhere->GetJoins()." "; if($strQueryWhere) { $strSql .= " WHERE ".$strQueryWhere." "; } if(count($arQueryOrder) > 0) { $strSql .= " ORDER BY ".implode(", ", $arQueryOrder)." "; } return $DB->Query($strSql, false, '', array('fixed_connection'=>true)); }
public static function GetList($arOrder = false, $arFilter = false) { global $DB; if (!is_array($arOrder)) { $arOrder = array(); } $arQueryOrder = array(); foreach ($arOrder as $strColumn => $strDirection) { $strColumn = strtoupper($strColumn); $strDirection = strtoupper($strDirection) == "ASC" ? "ASC" : "DESC"; switch ($strColumn) { case "ID": case "NAME": $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; break; } } $obQueryWhere = new CSQLWhere(); $arFields = array("ID" => array("TABLE_ALIAS" => "cc", "FIELD_NAME" => "cc.ID", "FIELD_TYPE" => "int", "JOIN" => false), "CONTROLLER_GROUP_ID" => array("TABLE_ALIAS" => "ccg", "FIELD_NAME" => "ccg.CONTROLLER_GROUP_ID", "FIELD_TYPE" => "int", "JOIN" => "INNER JOIN b_controller_counter_group ccg ON ccg.CONTROLLER_COUNTER_ID = cc.ID", "LEFT_JOIN" => "LEFT JOIN b_controller_counter_group ccg ON ccg.CONTROLLER_COUNTER_ID = cc.ID")); $obQueryWhere->SetFields($arFields); if (!is_array($arFilter)) { $arFilter = array(); } $strQueryWhere = $obQueryWhere->GetQuery($arFilter); $bDistinct = $obQueryWhere->bDistinctReqired; $strSql = "\n\t\t\tSELECT " . ($bDistinct ? "DISTINCT" : "") . "\n\t\t\t\tcc.*\n\t\t\t\t," . $DB->DateToCharFunction("cc.TIMESTAMP_X") . " TIMESTAMP_X\n\t\t\tFROM\n\t\t\t\tb_controller_counter cc\n\t\t\t" . $obQueryWhere->GetJoins() . "\n\t\t"; if ($strQueryWhere) { $strSql .= "\n\t\t\t\tWHERE\n\t\t\t\t" . $strQueryWhere . "\n\t\t\t"; } if (count($arQueryOrder) > 0) { $strSql .= "\n\t\t\t\tORDER BY\n\t\t\t\t" . implode(", ", $arQueryOrder) . "\n\t\t\t"; } return $DB->Query($strSql); }
function GetList($arOrder = array(), $arFilter = array(), $arSelect = array(), $arOptions = array()) { global $DB, $USER_FIELD_MANAGER; $bEmptySelect = !is_array($arSelect) || empty($arSelect); if (is_array($arOrder) && is_array($arSelect)) { foreach ($arOrder as $k => $v) { $arSelect[] = $k; } } $obUserFieldsSql = new CUserTypeSQL(); $obUserFieldsSql->SetEntity("CONTROLLER_MEMBER", "M.ID"); $obUserFieldsSql->SetSelect($arSelect); $obUserFieldsSql->SetFilter($arFilter); $obUserFieldsSql->SetOrder($arOrder); static $arFields = array("ID" => array("FIELD_NAME" => "M.ID", "FIELD_TYPE" => "int"), "MEMBER_ID" => array("FIELD_NAME" => "M.MEMBER_ID", "FIELD_TYPE" => "string"), "SECRET_ID" => array("FIELD_NAME" => "M.SECRET_ID", "FIELD_TYPE" => "string"), "NAME" => array("FIELD_NAME" => "M.NAME", "FIELD_TYPE" => "string"), "EMAIL" => array("FIELD_NAME" => "M.EMAIL", "FIELD_TYPE" => "string"), "CONTACT_PERSON" => array("FIELD_NAME" => "M.CONTACT_PERSON", "FIELD_TYPE" => "string"), "URL" => array("FIELD_NAME" => "M.URL", "FIELD_TYPE" => "string"), "CONTROLLER_GROUP_ID" => array("FIELD_NAME" => "M.CONTROLLER_GROUP_ID", "FIELD_TYPE" => "int"), "CONTROLLER_GROUP_NAME" => array("FIELD_NAME" => "G.NAME", "FIELD_TYPE" => "string", "TABLE_ALIAS" => "G", "JOIN" => "INNER JOIN b_controller_group G ON G.ID = M.CONTROLLER_GROUP_ID", "LEFT_JOIN" => "LEFT JOIN b_controller_group G ON G.ID = M.CONTROLLER_GROUP_ID"), "IN_GROUP_FROM" => array("FIELD_NAME" => "M.IN_GROUP_FROM", "FIELD_TYPE" => "datetime", "FORMAT" => "FULL"), "SHARED_KERNEL" => array("FIELD_NAME" => "M.SHARED_KERNEL", "FIELD_TYPE" => "string"), "ACTIVE" => array("FIELD_NAME" => "M.ACTIVE", "FIELD_TYPE" => "string"), "SITE_ACTIVE" => array("FIELD_NAME" => "M.SITE_ACTIVE", "FIELD_TYPE" => "string"), "DISCONNECTED" => array("FIELD_NAME" => "M.DISCONNECTED", "FIELD_TYPE" => "string"), "DATE_ACTIVE_FROM" => array("FIELD_NAME" => "M.DATE_ACTIVE_FROM", "FIELD_TYPE" => "datetime", "FORMAT" => "SHORT"), "DATE_ACTIVE_TO" => array("FIELD_NAME" => "M.DATE_ACTIVE_TO", "FIELD_TYPE" => "datetime", "FORMAT" => "SHORT"), "TIMESTAMP_X" => array("FIELD_NAME" => "M.TIMESTAMP_X", "FIELD_TYPE" => "datetime", "FORMAT" => "FULL"), "MODIFIED_BY" => array("FIELD_NAME" => "M.MODIFIED_BY", "FIELD_TYPE" => "int"), "MODIFIED_BY_USER" => array("FIELD_TYPE" => "string"), "DATE_CREATE" => array("FIELD_NAME" => "M.DATE_CREATE", "FIELD_TYPE" => "datetime", "FORMAT" => "FULL"), "CREATED_BY" => array("FIELD_NAME" => "M.CREATED_BY", "FIELD_TYPE" => "int"), "CREATED_BY_USER" => array("FIELD_TYPE" => "string"), "COUNTER_FREE_SPACE" => array("FIELD_NAME" => "M.COUNTER_FREE_SPACE", "FIELD_TYPE" => "int"), "COUNTER_SITES" => array("FIELD_NAME" => "M.COUNTER_SITES", "FIELD_TYPE" => "int"), "COUNTER_USERS" => array("FIELD_NAME" => "M.COUNTER_USERS", "FIELD_TYPE" => "int"), "COUNTER_LAST_AUTH" => array("FIELD_NAME" => "M.COUNTER_LAST_AUTH", "FIELD_TYPE" => "datetime", "FORMAT" => "FULL"), "COUNTERS_UPDATED" => array("FIELD_NAME" => "M.COUNTERS_UPDATED", "FIELD_TYPE" => "datetime", "FORMAT" => "FULL"), "NOTES" => array("FIELD_NAME" => "M.NOTES")); $arFields["MODIFIED_BY_USER"]["FIELD_NAME"] = $DB->Concat("'('", "UM.LOGIN", "') '", "UM.NAME", "' '", "UM.LAST_NAME"); $arFields["CREATED_BY_USER"]["FIELD_NAME"] = $DB->Concat("'('", "UC.LOGIN", "') '", "UC.NAME", "' '", "UC.LAST_NAME"); $rsCounters = CControllerCounter::GetList(); while ($arCounter = $rsCounters->Fetch()) { $arFields["COUNTER_" . $arCounter["ID"]] = array("FIELD_NAME" => "CCV_" . $arCounter["ID"] . "." . CControllerCounter::GetTypeColumn($arCounter["COUNTER_TYPE"]), "FIELD_TYPE" => CControllerCounter::GetTypeUserType($arCounter["COUNTER_TYPE"]), "TABLE_ALIAS" => "CCV_" . $arCounter["ID"], "JOIN" => "INNER JOIN b_controller_counter_value CCV_" . $arCounter["ID"] . " ON CCV_" . $arCounter["ID"] . ".CONTROLLER_COUNTER_ID = " . $arCounter["ID"] . " AND CCV_" . $arCounter["ID"] . ".CONTROLLER_MEMBER_ID = M.ID", "LEFT_JOIN" => "LEFT JOIN b_controller_counter_value CCV_" . $arCounter["ID"] . " ON CCV_" . $arCounter["ID"] . ".CONTROLLER_COUNTER_ID = " . $arCounter["ID"] . " AND CCV_" . $arCounter["ID"] . ".CONTROLLER_MEMBER_ID = M.ID"); } $obWhere = new CSQLWhere(); $obWhere->SetFields($arFields); $arDateFields = array(); foreach ($arFields as $code => $arField) { if ($arField["FIELD_TYPE"] == "datetime") { $arDateFields[] = $code; } } $date_field = "/(" . implode("|", $arDateFields) . ")\$/"; $arFilterNew = array(); if (is_array($arFilter)) { foreach ($arFilter as $k => $value) { if (is_array($value)) { if (!empty($value)) { $arFilterNew[$k] = $value; } } elseif ($value === false) { $arFilterNew[$k] = $value; } elseif (strlen($value) > 0) { if (array_key_exists("date_format", $arOptions) && preg_match($date_field, $k)) { $arFilterNew[$k] = ConvertTimeStamp(MakeTimeStamp($value, $arOptions["date_format"]), "FULL"); } else { $arFilterNew[$k] = $value; } } } } $strWhere = "1 = 1"; $r = $obWhere->GetQuery($arFilterNew); if (strlen($r) > 0) { $strWhere .= " AND (" . $r . ") "; } $r = $obUserFieldsSql->GetFilter(); if (strlen($r) > 0) { $strWhere .= " AND (" . $r . ") "; } if (is_array($arOrder)) { foreach ($arOrder as $key => $value) { $key = strtoupper($key); if (array_key_exists($key, $arFields) && isset($arFields[$key]["LEFT_JOIN"])) { $obWhere->c_joins[$key]++; } } } if ($bEmptySelect) { $arSelectAdd = array("ID", "MEMBER_ID", "SECRET_ID", "NAME", "URL", "EMAIL", "CONTACT_PERSON", "CONTROLLER_GROUP_ID", "DISCONNECTED", "SHARED_KERNEL", "ACTIVE", "DATE_ACTIVE_FROM", "DATE_ACTIVE_TO", "SITE_ACTIVE", "TIMESTAMP_X", "MODIFIED_BY", "DATE_CREATE", "CREATED_BY", "IN_GROUP_FROM", "NOTES", "COUNTER_FREE_SPACE", "COUNTER_SITES", "COUNTER_USERS", "COUNTER_LAST_AUTH", "COUNTERS_UPDATED", "MODIFIED_BY_USER", "CREATED_BY_USER"); if (is_array($arSelect)) { $arSelect = array_merge($arSelect, $arSelectAdd); } else { $arSelect = $arSelectAdd; } } $duplicates = array("ID" => 1); $strSelect = "M.ID AS ID\n"; foreach ($arSelect as $key) { $key = strtoupper($key); if (array_key_exists($key, $arFields) && !array_key_exists($key, $duplicates)) { $duplicates[$key]++; if (isset($arFields[$key]["LEFT_JOIN"])) { $obWhere->c_joins[$key]++; } if ($arFields[$key]["FIELD_TYPE"] == "datetime") { if (array_key_exists("date_format", $arOptions)) { $strSelect .= "," . $DB->DateFormatToDB($arOptions["date_format"], $arFields[$key]["FIELD_NAME"]) . " AS " . $key . "\n"; } else { $strSelect .= "," . $arFields[$key]["FIELD_NAME"] . " AS " . $key . "_TMP," . $DB->DateToCharFunction($arFields[$key]["FIELD_NAME"], $arFields[$key]["FORMAT"]) . " AS " . $key . "\n"; } } else { $strSelect .= "," . $arFields[$key]["FIELD_NAME"] . " AS " . $key . "\n"; } } } $bUseSubQuery = false; if ($DB->type == "ORACLE" && $obUserFieldsSql->GetDistinct()) { $bUseSubQuery = true; } if ($bUseSubQuery) { $ob = new CUserTypeSQL(); $ob->SetEntity("CONTROLLER_MEMBER", "M.ID"); $ob->SetSelect($arSelect); $ob->SetOrder($arOrder); $strSql = "\n\t\t\t\tSELECT " . $strSelect . $ob->GetSelect() . "\n\t\t\t\tFROM b_controller_member M\n\t\t\t\t\tLEFT JOIN b_user UC ON UC.ID = M.CREATED_BY\n\t\t\t\t\tLEFT JOIN b_user UM ON UM.ID = M.MODIFIED_BY\n\t\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t\t" . $ob->GetJoin("M.ID") . "\n\t\t\t\tWHERE M.ID IN (\n\t\t\t\t\tSELECT M.ID\n\t\t\t\t\tFROM b_controller_member M\n\t\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t\t" . $obUserFieldsSql->GetJoin("M.ID") . "\n\t\t\t\t\tWHERE " . $strWhere . "\n\t\t\t\t)\n\t\t\t\t" . CControllerAgent::_OrderBy($arOrder, $arFields, $ob) . "\n\t\t\t"; } else { $strSql = "\n\t\t\t\tSELECT " . ($obUserFieldsSql->GetDistinct() ? "DISTINCT" : "") . " " . $strSelect . $obUserFieldsSql->GetSelect() . "\n\t\t\t\tFROM b_controller_member M\n\t\t\t\t\tLEFT JOIN b_user UC ON UC.ID = M.CREATED_BY\n\t\t\t\t\tLEFT JOIN b_user UM ON UM.ID = M.MODIFIED_BY\n\t\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t\t" . $obUserFieldsSql->GetJoin("M.ID") . "\n\t\t\t\tWHERE " . $strWhere . "\n\t\t\t\t" . CControllerAgent::_OrderBy($arOrder, $arFields, $obUserFieldsSql) . "\n\t\t\t"; } $dbr = $DB->Query($strSql); $dbr->is_filtered = $strWhere != "1 = 1"; $dbr->SetUserFields($USER_FIELD_MANAGER->GetUserFields("CONTROLLER_MEMBER")); return $dbr; }
public static function GetList($arOrder, $arFilter, $bGroup, $arNavStartParams, $arSelect) { global $DB; if (!is_array($arSelect)) { $arSelect = array(); } if (count($arSelect) < 1) { $arSelect = array("ID"); } if (!is_array($arOrder)) { $arOrder = array(); } if (count($arOrder) < 1) { $arOrder = array("ID" => "DESC"); } $arQueryOrder = array(); foreach ($arOrder as $strColumn => $strDirection) { $strColumn = strtoupper($strColumn); if (preg_match("/^(MIN|MAX|AVG|SUM)_(.*)\$/", $strColumn, $arMatch)) { $strGroupFunc = $arMatch[1]; $strColumn = $arMatch[2]; } else { $strGroupFunc = ""; } $strDirection = strtoupper($strDirection) == "ASC" ? "ASC" : "DESC"; switch ($strColumn) { case "ID": case "IS_ADMIN": case "REQUEST_METHOD": case "SERVER_NAME": case "SERVER_PORT": case "SCRIPT_NAME": case "REQUEST_URI": if ($strGroupFunc == "") { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; } break; case "INCLUDED_FILES": case "MEMORY_PEAK_USAGE": case "CACHE_SIZE": case "CACHE_COUNT": case "CACHE_COUNT_R": case "CACHE_COUNT_W": case "CACHE_COUNT_C": case "QUERIES": case "QUERIES_TIME": case "PAGE_TIME": case "PROLOG_TIME": case "PROLOG_BEFORE_TIME": case "AGENTS_TIME": case "PROLOG_AFTER_TIME": case "WORK_AREA_TIME": case "EPILOG_TIME": case "EPILOG_BEFORE_TIME": case "EVENTS_TIME": case "EPILOG_AFTER_TIME": case "COMPONENTS": case "COMPONENTS_TIME": if ($strGroupFunc == "") { if (!$bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; } } else { if ($bGroup) { $arSelect[] = $strGroupFunc . "_" . $strColumn; $arQueryOrder[$strGroupFunc . "_" . $strColumn] = $strGroupFunc . "_" . $strColumn . " " . $strDirection; } } break; case "DATE_HIT": if ($strGroupFunc == "" && !$bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = "TMP_DH " . $strDirection; } break; case "COUNT": if ($strGroupFunc == "" && $bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; } break; } } $arQueryGroup = array(); $arQuerySelect = array(); foreach ($arSelect as $strColumn) { $strColumn = strtoupper($strColumn); if (preg_match("/^(MIN|MAX|AVG|SUM)_(.*)\$/", $strColumn, $arMatch)) { $strGroupFunc = $arMatch[1]; $strColumn = $arMatch[2]; } else { $strGroupFunc = ""; } switch ($strColumn) { case "ID": case "IS_ADMIN": case "REQUEST_METHOD": case "SERVER_NAME": case "SERVER_PORT": case "SCRIPT_NAME": case "REQUEST_URI": case "SQL_LOG": if ($strGroupFunc == "") { if ($bGroup) { $arQueryGroup[$strColumn] = "h." . $strColumn; } $arQuerySelect[$strColumn] = "h." . $strColumn; } break; case "INCLUDED_FILES": case "MEMORY_PEAK_USAGE": case "CACHE_SIZE": case "CACHE_COUNT_R": case "CACHE_COUNT_W": case "CACHE_COUNT_C": case "QUERIES": case "QUERIES_TIME": case "PAGE_TIME": case "PROLOG_TIME": case "PROLOG_BEFORE_TIME": case "AGENTS_TIME": case "PROLOG_AFTER_TIME": case "WORK_AREA_TIME": case "EPILOG_TIME": case "EPILOG_BEFORE_TIME": case "EVENTS_TIME": case "EPILOG_AFTER_TIME": case "COMPONENTS": case "COMPONENTS_TIME": if ($strGroupFunc == "") { if (!$bGroup) { $arQuerySelect[$strColumn] = "h." . $strColumn; } } else { if ($bGroup) { $arQuerySelect[$strGroupFunc . "_" . $strColumn] = $strGroupFunc . "(h." . $strColumn . ") " . $strGroupFunc . "_" . $strColumn; } } break; case "CACHE_COUNT": if ($strGroupFunc == "") { if (!$bGroup) { $arQuerySelect[$strColumn] = "(h.CACHE_COUNT_R + h.CACHE_COUNT_W + h.CACHE_COUNT_C) CACHE_COUNT"; } } else { if ($bGroup) { $arQuerySelect[$strGroupFunc . "_" . $strColumn] = $strGroupFunc . "(h.CACHE_COUNT_R + h.CACHE_COUNT_W + h.CACHE_COUNT_C) " . $strGroupFunc . "_" . $strColumn; } } break; case "DATE_HIT": if ($strGroupFunc == "" && !$bGroup) { $arQuerySelect["TMP_DH"] = "h." . $strColumn . " TMP_DH"; $arQuerySelect[$strColumn] = $DB->DateToCharFunction("h." . $strColumn, "SHORT") . " " . $strColumn; $arQuerySelect["FULL_" . $strColumn] = $DB->DateToCharFunction("h." . $strColumn, "FULL") . " FULL_" . $strColumn; } break; case "COUNT": if ($strGroupFunc == "" && $bGroup) { $arQuerySelect[$strColumn] = "COUNT(h.ID) " . $strColumn; } break; } } static $arWhereFields = array("SCRIPT_NAME" => array("TABLE_ALIAS" => "h", "FIELD_NAME" => "h.SCRIPT_NAME", "FIELD_TYPE" => "string", "JOIN" => false), "IS_ADMIN" => array("TABLE_ALIAS" => "h", "FIELD_NAME" => "h.IS_ADMIN", "FIELD_TYPE" => "string", "JOIN" => false), "REQUEST_METHOD" => array("TABLE_ALIAS" => "h", "FIELD_NAME" => "h.REQUEST_METHOD", "FIELD_TYPE" => "string", "JOIN" => false), "ID" => array("TABLE_ALIAS" => "h", "FIELD_NAME" => "ID", "FIELD_TYPE" => "int", "JOIN" => false), "CACHE_TYPE" => array("TABLE_ALIAS" => "h", "FIELD_NAME" => "h.CACHE_TYPE", "FIELD_TYPE" => "string", "JOIN" => false), "MENU_RECALC" => array("TABLE_ALIAS" => "h", "FIELD_NAME" => "h.MENU_RECALC", "FIELD_TYPE" => "int", "JOIN" => false)); $obQueryWhere = new CSQLWhere(); $obQueryWhere->SetFields($arWhereFields); if (count($arQuerySelect) < 1) { $arQuerySelect = array("ID" => "h.ID"); } $strQueryWhere = $obQueryWhere->GetQuery($arFilter); $strHaving = ""; if ($bGroup && count($arQueryGroup) > 0) { $obQueryHaving = new CSQLWhere(); $obQueryHaving->SetFields(array("COUNT" => array("TABLE_ALIAS" => "", "FIELD_NAME" => "COUNT(h.ID)", "FIELD_TYPE" => "int", "JOIN" => false))); $strHaving = $obQueryHaving->GetQuery($arFilter); if ($strHaving) { $strHaving = "HAVING " . $strHaving . " "; } } if (is_array($arNavStartParams) && $arNavStartParams["nTopCount"] > 0) { $strSql = $DB->TopSQL("\n\t\t\t\tSELECT " . implode(", ", $arQuerySelect) . "\n\t\t\t\tFROM b_perf_hit h\n\t\t\t\t" . $obQueryWhere->GetJoins() . "\n\t\t\t\t" . ($strQueryWhere ? "WHERE " . $strQueryWhere : "") . "\n\t\t\t\t" . ($bGroup && count($arQueryGroup) > 0 ? "GROUP BY " . implode(", ", $arQueryGroup) : "") . "\n\t\t\t\t" . $strHaving . "\n\t\t\t\t" . (count($arQueryOrder) ? "ORDER BY " . implode(", ", $arQueryOrder) : "") . "\n\t\t\t", $arNavStartParams["nTopCount"]); $res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } elseif (is_array($arNavStartParams)) { $strSql = "\n\t\t\t\tSELECT count('x') CNT\n\t\t\t\tFROM b_perf_hit h\n\t\t\t\t" . $obQueryWhere->GetJoins() . "\n\t\t\t\t" . ($strQueryWhere ? "WHERE " . $strQueryWhere : "") . "\n\t\t\t\t" . ($bGroup && count($arQueryGroup) > 0 ? "GROUP BY " . implode(", ", $arQueryGroup) : "") . "\n\t\t\t\t" . $strHaving . "\n\t\t\t"; $res_cnt = $DB->Query($strSql); $ar_cnt = $res_cnt->Fetch(); $strSql = "\n\t\t\t\tSELECT " . implode(", ", $arQuerySelect) . "\n\t\t\t\tFROM b_perf_hit h\n\t\t\t\t" . $obQueryWhere->GetJoins() . "\n\t\t\t\t" . ($strQueryWhere ? "WHERE " . $strQueryWhere : "") . "\n\t\t\t\t" . ($bGroup && count($arQueryGroup) > 0 ? "GROUP BY " . implode(", ", $arQueryGroup) : "") . "\n\t\t\t\t" . $strHaving . "\n\t\t\t\t" . (count($arQueryOrder) ? "ORDER BY " . implode(", ", $arQueryOrder) : "") . "\n\t\t\t"; $res = new CDBResult(); $res->NavQuery($strSql, $ar_cnt["CNT"], $arNavStartParams); } else { $strSql = "\n\t\t\t\tSELECT " . implode(", ", $arQuerySelect) . "\n\t\t\t\tFROM b_perf_hit h\n\t\t\t\t" . $obQueryWhere->GetJoins() . "\n\t\t\t\t" . ($strQueryWhere ? "WHERE " . $strQueryWhere : "") . "\n\t\t\t\t" . ($bGroup && count($arQueryGroup) > 0 ? "GROUP BY " . implode(", ", $arQueryGroup) : "") . "\n\t\t\t\t" . $strHaving . "\n\t\t\t\t" . (count($arQueryOrder) ? "ORDER BY " . implode(", ", $arQueryOrder) : "") . "\n\t\t\t"; $res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $res; }
public static function GetList($arOrder = array(), $arFilter = array(), $arSelect = array()) { global $DB, $USER_FIELD_MANAGER; $obUserFieldsSql = new CUserTypeSQL(); $obUserFieldsSql->SetEntity("CONTROLLER_GROUP", "G.ID"); $obUserFieldsSql->SetSelect($arSelect); $obUserFieldsSql->SetFilter($arFilter); $obUserFieldsSql->SetOrder($arOrder); static $arFields = array("ID" => array("FIELD_NAME" => "G.ID", "FIELD_TYPE" => "int"), "NAME" => array("FIELD_NAME" => "G.NAME", "FIELD_TYPE" => "string"), "TIMESTAMP_X" => array("FIELD_NAME" => "G.TIMESTAMP_X", "FIELD_TYPE" => "datetime"), "MODIFIED_BY" => array("FIELD_NAME" => "G.MODIFIED_BY", "FIELD_TYPE" => "int"), "UPDATE_PERIOD" => array("FIELD_NAME" => "G.UPDATE_PERIOD", "FIELD_TYPE" => "int"), "MODIFIED_BY_USER" => array("FIELD_TYPE" => "string"), "DATE_CREATE" => array("FIELD_NAME" => "G.DATE_CREATE", "FIELD_TYPE" => "datetime"), "CREATED_BY" => array("FIELD_NAME" => "G.CREATED_BY", "FIELD_TYPE" => "int"), "CREATED_BY_USER" => array("FIELD_TYPE" => "string"), "TRIAL_PERIOD" => array("FIELD_NAME" => "G.TRIAL_PERIOD", "FIELD_TYPE" => "int"), "COUNTER_UPDATE_PERIOD" => array("FIELD_NAME" => "G.COUNTER_UPDATE_PERIOD", "FIELD_TYPE" => "int"), "CHECK_COUNTER_FREE_SPACE" => array("FIELD_NAME" => "G.CHECK_COUNTER_FREE_SPACE", "FIELD_TYPE" => "string"), "CHECK_COUNTER_SITES" => array("FIELD_NAME" => "G.CHECK_COUNTER_SITES", "FIELD_TYPE" => "string"), "CHECK_COUNTER_USERS" => array("FIELD_NAME" => "G.CHECK_COUNTER_USERS", "FIELD_TYPE" => "string"), "CHECK_COUNTER_LAST_AUTH" => array("FIELD_NAME" => "G.CHECK_COUNTER_LAST_AUTH", "FIELD_TYPE" => "string")); $arFields["MODIFIED_BY_USER"]["FIELD_NAME"] = $DB->Concat("UM.LOGIN", "UM.NAME", "UM.LAST_NAME"); $arFields["CREATED_BY_USER"]["FIELD_NAME"] = $DB->Concat("UC.LOGIN", "UC.NAME", "UC.LAST_NAME"); $obWhere = new CSQLWhere(); $obWhere->SetFields($arFields); $arFilterNew = array(); foreach ($arFilter as $k => $value) { if (strlen($value) > 0 || $value === false) { $arFilterNew[$k] = $value; } } $strWhere = "1 = 1"; $r = $obWhere->GetQuery($arFilterNew); if (strlen($r) > 0) { $strWhere .= " AND (" . $r . ") "; } $r = $obUserFieldsSql->GetFilter(); if (strlen($r) > 0) { $strWhere .= " AND (" . $r . ") "; } $strSql = "\n\t\t\tSELECT " . ($obUserFieldsSql->GetDistinct() ? "DISTINCT" : "") . " G.*\n\t\t\t\t,UC.LOGIN as CREATED_BY_LOGIN\n\t\t\t\t,UC.NAME as CREATED_BY_NAME\n\t\t\t\t,UC.LAST_NAME as CREATED_BY_LAST_NAME\n\t\t\t\t,UM.LOGIN as MODIFIED_BY_LOGIN\n\t\t\t\t,UM.NAME as MODIFIED_BY_NAME\n\t\t\t\t,UM.LAST_NAME as MODIFIED_BY_LAST_NAME\n\t\t\t\t," . $DB->DateToCharFunction("G.TIMESTAMP_X") . " as TIMESTAMP_X\n\t\t\t\t," . $DB->DateToCharFunction("G.DATE_CREATE") . " as DATE_CREATE\n\t\t\t\t" . $obUserFieldsSql->GetSelect() . "\n\t\t\tFROM b_controller_group G\n\t\t\t\tLEFT JOIN b_user UC ON UC.ID=G.CREATED_BY\n\t\t\t\tLEFT JOIN b_user UM ON UM.ID=G.MODIFIED_BY\n\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t" . $obUserFieldsSql->GetJoin("G.ID") . "\n\t\t\tWHERE " . $strWhere . "\n\t\t\t" . CControllerAgent::_OrderBy($arOrder, $arFields, $obUserFieldsSql) . "\n\t\t"; $dbr = $DB->Query($strSql); $dbr->is_filtered = strlen($strWhere) > 0; return $dbr; }
public static function GetList($arOrder, $arFilter, $bGroup, $arNavStartParams, $arSelect) { global $DB; if (!is_array($arSelect)) { $arSelect = array(); } if (count($arSelect) < 1) { $arSelect = array("ID"); } if (!is_array($arOrder)) { $arOrder = array(); } if (count($arOrder) < 1) { $arOrder = array("HIT_ID" => "DESC", "NN" => "ASC"); } $arQueryOrder = array(); foreach ($arOrder as $strColumn => $strDirection) { $strColumn = strtoupper($strColumn); if (preg_match("/^(MIN|MAX|AVG|SUM)_(.*)\$/", $strColumn, $arMatch)) { $strGroupFunc = $arMatch[1]; $strColumn = $arMatch[2]; } else { $strGroupFunc = ""; } $strDirection = strtoupper($strDirection) == "ASC" ? "ASC" : "DESC"; switch ($strColumn) { case "ID": case "HIT_ID": case "NN": case "CACHE_TYPE": case "COMPONENT_NAME": if ($strGroupFunc == "") { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; } break; case "CACHE_SIZE": case "CACHE_COUNT_R": case "CACHE_COUNT_W": case "CACHE_COUNT_C": case "COMPONENT_TIME": case "QUERIES": case "QUERIES_TIME": if ($strGroupFunc == "") { if (!$bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; } } else { if ($bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strGroupFunc . "_" . $strColumn] = $strGroupFunc . "_" . $strColumn . " " . $strDirection; } } break; case "COUNT": if ($bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; } break; } } $arQueryGroup = array(); $arQuerySelect = array(); foreach ($arSelect as $strColumn) { $strColumn = strtoupper($strColumn); if (preg_match("/^(MIN|MAX|AVG|SUM)_(.*)\$/", $strColumn, $arMatch)) { $strGroupFunc = $arMatch[1]; $strColumn = $arMatch[2]; } else { $strGroupFunc = ""; } switch ($strColumn) { case "ID": case "HIT_ID": case "NN": case "CACHE_TYPE": case "COMPONENT_NAME": if ($strGroupFunc == "") { if ($bGroup) { $arQueryGroup[$strColumn] = "c." . $strColumn; } $arQuerySelect[$strColumn] = "c." . $strColumn; } break; case "CACHE_SIZE": case "CACHE_COUNT_R": case "CACHE_COUNT_W": case "CACHE_COUNT_C": case "COMPONENT_TIME": case "QUERIES": case "QUERIES_TIME": if ($strGroupFunc == "") { if (!$bGroup) { $arQuerySelect[$strColumn] = "c." . $strColumn; } } else { if ($bGroup) { $arQuerySelect[$strGroupFunc . "_" . $strColumn] = $strGroupFunc . "(c." . $strColumn . ") " . $strGroupFunc . "_" . $strColumn; } } break; case "CACHE_COUNT": if ($strGroupFunc == "") { if (!$bGroup) { $arQuerySelect[$strColumn] = "(c.CACHE_COUNT_R + c.CACHE_COUNT_W + c.CACHE_COUNT_C) CACHE_COUNT"; } } else { if ($bGroup) { $arQuerySelect[$strGroupFunc . "_" . $strColumn] = $strGroupFunc . "(c.CACHE_COUNT_R + c.CACHE_COUNT_W + c.CACHE_COUNT_C) " . $strGroupFunc . "_" . $strColumn; } } break; case "COUNT": if ($strGroupFunc == "" && $bGroup) { $arQuerySelect[$strColumn] = "COUNT(c.ID) " . $strColumn; } break; } } $obQueryWhere = new CSQLWhere(); static $arWhereFields = array("HIT_ID" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.HIT_ID", "FIELD_TYPE" => "int", "JOIN" => false), "COMPONENT_NAME" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.COMPONENT_NAME", "FIELD_TYPE" => "string", "JOIN" => false), "ID" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.ID", "FIELD_TYPE" => "int", "JOIN" => false), "CACHE_TYPE" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.CACHE_TYPE", "FIELD_TYPE" => "string", "JOIN" => false), "CACHE_SIZE" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.CACHE_SIZE", "FIELD_TYPE" => "int", "JOIN" => false), "QUERIES" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.QUERIES", "FIELD_TYPE" => "int", "JOIN" => false), "HIT_SCRIPT_NAME" => array("TABLE_ALIAS" => "h", "FIELD_NAME" => "h.SCRIPT_NAME", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_perf_hit h on h.ID = c.HIT_ID"), "HIT_IS_ADMIN" => array("TABLE_ALIAS" => "h", "FIELD_NAME" => "h.IS_ADMIN", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_perf_hit h on h.ID = c.HIT_ID"), "HIT_CACHE_TYPE" => array("TABLE_ALIAS" => "h", "FIELD_NAME" => "h.CACHE_TYPE", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_perf_hit h on h.ID = c.HIT_ID"), "HIT_MENU_RECALC" => array("TABLE_ALIAS" => "h", "FIELD_NAME" => "h.MENU_RECALC", "FIELD_TYPE" => "int", "JOIN" => "INNER JOIN b_perf_hit h on h.ID = c.HIT_ID")); $obQueryWhere->SetFields($arWhereFields); if (count($arQuerySelect) < 1) { $arQuerySelect = array("ID" => "c.ID"); } $strQueryWhere = $obQueryWhere->GetQuery($arFilter); $strHaving = ""; if ($bGroup && count($arQueryGroup) > 0 && array_key_exists(">COUNT", $arFilter)) { $strHaving = "HAVING COUNT(*) > " . intval($arFilter["COUNT"]) . ""; } if (is_array($arNavStartParams) && $arNavStartParams["nTopCount"] > 0) { $strSql = $DB->TopSQL("\n\t\t\t\tSELECT " . implode(", ", $arQuerySelect) . "\n\t\t\t\tFROM b_perf_component c\n\t\t\t\t" . $obQueryWhere->GetJoins() . "\n\t\t\t\t" . ($strQueryWhere ? "WHERE " . $strQueryWhere : "") . "\n\t\t\t\t" . ($bGroup ? "GROUP BY " . implode(", ", $arQueryGroup) : "") . "\n\t\t\t\t" . $strHaving . "\n\t\t\t\t" . (count($arQueryOrder) ? "ORDER BY " . implode(", ", $arQueryOrder) : "") . "\n\t\t\t", $arNavStartParams["nTopCount"]); $res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } elseif (is_array($arNavStartParams)) { $strSql = "\n\t\t\t\tSELECT count('x') CNT\n\t\t\t\tFROM b_perf_component c\n\t\t\t\t" . $obQueryWhere->GetJoins() . "\n\t\t\t\t" . ($strQueryWhere ? "WHERE " . $strQueryWhere : "") . "\n\t\t\t\t" . ($bGroup ? "GROUP BY " . implode(", ", $arQueryGroup) : "") . "\n\t\t\t\t" . $strHaving . "\n\t\t\t"; $res_cnt = $DB->Query($strSql); $ar_cnt = $res_cnt->Fetch(); $strSql = "\n\t\t\t\tSELECT " . implode(", ", $arQuerySelect) . "\n\t\t\t\tFROM b_perf_component c\n\t\t\t\t" . $obQueryWhere->GetJoins() . "\n\t\t\t\t" . ($strQueryWhere ? "WHERE " . $strQueryWhere : "") . "\n\t\t\t\t" . ($bGroup ? "GROUP BY " . implode(", ", $arQueryGroup) : "") . "\n\t\t\t\t" . $strHaving . "\n\t\t\t\t" . (count($arQueryOrder) ? "ORDER BY " . implode(", ", $arQueryOrder) : "") . "\n\t\t\t"; $res = new CDBResult(); $res->NavQuery($strSql, $ar_cnt["CNT"], $arNavStartParams); } else { $strSql = "\n\t\t\t\tSELECT " . implode(", ", $arQuerySelect) . "\n\t\t\t\tFROM b_perf_component c\n\t\t\t\t" . $obQueryWhere->GetJoins() . "\n\t\t\t\t" . ($strQueryWhere ? "WHERE " . $strQueryWhere : "") . "\n\t\t\t\t" . ($bGroup ? "GROUP BY " . implode(", ", $arQueryGroup) : "") . "\n\t\t\t\t" . $strHaving . "\n\t\t\t\t" . (count($arQueryOrder) ? "ORDER BY " . implode(", ", $arQueryOrder) : "") . "\n\t\t\t"; $res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } return $res; }
/** * @param array[string]string $arOrder * @param array[string]string $arFilter * @param array[string]string $arSelect * @return CDBResult */ static function GetList($arOrder = false, $arFilter = false, $arSelect = false) { global $DB; if (!is_array($arSelect)) { $arSelect = array(); } if (count($arSelect) < 1) { $arSelect = array("ID", "ACTIVE", "READ_ONLY", "SORT", "SERVICE_ID", "LOCATION", "BUCKET", "SETTINGS", "CNAME", "PREFIX", "FILE_COUNT", "FILE_SIZE", "LAST_FILE_ID", "FILE_RULES"); } if (!is_array($arOrder)) { $arOrder = array(); } $arQueryOrder = array(); foreach ($arOrder as $strColumn => $strDirection) { $strColumn = strtoupper($strColumn); $strDirection = strtoupper($strDirection) === "ASC" ? "ASC" : "DESC"; switch ($strColumn) { case "ID": case "SORT": $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; break; default: break; } } $arQuerySelect = array(); foreach ($arSelect as $strColumn) { $strColumn = strtoupper($strColumn); switch ($strColumn) { case "ID": case "ACTIVE": case "READ_ONLY": case "SORT": case "SERVICE_ID": case "LOCATION": case "BUCKET": case "SETTINGS": case "CNAME": case "PREFIX": case "FILE_COUNT": case "FILE_SIZE": case "LAST_FILE_ID": case "FILE_RULES": $arQuerySelect[$strColumn] = "s." . $strColumn; break; } } if (count($arQuerySelect) < 1) { $arQuerySelect = array("ID" => "s.ID"); } $obQueryWhere = new CSQLWhere(); $arFields = array("ID" => array("TABLE_ALIAS" => "s", "FIELD_NAME" => "s.ID", "FIELD_TYPE" => "int"), "SERVICE_ID" => array("TABLE_ALIAS" => "s", "FIELD_NAME" => "s.SERVICE_ID", "FIELD_TYPE" => "string"), "BUCKET" => array("TABLE_ALIAS" => "s", "FIELD_NAME" => "s.BUCKET", "FIELD_TYPE" => "string")); $obQueryWhere->SetFields($arFields); if (!is_array($arFilter)) { $arFilter = array(); } $strQueryWhere = $obQueryWhere->GetQuery($arFilter); $bDistinct = $obQueryWhere->bDistinctReqired; $strSql = "\n\t\t\tSELECT " . ($bDistinct ? "DISTINCT" : "") . "\n\t\t\t" . implode(", ", $arQuerySelect) . "\n\t\t\tFROM\n\t\t\t\tb_clouds_file_bucket s\n\t\t\t" . $obQueryWhere->GetJoins() . "\n\t\t"; if ($strQueryWhere != "") { $strSql .= "\n\t\t\t\tWHERE\n\t\t\t\t" . $strQueryWhere . "\n\t\t\t"; } if (count($arQueryOrder) > 0) { $strSql .= "\n\t\t\t\tORDER BY\n\t\t\t\t" . implode(", ", $arQueryOrder) . "\n\t\t\t"; } return $DB->Query($strSql); }
public static function GetList($arOrder = false, $arFilter = false, $arSelect = false) { global $DB; if (!is_array($arSelect)) { $arSelect = array(); } if (count($arSelect) < 1) { $arSelect = array("ID", "ACTIVE", "ROLE_ID", "NAME", "DESCRIPTION", "DB_HOST", "DB_NAME", "DB_LOGIN", "DB_PASSWORD", "MASTER_ID", "SERVER_ID", "STATUS", "WEIGHT", "SELECTABLE", "GROUP_ID"); } if (!is_array($arOrder)) { $arOrder = array(); } $arQueryOrder = array(); foreach ($arOrder as $strColumn => $strDirection) { $strColumn = strtoupper($strColumn); $strDirection = strtoupper($strDirection) == "ASC" ? "ASC" : "DESC"; switch ($strColumn) { case "ID": case "NAME": $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; break; } } $arQuerySelect = array(); foreach ($arSelect as $strColumn) { $strColumn = strtoupper($strColumn); switch ($strColumn) { case "ID": case "ACTIVE": case "ROLE_ID": case "NAME": case "DESCRIPTION": case "DB_HOST": case "DB_NAME": case "DB_LOGIN": case "DB_PASSWORD": case "MASTER_ID": case "SERVER_ID": case "STATUS": case "WEIGHT": case "SELECTABLE": case "GROUP_ID": $arQuerySelect[$strColumn] = "n." . $strColumn; break; } } if (count($arQuerySelect) < 1) { $arQuerySelect = array("ID" => "n.ID"); } $obQueryWhere = new CSQLWhere(); $arFields = array("ID" => array("TABLE_ALIAS" => "n", "FIELD_NAME" => "n.ID", "FIELD_TYPE" => "int", "JOIN" => false), "GROUP_ID" => array("TABLE_ALIAS" => "n", "FIELD_NAME" => "n.GROUP_ID", "FIELD_TYPE" => "string", "JOIN" => false), "ROLE_ID" => array("TABLE_ALIAS" => "n", "FIELD_NAME" => "n.ROLE_ID", "FIELD_TYPE" => "string", "JOIN" => false), "ACTIVE" => array("TABLE_ALIAS" => "n", "FIELD_NAME" => "n.ACTIVE", "FIELD_TYPE" => "string", "JOIN" => false), "SERVER_ID" => array("TABLE_ALIAS" => "n", "FIELD_NAME" => "n.SERVER_ID", "FIELD_TYPE" => "int", "JOIN" => false), "MASTER_ID" => array("TABLE_ALIAS" => "n", "FIELD_NAME" => "n.MASTER_ID", "FIELD_TYPE" => "int", "JOIN" => false), "STATUS" => array("TABLE_ALIAS" => "n", "FIELD_NAME" => "n.STATUS", "FIELD_TYPE" => "string", "JOIN" => false), "NAME" => array("TABLE_ALIAS" => "n", "FIELD_NAME" => "n.NAME", "FIELD_TYPE" => "string", "JOIN" => false)); $obQueryWhere->SetFields($arFields); if (!is_array($arFilter)) { $arFilter = array(); } $strQueryWhere = $obQueryWhere->GetQuery($arFilter); $bDistinct = $obQueryWhere->bDistinctReqired; $strSql = "\n\t\t\tSELECT " . ($bDistinct ? "DISTINCT" : "") . "\n\t\t\t" . implode(", ", $arQuerySelect) . "\n\t\t\tFROM\n\t\t\t\tb_cluster_dbnode n\n\t\t\t" . $obQueryWhere->GetJoins() . "\n\t\t"; if ($strQueryWhere) { $strSql .= "\n\t\t\t\tWHERE\n\t\t\t\t" . $strQueryWhere . "\n\t\t\t"; } if (count($arQueryOrder) > 0) { $strSql .= "\n\t\t\t\tORDER BY\n\t\t\t\t" . implode(", ", $arQueryOrder) . "\n\t\t\t"; } return $DB->Query($strSql, false, '', array('fixed_connection' => true)); }
public static function GetList($arOrder = array(), $arFilter = array(), $arNavParams = false) { /** @global CDatabase $DB */ global $DB; static $arFields = array("ID" => array("FIELD_NAME" => "L.ID", "FIELD_TYPE" => "int"), "CONTROLLER_MEMBER_ID" => array("FIELD_NAME" => "L.CONTROLLER_MEMBER_ID", "FIELD_TYPE" => "int"), "NAME" => array("FIELD_NAME" => "L.NAME", "FIELD_TYPE" => "string"), "TIMESTAMP_X" => array("FIELD_NAME" => "L.TIMESTAMP_X", "FIELD_TYPE" => "datetime"), "TASK_ID" => array("FIELD_NAME" => "L.TASK_ID", "FIELD_TYPE" => "int"), "TASK_NAME" => array("FIELD_NAME" => "T.TASK_ID", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_controller_task T ON T.ID = L.TASK_ID", "LEFT_JOIN" => "LEFT JOIN b_controller_task T ON T.ID = L.TASK_ID"), "USER_ID" => array("FIELD_NAME" => "L.USER_ID", "FIELD_TYPE" => "int"), "USER_NAME" => array("FIELD_NAME" => "L.USER_ID", "FIELD_TYPE" => "string"), "USER_LAST_NAME" => array("FIELD_NAME" => "U.LAST_NAME", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_user U ON U.ID = L.USER_ID", "LEFT_JOIN" => "LEFT JOIN b_user U ON U.ID = L.USER_ID"), "USER_LOGIN" => array("FIELD_NAME" => "U.LOGIN", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_user U ON U.ID = L.USER_ID", "LEFT_JOIN" => "LEFT JOIN b_user U ON U.ID = L.USER_ID"), "STATUS" => array("FIELD_NAME" => "L.STATUS", "FIELD_TYPE" => "string"), "CONTROLLER_MEMBER_NAME" => array("FIELD_NAME" => "M.NAME", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_controller_member M ON M.ID = L.CONTROLLER_MEMBER_ID", "LEFT_JOIN" => "LEFT JOIN b_controller_member M ON M.ID = L.CONTROLLER_MEMBER_ID"), "CONTROLLER_MEMBER_URL" => array("FIELD_NAME" => "M.URL", "FIELD_TYPE" => "string", "JOIN" => "INNER JOIN b_controller_member M ON M.ID = L.CONTROLLER_MEMBER_ID", "LEFT_JOIN" => "LEFT JOIN b_controller_member M ON M.ID = L.CONTROLLER_MEMBER_ID")); $obWhere = new CSQLWhere(); $obWhere->SetFields($arFields); $arFilterNew = array(); foreach ($arFilter as $k => $value) { if (strlen($value) > 0 || $value === false) { $arFilterNew[$k] = $value; } } $strWhere = $obWhere->GetQuery($arFilterNew); $strSelect = "\n\t\t\tSELECT L.*\n\t\t\t\t,M.NAME as CONTROLLER_MEMBER_NAME\n\t\t\t\t,M.URL as CONTROLLER_MEMBER_URL\n\t\t\t\t,U.NAME as USER_NAME\n\t\t\t\t,U.LAST_NAME as USER_LAST_NAME\n\t\t\t\t,U.LOGIN as USER_LOGIN\n\t\t\t\t,T.TASK_ID as TASK_NAME\n\t\t\t\t," . $DB->DateToCharFunction("L.TIMESTAMP_X") . " as TIMESTAMP_X\n\t\t"; $strSql = "\n\t\t\tFROM b_controller_log L\n\t\t\tLEFT JOIN b_controller_member M ON L.CONTROLLER_MEMBER_ID=M.ID\n\t\t\tLEFT JOIN b_controller_task T ON T.ID = L.TASK_ID\n\t\t\tLEFT JOIN b_user U ON U.ID = L.USER_ID\n\t\t\t" . (strlen($strWhere) <= 0 ? "" : "WHERE " . $strWhere) . "\n\t\t"; $strOrder = CControllerAgent::_OrderBy($arOrder, $arFields); if (is_array($arNavParams) && $arNavParams["nTopCount"] > 0) { $strSql = $DB->TopSQL($strSelect . $strSql . $strOrder, $arNavParams["nTopCount"]); $dbr = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } elseif (is_array($arNavParams)) { $res_cnt = $DB->Query("\n\t\t\t\tSELECT count('x') CNT\n\t\t\t\tFROM b_controller_log L\n\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t" . (strlen($strWhere) <= 0 ? "" : "WHERE " . $strWhere) . "\n\t\t\t"); $ar_cnt = $res_cnt->Fetch(); $dbr = new CDBResult(); $dbr->NavQuery($strSelect . $strSql . $strOrder, $ar_cnt["CNT"], $arNavParams); } else { $dbr = $DB->Query($strSelect . $strSql . $strOrder, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } $dbr->is_filtered = strlen($strWhere) > 0; return $dbr; }
function GetList($arOrder, $arFilter, $bGroup, $arNavStartParams, $arSelect) { global $DB; if (!is_array($arSelect)) { $arSelect = array(); } if (count($arSelect) < 1) { $arSelect = array("ID"); } $arQueryGroup = array(); $arQuerySelect = array(); foreach ($arSelect as $strColumn) { $strColumn = strtoupper($strColumn); if (preg_match("/^(MIN|MAX|AVG|SUM)_(.*)\$/", $strColumn, $arMatch)) { $strGroupFunc = $arMatch[1]; $strColumn = $arMatch[2]; } else { $strGroupFunc = ""; } switch ($strColumn) { case "ID": case "HIT_ID": case "COMPONENT_ID": case "NN": case "OP_MODE": case "MODULE_NAME": case "COMPONENT_NAME": case "BASE_DIR": case "INIT_DIR": case "FILE_NAME": case "FILE_PATH": if ($strGroupFunc == "") { if ($bGroup) { $arQueryGroup[$strColumn] = "c." . $strColumn; } $arQuerySelect[$strColumn] = "c." . $strColumn; } break; case "HIT_RATIO": if ($strGroupFunc == "" && $bGroup) { $sql = "case when sum((case when c.OP_MODE='W' then 1 else 0 end)+(case when c.OP_MODE='R' then 1 else 0 end)) > 0 then sum(case when c.OP_MODE='R' then 1 else 0 end)/sum((case when c.OP_MODE='W' then 1 else 0 end)+(case when c.OP_MODE='R' then 1 else 0 end)) else null end"; $arQuerySelect[$strColumn] = $sql . " " . $strColumn; } break; case "CACHE_PATH": if ($strGroupFunc == "") { if ($bGroup) { $arQueryGroup[$strColumn] = $DB->Concat("c.BASE_DIR", "c.INIT_DIR", "c.FILE_NAME"); } $arQuerySelect[$strColumn] = $DB->Concat("c.BASE_DIR", "','", "c.INIT_DIR", "','", "c.FILE_NAME") . " " . $strColumn; } break; case "CACHE_SIZE": if ($strGroupFunc == "") { if (!$bGroup) { $arQuerySelect[$strColumn] = "c." . $strColumn; } } else { if ($bGroup) { $arQuerySelect[$strGroupFunc . "_" . $strColumn] = $strGroupFunc . "(c." . $strColumn . ") " . $strGroupFunc . "_" . $strColumn; } } break; case "COUNT": if ($strGroupFunc == "" && $bGroup) { $arQuerySelect[$strColumn] = "COUNT(c.ID) " . $strColumn; } break; case "COUNT_R": if ($strGroupFunc == "" && $bGroup) { $arQuerySelect[$strColumn] = "SUM(case when c.OP_MODE='R' then 1 else 0 end) " . $strColumn; } break; case "COUNT_W": if ($strGroupFunc == "" && $bGroup) { $arQuerySelect[$strColumn] = "SUM(case when c.OP_MODE='W' then 1 else 0 end) " . $strColumn; } break; case "COUNT_C": if ($strGroupFunc == "" && $bGroup) { $arQuerySelect[$strColumn] = "SUM(case when c.OP_MODE='C' then 1 else 0 end) " . $strColumn; } break; } } if (!is_array($arOrder)) { $arOrder = array(); } $arQueryOrder = array(); foreach ($arOrder as $strColumn => $strDirection) { $strColumn = strtoupper($strColumn); if (!array_key_exists($strColumn, $arQuerySelect)) { continue; } if (preg_match("/^(MIN|MAX|AVG|SUM)_(.*)\$/", $strColumn, $arMatch)) { $strGroupFunc = $arMatch[1]; $strColumn = $arMatch[2]; } else { $strGroupFunc = ""; } $strDirection = strtoupper($strDirection) == "ASC" ? "ASC" : "DESC"; switch ($strColumn) { case "ID": case "HIT_ID": case "COMPONENT_ID": case "NN": case "OP_MODE": case "MODULE_NAME": case "COMPONENT_NAME": case "BASE_DIR": case "INIT_DIR": case "FILE_NAME": if ($strGroupFunc == "") { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; } break; case "HIT_RATIO": if ($strGroupFunc == "" && $bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; } break; case "FILE_PATH": if (!isset($arQueryOrder["BASE_DIR"]) && !isset($arQueryOrder["INIT_DIR"]) && !isset($arQueryOrder["FILE_NAME"]) && $strGroupFunc == "") { $arSelect[] = "BASE_DIR"; $arQueryOrder["BASE_DIR"] = "BASE_DIR " . $strDirection; $arSelect[] = "INIT_DIR"; $arQueryOrder["INIT_DIR"] = "INIT_DIR " . $strDirection; $arSelect[] = "FILE_NAME"; $arQueryOrder["FILE_NAME"] = "FILE_NAME " . $strDirection; } break; case "INIT_DIR": if (!isset($arQueryOrder["BASE_DIR"]) && !isset($arQueryOrder["INIT_DIR"]) && $strGroupFunc == "") { $arSelect[] = "BASE_DIR"; $arQueryOrder["BASE_DIR"] = "BASE_DIR " . $strDirection; $arSelect[] = "INIT_DIR"; $arQueryOrder["INIT_DIR"] = "INIT_DIR " . $strDirection; } break; case "CACHE_SIZE": if ($strGroupFunc == "") { if (!$bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; } } else { if ($bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strGroupFunc . "_" . $strColumn] = $strGroupFunc . "_" . $strColumn . " " . $strDirection; } } break; case "COUNT": case "COUNT_R": case "COUNT_W": case "COUNT_C": if ($strGroupFunc == "" && $bGroup) { $arSelect[] = $strColumn; $arQueryOrder[$strColumn] = $strColumn . " " . $strDirection; } break; } } $obQueryWhere = new CSQLWhere(); static $arWhereFields = array("ID" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.ID", "FIELD_TYPE" => "int", "JOIN" => false), "HIT_ID" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.HIT_ID", "FIELD_TYPE" => "int", "JOIN" => false), "COMPONENT_ID" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.COMPONENT_ID", "FIELD_TYPE" => "int", "JOIN" => false), "OP_MODE" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.OP_MODE", "FIELD_TYPE" => "string", "JOIN" => false), "MODULE_NAME" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.MODULE_NAME", "FIELD_TYPE" => "string", "JOIN" => false), "COMPONENT_NAME" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.COMPONENT_NAME", "FIELD_TYPE" => "string", "JOIN" => false), "BASE_DIR" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.BASE_DIR", "FIELD_TYPE" => "string", "JOIN" => false), "INIT_DIR" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.INIT_DIR", "FIELD_TYPE" => "string", "JOIN" => false), "FILE_NAME" => array("TABLE_ALIAS" => "c", "FIELD_NAME" => "c.FILE_NAME", "FIELD_TYPE" => "string", "JOIN" => false)); $obQueryWhere->SetFields($arWhereFields); if (count($arQuerySelect) < 1) { $arQuerySelect = array("ID" => "c.ID"); } $strSql = "\n\t\t\tSELECT\n\t\t\t" . implode(", ", $arQuerySelect) . "\n\t\t\tFROM\n\t\t\t\tb_perf_cache c\n\t\t"; if (!is_array($arFilter)) { $arFilter = array(); } if ($strQueryWhere = $obQueryWhere->GetQuery($arFilter)) { $strSql .= $obQueryWhere->GetJoins() . "\n\t\t\t\tWHERE\n\t\t\t\t" . $strQueryWhere . "\n\t\t\t"; } if ($bGroup && count($arQueryGroup) > 0) { $strSql .= "\n\t\t\t\tGROUP BY\n\t\t\t\t" . implode(", ", $arQueryGroup) . "\n\t\t\t"; if (array_key_exists(">COUNT", $arFilter)) { $strSql .= "\n\t\t\t\t\tHAVING\n\t\t\t\t\tCOUNT(*) > " . intval($arFilter["COUNT"]) . "\n\t\t\t\t"; } } if (count($arQueryOrder) > 0) { $strSql .= "\n\t\t\t\tORDER BY\n\t\t\t\t" . implode(", ", $arQueryOrder) . "\n\t\t\t"; } return $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); }