-- =====================================================
|
-- 课题经费拨付对接财务报销 - 开发库增量脚本
|
--
|
-- 背景：每条 `research_fund_record` 拨款记录改为自动生成并提交一张财务报销单
|
-- （finance_reimbursement，1:1、金额一致），财务审批结果由
|
-- ResearchFundStatusListener 回写本台账状态。
|
--
|
-- 执行库：脚本用 DATABASE() 判定，与库名无关，故可对任意目标库执行。
|
-- 2026-09-29 已在 application-local.yaml 指向的 mom-jssm 执行并核对通过；
|
-- 其余环境（dev/test 指向 ruoyi-vue-pro，yzfx/wtxc/zsjc/chdl/kyzn 指向各自 mom-* 库）按需另跑。
|
-- 本脚本幂等，可重复执行。
|
-- 内容：① research_fund_record 新增 4 列 + status 注释更新 + 索引
|
-- ② system_dict_data 的 research_fund_status 补「报销审批中/已驳回」
|
-- 注意：本脚本不含 tenant_id（项目不使用多租户模式）。
|
-- =====================================================
|
|
-- ----- 1. research_fund_record 新增 4 列（ALTER TABLE ADD COLUMN 不支持 IF NOT EXISTS，用 information_schema 守卫） -----
|
|
SET @c = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'research_fund_record' AND column_name = 'reimbursement_id');
|
SET @sql = IF(@c = 0,
|
'ALTER TABLE research_fund_record ADD COLUMN reimbursement_id bigint DEFAULT NULL COMMENT ''关联财务报销单 ID（finance_reimbursement.id）'' AFTER status',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @c = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'research_fund_record' AND column_name = 'reimbursement_no');
|
SET @sql = IF(@c = 0,
|
'ALTER TABLE research_fund_record ADD COLUMN reimbursement_no varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '''' COMMENT ''关联财务报销单号'' AFTER reimbursement_id',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @c = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'research_fund_record' AND column_name = 'reject_reason');
|
SET @sql = IF(@c = 0,
|
'ALTER TABLE research_fund_record ADD COLUMN reject_reason varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '''' COMMENT ''报销驳回原因（BPM 回写）'' AFTER reimbursement_no',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @c = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'research_fund_record' AND column_name = 'reject_time');
|
SET @sql = IF(@c = 0,
|
'ALTER TABLE research_fund_record ADD COLUMN reject_time datetime DEFAULT NULL COMMENT ''报销驳回时间'' AFTER reject_reason',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- status 列注释更新为「0-待拨付 15-报销审批中 20-已拨付 30-已驳回」
|
ALTER TABLE research_fund_record
|
MODIFY COLUMN status tinyint NOT NULL DEFAULT '0' COMMENT '状态：0-待拨付 15-报销审批中 20-已拨付 30-已驳回';
|
|
-- 按报销单号回查台账的索引
|
SET @c = (SELECT COUNT(*) FROM information_schema.statistics
|
WHERE table_schema = DATABASE() AND table_name = 'research_fund_record' AND index_name = 'idx_reimbursement_id');
|
SET @sql = IF(@c = 0,
|
'ALTER TABLE research_fund_record ADD INDEX idx_reimbursement_id (reimbursement_id) USING BTREE',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ----- 2. 字典 research_fund_status 补 2 条 + 排序调整 + 类型备注 -----
|
|
UPDATE system_dict_type SET remark = '课题经费待拨付/报销审批中/已拨付/已驳回', update_time = NOW()
|
WHERE `type` = 'research_fund_status';
|
|
-- 先删后插，保证幂等（同 research_module.sql 的写法）
|
DELETE FROM system_dict_data WHERE dict_type = 'research_fund_status';
|
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
|
VALUES (8100401, 1, '待拨付', '0', 'research_fund_status', 0, 'warning', '', '1', NOW(), '1', NOW(), b'0');
|
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
|
VALUES (8100403, 2, '报销审批中', '15', 'research_fund_status', 0, 'processing', '', '1', NOW(), '1', NOW(), b'0');
|
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
|
VALUES (8100402, 3, '已拨付', '20', 'research_fund_status', 0, 'success', '', '1', NOW(), '1', NOW(), b'0');
|
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, remark, creator, create_time, updater, update_time, deleted)
|
VALUES (8100404, 4, '已驳回', '30', 'research_fund_status', 0, 'danger', '', '1', NOW(), '1', NOW(), b'0');
|
