pwugh1eH

1
2DELIMITER $$
3
4CREATE STORED PROCEDURE IF NOT EXISTS ACTIVITYGRAB(
5
6
7
8
9
10
11
12CREATE TEMPORARY TABLE 
13IF NOT EXISTS STUDENTGRAB1 AS (
14
15
16SELECT
17u.id as userid,
18CONCAT(u.firstname, ' ', u.lastname)AS STUDENTS, 
19            u.username,
20			u.idnumber,
21            cour.fullname,
22		    cour.id AS courseid,
23            groupname
24
25            
26from mdl_user as u
27INNER JOIN mdl_user_enrolments ue ON ue.userid = u.id
28INNER JOIN mdl_enrol e ON e.id = ue.enrolid
29INNER JOIN mdl_role_assignments as ra ON ra.userid = u.id
30INNER JOIN mdl_role as r ON ra.roleid = r.id
31INNER JOIN mdl_context as con ON ra.contextid = con.id
32INNER JOIN mdl_course as cour ON cour.id = con.instanceid AND con.contextlevel = 50
33Left Outer Join 
34(select gr.name as groupname,gm.userid as gmuserid,cour.fullname as coursefullname 
35from  mdl_groups as gr
36Inner join mdl_course as cour on  gr.courseid = cour.id 
37Inner Join mdl_groups_members as gm on gm.groupid = gr.id
38Inner Join mdl_user as u on u.id = gm.userid) as groups on u.id = gmuserid and cour.fullname = coursefullname 
39and u.suspended = 0 and u.deleted = 0 and r.shortname = 'student'
40 and r.id = 5 );
41 
42 CREATE INDEX sg_userid ON STUDENTGRAB(userid);
43 CREATE INDEX sg_courseid ON STUDENTGRAB(courseid);
44 
45 
46CREATE TEMPORARY TABLE IF NOT EXISTS 
47SUBQUERY1 AS (
48   
49SELECT  distinct concat(cm.module,cm.instance) as modinstance FROM mdl_course_modules_completion cmc
50INNER JOIN mdl_course_modules cm ON cm.id=cmc.coursemoduleid   
51INNER JOIN mdl_course as cour on cour.id on cm.course
52INNER JOIN mdl_enrol as e on e.courseid = cour.id
53INNER JOIN mdl_user_enrolments as ue on ue.userid = u.id 
54INNER JOIN STUDENTGRAB as sg on sg.userid = ue.userid and cour.id = sg.courseid);
55
56CREATE INDEX sq_modinstance ON SUBQUERY1(modinstance);
57
58
59
60SELECT u.id as userid,
61u.username,
62c.id courseID,
63c.fullname CourseName,
64module.id ModuleID,
65module.name ModuleName,
66cm.section SectionID, 
67cs.section,
68cm.instance InstanceID,
69	CASE module.name 
70		WHEN 'assign' THEN (select name from mdl_assign WHERE id=instance)
71		WHEN 'book' THEN (select name from mdl_book WHERE id=instance)
72		WHEN 'chat' THEN (select name from mdl_chat WHERE id=instance)
73		WHEN 'choice' THEN (select name from mdl_choice WHERE id=instance)
74		WHEN 'data' THEN (select name from mdl_data WHERE id=instance)
75		WHEN 'feedback' THEN (select name from mdl_feedback WHERE id=instance)
76		WHEN 'folder' THEN (select name from mdl_folder WHERE id=instance)
77		WHEN 'forum' THEN (select name from mdl_forum WHERE id=instance)
78		WHEN 'glossary' THEN (select name from mdl_glossary WHERE id=instance)
79		WHEN 'imscp' THEN (select name from mdl_imscp WHERE id=instance)
80		WHEN 'label' THEN (select name from mdl_label WHERE id=instance)
81		WHEN 'lesson' THEN (select name from mdl_lesson WHERE id=instance)
82		WHEN 'lti' THEN (select name from mdl_lti WHERE id=instance)
83		WHEN 'page' THEN (select name from mdl_page WHERE id=instance)
84		WHEN 'quiz' THEN (select name from mdl_quiz WHERE id=instance)
85		WHEN 'resource' THEN (select name from mdl_resource WHERE id=instance)
86		WHEN 'scorm' THEN (select name from mdl_scorm WHERE id=instance)
87		WHEN 'survey' THEN (select name from mdl_survey WHERE id=instance)
88		WHEN 'url' THEN (select name from mdl_url WHERE id=instance)
89		WHEN 'wiki' THEN (select name from mdl_wiki WHERE id=instance)
90		WHEN 'workshop' THEN (select name from mdl_workshop WHERE id=instance)
91		WHEN 'certificate' THEN (select name from mdl_certificate WHERE id=instance)
92
93			ELSE 'other types' 
94	END AS instanceName,
95if(concat(cm.module,cm.instance) 
96	IN(
97	SELECT modinstance from SUBQUERY1 
98	     ),1, 0) AS Completed
99
100	
101FROM mdl_user u
102	INNER JOIN mdl_user_enrolments ue ON ue.userid = u.id
103	INNER JOIN mdl_enrol e ON e.id = ue.enrolid
104	INNER JOIN mdl_course c ON e.courseid = c.id
105	INNER JOIN mdl_course_modules cm ON c.id=cm.course
106	INNER JOIN mdl_modules module ON module.id=cm.module
107	INNER JOIN mdl_course_sections cs ON cs.id=cm.section
108	INNER JOIN STUDENTGRAB as sg ON sg.courseid = c.id and sg.userid = u.id
109WHERE u.suspended = 0 
110AND u.deleted = 0 
111AND c.enablecompletion = 1
112
113
114
115DROP TABLE STUDENTGRAB;
116DROP TABLE SUBQUERY1;
117
118END $$
119
120
121
122
123
124;