mike's queries

gistfile1.sql

select distinct mc.shortname as course_id, mr.shortname as role, mu.username as loginname, mcon.contextlevel
from mdl_user_enrolments mue
join mdl_enrol me on mue.enrolid=me.id
join mdl_user mu on mue.userid=mu.id
join mdl_course mc on me.courseid=mc.id
join mdl_role_assignments mra on mu.id=mra.userid
join mdl_role mr on mra.roleid=mr.id
join mdl_context mcon on mcon.instanceid=mc.id where mr.shortname='student' order by mc.shortname and mu.username=’craigl’;
 
-- yields
 
--           course_id          |  role   | loginname | contextlevel
-- ---------------------------+---------+-----------+--------------
-- B233c                       | student | craigl    |           30
-- B233c                       | student | craigl    |           50
-- B233c                       | student | craigl    |           70
-- Demo - Template4Conversions | student | craigl    |           50
-- Demo - Template4Conversions | student | craigl    |           70
-- Demo - Template4Conversions | student | craigl    |           80
-- Template4Conversions        | student | craigl    |           50
-- Template4Conversions        | student | craigl    |           70
-- Template4Conversions        | student | craigl    |           80
 
-- define('CONTEXT_USER', 30);
-- define('CONTEXT_COURSE', 50);
-- define('CONTEXT_MODULE', 70);
-- define('CONTEXT_BLOCK', 80);
 
 
-- And they’re all showing student which is obviously not right in this case because craig is an ‘editingteacher’ in ‘Demo - Template4Conversions’
 
-- This query:
select mc.shortname as courseshortname,  mr.shortname as roleshortname, mu.username
from mdl_course mc
join mdl_context mcon on mcon.contextlevel=50 and mcon.instanceid=mc.id
join mdl_role_assignments mra on mra.contextid=mcon.id
join mdl_role mr on mr.id=mra.roleid
join mdl_user mu on mu.id = mra.userid
where  mu.username='craigl';
 
 
-- yields:
-- courseshortname       | roleshortname  | username
-- ---------------------------+----------------+----------
-- B233c                       | student        | craigl
-- Template4Conversions        | editingteacher | craigl
-- Demo - Template4Conversions | editingteacher | craigl
 
--RAS Engage Report Queries
--find most recent DF post (example)
select mu.username,mu.firstname,mu.lastname,mc.fullname as course_title,mc.shortname as course_id, to_timestamp(max(mfp.created)) as date_posted
from mdl_forum_posts mfp
left join mdl_forum_discussions mfd on mfp.discussion=mfd.id
left join mdl_user mu on mfp.userid=mu.id
left join mdl_course mc on mfd.course=mc.id
group by mu.username, mu.firstname, mu.lastname, mc.fullname, mc.shortname;
 
--lda report by category (Accelerated Last Attendance in Course)
select distinct mu.username,mu.firstname,mu.lastname,mc.fullname as course_title,mc.shortname as course_id,
(select to_timestamp(max(X)) from
                (
                select max(mfp.created) as X from mdl_forum_posts mfp join mdl_forum_discussions mfd on mfp.discussion=mfd.id where mfd.course=mc.id and mfp.userid=mu.id
                union
                select max(mqa.timefinish) as X from mdl_quiz_attempts mqa join mdl_quiz mq on mqa.quiz=mq.id where mq.course=mc.id and mqa.userid=mu.id
                union
                select max(mas.timecreated) as X from mdl_assignment_submissions mas join mdl_assignment ma on mas.assignment=ma.id where ma.course=mc.id and mas.userid=mu.id
                ) as t2
) as LDA
from mdl_user_enrolments mue
join mdl_enrol me on mue.enrolid=me.id
join mdl_course mc on me.courseid=mc.id
join mdl_user mu on mue.userid=mu.id
join mdl_role_assignments mra on mu.id=mra.userid
join mdl_role mr on mra.roleid=mr.id
where mc.category = '1' and -- mdl_course_categories.id = '1' and name='Spring 2012' in STM database
mr.shortname='student'
order by mc.shortname, mu.lastname, mu.firstname;
 
