all-project/docs/设计文档/database/vw_jw_student_export.sql

-- =============================================================================
-- 学生基本信息 → 教务对接宽表视图
-- 说明见：跟教务系统同步学生基础信息/学生基本信息对接说明.md
--
-- 口径摘要：
--   * 身份证件类型：有 id_card 则「居民身份证」，否则 NULL
--   * 所在校区：有有效宿舍分配则取 xg_dorm_campus.campus_name，否则 NULL
--   * 班主任工号：class_teacher_id → sys_user.user_no（空则 user_name）
--   * 学生是否在校：默认「在校」；退学(4)、毕业(5) 为「不在校」
--   * 异动类型：固定 NULL
--   * 毕业时间/毕业学年学期：在籍按 入学年份+学制 推算；已毕业(5)取 BI_YE 异动 effective_date
--   * 学习经历/何年何月/在何地学习/在校照片：固定 NULL
--   * 性别：0→男，1→女
--   * 成员1/2：父亲/母亲及关系、电话
--   * 学生当前状态：输出教务中文名称（见对接说明 §状态映射）
-- =============================================================================

DROP VIEW IF EXISTS `vw_jw_student_export`;

CREATE VIEW `vw_jw_student_export` AS
SELECT
  (m.`name` COLLATE utf8mb4_unicode_ci) AS `姓名`,
  (m.student_no COLLATE utf8mb4_unicode_ci) AS `学号`,
  (m.phone COLLATE utf8mb4_unicode_ci) AS `联系电话`,
  (CASE
    WHEN NULLIF(TRIM(m.id_card), '') IS NOT NULL
      THEN ('居民身份证' COLLATE utf8mb4_unicode_ci)
    ELSE (CAST(NULL AS CHAR(32) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci)
  END) AS `身份证件类型`,
  (m.id_card COLLATE utf8mb4_unicode_ci) AS `身份证件号`,
  (COALESCE(m.class_name, cm.class_name) COLLATE utf8mb4_unicode_ci) AS `班级信息`,
  (dorm.campus_name COLLATE utf8mb4_unicode_ci) AS `所在校区`,
  (COALESCE(m.grade, cm.grade) COLLATE utf8mb4_unicode_ci) AS `所在年级`,
  m.enrollment_year AS `入学年份`,
  (COALESCE(m.college_name, cm.college_name) COLLATE utf8mb4_unicode_ci) AS `系部信息`,
  (COALESCE(m.major_name, cm.major_name) COLLATE utf8mb4_unicode_ci) AS `专业信息`,
  (COALESCE(NULLIF(TRIM(su.user_no), ''), su.user_name) COLLATE utf8mb4_unicode_ci) AS `班主任工号`,
  (COALESCE(m.class_teacher_name, cm.class_teacher_name) COLLATE utf8mb4_unicode_ci) AS `班主任姓名`,
  (CASE m.student_status
    WHEN '1' THEN '在读'
    WHEN '2' THEN '休学'
    WHEN '3' THEN '复学'
    WHEN '4' THEN '退学'
    WHEN '5' THEN '毕业'
    WHEN '6' THEN '其他'
    WHEN '7' THEN '结业'
    WHEN '8' THEN '停学'
    WHEN '9' THEN '其他'
    ELSE COALESCE(m.student_status, '')
  END COLLATE utf8mb4_unicode_ci) AS `学生当前状态`,
  (CASE
    WHEN (m.student_status COLLATE utf8mb4_unicode_ci) IN (
      ('4' COLLATE utf8mb4_unicode_ci),
      ('5' COLLATE utf8mb4_unicode_ci)
    ) THEN ('不在校' COLLATE utf8mb4_unicode_ci)
    ELSE ('在校' COLLATE utf8mb4_unicode_ci)
  END) AS `学生是否在校`,
  (CAST(NULL AS CHAR(32) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `异动类型`,
  (CASE cm.class_type
    WHEN '0' THEN '三年制中级技工'
    WHEN '1' THEN '三年制技师'
    WHEN '2' THEN '四年制高级工'
    WHEN '3' THEN '五年制高级技工'
    WHEN '4' THEN '六年制技师'
    WHEN '5' THEN '中高职一体'
    ELSE cm.class_type
  END COLLATE utf8mb4_unicode_ci) AS `培养层次`,
  COALESCE(cm.school_system, m.school_system) AS `学制`,
  (
    CASE
      WHEN (m.student_status COLLATE utf8mb4_unicode_ci) = ('5' COLLATE utf8mb4_unicode_ci)
       AND grad_sc.effective_date IS NOT NULL
        THEN YEAR(grad_sc.effective_date)
      WHEN m.enrollment_year IS NOT NULL
       AND COALESCE(cm.school_system, m.school_system) IS NOT NULL
        THEN m.enrollment_year + COALESCE(cm.school_system, m.school_system)
      WHEN cm.graduation_year IS NOT NULL
        THEN cm.graduation_year
      ELSE NULL
    END
  ) AS `毕业时间`,
  (
    CASE
      WHEN (
        CASE
          WHEN (m.student_status COLLATE utf8mb4_unicode_ci) = ('5' COLLATE utf8mb4_unicode_ci)
           AND grad_sc.effective_date IS NOT NULL
            THEN YEAR(grad_sc.effective_date)
          WHEN m.enrollment_year IS NOT NULL
           AND COALESCE(cm.school_system, m.school_system) IS NOT NULL
            THEN m.enrollment_year + COALESCE(cm.school_system, m.school_system)
          WHEN cm.graduation_year IS NOT NULL
            THEN cm.graduation_year
          ELSE NULL
        END
      ) IS NOT NULL
        THEN CONCAT(
          (
            CASE
              WHEN (m.student_status COLLATE utf8mb4_unicode_ci) = ('5' COLLATE utf8mb4_unicode_ci)
               AND grad_sc.effective_date IS NOT NULL
                THEN YEAR(grad_sc.effective_date)
              WHEN m.enrollment_year IS NOT NULL
               AND COALESCE(cm.school_system, m.school_system) IS NOT NULL
                THEN m.enrollment_year + COALESCE(cm.school_system, m.school_system)
              WHEN cm.graduation_year IS NOT NULL
                THEN cm.graduation_year
              ELSE NULL
            END
          ) - 1,
          '-',
          (
            CASE
              WHEN (m.student_status COLLATE utf8mb4_unicode_ci) = ('5' COLLATE utf8mb4_unicode_ci)
               AND grad_sc.effective_date IS NOT NULL
                THEN YEAR(grad_sc.effective_date)
              WHEN m.enrollment_year IS NOT NULL
               AND COALESCE(cm.school_system, m.school_system) IS NOT NULL
                THEN m.enrollment_year + COALESCE(cm.school_system, m.school_system)
              WHEN cm.graduation_year IS NOT NULL
                THEN cm.graduation_year
              ELSE NULL
            END
          ),
          '-2'
        )
      ELSE NULL
    END
  ) AS `毕业学年学期`,
  (CAST(NULL AS CHAR(500) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `学习经历`,
  (CAST(NULL AS CHAR(128) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `何年何月-何年何月`,
  (CAST(NULL AS CHAR(256) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `在何地学习`,
  (CASE m.gender
    WHEN '0' THEN '男'
    WHEN '1' THEN '女'
    ELSE NULL
  END COLLATE utf8mb4_unicode_ci) AS `性别`,
  (m.native_place COLLATE utf8mb4_unicode_ci) AS `籍贯地`,
  (CAST(NULL AS CHAR(500) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `在校照片`,
  DATE(m.birth_date) AS `出生日期`,
  (COALESCE(NULLIF(TRIM(m.home_address), ''), m.current_address) COLLATE utf8mb4_unicode_ci) AS `家庭现住址`,
  (m.father_name COLLATE utf8mb4_unicode_ci) AS `成员1姓名`,
  (m.mother_name COLLATE utf8mb4_unicode_ci) AS `成员2姓名`,
  (CASE
    WHEN NULLIF(TRIM(m.father_name), '') IS NOT NULL
      THEN ('父亲' COLLATE utf8mb4_unicode_ci)
    ELSE (CAST(NULL AS CHAR(16) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci)
  END) AS `成员1关系`,
  (CASE
    WHEN NULLIF(TRIM(m.mother_name), '') IS NOT NULL
      THEN ('母亲' COLLATE utf8mb4_unicode_ci)
    ELSE (CAST(NULL AS CHAR(16) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci)
  END) AS `成员2关系`,
  (m.father_phone COLLATE utf8mb4_unicode_ci) AS `成员1联系电话`,
  (m.mother_phone COLLATE utf8mb4_unicode_ci) AS `成员2联系电话`,
  m.id AS `来源记录id`
FROM xg_student_msg m
LEFT JOIN xg_class_msg cm
  ON cm.deleted = 0
 AND (cm.tenant_id COLLATE utf8mb4_unicode_ci) = (m.tenant_id COLLATE utf8mb4_unicode_ci)
 AND cm.id = CAST(NULLIF(TRIM(m.class_id), '') AS UNSIGNED)
LEFT JOIN sys_user su
  ON su.user_id = COALESCE(m.class_teacher_id, cm.class_teacher_id)
 AND (su.del_flag COLLATE utf8mb4_unicode_ci) = ('0' COLLATE utf8mb4_unicode_ci)
 AND (su.tenant_id COLLATE utf8mb4_unicode_ci) = (m.tenant_id COLLATE utf8mb4_unicode_ci)
LEFT JOIN (
  SELECT
    dbs.student_id,
    MAX(dc.campus_name) AS campus_name
  FROM xg_dorm_bed_student dbs
  INNER JOIN (
    SELECT student_id, MAX(assign_time) AS max_assign
    FROM xg_dorm_bed_student
    WHERE deleted = '0'
      AND (status COLLATE utf8mb4_unicode_ci) = ('0' COLLATE utf8mb4_unicode_ci)
    GROUP BY student_id
  ) latest
    ON latest.student_id = dbs.student_id
   AND dbs.assign_time = latest.max_assign
  INNER JOIN xg_dorm_campus dc
    ON dc.id = dbs.campus_id
   AND (dc.deleted COLLATE utf8mb4_unicode_ci) = ('0' COLLATE utf8mb4_unicode_ci)
  WHERE dbs.deleted = '0'
    AND (dbs.status COLLATE utf8mb4_unicode_ci) = ('0' COLLATE utf8mb4_unicode_ci)
  GROUP BY dbs.student_id
) dorm ON dorm.student_id = m.id
LEFT JOIN (
  SELECT
    sc.student_id,
    MAX(sc.effective_date) AS effective_date
  FROM xg_student_status_change sc
  WHERE sc.deleted = 0
    AND sc.process_status = 1
    AND (sc.change_type COLLATE utf8mb4_unicode_ci) = ('BI_YE' COLLATE utf8mb4_unicode_ci)
  GROUP BY sc.student_id
) grad_sc ON grad_sc.student_id = m.id
WHERE m.deleted = 0;

-- -----------------------------------------------------------------------------
-- 授权：用户 jw_cx 仅查询本视图（需使用具备 GRANT 权限的账号执行，如 root）
-- -----------------------------------------------------------------------------
GRANT SELECT ON `hzdyjs_xg`.`vw_jw_student_export` TO 'jw_cx'@'%';
FLUSH PRIVILEGES;