/**
* @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;
}
protected function getJoinCswFields($reference)
{
$fields = array();
foreach ($reference as $k => $v) {
if ($k === 'LOGIC') {
continue;
}
if (is_numeric($k)) {
$fields = array_merge($fields, $this->getJoinCswFields($v));
} else {
// key
$csw_result = \CSQLWhere::MakeOperation($k);
list($field, ) = array_values($csw_result);
$fields[$field] = array('TABLE_ALIAS' => 'alias', 'FIELD_NAME' => $field, 'FIELD_TYPE' => 'string', 'MULTIPLE' => '', 'JOIN' => '');
// no need to add values as csw fields
}
}
return $fields;
}