81 lines
3.8 KiB
SQL
81 lines
3.8 KiB
SQL
-- 遂宁成品仓 / 主存区完整仓位初始化。
|
||
-- 编码规则：层 + 两位块号 - 两位排号 - 两位列号，例如 203-04-05。
|
||
-- 当前布局：12 块、3 层，每层 364 个仓位，共 1092 个仓位。
|
||
-- placement_type：01 双通、02 左通、03 右通。
|
||
-- 使用 NOT EXISTS 保证重复执行时不重复插入相同 struct_id / struct_code。
|
||
|
||
INSERT INTO wms_structattr
|
||
(struct_id, struct_code, struct_name, simple_name,
|
||
sect_id, sect_code, sect_name,
|
||
stor_id, stor_code, stor_name,
|
||
is_tempstruct, row_num, col_num, layer_num, block_num, placement_type,
|
||
creator, create_time, updater, update_time, deleted, is_used,
|
||
storagevehicle_qty, lock_type, remark)
|
||
SELECT code_data.struct_code,
|
||
code_data.struct_code,
|
||
CONCAT('主存区-', LPAD(code_data.block_num, 2, '0'), '块-',
|
||
LPAD(code_data.row_num, 2, '0'), '排-',
|
||
LPAD(code_data.col_num, 2, '0'), '列-',
|
||
code_data.layer_num, '层'),
|
||
code_data.struct_code,
|
||
'2078406241102450689', 'ZC01', '主存区',
|
||
'2078405279260463106', 'AC01', '遂宁成品仓',
|
||
'0', code_data.row_num, code_data.col_num, code_data.layer_num,
|
||
code_data.block_num, code_data.placement_type,
|
||
'1', NOW(), '1', NOW(), '0', 1,
|
||
0, '1', '根据仓库CAD布局初始化'
|
||
FROM (
|
||
SELECT CONCAT(layer_num, LPAD(block_num, 2, '0'), '-',
|
||
LPAD(row_num, 2, '0'), '-', LPAD(col_num, 2, '0')) AS struct_code,
|
||
block_num, row_num, col_num, layer_num, placement_type
|
||
FROM (
|
||
SELECT layout.block_num,
|
||
row_seq.num AS row_num,
|
||
col_seq.num AS col_num,
|
||
layer_seq.layer_num,
|
||
layout.placement_type
|
||
FROM (
|
||
SELECT 1 block_num, 1 row_start, 6 row_end, 7 col_count, '02' placement_type
|
||
UNION ALL SELECT 2, 1, 3, 4, '02'
|
||
UNION ALL SELECT 2, 4, 7, 2, '02'
|
||
UNION ALL SELECT 3, 1, 2, 4, '02'
|
||
UNION ALL SELECT 3, 3, 6, 2, '02'
|
||
UNION ALL SELECT 3, 7, 7, 1, '02'
|
||
UNION ALL SELECT 3, 8, 8, 2, '02'
|
||
UNION ALL SELECT 4, 1, 11, 2, '01'
|
||
UNION ALL SELECT 5, 1, 8, 2, '01'
|
||
UNION ALL SELECT 6, 1, 8, 2, '01'
|
||
UNION ALL SELECT 6, 9, 17, 1, '01'
|
||
UNION ALL SELECT 7, 1, 11, 5, '01'
|
||
UNION ALL SELECT 8, 1, 8, 5, '01'
|
||
UNION ALL SELECT 9, 1, 16, 5, '01'
|
||
UNION ALL SELECT 10, 1, 7, 1, '03'
|
||
UNION ALL SELECT 11, 1, 27, 1, '03'
|
||
UNION ALL SELECT 12, 1, 11, 1, '03'
|
||
) layout
|
||
INNER JOIN (
|
||
SELECT 1 num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
|
||
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
|
||
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
|
||
UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16
|
||
UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
|
||
UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
|
||
UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27
|
||
) row_seq ON row_seq.num BETWEEN layout.row_start AND layout.row_end
|
||
INNER JOIN (
|
||
SELECT 1 num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
|
||
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
|
||
) col_seq ON col_seq.num <= layout.col_count
|
||
CROSS JOIN (
|
||
SELECT 1 layer_num UNION ALL SELECT 2 UNION ALL SELECT 3
|
||
) layer_seq
|
||
) expanded_layout
|
||
) code_data
|
||
WHERE NOT EXISTS (
|
||
SELECT 1
|
||
FROM wms_structattr existing
|
||
WHERE existing.struct_id = code_data.struct_code
|
||
OR existing.struct_code = code_data.struct_code
|
||
);
|
||
|