-- 在 after_sale_collaboration_20261105.sql 后执行。仅保存财务待审核草稿,不产生付款或凭证。 SET @sql_amount := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='after_sale_collaboration' AND column_name='amount')=0, 'ALTER TABLE after_sale_collaboration ADD COLUMN amount DECIMAL(18,2) NULL COMMENT ''金额(元)''', 'SELECT 1'); PREPARE stmt_amount FROM @sql_amount; EXECUTE stmt_amount; DEALLOCATE PREPARE stmt_amount; SET @sql_direction := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='after_sale_collaboration' AND column_name='direction')=0, 'ALTER TABLE after_sale_collaboration ADD COLUMN direction VARCHAR(16) NULL COMMENT ''RECEIVABLE、PAYABLE、INTERNAL''', 'SELECT 1'); PREPARE stmt_direction FROM @sql_direction; EXECUTE stmt_direction; DEALLOCATE PREPARE stmt_direction; SET @sql_party := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='after_sale_collaboration' AND column_name='party_name')=0, 'ALTER TABLE after_sale_collaboration ADD COLUMN party_name VARCHAR(200) NULL COMMENT ''往来方名称''', 'SELECT 1'); PREPARE stmt_party FROM @sql_party; EXECUTE stmt_party; DEALLOCATE PREPARE stmt_party; SET @sql_finance_id := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='after_sale_collaboration' AND column_name='finance_draft_id')=0, 'ALTER TABLE after_sale_collaboration ADD COLUMN finance_draft_id BIGINT NULL COMMENT ''财务草稿编号''', 'SELECT 1'); PREPARE stmt_finance_id FROM @sql_finance_id; EXECUTE stmt_finance_id; DEALLOCATE PREPARE stmt_finance_id; SET @sql_business_actor := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='after_sale_collaboration' AND column_name='business_approver_id')=0, 'ALTER TABLE after_sale_collaboration ADD COLUMN business_approver_id BIGINT NULL COMMENT ''业务审核人''', 'SELECT 1'); PREPARE stmt_business_actor FROM @sql_business_actor; EXECUTE stmt_business_actor; DEALLOCATE PREPARE stmt_business_actor; SET @sql_business_time := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='after_sale_collaboration' AND column_name='business_approval_time')=0, 'ALTER TABLE after_sale_collaboration ADD COLUMN business_approval_time DATETIME NULL COMMENT ''业务审核时间''', 'SELECT 1'); PREPARE stmt_business_time FROM @sql_business_time; EXECUTE stmt_business_time; DEALLOCATE PREPARE stmt_business_time; SET @sql_business_remark := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='after_sale_collaboration' AND column_name='business_remark')=0, 'ALTER TABLE after_sale_collaboration ADD COLUMN business_remark VARCHAR(1000) NULL COMMENT ''业务审核意见''', 'SELECT 1'); PREPARE stmt_business_remark FROM @sql_business_remark; EXECUTE stmt_business_remark; DEALLOCATE PREPARE stmt_business_remark; SET @sql_previous := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='after_sale_collaboration' AND column_name='previous_id')=0, 'ALTER TABLE after_sale_collaboration ADD COLUMN previous_id BIGINT NULL COMMENT ''上一次退回的协同事项''', 'SELECT 1'); PREPARE stmt_previous FROM @sql_previous; EXECUTE stmt_previous; DEALLOCATE PREPARE stmt_previous; SET @sql_previous_index := IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='after_sale_collaboration' AND index_name='uq_collaboration_previous')=0, 'ALTER TABLE after_sale_collaboration ADD UNIQUE KEY uq_collaboration_previous (previous_id)', 'SELECT 1'); PREPARE stmt_previous_index FROM @sql_previous_index; EXECUTE stmt_previous_index; DEALLOCATE PREPARE stmt_previous_index; CREATE TABLE IF NOT EXISTS erp_after_sale_finance_draft ( id BIGINT NOT NULL AUTO_INCREMENT, collaboration_id BIGINT NOT NULL COMMENT '售后协同事项', ticket_id BIGINT NOT NULL COMMENT '售后工单', category VARCHAR(32) NOT NULL COMMENT '协同类型', direction VARCHAR(16) NOT NULL COMMENT 'RECEIVABLE、PAYABLE、INTERNAL', amount DECIMAL(18,2) NOT NULL COMMENT '金额(元)', party_name VARCHAR(200) DEFAULT NULL COMMENT '往来方名称', description VARCHAR(2000) NOT NULL COMMENT '业务说明', business_approver_id BIGINT NOT NULL COMMENT '业务确认人', status TINYINT NOT NULL DEFAULT 0 COMMENT '0 待财务审核,1 审核通过,2 退回', reviewer_id BIGINT DEFAULT NULL, review_remark VARCHAR(1000) DEFAULT NULL, review_time DATETIME DEFAULT NULL, 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), UNIQUE KEY uq_as_finance_collaboration (collaboration_id), KEY idx_as_finance_status (status, deleted), KEY idx_as_finance_ticket (ticket_id, deleted) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后财务审核草稿'; -- 关联主数据,同时保留名称快照;历史记录允许为空。 SET @sql_party_type := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='after_sale_collaboration' AND column_name='party_type')=0, 'ALTER TABLE after_sale_collaboration ADD COLUMN party_type VARCHAR(16) NULL', 'SELECT 1'); PREPARE stmt_party_type FROM @sql_party_type; EXECUTE stmt_party_type; DEALLOCATE PREPARE stmt_party_type; SET @sql_party_id := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='after_sale_collaboration' AND column_name='party_id')=0, 'ALTER TABLE after_sale_collaboration ADD COLUMN party_id BIGINT NULL', 'SELECT 1'); PREPARE stmt_party_id FROM @sql_party_id; EXECUTE stmt_party_id; DEALLOCATE PREPARE stmt_party_id; SET @sql_draft_party_type := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='erp_after_sale_finance_draft' AND column_name='party_type')=0, 'ALTER TABLE erp_after_sale_finance_draft ADD COLUMN party_type VARCHAR(16) NULL', 'SELECT 1'); PREPARE stmt_draft_party_type FROM @sql_draft_party_type; EXECUTE stmt_draft_party_type; DEALLOCATE PREPARE stmt_draft_party_type; SET @sql_draft_party_id := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='erp_after_sale_finance_draft' AND column_name='party_id')=0, 'ALTER TABLE erp_after_sale_finance_draft ADD COLUMN party_id BIGINT NULL', 'SELECT 1'); PREPARE stmt_draft_party_id FROM @sql_draft_party_id; EXECUTE stmt_draft_party_id; DEALLOCATE PREPARE stmt_draft_party_id;