all-project/宿舍信息同步到数据库中/xixi6_dorm_import.sql

-- 西溪6号楼批量入库脚本（可独立执行）
-- 说明：脚本内已包含源数据临时表构建（含班级口径：电商231/烹饪221 保持原名）
-- 会写正式表：xg_dorm_bed_student / xg_dorm_bed / xg_student_msg

-- 西溪6号楼住宿预检查脚本（根据 学校学生住宿情况@西溪6号楼.md 自动生成）
-- 说明: 本脚本仅做预检查，不会对正式表进行增删改

DROP TEMPORARY TABLE IF EXISTS tmp_assign_input_raw;
CREATE TEMPORARY TABLE tmp_assign_input_raw (
    room_code VARCHAR(20) NOT NULL,
    bed_name VARCHAR(20) NOT NULL,
    student_name VARCHAR(50) NULL,
    class_name_raw VARCHAR(100) NULL,
    remark VARCHAR(200) NULL,
    source_line INT NULL,
    raw_text VARCHAR(255) NULL
);

INSERT INTO tmp_assign_input_raw(room_code, bed_name, student_name, class_name_raw, remark, source_line, raw_text)
VALUES
('6302', '1上', '徐湘琴', '药剂211', NULL, 32, '1上 徐湘琴 药剂211'),
('6302', '1下', '王瑜洁', '药剂211', NULL, 33, '1下 王瑜洁 药剂211'),
('6302', '2上', '周慧颖', '药剂211', NULL, 34, '2上 周慧颖 药剂211'),
('6302', '2下', '鲁子奕', '药剂211', NULL, 35, '2下 鲁子奕 药剂211'),
('6303', '1上', NULL, NULL, NULL, 39, '1上'),
('6303', '1下', '王佳慧', '药剂211', NULL, 40, '1下 王佳慧 药剂211'),
('6303', '2上', '张雅婷', '药剂211', NULL, 41, '2上 张雅婷 药剂211'),
('6303', '2下', '章征浣', '药剂211', NULL, 42, '2下 章征浣 药剂211'),
('6304', '1上', '吕杭', '药剂211', NULL, 46, '1上 吕杭 药剂211'),
('6304', '1下', '沈晓冉', '药剂211', NULL, 47, '1下 沈晓冉 药剂211'),
('6304', '2上', '应雪燕', '药剂211', NULL, 48, '2上 应雪燕 药剂211'),
('6304', '2下', NULL, NULL, NULL, 49, '2下'),
('6305', '1上', '邱夕颜', '药剂232', NULL, 53, '1上 邱夕颜 药剂232'),
('6305', '1下', '郑飞杨', '药剂211', NULL, 54, '1下 郑飞杨 药剂211'),
('6305', '2上', '涂舒涵', '药剂211', NULL, 55, '2上 涂舒涵 药剂211'),
('6305', '2下', '张欣雅', '中药232', NULL, 56, '2下 张欣雅 中药232'),
('6306', '1上', '来思琴', '药剂211', NULL, 60, '1上 来思琴 药剂211'),
('6306', '1下', '祝丽倩', '药剂211', NULL, 61, '1下 祝丽倩 药剂211'),
('6306', '2上', '吴思辰', '药剂211', NULL, 62, '2上 吴思辰 药剂211'),
('6306', '2下', '姚冰荧', '药剂211', NULL, 63, '2下 姚冰荧 药剂211'),
('6307', '1上', '刘欢', '药剂211', NULL, 67, '1上 刘欢 药剂211'),
('6307', '1下', '代舒琴', '药剂211', NULL, 68, '1下 代舒琴 药剂211'),
('6307', '2上', '冉庆铃', '药剂211', NULL, 69, '2上 冉庆铃 药剂211'),
('6307', '2下', '金舒琪', '药剂211', NULL, 70, '2下 金舒琪 药剂211'),
('6309', '1上', '傅瑜菲', '药剂211', '升学', 74, '1上 傅瑜菲 药剂211 升学'),
('6309', '1下', '何苗苗', '机器人231', NULL, 75, '1下 何苗苗 机器人231'),
('6309', '2上', '何静', '机器人231', NULL, 76, '2上 何静 机器人231'),
('6309', '2下', '袁笑笑', '机器人231', NULL, 77, '2下 袁笑笑 机器人231'),
('6401', '1上', '四郎志玛', '烹饪221', NULL, 81, '1上 四郎志玛 烹饪221'),
('6401', '1下', '仁青拉姆', '烹饪221', NULL, 82, '1下 仁青拉姆 烹饪221'),
('6401', '2上', '多吉志玛', '烹饪221', NULL, 83, '2上 多吉志玛 烹饪221'),
('6401', '2下', NULL, NULL, NULL, 84, '2下'),
('6402', '1上', '倪诗余', '电商231', NULL, 88, '1上 倪诗余 电商231'),
('6402', '1下', '颜雨焓', '电商231', NULL, 89, '1下 颜雨焓 电商231'),
('6402', '2上', '龚艾雯', '电商231', NULL, 90, '2上 龚艾雯 电商231'),
('6402', '2下', '占佳琪', '电商231', NULL, 91, '2下 占佳琪 电商231'),
('6403', '1上', '任果果', '电商231', NULL, 95, '1上 任果果 电商231'),
('6403', '1下', '赖桦蓉', '电商231', NULL, 96, '1下 赖桦蓉 电商231'),
('6403', '2上', '顾晶晶', '电商231', NULL, 97, '2上 顾晶晶 电商231'),
('6403', '2下', '裘园园', '电商231', NULL, 98, '2下 裘园园 电商231'),
('6404', '1上', '张未茜', '电商231', NULL, 102, '1上 张未茜 电商231'),
('6404', '1下', '周美德', '电商231', NULL, 103, '1下 周美德 电商231'),
('6404', '2上', '孙喜悦', '电商231', NULL, 104, '2上 孙喜悦 电商231'),
('6404', '2下', '姜萌怡', '电商231', NULL, 105, '2下 姜萌怡 电商231'),
('6405', '1上', '张善媛', '电商231', NULL, 109, '1上 张善媛 电商231'),
('6405', '1下', '秦诗怡', '电商231', NULL, 110, '1下 秦诗怡 电商231'),
('6405', '2上', '沈张瑶', '电商231', NULL, 111, '2上 沈张瑶 电商231'),
('6405', '2下', '李玲', '电商231', NULL, 112, '2下 李玲 电商231'),
('6406', '1上', '项子婷', '电商231', NULL, 116, '1上 项子婷 电商231'),
('6406', '1下', '李静姝', '电商231', NULL, 117, '1下 李静姝 电商231'),
('6406', '2上', NULL, NULL, NULL, 118, '2上'),
('6406', '2下', '吴晓燕', '电商231', NULL, 119, '2下 吴晓燕 电商231'),
('6407', '1上', '邱夕颜', '药剂232', NULL, 123, '1上 邱夕颜 药剂232'),
('6407', '1下', '张欣雅', '中药232', NULL, 124, '1下 张欣雅 中药232'),
('6407', '2上', '郑佳妮', '中药251', NULL, 125, '2上 郑佳妮 中药251'),
('6407', '2下', '张雅琴', '西餐运营232', NULL, 126, '2下 张雅琴 西餐运营232')
;

