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',
'CONTACT_ID' => 'L.CONTACT_ID',
'COMPANY_ID' => 'L.COMPANY_ID',
'IS_RETURN_CUSTOMER' => 'L.IS_RETURN_CUSTOMER',
'POST' => 'L.POST',
'ADDRESS' => 'L.ADDRESS',
'COMMENTS' => 'L.COMMENTS',
'NAME' => 'L.NAME',
'COMPANY_TITLE' => 'L.COMPANY_TITLE',
'TITLE' => 'L.TITLE',
'PRODUCT_ID' => 'L.PRODUCT_ID',
'SOURCE_ID' => 'L.SOURCE_ID',
'SOURCE_DESCRIPTION' => 'L.SOURCE_DESCRIPTION',
'STATUS_ID' => 'L.STATUS_ID',
'STATUS_DESCRIPTION' => 'L.STATUS_DESCRIPTION',
'SECOND_NAME' => 'L.SECOND_NAME',
'LAST_NAME' => 'L.LAST_NAME',
'FULL_NAME' => 'L.FULL_NAME',
'OPPORTUNITY' => 'L.OPPORTUNITY',
'IS_MANUAL_OPPORTUNITY' => 'L.IS_MANUAL_OPPORTUNITY',
'CURRENCY_ID' => 'L.CURRENCY_ID',
'OPPORTUNITY_ACCOUNT' => 'L.OPPORTUNITY_ACCOUNT',
'ACCOUNT_CURRENCY_ID' => 'L.ACCOUNT_CURRENCY_ID',
'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'),
'BIRTHDATE' => $DB->DateToCharFunction('L.BIRTHDATE'),
'OPENED' => 'L.OPENED',
'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',
'EXCH_RATE' => 'L.EXCH_RATE',
'ORIGINATOR_ID' => 'L.ORIGINATOR_ID', //EXTERNAL SYSTEM THAT OWNS THIS ITEM
'ORIGIN_ID' => 'L.ORIGIN_ID', //ITEM ID IN EXTERNAL SYSTEM
'DATE_CLOSED' => $DB->DateToCharFunction('L.DATE_CLOSED')
);
$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) || 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) || 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 ';
}
foreach($arSelect as $field)
{
$field = mb_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);
if (isset($arFilter['FM']) && !empty($arFilter['FM']))
{
$res = CCrmFieldMulti::GetList(array('ID' => 'asc'), array('ENTITY_ID' => 'LEAD', 'FILTER' => $arFilter['FM']));
$ids = array();
while($ar = $res->Fetch())
{
$ids[] = $ar['ELEMENT_ID'];
}
if(count($ids) == 0)
{
// Fix for #26789 (nothing found)
$rs = new CDBResult();
$rs->InitFromArray(array());
return $rs;
}
$arFilter['ID'] = $ids;
}
$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($sSqlPerm <> '')
{
$sSqlPerm = ' AND '.$sSqlPerm;
}
}
// where
$arWhereFields = array(
'ID' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.ID',
'FIELD_TYPE' => 'int',
'JOIN' => false
),
'CONTACT_ID' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.CONTACT_ID',
'FIELD_TYPE' => 'int',
'JOIN' => false
),
'COMPANY_ID' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.COMPANY_ID',
'FIELD_TYPE' => 'int',
'JOIN' => false
),
'IS_RETURN_CUSTOMER' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.IS_RETURN_CUSTOMER',
'FIELD_TYPE' => 'string',
'JOIN' => false
),
'SOURCE_ID' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.SOURCE_ID',
'FIELD_TYPE' => 'string',
'JOIN' => false
),
'STATUS_ID' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.STATUS_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
),
'IS_MANUAL_OPPORTUNITY' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.IS_MANUAL_OPPORTUNITY',
'FIELD_TYPE' => 'string',
'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
),
'PRODUCT_ID' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.PRODUCT_ID',
'FIELD_TYPE' => 'string',
'JOIN' => false
),
'NAME' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.NAME',
'FIELD_TYPE' => 'string',
'JOIN' => false
),
'SECOND_NAME' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.SECOND_NAME',
'FIELD_TYPE' => 'string',
'JOIN' => false
),
'LAST_NAME' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.LAST_NAME',
'FIELD_TYPE' => 'string',
'JOIN' => false
),
'FULL_NAME' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.FULL_NAME',
'FIELD_TYPE' => 'string',
'JOIN' => false
),
'TITLE' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.TITLE',
'FIELD_TYPE' => 'string',
'JOIN' => false
),
'COMPANY_TITLE' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.COMPANY_TITLE',
'FIELD_TYPE' => 'string',
'JOIN' => false
),
'POST' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.POST',
'FIELD_TYPE' => 'string',
'JOIN' => false
),
'ADDRESS' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.ADDRESS',
'FIELD_TYPE' => 'string',
'JOIN' => false
),
'COMMENTS' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.COMMENTS',
'FIELD_TYPE' => 'string',
'JOIN' => false
),
'DATE_CREATE' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.DATE_CREATE',
'FIELD_TYPE' => 'datetime',
'JOIN' => false
),
'DATE_MODIFY' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.DATE_MODIFY',
'FIELD_TYPE' => 'datetime',
'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
),
'DATE_CLOSED' => array(
'TABLE_ALIAS' => 'L',
'FIELD_NAME' => 'L.DATE_CLOSED',
'FIELD_TYPE' => 'datetime',
'JOIN' => false
)
);
$obQueryWhere->SetFields($arWhereFields);
if(!is_array($arFilter))
$arFilter = array();
$sQueryWhereFields = $obQueryWhere->GetQuery($arFilter);
$sSqlSearch = '';
foreach($arSqlSearch as $r)
if($r <> '')
$sSqlSearch .= "\n\t\t\t\tAND ($r) ";
$CCrmUserType = new CCrmUserType($GLOBALS['USER_FIELD_MANAGER'], self::$sUFEntityID);
$CCrmUserType->ListPrepareFilter($arFilter);
$r = $obUserFieldsSql->GetFilter();
if($r <> '')
$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',
'DATE_CLOSED' => 'L.DATE_CLOSED'
);
// order
$arSqlOrder = Array();
if (!is_array($arOrder))
$arOrder = Array('DATE_CREATE' => 'DESC');
foreach ($arOrder as $by => $order)
{
$by = mb_strtoupper($by);
$order = mb_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 = "
SELECT
$sSqlSelect
{$obUserFieldsSql->GetSelect()}
FROM
b_crm_lead L $sSqlJoin
{$obUserFieldsSql->GetJoin('L.ID')}
WHERE
1=1
$sSqlSearch
$sSqlPerm
$sSqlOrder";
if ($nPageTop !== false)
{
$nPageTop = (int) $nPageTop;
$sSql = $DB->TopSql($sSql, $nPageTop);
}
$obRes = $DB->Query($sSql, false, 'FILE: '.__FILE__.'
LINE: '.__LINE__);
$obRes->SetUserFields($USER_FIELD_MANAGER->GetUserFields(self::$sUFEntityID));
return $obRes;
}