-- ==========================================
-- 统一身份认证中间库 - MySQL 目标库同步 SQL
-- 适用：SOURCE 与 TARGET 均为 MySQL（研发同库测试）
-- 使用方式：由 sync_user_to_sso.sh 调用，可传入增量时间条件
-- ==========================================

SET NAMES utf8mb4;

-- 1. 院系所单位 GXXX_YXSDWJBSJZL <- sys_dept
INSERT INTO GXXX_YXSDWJBSJZL (
  dwh, dwmc, lsdwh, dwyxbs, zhgxsj, scbz
)
SELECT
  RIGHT(CAST(d.dept_id AS CHAR), 10) AS dwh,
  LEFT(IFNULL(d.dept_name, ''), 60) AS dwmc,
  CASE
    WHEN d.parent_id IS NULL OR d.parent_id = 0 THEN NULL
    ELSE RIGHT(CAST(d.parent_id AS CHAR), 10)
  END AS lsdwh,
  CASE WHEN d.status = '0' AND d.del_flag = '0' THEN '1' ELSE '0' END AS dwyxbs,
  DATE_FORMAT(IFNULL(d.update_time, d.create_time), '%Y%m%d%H%i%s') AS zhgxsj,
  CASE WHEN d.del_flag = '1' THEN 'y' ELSE 'n' END AS scbz
FROM sys_dept d
WHERE 1=1
  /*INCREMENTAL_FILTER_DEPT*/
ON DUPLICATE KEY UPDATE
  dwmc = VALUES(dwmc),
  lsdwh = VALUES(lsdwh),
  dwyxbs = VALUES(dwyxbs),
  zhgxsj = VALUES(zhgxsj),
  scbz = VALUES(scbz);

-- 2. 教职工基础 GXJG_JZGJCSJZLB <- sys_user (account_type=1)
INSERT INTO GXJG_JZGJCSJZLB (
  gh, dwh, xm, xbm, dqztm, zhgxsj, scbz
)
SELECT
  LEFT(u.user_name, 20) AS gh,
  RIGHT(CAST(IFNULL(u.dept_id, 0) AS CHAR), 10) AS dwh,
  LEFT(IFNULL(u.nick_name, u.user_name), 36) AS xm,
  CASE u.sex WHEN '0' THEN '1' WHEN '1' THEN '2' ELSE '0' END AS xbm,
  CASE WHEN u.status = '0' THEN '11' ELSE '99' END AS dqztm,
  DATE_FORMAT(IFNULL(u.update_time, u.create_time), '%Y%m%d%H%i%s') AS zhgxsj,
  CASE WHEN u.del_flag = '1' THEN 'y' ELSE 'n' END AS scbz
FROM sys_user u
WHERE u.account_type = '1'
  /*INCREMENTAL_FILTER_TEACHER*/
ON DUPLICATE KEY UPDATE
  dwh = VALUES(dwh),
  xm = VALUES(xm),
  xbm = VALUES(xbm),
  dqztm = VALUES(dqztm),
  zhgxsj = VALUES(zhgxsj),
  scbz = VALUES(scbz);

-- 3. 教职工通讯 GXJG_GRTXFSZL <- sys_user (account_type=1)
INSERT INTO GXJG_GRTXFSZL (
  id, gh, yddh, dzxx, zhgxsj, scbz
)
SELECT
  LEFT(u.user_name, 48) AS id,
  LEFT(u.user_name, 20) AS gh,
  LEFT(NULLIF(u.phonenumber, ''), 30) AS yddh,
  LEFT(NULLIF(u.email, ''), 40) AS dzxx,
  DATE_FORMAT(IFNULL(u.update_time, u.create_time), '%Y%m%d%H%i%s') AS zhgxsj,
  CASE WHEN u.del_flag = '1' THEN 'y' ELSE 'n' END AS scbz
FROM sys_user u
WHERE u.account_type = '1'
  /*INCREMENTAL_FILTER_TEACHER*/
ON DUPLICATE KEY UPDATE
  gh = VALUES(gh),
  yddh = VALUES(yddh),
  dzxx = VALUES(dzxx),
  zhgxsj = VALUES(zhgxsj),
  scbz = VALUES(scbz);

