hqW59WUd

1# добавляем понятие Тип упаковки (Пленка, Пакет, Коробка) и проставляем для всех упаковок
2ALTER TABLE `boxes` ADD COLUMN `type` TINYINT(1) NOT NULL DEFAULT 1;
3UPDATE `boxes` SET type = 1 WHERE boxes_id IN(1,2,3,4,10,11,12,13,14,15,16);
4UPDATE `boxes` SET type = 2 WHERE boxes_id IN(5,6,7,8,9,17,18,19);
5
6# переименовываем Пакеты почта
7UPDATE `boxes` SET boxes_title = 'большой' WHERE boxes_id = 13;
8UPDATE `boxes` SET boxes_title = 'средний' WHERE boxes_id = 14;
9UPDATE `boxes` SET boxes_title = 'маленький' WHERE boxes_id = 15;
10
11# добавляем Пакеты DHL
12INSERT INTO `boxes_group` (`boxes_group_title`) VALUES ('Пакеты DHL');
13set @dhlPocketsGroupId = last_insert_id();
14INSERT INTO `boxes` (`boxes_title`, `boxes_group_id`, `boxes_weight`, `boxes_width`, `boxes_length`, `boxes_height`, `type`) VALUES('маленький', @dhlPocketsGroupId, 1, 30, 46, 0, 1);
15INSERT INTO `boxes_access` (`boxes_id`, `delivery_type_id`) VALUES (last_insert_id(), 22);
16INSERT INTO `boxes` (`boxes_title`, `boxes_group_id`, `boxes_weight`, `boxes_width`, `boxes_length`, `boxes_height`, `type`) VALUES('большой', @dhlPocketsGroupId, 1, 38, 53, 0, 1);
17INSERT INTO `boxes_access` (`boxes_id`, `delivery_type_id`) VALUES (last_insert_id(), 22);
18
19# удаляем доступ к некоторым пакетам SPSR, Почта, EMS для DHL
20DELETE FROM `boxes_access` WHERE `delivery_type_id` IN (22) AND `boxes_id` IN (1,2,3,4,13,14,15,16);
21
22# добавляем доступ к некоторым коробкам для ТК, где нет доступа
23INSERT INTO `boxes_access` (`boxes_id`, `delivery_type_id`) VALUES (19, 20);
24INSERT INTO `boxes_access` (`boxes_id`, `delivery_type_id`) VALUES (17, 17);
25INSERT INTO `boxes_access` (`boxes_id`, `delivery_type_id`) VALUES (17, 21);
26INSERT INTO `boxes_access` (`boxes_id`, `delivery_type_id`) VALUES (17, 22);
27INSERT INTO `boxes_access` (`boxes_id`, `delivery_type_id`) VALUES (17, 14);
28
29# добавляем коробку 80x20x20 и даем доступ всем ТК
30INSERT INTO `boxes` (`boxes_title`, `boxes_group_id`, `boxes_weight`, `boxes_width`, `boxes_length`, `boxes_height`, `type`) VALUES('80*20*20', 2, 6.4, 80, 20, 20, 2);
31set @newBoxId = last_insert_id();
32INSERT INTO `boxes_access` (`boxes_id`, `delivery_type_id`) VALUES (@newBoxId , 17);
33INSERT INTO `boxes_access` (`boxes_id`, `delivery_type_id`) VALUES (@newBoxId , 21);
34INSERT INTO `boxes_access` (`boxes_id`, `delivery_type_id`) VALUES (@newBoxId , 13);
35INSERT INTO `boxes_access` (`boxes_id`, `delivery_type_id`) VALUES (@newBoxId , 22);
36INSERT INTO `boxes_access` (`boxes_id`, `delivery_type_id`) VALUES (@newBoxId , 14);
37INSERT INTO `boxes_access` (`boxes_id`, `delivery_type_id`) VALUES (@newBoxId , 20);
38INSERT INTO `boxes_access` (`boxes_id`, `delivery_type_id`) VALUES (@newBoxId , 44);
39
40# добавляем группу Пленка, упаковку Пленка и связываем ее со всеми ТК, где есть Коробка Нестандарт
41INSERT INTO `boxes_group` (`boxes_group_title`) VALUES ('Пленка');
42INSERT INTO `boxes` (`boxes_title`, `boxes_group_id`, `type`) VALUES('пленка', last_insert_id(), 3);
43set @tapeBoxId = last_insert_id();
44INSERT INTO `boxes_access` (`boxes_id`, `delivery_type_id`) VALUES
45(@tapeBoxId, 5),
46(@tapeBoxId, 6),
47(@tapeBoxId, 13),
48(@tapeBoxId, 14),
49(@tapeBoxId, 17),
50(@tapeBoxId, 20),
51(@tapeBoxId, 21),
52(@tapeBoxId, 22),
53(@tapeBoxId, 44),
54(@tapeBoxId, 45);
55
56#сортировка внутри групп
57UPDATE `boxes` SET `sorting` = 1 WHERE boxes_id = 2;
58UPDATE `boxes` SET `sorting` = 2 WHERE boxes_id = 1;
59UPDATE `boxes` SET `sorting` = 3 WHERE boxes_id = 3;
60UPDATE `boxes` SET `sorting` = 4 WHERE boxes_id = 4;
61
62UPDATE `boxes` SET `sorting` = 1 WHERE boxes_id = 20;
63UPDATE `boxes` SET `sorting` = 2 WHERE boxes_id = 21;
64
65UPDATE `boxes` SET `sorting` = 1 WHERE boxes_id = 11;
66UPDATE `boxes` SET `sorting` = 2 WHERE boxes_id = 10;
67
68UPDATE `boxes` SET `sorting` = 1 WHERE boxes_id = 14;
69UPDATE `boxes` SET `sorting` = 2 WHERE boxes_id = 13;
70UPDATE `boxes` SET `sorting` = 3 WHERE boxes_id = 15;
71
72UPDATE `boxes` SET `sorting` = 1 WHERE boxes_id = 8;
73UPDATE `boxes` SET `sorting` = 2 WHERE boxes_id = 18;
74UPDATE `boxes` SET `sorting` = 3 WHERE boxes_id = 7;
75UPDATE `boxes` SET `sorting` = 4 WHERE boxes_id = 6;
76UPDATE `boxes` SET `sorting` = 5 WHERE boxes_id = 19;
77UPDATE `boxes` SET `sorting` = 6 WHERE boxes_id = 5;
78UPDATE `boxes` SET `sorting` = 6 WHERE boxes_id = 5;
79UPDATE `boxes` SET `sorting` = 7 WHERE boxes_id = 17;
80UPDATE `boxes` SET `sorting` = 8 WHERE boxes_id = 22;
81UPDATE `boxes` SET `sorting` = 9 WHERE boxes_id = 9;
82
83# поправим длину и ширину у коробки, у которой они не соответствуют названию (уточнить)
84UPDATE `boxes` SET boxes_width = 30.5, boxes_length = 20.8 WHERE boxes_id = 18;
85
86# таблица для хранения габаритов и количества упаковки, а также объемного веса для заказов с нестандартной упаковкой
87DROP TABLE IF EXISTS `orders_nonstandard_boxes`;
88CREATE TABLE `orders_nonstandard_boxes` (
89  `orders_nonstandard_boxes_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
90  `orders_id` INT UNSIGNED NOT NULL,
91  `boxes_id` INT UNSIGNED NOT NULL COMMENT 'ID стандартной упаковки, из которой берется только длина и ширина',
92  `weight` FLOAT UNSIGNED NOT NULL DEFAULT 0,
93  `width` FLOAT UNSIGNED NOT NULL DEFAULT 0,
94  `length` FLOAT UNSIGNED NOT NULL DEFAULT 0,
95  `height` FLOAT UNSIGNED NOT NULL DEFAULT 0,
96  `title` VARCHAR(50) NULL DEFAULT NULL,
97  `amount` FLOAT UNSIGNED NOT NULL DEFAULT 1,
98  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
99  PRIMARY KEY (`orders_nonstandard_boxes_id`),
100  INDEX `boxes_id` (`boxes_id`),
101  UNIQUE INDEX `orders_id` (`orders_id`)
102)
103  COLLATE='utf8_general_ci'
104  ENGINE=InnoDB
105;
106
107# выводить ли вариант на упаковке
108ALTER TABLE `boxes` ADD COLUMN `hidden` TINYINT(1) DEFAULT 0;
109# скрываем коробку нестандарт
110UPDATE `boxes` SET hidden = 1 WHERE boxes_id = 9;
111
112# обновляем запрос Данные по упаковочным материалам в query_storage, чтобы считалось количество тары, а не количество заказов
113UPDATE `query_storage` SET `query_storage_query` =
114"SELECT
115DATE_FORMAT(sh.date_added, '%Y-%m') as month,
116IF (b.boxes_title IS NULL, 'без упаковки', CONCAT(bg.boxes_group_title, ' (', b.boxes_title, ')')) as box,
117SUM(IF(onb.amount IS NULL, 1, onb.amount)) as count
118FROM (
119  SELECT MIN(sh.date_added) as date_added, sh.object_id
120  FROM orders_status_history as sh
121  WHERE sh.date_added BETWEEN #Дата от|date_from|date# AND #Дата до|date_to|date_end# AND sh.status_id = 16
122  GROUP BY sh.object_id
123) as sh
124INNER JOIN orders as o ON o.orders_id = sh.object_id
125LEFT JOIN orders_nonstandard_boxes onb ON o.orders_id = onb.orders_id
126LEFT JOIN boxes as b ON b.boxes_id = o.orders_box_id OR b.boxes_id = onb.boxes_id
127LEFT JOIN boxes_group as bg ON bg.boxes_group_id = b.boxes_group_id
128GROUP BY b.boxes_group_id, b.boxes_id;"
129WHERE `query_storage_id` = 7;
130
131# Таблица refunds_acts_to_categories
132ALTER TABLE `refunds_acts_to_categories`
133  CHANGE COLUMN `categories_id` `object_id` INT(11) NOT NULL AFTER `refunds_acts_id`;
134
135ALTER TABLE `refunds_acts_to_categories`
136  ADD COLUMN `entities_id` INT(11) NOT NULL AFTER `object_id`;
137
138UPDATE `refunds_acts_to_categories` SET `entities_id` = 33;
139
140RENAME TABLE `refunds_acts_to_categories` TO `refunds_acts_to_objects`;
141
142# Таблица refunds_acts_to_products
143ALTER TABLE `refunds_acts_to_products`
144  CHANGE COLUMN `categories_id` `object_id` INT(11) NOT NULL AFTER `products_attributes_id`;
145
146ALTER TABLE `refunds_acts_to_products`
147  ADD COLUMN `entities_id` INT(11) NOT NULL AFTER `object_id`;
148
149UPDATE `refunds_acts_to_products` SET `entities_id` = 33;
150
151ALTER TABLE `refunds_acts_to_objects`
152  CHANGE COLUMN `refunds_acts_to_categories_products_count_total` `refunds_acts_to_object_products_count_total` INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `refunds_acts_to_categories_total`;
153
154ALTER TABLE `incomes`
155  ADD COLUMN `stock_zones_id` INT NULL DEFAULT NULL COMMENT 'родительская зона склада, привязанная к поставке' AFTER `supply_date`;
156
157
158INSERT IGNORE INTO acl_settings (acl_roles_id, acl_rules_id, access_type, assert)
159  SELECT aro.acl_roles_id, aru.acl_rules_id, 'ALLOW', ''
160  FROM acl_roles aro, acl_rules aru
161  WHERE aro.acl_roles_id IN (44) AND aru.module = 'call' AND aru.controller = 'shop' AND aru.action IN ('income-edit');
162
163DELETE FROM `log_blog` WHERE `log_articles_id` IN ('4521');
164
165UPDATE `orders_products_new` SET `is_return`=0 WHERE  `orders_id`=2063294;
166
167insert into acl_exceptions (`customers_id`, `controller`, `method`) values (3948381, 'statistic', 'get-total-sale');
168
169ALTER TABLE `check_1c_payments`
170  ADD INDEX `object_id` (`object_id`),
171  ADD INDEX `entities_id` (`entities_id`),
172  ADD INDEX `is_deleted` (`is_deleted`),
173  ADD INDEX `approved_bank` (`approved_bank`);
174
175ALTER TABLE `refunds_acts`
176  ADD INDEX `refunds_acts_is_deleted` (`refunds_acts_is_deleted`),
177  ADD INDEX `refunds_acts_date` (`refunds_acts_date`);
178
179ALTER TABLE `suppliers_contracts`
180  ADD INDEX `suppliers_contracts_status` (`suppliers_contracts_status`);
181
182ALTER TABLE `contractors_payments`
183  ADD INDEX `is_deleted` (`is_deleted`);
184
185ALTER TABLE `payments_nettings`
186  ADD INDEX `payments_nettings_document_types_id` (`payments_nettings_document_types_id`);
187
188ALTER TABLE `products_other`
189  ADD COLUMN `hs_code` VARCHAR(50) NULL AFTER `loaded_reserve`;