CAllCrmDeal::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',
		'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',
		/*'STATE_ID' => 'L.STATE_ID',*/
		'STAGE_ID' => 'L.STAGE_ID',
		'CLOSED' => 'L.CLOSED',
		'TYPE_ID' => 'L.TYPE_ID',
		'PRODUCT_ID' => 'L.PRODUCT_ID',
		'PROBABILITY' => 'L.PROBABILITY',
		'OPPORTUNITY' => 'L.OPPORTUNITY',
		'IS_MANUAL_OPPORTUNITY' => 'L.IS_MANUAL_OPPORTUNITY',
		'TAX_VALUE' => 'L.TAX_VALUE',
		'CURRENCY_ID' => 'L.CURRENCY_ID',
		'IS_RECURRING' => 'L.IS_RECURRING',
		'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', //EXTERNAL SYSTEM THAT OWNS THIS ITEM
		'ORIGIN_ID' => 'L.ORIGIN_ID', //ITEM ID IN EXTERNAL SYSTEM
		'ORDER_STAGE' => 'L.ORDER_STAGE',
		'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 (
		!isset($arFilter['IS_RECURRING'])
		&& !isset($arFilter['=IS_RECURRING'])
		&& !(isset($arFilter['ID']) || isset($arFilter['=ID']) || isset($arFilter['@ID']))
	)
	{
		$arFilter['=IS_RECURRING'] = 'N';
	}

	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 = 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);

	$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
		),
		'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
		),
		'IS_MANUAL_OPPORTUNITY' => array(
			'TABLE_ALIAS' => 'L',
			'FIELD_NAME' => 'L.IS_MANUAL_OPPORTUNITY',
			'FIELD_TYPE' => 'string',
			'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
		),
		'IS_RECURRING' => array(
			'TABLE_ALIAS' => 'L',
			'FIELD_NAME' => 'L.IS_RECURRING',
			'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 ($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'
	);

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