81 lines
3.8 KiB
MySQL
81 lines
3.8 KiB
MySQL
|
-- 遂宁成品仓 / 主存区完整仓位初始化。
|
|||
|
|
-- 编码规则：层 + 两位块号 - 两位排号 - 两位列号，例如 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
|
|||
|
|
);
|
|||
|
|
|