public static function search($words)
{
$dbConnection = Main\HttpApplication::getConnection();
$dbHelper = Main\HttpApplication::getConnection()->getSqlHelper();
$wordStatTableName = WordStatTable::getTableName();
$preparedLike = array();
foreach ($words as $word) {
$preparedLike[] = "%TABLE_NAME%.WORD like '" . $dbHelper->forSql($word) . "%'";
}
$preparedLike = implode(' or ', $preparedLike);
$sql = "\n\t\t\tselect C.*, WS_CITY.WORD as CWORD, WS_VILLAGE.WORD as VWORD, WS_STREET.WORD as SWORD from " . static::getTableName() . " C\n\n\t\t\t\tinner join b_sale_loc_word_stat WS_STREET on \n\n\t\t\t\t\t(\n\t\t\t\t\t\tWS_STREET.TYPE_ID = '7'\n\t\t\t\t\t\tand\n\t\t\t\t\t\tWS_STREET.LOCATION_ID = C.STREET_ID\n\t\t\t\t\t\tand\n\t\t\t\t\t\t(\n\t\t\t\t\t\t\t(" . str_replace(array('%TABLE_NAME%'), array('WS_STREET'), $preparedLike) . ")\n\t\t\t\t\t\t\tor\n\t\t\t\t\t\t\t(WS_STREET.LOCATION_ID = '0')\n\t\t\t\t\t\t)\n\t\t\t\t\t)\n\n\t\t\t\tinner join b_sale_loc_word_stat WS_VILLAGE on \n\n\t\t\t\t\t(\n\t\t\t\t\t\tWS_VILLAGE.TYPE_ID = '6'\n\t\t\t\t\t\tand\n\t\t\t\t\t\tWS_VILLAGE.LOCATION_ID = C.VILLAGE_ID\n\t\t\t\t\t\tand\n\t\t\t\t\t\t(\n\t\t\t\t\t\t\t(" . str_replace(array('%TABLE_NAME%'), array('WS_VILLAGE'), $preparedLike) . ")\n\t\t\t\t\t\t\tor\n\t\t\t\t\t\t\t(WS_VILLAGE.LOCATION_ID = '0')\n\t\t\t\t\t\t)\n\t\t\t\t\t)\n\n\t\t\t\tinner join b_sale_loc_word_stat WS_CITY on \n\n\t\t\t\t\t(\n\t\t\t\t\t\tWS_CITY.TYPE_ID = '3'\n\t\t\t\t\t\tand\n\t\t\t\t\t\tWS_CITY.LOCATION_ID = C.CITY_ID\n\t\t\t\t\t\tand\n\t\t\t\t\t\t(\n\t\t\t\t\t\t\t(" . str_replace(array('%TABLE_NAME%'), array('WS_CITY'), $preparedLike) . ")\n\t\t\t\t\t\t\tor\n\t\t\t\t\t\t\t(WS_CITY.LOCATION_ID = '0')\n\t\t\t\t\t\t)\n\t\t\t\t\t)\n\n\t\t\torder by C.TYPE_SORT desc\n\t\t\tlimit 5\n\t\t";
/*
$sql = "
select * from ".static::getTableName()." C
where
(
C.CITY_ID = 0 or C.CITY_ID in (
select LOCATION_ID from b_sale_loc_word_stat
where
TYPE_ID = 3
and
(".$preparedLike.")
)
)
and
(
C.VILLAGE_ID = 0 or C.VILLAGE_ID in (
select LOCATION_ID from b_sale_loc_word_stat
where
TYPE_ID = 6
and
(".$preparedLike.")
)
)
and
(
C.STREET_ID = 0 or C.STREET_ID in (
select LOCATION_ID from b_sale_loc_word_stat
where
TYPE_ID = 7
and
(".$preparedLike.")
)
)
order by C.TYPE_SORT desc
limit 5
";
*/
print '<pre>';
print_r($sql);
print '</pre>';
return $dbConnection->query($sql);
}
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 addPropertyValueField($tableAlias = 'V', &$arFields, &$arSelectFields)
{
$tableAlias = \Bitrix\Main\HttpApplication::getConnection()->getSqlHelper()->forSql($tableAlias);
// locations kept in CODEs, but must be shown as IDs
if (CSaleLocation::isLocationProMigrated()) {
$arSelectFields = array_merge(array('PROP_TYPE'), $arSelectFields);
// P.TYPE should be there and go above our join
$arFields['VALUE'] = array("FIELD" => "\n\t\t\t\tCASE\n\n\t\t\t\t\tWHEN\n\t\t\t\t\t\tP.TYPE = 'LOCATION'\n\t\t\t\t\tTHEN\n\t\t\t\t\t\tCAST(L.ID as " . \Bitrix\Sale\Location\DB\Helper::getSqlForDataType('char', 255) . ")\n\n\t\t\t\t\tELSE\n\t\t\t\t\t\t" . $tableAlias . ".VALUE\n\t\t\t\tEND\n\t\t\t", "TYPE" => "string", "FROM" => "LEFT JOIN b_sale_location L ON (P.TYPE = 'LOCATION' AND " . $tableAlias . ".VALUE IS NOT NULL AND " . $tableAlias . ".VALUE = L.CODE)");
$arFields['VALUE_ORIG'] = array("FIELD" => $tableAlias . ".VALUE", "TYPE" => "string");
} else {
$arFields['VALUE'] = array("FIELD" => $tableAlias . ".VALUE", "TYPE" => "string");
}
}
public static function dropIndexByName($indexName, $tableName)
{
$dbConnection = Main\HttpApplication::getConnection();
$dbHelper = $dbConnection->getSqlHelper();
$indexName = $dbHelper->forSql(trim($indexName));
$tableName = $dbHelper->forSql(trim($tableName));
if (!strlen($indexName) || !strlen($tableName)) {
return false;
}
if (!static::checkIndexNameExists($indexName, $tableName)) {
return false;
}
$dbConnection->query("drop index {$indexName} on {$tableName}");
return true;
}
protected function stageRebalanceWalkTree()
{
if (!isset($this->data['rebalance']['queue'])) {
$this->restoreIndexes('IX_B_SALE_LOC_PARENT');
$this->logMessage('initialize Queue');
$this->data['rebalance']['margin'] = -1;
$this->data['processed'] = 0;
$this->data['rebalance']['queue'] = array(array('I' => 'root', 'D' => 0));
$tableName = Location\LocationTable::getTableName();
$res = Main\HttpApplication::getConnection()->query("select count(*) as CNT from {$tableName}")->fetch();
$this->data['rebalance']['cnt'] = intval($res['CNT']);
}
$i = -1;
while (!empty($this->data['rebalance']['queue']) && $this->checkQuota()) {
$i++;
$node =& $this->data['rebalance']['queue'][0];
if (isset($node['L'])) {
// we have already been here
array_shift($this->data['rebalance']['queue']);
if ($node['I'] != 'root') {
$this->acceptRebalancedNode(array('I' => $node['I'], 'D' => $node['D'], 'L' => $node['L'], 'R' => ++$this->data['rebalance']['margin']));
} else {
$this->data['rebalance']['margin']++;
}
} else {
$a = $this->getCachedBundle($node['I']);
if (!empty($a)) {
// go deeper
$node['L'] = ++$this->data['rebalance']['margin'];
foreach ($a as $id) {
if ($this->checkNodeIsParent($id)) {
array_unshift($this->data['rebalance']['queue'], array('I' => $id, 'D' => $node['D'] + 1));
} else {
$this->acceptRebalancedNode(array('I' => $id, 'D' => $node['D'] + 1, 'L' => ++$this->data['rebalance']['margin'], 'R' => ++$this->data['rebalance']['margin']));
}
}
} else {
array_shift($this->data['rebalance']['queue']);
$this->acceptRebalancedNode(array('I' => $node['I'], 'D' => $node['D'], 'L' => ++$this->data['rebalance']['margin'], 'R' => ++$this->data['rebalance']['margin']));
}
}
}
$this->logMessage('Q size is ' . count($this->data['rebalance']['queue']) . ' already processed: ' . $this->data['processed'] . '/' . $this->data['rebalance']['cnt']);
$this->logMemoryUsage();
if (empty($this->data['rebalance']['queue'])) {
// last flush & then merge
$this->mergeRebalancedNodes();
$this->nextStage();
return;
}
$this->rebalanceInserter->flush();
$this->nextStep();
}
public static function checkLinkExists($id, $parentId, array $parameters = array('BIDIRECTIONAL' => false))
{
$parentColName = static::getPARENTIDColumnName();
$idColName = static::getIDColumnName();
//$directColName = static::getDIRECTColumnName();
$id = intval($id);
$parentId = intval($parentId);
if (!$id || !$parentId) {
return false;
// link to non-existed nodes does not exist
}
$item = HttpApplication::getConnection()->query("\n\t\t\tselect " . $idColName . " \n\t\t\t\tfrom \n\t\t\t\t\t" . static::getTableName() . " \n\t\t\t\twhere \n\t\t\t\t\t(\n\t\t\t\t\t\t" . $idColName . " = '" . $id . "' \n\t\t\t\t\t\tand " . $parentColName . " = '" . $parentId . "' \n\t\t\t\t\t)\n\t\t\t\t\t" . ($parameters['BIDIRECTIONAL'] ? "\n\n\t\t\t\t\tor\n\t\t\t\t\t(\n\t\t\t\t\t\t" . $idColName . " = '" . $parentId . "' \n\t\t\t\t\t\tand " . $parentColName . " = '" . $id . "' \n\t\t\t\t\t)\n\n\t\t\t\t\t" : "") . "\n\t\t\t")->fetch();
return is_array($item);
}
/**
* Removes all checklist's items for given template.
* This function is low-level, i.e. it disrespects any events\callbacks
*
* @param integer $templateId
* @throws \Bitrix\Main\ArgumentException
*/
public static function deleteByTemplateId($templateId)
{
$templateId = Assert::expectIntegerPositive($templateId, '$templateId');
\Bitrix\Main\HttpApplication::getConnection()->query("DELETE FROM " . static::getTableName() . " WHERE TEMPLATE_ID = '" . $templateId . "'");
}
public static function resetAutoIncrement($tableName, $startIndex = 1)
{
$startIndex = intval($startIndex);
if ($startIndex <= 0 || !strlen($tableName)) {
return false;
}
$dbConnection = Main\HttpApplication::getConnection();
$dbHelper = $dbConnection->getSqlHelper();
$dbName = $dbConnection->getDbName();
$tableName = $dbHelper->forSql(trim($tableName));
$dbConnection->query("DBCC CHECKIDENT('" . $dbName . ".dbo." . $tableName . "', RESEED, " . ($startIndex - 1) . ")");
return true;
}
public function create()
{
$dbConnection = Main\HttpApplication::getConnection();
$table = static::getTableName();
global $DB;
if (!$DB->query('select * from ' . $table . ' where 1=0', true)) {
$dbConnection->query("create table " . $table . " (\n\n\t\t\t\tID int not null auto_increment primary key,\n\n\t\t\t\tCODE varchar(100),\n\t\t\t\tPARENT_CODE varchar(100),\n\n\t\t\t\tSYS_CODE varchar(100),\n\n\t\t\t\tTYPE_CODE varchar(20),\n\t\t\t\tFIAS_TYPE varchar(10),\n\n\t\t\t\tNAME varchar(100) not null,\n\t\t\t\tNAME_UA varchar(100) not null,\n\t\t\t\tZIP varchar(10),\n\t\t\t\tZIP_TO varchar(10),\n\n\t\t\t\tLANGNAMES varchar(300),\n\t\t\t\tEXTERNALS varchar(200),\n\n\t\t\t\tLATITUDE varchar(30),\n\t\t\t\tLONGITUDE varchar(30),\n\n\t\t\t\tALTERNATE_COORDS varchar(100),\n\t\t\t\tBOUNDED_WITH varchar(100),\n\n\t\t\t\tSOURCE varchar(2) default 'U'\n\t\t\t)");
// SYS_CODE will be U_ + settlement id
$this->restoreIndexes();
}
}
protected function prepareSql($row)
{
if(!is_array($row) || empty($row))
return '';
$sql = array();
foreach($this->fldVector as $fld => $none)
{
$val = $row[$fld];
// only numeric and literal fields supported at the moment
if($this->tableMap[$fld]['data_type'] == 'integer')
$sql[] = intval($val);
else
$sql[] = "'".Main\HttpApplication::getConnection()->getSqlHelper()->forSql($val)."'";
}
return '('.implode(',', $sql).')';
}
public static function truncateTable($tableName)
{
$dbConnection = Main\HttpApplication::getConnection();
$tableName = $dbConnection->getSqlHelper()->forSql($tableName);
if ($dbConnection->isTableExists($tableName)) {
Main\HttpApplication::getConnection()->query('truncate table ' . $tableName);
}
}
public function initializeData()
{
$dbConnection = Main\HttpApplication::getConnection();
$res = Location\LocationTable::getList(array('select' => array('ID', 'TYPE_ID', 'DEPTH_LEVEL', 'SORT'), 'order' => array('LEFT_MARGIN' => 'asc'), 'limit' => self::STEP_SIZE, 'offset' => $this->procData['OFFSET']));
$this->procData['TYPE_SORT'] = $this->rarefact($this->procData['TYPE_SORT']);
$cnt = 0;
while ($item = $res->fetch()) {
// tmp!!!!
//$name = Location\Name\LocationTable::getList(array('select' => array('NAME'), 'filter' => array('=LOCATION_ID' => $item['ID'], '=LANGUAGE_ID' => 'ru')))->fetch();
if ($item['DEPTH_LEVEL'] < $this->procData['DEPTH']) {
$newPC = array();
foreach ($this->procData['PATH'] as $dl => $id) {
if ($dl >= $item['DEPTH_LEVEL']) {
break;
}
$newPC[$dl] = $id;
}
$this->procData['PATH'] = $newPC;
}
$this->procData['PATH'][$item['DEPTH_LEVEL']] = array('TYPE' => $item['TYPE_ID'], 'ID' => $item['ID']);
if (is_array($this->procData['ALLOWED_TYPES']) && in_array($item['TYPE_ID'], $this->procData['ALLOWED_TYPES'])) {
$data = array('LOCATION_ID' => $item['ID'], 'RELEVANCY' => $this->procData['TYPE_SORT'][$item['TYPE_ID']] + $item['SORT']);
$wordsAdded = array();
/*
_dump_r('############################');
_dump_r('LOCATION: '.$name['NAME']);
_dump_r('TYPE RELEVANCY: '.$data['RELEVANCY']);
_dump_r('PATH:');
_dump_r($this->procData['PATH']);
*/
$this->procData['DEPTH'] = $item['DEPTH_LEVEL'];
// pre-load missing words
$wordCount = 0;
foreach ($this->procData['PATH'] as &$pathItem) {
if (!isset($pathItem['WORDS'])) {
$sql = "\n\t\t\t\t\t\t\tselect WS.POSITION from " . WordTable::getTableNameWord2Location() . " WL\n\t\t\t\t\t\t\t\tinner join " . WordTable::getTableName() . " WS on WL.WORD_ID = WS.ID\n\t\t\t\t\t\t\twhere\n\t\t\t\t\t\t\t\tWL.LOCATION_ID = '" . intval($pathItem['ID']) . "'\n\t\t\t\t\t\t";
$wordRes = $dbConnection->query($sql);
$pathItem['WORDS'] = array();
while ($wordItem = $wordRes->fetch()) {
$pathItem['WORDS'][] = $wordItem['POSITION'];
}
$pathItem['WORDS'] = array_unique($pathItem['WORDS']);
}
$wordCount += count($pathItem['WORDS']);
}
// count words
//_dump_r('Words total: '.$wordCount);
$wOffset = 0;
foreach ($this->procData['PATH'] as &$pathItem) {
foreach ($pathItem['WORDS'] as $i => $position) {
$wordWeight = $wordCount - $wOffset;
$tmpData = $data;
$tmpData['RELEVANCY'] += $wordWeight;
//_dump_r(' Word relevancy: '.$data['RELEVANCY'].' ==>> '.$tmpData['RELEVANCY']);
if (!isset($wordsAdded[$position])) {
$this->indexInserter->insert(array_merge(array('POSITION' => $position), $tmpData));
$wordsAdded[$position] = true;
}
$wOffset++;
}
}
unset($pathItem);
}
$cnt++;
}
$this->indexInserter->flush();
$this->procData['OFFSET'] += self::STEP_SIZE;
return !$cnt;
}
public static function cleanUp()
{
Main\HttpApplication::getConnection()->query('truncate table ' . static::getTableName());
}
/**
*
*
* @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;
}
}
public static function search($words, $offset)
{
$dbConnection = Main\HttpApplication::getConnection();
$dbHelper = Main\HttpApplication::getConnection()->getSqlHelper();
$where = array();
foreach ($words as $word) {
$whereWord = array();
for ($k = 1; $k <= 10; $k++) {
$whereWord[] = "W_" . $k . " like '" . $dbHelper->forSql($word) . "%'";
}
$where[] = '(' . implode(' or ', $whereWord) . ')';
}
$sql = "\n\t\t\tselect SQL_NO_CACHE IX.LOCATION_ID as ID, L.CODE, IX.TYPE_ID, L.LEFT_MARGIN, L.RIGHT_MARGIN, N.NAME as NAME from " . static::getTableName() . " IX\n\n\t\t\t\tinner join b_sale_location L on IX.LOCATION_ID = L.ID\n\t\t\t\tinner join b_sale_loc_name N on IX.LOCATION_ID = N.LOCATION_ID and N.LANGUAGE_ID = 'ru'\n\n\t\t\t\twhere " . implode(' and ', $where) . "\n\n\t\t\torder by \n\t\t\t\tIX.SORT asc,\n\t\t\t\tIX.TYPE_SORT asc\n\t\t\tlimit 10\n\t\t\t" . (intval($offset) ? 'offset ' . intval($offset) : '') . "\n\t\t";
_dump_r($sql);
/*
print('<pre>');
print_r($sql);
print('</pre>');
*/
return $dbConnection->query($sql);
}
protected static function checkLinkExists($id, $parentId)
{
$parentColName = static::getPARENTIDColumnName();
$idColName = static::getIDColumnName();
$directColName = static::getDIRECTColumnName();
$id = intval($id);
$parentId = intval($parentId);
if (!$id || !$parentId) {
return false;
// link to non-existed nodes does not exist
}
$item = Main\HttpApplication::getConnection()->query("select " . $idColName . " from " . static::getTableName() . " where " . $idColName . " = '" . $id . "' and " . $parentColName . " = '" . $parentId . "' and " . $directColName . " = '1'")->fetch();
return is_array($item);
}
public static function processFilterForGetList($arFilter, $fieldMap = array(), $fieldProxy = array(), $query = null)
{
if(is_array($arFilter))
{
$filterFieldsClean = array();
$arFilterParsed = array();
$pseudoFields = array();
$fieldProxy = array_flip($fieldProxy);
$dbConnection = Main\HttpApplication::getConnection();
$dbHelper = $dbConnection->getSqlHelper();
// parse filter and stirp away unknown fields
$arFilter = array_change_key_case($arFilter, CASE_UPPER);
foreach($arFilter as $fld => $value)
{
$fld = trim($fld);
$fld = preg_replace(self::LEADING_TILDA_SEARCH_R, '', $fld);
$found = array();
preg_match(self::MODIFIER_SEARCH_R, $fld, $found);
$modifier = strlen($found[1]) ? $found[1] : '';
$fldClean = preg_replace(self::MODIFIER_SEARCH_R, '', $fld);
if(isset($fieldProxy[$fldClean]))
$fldClean = $fieldProxy[$fldClean];
if(!isset($fieldMap[$fldClean]))
continue;
$fldClean = $fieldMap[$fldClean];
if($modifier == '+') // ORM does not understand + modifier in a filter
{
if($query != null)
{
// $value could be array or scalar
if(is_array($value))
{
foreach($value as $i => $v)
{
$value[$i] = "'".$dbHelper->forSql($v)."'";
}
$value = 'in ('.implode(', ', $value).')';
}
else
$value = '= '.$dbHelper->forSql($value);
$query->registerRuntimeField('PLUS_'.$fldClean, array(
'data_type' => 'integer',
'expression' => array(
"case when ((%s ".$value.") or (%s is null) or (%s = 0)) then 1 else 0 end",
$fldClean,
$fldClean,
$fldClean
)
));
$arFilterParsed['=PLUS_'.$fldClean] = 1;
}
continue;
}
$arFilterParsed[$modifier.$fldClean] = $value;
$filterFieldsClean[] = $fldClean;
}
return array($arFilterParsed, $filterFieldsClean, $pseudoFields);
}
else
return array(false, array(), array());
}
/**
*
* Deletes the task from the sorting tree
* @param int $taskId
*/
public static function deleteByTaskId($taskId)
{
$taskId = intval($taskId);
if (!$taskId) {
return;
}
$rows = self::getList(array("filter" => array("TASK_ID" => $taskId)));
while ($row = $rows->fetch()) {
static::fixSiblings($row);
}
HttpApplication::getConnection()->query("delete from " . static::getTableName() . " where TASK_ID = " . $taskId);
}
public static function mergeRelationsFromTemporalTable($temporalTabName, $additinalFlds = array(), $fldMap = array())
{
$dbConnection = Main\HttpApplication::getConnection();
$dbHelper = $dbConnection->getSqlHelper();
$temporalTabName = Assert::expectStringNotNull($temporalTabName, false, 'Name of temporal table must be a non-zero length string');
$temporalTabName = $dbHelper->forSql($temporalTabName);
$entityTableName = static::getTableName();
if (!is_array($additinalFlds)) {
$additinalFlds = array();
}
$additinalFlds = array_merge(array('LEFT_MARGIN', 'RIGHT_MARGIN', 'DEPTH_LEVEL'), $additinalFlds);
$fldReplace = array();
foreach ($additinalFlds as &$fld) {
$fld = $dbHelper->forSql($fld);
$fldReplace[$fld] = is_array($fldMap) && isset($fldMap[$fld]) ? $dbHelper->forSql($fldMap[$fld]) : $fld;
}
$idReplace = is_array($fldMap) && isset($fldMap['ID']) ? $dbHelper->forSql($fldMap['ID']) : 'ID';
if ($dbConnection->getType() == 'mysql') {
$sql = 'update ' . $entityTableName . ', ' . $temporalTabName . ' set ';
$additFldCnt = count($additinalFlds);
for ($i = 0; $i < $additFldCnt; $i++) {
$sql .= $entityTableName . '.' . $additinalFlds[$i] . ' = ' . $temporalTabName . '.' . $fldReplace[$additinalFlds[$i]] . ($i == count($additinalFlds) - 1 ? '' : ', ');
}
$sql .= ' where ' . $entityTableName . '.ID = ' . $temporalTabName . '.' . $idReplace;
} elseif ($dbConnection->getType() == 'mssql') {
$sql = 'update ' . $entityTableName . ' set ';
$additFldCnt = count($additinalFlds);
for ($i = 0; $i < $additFldCnt; $i++) {
$sql .= $additinalFlds[$i] . ' = ' . $temporalTabName . '.' . $fldReplace[$additinalFlds[$i]] . ($i == count($additinalFlds) - 1 ? '' : ', ');
}
$sql .= ' from ' . $entityTableName . ' join ' . $temporalTabName . ' on ' . $entityTableName . '.ID = ' . $temporalTabName . '.' . $idReplace;
} elseif ($dbConnection->getType() == 'oracle') {
// update tab1 set (aa,bb) = (select aa,bb from tab2 where tab2.id = tab1.id)
$sql = 'update ' . $entityTableName . ' set (' . implode(', ', $additinalFlds) . ') = (select ' . implode(', ', $fldReplace) . ' from ' . $temporalTabName . ' where ' . $entityTableName . '.ID = ' . $temporalTabName . '.' . $idReplace . ')';
}
$dbConnection->query($sql);
}
/**
* A special paginator for UNION-like queries
*
* @param string $sql SQL select query to execute, wrapped in union
* @param integer $limit record limit to fetch (default: 0)
* @param integer $offset offset to start fetching records from (default: 0)
*
* @return Bitrix\Main\DB\Result
*/
protected static function queryPage($sql, $limit = 0, $offset = 0)
{
$artificialNav = false;
$limit = intval($limit);
$offset = intval($offset);
// todo: Luke, use Bitrix\Main\DB\Paginator here
$dbConnection = Main\HttpApplication::getConnection();
if ($limit) {
if ($dbConnection->getType() == 'mysql') {
$sql .= ' limit ' . ($offset ? $offset . ', ' : '') . $limit;
} else {
$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;
}
}
/**
* This method is for internal use only. It may be changed without any notification further, or even mystically disappear.
*
* @access private
*/
public static function deleteMultipleByParentRangeSql($sql)
{
if (!strlen($sql)) {
throw new Main\SystemException('Range sql is empty');
}
$dbConnection = Main\HttpApplication::getConnection();
$dbConnection->query('delete from ' . static::getTableName() . ' where ' . static::getReferenceFieldName() . ' in (' . $sql . ')');
}
/**
* Removes a task from favorites for all users. This function DOES NOT check permissions.
*
* @param integer Task id
* @param mixed[] Behaviour
*
* <li> LOW_LEVEL boolean If set to true, function will ignore all events and do a low-level db query
*
* @return \Bitrix\Main\Entity\DeleteResult
*/
public static function deleteByTaskId($taskId, $behaviour = array('LOW_LEVEL' => false))
{
$taskId = Assert::expectIntegerPositive($taskId, '$taskId');
if (!is_array($behaviour)) {
$behaviour = array();
}
if (!isset($behaviour['LOW_LEVEL'])) {
$behaviour['LOW_LEVEL'] = false;
}
if ($behaviour['LOW_LEVEL']) {
HttpApplication::getConnection()->query("delete from " . static::getTableName() . " where TASK_ID = '" . intval($taskId) . "'");
$result = true;
} else {
$result = array();
$res = static::getList(array('filter' => array('=TASK_ID' => $taskId)));
while ($item = $res->fetch()) {
$result[] = static::delete(array('TASK_ID' => $item['TASK_ID'], 'USER_ID' => $item['USER_ID']));
}
}
return $result;
}
public static function initData($parameters = array())
{
static::cleanUp();
$sql = "\n\t\t\tinsert into b_sale_loc_search_sitelink \n\t\t\t\t(LOCATION_ID, SITE_ID) \n\t\t\tselect distinct LC.ID, LS.SITE_ID\n\t\t\t\tfrom b_sale_loc_2site LS\n\t\t\t\t\tinner join b_sale_location L on LS.LOCATION_ID = L.ID\n\t\t\t\t\tinner join b_sale_location LC on LC.LEFT_MARGIN >= L.LEFT_MARGIN and LC.RIGHT_MARGIN <= L.RIGHT_MARGIN\n\t\t";
Main\HttpApplication::getConnection()->query($sql);
}
public static function getBoundsByWord($word)
{
$word = trim($word);
$dbConnection = Main\HttpApplication::getConnection();
$sql = "select MIN(POSITION) as INF, MAX(POSITION) as SUP from " . static::getTableName() . " where WORD like '" . ToUpper($dbConnection->getSqlHelper()->forSql($word)) . "%'";
return $dbConnection->query($sql)->fetch();
}
public static function resetLegacyPath()
{
$dbConnection = Main\HttpApplication::getConnection();
$locTable = static::getTableName();
$types = array();
$res = TypeTable::getList(array('filter' => array('CODE' => array('COUNTRY', 'REGION', 'CITY')), 'select' => array('ID', 'CODE')));
while ($item = $res->fetch()) {
$types[$item['CODE']] = $item['ID'];
}
if (!empty($types)) {
if (!$dbConnection->isTableExists('b_sale_loc_rebind')) {
$dbConnection->query("create table b_sale_loc_rebind (TARGET_ID " . Helper::getSqlForDataType('int') . ", LOCATION_ID " . Helper::getSqlForDataType('int') . ")");
} else {
$dbConnection->query("truncate table b_sale_loc_rebind");
}
$sqlWhere = array();
foreach ($types as $code => $id) {
$sqlWhere[] = "'" . intval($id) . "'";
}
$dbConnection->query("update " . $locTable . " set COUNTRY_ID = NULL, REGION_ID = NULL, CITY_ID = NULL where TYPE_ID in (" . implode(', ', $sqlWhere) . ")");
if (intval($types['REGION']) && intval($types['COUNTRY'])) {
// countries for regions
$dbConnection->query("insert into b_sale_loc_rebind (TARGET_ID, LOCATION_ID) select A.ID as ONE, B.ID as TWO from " . $locTable . " A inner join " . $locTable . " B on A.TYPE_ID = '" . intval($types['REGION']) . "' and B.TYPE_ID = '" . intval($types['COUNTRY']) . "' and B.LEFT_MARGIN <= A.LEFT_MARGIN and B.RIGHT_MARGIN >= A.RIGHT_MARGIN");
Helper::mergeTables($locTable, 'b_sale_loc_rebind', array('COUNTRY_ID' => 'LOCATION_ID'), array('ID' => 'TARGET_ID'));
$dbConnection->query("truncate table b_sale_loc_rebind");
}
if (intval($types['REGION']) && intval($types['CITY'])) {
// regions for cities
$dbConnection->query("insert into b_sale_loc_rebind (TARGET_ID, LOCATION_ID) select A.ID as ONE, B.ID as TWO from " . $locTable . " A inner join " . $locTable . " B on A.TYPE_ID = '" . intval($types['CITY']) . "' and B.TYPE_ID = '" . intval($types['REGION']) . "' and B.LEFT_MARGIN <= A.LEFT_MARGIN and B.RIGHT_MARGIN >= A.RIGHT_MARGIN");
Helper::mergeTables($locTable, 'b_sale_loc_rebind', array('REGION_ID' => 'LOCATION_ID'), array('ID' => 'TARGET_ID'));
$dbConnection->query("truncate table b_sale_loc_rebind");
}
if (intval($types['COUNTRY']) && intval($types['CITY'])) {
// countries for cities
$dbConnection->query("insert into b_sale_loc_rebind (TARGET_ID, LOCATION_ID) select A.ID as ONE, B.ID as TWO from " . $locTable . " A inner join " . $locTable . " B on A.TYPE_ID = '" . intval($types['CITY']) . "' and B.TYPE_ID = '" . intval($types['COUNTRY']) . "' and B.LEFT_MARGIN <= A.LEFT_MARGIN and B.RIGHT_MARGIN >= A.RIGHT_MARGIN");
Helper::mergeTables($locTable, 'b_sale_loc_rebind', array('COUNTRY_ID' => 'LOCATION_ID'), array('ID' => 'TARGET_ID'));
}
Helper::dropTable('b_sale_loc_rebind');
if (intval($types['COUNTRY'])) {
$dbConnection->query("update " . $locTable . " set COUNTRY_ID = ID where TYPE_ID = '" . intval($types['COUNTRY']) . "'");
}
if (intval($types['REGION'])) {
$dbConnection->query("update " . $locTable . " set REGION_ID = ID where TYPE_ID = '" . intval($types['REGION']) . "'");
}
if (intval($types['CITY'])) {
$dbConnection->query("update " . $locTable . " set CITY_ID = ID where TYPE_ID = '" . intval($types['CITY']) . "'");
}
}
}
protected static function getNameOfParentOfType($item, $typeCode, $strLang = LANGUAGE_ID)
{
/*
$item = Location\LocationTable::getList(array('filter' => array(
'<=LEFT_MARGIN' => $item['LEFT_MARGIN'],
'>=RIGHT_MARGIN' => $item['RIGHT_MARGIN'],
'=TYPE.CODE' => $typeCode,
'=NAME.LANGUAGE_ID' => $strLang
), 'select' => array(
ToUpper($typeCode).'_NAME' => 'NAME.NAME',
ToUpper($typeCode).'_NAME_ORIG' => 'NAME.NAME',
ToUpper($typeCode).'_SHORT_NAME' => 'NAME.SHORT_NAME',
ToUpper($typeCode).'_NAME_LANG' => 'NAME.LANGUAGE_ID'
)))->fetch();
*/
global $DB;
$dbConnection = Main\HttpApplication::getConnection();
$dbHelper = $dbConnection->getSqlHelper();
$types = self::getTypes();
$typeCode = ToUpper($dbHelper->forSql($typeCode));
$strLang = substr($dbHelper->forSql($strLang), 0, 2);
$mappedTypes = array("'" . intval($types[$typeCode]) . "'");
if ($typeCode == 'CITY' && intval($types['VILLAGE'])) {
$mappedTypes[] = "'" . intval($types['VILLAGE']) . "'";
}
$query = "\n\t\t\tselect \n\t\t\t\tN.NAME as " . $typeCode . "_NAME,\n\t\t\t\tN.SHORT_NAME as " . $typeCode . "_SHORT_NAME,\n\t\t\t\tL.ID as " . $typeCode . "_ID\n\t\t\tfrom \n\t\t\t\tb_sale_loc_name N\n\t\t\t\tinner join b_sale_location L on \n\t\t\t\t\tN.LOCATION_ID = L.ID \n\t\t\t\t\tand\n\t\t\t\t\tN.LANGUAGE_ID = '" . $strLang . "'\n\t\t\t\t\tand\n\t\t\t\t\tL.LEFT_MARGIN <= '" . intval($item['LEFT_MARGIN']) . "'\n\t\t\t\t\tand\n\t\t\t\t\tL.RIGHT_MARGIN >= '" . intval($item['RIGHT_MARGIN']) . "'\n\t\t\t\t\tand\n\t\t\t\t\tL.TYPE_ID in (" . implode(', ', $mappedTypes) . ")\n\t\t";
$item = $DB->query($query)->fetch();
$item[$typeCode . '_NAME_ORIG'] = $item[$typeCode . '_NAME'];
$item[$typeCode . '_NAME_LANG'] = $item[$typeCode . '_NAME'];
if (!is_array($item)) {
return array();
}
return $item;
}
public static function switchIndexes($way = true)
{
Main\HttpApplication::getConnection()->query('alter table ' . static::getTableName() . ' ' . ($way ? 'enable' : 'disable') . ' keys');
}
public static function resetLegacyPath()
{
Helper::dropTable(self::TABLE_LEGACY_RELATIONS);
$dbConnection = \Bitrix\Main\HttpApplication::getConnection();
$dbConnection->query("create table " . self::TABLE_LEGACY_RELATIONS . " (\n\t\t\tID " . Helper::getSqlForDataType('int') . ",\n\t\t\tCOUNTRY_ID " . Helper::getSqlForDataType('int') . ",\n\t\t\tREGION_ID " . Helper::getSqlForDataType('int') . ",\n\t\t\tCITY_ID " . Helper::getSqlForDataType('int') . "\n\t\t)");
$dbConnection->query("insert into " . self::TABLE_LEGACY_RELATIONS . " (ID, COUNTRY_ID, REGION_ID, CITY_ID) select ID, COUNTRY_ID, REGION_ID, CITY_ID from b_sale_location");
Location\LocationTable::resetLegacyPath();
}
protected static function escapeArray($columns)
{
foreach ($columns as &$col) {
$col = Main\HttpApplication::getConnection()->getSqlHelper()->forSql($col);
}
return $columns;
}
public static function resetAutoIncrement($tableName, $startIndex = 1)
{
$startIndex = intval($startIndex);
if ($startIndex <= 0 || !strlen($tableName)) {
return false;
}
$dbConnection = Main\HttpApplication::getConnection();
$dbHelper = $dbConnection->getSqlHelper();
$tableName = $dbHelper->forSql(trim($tableName));
if (strlen($tableName) > 27) {
// too long
return false;
}
if ($sqName = Helper::checkSequenceExistsForTable($tableName)) {
$dbConnection->query('drop sequence ' . $sqName);
}
$dbConnection->query('create sequence ' . $sqName . ' start with ' . $startIndex . ' increment by 1 NOMAXVALUE NOCYCLE NOCACHE NOORDER');
$dbConnection->query("\n\t\t\tCREATE OR REPLACE TRIGGER " . $tableName . "_I\n\t\t\tBEFORE INSERT\n\t\t\tON " . $tableName . "\n\t\t\tFOR EACH ROW\n\t\t\tBEGIN\n\t\t\t\tIF :NEW.ID IS NULL THEN\n\t\t\t\t\tSELECT " . $sqName . ".NEXTVAL INTO :NEW.ID FROM dual;\n\t\t\t\tEND IF;\n\t\t\tEND;\n\t\t");
return true;
}
