Changes between Version 12 and Version 13 of AdminTips


Ignore:
Timestamp:
2013-02-23T02:41:17Z (11 years ago)
Author:
Pan Luo
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • AdminTips

    v12 v13  
    8080}}}
    8181
     82=== Merge Students old accounts into new ones ===
     83The old student accounts using student number as username and the new ones uses PUID.
     84{{{
     85#!sql
     86DROP PROCEDURE IF EXISTS merge_accounts;
     87DROP PROCEDURE IF EXISTS merge_by_student_no;
     88
     89delimiter //
     90
     91CREATE PROCEDURE merge_accounts()
     92BEGIN
     93        DECLARE old, new, count INT;
     94        DECLARE sn VARCHAR(20);
     95        DECLARE done INT DEFAULT FALSE;
     96        DECLARE cur CURSOR FOR SELECT student_no, count(*) c FROM users group by student_no having c = 2;
     97        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     98
     99        OPEN cur;
     100
     101        read_loop: LOOP
     102                        FETCH cur INTO sn, count;
     103                        IF done THEN
     104                                        LEAVE read_loop;
     105                        END IF;
     106         CALL merge_by_student_no(sn);
     107
     108        END LOOP;
     109
     110        CLOSE cur;
     111END//
     112
     113CREATE PROCEDURE merge_by_student_no(IN sn VARCHAR(20))
     114BEGIN
     115        DECLARE target_id, source_id, temp_id INT;
     116        DECLARE username_v1,username_v2 VARCHAR(20);
     117        DECLARE done INT DEFAULT FALSE;
     118        DECLARE cur CURSOR FOR (SELECT a.id, b.id, a.username, b.username FROM
     119                (SELECT id, username FROM users WHERE student_no = sn AND username != student_no AND LENGTH(username) = 12) as a,
     120                (SELECT id, username FROM users WHERE student_no = sn AND username = student_no AND LENGTH(username) != 12) as b);
     121        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     122
     123        OPEN cur;
     124       
     125        read_loop: LOOP
     126                FETCH cur INTO target_id, source_id, username_v1, username_v2;
     127                IF done THEN
     128                                LEAVE read_loop;
     129                END IF;
     130
     131                SELECT CONCAT("Merging user ", source_id, "(", username_v2,") into ", target_id, "(",username_v1,")") as "";
     132       UPDATE IGNORE groups_members set user_id = target_id where user_id = source_id;
     133       UPDATE IGNORE user_enrols set user_id = target_id where user_id = source_id;
     134                UPDATE IGNORE survey_group_members set user_id = target_id where user_id = source_id;
     135       UPDATE survey_inputs set user_id = target_id where user_id = source_id;
     136       UPDATE evaluation_submissions set submitter_id = target_id where submitter_id = source_id;
     137       UPDATE evaluation_simples set evaluator = target_id where evaluator = source_id;
     138       UPDATE evaluation_simples set evaluatee = target_id where evaluatee = source_id;
     139                UPDATE evaluation_simples set creator_id = target_id where creator_id = source_id;
     140                UPDATE evaluation_simples set updater_id = target_id where updater_id = source_id;
     141       UPDATE evaluation_rubrics set evaluator = target_id where evaluator = source_id;
     142       UPDATE evaluation_rubrics set evaluatee = target_id where evaluatee = source_id;
     143                UPDATE evaluation_rubrics set creator_id = target_id where creator_id = source_id;
     144                UPDATE evaluation_rubrics set updater_id = target_id where updater_id = source_id;
     145                UPDATE evaluation_rubric_details set creator_id = target_id where creator_id = source_id;
     146                UPDATE evaluation_rubric_details set updater_id = target_id where updater_id = source_id;
     147       UPDATE evaluation_mixevals set evaluator = target_id where evaluator = source_id;
     148       UPDATE evaluation_mixevals set evaluatee = target_id where evaluatee = source_id;
     149                UPDATE evaluation_mixevals set creator_id = target_id where creator_id = source_id;
     150                UPDATE evaluation_mixevals set updater_id = target_id where updater_id = source_id;
     151                UPDATE evaluation_mixeval_details set creator_id = target_id where creator_id = source_id;
     152                UPDATE evaluation_mixeval_details set updater_id = target_id where updater_id = source_id;
     153       DELETE from users where id = source_id LIMIT 1;
     154
     155        END LOOP;
     156        CLOSE cur;
     157END//
     158
     159delimiter ;
     160
     161}}}
     162
    82163=== Remove the Leading "s" in Username ===
    83164{{{