public static function GetList($arFilter = array(), $arOrder = array())
{
global $DB;
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":
$arSelect[] = $strColumn;
$arQueryOrder[$strColumn] = $strColumn . " " . $strDirection;
break;
}
}
$obQueryWhere = new CSQLWhere();
$obQueryWhere->SetFields(array("ID" => array("TABLE_ALIAS" => "s", "FIELD_NAME" => "ID", "FIELD_TYPE" => "int", "JOIN" => false)));
if (!is_array($arFilter)) {
$arFilter = array();
}
$strQueryWhere = $obQueryWhere->GetQuery($arFilter);
$strSql = "\n\t\t\tSELECT *\n\t\t\tFROM b_perf_index_complete s\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;
}
function GetList($arOrder, $arFilter = array())
{
global $DB;
if (!is_array($arOrder)) {
$arOrder = array();
}
if (count($arOrder) < 1) {
$arOrder = array("ID" => "DESC");
}
$arQueryOrder = array();
foreach ($arOrder as $strColumn => $strDirection) {
$strColumn = strtoupper($strColumn);
$strDirection = strtoupper($strDirection) == "ASC" ? "ASC" : "DESC";
switch ($strColumn) {
case "ID":
$arQueryOrder[$strColumn] = $strColumn . " " . $strDirection;
break;
}
}
static $arWhereFields = array("ID" => array("TABLE_ALIAS" => "h", "FIELD_NAME" => "ID", "FIELD_TYPE" => "int", "JOIN" => false));
$obQueryWhere = new CSQLWhere();
$obQueryWhere->SetFields($arWhereFields);
$strSql = "\n\t\t\tSELECT\n\t\t\t\th.*\n\t\t\t\t," . $DB->DateToCharFunction("h.TIMESTAMP_X") . " TIMESTAMP_X\n\t\t\tFROM\n\t\t\t\tb_perf_history h\n\t\t";
if (!is_array($arFilter)) {
$arFilter = array();
}
if ($strQueryWhere = $obQueryWhere->GetQuery($arFilter)) {
$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($arSelect, $arFilter, $arOrder = array(), $arNavParams = false)
{
global $DB;
$arFields = $this->GetTableFields();
if (!is_array($arSelect)) {
$arSelect = array();
}
if (count($arSelect) < 1) {
$arSelect = array_keys($arFields);
}
if (!is_array($arOrder)) {
$arOrder = array();
}
$arQueryOrder = array();
foreach ($arOrder as $strColumn => $strDirection) {
$strDirection = strtoupper($strDirection) == "ASC" ? "ASC" : "DESC";
if (array_key_exists($strColumn, $arFields)) {
$arSelect[] = $strColumn;
if ($arFields[$strColumn] == "datetime") {
$arQueryOrder[$strColumn] = $this->escapeColumn("TMP_" . $strColumn) . " " . $strDirection;
} else {
$arQueryOrder[$strColumn] = $this->escapeColumn($strColumn) . " " . $strDirection;
}
}
}
$arQuerySelect = array();
foreach ($arSelect as $strColumn) {
if (array_key_exists($strColumn, $arFields)) {
if ($arFields[$strColumn] == "datetime" || $arFields[$strColumn] == "date") {
$arQuerySelect["TMP_" . $strColumn] = "t." . $strColumn . " TMP_" . $strColumn;
$arQuerySelect[$strColumn] = $DB->DateToCharFunction("t." . $strColumn, "SHORT") . " " . $strColumn;
$arQuerySelect["FULL_" . $strColumn] = $DB->DateToCharFunction("t." . $strColumn, "FULL") . " FULL_" . $strColumn;
$arQuerySelect["SHORT_" . $strColumn] = $DB->DateToCharFunction("t." . $strColumn, "SHORT") . " SHORT_" . $strColumn;
} else {
$arQuerySelect[$strColumn] = "t." . $strColumn;
}
}
}
foreach ($arFields as $FIELD_NAME => $FIELD_TYPE) {
$arFields[$FIELD_NAME] = array("TABLE_ALIAS" => "t", "FIELD_NAME" => $FIELD_NAME, "FIELD_TYPE" => $FIELD_TYPE, "JOIN" => false);
}
$obQueryWhere = new CSQLWhere();
$obQueryWhere->SetFields($arFields);
if (count($arQuerySelect) < 1) {
$arQuerySelect = array("*" => "t.*");
}
if (is_array($arNavParams)) {
return $this->NavQuery($arNavParams, $arQuerySelect, $this->TABLE_NAME, $obQueryWhere->GetQuery($arFilter), $arQueryOrder);
} else {
$strSql = "\n\t\t\t\tSELECT\n\t\t\t\t" . implode(", ", $arQuerySelect) . "\n\t\t\t\tFROM\n\t\t\t\t\t" . $this->TABLE_NAME . " t\n\t\t\t";
if ($strQueryWhere = $obQueryWhere->GetQuery($arFilter)) {
$strSql .= "\n\t\t\t\t\tWHERE\n\t\t\t\t\t" . $strQueryWhere . "\n\t\t\t\t";
}
if (count($arQueryOrder) > 0) {
$strSql .= "\n\t\t\t\t\tORDER BY\n\t\t\t\t\t" . implode(", ", $arQueryOrder) . "\n\t\t\t\t";
}
//echo "<pre>",htmlspecialcharsbx($strSql),"</pre><hr>";
return $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
}
function GetList($arOrder = array(), $arFilter = array())
{
global $DB;
$arFields = array('GROUP_ID' => array('TABLE_ALIAS' => 'UG', 'FIELD_NAME' => 'UG.GROUP_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'USER_ID' => array('TABLE_ALIAS' => 'UG', 'FIELD_NAME' => 'UG.USER_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'CAN_VIEW_GROUP_MESSAGES' => array('TABLE_ALIAS' => 'UG', 'FIELD_NAME' => 'UG.CAN_VIEW_GROUP_MESSAGES', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'CAN_MAIL_GROUP_MESSAGES' => array('TABLE_ALIAS' => 'UG', 'FIELD_NAME' => 'UG.CAN_MAIL_GROUP_MESSAGES', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'CAN_MAIL_UPDATE_GROUP_MESSAGES' => array('TABLE_ALIAS' => 'UG', 'FIELD_NAME' => 'UG.CAN_MAIL_UPDATE_GROUP_MESSAGES', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'GROUP_NAME' => array('TABLE_ALIAS' => 'G', 'FIELD_NAME' => 'G.NAME', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'IS_TEAM_GROUP' => array('TABLE_ALIAS' => 'G', 'FIELD_NAME' => 'G.IS_TEAM_GROUP', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'LOGIN' => array('TABLE_ALIAS' => 'U', 'FIELD_NAME' => 'U.LOGIN', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'FIRST_NAME' => array('TABLE_ALIAS' => 'U', 'FIELD_NAME' => 'U.NAME', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'LAST_NAME' => array('TABLE_ALIAS' => 'U', 'FIELD_NAME' => 'U.LAST_NAME', 'FIELD_TYPE' => 'string', 'JOIN' => false));
$strOrder = '';
if (is_array($arOrder) && count($arOrder) > 0) {
foreach ($arOrder as $k => $v) {
if (array_key_exists($k, $arFields)) {
$v = strtoupper($v);
if ($v != 'DESC') {
$v = 'ASC';
}
if (strlen($strOrder) > 0) {
$strOrder .= ', ';
}
$strOrder .= $arFields[$k]['FIELD_NAME'] . ' ' . $v;
}
}
}
$obQueryWhere = new CSQLWhere();
$obQueryWhere->SetFields($arFields);
$where = $obQueryWhere->GetQuery($arFilter);
$strQuery = 'SELECT ' . 'UG.*, G.NAME GROUP_NAME, G.IS_TEAM_GROUP, ' . 'U.LOGIN, U.NAME FIRST_NAME, U.LAST_NAME ' . 'FROM b_ticket_user_ugroup UG ' . 'INNER JOIN b_ticket_ugroups G ON (UG.GROUP_ID=G.ID) ' . 'INNER JOIN b_user U ON (UG.USER_ID=U.ID) ';
if (strlen($where) > 0) {
$strQuery .= ' WHERE ' . $where;
}
if (strlen($strOrder) > 0) {
$strQuery .= ' ORDER BY ' . $strOrder;
}
return $DB->Query($strQuery, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
public static function GetList($arOrder = array(), $arFilter = array())
{
global $DB;
$arFields = array('ID' => array('TABLE_ALIAS' => 'G', 'FIELD_NAME' => 'ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'NAME' => array('TABLE_ALIAS' => 'G', 'FIELD_NAME' => 'NAME', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'XML_ID' => array('TABLE_ALIAS' => 'G', 'FIELD_NAME' => 'XML_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'SORT' => array('TABLE_ALIAS' => 'G', 'FIELD_NAME' => 'SORT', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'IS_TEAM_GROUP' => array('TABLE_ALIAS' => 'G', 'FIELD_NAME' => 'IS_TEAM_GROUP', 'FIELD_TYPE' => 'string', 'JOIN' => false));
$strOrder = '';
if (is_array($arOrder) && count($arOrder) > 0) {
foreach ($arOrder as $k => $v) {
if (array_key_exists($k, $arFields)) {
$v = strtoupper($v);
if ($v != 'DESC') {
$v = 'ASC';
}
if (strlen($strOrder) > 0) {
$strOrder .= ', ';
}
$strOrder .= $arFields[$k]['TABLE_ALIAS'] . '.' . $arFields[$k]['FIELD_NAME'] . ' ' . $v;
}
}
}
$obQueryWhere = new CSQLWhere();
$obQueryWhere->SetFields($arFields);
$where = $obQueryWhere->GetQuery($arFilter);
$strQuery = 'SELECT G.* FROM b_ticket_ugroups G';
if (strlen($where) > 0) {
$strQuery .= ' WHERE ' . $where;
}
if (strlen($strOrder) > 0) {
$strQuery .= ' ORDER BY ' . $strOrder;
}
return $DB->Query($strQuery, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
/**
* @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 GetBestSellerList($by = "AMOUNT", $arFilter = array(), $arOrderFilter = array(), $limit = 0)
{
global $DB;
$byQuantity = false;
if ($by == "QUANTITY") {
$byQuantity = true;
}
$arJoin = array();
$arWhere = array();
$orderFilter = "";
$i = 1;
if (is_array($arFilter) && count($arFilter) > 0) {
foreach ($arFilter as $key => $value) {
$arJoin[] = "LEFT JOIN b_sale_basket_props p" . $i . " ON (b.ID = p" . $i . ".BASKET_ID)";
$arFilter = CSaleProduct::GetFilterOperation($key, $value);
$arWhere[] = " AND p" . $i . ".CODE = '" . $arFilter["field"] . "' AND p" . $i . ".VALUE " . $arFilter["operation"] . " " . $arFilter["value"];
$i++;
}
}
$arFields = array("ID" => array("FIELD_NAME" => "O.ID", "FIELD_TYPE" => "int"), "LID" => array("FIELD_NAME" => "O.LID", "FIELD_TYPE" => "string"), "PERSON_TYPE_ID" => array("FIELD_NAME" => "O.PERSON_TYPE_ID", "FIELD_TYPE" => "int"), "PAYED" => array("FIELD_NAME" => "O.PAYED", "FIELD_TYPE" => "string"), "DATE_PAYED" => array("FIELD_NAME" => "O.DATE_PAYED", "FIELD_TYPE" => "datetime"), "EMP_PAYED_ID" => array("FIELD_NAME" => "O.EMP_PAYED_ID", "FIELD_TYPE" => "int"), "CANCELED" => array("FIELD_NAME" => "O.CANCELED", "FIELD_TYPE" => "string"), "DATE_CANCELED" => array("FIELD_NAME" => "O.DATE_CANCELED", "FIELD_TYPE" => "datetime"), "EMP_CANCELED_ID" => array("FIELD_NAME" => "O.EMP_CANCELED_ID", "FIELD_TYPE" => "int"), "REASON_CANCELED" => array("FIELD_NAME" => "O.REASON_CANCELED", "FIELD_TYPE" => "string"), "STATUS_ID" => array("FIELD_NAME" => "O.STATUS_ID", "FIELD_TYPE" => "string"), "DATE_STATUS" => array("FIELD_NAME" => "O.DATE_STATUS", "FIELD_TYPE" => "datetime"), "PAY_VOUCHER_NUM" => array("FIELD_NAME" => "O.PAY_VOUCHER_NUM", "FIELD_TYPE" => "string"), "PAY_VOUCHER_DATE" => array("FIELD_NAME" => "O.PAY_VOUCHER_DATE", "FIELD_TYPE" => "date"), "EMP_STATUS_ID" => array("FIELD_NAME" => "O.EMP_STATUS_ID", "FIELD_TYPE" => "int"), "PRICE_DELIVERY" => array("FIELD_NAME" => "O.PRICE_DELIVERY", "FIELD_TYPE" => "double"), "ALLOW_DELIVERY" => array("FIELD_NAME" => "O.ALLOW_DELIVERY", "FIELD_TYPE" => "string"), "DATE_ALLOW_DELIVERY" => array("FIELD_NAME" => "O.DATE_ALLOW_DELIVERY", "FIELD_TYPE" => "datetime"), "EMP_ALLOW_DELIVERY_ID" => array("FIELD_NAME" => "O.EMP_ALLOW_DELIVERY_ID", "FIELD_TYPE" => "int"), "PRICE" => array("FIELD_NAME" => "O.PRICE", "FIELD_TYPE" => "double"), "CURRENCY" => array("FIELD_NAME" => "O.CURRENCY", "FIELD_TYPE" => "string"), "DISCOUNT_VALUE" => array("FIELD_NAME" => "O.DISCOUNT_VALUE", "FIELD_TYPE" => "double"), "SUM_PAID" => array("FIELD_NAME" => "O.SUM_PAID", "FIELD_TYPE" => "double"), "USER_ID" => array("FIELD_NAME" => "O.USER_ID", "FIELD_TYPE" => "int"), "PAY_SYSTEM_ID" => array("FIELD_NAME" => "O.PAY_SYSTEM_ID", "FIELD_TYPE" => "int"), "DELIVERY_ID" => array("FIELD_NAME" => "O.DELIVERY_ID", "FIELD_TYPE" => "string"), "DATE_INSERT" => array("FIELD_NAME" => "O.DATE_INSERT", "FIELD_TYPE" => "datetime"), "DATE_INSERT_FORMAT" => array("FIELD_NAME" => "O.DATE_INSERT", "FIELD_TYPE" => "datetime"), "DATE_UPDATE" => array("FIELD_NAME" => "O.DATE_UPDATE", "FIELD_TYPE" => "datetime"), "USER_DESCRIPTION" => array("FIELD_NAME" => "O.USER_DESCRIPTION", "FIELD_TYPE" => "string"), "ADDITIONAL_INFO" => array("FIELD_NAME" => "O.ADDITIONAL_INFO", "FIELD_TYPE" => "string"), "PS_STATUS" => array("FIELD_NAME" => "O.PS_STATUS", "FIELD_TYPE" => "string"), "PS_STATUS_CODE" => array("FIELD_NAME" => "O.PS_STATUS_CODE", "FIELD_TYPE" => "string"), "PS_STATUS_DESCRIPTION" => array("FIELD_NAME" => "O.PS_STATUS_DESCRIPTION", "FIELD_TYPE" => "string"), "PS_STATUS_MESSAGE" => array("FIELD_NAME" => "O.PS_STATUS_MESSAGE", "FIELD_TYPE" => "string"), "PS_SUM" => array("FIELD_NAME" => "O.PS_SUM", "FIELD_TYPE" => "double"), "PS_CURRENCY" => array("FIELD_NAME" => "O.PS_CURRENCY", "FIELD_TYPE" => "string"), "PS_RESPONSE_DATE" => array("FIELD_NAME" => "O.PS_RESPONSE_DATE", "FIELD_TYPE" => "datetime"), "COMMENTS" => array("FIELD_NAME" => "O.COMMENTS", "FIELD_TYPE" => "string"), "TAX_VALUE" => array("FIELD_NAME" => "O.TAX_VALUE", "FIELD_TYPE" => "double"), "STAT_GID" => array("FIELD_NAME" => "O.STAT_GID", "FIELD_TYPE" => "string"), "RECURRING_ID" => array("FIELD_NAME" => "O.RECURRING_ID", "FIELD_TYPE" => "int"), "RECOUNT_FLAG" => array("FIELD_NAME" => "O.RECOUNT_FLAG", "FIELD_TYPE" => "string"), "AFFILIATE_ID" => array("FIELD_NAME" => "O.AFFILIATE_ID", "FIELD_TYPE" => "int"), "DELIVERY_DOC_NUM" => array("FIELD_NAME" => "O.DELIVERY_DOC_NUM", "FIELD_TYPE" => "string"), "DELIVERY_DOC_DATE" => array("FIELD_NAME" => "O.DELIVERY_DOC_DATE", "FIELD_TYPE" => "date"));
if (is_array($arOrderFilter) && count($arOrderFilter) > 0) {
$sqlWhere = new CSQLWhere();
$sqlWhere->SetFields($arFields, $arJ);
$orderFilter = $sqlWhere->GetQueryEx($arOrderFilter, $arJ);
}
//if($byQuantity)
// $strSql = "SELECT b.PRODUCT_ID, b.CATALOG_XML_ID, b.PRODUCT_XML_ID, SUM(b.QUANTITY) as QUANTITY \n";
//else
$strSql = "SELECT b.PRODUCT_ID, b.NAME, b.CATALOG_XML_ID, b.PRODUCT_XML_ID, SUM(b.PRICE*b.QUANTITY) as PRICE, AVG(b.PRICE) as AVG_PRICE, SUM(b.QUANTITY) as QUANTITY, b.CURRENCY \n";
$strSql .= "FROM b_sale_basket b \n";
foreach ($arJoin as $v) {
$strSql .= $v . "\n";
}
if (strlen($orderFilter) > 0) {
$strSql .= "INNER JOIN b_sale_order O ON (b.ORDER_ID = O.ID) \n";
}
$strSql .= "WHERE \n" . " b.ORDER_ID is not null \n";
foreach ($arWhere as $v) {
$strSql .= $v . "\n";
}
if (strlen($orderFilter) > 0) {
$strSql .= " AND " . $orderFilter . "\n";
}
$strSql .= " GROUP BY b.PRODUCT_ID, b.CATALOG_XML_ID, b.PRODUCT_XML_ID, b.CURRENCY \n";
if ($byQuantity) {
$strSql .= " ORDER BY QUANTITY DESC\n";
} else {
$strSql .= " ORDER BY PRICE DESC\n";
}
if (IntVal($limit) > 0) {
$strSql .= "LIMIT " . IntVal($limit);
}
// echo htmlspecialcharsbx($strSql);
$dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
return $dbRes;
}
function GetList($arOrder = array(), $arFilter = array())
{
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"), "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"), "USER_LOGIN" => array("FIELD_NAME" => "U.LOGIN", "FIELD_TYPE" => "string"), "STATUS" => array("FIELD_NAME" => "L.STATUS", "FIELD_TYPE" => "string"), "CONTROLLER_MEMBER_NAME" => array("FIELD_NAME" => "M.NAME", "FIELD_TYPE" => "string"), "CONTROLLER_MEMBER_URL" => array("FIELD_NAME" => "M.URL", "FIELD_TYPE" => "string"));
$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);
$strSql = "SELECT L.*, " . "\tM.NAME as CONTROLLER_MEMBER_NAME, M.URL as CONTROLLER_MEMBER_URL, " . "\tU.NAME as USER_NAME, U.LAST_NAME as USER_LAST_NAME, U.LOGIN as USER_LOGIN, " . "\tT.TASK_ID as TASK_NAME, " . "\t" . $DB->DateToCharFunction("L.TIMESTAMP_X") . "\tas TIMESTAMP_X " . "FROM b_controller_log L " . "\tLEFT JOIN b_controller_member M ON L.CONTROLLER_MEMBER_ID=M.ID " . "\tLEFT JOIN b_controller_task T ON T.ID=L.TASK_ID " . "\tLEFT JOIN b_user U ON U.ID=L.USER_ID " . (strlen($strWhere) <= 0 ? "" : "WHERE " . $strWhere) . " " . CControllerAgent::_OrderBy($arOrder, $arFields);
$dbr = $DB->Query($strSql);
$dbr->is_filtered = strlen($strWhere) > 0;
return $dbr;
}
public static function GetList($arOrder = array('ID' => 'DESC'), $arFilter = array())
{
global $DB;
// where
$arWhereFields = array('ID' => array('TABLE_ALIAS' => 'R', 'FIELD_NAME' => 'R.ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'NAME' => array('TABLE_ALIAS' => 'R', 'FIELD_NAME' => 'R.NAME', 'FIELD_TYPE' => 'string', 'JOIN' => false));
$obQueryWhere = new CSQLWhere();
$obQueryWhere->SetFields($arWhereFields);
if (!is_array($arFilter)) {
$arFilter = array();
}
$sQueryWhereFields = $obQueryWhere->GetQuery($arFilter);
$sSqlSearch = '';
if (!empty($sQueryWhereFields)) {
$sSqlSearch .= "\n\t\t\t\tAND ({$sQueryWhereFields}) ";
}
// order
$arSqlOrder = array();
if (!is_array($arOrder)) {
$arOrder = array('ID' => 'DESC');
}
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtolower($order);
if ($order != 'asc') {
$order = 'desc';
}
if (isset($arWhereFields[$by])) {
$arSqlOrder[$by] = " R.{$by} {$order} ";
} else {
$by = 'id';
$arSqlOrder[$by] = " R.ID {$order} ";
}
}
if (count($arSqlOrder) > 0) {
$sSqlOrder = "\n\t\t\t\tORDER BY " . implode(', ', $arSqlOrder);
} else {
$sSqlOrder = '';
}
$sSql = "\n\t\t\tSELECT\n\t\t\t\tID, NAME\n\t\t\tFROM\n\t\t\t\tb_crm_role R\n\t\t\tWHERE\n\t\t\t\t1=1 {$sSqlSearch}\n\t\t\t{$sSqlOrder}";
$obRes = $DB->Query($sSql, false, 'FILE: ' . __FILE__ . '<br /> LINE: ' . __LINE__);
return $obRes;
}
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;
}
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 SetEntity($entity_id, $ID)
{
global $USER_FIELD_MANAGER;
$this->user_fields = $USER_FIELD_MANAGER->GetUserFields($entity_id);
$this->entity_id = strtolower(preg_replace("/[^0-9A-Z_]+/", "", $entity_id));
$this->select = array();
$this->filter = array();
$this->order = array();
$this->obWhere = new CSQLWhere;
$num = 0;
$arFields = array();
foreach($this->user_fields as $FIELD_NAME=>$arField)
{
if($arField["MULTIPLE"]=="Y")
$num++;
$table_alias = $arField["MULTIPLE"]=="N"? $this->table_alias: $this->table_alias.$num;
$arType = $this->user_fields[$FIELD_NAME]["USER_TYPE"];
if($arField["MULTIPLE"]=="N")
$TABLE_FIELD_NAME = $table_alias.".".$FIELD_NAME;
elseif($arType["BASE_TYPE"]=="int")
$TABLE_FIELD_NAME = $table_alias.".VALUE_INT";
elseif($arType["BASE_TYPE"]=="file")
$TABLE_FIELD_NAME = $table_alias.".VALUE_INT";
elseif($arType["BASE_TYPE"]=="enum")
$TABLE_FIELD_NAME = $table_alias.".VALUE_INT";
elseif($arType["BASE_TYPE"]=="double")
$TABLE_FIELD_NAME = $table_alias.".VALUE_DOUBLE";
elseif($arType["BASE_TYPE"]=="datetime")
$TABLE_FIELD_NAME = $table_alias.".VALUE_DATE";
else
$TABLE_FIELD_NAME = $table_alias.".VALUE";
$arFields[$FIELD_NAME] = array(
"TABLE_ALIAS" => $table_alias,
"FIELD_NAME" => $TABLE_FIELD_NAME,
"FIELD_TYPE" => $arType["BASE_TYPE"],
"MULTIPLE" => $arField["MULTIPLE"],
"JOIN" => $arField["MULTIPLE"]=="N"?
"INNER JOIN b_uts_".$this->entity_id." ".$table_alias." ON ".$table_alias.".VALUE_ID = ".$ID:
"INNER JOIN b_utm_".$this->entity_id." ".$table_alias." ON ".$table_alias.".FIELD_ID = ".$arField["ID"]." AND ".$table_alias.".VALUE_ID = ".$ID,
"LEFT_JOIN" => $arField["MULTIPLE"]=="N"?
"LEFT JOIN b_uts_".$this->entity_id." ".$table_alias." ON ".$table_alias.".VALUE_ID = ".$ID:
"LEFT JOIN b_utm_".$this->entity_id." ".$table_alias." ON ".$table_alias.".FIELD_ID = ".$arField["ID"]." AND ".$table_alias.".VALUE_ID = ".$ID,
);
if($arType["BASE_TYPE"]=="enum")
{
$arFields[$FIELD_NAME."_VALUE"] = array(
"TABLE_ALIAS" => $table_alias."EN",
"FIELD_NAME" => $table_alias."EN.VALUE",
"FIELD_TYPE" => "string",
"MULTIPLE" => $arField["MULTIPLE"],
"JOIN" => $arField["MULTIPLE"]=="N"?
"INNER JOIN b_uts_".$this->entity_id." ".$table_alias."E ON ".$table_alias."E.VALUE_ID = ".$ID."
INNER JOIN b_user_field_enum ".$table_alias."EN ON ".$table_alias."EN.ID = ".$table_alias."E.".$FIELD_NAME:
"INNER JOIN b_utm_".$this->entity_id." ".$table_alias."E ON ".$table_alias."E.FIELD_ID = ".$arField["ID"]." AND ".$table_alias."E.VALUE_ID = ".$ID."
INNER JOIN b_user_field_enum ".$table_alias."EN ON ".$table_alias."EN.ID = ".$table_alias."E.VALUE_INT",
"LEFT_JOIN" => $arField["MULTIPLE"]=="N"?
"LEFT JOIN b_uts_".$this->entity_id." ".$table_alias."E ON ".$table_alias."E.VALUE_ID = ".$ID."
LEFT JOIN b_user_field_enum ".$table_alias."EN ON ".$table_alias."EN.ID = ".$table_alias."E.".$FIELD_NAME:
"LEFT JOIN b_utm_".$this->entity_id." ".$table_alias."E ON ".$table_alias."E.FIELD_ID = ".$arField["ID"]." AND ".$table_alias."E.VALUE_ID = ".$ID."
LEFT JOIN b_user_field_enum ".$table_alias."EN ON ".$table_alias."EN.ID = ".$table_alias."E.VALUE_INT",
);
}
}
$this->obWhere->SetFields($arFields);
}
function GetByFilename($FILENAME, $SITE_ID, $arFilter = false)
{
if (!is_array($arFilter)) {
$arFilter = array("!STATUS_ID" => 1);
}
$obQueryWhere = new CSQLWhere();
$obQueryWhere->SetFields(array("STATUS_ID" => array("TABLE_ALIAS" => "D", "FIELD_NAME" => "D.STATUS_ID", "FIELD_TYPE" => "int", "JOIN" => false)));
$strSqlWhere = $obQueryWhere->GetQuery($arFilter);
$err_mess = CWorkflowStatus::err_mess() . "<br>Function: GetByFilename<br>Line: ";
global $DB, $USER;
$MAX_LOCK = intval(COption::GetOptionString("workflow", "MAX_LOCK_TIME", "60"));
$uid = intval($USER->GetID());
$strSql = "\n\t\t\tSELECT\n\t\t\t\tD.*,\n\t\t\t\t" . $DB->DateToCharFunction("D.DATE_ENTER") . " DATE_ENTER,\n\t\t\t\t" . $DB->DateToCharFunction("D.DATE_MODIFY") . " DATE_MODIFY,\n\t\t\t\t" . $DB->DateToCharFunction("D.DATE_LOCK") . " DATE_LOCK,\n\t\t\t\tconcat('(',UM.LOGIN,') ',ifnull(UM.NAME,''),' ',ifnull(UM.LAST_NAME,'')) MUSER_NAME,\n\t\t\t\tconcat('(',UE.LOGIN,') ',ifnull(UE.NAME,''),' ',ifnull(UE.LAST_NAME,'')) EUSER_NAME,\n\t\t\t\tconcat('(',UL.LOGIN,') ',ifnull(UL.NAME,''),' ',ifnull(UL.LAST_NAME,'')) LUSER_NAME,\n\t\t\t\tS.TITLE STATUS_TITLE,\n\t\t\t\tif (D.DATE_LOCK is null, 'green',\n\t\t\t\t\tif(DATE_ADD(D.DATE_LOCK, interval {$MAX_LOCK} MINUTE)<now(), 'green',\n\t\t\t\t\t\tif(D.LOCKED_BY={$uid}, 'yellow', 'red'))) LOCK_STATUS\n\t\t\tFROM\n\t\t\t\tb_workflow_document D\n\t\t\t\tLEFT JOIN b_user UM ON (UM.ID = D.MODIFIED_BY)\n\t\t\t\tLEFT JOIN b_user UE ON (UE.ID = D.ENTERED_BY)\n\t\t\t\tLEFT JOIN b_user UL ON (UL.ID = D.LOCKED_BY)\n\t\t\t\tLEFT JOIN b_workflow_status S ON (S.ID = D.STATUS_ID)\n\t\t\tWHERE\n\t\t\t\tSITE_ID = '" . $DB->ForSql($SITE_ID, 2) . "'\n\t\t\t\tAND D.FILENAME = '" . $DB->ForSql($FILENAME, 255) . "'\n\t\t\t\t" . ($strSqlWhere ? "AND " . $strSqlWhere : "") . "\n\t\t";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
return $res;
}
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);
}
/**
* @param array $arOrder
* @param array $arFilter
* @param array $arSelect
* @return CDBResult
* Obsolete. Always select all record from database. Please use GetListEx instead.
*/
public static function GetList($arOrder = array('DATE_CREATE' => 'DESC'), $arFilter = array(), $arSelect = array(), $nPageTop = false)
{
global $DB, $USER_FIELD_MANAGER;
// fields
$arFields = array('ID' => 'L.ID', 'COMMENTS' => 'L.COMMENTS', 'ADDITIONAL_INFO' => 'L.ADDITIONAL_INFO', 'LOCATION_ID' => 'L.LOCATION_ID', 'TITLE' => 'L.TITLE', 'LEAD_ID' => 'L.LEAD_ID', 'COMPANY_ID' => 'L.COMPANY_ID', 'COMPANY_TITLE' => 'C.TITLE', 'CONTACT_ID' => 'L.CONTACT_ID', 'CONTACT_FULL_NAME' => 'CT.FULL_NAME', 'STAGE_ID' => 'L.STAGE_ID', 'CLOSED' => 'L.CLOSED', 'TYPE_ID' => 'L.TYPE_ID', 'PRODUCT_ID' => 'L.PRODUCT_ID', 'PROBABILITY' => 'L.PROBABILITY', 'OPPORTUNITY' => 'L.OPPORTUNITY', 'TAX_VALUE' => 'L.TAX_VALUE', 'CURRENCY_ID' => 'L.CURRENCY_ID', 'OPPORTUNITY_ACCOUNT' => 'L.OPPORTUNITY_ACCOUNT', 'TAX_VALUE_ACCOUNT' => 'L.TAX_VALUE_ACCOUNT', 'ACCOUNT_CURRENCY_ID' => 'L.ACCOUNT_CURRENCY_ID', 'BEGINDATE' => $DB->DateToCharFunction('L.BEGINDATE'), 'CLOSEDATE' => $DB->DateToCharFunction('L.CLOSEDATE'), 'EVENT_ID' => 'L.EVENT_ID', 'EVENT_DATE' => $DB->DateToCharFunction('L.EVENT_DATE'), 'EVENT_DESCRIPTION' => 'L.EVENT_DESCRIPTION', 'ASSIGNED_BY' => 'L.ASSIGNED_BY_ID', 'ASSIGNED_BY_ID' => 'L.ASSIGNED_BY_ID', 'CREATED_BY' => 'L.CREATED_BY_ID', 'CREATED_BY_ID' => 'L.CREATED_BY_ID', 'MODIFY_BY' => 'L.MODIFY_BY_ID', 'MODIFY_BY_ID' => 'L.MODIFY_BY_ID', 'DATE_CREATE' => $DB->DateToCharFunction('L.DATE_CREATE'), 'DATE_MODIFY' => $DB->DateToCharFunction('L.DATE_MODIFY'), 'OPENED' => 'L.OPENED', 'EXCH_RATE' => 'L.EXCH_RATE', 'ORIGINATOR_ID' => 'L.ORIGINATOR_ID', 'ORIGIN_ID' => 'L.ORIGIN_ID', 'ASSIGNED_BY_LOGIN' => 'U.LOGIN', 'ASSIGNED_BY_NAME' => 'U.NAME', 'ASSIGNED_BY_LAST_NAME' => 'U.LAST_NAME', 'ASSIGNED_BY_SECOND_NAME' => 'U.SECOND_NAME', 'CREATED_BY_LOGIN' => 'U2.LOGIN', 'CREATED_BY_NAME' => 'U2.NAME', 'CREATED_BY_LAST_NAME' => 'U2.LAST_NAME', 'CREATED_BY_SECOND_NAME' => 'U2.SECOND_NAME', 'MODIFY_BY_LOGIN' => 'U3.LOGIN', 'MODIFY_BY_NAME' => 'U3.NAME', 'MODIFY_BY_LAST_NAME' => 'U3.LAST_NAME', 'MODIFY_BY_SECOND_NAME' => 'U3.SECOND_NAME');
$arSqlSelect = array();
$sSqlJoin = '';
if (count($arSelect) == 0) {
$arSelect = array_merge(array_keys($arFields), array('UF_*'));
}
$obQueryWhere = new CSQLWhere();
$arFilterField = $arSelect;
foreach ($arFilter as $sKey => $sValue) {
$arField = $obQueryWhere->MakeOperation($sKey);
$arFilterField[] = $arField['FIELD'];
}
if (in_array('ASSIGNED_BY_LOGIN', $arFilterField) || in_array('ASSIGNED_BY', $arFilterField)) {
$arSelect[] = 'ASSIGNED_BY_LOGIN';
$arSelect[] = 'ASSIGNED_BY_NAME';
$arSelect[] = 'ASSIGNED_BY_LAST_NAME';
$arSelect[] = 'ASSIGNED_BY_SECOND_NAME';
$sSqlJoin .= ' LEFT JOIN b_user U ON L.ASSIGNED_BY_ID = U.ID ';
}
if (in_array('CREATED_BY_LOGIN', $arFilterField)) {
$arSelect[] = 'CREATED_BY';
$arSelect[] = 'CREATED_BY_LOGIN';
$arSelect[] = 'CREATED_BY_NAME';
$arSelect[] = 'CREATED_BY_LAST_NAME';
$arSelect[] = 'CREATED_BY_SECOND_NAME';
$sSqlJoin .= ' LEFT JOIN b_user U2 ON L.CREATED_BY_ID = U2.ID ';
}
if (in_array('MODIFY_BY_LOGIN', $arFilterField)) {
$arSelect[] = 'MODIFY_BY';
$arSelect[] = 'MODIFY_BY_LOGIN';
$arSelect[] = 'MODIFY_BY_NAME';
$arSelect[] = 'MODIFY_BY_LAST_NAME';
$arSelect[] = 'MODIFY_BY_SECOND_NAME';
$sSqlJoin .= ' LEFT JOIN b_user U3 ON L.MODIFY_BY_ID = U3.ID ';
}
if (in_array('COMPANY_ID', $arFilterField) || in_array('COMPANY_TITLE', $arFilterField)) {
$arSelect[] = 'COMPANY_ID';
$arSelect[] = 'COMPANY_TITLE';
$sSqlJoin .= ' LEFT JOIN b_crm_company C ON L.COMPANY_ID = C.ID ';
}
if (in_array('CONTACT_ID', $arFilterField) || in_array('CONTACT_FULL_NAME', $arFilterField)) {
$arSelect[] = 'CONTACT_ID';
$arSelect[] = 'CONTACT_FULL_NAME';
$sSqlJoin .= ' LEFT JOIN b_crm_contact CT ON L.CONTACT_ID = CT.ID ';
}
foreach ($arSelect as $field) {
$field = strtoupper($field);
if (array_key_exists($field, $arFields)) {
$arSqlSelect[$field] = $arFields[$field] . ($field != '*' ? ' AS ' . $field : '');
}
}
if (!isset($arSqlSelect['ID'])) {
$arSqlSelect['ID'] = $arFields['ID'];
}
$sSqlSelect = implode(",\n", $arSqlSelect);
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity(self::$sUFEntityID, 'L.ID');
$obUserFieldsSql->SetSelect($arSelect);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
$arSqlSearch = array();
// check permissions
$sSqlPerm = '';
if (!CCrmPerms::IsAdmin() && (!array_key_exists('CHECK_PERMISSIONS', $arFilter) || $arFilter['CHECK_PERMISSIONS'] !== 'N')) {
$arPermType = array();
if (!isset($arFilter['PERMISSION'])) {
$arPermType[] = 'READ';
} else {
$arPermType = is_array($arFilter['PERMISSION']) ? $arFilter['PERMISSION'] : array($arFilter['PERMISSION']);
}
$sSqlPerm = self::BuildPermSql('L', $arPermType);
if ($sSqlPerm === false) {
$CDBResult = new CDBResult();
$CDBResult->InitFromArray(array());
return $CDBResult;
}
if (strlen($sSqlPerm) > 0) {
$sSqlPerm = ' AND ' . $sSqlPerm;
}
}
// where
$arWhereFields = array('ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'LEAD_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.LEAD_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'CONTACT_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CONTACT_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'CONTACT_FULL_NAME' => array('TABLE_ALIAS' => 'CT', 'FIELD_NAME' => 'CT.FULL_NAME', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'COMPANY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.COMPANY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'COMPANY_TITLE' => array('TABLE_ALIAS' => 'C', 'FIELD_NAME' => 'C.TITLE', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'STATE_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.STATE_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'STAGE_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.STAGE_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'TYPE_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TYPE_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'PRODUCT_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.PRODUCT_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'CURRENCY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CURRENCY_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'OPPORTUNITY' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.OPPORTUNITY', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'TAX_VALUE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TAX_VALUE', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'ACCOUNT_CURRENCY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ACCOUNT_CURRENCY_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'OPPORTUNITY_ACCOUNT' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.OPPORTUNITY_ACCOUNT', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'TAX_VALUE_ACCOUNT' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TAX_VALUE_ACCOUNT', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'TITLE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TITLE', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'CLOSED' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CLOSED', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'COMMENTS' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.COMMENTS', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'ADDITIONAL_INFO' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ADDITIONAL_INFO', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'LOCATION_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.LOCATION_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'DATE_CREATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.DATE_CREATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'BEGINDATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.BEGINDATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'CLOSEDATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CLOSEDATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'EVENT_DATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.EVENT_DATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'DATE_MODIFY' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.DATE_MODIFY', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'PROBABILITY' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.PROBABILITY', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'EVENT_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.EVENT_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'CREATED_BY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CREATED_BY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'ASSIGNED_BY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ASSIGNED_BY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'OPENED' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.OPENED', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'MODIFY_BY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.MODIFY_BY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'EXCH_RATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.EXCH_RATE', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'ORIGINATOR_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ORIGINATOR_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'ORIGIN_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ORIGIN_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false));
$obQueryWhere->SetFields($arWhereFields);
if (!is_array($arFilter)) {
$arFilter = array();
}
$sQueryWhereFields = $obQueryWhere->GetQuery($arFilter);
$sSqlSearch = '';
foreach ($arSqlSearch as $r) {
if (strlen($r) > 0) {
$sSqlSearch .= "\n\t\t\t\tAND ({$r}) ";
}
}
$CCrmUserType = new CCrmUserType($GLOBALS['USER_FIELD_MANAGER'], self::$sUFEntityID);
$CCrmUserType->ListPrepareFilter($arFilter);
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$sSqlSearch .= "\n\t\t\t\tAND ({$r}) ";
}
if (!empty($sQueryWhereFields)) {
$sSqlSearch .= "\n\t\t\t\tAND ({$sQueryWhereFields}) ";
}
$arFieldsOrder = array('ASSIGNED_BY' => 'L.ASSIGNED_BY_ID', 'CREATED_BY' => 'L.CREATED_BY_ID', 'MODIFY_BY' => 'L.MODIFY_BY_ID', 'DATE_CREATE' => 'L.DATE_CREATE', 'DATE_MODIFY' => 'L.DATE_MODIFY');
// order
$arSqlOrder = array();
if (!is_array($arOrder)) {
$arOrder = array('DATE_CREATE' => 'DESC');
}
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtolower($order);
if ($order != 'asc') {
$order = 'desc';
}
if (isset($arFieldsOrder[$by])) {
$arSqlOrder[$by] = " {$arFieldsOrder[$by]} {$order} ";
} else {
if (isset($arFields[$by]) && $by != 'ADDRESS') {
$arSqlOrder[$by] = " L.{$by} {$order} ";
} else {
if ($s = $obUserFieldsSql->GetOrder($by)) {
$arSqlOrder[$by] = " {$s} {$order} ";
} else {
$by = 'date_create';
$arSqlOrder[$by] = " L.DATE_CREATE {$order} ";
}
}
}
}
if (count($arSqlOrder) > 0) {
$sSqlOrder = "\n\t\t\t\tORDER BY " . implode(', ', $arSqlOrder);
} else {
$sSqlOrder = '';
}
$sSql = "\n\t\t\tSELECT\n\t\t\t\t{$sSqlSelect}\n\t\t\t\t{$obUserFieldsSql->GetSelect()}\n\t\t\tFROM\n\t\t\t\tb_crm_deal L {$sSqlJoin}\n\t\t\t\t{$obUserFieldsSql->GetJoin('L.ID')}\n\t\t\tWHERE\n\t\t\t\t1=1 {$sSqlSearch}\n\t\t\t\t{$sSqlPerm}\n\t\t\t{$sSqlOrder}";
if ($nPageTop !== false) {
$nPageTop = (int) $nPageTop;
$sSql = $DB->TopSql($sSql, $nPageTop);
}
$obRes = $DB->Query($sSql, false, 'FILE: ' . __FILE__ . '<br /> LINE: ' . __LINE__);
$obRes->SetUserFields($USER_FIELD_MANAGER->GetUserFields(self::$sUFEntityID));
return $obRes;
}
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;
}
function GetList($arOrder = array(), $arFilter = array(), $bCnt = false)
{
global $DB;
static $arFields = array("ID" => array("FIELD_NAME" => "T.ID", "FIELD_TYPE" => "int"), "TIMESTAMP_X" => array("FIELD_NAME" => "T.TIMESTAMP_X", "FIELD_TYPE" => "datetime"), "DATE_CREATE" => array("FIELD_NAME" => "T.DATE_CREATE", "FIELD_TYPE" => "datetime"), "TASK_ID" => array("FIELD_NAME" => "T.TASK_ID", "FIELD_TYPE" => "string"), "CONTROLLER_MEMBER_ID" => array("FIELD_NAME" => "T.CONTROLLER_MEMBER_ID", "FIELD_TYPE" => "int"), "CONTROLLER_MEMBER_NAME" => array("FIELD_NAME" => "M.NAME", "FIELD_TYPE" => "string"), "CONTROLLER_MEMBER_URL" => array("FIELD_NAME" => "M.URL", "FIELD_TYPE" => "string"), "STATUS" => array("FIELD_NAME" => "T.STATUS", "FIELD_TYPE" => "string"), "DATE_EXECUTE" => array("FIELD_NAME" => "T.DATE_EXECUTE", "FIELD_TYPE" => "datetime"));
$obWhere = new CSQLWhere();
$obWhere->SetFields($arFields);
$arFilterNew = array();
foreach ($arFilter as $k => $value) {
if (is_array($value) || strlen($value) > 0 || $value === false) {
$arFilterNew[$k] = $value;
}
}
$strWhere = $obWhere->GetQuery($arFilterNew);
if ($bCnt) {
$strSql = "SELECT COUNT('x') as C, MIN(T.ID) as MIN_ID, MAX(T.ID) as MAX_ID ";
} else {
$strSql = "SELECT T.*, " . "\tM.NAME as CONTROLLER_MEMBER_NAME, M.URL as CONTROLLER_MEMBER_URL, " . "\t" . $DB->DateToCharFunction("T.TIMESTAMP_X") . "\tas TIMESTAMP_X, " . "\t" . $DB->DateToCharFunction("T.DATE_EXECUTE") . "\tas DATE_EXECUTE, " . "\t" . $DB->DateToCharFunction("T.DATE_CREATE") . "\tas DATE_CREATE ";
}
$strSql .= "FROM b_controller_task T " . "\tINNER JOIN b_controller_member M ON T.CONTROLLER_MEMBER_ID=M.ID " . (strlen($strWhere) <= 0 ? "" : "WHERE " . $strWhere) . " " . CControllerAgent::_OrderBy($arOrder, $arFields);
$dbr = $DB->Query($strSql);
$dbr->is_filtered = strlen($strWhere) > 0;
return $dbr;
}
function GetList($arOrder = false, $arFilter = false, $arSelect = false, $bGroup = false)
{
$DB = CDatabase::GetModuleConnection('search');
static $arDefSelect = array(
"ID",
"TIMESTAMP_X",
"SITE_ID",
"RESULT_COUNT",
"PAGES",
"PHRASE",
"TAGS",
"URL_TO",
"URL_TO_404",
"URL_TO_SITE_ID",
"STAT_SESS_ID",
);
if(!is_array($arSelect))
$arSelect = array();
if(count($arSelect) < 1)
$arSelect =$arDefSelect;
if(!is_array($arOrder))
$arOrder = array();
if(count($arOrder) < 1)
$arOrder = array(
"ID" => "DESC",
);
$arQueryOrder = array();
foreach($arOrder as $strColumn => $strDirection)
{
$strColumn = strtoupper($strColumn);
$strDirection = strtoupper($strDirection)=="ASC"? "ASC": "DESC";
if(in_array($strColumn, $arDefSelect))
{
$arSelect[] = $strColumn;
if($strColumn == "TIMESTAMP_X")
$arQueryOrder[$strColumn] = "TMP_TS ".$strDirection;
else
$arQueryOrder[$strColumn] = $strColumn." ".$strDirection;
}
elseif($strColumn == "COUNT" && $bGroup)
{
$arSelect[] = $strColumn;
$arQueryOrder[$strColumn] = $strColumn." ".$strDirection;
}
}
$arQueryGroup = array();
$arQuerySelect = array();
foreach($arSelect as $strColumn)
{
$strColumn = strtoupper($strColumn);
if(in_array($strColumn, $arDefSelect))
{
if($strColumn == "TIMESTAMP_X")
{
$arQuerySelect["TMP_TS"] = "sph.".$strColumn." TMP_TS";
$arQuerySelect[$strColumn] = $DB->DateToCharFunction("sph.".$strColumn, "FULL")." ".$strColumn;
}
else
{
$arQuerySelect[$strColumn] = "sph.".$strColumn;
}
if($bGroup)
$arQueryGroup[$strColumn] = "sph.".$strColumn;
}
elseif($strColumn == "COUNT" && $bGroup)
{
$arQuerySelect[$strColumn] = "count(*) ".$strColumn;
}
}
$obQueryWhere = new CSQLWhere;
$obQueryWhere->SetFields(array(
"ID" => array(
"TABLE_ALIAS" => "sph",
"FIELD_NAME" => "sph.ID",
"FIELD_TYPE" => "int", //int, double, file, enum, int, string, date, datetime
"JOIN" => false,
),
"PHRASE" => array(
"TABLE_ALIAS" => "sph",
"FIELD_NAME" => "sph.PHRASE",
"FIELD_TYPE" => "string",
"JOIN" => false,
),
"TAGS" => array(
"TABLE_ALIAS" => "sph",
"FIELD_NAME" => "sph.TAGS",
"FIELD_TYPE" => "string",
"JOIN" => false,
),
"TIMESTAMP_X" => array(
"TABLE_ALIAS" => "sph",
"FIELD_NAME" => "sph.TIMESTAMP_X",
"FIELD_TYPE" => "datetime",
"JOIN" => false,
),
"SITE_ID" => array(
"TABLE_ALIAS" => "sph",
"FIELD_NAME" => "sph.SITE_ID",
"FIELD_TYPE" => "string",
"JOIN" => false,
),
"URL_TO" => array(
"TABLE_ALIAS" => "sph",
"FIELD_NAME" => "sph.URL_TO",
"FIELD_TYPE" => "string",
"JOIN" => false,
),
"URL_TO_404" => array(
"TABLE_ALIAS" => "sph",
"FIELD_NAME" => "sph.URL_TO_404",
"FIELD_TYPE" => "string",
"JOIN" => false,
),
"STAT_SESS_ID" => array(
"TABLE_ALIAS" => "sph",
"FIELD_NAME" => "sph.STAT_SESS_ID",
"FIELD_TYPE" => "int",
"JOIN" => false,
),
));
if(count($arQuerySelect) < 1)
$arQuerySelect = array("ID"=>"sph.ID");
$strSql = "
SELECT
".implode(", ", $arQuerySelect)."
FROM
b_search_phrase sph
";
if(!is_array($arFilter))
$arFilter = array();
if($strQueryWhere = $obQueryWhere->GetQuery($arFilter))
{
$strSql .= "
WHERE
".$strQueryWhere."
";
}
if($bGroup && count($arQueryGroup) > 0)
{
$strSql .= "
GROUP BY
".implode(", ", $arQueryGroup)."
";
}
if(count($arQueryOrder) > 0)
{
$strSql .= "
ORDER BY
".implode(", ", $arQueryOrder)."
";
}
return $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
}
public static function GetList($arOrder = Array(), $arFilter = Array())
{
global $DB;
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 = $obWhere->GetQuery($arFilterNew);
$strSql =
"SELECT G.*, ".
" UC.LOGIN as CREATED_BY_LOGIN, UC.NAME as CREATED_BY_NAME, UC.LAST_NAME as CREATED_BY_LAST_NAME, ".
" UM.LOGIN as MODIFIED_BY_LOGIN, UM.NAME as MODIFIED_BY_NAME, UM.LAST_NAME as MODIFIED_BY_LAST_NAME, ".
" ".$DB->DateToCharFunction("G.TIMESTAMP_X")." as TIMESTAMP_X, ".
" ".$DB->DateToCharFunction("G.DATE_CREATE")." as DATE_CREATE ".
"FROM b_controller_group G ".
" LEFT JOIN b_user UC ON UC.ID=G.CREATED_BY ".
" LEFT JOIN b_user UM ON UM.ID=G.MODIFIED_BY ".
(strlen($strWhere)<=0 ? "" : "WHERE ".$strWhere)." ".
CControllerAgent::_OrderBy($arOrder, $arFields);
$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(
"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 "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 "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 "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", //int, double, file, enum, int, string, date, datetime
"JOIN" => false,
//"LEFT_JOIN" => "lt",
),
"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");
$strSql = "
SELECT
".implode(", ", $arQuerySelect)."
FROM
b_perf_hit h
";
if(!is_array($arFilter))
$arFilter = array();
if($strQueryWhere = $obQueryWhere->GetQuery($arFilter))
{
$strSql .= "
WHERE
".$strQueryWhere."
";
}
if($bGroup && count($arQueryGroup) > 0)
{
$strSql .= "
GROUP BY
".implode(", ", $arQueryGroup)."
";
$obQueryHaving = new CSQLWhere;
$obQueryHaving->SetFields(array(
"COUNT" => array(
"TABLE_ALIAS" => "",
"FIELD_NAME" => "COUNT(h.ID)",
"FIELD_TYPE" => "int",
"JOIN" => false,
),
));
$strQueryHaving = $obQueryHaving->GetQuery($arFilter);
if($strQueryHaving)
{
$strSql .= "
HAVING
".$strQueryHaving."
";
}
}
if(count($arQueryOrder) > 0)
{
$strSql .= "
ORDER BY
".implode(", ", $arQueryOrder)."
";
}
//echo "<pre>",htmlspecialcharsbx($strSql),"</pre><hr>";
return $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
}
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;
}
/**
* @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));
}
/**
* @param array[string]string $arOrder
* @param array[string]string $arFilter
* @return CDBResult
*/
public static function GetList($arOrder = array(), $arFilter = array())
{
$DB = CDatabase::GetModuleConnection('statistic');
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 "COUNTRY_ID":
case "COUNTRY_SHORT_NAME":
case "COUNTRY_NAME":
case "REGION_NAME":
case "CITY_NAME":
$arQueryOrder[$strColumn] = $strColumn . " " . $strDirection;
break;
case "CITY":
$arQueryOrder["COUNTRY_ID"] = "COUNTRY_ID " . $strDirection;
$arQueryOrder["REGION_NAME"] = "REGION_NAME " . $strDirection;
$arQueryOrder["CITY_NAME"] = "CITY_NAME " . $strDirection;
break;
default:
break;
}
}
$obQueryWhere = new CSQLWhere();
$obQueryWhere->SetFields(array("CITY_ID" => array("TABLE_ALIAS" => "CITY", "FIELD_NAME" => "CITY.ID", "FIELD_TYPE" => "int", "JOIN" => ""), "COUNTRY_ID" => array("TABLE_ALIAS" => "CITY", "FIELD_NAME" => "CITY.COUNTRY_ID", "FIELD_TYPE" => "string", "JOIN" => ""), "COUNTRY_SHORT_NAME" => array("TABLE_ALIAS" => "C", "FIELD_NAME" => "C.SHORT_NAME", "FIELD_TYPE" => "string", "JOIN" => ""), "COUNTRY_NAME" => array("TABLE_ALIAS" => "C", "FIELD_NAME" => "C.NAME", "FIELD_TYPE" => "string", "JOIN" => ""), "REGION_NAME" => array("TABLE_ALIAS" => "CITY", "FIELD_NAME" => "CITY.REGION", "FIELD_TYPE" => "string", "JOIN" => ""), "CITY_NAME" => array("TABLE_ALIAS" => "CITY", "FIELD_NAME" => "CITY.NAME", "FIELD_TYPE" => "string", "JOIN" => "")));
$strSql = "\n\t\t\tSELECT\n\t\t\t\tCITY.ID CITY_ID\n\t\t\t\t,CITY.COUNTRY_ID\n\t\t\t\t,C.SHORT_NAME COUNTRY_SHORT_NAME\n\t\t\t\t,C.NAME COUNTRY_NAME\n\t\t\t\t,CITY.REGION REGION_NAME\n\t\t\t\t,CITY.NAME CITY_NAME\n\t\t\tFROM\n\t\t\t\tb_stat_city CITY\n\t\t\t\tINNER JOIN b_stat_country C on C.ID = CITY.COUNTRY_ID\n\t\t";
$strQueryWhere = $obQueryWhere->GetQuery($arFilter);
if (strlen($strQueryWhere) > 0) {
$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, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
protected static function PrepareSql(&$arFields, $arOrder, &$arFilter, $arSelectFields = false, $arNavStartParams = false)
{
global $DB;
$strSqlSelect = "";
$strSqlFrom = "";
$strSqlWhere = "";
$strSqlGroupBy = "";
$strSqlOrderBy = "";
$strSqlLimit = "";
$arGroupByFunct = array();
$arAlreadyJoined = array();
// SELECT -->
$arFieldsKeys = array_keys($arFields);
if (isset($arSelectFields) && !is_array($arSelectFields) && is_string($arSelectFields) && strlen($arSelectFields) > 0 && array_key_exists($arSelectFields, $arFields)) {
$arSelectFields = array($arSelectFields);
}
if (!isset($arSelectFields) || !is_array($arSelectFields) || count($arSelectFields) <= 0 || in_array("*", $arSelectFields)) {
foreach ($arFields as $key => $arField) {
if (isset($arField["WHERE_ONLY"]) && $arField["WHERE_ONLY"] == "Y") {
continue;
}
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
if ($arField["FIELD_TYPE"] == "datetime") {
if ((strtoupper($DB->type) == "ORACLE" || strtoupper($DB->type) == "MSSQL") && array_key_exists($key, $arOrder)) {
$strSqlSelect .= $arField["FIELD_NAME"] . " as " . $key . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arField["FIELD_NAME"], "FULL") . " as " . $key;
} elseif ($arField["FIELD_TYPE"] == "date") {
if ((strtoupper($DB->type) == "ORACLE" || strtoupper($DB->type) == "MSSQL") && array_key_exists($key, $arOrder)) {
$strSqlSelect .= $arField["FIELD_NAME"] . " as " . $key . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arField["FIELD_NAME"], "SHORT") . " as " . $key;
} else {
$strSqlSelect .= $arField["FIELD_NAME"] . " as " . $key;
}
if (isset($arField["FROM"]) && strlen($arField["FROM"]) > 0 && !in_array($arField["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arField["FROM"];
$arAlreadyJoined[] = $arField["FROM"];
}
}
} else {
foreach ($arSelectFields as $key => $val) {
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields)) {
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
if (in_array($key, $arGroupByFunct)) {
$strSqlSelect .= $key . "(" . $arFields[$val]["FIELD_NAME"] . ") as " . $val;
} else {
if ($arFields[$val]["FIELD_TYPE"] == "datetime") {
if ((strtoupper($DB->type) == "ORACLE" || strtoupper($DB->type) == "MSSQL") && array_key_exists($val, $arOrder)) {
$strSqlSelect .= $arFields[$val]["FIELD_NAME"] . " as " . $val . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL") . " as " . $val;
} elseif ($arFields[$val]["FIELD_TYPE"] == "date") {
if ((strtoupper($DB->type) == "ORACLE" || strtoupper($DB->type) == "MSSQL") && array_key_exists($val, $arOrder)) {
$strSqlSelect .= $arFields[$val]["FIELD_NAME"] . " as " . $val . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD_NAME"], "SHORT") . " as " . $val;
} else {
$strSqlSelect .= $arFields[$val]["FIELD_NAME"] . " as " . $val;
}
}
if (isset($arFields[$val]["FROM"]) && strlen($arFields[$val]["FROM"]) > 0 && !in_array($arFields[$val]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
// <-- SELECT
// WHERE -->
$obWhere = new CSQLWhere();
$obWhere->SetFields($arFields);
$strSqlWhere = $obWhere->GetQuery($arFilter);
// ORDER BY -->
$arSqlOrder = array();
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtoupper($order);
if ($order != "ASC") {
$order = "DESC";
} else {
$order = "ASC";
}
if (array_key_exists($by, $arFields)) {
$arSqlOrder[] = " " . $arFields[$by]["FIELD_NAME"] . " " . $order . " ";
if (isset($arFields[$by]["FROM"]) && strlen($arFields[$by]["FROM"]) > 0 && !in_array($arFields[$by]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$by]["FROM"];
$arAlreadyJoined[] = $arFields[$by]["FROM"];
}
}
}
$strSqlOrderBy = "";
DelDuplicateSort($arSqlOrder);
$cnt = count($arSqlOrder);
for ($i = 0; $i < $cnt; $i++) {
if (strlen($strSqlOrderBy) > 0) {
$strSqlOrderBy .= ", ";
}
if (strtoupper($DB->type) == "ORACLE") {
if (substr($arSqlOrder[$i], -3) == "ASC") {
$strSqlOrderBy .= $arSqlOrder[$i] . " NULLS FIRST";
} else {
$strSqlOrderBy .= $arSqlOrder[$i] . " NULLS LAST";
}
} else {
$strSqlOrderBy .= $arSqlOrder[$i];
}
}
// <-- ORDER BY
if (is_array($arNavStartParams) && IntVal($arNavStartParams["nTopCount"]) > 0) {
$dbType = strtoupper($DB->type);
switch ($dbType) {
case "MSSQL":
$strSqlSelect = "TOP " . $arNavStartParams["nTopCount"] . " " . $strSqlSelect;
break;
case "ORACLE":
if (strlen($strSqlWhere) > 0) {
$strSqlWhere .= " AND ";
}
$strSqlWhere .= "ROWNUM<=" . $arNavStartParams["nTopCount"];
break;
case "MYSQL":
$strSqlLimit .= "LIMIT " . $arNavStartParams["nTopCount"];
}
}
return array("SELECT" => $strSqlSelect, "FROM" => $strSqlFrom, "WHERE" => $strSqlWhere, "GROUPBY" => $strSqlGroupBy, "ORDERBY" => $strSqlOrderBy, "LIMIT" => $strSqlLimit);
}
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__);
}
function GetLogList($arOrder = array(), $arFilter = array())
{
global $DB;
$arFields = array('COUPON_ID' => array('TABLE_ALIAS' => 'C', 'FIELD_NAME' => 'C.ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'COUPON' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.COUPON', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'SUCCESS' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.SUCCESS', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'USER_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.USER_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'TIMESTAMP_X' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TIMESTAMP_X', 'FIELD_TYPE' => 'datetime', 'JOIN' => false));
$obQueryWhere = new CSQLWhere();
$obQueryWhere->SetFields($arFields);
$where = $obQueryWhere->GetQuery($arFilter);
$order = '';
if (is_array($arOrder)) {
foreach ($arOrder as $k => $v) {
if (array_key_exists($k, $arFields)) {
$v = strtoupper($v);
if ($v != 'DESC') {
$v = 'ASC';
}
if (strlen($order) > 0) {
$order .= ', ';
}
$order .= $arFields[$k]['FIELD_NAME'] . ' ' . $v;
}
}
}
$strQuery = "SELECT C.ID COUPON_ID, L.COUPON COUPON,\n\t\tL.USER_ID USER_ID, L.SUCCESS SUCCESS, L.AFTER_COUNT AFTER_COUNT,\n\t\tL.SESSION_ID SESSION_ID, L.GUEST_ID GUEST_ID,\n\t\tU.LOGIN LOGIN, U.NAME FIRST_NAME, U.LAST_NAME LAST_NAME,\n\t\t" . $DB->DateToCharFunction('L.TIMESTAMP_X') . " TIMESTAMP_X\n\t\tFROM b_ticket_supercoupons_log L\n\t\tLEFT JOIN b_ticket_supercoupons C ON (L.COUPON_ID = C.ID)\n\t\tLEFT JOIN b_user U ON (L.USER_ID IS NOT NULL AND L.USER_ID = U.ID)";
if (strlen($where) > 0) {
$strQuery .= ' WHERE ' . $where;
}
if (strlen($order) > 0) {
$strQuery .= ' ORDER BY ' . $order;
}
return $DB->Query($strQuery, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
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__);
}
public static function GetList($arSelect, $arFilter, $arOrder, $bGroup = false)
{
global $DB;
if (!is_array($arSelect)) {
$arSelect = array();
}
if (count($arSelect) < 1) {
$arSelect = array("ID", "HIT_ID", "ERRNO", "ERRFILE", "ERRLINE", "ERRSTR");
}
if (!is_array($arOrder)) {
$arOrder = array();
}
if (count($arOrder) < 1) {
$arOrder = array("HIT_ID" => "DESC", "ID" => "DESC");
}
$arQueryOrder = array();
foreach ($arOrder as $strColumn => $strDirection) {
$strColumn = strtoupper($strColumn);
$strDirection = strtoupper($strDirection) == "ASC" ? "ASC" : "DESC";
switch ($strColumn) {
case "ID":
case "HIT_ID":
case "ERRNO":
case "ERRFILE":
case "ERRLINE":
case "ERRSTR":
$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":
if (!$bGroup) {
$arQuerySelect[$strColumn] = "e." . $strColumn;
}
break;
case "ERRNO":
case "ERRFILE":
case "ERRLINE":
case "ERRSTR":
if ($bGroup) {
$arQueryGroup[$strColumn] = "e." . $strColumn;
}
$arQuerySelect[$strColumn] = "e." . $strColumn;
break;
case "COUNT":
if ($bGroup) {
$arQuerySelect[$strColumn] = "COUNT(e.ID) " . $strColumn;
}
break;
}
}
$obQueryWhere = new CSQLWhere();
$obQueryWhere->SetFields(array("HIT_ID" => array("TABLE_ALIAS" => "e", "FIELD_NAME" => "HIT_ID", "FIELD_TYPE" => "int", "JOIN" => false), "ERRNO" => array("TABLE_ALIAS" => "e", "FIELD_NAME" => "ERRNO", "FIELD_TYPE" => "int", "JOIN" => false), "ERRFILE" => array("TABLE_ALIAS" => "e", "FIELD_NAME" => "ERRFILE", "FIELD_TYPE" => "string", "JOIN" => false), "ERRSTR" => array("TABLE_ALIAS" => "e", "FIELD_NAME" => "ERRSTR", "FIELD_TYPE" => "string", "JOIN" => false), "ERRLINE" => array("TABLE_ALIAS" => "e", "FIELD_NAME" => "ERRLINE", "FIELD_TYPE" => "int", "JOIN" => false)));
if (count($arQuerySelect) < 1) {
$arQuerySelect = array("ID" => "e.ID");
}
$strSql = "\n\t\t\tSELECT\n\t\t\t" . implode(", ", $arQuerySelect) . "\n\t\t\tFROM\n\t\t\t\tb_perf_error e\n\t\t";
if (!is_array($arFilter)) {
$arFilter = array();
}
if ($strQueryWhere = $obQueryWhere->GetQuery($arFilter)) {
$strSql .= "\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 (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__);
}
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;
}