本文整理汇总了PHP中CUserTypeSQL::GetOrder方法的典型用法代码示例。如果您正苦于以下问题：PHP CUserTypeSQL::GetOrder方法的具体用法？PHP CUserTypeSQL::GetOrder怎么用？PHP CUserTypeSQL::GetOrder使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类CUserTypeSQL的用法示例。
在下文中一共展示了CUserTypeSQL::GetOrder方法的12个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的PHP代码示例。
示例1: GetList
//.........这里部分代码省略.........
break;
case "!LAST_ACTIVITY":
if ($val === false) {
$arSqlSearch[] = "U.LAST_ACTIVITY_DATE IS NOT NULL";
}
break;
case "INTRANET_USERS":
$arSqlSearch[] = "U.ACTIVE = 'Y' AND U.LAST_LOGIN IS NOT NULL AND EXISTS(SELECT 'x' FROM b_utm_user UF1, b_user_field F1 WHERE F1.ENTITY_ID = 'USER' AND F1.FIELD_NAME = 'UF_DEPARTMENT' AND UF1.FIELD_ID = F1.ID AND UF1.VALUE_ID = U.ID AND UF1.VALUE_INT IS NOT NULL AND UF1.VALUE_INT <> 0)";
break;
default:
if (in_array($key, $arFields)) {
$arSqlSearch[] = GetFilterQuery('U.' . $key, $val, $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y");
}
}
}
}
$arSqlOrder = array();
foreach ($arOrder as $field => $dir) {
$field = strtoupper($field);
if (strtolower($dir) != "asc") {
$dir = "desc";
if ($bSingleBy) {
$order = "desc";
}
}
if ($field == "CURRENT_BIRTHDAY") {
$cur_year = intval(date('Y'));
$arSqlOrder[$field] = "IF(ISNULL(PERSONAL_BIRTHDAY), '9999-99-99', IF (\n\t\t\t\t\tDATE_FORMAT(U.PERSONAL_BIRTHDAY, '" . $cur_year . "-%m-%d') < DATE_FORMAT(DATE_ADD(" . $DB->CurrentTimeFunction() . ", INTERVAL " . CTimeZone::GetOffset() . " SECOND), '%Y-%m-%d'),\n\t\t\t\t\tDATE_FORMAT(U.PERSONAL_BIRTHDAY, '" . ($cur_year + 1) . "-%m-%d'),\n\t\t\t\t\tDATE_FORMAT(U.PERSONAL_BIRTHDAY, '" . $cur_year . "-%m-%d')\n\t\t\t\t)) " . $dir;
} elseif ($field == "IS_ONLINE") {
$arSelectFields[$field] = "IF(U.LAST_ACTIVITY_DATE > DATE_SUB(NOW(), INTERVAL " . $online_interval . " SECOND), 'Y', 'N') IS_ONLINE";
$arSqlOrder[$field] = "IS_ONLINE " . $dir;
} elseif (in_array($field, $arFields_all)) {
$arSqlOrder[$field] = "U." . $field . " " . $dir;
} elseif ($s = $obUserFieldsSql->GetOrder($field)) {
$arSqlOrder[$field] = strtoupper($s) . " " . $dir;
} elseif (preg_match('/^RATING_(\\d+)$/i', $field, $matches)) {
$ratingId = intval($matches[1]);
if ($ratingId > 0) {
$arSqlOrder[$field] = $field . "_ISNULL ASC, " . $field . " " . $dir;
$arParams['SELECT'][] = $field;
} else {
$field = "TIMESTAMP_X";
$arSqlOrder[$field] = "U." . $field . " " . $dir;
if ($bSingleBy) {
$by = strtolower($field);
}
}
} else {
$field = "TIMESTAMP_X";
$arSqlOrder[$field] = "U." . $field . " " . $dir;
if ($bSingleBy) {
$by = strtolower($field);
}
}
}
$userFieldsSelect = $obUserFieldsSql->GetSelect();
$arSqlSearch[] = $obUserFieldsSql->GetFilter();
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$sSelect = ($obUserFieldsSql->GetDistinct() ? "DISTINCT " : "") . implode(', ', $arSelectFields) . "\n\t\t\t" . $userFieldsSelect . "\n\t\t";
if (is_array($arParams['SELECT'])) {
$arRatingInSelect = array();
foreach ($arParams['SELECT'] as $column) {
if (preg_match('/^RATING_(\\d+)$/i', $column, $matches)) {
$ratingId = intval($matches[1]);
if ($ratingId > 0 && !in_array($ratingId, $arRatingInSelect)) {
$sSelect .= ", RR" . $ratingId . ".CURRENT_POSITION IS NULL as RATING_" . $ratingId . "_ISNULL";示例2: Prepare
public function Prepare($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array(), $arOptions = array())
{
global $DB;
if (!is_array($arOrder)) {
$arOrder = array();
}
if (!is_array($arFilter)) {
$arFilter = array();
}
// ID must present in select (If select is empty it will be filled by CSqlUtil::PrepareSql)
if (!is_array($arSelectFields)) {
$arSelectFields = array();
}
if (count($arSelectFields) > 0 && !in_array('*', $arSelectFields, true) && !in_array('ID', $arSelectFields, true)) {
$arSelectFields[] = 'ID';
}
if (!is_array($arOptions)) {
$arOptions = array();
}
$arOptions['DB_TYPE'] = $this->dbType;
$isExternalContext = isset($arOptions['IS_EXTERNAL_CONTEXT']) && ($arOptions['IS_EXTERNAL_CONTEXT'] === true || $arOptions['IS_EXTERNAL_CONTEXT'] === 'Y');
if ($isExternalContext) {
// Sanitizing of filter data
if (isset($arFilter['__JOINS'])) {
unset($arFilter['__JOINS']);
}
if (isset($arFilter['CHECK_PERMISSIONS'])) {
unset($arFilter['CHECK_PERMISSIONS']);
}
}
// Processing of special fields
if ($this->fmEntityID !== '' && isset($arFilter['FM'])) {
CCrmFieldMulti::PrepareExternalFilter($arFilter, array('ENTITY_ID' => $this->fmEntityID, 'MASTER_ALIAS' => $this->tableAlias, 'MASTER_IDENTITY' => 'ID'));
}
// Processing user fields
$ufSelectSql = null;
$ufFilterSql = null;
if ($this->ufEntityID !== '') {
$ufSelectSql = new CUserTypeSQL();
$ufSelectSql->SetEntity($this->ufEntityID, $this->tableAlias . '.ID');
$ufSelectSql->SetSelect($arSelectFields);
$ufSelectSql->SetOrder($arOrder);
$ufFilterSql = new CUserTypeSQL();
$ufFilterSql->SetEntity($this->ufEntityID, $this->tableAlias . '.ID');
$ufFilterSql->SetFilter($arFilter);
$userType = new CCrmUserType($GLOBALS['USER_FIELD_MANAGER'], $this->ufEntityID);
$userType->ListPrepareFilter($arFilter);
}
$this->sqlData = CSqlUtil::PrepareSql($this->fields, $arOrder, $arFilter, $arGroupBy, $arSelectFields, $arOptions);
$this->sqlData['SELECT'] = str_replace('%%_DISTINCT_%% ', '', $this->sqlData['SELECT']);
// 'Joins' implement custom filter logic
$joins = array();
if (isset($arFilter['__JOINS'])) {
if (is_array($arFilter['__JOINS'])) {
$joins = $arFilter['__JOINS'];
}
unset($arFilter['__JOINS']);
}
if (count($joins) > 0) {
foreach ($joins as &$join) {
// INNER JOINs will be added tostart
$this->Add2SqlData($join['SQL'], 'FROM', !isset($join['TYPE']) || $join['TYPE'] === 'INNER', isset($join['REPLACE']) ? $join['REPLACE'] : '');
}
unset($join);
}
// Apply user permission logic
if (count($this->permissionCallback) > 0) {
if ((!array_key_exists('CHECK_PERMISSIONS', $arFilter) || $arFilter['CHECK_PERMISSIONS'] !== 'N') && !CCrmPerms::IsAdmin()) {
$arPermType = !isset($arFilter['PERMISSION']) ? 'READ' : (is_array($arFilter['PERMISSION']) ? $arFilter['PERMISSION'] : array($arFilter['PERMISSION']));
$permissionSql = call_user_func_array($this->permissionCallback, array($this->tableAlias, $arPermType, $arOptions));
if (is_bool($permissionSql) && !$permissionSql) {
$CDBResult = new CDBResult();
$CDBResult->InitFromArray(array());
return $CDBResult;
}
if ($permissionSql !== '') {
$sqlType = isset($arOptions['PERMISSION_SQL_TYPE']) && $arOptions['PERMISSION_SQL_TYPE'] === 'FROM' ? 'FROM' : 'WHERE';
$this->Add2SqlData($permissionSql, $sqlType, $sqlType === 'FROM');
}
}
}
// Apply custom SQL logic
if (count($this->afterPrepareSqlCallback) > 0) {
$arUserSql = call_user_func_array($this->afterPrepareSqlCallback, array($this, $arOrder, $arFilter, $arGroupBy, $arSelectFields));
if (is_array($arUserSql)) {
if (isset($arUserSql['FROM'])) {
$this->Add2SqlData($arUserSql['FROM'], 'FROM');
}
if (isset($arUserSql['WHERE'])) {
$this->Add2SqlData($arUserSql['WHERE'], 'WHERE');
}
}
}
if ($ufSelectSql) {
// Adding user fields to SELECT
$this->Add2SqlData($ufSelectSql->GetSelect(), 'SELECT');
// Adding user fields to ORDER BY
if (is_array($arOrder)) {
foreach ($arOrder as $orderKey => $order) {
$orderSql = $ufSelectSql->GetOrder($orderKey);
//.........这里部分代码省略.........
示例3: GetList
//.........这里部分代码省略.........
} elseif ($by == "s_site_id" || $by == "s_lid") {
$strSqlOrder = "ORDER BY T.SITE_ID";
} elseif ($by == "s_lamp") {
$strSqlOrder = "ORDER BY LAMP";
} elseif ($by == "s_is_overdue") {
$strSqlOrder = "ORDER BY T.IS_OVERDUE";
} elseif ($by == "s_is_notified") {
$strSqlOrder = "ORDER BY T.IS_NOTIFIED";
} elseif ($by == "s_date_create") {
$strSqlOrder = "ORDER BY T.DATE_CREATE";
} elseif ($by == "s_timestamp") {
$strSqlOrder = "ORDER BY T.TIMESTAMP_X";
} elseif ($by == "s_date_close") {
$strSqlOrder = "ORDER BY T.DATE_CLOSE";
} elseif ($by == "s_owner") {
$strSqlOrder = "ORDER BY T.OWNER_USER_ID";
} elseif ($by == "s_modified_by") {
$strSqlOrder = "ORDER BY T.MODIFIED_USER_ID";
} elseif ($by == "s_title") {
$strSqlOrder = "ORDER BY T.TITLE ";
} elseif ($by == "s_responsible") {
$strSqlOrder = "ORDER BY T.RESPONSIBLE_USER_ID";
} elseif ($by == "s_messages") {
$strSqlOrder = "ORDER BY T.MESSAGES";
} elseif ($by == "s_category") {
$strSqlOrder = "ORDER BY T.CATEGORY_ID";
} elseif ($by == "s_criticality") {
$strSqlOrder = "ORDER BY T.CRITICALITY_ID";
} elseif ($by == "s_sla") {
$strSqlOrder = "ORDER BY T.SLA_ID";
} elseif ($by == "s_status") {
$strSqlOrder = "ORDER BY T.STATUS_ID";
} elseif ($by == "s_difficulty") {
$strSqlOrder = "ORDER BY T.DIFFICULTY_ID";
} elseif ($by == "s_problem_time") {
$strSqlOrder = "ORDER BY T.PROBLEM_TIME";
} elseif ($by == "s_mark") {
$strSqlOrder = "ORDER BY T.MARK_ID";
} elseif ($by == "s_online") {
$strSqlOrder = "ORDER BY USERS_ONLINE";
} elseif ($by == "s_support_comments") {
$strSqlOrder = "ORDER BY T.SUPPORT_COMMENTS";
} elseif ($by == "s_auto_close_days_left") {
$strSqlOrder = "ORDER BY AUTO_CLOSE_DAYS_LEFT";
} elseif ($by == 's_coupon') {
$strSqlOrder = 'ORDER BY T.COUPON';
} elseif ($by == 's_deadline') {
$strSqlOrder = 'ORDER BY T.SUPPORT_DEADLINE';
} elseif ($s = $obUserFieldsSql->GetOrder($by)) {
$strSqlOrder = "ORDER BY " . strtoupper($s);
} else {
$by = "s_default";
$strSqlOrder = "ORDER BY IS_SUPER_TICKET DESC, T.IS_OVERDUE DESC, T.IS_NOTIFIED DESC, T.LAST_MESSAGE_DATE";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$arSqlSearch[] = $obUserFieldsSql->GetFilter();
if ($getUserName == "Y") {
$u_select = "\n\t\t\t\t,\n\t\t\t\tUO.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tOWNER_LOGIN,\n\t\t\t\tUO.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tOWNER_EMAIL,\n\t\t\t\tconcat(ifnull(UO.NAME,''),' ',ifnull(UO.LAST_NAME,''))\t\tOWNER_NAME,\n\t\t\t\tUR.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tRESPONSIBLE_LOGIN,\n\t\t\t\tUR.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tRESPONSIBLE_EMAIL,\n\t\t\t\tconcat(ifnull(UR.NAME,''),' ',ifnull(UR.LAST_NAME,''))\t\tRESPONSIBLE_NAME,\n\t\t\t\tUM.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_BY_LOGIN,\n\t\t\t\tUM.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_BY_EMAIL,\n\t\t\t\tconcat(ifnull(UM.NAME,''),' ',ifnull(UM.LAST_NAME,''))\t\tMODIFIED_BY_NAME,\n\t\t\t\tUM.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_LOGIN,\n\t\t\t\tUM.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_EMAIL,\n\t\t\t\tconcat(ifnull(UM.NAME,''),' ',ifnull(UM.LAST_NAME,''))\t\tMODIFIED_NAME,\n\t\t\t\tUL.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tLAST_MESSAGE_LOGIN,\n\t\t\t\tUL.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tLAST_MESSAGE_EMAIL,\n\t\t\t\tconcat(ifnull(UL.NAME,''),' ',ifnull(UL.LAST_NAME,''))\t\tLAST_MESSAGE_NAME,\n\t\t\t\tUC.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tCREATED_LOGIN,\n\t\t\t\tUC.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tCREATED_EMAIL,\n\t\t\t\tconcat(ifnull(UC.NAME,''),' ',ifnull(UC.LAST_NAME,''))\t\tCREATED_NAME\n\t\t\t";
$u_join = "\n\t\t\tLEFT JOIN b_user UO ON (UO.ID = T.OWNER_USER_ID)\n\t\t\tLEFT JOIN b_user UR ON (UR.ID = T.RESPONSIBLE_USER_ID)\n\t\t\tLEFT JOIN b_user UM ON (UM.ID = T.MODIFIED_USER_ID)\n\t\t\tLEFT JOIN b_user UL ON (UL.ID = T.LAST_MESSAGE_USER_ID)\n\t\t\tLEFT JOIN b_user UC ON (UC.ID = T.CREATED_USER_ID)\n\t\t\t";
}
if ($getExtraNames == "Y") {
$d_select = "\n\t\t\t\t,\n\t\t\t\tDC.NAME\t\t\t\t\t\t\t\t\t\t\t\t\t\tCATEGORY_NAME,\n\t\t\t\tDC.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tCATEGORY_DESC,\n\t\t\t\tDC.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tCATEGORY_SID,\n\t\t\t\tDK.NAME\t\t\t\t\t\t\t\t\t\t\t\t\t\tCRITICALITY_NAME,\n\t\t\t\tDK.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tCRITICALITY_DESC,\n\t\t\t\tDK.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tCRITICALITY_SID,\n\t\t\t\tDS.NAME\t\t\t\t\t\t\t\t\t\t\t\t\t\tSTATUS_NAME,\n\t\t\t\tDS.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tSTATUS_DESC,\n\t\t\t\tDS.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tSTATUS_SID,\n\t\t\t\tDM.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tMARK_NAME,\n\t\t\t\tDM.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tMARK_DESC,\n\t\t\t\tDM.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tMARK_SID,\n\t\t\t\tDSR.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tSOURCE_NAME,\n\t\t\t\tDSR.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tSOURCE_DESC,\n\t\t\t\tDSR.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tSOURCE_SID,\n\t\t\t\tDD.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tDIFFICULTY_NAME,\n\t\t\t\tDD.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tDIFFICULTY_DESC,\n\t\t\t\tDD.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tDIFFICULTY_SID,\n\t\t\t\tSLA.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tSLA_NAME\n\t\t\t";
$d_join = "\n\t\t\tLEFT JOIN b_ticket_dictionary DC ON (DC.ID = T.CATEGORY_ID and DC.C_TYPE = 'C')\n\t\t\tLEFT JOIN b_ticket_dictionary DK ON (DK.ID = T.CRITICALITY_ID and DK.C_TYPE = 'K')\n\t\t\tLEFT JOIN b_ticket_dictionary DS ON (DS.ID = T.STATUS_ID and DS.C_TYPE = 'S')\n\t\t\tLEFT JOIN b_ticket_dictionary DM ON (DM.ID = T.MARK_ID and DM.C_TYPE = 'M')\n\t\t\tLEFT JOIN b_ticket_dictionary DSR ON (DSR.ID = T.SOURCE_ID and DSR.C_TYPE = 'SR')\n\t\t\tLEFT JOIN b_ticket_dictionary DD ON (DD.ID = T.DIFFICULTY_ID and DD.C_TYPE = 'D')\n\t\t\tLEFT JOIN b_ticket_sla SLA ON (SLA.ID = T.SLA_ID)\n\t\t\t";
}
if (strlen($siteID) > 0) {
$dates_select = "\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "FULL", $siteID, true) . "\tDATE_CREATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "FULL", $siteID, true) . "\tTIMESTAMP_X,\n\t\t\t\t" . $DB->DateToCharFunction("T.LAST_MESSAGE_DATE", "FULL", $siteID, true) . "\tLAST_MESSAGE_DATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "FULL", $siteID, true) . "\tDATE_CLOSE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "SHORT", $siteID, true) . "\tDATE_CREATE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "SHORT", $siteID, true) . "\tTIMESTAMP_X_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "SHORT", $siteID, true) . "\tDATE_CLOSE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.SUPPORT_DEADLINE", "FULL", $siteID, true) . "\tSUPPORT_DEADLINE,\n\t\t\t\t" . $DB->DateToCharFunction("ADDDATE(T.TIMESTAMP_X, INTERVAL T.AUTO_CLOSE_DAYS DAY)", "FULL", $siteID, true) . "\tAUTO_CLOSE_DATE\n\t\t\t";
} else {
$dates_select = "\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "FULL") . "\t\tDATE_CREATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "FULL") . "\t\tTIMESTAMP_X,\n\t\t\t\t" . $DB->DateToCharFunction("T.LAST_MESSAGE_DATE", "FULL") . "\tLAST_MESSAGE_DATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "FULL") . "\t\tDATE_CLOSE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "SHORT") . "\tDATE_CREATE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "SHORT") . "\tTIMESTAMP_X_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "SHORT") . "\t\tDATE_CLOSE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.SUPPORT_DEADLINE", "FULL") . "\tSUPPORT_DEADLINE,\n\t\t\t\t" . $DB->DateToCharFunction("ADDDATE(T.TIMESTAMP_X, INTERVAL T.AUTO_CLOSE_DAYS DAY)", "FULL") . "\tAUTO_CLOSE_DATE\n\t\t\t";
}
$ugroupJoin = '';
$strSqlSearchUser = '';
if ($bJoinSupportTeamTbl || !($bAdmin == 'Y' || $bDemo == 'Y') && $bSupportTeam == 'Y') {
$ugroupJoin .= "\n\t\t\t\tLEFT JOIN b_ticket_user_ugroup UGS ON (UGS.USER_ID = T.RESPONSIBLE_USER_ID) ";
}
if ($bJoinClientTbl || !($bAdmin == 'Y' || $bDemo == 'Y') && $bSupportClient == 'Y') {
$ugroupJoin .= "\n\t\t\t\tLEFT JOIN b_ticket_user_ugroup UGC ON (UGC.USER_ID = T.OWNER_USER_ID) ";
}
if (!($bAdmin == 'Y' || $bDemo == 'Y')) {
$strSqlSearchUser = "(T.OWNER_USER_ID='{$uid}' OR T.RESPONSIBLE_USER_ID='{$uid}'";
if ($bSupportTeam == 'Y') {
$strSqlSearchUser .= " OR (UGS2.USER_ID IS NOT NULL AND UGS2.USER_ID='{$uid}' AND UUS.IS_TEAM_GROUP IS NOT NULL AND UUS.IS_TEAM_GROUP='Y')";
$ugroupJoin .= "\n\t\t\t\t\tLEFT JOIN b_ticket_user_ugroup UGS2 ON (UGS2.GROUP_ID = UGS.GROUP_ID AND UGS2.CAN_VIEW_GROUP_MESSAGES = 'Y')\n\t\t\t\t\tLEFT JOIN b_ticket_ugroups UUS ON (UUS.ID = UGS.GROUP_ID) ";
} elseif ($bSupportClient == 'Y') {
$strSqlSearchUser .= " OR (UGC2.USER_ID IS NOT NULL AND UGC2.USER_ID='{$uid}' AND UUC.IS_TEAM_GROUP IS NOT NULL AND UUC.IS_TEAM_GROUP<>'Y')";
$ugroupJoin .= "\n\t\t\t\t\tLEFT JOIN b_ticket_user_ugroup UGC2 ON (UGC2.GROUP_ID = UGC.GROUP_ID AND UGC2.CAN_VIEW_GROUP_MESSAGES = 'Y')\n\t\t\t\t\tLEFT JOIN b_ticket_ugroups UUC ON (UUC.ID = UGC.GROUP_ID) ";
}
$strSqlSearchUser .= ')';
$arSqlSearch[] = $strSqlSearchUser;
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$onlineInterval = intval(COption::GetOptionString("support", "ONLINE_INTERVAL"));
$strSql = "\n\t\t\tSELECT\n\t\t\t\tT.*,\n\t\t\t\tT.SITE_ID,\n\t\t\t\tT.SITE_ID\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tLID,\n\t\t\t\t{$dates_select},\n\t\t\t\tUNIX_TIMESTAMP(T.DATE_CLOSE)-UNIX_TIMESTAMP(T.DATE_CREATE)\t\t\t\t\t\t\tTICKET_TIME,\n\t\t\t\tTO_DAYS(ADDDATE(T.TIMESTAMP_X, INTERVAL T.AUTO_CLOSE_DAYS DAY))-TO_DAYS(now())\t\tAUTO_CLOSE_DAYS_LEFT,\n\t\t\t\tcount(distinct TN.USER_ID)\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tUSERS_ONLINE,\n\t\t\t\tif(T.COUPON IS NOT NULL, 1, 0)\t\t\t\t\t\t\t\t\t\t\t\t\t\tIS_SUPER_TICKET,\n\t\t\t\t{$lamp}\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tLAMP\n\t\t\t\t{$d_select}\n\t\t\t\t{$u_select}\n\t\t\t\t" . $obUserFieldsSql->GetSelect() . "\n\t\t\tFROM\n\t\t\t\tb_ticket T\n\t\t\tLEFT JOIN b_ticket_online TN ON (TN.TICKET_ID = T.ID and TN.TIMESTAMP_X >= DATE_ADD(now(), INTERVAL - {$onlineInterval} SECOND))\n\t\t\t{$u_join}\n\t\t\t{$d_join}\n\t\t\t{$messJoin}\n\t\t\t{$ugroupJoin}\n\t\t\t\t" . $obUserFieldsSql->GetJoin("T.ID") . "\n\t\t\tWHERE\n\t\t\t{$strSqlSearch}\n\t\t\tGROUP BY\n\t\t\t\tT.ID\n\t\t\t{$strSqlOrder}\n\t\t\t";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("SUPPORT"));
$isFiltered = IsFiltered($strSqlSearch);
return $res;
}示例4: GetList
//.........这里部分代码省略.........
$strSqlOrder = "ORDER BY T.OWNER_USER_ID";
} elseif ($by == "s_modified_by") {
$strSqlOrder = "ORDER BY T.MODIFIED_USER_ID";
} elseif ($by == "s_title") {
$strSqlOrder = "ORDER BY T.TITLE ";
} elseif ($by == "s_responsible") {
$strSqlOrder = "ORDER BY T.RESPONSIBLE_USER_ID";
} elseif ($by == "s_messages") {
$strSqlOrder = "ORDER BY T.MESSAGES";
} elseif ($by == "s_category") {
$strSqlOrder = "ORDER BY T.CATEGORY_ID";
} elseif ($by == "s_criticality") {
$strSqlOrder = "ORDER BY T.CRITICALITY_ID";
} elseif ($by == "s_sla") {
$strSqlOrder = "ORDER BY T.SLA_ID";
} elseif ($by == "s_status") {
$strSqlOrder = "ORDER BY T.STATUS_ID";
} elseif ($by == "s_difficulty") {
$strSqlOrder = "ORDER BY T.DIFFICULTY_ID";
} elseif ($by == "s_problem_time") {
$strSqlOrder = "ORDER BY T.PROBLEM_TIME";
} elseif ($by == "s_mark") {
$strSqlOrder = "ORDER BY T.MARK_ID";
} elseif ($by == "s_online") {
$strSqlOrder = "ORDER BY USERS_ONLINE";
} elseif ($by == "s_support_comments") {
$strSqlOrder = "ORDER BY T.SUPPORT_COMMENTS";
} elseif ($by == "s_auto_close_days_left") {
$strSqlOrder = "ORDER BY AUTO_CLOSE_DAYS_LEFT";
} elseif ($by == 's_coupon') {
$strSqlOrder = 'ORDER BY T.COUPON';
} elseif ($by == 's_deadline') {
$strSqlOrder = 'ORDER BY T.SUPPORT_DEADLINE';
} elseif ($s = $obUserFieldsSql->GetOrder($by)) {
$strSqlOrder = "ORDER BY " . strtoupper($s);
} else {
$by = "s_default";
$strSqlOrder = "ORDER BY IS_SUPER_TICKET DESC, T.IS_OVERDUE DESC, T.IS_NOTIFIED DESC, T.LAST_MESSAGE_DATE";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$arSqlSearch[] = $obUserFieldsSql->GetFilter();
if ($getUserName == "Y") {
$u_select = "\n\t\t\t\t,\n\t\t\t\tUO.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tOWNER_LOGIN,\n\t\t\t\tUO.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tOWNER_EMAIL,\n\t\t\t\tconcat(ifnull(UO.NAME,''),' ',ifnull(UO.LAST_NAME,''))\t\tOWNER_NAME,\n\t\t\t\tUR.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tRESPONSIBLE_LOGIN,\n\t\t\t\tUR.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tRESPONSIBLE_EMAIL,\n\t\t\t\tconcat(ifnull(UR.NAME,''),' ',ifnull(UR.LAST_NAME,''))\t\tRESPONSIBLE_NAME,\n\t\t\t\tUM.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_BY_LOGIN,\n\t\t\t\tUM.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_BY_EMAIL,\n\t\t\t\tconcat(ifnull(UM.NAME,''),' ',ifnull(UM.LAST_NAME,''))\t\tMODIFIED_BY_NAME,\n\t\t\t\tUM.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_LOGIN,\n\t\t\t\tUM.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tMODIFIED_EMAIL,\n\t\t\t\tconcat(ifnull(UM.NAME,''),' ',ifnull(UM.LAST_NAME,''))\t\tMODIFIED_NAME,\n\t\t\t\tUL.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tLAST_MESSAGE_LOGIN,\n\t\t\t\tUL.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tLAST_MESSAGE_EMAIL,\n\t\t\t\tconcat(ifnull(UL.NAME,''),' ',ifnull(UL.LAST_NAME,''))\t\tLAST_MESSAGE_NAME,\n\t\t\t\tUC.LOGIN\t\t\t\t\t\t\t\t\t\t\t\t\tCREATED_LOGIN,\n\t\t\t\tUC.EMAIL\t\t\t\t\t\t\t\t\t\t\t\t\tCREATED_EMAIL,\n\t\t\t\tconcat(ifnull(UC.NAME,''),' ',ifnull(UC.LAST_NAME,''))\t\tCREATED_NAME\n\t\t\t";
$u_join = "\n\t\t\tLEFT JOIN b_user UO ON (UO.ID = T.OWNER_USER_ID)\n\t\t\tLEFT JOIN b_user UR ON (UR.ID = T.RESPONSIBLE_USER_ID)\n\t\t\tLEFT JOIN b_user UM ON (UM.ID = T.MODIFIED_USER_ID)\n\t\t\tLEFT JOIN b_user UL ON (UL.ID = T.LAST_MESSAGE_USER_ID)\n\t\t\tLEFT JOIN b_user UC ON (UC.ID = T.CREATED_USER_ID)\n\t\t\t";
}
if ($getExtraNames == "Y") {
$d_select = "\n\t\t\t\t,\n\t\t\t\tDC.NAME\t\t\t\t\t\t\t\t\t\t\t\t\t\tCATEGORY_NAME,\n\t\t\t\tDC.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tCATEGORY_DESC,\n\t\t\t\tDC.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tCATEGORY_SID,\n\t\t\t\tDK.NAME\t\t\t\t\t\t\t\t\t\t\t\t\t\tCRITICALITY_NAME,\n\t\t\t\tDK.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tCRITICALITY_DESC,\n\t\t\t\tDK.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tCRITICALITY_SID,\n\t\t\t\tDS.NAME\t\t\t\t\t\t\t\t\t\t\t\t\t\tSTATUS_NAME,\n\t\t\t\tDS.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tSTATUS_DESC,\n\t\t\t\tDS.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tSTATUS_SID,\n\t\t\t\tDM.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tMARK_NAME,\n\t\t\t\tDM.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tMARK_DESC,\n\t\t\t\tDM.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tMARK_SID,\n\t\t\t\tDSR.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tSOURCE_NAME,\n\t\t\t\tDSR.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tSOURCE_DESC,\n\t\t\t\tDSR.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tSOURCE_SID,\n\t\t\t\tDD.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tDIFFICULTY_NAME,\n\t\t\t\tDD.DESCR\t\t\t\t\t\t\t\t\t\t\t\t\tDIFFICULTY_DESC,\n\t\t\t\tDD.SID\t\t\t\t\t\t\t\t\t\t\t\t\t\tDIFFICULTY_SID,\n\t\t\t\tSLA.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tSLA_NAME\n\t\t\t";
$d_join = "\n\t\t\tLEFT JOIN b_ticket_dictionary DC ON (DC.ID = T.CATEGORY_ID and DC.C_TYPE = 'C')\n\t\t\tLEFT JOIN b_ticket_dictionary DK ON (DK.ID = T.CRITICALITY_ID and DK.C_TYPE = 'K')\n\t\t\tLEFT JOIN b_ticket_dictionary DS ON (DS.ID = T.STATUS_ID and DS.C_TYPE = 'S')\n\t\t\tLEFT JOIN b_ticket_dictionary DM ON (DM.ID = T.MARK_ID and DM.C_TYPE = 'M')\n\t\t\tLEFT JOIN b_ticket_dictionary DSR ON (DSR.ID = T.SOURCE_ID and DSR.C_TYPE = 'SR')\n\t\t\tLEFT JOIN b_ticket_dictionary DD ON (DD.ID = T.DIFFICULTY_ID and DD.C_TYPE = 'D')\n\t\t\tLEFT JOIN b_ticket_sla SLA ON (SLA.ID = T.SLA_ID)\n\t\t\t";
}
if (strlen($siteID) > 0) {
$dates_select = "\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "FULL", $siteID, true) . "\tDATE_CREATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "FULL", $siteID, true) . "\tTIMESTAMP_X,\n\t\t\t\t" . $DB->DateToCharFunction("T.LAST_MESSAGE_DATE", "FULL", $siteID, true) . "\tLAST_MESSAGE_DATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "FULL", $siteID, true) . "\tDATE_CLOSE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "SHORT", $siteID, true) . "\tDATE_CREATE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "SHORT", $siteID, true) . "\tTIMESTAMP_X_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "SHORT", $siteID, true) . "\tDATE_CLOSE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.SUPPORT_DEADLINE", "FULL", $siteID, true) . "\tSUPPORT_DEADLINE,\n\t\t\t\tCASE WHEN (UNIX_TIMESTAMP(T.DATE_CLOSE) IS NULL OR UNIX_TIMESTAMP(T.DATE_CLOSE) = 0) AND T.LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y' THEN " . $DB->DateToCharFunction("ADDDATE(T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY)", "FULL", $siteID, true) . " ELSE NULL END AUTO_CLOSE_DATE\n\t\t\t";
} else {
$dates_select = "\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "FULL") . "\t\tDATE_CREATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "FULL") . "\t\tTIMESTAMP_X,\n\t\t\t\t" . $DB->DateToCharFunction("T.LAST_MESSAGE_DATE", "FULL") . "\tLAST_MESSAGE_DATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "FULL") . "\t\tDATE_CLOSE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE", "SHORT") . "\tDATE_CREATE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.TIMESTAMP_X", "SHORT") . "\tTIMESTAMP_X_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CLOSE", "SHORT") . "\t\tDATE_CLOSE_SHORT,\n\t\t\t\t" . $DB->DateToCharFunction("T.SUPPORT_DEADLINE", "FULL") . "\tSUPPORT_DEADLINE,\n\t\t\t\tCASE WHEN (UNIX_TIMESTAMP(T.DATE_CLOSE) IS NULL OR UNIX_TIMESTAMP(T.DATE_CLOSE) = 0) AND T.LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y' THEN " . $DB->DateToCharFunction("ADDDATE(T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY)", "FULL") . " ELSE NULL END AUTO_CLOSE_DATE\n\t\t\t";
}
$ugroupJoin = '';
if ($bJoinSupportTeamTbl) {
$ugroupJoin .= "\n\t\t\tLEFT JOIN b_ticket_user_ugroup UGS ON (UGS.USER_ID = T.RESPONSIBLE_USER_ID) ";
$need_group = true;
}
if ($bJoinClientTbl) {
$ugroupJoin .= "\n\t\t\tLEFT JOIN b_ticket_user_ugroup UGC ON (UGC.USER_ID = T.OWNER_USER_ID) ";
$need_group = true;
}示例5: PrepareSql
function PrepareSql(&$arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields, $arUF = array())
{
global $DB;
$obUserFieldsSql = false;
if (is_array($arUF) && array_key_exists("ENTITY_ID", $arUF)) {
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity($arUF["ENTITY_ID"], $arFields["ID"]["FIELD"]);
$obUserFieldsSql->SetSelect($arSelectFields);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
}
$strSqlSelect = "";
$strSqlFrom = "";
$strSqlWhere = "";
$strSqlGroupBy = "";
$strSqlOrderBy = "";
$arGroupByFunct = array("COUNT", "AVG", "MIN", "MAX", "SUM");
$arAlreadyJoined = array();
// GROUP BY -->
if (is_array($arGroupBy) && count($arGroupBy) > 0) {
$arSelectFields = $arGroupBy;
foreach ($arGroupBy as $key => $val) {
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields) && !in_array($key, $arGroupByFunct)) {
if (strlen($strSqlGroupBy) > 0) {
$strSqlGroupBy .= ", ";
}
$strSqlGroupBy .= $arFields[$val]["FIELD"];
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"];
}
}
}
}
// <-- GROUP BY
// WHERE -->
$arAlreadyJoinedOld = $arAlreadyJoined;
$strSqlWhere .= CSqlUtil::PrepareWhere($arFields, $arFilter, $arAlreadyJoined);
$arAlreadyJoinedDiff = array_diff($arAlreadyJoined, $arAlreadyJoinedOld);
foreach ($arAlreadyJoinedDiff as $from_tmp) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $from_tmp;
}
if ($obUserFieldsSql) {
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$strSqlWhere .= (strlen($strSqlWhere) > 0 ? " AND" : "") . " (" . $r . ") ";
}
}
// <-- WHERE
// 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)) {
if ($arFields[$by]["TYPE"] == "datetime" || $arFields[$by]["TYPE"] == "date") {
$arSqlOrder[] = " " . $by . "_X1 " . $order . " ";
if (!is_array($arSelectFields) || !in_array($by, $arSelectFields)) {
$arSelectFields[] = $by;
}
} else {
$arSqlOrder[] = " " . $arFields[$by]["FIELD"] . " " . $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"];
}
} elseif ($obUserFieldsSql && ($s = $obUserFieldsSql->GetOrder($by))) {
$arSqlOrder[$by] = " " . $s . " " . $order . " ";
}
}
$strSqlOrderBy = "";
DelDuplicateSort($arSqlOrder);
$tmp_count = count($arSqlOrder);
for ($i = 0; $i < $tmp_count; $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";
}
//.........这里部分代码省略.........
示例6: GetList
//.........这里部分代码省略.........
{
$ar_res["LAST_MESSAGE_ID"] = $ar_res1["LAST_MESSAGE_ID"];
}
}
}
return $ar_res;
}
else
{
$strSql = "SELECT COUNT(FM.ID) as CNT \n ".$strFrom;
$db_res = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
$iCnt = 0;
if ($db_res && $ar_res = $db_res->Fetch())
$iCnt = intVal($ar_res["CNT"]);
if ($bCount)
return $iCnt;
}
// /This code was changed because of http://bugs.mysql.com/bug.php?id=64002
}
foreach ($arOrder as $by=>$order)
{
$by = strtoupper($by); $order = strtoupper($order);
if ($order!="ASC") $order = "DESC";
if ($by == "AUTHOR_NAME") $arSqlOrder[] = " FM.AUTHOR_NAME ".$order." ";
elseif ($by == "EDIT_DATE") $arSqlOrder[] = " FM.EDIT_DATE ".$order." ";
elseif ($by == "POST_DATE") $arSqlOrder[] = " FM.POST_DATE ".$order." ";
elseif ($by == "FORUM_ID") $arSqlOrder[] = " FM.FORUM_ID ".$order." ";
elseif ($by == "TOPIC_ID") $arSqlOrder[] = " FM.TOPIC_ID ".$order." ";
elseif ($by == "NEW_TOPIC") $arSqlOrder[] = " FM.NEW_TOPIC ".$order." ";
elseif ($by == "APPROVED") $arSqlOrder[] = " FM.APPROVED ".$order." ";
else
{
$r = $obUserFieldsSql->GetOrder($by);
if (!!$r)
{
$arSqlOrder[] = " ".$r." ".$order." ";
}
else
{
$arSqlOrder[] = " FM.ID ".$order." ";
$by = "ID";
}
}
}
$arSqlOrder = array_unique($arSqlOrder);
DelDuplicateSort($arSqlOrder);
if(!empty($arSqlOrder))
$strSqlOrder = " ORDER BY ".implode(", ", $arSqlOrder);
$strSqlUserFieldJoin = $obUserFieldsSql->GetJoin("FM.ID");
if ($obUserFieldsSql->GetDistinct())
{
$obUserFieldsSqlSelect = new CUserTypeSQL;
$obUserFieldsSqlSelect->SetEntity("FORUM_MESSAGE", "FM.ID");
$obUserFieldsSqlSelect->SetSelect($arAddParams["SELECT"]);
$obUserFieldsSqlSelect->SetOrder($arOrder);
$strSqlUserFieldJoin =
$obUserFieldsSqlSelect->GetJoin("FM.ID")."
INNER JOIN (
SELECT DISTINCT FM.ID
FROM b_forum_message FM\n".
$obUserFieldsSql->GetJoin("FM.ID")."\n".
"WHERE 1 = 1 ".$strSqlSearch.") FM2 ON (FM2.ID = FM.ID) ";
$strSqlSearch = "";示例7: GetList
//.........这里部分代码省略.........
foreach ($arSelect as $field) {
$field = strtoupper($field);
if (array_key_exists($field, $arFields)) {
$arSqlSelect[$field] = $arFields[$field] . " AS " . $field;
}
}
if (array_key_exists("DESCRIPTION", $arSqlSelect)) {
$arSqlSelect["DESCRIPTION_TYPE"] = $arFields["DESCRIPTION_TYPE"] . " AS DESCRIPTION_TYPE";
}
if (array_key_exists("LIST_PAGE_URL", $arSqlSelect) || array_key_exists("SECTION_PAGE_URL", $arSqlSelect)) {
$arSqlSelect["ID"] = $arFields["ID"] . " AS ID";
$arSqlSelect["CODE"] = $arFields["CODE"] . " AS CODE";
$arSqlSelect["EXTERNAL_ID"] = $arFields["EXTERNAL_ID"] . " AS EXTERNAL_ID";
$arSqlSelect["IBLOCK_TYPE_ID"] = $arFields["IBLOCK_TYPE_ID"] . " AS IBLOCK_TYPE_ID";
$arSqlSelect["IBLOCK_ID"] = $arFields["IBLOCK_ID"] . " AS IBLOCK_ID";
$arSqlSelect["IBLOCK_CODE"] = $arFields["IBLOCK_CODE"] . " AS IBLOCK_CODE";
$arSqlSelect["IBLOCK_EXTERNAL_ID"] = $arFields["IBLOCK_EXTERNAL_ID"] . " AS IBLOCK_EXTERNAL_ID";
$arSqlSelect["GLOBAL_ACTIVE"] = $arFields["GLOBAL_ACTIVE"] . " AS GLOBAL_ACTIVE";
//$arr["LANG_DIR"],
}
if (count($arSqlSelect)) {
$sSelect = implode(",\n", $arSqlSelect);
} else {
$sSelect = "\n\t\t\t\tBS.*,\n\t\t\t\tB.LIST_PAGE_URL,\n\t\t\t\tB.SECTION_PAGE_URL,\n\t\t\t\tB.IBLOCK_TYPE_ID,\n\t\t\t\tB.CODE as IBLOCK_CODE,\n\t\t\t\tB.XML_ID as IBLOCK_EXTERNAL_ID,\n\t\t\t\tBS.XML_ID as EXTERNAL_ID,\n\t\t\t\t" . $DB->DateToCharFunction("BS.TIMESTAMP_X") . " as TIMESTAMP_X,\n\t\t\t\t" . $DB->DateToCharFunction("BS.DATE_CREATE") . " as DATE_CREATE\n\t\t\t";
}
if (!$bIncCnt) {
$strSelect = $sSelect . (isset($obUserFieldsSql) ? $obUserFieldsSql->GetSelect() : "");
$strSql = "\n\t\t\t\tFROM b_iblock_section BS\n\t\t\t\t\tINNER JOIN b_iblock B ON BS.IBLOCK_ID = B.ID\n\t\t\t\t\t" . (isset($obUserFieldsSql) ? $obUserFieldsSql->GetJoin("BS.ID") : "") . "\n\t\t\t\t" . (strlen($strProp1) > 0 ? "\tINNER JOIN b_iblock_section BSTEMP ON BSTEMP.IBLOCK_ID = BS.IBLOCK_ID\n\t\t\t\t\t\tLEFT JOIN b_iblock_section_element BSE ON BSE.IBLOCK_SECTION_ID=BSTEMP.ID\n\t\t\t\t\t\tLEFT JOIN b_iblock_element BE ON (BSE.IBLOCK_ELEMENT_ID=BE.ID\n\t\t\t\t\t\t\tAND ((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL )\n\t\t\t\t\t\t\tAND BE.IBLOCK_ID = BS.IBLOCK_ID\n\t\t\t\t\t" . ($arFilter["CNT_ALL"] == "Y" ? " OR BE.WF_NEW='Y' " : "") . ")\n\t\t\t\t\t" . ($arFilter["CNT_ACTIVE"] == "Y" ? " AND BE.ACTIVE='Y'\n\t\t\t\t\t\tAND (BE.ACTIVE_TO >= " . $DB->CurrentTimeFunction() . " OR BE.ACTIVE_TO IS NULL)\n\t\t\t\t\t\tAND (BE.ACTIVE_FROM <= " . $DB->CurrentTimeFunction() . " OR BE.ACTIVE_FROM IS NULL)" : "") . ")\n\t\t\t\t\t\t" . $strProp1 . " " : "") . "\n\t\t\t\tWHERE 1=1\n\t\t\t\t" . (strlen($strProp1) > 0 ? "\tAND BSTEMP.LEFT_MARGIN >= BS.LEFT_MARGIN\n\t\t\t\t\t\tAND BSTEMP.RIGHT_MARGIN <= BS.RIGHT_MARGIN " : "") . "\n\t\t\t\t" . $strSqlSearch . "\n\t\t\t";
$strGroupBy = "";
} else {
$strSelect = $sSelect . ",COUNT(DISTINCT BE.ID) as ELEMENT_CNT" . (isset($obUserFieldsSql) ? $obUserFieldsSql->GetSelect() : "");
$strSql = "\n\t\t\t\tFROM b_iblock_section BS\n\t\t\t\t\tINNER JOIN b_iblock B ON BS.IBLOCK_ID = B.ID\n\t\t\t\t\t" . (isset($obUserFieldsSql) ? $obUserFieldsSql->GetJoin("BS.ID") : "") . "\n\t\t\t\t" . ($arFilter["ELEMENT_SUBSECTIONS"] == "N" ? "\tLEFT JOIN b_iblock_section_element BSE ON BSE.IBLOCK_SECTION_ID=BS.ID " : "\tINNER JOIN b_iblock_section BSTEMP ON BSTEMP.IBLOCK_ID = BS.IBLOCK_ID\n\t\t\t\t\t\tLEFT JOIN b_iblock_section_element BSE ON BSE.IBLOCK_SECTION_ID=BSTEMP.ID ") . "\n\t\t\t\t\tLEFT JOIN b_iblock_element BE ON (BSE.IBLOCK_ELEMENT_ID=BE.ID\n\t\t\t\t\t\tAND ((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL )\n\t\t\t\t\t\tAND BE.IBLOCK_ID = BS.IBLOCK_ID\n\t\t\t\t" . ($arFilter["CNT_ALL"] == "Y" ? " OR BE.WF_NEW='Y' " : "") . ")\n\t\t\t\t" . ($arFilter["CNT_ACTIVE"] == "Y" ? " AND BE.ACTIVE='Y'\n\t\t\t\t\tAND (BE.ACTIVE_TO >= " . $DB->CurrentTimeFunction() . " OR BE.ACTIVE_TO IS NULL)\n\t\t\t\t\tAND (BE.ACTIVE_FROM <= " . $DB->CurrentTimeFunction() . " OR BE.ACTIVE_FROM IS NULL)" : "") . ")\n\t\t\t\t\t" . $strProp1 . "\n\t\t\t\tWHERE 1=1\n\t\t\t\t" . ($arFilter["ELEMENT_SUBSECTIONS"] == "N" ? "\t" : "\tAND BSTEMP.IBLOCK_ID = BS.IBLOCK_ID\n\t\t\t\t\t\tAND BSTEMP.LEFT_MARGIN >= BS.LEFT_MARGIN\n\t\t\t\t\t\tAND BSTEMP.RIGHT_MARGIN <= BS.RIGHT_MARGIN ") . "\n\t\t\t\t" . $strSqlSearch . "\n\t\t\t";
$strGroupBy = "GROUP BY BS.ID, B.ID";
}
$arSqlOrder = array();
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
if (isset($arSqlOrder[$by])) {
continue;
}
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if ($by == "id") {
$arSqlOrder[$by] = " BS.ID " . $order . " ";
} elseif ($by == "section") {
$arSqlOrder[$by] = " BS.IBLOCK_SECTION_ID " . $order . " ";
} elseif ($by == "name") {
$arSqlOrder[$by] = " BS.NAME " . $order . " ";
} elseif ($by == "code") {
$arSqlOrder[$by] = " BS.CODE " . $order . " ";
} elseif ($by == "active") {
$arSqlOrder[$by] = " BS.ACTIVE " . $order . " ";
} elseif ($by == "left_margin") {
$arSqlOrder[$by] = " BS.LEFT_MARGIN " . $order . " ";
} elseif ($by == "depth_level") {
$arSqlOrder[$by] = " BS.DEPTH_LEVEL " . $order . " ";
} elseif ($by == "sort") {
$arSqlOrder[$by] = " BS.SORT " . $order . " ";
} elseif ($by == "created") {
$arSqlOrder[$by] = " BS.DATE_CREATE " . $order . " ";
} elseif ($by == "created_by") {
$arSqlOrder[$by] = " BS.CREATED_BY " . $order . " ";
} elseif ($by == "modified_by") {
$arSqlOrder[$by] = " BS.MODIFIED_BY " . $order . " ";
} elseif ($bIncCnt && $by == "element_cnt") {
$arSqlOrder[$by] = " ELEMENT_CNT " . $order . " ";
} elseif (isset($obUserFieldsSql) && ($s = $obUserFieldsSql->GetOrder($by))) {
$arSqlOrder[$by] = " " . $s . " " . $order . " ";
} else {
$by = "timestamp_x";
$arSqlOrder[$by] = " BS.TIMESTAMP_X " . $order . " ";
}
}
if (count($arSqlOrder) > 0) {
$strSqlOrder = "\n\t\t\t\tORDER BY " . implode(", ", $arSqlOrder);
} else {
$strSqlOrder = "";
}
if (is_array($arNavStartParams)) {
$nTopCount = intval($arNavStartParams["nTopCount"]);
if ($nTopCount > 0) {
$res = $DB->Query($DB->TopSql("SELECT DISTINCT " . $strSelect . $strSql . $strGroupBy . $strSqlOrder, $nTopCount));
} else {
$res_cnt = $DB->Query("SELECT COUNT(DISTINCT BS.ID) as C " . $strSql);
$res_cnt = $res_cnt->Fetch();
$res = new CDBResult();
$res->NavQuery("SELECT DISTINCT " . $strSelect . $strSql . $strGroupBy . $strSqlOrder, $res_cnt["C"], $arNavStartParams);
}
} else {
$res = $DB->Query("SELECT DISTINCT " . $strSelect . $strSql . $strGroupBy . $strSqlOrder, false, "FILE: " . __FILE__ . "<br> LINE: " . __LINE__);
}
$res = new CIBlockResult($res);
if (isset($arFilter["IBLOCK_ID"]) && $arFilter["IBLOCK_ID"] > 0) {
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("IBLOCK_" . $arFilter["IBLOCK_ID"] . "_SECTION"));
$res->SetIBlockTag($arFilter["IBLOCK_ID"]);
}
return $res;
}示例8: PrepareSql
//.........这里部分代码省略.........
// 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))
{
if ($arFields[$by]["TYPE"] == "datetime" || $arFields[$by]["TYPE"] == "date")
{
$arSqlOrder[] = " ".$by."_X1 ".$order." ";
if (!is_array($arSelectFields) || !in_array($by, $arSelectFields))
$arSelectFields[] = $by;
}
else
$arSqlOrder[] = " ".$arFields[$by]["FIELD"]." ".$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"];
}
}
elseif($obUserFieldsSql && $s = $obUserFieldsSql->GetOrder($by))
$arSqlOrder[$by] = " ".$s." ".$order." ";
}
$strSqlOrderBy = "";
DelDuplicateSort($arSqlOrder);
$tmp_count = count($arSqlOrder);
for ($i=0; $i < $tmp_count; $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
// SELECT -->
$arFieldsKeys = array_keys($arFields);
if (is_array($arGroupBy) && count($arGroupBy)==0)
$strSqlSelect = "COUNT(%%_DISTINCT_%% ".$arFields[$arFieldsKeys[0]]["FIELD"].") as CNT ";
else
{
if (isset($arSelectFields) && !is_array($arSelectFields) && is_string($arSelectFields) && strlen($arSelectFields)>0 && array_key_exists($arSelectFields, $arFields))
$arSelectFields = array($arSelectFields);示例9: GetList
//.........这里部分代码省略.........
$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;
}示例10: GetList
static function GetList($arOrder = array(), $arFilter = array(), $arGroupBy = false, $arNavStartParams = false, $arSelectFields = array())
{
global $DB;
if (empty($arSelectFields)) {
$arSelectFields = array("ID", "ACTIVE", "TITLE", "PHONE", "SCHEDULE", "ADDRESS", "DESCRIPTION", "GPS_N", "GPS_S", "IMAGE_ID", "DATE_CREATE", "DATE_MODIFY", "USER_ID", "XML_ID", "SORT", "EMAIL", "ISSUING_CENTER", "SHIPPING_CENTER", "SITE_ID");
}
$keyForDelete = array_search("PRODUCT_AMOUNT", $arSelectFields);
if (!isset($arFilter["PRODUCT_ID"]) && $keyForDelete !== false) {
unset($arSelectFields[$keyForDelete]);
}
if ($keyForDelete == false) {
$keyForDelete = array_search("ELEMENT_ID", $arSelectFields);
if ($keyForDelete !== false) {
unset($arSelectFields[$keyForDelete]);
}
}
$productID = '(';
if (is_array($arFilter["PRODUCT_ID"])) {
foreach ($arFilter["PRODUCT_ID"] as $id) {
$productID .= intval($id) . ',';
}
$productID = rtrim($productID, ',') . ')';
} else {
$productID .= intval($arFilter["PRODUCT_ID"]) . ')';
}
$arFields = array("ID" => array("FIELD" => "CS.ID", "TYPE" => "int"), "ACTIVE" => array("FIELD" => "CS.ACTIVE", "TYPE" => "string"), "TITLE" => array("FIELD" => "CS.TITLE", "TYPE" => "string"), "PHONE" => array("FIELD" => "CS.PHONE", "TYPE" => "string"), "SCHEDULE" => array("FIELD" => "CS.SCHEDULE", "TYPE" => "string"), "ADDRESS" => array("FIELD" => "CS.ADDRESS", "TYPE" => "string"), "DESCRIPTION" => array("FIELD" => "CS.DESCRIPTION", "TYPE" => "string"), "GPS_N" => array("FIELD" => "CS.GPS_N", "TYPE" => "string"), "GPS_S" => array("FIELD" => "CS.GPS_S", "TYPE" => "string"), "IMAGE_ID" => array("FIELD" => "CS.IMAGE_ID", "TYPE" => "int"), "LOCATION_ID" => array("FIELD" => "CS.LOCATION_ID", "TYPE" => "int"), "DATE_CREATE" => array("FIELD" => "CS.DATE_CREATE", "TYPE" => "datetime"), "DATE_MODIFY" => array("FIELD" => "CS.DATE_MODIFY", "TYPE" => "datetime"), "USER_ID" => array("FIELD" => "CS.USER_ID", "TYPE" => "int"), "MODIFIED_BY" => array("FIELD" => "CS.MODIFIED_BY", "TYPE" => "int"), "XML_ID" => array("FIELD" => "CS.XML_ID", "TYPE" => "string"), "SORT" => array("FIELD" => "CS.SORT", "TYPE" => "int"), "EMAIL" => array("FIELD" => "CS.EMAIL", "TYPE" => "string"), "ISSUING_CENTER" => array("FIELD" => "CS.ISSUING_CENTER", "TYPE" => "char"), "SHIPPING_CENTER" => array("FIELD" => "CS.SHIPPING_CENTER", "TYPE" => "char"), "SITE_ID" => array("FIELD" => "CS.SITE_ID", "TYPE" => "string"), "PRODUCT_AMOUNT" => array("FIELD" => "CP.AMOUNT", "TYPE" => "double", "FROM" => "LEFT JOIN b_catalog_store_product CP ON (CS.ID = CP.STORE_ID AND CP.PRODUCT_ID IN " . $productID . ")"), "ELEMENT_ID" => array("FIELD" => "CP.PRODUCT_ID", "TYPE" => "int"));
$userField = new CUserTypeSQL();
$userField->SetEntity("CAT_STORE", "CS.ID");
$userField->SetSelect($arSelectFields);
$userField->SetFilter($arFilter);
$userField->SetOrder($arOrder);
$strUfFilter = $userField->GetFilter();
$strSqlUfFilter = strlen($strUfFilter) > 0 ? " (" . $strUfFilter . ") " : "";
$strSqlUfOrder = "";
foreach ($arOrder as $field => $by) {
$field = $userField->GetOrder($field);
if (empty($field)) {
continue;
}
if (strlen($strSqlUfOrder) > 0) {
$strSqlUfOrder .= ', ';
}
$strSqlUfOrder .= $field . " " . $by;
}
$arSqls = CCatalog::PrepareSql($arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields);
$arSqls["SELECT"] = str_replace("%%_DISTINCT_%%", "", $arSqls["SELECT"]);
if (empty($arGroupBy) && is_array($arGroupBy)) {
$strSql = "SELECT " . $arSqls["SELECT"] . " " . $userField->GetSelect() . " FROM b_catalog_store CS " . $arSqls["FROM"] . " " . $userField->GetJoin("CS.ID");
if (!empty($arSqls["WHERE"])) {
$strSql .= " WHERE " . $arSqls["WHERE"] . " ";
}
if (strlen($arSqls["WHERE"]) > 0 && strlen($strSqlUfFilter) > 0) {
$strSql .= " AND " . $strSqlUfFilter . " ";
} elseif (strlen($arSqls["WHERE"]) == 0 && strlen($strSqlUfFilter) > 0) {
$strSql .= " WHERE " . $strSqlUfFilter . " ";
}
if (!empty($arSqls["GROUPBY"])) {
$strSql .= " GROUP BY " . $arSqls["GROUPBY"];
}
$dbRes = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
if ($arRes = $dbRes->Fetch()) {
return $arRes["CNT"];
} else {
return false;
}
}
$strSql = "SELECT " . $arSqls["SELECT"] . " " . $userField->GetSelect() . " FROM b_catalog_store CS " . $arSqls["FROM"] . " " . $userField->GetJoin("CS.ID");
if (!empty($arSqls["WHERE"])) {
$strSql .= " WHERE " . $arSqls["WHERE"] . " ";
}
if (strlen($arSqls["WHERE"]) > 0 && strlen($strSqlUfFilter) > 0) {
$strSql .= " AND " . $strSqlUfFilter . " ";
} elseif (strlen($arSqls["WHERE"]) <= 0 && strlen($strSqlUfFilter) > 0) {
$strSql .= " WHERE " . $strSqlUfFilter . " ";
}
if (!empty($arSqls["GROUPBY"])) {
$strSql .= " GROUP BY " . $arSqls["GROUPBY"];
}
if (!empty($arSqls["ORDERBY"])) {
$strSql .= " ORDER BY " . $arSqls["ORDERBY"];
} elseif (strlen($arSqls["ORDERBY"]) <= 0 && strlen($strSqlUfOrder) > 0) {
$strSql .= " ORDER BY " . $strSqlUfOrder;
}
$intTopCount = 0;
$boolNavStartParams = !empty($arNavStartParams) && is_array($arNavStartParams);
if ($boolNavStartParams && array_key_exists('nTopCount', $arNavStartParams)) {
$intTopCount = intval($arNavStartParams["nTopCount"]);
}
if ($boolNavStartParams && 0 >= $intTopCount) {
$strSql_tmp = "SELECT COUNT('x') as CNT FROM b_catalog_store CS " . $arSqls["FROM"] . " " . $userField->GetJoin("CS.ID");
if (!empty($arSqls["WHERE"])) {
$strSql_tmp .= " WHERE " . $arSqls["WHERE"];
}
if (strlen($arSqls["WHERE"]) > 0 && strlen($strSqlUfFilter) > 0) {
$strSql_tmp .= " AND " . $strSqlUfFilter . " ";
} elseif (strlen($arSqls["WHERE"]) <= 0 && strlen($strSqlUfFilter) > 0) {
$strSql_tmp .= " WHERE " . $strSqlUfFilter . " ";
}
if (!empty($arSqls["GROUPBY"])) {
$strSql_tmp .= " GROUP BY " . $arSqls["GROUPBY"];
//.........这里部分代码省略.........
示例11: GetList
//.........这里部分代码省略.........
$filterByBaseTemplate = true;
}
if ($keyParsed['FIELD'] == 'TEMPLATE_CHILDREN_COUNT') {
$useChildrenCount = true;
}
}
$includeSubtree = $arParams['INCLUDE_TEMPLATE_SUBTREE'] === true || $arParams['INCLUDE_TEMPLATE_SUBTREE'] === 'Y';
$excludeSubtree = $arParams['EXCLUDE_TEMPLATE_SUBTREE'] === true || $arParams['EXCLUDE_TEMPLATE_SUBTREE'] === 'Y';
$treeJoin = '';
if ($excludeSubtree) {
$treeJoin = "";
} else {
$treeJoin = "LEFT JOIN " . Template\DependencyTable::getTableName() . " TD on TT.ID = TD.TEMPLATE_ID" . ($includeSubtree ? "" : " AND TD.DIRECT = '1'");
}
$temporalTableName = \Bitrix\Tasks\DB\Helper::getTemporaryTableNameSql();
$strFrom = "FROM\n\t\t\t\tb_tasks_template TT\n\n\t\t\t" . $treeJoin . "\n\n\t\t\t" . ($selectBaseTemplateId ? "\n\t\t\tLEFT JOIN\n\t\t\t\t" . Template\DependencyTable::getTableName() . " TDD ON TT.ID = TDD.TEMPLATE_ID AND TDD.DIRECT = '1'\n\t\t\t" : "\n\t\t\t") . "\n\n\t\t\t" . ($useChildrenCount ? "\n\t\t\t\tLEFT JOIN (\n\t\t\t\t\tSELECT TTI.ID, COUNT(TDDC.TEMPLATE_ID) AS TEMPLATE_CHILDREN_COUNT\n\t\t\t\t\tfrom\n\t\t\t\t\t\tb_tasks_template TTI\n\t\t\t\t\t\tINNER JOIN " . Template\DependencyTable::getTableName() . " TDDC ON TTI.ID = TDDC.PARENT_TEMPLATE_ID AND TDDC.DIRECT = '1'\n\t\t\t\t\tGROUP BY TTI.ID\n\t\t\t\t) " . $temporalTableName . " on " . $temporalTableName . ".ID = TT.ID\n\t\t\t" : "\n\t\t\t") . "\n\n\t\t\tLEFT JOIN\n\t\t\t\tb_user CU ON CU.ID = TT.CREATED_BY\n\t\t\tLEFT JOIN\n\t\t\t\tb_user RU ON RU.ID = TT.RESPONSIBLE_ID\n\t\t\t\n\t\t\t" . $obUserFieldsSql->GetJoin("TT.ID") . "\n\n\t\t\t" . (sizeof($arSqlSearch) ? "WHERE " . implode(" AND ", $arSqlSearch) : "") . " ";
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if ($by == "task") {
$arSqlOrder[] = " TT " . $order . " ";
} elseif ($by == "title") {
$arSqlOrder[] = " TT.TITLE " . $order . " ";
} elseif ($by == "depends_on") {
$arSqlOrder[] = " TT.DEPENDS_ON " . $order . " ";
} elseif ($by == "rand") {
$arSqlOrder[] = CTasksTools::getRandFunction();
} elseif ($by === 'responsible_last_name') {
$arSqlOrder[] = " RU.LAST_NAME " . $order . " ";
} elseif ($by === 'tparam_type') {
$arSqlOrder[] = " TT.TPARAM_TYPE " . $order . " ";
} elseif ($by === 'template_children_count') {
$arSqlOrder[] = " TEMPLATE_CHILDREN_COUNT " . $order . " ";
} elseif ($by === 'base_template_id') {
$arSqlOrder[] = " BASE_TEMPLATE_ID " . $order . " ";
} elseif (substr($by, 0, 3) === 'uf_') {
if ($s = $obUserFieldsSql->GetOrder($by)) {
$arSqlOrder[$by] = " " . $s . " " . $order . " ";
}
} else {
$arSqlOrder[] = " TT.ID " . $order . " ";
$by = "id";
}
if ($by !== 'rand' && !in_array(strtoupper($by), $arSelect)) {
$arSelect[] = strtoupper($by);
}
}
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
$arSqlOrderCnt = count($arSqlOrder);
for ($i = 0; $i < $arSqlOrderCnt; $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= $arSqlOrder[$i];
}
if (!in_array("ID", $arSelect)) {
$arSelect[] = "ID";
}
$arSqlSelect = array();
foreach ($arSelect as $field) {
$field = strtoupper($field);
if (array_key_exists($field, $arFields)) {
$arSqlSelect[$field] = \Bitrix\Tasks\DB\Helper::wrapColumnWithFunction($arFields[$field]['FIELD'], $arFields[$field]['WRAP']) . " AS " . $field;
}
}
if (!sizeof($arSqlSelect)) {
$arSqlSelect = "TT.ID AS ID";
} else {
$arSqlSelect = implode(",\n", $arSqlSelect);
}
$ufSelect = $obUserFieldsSql->GetSelect();
if (strlen($ufSelect)) {
$arSqlSelect .= $ufSelect;
}
$strSql = "\n\t\t\tSELECT \n\t\t\t\t" . $arSqlSelect . "\n\t\t\t\t" . $strFrom . "\n\t\t\t\t" . $strSqlOrder;
if (isset($arNavParams["NAV_PARAMS"]) && is_array($arNavParams["NAV_PARAMS"])) {
$nTopCount = (int) $arNavParams['NAV_PARAMS']['nTopCount'];
if ($nTopCount > 0) {
$strSql = $DB->TopSql($strSql, $nTopCount);
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("TASKS_TASK_TEMPLATE"));
} else {
$res_cnt = $DB->Query("SELECT COUNT(TT.ID) as C " . $strFrom);
$res_cnt = $res_cnt->Fetch();
$res = new CDBResult();
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("TASKS_TASK_TEMPLATE"));
$res->NavQuery($strSql, $res_cnt["C"], $arNavParams["NAV_PARAMS"]);
}
} else {
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("TASKS_TASK_TEMPLATE"));
}
return $res;
}示例12: GetList
//.........这里部分代码省略.........
$arSqlOrder[] = " PRIORITY " . $order . " ";
$needle = 'PRIORITY';
break;
case 'mark':
$arSqlOrder[] = " MARK " . $order . " ";
$needle = 'MARK';
break;
case 'created_by':
$arSqlOrder[] = " CREATED_BY_LAST_NAME " . $order . " ";
$needle = 'CREATED_BY_LAST_NAME';
break;
case 'responsible_id':
$arSqlOrder[] = " RESPONSIBLE_LAST_NAME " . $order . " ";
$needle = 'RESPONSIBLE_LAST_NAME';
break;
case 'group_id':
$arSqlOrder[] = " GROUP_ID " . $order . " ";
$needle = 'GROUP_ID';
break;
case 'time_estimate':
$arSqlOrder[] = " TIME_ESTIMATE " . $order . " ";
$needle = 'TIME_ESTIMATE';
break;
case 'allow_change_deadline':
$arSqlOrder[] = " ALLOW_CHANGE_DEADLINE " . $order . " ";
$needle = 'ALLOW_CHANGE_DEADLINE';
break;
case 'allow_time_tracking':
$arSqlOrder[] = " ALLOW_TIME_TRACKING " . $order . " ";
$needle = 'ALLOW_TIME_TRACKING';
break;
default:
if (substr($by, 0, 3) === 'uf_') {
if ($s = $obUserFieldsSql->GetOrder($by)) {
$arSqlOrder[$by] = " " . $s . " " . $order . " ";
}
} else {
CTaskAssert::logWarning('[0x9a92cf7d] invalid sort by field requested: ' . $by);
}
break;
}
if ($needle !== null && !in_array($needle, $arSelect)) {
$arSelect[] = $needle;
}
}
$arSqlSelect = array();
foreach ($arSelect as $field) {
$field = strtoupper($field);
if (array_key_exists($field, $arFields)) {
$arSqlSelect[$field] = $arFields[$field] . " AS " . $field;
}
}
if (!sizeof($arSqlSelect)) {
$arSqlSelect = "T.ID AS ID";
}
// First level logic MUST be 'AND', because of backward compatibility
// and some requests for checking rights, attached at first level of filter.
// Situtation when there is OR-logic at first level cannot be resolved
// in general case.
// So if there is OR-logic, it is FATAL error caused by programmer.
// But, if you want to use OR-logic at the first level of filter, you
// can do this by putting all your filter conditions to the ::SUBFILTER-xxx,
// except CHECK_PERMISSIONS, SUBORDINATE_TASKS (if you don't know exactly,
// what are consequences of this fields in OR-logic of subfilters).
if (isset($arFilter['::LOGIC'])) {
CTaskAssert::assert($arFilter['::LOGIC'] === 'AND');