all-project/script/sql/fix-project-application-missing-fields.sql

-- 修复项目申报表中缺失字段的问题
-- 日期: 2025-08-01
-- 说明: 修复cw_project_application表中可能为空的必填字段

-- 1. 检查当前有问题的记录
SELECT 
    id, 
    application_no, 
    project_name, 
    application_reason,
    construction_content,
    total_performance_goal,
    current_year_goal,
    department_name,
    approval_status
FROM cw_project_application 
WHERE application_reason IS NULL 
   OR application_reason = ''
   OR construction_content IS NULL
   OR construction_content = ''
   OR total_performance_goal IS NULL
   OR total_performance_goal = ''
   OR current_year_goal IS NULL
   OR current_year_goal = ''
   OR department_name IS NULL
   OR department_name = ''
ORDER BY create_time DESC;

-- 2. 修复application_reason为空的记录
UPDATE cw_project_application 
SET application_reason = CONCAT('项目申请原因：', project_name, '项目的设立基于实际业务需求，具有可行性和必要性。')
WHERE (application_reason IS NULL OR application_reason = '')
  AND project_name IS NOT NULL;

-- 3. 修复construction_content为空的记录
UPDATE cw_project_application 
SET construction_content = CONCAT('项目建设内容：', project_name, '项目的主要建设内容包括相关软硬件建设、人员培训等。')
WHERE (construction_content IS NULL OR construction_content = '')
  AND project_name IS NOT NULL;

-- 4. 修复total_performance_goal为空的记录（基于绩效指标自动生成）
UPDATE cw_project_application cpa
LEFT JOIN (
    SELECT 
        application_id,
        COUNT(CASE WHEN indicator_type IN ('quantity', 'quality', 'timeliness', 'cost') THEN 1 END) as output_count,
        COUNT(CASE WHEN indicator_type IN ('economic', 'social', 'environmental') THEN 1 END) as effect_count,
        COUNT(CASE WHEN indicator_type = 'satisfaction' THEN 1 END) as satisfaction_count
    FROM cw_performance_indicator 
    WHERE deleted = 0
    GROUP BY application_id
) pi ON cpa.id = pi.application_id
SET cpa.total_performance_goal = CASE 
    WHEN pi.application_id IS NOT NULL THEN
        CONCAT('通过本项目的实施，预期实现以下绩效目标：产出指标', IFNULL(pi.output_count, 0), '项，效益指标', IFNULL(pi.effect_count, 0), '项，满意度指标', IFNULL(pi.satisfaction_count, 0), '项，确保项目按期完成并达到预期效果。')
    ELSE
        CONCAT('通过', cpa.project_name, '项目的实施，预期实现既定的绩效目标，确保项目按期完成并达到预期效果。')
END
WHERE (cpa.total_performance_goal IS NULL OR cpa.total_performance_goal = '')
  AND cpa.project_name IS NOT NULL;

-- 5. 修复current_year_goal为空的记录
UPDATE cw_project_application 
SET current_year_goal = CONCAT(YEAR(CURDATE()), '年度绩效目标：确保项目关键节点按计划推进，各项指标达到设定标准，为项目整体目标的实现奠定坚实基础。')
WHERE (current_year_goal IS NULL OR current_year_goal = '')
  AND project_name IS NOT NULL;

-- 6. 修复department_name为空的记录（从sys_dept表获取）
UPDATE cw_project_application cpa
INNER JOIN sys_dept sd ON cpa.department_id = sd.dept_id
SET cpa.department_name = sd.dept_name
WHERE (cpa.department_name IS NULL OR cpa.department_name = '')
  AND cpa.department_id IS NOT NULL;

-- 7. 对于仍然缺失department_name的记录，设置默认值
UPDATE cw_project_application 
SET department_name = '未指定部门'
WHERE (department_name IS NULL OR department_name = '')
  AND applicant_id IS NOT NULL;

-- 8. 验证修复结果
SELECT 
    COUNT(*) as total_records,
    COUNT(CASE WHEN application_reason IS NULL OR application_reason = '' THEN 1 END) as null_application_reason,
    COUNT(CASE WHEN construction_content IS NULL OR construction_content = '' THEN 1 END) as null_construction_content,
    COUNT(CASE WHEN total_performance_goal IS NULL OR total_performance_goal = '' THEN 1 END) as null_total_performance_goal,
    COUNT(CASE WHEN current_year_goal IS NULL OR current_year_goal = '' THEN 1 END) as null_current_year_goal,
    COUNT(CASE WHEN department_name IS NULL OR department_name = '' THEN 1 END) as null_department_name
FROM cw_project_application;

-- 9. 显示修复后的记录样例
SELECT 
    id, 
    application_no, 
    project_name, 
    SUBSTRING(application_reason, 1, 50) as application_reason_preview,
    SUBSTRING(construction_content, 1, 50) as construction_content_preview,
    SUBSTRING(total_performance_goal, 1, 50) as total_performance_goal_preview,
    SUBSTRING(current_year_goal, 1, 50) as current_year_goal_preview,
    department_name,
    approval_status,
    create_time
FROM cw_project_application 
ORDER BY create_time DESC
LIMIT 10;