· 6 years ago · Dec 12, 2019, 12:36 PM
1
2
3INSERT IGNORE INTO entities_groups (`entities_groups_title`, `entities_groups_description`) VALUES ('Статусы', 'Статусы');
4SELECT @entities_groups_id := eg.entities_groups_id FROM entities_groups AS eg WHERE entities_groups_title = 'Статусы';
5
6INSERT IGNORE INTO entities (`entities_groups_id`, `entities_title`, `entities_name`, `entities_description`) VALUES (@entities_groups_id, 'Статусы', 'statuses', 'Таблицы со статусами разных сущностей');
7
8
9SELECT @entities_id := e.entities_id FROM entities AS e WHERE e.entities_name = 'statuses';
10
11INSERT IGNORE INTO entities_types (`entities_id`, `entities_types_title`, `entities_types_name`) VALUES (@entities_id, 'Универсальные статусы', 'statuses');
12INSERT IGNORE INTO entities_types (`entities_id`, `entities_types_title`, `entities_types_name`) VALUES (@entities_id, 'Статусы акций', 'categories_status');
13INSERT IGNORE INTO entities_types (`entities_id`, `entities_types_title`, `entities_types_name`) VALUES (@entities_id, 'Статусы пользователей', 'customers_status');
14INSERT IGNORE INTO entities_types (`entities_id`, `entities_types_title`, `entities_types_name`) VALUES (@entities_id, 'Статусы заказов', 'orders_status');
15
16
17CREATE TABLE IF NOT EXISTS statuses_groups (
18 `statuses_groups_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
19 `entities_types_id` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Таблица статусов',
20 `statuses_groups_name` VARCHAR(100) NOT NULL DEFAULT '',
21 `statuses_groups_title` VARCHAR(100) NOT NULL DEFAULT '',
22 PRIMARY KEY (`statuses_groups_id`),
23 UNIQUE INDEX `statuses_groups_name` (`statuses_groups_name`)
24)
25COMMENT='Произвольные группы статусов, которые используются в statuses_to_statuses_groups'
26COLLATE='utf8_general_ci'
27;
28
29CREATE TABLE `statuses_groups` (
30 `statuses_groups_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
31 `entities_types_id` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Таблица статусов',
32 `statuses_groups_name` VARCHAR(100) NOT NULL DEFAULT '',
33 `statuses_groups_title` VARCHAR(100) NOT NULL DEFAULT '',
34 `statuses_groups_sets_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Набор групп статусов',
35 PRIMARY KEY (`statuses_groups_id`),
36 UNIQUE INDEX `statuses_groups_name` (`statuses_groups_name`)
37)
38COMMENT='Произвольные группы статусов, которые используются в statuses_to_statuses_groups'
39COLLATE='utf8_general_ci'
40ENGINE=InnoDB
41;
42
43
44CREATE TABLE `statuses_groups_sets` (
45 `statuses_groups_sets_id` INT(11) NOT NULL AUTO_INCREMENT,
46 `statuses_groups_sets_name` VARCHAR(100) NOT NULL,
47 `statuses_groups_sets_title` VARCHAR(250) NOT NULL,
48 PRIMARY KEY (`statuses_groups_sets_id`),
49 UNIQUE INDEX `statuses_groups_sets_name` (`statuses_groups_sets_name`)
50)
51COMMENT='Произвольные наборы групп статусов для объединения групп'
52COLLATE='utf8_general_ci'
53ENGINE=InnoDB
54;
55
56
57SELECT @entities_types_id := et.entities_types_id FROM entities_types AS et WHERE et.entities_types_name = 'statuses';
58
59INSERT IGNORE INTO statuses_groups (`entities_types_id`, `statuses_groups_name`, `statuses_groups_title`) VALUES (@entities_types_id, 'suppliers_statuses_all', 'Все статусы поставщика');
60INSERT IGNORE INTO statuses_groups (`entities_types_id`, `statuses_groups_name`, `statuses_groups_title`) VALUES (@entities_types_id, 'suppliers_contracts_statuses_all', 'Все статусы договоров поставщика');
61
62INSERT IGNORE INTO statuses_groups (`entities_types_id`, `statuses_groups_name`, `statuses_groups_title`) VALUES (@entities_types_id, 'digital_documents_update_gtd', 'Статусы документа ЭДО в которых обновляем ГТД');
63INSERT IGNORE INTO statuses_groups (`entities_types_id`, `statuses_groups_name`, `statuses_groups_title`) VALUES (@entities_types_id, 'digital_documents_update_price', 'Статусы документа ЭДО в которых обновляем цены, НДС');
64INSERT IGNORE INTO statuses_groups (`entities_types_id`, `statuses_groups_name`, `statuses_groups_title`) VALUES (@entities_types_id, 'digital_documents_recalculate', 'Статусы документа ЭДО в которых пересчитываем недостачи');
65
66INSERT IGNORE INTO statuses_to_statuses_groups (`statuses_groups_id`, `statuses_id`)
67SELECT DISTINCT
68 sg.statuses_groups_id
69 , s.suppliers_status
70FROM suppliers AS s
71 JOIN statuses_groups AS sg ON sg.statuses_groups_name = 'suppliers_statuses_all';
72
73
74INSERT IGNORE INTO statuses_to_statuses_groups (`statuses_groups_id`, `statuses_id`)
75SELECT DISTINCT
76 sg.statuses_groups_id
77 , s.suppliers_contracts_status
78FROM suppliers_contracts AS s
79 JOIN statuses_groups AS sg ON sg.statuses_groups_name = 'suppliers_contracts_statuses_all';