fZ1uBJRR

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