[[PageOutline]]

== Database ==

=== Find student who saved the evaluation but not submit yet ===
{{{
#!sql
SET @EVENTID=EVENT_ID;
SELECT * FROM users 
WHERE id IN 
    (SELECT distinct(evaluator) FROM evaluation_mixevals WHERE event_id = @EVENTID AND evaluator NOT IN 
        (SELECT distinct(submitter_id) FROM evaluation_submissions WHERE event_id = @EVENTID)
    );
}}}

Replace EVENT_ID to event id.

=== Submit for those students ===
{{{
#!sql
SET @EVENTID=EVENT_ID, @COURSEID=COURSE_ID;
INSERT INTO evaluation_submissions 
    SELECT NULL,@EVENTID, group_events.id, user_id,1,NOW(),'A',1,NOW(),NULL,NOW() 
    FROM groups 
    LEFT JOIN groups_members ON groups.id = groups_members.group_id 
    LEFT JOIN group_events ON group_events.group_id = groups.id 
    WHERE
        course_id = @COURSEID
        AND event_id = @EVENTID
        AND user_id IN 
            (SELECT distinct(evaluator) FROM evaluation_mixevals WHERE event_id = @EVENTID AND evaluator NOT IN 
                (SELECT distinct(submitter_id) FROM evaluation_submissions WHERE event_id = @EVENTID)
            );
}}}

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//

delimiter ;

CALL merge_students();
}}}

=== Remove the Leading "s" in Username ===
{{{
#!sql
UPDATE users SET username = SUBSTRING(username, 2) WHERE username REGEXP 's[0-9]{8}';
}}}

=== Export Groups ===
{{{
#!sql
SELECT group_name, username  FROM `groups` as g JOIN groups_members as m on g.id = m.group_id JOIN users as u on u.id = m.user_id WHERE `course_id` = COURSE_ID
}}}

== Statistics ==
New course
{{{
#!sql
select * from courses where created between "2011-09-01 0:0:0" and "2011-12-31 23:59:59";
}}}

New evaluation
{{{
#!sql
select * from events where created between "2011-09-01 0:0:0" and "2011-12-31 23:59:59";
}}}

New instructor 
{{{
#!sql
select * from users where role LIKE "I" AND created between "2011-09-01 0:0:0" and "2011-12-31 23:59:59";
}}}

New student 
{{{
#!sql
select * from users where role LIKE "S" AND created between "2011-09-01 0:0:0" and "2011-12-31 23:59:59";
}}}

Total student 
{{{
#!sql
select * from users where role LIKE "S";
}}}