/modules/sale/lib/location/search/finder.php

<?php
/**
 * Bitrix Framework
 * @package Bitrix\Sale\Location
 * @subpackage sale
 * @copyright 2001-2014 Bitrix
 */
namespace Bitrix\Sale\Location\Search;

use Bitrix\Main;
use Bitrix\Main\DB;
use Bitrix\Main\Localization\Loc;
use Bitrix\Main\Config\Option;

use Bitrix\Sale\Location;
use Bitrix\Sale\Location\Util\Assert;

Loc::loadMessages(__FILE__);

class Finder
{
	const SALE_LOCATION_INDEXED_TYPES_OPT = 		'sale.location.indexed_types';
	const SALE_LOCATION_INDEXED_LANGUAGES_OPT = 	'sale.location.indexed_langs';
	const SALE_LOCATION_INDEX_VALID_OPT = 			'sale.location.index_valid';

	protected static $allowedOperations = array(
		'=' => true
	);

	public static function checkIndexValid()
	{
		return Option::get('sale', self::SALE_LOCATION_INDEX_VALID_OPT, '', '') == 'Y';
	}

	public static function setIndexValid()
	{
		Option::set('sale', self::SALE_LOCATION_INDEX_VALID_OPT, 'Y', '');
	}

	public static function setIndexInvalid()
	{
		Option::set('sale', self::SALE_LOCATION_INDEX_VALID_OPT, 'N', '');
	}

	public static function getIndexedTypes()
	{
		$types = Option::get('sale', self::SALE_LOCATION_INDEXED_TYPES_OPT, '', '');
		$typesFromDb = static::getTypesFromDb();

		if($types == '') // means "all"
			return array_keys($typesFromDb);

		$types = explode(':', $types);
		$result = array();

		if(is_array($types))
		{
			foreach($types as $type)
			{
				$type = intval($type);
				if(isset($typesFromDb[$type]))
					$result[] = $type;
			}
		}

		return array_unique($result);
	}

	public static function setIndexedTypes($types = array())
	{
		$result = array();
		if(is_array($types) && !empty($types))
		{
			$typesFromDb = static::getTypesFromDb();

			foreach($types as $type)
			{
				$type = intval($type);
				if(isset($typesFromDb[$type]))
					$result[] = $type;
			}

			$result = array_unique($result);
		}

		Option::set('sale', self::SALE_LOCATION_INDEXED_TYPES_OPT, implode(':', $result), '');
	}

	public static function getIndexedLanguages()
	{
		$langs = Option::get('sale', self::SALE_LOCATION_INDEXED_LANGUAGES_OPT, '', '');
		$langsFromDb = static::getLangsFromDb();

		if($langs == '')
			return array_keys($langsFromDb);

		$result = array();
		$langs = explode(':', $langs);

		if(is_array($langs))
		{
			foreach($langs as $lang)
			{
				if(isset($langsFromDb[$lang]))
					$result[] = $lang;
			}
		}

		return array_unique($result);
	}

	public static function setIndexedLanguages($langs = array())
	{
		if(is_array($langs) && !empty($langs))
			$langs = array_unique($langs);
		else
			$langs = array();

		$result = array();
		if(is_array($langs) && !empty($langs))
		{
			$langsFromDb = static::getLangsFromDb();

			foreach($langs as $lang)
			{
				if(isset($langsFromDb[$lang]))
					$result[] = $lang;
			}

			$result = array_unique($result);
		}

		Option::set('sale', self::SALE_LOCATION_INDEXED_LANGUAGES_OPT, implode(':', $result), '');
	}

	protected static function getLangsFromDb()
	{
		$langsFromDb = array();
		$res = \Bitrix\Main\Localization\LanguageTable::getList(array('select' => array('ID')));
		while($item = $res->fetch())
			$langsFromDb[$item['ID']] = true;

		return $langsFromDb;
	}

	protected static function getTypesFromDb()
	{
		$typesFromDb = array();
		$res = Location\TypeTable::getList(array('select' => array('ID')));
		while($item = $res->fetch())
			$typesFromDb[intval($item['ID'])] = true;

		return $typesFromDb;
	}

