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); 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 !== '') { $dbRes->SetUserFields($GLOBALS['USER_FIELD_MANAGER']->GetUserFields($this->ufEntityID)); } elseif (!empty($this->userFields)) { $dbRes->SetUserFields($this->userFields); } //Trace('CCrmEntityListBuilder::Prepare, SQL', $sql, 1); $cnt = $this->GetRowCount(RestrictionManager::getSqlRestriction()->getRowCountThreshold()); $dbRes->NavQuery($sql, $cnt, $arNavStartParams); } else { $limit = $top; $offset = 0; if (isset($arOptions['QUERY_OPTIONS']) && is_array($arOptions['QUERY_OPTIONS'])) { $queryOptions = $arOptions['QUERY_OPTIONS']; $limit = isset($queryOptions['LIMIT']) ? (int) $queryOptions['LIMIT'] : 0; $offset = isset($queryOptions['OFFSET']) ? (int) $queryOptions['OFFSET'] : 0; } $threshold = RestrictionManager::getSqlRestriction()->getRowCountThreshold(); if ($threshold > 0 && $threshold < $limit + $offset) { $delta = $threshold - $offset; if ($delta <= 0) { $obRes = new CDBResult(); $obRes->InitFromArray(array()); return $obRes; } $limit = $delta; } if ($limit > 0) { $sql = Main\Application::getConnection()->getSqlHelper()->getTopSql($sql, $limit, $offset); } //Trace('CCrmEntityListBuilder::Prepare, SQL', $sql, 1); $dbRes = $DB->Query($sql, false, 'File: ' . __FILE__ . '<br/>Line: ' . __LINE__); if ($this->ufEntityID !== '') { $dbRes->SetUserFields($GLOBALS['USER_FIELD_MANAGER']->GetUserFields($this->ufEntityID)); } elseif (!empty($this->userFields)) { $dbRes->SetUserFields($this->userFields); } } return $dbRes; }
/** * @param array $arOrder * @param array $arFilter * @param array $arSelect * @return CDBResult * Obsolete. Always select all record from database. Please use GetListEx instead. */ public static function GetList($arOrder = array('DATE_CREATE' => 'DESC'), $arFilter = array(), $arSelect = array(), $nPageTop = false) { global $DB, $USER_FIELD_MANAGER; // fields $arFields = array('ID' => 'L.ID', 'COMMENTS' => 'L.COMMENTS', 'ADDITIONAL_INFO' => 'L.ADDITIONAL_INFO', 'LOCATION_ID' => 'L.LOCATION_ID', 'TITLE' => 'L.TITLE', 'LEAD_ID' => 'L.LEAD_ID', 'COMPANY_ID' => 'L.COMPANY_ID', 'COMPANY_TITLE' => 'C.TITLE', 'CONTACT_ID' => 'L.CONTACT_ID', 'CONTACT_FULL_NAME' => 'CT.FULL_NAME', 'STAGE_ID' => 'L.STAGE_ID', 'CLOSED' => 'L.CLOSED', 'TYPE_ID' => 'L.TYPE_ID', 'PRODUCT_ID' => 'L.PRODUCT_ID', 'PROBABILITY' => 'L.PROBABILITY', 'OPPORTUNITY' => 'L.OPPORTUNITY', 'TAX_VALUE' => 'L.TAX_VALUE', 'CURRENCY_ID' => 'L.CURRENCY_ID', 'OPPORTUNITY_ACCOUNT' => 'L.OPPORTUNITY_ACCOUNT', 'TAX_VALUE_ACCOUNT' => 'L.TAX_VALUE_ACCOUNT', 'ACCOUNT_CURRENCY_ID' => 'L.ACCOUNT_CURRENCY_ID', 'BEGINDATE' => $DB->DateToCharFunction('L.BEGINDATE'), 'CLOSEDATE' => $DB->DateToCharFunction('L.CLOSEDATE'), 'EVENT_ID' => 'L.EVENT_ID', 'EVENT_DATE' => $DB->DateToCharFunction('L.EVENT_DATE'), 'EVENT_DESCRIPTION' => 'L.EVENT_DESCRIPTION', 'ASSIGNED_BY' => 'L.ASSIGNED_BY_ID', 'ASSIGNED_BY_ID' => 'L.ASSIGNED_BY_ID', 'CREATED_BY' => 'L.CREATED_BY_ID', 'CREATED_BY_ID' => 'L.CREATED_BY_ID', 'MODIFY_BY' => 'L.MODIFY_BY_ID', 'MODIFY_BY_ID' => 'L.MODIFY_BY_ID', 'DATE_CREATE' => $DB->DateToCharFunction('L.DATE_CREATE'), 'DATE_MODIFY' => $DB->DateToCharFunction('L.DATE_MODIFY'), 'OPENED' => 'L.OPENED', 'EXCH_RATE' => 'L.EXCH_RATE', 'ORIGINATOR_ID' => 'L.ORIGINATOR_ID', 'ORIGIN_ID' => 'L.ORIGIN_ID', 'ASSIGNED_BY_LOGIN' => 'U.LOGIN', 'ASSIGNED_BY_NAME' => 'U.NAME', 'ASSIGNED_BY_LAST_NAME' => 'U.LAST_NAME', 'ASSIGNED_BY_SECOND_NAME' => 'U.SECOND_NAME', 'CREATED_BY_LOGIN' => 'U2.LOGIN', 'CREATED_BY_NAME' => 'U2.NAME', 'CREATED_BY_LAST_NAME' => 'U2.LAST_NAME', 'CREATED_BY_SECOND_NAME' => 'U2.SECOND_NAME', 'MODIFY_BY_LOGIN' => 'U3.LOGIN', 'MODIFY_BY_NAME' => 'U3.NAME', 'MODIFY_BY_LAST_NAME' => 'U3.LAST_NAME', 'MODIFY_BY_SECOND_NAME' => 'U3.SECOND_NAME'); $arSqlSelect = array(); $sSqlJoin = ''; if (count($arSelect) == 0) { $arSelect = array_merge(array_keys($arFields), array('UF_*')); } $obQueryWhere = new CSQLWhere(); $arFilterField = $arSelect; foreach ($arFilter as $sKey => $sValue) { $arField = $obQueryWhere->MakeOperation($sKey); $arFilterField[] = $arField['FIELD']; } if (in_array('ASSIGNED_BY_LOGIN', $arFilterField) || in_array('ASSIGNED_BY', $arFilterField)) { $arSelect[] = 'ASSIGNED_BY_LOGIN'; $arSelect[] = 'ASSIGNED_BY_NAME'; $arSelect[] = 'ASSIGNED_BY_LAST_NAME'; $arSelect[] = 'ASSIGNED_BY_SECOND_NAME'; $sSqlJoin .= ' LEFT JOIN b_user U ON L.ASSIGNED_BY_ID = U.ID '; } if (in_array('CREATED_BY_LOGIN', $arFilterField)) { $arSelect[] = 'CREATED_BY'; $arSelect[] = 'CREATED_BY_LOGIN'; $arSelect[] = 'CREATED_BY_NAME'; $arSelect[] = 'CREATED_BY_LAST_NAME'; $arSelect[] = 'CREATED_BY_SECOND_NAME'; $sSqlJoin .= ' LEFT JOIN b_user U2 ON L.CREATED_BY_ID = U2.ID '; } if (in_array('MODIFY_BY_LOGIN', $arFilterField)) { $arSelect[] = 'MODIFY_BY'; $arSelect[] = 'MODIFY_BY_LOGIN'; $arSelect[] = 'MODIFY_BY_NAME'; $arSelect[] = 'MODIFY_BY_LAST_NAME'; $arSelect[] = 'MODIFY_BY_SECOND_NAME'; $sSqlJoin .= ' LEFT JOIN b_user U3 ON L.MODIFY_BY_ID = U3.ID '; } if (in_array('COMPANY_ID', $arFilterField) || in_array('COMPANY_TITLE', $arFilterField)) { $arSelect[] = 'COMPANY_ID'; $arSelect[] = 'COMPANY_TITLE'; $sSqlJoin .= ' LEFT JOIN b_crm_company C ON L.COMPANY_ID = C.ID '; } if (in_array('CONTACT_ID', $arFilterField) || in_array('CONTACT_FULL_NAME', $arFilterField)) { $arSelect[] = 'CONTACT_ID'; $arSelect[] = 'CONTACT_FULL_NAME'; $sSqlJoin .= ' LEFT JOIN b_crm_contact CT ON L.CONTACT_ID = CT.ID '; } foreach ($arSelect as $field) { $field = strtoupper($field); if (array_key_exists($field, $arFields)) { $arSqlSelect[$field] = $arFields[$field] . ($field != '*' ? ' AS ' . $field : ''); } } if (!isset($arSqlSelect['ID'])) { $arSqlSelect['ID'] = $arFields['ID']; } $sSqlSelect = implode(",\n", $arSqlSelect); $obUserFieldsSql = new CUserTypeSQL(); $obUserFieldsSql->SetEntity(self::$sUFEntityID, 'L.ID'); $obUserFieldsSql->SetSelect($arSelect); $obUserFieldsSql->SetFilter($arFilter); $obUserFieldsSql->SetOrder($arOrder); $arSqlSearch = array(); // check permissions $sSqlPerm = ''; if (!CCrmPerms::IsAdmin() && (!array_key_exists('CHECK_PERMISSIONS', $arFilter) || $arFilter['CHECK_PERMISSIONS'] !== 'N')) { $arPermType = array(); if (!isset($arFilter['PERMISSION'])) { $arPermType[] = 'READ'; } else { $arPermType = is_array($arFilter['PERMISSION']) ? $arFilter['PERMISSION'] : array($arFilter['PERMISSION']); } $sSqlPerm = self::BuildPermSql('L', $arPermType); if ($sSqlPerm === false) { $CDBResult = new CDBResult(); $CDBResult->InitFromArray(array()); return $CDBResult; } if (strlen($sSqlPerm) > 0) { $sSqlPerm = ' AND ' . $sSqlPerm; } } // where $arWhereFields = array('ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'LEAD_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.LEAD_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'CONTACT_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CONTACT_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'CONTACT_FULL_NAME' => array('TABLE_ALIAS' => 'CT', 'FIELD_NAME' => 'CT.FULL_NAME', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'COMPANY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.COMPANY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'COMPANY_TITLE' => array('TABLE_ALIAS' => 'C', 'FIELD_NAME' => 'C.TITLE', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'STATE_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.STATE_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'STAGE_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.STAGE_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'TYPE_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TYPE_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'PRODUCT_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.PRODUCT_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'CURRENCY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CURRENCY_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'OPPORTUNITY' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.OPPORTUNITY', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'TAX_VALUE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TAX_VALUE', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'ACCOUNT_CURRENCY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ACCOUNT_CURRENCY_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'OPPORTUNITY_ACCOUNT' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.OPPORTUNITY_ACCOUNT', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'TAX_VALUE_ACCOUNT' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TAX_VALUE_ACCOUNT', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'TITLE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TITLE', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'CLOSED' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CLOSED', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'COMMENTS' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.COMMENTS', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'ADDITIONAL_INFO' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ADDITIONAL_INFO', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'LOCATION_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.LOCATION_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'DATE_CREATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.DATE_CREATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'BEGINDATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.BEGINDATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'CLOSEDATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CLOSEDATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'EVENT_DATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.EVENT_DATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'DATE_MODIFY' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.DATE_MODIFY', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'PROBABILITY' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.PROBABILITY', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'EVENT_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.EVENT_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'CREATED_BY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CREATED_BY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'ASSIGNED_BY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ASSIGNED_BY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'OPENED' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.OPENED', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'MODIFY_BY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.MODIFY_BY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'EXCH_RATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.EXCH_RATE', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'ORIGINATOR_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ORIGINATOR_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'ORIGIN_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ORIGIN_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false)); $obQueryWhere->SetFields($arWhereFields); if (!is_array($arFilter)) { $arFilter = array(); } $sQueryWhereFields = $obQueryWhere->GetQuery($arFilter); $sSqlSearch = ''; foreach ($arSqlSearch as $r) { 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; }