-- 南通苏珩线缆业务需求扩展 -- 执行前请备份测试库。脚本使用信息_schema判断字段,支持重复执行。 SET @db_name = DATABASE(); -- 财务单据与采购、销售、仓储单据关联 SET @sql = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE erp_purchase_invoice ADD COLUMN arrival_notice_id BIGINT NULL COMMENT ''关联入库单'' AFTER purchase_order_id', 'SELECT 1') FROM information_schema.columns WHERE table_schema = @db_name AND table_name = 'erp_purchase_invoice' AND column_name = 'arrival_notice_id'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE crm_invoice ADD COLUMN sale_order_id BIGINT NULL COMMENT ''关联销售订单'' AFTER contract_id', 'SELECT 1') FROM information_schema.columns WHERE table_schema = @db_name AND table_name = 'crm_invoice' AND column_name = 'sale_order_id'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE crm_invoice ADD COLUMN sale_out_id BIGINT NULL COMMENT ''关联出库单'' AFTER sale_order_id', 'SELECT 1') FROM information_schema.columns WHERE table_schema = @db_name AND table_name = 'crm_invoice' AND column_name = 'sale_out_id'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE crm_invoice ADD COLUMN contract_amount DECIMAL(18,2) NULL COMMENT ''合同金额'' AFTER price', 'SELECT 1') FROM information_schema.columns WHERE table_schema = @db_name AND table_name = 'crm_invoice' AND column_name = 'contract_amount'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE crm_invoice ADD COLUMN prepayment_amount DECIMAL(18,2) NULL COMMENT ''预付款金额'' AFTER contract_amount', 'SELECT 1') FROM information_schema.columns WHERE table_schema = @db_name AND table_name = 'crm_invoice' AND column_name = 'prepayment_amount'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE crm_invoice ADD COLUMN delivery_amount DECIMAL(18,2) NULL COMMENT ''到货款金额'' AFTER prepayment_amount', 'SELECT 1') FROM information_schema.columns WHERE table_schema = @db_name AND table_name = 'crm_invoice' AND column_name = 'delivery_amount'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE crm_invoice ADD COLUMN acceptance_amount DECIMAL(18,2) NULL COMMENT ''验收款金额'' AFTER delivery_amount', 'SELECT 1') FROM information_schema.columns WHERE table_schema = @db_name AND table_name = 'crm_invoice' AND column_name = 'acceptance_amount'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 每个付款节点的时间 CREATE TABLE IF NOT EXISTS crm_invoice_payment_node ( id BIGINT NOT NULL AUTO_INCREMENT, invoice_id BIGINT NOT NULL COMMENT '开票申请/发票编号', node_type TINYINT NOT NULL COMMENT '1预付款 2到货款 3验收款', amount DECIMAL(18,2) NOT NULL DEFAULT 0, node_time DATETIME NULL, remark VARCHAR(500) DEFAULT '', creator VARCHAR(64) DEFAULT '', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updater VARCHAR(64) DEFAULT '', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted BIT(1) NOT NULL DEFAULT b'0', PRIMARY KEY (id), KEY idx_invoice_node (invoice_id, node_type, deleted) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='销售发票付款节点'; -- 售后反馈、处理结果和流程留痕 SET @sql = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE after_sale_ticket ADD COLUMN feedback_description TEXT NULL COMMENT ''客户诉求反馈描述'' AFTER issue_description', 'SELECT 1') FROM information_schema.columns WHERE table_schema = @db_name AND table_name = 'after_sale_ticket' AND column_name = 'feedback_description'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE after_sale_ticket ADD COLUMN result_description TEXT NULL COMMENT ''处理结果描述'' AFTER feedback_description', 'SELECT 1') FROM information_schema.columns WHERE table_schema = @db_name AND table_name = 'after_sale_ticket' AND column_name = 'result_description'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; CREATE TABLE IF NOT EXISTS after_sale_ticket_process ( id BIGINT NOT NULL AUTO_INCREMENT, ticket_id BIGINT NOT NULL, process_type VARCHAR(32) NOT NULL COMMENT '受理、处理、审核、关闭等', process_status TINYINT NULL, process_description TEXT, attachment_count INT NOT NULL DEFAULT 0, operator_user_id BIGINT NULL, process_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, creator VARCHAR(64) DEFAULT '', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updater VARCHAR(64) DEFAULT '', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted BIT(1) NOT NULL DEFAULT b'0', PRIMARY KEY (id), KEY idx_ticket_process (ticket_id, process_time, deleted) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='售后工单流转记录'; -- 离职交接:同时记录员工、用户和数据交接状态 SET @sql = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE hrm_resignation_application ADD COLUMN handover_employee_id BIGINT NULL COMMENT ''交接员工ID'' AFTER handover_user_id', 'SELECT 1') FROM information_schema.columns WHERE table_schema = @db_name AND table_name = 'hrm_resignation_application' AND column_name = 'handover_employee_id'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE hrm_resignation_application ADD COLUMN handover_data_status TINYINT NOT NULL DEFAULT 0 COMMENT ''用户数据交接状态:0未开始 10进行中 20已完成'' AFTER handover_status', 'SELECT 1') FROM information_schema.columns WHERE table_schema = @db_name AND table_name = 'hrm_resignation_application' AND column_name = 'handover_data_status'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 设备台账已有 code 字段;计量器具已有 Excel 导入表单,公告附件使用系统存储附件表绑定。