116 lines
3.7 KiB
SQL
116 lines
3.7 KiB
SQL
-- 西溪3号楼批量入库脚本（排除3个缺失学生）
|
||
-- 说明：
|
||
-- 1) 请先执行 xixi3_dorm_precheck.sql 中的数据装载部分（tmp_assign_input_raw + tmp_assign_norm）
|
||
-- 2) 本脚本会排除：倪俊麟、胡俊𤋮、马腾子乐
|
||
-- 3) 本脚本会写正式表：xg_dorm_bed_student / xg_dorm_bed / xg_student_msg
|
||
|
||
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 = '西溪3号楼'
|
||
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
|
||
AND t.student_name NOT IN ('倪俊麟', '胡俊𤋮', '马腾子乐');
|
||
|
||
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('西溪3号楼批量导入，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;
|