本文整理汇总了PHP中CUserTypeSQL::GetSelect方法的典型用法代码示例。如果您正苦于以下问题：PHP CUserTypeSQL::GetSelect方法的具体用法？PHP CUserTypeSQL::GetSelect怎么用？PHP CUserTypeSQL::GetSelect使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类CUserTypeSQL的用法示例。
在下文中一共展示了CUserTypeSQL::GetSelect方法的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的PHP代码示例。
示例1: PrepareSql
//.........这里部分代码省略.........
$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);
}
if (!isset($arSelectFields) || !is_array($arSelectFields) || count($arSelectFields) <= 0 || in_array("*", $arSelectFields)) {
$tmp_count = count($arFieldsKeys);
for ($i = 0; $i < $tmp_count; $i++) {
if (isset($arFields[$arFieldsKeys[$i]]["WHERE_ONLY"]) && $arFields[$arFieldsKeys[$i]]["WHERE_ONLY"] == "Y") {
continue;
}
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
if ($arFields[$arFieldsKeys[$i]]["TYPE"] == "datetime") {
if (array_key_exists($arFieldsKeys[$i], $arOrder)) {
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"] . " as " . $arFieldsKeys[$i] . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "FULL") . " as " . $arFieldsKeys[$i];
} elseif ($arFields[$arFieldsKeys[$i]]["TYPE"] == "date") {
if (array_key_exists($arFieldsKeys[$i], $arOrder)) {
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"] . " as " . $arFieldsKeys[$i] . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "SHORT") . " as " . $arFieldsKeys[$i];
} else {
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"] . " as " . $arFieldsKeys[$i];
}
if (isset($arFields[$arFieldsKeys[$i]]["FROM"]) && strlen($arFields[$arFieldsKeys[$i]]["FROM"]) > 0 && !in_array($arFields[$arFieldsKeys[$i]]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$arFieldsKeys[$i]]["FROM"];
$arAlreadyJoined[] = $arFields[$arFieldsKeys[$i]]["FROM"];
}
}
} else {
foreach ($arSelectFields as $key => $val) {
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields)) {
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
if (in_array($key, $arGroupByFunct)) {
$strSqlSelect .= $key . "(" . $arFields[$val]["FIELD"] . ") as " . $val;
} else {
if ($arFields[$val]["TYPE"] == "datetime") {
if (array_key_exists($val, $arOrder)) {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL") . " as " . $val;
} elseif ($arFields[$val]["TYPE"] == "date") {
if (array_key_exists($val, $arOrder)) {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val . "_X1, ";
}
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "SHORT") . " as " . $val;
} else {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val;
}
}
if (isset($arFields[$val]["FROM"]) && strlen($arFields[$val]["FROM"]) > 0 && !in_array($arFields[$val]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
if ($obUserFieldsSql) {
$strSqlSelect .= (strlen($strSqlSelect) <= 0 ? $arFields["ID"]["FIELD"] : "") . $obUserFieldsSql->GetSelect();
}
if (strlen($strSqlGroupBy) > 0) {
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
$strSqlSelect .= "COUNT(%%_DISTINCT_%% " . $arFields[$arFieldsKeys[0]]["FIELD"] . ") as CNT";
} else {
$strSqlSelect = "%%_DISTINCT_%% " . $strSqlSelect;
}
}
// <-- SELECT
if ($obUserFieldsSql) {
$strSqlFrom .= " " . $obUserFieldsSql->GetJoin($arFields["ID"]["FIELD"]);
}
return array("SELECT" => $strSqlSelect, "FROM" => $strSqlFrom, "WHERE" => $strSqlWhere, "GROUPBY" => $strSqlGroupBy, "ORDERBY" => $strSqlOrderBy);
}示例2: GetListUni
//.........这里部分代码省略.........
$modeSQL_defaultSortField = "TLE.SORT";
// as SORT
$arFieldsMap['EDGE_SORT'] = 'TLE.SORT';
$arFieldsMap['SORT'] = 'TLE.SORT';
}
if ($bReplaceCourseId) {
$arFieldsMap['ID'] = $arFieldsMap['COURSE_ID'];
}
// Select all fields by default
if (count($arSelectFields) == 0) {
$arSelectFields = array_keys($arFieldsMap);
}
// Ensure that all order fields will be selected
foreach ($arOrder as $by => $order) {
$fieldName = strtoupper($by);
if (!in_array($fieldName, $arSelectFields)) {
$arSelectFields[] = $fieldName;
}
}
// Build list of fields to be selected
$strSqlSelect = '';
$bFirstPass = true;
$bDefaultSortFieldSelected = false;
foreach ($arSelectFields as $selectFieldName) {
if (substr($selectFieldName, 0, 3) === 'UF_') {
continue;
}
if (!$bFirstPass) {
$strSqlSelect .= ', ';
} else {
$bFirstPass = false;
}
if (!isset($arFieldsMap[$selectFieldName])) {
throw new LearnException('EA_OTHER: UNKNOWN FIELD: ' . $selectFieldName, LearnException::EXC_ERR_ALL_GIVEUP);
}
$strSqlSelect .= $arFieldsMap[$selectFieldName] . ' AS ' . $selectFieldName;
if ($selectFieldName === 'SORT' && $arFieldsMap[$selectFieldName] === $modeSQL_defaultSortField) {
$bDefaultSortFieldSelected = true;
}
}
if (!$bDefaultSortFieldSelected) {
if ($strSqlSelect !== '') {
$strSqlSelect .= ', ';
}
$strSqlSelect .= $modeSQL_defaultSortField . ' AS SORT';
}
$strSqlSelect .= $obUserFieldsSql->GetSelect();
$sqlLangConstraint = '';
if (strlen($SqlSearchLang) > 2) {
$sqlLangConstraint = "\n\t\t\tAND\n\t\t\tEXISTS\n\t\t\t(\n\t\t\t\tSELECT 'x' FROM b_learn_course_site TCS\n\t\t\t\tWHERE TC.ID = TCS.COURSE_ID AND TCS.SITE_ID IN (" . $SqlSearchLang . ")\n\t\t\t)\n\t\t\t";
}
$strSqlFrom = "FROM b_learn_lesson TL\n\t\t\tLEFT JOIN b_learn_course TC \n\t\t\t\tON TC.LINKED_LESSON_ID = TL.ID\n\t\t\tLEFT JOIN b_user TU \n\t\t\t\tON TU.ID = TL.CREATED_BY " . $modeSQL_join . $obUserFieldsSql->GetJoin("TL.ID") . " WHERE 1 = 1 " . $sqlLangConstraint . $modeSQL_where;
// for getting only parents/childs, if need
if ($oPermParser->IsNeedCheckPerm()) {
$strSqlFrom .= " AND TL.ID IN (" . $oPermParser->SQLForAccessibleLessons() . ") ";
}
$strSqlFrom .= $sqlSearch;
$sql = "SELECT " . $strSqlSelect . " " . $strSqlFrom;
$arSqlOrder = array();
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
$order = strtolower($order);
if ($order !== 'asc') {
$order = 'desc';
}
if ($s = $obUserFieldsSql->getOrder(strtolower($by))) {
$arSqlOrder[] = ' ' . $s . ' ' . $order . ' ';
}
if (substr($by, 0, 3) !== 'UF_') {
if (!isset($arMap[$by])) {
throw new LearnException('EA_PARAMS: unknown order by field: "' . $by . '"', LearnException::EXC_ERR_ALL_PARAMS);
}
}
$arSqlOrder[] = ' ' . $arMap[$by] . ' ' . $order . ' ';
}
// on duplicate first occured FIELD will be used according to function description
DelDuplicateSort($arSqlOrder);
$sql .= ' ORDER BY ' . implode(', ', $arSqlOrder);
if (is_array($arNavParams) && !empty($arNavParams)) {
if (isset($arNavParams['nTopCount']) && (int) $arNavParams['nTopCount'] > 0) {
$sql = $DB->TopSql($sql, (int) $arNavParams['nTopCount']);
$res = $DB->Query($sql, true);
} else {
$res_cnt = $DB->Query("SELECT COUNT(TL.ID) as C " . $strSqlFrom);
$res_cnt = $res_cnt->fetch();
$res = new CDBResult();
$rc = $res->NavQuery($sql, $res_cnt['C'], $arNavParams, true);
if ($rc === false) {
throw new LearnException('EA_SQLERROR', LearnException::EXC_ERR_ALL_GIVEUP);
}
}
} else {
$res = $DB->Query($sql, true);
}
if ($res === false) {
throw new LearnException('EA_SQLERROR', LearnException::EXC_ERR_ALL_GIVEUP);
}
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields('LEARNING_LESSONS'));
return $res;
}示例3: GetList
//.........这里部分代码省略.........
* {
* echo "Question name: ".$arQuestion["NAME"]."<br>";
* }
* }
*
* ?>
* </pre>
*
*
* <h4>See Also</h4>
* <ul> <li> <a href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a> </li> <li> <a
* href="http://dev.1c-bitrix.ru/api_help/learning/classes/clquestion/index.php">CLQuestion</a>::<a
* href="http://dev.1c-bitrix.ru/api_help/learning/classes/clquestion/getbyid.php">GetByID</a> </li> <li> <a
* href="http://dev.1c-bitrix.ru/api_help/learning/fields.php#question">Поля вопроса</a> </li> </ul> <a
* name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/learning/classes/clquestion/getlist.php
* @author Bitrix
*/
public static function GetList($arOrder = array(), $arFilter = array(), $bHz = false, $arNavParams = array(), $arSelect = array())
{
global $DB, $USER, $USER_FIELD_MANAGER;
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity('LEARNING_QUESTIONS', "CQ.ID");
$obUserFieldsSql->SetSelect($arSelect);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
$arSqlSearch = array_filter(CLQuestion::GetFilter($arFilter));
$strSqlFrom = "FROM b_learn_question CQ " . "INNER JOIN b_learn_lesson CL ON CQ.LESSON_ID = CL.ID " . $obUserFieldsSql->GetJoin("CQ.ID") . " WHERE ";
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$arSqlSearch[] = "(" . $r . ")";
}
if (!empty($arSqlSearch)) {
$strSqlFrom .= implode(' AND ', $arSqlSearch);
} else {
$strSqlFrom .= ' 1=1 ';
}
$strSql = "SELECT CQ.ID, CQ.ACTIVE, CQ.LESSON_ID, CQ.QUESTION_TYPE,\n\t\t\t\tCQ.NAME, CQ.SORT, CQ.DESCRIPTION, CQ.DESCRIPTION_TYPE,\n\t\t\t\tCQ.COMMENT_TEXT, CQ.FILE_ID, CQ.SELF, CQ.POINT, CQ.DIRECTION,\n\t\t\t\tCQ.CORRECT_REQUIRED, CQ.EMAIL_ANSWER, CQ.INCORRECT_MESSAGE," . $DB->DateToCharFunction("CQ.TIMESTAMP_X") . " as TIMESTAMP_X " . $obUserFieldsSql->GetSelect() . " " . $strSqlFrom;
if (!is_array($arOrder)) {
$arOrder = array();
}
foreach ($arOrder as $by => $order) {
$by = strtolower($by);
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if ($by == "id") {
$arSqlOrder[] = " CQ.ID " . $order . " ";
} elseif ($by == "name") {
$arSqlOrder[] = " CQ.NAME " . $order . " ";
} elseif ($by == "sort") {
$arSqlOrder[] = " CQ.SORT " . $order . " ";
} elseif ($by == "point") {
$arSqlOrder[] = " CQ.POINT " . $order . " ";
} elseif ($by == "type") {
$arSqlOrder[] = " CQ.QUESTION_TYPE " . $order . " ";
} elseif ($by == "self") {
$arSqlOrder[] = " CQ.SELF " . $order . " ";
} elseif ($by == "active") {
$arSqlOrder[] = " CQ.ACTIVE " . $order . " ";
} elseif ($by == "correct_required") {
$arSqlOrder[] = " CQ.CORRECT_REQUIRED " . $order . " ";
} elseif ($s = $obUserFieldsSql->getOrder($by)) {
$arSqlOrder[] = ' ' . $s . ' ' . $order . ' ';
} else {
$arSqlOrder[] = " CQ.TIMESTAMP_X " . $order . " ";
}
}
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
$cnt = count($arSqlOrder);
for ($i = 0; $i < $cnt; $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= $strSqlOrder;
if (is_array($arNavParams) && !empty($arNavParams)) {
if (isset($arNavParams['nTopCount']) && (int) $arNavParams['nTopCount'] > 0) {
$strSql = $DB->TopSql($strSql, (int) $arNavParams['nTopCount']);
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
} else {
$res_cnt = $DB->Query("SELECT COUNT(CQ.ID) as C " . $strSqlFrom);
$res_cnt = $res_cnt->fetch();
$res = new CDBResult();
$res->NavQuery($strSql, $res_cnt['C'], $arNavParams);
}
} else {
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields('LEARNING_QUESTIONS'));
return $res;
}示例4: GetList
//.........这里部分代码省略.........
$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;
}
// add permissions check
if (!($bAdmin == 'Y' || $bDemo == 'Y')) {
// a list of users who own or are responsible for tickets, which we can show to our current user
$ticketUsers = array($uid);
// check if user has groups
$result = $DB->Query('SELECT GROUP_ID FROM b_ticket_user_ugroup WHERE USER_ID = ' . $uid . ' AND CAN_VIEW_GROUP_MESSAGES = \'Y\'');
if ($result) {
// collect members of these groups
$uGroups = array();
while ($row = $result->Fetch()) {
$uGroups[] = $row['GROUP_ID'];
}
if (!empty($uGroups)) {
$result = $DB->Query('SELECT USER_ID FROM b_ticket_user_ugroup WHERE GROUP_ID IN (' . join(',', $uGroups) . ')');
if ($result) {
while ($row = $result->Fetch()) {
$ticketUsers[] = $row['USER_ID'];
}
}
}
}
// build sql
$strSqlSearchUser = "";
if ($bSupportTeam == 'Y') {
$strSqlSearchUser = 'T.RESPONSIBLE_USER_ID IN (' . join(',', $ticketUsers) . ')';
} elseif ($bSupportClient == 'Y') {
$strSqlSearchUser = 'T.OWNER_USER_ID IN (' . join(',', $ticketUsers) . ')';
}
$arSqlSearch[] = $strSqlSearchUser;
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$onlineInterval = intval(COption::GetOptionString("support", "ONLINE_INTERVAL"));
$strSqlSelect = "\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\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\n\t\t\t\t\tTO_DAYS(\n\t\t\t\t\t\tADDDATE(\n\t\t\t\t\t\t\tT.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY\n\t\t\t\t\t\t)\n\t\t\t\t\t) - TO_DAYS(now())\n\t\t\t\tELSE -1 END AUTO_CLOSE_DAYS_LEFT,\n\t\t\t\t(SELECT COUNT(DISTINCT USER_ID) FROM b_ticket_online WHERE TICKET_ID = T.ID AND TIMESTAMP_X >= DATE_ADD(now(), INTERVAL - " . $onlineInterval . " SECOND)) USERS_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();
$strSqlFrom = "\n\t\t\tFROM\n\t\t\t\tb_ticket T\n\t\t\t{$u_join}\n\t\t\t{$d_join}\n\t\t\t{$messJoin}\n\t\t\t{$searchJoin}\n\t\t\t{$ugroupJoin}\n\t\t\t\t" . $obUserFieldsSql->GetJoin("T.ID");
$strSqlWhere = "\n\t\t\tWHERE\n\t\t\t{$strSqlSearch}\n\t\t";
$strSqlGroup = $need_group ? ' GROUP BY T.ID ' : '';
$strSqlHaving = $arSqlHaving ? ' HAVING ' . join(' AND ', $arSqlHaving) . ' ' : '';
$strSql = $strSqlSelect . $strSqlFrom . $strSqlWhere . $strSqlGroup . $strSqlHaving . $strSqlOrder;
if (is_array($arParams) && isset($arParams["NAV_PARAMS"]) && is_array($arParams["NAV_PARAMS"])) {
$nTopCount = isset($arParams['NAV_PARAMS']['nTopCount']) ? intval($arParams['NAV_PARAMS']['nTopCount']) : 0;
if ($nTopCount > 0) {
$strSql = $DB->TopSql($strSql, $nTopCount);
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("SUPPORT"));
} else {
$cntSql = "SELECT COUNT(T.ID) as C " . $strSqlFrom . $strSqlWhere . $strSqlGroup . $strSqlHaving;
if (!empty($strSqlGroup)) {
$cntSql = 'SELECT COUNT(1) AS C FROM (' . $cntSql . ') tt';
}
$res_cnt = $DB->Query($cntSql);
$res_cnt = $res_cnt->Fetch();
$res = new CDBResult();
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("SUPPORT"));
$res->NavQuery($strSql, $res_cnt["C"], $arParams["NAV_PARAMS"]);
}
} else {
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("SUPPORT"));
}
$isFiltered = IsFiltered($strSqlSearch);
return $res;
}示例5: 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;
}示例6: GetList
//.........这里部分代码省略.........
$strProp1 .= "\n\t\t\t\tLEFT JOIN b_iblock_property FP" . $i . " ON FP" . $i . ".IBLOCK_ID=B.ID AND\n\t\t\t\t" . (IntVal($propID) > 0 ? " FP" . $i . ".ID=" . IntVal($propID) . " " : " FP" . $i . ".CODE='" . $DB->ForSQL($propID, 200) . "' ") . "\n\t\t\t\tLEFT JOIN " . $strTable . " FPV" . $i . " ON FP" . $i . ".ID=FPV" . $i . ".IBLOCK_PROPERTY_ID AND FPV" . $i . ".IBLOCK_ELEMENT_ID=BE.ID ";
}
if ($bJoinFlatProp) {
$strProp1 .= "\n\t\t\t\tLEFT JOIN b_iblock_element_prop_s" . $bJoinFlatProp . " FPS ON FPS.IBLOCK_ELEMENT_ID = BE.ID\n\t\t\t";
}
$arFields = array("ID" => "BS.ID", "CODE" => "BS.CODE", "XML_ID" => "BS.XML_ID", "EXTERNAL_ID" => "BS.XML_ID", "IBLOCK_ID" => "BS.IBLOCK_ID", "IBLOCK_SECTION_ID" => "BS.IBLOCK_SECTION_ID", "TIMESTAMP_X" => $DB->DateToCharFunction("BS.TIMESTAMP_X"), "TIMESTAMP_X_UNIX" => 'UNIX_TIMESTAMP(BS.TIMESTAMP_X)', "SORT" => "BS.SORT", "NAME" => "BS.NAME", "ACTIVE" => "BS.ACTIVE", "GLOBAL_ACTIVE" => "BS.GLOBAL_ACTIVE", "PICTURE" => "BS.PICTURE", "DESCRIPTION" => "BS.DESCRIPTION", "DESCRIPTION_TYPE" => "BS.DESCRIPTION_TYPE", "LEFT_MARGIN" => "BS.LEFT_MARGIN", "RIGHT_MARGIN" => "BS.RIGHT_MARGIN", "DEPTH_LEVEL" => "BS.DEPTH_LEVEL", "SEARCHABLE_CONTENT" => "BS.SEARCHABLE_CONTENT", "MODIFIED_BY" => "BS.MODIFIED_BY", "DATE_CREATE" => $DB->DateToCharFunction("BS.DATE_CREATE"), "DATE_CREATE_UNIX" => 'UNIX_TIMESTAMP(BS.DATE_CREATE)', "CREATED_BY" => "BS.CREATED_BY", "DETAIL_PICTURE" => "BS.DETAIL_PICTURE", "TMP_ID" => "BS.TMP_ID", "LIST_PAGE_URL" => "B.LIST_PAGE_URL", "SECTION_PAGE_URL" => "B.SECTION_PAGE_URL", "IBLOCK_TYPE_ID" => "B.IBLOCK_TYPE_ID", "IBLOCK_CODE" => "B.CODE", "IBLOCK_EXTERNAL_ID" => "B.XML_ID", "SOCNET_GROUP_ID" => "BS.SOCNET_GROUP_ID");
$arSqlSelect = array();
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") {示例7: 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;
}示例8: GetList
//.........这里部分代码省略.........
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";
$sSelect .= ", RR" . $ratingId . ".CURRENT_VALUE as RATING_" . $ratingId;
$sSelect .= ", RR" . $ratingId . ".CURRENT_VALUE as RATING_" . $ratingId . "_CURRENT_VALUE";
$sSelect .= ", RR" . $ratingId . ".PREVIOUS_VALUE as RATING_" . $ratingId . "_PREVIOUS_VALUE";
$sSelect .= ", RR" . $ratingId . ".CURRENT_POSITION as RATING_" . $ratingId . "_CURRENT_POSITION";
$sSelect .= ", RR" . $ratingId . ".PREVIOUS_POSITION as RATING_" . $ratingId . "_PREVIOUS_POSITION";
$strJoin .= " LEFT JOIN b_rating_results RR" . $ratingId . "\n\t\t\t\t\t\t\tON RR" . $ratingId . ".RATING_ID=" . $ratingId . "\n\t\t\t\t\t\t\tand RR" . $ratingId . ".ENTITY_TYPE_ID = 'USER'\n\t\t\t\t\t\t\tand RR" . $ratingId . ".ENTITY_ID = U.ID ";
$arRatingInSelect[] = $ratingId;
}
}
}
}
$strFrom = "\n\t\t\tFROM\n\t\t\t\tb_user U\n\t\t\t\t" . $obUserFieldsSql->GetJoin("U.ID") . "\n\t\t\t\t" . $strJoin . "\n\t\t\tWHERE\n\t\t\t\t" . $strSqlSearch . "\n\t\t\t";
$strSqlOrder = '';
if (!empty($arSqlOrder)) {
$strSqlOrder = 'ORDER BY ' . implode(', ', $arSqlOrder);
}
$strSql = "SELECT " . $sSelect . $strFrom . $strSqlOrder;
if (array_key_exists("NAV_PARAMS", $arParams) && is_array($arParams["NAV_PARAMS"])) {
$nTopCount = intval($arParams['NAV_PARAMS']['nTopCount']);
if ($nTopCount > 0) {
$strSql = $DB->TopSql($strSql, $nTopCount);
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
if ($userFieldsSelect != '') {
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("USER"));
}
} else {
$res_cnt = $DB->Query("SELECT COUNT(" . ($obUserFieldsSql->GetDistinct() ? "DISTINCT " : "") . "U.ID) as C " . $strFrom);
$res_cnt = $res_cnt->Fetch();
$res = new CDBResult();
if ($userFieldsSelect != '') {
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("USER"));
}
$res->NavQuery($strSql, $res_cnt["C"], $arParams["NAV_PARAMS"]);
}
} else {
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
if ($userFieldsSelect != '') {
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("USER"));
}
}
$res->is_filtered = IsFiltered($strSqlSearch);
return $res;
}示例9: getList
/**
* Get list of existing learning groups
*
* @param array $arOrder
* @param array $arFilter
* @param array $arSelect
* @param array $arNavParams
*
* @return CDBResult
*/
public static function getList($arOrder, $arFilter, $arSelect = array(), $arNavParams = array())
{
global $DB, $USER, $USER_FIELD_MANAGER;
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity("LEARNING_LGROUPS", "LG.ID");
$obUserFieldsSql->SetSelect($arSelect);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
$arFields = array('ID' => 'LG.ID', 'TITLE' => 'LG.TITLE', 'ACTIVE' => 'LG.ACTIVE', 'CODE' => 'LG.CODE', 'SORT' => 'LG.SORT', 'ACTIVE_FROM' => $DB->DateToCharFunction('LG.ACTIVE_FROM', 'FULL'), 'ACTIVE_TO' => $DB->DateToCharFunction('LG.ACTIVE_TO', 'FULL'), 'COURSE_LESSON_ID' => 'LG.COURSE_LESSON_ID', 'COURSE_TITLE' => 'LL.NAME', 'MEMBER_ID' => 'LGM.USER_ID');
$arFieldsSort = $arFields;
$arFieldsSort["ACTIVE_FROM"] = "LG.ACTIVE_FROM";
$arFieldsSort["ACTIVE_TO"] = "LG.ACTIVE_TO";
if (count($arSelect) <= 0 || in_array("*", $arSelect)) {
$arSelect = array_diff(array_keys($arFields), array('MEMBER_ID'));
} elseif (!in_array("ID", $arSelect)) {
$arSelect[] = "ID";
}
if (!is_array($arOrder)) {
$arOrder = array();
}
foreach ($arOrder as $by => $order) {
$by = (string) $by;
$byUppercase = strtoupper($by);
$needle = null;
$order = strtolower($order);
if ($order != "asc") {
$order = "desc";
}
if (array_key_exists($byUppercase, $arFieldsSort)) {
$arSqlOrder[] = ' ' . $arFieldsSort[$byUppercase] . ' ' . $order . ' ';
$needle = $byUppercase;
} elseif ($s = $obUserFieldsSql->getOrder(strtolower($by))) {
$arSqlOrder[] = ' ' . $s . ' ' . $order . ' ';
}
if ($needle !== null && !in_array($needle, $arSelect, true)) {
$arSelect[] = $needle;
}
}
if (isset($arFilter['MEMBER_ID']) && !in_array('MEMBER_ID', $arSelect, true)) {
$arSelect[] = 'MEMBER_ID';
}
$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 = 'LG.ID AS ID';
}
$arSqlSearch = self::getFilter($arFilter);
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$arSqlSearch[] = "(" . $r . ")";
}
$strSql = "\n\t\t\tSELECT\n\t\t\t\t" . implode(",\n", $arSqlSelect) . "\n\t\t\t\t" . $obUserFieldsSql->GetSelect();
$strFrom = "\n\t\t\tFROM\n\t\t\t\tb_learn_groups LG\n\t\t\t\t";
if (in_array('COURSE_TITLE', $arSelect, true)) {
$strFrom .= "LEFT OUTER JOIN b_learn_lesson LL ON LL.ID = LG.COURSE_LESSON_ID \n";
}
if (in_array('MEMBER_ID', $arSelect, true)) {
$strFrom .= "LEFT JOIN b_learn_groups_member LGM ON LGM.LEARNING_GROUP_ID = LG.ID \n";
}
$strFrom .= $obUserFieldsSql->GetJoin("LG.ID") . " " . (sizeof($arSqlSearch) ? " WHERE " . implode(" AND ", $arSqlSearch) : "") . " ";
$strSql .= $strFrom;
$strSqlOrder = "";
DelDuplicateSort($arSqlOrder);
for ($i = 0, $arSqlOrderCnt = count($arSqlOrder); $i < $arSqlOrderCnt; $i++) {
if ($i == 0) {
$strSqlOrder = " ORDER BY ";
} else {
$strSqlOrder .= ",";
}
$strSqlOrder .= $arSqlOrder[$i];
}
$strSql .= $strSqlOrder;
if (count($arNavParams)) {
if (isset($arNavParams['nTopCount'])) {
$strSql = $DB->TopSql($strSql, (int) $arNavParams['nTopCount']);
$res = $DB->Query($strSql, $bIgnoreErrors = false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("LEARNING_LGROUPS"));
} else {
$res_cnt = $DB->Query("SELECT COUNT(LG.ID) as C " . $strFrom);
$res_cnt = $res_cnt->Fetch();
$res = new CDBResult();
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("LEARNING_LGROUPS"));
$rc = $res->NavQuery($strSql, $res_cnt["C"], $arNavParams, $bIgnoreErrors = false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
} else {
//.........这里部分代码省略.........
示例10: GetList
//.........这里部分代码省略.........
$obUserFieldsSql->SetSelect($arSelect);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
static $arFields = array("ID" => array("FIELD_NAME" => "M.ID", "FIELD_TYPE" => "int"), "MEMBER_ID" => array("FIELD_NAME" => "M.MEMBER_ID", "FIELD_TYPE" => "string"), "SECRET_ID" => array("FIELD_NAME" => "M.SECRET_ID", "FIELD_TYPE" => "string"), "NAME" => array("FIELD_NAME" => "M.NAME", "FIELD_TYPE" => "string"), "EMAIL" => array("FIELD_NAME" => "M.EMAIL", "FIELD_TYPE" => "string"), "CONTACT_PERSON" => array("FIELD_NAME" => "M.CONTACT_PERSON", "FIELD_TYPE" => "string"), "URL" => array("FIELD_NAME" => "M.URL", "FIELD_TYPE" => "string"), "CONTROLLER_GROUP_ID" => array("FIELD_NAME" => "M.CONTROLLER_GROUP_ID", "FIELD_TYPE" => "int"), "CONTROLLER_GROUP_NAME" => array("FIELD_NAME" => "G.NAME", "FIELD_TYPE" => "string", "TABLE_ALIAS" => "G", "JOIN" => "INNER JOIN b_controller_group G ON G.ID = M.CONTROLLER_GROUP_ID", "LEFT_JOIN" => "LEFT JOIN b_controller_group G ON G.ID = M.CONTROLLER_GROUP_ID"), "IN_GROUP_FROM" => array("FIELD_NAME" => "M.IN_GROUP_FROM", "FIELD_TYPE" => "datetime", "FORMAT" => "FULL"), "SHARED_KERNEL" => array("FIELD_NAME" => "M.SHARED_KERNEL", "FIELD_TYPE" => "string"), "ACTIVE" => array("FIELD_NAME" => "M.ACTIVE", "FIELD_TYPE" => "string"), "SITE_ACTIVE" => array("FIELD_NAME" => "M.SITE_ACTIVE", "FIELD_TYPE" => "string"), "DISCONNECTED" => array("FIELD_NAME" => "M.DISCONNECTED", "FIELD_TYPE" => "string"), "DATE_ACTIVE_FROM" => array("FIELD_NAME" => "M.DATE_ACTIVE_FROM", "FIELD_TYPE" => "datetime", "FORMAT" => "SHORT"), "DATE_ACTIVE_TO" => array("FIELD_NAME" => "M.DATE_ACTIVE_TO", "FIELD_TYPE" => "datetime", "FORMAT" => "SHORT"), "TIMESTAMP_X" => array("FIELD_NAME" => "M.TIMESTAMP_X", "FIELD_TYPE" => "datetime", "FORMAT" => "FULL"), "MODIFIED_BY" => array("FIELD_NAME" => "M.MODIFIED_BY", "FIELD_TYPE" => "int"), "MODIFIED_BY_USER" => array("FIELD_TYPE" => "string"), "DATE_CREATE" => array("FIELD_NAME" => "M.DATE_CREATE", "FIELD_TYPE" => "datetime", "FORMAT" => "FULL"), "CREATED_BY" => array("FIELD_NAME" => "M.CREATED_BY", "FIELD_TYPE" => "int"), "CREATED_BY_USER" => array("FIELD_TYPE" => "string"), "COUNTER_FREE_SPACE" => array("FIELD_NAME" => "M.COUNTER_FREE_SPACE", "FIELD_TYPE" => "int"), "COUNTER_SITES" => array("FIELD_NAME" => "M.COUNTER_SITES", "FIELD_TYPE" => "int"), "COUNTER_USERS" => array("FIELD_NAME" => "M.COUNTER_USERS", "FIELD_TYPE" => "int"), "COUNTER_LAST_AUTH" => array("FIELD_NAME" => "M.COUNTER_LAST_AUTH", "FIELD_TYPE" => "datetime", "FORMAT" => "FULL"), "COUNTERS_UPDATED" => array("FIELD_NAME" => "M.COUNTERS_UPDATED", "FIELD_TYPE" => "datetime", "FORMAT" => "FULL"), "NOTES" => array("FIELD_NAME" => "M.NOTES"));
$arFields["MODIFIED_BY_USER"]["FIELD_NAME"] = $DB->Concat("'('", "UM.LOGIN", "') '", "UM.NAME", "' '", "UM.LAST_NAME");
$arFields["CREATED_BY_USER"]["FIELD_NAME"] = $DB->Concat("'('", "UC.LOGIN", "') '", "UC.NAME", "' '", "UC.LAST_NAME");
$rsCounters = CControllerCounter::GetList();
while ($arCounter = $rsCounters->Fetch()) {
$arFields["COUNTER_" . $arCounter["ID"]] = array("FIELD_NAME" => "CCV_" . $arCounter["ID"] . "." . CControllerCounter::GetTypeColumn($arCounter["COUNTER_TYPE"]), "FIELD_TYPE" => CControllerCounter::GetTypeUserType($arCounter["COUNTER_TYPE"]), "TABLE_ALIAS" => "CCV_" . $arCounter["ID"], "JOIN" => "INNER JOIN b_controller_counter_value CCV_" . $arCounter["ID"] . " ON CCV_" . $arCounter["ID"] . ".CONTROLLER_COUNTER_ID = " . $arCounter["ID"] . " AND CCV_" . $arCounter["ID"] . ".CONTROLLER_MEMBER_ID = M.ID", "LEFT_JOIN" => "LEFT JOIN b_controller_counter_value CCV_" . $arCounter["ID"] . " ON CCV_" . $arCounter["ID"] . ".CONTROLLER_COUNTER_ID = " . $arCounter["ID"] . " AND CCV_" . $arCounter["ID"] . ".CONTROLLER_MEMBER_ID = M.ID");
}
$obWhere = new CSQLWhere();
$obWhere->SetFields($arFields);
$arDateFields = array();
foreach ($arFields as $code => $arField) {
if ($arField["FIELD_TYPE"] == "datetime") {
$arDateFields[] = $code;
}
}
$date_field = "/(" . implode("|", $arDateFields) . ")\$/";
$arFilterNew = array();
if (is_array($arFilter)) {
foreach ($arFilter as $k => $value) {
if (is_array($value)) {
if (!empty($value)) {
$arFilterNew[$k] = $value;
}
} elseif ($value === false) {
$arFilterNew[$k] = $value;
} elseif (strlen($value) > 0) {
if (array_key_exists("date_format", $arOptions) && preg_match($date_field, $k)) {
$arFilterNew[$k] = ConvertTimeStamp(MakeTimeStamp($value, $arOptions["date_format"]), "FULL");
} else {
$arFilterNew[$k] = $value;
}
}
}
}
$strWhere = "1 = 1";
$r = $obWhere->GetQuery($arFilterNew);
if (strlen($r) > 0) {
$strWhere .= " AND (" . $r . ") ";
}
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$strWhere .= " AND (" . $r . ") ";
}
if (is_array($arOrder)) {
foreach ($arOrder as $key => $value) {
$key = strtoupper($key);
if (array_key_exists($key, $arFields) && isset($arFields[$key]["LEFT_JOIN"])) {
$obWhere->c_joins[$key]++;
}
}
}
if ($bEmptySelect) {
$arSelectAdd = array("ID", "MEMBER_ID", "SECRET_ID", "NAME", "URL", "EMAIL", "CONTACT_PERSON", "CONTROLLER_GROUP_ID", "DISCONNECTED", "SHARED_KERNEL", "ACTIVE", "DATE_ACTIVE_FROM", "DATE_ACTIVE_TO", "SITE_ACTIVE", "TIMESTAMP_X", "MODIFIED_BY", "DATE_CREATE", "CREATED_BY", "IN_GROUP_FROM", "NOTES", "COUNTER_FREE_SPACE", "COUNTER_SITES", "COUNTER_USERS", "COUNTER_LAST_AUTH", "COUNTERS_UPDATED", "MODIFIED_BY_USER", "CREATED_BY_USER");
if (is_array($arSelect)) {
$arSelect = array_merge($arSelect, $arSelectAdd);
} else {
$arSelect = $arSelectAdd;
}
}
$duplicates = array("ID" => 1);
$strSelect = "M.ID AS ID\n";
foreach ($arSelect as $key) {
$key = strtoupper($key);
if (array_key_exists($key, $arFields) && !array_key_exists($key, $duplicates)) {
$duplicates[$key]++;
if (isset($arFields[$key]["LEFT_JOIN"])) {
$obWhere->c_joins[$key]++;
}
if ($arFields[$key]["FIELD_TYPE"] == "datetime") {
if (array_key_exists("date_format", $arOptions)) {
$strSelect .= "," . $DB->DateFormatToDB($arOptions["date_format"], $arFields[$key]["FIELD_NAME"]) . " AS " . $key . "\n";
} else {
$strSelect .= "," . $arFields[$key]["FIELD_NAME"] . " AS " . $key . "_TMP," . $DB->DateToCharFunction($arFields[$key]["FIELD_NAME"], $arFields[$key]["FORMAT"]) . " AS " . $key . "\n";
}
} else {
$strSelect .= "," . $arFields[$key]["FIELD_NAME"] . " AS " . $key . "\n";
}
}
}
$bUseSubQuery = false;
if ($DB->type == "ORACLE" && $obUserFieldsSql->GetDistinct()) {
$bUseSubQuery = true;
}
if ($bUseSubQuery) {
$ob = new CUserTypeSQL();
$ob->SetEntity("CONTROLLER_MEMBER", "M.ID");
$ob->SetSelect($arSelect);
$ob->SetOrder($arOrder);
$strSql = "\n\t\t\t\tSELECT " . $strSelect . $ob->GetSelect() . "\n\t\t\t\tFROM b_controller_member M\n\t\t\t\t\tLEFT JOIN b_user UC ON UC.ID = M.CREATED_BY\n\t\t\t\t\tLEFT JOIN b_user UM ON UM.ID = M.MODIFIED_BY\n\t\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t\t" . $ob->GetJoin("M.ID") . "\n\t\t\t\tWHERE M.ID IN (\n\t\t\t\t\tSELECT M.ID\n\t\t\t\t\tFROM b_controller_member M\n\t\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t\t" . $obUserFieldsSql->GetJoin("M.ID") . "\n\t\t\t\t\tWHERE " . $strWhere . "\n\t\t\t\t)\n\t\t\t\t" . CControllerAgent::_OrderBy($arOrder, $arFields, $ob) . "\n\t\t\t";
} else {
$strSql = "\n\t\t\t\tSELECT " . ($obUserFieldsSql->GetDistinct() ? "DISTINCT" : "") . " " . $strSelect . $obUserFieldsSql->GetSelect() . "\n\t\t\t\tFROM b_controller_member M\n\t\t\t\t\tLEFT JOIN b_user UC ON UC.ID = M.CREATED_BY\n\t\t\t\t\tLEFT JOIN b_user UM ON UM.ID = M.MODIFIED_BY\n\t\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t\t" . $obUserFieldsSql->GetJoin("M.ID") . "\n\t\t\t\tWHERE " . $strWhere . "\n\t\t\t\t" . CControllerAgent::_OrderBy($arOrder, $arFields, $obUserFieldsSql) . "\n\t\t\t";
}
$dbr = $DB->Query($strSql);
$dbr->is_filtered = $strWhere != "1 = 1";
$dbr->SetUserFields($USER_FIELD_MANAGER->GetUserFields("CONTROLLER_MEMBER"));
return $dbr;
}示例11: GetList
public static function GetList($arOrder = array(), $arFilter = array(), $arSelect = array())
{
global $DB, $USER_FIELD_MANAGER;
$obUserFieldsSql = new CUserTypeSQL();
$obUserFieldsSql->SetEntity("CONTROLLER_GROUP", "G.ID");
$obUserFieldsSql->SetSelect($arSelect);
$obUserFieldsSql->SetFilter($arFilter);
$obUserFieldsSql->SetOrder($arOrder);
static $arFields = array("ID" => array("FIELD_NAME" => "G.ID", "FIELD_TYPE" => "int"), "NAME" => array("FIELD_NAME" => "G.NAME", "FIELD_TYPE" => "string"), "TIMESTAMP_X" => array("FIELD_NAME" => "G.TIMESTAMP_X", "FIELD_TYPE" => "datetime"), "MODIFIED_BY" => array("FIELD_NAME" => "G.MODIFIED_BY", "FIELD_TYPE" => "int"), "UPDATE_PERIOD" => array("FIELD_NAME" => "G.UPDATE_PERIOD", "FIELD_TYPE" => "int"), "MODIFIED_BY_USER" => array("FIELD_TYPE" => "string"), "DATE_CREATE" => array("FIELD_NAME" => "G.DATE_CREATE", "FIELD_TYPE" => "datetime"), "CREATED_BY" => array("FIELD_NAME" => "G.CREATED_BY", "FIELD_TYPE" => "int"), "CREATED_BY_USER" => array("FIELD_TYPE" => "string"), "TRIAL_PERIOD" => array("FIELD_NAME" => "G.TRIAL_PERIOD", "FIELD_TYPE" => "int"), "COUNTER_UPDATE_PERIOD" => array("FIELD_NAME" => "G.COUNTER_UPDATE_PERIOD", "FIELD_TYPE" => "int"), "CHECK_COUNTER_FREE_SPACE" => array("FIELD_NAME" => "G.CHECK_COUNTER_FREE_SPACE", "FIELD_TYPE" => "string"), "CHECK_COUNTER_SITES" => array("FIELD_NAME" => "G.CHECK_COUNTER_SITES", "FIELD_TYPE" => "string"), "CHECK_COUNTER_USERS" => array("FIELD_NAME" => "G.CHECK_COUNTER_USERS", "FIELD_TYPE" => "string"), "CHECK_COUNTER_LAST_AUTH" => array("FIELD_NAME" => "G.CHECK_COUNTER_LAST_AUTH", "FIELD_TYPE" => "string"));
$arFields["MODIFIED_BY_USER"]["FIELD_NAME"] = $DB->Concat("UM.LOGIN", "UM.NAME", "UM.LAST_NAME");
$arFields["CREATED_BY_USER"]["FIELD_NAME"] = $DB->Concat("UC.LOGIN", "UC.NAME", "UC.LAST_NAME");
$obWhere = new CSQLWhere();
$obWhere->SetFields($arFields);
$arFilterNew = array();
foreach ($arFilter as $k => $value) {
if (strlen($value) > 0 || $value === false) {
$arFilterNew[$k] = $value;
}
}
$strWhere = "1 = 1";
$r = $obWhere->GetQuery($arFilterNew);
if (strlen($r) > 0) {
$strWhere .= " AND (" . $r . ") ";
}
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$strWhere .= " AND (" . $r . ") ";
}
$strSql = "\n\t\t\tSELECT " . ($obUserFieldsSql->GetDistinct() ? "DISTINCT" : "") . " G.*\n\t\t\t\t,UC.LOGIN as CREATED_BY_LOGIN\n\t\t\t\t,UC.NAME as CREATED_BY_NAME\n\t\t\t\t,UC.LAST_NAME as CREATED_BY_LAST_NAME\n\t\t\t\t,UM.LOGIN as MODIFIED_BY_LOGIN\n\t\t\t\t,UM.NAME as MODIFIED_BY_NAME\n\t\t\t\t,UM.LAST_NAME as MODIFIED_BY_LAST_NAME\n\t\t\t\t," . $DB->DateToCharFunction("G.TIMESTAMP_X") . " as TIMESTAMP_X\n\t\t\t\t," . $DB->DateToCharFunction("G.DATE_CREATE") . " as DATE_CREATE\n\t\t\t\t" . $obUserFieldsSql->GetSelect() . "\n\t\t\tFROM b_controller_group G\n\t\t\t\tLEFT JOIN b_user UC ON UC.ID=G.CREATED_BY\n\t\t\t\tLEFT JOIN b_user UM ON UM.ID=G.MODIFIED_BY\n\t\t\t\t" . $obWhere->GetJoins() . "\n\t\t\t\t" . $obUserFieldsSql->GetJoin("G.ID") . "\n\t\t\tWHERE " . $strWhere . "\n\t\t\t" . CControllerAgent::_OrderBy($arOrder, $arFields, $obUserFieldsSql) . "\n\t\t";
$dbr = $DB->Query($strSql);
$dbr->is_filtered = strlen($strWhere) > 0;
return $dbr;
}示例12: GetList
//.........这里部分代码省略.........
$arSqlSearch[] = $q;
}
} elseif ($n == 'DAV_XML_ID' && is_array($val)) {
array_walk($val, array($DB, 'ForSql'));
$arSqlSearch[] = 'CE.DAV_XML_ID IN (\'' . implode('\',\'', $val) . '\')';
} elseif (isset($arFields[$n])) {
$arSqlSearch[] = GetFilterQuery($arFields[$n]["FIELD_NAME"], $val, 'N');
}
}
}
if ($getUF) {
$r = $obUserFieldsSql->GetFilter();
if (strlen($r) > 0) {
$arSqlSearch[] = "(" . $r . ")";
}
}
if ($skipDeclined) {
//$arSqlSearch[] = "(CE.IS_MEETING<>1 OR CE.MEETING_STATUS<>'N')";
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strOrderBy = '';
foreach ($arOrder as $by => $order) {
if (isset($arFields[strtoupper($by)])) {
$strOrderBy .= $arFields[strtoupper($by)]["FIELD_NAME"] . ' ' . (strtolower($order) == 'desc' ? 'desc' . (strtoupper($DB->type) == "ORACLE" ? " NULLS LAST" : "") : 'asc' . (strtoupper($DB->type) == "ORACLE" ? " NULLS FIRST" : "")) . ',';
}
}
if (strlen($strOrderBy) > 0) {
$strOrderBy = "ORDER BY " . rtrim($strOrderBy, ",");
}
$selectList = "";
foreach ($arFields as $field) {
$selectList .= $field['FIELD_NAME'] . ", ";
}
$strSql = "\n\t\t\t\tSELECT " . $selectList . "CES.SECT_ID, CES.REL\n\t\t\t\t\t" . ($getUF ? $obUserFieldsSql->GetSelect() : '') . "\n\t\t\t\tFROM\n\t\t\t\t\tb_calendar_event CE\n\t\t\t\tLEFT JOIN b_calendar_event_sect CES ON (CE.ID=CES.EVENT_ID)\n\t\t\t\t" . ($getUF ? $obUserFieldsSql->GetJoin("CE.ID") : '') . "\n\t\t\t\tWHERE\n\t\t\t\t\t{$strSqlSearch}\n\t\t\t\t{$strOrderBy}";
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
if ($getUF) {
$res->SetUserFields($USER_FIELD_MANAGER->GetUserFields("CALENDAR_EVENT"));
}
$arResult = array();
$arMeetingIds = array();
$arEvents = array();
$bIntranet = CCalendar::IsIntranetEnabled();
$defaultMeetingSection = false;
while ($event = $res->Fetch()) {
$event['IS_MEETING'] = intVal($event['IS_MEETING']) > 0;
if ($event['IS_MEETING'] && $event['CAL_TYPE'] == 'user' && $event['OWNER_ID'] == $userId && !$event['SECT_ID']) {
if (!$defaultMeetingSection) {
$defaultMeetingSection = CCalendar::GetMeetingSection($userId);
if (!$defaultMeetingSection || !CCalendarSect::GetById($defaultMeetingSection, false)) {
$sectRes = CCalendarSect::GetSectionForOwner($event['CAL_TYPE'], $userId);
$defaultMeetingSection = $sectRes['sectionId'];
}
}
self::ConnectEventToSection($event['ID'], $defaultMeetingSection);
$event['SECT_ID'] = $defaultMeetingSection;
}
$arEvents[] = $event;
if ($bIntranet && $event['IS_MEETING']) {
$arMeetingIds[] = $event['ID'];
}
}
if ($Params['fetchAttendees'] && count($arMeetingIds) > 0) {
$arAttendees = self::GetAttendees($arMeetingIds);
} else {
$arAttendees = array();
}示例13: 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"];
//.........这里部分代码省略.........
示例14: 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);
//.........这里部分代码省略.........
示例15: PrepareSql
//.........这里部分代码省略.........
if ($arFields[$arFieldsKeys[$i]]["TYPE"] == "datetime")
{
if (array_key_exists($arFieldsKeys[$i], $arOrder))
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i]."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "FULL")." as ".$arFieldsKeys[$i];
}
elseif ($arFields[$arFieldsKeys[$i]]["TYPE"] == "date")
{
if (array_key_exists($arFieldsKeys[$i], $arOrder))
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i]."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "SHORT")." as ".$arFieldsKeys[$i];
}
else
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i];
if (isset($arFields[$arFieldsKeys[$i]]["FROM"])
&& strlen($arFields[$arFieldsKeys[$i]]["FROM"]) > 0
&& !in_array($arFields[$arFieldsKeys[$i]]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$arFieldsKeys[$i]]["FROM"];
$arAlreadyJoined[] = $arFields[$arFieldsKeys[$i]]["FROM"];
}
}
}
else
{
foreach ($arSelectFields as $key => $val)
{
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields))
{
if (strlen($strSqlSelect) > 0)
$strSqlSelect .= ", ";
if (in_array($key, $arGroupByFunct))
$strSqlSelect .= $key."(".$arFields[$val]["FIELD"].") as ".$val;
else
{
if ($arFields[$val]["TYPE"] == "datetime")
{
if (array_key_exists($val, $arOrder))
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL")." as ".$val;
}
elseif ($arFields[$val]["TYPE"] == "date")
{
if (array_key_exists($val, $arOrder))
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "SHORT")." as ".$val;
}
else
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val;
}
if (isset($arFields[$val]["FROM"])
&& strlen($arFields[$val]["FROM"]) > 0
&& !in_array($arFields[$val]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
if ($obUserFieldsSql)
$strSqlSelect .= (strlen($strSqlSelect) <= 0 ? $arFields["ID"]["FIELD"] : "").$obUserFieldsSql->GetSelect();
if (strlen($strSqlGroupBy) > 0)
{
if (strlen($strSqlSelect) > 0)
$strSqlSelect .= ", ";
$strSqlSelect .= "COUNT(%%_DISTINCT_%% ".$arFields[$arFieldsKeys[0]]["FIELD"].") as CNT";
}
else
$strSqlSelect = "%%_DISTINCT_%% ".$strSqlSelect;
}
// <-- SELECT
if ($obUserFieldsSql)
$strSqlFrom .= " ".$obUserFieldsSql->GetJoin($arFields["ID"]["FIELD"]);
return array(
"SELECT" => $strSqlSelect,
"FROM" => $strSqlFrom,
"WHERE" => $strSqlWhere,
"GROUPBY" => $strSqlGroupBy,
"ORDERBY" => $strSqlOrderBy
);
}