本文整理汇总了PHP中CSQLWhere类的典型用法代码示例。如果您正苦于以下问题：PHP CSQLWhere类的具体用法？PHP CSQLWhere怎么用？PHP CSQLWhere使用的例子？那么恭喜您, 这里精选的类代码示例或许可以为您提供帮助。
在下文中一共展示了CSQLWhere类的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的PHP代码示例。
示例1: GetList
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__);
}
}示例2: GetList
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__);
}示例3: GetList
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__);
}示例4: GetList
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;
}示例5: GetList
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__);
}示例6: GetList
/**
* @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));
}示例7: GetBestSellerList
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;
}示例8: GetList
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;
}示例9: GetList
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;
}示例10: GetList
/**
* @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);
}示例11: getJoinCswFields
protected function getJoinCswFields($reference)
{
$fields = array();
foreach ($reference as $k => $v) {
if ($k === 'LOGIC') {
continue;
}
if (is_numeric($k)) {
$fields = array_merge($fields, $this->getJoinCswFields($v));
} else {
// key
$csw_result = \CSQLWhere::makeOperation($k);
list($field, ) = array_values($csw_result);
$fields[$field] = array('TABLE_ALIAS' => 'alias', 'FIELD_NAME' => $field, 'FIELD_TYPE' => 'string', 'MULTIPLE' => '', 'JOIN' => '');
// no need to add values as csw fields
}
}
return $fields;
}示例12: GetList
/**
* @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) {
//.........这里部分代码省略.........
示例13: GetList
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));
}示例14: GetList
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;
}示例15: GetList
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);
}