--lda report by user (Accelerated LDA Lookup)
select distinct mu.username,mu.firstname,mu.lastname,mc.fullname as course_title,mc.shortname as course_id,
(select to_timestamp(max(X)) from
                (
                select max(mfp.created) as X from mdl_forum_posts mfp join mdl_forum_discussions mfd on mfp.discussion=mfd.id where mfd.course=mc.id and mfp.userid=mu.id
                union
                select max(mqa.timefinish) as X from mdl_quiz_attempts mqa join mdl_quiz mq on mqa.quiz=mq.id where mq.course=mc.id and mqa.userid=mu.id
                union
                select max(mas.timecreated) as X from mdl_assignment_submissions mas join mdl_assignment ma on mas.assignment=ma.id where ma.course=mc.id and mas.userid=mu.id
                ) as t2
) as LDA
from mdl_user_enrolments mue
join mdl_enrol me on mue.enrolid=me.id
join mdl_course mc on me.courseid=mc.id
join mdl_user mu on mue.userid=mu.id
join mdl_role_assignments mra on mu.id=mra.userid
join mdl_role mr on mra.roleid=mr.id
where mc.category = '1' and -- mdl_course_categories.id = '1' and name='Spring 2012' in STM database
mr.shortname='student' and
mu.username='mike.buchanon' --mdl_user.username is like ANGEL loginname
order by mc.shortname, mu.lastname, mu.firstname;
 
--Accelerated Orientation Monitoring Report
select username,
case when firstaccess is not null then to_char(to_timestamp(firstaccess), 'MM/DD/YYYY HH:MI:SS') else 'None' end as first_login,
case when lastlogin is not null then to_char(to_timestamp(lastlogin), 'MM/DD/YYYY HH:MI:SS') else 'None' end as last_login,
case when lastaccess is not null then to_char(to_timestamp(lastaccess), 'MM/DD/YYYY HH:MI:SS') else 'None' end as last_access,
(select case when (max(mfp.created    )) is not null then to_char(to_timestamp(max(mfp.created    )), 'MM/DD/YYYY HH:MI:SS') else 'no' end from mdl_forum_posts mfp join mdl_forum_discussions mfd on mfp.discussion=mfd.id where mfd.course=mc.id and mfp.userid=mu.id) as DiscussionPosted,
(select case when (max(mqa.timefinish )) is not null then to_char(to_timestamp(max(mqa.timefinish )), 'MM/DD/YYYY HH:MI:SS') else 'no' end from mdl_quiz_attempts mqa join mdl_quiz mq on mqa.quiz=mq.id where mq.course=mc.id and mqa.userid=mu.id) as QuizSubmitted,
(select case when (max(mas.timecreated)) is not null then to_char(to_timestamp(max(mas.timecreated)), 'MM/DD/YYYY HH:MI:SS') else 'no' end from mdl_assignment_submissions mas join mdl_assignment ma on mas.assignment=ma.id where ma.course=mc.id and mas.userid=mu.id) as AssignmentSubmitted
from mdl_user_enrolments mue
join mdl_enrol me on mue.enrolid=me.id
join mdl_course mc on me.courseid=mc.id
join mdl_user mu on mue.userid=mu.id
where mc.shortname = 'CHEM_201'; --shortname like ANGEL courses.course_id; mc.fullname like ANGEL courses.title

-- Next set

--RAS Engage Report Queries
--find most recent DF post (example)
select mu.username,mu.firstname,mu.lastname,mc.fullname as course_title,mc.shortname as course_id, to_timestamp(max(mfp.created)) as date_posted
from mdl_forum_posts mfp
left join mdl_forum_discussions mfd on mfp.discussion=mfd.id
left join mdl_user mu on mfp.userid=mu.id
left join mdl_course mc on mfd.course=mc.id
group by mu.username, mu.firstname, mu.lastname, mc.fullname, mc.shortname;
 
--lda report by category (Accelerated Last Attendance in Course) (might need to be updated for context)
select distinct mu.username,mu.firstname,mu.lastname,mc.fullname as course_title,mc.shortname as course_id,
(select to_timestamp(max(X)) from
                (
                select max(mfp.created) as X from mdl_forum_posts mfp join mdl_forum_discussions mfd on mfp.discussion=mfd.id where mfd.course=mc.id and mfp.userid=mu.id
                union
                select max(mqa.timefinish) as X from mdl_quiz_attempts mqa join mdl_quiz mq on mqa.quiz=mq.id where mq.course=mc.id and mqa.userid=mu.id
                union
                select max(mas.timecreated) as X from mdl_assignment_submissions mas join mdl_assignment ma on mas.assignment=ma.id where ma.course=mc.id and mas.userid=mu.id
                ) as t2
) as LDA
from mdl_user_enrolments mue
join mdl_enrol me on mue.enrolid=me.id
join mdl_course mc on me.courseid=mc.id
join mdl_user mu on mue.userid=mu.id
join mdl_role_assignments mra on mu.id=mra.userid
join mdl_role mr on mra.roleid=mr.id
where mc.category = '1' and -- mdl_course_categories.id = '1' and name='Spring 2012' in STM database
mr.shortname='student'
order by mc.shortname, mu.lastname, mu.firstname;
 
