-- =============================================
|
-- 采购台账改造 + 销售订单收款日期
|
-- 目标库：mom-ntsh（MySQL 8.4）
|
-- 说明：
|
-- 1. 脚本可重复执行：列用 information_schema 预检查 + PREPARE，字典/菜单用先查后插
|
-- 2. 不包含 tenant_id（本项目不使用多租户）
|
-- 3. 历史采购单 payment_date 允许为空，编辑时由前端要求补填
|
-- =============================================
|
|
-- ---------------------------------------------
|
-- 一、erp_purchase_order 采购台账表头字段
|
-- ---------------------------------------------
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'erp_purchase_order' AND column_name = 'purchase_type');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE erp_purchase_order ADD COLUMN purchase_type TINYINT NULL COMMENT ''采购类型：1生产 2办公用品 3其他'' AFTER remark',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'erp_purchase_order' AND column_name = 'contract_no');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE erp_purchase_order ADD COLUMN contract_no VARCHAR(64) NULL COMMENT ''合同号（客户手工输入，不自动生成）'' AFTER purchase_type',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'erp_purchase_order' AND column_name = 'project_name');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE erp_purchase_order ADD COLUMN project_name VARCHAR(255) NULL COMMENT ''项目名称（非必填）'' AFTER contract_no',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'erp_purchase_order' AND column_name = 'payment_date');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE erp_purchase_order ADD COLUMN payment_date DATETIME NULL COMMENT ''付款日期'' AFTER project_name',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'erp_purchase_order' AND column_name = 'payment_method');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE erp_purchase_order ADD COLUMN payment_method TINYINT NULL COMMENT ''付款方式：1电汇 2汇票 3现金 4手机支付'' AFTER payment_date',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------------------------------------------
|
-- 二、erp_purchase_order_items 产品编号（MDM 物料编码快照）
|
-- ---------------------------------------------
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'erp_purchase_order_items' AND column_name = 'product_code');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE erp_purchase_order_items ADD COLUMN product_code VARCHAR(64) NULL COMMENT ''产品编号（MDM 物料编码快照）'' AFTER product_id',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------------------------------------------
|
-- 三、erp_sale_order 收款日期
|
-- ---------------------------------------------
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'erp_sale_order' AND column_name = 'receipt_date');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE erp_sale_order ADD COLUMN receipt_date DATETIME NULL COMMENT ''收款日期'' AFTER order_time',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------------------------------------------
|
-- 四、字典：采购类型、付款方式
|
-- ---------------------------------------------
|
|
INSERT INTO system_dict_type (name, type, status, remark)
|
SELECT 'ERP 采购类型', 'erp_purchase_type', 0, '采购台账-采购类型：1生产 2办公用品 3其他'
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM system_dict_type WHERE type = 'erp_purchase_type' AND deleted = 0);
|
|
INSERT INTO system_dict_type (name, type, status, remark)
|
SELECT 'ERP 付款方式', 'erp_payment_method', 0, '采购台账-付款方式：1电汇 2汇票 3现金 4手机支付'
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM system_dict_type WHERE type = 'erp_payment_method' AND deleted = 0);
|
|
INSERT INTO system_dict_data (sort, label, value, dict_type, status, color_type, css_class)
|
SELECT 0, '生产', '1', 'erp_purchase_type', 0, '', ''
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'erp_purchase_type' AND value = '1' AND deleted = 0);
|
INSERT INTO system_dict_data (sort, label, value, dict_type, status, color_type, css_class)
|
SELECT 1, '办公用品', '2', 'erp_purchase_type', 0, '', ''
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'erp_purchase_type' AND value = '2' AND deleted = 0);
|
INSERT INTO system_dict_data (sort, label, value, dict_type, status, color_type, css_class)
|
SELECT 2, '其他', '3', 'erp_purchase_type', 0, '', ''
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'erp_purchase_type' AND value = '3' AND deleted = 0);
|
|
INSERT INTO system_dict_data (sort, label, value, dict_type, status, color_type, css_class)
|
SELECT 0, '电汇', '1', 'erp_payment_method', 0, '', ''
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'erp_payment_method' AND value = '1' AND deleted = 0);
|
INSERT INTO system_dict_data (sort, label, value, dict_type, status, color_type, css_class)
|
SELECT 1, '汇票', '2', 'erp_payment_method', 0, '', ''
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'erp_payment_method' AND value = '2' AND deleted = 0);
|
INSERT INTO system_dict_data (sort, label, value, dict_type, status, color_type, css_class)
|
SELECT 2, '现金', '3', 'erp_payment_method', 0, '', ''
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'erp_payment_method' AND value = '3' AND deleted = 0);
|
INSERT INTO system_dict_data (sort, label, value, dict_type, status, color_type, css_class)
|
SELECT 3, '手机支付', '4', 'erp_payment_method', 0, '', ''
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'erp_payment_method' AND value = '4' AND deleted = 0);
|
|
-- ---------------------------------------------
|
-- 五、菜单名称：采购订单 -> 采购台账（权限串保持不变，避免角色权限失效）
|
-- ---------------------------------------------
|
|
UPDATE system_menu SET name = '采购台账' WHERE id = 2666;
|
UPDATE system_menu SET name = '采购台账创建' WHERE id = 2668;
|
UPDATE system_menu SET name = '采购台账更新' WHERE id = 2669;
|
UPDATE system_menu SET name = '采购台账删除' WHERE id = 2670;
|
UPDATE system_menu SET name = '采购台账导出' WHERE id = 2671;
|
UPDATE system_menu SET name = '采购台账审批' WHERE id = 2672;
|
