vu0eDGtQ

1INSERT INTO `acl_rules` (`module`, `controller`, `action`) VALUES ('call', 'statistic', 'pickup-points-report');
2INSERT IGNORE INTO acl_settings (acl_roles_id, acl_rules_id, access_type, assert)
3  SELECT aro.acl_roles_id, aru.acl_rules_id, 'ALLOW', '' FROM acl_roles aro, acl_rules aru
4  WHERE aro.acl_roles_title_en IN ('mainLogist') AND aru.module = 'call' AND aru.controller = 'statistic' AND
5        aru.action IN ('pickup-points-report', 'save-pickup-points-report-data');
6
7DROP TABLE IF EXISTS `pickup_points_report_data`;
8CREATE TABLE `pickup_points_report_data` (
9  `pickup_points_report_data_id` INT(11) NOT NULL AUTO_INCREMENT,
10  `pickup_points_id` INT(11) NOT NULL DEFAULT '0',
11  `year` INT(11) NOT NULL DEFAULT '0',
12  `month` INT(11) NOT NULL DEFAULT '0',
13  `expenses` INT(11) NOT NULL DEFAULT '0',
14  `customers_id` INT(11) NOT NULL DEFAULT '0',
15  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
16  PRIMARY KEY (`pickup_points_report_data_id`),
17  UNIQUE INDEX `pickup_points_id_year_month` (`pickup_points_id`, `year`, `month`),
18  INDEX `pickup_points_id` (`pickup_points_id`),
19  INDEX `year_month` (`year`, `month`)
20)
21  COLLATE='utf8_general_ci'
22  ENGINE=InnoDB;
23
24DROP TABLE IF EXISTS `orders_to_pickup_points`;
25CREATE TABLE `orders_to_pickup_points` (
26  `orders_to_pickup_points_id` INT(11) NOT NULL AUTO_INCREMENT,
27  `orders_id` INT(11) NOT NULL DEFAULT '0',
28  `pickup_points_id` INT(11) NOT NULL DEFAULT '0',
29  PRIMARY KEY (`orders_to_pickup_points_id`),
30  INDEX `pickup_points_id` (`pickup_points_id`),
31  UNIQUE INDEX `orders_id_unique` (`orders_id`)
32)
33  COMMENT='Привязка заказа к пункту самовывоза Мамси, через который он пройдет'
34  COLLATE='utf8_general_ci'
35  ENGINE=InnoDB
36;
37-- заполнение связки заказов с ПВЗ Мамси по существующим заказам
38INSERT IGNORE INTO `orders_to_pickup_points` (`orders_id`, `pickup_points_id`)
39  SELECT
40    o.orders_id,
41    (case
42     when f.region = 779782 then 1
43     when f.region = 335031 then 2
44     when f.region = 238025 then 3
45     end) as pickup_points_id
46
47  FROM
48    orders o
49    JOIN
50    fias f ON o.customers_fias_id = f.id AND (
51    (o.shipping_class IN('pickup_mamsy', 'courier_mamsy') AND f.region IN(779782, 335031)) OR
52    (o.shipping_class IN('pickup_mamsy') AND f.region IN(238025))
53  );
54
55 ALTER TABLE `pickup_points`
56	ADD COLUMN `chart_color` VARCHAR(25) NULL DEFAULT '#000000' AFTER `sort`;
57
58UPDATE `pickup_points` SET chart_color = '#F11372' WHERE pickup_points_id = 1;
59UPDATE `pickup_points` SET chart_color = '#05BBFC' WHERE pickup_points_id = 2;
60UPDATE `pickup_points` SET chart_color = '#36d11b' WHERE pickup_points_id = 3;