public static function cleanUpData()
{
Helper::dropTable(static::getTableName());
// ORACLE: OK, MSSQL: OK
$sql = "create table " . static::getTableName() . " \n\t\t\t(\n\t\t\t\tLOCATION_ID " . Helper::getSqlForDataType('int') . ",\n\t\t\t\tSITE_ID " . Helper::getSqlForDataType('char', 2) . ",\n\n\t\t\t\tprimary key (LOCATION_ID, SITE_ID)\n\t\t\t)";
Main\HttpApplication::getConnection()->query($sql);
}
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 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 resort($dontCareEvents = false)
{
$edges = array();
$nodes = array();
$res = parent::getList(array('select' => array('ID', 'PARENT_ID', 'LEFT_MARGIN', 'RIGHT_MARGIN')));
while ($item = $res->Fetch()) {
$nodes[$item['ID']] = array('LEFT_MARGIN' => $item['LEFT_MARGIN'], 'RIGHT_MARGIN' => $item['RIGHT_MARGIN']);
if (!intval($item['PARENT_ID'])) {
$edges['ROOT'][] = $item['ID'];
} else {
$edges[$item['PARENT_ID']][] = $item['ID'];
}
}
// walk tree in-deep to obtain correct margins
self::walkTreeInDeep('ROOT', $edges, $nodes, 0, 0, $dontCareEvents);
// now massively insert new values into the database, using a temporal table
$tabName = 'b_sale_location_temp_' . rand(99, 9999);
$entityTableName = static::getTableName();
$dbConnection = Main\HttpApplication::getConnection();
$dbConnection->query("create table " . $tabName . " (\n\t\t\tID " . Helper::getSqlForDataType('int') . ",\n\t\t\tLEFT_MARGIN " . Helper::getSqlForDataType('int') . ",\n\t\t\tRIGHT_MARGIN " . Helper::getSqlForDataType('int') . ",\n\t\t\tDEPTH_LEVEL " . Helper::getSqlForDataType('int') . "\n\t\t)");
$handle = new BlockInserter(array('tableName' => $tabName, 'exactFields' => array('ID' => array('data_type' => 'integer'), 'LEFT_MARGIN' => array('data_type' => 'integer'), 'RIGHT_MARGIN' => array('data_type' => 'integer'), 'DEPTH_LEVEL' => array('data_type' => 'integer')), 'parameters' => array('mtu' => self::BLOCK_INSERT_MTU)));
foreach ($nodes as $id => $node) {
$node['ID'] = $id;
$handle->insert($node);
}
$handle->flush();
// merge temp table with location table
Helper::mergeTables($entityTableName, $tabName, array('LEFT_MARGIN' => 'LEFT_MARGIN', 'RIGHT_MARGIN' => 'RIGHT_MARGIN', 'DEPTH_LEVEL' => 'DEPTH_LEVEL'), array('ID' => 'ID'));
$dbConnection->query("drop table {$tabName}");
}
public static function cleanUpData()
{
Helper::dropTable(static::getTableName());
Main\HttpApplication::getConnection()->query("create table " . static::getTableName() . " (\n\n\t\t\tLOCATION_ID " . Helper::getSqlForDataType('int') . ",\n\t\t\tRELEVANCY " . Helper::getSqlForDataType('int') . " default '0',\n\t\t\tPOSITION " . Helper::getSqlForDataType('int') . " default '0',\n\n\t\t\tprimary key (POSITION, LOCATION_ID)\n\n\t\t)");
}
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();
}
public static function cleanUpData()
{
$dbConnection = Main\HttpApplication::getConnection();
Helper::dropTable(static::getTableName());
$binary = ToLower($dbConnection->getType()) == 'mysql' ? 'binary' : '';
// http://bugs.mysql.com/bug.php?id=34096
// ORACE: OK, MSSQL: OK
Main\HttpApplication::getConnection()->query("create table " . static::getTableName() . " (\n\n\t\t\tID " . Helper::getSqlForDataType('int') . " not null " . Helper::getSqlForAutoIncrement() . " primary key,\n\t\t\tWORD " . Helper::getSqlForDataType('varchar', 50) . " " . $binary . " not null,\n\t\t\tPOSITION " . Helper::getSqlForDataType('int') . " default '0'\n\t\t)");
Helper::addAutoIncrement(static::getTableName());
// only for ORACLE
Helper::createIndex(static::getTableName(), 'TMP', array('WORD'), true);
Helper::dropTable(static::getTableNameWord2Location());
// ORACLE: OK, MSSQL: OK
Main\HttpApplication::getConnection()->query("create table " . static::getTableNameWord2Location() . " (\n\n\t\t\tLOCATION_ID " . Helper::getSqlForDataType('int') . " not null,\n\t\t\tWORD_ID " . Helper::getSqlForDataType('int') . " not null,\n\n\t\t\tprimary key (LOCATION_ID, WORD_ID)\n\t\t)");
Helper::dropTable(static::getTableNamePositionTemporal());
$dbConnection->query("create table " . static::getTableNamePositionTemporal() . " (\n\t\t\tWORD_ID " . Helper::getSqlForDataType('int') . " not null,\n\t\t\tPOSITION " . Helper::getSqlForDataType('int') . " default '0'\n\t\t)");
}
public static function addPropertyDefaultValueField($tableAlias = 'V', &$arFields)
{
$tableAlias = \Bitrix\Main\HttpApplication::getConnection()->getSqlHelper()->forSql($tableAlias);
// locations kept in CODEs, but must be shown as IDs
if (CSaleLocation::isLocationProMigrated()) {
$arFields['DEFAULT_VALUE'] = array("FIELD" => "\n\n\t\t\t\tCASE \n\n\t\t\t\t\tWHEN \n\t\t\t\t\t\tTYPE = '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 . ".DEFAULT_VALUE \n\t\t\t\tEND", "TYPE" => "string", "FROM" => "LEFT JOIN b_sale_location L ON (TYPE = 'LOCATION' AND " . $tableAlias . ".DEFAULT_VALUE IS NOT NULL AND " . $tableAlias . ".DEFAULT_VALUE = L.CODE)");
$arFields['DEFAULT_VALUE_ORIG'] = array("FIELD" => $tableAlias . ".DEFAULT_VALUE", "TYPE" => "string");
} else {
$arFields['DEFAULT_VALUE'] = array("FIELD" => $tableAlias . ".DEFAULT_VALUE", "TYPE" => "string");
}
}
