CAllCrmDeal::__AfterPrepareSql

  1. Bitrix24 API (v. 23.675.0)
  2. crm
  3. CAllCrmDeal
  4. __AfterPrepareSql
  • Модуль: crm
  • Путь к файлу: ~/bitrix/modules/crm/classes/general/crm_deal.php
  • Класс: \CAllCrmDeal
  • Вызов: CAllCrmDeal::__AfterPrepareSql
static function __AfterPrepareSql(/*CCrmEntityListBuilder*/ $sender, $arOrder, $arFilter, $arGroupBy, $arSelectFields)
{
	$sqlData = [
		'SELECT' => [],
		'FROM' => [],
		'WHERE' => [],
		'ORDERBY' => [],
	];
	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::Deal,
				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['%STAGE_ID_FROM_HISTORY']) || !empty($arFilter['%STAGE_ID_FROM_SUPPOSED_HISTORY']) || !empty($arFilter['%STAGE_SEMANTIC_ID_FROM_HISTORY']))
	{
		global $DB;

		$supposedHistoryConditions = [];

		if (!empty($arFilter['ACTIVE_TIME_PERIOD_from']) && !empty($arFilter['ACTIVE_TIME_PERIOD_to']))
		{
			$supposedHistoryConditions[] = "DSHWS.LAST_UPDATE_DATE <= " . $DB->CharToDateFunction($arFilter['ACTIVE_TIME_PERIOD_to'], 'SHORT');
			$supposedHistoryConditions[] = "DSHWS.CLOSE_DATE >= " . $DB->CharToDateFunction($arFilter['ACTIVE_TIME_PERIOD_from'], 'SHORT');
		}

		if (!empty($arFilter['%STAGE_SEMANTIC_ID_FROM_HISTORY']))
		{
			$stageSemanticIdsFromFilter = is_array($arFilter['%STAGE_SEMANTIC_ID_FROM_HISTORY']) ? $arFilter['%STAGE_SEMANTIC_ID_FROM_HISTORY'] : array($arFilter['%STAGE_SEMANTIC_ID_FROM_HISTORY']);
			$stageIdsForSql = [];
			foreach ($stageSemanticIdsFromFilter as $value)
			{
				$stageIdsForSql[] = "'" . \Bitrix\Main\Application::getConnection()->getSqlHelper()->forSql($value) . "'";
			}
			$supposedHistoryConditions[] = "DSHWS.IS_SUPPOSED = 'N'";
			$supposedHistoryConditions[] = "DSHWS.STAGE_SEMANTIC_ID IN (" . implode(', ', $stageIdsForSql) . ")";
		}

		if (!empty($arFilter['%STAGE_ID_FROM_HISTORY']))
		{
			$statusIdsFromFilter = is_array($arFilter['%STAGE_ID_FROM_HISTORY']) ? $arFilter['%STAGE_ID_FROM_HISTORY'] : array($arFilter['%STAGE_ID_FROM_HISTORY']);
			$statusIdsForSql = [];
			foreach ($statusIdsFromFilter as $value)
			{
				$statusIdsForSql[] = "'" . \Bitrix\Main\Application::getConnection()->getSqlHelper()->forSql($value) . "'";
			}
			$supposedHistoryConditions[] = "DSHWS.IS_SUPPOSED = 'N'";
			$supposedHistoryConditions[] = "DSHWS.STAGE_ID  IN (" . implode(',', $statusIdsForSql) . ")";
		}

		if (!empty($arFilter['%STAGE_ID_FROM_SUPPOSED_HISTORY']))
		{
			$statusIdsFromFilter = is_array($arFilter['%STAGE_ID_FROM_SUPPOSED_HISTORY']) ? $arFilter['%STAGE_ID_FROM_SUPPOSED_HISTORY'] : array($arFilter['%STAGE_ID_FROM_SUPPOSED_HISTORY']);
			$statusIdsForSql = [];
			foreach ($statusIdsFromFilter as $value)
			{
				$statusIdsForSql[] = "'" . \Bitrix\Main\Application::getConnection()->getSqlHelper()->forSql($value) . "'";
			}
			$supposedHistoryConditions[] = "DSHWS.STAGE_ID IN (" . implode(',', $statusIdsForSql) . ")";
		}

		if(count($supposedHistoryConditions) > 0)
		{
			$sqlData['WHERE'][] = "L.ID IN (SELECT DISTINCT DSHWS.OWNER_ID FROM b_crm_deal_stage_history_with_supposed DSHWS 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');
		}
		elseif ($arFilter['CALENDAR_FIELD'] == 'CLOSEDATE')
		{
			$sqlData['WHERE'][] = "L.CLOSEDATE <= ".$DB->CharToDateFunction($arFilter['CALENDAR_DATE_TO'], 'SHORT');
			$sqlData['WHERE'][] = "L.CLOSEDATE >= ".$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_deal
				$sqlData['FROM'][] = "INNER JOIN b_calendar_resource RBUF ".
					"ON RBUF.PARENT_ID = L.ID".
					" AND RBUF.PARENT_TYPE = 'CRM_DEAL'".
					" 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
	$productId = 0;
	$operationInfo = Crm\UI\Filter\EntityHandler::findFieldOperation('PRODUCT_ROW_PRODUCT_ID', $arFilter);
	if(is_array($operationInfo))
	{
		$productFilter = '';
		$productId = $operationInfo['CONDITION'];
		if (is_array($productId))
		{
			\Bitrix\Main\Type\Collection::normalizeArrayValuesByInt($productId);
			$productIds = implode(',', $productId);
			$productFilter = "and DP.PRODUCT_ID in ({$productIds})";
		}
		else
		{
			$productId = (int)$productId;
			if ($productId > 0)
			{
				$productFilter = "and DP.PRODUCT_ID = {$productId}";
			}
		}
		if (!empty($productFilter) && $operationInfo['OPERATION'] === '=')
		{
			$tableAlias = $sender->GetTableAlias();
			$sqlData['WHERE'][] = "{$tableAlias}.ID IN (
				SELECT DP.OWNER_ID from b_crm_product_row DP
				where DP.OWNER_TYPE = 'D'
				and DP.OWNER_ID = {$tableAlias}.ID
				{$productFilter}
			)";
		}
	}

	$operationInfo = Crm\UI\Filter\EntityHandler::findFieldOperation('IS_PRODUCT_RESERVED', $arFilter);
	if (is_array($operationInfo) && \Bitrix\Main\Loader::includeModule('sale'))
	{
		$productFilter = '';
		if (is_array($productId))
		{
			\Bitrix\Main\Type\Collection::normalizeArrayValuesByInt($productId);
			$productIds = implode(',', $productId);
			$productFilter = "and DP.PRODUCT_ID in ({$productIds})";
		}
		elseif ($productId > 0)
		{
			$productFilter ="AND DP.PRODUCT_ID = {$productId}";
		}

		$inCondition = $operationInfo['CONDITION'] === 'Y' ? 'IN' : 'NOT IN';
		$reserveStoreFilter = '';
		$reserveStoreIdOperationInfo = Crm\UI\Filter\EntityHandler::findFieldOperation('RESERVE_STORE_ID', $arFilter);
		if (is_array($reserveStoreIdOperationInfo))
		{
			$reserveStoreId = (int)$reserveStoreIdOperationInfo['CONDITION'];
			$reserveStoreFilter = "AND BR.STORE_ID = {$reserveStoreId}";
		}
		$tableAlias = $sender->GetTableAlias();
		$sqlData['WHERE'][] = "{$tableAlias}.ID {$inCondition} (
			SELECT DP.OWNER_ID FROM b_crm_product_row DP
			INNER JOIN b_crm_product_reservation_map RM ON RM.PRODUCT_ROW_ID = DP.ID
			INNER JOIN b_sale_basket_reservation BR ON RM.BASKET_RESERVATION_ID = BR.ID
			WHERE DP.OWNER_TYPE = 'D'
			AND BR.QUANTITY > 0
			{$productFilter}
			{$reserveStoreFilter}
		)";
	}

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

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

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

	$sqlData = array_merge_recursive(
		$sqlData,
		self::getClientUFSqlData(
			$arOrder,
			$arFilter,
			CCrmOwnerType::Contact
		),
		self::getClientUFSqlData(
			$arOrder,
			$arFilter,
			CCrmOwnerType::Company
		),
	);

	$result = array();
	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']);
	}
	if(!empty($sqlData['ORDERBY']))
	{
		$result['ORDERBY'] = [
			'SQL' => implode(', ', $sqlData['ORDERBY']),
			'POSITION' => 0,
		];
	}

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

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