5ysMXRAJ

1DROP TABLE IF EXISTS `delivery_config_new`;
2CREATE TABLE `delivery_config_new` (
3	`delivery_config_id` INT(10) NOT NULL AUTO_INCREMENT,
4	`delivery_types_id` INT(10) NOT NULL COMMENT 'Ссылка на способ доставки',
5	`fias_id` INT(11) NOT NULL DEFAULT 0,
6	`price_default` INT(11) NOT NULL COMMENT 'Цена по умолчанию (для отчетов)',
7	`zone` VARCHAR(50) NOT NULL DEFAULT '0' COMMENT 'Зона для расчета трукоста',
8	`data` VARCHAR(50) NULL DEFAULT NULL,
9	`customers_id` INT(11) NOT NULL DEFAULT 0,
10	`date_start` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
11	`date_end` TIMESTAMP NOT NULL DEFAULT '2038-01-01 00:00:00',
12	`is_active` TINYINT(1) NOT NULL DEFAULT '0',
13	PRIMARY KEY (`delivery_config_id`),
14	INDEX `delivery_types_id_fias_id_idx` (`delivery_types_id`, `fias_id`)
15)
16COMMENT='Настройки доставки ТК'
17COLLATE='utf8_general_ci'
18ENGINE=InnoDB
19;
20
21DROP TABLE IF EXISTS `delivery_config_extra`;
22CREATE TABLE `delivery_config_extra` (
23	`delivery_types_id` INT(10) NOT NULL COMMENT 'Ссылка на способ доставки',
24	`fias_id` INT(11) NOT NULL DEFAULT 0,
25	`tc_city_code` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Код города в системе ТК',
26	PRIMARY KEY (`delivery_types_id`, `fias_id`),
27	INDEX `tc_city_code_idx` (`tc_city_code`)
28)
29COMMENT='Связь фиаса и города в системе ТК'
30COLLATE='utf8_general_ci'
31ENGINE=InnoDB
32;
33
34DROP TABLE IF EXISTS `delivery_ranges`;
35CREATE TABLE `delivery_ranges` (
36	`delivery_ranges_id` INT(10) NOT NULL AUTO_INCREMENT,
37	`object_id` INT(11) NOT NULL DEFAULT '0',
38	`entities_id` INT(11) NOT NULL DEFAULT '0',
39	`subtotal_from` DECIMAL(15,2) NOT NULL DEFAULT 0 COMMENT 'Стоимость товаров от',
40	`subtotal_to` DECIMAL(15,2) NOT NULL DEFAULT 0 COMMENT 'Стоимость товаров до',
41	`price` INT(11) NOT NULL DEFAULT '0',
42	INDEX `object_id_idx` (`object_id`),
43	INDEX `entities_id_idx` (`entities_id`),
44	PRIMARY KEY (`delivery_ranges_id`)
45)
46COMMENT='Диапазоны стоимостей доставки'
47COLLATE='utf8_general_ci'
48ENGINE=InnoDB
49;
50
51
52-- ############### Курьер DHL #################
53
54-- DHL, переносим отключенные версии без активных конфигов
55INSERT INTO delivery_config_new
56SELECT
57	null,
58	dc.company as delivery_types_id,
59	dc.fias_id,
60	dc.price_for_user as price_default,
61	dc.zone,
62	dc.`data`,
63	1 as customers_id,
64	'2005-01-01 00:00:00' as date_start,
65	'2038-01-01 00:00:00' as date_end,
66	'0' as is_active
67FROM
68	delivery_config dc
69JOIN (
70	SELECT
71		SUBSTRING_INDEX(group_concat(dc.id ORDER BY dc.id DESC), ',', 1) as maxId
72	FROM
73		delivery_config dc
74	WHERE
75		dc.company = 22 AND dc.is_active = 0
76	GROUP BY dc.fias_id
77) as t ON dc.id = t.maxId
78
79LEFT JOIN delivery_config as delivery_config_active ON dc.fias_id = delivery_config_active.fias_id AND delivery_config_active.is_active = 1 AND delivery_config_active.company = 22
80
81WHERE
82	dc.company = 22 AND dc.is_active = 0 AND delivery_config_active.id IS NULL;
83
84-- DHL, переносим активные конфиги
85INSERT INTO delivery_config_new
86SELECT
87	null,
88	dc.company as delivery_types_id,
89	dc.fias_id,
90	dc.price_for_user as price_default,
91	dc.zone,
92	dc.`data`,
93	1 as customers_id,
94	IF(ISNULL(date_start), '2005-01-01 00:00:00', date_end + INTERVAL 1 SECOND) as date_start,
95	'2038-01-01 00:00:00' as date_end,
96	'1' as is_active
97FROM
98	delivery_config dc
99LEFT JOIN
100	delivery_config_new dcn ON dc.fias_id = dcn.fias_id AND dc.company = dcn.delivery_types_id
101WHERE
102	dc.company = 22 AND dc.is_active = 1;
103
104-- DHL, диапазоны
105INSERT INTO delivery_ranges
106SELECT null, dcn.delivery_config_id, '107' AS entities_id, '0' AS subtotal_from, '100000000' AS subtotal_to, dcn.price_default AS price
107FROM delivery_config_new dcn
108WHERE dcn.delivery_types_id = 22;
109
110
111
112-- ############### Курьер Mamsy #################
113
114-- Курьер Mamsy, переносим отключенные версии без активных конфигов
115INSERT INTO delivery_config_new
116SELECT
117	null,
118	dc.company as delivery_types_id,
119	dc.fias_id,
120	dc.price_for_user as price_default,
121	dc.zone,
122	dc.`data`,
123	1 as customers_id,
124	'2005-01-01 00:00:00' as date_start,
125	'2038-01-01 00:00:00' as date_end,
126	'0' as is_active
127FROM
128	delivery_config dc
129JOIN (
130	SELECT
131		SUBSTRING_INDEX(group_concat(dc.id ORDER BY dc.id DESC), ',', 1) as maxId
132	FROM
133		delivery_config dc
134	WHERE
135		dc.company = 13 AND dc.is_active = 0
136	GROUP BY dc.fias_id
137) as t ON dc.id = t.maxId
138
139LEFT JOIN delivery_config as delivery_config_active ON dc.fias_id = delivery_config_active.fias_id AND delivery_config_active.is_active = 1 AND delivery_config_active.company = 13
140
141WHERE
142	dc.company = 13 AND dc.is_active = 0 AND delivery_config_active.id IS NULL;
143
144-- Курьер Mamsy, переносим активные конфиги
145INSERT INTO delivery_config_new
146SELECT
147	null,
148	dc.company as delivery_types_id,
149	dc.fias_id,
150	dc.price_for_user as price_default,
151	dc.zone,
152	dc.`data`,
153	1 as customers_id,
154	IF(ISNULL(date_start), '2005-01-01 00:00:00', date_end + INTERVAL 1 SECOND) as date_start,
155	'2038-01-01 00:00:00' as date_end,
156	'1' as is_active
157FROM
158	delivery_config dc
159LEFT JOIN
160	delivery_config_new dcn ON dc.fias_id = dcn.fias_id AND dc.company = dcn.delivery_types_id
161WHERE
162	dc.company = 13 AND dc.is_active = 1;
163
164-- Курьер Mamsy, диапазоны
165INSERT INTO delivery_ranges
166SELECT null, dcn.delivery_config_id, '107' AS entities_id, '0' AS subtotal_from, '100000000' AS subtotal_to, dcn.price_default AS price
167FROM delivery_config_new dcn
168WHERE dcn.delivery_types_id = 13;
169
170
171-- ############### Курьер SPSR #################
172
173-- Курьер SPSR, переносим отключенные версии без активных конфигов
174INSERT INTO delivery_config_new
175SELECT
176	null,
177	dc.company as delivery_types_id,
178	dc.fias_id,
179	dc.price_for_user as price_default,
180	dc.zone,
181	dc.`data`,
182	1 as customers_id,
183	'2005-01-01 00:00:00' as date_start,
184	'2038-01-01 00:00:00' as date_end,
185	'0' as is_active
186FROM
187	delivery_config dc
188JOIN (
189	SELECT
190		SUBSTRING_INDEX(group_concat(dc.id ORDER BY dc.id DESC), ',', 1) as maxId
191	FROM
192		delivery_config dc
193	WHERE
194		dc.company = 14 AND dc.is_active = 0
195	GROUP BY dc.fias_id
196) as t ON dc.id = t.maxId
197
198LEFT JOIN delivery_config as delivery_config_active ON dc.fias_id = delivery_config_active.fias_id AND delivery_config_active.is_active = 1 AND delivery_config_active.company = 14
199
200WHERE
201	dc.company = 14 AND dc.is_active = 0 AND delivery_config_active.id IS NULL;
202
203-- Курьер SPSR, переносим активные конфиги
204INSERT INTO delivery_config_new
205SELECT
206	null,
207	dc.company as delivery_types_id,
208	dc.fias_id,
209	dc.price_for_user as price_default,
210	dc.zone,
211	dc.`data`,
212	1 as customers_id,
213	IF(ISNULL(date_start), '2005-01-01 00:00:00', date_end + INTERVAL 1 SECOND) as date_start,
214	'2038-01-01 00:00:00' as date_end,
215	'1' as is_active
216FROM
217	delivery_config dc
218LEFT JOIN
219	delivery_config_new dcn ON dc.fias_id = dcn.fias_id AND dc.company = dcn.delivery_types_id
220WHERE
221	dc.company = 14 AND dc.is_active = 1;
222
223-- Курьер SPSR, диапазоны
224INSERT INTO delivery_ranges
225SELECT null, dcn.delivery_config_id, '107' AS entities_id, '0' AS subtotal_from, '100000000' AS subtotal_to, dcn.price_default AS price
226FROM delivery_config_new dcn
227WHERE dcn.delivery_types_id = 14;
228
229
230-- ############### Самовывоз Mamsy #################
231
232-- Самовывоз Mamsy, переносим отключенные версии без активных конфигов
233INSERT INTO delivery_config_new
234SELECT
235	null,
236	dc.company as delivery_types_id,
237	dc.fias_id,
238	dc.price_for_user as price_default,
239	dc.zone,
240	dc.`data`,
241	1 as customers_id,
242	'2005-01-01 00:00:00' as date_start,
243	'2038-01-01 00:00:00' as date_end,
244	'0' as is_active
245FROM
246	delivery_config dc
247JOIN (
248	SELECT
249		SUBSTRING_INDEX(group_concat(dc.id ORDER BY dc.id DESC), ',', 1) as maxId
250	FROM
251		delivery_config dc
252	WHERE
253		dc.company = 44 AND dc.is_active = 0
254	GROUP BY dc.fias_id
255) as t ON dc.id = t.maxId
256
257LEFT JOIN delivery_config as delivery_config_active ON dc.fias_id = delivery_config_active.fias_id AND delivery_config_active.is_active = 1 AND delivery_config_active.company = 44
258
259WHERE
260	dc.company = 44 AND dc.is_active = 0 AND delivery_config_active.id IS NULL;
261
262-- Самовывоз Mamsy, переносим активные конфиги
263INSERT INTO delivery_config_new
264SELECT
265	null,
266	dc.company as delivery_types_id,
267	dc.fias_id,
268	dc.price_for_user as price_default,
269	dc.zone,
270	dc.`data`,
271	1 as customers_id,
272	IF(ISNULL(date_start), '2005-01-01 00:00:00', date_end + INTERVAL 1 SECOND) as date_start,
273	'2038-01-01 00:00:00' as date_end,
274	'1' as is_active
275FROM
276	delivery_config dc
277LEFT JOIN
278	delivery_config_new dcn ON dc.fias_id = dcn.fias_id AND dc.company = dcn.delivery_types_id
279WHERE
280	dc.company = 44 AND dc.is_active = 1;
281
282-- Самовывоз Mamsy, диапазоны
283INSERT INTO delivery_ranges
284SELECT null, dcn.delivery_config_id, '107' AS entities_id, '0' AS subtotal_from, '100000000' AS subtotal_to, dcn.price_default AS price
285FROM delivery_config_new dcn
286WHERE dcn.delivery_types_id = 44;
287
288-- ############### Курьер ЖДЭ #################
289
290-- Курьер ЖДЭ, переносим отключенные версии без активных конфигов
291INSERT INTO delivery_config_new
292SELECT
293	null,
294	dc.company as delivery_types_id,
295	dc.fias_id,
296	dc.price_for_user as price_default,
297	dc.zone,
298	dc.`data`,
299	1 as customers_id,
300	'2005-01-01 00:00:00' as date_start,
301	'2038-01-01 00:00:00' as date_end,
302	'0' as is_active
303FROM
304	delivery_config dc
305JOIN (
306	SELECT
307		SUBSTRING_INDEX(group_concat(dc.id ORDER BY dc.id DESC), ',', 1) as maxId
308	FROM
309		delivery_config dc
310	WHERE
311		dc.company = 45 AND dc.is_active = 0
312	GROUP BY dc.fias_id
313) as t ON dc.id = t.maxId
314
315LEFT JOIN delivery_config as delivery_config_active ON dc.fias_id = delivery_config_active.fias_id AND delivery_config_active.is_active = 1 AND delivery_config_active.company = 45
316
317WHERE
318	dc.company = 45 AND dc.is_active = 0 AND delivery_config_active.id IS NULL;
319
320-- Курьер ЖДЭ, переносим активные конфиги
321INSERT INTO delivery_config_new
322SELECT
323	null,
324	dc.company as delivery_types_id,
325	dc.fias_id,
326	dc.price_for_user as price_default,
327	dc.zone,
328	dc.`data`,
329	1 as customers_id,
330	IF(ISNULL(date_start), '2005-01-01 00:00:00', date_end + INTERVAL 1 SECOND) as date_start,
331	'2038-01-01 00:00:00' as date_end,
332	'1' as is_active
333FROM
334	delivery_config dc
335LEFT JOIN
336	delivery_config_new dcn ON dc.fias_id = dcn.fias_id AND dc.company = dcn.delivery_types_id
337WHERE
338	dc.company = 45 AND dc.is_active = 1;
339
340-- Курьер ЖДЭ, диапазоны
341INSERT INTO delivery_ranges
342SELECT null, dcn.delivery_config_id, '107' AS entities_id, '0' AS subtotal_from, '100000000' AS subtotal_to, dcn.price_default AS price
343FROM delivery_config_new dcn
344WHERE dcn.delivery_types_id = 45;
345
346
347-- ############### Курьер Shiptor #################
348
349-- Курьер Shiptor, переносим отключенные версии без активных конфигов
350INSERT INTO delivery_config_new
351SELECT
352	null,
353	dc.company as delivery_types_id,
354	dc.fias_id,
355	dc.price_for_user as price_default,
356	dc.zone,
357	dc.`data`,
358	1 as customers_id,
359	'2005-01-01 00:00:00' as date_start,
360	'2038-01-01 00:00:00' as date_end,
361	'0' as is_active
362FROM
363	delivery_config dc
364JOIN (
365	SELECT
366		SUBSTRING_INDEX(group_concat(dc.id ORDER BY dc.id DESC), ',', 1) as maxId
367	FROM
368		delivery_config dc
369	WHERE
370		dc.company = 46 AND dc.is_active = 0
371	GROUP BY dc.fias_id
372) as t ON dc.id = t.maxId
373
374LEFT JOIN delivery_config as delivery_config_active ON dc.fias_id = delivery_config_active.fias_id AND delivery_config_active.is_active = 1 AND delivery_config_active.company = 46
375
376WHERE
377	dc.company = 46 AND dc.is_active = 0 AND delivery_config_active.id IS NULL;
378
379-- Курьер Shiptor, переносим активные конфиги
380INSERT INTO delivery_config_new
381SELECT
382	null,
383	dc.company as delivery_types_id,
384	dc.fias_id,
385	dc.price_for_user as price_default,
386	dc.zone,
387	dc.`data`,
388	1 as customers_id,
389	IF(ISNULL(date_start), '2005-01-01 00:00:00', date_end + INTERVAL 1 SECOND) as date_start,
390	'2038-01-01 00:00:00' as date_end,
391	'1' as is_active
392FROM
393	delivery_config dc
394LEFT JOIN
395	delivery_config_new dcn ON dc.fias_id = dcn.fias_id AND dc.company = dcn.delivery_types_id
396WHERE
397	dc.company = 46 AND dc.is_active = 1;
398
399-- Курьер Shiptor, диапазоны
400INSERT INTO delivery_ranges
401SELECT null, dcn.delivery_config_id, '107' AS entities_id, '0' AS subtotal_from, '100000000' AS subtotal_to, dcn.price_default AS price
402FROM delivery_config_new dcn
403WHERE dcn.delivery_types_id = 46;
404
405
406-- ############### Курьер DPD #################
407
408-- Курьер DPD, переносим отключенные версии без активных конфигов
409INSERT INTO delivery_config_new
410SELECT
411	null,
412	dc.company as delivery_types_id,
413	dc.fias_id,
414	dc.price_for_user as price_default,
415	dc.zone,
416	dc.`data`,
417	1 as customers_id,
418	'2005-01-01 00:00:00' as date_start,
419	'2038-01-01 00:00:00' as date_end,
420	'0' as is_active
421FROM
422	delivery_config dc
423JOIN (
424	SELECT
425		SUBSTRING_INDEX(group_concat(dc.id ORDER BY dc.id DESC), ',', 1) as maxId
426	FROM
427		delivery_config dc
428	WHERE
429		dc.company = 48 AND dc.is_active = 0
430	GROUP BY dc.fias_id
431) as t ON dc.id = t.maxId
432
433LEFT JOIN delivery_config as delivery_config_active ON dc.fias_id = delivery_config_active.fias_id AND delivery_config_active.is_active = 1 AND delivery_config_active.company = 48
434
435WHERE
436	dc.company = 48 AND dc.is_active = 0 AND delivery_config_active.id IS NULL;
437
438-- Курьер DPD, переносим активные конфиги
439INSERT INTO delivery_config_new
440SELECT
441	null,
442	dc.company as delivery_types_id,
443	dc.fias_id,
444	dc.price_for_user as price_default,
445	dc.zone,
446	dc.`data`,
447	1 as customers_id,
448	IF(ISNULL(date_start), '2005-01-01 00:00:00', date_end + INTERVAL 1 SECOND) as date_start,
449	'2038-01-01 00:00:00' as date_end,
450	'1' as is_active
451FROM
452	delivery_config dc
453LEFT JOIN
454	delivery_config_new dcn ON dc.fias_id = dcn.fias_id AND dc.company = dcn.delivery_types_id
455WHERE
456	dc.company = 48 AND dc.is_active = 1;
457
458-- Курьер DPD, диапазоны
459INSERT INTO delivery_ranges
460SELECT null, dcn.delivery_config_id, '107' AS entities_id, '0' AS subtotal_from, '100000000' AS subtotal_to, dcn.price_default AS price
461FROM delivery_config_new dcn
462WHERE dcn.delivery_types_id = 48;
463
464
465-- ############### Курьер DPD GS #################
466
467-- Курьер DPD GS, переносим отключенные версии без активных конфигов
468INSERT INTO delivery_config_new
469SELECT
470	null,
471	dc.company as delivery_types_id,
472	dc.fias_id,
473	dc.price_for_user as price_default,
474	dc.zone,
475	dc.`data`,
476	1 as customers_id,
477	'2005-01-01 00:00:00' as date_start,
478	'2038-01-01 00:00:00' as date_end,
479	'0' as is_active
480FROM
481	delivery_config dc
482JOIN (
483	SELECT
484		SUBSTRING_INDEX(group_concat(dc.id ORDER BY dc.id DESC), ',', 1) as maxId
485	FROM
486		delivery_config dc
487	WHERE
488		dc.company = 50 AND dc.is_active = 0
489	GROUP BY dc.fias_id
490) as t ON dc.id = t.maxId
491
492LEFT JOIN delivery_config as delivery_config_active ON dc.fias_id = delivery_config_active.fias_id AND delivery_config_active.is_active = 1 AND delivery_config_active.company = 50
493
494WHERE
495	dc.company = 50 AND dc.is_active = 0 AND delivery_config_active.id IS NULL;
496
497-- Курьер DPD GS, переносим активные конфиги
498INSERT INTO delivery_config_new
499SELECT
500	null,
501	dc.company as delivery_types_id,
502	dc.fias_id,
503	dc.price_for_user as price_default,
504	dc.zone,
505	dc.`data`,
506	1 as customers_id,
507	IF(ISNULL(date_start), '2005-01-01 00:00:00', date_end + INTERVAL 1 SECOND) as date_start,
508	'2038-01-01 00:00:00' as date_end,
509	'1' as is_active
510FROM
511	delivery_config dc
512LEFT JOIN
513	delivery_config_new dcn ON dc.fias_id = dcn.fias_id AND dc.company = dcn.delivery_types_id
514WHERE
515	dc.company = 50 AND dc.is_active = 1;
516
517-- Курьер DPD GS, диапазоны
518INSERT INTO delivery_ranges
519SELECT null, dcn.delivery_config_id, '107' AS entities_id, '0' AS subtotal_from, '100000000' AS subtotal_to, dcn.price_default AS price
520FROM delivery_config_new dcn
521WHERE dcn.delivery_types_id = 50;
522
523
524-- ############### Курьер IML #################
525
526-- Курьер IML, переносим отключенные версии без активных конфигов
527INSERT INTO delivery_config_new
528SELECT
529	null,
530	dc.company as delivery_types_id,
531	dc.fias_id,
532	dc.price_for_user as price_default,
533	dc.zone,
534	dc.`data`,
535	1 as customers_id,
536	'2005-01-01 00:00:00' as date_start,
537	'2038-01-01 00:00:00' as date_end,
538	'0' as is_active
539FROM
540	delivery_config dc
541JOIN (
542	SELECT
543		SUBSTRING_INDEX(group_concat(dc.id ORDER BY dc.id DESC), ',', 1) as maxId
544	FROM
545		delivery_config dc
546	WHERE
547		dc.company = 6 AND dc.is_active = 0
548	GROUP BY dc.fias_id
549) as t ON dc.id = t.maxId
550
551LEFT JOIN delivery_config as delivery_config_active ON dc.fias_id = delivery_config_active.fias_id AND delivery_config_active.is_active = 1 AND delivery_config_active.company = 6
552
553WHERE
554	dc.company = 6 AND dc.is_active = 0 AND delivery_config_active.id IS NULL;
555
556-- Курьер IML, переносим активные конфиги
557INSERT INTO delivery_config_new
558SELECT
559	null,
560	dc.company as delivery_types_id,
561	dc.fias_id,
562	dc.price_for_user as price_default,
563	dc.zone,
564	dc.`data`,
565	1 as customers_id,
566	IF(ISNULL(date_start), '2005-01-01 00:00:00', date_end + INTERVAL 1 SECOND) as date_start,
567	'2038-01-01 00:00:00' as date_end,
568	'1' as is_active
569FROM
570	delivery_config dc
571LEFT JOIN
572	delivery_config_new dcn ON dc.fias_id = dcn.fias_id AND dc.company = dcn.delivery_types_id
573WHERE
574	dc.company = 6 AND dc.is_active = 1;
575
576-- Курьер IML, диапазоны
577INSERT INTO delivery_ranges
578SELECT null, dcn.delivery_config_id, '107' AS entities_id, '0' AS subtotal_from, '100000000' AS subtotal_to, dcn.price_default AS price
579FROM delivery_config_new dcn
580WHERE dcn.delivery_types_id = 6;
581
582
583-- ############### Pickpoint #################
584
585-- Pickpoint, переносим отключенные версии без активных конфигов
586INSERT INTO delivery_config_new
587SELECT
588	null,
589	dc.delivery_types_id,
590	dc.fias_id,
591	dc.price as price_default,
592	dc.zone,
593	null,
594	1 as customers_id,
595	'2005-01-01 00:00:00' as date_start,
596	'2038-01-01 00:00:00' as date_end,
597	'0' as is_active
598FROM
599	delivery_points_config dc
600JOIN (
601	SELECT
602		SUBSTRING_INDEX(group_concat(dc.delivery_points_config_id ORDER BY dc.delivery_points_config_id DESC), ',', 1) as maxId
603	FROM
604		delivery_points_config dc
605	WHERE
606		dc.delivery_types_id = 21 AND dc.is_active = 0
607	GROUP BY dc.fias_id
608) as t ON dc.delivery_points_config_id = t.maxId
609
610LEFT JOIN delivery_points_config as delivery_config_active ON dc.fias_id = delivery_config_active.fias_id AND delivery_config_active.is_active = 1 AND delivery_config_active.delivery_types_id = 21
611
612WHERE
613	dc.delivery_types_id = 21 AND dc.is_active = 0 AND delivery_config_active.delivery_points_config_id IS NULL;
614
615-- Pickpoint, переносим активные конфиги
616INSERT INTO delivery_config_new
617SELECT
618	null,
619	dc.delivery_types_id,
620	dc.fias_id,
621	dc.price as price_default,
622	dc.zone,
623	null,
624	1 as customers_id,
625	IF(ISNULL(date_start), '2005-01-01 00:00:00', date_end + INTERVAL 1 SECOND) as date_start,
626	'2038-01-01 00:00:00' as date_end,
627	'1' as is_active
628FROM
629	delivery_points_config dc
630LEFT JOIN
631	delivery_config_new dcn ON dc.fias_id = dcn.fias_id AND dc.delivery_types_id = dcn.delivery_types_id
632WHERE
633	dc.delivery_types_id = 21 AND dc.is_active = 1;
634
635-- Pickpoint, диапазоны
636INSERT INTO delivery_ranges
637SELECT null, dcn.delivery_config_id, '107' AS entities_id, '0' AS subtotal_from, '100000000' AS subtotal_to, dcn.price_default AS price
638FROM delivery_config_new dcn
639WHERE dcn.delivery_types_id = 21;
640
641
642-- ############### ПВЗ Hermes #################
643
644-- ПВЗ Hermes, переносим отключенные версии без активных конфигов
645INSERT INTO delivery_config_new
646SELECT
647	null,
648	dc.delivery_types_id,
649	dc.fias_id,
650	dc.price as price_default,
651	dc.zone,
652	null,
653	1 as customers_id,
654	'2005-01-01 00:00:00' as date_start,
655	'2038-01-01 00:00:00' as date_end,
656	'0' as is_active
657FROM
658	delivery_points_config dc
659JOIN (
660	SELECT
661		SUBSTRING_INDEX(group_concat(dc.delivery_points_config_id ORDER BY dc.delivery_points_config_id DESC), ',', 1) as maxId
662	FROM
663		delivery_points_config dc
664	WHERE
665		dc.delivery_types_id = 51 AND dc.is_active = 0
666	GROUP BY dc.fias_id
667) as t ON dc.delivery_points_config_id = t.maxId
668
669LEFT JOIN delivery_points_config as delivery_config_active ON dc.fias_id = delivery_config_active.fias_id AND delivery_config_active.is_active = 1 AND delivery_config_active.delivery_types_id = 51
670
671WHERE
672	dc.delivery_types_id = 51 AND dc.is_active = 0 AND delivery_config_active.delivery_points_config_id IS NULL;
673
674-- ПВЗ Hermes, переносим активные конфиги
675INSERT INTO delivery_config_new
676SELECT
677	null,
678	dc.delivery_types_id,
679	dc.fias_id,
680	dc.price as price_default,
681	dc.zone,
682	null,
683	1 as customers_id,
684	IF(ISNULL(date_start), '2005-01-01 00:00:00', date_end + INTERVAL 1 SECOND) as date_start,
685	'2038-01-01 00:00:00' as date_end,
686	'1' as is_active
687FROM
688	delivery_points_config dc
689LEFT JOIN
690	delivery_config_new dcn ON dc.fias_id = dcn.fias_id AND dc.delivery_types_id = dcn.delivery_types_id
691WHERE
692	dc.delivery_types_id = 51 AND dc.is_active = 1;
693
694-- ПВЗ Hermes, диапазоны
695INSERT INTO delivery_ranges
696SELECT null, dcn.delivery_config_id, '107' AS entities_id, '0' AS subtotal_from, '100000000' AS subtotal_to, dcn.price_default AS price
697FROM delivery_config_new dcn
698WHERE dcn.delivery_types_id = 51;
699
700
701-- ############### ПВЗ Hermes GS #################
702
703-- ПВЗ Hermes GS, переносим отключенные версии без активных конфигов
704INSERT INTO delivery_config_new
705SELECT
706	null,
707	dc.delivery_types_id,
708	dc.fias_id,
709	dc.price as price_default,
710	dc.zone,
711	null,
712	1 as customers_id,
713	'2005-01-01 00:00:00' as date_start,
714	'2038-01-01 00:00:00' as date_end,
715	'0' as is_active
716FROM
717	delivery_points_config dc
718JOIN (
719	SELECT
720		SUBSTRING_INDEX(group_concat(dc.delivery_points_config_id ORDER BY dc.delivery_points_config_id DESC), ',', 1) as maxId
721	FROM
722		delivery_points_config dc
723	WHERE
724		dc.delivery_types_id = 52 AND dc.is_active = 0
725	GROUP BY dc.fias_id
726) as t ON dc.delivery_points_config_id = t.maxId
727
728LEFT JOIN delivery_points_config as delivery_config_active ON dc.fias_id = delivery_config_active.fias_id AND delivery_config_active.is_active = 1 AND delivery_config_active.delivery_types_id = 52
729
730WHERE
731	dc.delivery_types_id = 52 AND dc.is_active = 0 AND delivery_config_active.delivery_points_config_id IS NULL;
732
733-- ПВЗ Hermes GS, переносим активные конфиги
734INSERT INTO delivery_config_new
735SELECT
736	null,
737	dc.delivery_types_id,
738	dc.fias_id,
739	dc.price as price_default,
740	dc.zone,
741	null,
742	1 as customers_id,
743	IF(ISNULL(date_start), '2005-01-01 00:00:00', date_end + INTERVAL 1 SECOND) as date_start,
744	'2038-01-01 00:00:00' as date_end,
745	'1' as is_active
746FROM
747	delivery_points_config dc
748LEFT JOIN
749	delivery_config_new dcn ON dc.fias_id = dcn.fias_id AND dc.delivery_types_id = dcn.delivery_types_id
750WHERE
751	dc.delivery_types_id = 52 AND dc.is_active = 1;
752
753-- ПВЗ Hermes GS, диапазоны
754INSERT INTO delivery_ranges
755SELECT null, dcn.delivery_config_id, '107' AS entities_id, '0' AS subtotal_from, '100000000' AS subtotal_to, dcn.price_default AS price
756FROM delivery_config_new dcn
757WHERE dcn.delivery_types_id = 52;
758
759-- Связной, переносим отключенные версии без активных конфигов
760INSERT INTO delivery_config_new
761SELECT
762	null,
763	47 as delivery_types_id,
764	dc.fias_id,
765	dc.price as price_default,
766	dc.zone,
767	null,
768	1 as customers_id,
769	'2005-01-01 00:00:00' as date_start,
770	'2038-01-01 00:00:00' as date_end,
771	'0' as is_active
772FROM
773	delivery_svyaznoy dc
774JOIN (
775	SELECT
776		SUBSTRING_INDEX(group_concat(dc.svyaznoy_city_id ORDER BY dc.svyaznoy_city_id DESC), ',', 1) as maxId
777	FROM
778		delivery_svyaznoy dc
779	WHERE
780		dc.is_active = 0 OR dc.is_active = -1
781	GROUP BY dc.fias_id
782) as t ON dc.svyaznoy_city_id = t.maxId
783
784LEFT JOIN delivery_svyaznoy as delivery_config_active ON dc.fias_id = delivery_config_active.fias_id AND delivery_config_active.is_active = 1
785
786WHERE
787	(dc.is_active = 0 OR dc.is_active = -1) AND delivery_config_active.svyaznoy_city_id IS NULL;
788
789-- Связной, переносим активные конфиги
790INSERT INTO delivery_config_new
791SELECT
792	null,
793	47 as delivery_types_id,
794	dc.fias_id,
795	dc.price as price_default,
796	dc.zone,
797	null,
798	1 as customers_id,
799	IF(ISNULL(date_start), '2005-01-01 00:00:00', date_end + INTERVAL 1 SECOND) as date_start,
800	'2038-01-01 00:00:00' as date_end,
801	'1' as is_active
802FROM
803	delivery_svyaznoy dc
804LEFT JOIN
805	delivery_config_new dcn ON dc.fias_id = dcn.fias_id AND 47 = dcn.delivery_types_id
806WHERE
807	dc.is_active = 1;
808
809	-- Связной, диапазоны
810INSERT INTO delivery_ranges
811SELECT null, dcn.delivery_config_id, '107' AS entities_id, '0' AS subtotal_from, '100000000' AS subtotal_to, dcn.price_default AS price
812FROM delivery_config_new dcn
813WHERE dcn.delivery_types_id = 47;
814
815-- связки фиаса и города в системе ТК
816INSERT IGNORE INTO delivery_config_extra
817SELECT dpc.delivery_types_id, dpc.fias_id, dpc.tc_city_code FROM delivery_points_config dpc;
818
819-- DROP диапазоны
820INSERT INTO delivery_ranges
821SELECT null, dp.delivery_points_id as object_id, 108 as entities_id, '0' as subtotal_from, '100000000' as subtotal_to, dp.drop_price as price  FROM delivery_points dp WHERE dp.drop_on = 1;
822
823INSERT INTO `admin_menu` (`pid`, `name`, `url`) VALUES ('106', 'Диапазоны тарифов', '/call/delivery/config-ranges');
824
825INSERT INTO `acl_rules` (`module`, `controller`, `action`) VALUES ('call', 'delivery', 'config-ranges');
826SET @ruleId = LAST_INSERT_ID();
827
828INSERT INTO `acl_settings` (`acl_roles_id`, `acl_rules_id`) VALUES
829       (25, @ruleId),
830       (64, @ruleId)
831;