DROP TEMPORARY TABLE IF EXISTS tmp_assign_norm;
CREATE TEMPORARY TABLE tmp_assign_norm AS
SELECT
    TRIM(room_code) AS room_code,
    TRIM(bed_name) AS bed_name,
    NULLIF(TRIM(student_name), '') AS student_name,
    CASE NULLIF(TRIM(SUBSTRING_INDEX(COALESCE(class_name_raw, ''), ' ', 1)), '')
        WHEN '数媒251' THEN '数字媒体251'
        WHEN '电商251' THEN '电子商务251'
        WHEN '电商252' THEN '电子商务252'
        WHEN '西餐运营211' THEN '西餐211'
        ELSE NULLIF(TRIM(SUBSTRING_INDEX(COALESCE(class_name_raw, ''), ' ', 1)), '')
    END AS class_name,
    NULLIF(TRIM(class_name_raw), '') AS class_name_raw,
    NULLIF(TRIM(remark), '') AS remark,
    source_line,
    raw_text
FROM tmp_assign_input_raw;

START TRANSACTION;

DROP TEMPORARY TABLE IF EXISTS tmp_assign_import_ready;
CREATE TEMPORARY TABLE tmp_assign_import_ready AS
SELECT
    t.room_code,
    t.bed_name,
    t.student_name,
    t.class_name,
    c.id AS class_id,
    c.college_id,
    c.major_id,
    s.id AS student_id,
    bld.id AS building_id,
    bld.area_id,
    bld.campus_id,
    rm.id AS room_id,
    bed.id AS bed_id,
    t.source_line,
    t.raw_text