	/**
	 * 
	 * $parameters is an ORM`s getList compatible array of parameters
	 * 
	 * 
	 */
	public static function find($parameters, $behaviour = array('FALLBACK_TO_NOINDEX_ON_NOTFOUND' => true, 'USE_INDEX' => true, 'USE_ORM' => true))
	{
		/////////////////////////////////
		// parameter check and process

		Assert::expectArray($parameters, '$parameters');

		if(!is_array($behaviour))
			$behaviour = array();
		if(!isset($behaviour['FALLBACK_TO_NOINDEX_ON_NOTFOUND']))
			$behaviour['FALLBACK_TO_NOINDEX_ON_NOTFOUND'] = true;
		if(!isset($behaviour['USE_INDEX']))
			$behaviour['USE_INDEX'] = true;
		if(!isset($behaviour['USE_ORM']))
			$behaviour['USE_ORM'] = true;

		if(!isset($parameters['select']))
			$parameters['select'] = array('ID');

		Assert::expectArray($parameters['select'], '$parameters[select]');

		if(isset($parameters['filter']))
		{
			Assert::expectArray($parameters['filter'], '$parameters[filter]');

			// spikes, refactor later

			if(isset($parameters['filter']['PHRASE']) || isset($parameters['filter']['=PHRASE']))
			{
				$key = isset($parameters['filter']['PHRASE']) ? 'PHRASE' : '=PHRASE';

				$parameters['filter'][$key] = Assert::expectStringNotNull($parameters['filter'][$key], '$parameters[filter]['.$key.']');
				$parameters['filter'][$key] = str_replace('%', '', $parameters['filter'][$key]); // cannot pass '%' to like
			}

			if(isset($parameters['filter']['SITE_ID']) || isset($parameters['filter']['=SITE_ID']))
			{
				$key = isset($parameters['filter']['SITE_ID']) ? 'SITE_ID' : '=SITE_ID';
				$parameters['filter'][$key] = Assert::expectStringNotNull($parameters['filter'][$key], '$parameters[filter]['.$key.']'); // stronger here

				if(!Location\SiteLocationTable::checkLinkUsageAny($parameters['filter'][$key]))
					unset($parameters['filter'][$key]);
			}
		}

		if(isset($parameters['limit']))
			$parameters['limit'] = Assert::expectIntegerNonNegative($parameters['limit'], '$parameters[limit]');
		if(isset($parameters['offset']))
			$parameters['offset'] = Assert::expectIntegerNonNegative($parameters['offset'], '$parameters[offset]');

		/////////////////////////////////

		if(
			(isset($parameters['filter']['PHRASE']) || isset($parameters['filter']['SITE_ID']) || isset($parameters['filter']['=PHRASE']) || isset($parameters['filter']['=SITE_ID']))
			||
			$behaviour['USE_ORM'] === false
		)
		{
			if(static::checkIndexValid() && $behaviour['USE_INDEX'])
			{
				$result = static::findUsingIndex($parameters);
				if(!$behaviour['FALLBACK_TO_NOINDEX_ON_NOTFOUND'])
				{
					return $result;
				}
				else
				{
					$temporalBuffer = array();
					while($item = $result->fetch())
					{
						$temporalBuffer[] = $item;
					}

					if(empty($temporalBuffer))
					{
						return static::findNoIndex($parameters);
					}
					else
					{
						return new DB\ArrayResult($temporalBuffer);
					}
				}
			}
			else
			{
				return static::findNoIndex($parameters);
			}
		}
		else
		{
			return Location\LocationTable::getList($parameters);
		}
	}

	protected static function parseFilter($filter)
	{
		$parsed = array();

		if(is_array($filter))
		{
			foreach($filter as $field => $value)
			{
				$found = array();
				preg_match("#^(=?)(.+)#", $field, $found);

				if($found[1] <> '')
				{
					$op = $found[1];
				}
				else
				{
					$op = '=';
				}

				if(!isset(static::$allowedOperations[$op]))
					throw new Main\ArgumentException('Unknown modifier in the filter');

				$fieldParsed = $found[2];

				$parsed[$fieldParsed] = array(
					'OP' => $op <> ''? $op : '=',
					'VALUE' => $value
				);
			}
		}

		return $parsed;
	}

