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;
}
}