--lda report by user (Accelerated LDA Lookup)
select distinct mu.username,mu.firstname,mu.lastname,mc.fullname as course_title,mc.shortname as course_id,
(select to_timestamp(max(X)) from
                (
                select max(mfp.created) as X from mdl_forum_posts mfp join mdl_forum_discussions mfd on mfp.discussion=mfd.id where mfd.course=mc.id and mfp.userid=mu.id
                union
                select max(mqa.timefinish) as X from mdl_quiz_attempts mqa join mdl_quiz mq on mqa.quiz=mq.id where mq.course=mc.id and mqa.userid=mu.id
                union
                select max(mas.timecreated) as X from mdl_assignment_submissions mas join mdl_assignment ma on mas.assignment=ma.id where ma.course=mc.id and mas.userid=mu.id
                ) as t2
) as LDA
from mdl_user_enrolments mue
join mdl_enrol me on mue.enrolid=me.id
join mdl_course mc on me.courseid=mc.id
join mdl_user mu on mue.userid=mu.id
join mdl_role_assignments mra on mu.id=mra.userid
join mdl_role mr on mra.roleid=mr.id
where mc.category = '1' and -- mdl_course_categories.id = '1' and name='Spring 2012' in STM database
mr.shortname='student' and
mu.username='mike.buchanon' --mdl_user.username is like ANGEL loginname
order by mc.shortname, mu.lastname, mu.firstname;
 
--Accelerated Orientation Monitoring Report
select username,
case when firstaccess is not null then to_char(to_timestamp(firstaccess), 'MM/DD/YYYY HH:MI:SS') else 'None' end as first_login,
case when lastlogin is not null then to_char(to_timestamp(lastlogin), 'MM/DD/YYYY HH:MI:SS') else 'None' end as last_login,
case when lastaccess is not null then to_char(to_timestamp(lastaccess), 'MM/DD/YYYY HH:MI:SS') else 'None' end as last_access,
(select case when (max(mfp.created    )) is not null then to_char(to_timestamp(max(mfp.created    )), 'MM/DD/YYYY HH:MI:SS') else 'no' end from mdl_forum_posts mfp join mdl_forum_discussions mfd on mfp.discussion=mfd.id where mfd.course=mc.id and mfp.userid=mu.id) as DiscussionPosted,
(select case when (max(mqa.timefinish )) is not null then to_char(to_timestamp(max(mqa.timefinish )), 'MM/DD/YYYY HH:MI:SS') else 'no' end from mdl_quiz_attempts mqa join mdl_quiz mq on mqa.quiz=mq.id where mq.course=mc.id and mqa.userid=mu.id) as QuizSubmitted,
(select case when (max(mas.timecreated)) is not null then to_char(to_timestamp(max(mas.timecreated)), 'MM/DD/YYYY HH:MI:SS') else 'no' end from mdl_assignment_submissions mas join mdl_assignment ma on mas.assignment=ma.id where ma.course=mc.id and mas.userid=mu.id) as AssignmentSubmitted
from mdl_user_enrolments mue
join mdl_enrol me on mue.enrolid=me.id
join mdl_course mc on me.courseid=mc.id
join mdl_user mu on mue.userid=mu.id
where mc.shortname = 'CHEM_201'; --shortname like ANGEL courses.course_id; mc.fullname like ANGEL courses.title
 
--Accelerated Participation Stats (notes):
select * from mdl_user_lastaccess limit 10;
select * from mdl_context where contextlevel=50; --50 is constant in code that signifies 'course' /lib/accesslib.php constants
select * from mdl_role; --mr.shortname='student' to find student role_id
select * from mdl_role_assignments; -- mra.roleid links to id from mdl_role, userid to the user and contextid to mdl_conetxt to get courselinkage
--Accelerated Participation Stats Query
select courseshortname, total_students, number_of_students_accessed, (number_of_students_accessed/total_students * 100) as percentage
from
(
select mc.shortname as courseshortname,  count(mu.username) as total_students,
(
                select count(1)
                from mdl_course mc1
                join mdl_context mcon1 on mcon1.contextlevel=50 and mcon1.instanceid=mc1.id
                join mdl_role_assignments mra1 on mra1.contextid=mcon1.id
                join mdl_role mr1 on mr1.id=mra1.roleid
                join mdl_user mu1 on mu1.id = mra1.userid
                join mdl_user_lastaccess mul1 on mul1.courseid=mc1.id and mul1.userid=mu1.id
                where mc1.id=mc.id and mr1.shortname='student'
) as number_of_students_accessed
from mdl_course mc
join mdl_context mcon on mcon.contextlevel=50 and mcon.instanceid=mc.id
join mdl_role_assignments mra on mra.contextid=mcon.id
join mdl_role mr on mr.id=mra.roleid
join mdl_user mu on mu.id = mra.userid
join mdl_course_categories mcc on mcc.id=mc.category
where mr.shortname='student' --role name you're looking for
and mcc.name='Miscellaneous' --category name
group by mc.shortname, mc.id
order by mc.shortname
) as t;
 
