public static function initializeData() { $locationTable = Location\LocationTable::getTableName(); $groupLocationTable = Location\GroupLocationTable::getTableName(); $siteLocationTable = Location\SiteLocationTable::getTableName(); // ORACLE: OK, MSSQL: OK $sql = "\n\t\t\tinsert into " . static::getTableName() . " \n\t\t\t\t(LOCATION_ID, SITE_ID) \n\t\t\tselect LC.ID, LS.SITE_ID\n\t\t\t\tfrom " . $siteLocationTable . " LS\n\t\t\t\t\tinner join " . $locationTable . " L on LS.LOCATION_ID = L.ID and LS.LOCATION_TYPE = 'L'\n\t\t\t\t\tinner join " . $locationTable . " LC on LC.LEFT_MARGIN >= L.LEFT_MARGIN and LC.RIGHT_MARGIN <= L.RIGHT_MARGIN\n\t\t\tunion \n\t\t\tselect LC.ID, LS.SITE_ID\n\t\t\t\tfrom " . $siteLocationTable . " LS\n\t\t\t\t\tinner join " . $groupLocationTable . " LG on LS.LOCATION_ID = LG.LOCATION_GROUP_ID and LS.LOCATION_TYPE = 'G'\n\t\t\t\t\tinner join " . $locationTable . " L on LG.LOCATION_ID = L.ID\n\t\t\t\t\tinner join " . $locationTable . " LC on LC.LEFT_MARGIN >= L.LEFT_MARGIN and LC.RIGHT_MARGIN <= L.RIGHT_MARGIN\n\t\t"; Main\HttpApplication::getConnection()->query($sql); }
protected static function processSearchGetList($parameters) { if($gId = intval($_REQUEST['FILTER']['GROUP_ID'])) { $res = Location\GroupLocationTable::getConnectedLocations($gId, $parameters); $res->addReplacedAliases(array('LNAME' => 'NAME')); $result = array(); while($item = $res->fetch()) { $result[$item['ID']] = $item; } } else $result = parent::processSearchGetList($parameters); return $result; }
protected function stageDeleteAll() { switch ($this->step) { case 0: $this->dbConnection->query('truncate table ' . Location\LocationTable::getTableName()); break; case 1: $this->dbConnection->query('truncate table ' . Location\Name\LocationTable::getTableName()); break; case 2: $this->dbConnection->query('truncate table ' . Location\ExternalTable::getTableName()); break; case 3: Location\GroupLocationTable::deleteAll(); break; case 4: Location\SiteLocationTable::deleteAll(); break; } $this->nextStep(); if ($this->step >= 5) { $this->nextStage(); } }
/** * <p>Метод удаляет все местоположения из базы. Метод динамичный.</p> <br><br> * * * @return mixed * * @static * @link http://dev.1c-bitrix.ru/api_help/sale/classes/csalelocation/csalelocation__deleteall.1cda6559.php * @author Bitrix */ public static function DeleteAll() { global $DB; foreach (GetModuleEvents("sale", "OnBeforeLocationDeleteAll", true) as $arEvent) { if (ExecuteModuleEventEx($arEvent) === false) { return false; } } if (self::isLocationProMigrated()) { //main $DB->Query("DELETE FROM " . Location\LocationTable::getTableName()); $DB->Query("DELETE FROM " . Location\GroupTable::getTableName()); $DB->Query("DELETE FROM " . Location\TypeTable::getTableName()); //names $DB->Query("DELETE FROM " . Location\Name\LocationTable::getTableName()); $DB->Query("DELETE FROM " . Location\Name\GroupTable::getTableName()); $DB->Query("DELETE FROM " . Location\Name\TypeTable::getTableName()); //links $DB->Query("DELETE FROM " . Location\GroupLocationTable::getTableName()); $DB->Query("DELETE FROM " . Location\SiteLocationTable::getTableName()); $DB->Query("DELETE FROM " . Delivery\DeliveryLocationTable::getTableName()); //other $DB->Query("DELETE FROM " . Location\DefaultSiteTable::getTableName()); $DB->Query("DELETE FROM " . Location\ExternalTable::getTableName()); $DB->Query("DELETE FROM " . Location\ExternalServiceTable::getTableName()); } $DB->Query("DELETE FROM b_sale_location2location_group"); $DB->Query("DELETE FROM b_sale_location_group_lang"); $DB->Query("DELETE FROM b_sale_location_group"); $DB->Query("DELETE FROM b_sale_delivery2location"); $DB->Query("DELETE FROM b_sale_location"); $DB->Query("DELETE FROM b_sale_location_city_lang"); $DB->Query("DELETE FROM b_sale_location_city"); $DB->Query("DELETE FROM b_sale_location_country_lang"); $DB->Query("DELETE FROM b_sale_location_country"); $DB->Query("DELETE FROM b_sale_location_region_lang"); $DB->Query("DELETE FROM b_sale_location_region"); $DB->Query("DELETE FROM b_sale_location_zip"); foreach (GetModuleEvents("sale", "OnLocationDeleteAll", true) as $arEvent) { ExecuteModuleEventEx($arEvent); } }
public static function getConnectedEntitiesQuery($locationPrimary, $linkType = 'id', $parameters = array()) { if ($linkType == 'id') { $locationPrimary = Assert::expectIntegerPositive($locationPrimary, Loc::getMessage('SALE_LOCATION_CONNECTOR_ENTITY_LOCATION_PRIMARY_FLD_NAME')); } else { $locationPrimary = Assert::expectStringNotNull($locationPrimary, Loc::getMessage('SALE_LOCATION_CONNECTOR_ENTITY_LOCATION_PRIMARY_FLD_NAME')); } $useGroups = GroupTable::checkGroupUsage() && static::getUseGroups(); // check if we have groups in project and entity uses groups $useCodes = static::getUseCodes(); // this entity uses codes $groupUseCodes = GroupLocationTable::getUseCodes(); // group entity uses codes $typeFld = static::getTypeField(); /*LOCATION_TYPE*/ $linkFld = static::getLinkField(); /*DELIVERY_ID*/ $locationLinkFld = static::getLocationLinkField(); /*LOCATION_ID*/ $targetPrimaryFld = static::getTargetEntityPrimaryField(); /*ID*/ $groupLocationLinkFld = GroupLocationTable::getLocationLinkField(); /*LOCATION_ID*/ $groupLinkFld = GroupLocationTable::getLinkField(); /*LOCATION_GROUP_ID*/ $seachById = $linkType == 'id'; $dbConnection = Main\HttpApplication::getConnection(); if (!is_array($parameters)) { $parameters = array(); } if (is_array($parameters['runtime'])) { Assert::announceNotImplemented('Sorry, runtime clause is not implemented currently.'); } $order = array(); if (is_array($parameters['order'])) { Assert::announceNotImplemented('Sorry, order-over-union clause is not implemented currently.'); } $filter = array(); if (is_array($parameters['filter']) && !empty($parameters['filter'])) { $filter = $parameters['filter']; } $select = array('*'); if (is_array($parameters['select']) && !empty($parameters['select'])) { $select = $parameters['select']; } /* query example when working with delivery: select distinct D.* from b_sale_delivery D inner join b_sale_delivery2location DL on D.ID = DL.DELIVERY_ID and DL.LOCATION_TYPE = 'L' inner join b_sale_location L1 on L1.CODE = DL.LOCATION_ID inner join b_sale_location L2 on L2.ID(there will be CODE, if we search by code) = 65683 and L2.LEFT_MARGIN >= L1.LEFT_MARGIN and L2.RIGHT_MARGIN <= L1.RIGHT_MARGIN; */ $query = new Entity\Query(static::getTargetEntityName()); $DLCondition = array('=this.' . $targetPrimaryFld => 'ref.' . $linkFld); if ($useGroups) { $DLCondition['=ref.' . $typeFld] = array('?', self::DB_LOCATION_FLAG); } $query->registerRuntimeField('DL', array('data_type' => get_called_class(), 'reference' => $DLCondition, 'join_type' => 'inner'))->registerRuntimeField('L1', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=this.DL.' . $locationLinkFld => 'ref.' . ($useCodes ? 'CODE' : 'ID')), 'join_type' => 'inner'))->registerRuntimeField('L2', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=ref.' . ($seachById ? 'ID' : 'CODE') => array('?', $locationPrimary), '>=ref.LEFT_MARGIN' => 'this.L1.LEFT_MARGIN', '<=ref.RIGHT_MARGIN' => 'this.L1.RIGHT_MARGIN'), 'join_type' => 'inner'))->setSelect($select)->setFilter($filter)->setOrder($order); if (!$useGroups) { // emulate "select distinct" $query->setGroup($select); return $query->getQuery(); } else { $sqls = array($query->getQuery()); $query = new Entity\Query(static::getTargetEntityName()); /* query example when working with delivery: select D.* from b_sale_delivery D inner join b_sale_delivery2location DL on D.ID = DL.DELIVERY_ID and DL.LOCATION_TYPE = 'G' inner join b_sale_location_group G on G.CODE = DL.LOCATION_ID (if this entity uses ID, skip this join) inner join b_sale_grouplocation GL on GL.LOCATION_GROUP_ID = G.ID (if this entity uses ID, there will be DL.LOCATION_ID) inner join b_sale_location L1 on L1.ID (there will be CODE, if grouplocation entity uses CODE) = GL.LOCATION_ID inner join b_sale_location L2 on L2.ID (there will be CODE, if we seach by code) = 65683 and L2.LEFT_MARGIN >= L1.LEFT_MARGIN and L2.RIGHT_MARGIN <= L1.RIGHT_MARGIN; */ $query->registerRuntimeField('DL', array('data_type' => get_called_class(), 'reference' => array('=this.' . $targetPrimaryFld => 'ref.' . $linkFld, '=ref.' . $typeFld => array('?', self::DB_GROUP_FLAG)), 'join_type' => 'inner')); if ($useCodes) { $query->registerRuntimeField('G', array('data_type' => '\\Bitrix\\Sale\\Location\\Group', 'reference' => array('=this.DL.' . $locationLinkFld => 'ref.CODE'), 'join_type' => 'inner')); } $query->registerRuntimeField('GL', array('data_type' => '\\Bitrix\\Sale\\Location\\GroupLocation', 'reference' => array($useCodes ? '=this.G.ID' : '=this.DL.' . $locationLinkFld => 'ref.' . $groupLinkFld), 'join_type' => 'inner'))->registerRuntimeField('L1', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=this.GL.' . $groupLocationLinkFld => 'ref.' . ($groupUseCodes ? 'CODE' : 'ID')), 'join_type' => 'inner'))->registerRuntimeField('L2', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=ref.' . ($seachById ? 'ID' : 'CODE') => array('?', $locationPrimary), '>=ref.LEFT_MARGIN' => 'this.L1.LEFT_MARGIN', '<=ref.RIGHT_MARGIN' => 'this.L1.RIGHT_MARGIN'), 'join_type' => 'inner'))->setSelect($select)->setFilter($filter)->setOrder($order); $sqls[] = $query->getQuery(); return static::unionize($sqls); } }
/** * * * @param * * @return */ protected static function findNoIndex($parameters) { $dbConnection = Main\HttpApplication::getConnection(); $dbHelper = $dbConnection->getSqlHelper(); // tables $locationTable = Location\LocationTable::getTableName(); $locationNameTable = Location\Name\LocationTable::getTableName(); $locationGroupTable = Location\GroupLocationTable::getTableName(); $locationSiteTable = Location\SiteLocationTable::getTableName(); $locationTypeTable = Location\TypeTable::getTableName(); ////////////////////////////////// // sql parameters prepare ////////////////////////////////// $filter = static::parseFilter($parameters['filter']); if (strlen($filter['SITE_ID']['VALUE'])) { $filterSite = $dbHelper->forSql(substr($filter['SITE_ID']['VALUE'], 0, 2)); $hasLocLinks = Location\SiteLocationTable::checkLinkUsage($filterSite, Location\SiteLocationTable::DB_LOCATION_FLAG); $hasGrpLinks = Location\SiteLocationTable::checkLinkUsage($filterSite, Location\SiteLocationTable::DB_GROUP_FLAG); $doFilterBySite = true; } if (strlen($filter['PHRASE']['VALUE'])) { $doFilterByName = true; $filterName = ToUpper($dbHelper->forSql($filter['PHRASE']['VALUE'])); } if (intval($filter['ID']['VALUE'])) { $doFilterById = true; $filterId = intval($filter['ID']['VALUE']); } if (intval($filter['CODE']['VALUE'])) { $doFilterByCode = true; $filterCode = $dbHelper->forSql($filter['CODE']['VALUE']); } $doFilterByLang = true; if (strlen($filter['NAME.LANGUAGE_ID']['VALUE'])) { $filterLang = $dbHelper->forSql(substr($filter['NAME.LANGUAGE_ID']['VALUE'], 0, 2)); } else { $filterLang = LANGUAGE_ID; } if (isset($filter['PARENT_ID']) && intval($filter['PARENT_ID']['VALUE']) >= 0) { $doFilterByParent = true; $filterParentId = intval($filter['PARENT_ID']['VALUE']); } if (intval($filter['TYPE_ID']['VALUE'])) { $doFilterByType = true; $filterTypeId = intval($filter['TYPE_ID']['VALUE']); } // filter select fields if (!is_array($parameters['select'])) { $parameters['select'] = array(); } $map = Location\LocationTable::getMap(); $nameAlias = false; foreach ($parameters['select'] as $alias => $field) { if ($field == 'CHILD_CNT') { $doCountChildren = true; } if ($field == 'NAME.NAME') { $nameAlias = $alias; } if (!isset($map[$field]) || !in_array($map[$field]['data_type'], array('integer', 'string', 'float', 'boolean')) || isset($map[$field]['expression'])) { unset($parameters['select'][$alias]); } } ////////////////////////////////// // sql query build ////////////////////////////////// // mandatory fields to be selected anyway // alias => field $fields = array('L.ID' => 'L.ID', 'L.CODE' => 'L.CODE', 'L.SORT' => 'L.SORT', 'LT_SORT' => 'LT.DISPLAY_SORT'); if ($nameAlias === false || !preg_match('#^[a-zA-Z0-9]+$#', $nameAlias)) { $fields['NAME'] = 'LN.NAME'; } else { $fields[$nameAlias] = 'LN.NAME'; } $fields = array_merge($fields, array('L.LEFT_MARGIN' => 'L.LEFT_MARGIN', 'L.RIGHT_MARGIN' => 'L.RIGHT_MARGIN')); $groupFields = $fields; // additional fields to select foreach ($parameters['select'] as $alias => $fld) { $lFld = 'L.' . $fld; // check if field is already selected if ((string) $alias === (string) intval($alias)) { // already selected if (in_array($lFld, $fields)) { continue; } $fields[$lFld] = $lFld; //$groupFields[$lFld] = $lFld; } else { if (isset($fields[$alias])) { continue; } $fields[$alias] = $lFld; //$groupFields[$alias] = $lFld; } $groupFields[$lFld] = $lFld; } if ($doCountChildren) { $fields['CHILD_CNT'] = 'COUNT(LC.ID)'; } // make select sql $selectSql = array(); foreach ($fields as $alias => $fld) { if ($fld == $alias) { $selectSql[] = $fld; } else { $selectSql[] = $fld . ' as ' . $alias; } } $selectSql = implode(', ', $selectSql); //$groupSql = implode(', ', array_keys($groupFields)); $groupSql = implode(', ', $groupFields); $mainSql = "select {$selectSql}\n\t\t\t\t\t\tfrom {$locationTable} L \n\t\t\t\t\t\t\tinner join {$locationNameTable} LN on L.ID = LN.LOCATION_ID\n\t\t\t\t\t\t\tinner join {$locationTypeTable} LT on L.TYPE_ID = LT.ID " . ($doCountChildren ? "\n\t\t\t\t\t\t\t\tleft join {$locationTable} LC on L.ID = LC.PARENT_ID\n\t\t\t\t\t\t\t" : "") . " \n\n\t\t\t\t\t\t%SITE_FILTER_CONDITION%\n\n\t\t\t\t\t\twhere \n\n\t\t\t\t\t\t\t%MAIN_FILTER_CONDITION%\n\n\t\t\t\t\t\t\t%GROUP_BY%\n\t\t\t\t\t\t\t"; $where = array(); if ($doFilterByLang) { $where[] = "LN.LANGUAGE_ID = '" . $filterLang . "'"; } if ($doFilterByParent) { $where[] = "L.PARENT_ID = '" . $filterParentId . "'"; } if ($doFilterById) { $where[] = "L.ID = '" . $filterId . "'"; } if ($doFilterByCode) { $where[] = "L.CODE = '" . $filterCode . "'"; } if ($doFilterByType) { $where[] = "L.TYPE_ID = '" . $filterTypeId . "'"; } if ($doFilterByName) { $where[] = "LN.NAME_UPPER like '" . $filterName . "%'"; } $mainSql = str_replace('%MAIN_FILTER_CONDITION%', implode(' and ', $where), $mainSql); $needDistinct = false; $unionized = false; $artificialNav = false; if (!$doFilterBySite) { $sql = str_replace('%SITE_FILTER_CONDITION%', '', $mainSql); } else { $sql = array(); if ($hasLocLinks) { $sql[] = str_replace('%SITE_FILTER_CONDITION%', "\n\n\t\t\t\t\tinner join {$locationTable} L2 on L2.LEFT_MARGIN <= L.LEFT_MARGIN and L2.RIGHT_MARGIN >= L.RIGHT_MARGIN\n\t\t\t\t\tinner join {$locationSiteTable} LS2 on L2.ID = LS2.LOCATION_ID and LS2.LOCATION_TYPE = 'L' and LS2.SITE_ID = '{$filterSite}'\n\n\t\t\t\t", $mainSql); } if ($hasGrpLinks) { $sql[] = str_replace('%SITE_FILTER_CONDITION%', "\n\n\t\t\t\t\tinner join {$locationTable} L2 on L2.LEFT_MARGIN <= L.LEFT_MARGIN and L2.RIGHT_MARGIN >= L.RIGHT_MARGIN\n\t\t\t\t\tinner join {$locationGroupTable} LG on LG.LOCATION_ID = L2.ID\n\t\t\t\t\tinner join {$locationSiteTable} LS2 on LG.LOCATION_GROUP_ID = LS2.LOCATION_ID and LS2.LOCATION_TYPE = 'G' and LS2.SITE_ID = '{$filterSite}'\n\n\t\t\t\t", $mainSql); $useDistinct = true; } $cnt = count($sql); if ($cnt == 1) { $needDistinct = true; } else { // UNION removes duplicates, so distinct is required only when no union here $unionized = true; } $sql = ($cnt > 1 ? '(' : '') . implode(') union (', $sql) . ($cnt > 1 ? ')' : ''); } // set groupping if needed $sql = str_replace('%GROUP_BY%', $needDistinct || $doCountChildren ? "group by {$groupSql}" : '', $sql); if (!is_array($parameters['order'])) { $sql .= " order by 3, 4 asc, 5"; } else { // currenly spike if (isset($parameters['order']['NAME.NAME'])) { $sql .= " order by 5 " . ($parameters['order']['NAME.NAME'] == 'asc' ? 'asc' : 'desc'); } } $offset = intval($parameters['offset']); $limit = intval($parameters['limit']); if ($limit) { if ($dbConnection->getType() == 'mssql') { // due to huge amount of limitations of windowed functions in transact, using artificial nav here // (does not support UNION and integer indices in ORDER BY) $artificialNav = true; } else { $sql = $dbHelper->getTopSql($sql, $limit, $offset); } } $res = $dbConnection->query($sql); if ($artificialNav) { $result = array(); $i = -1; while ($item = $res->fetch()) { $i++; if ($i < $offset) { continue; } if ($i >= $offset + $limit) { break; } $result[] = $item; } return new DB\ArrayResult($result); } else { return $res; } }
/** * * * @param * * @return */ public static function getListFast($parameters = array()) { $dbConnection = Main\HttpApplication::getConnection(); $dbHelper = $dbConnection->getSqlHelper(); // we require autocomplete to answer ASAP, so say hello to direct query // tables $locationTable = LocationTable::getTableName(); $locationNameTable = Name\LocationTable::getTableName(); $locationGroupTable = GroupLocationTable::getTableName(); $locationSiteTable = SiteLocationTable::getTableName(); $locationTypeTable = TypeTable::getTableName(); ////////////////////////////////// // sql parameters prepare ////////////////////////////////// if (strlen($parameters['filter']['SITE_ID'])) { $filterSite = $dbHelper->forSql(substr($parameters['filter']['SITE_ID'], 0, 2)); $hasLocLinks = SiteLocationTable::checkLinkUsage($filterSite, 'L'); $hasGrpLinks = SiteLocationTable::checkLinkUsage($filterSite, 'G'); if ($hasLocLinks || $hasGrpLinks) { $doFilterBySite = true; } } if (strlen($parameters['filter']['NAME'])) { $doFilterByMainParams = true; $doFilterByName = true; // user-typed '%' are not allowed in like expression - ddos risk $filterName = ToUpper($dbHelper->forSql(str_replace('%', '', $parameters['filter']['NAME']))); } if (strlen($parameters['filter']['PRIMARY'])) { $doFilterByMainParams = true; $doFilterByPrimaryCode = true; // user-typed '%' are not allowed in like expression - ddos risk $filterPrimaryCode = ToLower($dbHelper->forSql(str_replace('%', '', $parameters['filter']['PRIMARY']))); if (is_numeric($parameters['filter']['PRIMARY']) && $parameters['filter']['PRIMARY'] == intval($parameters['filter']['PRIMARY'])) { $doFilterByPrimaryId = true; $filterPrimaryId = intval($parameters['filter']['PRIMARY']); } } if (intval($parameters['filter']['ID'])) { $doFilterById = true; $filterId = intval($parameters['filter']['ID']); } if (strlen($parameters['filter']['LANGUAGE_ID'])) { $filterLang = $dbHelper->forSql(substr($parameters['filter']['LANGUAGE_ID'], 0, 2)); } if (isset($parameters['filter']['PARENT_ID']) && intval($parameters['filter']['PARENT_ID']) >= 0) { $doFilterByParent = true; $filterParentId = intval($parameters['filter']['PARENT_ID']); } if (intval($parameters['filter']['TYPE_ID'])) { $doFilterByType = true; $filterTypeId = intval($parameters['filter']['TYPE_ID']); } if (intval($parameters['filter']['EXCLUDE_SUBTREE'])) { $doFilterByExclude = true; $filterExclude = intval($parameters['filter']['EXCLUDE_SUBTREE']); $res = self::getById($filterExclude)->fetch(); if ($res) { $excludeMarginLeft = $res['LEFT_MARGIN']; $excludeMarginRight = $res['RIGHT_MARGIN']; } else { $doFilterByExclude = false; } } // filter select fields if (!is_array($parameters['select'])) { $parameters['select'] = array(); } $map = self::getMap(); foreach ($parameters['select'] as $k => $field) { if ($field == 'CHILD_CNT') { $doCountChildren = true; } if (in_array($field, array('ID', 'CODE', 'SORT', 'LEFT_MARGIN', 'RIGHT_MARGIN')) || !isset($map[$field]) || !in_array($map[$field]['data_type'], array('integer', 'string', 'float', 'boolean'))) { unset($parameters['select'][$k]); } } ////////////////////////////////// // sql query build ////////////////////////////////// $fields = array('L.ID' => 'L.ID', 'L.CODE' => 'L.CODE', 'L.SORT' => 'L.SORT', 'LT.SORT' => 'LT_SORT', 'LN.NAME' => 'LN.NAME', 'L.LEFT_MARGIN' => 'L.LEFT_MARGIN', 'L.RIGHT_MARGIN' => 'L.RIGHT_MARGIN'); $groupFields = $fields; // for select fields and group fields foreach ($parameters['select'] as $fld) { if ($fld == 'CHILD_CNT') { $fields['COUNT(LC.ID)'] = 'CHILD_CNT'; } else { $lFld = 'L.' . $fld; if (isset($fields[$lFld])) { continue; } $fields[$lFld] = $lFld; $groupFields[$lFld] = $lFld; } } // make select sql $selectSql = array(); foreach ($fields as $fld => $alias) { if ($fld == $alias) { $selectSql[] = $fld; } else { $selectSql[] = $fld . ' as ' . $alias; } } $selectSql = implode(', ', $selectSql); $groupSql = implode(', ', array_keys($groupFields)); $mainSql = "select {$selectSql}\n\t\t\t\t\t\tfrom {$locationTable} L \n\t\t\t\t\t\t\tinner join {$locationNameTable} LN on L.ID = LN.LOCATION_ID\n\t\t\t\t\t\t\tinner join {$locationTypeTable} LT on L.TYPE_ID = LT.ID " . ($doCountChildren ? "\n\t\t\t\t\t\t\t\tleft join {$locationTable} LC on L.ID = LC.PARENT_ID\n\t\t\t\t\t\t\t" : "") . " \n\n\t\t\t\t\t\t%SITE_FILTER_CONDITION%\n\n\t\t\t\t\t\twhere \n\t\t\t\t\t\t\tLN.LANGUAGE_ID = '{$filterLang}'\n\n\t\t\t\t\t\t\t%MAIN_FILTER_CONDITION%\n\n\t\t\t\t\t\t\t" . ($doFilterByParent ? "\n\t\t\t\t\t\t\t\tand L.PARENT_ID = {$filterParentId}\n\t\t\t\t\t\t\t" : "") . ($doFilterById ? "\n\t\t\t\t\t\t\t\tand L.ID = {$filterId}\n\t\t\t\t\t\t\t" : "") . ($doFilterByExclude ? "\n\t\t\t\t\t\t\t\tand not(L.LEFT_MARGIN <= {$excludeMarginLeft} and L.RIGHT_MARGIN >= {$excludeMarginRight})\n\t\t\t\t\t\t\t" : "") . ($doFilterByType ? "\n\t\t\t\t\t\t\t\tand L.TYPE_ID = {$filterTypeId}\n\t\t\t\t\t\t\t" : "") . ($doCountChildren ? "\n\t\t\t\t\t\t\t\tgroup by {$groupSql}\n\t\t\t\t\t\t\t" : ""); // todo: when search by ID or CODE, the better way is to break query onto UNIONs: first union stands for NAME_UPPER search, second - for ID exact match and the third - for CODE if ($doFilterByMainParams) { $mp = array(); if ($doFilterByPrimaryId) { $mp[] = "L.ID = {$filterPrimaryId}"; } if ($doFilterByPrimaryCode) { $mp[] = "L.CODE like '{$filterPrimaryCode}%'"; } if ($doFilterByName) { $mp[] = "LN.NAME_UPPER like '{$filterName}%'"; } $cnt = count($mp); $mainSql = str_replace('%MAIN_FILTER_CONDITION%', ' and ' . ($cnt > 1 ? '(' : '') . implode(' or ', $mp) . ($cnt > 1 ? ')' : ''), $mainSql); } else { $mainSql = str_replace('%MAIN_FILTER_CONDITION%', '', $mainSql); } if (!$doFilterBySite) { $sql = str_replace('%SITE_FILTER_CONDITION%', '', $mainSql); } else { $sql = array(); if ($hasLocLinks) { $sql[] = str_replace('%SITE_FILTER_CONDITION%', "\n\n\t\t\t\t\tinner join {$locationTable} L2 on L2.LEFT_MARGIN <= L.LEFT_MARGIN and L2.RIGHT_MARGIN >= L.RIGHT_MARGIN\n\t\t\t\t\tinner join {$locationSiteTable} LS2 on L2.ID = LS2.LOCATION_ID and LS2.LOCATION_TYPE = 'L' and LS2.SITE_ID = '{$filterSite}'\n\n\t\t\t\t", $mainSql); } if ($hasGrpLinks) { $sql[] = str_replace('%SITE_FILTER_CONDITION%', "\n\n\t\t\t\t\tinner join {$locationTable} L2 on L2.LEFT_MARGIN <= L.LEFT_MARGIN and L2.RIGHT_MARGIN >= L.RIGHT_MARGIN\n\t\t\t\t\tinner join {$locationGroupTable} LG on LG.LOCATION_ID = L2.ID\n\t\t\t\t\tinner join {$locationSiteTable} LS2 on LG.LOCATION_GROUP_ID = LS2.LOCATION_ID and LS2.LOCATION_TYPE = 'G' and LS2.SITE_ID = '{$filterSite}'\n\n\t\t\t\t", $mainSql); } $cnt = count($sql); $sql = ($cnt > 1 ? '(' : '') . implode(') union (', $sql) . ($cnt > 1 ? ')' : ''); } if (!is_array($parameters['order'])) { $sql .= " order by 3, 4 desc, 5"; } else { // currenly spike if (isset($parameters['order']['NAME.NAME'])) { $sql .= " order by 5 " . ($parameters['order']['NAME.NAME'] == 'asc' ? 'asc' : 'desc'); } } $artificialNav = false; $offset = intval($parameters['offset']); $limit = intval($parameters['limit']); if ($limit) { if ($dbConnection->getType() != 'mssql') { $sql = $dbHelper->getTopSql($sql, $limit, $offset); } else { // have no idea how to use limit-offset in UNION for transact $artificialNav = true; } } $res = $dbConnection->query($sql); if ($artificialNav) { $result = array(); $i = -1; while ($item = $res->fetch()) { $i++; if ($i < $offset) { continue; } if ($i >= $offset + $limit) { break; } $result[] = $item; } return new DB\ArrayResult($result); } else { return $res; } }