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

-- 临安6号楼批量入库脚本（可独立执行）
-- 说明：脚本内已包含源数据临时表构建（床位 1-6 格式；已排除指定不存在学生；王建班级已修正）
-- 会写正式表：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
('6102', '1', '杨长明', '数控241', NULL, 32, '1 杨长明 数控241'),
('6102', '2', '王忆凡', '数控241', NULL, 33, '2 王忆凡 数控241'),
('6102', '3', '谢宇轩', '数控241', NULL, 34, '3 谢宇轩 数控241'),
('6102', '4', '童乐', '数控241', NULL, 35, '4 童乐 数控241'),
('6102', '5', '徐俊浩', '数控241', NULL, 36, '5 徐俊浩 数控241'),
('6102', '6', '许嘉琪', '数控241', NULL, 37, '6 许嘉琪 数控241'),
('6103', '1', '陈钰林', '数控241', NULL, 41, '1 陈钰林 数控241'),
('6103', '2', '席天羽', '数控241', NULL, 42, '2 席天羽 数控241'),
('6103', '3', '王博远', '数控241', NULL, 43, '3 王博远 数控241'),
('6103', '4', '胡子浩', '数控241', NULL, 44, '4 胡子浩 数控241'),
('6103', '5', NULL, NULL, NULL, 45, '5'),
('6103', '6', '张成', '数控241', NULL, 46, '6 张成 数控241'),
('6104', '1', '司乐乐', '数控241', NULL, 50, '1 司乐乐 数控241'),
('6104', '2', '高开廉', '数控241', NULL, 51, '2 高开廉 数控241'),
('6104', '3', '罗睿', '数控241', NULL, 52, '3 罗睿 数控241'),
('6104', '4', '栾丁瑞', '数控241', NULL, 53, '4 栾丁瑞 数控241'),
('6104', '5', '江禹麒', '数控241', NULL, 54, '5 江禹麒 数控241'),
('6104', '6', '刘泽', '数控241', NULL, 55, '6 刘泽 数控241'),
('6105', '1', '范宇浩', '数控241', NULL, 59, '1 范宇浩 数控241'),
('6105', '2', '肖祥文', '数控241', NULL, 60, '2 肖祥文 数控241'),
('6105', '3', NULL, NULL, NULL, 61, '3'),
('6105', '4', '于金硕', '数控241', NULL, 62, '4 于金硕 数控241'),
('6105', '5', '甘小丰', '数控241', NULL, 63, '5 甘小丰 数控241'),
('6105', '6', '刘泳刚', '数控241', NULL, 64, '6 刘泳刚 数控241'),
('6106', '1', '潘程', '数控241', NULL, 68, '1 潘程 数控241'),
('6106', '2', '陈宇晨', '数控241', NULL, 69, '2 陈宇晨 数控241'),
('6106', '3', '程国彬', '数控241', NULL, 70, '3 程国彬 数控241'),
('6106', '4', '王建', '数控241', NULL, 71, '4 王建 数控241'),
('6106', '5', '汤轩然', '数控241', NULL, 72, '5 汤轩然 数控241'),
('6106', '6', '薛清阳', '数控241', NULL, 73, '6 薛清阳 数控241'),
('6107', '1', '顾锦涛', '数控241', NULL, 77, '1 顾锦涛 数控241'),
('6107', '2', '余昌皓', '数控241', NULL, 78, '2 余昌皓 数控241'),
('6107', '3', '吴成睿', '数控241', NULL, 79, '3 吴成睿 数控241'),
('6107', '4', '任周杰', '数控241', NULL, 80, '4 任周杰 数控241'),
('6107', '5', '陈郑浩', '数控241', NULL, 81, '5 陈郑浩 数控241'),
('6107', '6', '沈子皓', '数控241', NULL, 82, '6 沈子皓 数控241'),
('6108', '1', NULL, NULL, NULL, 86, '1'),
('6108', '2', '俞子恒', '数控241', NULL, 87, '2 俞子恒 数控241'),
('6108', '3', NULL, NULL, NULL, 88, '3'),
('6108', '4', '彭俊峰', '数控241', NULL, 89, '4 彭俊峰 数控241'),
('6108', '5', '项嘉瑞', '数控241', NULL, 90, '5 项嘉瑞 数控241'),
('6108', '6', '张世豪', '数控241', NULL, 91, '6 张世豪 数控241'),
('6109', '1', '徐方浩', '数控241', NULL, 95, '1 徐方浩 数控241'),
('6109', '2', NULL, NULL, NULL, 96, '2'),
('6109', '3', '吴进博', '数控241', NULL, 97, '3 吴进博 数控241'),
('6109', '4', '王龙华', '数控241', NULL, 98, '4 王龙华 数控241'),
('6109', '5', NULL, NULL, NULL, 99, '5'),
('6109', '6', '顾承轩', '数控241', NULL, 100, '6 顾承轩 数控241'),
('6110', '1', '高晓卫', '汽修241', NULL, 104, '1 高晓卫 汽修241'),
('6110', '2', '曹楚阳', '汽修241', NULL, 105, '2 曹楚阳 汽修241'),
('6110', '3', '岑烨宇', '汽修241', NULL, 106, '3 岑烨宇 汽修241'),
('6110', '4', '施烨超', '汽修241', NULL, 107, '4 施烨超 汽修241'),
('6110', '5', '吴明浩', '汽修241', NULL, 108, '5 吴明浩 汽修241'),
('6110', '6', '杨承霖', '汽修241', NULL, 109, '6 杨承霖 汽修241'),
('6111', '1', '岳俊辉', '汽修241', NULL, 113, '1 岳俊辉 汽修241'),
('6111', '2', '潘一诺', '汽修241', NULL, 114, '2 潘一诺 汽修241'),
('6111', '3', '夏俊杰', '汽修241', NULL, 115, '3 夏俊杰 汽修241'),
('6111', '4', '何宇航', '汽修241', NULL, 116, '4 何宇航 汽修241'),
('6111', '5', NULL, NULL, NULL, 117, '5'),
('6111', '6', NULL, NULL, NULL, 118, '6'),
('6112', '1', '陈轲杰', '汽修241', NULL, 122, '1 陈轲杰 汽修241'),
('6112', '2', '伍家乐', '汽修241', NULL, 123, '2 伍家乐 汽修241'),
('6112', '3', '周颂', '汽修241', NULL, 124, '3 周颂 汽修241'),
('6112', '4', NULL, NULL, NULL, 125, '4'),
('6112', '5', NULL, NULL, NULL, 126, '5'),
('6112', '6', NULL, NULL, NULL, 127, '6'),
('6201', '1', '邵靖靖', '数字制造231', '集训', 131, '1 邵靖靖 数字制造231 集训'),
('6201', '2', '余旭峰', '数字制造231', '集训', 132, '2 余旭峰 数字制造231 集训'),
('6201', '3', '朱德良', '数字制造231', '集训', 133, '3 朱德良 数字制造231 集训'),
('6201', '4', NULL, NULL, NULL, 134, '4'),
('6201', '5', NULL, NULL, NULL, 135, '5'),
('6201', '6', NULL, NULL, NULL, 136, '6'),
('6203', '1', '吕宇浩', '数字制造241', NULL, 140, '1 吕宇浩 数字制造241'),
('6203', '2', '於威成', '数字制造241', NULL, 141, '2 於威成 数字制造241'),
('6203', '3', '郭靖宇', '数字制造241', NULL, 142, '3 郭靖宇 数字制造241'),
('6203', '4', '李歆杭', '数字制造241', NULL, 143, '4 李歆杭 数字制造241'),
('6203', '5', '曾翔宇', '数字制造241', NULL, 144, '5 曾翔宇 数字制造241'),
('6203', '6', '蔡赵鑫', '数字制造241', NULL, 145, '6 蔡赵鑫 数字制造241'),
('6204', '1', '姚马彦', '数字制造241', NULL, 149, '1 姚马彦 数字制造241'),
('6204', '2', '俞哲琦', '数字制造241', NULL, 150, '2 俞哲琦 数字制造241'),
('6204', '3', '金嘉诚', '数字制造241', NULL, 151, '3 金嘉诚 数字制造241'),
('6204', '4', '江睿宇', '数字制造241', NULL, 152, '4 江睿宇 数字制造241'),
('6204', '5', '魏鑫泉', '数字制造241', NULL, 153, '5 魏鑫泉 数字制造241'),
('6204', '6', '查心宇', '数字制造241', NULL, 154, '6 查心宇 数字制造241'),
('6205', '1', '胡恩泽', '数字制造241', NULL, 158, '1 胡恩泽 数字制造241'),
('6205', '2', '袁玉瑞', '数字制造241', NULL, 159, '2 袁玉瑞 数字制造241'),
('6205', '3', '罗嗣豪', '数字制造241', NULL, 160, '3 罗嗣豪 数字制造241'),
('6205', '4', '李轩宇', '数字制造241', NULL, 161, '4 李轩宇 数字制造241'),
('6205', '5', '吴勇豪', '数字制造241', NULL, 162, '5 吴勇豪 数字制造241'),
('6205', '6', '陈怿轩', '数字制造241', NULL, 163, '6 陈怿轩 数字制造241'),
('6206', '1', '叶枫', '数字制造241', NULL, 167, '1 叶枫 数字制造241'),
('6206', '2', '林鑫垒', '数字制造241', NULL, 168, '2 林鑫垒 数字制造241'),
('6206', '3', NULL, NULL, NULL, 169, '3'),
('6206', '4', '孙翔', '数字制造241', NULL, 170, '4 孙翔 数字制造241'),
('6206', '5', '徐贞浩', '数字制造241', NULL, 171, '5 徐贞浩 数字制造241'),
('6206', '6', '许颖辉', '数字制造241', NULL, 172, '6 许颖辉 数字制造241'),
('6207', '1', '倪懿泽', '数字制造241', NULL, 176, '1 倪懿泽 数字制造241'),
('6207', '2', '兰瑞锋', '数字制造241', NULL, 177, '2 兰瑞锋 数字制造241'),
('6207', '3', '程军强', '数字制造241', NULL, 178, '3 程军强 数字制造241'),
('6207', '4', '詹宇涛', '数字制造241', NULL, 179, '4 詹宇涛 数字制造241'),
('6207', '5', '方平钦', '数字制造241', NULL, 180, '5 方平钦 数字制造241'),
('6207', '6', '孔一鹏', '数字制造241', NULL, 181, '6 孔一鹏 数字制造241'),
('6208', '1', '蒋粒富', '数字制造241', NULL, 185, '1 蒋粒富 数字制造241'),
('6208', '2', '罗允浩', '数字制造241', NULL, 186, '2 罗允浩 数字制造241'),
('6208', '3', '金佳乐', '数字制造241', NULL, 187, '3 金佳乐 数字制造241'),
('6208', '4', '孙宇', '数字制造241', NULL, 188, '4 孙宇 数字制造241'),
('6208', '5', '张敏浩', '数字制造241', NULL, 189, '5 张敏浩 数字制造241'),
('6208', '6', '倪嘉诚', '数字制造241', NULL, 190, '6 倪嘉诚 数字制造241'),
('6209', '1', '祝城凯', '汽修241', NULL, 194, '1 祝城凯 汽修241'),
('6209', '2', '董金灏', '汽修241', NULL, 195, '2 董金灏 汽修241'),
('6209', '3', '徐佳', '汽修241', NULL, 196, '3 徐佳 汽修241'),
('6209', '4', '沈锦涵', '汽修241', NULL, 197, '4 沈锦涵 汽修241'),
('6209', '5', NULL, NULL, NULL, 198, '5'),
('6209', '6', NULL, NULL, NULL, 199, '6'),
('6210', '1', '倪骏熠', '汽修241', NULL, 203, '1 倪骏熠 汽修241'),
('6210', '2', '殷锦峰', '汽修241', NULL, 204, '2 殷锦峰 汽修241'),
('6210', '3', '陈坤', '汽修241', NULL, 205, '3 陈坤 汽修241'),
('6210', '4', '余帅', '汽修241', NULL, 206, '4 余帅 汽修241'),
('6210', '5', '蒋锡承', '汽修241', NULL, 207, '5 蒋锡承 汽修241'),
('6210', '6', NULL, NULL, NULL, 208, '6'),
('6211', '1', '李好', '汽修241', NULL, 212, '1 李好 汽修241'),
('6211', '2', '沈晓愚', '汽修241', NULL, 213, '2 沈晓愚 汽修241'),
('6211', '3', '李鑫宇', '汽修241', NULL, 214, '3 李鑫宇 汽修241'),
('6211', '4', '刘德颖', '汽修241', NULL, 215, '4 刘德颖 汽修241'),
('6211', '5', '滕浩', '汽修241', NULL, 216, '5 滕浩 汽修241'),
('6211', '6', '张翌轩', '汽修241', NULL, 217, '6 张翌轩 汽修241'),
('6212', '1', '赵海瑞', '汽修241', NULL, 222, '1 赵海瑞 汽修241'),
('6212', '2', '王启杰', '汽修241', NULL, 223, '2 王启杰 汽修241'),
('6212', '3', '高佳豪', '汽修241', NULL, 224, '3 高佳豪 汽修241'),
('6212', '5', '童佳浩', '汽修241', NULL, 225, '5 童佳浩 汽修241'),
('6212', '6', '余健哲', '汽修241', NULL, 226, '6 余健哲 汽修241'),
('6213', '1', '毛宇祺', '汽修241', NULL, 230, '1 毛宇祺 汽修241'),
('6213', '2', '王志成', '汽修241', NULL, 231, '2 王志成 汽修241'),
('6213', '3', '卓康', '汽修241', NULL, 232, '3 卓康 汽修241'),
('6213', '4', '陈科', '汽修241', NULL, 233, '4 陈科 汽修241'),
('6213', '5', '毛宇轩', '汽修241', NULL, 234, '5 毛宇轩 汽修241'),
('6213', '6', '汪豪彬', '汽修241', NULL, 235, '6 汪豪彬 汽修241'),
('6301', '1', '洪俞磊', '数控231', NULL, 239, '1 洪俞磊 数控231'),
('6301', '2', '陈永乐', '数控231', NULL, 240, '2 陈永乐 数控231'),
('6301', '3', '袁俊杰', '数控231', NULL, 241, '3 袁俊杰 数控231'),
('6301', '4', '朱国江', '数控231', NULL, 242, '4 朱国江 数控231'),
('6301', '5', '邓郁文', '数控231', NULL, 243, '5 邓郁文 数控231'),
('6301', '6', '傅江诚', '数控231', NULL, 244, '6 傅江诚 数控231'),
('6302', '1', '廖金杭', '数控231', NULL, 248, '1 廖金杭 数控231'),
('6302', '2', '韩子旭', '数控231', NULL, 249, '2 韩子旭 数控231'),
('6302', '3', '简洪凯', '数控231', NULL, 250, '3 简洪凯 数控231'),
('6302', '4', '胡俊', '数控231', NULL, 251, '4 胡俊 数控231'),
('6302', '5', '毛凯', '数控231', NULL, 252, '5 毛凯 数控231'),
('6302', '6', '张珑腾', '数控231', NULL, 253, '6 张珑腾 数控231'),
('6303', '1', '杨鑫杰', '数控231', NULL, 257, '1 杨鑫杰 数控231'),
('6303', '2', '连宏奕', '数控231', NULL, 258, '2 连宏奕 数控231'),
('6303', '3', '颊振鑫', '数控231', NULL, 259, '3 颊振鑫 数控231'),
('6303', '4', '俞培梁', '数控231', NULL, 260, '4 俞培梁 数控231'),
('6303', '5', '杨润东', '数控231', NULL, 261, '5 杨润东 数控231'),
('6303', '6', '徐奥权', '数控231', NULL, 262, '6 徐奥权 数控231'),
('6304', '1', '林嘉恒', '数控231', NULL, 266, '1 林嘉恒 数控231'),
('6304', '2', '陈逸钦', '数控231', NULL, 267, '2 陈逸钦 数控231'),
('6304', '3', '李雨航', '数控231', NULL, 268, '3 李雨航 数控231'),
('6304', '4', '赵文杰', '数控231', NULL, 269, '4 赵文杰 数控231'),
('6304', '5', '张振东', '数控231', NULL, 270, '5 张振东 数控231'),
('6304', '6', '陈世昌', '数控231', NULL, 271, '6 陈世昌 数控231'),
('6305', '1', '邢骏逸', '数控231', NULL, 275, '1 邢骏逸 数控231'),
('6305', '2', '许骏源', '数控231', NULL, 276, '2 许骏源 数控231'),
('6305', '3', '张郑权', '数控231', NULL, 277, '3 张郑权 数控231'),
('6305', '4', '周晟赫', '数控231', NULL, 278, '4 周晟赫 数控231'),
('6305', '5', '夏华雄', '数控231', NULL, 279, '5 夏华雄 数控231'),
('6305', '6', '何俊', '数控231', NULL, 280, '6 何俊 数控231'),
('6306', '1', '胡顺辰', '数控231', NULL, 284, '1 胡顺辰 数控231'),
('6306', '2', '刘博文', '数控231', NULL, 285, '2 刘博文 数控231'),
('6306', '3', '王高', '数控231', NULL, 286, '3 王高 数控231'),
('6306', '4', '孙国钦', '数控231', NULL, 287, '4 孙国钦 数控231'),
('6306', '5', '肖润鑫', '数控231', NULL, 288, '5 肖润鑫 数控231'),
('6306', '6', '沈杭', '数控231', NULL, 289, '6 沈杭 数控231'),
('6307', '1', '余飞龙', '数控231', NULL, 293, '1 余飞龙 数控231'),
('6307', '2', '应智宸', '数控231', NULL, 294, '2 应智宸 数控231'),
('6307', '3', '王奥凯', '数控231', NULL, 295, '3 王奥凯 数控231'),
('6307', '4', '郭俊辉', '数控231', NULL, 296, '4 郭俊辉 数控231'),
('6307', '5', '周嘉锋', '数控231', NULL, 297, '5 周嘉锋 数控231'),
('6307', '6', '孙睿', '数控231', NULL, 298, '6 孙睿 数控231'),
('6401', '1', '张浩', '数控221', NULL, 302, '1 张浩 数控221'),
('6401', '2', '林文唐', '数控221', NULL, 303, '2 林文唐 数控221'),
('6401', '3', '王国柱', '数控221', NULL, 304, '3 王国柱 数控221'),
('6401', '4', '牟家轩', '数控221', NULL, 305, '4 牟家轩 数控221'),
('6401', '5', '徐诗涛', '数控221', NULL, 306, '5 徐诗涛 数控221'),
('6401', '6', '陆天路', '数控221', NULL, 307, '6 陆天路 数控221'),
('6402', '1', '何吉成', '数控221', NULL, 311, '1 何吉成 数控221'),
('6402', '2', '姜畅', '数控221', NULL, 312, '2 姜畅 数控221'),
('6402', '3', '柯诗杰', '数控221', NULL, 313, '3 柯诗杰 数控221'),
('6402', '4', '郑智宇', '数控221', NULL, 314, '4 郑智宇 数控221'),
('6402', '5', '未建行', '数控221', NULL, 315, '5 未建行 数控221'),
('6402', '6', '凡福林', '数控221', NULL, 316, '6 凡福林 数控221'),
('6403', '1', '王一楠', '数控221', NULL, 320, '1 王一楠 数控221'),
('6403', '2', '朱翔宇', '数控221', NULL, 321, '2 朱翔宇 数控221'),
('6403', '3', '王可', '数控221', NULL, 322, '3 王可 数控221'),
('6403', '4', '周骏程', '数控221', NULL, 323, '4 周骏程 数控221'),
('6403', '5', '余承志', '数控221', NULL, 324, '5 余承志 数控221'),
('6403', '6', '凌子浩', '数控221', NULL, 325, '6 凌子浩 数控221'),
('6404', '1', '王俊辉', '数控221', NULL, 329, '1 王俊辉 数控221'),
('6404', '2', '姬彦宇', '数控221', NULL, 330, '2 姬彦宇 数控221'),
('6404', '3', '倪春鉴', '数控221', NULL, 331, '3 倪春鉴 数控221'),
('6404', '4', '宋强', '数控221', NULL, 332, '4 宋强 数控221'),
('6404', '5', '张俊文', '数控221', NULL, 333, '5 张俊文 数控221'),
('6404', '6', '王梦林', '数控221', NULL, 334, '6 王梦林 数控221'),
('6405', '1', '许泽昊', '数控221', NULL, 338, '1 许泽昊 数控221'),
('6405', '2', '杨子皓', '数控221', NULL, 339, '2 杨子皓 数控221'),
('6405', '3', '叶凯文', '数控221', NULL, 340, '3 叶凯文 数控221'),
('6405', '4', '王富音', '数控221', NULL, 341, '4 王富音 数控221'),
('6405', '5', '林楡富', '数控221', NULL, 342, '5 林楡富 数控221'),
('6405', '6', '程钟杰', '数控221', NULL, 343, '6 程钟杰 数控221'),
('6406', '1', '鲍宇航', '数控221', NULL, 347, '1 鲍宇航 数控221'),
('6406', '2', '王纪炎', '数控221', NULL, 348, '2 王纪炎 数控221'),
('6406', '3', '黄志强', '数控221', NULL, 349, '3 黄志强 数控221'),
('6406', '4', '潘怿', '数控221', NULL, 350, '4 潘怿 数控221'),
('6406', '5', '唐铖', '数控221', NULL, 351, '5 唐铖 数控221'),
('6406', '6', '魏晨宇', '数控221', NULL, 352, '6 魏晨宇 数控221'),
('6407', '1', '韩源', '数控221', NULL, 356, '1 韩源 数控221'),
('6407', '2', '李晓舟', '数控221', NULL, 357, '2 李晓舟 数控221'),
('6407', '3', '丁文韬', '数控221', NULL, 358, '3 丁文韬 数控221'),
('6407', '4', NULL, NULL, NULL, 359, '4'),
('6407', '5', NULL, NULL, NULL, 360, '5'),
('6407', '6', NULL, NULL, NULL, 361, '6'),
('6408', '1', '钟涛', '数字制造221', NULL, 365, '1 钟涛 数字制造221'),
('6408', '2', '杨俊晖', '数字制造221', NULL, 366, '2 杨俊晖 数字制造221'),
('6408', '3', '项晨', '数字制造221', NULL, 367, '3 项晨 数字制造221'),
('6408', '4', '金奕凯', '数字制造221', NULL, 368, '4 金奕凯 数字制造221'),
('6408', '5', '洪一非', '数字制造221', NULL, 369, '5 洪一非 数字制造221'),
('6408', '6', NULL, NULL, NULL, 370, '6'),
('6409', '1', '丁文洁', '数字制造221', NULL, 374, '1 丁文洁 数字制造221'),
('6409', '2', '崔腾', '数字制造221', NULL, 375, '2 崔腾 数字制造221'),
('6409', '3', '周翌阳', '数字制造221', NULL, 376, '3 周翌阳 数字制造221'),
('6409', '4', '周锦川', '数字制造221', NULL, 377, '4 周锦川 数字制造221'),
('6409', '5', NULL, NULL, NULL, 378, '5'),
('6409', '6', '何斌', '数字制造221', NULL, 379, '6 何斌 数字制造221'),
('6410', '1', '张佳豪', '数字制造221', NULL, 383, '1 张佳豪 数字制造221'),
('6410', '2', '熊忠文', '数字制造221', NULL, 384, '2 熊忠文 数字制造221'),
('6410', '3', '叶锦科', '数字制造221', NULL, 385, '3 叶锦科 数字制造221'),
('6410', '4', NULL, NULL, NULL, 386, '4'),
('6410', '5', '姜伯潮', '数字制造221', NULL, 387, '5 姜伯潮 数字制造221'),
('6410', '6', NULL, NULL, NULL, 388, '6'),
('6411', '1', '李政', '数字制造221', NULL, 392, '1 李政 数字制造221'),
('6411', '2', '滕嘉鹏', '数字制造221', NULL, 393, '2 滕嘉鹏 数字制造221'),
('6411', '3', '罗芳辉', '数字制造221', NULL, 394, '3 罗芳辉 数字制造221'),
('6411', '4', '王如森', '数字制造221', NULL, 395, '4 王如森 数字制造221'),
('6411', '5', '王叶佺', '数字制造221', NULL, 396, '5 王叶佺 数字制造221'),
('6411', '6', NULL, NULL, NULL, 397, '6'),
('6412', '1', '郑远航', '数字制造221', NULL, 401, '1 郑远航 数字制造221'),
('6412', '2', '张峻玮', '数字制造221', NULL, 402, '2 张峻玮 数字制造221'),
('6412', '3', '楼雨铭', '数字制造221', NULL, 403, '3 楼雨铭 数字制造221'),
('6412', '4', '臧佳玮', '数字制造221', NULL, 404, '4 臧佳玮 数字制造221'),
('6412', '5', '袁钦宏', '数字制造221', NULL, 405, '5 袁钦宏 数字制造221'),
('6412', '6', NULL, NULL, NULL, 406, '6'),
('6413', '1', '方诚迪', '数字制造221', NULL, 410, '1 方诚迪 数字制造221'),
('6413', '2', '徐正', '数字制造221', NULL, 411, '2 徐正 数字制造221'),
('6413', '3', '沈鑫程', '数字制造221', NULL, 412, '3 沈鑫程 数字制造221'),
('6413', '4', '马旭初', '数字制造221', NULL, 413, '4 马旭初 数字制造221'),
('6413', '5', '姜文轩', '数字制造221', NULL, 414, '5 姜文轩 数字制造221'),
('6413', '6', NULL, NULL, NULL, 415, '6'),
('6501', '1', '李梓俊', '汽修221', NULL, 419, '1 李梓俊 汽修221'),
('6501', '2', '周宁波', '汽修221', NULL, 420, '2 周宁波 汽修221'),
('6501', '3', '夏涛', '汽修221', NULL, 421, '3 夏涛 汽修221'),
('6501', '4', '张孺男', '汽修221', NULL, 422, '4 张孺男 汽修221'),
('6501', '5', '韩望', '汽修221', NULL, 423, '5 韩望 汽修221'),
('6501', '6', '宁程永', '汽修221', NULL, 424, '6 宁程永 汽修221'),
('6502', '1', '郭成栋', '汽修221', NULL, 428, '1 郭成栋 汽修221'),
('6502', '2', '王涛', '汽修221', NULL, 429, '2 王涛 汽修221'),
('6502', '3', '别云龙', '汽修221', NULL, 430, '3 别云龙 汽修221'),
('6502', '4', '商杨翌', '汽修221', NULL, 431, '4 商杨翌 汽修221'),
('6502', '5', '童以晨', '汽修221', NULL, 432, '5 童以晨 汽修221'),
('6502', '6', '王智庭', '汽修221', NULL, 433, '6 王智庭 汽修221'),
('6503', '1', '蔡学志', '汽修221', NULL, 437, '1 蔡学志 汽修221'),
('6503', '2', '吴捍琛', '汽修221', NULL, 438, '2 吴捍琛 汽修221'),
('6503', '3', '吴云想', '汽修221', NULL, 439, '3 吴云想 汽修221'),
('6503', '4', '梁绍杰', '汽修221', NULL, 440, '4 梁绍杰 汽修221'),
('6503', '5', '陈波', '汽修221', NULL, 441, '5 陈波 汽修221'),
('6503', '6', '姜金瑞', '汽修221', NULL, 442, '6 姜金瑞 汽修221'),
('6504', '1', '王展宇', '汽修221', NULL, 446, '1 王展宇 汽修221'),
('6504', '2', '李雄伟', '汽修221', NULL, 447, '2 李雄伟 汽修221'),
('6504', '3', '张智恺', '汽修221', NULL, 448, '3 张智恺 汽修221'),
('6504', '4', '吴家友', '汽修221', NULL, 449, '4 吴家友 汽修221'),
('6504', '5', NULL, NULL, NULL, 450, '5'),
('6504', '6', NULL, NULL, NULL, 451, '6'),
('6505', '1', '刘欢', '汽修221', NULL, 455, '1 刘欢 汽修221'),
('6505', '2', '李静楠', '汽修221', NULL, 456, '2 李静楠 汽修221'),
('6505', '3', '张博研', '汽修221', NULL, 457, '3 张博研 汽修221'),
('6505', '4', '江科锐', '汽修221', NULL, 458, '4 江科锐 汽修221'),
('6505', '5', '张明坤', '汽修221', NULL, 459, '5 张明坤 汽修221'),
('6505', '6', '冯鑫鹏', '汽修221', NULL, 460, '6 冯鑫鹏 汽修221'),
('6506', '1', '曾永琪', '汽修221', NULL, 464, '1 曾永琪 汽修221'),
('6506', '2', '高立凯', '汽修221', NULL, 465, '2 高立凯 汽修221'),
('6506', '3', '孙翊涛', '汽修221', NULL, 466, '3 孙翊涛 汽修221'),
('6506', '4', '侯江乐', '汽修221', NULL, 467, '4 侯江乐 汽修221'),
('6506', '5', '曹博云', '汽修221', NULL, 468, '5 曹博云 汽修221'),
('6506', '6', '张屹', '汽修221', NULL, 469, '6 张屹 汽修221'),
('6507', '1', '包新隆', '汽修221', NULL, 473, '1 包新隆 汽修221'),
('6507', '2', '曾繁强', '汽修221', NULL, 474, '2 曾繁强 汽修221'),
('6507', '3', '王永恩', '汽修221', NULL, 475, '3 王永恩 汽修221'),
('6507', '4', '王永乐', '汽修221', NULL, 476, '4 王永乐 汽修221'),
('6507', '5', '周锋', '汽修221', NULL, 477, '5 周锋 汽修221'),
('6507', '6', '张文俊', '汽修221', NULL, 478, '6 张文俊 汽修221'),
('6508', '1', '乐意', '数字制造211', NULL, 482, '1 乐意 数字制造211'),
('6508', '2', '黄程彬', '数字制造211', NULL, 483, '2 黄程彬 数字制造211'),
('6508', '3', '叶彤', '数字制造211', NULL, 484, '3 叶彤 数字制造211'),
('6508', '4', '涂大嵩', '数字制造211', NULL, 485, '4 涂大嵩 数字制造211'),
('6508', '5', '郑浩', '数字制造211', NULL, 486, '5 郑浩 数字制造211'),
('6508', '6', NULL, NULL, NULL, 487, '6'),
('6509', '1', '朱罗战', '数字制造211', NULL, 491, '1 朱罗战 数字制造211'),
('6509', '2', '陈依楠', '数字制造211', NULL, 492, '2 陈依楠 数字制造211'),
('6509', '3', '冯致尧', '数字制造211', NULL, 493, '3 冯致尧 数字制造211'),
('6509', '4', '杨志鹏', '数字制造211', NULL, 494, '4 杨志鹏 数字制造211'),
('6509', '5', '包佳乐', '数字制造211', NULL, 495, '5 包佳乐 数字制造211'),
('6509', '6', '李任柏', '数字制造211', NULL, 496, '6 李任柏 数字制造211'),
('6510', '1', NULL, NULL, NULL, 500, '1'),
('6510', '2', '戴晨陆', '数字制造211', NULL, 501, '2 戴晨陆 数字制造211'),
('6510', '3', '万君毅', '数字制造211', NULL, 502, '3 万君毅 数字制造211'),
('6510', '4', '程佳辉', '数字制造211', NULL, 503, '4 程佳辉 数字制造211'),
('6510', '5', '庞铭昊', '数字制造211', NULL, 504, '5 庞铭昊 数字制造211'),
('6510', '6', NULL, NULL, NULL, 505, '6'),
('6511', '1', '林家辉', '数字制造211', NULL, 509, '1 林家辉 数字制造211'),
('6511', '2', '王琪乐', '数字制造211', NULL, 510, '2 王琪乐 数字制造211'),
('6511', '3', '韩天胜', '数字制造211', NULL, 511, '3 韩天胜 数字制造211'),
('6511', '4', '黄漂', '数字制造211', NULL, 512, '4 黄漂 数字制造211'),
('6511', '5', '洪靖翔', '数字制造211', NULL, 513, '5 洪靖翔 数字制造211'),
('6511', '6', '张杭鹏', '数字制造211', NULL, 514, '6 张杭鹏 数字制造211'),
('6512', '1', '金程凯', '数字制造211', NULL, 518, '1 金程凯 数字制造211'),
('6512', '2', '刘鑫', '数字制造211', NULL, 519, '2 刘鑫 数字制造211'),
('6512', '3', '姜文豪', '数字制造211', NULL, 520, '3 姜文豪 数字制造211'),
('6512', '4', '张鑫', '数字制造211', NULL, 521, '4 张鑫 数字制造211'),
('6512', '5', '戴倪浩', '数字制造211', NULL, 522, '5 戴倪浩 数字制造211'),
('6512', '6', '徐泽航', '数字制造211', NULL, 523, '6 徐泽航 数字制造211')
;

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,
    CASE
        WHEN NULLIF(TRIM(student_name), '') IN ('许嘉琪','吴明浩','童佳浩','颊振鑫','李雨航','王梦林','林楡富','王琪乐') THEN NULL
        ELSE NULLIF(TRIM(student_name), '')
    END AS student_name,
    CASE
        WHEN NULLIF(TRIM(student_name), '') = '王建' THEN '药剂173'
        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;