-- 托盘保存品种；仅从有效袋码中唯一且与已有批次兼容的品种回填。
|
SET @pallet_item_exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema=DATABASE() AND table_name='mes_pro_pallet' AND column_name='item_id');
|
SET @pallet_item_ddl = IF(@pallet_item_exists=0,
|
'ALTER TABLE mes_pro_pallet ADD COLUMN item_id BIGINT DEFAULT NULL COMMENT ''托盘品种编号'' AFTER temp_code', 'SELECT 1');
|
PREPARE pallet_item_stmt FROM @pallet_item_ddl;
|
EXECUTE pallet_item_stmt;
|
DEALLOCATE PREPARE pallet_item_stmt;
|
|
UPDATE mes_pro_pallet p
|
JOIN (SELECT pallet_id, MIN(item_id) item_id FROM mes_pro_bag_code
|
WHERE deleted=0 AND status<>30 AND pallet_id IS NOT NULL GROUP BY pallet_id
|
HAVING COUNT(DISTINCT item_id)=1 AND COUNT(*)=COUNT(item_id)) b ON b.pallet_id=p.id
|
LEFT JOIN mes_pro_batch ba ON ba.id=p.batch_id AND ba.deleted=0
|
SET p.item_id=b.item_id
|
WHERE p.deleted=0 AND p.item_id IS NULL AND (ba.item_id IS NULL OR ba.item_id=b.item_id);
|
|
UPDATE mes_pro_batch ba
|
JOIN (SELECT batch_id,MIN(item_id) item_id FROM mes_pro_bag_code
|
WHERE deleted=0 AND status<>30 AND batch_id IS NOT NULL GROUP BY batch_id
|
HAVING COUNT(DISTINCT item_id)=1 AND COUNT(*)=COUNT(item_id)) b ON b.batch_id=ba.id
|
SET ba.item_id=b.item_id
|
WHERE ba.deleted=0 AND ba.item_id IS NULL
|
AND NOT EXISTS (SELECT 1 FROM mes_trace_integrity_record r WHERE r.biz_type='BATCH' AND r.biz_id=ba.id AND r.deleted=0)
|
AND NOT EXISTS (SELECT 1 FROM mes_pro_pallet p WHERE p.batch_id=ba.id AND p.deleted=0 AND p.item_id IS NOT NULL AND p.item_id<>b.item_id);
|
