/**
* Returns a query object with the basics all set up to get assignment stuff
*
* @global moodle_database $DB
* @return block_ajax_marking_query_base
*/
public function query_factory()
{
global $USER;
$query = new block_ajax_marking_query_base($this);
$query->add_from(array('table' => $this->modulename, 'alias' => 'moduletable'));
$query->add_from(array('join' => 'INNER JOIN', 'table' => 'workshop_submissions', 'alias' => 'sub', 'on' => 'sub.workshopid = moduletable.id'));
$query->add_from(array('join' => 'LEFT JOIN', 'table' => 'workshop_assessments', 'alias' => 'a', 'on' => 'sub.id = a.submissionid'));
// Standard userid for joins.
$query->add_select(array('table' => 'sub', 'column' => 'authorid', 'alias' => 'userid'));
$query->add_select(array('table' => 'sub', 'column' => 'timemodified', 'alias' => 'timestamp'));
// Assumes that we want to see stuff that has not been assessed by the current user yet. Perhaps
// we have more than one assessor? Perhaps it's peer assessment only?
$query->add_where(array('type' => 'AND', 'condition' => 'NOT EXISTS(
SELECT 1
FROM {workshop_assessments} workshop_assessments
WHERE workshop_assessments.submissionid = sub.id
AND workshop_assessments.reviewerid = :workshopuserid
AND workshop_assessments.grade != -1
)'));
$query->add_where(array('type' => 'AND', 'condition' => 'moduletable.phase < ' . workshop::PHASE_CLOSED));
// Do we want to only see stuff when the workshop has been put into a later phase?
// If it has, a teacher will have done this manually and will know about the grading work.
// Unless there are two teachers.
$query->add_param('workshopuserid', $USER->id);
return $query;
}
/**
* Wraps the countwrapper query so that a join can be done to the table(s) that hold the name
* and other text fields which provide data for the node labels. We can't put the text
* fields into the countwrapper because Oracle won't have any of it.
*
* @static
* @param $countwrapperquery
* @param $filters
* @return block_ajax_marking_query_base
*/
private static function get_display_query($countwrapperquery, $filters)
{
// The outermost query just joins the already counted nodes with their display data e.g. we
// already have a count for each courseid, now we want course name and course description
// but we don't do this in the counting bit so as to avoid weird issues with group by on
// Oracle.
$displayquery = new block_ajax_marking_query_base();
$modulename = null;
if (!empty($filters['coursemoduleid']) && is_numeric($filters['coursemoduleid'])) {
$moduleobject = self::get_module_object_from_cmid($filters['coursemoduleid']);
if ($moduleobject) {
$modulename = $moduleobject->get_module_name();
}
}
$nextnodefilter = block_ajax_marking_get_nextnodefilter_from_params($filters);
$displayquery->add_select(array('table' => 'countwrapperquery', 'column' => 'id', 'alias' => $nextnodefilter));
$displayquery->add_select(array('table' => 'countwrapperquery', 'column' => 'itemcount'));
$displayquery->add_select(array('table' => 'countwrapperquery', 'column' => 'timestamp'));
$displayquery->add_select(array('table' => 'countwrapperquery', 'column' => 'recentcount'));
$displayquery->add_select(array('table' => 'countwrapperquery', 'column' => 'mediumcount'));
$displayquery->add_select(array('table' => 'countwrapperquery', 'column' => 'overduecount'));
$havecoursemodulefilter = array_key_exists('coursemoduleid', $filters);
if ($havecoursemodulefilter) {
// Need to have this pass through in case we have a mixture.
$displayquery->add_select(array('table' => 'countwrapperquery', 'column' => 'modulename'));
}
$displayquery->add_from(array('table' => $countwrapperquery, 'alias' => 'countwrapperquery', 'subquery' => true));
reset($filters);
foreach ($filters as $filtername => $filtervalue) {
if ($filtervalue == 'nextnodefilter') {
// This will attach an id to the query, to either be retrieved directly from the moduleunion,
// or added via a join of some sort.
self::add_query_filter($displayquery, $filtername, 'current', null, $modulename);
// If this one needs it, we add the decorator that gets the config settings.
// TODO this is not a very elegant way of determining this.
// Currently, we use the same wrapper for the display query, no matter what the mechanism
// for getting the settings into the countwrapper query is, because they will just have standard
// aliases. We don't always need it though.
if (in_array($filtername, array('courseid', 'coursemoduleid'))) {
self::add_query_filter($displayquery, 'core', 'select_config_display_displayquery');
}
}
}
return $displayquery;
}
/**
* Returns a query object with the basics all set up to get assignment stuff
*
* @global moodle_database $DB
* @return block_ajax_marking_query_base
*/
public function query_factory()
{
global $DB;
$query = new block_ajax_marking_query_base($this);
$query->add_from(array('table' => 'assignment', 'alias' => 'moduletable'));
$query->add_from(array('join' => 'INNER JOIN', 'table' => 'assignment_submissions', 'alias' => 'sub', 'on' => 'sub.assignment = moduletable.id'));
// Standard userid for joins.
$query->add_select(array('table' => 'sub', 'column' => 'userid'));
$query->add_select(array('table' => 'sub', 'column' => 'timemodified', 'alias' => 'timestamp'));
// First bit: not graded
// Second bit of first bit: has been resubmitted
// Third bit: if it's advanced upload, only care about the first bit if 'send for marking'
// was clicked.
$commentstring = $DB->sql_compare_text('sub.submissioncomment');
$assignmenttypestring = $DB->sql_compare_text('moduletable.assignmenttype');
$datastring = $DB->sql_compare_text('sub.data2');
// Resubmit seems not to be used for upload types.
$query->add_where(array('type' => 'AND', 'condition' => "( (sub.grade = -1 AND {$commentstring} = '') /* Never marked */\n OR\n ( ( moduletable.resubmit = 1\n OR ({$assignmenttypestring} = 'upload' AND moduletable.var4 = 1)\n ) /* Resubmit allowed */\n AND (sub.timemodified > sub.timemarked) /* Resubmit happened */\n )\n )\n /* Not in draft state */\n AND ( {$assignmenttypestring} != 'upload'\n OR ( {$assignmenttypestring} = 'upload' AND {$datastring} = 'submitted'))\n AND {$assignmenttypestring} != 'offline'\n "));
// TODO only sent for marking.
// Advanced upload: data2 will be 'submitted' and grade will be -1, but if 'save changes'
// is clicked, timemarked will be set to time(), but actually, grade and comment may
// still be empty.
return $query;
}
/**
* Returns a query object with the basics all set up to get assignment stuff
*
* @global moodle_database $DB
* @return block_ajax_marking_query_base
*/
public function query_factory()
{
global $DB;
$query = new block_ajax_marking_query_base($this);
$query->add_from(array('table' => $this->modulename, 'alias' => 'moduletable'));
$query->add_from(array('table' => 'quiz_attempts', 'on' => 'moduletable.id = quiz_attempts.quiz'));
$query->add_from(array('table' => 'question_attempts', 'on' => 'question_attempts.questionusageid = quiz_attempts.uniqueid'));
$query->add_from(array('table' => 'question_attempt_steps', 'alias' => 'sub', 'on' => 'question_attempts.id = sub.questionattemptid'));
$query->add_from(array('table' => 'question', 'on' => 'question_attempts.questionid = question.id'));
// Standard userid for joins.
$query->add_select(array('table' => 'quiz_attempts', 'column' => 'userid'));
$query->add_select(array('table' => 'sub', 'column' => 'timecreated', 'alias' => 'timestamp'));
$query->add_where(array('type' => 'AND', 'condition' => 'quiz_attempts.timefinish > 0'));
$query->add_where(array('type' => 'AND', 'condition' => 'quiz_attempts.preview = 0'));
$comparesql = $DB->sql_compare_text('question_attempts.behaviour') . " = 'manualgraded'";
$query->add_where(array('type' => 'AND', 'condition' => $comparesql));
$query->add_where(array('type' => 'AND', 'condition' => "sub.state = '" . question_state::$needsgrading . "' "));
// We want to get a list of graded states so we can retrieve all questions that don't have
// one.
$gradedstates = array();
$us = new ReflectionClass('question_state');
foreach ($us->getStaticProperties() as $name => $class) {
/* @var question_state $class */
if ($class->is_graded()) {
$gradedstates[] = $name;
}
}
list($gradedsql, $gradedparams) = $DB->get_in_or_equal($gradedstates, SQL_PARAMS_NAMED, 'quizq001');
$subsql = "NOT EXISTS( SELECT 1\n FROM {question_attempt_steps} st\n WHERE st.state {$gradedsql}\n AND st.questionattemptid = question_attempts.id)";
$query->add_where(array('type' => 'AND', 'condition' => $subsql));
$query->add_params($gradedparams);
return $query;
}
/**
* This is for when a courseid node is an ancestor of the node that has been
* selected, so we just do a where.
*
* @param block_ajax_marking_query_base $query
* @param int $courseid
* @SuppressWarnings(PHPMD.UnusedPrivateMethod) Dynamic method names don't register
*/
public static function configwhere_filter(block_ajax_marking_query_base $query, $courseid)
{
$conditions = array('type' => 'AND', 'condition' => 'course_modules.course = :courseidfiltercourseid');
$query->add_where($conditions);
$query->add_param('courseidfiltercourseid', $courseid);
}
/**
* For each module, we need to see if we can actually get any data back using the query from
* the module's query factory. Possible problem with third (fourth?) party module access code,
* so check first to see if the generator can handle making one to test with.
*/
public function test_module_query_factories()
{
global $DB;
// Assignment module is disabled in the PHPUnit DB, so we need to re-enable it.
$DB->set_field('modules', 'visible', 1, array('name' => 'assignment'));
$classes = block_ajax_marking_get_module_classes();
foreach ($classes as $modclass) {
$modname = $modclass->get_module_name();
// We need some submissions, but these are different for every module.
// Without a standardised way of doing this, we will use methods in this class to do
// the job until a better way emerges.
$createdatamethod = 'create_' . $modname . '_submission_data';
if (method_exists($this, $createdatamethod)) {
// Let the modules decide what number of things should be expected. Some are more
// complex than others.
$expectedcount = $this->{$createdatamethod}();
if (empty($expectedcount)) {
continue;
}
} else {
// No point carrying on without some data to check.
continue;
}
// Make query.
$query = $modclass->query_factory();
// We will get an error if we leave it like this as the userids in the first
// column are not unique.
$wrapper = new block_ajax_marking_query_base();
$wrapper->add_select(array('function' => 'COUNT', 'column' => '*', 'alias' => 'count'));
$wrapper->add_from(array('table' => $query, 'alias' => 'modulequery'));
// Run query. Get one stdClass with a count property.
$unmarkedstuff = $wrapper->execute();
// Make sure we get the right number of things back.
$this->assertEquals($expectedcount, reset($unmarkedstuff)->count);
// Now make sure we have the right columns for the SQL UNION ALL.
// We will get duplicate user ids causing problems in the first column if we
// use standard DB functions.
$records = $query->execute(true);
$firstrow = $records->current();
}
}
/**
* Returns a query object with the basics all set up to get assignment stuff
*
* @global moodle_database $DB
* @return block_ajax_marking_query_base
*/
public function query_factory()
{
global $USER;
$query = new block_ajax_marking_query_base($this);
// This currently does a simple check for whether or not the current user has added a
// rating or not. No scope for another teacher to do all the marking, or some of it.
list($notmyratingsql, $notmyratingparams) = $this->get_teacher_sql();
$query->add_params($notmyratingparams);
$query->add_from(array('table' => 'forum_posts', 'alias' => 'sub'));
$query->add_from(array('table' => 'forum_discussions', 'alias' => 'discussions', 'on' => 'sub.discussion = discussions.id'));
$query->add_from(array('table' => $this->modulename, 'alias' => 'moduletable', 'on' => 'discussions.forum = moduletable.id'));
// We need the context id to check the ratings table in the teacher SQL.
$query->add_from(array('table' => 'course_modules', 'alias' => 'forumcoursemodules', 'on' => 'moduletable.id = forumcoursemodules.instance ' . 'AND forumcoursemodules.module = ' . $this->get_module_id()));
$query->add_from(array('table' => 'context', 'alias' => 'forumcontext', 'on' => 'forumcoursemodules.id = forumcontext.instanceid ' . 'AND forumcontext.contextlevel = ' . CONTEXT_MODULE));
// Standard userid for joins.
$query->add_select(array('table' => 'sub', 'column' => 'userid'));
$query->add_select(array('table' => 'sub', 'column' => 'modified', 'alias' => 'timestamp'));
$query->add_where(array('type' => 'AND', 'condition' => 'sub.userid <> :forumuserid'));
$query->add_where(array('type' => 'AND', 'condition' => 'moduletable.assessed > 0'));
$query->add_where(array('type' => 'AND', 'condition' => " {$notmyratingsql} "));
$query->add_where(array('type' => 'AND', 'condition' => '( (moduletable.assesstimestart = 0) OR
(sub.created >= moduletable.assesstimestart) ) '));
$query->add_where(array('type' => 'AND', 'condition' => '( (moduletable.assesstimefinish = 0) OR
(sub.created <= moduletable.assesstimefinish) )'));
$query->add_param('forumuserid', $USER->id);
return $query;
}
/**
* Returns a query object with the basics all set up to get assignment stuff
*
* @global moodle_database $DB
* @return block_ajax_marking_query_base
*/
public function query_factory()
{
global $DB, $USER;
$query = new block_ajax_marking_query_base($this);
$query->add_from(array('table' => 'assign', 'alias' => 'moduletable'));
$query->add_from(array('join' => 'INNER JOIN', 'table' => 'assign_submission', 'alias' => 'sub', 'on' => 'sub.assignment = moduletable.id'));
// LEFT JOIN, rather than NOT EXISTS because we may have an empty form saved, which
// will create a grade record, but with a null grade. These should still count as ungraded.
$query->add_from(array('join' => 'LEFT JOIN', 'table' => 'assign_grades', 'on' => 'assign_grades.assignment = moduletable.id AND
assign_grades.userid = sub.userid AND
assign_grades.grader = :assigngraderid'));
$query->add_param('assigngraderid', $USER->id);
// Standard user id for joins.
$query->add_select(array('table' => 'sub', 'column' => 'userid'));
$query->add_select(array('table' => 'sub', 'column' => 'timemodified', 'alias' => 'timestamp'));
$statustext = $DB->sql_compare_text('sub.status');
$query->add_where(array('type' => 'AND', 'condition' => $statustext . " = '" . ASSIGN_SUBMISSION_STATUS_SUBMITTED . "'"));
$query->add_where(array('type' => 'AND', 'condition' => 'assign_grades.grade IS NULL'));
// First bit: not graded
// Second bit of first bit: has been resubmitted
// Third bit: if it's advanced upload, only care about the first bit if 'send for marking'
// was clicked.
return $query;
}
