-- ============================================= -- 采购台账改造 + 销售订单收款日期 -- 目标库: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;