/**
  * 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);
 }