Exemplo n.º 1
0
 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);
 }
Exemplo n.º 2
0
				if (!isset($item['LOCATION_ID']))
					$result['ERRORS'] = array('Not found');
				else
				{
					$siteId = '';
					if(!empty($_REQUEST['SITE_ID']))
						$siteId = (string)$_REQUEST['SITE_ID'];
					elseif (defined('SITE_ID'))
						$siteId = SITE_ID;

					$result['DATA']['ID'] = (int)$item['LOCATION_ID'];

					if ($siteId != '')
					{
						if (!Location\SiteLocationTable::checkConnectionExists($siteId, $result['DATA']['ID']))
							$result['ERRORS'] = array('Found, but not connected');
					}
				}
			}
		}
		elseif($_REQUEST['ACT'] == 'GET_ZIP_BY_LOC')
		{
			if(!intval($_REQUEST['LOC']))
				$result['ERRORS'] = array('No location id passed');
			else
			{
				$item = \Bitrix\Sale\Location\LocationTable::getList(
					array(
						'filter' => array(
							'=ID' => intval($_REQUEST['LOC']),
Exemplo n.º 3
0
	public static function processSearchRequest()
	{
		static::checkRequiredModules();

		$parameters = static::processSearchGetParameters();
		$parameters['order'] = array('NAME.NAME' => 'asc');

		// have to implement post-check for site connection, because we need to know which nodes may have connected children
		// will have a bottleneck here, in case of great links and items number. refactor later and replace with some db query

		$siteId = $_REQUEST['FILTER']['SITE_ID'];

		if(strlen($siteId))
		{
			$points = array();
			$res = Location\SiteLocationTable::getConnectedLocations($siteId, array('select' => array(
					'ID' => 'ID',
					'LEFT_MARGIN' => 'LEFT_MARGIN',
					'RIGHT_MARGIN' => 'RIGHT_MARGIN'
				)
			), array('GET_LINKED_THROUGH_GROUPS' => true));

			while($item = $res->fetch())
				$points[intval($item['ID'])] = $item;

			unset($parameters['filter']['SITE_ID']);
		}

		$result = static::processSearchGetList($parameters);
		$result = static::processSearchGetAdditional($result);

		if(strlen($siteId))
		{
			$res = Location\SiteLocationTable::getLinkStatusForMultipleNodes($result['ITEMS'], $siteId, $points);
			foreach($result['ITEMS'] as $k => &$item)
			{
				if($res[$item['ID']] == Location\SiteLocationTable::LSTAT_IN_NOT_CONNECTED_BRANCH)
					unset($result['ITEMS'][$k]);

				$item['IS_UNCHOOSABLE'] = $res[$item['ID']] == Location\SiteLocationTable::LSTAT_ABOVE_CONNECTOR;
			}
		}

		// drop unwanted data
		foreach($result['ITEMS'] as &$item)
		{
			if(!!$_REQUEST['BEHAVIOUR']['PREFORMAT'])
			{
				$unChoosable = $item['IS_UNCHOOSABLE'];
				$path = $item['PATH'];

				$item = array(
					'DISPLAY' => $item['NAME'],
					'VALUE' => $item['ID'],
					'CODE' => $item['CODE'],
					'IS_PARENT' => $item['CHILD_CNT'] > 0
				);

				if($unChoosable)
					$item['IS_UNCHOOSABLE'] = $unChoosable;
				if(is_array($path))
					$item['PATH'] = $path;
			}
			else
			{
				unset($item['LEFT_MARGIN']);
				unset($item['RIGHT_MARGIN']);
			}
		}

		return $result;
	}
Exemplo n.º 4
0
 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();
     }
 }
Exemplo n.º 5
0
 /**
  * <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);
     }
 }
Exemplo n.º 6
0
 /**
  *
  *
  * @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;
     }
 }
Exemplo n.º 7
0
 public static function getSelectedIds($siteId)
 {
     $typesAll = \CSaleLocation::getTypes();
     $types = array();
     if (isset($typesAll['COUNTRY'])) {
         $types[] = "'" . intval($typesAll['COUNTRY']) . "'";
     }
     if (isset($typesAll['REGION'])) {
         $types[] = "'" . intval($typesAll['REGION']) . "'";
     }
     if (isset($typesAll['CITY'])) {
         $types[] = "'" . intval($typesAll['CITY']) . "'";
     }
     $typesAll = array_flip($typesAll);
     if ((string) $siteId != '' && \Bitrix\Sale\Location\SiteLocationTable::checkLinkUsageAny($siteId) && !empty($types)) {
         $result = array();
         $sql = \Bitrix\Sale\Location\SiteLocationTable::getConnectedLocationsSql($siteId, array('select' => array('ID', 'LEFT_MARGIN', 'RIGHT_MARGIN')), array('GET_LINKED_THROUGH_GROUPS' => true));
         if ((string) $sql != '') {
             $res = $GLOBALS['DB']->query("\n\n\t\t\t\t\tselect SL.ID, SL.TYPE_ID from b_sale_location SL \n\t\t\t\t\t\tinner join (\n\t\t\t\t\t\t" . $sql . "\n\t\t\t\t\t\t) as TT on \n\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\tSL.LEFT_MARGIN >= TT.LEFT_MARGIN \n\t\t\t\t\t\t\t\t\t\tand \n\t\t\t\t\t\t\t\t\t\tSL.RIGHT_MARGIN <= TT.RIGHT_MARGIN\n\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\tor\n\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\tSL.LEFT_MARGIN <= TT.LEFT_MARGIN \n\t\t\t\t\t\t\t\t\t\tand \n\t\t\t\t\t\t\t\t\t\tSL.RIGHT_MARGIN >= TT.RIGHT_MARGIN\n\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\tand\n\t\t\t\t\t\t\t\tSL.TYPE_ID in (" . implode(', ', $types) . ")\n\t\t\t\t\t\t\t)\n\n\t\t\t\t\tgroup by SL.ID\n\t\t\t\t");
             while ($item = $res->fetch()) {
                 $typeId = $item['TYPE_ID'];
                 unset($item['TYPE_ID']);
                 $result[$typesAll[$typeId]][$item['ID']] = $item['ID'];
             }
             // special case: when all types are actually selected, an empty string ('') SHOULD be present among $index[$siteId][$type]
             $res = Location\LocationTable::getList(array('filter' => array('TYPE_ID' => $types), 'runtime' => array('CNT' => array('data_type' => 'integer', 'expression' => array('COUNT(*)'))), 'select' => array('CNT', 'TYPE_ID')));
             while ($item = $res->fetch()) {
                 if (intval($item['TYPE_ID'])) {
                     $typeCode = $typesAll[$item['TYPE_ID']];
                     if (isset($result[$typeCode]) && $item['CNT'] == count($result[$typeCode])) {
                         $result[$typeCode][] = '';
                     }
                 }
             }
         }
         return $result;
     } else {
         return array('COUNTRY' => array(''), 'REGION' => array(''), 'CITY' => array(''));
         // means "all"
     }
 }
Exemplo n.º 8
0
	protected function obtainDataAdditional()
	{
		if($this->filterBySite && $this->dbResult['LOCATION']['ID'])
		{
			$linkTypeMap = Location\SiteLocationTable::getLinkStatusForMultipleNodes(array($this->dbResult['LOCATION']), $this->arParams['FILTER_SITE_ID'], $this->dbResult['TEMP']['CONNECTORS']);

			if(!in_array($linkTypeMap[$this->dbResult['LOCATION']['ID']], array(Location\Connector::LSTAT_IS_CONNECTOR, Location\Connector::LSTAT_BELOW_CONNECTOR)))
				$this->errors['NONFATAL'][] = Loc::getMessage('SALE_SLS_SELECTED_NODE_UNCHOOSABLE');
		}
	}
Exemplo n.º 9
0
 /**
  *
  *
  * @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;
     }
 }