TVfeDRAu

1ALTER TABLE `incomes_attributes`
2  ADD COLUMN `incomes_attributes_status` INT(10) UNSIGNED NOT NULL DEFAULT '1' AFTER `customers_id`,
3  ADD INDEX `incomes_attributes_status` (`incomes_attributes_status`);
4
5INSERT INTO `partner_left_menu` (`pid`, `name`, `url`, `icon`) VALUES ('8', 'Статичные товары', '/partner/administration/range-suppliers-stock', 'icon-fire');
6
7CREATE TABLE if NOT EXISTS `incomes_attributes_status` (
8  `incomes_attributes_status_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
9  `incomes_attributes_status_name` VARCHAR(50) NOT NULL,
10  `use_as_active` TINYINT(4) NOT NULL,
11  PRIMARY KEY (`incomes_attributes_status_id`),
12  INDEX `use_as_active` (`use_as_active`)
13)
14  COLLATE='utf8_general_ci'
15  ENGINE=InnoDB
16;
17
18INSERT INTO `incomes_attributes_status` (`incomes_attributes_status_id`, `incomes_attributes_status_name`, `use_as_active`) VALUES (1, 'Активно', 1);
19INSERT INTO `incomes_attributes_status` (`incomes_attributes_status_id`, `incomes_attributes_status_name`, `use_as_active`) VALUES (2, 'Активно (цена)', 1);
20INSERT INTO `incomes_attributes_status` (`incomes_attributes_status_id`, `incomes_attributes_status_name`, `use_as_active`) VALUES (3, 'Неактивно (контент)', 0);
21INSERT INTO `incomes_attributes_status` (`incomes_attributes_status_id`, `incomes_attributes_status_name`, `use_as_active`) VALUES (4, 'Нулевой резерв', 0);
22INSERT INTO `incomes_attributes_status` (`incomes_attributes_status_id`, `incomes_attributes_status_name`, `use_as_active`) VALUES (5, 'Неактивно (цена внутренняя)', 0);
23INSERT INTO `incomes_attributes_status` (`incomes_attributes_status_id`, `incomes_attributes_status_name`, `use_as_active`) VALUES (6, 'Неактивно (цена внешняя)', 0);
24INSERT INTO `incomes_attributes_status` (`incomes_attributes_status_id`, `incomes_attributes_status_name`, `use_as_active`) VALUES (7, 'Неактивно (ассортимент)', 0);
25INSERT INTO `incomes_attributes_status` (`incomes_attributes_status_id`, `incomes_attributes_status_name`, `use_as_active`) VALUES (8, 'Неактивно (СБ)', 0);
26
27ALTER TABLE `incomes_attributes`
28  ADD COLUMN `date_updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `date_added`;
29
30ALTER TABLE `incomes_items_sold`
31  ADD INDEX `date` (`date`),
32  ADD INDEX `last_sold` (`last_sold`);
33
34CREATE TABLE if NOT EXISTS `incomes_attributes_comment_history` (
35  `incomes_attributes_comment_history_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
36  `incomes_attributes_status_history_id` INT(10) UNSIGNED NOT NULL,
37  `object_id` INT(10) UNSIGNED NOT NULL,
38  `customers_id` INT(10) UNSIGNED NOT NULL,
39  `status_id` INT(10) UNSIGNED NOT NULL,
40  `sub_status_id` INT(10) UNSIGNED NULL DEFAULT NULL,
41  `date_added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
42  `comment` TEXT NULL,
43  `is_important` TINYINT(4) NOT NULL DEFAULT '2' COMMENT '1 - true, 2 - false',
44  `flag` TINYINT(4) NOT NULL DEFAULT '1',
45  PRIMARY KEY (`incomes_attributes_comment_history_id`),
46  INDEX `incomes_attributes_status_history_id` (`incomes_attributes_status_history_id`),
47  INDEX `object_id` (`object_id`),
48  INDEX `status_id` (`status_id`),
49  INDEX `sub_status_id` (`sub_status_id`),
50  INDEX `date_added` (`date_added`)
51)
52  COLLATE='utf8_general_ci'
53  ENGINE=InnoDB
54;
55
56CREATE TABLE if NOT EXISTS `incomes_attributes_status_history` (
57  `incomes_attributes_status_history_id` INT(11) NOT NULL AUTO_INCREMENT,
58  `object_id` INT(11) NOT NULL,
59  `customers_id` INT(11) NOT NULL,
60  `status_id` INT(11) NOT NULL,
61  `date_added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
62  PRIMARY KEY (`incomes_attributes_status_history_id`),
63  INDEX `object_id` (`object_id`),
64  INDEX `status_id` (`status_id`),
65  INDEX `date_added` (`date_added`)
66)
67  COLLATE='utf8_general_ci'
68  ENGINE=InnoDB
69;
70
71INSERT IGNORE INTO acl_rules (module, controller, action) VALUES ('partner', 'administration', 'range-suppliers-stock');
72
73INSERT IGNORE INTO acl_settings (acl_roles_id, acl_rules_id, access_type, assert)
74  SELECT aro.acl_roles_id, aru.acl_rules_id, 'ALLOW', ''
75  FROM acl_roles aro, acl_rules aru
76  WHERE aro.acl_roles_id IN (50,66,16) AND aru.module = 'partner' AND aru.controller = 'administration' AND aru.action IN ('range-suppliers-stock');
77
78INSERT IGNORE INTO acl_rules (module, controller, action) VALUES ('partner', 'administration', 'range-suppliers-stock-list');
79
80INSERT IGNORE INTO acl_settings (acl_roles_id, acl_rules_id, access_type, assert)
81  SELECT aro.acl_roles_id, aru.acl_rules_id, 'ALLOW', ''
82  FROM acl_roles aro, acl_rules aru
83  WHERE aro.acl_roles_id IN (50,66,16) AND aru.module = 'partner' AND aru.controller = 'administration' AND aru.action IN ('range-suppliers-stock-list');
84
85INSERT IGNORE INTO acl_rules (module, controller, action) VALUES ('partner', 'administration', 'range-suppliers-stock-filter');
86
87INSERT IGNORE INTO acl_settings (acl_roles_id, acl_rules_id, access_type, assert)
88  SELECT aro.acl_roles_id, aru.acl_rules_id, 'ALLOW', ''
89  FROM acl_roles aro, acl_rules aru
90  WHERE aro.acl_roles_id IN (50,66,16) AND aru.module = 'partner' AND aru.controller = 'administration' AND aru.action IN ('range-suppliers-stock-filter');
91
92INSERT IGNORE INTO acl_rules (module, controller, action) VALUES ('partner', 'administration', 'range-suppliers-stock-history-sold');
93
94INSERT IGNORE INTO acl_settings (acl_roles_id, acl_rules_id, access_type, assert)
95  SELECT aro.acl_roles_id, aru.acl_rules_id, 'ALLOW', ''
96  FROM acl_roles aro, acl_rules aru
97  WHERE aro.acl_roles_id IN (50,66,16) AND aru.module = 'partner' AND aru.controller = 'administration' AND aru.action IN ('range-suppliers-stock-history-sold');
98
99INSERT IGNORE INTO acl_rules (module, controller, action) VALUES ('partner', 'administration', 'range-suppliers-stock-history-status');
100
101INSERT IGNORE INTO acl_settings (acl_roles_id, acl_rules_id, access_type, assert)
102  SELECT aro.acl_roles_id, aru.acl_rules_id, 'ALLOW', ''
103  FROM acl_roles aro, acl_rules aru
104  WHERE aro.acl_roles_id IN (50,66,16) AND aru.module = 'partner' AND aru.controller = 'administration' AND aru.action IN ('range-suppliers-stock-history-status');
105
106INSERT IGNORE INTO acl_rules (module, controller, action) VALUES ('partner', 'administration', 'range-suppliers-stock-history-comments');
107
108INSERT IGNORE INTO acl_settings (acl_roles_id, acl_rules_id, access_type, assert)
109  SELECT aro.acl_roles_id, aru.acl_rules_id, 'ALLOW', ''
110  FROM acl_roles aro, acl_rules aru
111  WHERE aro.acl_roles_id IN (50,66,16) AND aru.module = 'partner' AND aru.controller = 'administration' AND aru.action IN ('range-suppliers-stock-history-comments');
112
113INSERT IGNORE INTO acl_rules (module, controller, action) VALUES ('partner', 'administration', 'range-suppliers-stock-history-price');
114
115INSERT IGNORE INTO acl_settings (acl_roles_id, acl_rules_id, access_type, assert)
116  SELECT aro.acl_roles_id, aru.acl_rules_id, 'ALLOW', ''
117  FROM acl_roles aro, acl_rules aru
118  WHERE aro.acl_roles_id IN (50,66,16) AND aru.module = 'partner' AND aru.controller = 'administration' AND aru.action IN ('range-suppliers-stock-history-price');
119  
120#------------
121
122# 40 секунд на альфе
123ALTER TABLE
124  `suppliers_report_1c`
125MODIFY COLUMN
126  `component` ENUM('categories', 'logistc_requests', 'incomes') NOT NULL DEFAULT 'categories';
127
128# поправим нашу единственную пока поставку
129UPDATE `suppliers_report_1c` SET component = 'incomes' WHERE categories_id = 1;
130
131INSERT IGNORE INTO `mail_templates` (`name`, `code`, `type`, `is_del`) VALUES ('Оповещение о приемке поставки без расхождений', 'send-mail-income-complete-with-differences', 3, 0);
132
133INSERT IGNORE INTO acl_settings (acl_roles_id, acl_rules_id, access_type, assert)
134  SELECT aro.acl_roles_id, aru.acl_rules_id, 'ALLOW', '' FROM acl_roles aro, acl_rules aru
135  WHERE aro.acl_roles_title_en IN ('financialController') AND aru.module = 'call' AND aru.controller = 'shop' AND
136  aru.action IN ('incomes');
137  
138  INSERT IGNORE INTO `acl_rules` (`module`, `controller`, `action`, `acl_rules_fake`) VALUES ('call', 'shop', 'bind-images-to-static-products', 'N');
139INSERT IGNORE INTO acl_settings (acl_roles_id, acl_rules_id, access_type, assert)
140  SELECT aro.acl_roles_id, aru.acl_rules_id, 'ALLOW', '' FROM acl_roles aro, acl_rules aru
141  WHERE aro.acl_roles_title_en IN ('headContent') AND aru.module = 'call' AND aru.controller = 'shop' AND
142        aru.action IN ('bind-images-to-static-products', 'income-edit', 'incomes');
143		
144# -------------- по фотостудии -----------
145
146ALTER TABLE `log_inventory` MODIFY COLUMN `component` ENUM('marking_scan_defects','defects_to_okk','marking_scan_surpluses', 'marking_scan_photography') NOT NULL;
147INSERT INTO `export_data_1c_component` (`export_data_1c_component_name`, `table_name`, `key_field`, `comment`) VALUES ('income_photo_shipment', 'incomes', 'incomes_id', 'Отгрузка в фотостудию');
148ALTER TABLE `stock_zones` MODIFY COLUMN `type` ENUM('city','transit','static','box','box_surplus','sort','surplus','global_sale', 'photo_shipment', 'photo_in', 'photo_out') NOT NULL DEFAULT 'city';
149
150INSERT INTO `stock_zones` (`name`, `is_active`, `type`) VALUES ('Отгрузка в фотостудию', 1, 'photo_shipment');
151INSERT INTO `stock_zones` (`name`, `is_active`, `type`) VALUES ('Фотостудия', 1, 'photo_in');
152INSERT INTO `stock_zones` (`name`, `is_active`, `type`) VALUES ('Принято из фотостудии', 1, 'photo_out');
153
154ALTER TABLE `incomes_items` ADD COLUMN `special_mark` TINYINT(1) NOT NULL DEFAULT 0;
155
156# теперь будем хранить текущий special_mark прямо в incomes_items. Проставим на всякий случай метки фотосъемки для старых записей
157UPDATE
158    incomes_items AS ii1
159    JOIN(
160          SELECT
161            `l`.`incomes_items_id`,
162            lopsm.special_mark
163          FROM `incomes` AS `i`
164            INNER JOIN `incomes_attributes` AS `ia` ON ia.incomes_id = i.incomes_id
165            INNER JOIN `incomes_items` AS `ii` ON ii.incomes_attributes_id = ia.incomes_attributes_id
166            INNER JOIN `products_attributes` AS `pa` ON pa.products_attributes_id = ia.products_attributes_id
167            LEFT JOIN (
168                        SELECT `l`.`object_id` AS `incomes_items_id`, `l`.`special_mark`, MAX(l.log_orders_products_special_marks_id) AS `log_orders_products_special_marks_id`
169                        FROM `log_orders_products_special_marks` AS `l`
170                        WHERE (l.component = 'incomes_items')
171                        GROUP BY `l`.`object_id`
172                      ) AS `l` ON l.incomes_items_id = ii.incomes_items_id
173            LEFT JOIN `log_orders_products_special_marks` AS `lopsm` ON lopsm.log_orders_products_special_marks_id = l.log_orders_products_special_marks_id
174
175          WHERE (lopsm.special_mark = 5)) AS t ON t.incomes_items_id = ii1.incomes_items_id
176SET ii1.special_mark = t.special_mark;
177
178
179DROP TABLE IF EXISTS requests_photography;
180CREATE TABLE `requests_photography` (
181	`requests_photography_id` INT(11) NOT NULL AUTO_INCREMENT,
182	`categories_id` INT(11) NOT NULL,
183	`photo_request_status` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Был ли создан запрос на фотосъёмку',
184	`requests_photography_status_id` TINYINT(3) UNSIGNED NULL DEFAULT NULL COMMENT 'Стадия фотосъёмки',
185	`photo_request_date` DATETIME NULL DEFAULT NULL COMMENT 'Время отправки запроса на фотосъёмку.',
186	`photo_request_items_count` INT(11) NOT NULL DEFAULT '0',
187	`photo_request_file_info_id` INT(10) NULL DEFAULT NULL,
188	`photography_comments` TEXT NULL COMMENT 'Комментарии для фотографа',
189	`photography_file_info_id` INT(11) UNSIGNED NULL DEFAULT NULL COMMENT 'Файл с описью отфотографированных товаров',
190	PRIMARY KEY (`requests_photography_id`),
191	INDEX `categories_id_idx` (`categories_id`),
192	INDEX `photo_request_file_info_id_idx` (`photo_request_file_info_id`),
193	INDEX `requests_photography_status_id_idx` (`requests_photography_status_id`),
194	INDEX `photography_file_info_id_idx` (`photography_file_info_id`),
195	INDEX `photo_request_status_idx` (`photo_request_status`)
196)
197COLLATE='utf8_general_ci'
198ENGINE=InnoDB
199;
200INSERT INTO `requests_photography` SELECT * FROM categories_photography;
201ALTER TABLE `requests_photography` CHANGE COLUMN `categories_id` `object_id` INT(11) NOT NULL, ADD COLUMN `entities_id` INT(11) NOT NULL DEFAULT 33 AFTER requests_photography_id;
202
203CREATE TABLE `requests_photography_status` (
204	`requests_photography_status_id` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
205	`requests_photography_status_name` VARCHAR(255) NOT NULL,
206	PRIMARY KEY (`requests_photography_status_id`)
207)
208COLLATE='utf8_general_ci'
209ENGINE=InnoDB
210;
211INSERT INTO `requests_photography_status` SELECT * FROM categories_photography_status;
212
213# обновим имя таблиы и ключевое поле в компоненте 1с на заявку фотосъемки
214UPDATE `export_data_1c_component` SET `table_name` = 'requests_photography', `key_field` = 'requests_photography_id' WHERE `export_data_1c_component_id` = 15;
215
216ALTER TABLE `incomes` ADD COLUMN `photography_links` VARCHAR(500) NULL DEFAULT NULL, ADD COLUMN `our_photography_links` VARCHAR(500) NULL DEFAULT NULL;