本文整理汇总了PHP中CSQLWhere::SetFields方法的典型用法代码示例。如果您正苦于以下问题：PHP CSQLWhere::SetFields方法的具体用法？PHP CSQLWhere::SetFields怎么用？PHP CSQLWhere::SetFields使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类CSQLWhere的用法示例。
在下文中一共展示了CSQLWhere::SetFields方法的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的PHP代码示例。
示例1: 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;
}示例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($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__);
}
}示例4: 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__);
}示例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
//.........这里部分代码省略.........
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",
),示例11: GetList
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;
}示例12: SetEntity
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);
}示例13: GetByFilename
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;
}示例14: 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);
}示例15: 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) {
//.........这里部分代码省略.........