CAllCrmLead::GetList

  1. Bitrix24 API (v. 23.675.0)
  2. crm
  3. CAllCrmLead
  4. GetList
  • Модуль: crm
  • Путь к файлу: ~/bitrix/modules/crm/classes/general/crm_lead.php
  • Класс: \CAllCrmLead
  • Вызов: CAllCrmLead::GetList
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; }

Добавить комментарий