117 lines
4.6 KiB
SQL
117 lines
4.6 KiB
SQL
CREATE TABLE IF NOT EXISTS po_purchase_order_sales_order (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
purchase_order_id BIGINT NOT NULL,
|
||
sales_order_id BIGINT NOT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uq_po_so_link (purchase_order_id, sales_order_id),
|
||
KEY idx_po_so_link_so (sales_order_id),
|
||
CONSTRAINT fk_po_so_link_po FOREIGN KEY (purchase_order_id) REFERENCES po_purchase_order(id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_po_so_link_so FOREIGN KEY (sales_order_id) REFERENCES so_sales_order(id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
SET @idx_wh_stock_lot_item_lot_no_exists := (
|
||
SELECT COUNT(*)
|
||
FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'wh_stock_lot'
|
||
AND INDEX_NAME = 'idx_wh_stock_lot_item_lot_no'
|
||
);
|
||
SET @idx_wh_stock_lot_item_lot_no_sql := IF(
|
||
@idx_wh_stock_lot_item_lot_no_exists = 0,
|
||
'ALTER TABLE wh_stock_lot ADD INDEX idx_wh_stock_lot_item_lot_no (item_id, lot_no)',
|
||
'SELECT ''idx_wh_stock_lot_item_lot_no already exists'''
|
||
);
|
||
PREPARE idx_wh_stock_lot_item_lot_no_stmt FROM @idx_wh_stock_lot_item_lot_no_sql;
|
||
EXECUTE idx_wh_stock_lot_item_lot_no_stmt;
|
||
DEALLOCATE PREPARE idx_wh_stock_lot_item_lot_no_stmt;
|
||
|
||
SET @duplicate_stock_lot_no_groups := (
|
||
SELECT COUNT(*)
|
||
FROM (
|
||
SELECT lot_no
|
||
FROM wh_stock_lot
|
||
WHERE lot_no IS NOT NULL
|
||
GROUP BY lot_no
|
||
HAVING COUNT(*) > 1
|
||
) duplicated_lots
|
||
);
|
||
SET @uq_wh_stock_lot_lot_no_sql := (
|
||
SELECT IF(
|
||
@duplicate_stock_lot_no_groups > 0,
|
||
CONCAT('SIGNAL SQLSTATE ''45000'' SET MESSAGE_TEXT = ''wh_stock_lot.lot_no存在重复，重复批次号组数=', @duplicate_stock_lot_no_groups, '，请先清理后再执行迁移'''),
|
||
IF(
|
||
NOT EXISTS (
|
||
SELECT 1
|
||
FROM (
|
||
SELECT INDEX_NAME
|
||
FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'wh_stock_lot'
|
||
GROUP BY INDEX_NAME
|
||
HAVING MAX(NON_UNIQUE) = 0
|
||
AND COUNT(*) = 1
|
||
AND SUM(CASE WHEN COLUMN_NAME = 'lot_no' THEN 1 ELSE 0 END) = 1
|
||
) existing_unique_lot_no_indexes
|
||
),
|
||
'ALTER TABLE wh_stock_lot ADD UNIQUE KEY uq_wh_stock_lot_lot_no (lot_no)',
|
||
'SELECT ''uq_wh_stock_lot_lot_no already exists'''
|
||
)
|
||
)
|
||
);
|
||
PREPARE uq_wh_stock_lot_lot_no_stmt FROM @uq_wh_stock_lot_lot_no_sql;
|
||
EXECUTE uq_wh_stock_lot_lot_no_stmt;
|
||
DEALLOCATE PREPARE uq_wh_stock_lot_lot_no_stmt;
|
||
|
||
SET @idx_pp_issue_source_lot_exists := (
|
||
SELECT COUNT(DISTINCT INDEX_NAME)
|
||
FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'pp_work_order_material_issue'
|
||
AND COLUMN_NAME = 'source_lot_id'
|
||
);
|
||
SET @idx_pp_issue_source_lot_sql := IF(
|
||
@idx_pp_issue_source_lot_exists = 0,
|
||
'ALTER TABLE pp_work_order_material_issue ADD INDEX idx_pp_issue_source_lot (source_lot_id)',
|
||
'SELECT ''source_lot_id index already exists on pp_work_order_material_issue'''
|
||
);
|
||
PREPARE idx_pp_issue_source_lot_stmt FROM @idx_pp_issue_source_lot_sql;
|
||
EXECUTE idx_pp_issue_source_lot_stmt;
|
||
DEALLOCATE PREPARE idx_pp_issue_source_lot_stmt;
|
||
|
||
SET @uk_material_sub_batch_no_exists := (
|
||
SELECT COUNT(*)
|
||
FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'pp_work_order_material_issue'
|
||
AND INDEX_NAME = 'uk_material_sub_batch_no'
|
||
);
|
||
SET @drop_uk_material_sub_batch_no_sql := IF(
|
||
@uk_material_sub_batch_no_exists > 0,
|
||
'ALTER TABLE pp_work_order_material_issue DROP INDEX uk_material_sub_batch_no',
|
||
'SELECT ''uk_material_sub_batch_no already absent on pp_work_order_material_issue'''
|
||
);
|
||
PREPARE drop_uk_material_sub_batch_no_stmt FROM @drop_uk_material_sub_batch_no_sql;
|
||
EXECUTE drop_uk_material_sub_batch_no_stmt;
|
||
DEALLOCATE PREPARE drop_uk_material_sub_batch_no_stmt;
|
||
|
||
INSERT INTO po_purchase_order_sales_order (purchase_order_id, sales_order_id, created_at, updated_at)
|
||
SELECT DISTINCT po.id, so.id, NOW(), NOW()
|
||
FROM po_purchase_order po
|
||
JOIN po_purchase_order_item poi ON poi.purchase_order_id = po.id
|
||
JOIN mrp_material_demand md ON md.id = poi.source_demand_id
|
||
JOIN so_sales_order_item soi ON soi.id = md.sales_order_item_id
|
||
JOIN so_sales_order so ON so.id = soi.sales_order_id
|
||
WHERE NOT EXISTS (
|
||
SELECT 1
|
||
FROM po_purchase_order_sales_order existing
|
||
WHERE existing.purchase_order_id = po.id
|
||
AND existing.sales_order_id = so.id
|
||
);
|
||
|
||
UPDATE pp_work_order_material_issue issue
|
||
JOIN wh_stock_lot lot ON lot.id = issue.source_lot_id
|
||
SET issue.material_sub_batch_no = lot.lot_no
|
||
WHERE issue.material_sub_batch_no IS NULL
|
||
OR issue.material_sub_batch_no <> lot.lot_no;
|