PHP CUserTypeSQL::GetJoin方法代码示例

//.........这里部分代码省略.........
         $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;
 }

//.........这里部分代码省略.........
                 $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);
 }

 protected function UpdateList($arFields, $arFilter = array())
 {
     global $DB, $USER, $USER_FIELD_MANAGER;
     $strUpdate = $DB->PrepareUpdate("b_iblock_section", $arFields, "iblock", false, "BS");
     if ($strUpdate == "") {
         return false;
     }
     if (isset($arFilter["IBLOCK_ID"]) && $arFilter["IBLOCK_ID"] > 0) {
         $obUserFieldsSql = new CUserTypeSQL();
         $obUserFieldsSql->SetEntity("IBLOCK_" . $arFilter["IBLOCK_ID"] . "_SECTION", "BS.ID");
         $obUserFieldsSql->SetFilter($arFilter);
     } else {
         foreach ($arFilter as $key => $val) {
             $res = CIBlock::MkOperationFilter($key);
             if (preg_match("/^UF_/", $res["FIELD"])) {
                 trigger_error("arFilter parameter of the CIBlockSection::GetList contains user fields, but has no IBLOCK_ID field.", E_USER_WARNING);
                 break;
             }
         }
     }
     $arJoinProps = array();
     $bJoinFlatProp = false;
     $arSqlSearch = CIBlockSection::GetFilter($arFilter);
     $bCheckPermissions = !array_key_exists("CHECK_PERMISSIONS", $arFilter) || $arFilter["CHECK_PERMISSIONS"] !== "N";
     $bIsAdmin = is_object($USER) && $USER->IsAdmin();
     if ($bCheckPermissions && !$bIsAdmin) {
         $arSqlSearch[] = CIBlockSection::_check_rights_sql($arFilter["MIN_PERMISSION"]);
     }
     if (array_key_exists("PROPERTY", $arFilter)) {
         $val = $arFilter["PROPERTY"];
         foreach ($val as $propID => $propVAL) {
             $res = CIBlock::MkOperationFilter($propID);
             $propID = $res["FIELD"];
             $cOperationType = $res["OPERATION"];
             if ($db_prop = CIBlockProperty::GetPropertyArray($propID, CIBlock::_MergeIBArrays($arFilter["IBLOCK_ID"], $arFilter["IBLOCK_CODE"]))) {
                 $bSave = false;
                 if (array_key_exists($db_prop["ID"], $arJoinProps)) {
                     $iPropCnt = $arJoinProps[$db_prop["ID"]];
                 } elseif ($db_prop["VERSION"] != 2 || $db_prop["MULTIPLE"] == "Y") {
                     $bSave = true;
                     $iPropCnt = count($arJoinProps);
                 }
                 if (!is_array($propVAL)) {
                     $propVAL = array($propVAL);
                 }
                 if ($db_prop["PROPERTY_TYPE"] == "N" || $db_prop["PROPERTY_TYPE"] == "G" || $db_prop["PROPERTY_TYPE"] == "E") {
                     if ($db_prop["VERSION"] == 2 && $db_prop["MULTIPLE"] == "N") {
                         $r = CIBlock::FilterCreate("FPS.PROPERTY_" . $db_prop["ORIG_ID"], $propVAL, "number", $cOperationType);
                         $bJoinFlatProp = $db_prop["IBLOCK_ID"];
                     } else {
                         $r = CIBlock::FilterCreate("FPV" . $iPropCnt . ".VALUE_NUM", $propVAL, "number", $cOperationType);
                     }
                 } else {
                     if ($db_prop["VERSION"] == 2 && $db_prop["MULTIPLE"] == "N") {
                         $r = CIBlock::FilterCreate("FPS.PROPERTY_" . $db_prop["ORIG_ID"], $propVAL, "string", $cOperationType);
                         $bJoinFlatProp = $db_prop["IBLOCK_ID"];
                     } else {
                         $r = CIBlock::FilterCreate("FPV" . $iPropCnt . ".VALUE", $propVAL, "string", $cOperationType);
                     }
                 }
                 if (strlen($r) > 0) {
                     if ($bSave) {
                         $db_prop["iPropCnt"] = $iPropCnt;
                         $arJoinProps[$db_prop["ID"]] = $db_prop;
                     }
                     $arSqlSearch[] = $r;
                 }
             }
         }
     }
     $strSqlSearch = "";
     foreach ($arSqlSearch as $r) {
         if (strlen($r) > 0) {
             $strSqlSearch .= "\n\t\t\t\tAND  (" . $r . ") ";
         }
     }
     if (isset($obUserFieldsSql)) {
         $r = $obUserFieldsSql->GetFilter();
         if (strlen($r) > 0) {
             $strSqlSearch .= "\n\t\t\t\tAND (" . $r . ") ";
         }
     }
     $strProp1 = "";
     foreach ($arJoinProps as $propID => $db_prop) {
         if ($db_prop["VERSION"] == 2) {
             $strTable = "b_iblock_element_prop_m" . $db_prop["IBLOCK_ID"];
         } else {
             $strTable = "b_iblock_element_property";
         }
         $i = $db_prop["iPropCnt"];
         $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";
     }
     $strSql = "\n\t\t\tUPDATE\n\t\t\tb_iblock_section BS\n\t\t\t\tINNER JOIN b_iblock B ON BS.IBLOCK_ID = B.ID\n\t\t\t\t" . (isset($obUserFieldsSql) ? $obUserFieldsSql->GetJoin("BS.ID") : "") . "\n\t\t\t" . (strlen($strProp1) > 0 ? "\tINNER JOIN b_iblock_section BSTEMP ON BSTEMP.IBLOCK_ID = BS.IBLOCK_ID\n\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\tSET " . $strUpdate . "\n\t\t\tWHERE 1=1\n\t\t\t" . (strlen($strProp1) > 0 ? "\tAND BSTEMP.LEFT_MARGIN >= BS.LEFT_MARGIN\n\t\t\t\t\tAND BSTEMP.RIGHT_MARGIN <= BS.RIGHT_MARGIN " : "") . "\n\t\t\t" . $strSqlSearch . "\n\t\t";
     return $DB->Query($strSql, false, "FILE: " . __FILE__ . "<br> LINE: " . __LINE__);
 }

//.........这里部分代码省略.........
  *     );
  * 
  *     while ($arQuestion = $res->GetNext())
  *     {
  *         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") {

//.........这里部分代码省略.........
                 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;
 }

 function GetChildrenCount($arFilter, $arParentIDs)
 {
     global $DB, $USER;
     $obUserFieldsSql = new CUserTypeSQL();
     $obUserFieldsSql->SetEntity("TASKS_TASK", "T.ID");
     $obUserFieldsSql->SetFilter($arFilter);
     if (!is_array($arFilter)) {
         $arFilter = array();
     }
     if (!$arParentIDs) {
         return false;
     }
     $arFilter["PARENT_ID"] = $arParentIDs;
     $userID = is_object($USER) ? intval($USER->GetID()) : 0;
     unset($arFilter["ONLY_ROOT_TASKS"]);
     $arSqlSearch = CTasks::GetFilter($arFilter);
     $arSqlSearch[] = " T.ZOMBIE = 'N' ";
     $r = $obUserFieldsSql->GetFilter();
     if (strlen($r) > 0) {
         $arSqlSearch[] = "(" . $r . ")";
     }
     $strSql = "\n\t\t\tSELECT\n\t\t\t\tT.PARENT_ID AS PARENT_ID,\n\t\t\t\tCOUNT(T.ID) AS CNT\n\t\t\tFROM\n\t\t\t\tb_tasks T\n\t\t\tINNER JOIN b_user CU ON CU.ID = T.CREATED_BY\n\t\t\tINNER JOIN b_user RU ON RU.ID = T.RESPONSIBLE_ID\n\t\t\tLEFT JOIN\n\t\t\t\tb_tasks_viewed TV ON TV.TASK_ID = T.ID AND TV.USER_ID = " . $userID . "\n\t\t\t" . $obUserFieldsSql->GetJoin("T.ID") . "\n\t\t\t" . (sizeof($arSqlSearch) ? "WHERE " . implode(" AND ", $arSqlSearch) : "") . "\n\t\t\tGROUP BY\n\t\t\t\tT.PARENT_ID\n\t\t";
     $res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
     return $res;
 }