FROM tmp_assign_norm t
LEFT JOIN xg_class_msg c
  ON c.deleted = 0
 AND c.class_name COLLATE utf8mb4_general_ci = t.class_name COLLATE utf8mb4_general_ci
LEFT JOIN xg_student_msg s
  ON s.deleted = 0
 AND s.name COLLATE utf8mb4_general_ci = t.student_name COLLATE utf8mb4_general_ci
 AND s.class_name COLLATE utf8mb4_general_ci = t.class_name COLLATE utf8mb4_general_ci
LEFT JOIN xg_dorm_building bld
  ON bld.deleted = '0'
 AND bld.building_name = '西溪6号楼'
LEFT JOIN xg_dorm_room rm
  ON rm.deleted = '0'
 AND rm.building_id = bld.id
 AND rm.room_code COLLATE utf8mb4_general_ci = t.room_code COLLATE utf8mb4_general_ci
LEFT JOIN xg_dorm_bed bed
  ON bed.deleted = '0'
 AND bed.room_id = rm.id
 AND bed.bed_name COLLATE utf8mb4_general_ci = t.bed_name COLLATE utf8mb4_general_ci
WHERE t.student_name IS NOT NULL
  AND t.class_name IS NOT NULL;

DROP TEMPORARY TABLE IF EXISTS tmp_assign_import_final;
CREATE TEMPORARY TABLE tmp_assign_import_final AS
SELECT r.*
FROM tmp_assign_import_ready r
LEFT JOIN xg_dorm_bed_student bs_bed
  ON bs_bed.deleted = '0' AND bs_bed.status = '0' AND bs_bed.bed_id = r.bed_id
LEFT JOIN xg_dorm_bed_student bs_student
  ON bs_student.deleted = '0' AND bs_student.status = '0' AND bs_student.student_id = r.student_id
WHERE r.class_id IS NOT NULL
  AND r.student_id IS NOT NULL
  AND r.building_id IS NOT NULL
  AND r.room_id IS NOT NULL
  AND r.bed_id IS NOT NULL
  AND bs_bed.id IS NULL
  AND bs_student.id IS NULL;

SET @next_rel_id := (SELECT COALESCE(MAX(id), 0) FROM xg_dorm_bed_student);

INSERT INTO xg_dorm_bed_student (
    id, college_id, major_id, class_id, student_id, bed_id, room_id, building_id, area_id, campus_id,
    status, assign_time, checkout_time, deleted, tenant_id, create_by, create_dept, create_time, update_by, update_time, remark
)
SELECT
    (@next_rel_id := @next_rel_id + 1) AS id,
    college_id,
    major_id,
    class_id,
    student_id,
    bed_id,
    room_id,
    building_id,
    area_id,
    campus_id,
    '0' AS status,
    NOW() AS assign_time,
    NULL AS checkout_time,
    '0' AS deleted,
    0 AS tenant_id,
    NULL AS create_by,
    NULL AS create_dept,
    NOW() AS create_time,
    NULL AS update_by,
    NOW() AS update_time,
    CONCAT('西溪6号楼批量导入，source_line=', source_line) AS remark
FROM tmp_assign_import_final;

UPDATE xg_dorm_bed bed
JOIN tmp_assign_import_final i ON i.bed_id = bed.id
SET bed.bed_status = '1',
    bed.update_time = NOW()
WHERE bed.deleted = '0';

UPDATE xg_student_msg s
JOIN tmp_assign_import_final i ON i.student_id = s.id
SET s.is_boarder = 1,
    s.dormitory_building = CAST(i.building_id AS CHAR),
    s.dormitory_room = CAST(i.room_id AS CHAR),
    s.ext_field1 = CAST(i.bed_id AS CHAR),
    s.update_time = NOW()
WHERE s.deleted = 0;

SELECT
    (SELECT COUNT(*) FROM tmp_assign_import_ready) AS ready_cnt,
    (SELECT COUNT(*) FROM tmp_assign_import_final) AS imported_cnt;

COMMIT;