--Accelerated Weekly Attendance for Administrative Assistants
select username, firstname, lastname, course_title, course_id, lda
from
(
select distinct mu.username,mu.firstname,mu.lastname,mc.fullname as course_title,mc.shortname as course_id,
(select to_timestamp(max(X)) from
                (
                select max(mfp.created) as X from mdl_forum_posts mfp join mdl_forum_discussions mfd on mfp.discussion=mfd.id where mfd.course=mc.id and mfp.userid=mu.id
                union
                select max(mqa.timefinish) as X from mdl_quiz_attempts mqa join mdl_quiz mq on mqa.quiz=mq.id where mq.course=mc.id and mqa.userid=mu.id
                union
                select max(mas.timecreated) as X from mdl_assignment_submissions mas join mdl_assignment ma on mas.assignment=ma.id where ma.course=mc.id and mas.userid=mu.id
                ) as t2
) as LDA
from mdl_user_enrolments mue
join mdl_enrol me on mue.enrolid=me.id
join mdl_course mc on me.courseid=mc.id
join mdl_user mu on mue.userid=mu.id
join mdl_role_assignments mra on mu.id=mra.userid
join mdl_role mr on mra.roleid=mr.id
where mc.category = '1' and -- mdl_course_categories.id = '1' and name='Spring 2012' in STM database
mr.shortname='student'
order by mc.shortname, mu.lastname, mu.firstname
) as q
where lda >=
(select  case when extract(dow from (now() - interval '7 days')::date) < 1 then
(now()- interval '7days')::date - (extract(dow from (now()- interval '7 days')::date) + (7 - 1))::int
else
(now() - interval '7days')::date - (extract(dow from (now()- interval '7 days')::date) - 1)::int
end) -- previous monday
and lda <=
(select case when extract(dow from now() ) < 0 then
now()::date - (extract(dow from now()) + (7 - 0))::int
else
now()::date - (extract(dow from now()) - 0)::int
end) -- previous sunday following the monday from above
 
--Accelerated Online Course Final Grades for Administrative Assistants --may want to link to student campus for GROUPING
select mc.shortname as course_Id,mu.username,
case when ABS(ROUND(mgg.finalgrade, 0) - mgg.finalgrade) <= 0.0000005 then cast(mgg.finalgrade as text) else 'Pending...' end as final_grade,
mgi.itemname
from mdl_grade_grades mgg
join mdl_user mu on mgg.userid=mu.id
join mdl_user_enrolments mue on mue.userid=mu.id
join mdl_grade_items mgi on mgg.itemid=mgi.id
join mdl_course mc on mc.id=mgi.courseid
join mdl_enrol me on mue.enrolid=me.id and me.courseid=mc.id
join mdl_role_assignments mra on mu.id=mra.userid
join mdl_role mr on mra.roleid=mr.id
where mc.category = '1' and -- mdl_course_categories.id = '1'; id=1 => name='Spring 2012' as an example
mr.shortname='student' and -- only get people enrolled with a 'student' role in the course
mgi.itemname ilike '%Final%Grade%'; --anything with 'final' and 'grade' in it regardless of case and surrounding text
 
--Accelerated Weekly Grades for Administrative Assistants --look for overall average or overall grade on a per student basis
SELECT u.firstname AS First , u.lastname AS Last, u.firstname || ' ' || u.lastname AS Display_Name,
cc.name AS Category,
CASE
  WHEN gi.itemtype = 'course'
   THEN c.fullname || ' Course Total'
  ELSE gi.itemname
END AS Item_Name,
ROUND(gg.finalgrade,2) AS Grade
FROM mdl_course AS c
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS u ON u.id = ra.userid
JOIN mdl_grade_grades AS gg ON gg.userid = u.id
JOIN mdl_grade_items AS gi ON gi.id = gg.itemid
JOIN mdl_course_categories AS cc ON cc.id = c.category
WHERE  gi.courseid = c.id AND gi.itemtype = 'course'
ORDER BY lastname;