/**
* 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'));
}
/**
* 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.
}
/**
* 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);
}
/**
* 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);
}
/**
* 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
* @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');
}
/**
* 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'));
}
/**
* 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");
}
/**
* 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");
}
/**
* 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);
}
/**
* 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");
}
/**
* 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);
}
/**
* All modules have a common need to hide work which has been submitted to items that are now
* hidden. Not sure if this is relevant so much, but it's worth doing so that test data and test
* courses don't appear. General approach is to use cached context info from user session to
* find a small list of contexts that a teacher cannot grade in within the courses where they
* normally can, then do a NOT IN thing with it. Also the obvious visible = 1 stuff.
*
* @param block_ajax_marking_query $query
* @param string $coursemodulejoin What table.column to join to course_modules.id
* @param bool $includehidden Do we want to have hidden coursemodules included? Config = yes
* @return array The join string, where string and params array. Note, where starts with 'AND'
*/
private static function apply_sql_visible(block_ajax_marking_query $query, $coursemodulejoin = '', $includehidden = false)
{
global $DB;
if ($coursemodulejoin) {
// Only needed if the table is not already there.
$query->add_from(array('join' => 'INNER JOIN', 'table' => 'course_modules', 'on' => 'course_modules.id = ' . $coursemodulejoin));
}
$query->add_from(array('join' => 'INNER JOIN', 'table' => 'course', 'on' => 'course.id = course_modules.course'));
$mods = block_ajax_marking_get_module_classes();
$modids = array();
foreach ($mods as $mod) {
$modids[] = $mod->get_module_id();
// Save these for later.
}
// Get coursemoduleids for all items of this type in all courses as one query. Won't come
// back empty or else we would not have gotten this far.
$courses = block_ajax_marking_get_my_teacher_courses();
// TODO Note that change to login as... in another tab may break this. Needs testing.
list($coursesql, $courseparams) = $DB->get_in_or_equal(array_keys($courses), SQL_PARAMS_NAMED);
list($modsql, $modparams) = $DB->get_in_or_equal(array_keys($modids), SQL_PARAMS_NAMED);
$params = array_merge($courseparams, $modparams);
// Get all course modules the current user could potentially access. Limit to the enabled
// mods.
$sql = "SELECT context.*\n FROM {context} context\n INNER JOIN {course_modules} course_modules\n ON context.instanceid = course_modules.id\n AND context.contextlevel = " . CONTEXT_MODULE . "\n WHERE course_modules.course {$coursesql}\n AND course_modules.module {$modsql}";
// No point caching - only one request per module per page request...
$contexts = $DB->get_records_sql($sql, $params);
// Use has_capability to loop through them finding out which are blocked. Unset all that we
// have permission to grade, leaving just those we are not allowed (smaller list). Hopefully
// this will never exceed 1000 (oracle hard limit on number of IN values).
foreach ($mods as $mod) {
foreach ($contexts as $key => $context) {
// If we don't find any capabilities for a context, it will remain and be excluded
// from the SQL. Hopefully this will be a small list. n.b. the list is of all
// course modules.
if (has_capability($mod->get_capability(), new bulk_context_module($context))) {
unset($contexts[$key]);
}
}
}
// Return a get_in_or_equals with NOT IN if there are any, or empty strings if there aren't.
if (!empty($contexts)) {
list($contextssql, $contextsparams) = $DB->get_in_or_equal(array_keys($contexts), SQL_PARAMS_NAMED, 'context0000', false);
$query->add_where(array('type' => 'AND', 'condition' => "course_modules.id {$contextssql}"));
$query->add_params($contextsparams);
}
// Only show enabled mods.
list($visiblesql, $visibleparams) = $DB->get_in_or_equal($modids, SQL_PARAMS_NAMED, 'visible000');
$query->add_where(array('type' => 'AND', 'condition' => "course_modules.module {$visiblesql}"));
// We want the coursmeodules that are hidden to be gone form the main trees. For config,
// We may want to show them greyed out so that settings can be sorted before they are shown
// to students.
if (!$includehidden) {
$query->add_where(array('type' => 'AND', 'condition' => 'course_modules.visible = 1'));
}
$query->add_where(array('type' => 'AND', 'condition' => 'course.visible = 1'));
$query->add_params($visibleparams);
}