...Человеческий поиск в разработке...
- Модуль: 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; }