Finder::findNoIndex

static function findNoIndex($parameters)
{
	$dbConnection = MainHttpApplication::getConnection();
	$dbHelper = $dbConnection->getSqlHelper();

	// tables
	$locationTable = LocationLocationTable::getTableName();
	$locationNameTable = LocationNameLocationTable::getTableName();
	$locationGroupTable = LocationGroupLocationTable::getTableName();
	$locationSiteTable = LocationSiteLocationTable::getTableName();
	$locationTypeTable = LocationTypeTable::getTableName();

	//////////////////////////////////
	// sql parameters prepare
	//////////////////////////////////

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

	$doFilterBySite = false;
	$hasLocLinks = false;
	$hasGrpLinks = false;
	if (($filter['SITE_ID']['VALUE'] ?? '') !== '')
	{
		$filterSite = $dbHelper->forSql(mb_substr($filter['SITE_ID']['VALUE'], 0, 2));

		$hasLocLinks = LocationSiteLocationTable::checkLinkUsage($filterSite, LocationSiteLocationTable::DB_LOCATION_FLAG);
		$hasGrpLinks = LocationSiteLocationTable::checkLinkUsage($filterSite, LocationSiteLocationTable::DB_GROUP_FLAG);
		$doFilterBySite = true;
	}

	$doFilterByName = false;
	$filterName = '';
	$phrase = (string)($filter['PHRASE']['VALUE'] ?? '');
	if ($phrase !== '')
	{
		$doFilterByName = true;
		$filterName = $dbHelper->forSql(mb_strtoupper($phrase));
	}

	$doFilterById = false;
	$filterId = null;
	if (isset($filter['ID']))
	{
		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 ((int)$filter['ID']['VALUE'])
		{
			$doFilterById = true;
			$filterId = (int)$filter['ID']['VALUE'];
		}
	}

	$doFilterByCode = false;
	$filterCode = '';
	$codeValue = (int)($filter['CODE']['VALUE'] ?? 0);
	if ($codeValue)
	{
		$doFilterByCode = true;
		$filterCode = $dbHelper->forSql((string)$codeValue);
	}
	unset($codeValue);

	if (($filter['NAME.LANGUAGE_ID']['VALUE'] ?? '') !== '')
	{
		$filterLang = $dbHelper->forSql(mb_substr((string)$filter['NAME.LANGUAGE_ID']['VALUE'], 0, 2));
	}
	else
	{
		$filterLang = LANGUAGE_ID;
	}

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

	$doFilterByParent = false;
	$filterParentId = 0;
	if (isset($filter['PARENT_ID']) && (int)$filter['PARENT_ID']['VALUE'] >= 0)
	{
		$doFilterByParent = true;
		$filterParentId = (int)$filter['PARENT_ID']['VALUE'];
	}

	$doFilterByType = false;
	$filterTypeId = 0;
	if ((int)($filter['TYPE_ID']['VALUE'] ?? 0))
	{
		$doFilterByType = true;
		$filterTypeId = (int)$filter['TYPE_ID']['VALUE'];
	}

	// filter select fields
	$parameters['select'] ??= [];
	if(!is_array($parameters['select']))
	{
		$parameters['select'] = [];
	}

	$doCountChildren = false;
	$map = LocationLocationTable::getMap();
	$nameAlias = false;
	$allowTypes = [
		'integer' => true,
		'string' => true,
		'float' => true,
		'boolean' => true,
	];
	foreach($parameters['select'] as $alias => $field)
	{
		if ($field === 'CHILD_CNT')
		{
			$doCountChildren = true;
		}

		if ($field === 'NAME.NAME')
		{
			$nameAlias = $alias;
		}

		$badField = false;
		if (!isset($map[$field]))
		{
			$badField = true;
		}
		elseif (
			!isset($allowTypes[$map[$field]['data_type']])
			|| isset($map[$field]['expression'])
		)
		{
			$badField = true;
		}
		if ($badField)
		{
			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 = [];
	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();

	$where[] = "LN.LANGUAGE_ID = '" . $filterLang . "'";

	if ($doFilterByCountry)
	{
		$where[] = "L.COUNTRY_ID = " . $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);

	$parameters['order'] ??= null;
	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 = (int)($parameters['offset'] ?? 0);
	$limit = (int)($parameters['limit'] ?? 0);

	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 DBArrayResult($result);
	}
	else
	{
		return $res;
	}
}