-- 4. 学生基础 GXXS_XSJBSJZL <- xg_student_msg
INSERT INTO GXXS_XSJBSJZL (
  xh, xm, ywxm, cym, xbm, csrq, jg, sfzjlxm, sfzjh, zzmmm, xxm,
  sfdszn, zhgxsj, scbz, dwh, szbh, sfzx, sfyby
)
SELECT
  LEFT(s.student_no, 20) AS xh,
  LEFT(IFNULL(s.name, ''), 60) AS xm,
  LEFT(IFNULL(s.name_english, ''), 60) AS ywxm,
  LEFT(IFNULL(s.former_name, ''), 60) AS cym,
  CASE s.gender WHEN '0' THEN '1' WHEN '1' THEN '2' ELSE '0' END AS xbm,
  DATE_FORMAT(s.birth_date, '%Y%m%d') AS csrq,
  LEFT(IFNULL(s.native_place, ''), 60) AS jg,
  '1' AS sfzjlxm,
  LEFT(IFNULL(s.id_card, ''), 20) AS sfzjh,
  LEFT(IFNULL(s.political_status, ''), 2) AS zzmmm,
  LEFT(IFNULL(s.blood_type, ''), 30) AS xxm,
  CASE WHEN s.is_single_child = 1 THEN '1' ELSE '0' END AS sfdszn,
  DATE_FORMAT(IFNULL(s.update_time, s.create_time), '%Y%m%d%H%i%s') AS zhgxsj,
  CASE WHEN s.deleted = 1 THEN 'y' ELSE 'n' END AS scbz,
  LEFT(IFNULL(s.college_id, '0'), 20) AS dwh,
  LEFT(IFNULL(c.class_code, IFNULL(s.class_id, '')), 20) AS szbh,
  CASE WHEN s.student_status IN ('1', '2', '3') THEN '1' ELSE '0' END AS sfzx,
  CASE WHEN s.student_status = '5' THEN '1' ELSE '0' END AS sfyby
FROM xg_student_msg s
LEFT JOIN xg_class_msg c ON c.id = s.class_id AND c.deleted = 0
WHERE 1=1
  /*INCREMENTAL_FILTER_STUDENT*/
ON DUPLICATE KEY UPDATE
  xm = VALUES(xm),
  ywxm = VALUES(ywxm),
  cym = VALUES(cym),
  xbm = VALUES(xbm),
  csrq = VALUES(csrq),
  jg = VALUES(jg),
  sfzjh = VALUES(sfzjh),
  zzmmm = VALUES(zzmmm),
  xxm = VALUES(xxm),
  sfdszn = VALUES(sfdszn),
  zhgxsj = VALUES(zhgxsj),
  scbz = VALUES(scbz),
  dwh = VALUES(dwh),
  szbh = VALUES(szbh),
  sfzx = VALUES(sfzx),
  sfyby = VALUES(sfyby);

-- 5. 学生通讯 GXXS_GRTXZL <- xg_student_msg
INSERT INTO GXXS_GRTXZL (
  xh, yzbm, txdz, yddh, dzxx, jstxh, zhgxsj, scbz
)
SELECT
  LEFT(s.student_no, 20) AS xh,
  LEFT(IFNULL(s.postal_code, ''), 6) AS yzbm,
  LEFT(IFNULL(NULLIF(s.current_address, ''), s.home_address), 180) AS txdz,
  LEFT(IFNULL(s.phone, ''), 30) AS yddh,
  LEFT(IFNULL(s.email, ''), 40) AS dzxx,
  LEFT(IFNULL(NULLIF(s.qq, ''), s.wechat), 40) AS jstxh,
  DATE_FORMAT(IFNULL(s.update_time, s.create_time), '%Y%m%d%H%i%s') AS zhgxsj,
  CASE WHEN s.deleted = 1 THEN 'y' ELSE 'n' END AS scbz
FROM xg_student_msg s
WHERE 1=1
  /*INCREMENTAL_FILTER_STUDENT*/
ON DUPLICATE KEY UPDATE
  yzbm = VALUES(yzbm),
  txdz = VALUES(txdz),
  yddh = VALUES(yddh),
  dzxx = VALUES(dzxx),
  jstxh = VALUES(jstxh),
  zhgxsj = VALUES(zhgxsj),
  scbz = VALUES(scbz);