Bitrix-D7 23.9
 
Загрузка...
Поиск...
Не найдено
migrate.php
1<?php
2
3namespace Bitrix\Sale\Location\Migration;
4
5use Bitrix\Main;
6use Bitrix\Main\Config;
7use Bitrix\Sale\Delivery;
8use Bitrix\Sale;
9use Bitrix\Main\Localization\Loc;
10
11use Bitrix\Sale\Location;
12use Bitrix\Sale\Location\DB\Helper;
13use Bitrix\Sale\Location\DB\BlockInserter;
14
15include_once($_SERVER["DOCUMENT_ROOT"].BX_ROOT."/modules/main/classes/general/update_class.php");
16
17Loc::loadMessages(__FILE__);
18
19class CUpdaterLocationPro extends \CUpdater implements \Serializable
20{
21 // old tables that will be reused
22 const TABLE_LOCATION = 'b_sale_location';
23 const TABLE_LOCATION_GROUP = 'b_sale_location_group';
24 const TABLE_LOCATION2GROUP = 'b_sale_location2location_group';
25 const TABLE_DELIVERY2LOCATION = 'b_sale_delivery2location';
26 const TABLE_TAX2LOCATION = 'b_sale_tax2location';
27 const TABLE_LOCATION_GROUP_NAME = 'b_sale_location_group_lang';
28
29 // new tables to be created
30 const TABLE_LOCATION_NAME = 'b_sale_loc_name';
31 const TABLE_LOCATION_EXTERNAL = 'b_sale_loc_ext';
32 const TABLE_LOCATION_EXTERNAL_SERVICE = 'b_sale_loc_ext_srv';
33 const TABLE_LOCATION_TYPE = 'b_sale_loc_type';
34 const TABLE_LOCATION_TYPE_NAME = 'b_sale_loc_type_name';
35 const TABLE_LOCATION2SITE = 'b_sale_loc_2site'; // ?
36
37 // obsolete tables to get data from
38 const TABLE_LOCATION_ZIP = 'b_sale_location_zip';
39 const TABLE_LOCATION_COUNTRY_NAME = 'b_sale_location_country_lang';
40 const TABLE_LOCATION_REGION_NAME = 'b_sale_location_region_lang';
41 const TABLE_LOCATION_CITY_NAME = 'b_sale_location_city_lang';
42
43 // temporal tables
44 const TABLE_TEMP_TREE = 'b_sale_location_temp_tree';
45 const TABLE_LEGACY_RELATIONS = 'b_sale_loc_legacy';
46
47 const MODULE_ID = 'sale';
48
49 protected $data = array();
50
51 // to CUpdater ?
52 const DB_TYPE_MYSQL = 'MySQL';
53 const DB_TYPE_MSSQL = 'MSSQL';
54 const DB_TYPE_ORACLE = 'Oracle';
55
56 const DB_TYPE_MYSQL_LC = 'mysql';
57 const DB_TYPE_MSSQL_LC = 'mssql';
58 const DB_TYPE_ORACLE_LC = 'oracle';
59
60 public function __construct()
61 {
62 $this->Init($curPath = "", 'mysql', $updaterName = "", $curDir = "", self::MODULE_ID, "DB");
63 }
64
65 public function serialize()
66 {
67 return serialize($this->data);
68 }
69
70 public function unserialize($data)
71 {
72 $this->Init($curPath = "", 'mysql', $updaterName = "", $curDir = "", self::MODULE_ID, "DB");
73 $this->data = unserialize($data, ['allowed_classes' => false]);
74 }
75
76 public static function updateDBSchemaRestoreLegacyIndexes()
77 {
78 $dbConnection = \Bitrix\Main\HttpApplication::getConnection();
79
80 $agentName = '\Bitrix\Sale\Location\Migration\CUpdaterLocationPro::updateDBSchemaRestoreLegacyIndexes();';
81
82 if(!Helper::checkIndexNameExists('IX_B_SALE_LOC_EXT_LID_SID', 'b_sale_loc_ext'))
83 {
84 $dbConnection->query('create index IX_B_SALE_LOC_EXT_LID_SID on b_sale_loc_ext (LOCATION_ID, SERVICE_ID)');
85 return $agentName;
86 }
87
88 if(!Helper::checkIndexNameExists('IXS_LOCATION_COUNTRY_ID', 'b_sale_location'))
89 {
90 $dbConnection->query('create index IXS_LOCATION_COUNTRY_ID on b_sale_location (COUNTRY_ID)');
91 return $agentName;
92 }
93
94 if(!Helper::checkIndexNameExists('IXS_LOCATION_REGION_ID', 'b_sale_location'))
95 {
96 $dbConnection->query('create index IXS_LOCATION_REGION_ID on b_sale_location (REGION_ID)');
97 return $agentName;
98 }
99
100 if(!Helper::checkIndexNameExists('IXS_LOCATION_CITY_ID', 'b_sale_location'))
101 {
102 $dbConnection->query('create index IXS_LOCATION_CITY_ID on b_sale_location (CITY_ID)');
103 }
104
105 return false;
106 }
107
108 // only for module_updater
109 public static function updateDBSchemaRenameIndexes()
110 {
111 global $DB;
112
113 $updater = new \CUpdater();
114 $updater->Init($curPath = "", 'mysql', $updaterName = "", $curDir = "", "sale", "DB");
115
116 $locationTableExists = $updater->TableExists("b_sale_location");
117
118 if($locationTableExists) // module might not be installed, but tables may exist
119 {
120 // b_sale_location
121 if(static::checkIndexExistsByName('IX_SALE_LOCATION_CODE', 'b_sale_location'))
122 {
123 static::dropIndexByName('IX_SALE_LOCATION_CODE', 'b_sale_location');
124 $DB->query('create unique index IX_B_SALE_LOC_CODE on b_sale_location (CODE)');
125 }
126
127 if(static::checkIndexExistsByName('IX_SALE_LOCATION_MARGINS', 'b_sale_location'))
128 {
129 static::dropIndexByName('IX_SALE_LOCATION_MARGINS', 'b_sale_location');
130 $DB->query('create index IX_B_SALE_LOC_MARGINS on b_sale_location (LEFT_MARGIN, RIGHT_MARGIN)');
131 }
132
133 if(static::checkIndexExistsByName('IX_SALE_LOCATION_MARGINS_REV', 'b_sale_location'))
134 {
135 static::dropIndexByName('IX_SALE_LOCATION_MARGINS_REV', 'b_sale_location');
136 $DB->query('create index IX_B_SALE_LOC_MARGINS_REV on b_sale_location (RIGHT_MARGIN, LEFT_MARGIN)');
137 }
138
139 if(static::checkIndexExistsByName('IX_SALE_LOCATION_PARENT', 'b_sale_location'))
140 {
141 static::dropIndexByName('IX_SALE_LOCATION_PARENT', 'b_sale_location');
142 $DB->query('create index IX_B_SALE_LOC_PARENT on b_sale_location (PARENT_ID)');
143 }
144
145 if(static::checkIndexExistsByName('IX_SALE_LOCATION_DL', 'b_sale_location'))
146 {
147 static::dropIndexByName('IX_SALE_LOCATION_DL', 'b_sale_location');
148 $DB->query('create index IX_B_SALE_LOC_DL on b_sale_location (DEPTH_LEVEL)');
149 }
150
151 if(static::checkIndexExistsByName('IX_SALE_LOCATION_TYPE', 'b_sale_location'))
152 {
153 static::dropIndexByName('IX_SALE_LOCATION_TYPE', 'b_sale_location');
154 $DB->query('create index IX_B_SALE_LOC_TYPE on b_sale_location (TYPE_ID)');
155 }
156
157 // b_sale_loc_name
158 if(static::checkIndexExistsByName('IX_SALE_L_NAME_NAME_UPPER', 'b_sale_loc_name'))
159 {
160 static::dropIndexByName('IX_SALE_L_NAME_NAME_UPPER', 'b_sale_loc_name');
161 $DB->query('create index IX_B_SALE_LOC_NAME_NAME_U on b_sale_loc_name (NAME_UPPER)');
162 }
163
164 if(static::checkIndexExistsByName('IX_SALE_L_NAME_LID_LID', 'b_sale_loc_name'))
165 {
166 static::dropIndexByName('IX_SALE_L_NAME_LID_LID', 'b_sale_loc_name');
167 $DB->query('create index IX_B_SALE_LOC_NAME_LI_LI on b_sale_loc_name (LOCATION_ID, LANGUAGE_ID)');
168 }
169
170 // b_sale_loc_type_name
171 if(static::checkIndexExistsByName('IX_SALE_L_TYPE_NAME_TID_LID', 'b_sale_loc_type_name'))
172 {
173 static::dropIndexByName('IX_SALE_L_TYPE_NAME_TID_LID', 'b_sale_loc_type_name');
174 $DB->query('create index IX_B_SALE_LOC_TYPE_NAME_TI_LI on b_sale_loc_type_name (TYPE_ID, LANGUAGE_ID)');
175 }
176
177 // b_sale_location_group
178 if(static::checkIndexExistsByName('IX_SALE_LOCATION_GROUP_CODE', 'b_sale_location_group'))
179 {
180 static::dropIndexByName('IX_SALE_LOCATION_GROUP_CODE', 'b_sale_location_group');
181 $DB->query('create unique index IX_B_SALE_LOC_GROUP_CODE on b_sale_location_group (CODE)');
182 }
183 }
184 }
185
186 protected static function dropIndexByName($indexName, $tableName)
187 {
188 $dbConnection = Main\HttpApplication::getConnection();
189 $dbConnType = $dbConnection->getType();
190
191 if($dbConnType == self::DB_TYPE_MYSQL_LC)
192 $dbConnection->query("alter table {$tableName} drop index {$indexName}");
193 elseif($dbConnType == self::DB_TYPE_ORACLE_LC)
194 $dbConnection->query("drop index {$indexName}");
195 elseif($dbConnType == self::DB_TYPE_MSSQL_LC)
196 $dbConnection->query("drop index {$indexName} on {$tableName}");
197
198 return true;
199 }
200
201 protected static function checkIndexExistsByName($indexName, $tableName)
202 {
203 if(!mb_strlen($indexName) || !mb_strlen($tableName))
204 return false;
205
206 $dbConnection = Main\HttpApplication::getConnection();
207 $dbConnType = $dbConnection->getType();
208
209 if($dbConnType == self::DB_TYPE_MYSQL_LC)
210 $res = $dbConnection->query("show index from ".$tableName);
211 elseif($dbConnType == self::DB_TYPE_ORACLE_LC)
212 $res = $dbConnection->query("SELECT INDEX_NAME as Key_name FROM USER_IND_COLUMNS WHERE TABLE_NAME = '".ToUpper($tableName)."'");
213 elseif($dbConnType == self::DB_TYPE_MSSQL_LC)
214 {
215 $res = $dbConnection->query("SELECT si.name Key_name
216 FROM sysindexkeys s
217 INNER JOIN syscolumns c ON s.id = c.id AND s.colid = c.colid
218 INNER JOIN sysobjects o ON s.id = o.Id AND o.xtype = 'U'
219 LEFT JOIN sysindexes si ON si.indid = s.indid AND si.id = s.id
220 WHERE o.name = '".ToUpper($tableName)."'");
221 }
222
223 while($item = $res->fetch())
224 {
225 if (isset($item['Key_name']) && $item['Key_name'] === $indexName)
226 {
227 return true;
228 }
229 if (isset($item['KEY_NAME']) && $item['KEY_NAME'] === $indexName)
230 {
231 return true;
232 }
233 }
234
235 return false;
236 }
237
238 // function stands for the corresponding block in module_updater.php
239 public static function updateDBSchema()
240 {
241 global $DB;
242
243 $updater = new \CUpdater();
244 $updater->Init($curPath = "", 'mysql', $updaterName = "", $curDir = "", "sale", "DB");
245
246 // table existence check
247 $locationTableExists = $updater->TableExists("b_sale_location");
248
249 if($locationTableExists) // module might not be installed, but tables may exist
250 {
251 $locationGroupTableExists = $updater->TableExists("b_sale_location_group");
252 $locationGroupNameTableExists = $updater->TableExists("b_sale_location_group_lang");
253
254 $locationNameTableExists = $updater->TableExists("b_sale_loc_name");
255 $locationExternalServiceTableExists = $updater->TableExists("b_sale_loc_ext_srv");
256 $locationExternalTableExists = $updater->TableExists("b_sale_loc_ext");
257 $locationTypeTableExists = $updater->TableExists("b_sale_loc_type");
258 $locationTypeNameTableExists = $updater->TableExists("b_sale_loc_type_name");
259 $locationLoc2SiteTableExists = $updater->TableExists("b_sale_loc_2site");
260 $locationDefaul2SiteTableExists = $updater->TableExists("b_sale_loc_def2site");
261
262 $tax2LocationTableExists = $updater->TableExists("b_sale_tax2location");
263 $delivery2LocationTableExists = $updater->TableExists("b_sale_delivery2location");
264
265 // adding columns to B_SALE_LOCATION
266
267 // if CODE not exists, add it
268 if (!$DB->query("select CODE from b_sale_location WHERE 1=0", true))
269 {
270 $updater->query(array(
271 "MySQL" => "ALTER TABLE b_sale_location ADD CODE varchar(100) not null",
272 "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD CODE varchar(100) default '' NOT NULL", // OK
273 "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD CODE VARCHAR2(100 CHAR) default '' NOT NULL", //OK // oracle allows to add not-null column only with default specified
274 ));
275 }
276
277 // if CODE exists, copy values from ID and add index
278 if ($DB->query("select CODE from b_sale_location WHERE 1=0", true))
279 {
280 if (!$DB->IndexExists('b_sale_location', array('CODE')))
281 {
282 $DB->query("update b_sale_location set CODE = ID"); // OK: oracle, mssql
283 $DB->query("CREATE UNIQUE INDEX IX_B_SALE_LOC_CODE ON b_sale_location (CODE)"); // OK: oracle, mssql
284 }
285 }
286
287 // create LEFT_MARGIN
288 if (!$DB->query("select LEFT_MARGIN from b_sale_location WHERE 1=0", true))
289 {
290 $updater->query(array(
291 "MySQL" => "ALTER TABLE b_sale_location ADD LEFT_MARGIN int",
292 "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD LEFT_MARGIN int", // OK
293 "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD LEFT_MARGIN NUMBER(18)", // OK
294 ));
295 }
296
297 // create RIGHT_MARGIN
298 if (!$DB->query("select RIGHT_MARGIN from b_sale_location WHERE 1=0", true))
299 {
300 $updater->query(array(
301 "MySQL" => "ALTER TABLE b_sale_location ADD RIGHT_MARGIN int",
302 "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD RIGHT_MARGIN int", // OK
303 "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD RIGHT_MARGIN NUMBER(18)", // OK
304 ));
305 }
306
307 $lMarginExists = $DB->query("select LEFT_MARGIN from b_sale_location WHERE 1=0", true);
308 $rMarginExists = $DB->query("select RIGHT_MARGIN from b_sale_location WHERE 1=0", true);
309
310 // add indexes if margins exist, but indexes not
311 if($lMarginExists && $rMarginExists)
312 {
313 if (!$DB->IndexExists('b_sale_location', array('LEFT_MARGIN', 'RIGHT_MARGIN')))
314 {
315 $DB->query("CREATE INDEX IX_B_SALE_LOC_MARGINS ON b_sale_location (LEFT_MARGIN, RIGHT_MARGIN)"); // OK: oracle, mssql
316 }
317 if (!$DB->IndexExists('b_sale_location', array('RIGHT_MARGIN', 'LEFT_MARGIN')))
318 {
319 $DB->query("CREATE INDEX IX_B_SALE_LOC_MARGINS_REV ON b_sale_location (RIGHT_MARGIN, LEFT_MARGIN)"); // OK: oracle, mssql
320 }
321 }
322
323 // add PARENT_ID
324 if (!$DB->query("select PARENT_ID from b_sale_location WHERE 1=0", true))
325 {
326 $updater->query(array(
327 "MySQL" => "ALTER TABLE b_sale_location ADD PARENT_ID int DEFAULT '0'",
328 "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD PARENT_ID int DEFAULT '0'", // OK
329 "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD PARENT_ID NUMBER(18) DEFAULT '0'", // OK
330 ));
331 }
332
333 // add index, if not exist for PARENT_ID, that exists
334 if ($DB->query("select PARENT_ID from b_sale_location WHERE 1=0", true) && !$DB->IndexExists('b_sale_location', array('PARENT_ID')))
335 {
336 $DB->query('CREATE INDEX IX_B_SALE_LOC_PARENT ON b_sale_location (PARENT_ID)'); // OK: oracle, mssql
337 }
338
339 // add DEPTH_LEVEL
340 if (!$DB->query("select DEPTH_LEVEL from b_sale_location WHERE 1=0", true))
341 {
342 $updater->query(array(
343 "MySQL" => "ALTER TABLE b_sale_location ADD DEPTH_LEVEL int default '1'",
344 "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD DEPTH_LEVEL int DEFAULT '1'", // OK
345 "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD DEPTH_LEVEL NUMBER(18) DEFAULT '1'", // OK
346 ));
347 }
348
349 // add index, if not exist for DEPTH_LEVEL, that exists
350 if ($DB->query("select DEPTH_LEVEL from b_sale_location WHERE 1=0", true) && !$DB->IndexExists('b_sale_location', array('DEPTH_LEVEL')))
351 {
352 $DB->query("CREATE INDEX IX_B_SALE_LOC_DL ON b_sale_location (DEPTH_LEVEL)"); // OK: oracle, mssql
353 }
354
355 // add TYPE_ID
356 if (!$DB->query("select TYPE_ID from b_sale_location WHERE 1=0", true))
357 {
358 $updater->query(array(
359 "MySQL" => "ALTER TABLE b_sale_location ADD TYPE_ID int",
360 "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD TYPE_ID int", // OK
361 "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD TYPE_ID NUMBER(18)", // OK
362 ));
363 }
364
365 // add index, if not exist for TYPE_ID, that exists
366 if ($DB->query("select TYPE_ID from b_sale_location WHERE 1=0", true) && !$DB->IndexExists('b_sale_location', array('TYPE_ID')))
367 {
368 $DB->query("CREATE INDEX IX_B_SALE_LOC_TYPE ON b_sale_location (TYPE_ID)"); // OK: oracle, mssql
369 }
370
371 // add LATITUDE
372 if (!$DB->query("select LATITUDE from b_sale_location WHERE 1=0", true))
373 {
374 $updater->query(array(
375 "MySQL" => "ALTER TABLE b_sale_location ADD LATITUDE decimal(8,6)",
376 "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD LATITUDE decimal(8,6)", // OK
377 "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD LATITUDE NUMBER(8,6)", // OK
378 ));
379 }
380
381 // add LONGITUDE
382 if (!$DB->query("select LONGITUDE from b_sale_location WHERE 1=0", true))
383 {
384 $updater->query(array(
385 "MySQL" => "ALTER TABLE b_sale_location ADD LONGITUDE decimal(9,6)",
386 "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD LONGITUDE decimal(9,6)", // OK
387 "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD LONGITUDE NUMBER(9,6)", // OK
388 ));
389 }
390
391 // dropping not-nulls
392
393 $DB->query("ALTER TABLE b_sale_location MODIFY COUNTRY_ID int NULL");
394
395 // adding columns to B_SALE_LOCATION_GROUP
396
397 if($locationGroupTableExists)
398 {
399 if (!$DB->query("select CODE from b_sale_location_group WHERE 1=0", true))
400 {
401 $updater->query(array(
402 "MySQL" => "ALTER TABLE b_sale_location_group ADD CODE varchar(100) NOT NULL",
403 "MSSQL" => "ALTER TABLE B_SALE_LOCATION_GROUP ADD CODE varchar(100) default '' NOT NULL", // OK
404 "Oracle" => "ALTER TABLE B_SALE_LOCATION_GROUP ADD CODE VARCHAR2(100 CHAR) default '' NOT NULL", //OK // oracle allows to add not-null column only with default specified
405 ));
406 }
407
408 // if CODE exists, copy values from ID and add index
409 if ($DB->query("select CODE from b_sale_location_group WHERE 1=0", true))
410 {
411 if (!$DB->IndexExists('b_sale_location_group', array('CODE')))
412 {
413 $DB->query("update b_sale_location_group set CODE = ID"); // OK: oracle, mssql
414 $DB->query("CREATE UNIQUE INDEX IX_B_SALE_LOC_GROUP_CODE ON b_sale_location_group (CODE)"); // OK: oracle, mssql
415 }
416 }
417
418 }
419
420 if (!$locationNameTableExists)
421 {
422 $updater->query(array(
423 "MySQL" => "create table b_sale_loc_name (
424 ID int not null auto_increment,
425 LANGUAGE_ID char(2) not null,
426 LOCATION_ID int not null,
427 NAME varchar(100) not null,
428 NAME_UPPER varchar(100) not null,
429 SHORT_NAME varchar(100),
430
431 primary key (ID)
432 )",
433
434 "MSSQL" => "CREATE TABLE B_SALE_LOC_NAME (
435 ID int NOT NULL IDENTITY (1, 1),
436 LANGUAGE_ID char(2) NOT NULL,
437 LOCATION_ID int NOT NULL,
438 NAME varchar(100) NOT NULL,
439 NAME_UPPER varchar(100) NOT NULL,
440 SHORT_NAME varchar(100)
441
442 CONSTRAINT PK_B_SALE_LOC_NAME PRIMARY KEY (ID)
443 )", // OK
444
445 "Oracle" => "CREATE TABLE B_SALE_LOC_NAME(
446 ID NUMBER(18) NOT NULL,
447 LANGUAGE_ID CHAR(2 CHAR) NOT NULL,
448 LOCATION_ID NUMBER(18) NOT NULL,
449 NAME VARCHAR2(100 CHAR) NOT NULL,
450 NAME_UPPER VARCHAR2(100 CHAR) NOT NULL,
451 SHORT_NAME VARCHAR2(100 CHAR),
452
453 PRIMARY KEY (ID)
454 )", // OK
455 ));
456
457 $locationNameTableExists = true;
458 }
459
460 if ($locationNameTableExists)
461 {
462 if (!$DB->IndexExists('b_sale_loc_name', array('NAME_UPPER')))
463 {
464 $DB->query("CREATE INDEX IX_B_SALE_LOC_NAME_NAME_U ON b_sale_loc_name (NAME_UPPER)"); // OK: oracle, mssql
465 }
466
467 if (!$DB->IndexExists('b_sale_loc_name', array('LOCATION_ID', 'LANGUAGE_ID')))
468 {
469 $DB->query("CREATE INDEX IX_B_SALE_LOC_NAME_LI_LI ON b_sale_loc_name (LOCATION_ID, LANGUAGE_ID)"); // OK: oracle, mssql
470 }
471 }
472
473 if (!$locationExternalServiceTableExists)
474 {
475 $updater->query(array(
476 "MySQL" => "create table b_sale_loc_ext_srv(
477 ID int not null auto_increment,
478 CODE varchar(100) not null,
479
480 primary key (ID)
481 )",
482
483 "MSSQL" => "CREATE TABLE B_SALE_LOC_EXT_SRV(
484 ID int NOT NULL IDENTITY (1, 1),
485 CODE varchar(100) NOT NULL
486
487 CONSTRAINT PK_B_SALE_LOC_EXT_SRV PRIMARY KEY (ID)
488 )", // OK
489
490 "Oracle" => "CREATE TABLE B_SALE_LOC_EXT_SRV(
491 ID NUMBER(18) NOT NULL,
492 CODE VARCHAR2(100 CHAR) NOT NULL,
493
494 PRIMARY KEY (ID)
495 )", // OK
496 ));
497
498 $locationExternalServiceTableExists = true;
499 }
500
501 if (!$locationExternalTableExists)
502 {
503 $updater->query(array(
504 "MySQL" => "create table b_sale_loc_ext(
505 ID int not null auto_increment,
506 SERVICE_ID int not null,
507 LOCATION_ID int not null,
508 XML_ID varchar(100) not null,
509
510 primary key (ID)
511 )",
512
513 "MSSQL" => "CREATE TABLE B_SALE_LOC_EXT(
514 ID int NOT NULL IDENTITY (1, 1),
515 SERVICE_ID int NOT NULL,
516 LOCATION_ID int NOT NULL,
517 XML_ID varchar(100) NOT NULL
518
519 CONSTRAINT PK_B_SALE_LOC_EXT PRIMARY KEY (ID)
520 )", // OK
521
522 "Oracle" => "CREATE TABLE B_SALE_LOC_EXT(
523 ID NUMBER(18) NOT NULL,
524 SERVICE_ID NUMBER(18) NOT NULL,
525 LOCATION_ID NUMBER(18) NOT NULL,
526 XML_ID VARCHAR2(100 CHAR) NOT NULL,
527
528 PRIMARY KEY (ID)
529 )", // OK
530 ));
531
532 $locationExternalTableExists = true;
533 }
534
535 if ($locationExternalTableExists && !$DB->IndexExists('b_sale_loc_ext', array('LOCATION_ID', 'SERVICE_ID')))
536 {
537 $DB->query("CREATE INDEX IX_B_SALE_LOC_EXT_LID_SID ON b_sale_loc_ext (LOCATION_ID, SERVICE_ID)"); // OK: oracle, mssql
538 }
539
540 if (!$locationTypeTableExists)
541 {
542 $updater->query(array(
543 "MySQL" => "create table b_sale_loc_type(
544 ID int not null auto_increment,
545 CODE varchar(30) not null,
546 SORT int default '100',
547
548 primary key (ID)
549 )",
550
551 "MSSQL" => "CREATE TABLE B_SALE_LOC_TYPE(
552 ID int NOT NULL IDENTITY (1, 1),
553 CODE varchar(30) NOT NULL,
554 SORT int
555
556 CONSTRAINT PK_B_SALE_LOC_TYPE PRIMARY KEY (ID)
557 )", // OK
558
559 "Oracle" => "CREATE TABLE B_SALE_LOC_TYPE(
560 ID NUMBER(18) NOT NULL,
561 CODE VARCHAR2(30 CHAR) NOT NULL,
562 SORT NUMBER(18) DEFAULT '100',
563
564 PRIMARY KEY (ID)
565 )", // OK
566 ));
567
568 $updater->query(array(
569 "MSSQL" => "ALTER TABLE B_SALE_LOC_TYPE ADD CONSTRAINT DF_B_SALE_LOC_TYPE_SORT DEFAULT '100' FOR SORT", // OK
570 ));
571
572 $locationTypeTableExists = true;
573 }
574
575 if(!$locationTypeNameTableExists)
576 {
577 $updater->query(array(
578 "MySQL" => "create table b_sale_loc_type_name(
579 ID int not null auto_increment,
580 LANGUAGE_ID char(2) not null,
581 NAME varchar(100) not null,
582 TYPE_ID int not null,
583
584 primary key (ID)
585 )",
586
587 "MSSQL" => "CREATE TABLE B_SALE_LOC_TYPE_NAME(
588 ID int NOT NULL IDENTITY (1, 1),
589 LANGUAGE_ID char(2) NOT NULL,
590 NAME varchar(100) NOT NULL,
591 TYPE_ID int NOT NULL
592
593 CONSTRAINT PK_B_SALE_LOC_TYPE_NAME PRIMARY KEY (ID)
594 )", // OK
595
596 "Oracle" => "CREATE TABLE B_SALE_LOC_TYPE_NAME(
597 ID NUMBER(18) NOT NULL,
598 LANGUAGE_ID CHAR(2 CHAR) NOT NULL,
599 NAME VARCHAR2(100 CHAR) NOT NULL,
600 TYPE_ID NUMBER(18) NOT NULL,
601
602 PRIMARY KEY (ID)
603 )", // OK
604 ));
605
606 $locationTypeNameTableExists = true;
607 }
608
609 if ($locationTypeNameTableExists)
610 {
611 if (!$DB->IndexExists('b_sale_loc_type_name', array('TYPE_ID', 'LANGUAGE_ID')))
612 {
613 $DB->query('CREATE INDEX IX_B_SALE_LOC_TYPE_NAME_TI_LI ON b_sale_loc_type_name (TYPE_ID, LANGUAGE_ID)'); // OK: oracle, mssql
614 }
615 }
616
617 if (!$locationLoc2SiteTableExists)
618 {
619 $updater->query(array(
620 "MySQL" => "create table b_sale_loc_2site(
621 LOCATION_ID int not null,
622 SITE_ID char(2) not null,
623 LOCATION_TYPE char(1) not null default 'L',
624
625 primary key (SITE_ID, LOCATION_ID, LOCATION_TYPE)
626 )",
627
628 "MSSQL" => "CREATE TABLE B_SALE_LOC_2SITE(
629 LOCATION_ID int NOT NULL,
630 SITE_ID char(2) NOT NULL,
631 LOCATION_TYPE char(1) NOT NULL
632
633 CONSTRAINT PK_B_SALE_LOC_2SITE PRIMARY KEY (SITE_ID, LOCATION_ID, LOCATION_TYPE)
634 )", // OK
635
636 "Oracle" => "CREATE TABLE B_SALE_LOC_2SITE(
637 LOCATION_ID NUMBER(18) NOT NULL,
638 SITE_ID CHAR(2 CHAR) NOT NULL,
639 LOCATION_TYPE CHAR(1 CHAR) DEFAULT 'L' NOT NULL,
640
641 PRIMARY KEY (SITE_ID, LOCATION_ID, LOCATION_TYPE)
642 )", // OK
643 ));
644 $updater->query(array(
645 "MSSQL" => "ALTER TABLE B_SALE_LOC_2SITE ADD CONSTRAINT DF_B_SALE_LOC_2SITE DEFAULT 'L' FOR LOCATION_TYPE", // OK
646 ));
647 }
648
649 if (!$locationDefaul2SiteTableExists)
650 {
651 $updater->query(array(
652 "MySQL" => "create table b_sale_loc_def2site(
653 LOCATION_CODE varchar(100) not null,
654 SITE_ID char(2) not null,
655 SORT int default '100',
656
657 primary key (LOCATION_CODE, SITE_ID)
658 )",
659
660 "MSSQL" => "CREATE TABLE B_SALE_LOC_DEF2SITE(
661 LOCATION_CODE varchar(100) NOT NULL,
662 SITE_ID char(2) NOT NULL,
663 SORT int
664
665 CONSTRAINT PK_B_SALE_LOC_DEF2SITE PRIMARY KEY (LOCATION_CODE, SITE_ID)
666 )", // OK
667
668 "Oracle" => "CREATE TABLE B_SALE_LOC_DEF2SITE(
669 LOCATION_CODE VARCHAR2(100 CHAR) NOT NULL,
670 SITE_ID CHAR(2 CHAR) NOT NULL,
671 SORT NUMBER(18) DEFAULT '100',
672
673 PRIMARY KEY (LOCATION_CODE, SITE_ID)
674 )", // OK
675 ));
676 $updater->query(array(
677 "MSSQL" => "ALTER TABLE B_SALE_LOC_DEF2SITE ADD CONSTRAINT DF_B_SALE_LOC_DEF2SITE_SORT DEFAULT '100' FOR SORT",
678 ));
679 }
680
681 // move tax and delivery to the new relation field: code
682
683 if ($tax2LocationTableExists && $DB->query("select LOCATION_ID from b_sale_tax2location WHERE 1=0", true)) // OK: oracle, mssql
684 {
685 $DB->query('delete from b_sale_tax2location where LOCATION_ID is null'); // OK: oracle, mssql // useless records to be deleted
686
687 if (!$DB->query("select LOCATION_CODE from b_sale_tax2location WHERE 1=0", true))
688 {
689 $updater->query(array(
690 "MySQL" => "ALTER TABLE b_sale_tax2location ADD LOCATION_CODE varchar(100) NOT NULL",
691 "MSSQL" => "ALTER TABLE B_SALE_TAX2LOCATION ADD LOCATION_CODE varchar(100) default '' NOT NULL",
692 "Oracle" => "ALTER TABLE B_SALE_TAX2LOCATION ADD LOCATION_CODE VARCHAR2(100 CHAR) default '' NOT NULL", // OK // oracle allows to add not-null column only with default specified
693 ));
694 }
695
696 $DB->query('update b_sale_tax2location set LOCATION_CODE = LOCATION_ID'); // OK: oracle, mssql
697
698 $DB->query('ALTER TABLE b_sale_tax2location DROP PRIMARY KEY'); // OK: oracle
699
700 $DB->query('ALTER TABLE b_sale_tax2location DROP COLUMN LOCATION_ID'); // OK: oracle, mssql
701
702 $DB->query('ALTER TABLE b_sale_tax2location ADD CONSTRAINT PK_B_SALE_TAX2LOCATION PRIMARY KEY (TAX_RATE_ID, LOCATION_CODE, LOCATION_TYPE)'); // OK: oracle, mssql
703 }
704
705 if ($delivery2LocationTableExists && $DB->query("select LOCATION_ID from b_sale_delivery2location WHERE 1=0", true)) // OK: oracle
706 {
707 $DB->query('delete from b_sale_delivery2location where LOCATION_ID is null'); // OK: oracle, mssql // useless records to be deleted
708
709 if (!$DB->query("select LOCATION_CODE from b_sale_delivery2location WHERE 1=0", true))
710 {
711 $updater->query(array(
712 "MySQL" => "ALTER TABLE b_sale_delivery2location ADD LOCATION_CODE varchar(100) NOT NULL",
713 "MSSQL" => "ALTER TABLE B_SALE_DELIVERY2LOCATION ADD LOCATION_CODE varchar(100) default '' NOT NULL", // OK
714 "Oracle" => "ALTER TABLE B_SALE_DELIVERY2LOCATION ADD LOCATION_CODE VARCHAR2(100 CHAR) default '' NOT NULL", // OK // oracle allows to add not-null column only with default specified
715 ));
716 }
717
718 $DB->query('update b_sale_delivery2location set LOCATION_CODE = LOCATION_ID'); // OK: oracle, mssql
719
720 $DB->query('ALTER TABLE b_sale_delivery2location DROP PRIMARY KEY'); // OK: oracle
721
722 $DB->query('ALTER TABLE b_sale_delivery2location DROP COLUMN LOCATION_ID'); // OK: oracle, mssql
723
724 $DB->query('ALTER TABLE b_sale_delivery2location ADD CONSTRAINT PK_B_SALE_DELIVERY2LOCATION PRIMARY KEY (DELIVERY_ID, LOCATION_CODE, LOCATION_TYPE)'); // OK: oracle, mssql
725 }
726
727 if(\COption::GetOptionString('sale', 'sale_locationpro_migrated', '') != 'Y') // CSaleLocation::isLocationProMigrated()
728 {
729 \CAdminNotify::Add(
730 array(
731 "MESSAGE" => Loc::getMessage('SALE_LOCATION_MIGRATION_PLZ_MIGRATE_NOTIFIER', array(
732 '#ANCHOR_MIGRATE#' => '<a href="/bitrix/admin/sale_location_migration.php">',
733 '#ANCHOR_END#' => '</a>'
734 )),
735 "TAG" => "SALE_LOCATIONPRO_PLZ_MIGRATE",
736 "MODULE_ID" => "SALE",
737 "ENABLE_CLOSE" => "Y"
738 )
739 );
740 }
741 }
742 }
743
747
748 public function copyId2Code()
749 {
750 // in locations
751 $this->Query(array(
752 self::DB_TYPE_MYSQL => 'update '.self::TABLE_LOCATION.' set CODE = ID;',
753 self::DB_TYPE_MSSQL => 'update '.mb_strtoupper(self::TABLE_LOCATION).' set CODE = ID;',
754 self::DB_TYPE_ORACLE => 'update '.mb_strtoupper(self::TABLE_LOCATION).' set CODE = ID;'
755 ));
756
757 // in groups
758 $this->Query(array(
759 self::DB_TYPE_MYSQL => 'update '.self::TABLE_LOCATION_GROUP.' set CODE = ID;',
760 self::DB_TYPE_MSSQL => 'update '.mb_strtoupper(self::TABLE_LOCATION_GROUP).' set CODE = ID;',
761 self::DB_TYPE_ORACLE => 'update '.mb_strtoupper(self::TABLE_LOCATION_GROUP).' set CODE = ID;'
762 ));
763 }
764
765 public function copyZipCodes()
766 {
767 global $DB;
768
769 Helper::truncateTable(self::TABLE_LOCATION_EXTERNAL);
770
771 $zipServiceId = false;
772 $zip = Location\ExternalServiceTable::getList(array('filter' => array('=CODE' => 'ZIP')))->fetch();
773 if(intval($zip['ID']))
774 $zipServiceId = intval($zip['ID']);
775
776 if($zipServiceId === false)
777 {
778 $res = Location\ExternalServiceTable::add(array('CODE' => 'ZIP'));
779 if(!$res->isSuccess())
780 throw new Main\SystemException('Cannot add external system: '.implode(', ', $res->getErrors()), 0, __FILE__, __LINE__);
781
782 $zipServiceId = $res->getId();
783 }
784
785 if($this->TableExists(self::TABLE_LOCATION_ZIP))
786 {
787 $loc2External = new BlockInserter(array(
788 'entityName' => '\Bitrix\Sale\Location\ExternalTable',
789 'exactFields' => array('LOCATION_ID', 'XML_ID', 'SERVICE_ID'),
790 'parameters' => array(
791 //'autoIncrementFld' => 'ID',
792 'mtu' => 9999
793 )
794 ));
795
796 $res = $DB->query('select * from '.self::TABLE_LOCATION_ZIP);
797 while($item = $res->fetch())
798 {
799 $item['LOCATION_ID'] = trim($item['LOCATION_ID']);
800 $item['ZIP'] = trim($item['ZIP']);
801
802 if(mb_strlen($item['LOCATION_ID']) && mb_strlen($item['ZIP']))
803 {
804 $loc2External->insert(array(
805 'LOCATION_ID' => $item['LOCATION_ID'],
806 'XML_ID' => $item['ZIP'],
807 'SERVICE_ID' => $zipServiceId
808 ));
809 }
810 }
811 $loc2External->flush();
812 }
813 }
814
815 private function convertEntityLocationLinks($entityName)
816 {
818 $class = $entityName.'Table';
819 $typeField = $class::getTypeField();
820 $locationLinkField = $class::getLocationLinkField();
821 $linkField = $class::getLinkField();
822 $useGroups = $class::getUseGroups();
823
824 $res = $class::getList();
825 $links = array();
826
827 while($item = $res->fetch())
828 {
829 if($useGroups)
830 $links[$item[$linkField]][$item[$typeField]][] = $item[$locationLinkField];
831 else
832 $links[$item[$linkField]][$class::DB_LOCATION_FLAG][] = $item[$locationLinkField];
833 }
834
835 foreach($links as $entityId => $rels)
836 {
837 if(is_array($rels[$class::DB_LOCATION_FLAG]))
838 $rels[$class::DB_LOCATION_FLAG] = $class::normalizeLocationList($rels[$class::DB_LOCATION_FLAG]);
839
840 if(isset($rels[$class::DB_LOCATION_FLAG]) && (!is_array($rels[$class::DB_LOCATION_FLAG]) || empty($rels[$class::DB_LOCATION_FLAG])))
841 unset($rels[$class::DB_LOCATION_FLAG]);
842
843 if(isset($rels[$class::DB_GROUP_FLAG]) && (!is_array($rels[$class::DB_GROUP_FLAG]) || empty($rels[$class::DB_GROUP_FLAG])))
844 unset($rels[$class::DB_GROUP_FLAG]);
845
846 $class::resetMultipleForOwner($entityId, $rels);
847 }
848 }
849
850 public function convertGroupLocationLinks()
851 {
852 $this->convertEntityLocationLinks('\Bitrix\Sale\Location\GroupLocation');
853 }
854
855 public function convertDeliveryLocationLinks()
856 {
857 $this->convertEntityLocationLinks('\Bitrix\Sale\Delivery\DeliveryLocation');
858 }
859
860 public function convertTaxRateLocationLinks()
861 {
862 $this->convertEntityLocationLinks('\Bitrix\Sale\Tax\RateLocation');
863 }
864
865 public function convertSalesZones()
866 {
867 $siteList = \CSaleLocation::getSites();
868 $siteList[] = ''; // 'empty site' too
869
870 foreach($siteList as $siteId)
871 {
872 $countries = Sale\SalesZone::getCountriesIds($siteId);
873 $regions = Sale\SalesZone::getRegionsIds($siteId);
874 $cities = Sale\SalesZone::getCitiesIds($siteId);
875
876 if(empty($countries) && empty($regions) && empty($cities))
877 continue;
878
879 Sale\SalesZone::saveSelectedTypes(array(
880 'COUNTRY' => $countries,
881 'REGION' => $regions,
882 'CITY' => $cities
883 ), $siteId);
884 }
885 }
886
887 public function copyDefaultLocations()
888 {
889 $sRes = Main\SiteTable::getList();
890 $sites = array();
891 while($site = $sRes->fetch())
892 $sites[] = $site['LID'];
893
894 $existed = array();
895 $res = Location\DefaultSiteTable::getList();
896 while($item = $res->fetch())
897 $existed[$item['SITE_ID']][$item['LOCATION_CODE']] = true;
898
899 $res = \CSaleLocation::GetList(array(), array(
900 'LID' => 'en',
901 'LOC_DEFAULT' => 'Y'
902 ), false, false, array('ID'));
903
904 while($item = $res->fetch())
905 {
906 foreach($sites as $site)
907 {
908 if(isset($existed[$site][$item['ID']]))
909 continue;
910
911 $opRes = Location\DefaultSiteTable::add(array(
912 'SITE_ID' => $site,
913 'LOCATION_CODE' => $item['ID']
914 ));
915 if(!$opRes->isSuccess())
916 throw new Main\SystemException('Cannot add default location');
917 }
918 }
919 }
920
921 public static function createBaseTypes()
922 {
923 $types = array(
924 'COUNTRY' => array(
925 'CODE' => 'COUNTRY',
926 'SORT' => 100,
927 'DISPLAY_SORT' => 700,
928 'NAME' => array()
929 ),
930 'REGION' => array(
931 'CODE' => 'REGION',
932 'SORT' => 300,
933 'DISPLAY_SORT' => 500,
934 'NAME' => array()
935 ),
936 'CITY' => array(
937 'CODE' => 'CITY',
938 'SORT' => 600,
939 'DISPLAY_SORT' => 100,
940 'NAME' => array()
941 ),
942 );
943
944 $langs = array();
945 $res = \Bitrix\Main\Localization\LanguageTable::getList();
946 while($item = $res->Fetch())
947 {
948 $MESS = array();
949 @include($_SERVER['DOCUMENT_ROOT'].'/bitrix/modules/sale/lang/'.$item['LID'].'/lib/location/migration/migrate.php');
950
951 if(!empty($MESS))
952 {
953 $types['COUNTRY']['NAME'][$item['LID']]['NAME'] = $MESS['SALE_LOCATION_TYPE_COUNTRY'];
954 $types['REGION']['NAME'][$item['LID']]['NAME'] = $MESS['SALE_LOCATION_TYPE_REGION'];
955 $types['CITY']['NAME'][$item['LID']]['NAME'] = $MESS['SALE_LOCATION_TYPE_CITY'];
956 }
957
958 $langs[$item['LID']] = true;
959 }
960
961 $typeCode2Id = array();
962 $res = Location\TypeTable::getList(array('select' => array('ID', 'CODE')));
963 while($item = $res->Fetch())
964 $typeCode2Id[$item['CODE']] = $item['ID'];
965
966 foreach($types as $code => &$type)
967 {
968 foreach($langs as $lid => $f)
969 {
970 $type['NAME'][$lid] = \Bitrix\Sale\Location\Admin\NameHelper::getTranslatedName($type['NAME'], $lid);
971 }
972
973 if(!isset($typeCode2Id[$type['CODE']]))
974 {
975 $typeCode2Id[$type['CODE']] = Location\TypeTable::add($type);
976 }
977 else
978 {
979 // ensure it has all appropriate translations
980 // we can not use ::updateMultipleForOwner() here, because user may rename types manually
981 Location\Name\TypeTable::addAbsentForOwner($typeCode2Id[$type['CODE']], $type['NAME']);
982 }
983 }
984
985 return $typeCode2Id;
986 }
987
988 public function createTypes()
989 {
990 $this->data['TYPE'] = self::createBaseTypes();
991 }
992
993 public function convertTree()
994 {
995 $res = Location\Name\LocationTable::getList(array('select' => array('ID'), 'limit' => 1))->fetch();
996 if(!$res['ID']) // if we got smth in name table - this means we already have done this conversion in the past
997 {
998 $this->grabTree();
999 $this->convertCountries();
1000 $this->convertRegions();
1001 $this->convertCities();
1002
1003 $this->resort();
1004
1005 $this->insertTreeInfo();
1006 $this->insertNames();
1007 }
1008 }
1009
1010 public function resetLegacyPath()
1011 {
1012 Helper::dropTable(self::TABLE_LEGACY_RELATIONS);
1013
1014 $dbConnection = \Bitrix\Main\HttpApplication::getConnection();
1015 $dbConnection->query("create table ".self::TABLE_LEGACY_RELATIONS." (
1016 ID ".Helper::getSqlForDataType('int').",
1017 COUNTRY_ID ".Helper::getSqlForDataType('int').",
1018 REGION_ID ".Helper::getSqlForDataType('int').",
1019 CITY_ID ".Helper::getSqlForDataType('int')."
1020 )");
1021
1022 $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");
1023
1024 Location\LocationTable::resetLegacyPath();
1025 }
1026
1027 public function rollBack()
1028 {
1029 if(Helper::checkTableExists(self::TABLE_LEGACY_RELATIONS))
1030 {
1031 Helper::mergeTables(
1032 'b_sale_location',
1033 self::TABLE_LEGACY_RELATIONS,
1034 array(
1035 'COUNTRY_ID' => 'COUNTRY_ID',
1036 'REGION_ID' => 'REGION_ID',
1037 'CITY_ID' => 'CITY_ID',
1038 ),
1039 array('ID' => 'ID')
1040 );
1041 }
1042
1043 Helper::truncateTable(self::TABLE_LOCATION_NAME);
1044 Helper::truncateTable(self::TABLE_LOCATION_EXTERNAL);
1045
1046 \CSaleLocation::locationProSetRolledBack();
1047 }
1048
1049 // in this function we track dependences between countries, regions and cities
1050 private function grabTree()
1051 {
1052 $this->data['LOC'] = array();
1053
1054 $auxIndex = array(
1055 'COUNTRY' => array(),
1056 'REGION' => array(),
1057 'CITY' => array()
1058 );
1059
1060 $this->data['LOC'] = array(
1061 'COUNTRY' => array(),
1062 'REGION' => array(),
1063 'CITY' => array()
1064 );
1065
1066 // level 1: country
1067 $res = \CSaleLocation::GetList(array(), array(
1068 '!COUNTRY_ID' => false,
1069 'REGION_ID' => false,
1070 'CITY_ID' => false,
1071 'LID' => 'en'
1072 ));
1073
1074 while($item = $res->Fetch())
1075 {
1076 if(!isset($this->data['LOC']['COUNTRY'][$item['ID']]))
1077 {
1078 $this->data['LOC']['COUNTRY'][$item['ID']] = array(
1079 'SUBJ_ID' => $item['COUNTRY_ID'],
1080 'PARENT_ID' => false,
1081 'PARENT_TYPE' => false
1082 );
1083 $auxIndex['COUNTRY'][$item['COUNTRY_ID']] = $item['ID'];
1084 }
1085 }
1086
1087 // level 2: country - region
1088 $res = \CSaleLocation::GetList(array(), array(
1089 //'!COUNTRY_ID' => false,
1090 '!REGION_ID' => false,
1091 'CITY_ID' => false,
1092 'LID' => 'en'
1093 ));
1094
1095 while($item = $res->Fetch())
1096 {
1097 if(!isset($this->data['LOC']['REGION'][$item['ID']]))
1098 {
1099 $this->data['LOC']['REGION'][$item['ID']] = array(
1100 'SUBJ_ID' => $item['REGION_ID'],
1101 'PARENT_ID' => $auxIndex['COUNTRY'][$item['COUNTRY_ID']],
1102 'PARENT_TYPE' => 'COUNTRY'
1103 );
1104 $auxIndex['REGION'][$item['REGION_ID']] = $item['ID'];
1105 }
1106 }
1107
1108 // level 2: country - city
1109 $res = \CSaleLocation::GetList(array(), array(
1110 //'!COUNTRY_ID' => false,
1111 'REGION_ID' => false,
1112 '!CITY_ID' => false,
1113 'LID' => 'en'
1114 ));
1115
1116 while($item = $res->Fetch())
1117 {
1118 if(!isset($this->data['LOC']['CITY'][$item['ID']]))
1119 $this->data['LOC']['CITY'][$item['ID']] = array(
1120 'SUBJ_ID' => $item['CITY_ID'],
1121 'PARENT_ID' => $auxIndex['COUNTRY'][$item['COUNTRY_ID']],
1122 'PARENT_TYPE' => 'COUNTRY'
1123 );
1124 }
1125
1126 // level 3: country - region - city
1127 $res = \CSaleLocation::GetList(array(), array(
1128 //'!COUNTRY_ID' => false,
1129 '!REGION_ID' => false,
1130 '!CITY_ID' => false,
1131 'LID' => 'en'
1132 ));
1133
1134 while($item = $res->Fetch())
1135 {
1136 if(!isset($this->data['LOC']['CITY'][$item['ID']]))
1137 $this->data['LOC']['CITY'][$item['ID']] = array(
1138 'SUBJ_ID' => $item['CITY_ID'],
1139 'PARENT_ID' => $auxIndex['REGION'][$item['REGION_ID']],
1140 'PARENT_TYPE' => 'REGION'
1141 );
1142 }
1143
1144 // language list
1145 $lang = new \CLanguage();
1146 $res = $lang->GetList();
1147 $this->data['LANG'] = array();
1148 while($item = $res->Fetch())
1149 $this->data['LANG'][] = $item['LID'];
1150
1151 // type list
1152 $res = Location\TypeTable::getList();
1153 while($item = $res->Fetch())
1154 $this->data['TYPE'][$item['CODE']] = $item['ID'];
1155 }
1156
1157 private function convertCountries()
1158 {
1159 global $DB;
1160
1161 // fetch name referece, separated with lang
1162 $langIndex = array();
1163 $res = $DB->query('select * from '.self::TABLE_LOCATION_COUNTRY_NAME);
1164 while($item = $res->Fetch())
1165 {
1166 $langIndex[$item['COUNTRY_ID']][$item['LID']] = array(
1167 'NAME' => $item['NAME'],
1168 'SHORT_NAME' => $item['SHORT_NAME']
1169 );
1170 }
1171
1172 if(is_array($this->data['LOC']['COUNTRY']))
1173 {
1174 foreach($this->data['LOC']['COUNTRY'] as $id => &$item)
1175 {
1176 $this->data['NAME'][$id] = $langIndex[$item['SUBJ_ID']];
1177 $this->data['TREE'][$id] = array(
1178 'PARENT_ID' => false,
1179 'TYPE_ID' => $this->data['TYPE']['COUNTRY'],
1180 'DEPTH_LEVEL' => 1
1181 );
1182 }
1183 }
1184 unset($this->data['LOC']['COUNTRY']);
1185 }
1186
1187 private function convertRegions()
1188 {
1189 global $DB;
1190
1191 // fetch name referece, separated with lang
1192 $langIndex = array();
1193 $res = $DB->query('select * from '.self::TABLE_LOCATION_REGION_NAME);
1194 while($item = $res->Fetch())
1195 {
1196 $langIndex[$item['REGION_ID']][$item['LID']] = array(
1197 'NAME' => $item['NAME'],
1198 'SHORT_NAME' => $item['SHORT_NAME']
1199 );
1200 }
1201
1202 if(is_array($this->data['LOC']['REGION']))
1203 {
1204 foreach($this->data['LOC']['REGION'] as $id => &$item)
1205 {
1206 $this->data['NAME'][$id] = $langIndex[$item['SUBJ_ID']];
1207 $this->data['TREE'][$id] = array(
1208 'PARENT_ID' => $item['PARENT_ID'],
1209 'TYPE_ID' => $this->data['TYPE']['REGION'],
1210 'DEPTH_LEVEL' => 2
1211 );
1212 }
1213 }
1214 unset($this->data['LOC']['REGION']);
1215 }
1216
1217 private function convertCities()
1218 {
1219 global $DB;
1220
1221 // fetch name referece, separated with lang
1222 $langIndex = array();
1223 $res = $DB->query('select * from '.self::TABLE_LOCATION_CITY_NAME);
1224 while($item = $res->Fetch())
1225 {
1226 $langIndex[$item['CITY_ID']][$item['LID']] = array(
1227 'NAME' => $item['NAME'],
1228 'SHORT_NAME' => $item['SHORT_NAME']
1229 );
1230 }
1231
1232 if(is_array($this->data['LOC']['CITY']))
1233 {
1234 foreach($this->data['LOC']['CITY'] as $id => &$item)
1235 {
1236 $this->data['NAME'][$id] = $langIndex[$item['SUBJ_ID']];
1237 $this->data['TREE'][$id] = array(
1238 'PARENT_ID' => $item['PARENT_ID'],
1239 'TYPE_ID' => $this->data['TYPE']['CITY'],
1240 'DEPTH_LEVEL' => $item['PARENT_TYPE'] == 'REGION' ? 3 : 2
1241 );
1242 }
1243 }
1244 unset($this->data['LOC']['CITY']);
1245 }
1246
1247 private function resort()
1248 {
1249 $edges = array();
1250 $nodes = array();
1251
1252 if(is_array($this->data['TREE']))
1253 {
1254 foreach($this->data['TREE'] as $id => $item)
1255 {
1256 $nodes[$id] = array();
1257
1258 if(!intval($item['PARENT_ID']))
1259 $edges['ROOT'][] = $id;
1260 else
1261 $edges[$item['PARENT_ID']][] = $id;
1262 }
1263 }
1264
1265 $this->walkTreeInDeep('ROOT', $edges, $nodes, 0);
1266 }
1267
1268 private function walkTreeInDeep($nodeId, $edges, &$nodes, $margin, $depth = 0)
1269 {
1270 $lMargin = $margin;
1271
1272 if(empty($edges[$nodeId]))
1273 $rMargin = $margin + 1;
1274 else
1275 {
1276 $offset = $margin + 1;
1277 foreach($edges[$nodeId] as $sNode)
1278 $offset = $this->walkTreeInDeep($sNode, $edges, $nodes, $offset, $depth + 1);
1279
1280 $rMargin = $offset;
1281 }
1282
1283 if($nodeId != 'ROOT')
1284 {
1285 // store margins
1286 $this->data['TREE'][$nodeId]['LEFT_MARGIN'] = $lMargin;
1287 $this->data['TREE'][$nodeId]['RIGHT_MARGIN'] = $rMargin;
1288 $this->data['TREE'][$nodeId]['DEPTH_LEVEL'] = $depth;
1289 }
1290
1291 return $rMargin + 1;
1292 }
1293
1294 private function insertTreeInfo()
1295 {
1296 // We make temporal table, place margins, parent and lang data into it, then perform an update of the old table from the temporal one.
1297
1298 $this->createTemporalTable(
1299 self::TABLE_TEMP_TREE,
1300 array(
1301 'ID' => array(
1302 'TYPE' => array(
1303 self::DB_TYPE_MYSQL => 'int',
1304 self::DB_TYPE_MSSQL => 'int',
1305 self::DB_TYPE_ORACLE => 'NUMBER(18)',
1306 )
1307 ),
1308 'PARENT_ID' => array(
1309 'TYPE' => array(
1310 self::DB_TYPE_MYSQL => 'int',
1311 self::DB_TYPE_MSSQL => 'int',
1312 self::DB_TYPE_ORACLE => 'NUMBER(18)',
1313 )
1314 ),
1315 'TYPE_ID' => array(
1316 'TYPE' => array(
1317 self::DB_TYPE_MYSQL => 'int',
1318 self::DB_TYPE_MSSQL => 'int',
1319 self::DB_TYPE_ORACLE => 'NUMBER(18)',
1320 )
1321 ),
1322 'DEPTH_LEVEL' => array(
1323 'TYPE' => array(
1324 self::DB_TYPE_MYSQL => 'int',
1325 self::DB_TYPE_MSSQL => 'int',
1326 self::DB_TYPE_ORACLE => 'NUMBER(18)',
1327 )
1328 ),
1329 'LEFT_MARGIN' => array(
1330 'TYPE' => array(
1331 self::DB_TYPE_MYSQL => 'int',
1332 self::DB_TYPE_MSSQL => 'int',
1333 self::DB_TYPE_ORACLE => 'NUMBER(18)',
1334 )
1335 ),
1336 'RIGHT_MARGIN' => array(
1337 'TYPE' => array(
1338 self::DB_TYPE_MYSQL => 'int',
1339 self::DB_TYPE_MSSQL => 'int',
1340 self::DB_TYPE_ORACLE => 'NUMBER(18)',
1341 )
1342 )
1343 )
1344 );
1345
1346 $handle = new BlockInserter(array(
1347 'tableName' => self::TABLE_TEMP_TREE,
1348 'exactFields' => array(
1349 'ID' => array('data_type' => 'integer'),
1350 'PARENT_ID' => array('data_type' => 'integer'),
1351 'TYPE_ID' => array('data_type' => 'integer'),
1352 'DEPTH_LEVEL' => array('data_type' => 'integer'),
1353 'LEFT_MARGIN' => array('data_type' => 'integer'),
1354 'RIGHT_MARGIN' => array('data_type' => 'integer'),
1355 ),
1356 'parameters' => array(
1357 'mtu' => 9999
1358 )
1359 ));
1360
1361 // fill temporal table
1362 if(is_array($this->data['TREE']))
1363 {
1364 foreach($this->data['TREE'] as $id => $node)
1365 {
1366 $handle->insert(array(
1367 'ID' => $id,
1368 'PARENT_ID' => $node['PARENT_ID'],
1369 'TYPE_ID' => $node['TYPE_ID'],
1370 'DEPTH_LEVEL' => $node['DEPTH_LEVEL'],
1371 'LEFT_MARGIN' => $node['LEFT_MARGIN'],
1372 'RIGHT_MARGIN' => $node['RIGHT_MARGIN'],
1373 ));
1374 }
1375 }
1376
1377 $handle->flush();
1378
1379 // merge temp table with location table
1380 Location\LocationTable::mergeRelationsFromTemporalTable(self::TABLE_TEMP_TREE, array('TYPE_ID', 'PARENT_ID'));
1381
1382 $this->dropTable(self::TABLE_TEMP_TREE);
1383 }
1384
1385 private function insertNames()
1386 {
1387 $handle = new BlockInserter(array(
1388 'entityName' => '\Bitrix\Sale\Location\Name\LocationTable',
1389 'exactFields' => array('LOCATION_ID', 'LANGUAGE_ID', 'NAME', 'SHORT_NAME', 'NAME_UPPER'),
1390 'parameters' => array(
1391 //'autoIncrementFld' => 'ID',
1392 'mtu' => 9999
1393 )
1394 ));
1395
1396 if(is_array($this->data['NAME']) && !empty($this->data['NAME']))
1397 {
1398 foreach($this->data['NAME'] as $id => $nameLang)
1399 {
1400 if(is_array($nameLang))
1401 {
1402 foreach($nameLang as $lang => $name)
1403 {
1404 $handle->insert(array(
1405 'LOCATION_ID' => $id,
1406 'LANGUAGE_ID' => $lang,
1407 'NAME' => $name['NAME'],
1408 'NAME_UPPER' => ToUpper($name['NAME']),
1409 'SHORT_NAME' => $name['SHORT_NAME']
1410 ));
1411 }
1412 }
1413 }
1414 }
1415
1416 $handle->flush();
1417 }
1418
1422
1423 protected function dropTable($tableName = '')
1424 {
1425 if($tableName == '')
1426 return false;
1427
1428 global $DB;
1429
1430 if($this->TableExists($tableName))
1431 $DB->query('drop table '.$DB->ForSql($tableName));
1432
1433 return true;
1434 }
1435
1436 protected function createTemporalTable($tableName = '', $columns = array())
1437 {
1438 if($tableName == '')
1439 return false;
1440
1441 if($this->dropTable($tableName));
1442
1443 return $this->createTable($tableName, $columns);
1444 }
1445
1446 protected function createTable($tableName = '', $columns = array(), $constraints = array())
1447 {
1448 if(!mb_strlen($tableName) || !is_array($columns) || empty($columns) || $this->TableExists($tableName))
1449 return false;
1450
1451 global $DB;
1452
1453 $tableName = $DB->ForSql($tableName);
1454 $tableNameUC = mb_strtoupper($tableName);
1455
1456 // queries that should be called after table creation
1457 $afterTableCreate = array();
1458
1459 // column sqls separated by dbtype
1460 $columnsSql = array();
1461 foreach($columns as $colName => $colProps)
1462 if($col = self::prepareFieldSql($colProps, $afterTableCreate))
1463 $columnsSql[$colName] = $col;
1464
1465 // constraint sqls separated by dbtype
1466 $constSql = self::prepareConstraintSql($constraints);
1467
1468 $queries = array();
1469
1470 if($sql = self::prepareCreateTable($tableName, $columnsSql, $constSql, self::DB_TYPE_MYSQL))
1471 $queries[self::DB_TYPE_MYSQL] = $sql;
1472
1473 if($sql = self::prepareCreateTable($tableNameUC, $columnsSql, $constSql, self::DB_TYPE_MSSQL))
1474 $queries[self::DB_TYPE_MSSQL] = $sql;
1475
1476 if($sql = self::prepareCreateTable($tableNameUC, $columnsSql, $constSql, self::DB_TYPE_ORACLE))
1477 $queries[self::DB_TYPE_ORACLE] = $sql;
1478
1479 if(!empty($queries))
1480 $this->Query($queries);
1481
1482 foreach($afterTableCreate as $dbType => $queries)
1483 {
1484 foreach($queries as $query)
1485 {
1486 $this->Query(array(
1487 $dbType => str_replace('%TABLE_NAME%', self::DB_TYPE_MYSQL == $dbType ? $tableName : $tableNameUC, $query)
1488 ));
1489 }
1490 }
1491
1492 return true;
1493 }
1494
1495 protected function prepareCreateTable($tableName, $columnsSql, $constSql, $dbType)
1496 {
1497 $columnsSqlSpec = $this->prepareTableFields($columnsSql, $dbType);
1498 if(!empty($columnsSqlSpec))
1499 return 'create table '.$tableName.' ('.$columnsSqlSpec.(!empty($constSql[$dbType]) ? ', '.implode(', ', $constSql[$dbType]) : '').')';
1500
1501 return false;
1502 }
1503
1504 // might be some overhead
1505 protected function prepareConstraintSql($constraints)
1506 {
1507 global $DB;
1508
1509 $cSql = array();
1510 foreach($constraints as $cCode => $cVal)
1511 {
1512 if($cCode == 'PRIMARY')
1513 {
1514 if(is_array($cVal) || !empty($cVal))
1515 {
1516 foreach($cVal as &$fld)
1517 $fld = $DB->ForSql($fld);
1518
1519 $key = implode(', ', $cVal);
1520 }
1521 else
1522 $key = $DB->ForSql($cVal);
1523
1524 $pk = 'PRIMARY KEY ('.$key.')';
1525
1526 $cSql[self::DB_TYPE_MYSQL][] = $pk;
1527 $cSql[self::DB_TYPE_MSSQL][] = $pk;
1528 $cSql[self::DB_TYPE_ORACLE][] = $pk;
1529 }
1530 }
1531
1532 return $cSql;
1533 }
1534
1535 protected function prepareTableFields($columnsSql, $dbType)
1536 {
1537 $resSql = array();
1538 foreach($columnsSql as $colName => $sqls)
1539 if(isset($sqls[$dbType]))
1540 $resSql[] = $colName.' '.$sqls[$dbType];
1541
1542 return implode(', ', $resSql);
1543 }
1544
1545 protected function prepareFieldSql($field, &$afterCreate)
1546 {
1547 $prepared = array();
1548
1549 global $DB;
1550
1551 foreach($field['TYPE'] as $dbType => $fldType)
1552 {
1553 $prepared[$dbType] = $fldType;
1554
1555 if($field['PRIMARY'])
1556 $prepared[$dbType] .= ' primary key';
1557
1558 if($field['AUTO_INCREMENT'])
1559 {
1560 if($dbType == self::DB_TYPE_MYSQL)
1561 $prepared[$dbType] .= ' auto_increment';
1562 if($dbType == self::DB_TYPE_MSSQL)
1563 $prepared[$dbType] .= ' IDENTITY (1, 1)';
1564 if($dbType == self::DB_TYPE_ORACLE)
1565 {
1566 // create a sequence
1567 $afterCreate[self::DB_TYPE_ORACLE][] = 'CREATE SEQUENCE SQ_B_%TABLE_NAME%';
1568
1569 // then create a trigger that uses the sequence
1570 $afterCreate[self::DB_TYPE_ORACLE][] = 'CREATE OR REPLACE TRIGGER %TABLE_NAME%_insert
1571 BEFORE INSERT
1572 ON %TABLE_NAME%
1573 FOR EACH ROW
1574 BEGIN
1575 IF :NEW.ID IS NULL THEN
1576 SELECT SQ_%TABLE_NAME%.NEXTVAL INTO :NEW.ID FROM dual;
1577 END IF;
1578 END;';
1579 }
1580 }
1581
1582 if(isset($field['DEFAULT']))
1583 $prepared[$dbType] .= ' DEFAULT '.(empty($field['DEFAULT']) ? 'NULL' : "'".$DB->ForSql($field['DEFAULT'])."'");
1584
1585 if(isset($field['NULL']))
1586 $prepared[$dbType] .= ' '.($field['NULL'] ? '' : 'NOT ').'NULL';
1587
1588 }
1589
1590 return $prepared;
1591 }
1592
1593 public function TableExists($tableName)
1594 {
1595 if (!in_array("DATABASE", $this->callType))
1596 return False;
1597
1598 $tableName = preg_replace("/[^A-Za-z0-9%_]+/i", "", $tableName);
1599 $tableName = Trim($tableName);
1600
1601 if ($tableName == '')
1602 return False;
1603
1604 global $DB;
1605
1606 if($this->UsingMysql())
1607 {
1608 return $DB->query('select * from '.$DB->ForSql($tableName).' where 1=0', true);
1609 }
1610 else
1611 {
1612 $strSql = '';
1613 if($this->UsingOracle())
1614 $strSql = "SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE UPPER('".mb_strtoupper($DB->ForSql($tableName))."')";
1615 elseif($this->UsingMssql())
1616 $strSql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '".mb_strtoupper($DB->ForSql($tableName))."'";
1617
1618 return !!$DB->Query($strSql)->fetch();
1619 }
1620 }
1621
1622 protected function UsingMysql()
1623 {
1624 return $this->dbType == 'MYSQL';
1625 }
1626 protected function UsingMssql()
1627 {
1628 return $this->dbType == 'MSSQL';
1629 }
1630 protected function UsingOracle()
1631 {
1632 return $this->dbType == 'ORACLE';
1633 }
1634}
Bitrix\Main\Localization\Loc\loadMessages
static loadMessages($file)
Definition loc.php:64
Bitrix\Main\Localization\Loc\getMessage
static getMessage($code, $replace=null, $language=null)
Definition loc.php:29
Bitrix\Sale\Location\Migration\CUpdaterLocationPro\createTable
createTable($tableName='', $columns=array(), $constraints=array())
Definition migrate.php:1446
Bitrix\Sale\Location\Migration\CUpdaterLocationPro\prepareCreateTable
prepareCreateTable($tableName, $columnsSql, $constSql, $dbType)
Definition migrate.php:1495
Bitrix\Sale\Location\Migration\CUpdaterLocationPro\dropIndexByName
static dropIndexByName($indexName, $tableName)
Definition migrate.php:186
Bitrix\Sale\Location\Migration\CUpdaterLocationPro\createTemporalTable
createTemporalTable($tableName='', $columns=array())
Definition migrate.php:1436
Bitrix\Sale\Location\Migration\CUpdaterLocationPro\checkIndexExistsByName
static checkIndexExistsByName($indexName, $tableName)
Definition migrate.php:201