Changes between Version 12 and Version 13 of AdminTips

Timestamp:

2013-02-23T02:41:17Z (12 years ago)

Author:

Pan Luo

Comment:

--

AdminTips

@@ -80 +80 @@
 }}}
 
+=== Merge Students old accounts into new ones ===
+The old student accounts using student number as username and the new ones uses PUID.
+{{{
+#!sql
+DROP PROCEDURE IF EXISTS merge_accounts;
+DROP PROCEDURE IF EXISTS merge_by_student_no;
+
+delimiter //
+
+CREATE PROCEDURE merge_accounts()
+BEGIN
+        DECLARE old, new, count INT;
+        DECLARE sn VARCHAR(20);
+        DECLARE done INT DEFAULT FALSE;
+        DECLARE cur CURSOR FOR SELECT student_no, count(*) c FROM users group by student_no having c = 2;
+        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+
+        OPEN cur;
+
+        read_loop: LOOP
+                        FETCH cur INTO sn, count;
+                        IF done THEN
+                                        LEAVE read_loop;
+                        END IF;
+         CALL merge_by_student_no(sn);
+
+        END LOOP;
+
+        CLOSE cur;
+END//
+
+CREATE PROCEDURE merge_by_student_no(IN sn VARCHAR(20))
+BEGIN
+        DECLARE target_id, source_id, temp_id INT;
+        DECLARE username_v1,username_v2 VARCHAR(20);
+        DECLARE done INT DEFAULT FALSE;
+        DECLARE cur CURSOR FOR (SELECT a.id, b.id, a.username, b.username FROM 
+                (SELECT id, username FROM users WHERE student_no = sn AND username != student_no AND LENGTH(username) = 12) as a,
+                (SELECT id, username FROM users WHERE student_no = sn AND username = student_no AND LENGTH(username) != 12) as b);
+        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+
+        OPEN cur;
+        
+        read_loop: LOOP
+                FETCH cur INTO target_id, source_id, username_v1, username_v2;
+                IF done THEN
+                                LEAVE read_loop;
+                END IF;
+
+                SELECT CONCAT("Merging user ", source_id, "(", username_v2,") into ", target_id, "(",username_v1,")") as "";
+       UPDATE IGNORE groups_members set user_id = target_id where user_id = source_id; 
+       UPDATE IGNORE user_enrols set user_id = target_id where user_id = source_id; 
+                UPDATE IGNORE survey_group_members set user_id = target_id where user_id = source_id; 
+       UPDATE survey_inputs set user_id = target_id where user_id = source_id; 
+       UPDATE evaluation_submissions set submitter_id = target_id where submitter_id = source_id; 
+       UPDATE evaluation_simples set evaluator = target_id where evaluator = source_id; 
+       UPDATE evaluation_simples set evaluatee = target_id where evaluatee = source_id; 
+                UPDATE evaluation_simples set creator_id = target_id where creator_id = source_id; 
+                UPDATE evaluation_simples set updater_id = target_id where updater_id = source_id; 
+       UPDATE evaluation_rubrics set evaluator = target_id where evaluator = source_id; 
+       UPDATE evaluation_rubrics set evaluatee = target_id where evaluatee = source_id; 
+                UPDATE evaluation_rubrics set creator_id = target_id where creator_id = source_id; 
+                UPDATE evaluation_rubrics set updater_id = target_id where updater_id = source_id; 
+                UPDATE evaluation_rubric_details set creator_id = target_id where creator_id = source_id; 
+                UPDATE evaluation_rubric_details set updater_id = target_id where updater_id = source_id; 
+       UPDATE evaluation_mixevals set evaluator = target_id where evaluator = source_id; 
+       UPDATE evaluation_mixevals set evaluatee = target_id where evaluatee = source_id; 
+                UPDATE evaluation_mixevals set creator_id = target_id where creator_id = source_id; 
+                UPDATE evaluation_mixevals set updater_id = target_id where updater_id = source_id; 
+                UPDATE evaluation_mixeval_details set creator_id = target_id where creator_id = source_id; 
+                UPDATE evaluation_mixeval_details set updater_id = target_id where updater_id = source_id; 
+       DELETE from users where id = source_id LIMIT 1;
+
+        END LOOP;
+        CLOSE cur;
+END//
+
+delimiter ;
+
+}}}
+
 === Remove the Leading "s" in Username ===
 {{{