/**
* @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__);
}