	protected static function findUsingIndex($parameters)
	{
		$query = array();

		$dbConnection = Main\HttpApplication::getConnection();
		$dbHelper = Main\HttpApplication::getConnection()->getSqlHelper();

		$filter = static::parseFilter($parameters['filter']);

		$filterByPhrase = isset($filter['PHRASE']) && mb_strlen($filter['PHRASE']['VALUE']);

		if($filterByPhrase) // filter by phrase
		{
			$bounds = WordTable::getBoundsForPhrase($filter['PHRASE']['VALUE']);

			$firstBound = array_shift($bounds);
			$k = 0;
			foreach($bounds as $bound)
			{
				$query['JOIN'][] = " inner join ".ChainTable::getTableName()." A".$k." on A.LOCATION_ID = A".$k.".LOCATION_ID and (

					".($bound['INF'] == $bound['SUP']
						? " A".$k.".POSITION = '".$bound['INF']."'"
						: " A".$k.".POSITION >= '".$bound['INF']."' and A".$k.".POSITION <= '".$bound['SUP']."'"
					)."
				)";
				$k++;
			}

			$query['WHERE'][] = (
				$firstBound['INF'] == $firstBound['SUP']
				? " A.POSITION = '".$firstBound['INF']."'"
				: " A.POSITION >= '".$firstBound['INF']."' and A.POSITION <= '".$firstBound['SUP']."'"
			);

