-- Course Export Information
-- Created by Nathan Garrett
-- http://profgarrett.com
-- All permission granted to modify, use, and distribute.
--

-- Intructions
-- Go into a course, the admin setting, choose reports, and then select dashboard.  Then  choose admin reports.
-- If you don't have admin access to Moodle, contact your IT department to run the code for you, or use a custom
-- module that allows you to create a custom SQL report.


SELECT 
assign.name,
stu.username,
gd.name as 'RubricName',
grc.description as 'CriteriaDescription', 
CONCAT ('(',ROUND(grl.score,0),') ', grl.definition) AS 'CriteriaScoreAndDefinition', grl.score,
grf.remark as GraderRemarks, grf.criterionid AS CriterionId,
rubm.username as Grader,
DATE_ADD('1970-01-01', INTERVAL gin.timemodified SECOND) as DateGraded,
course.id, course.fullname

FROM mdl_course course 
  INNER JOIN mdl_course_modules AS cm ON course.id = cm.course
  INNER JOIN mdl_assign AS assign ON cm.instance = assign.id
  INNER JOIN mdl_context AS c ON c.instanceid = cm.id
  INNER JOIN mdl_grading_areas AS ga ON c.id=ga.contextid
  INNER JOIN mdl_grading_definitions AS gd ON ga.id = gd.areaid
  INNER JOIN mdl_gradingform_rubric_criteria AS grc ON grc.definitionid = gd.id 
  INNER JOIN mdl_gradingform_rubric_levels AS grl ON grl.criterionid = grc.id
  INNER JOIN mdl_grading_instances AS gin ON gin.definitionid = gd.id
  INNER JOIN mdl_assign_grades AS ag ON ag.id = gin.itemid
  INNER JOIN mdl_user AS stu ON stu.id = ag.userid
  INNER JOIN mdl_user AS rubm ON rubm.id = gin.raterid
  INNER JOIN mdl_gradingform_rubric_fillings AS grf ON grf.instanceid = gin.id AND grf.criterionid = grc.id AND grf.levelid = grl.id

where 
gin.status = 1 AND


-- Our school uses the format 2014_Fall_MRKT.455.S1 for titles in Moodle.
-- 
-- If your school is similar, you can modify the below.
-- Otherwise, just replace the remaining text with the following sample, 
-- adding lines as needed and removing the -- symbols.
--
-- (
-- course.fullname = 'COURSENAME1'
-- OR
-- course.fullname = 'COURSENAME1'
-- )


-- Year 
LEFT(course.fullname,7) IN (
	'2018_Sp',
	'2017_Fa', '2016_Fa', '2015_Fa', '2014_Fa', '2013_Fa',
	'2018_Sp', '2017_Sp', '2016_Sp', '2015_Sp', '2014_Sp'
)

-- Course code
AND
(
course.fullname LIKE '%_MRKT.455.%'
OR
course.fullname LIKE '%_WMBA.505.%'
OR
course.fullname LIKE '%_WMBA.560.%'
OR
course.fullname LIKE '%_WMBA.509.%'
OR
course.fullname LIKE '%_MGMT.100.%'
OR
course.fullname LIKE '%_MGMT.110.%'
OR 
course.fullname LIKE '%_MGMT.336.%'
OR
course.fullname LIKE '%_MGMT.326.%'
OR 
course.fullname LIKE '%_FINA.360.%'
OR 
course.fullname LIKE '%_MRKT.301.%'
OR 
course.fullname LIKE '%_MGMT.350.%'
OR 
course.fullname LIKE '%_MGMT.461.%'
)