Changes between Version 8 and Version 9 of AdminTips

Timestamp:

2012-09-12T17:56:50Z (13 years ago)

Author:

Pan Luo

Comment:

--

AdminTips

@@ -36 +36 @@
 Replace EVENT_ID to event id and COURSE_ID to course id.
 
+=== Merge Students with Duplicated Accounts (the accounts starts with "s" and without) ===
+{{{
+#!sql
+DROP PROCEDURE IF EXISTS merge_students;
+
+delimiter //
+
+CREATE PROCEDURE merge_students()
+BEGIN
+        DECLARE old, new INT;
+        DECLARE done INT DEFAULT FALSE;
+        DECLARE cur CURSOR FOR (SELECT u1.id, u2.id FROM users as u1 JOIN users as u2 ON u2.username = CONCAT("s", u1.username) WHERE u1.username REGEXP '[0-9]{8}');
+        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+        
+        OPEN cur;
+        
+        read_loop: LOOP
+                FETCH cur INTO old, new;
+                IF done THEN
+                        LEAVE read_loop;
+                END IF;
+          SELECT CONCAT("Merging user ", new, " into ", old) as "";
+        update groups_members set user_id = old where user_id = new; 
+        update user_enrols set user_id = old where user_id = new; 
+        update survey_inputs set user_id = old where user_id = new; 
+        update evaluation_submissions set submitter_id = old where submitter_id = new; 
+        update evaluation_simples set evaluator = old where evaluator = new; 
+        update evaluation_simples set evaluatee = old where evaluatee = new; 
+        update evaluation_rubrics set evaluator = old where evaluator = new; 
+        update evaluation_rubrics set evaluatee = old where evaluatee = new; 
+        update evaluation_mixevals set evaluator = old where evaluator = new; 
+        update evaluation_mixevals set evaluatee = old where evaluatee = new; 
+        delete from users where id = new;
+
+        END LOOP;
+        
+        CLOSE cur;
+END;
+
+CALL merge_students();
+}}}
 
 == Statistics ==