-- ===================================================================== -- 宁夏万通需求:开票管理支持关联销售订单,开票金额按销售订单金额校验 -- 2026-11-04 -- 说明:crm_invoice 新增 sale_order_id(销售订单编号)、sale_order_no(销售订单号,冗余展示) -- 这两个字段都可为空:不关联销售订单时,行为与改造前完全一致(按合同金额校验) -- ===================================================================== -- 1. sale_order_id SET @exists_sale_order_id = ( SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'crm_invoice' AND column_name = 'sale_order_id' ); SET @ddl = IF(@exists_sale_order_id = 0, 'ALTER TABLE crm_invoice ADD COLUMN sale_order_id BIGINT NULL COMMENT ''销售订单编号,关联 erp_sale_order.id'' AFTER contract_id', 'SELECT 1'); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 2. sale_order_no SET @exists_sale_order_no = ( SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'crm_invoice' AND column_name = 'sale_order_no' ); SET @ddl = IF(@exists_sale_order_no = 0, 'ALTER TABLE crm_invoice ADD COLUMN sale_order_no VARCHAR(64) NULL COMMENT ''销售订单号,冗余便于展示'' AFTER sale_order_id', 'SELECT 1'); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 3. 索引(按订单查询已开票金额) SET @exists_idx = ( SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'crm_invoice' AND index_name = 'idx_crm_invoice_sale_order_id' ); SET @ddl = IF(@exists_idx = 0, 'ALTER TABLE crm_invoice ADD INDEX idx_crm_invoice_sale_order_id (sale_order_id)', 'SELECT 1'); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;