1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
| -- 新高电力：补齐质检与仓库业务自动编码规则。
| -- 可重复执行：已有规则和分段不会重复插入。
|
| START TRANSACTION;
|
| DROP TEMPORARY TABLE IF EXISTS tmp_xgdl_autocode_rule;
| CREATE TEMPORARY TABLE tmp_xgdl_autocode_rule (
| code VARCHAR(64) COLLATE utf8mb4_unicode_ci PRIMARY KEY,
| name VARCHAR(100) NOT NULL,
| description VARCHAR(255) NOT NULL,
| max_length INT NOT NULL,
| prefix VARCHAR(16) NOT NULL,
| has_date TINYINT NOT NULL,
| serial_length INT NOT NULL
| );
|
| INSERT INTO tmp_xgdl_autocode_rule
| (code, name, description, max_length, prefix, has_date, serial_length)
| VALUES
| ('QC_INDICATOR_CODE', '质检指标编码', '质检指标自动编码', 12, 'QI_', 0, 6),
| ('QC_TEMPLATE_CODE', '质检方案编码', '质检方案自动编码', 12, 'QT_', 0, 6),
| ('QC_NCR_CODE', '不合格品处理单编码', '不合格品处理单自动编码', 16, 'NCR', 1, 4),
| ('QC_OUTSOURCE_TEST_CODE', '委外检测单编码', '委外检测/试验单自动编码', 16, 'WT', 1, 4),
| ('WM_PRODUCT_ISSUE_CODE', '生产领料单编码', '生产领料出库单自动编码', 16, 'PI', 1, 4),
| ('WM_PRODUCT_RECEIPT_CODE', '产品入库单编码', '产品完工入库单自动编码', 16, 'PR', 1, 4);
|
| INSERT INTO mes_md_auto_code_rule
| (code, name, description, max_length, padded, padded_char, padded_method,
| status, remark, creator, create_time, updater, update_time, deleted)
| SELECT t.code, t.name, t.description, t.max_length,
| b'0', NULL, NULL, 0, '质检/仓库必要规则', '1', NOW(), '1', NOW(), b'0'
| FROM tmp_xgdl_autocode_rule t
| WHERE NOT EXISTS (
| SELECT 1 FROM mes_md_auto_code_rule r
| WHERE r.code = t.code AND r.deleted = b'0'
| );
|
| -- 固定前缀。
| INSERT INTO mes_md_auto_code_part
| (rule_id, sort, type, length, date_format, fix_character, serial_start_no,
| serial_step, cycle_flag, cycle_method, remark, creator, create_time, updater, update_time, deleted)
| SELECT r.id, 1, 3, CHAR_LENGTH(t.prefix), NULL, t.prefix, NULL,
| NULL, b'0', NULL, '固定前缀', '1', NOW(), '1', NOW(), b'0'
| FROM tmp_xgdl_autocode_rule t
| JOIN mes_md_auto_code_rule r ON r.code = t.code AND r.deleted = b'0'
| WHERE NOT EXISTS (
| SELECT 1 FROM mes_md_auto_code_part p
| WHERE p.rule_id = r.id AND p.sort = 1 AND p.deleted = b'0'
| );
|
| -- 需要业务日期的单据使用 yyyyMMdd。
| INSERT INTO mes_md_auto_code_part
| (rule_id, sort, type, length, date_format, fix_character, serial_start_no,
| serial_step, cycle_flag, cycle_method, remark, creator, create_time, updater, update_time, deleted)
| SELECT r.id, 2, 2, 8, 'yyyyMMdd', NULL, NULL,
| NULL, b'0', NULL, '业务日期', '1', NOW(), '1', NOW(), b'0'
| FROM tmp_xgdl_autocode_rule t
| JOIN mes_md_auto_code_rule r ON r.code = t.code AND r.deleted = b'0'
| WHERE t.has_date = 1
| AND NOT EXISTS (
| SELECT 1 FROM mes_md_auto_code_part p
| WHERE p.rule_id = r.id AND p.sort = 2 AND p.deleted = b'0'
| );
|
| -- 有日期的流水号按日循环；基础资料编码使用连续流水号。
| INSERT INTO mes_md_auto_code_part
| (rule_id, sort, type, length, date_format, fix_character, serial_start_no,
| serial_step, cycle_flag, cycle_method, remark, creator, create_time, updater, update_time, deleted)
| SELECT r.id, IF(t.has_date = 1, 3, 2), 4, t.serial_length, NULL, NULL, 1,
| 1, IF(t.has_date = 1, b'1', b'0'), IF(t.has_date = 1, 3, NULL),
| IF(t.has_date = 1, '按日循环流水号', '连续流水号'),
| '1', NOW(), '1', NOW(), b'0'
| FROM tmp_xgdl_autocode_rule t
| JOIN mes_md_auto_code_rule r ON r.code = t.code AND r.deleted = b'0'
| WHERE NOT EXISTS (
| SELECT 1 FROM mes_md_auto_code_part p
| WHERE p.rule_id = r.id
| AND p.sort = IF(t.has_date = 1, 3, 2)
| AND p.deleted = b'0'
| );
|
| DROP TEMPORARY TABLE tmp_xgdl_autocode_rule;
|
| COMMIT;
|
|