//.........这里部分代码省略.........
     $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;
 }

//.........这里部分代码省略.........
             }
         }
     }
     // 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);
                 if (!is_string($orderSql) || $orderSql === '') {
                     continue;
                 }
                 $order = strtoupper($order);
                 if ($order !== 'ASC' && $order !== 'DESC') {
                     $order = 'ASC';
                 }
                 $this->Add2SqlData("{$orderSql} {$order}", 'ORDERBY');
             }
         }
         // Adding user fields to joins
         $this->Add2SqlData($ufSelectSql->GetJoin($this->tableAlias . '.ID'), 'FROM');
     }
     if ($ufFilterSql) {
         // Adding user fields to WHERE
         $ufWhere = $ufFilterSql->GetFilter();
         if ($ufWhere !== '') {
             $ufSql = $this->tableAlias . '.ID IN (SELECT ' . $this->tableAlias . '.ID FROM ' . $this->tableName . ' ' . $this->tableAlias . ' ' . $ufFilterSql->GetJoin($this->tableAlias . '.ID') . ' WHERE ' . $ufWhere . ')';
             // Adding user fields to joins
             $this->Add2SqlData($ufSql, 'WHERE');
         }
     }
     //Get count only
     if (is_array($arGroupBy) && count($arGroupBy) == 0) {
         return $this->GetRowCount(RestrictionManager::getSqlRestriction()->getRowCountThreshold());
     }
     $sql = 'SELECT ' . $this->sqlData['SELECT'] . ' FROM ' . $this->tableName . ' ' . $this->tableAlias;
     if (isset($this->sqlData['FROM'][0])) {
         $sql .= ' ' . $this->sqlData['FROM'];
     }
     if (isset($this->sqlData['WHERE'][0])) {
         $sql .= ' WHERE ' . $this->sqlData['WHERE'];
     }
     if (isset($this->sqlData['GROUPBY'][0])) {
         $sql .= ' GROUP BY ' . $this->sqlData['GROUPBY'];
     }
     if (isset($this->sqlData['ORDERBY'][0])) {
         $sql .= ' ORDER BY ' . $this->sqlData['ORDERBY'];
     }
     $enableNavigation = is_array($arNavStartParams);
     $top = $enableNavigation && isset($arNavStartParams['nTopCount']) ? intval($arNavStartParams['nTopCount']) : 0;
     if ($enableNavigation && $top <= 0) {
         $dbRes = new CDBResult();
         if ($this->ufEntityID !== '') {

 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;
 }

 /**
  * 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 {
//.........这里部分代码省略.........

//.........这里部分代码省略.........
                         $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();
         }

 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"];
//.........这里部分代码省略.........

//.........这里部分代码省略.........
     }
     foreach ($arOrder as $field => $direction) {
         if ($field == 'BASE_TEMPLATE_ID') {
             $selectBaseTemplateId = true;
         }
         if ($field == 'TEMPLATE_CHILDREN_COUNT') {
             $useChildrenCount = true;
         }
     }
     if (!is_array($arFilter)) {
         $arFilter = array();
     }
     if (!is_array($arParams)) {
         $arParams = array();
     }
     foreach ($arFilter as $key => $value) {
         $keyParsed = CTasks::MkOperationFilter($key);
         if ($keyParsed['FIELD'] == 'BASE_TEMPLATE_ID') {
             $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);

 public static function GetDestinationUsers($arCodes, $bFetchUsers = false)
 {
     global $DB;
     $arUsers = array();
     $arCodes2 = array();
     if (!$bFetchUsers) {
         foreach ($arCodes as $code) {
             if (substr($code, 0, 1) === 'U' && $code !== 'UA') {
                 $id = intVal(substr($code, 1));
                 if ($id > 0) {
                     $arUsers[] = $id;
                     continue;
                 }
             }
             if (substr($code, 0, 2) === 'SG') {
                 $arCodes2[] = $code . '_K';
             }
             $arCodes2[] = $code;
         }
         $bUnique = count($arCodes2) > 0 && count($arUsers) > 0;
     } else {
         foreach ($arCodes as $code) {
             if (substr($code, 0, 2) === 'SG') {
                 $arCodes2[] = $code . '_K';
             }
             $arCodes2[] = $code;
         }
         $bUnique = false;
     }
     $obUserFieldsSql = new CUserTypeSQL();
     $obUserFieldsSql->SetEntity("USER", "USER_ID");
     $obUserFieldsSql->SetFilter(array("!UF_DEPARTMENT" => false));
     $where = $obUserFieldsSql->GetFilter();
     $join = $obUserFieldsSql->GetJoin("UA.USER_ID");
     if ($where == '') {
         $where = '1=1';
     }
     $strCodes = in_array('UA', $arCodes2) ? "'G2'" : "'" . join("','", $arCodes2) . "'";
     if ($bFetchUsers) {
         $strSql = "SELECT DISTINCT UA.USER_ID, U.LOGIN, U.NAME, U.LAST_NAME, U.SECOND_NAME, U.EMAIL, U.PERSONAL_PHOTO, U.WORK_POSITION " . "FROM b_user_access UA\n\t\t\t\tINNER JOIN b_user U ON (U.ID=UA.USER_ID)" . $join . " WHERE ACCESS_CODE in (" . $strCodes . ") AND " . $where;
     } else {
         $strSql = "SELECT DISTINCT USER_ID " . "FROM b_user_access UA " . $join . " WHERE ACCESS_CODE in (" . $strCodes . ") AND " . $where;
     }
     $res = $DB->Query($strSql, false, "FILE: " . __FILE__ . "<br> LINE: " . __LINE__);
     if ($bFetchUsers) {
         while ($ar = $res->Fetch()) {
             if ($ar > 0) {
                 $arUsers[] = $ar;
             }
         }
     } else {
         while ($ar = $res->Fetch()) {
             if ($ar['USER_ID'] > 0) {
                 $arUsers[] = $ar['USER_ID'];
             }
         }
     }
     if ($bUnique) {
         $arUsers = array_unique($arUsers);
     }
     return $arUsers;
 }

//.........这里部分代码省略.........
         $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;
 }