CAllCrmLead::__AfterPrepareSql

static function __AfterPrepareSql(/*CCrmEntityListBuilder*/ $sender, $arOrder, $arFilter, $arGroupBy, $arSelectFields)
{
	$sqlData = array('FROM' => array(), 'WHERE' => array());
	if(isset($arFilter['SEARCH_CONTENT']) && $arFilter['SEARCH_CONTENT'] !== '')
	{
		$tableAlias = $sender->GetTableAlias();
		$queryWhere = new CSQLWhere();
		$queryWhere->SetFields(
			array(
				'SEARCH_CONTENT' => array(
					'FIELD_NAME' => "{$tableAlias}.SEARCH_CONTENT",
					'FIELD_TYPE' => 'string',
					'JOIN' => false
				)
			)
		);
		$options = [];
		if (isset($arFilter['__ENABLE_SEARCH_CONTENT_PHONE_DETECTION']))
		{
			$options['ENABLE_PHONE_DETECTION'] = $arFilter['__ENABLE_SEARCH_CONTENT_PHONE_DETECTION'];
			unset($arFilter['__ENABLE_SEARCH_CONTENT_PHONE_DETECTION']);
		}
		$query = $queryWhere->GetQuery(
			Crm\Search\SearchEnvironment::prepareEntityFilter(
				CCrmOwnerType::Lead,
				array(
					'SEARCH_CONTENT' => Crm\Search\SearchEnvironment::prepareSearchContent($arFilter['SEARCH_CONTENT'], $options)
				)
			)
		);
		if($query !== '')
		{
			$sqlData['WHERE'][] = $query;
		}
	}

	if (!empty($arFilter['ACTIVE_TIME_PERIOD_from']) || !empty($arFilter['%STATUS_ID_FROM_HISTORY']) || !empty($arFilter['%STATUS_ID_FROM_SUPPOSED_HISTORY']) || !empty($arFilter['%STATUS_SEMANTIC_ID_FROM_HISTORY']))
	{
		global $DB;
		$supposedHistoryConditions = [];

		if (!empty($arFilter['ACTIVE_TIME_PERIOD_from']) && !empty($arFilter['ACTIVE_TIME_PERIOD_to']))
		{
			$supposedHistoryConditions[] = "LSHWS.LAST_UPDATE_DATE <= " . $DB->CharToDateFunction($arFilter['ACTIVE_TIME_PERIOD_to'], 'SHORT');
			$supposedHistoryConditions[] = "LSHWS.CLOSE_DATE >= " . $DB->CharToDateFunction($arFilter['ACTIVE_TIME_PERIOD_from'], 'SHORT');
		}
		if (!empty($arFilter['%STATUS_SEMANTIC_ID_FROM_HISTORY']))
		{
			$statusSemanticIdsFromFilter = is_array($arFilter['%STATUS_SEMANTIC_ID_FROM_HISTORY']) ? $arFilter['%STATUS_SEMANTIC_ID_FROM_HISTORY'] : array($arFilter['%STATUS_SEMANTIC_ID_FROM_HISTORY']);
			$statusSemanticIdsForSql = [];
			foreach ($statusSemanticIdsFromFilter as $value)
			{
				$statusSemanticIdsForSql[] = "'" . \Bitrix\Main\Application::getConnection()->getSqlHelper()->forSql($value) . "'";
			}
			$supposedHistoryConditions[] = "LSHWS.IS_SUPPOSED = 'N'";
			$supposedHistoryConditions[] = "LSHWS.STATUS_SEMANTIC_ID IN (" . implode(', ', $statusSemanticIdsForSql) . ")";
		}
		if (!empty($arFilter['%STATUS_ID_FROM_HISTORY']))
		{
			$statusIdsFromFilter = is_array($arFilter['%STATUS_ID_FROM_HISTORY']) ? $arFilter['%STATUS_ID_FROM_HISTORY'] : array($arFilter['%STATUS_ID_FROM_HISTORY']);
			$statusIdsForSql = [];
			foreach ($statusIdsFromFilter as $value)
			{
				$statusIdsForSql[] = "'" . \Bitrix\Main\Application::getConnection()->getSqlHelper()->forSql($value) . "'";
			}
			$supposedHistoryConditions[] = "LSHWS.IS_SUPPOSED = 'N'";
			$supposedHistoryConditions[] = "LSHWS.STATUS_ID  IN (" . implode(', ', $statusIdsForSql) . ")";
		}
		if (!empty($arFilter['%STATUS_ID_FROM_SUPPOSED_HISTORY']))
		{
			$statusIdsFromFilter = is_array($arFilter['%STATUS_ID_FROM_SUPPOSED_HISTORY']) ? $arFilter['%STATUS_ID_FROM_SUPPOSED_HISTORY'] : array($arFilter['%STATUS_ID_FROM_SUPPOSED_HISTORY']);
			$statusIdsForSql = [];
			foreach ($statusIdsFromFilter as $value)
			{
				$statusIdsForSql[] = "'" . \Bitrix\Main\Application::getConnection()->getSqlHelper()->forSql($value) . "'";
			}
			$supposedHistoryConditions[] .= " LSHWS.STATUS_ID  IN (" . implode(', ', $statusIdsForSql) . ")";
		}

		$sqlData['WHERE'][] = "L.ID IN (SELECT DISTINCT LSHWS.OWNER_ID FROM b_crm_lead_status_history_with_supposed LSHWS WHERE " . implode(" AND ", $supposedHistoryConditions) . ")";
	}

	if(isset($arFilter['CALENDAR_DATE_FROM']) && $arFilter['CALENDAR_DATE_FROM'] !== ''
	&& isset($arFilter['CALENDAR_DATE_TO']) && $arFilter['CALENDAR_DATE_TO'] !== '')
	{
		global $DB;

		if ($arFilter['CALENDAR_FIELD'] == 'DATE_CREATE')
		{
			$sqlData['WHERE'][] = "L.DATE_CREATE <= ".$DB->CharToDateFunction($arFilter['CALENDAR_DATE_TO'], 'SHORT');
			$sqlData['WHERE'][] = "L.DATE_CREATE >= ".$DB->CharToDateFunction($arFilter['CALENDAR_DATE_FROM'], 'SHORT');
		}
		else
		{
			[$ufId, $ufType, $ufName] = \Bitrix\Crm\Integration\Calendar::parseUserfieldKey($arFilter['CALENDAR_FIELD']);

			if (intval($ufId) > 0 && $ufType == 'resourcebooking' || is_null($ufType))
			{
				// L = b_crm_lead
				$sqlData['FROM'][] = "INNER JOIN b_calendar_resource RBUF ".
					"ON RBUF.PARENT_ID = L.ID".
					" AND RBUF.PARENT_TYPE = 'CRM_LEAD'".
					" AND RBUF.UF_ID = ".intval($arFilter['CALENDAR_FIELD']);

				$sqlData['SELECT'][] = $DB->DateToCharFunction("RBUF.DATE_FROM").' as RES_BOOKING_FROM';
				$sqlData['SELECT'][] = $DB->DateToCharFunction("RBUF.DATE_TO").' as RES_BOOKING_TO';
				$sqlData['SELECT'][] = 'RBUF.SKIP_TIME as RES_BOOKING_SKIP_TIME';
				$sqlData['SELECT'][] = 'RBUF.TZ_FROM as RES_BOOKING_TZ_FROM';
				$sqlData['SELECT'][] = 'RBUF.TZ_TO as RES_BOOKING_TZ_TO';
				$sqlData['SELECT'][] = 'RBUF.RESOURCE_ID as RES_BOOKING_RESOURCE_ID';
				$sqlData['SELECT'][] = 'RBUF.CAL_TYPE as RES_BOOKING_CAL_TYPE';
				$sqlData['SELECT'][] = 'RBUF.EVENT_ID as RES_BOOKING_EVENT_ID';

				$sqlData['WHERE'][] = "RBUF.DATE_FROM <= ".$DB->CharToDateFunction($arFilter['CALENDAR_DATE_TO'], 'SHORT');
				$sqlData['WHERE'][] = "RBUF.DATE_TO >= ".$DB->CharToDateFunction($arFilter['CALENDAR_DATE_FROM'], 'SHORT');
			}
			elseif(intval($ufId) > 0 && ($ufType == 'date' || $ufType == 'datetime'))
			{
				if (!in_array($ufName, $arSelectFields))
				{
					$alias = $sender->GetTableAlias();

					$ufSelectSql = new CUserTypeSQL();
					$ufSelectSql->SetEntity(self::GetUserFieldEntityID(), $alias.'.ID');
					$ufSelectSql->SetSelect(array($ufName));
					$sqlData['SELECT'][] = trim($ufSelectSql->GetSelect(), ', ');
					$sqlData['FROM'][] = $ufSelectSql->GetJoin($alias.'.ID');
				}

				$sqlData['WHERE'][] = $DB->ForSql($ufName)." <= ".$DB->CharToDateFunction($arFilter['CALENDAR_DATE_TO'], 'SHORT');
				$sqlData['WHERE'][] = $DB->ForSql($ufName)." >= ".$DB->CharToDateFunction($arFilter['CALENDAR_DATE_FROM'], 'SHORT');
			}
		}
	}

	// Applying filter by PRODUCT_ID
	$operationInfo = Crm\UI\Filter\EntityHandler::findFieldOperation('PRODUCT_ROW_PRODUCT_ID', $arFilter);
	if(is_array($operationInfo))
	{
		$prodID = (int)$operationInfo['CONDITION'];
		if($prodID > 0 && $operationInfo['OPERATION'] === '=')
		{
			$tableAlias = $sender->GetTableAlias();
			$sqlData['WHERE'][] = "{$tableAlias}.ID IN (SELECT LP.OWNER_ID from b_crm_product_row LP where LP.OWNER_TYPE = 'L' and LP.OWNER_ID = {$tableAlias}.ID and LP.PRODUCT_ID = {$prodID})";
		}
	}

	$operationInfo = Crm\UI\Filter\EntityHandler::findFieldOperation('ASSOCIATED_CONTACT_ID', $arFilter);
	if(is_array($operationInfo))
	{
		if($operationInfo['OPERATION'] === '=')
		{
			$sqlData['FROM'][] = LeadContactTable::prepareFilterJoinSql(
				CCrmOwnerType::Contact,
				$operationInfo['CONDITION'],
				$sender->GetTableAlias()
			);
		}
	}

	Tracking\UI\Filter::buildFilterAfterPrepareSql(
		$sqlData,
		$arFilter,
		\CCrmOwnerType::Lead,
		$sender->GetTableAlias()
	);

	if (isset($arFilter['OBSERVER_IDS']))
	{
		$observerIds = is_array($arFilter['OBSERVER_IDS']) ? $arFilter['OBSERVER_IDS'] : [];
		$observersFilter = CCrmEntityHelper::prepareObserversFieldFilter(
			CCrmOwnerType::Lead,
			$sender->GetTableAlias(),
			$observerIds
		);
		if (!empty($observersFilter))
		{
			$sqlData['WHERE'][] = $observersFilter;
		}
	}

	$result = [];
	if(!empty($sqlData['SELECT']))
	{
		$result['SELECT'] = ", ".implode(', ', $sqlData['SELECT']);
	}
	if(!empty($sqlData['FROM']))
	{
		$result['FROM'] = implode(' ', $sqlData['FROM']);
	}
	if(!empty($sqlData['WHERE']))
	{
		$result['WHERE'] = implode(' AND ', $sqlData['WHERE']);
	}

	return !empty($result) ? $result : false;
}