/**
* Adds SQL to a dynamic query for when there is a question node as an ancestor of the current
* nodes.
*
* @static
* @param block_ajax_marking_query $query
* @return mixed|void
*/
protected function alter_query(block_ajax_marking_query $query)
{
// Apply WHERE clause.
$conditions = array('table' => 'question', 'column' => 'id', 'alias' => 'questionid');
// TODO needs applying to just the quiz one.
$query->add_select($conditions);
}
/**
* @param block_ajax_marking_query $query
* @param int|string $userid
*/
protected function alter_query(block_ajax_marking_query $query, $userid)
{
// Applies if users are not the final nodes.
$clause = array('type' => 'AND', 'condition' => 'quiz_attempts.userid = :quizuseridfilterancestor');
$query->add_where($clause);
$query->add_param('quizuseridfilterancestor', $userid);
}
/**
* Adds SQL to construct a set of discussion nodes.
*
* @param block_ajax_marking_query $query
* @return mixed|void
*/
protected function alter_query(block_ajax_marking_query $query)
{
// We join like this because we can't put extra stuff into the UNION ALL bit
// unless all modules have it and this is unique to forums.
$query->add_from(array('table' => 'forum_posts', 'on' => 'moduleunion.subid = post.id', 'alias' => 'post'));
$query->add_from(array('table' => 'forum_discussions', 'on' => 'discussion.id = post.discussion', 'alias' => 'discussion'));
$query->add_select(array('table' => 'discussion', 'column' => 'id'), true);
}
/**
* Applies the filter needed for course nodes or their descendants
*
* @param block_ajax_marking_query $query
* @SuppressWarnings(PHPMD.UnusedPrivateMethod) Dynamic method names don't register
*/
protected function alter_query(block_ajax_marking_query $query)
{
// This is for the displayquery when we are making course nodes.
$query->add_from(array('table' => 'course', 'alias' => 'course', 'on' => 'countwrapperquery.id = course.id'));
$query->add_select(array('table' => 'course', 'column' => 'shortname', 'alias' => 'name'));
$query->add_select(array('table' => 'course', 'column' => 'fullname', 'alias' => 'tooltip'));
$query->add_orderby('course.shortname ASC');
}
/**
* Applies the filter needed for course nodes or their descendants
*
* @param block_ajax_marking_query $query
*/
protected function alter_query(block_ajax_marking_query $query)
{
// This is for the displayquery when we are making course nodes.
$query->add_from(array('join' => 'LEFT JOIN', 'table' => 'groups', 'on' => 'countwrapperquery.id = groups.id'));
// We may get a load of people in no group.
$query->add_select(array('function' => 'COALESCE', 'table' => array('groups' => 'name', get_string('notingroup', 'block_ajax_marking')), 'alias' => 'name'));
$query->add_select(array('function' => 'COALESCE', 'table' => array('groups' => 'description', get_string('notingroupdescription', 'block_ajax_marking')), 'alias' => 'tooltip'));
$query->add_orderby("COALESCE(groups.name, '" . get_string('notingroup', 'block_ajax_marking') . "') ASC");
}
/**
* This will join the cohorts tables so tht the id can be added to the query in some way.
*
* @todo doesn't deal with a user being in more than one cohort yet.
* @param block_ajax_marking_query $query
* @return void
*/
protected function alter_query(block_ajax_marking_query $query)
{
// We need to join the userid to the cohort, if there is one.
// TODO when is there not one?
// Add join to cohort_members.
$table = array('join' => 'INNER JOIN', 'table' => 'cohort_members', 'on' => 'cohort_members.userid = moduleunion.userid');
$query->add_from($table);
$table = array('join' => 'INNER JOIN', 'table' => 'cohort', 'on' => 'cohort_members.cohortid = cohort.id');
$query->add_from($table);
}
/**
* Makes SQL for the text labels for the course nodes.
*
* @static
* @param block_ajax_marking_query $query
*/
protected function alter_query(block_ajax_marking_query $query)
{
// Same order as the super query will need them. Prefixed so we will have it as the
// first column for the GROUP BY.
$query->add_from(array('join' => 'INNER JOIN', 'table' => 'course_modules', 'on' => 'course_modules.id = countwrapperquery.id'));
$query->add_select(array('table' => 'course_modules', 'column' => 'id', 'alias' => 'coursemoduleid'));
// The javascript needs this for styling.
$query->add_select(array('table' => 'countwrapperquery', 'column' => 'modulename'));
// This will add the stuff that joins to the various module tables and gets the right names.
$moduleclasses = block_ajax_marking_get_module_classes();
$introcoalesce = array();
$namecoalesce = array();
$orderbycoalesce = array();
foreach ($moduleclasses as $moduleclass) {
$moduletablename = $moduleclass->get_module_name();
$query->add_from(array('join' => 'LEFT JOIN', 'table' => $moduletablename, 'on' => "(course_modules.instance = " . $moduletablename . ".id\n AND course_modules.module = '" . $moduleclass->get_module_id() . "')"));
$namecoalesce[$moduletablename] = 'name';
$introcoalesce[$moduletablename] = 'intro';
$orderbycoalesce[$moduletablename] = $moduletablename . '.name';
}
$query->add_select(array('table' => 'course_modules', 'column' => 'id', 'alias' => 'coursemoduleid'));
$query->add_select(array('table' => $namecoalesce, 'function' => 'COALESCE', 'column' => 'name', 'alias' => 'name'));
$query->add_select(array('table' => $introcoalesce, 'function' => 'COALESCE', 'column' => 'intro', 'alias' => 'tooltip'));
$query->add_orderby('COALESCE(' . implode(', ', $orderbycoalesce) . ') ASC');
// This will add the stuff that will show us the name of the actual module instance.
// We use the same stuff for both config and marking trees, but the config tree doesn't need
// the stuff to pull through submission counts.
// TODO separate counts.
// This allows us to have separate decorators, but may obfuscate what's happening a bit.
// Code is not duplicated, though.
}
/**
* This will add a groupid to each of the submissions coming out of the moduleunion. This means getting
* all the group memberships for this course and choosing the maximum groupid. We can't count more than
* once when students are in two groups, so we need to do it like this. It's a greatest-n-per-group
* problem, solved with the classic left-join approach.
*
* @param block_ajax_marking_query $query
* @return void
*/
protected function alter_query(block_ajax_marking_query $query)
{
list($maxquery, $maxparams) = block_ajax_marking_group_max_subquery();
list($memberquery, $memberparams) = block_ajax_marking_group_members_subquery();
list($visibilitysubquery, $visibilityparams) = block_ajax_marking_group_visibility_subquery();
// We need to join to groups members to see if there are any memberships at all (in which case
// we use the highest visible id if there is one), or 0 if there are no memberships at all.
$table = array('join' => 'LEFT JOIN', 'table' => $memberquery, 'on' => 'membergroupquery.userid = moduleunion.userid
AND membergroupquery.coursemoduleid = moduleunion.coursemoduleid', 'alias' => 'membergroupquery', 'subquery' => true);
$query->add_from($table);
$query->add_params($memberparams);
// To make sure it's the highest visible one, we use this subquery as a greatest-n-per-group thing.
$table = array('join' => 'LEFT JOIN', 'table' => $maxquery, 'on' => 'membergroupquery.userid = maxgroupquery.userid
AND membergroupquery.coursemoduleid = maxgroupquery.coursemoduleid
AND maxgroupquery.groupid > membergroupquery.groupid', 'alias' => 'maxgroupquery', 'subquery' => true);
$query->add_from($table);
$query->add_params($maxparams);
// We join only if the group id is larger, then specify that it must be null. This means that
// the membergroupquery group id will be the largest available.
$query->add_where(array('type' => 'AND', 'condition' => 'maxgroupquery.groupid IS NULL'));
// Make sure it's not hidden. We want to know if there are people with no group, compared to a group that
// is hidden, so the aim is to get a null group id if there are no memberships by left joining, then
// hide that null row if the settings for group id 0 say so.
$table = array('join' => 'LEFT JOIN', 'table' => $visibilitysubquery, 'on' => '(membervisibilityquery.groupid = membergroupquery.groupid OR
(membervisibilityquery.groupid = 0 AND membergroupquery.groupid IS NULL))
AND membervisibilityquery.coursemoduleid = membergroupquery.coursemoduleid', 'alias' => 'membervisibilityquery', 'subquery' => true);
$query->add_from($table);
$query->add_params($visibilityparams);
$query->add_where(array('type' => 'AND', 'condition' => 'membervisibilityquery.coursemoduleid IS NULL'));
}
/**
* Applies the filter needed for course nodes or their descendants
*
* @param block_ajax_marking_query $query
* @SuppressWarnings(PHPMD.UnusedPrivateMethod) Dynamic method names don't register
*/
protected function alter_query(block_ajax_marking_query $query)
{
// What do we need for the nodes?
$table = array('join' => 'INNER JOIN', 'table' => 'cohort', 'on' => 'countwrapperquery.id = cohort.id');
$query->add_from($table);
$conditions = array('table' => 'cohort', 'column' => 'name');
$query->add_select($conditions);
$conditions = array('table' => 'cohort', 'column' => 'description');
$query->add_select($conditions);
$query->add_orderby('cohort.name ASC');
}
/**
* Makes user nodes for the assign modules by grouping them and then adding in the right
* text to describe them.
*
* @static
* @param block_ajax_marking_query $query
*/
protected function alter_query(block_ajax_marking_query $query)
{
$conditions = array('table' => 'countwrapperquery', 'column' => 'timestamp', 'alias' => 'tooltip');
$query->add_select($conditions);
$conditions = array('table' => 'usertable', 'column' => 'firstname');
$query->add_select($conditions);
$conditions = array('table' => 'usertable', 'column' => 'lastname');
$query->add_select($conditions);
$table = array('table' => 'user', 'alias' => 'usertable', 'on' => 'usertable.id = countwrapperquery.id');
$query->add_from($table);
}
/**
* Makes a bunch of user nodes by grouping quiz submissions by the user id. The grouping is
* automatic, but the text labels for the nodes are specified here.
*
* @static
* @param block_ajax_marking_query $query
*/
protected function alter_query(block_ajax_marking_query $query)
{
$query->add_select(array('table' => 'countwrapperquery', 'column' => 'timestamp', 'alias' => 'tooltip'));
$query->add_select(array('table' => 'usertable', 'column' => 'firstname'));
$query->add_select(array('table' => 'usertable', 'column' => 'lastname'));
$query->add_from(array('join' => 'INNER JOIN', 'table' => 'user', 'alias' => 'usertable', 'on' => 'usertable.id = countwrapperquery.id'));
// This is only needed to add the right callback function.
$query->add_select(array('column' => "'quiz'", 'alias' => 'modulename'));
}
/**
* Makes a set of question nodes by grouping submissions by questionid.
*
* @static
* @param block_ajax_marking_query $query
*/
protected function alter_query(block_ajax_marking_query $query)
{
// Outer bit to get display name.
$query->add_from(array('join' => 'INNER JOIN', 'table' => 'question', 'on' => 'question.id = countwrapperquery.id'));
$query->add_select(array('table' => 'question', 'column' => 'name'));
$query->add_select(array('table' => 'question', 'column' => 'questiontext', 'alias' => 'tooltip'));
// This is only needed to add the right callback function.
$query->add_select(array('column' => "'quiz'", 'alias' => 'modulename'));
$query->add_orderby("question.name ASC");
}
/**
* Makes user nodes for the assignment modules by grouping them and then adding in the right
* text to describe them.
*
* @static
* @param block_ajax_marking_query $query
*/
protected function alter_query(block_ajax_marking_query $query)
{
$conditions = array('table' => 'countwrapperquery', 'column' => 'timestamp', 'alias' => 'tooltip');
$query->add_select($conditions);
// Need this to make the popup show properly because some assignment code shows or
// not depending on this flag to tell if it's in a pop-up e.g. the revert to draft
// button for advanced upload.
$conditions = array('column' => "'single'", 'alias' => 'mode');
$query->add_select($conditions);
$conditions = array('table' => 'usertable', 'column' => 'firstname');
$query->add_select($conditions);
$conditions = array('table' => 'usertable', 'column' => 'lastname');
$query->add_select($conditions);
$table = array('table' => 'user', 'alias' => 'usertable', 'on' => 'usertable.id = countwrapperquery.id');
$query->add_from($table);
}
/**
* Adds SQL to construct a set of discussion nodes.
*
* @static
* @param block_ajax_marking_query $query
*/
protected function alter_query(block_ajax_marking_query $query)
{
// This will be derived form the coursemodule id, but how to get it cleanly?
// The query will know, but not easy to get it out. Might have been prefixed.
// TODO pass this properly somehow.
$coursemoduleid = required_param('coursemoduleid', PARAM_INT);
// Normal forum needs discussion title as label, participant usernames as
// description eachuser needs username as title and discussion subject as
// description.
if (block_ajax_marking_forum::forum_is_eachuser($coursemoduleid)) {
$query->add_select(array('table' => 'firstpost', 'column' => 'subject', 'alias' => 'description'));
} else {
$query->add_select(array('table' => 'firstpost', 'column' => 'subject', 'alias' => 'label'));
// TODO need a SELECT bit to get all userids of people in the discussion
// instead.
$query->add_select(array('table' => 'firstpost', 'column' => 'message', 'alias' => 'tooltip'));
}
$query->add_from(array('join' => 'INNER JOIN', 'table' => 'forum_discussions', 'alias' => 'outerdiscussions', 'on' => 'countwrapperquery.id = outerdiscussions.id'));
$query->add_from(array('join' => 'INNER JOIN', 'table' => 'forum_posts', 'alias' => 'firstpost', 'on' => 'firstpost.id = outerdiscussions.firstpost'));
$query->add_orderby("timestamp ASC");
}
/**
* @param block_ajax_marking_query $query
* @return void
*/
protected function alter_query(block_ajax_marking_query $query)
{
// This will add the select, which will get a zero as the groupid if we don't have a group membership at all.
$query->add_select(array('column' => block_ajax_marking_get_countwrapper_groupid_sql(), 'alias' => 'id'));
}
/**
* This will change the query so that it does whatever this decorator is supposed to do.
*
* @param block_ajax_marking_query $query
* @return void
*/
protected function alter_query(block_ajax_marking_query $query)
{
$select = array('table' => 'moduleunion', 'column' => 'course', 'alias' => 'id');
$query->add_select($select, true);
}
/**
* Makes user nodes for the assignment modules by grouping them and then adding in the right
* text to describe them.
*
* @static
* @param block_ajax_marking_query $query
* @return void
*/
protected function alter_query(block_ajax_marking_query $query)
{
// Make the count be grouped by user id.
$conditions = array('table' => 'moduleunion', 'column' => 'userid', 'alias' => 'id');
$query->add_select($conditions, true);
}
/**
* Adds SQL to a dynamic query for when there is a question node as an ancestor of the current
* nodes.
*
* @static
* @param block_ajax_marking_query $query
* @param int $questionid
*/
protected function alter_query(block_ajax_marking_query $query, $questionid)
{
$clause = array('type' => 'AND', 'condition' => 'moduleunion.questionid = :quizfilterquestionidancestor');
$query->add_where($clause);
$query->add_param('quizfilterquestionidancestor', $questionid);
}
/**
* Used when there is an ancestor node representing a coursemodule.
*
* @static
* @param block_ajax_marking_query $query
* @param int|string $groupid
* @return void
*/
protected function alter_query(block_ajax_marking_query $query, $groupid)
{
$conditions = array('type' => 'AND', 'condition' => block_ajax_marking_get_countwrapper_groupid_sql() . ' = :groupid');
$query->add_where($conditions);
$query->add_param('groupid', $groupid);
}
/**
* Applies the filter needed for course nodes or their descendants
*
* @param block_ajax_marking_query $query
* @SuppressWarnings(PHPMD.UnusedPrivateMethod) Dynamic method names don't register
*/
protected function alter_query(block_ajax_marking_query $query)
{
global $USER;
// This is for the displayquery when we are making course nodes.
$query->add_from(array('table' => 'course', 'alias' => 'course', 'on' => 'course_modules.course = course.id'));
$query->add_select(array('table' => 'course', 'column' => 'id', 'alias' => 'courseid', 'distinct' => true));
$query->add_select(array('table' => 'course', 'column' => 'shortname', 'alias' => 'name'));
$query->add_select(array('table' => 'course', 'column' => 'fullname', 'alias' => 'tooltip'));
// We need the config settings too, if there are any.
// TODO this should be in the config filter.
$query->add_from(array('join' => 'LEFT JOIN', 'table' => 'block_ajax_marking', 'alias' => 'settings', 'on' => "settings.instanceid = course.id\n AND settings.tablename = 'course'\n AND settings.userid = :settingsuserid"));
$query->add_param('settingsuserid', $USER->id);
$query->add_select(array('table' => 'settings', 'column' => 'display'));
$query->add_select(array('table' => 'settings', 'column' => 'groupsdisplay'));
$query->add_select(array('table' => 'settings', 'column' => 'id', 'alias' => 'settingsid'));
$query->add_orderby('course.shortname ASC');
}
/**
* @param block_ajax_marking_query $query
* @return void
*/
protected function alter_query(block_ajax_marking_query $query)
{
$conditions = array('table' => 'cohort', 'column' => 'id');
$query->add_select($conditions);
}
/**
* 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 $query
* @param int $courseid
*/
protected function alter_query(block_ajax_marking_query $query, $courseid)
{
$conditions = array('type' => 'AND', 'condition' => 'moduleunion.course = :courseidancestorcourseid');
$query->add_where($conditions);
$query->add_param('courseidancestorcourseid', $courseid);
}
/**
* Not sure we'll ever need this, but just in case...
*
* @static
* @param block_ajax_marking_query $query
* @param int $userid
*/
public function alter_query(block_ajax_marking_query $query, $userid)
{
$clause = array('type' => 'AND', 'condition' => 'sub.userid = :assignmentuseridfilteruserid');
$query->add_where($clause);
$query->add_param('assignmentuseridfilteruserid', $userid);
}
/**
* Adds SQL for when there is a discussion node as an ancestor of the current nodes.
*
* @static
* @param block_ajax_marking_query $query
* @param int $discussionid
*/
protected function alter_query(block_ajax_marking_query $query, $discussionid)
{
$clause = array('type' => 'AND', 'condition' => 'discussion.id = :discussionidfilterdiscussionid');
$query->add_where($clause);
$query->add_param('discussionidfilterdiscussionid', $discussionid);
}
/**
* 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 $query
* @param int|string $cohortid
* @return void
*/
protected function alter_query(block_ajax_marking_query $query, $cohortid)
{
$clause = array('type' => 'AND', 'condition' => 'cohort.id = :cohortidfiltercohortid');
$query->add_where($clause);
$query->add_param('cohortidfiltercohortid', $cohortid);
}
/**
* Config nodes need some stuff to be returned from the config tables so we can have settings
* adjusted based on existing values.
*
* @param block_ajax_marking_query $query
* @param string $nextnodefilter
* @return void
*/
private static function attach_config_settings(block_ajax_marking_query $query, $nextnodefilter)
{
$nodesthatneedconfigsettings = array('courseid', 'coursemoduleid');
if (!in_array($nextnodefilter, $nodesthatneedconfigsettings)) {
return;
}
// The inner query joins to the config tables already for the WHERE clauses, so we
// make use of them to get the settings for those nodes that are not filtered out.
$countwrapper = $query->get_subquery('countwrapperquery');
switch ($nextnodefilter) {
// This is for the ordinary nodes. We need to work out what to request for the next node
// so groupsdisplay has to be sent through. Also for the config context menu to work.
// COALESCE is no good here as we need the actual settings, so we work out that stuff
// in JavaScript.
case 'courseid':
$countwrapper->add_select(array('table' => 'courseconfig', 'column' => 'display'));
$countwrapper->add_select(array('table' => 'courseconfig', 'column' => 'groupsdisplay'));
break;
case 'coursemoduleid':
// The inner query joins to the config tables already for the WHERE clauses, so we
// make use of them to get the settings for those nodes that are not filtered out.
$countwrapper = $query->get_subquery('countwrapperquery');
$countwrapper->add_select(array('table' => 'cmconfig', 'column' => 'display'));
$countwrapper->add_select(array('table' => 'cmconfig', 'column' => 'groupsdisplay'));
break;
}
// The outer query (we need one because we have to do a join between the numeric
// fields that can be fed into a GROUP BY and the text fields that we display) pulls
// through the display fields, which were sent through from the middle query using the
// stuff above.
$query->add_select(array('table' => 'countwrapperquery', 'column' => 'display'));
$query->add_select(array('table' => 'countwrapperquery', 'column' => 'groupsdisplay'));
}
/**
* Adds SQL to a dynamic query for when there is a question node as an ancestor of the current
* nodes.
*
* @static
* @param block_ajax_marking_query $query
* @return mixed|void
*/
protected function alter_query(block_ajax_marking_query $query)
{
$query->add_select(array('table' => 'moduleunion', 'column' => 'questionid', 'alias' => 'id'));
}