			$mainTableJoinCondition = 'A.LOCATION_ID';
		}
		else
		{
			$mainTableJoinCondition = 'L.ID';
		}

		// site link search
		if(mb_strlen($filter['SITE_ID']['VALUE']) && SiteLinkTable::checkTableExists())
		{
			$query['JOIN'][] = "inner join ".SiteLinkTable::getTableName()." SL on SL.LOCATION_ID = ".$mainTableJoinCondition." and SL.SITE_ID = '".$dbHelper->forSql($filter['SITE_ID']['VALUE'])."'";
		}

		// process filter and select statements
		// at least, we support here basic field selection and filtration + NAME.NAME and NAME.LANGUAGE_ID

		$map = Location\LocationTable::getMap();
		$nameRequired = false;
		$locationRequired = false;

		if(is_array($parameters['select']))
		{
			foreach($parameters['select'] as $alias => $field)
			{
				if($field == 'NAME.NAME' || $field == 'NAME.LANGUAGE_ID')
				{
					$nameRequired = true;
					continue;
				}

				if(
					!isset($map[$field]) || 
					!in_array($map[$field]['data_type'], array('integer', 'string', 'float', 'boolean')) ||
					isset($map[$field]['expression'])
				)
				{
					unset($parameters['select'][$alias]);
				}

				$locationRequired = true;
			}
		}

		foreach($filter as $field => $params)
		{
			if($field == 'NAME.NAME' || $field == 'NAME.LANGUAGE_ID')
			{
				$nameRequired = true;
				continue;
			}

			if(
				!isset($map[$field]) || 
				!in_array($map[$field]['data_type'], array('integer', 'string', 'float', 'boolean')) ||
				isset($map[$field]['expression'])
			)
			{
				unset($filter[$field]);
			}

			$locationRequired = true;
		}

		// data join, only if extended select specified

		if($locationRequired && $filterByPhrase)
			$query['JOIN'][] = "inner join ".Location\LocationTable::getTableName()." L on A.LOCATION_ID = L.ID";

		if($nameRequired)
			$query['JOIN'][] = "inner join ".Location\Name\LocationTable::getTableName()." NAME on NAME.LOCATION_ID = ".$mainTableJoinCondition; //  and N.LANGUAGE_ID = 'ru'

		// making select
		if(is_array($parameters['select']))
		{
			$select = array();
			foreach($parameters['select'] as $alias => $field)
			{
				if($field != 'NAME.NAME' && $field != 'NAME.LANGUAGE_ID')
					$field = 'L.'.$dbHelper->forSql($field);

				if((string) $alias === (string) intval($alias))
					$select[] = $field;
				else
					$select[] = $field.' as '.$dbHelper->forSql($alias);
			}

			$sqlSelect = implode(', ', $select);
		}
		else
			$sqlSelect = $mainTableJoinCondition.' as ID';

		// making filter
		foreach($filter as $field => $params)
		{
			if($field != 'NAME.NAME' && $field != 'NAME.LANGUAGE_ID')
				$field = 'L.'.$dbHelper->forSql($field);

			$values = $params['VALUE'];

			if(!is_array($values))
				$values = array($values);

			foreach($values as $value)
				$query['WHERE'][] = $field.' '.$params['OP']." '".$dbHelper->forSql($value)."'";
		}

		if($filterByPhrase)
		{
			$sql = "
				select ".($dbConnection->getType() != 'mysql' ? '' : 'distinct')/*fix this in more clever way later*/." 
					".$sqlSelect.(\Bitrix\Sale\Location\DB\Helper::needSelectFieldsInOrderByWhenDistinct() ? ', A.RELEVANCY' : '')."

				from ".ChainTable::getTableName()." A

					".implode(' ', $query['JOIN'])."

				".(count($query['WHERE']) ? 'where ' : '').implode(' and ', $query['WHERE'])."

				order by A.RELEVANCY asc
			";
		}
		else
		{
			$sql = "

				select 
					".$sqlSelect."

				from ".Location\LocationTable::getTableName()." L

					".implode(' ', $query['JOIN'])."

				".(count($query['WHERE']) ? 'where ' : '').implode(' and ', $query['WHERE'])."
			";
		}

		$offset = intval($parameters['offset']);
		$limit = intval($parameters['limit']);

		if($limit)
			$sql = $dbHelper->getTopSql($sql, $limit, $offset);

		$res = $dbConnection->query($sql);

		return $res;
	}

	/**
	*
	*
	* @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($filter['SITE_ID']['VALUE'] <> '')
		{
			$filterSite = $dbHelper->forSql(mb_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($filter['PHRASE']['VALUE'] <> '')
		{
			$doFilterByName = true;

			$filterName = ToUpper($dbHelper->forSql($filter['PHRASE']['VALUE']));
		}

		if(is_array($filter['ID']['VALUE']))
		{
			$doFilterById = true;

			if(count($filter['ID']['VALUE']) === 1)
			{
				reset($filter['ID']['VALUE']);
				$filterId = (int)current($filter['ID']['VALUE']);
			}
			else
			{
				$filterId = $filter['ID']['VALUE'];
			}
		}
		elseif(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($filter['NAME.LANGUAGE_ID']['VALUE'] <> '')
		{
			$filterLang = $dbHelper->forSql(mb_substr($filter['NAME.LANGUAGE_ID']['VALUE'], 0, 2));
		}
		else
		{
			$filterLang = LANGUAGE_ID;
		}

		if (isset($filter['COUNTRY_ID']) && (int)$filter['COUNTRY_ID']['VALUE'] >= 0)
		{
			$doFilterByCountry = true;
			$filterCountryId = (int)$filter['COUNTRY_ID']['VALUE'];
		}

		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(/*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')) ||
				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 // alias is not a number
			{
				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}
						from {$locationTable} L 
							inner join {$locationNameTable} LN on L.ID = LN.LOCATION_ID
							inner join {$locationTypeTable} LT on L.TYPE_ID = LT.ID ".

							($doCountChildren ? "
								left join {$locationTable} LC on L.ID = LC.PARENT_ID
							" : "")." 

						%SITE_FILTER_CONDITION%

						where 

							%MAIN_FILTER_CONDITION%

							%GROUP_BY%
							";

		$where = array();

		if($doFilterByLang)
			$where[] = "LN.LANGUAGE_ID = '".$filterLang."'";

		if ($doFilterByCountry)
		{
			$where[] = "L.COUNTRY_ID = " . (int)$filterCountryId . " ";
		}

		if($doFilterByParent)
			$where[] = "L.PARENT_ID = '".$filterParentId."'";

		if($doFilterById)
		{
			if(is_array($filterId))
			{
				foreach($filterId as $idx => $id)
				{
					$filterId[$idx] = (int)$id;
				}

				$where[] = "L.ID IN (".implode(',', $filterId).")";
			}
			else
			{
				$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%', "

					inner join {$locationTable} L2 on L2.LEFT_MARGIN <= L.LEFT_MARGIN and L2.RIGHT_MARGIN >= L.RIGHT_MARGIN
					inner join {$locationSiteTable} LS2 on L2.ID = LS2.LOCATION_ID and LS2.LOCATION_TYPE = 'L' and LS2.SITE_ID = '{$filterSite}'

				", $mainSql);
			}
			if($hasGrpLinks)
			{
				$sql[] = str_replace('%SITE_FILTER_CONDITION%', "

					inner join {$locationTable} L2 on L2.LEFT_MARGIN <= L.LEFT_MARGIN and L2.RIGHT_MARGIN >= L.RIGHT_MARGIN
					inner join {$locationGroupTable} LG on LG.LOCATION_ID = L2.ID
					inner join {$locationSiteTable} LS2 on LG.LOCATION_GROUP_ID = LS2.LOCATION_ID and LS2.LOCATION_TYPE = 'G' and LS2.SITE_ID = '{$filterSite}'

				", $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;
		}
	}
}