Add extensions to user for specific module - UQ-eLIPSE/shifoo-docs GitHub Wiki

--
-- HOWTO: CREATE EXTENSION
--
set @USR = 's4740420';
--
-- view current modules and extensions (based on user, not course)
--
select *
from shifoo.user u
join shifoo.course c on u.course_id = c.id
join shifoo.module m on c.id = m.course_id
left outer join shifoo.extensions e on e.user_id = u.id and e.module_id = m.id
where u.username = @USR
order by m.order;
--
-- add extension record for specific module
-- 
set @MOD = 1;
insert into shifoo.extensions (user_id, module_id, due)
select u.id, m.id, m.due 
from shifoo.user u
join shifoo.course c on u.course_id = c.id
join shifoo.module m on c.id = m.course_id
where u.username = @USR
and m.order = @MOD;
--
-- Review extensions: Add extension interval to new extension
--
SELECT * FROM shifoo.extensions;
update shifoo.extensions set due = date_add(due, interval 7 day) where id = 4;
--
-- Report to Course Coordinator (cc student)
--
select u.username, c.context_id context, m.name module, m.due due, e.due extension
from shifoo.user u
join shifoo.course c on u.course_id = c.id
join shifoo.module m on c.id = m.course_id
left outer join shifoo.extensions e on e.user_id = u.id and e.module_id = m.id
where u.username = @USR
and m.id = @MOD;