/**
  * resolves queues by filling empty group places in defined order with students from the queue
  *
  * @todo there's a bug which prevents deletion of some queue entries, only happened on
  *       development system with admin-users account several times, whatch out for the future
  * @todo sometimes not every queue entry is resolved, happened unregularly on development system
  *       watch in production system
  *
  * @param string $mode optional, without function, reserved for optional later implementation
  *                     (random/alphabetical/... distribution, etc)
  * @param bool $previewonly show only preview of actions
  * @return array ($error, $message)
  */
 public function resolve_queues($mode = 'sortorder', $previewonly = false)
 {
     global $OUTPUT, $DB, $USER;
     $error = false;
     $returntext = "";
     // Trigger event!
     \mod_grouptool\event\dequeuing_started::create_from_object($this->cm)->trigger();
     if (empty($grouptoolid)) {
         $grouptoolid = $this->grouptool->id;
         $grouptool = $this->grouptool;
         $context = $this->context;
     } else {
         $cmid = get_coursemodule_from_instance('grouptool', $grouptoolid);
         $grouptool = $DB->get_record('grouptool', array('id' => $grouptoolid), '*', MUST_EXIST);
         $context = context_module::instance($cmid->id);
     }
     require_capability('mod/grouptool:register_students', $context);
     $agrps = $this->get_active_groups(false, false, 0, 0, 0, false);
     if (!empty($agrps)) {
         $agrpids = array_keys($agrps);
         list($agrpssql, $agrpsparam) = $DB->get_in_or_equal($agrpids);
         $agrpsfiltersql = " AND agrp.id " . $agrpssql;
         $agrpsfilterparams = array_merge(array($grouptool->id), $agrpsparam);
         // Get queue-entries (sorted by timestamp)!
         if (!empty($grouptool->allow_multiple)) {
             $queuedsql = " WHERE queued.agrpid " . $agrpssql . " ";
             $queuedparams = array_merge($agrpsparam, $agrpsparam);
             $queueentries = $DB->get_records_sql("\n                      SELECT queued.id, MAX(queued.agrpid) AS agrpid, MAX(queued.userid) AS userid,\n                             MAX(queued.timestamp), (COUNT(DISTINCT reg.id) < ?) AS priority\n                        FROM {grouptool_queued} queued\n                   LEFT JOIN {grouptool_registered} reg ON queued.userid = reg.userid AND reg.agrpid " . $agrpssql . " AND reg.modified_by >= 0\n                    " . $queuedsql . "\n                    GROUP BY queued.id\n                    ORDER BY priority DESC, queued.timestamp ASC", array_merge(array($grouptool->choose_min), $queuedparams));
         } else {
             $queuedsql = " WHERE queued.agrpid " . $agrpssql . " ";
             $queuedparams = $agrpsparam;
             $queueentries = $DB->get_records_sql("SELECT *, '1' AS priority\n                                                        FROM {grouptool_queued} queued" . $queuedsql . "ORDER BY timestamp ASC", $queuedparams);
         }
         $userregs = $DB->get_records_sql_menu('SELECT reg.userid, COUNT(DISTINCT reg.id)
                                                  FROM {grouptool_registered} reg
                                                 WHERE reg.agrpid ' . $agrpssql . ' AND modified_by >= 0
                                              GROUP BY reg.userid', $agrpsparam);
     } else {
         return array(true, get_string('no_active_groups', 'grouptool'));
     }
     // Get group entries (sorted by sort-order)!
     $groupsdata = $DB->get_records_sql("\n                SELECT agrp.id AS id, MAX(agrp.groupid) AS groupid, MAX(agrp.grpsize) AS grpsize,\n                       COUNT(DISTINCT reg.id) AS registered\n                  FROM {grouptool_agrps} agrp\n             LEFT JOIN {grouptool_registered} reg ON reg.agrpid = agrp.id AND modified_by >= 0\n                 WHERE agrp.grouptoolid = ?" . $agrpsfiltersql . "\n              GROUP BY agrp.id\n              ORDER BY agrp.sort_order ASC", $agrpsfilterparams);
     $i = 0;
     if (!empty($groupsdata) && !empty($queueentries)) {
         $groupsnav = array_keys($groupsdata);
         $queuenav = array_keys($queueentries);
         $planned = new stdClass();
         $curgroup = null;
         $maxregs = !empty($this->grouptool->allow_multiple) ? $this->grouptool->choose_max : 1;
         reset($groupsdata);
         foreach ($queueentries as $queue) {
             // Get first non-full group!
             while ($curgroup == null || $curgroup->grpsize <= $curgroup->registered) {
                 if ($curgroup === null) {
                     $curgroup = current($groupsdata);
                 } else {
                     $curgroup = next($groupsdata);
                 }
                 if ($curgroup === false) {
                     $error = true;
                     $returntext .= html_writer::tag('div', get_string('all_groups_full', 'grouptool', $queue->userid), array('class' => 'error'));
                     return array($error, $returntext);
                 } else {
                     $curgroup->grpsize = $grouptool->use_individual && !empty($curgroup->grpsize) ? $curgroup->grpsize : $grouptool->grpsize;
                 }
             }
             if (!isset($planned->{$queue->userid})) {
                 $planned->{$queue->userid} = array();
             }
             // If user has got too many regs allready!
             if (!empty($userregs[$queue->userid]) && $userregs[$queue->userid] >= $maxregs) {
                 $returntext .= html_writer::tag('div', get_string('too_many_regs', 'grouptool'), array('class' => 'error'));
                 $error = true;
                 // Continue with next user/queue-entry!
                 continue;
             }
             while ($DB->record_exists('grouptool_registered', array('agrpid' => $curgroup->id, 'userid' => $queue->userid)) || in_array($curgroup->id, $planned->{$queue->userid}) || $curgroup->registered >= $curgroup->grpsize) {
                 $curgroup = next($groupsdata);
                 $i++;
                 if ($curgroup === false) {
                     break;
                     // No group left for this user!
                 }
             }
             if ($curgroup !== false) {
                 // Register him or mark as planed!
                 if ($previewonly) {
                     list($curerror, $curtext) = $this->register_in_agrp($curgroup->id, $queue->userid, true, true);
                     if (!$curerror) {
                         $planned->{$queue->userid}[] = $curgroup->id;
                     }
                     $class = $curerror ? 'error' : 'success';
                     $data = new stdClass();
                     $data->userid = $queue->userid;
                     $data->agrpid = $queue->agrpid;
                     $data->current_grp = $curgroup->id;
                     $data->current_text = $curtext;
                     $movetext = get_string('user_move_prev', 'grouptool', $data);
                     $returntext .= html_writer::tag('div', $movetext . ' (' . $curtext . ')', array('class' => $class));
                     if (!isset($status[$queue->userid])) {
                         $status[$queue->userid] = new stdClass();
                     }
                     $status[$queue->userid]->error = $curerror;
                     $error = $error || $curerror;
                     $curgroup->registered++;
                 } else {
                     list($curerror, $curtext) = $this->register_in_agrp($curgroup->id, $queue->userid, false, true);
                     $class = $curerror ? 'error' : 'success';
                     $data = new stdClass();
                     $data->userid = $queue->userid;
                     $data->agrpid = $queue->agrpid;
                     $data->current_grp = $curgroup->id;
                     $data->current_text = $curtext;
                     $movedtext = get_string('user_moved', 'grouptool', $data);
                     $returntext .= html_writer::tag('div', $movedtext . ' (' . $curtext . ')', array('class' => $class));
                     $curgroup->registered++;
                     $error = $error || $curerror;
                     $attr = array('id' => $queue->id, 'userid' => $queue->userid, 'agrpid' => $queue->agrpid);
                     // Delete queue entry if successfull or print message!
                     $queues = $DB->get_records('grouptool_queued', $attr);
                     $DB->delete_records('grouptool_queued', $attr);
                     // Log user moved!
                     $queue->groupid = $DB->get_field('grouptool_agrps', 'groupid', array('id' => $queue->agrpid), MUST_EXIST);
                     $to = new stdClass();
                     $to->agrpid = $curgroup->id;
                     $to->userid = $queue->userid;
                     $to->groupid = $DB->get_field('grouptool_agrps', 'groupid', array('id' => $curgroup->id), MUST_EXIST);
                     $to->id = $DB->get_field('grouptool_registered', 'id', array('agrpid' => $to->agrpid, 'userid' => $to->userid), MUST_EXIST);
                     \mod_grouptool\event\user_moved::move($this->cm, $queue, $to)->trigger();
                     if ($DB->record_exists('grouptool_queued', $attr)) {
                         $returntext .= "Could not delete!";
                     }
                 }
             }
             while ($i !== 0) {
                 $curgroup = prev($groupsdata);
                 $i--;
             }
         }
     }
     if (empty($returntext)) {
         $returntext = get_string('no_queues_to_resolve', 'grouptool');
         $error = false;
     }
     return array($error, $returntext);
 }
 /**
  * group_remove_member_handler
  * event:       groups_member_removed
  * schedule:    instant
  *
  * @param \core\event\group_member_removed $event Event object containing useful data
  * @return bool true if success
  */
 public static function group_member_removed(\core\event\group_member_removed $event)
 {
     global $DB, $CFG;
     $sql = "SELECT DISTINCT {grouptool}.id, {grouptool}.ifmemberremoved, {grouptool}.course,\n                                {grouptool}.use_queue, {grouptool}.immediate_reg, {grouptool}.allow_multiple,\n                                {grouptool}.choose_max, {grouptool}.name\n                           FROM {grouptool}\n                     RIGHT JOIN {grouptool_agrps} agrp ON agrp.grouptoolid = {grouptool}.id\n                          WHERE agrp.groupid = ?";
     $params = array($event->objectid);
     if (!($grouptools = $DB->get_records_sql($sql, $params))) {
         return true;
     }
     $sql = "SELECT agrps.grouptoolid grouptoolid, agrps.id id\n                  FROM {grouptool_agrps} agrps\n                 WHERE agrps.groupid = :groupid";
     $agrp = $DB->get_records_sql($sql, array('groupid' => $event->objectid));
     foreach ($grouptools as $grouptool) {
         switch ($grouptool->ifmemberremoved) {
             case GROUPTOOL_FOLLOW:
                 $sql = "SELECT reg.id AS id, reg.agrpid AS agrpid, reg.userid AS userid, agrps.groupid\n                              FROM {grouptool_agrps} agrps\n                        INNER JOIN {grouptool_registered} reg ON agrps.id = reg.agrpid\n                             WHERE reg.userid = :userid\n                                   AND agrps.grouptoolid = :grouptoolid\n                                   AND agrps.groupid = :groupid";
                 if ($regs = $DB->get_records_sql($sql, array('grouptoolid' => $grouptool->id, 'userid' => $event->relateduserid, 'groupid' => $event->objectid))) {
                     $DB->delete_records_list('grouptool_registered', 'id', array_keys($regs));
                     foreach ($regs as $reg) {
                         // Trigger event!
                         $cm = get_coursemodule_from_instance('grouptool', $grouptool->id, $grouptool->course, false, MUST_EXIST);
                         \mod_grouptool\event\registration_deleted::create_via_eventhandler($cm, $reg)->trigger();
                     }
                     // Get next queued user and put him in the group (and delete queue entry)!
                     if (!empty($grouptool->use_queue)) {
                         $agrpids = $DB->get_fieldset_sql('SELECT id
                                                             FROM {grouptool_agrps}
                                                            WHERE grouptoolid = ?', array($grouptool->id));
                         list($agrpssql, $agrpsparam) = $DB->get_in_or_equal($agrpids);
                         $sql = "SELECT queued.id, MAX(queued.agrpid) AS agrpid, MAX(queued.userid) AS userid,\n                                                      MAX(queued.timestamp), (COUNT(DISTINCT reg.id) < ?) AS priority\n                                      FROM {grouptool_queued} queued\n                                 LEFT JOIN {grouptool_registered} reg ON queued.userid = reg.userid\n                                                                         AND reg.agrpid " . $agrpssql . "\n                                     WHERE queued.agrpid = ?\n                                  GROUP BY queued.id\n                                  ORDER BY priority DESC, queued.timestamp ASC\n                                     LIMIT 1";
                         $params = array_merge(array($grouptool->choose_max), $agrpsparam, array($agrp[$grouptool->id]->id));
                         $record = $DB->get_record_sql($sql, $params);
                         if (is_object($record)) {
                             $newrecord = clone $record;
                             unset($newrecord->id);
                             $newrecord->modified_by = $newrecord->userid;
                             $newrecord->id = $DB->insert_record('grouptool_registered', $newrecord);
                             if (!empty($grouptool->immediate_reg)) {
                                 groups_add_member($event->objectid, $newrecord->userid);
                             }
                             // Trigger event!
                             // We got the cm above already!
                             $newrecord->groupid = $event->objectid;
                             $record->groupid = $event->objectid;
                             \mod_grouptool\event\user_moved::promotion_from_queue($cm, $record, $newrecord)->trigger();
                             $allowm = $grouptool->allow_multiple;
                             $agrps = $DB->get_fieldset_sql("SELECT id\n                                                                FROM {grouptool_agrps} agrps\n                                                                WHERE agrps.grouptoolid = :grptlid", array('grptlid' => $grouptool->id));
                             list($sql, $params) = $DB->get_in_or_equal($agrps);
                             $usrregcnt = $DB->count_records_select('grouptool_registered', ' userid = ?
                                                                     AND agrpid ' . $sql, array_merge(array($newrecord->userid), $params));
                             $max = $grouptool->choose_max;
                             // Get belonging course!
                             $course = $DB->get_record('course', array('id' => $grouptool->course));
                             // Get CM!
                             $cm = get_coursemodule_from_instance('grouptool', $grouptool->id, $course->id);
                             $message = new stdClass();
                             $userdata = $DB->get_record('user', array('id' => $newrecord->userid));
                             $message->username = fullname($userdata);
                             $groupdata = $DB->get_record('grouptool_agrps', array('id' => $agrp[$grouptool->id]->id));
                             $groupdata->name = $DB->get_field('groups', 'name', array('id' => $groupdata->groupid));
                             $message->groupname = $groupdata->name;
                             $strgrouptools = get_string("modulenameplural", "grouptool");
                             $strgrouptool = get_string("modulename", "grouptool");
                             $postsubject = $course->shortname . ': ' . $strgrouptools . ': ' . format_string($grouptool->name, true);
                             $posttext = $course->shortname . ' -> ' . $strgrouptools . ' -> ' . format_string($grouptool->name, true) . "\n";
                             $posttext .= "----------------------------------------------------------\n";
                             $posttext .= get_string("register_you_in_group_successmail", "grouptool", $message) . "\n";
                             $posttext .= "----------------------------------------------------------\n";
                             $usermailformat = $DB->get_field('user', 'mailformat', array('id' => $newrecord->userid));
                             if ($usermailformat == 1) {
                                 // HTML!
                                 $posthtml = "<p><font face=\"sans-serif\">";
                                 $posthtml = "<a href=\"" . $CFG->wwwroot . "/course/view.php?id=" . $course->id . "\">" . $course->shortname . "</a> ->";
                                 $posthtml = "<a href=\"" . $CFG->wwwroot . "/mod/grouptool/index.php?id=" . $course->id . "\">" . $strgrouptools . "</a> ->";
                                 $posthtml = "<a href=\"" . $CFG->wwwroot . "/mod/grouptool/view.php?id=" . $cm->id . "\">" . format_string($grouptool->name, true) . "</a></font></p>";
                                 $posthtml .= "<hr /><font face=\"sans-serif\">";
                                 $posthtml .= "<p>" . get_string("register_you_in_group_successmailhtml", "grouptool", $message) . "</p>";
                                 $posthtml .= "</font><hr />";
                             } else {
                                 $posthtml = "";
                             }
                             $messageuser = $DB->get_record('user', array('id' => $newrecord->userid));
                             $eventdata = new stdClass();
                             $eventdata->modulename = 'grouptool';
                             $userfrom = core_user::get_noreply_user();
                             $eventdata->userfrom = $userfrom;
                             $eventdata->userto = $messageuser;
                             $eventdata->subject = $postsubject;
                             $eventdata->fullmessage = $posttext;
                             $eventdata->fullmessageformat = FORMAT_PLAIN;
                             $eventdata->fullmessagehtml = $posthtml;
                             $eventdata->smallmessage = get_string('register_you_in_group_success', 'grouptool', $message);
                             $eventdata->name = 'grouptool_moveupreg';
                             $eventdata->component = 'mod_grouptool';
                             $eventdata->notification = 1;
                             $eventdata->contexturl = $CFG->wwwroot . '/mod/grouptool/view.php?id=' . $cm->id;
                             $eventdata->contexturlname = $grouptool->name;
                             message_send($eventdata);
                             if ($allowm && $usrregcnt >= $max || !$allowm) {
                                 // Get all queue entries and trigger queue_entry_deleted events for each!
                                 $queueentries = $DB->get_records_sql("SELECT queued.*, agrp.groupid\n                                                                             FROM {grouptool_queued} queued\n                                                                             JOIN {grouptool_agrps} agrp ON queued.agrpid = agrp.id\n                                                                            WHERE userid = ? AND agrpid " . $sql, array_merge(array($newrecord->userid), $params));
                                 $DB->delete_records_select('grouptool_queued', ' userid = ? AND agrpid ' . $sql, array_merge(array($newrecord->userid), $params));
                                 foreach ($queueentries as $cur) {
                                     // Trigger event!
                                     // We got the cm above already!
                                     \mod_grouptool\event\queue_entry_deleted::create_via_eventhandler($cm, $cur)->trigger();
                                 }
                             } else {
                                 $queueentries = $DB->get_records_sql("SELECT queued.*, agrp.groupid\n                                                                             FROM {grouptool_queued} queued\n                                                                             JOIN {grouptool_agrps} agrp ON queued.agrpid = agrp.id\n                                                                            WHERE userid = :userid AND agrpid = :agrpid", array('userid' => $newrecord->userid, 'agrpid' => $agrp[$grouptool->id]->id));
                                 $DB->delete_records('grouptool_queued', array('userid' => $newrecord->userid, 'agrpid' => $agrp[$grouptool->id]->id));
                                 foreach ($queueentries as $cur) {
                                     // Trigger event!
                                     // We got the cm above already!
                                     \mod_grouptool\event\queue_entry_deleted::create_via_eventhandler($cm, $cur)->trigger();
                                 }
                             }
                         }
                     }
                 }
                 break;
             default:
             case GROUPTOOL_IGNORE:
                 break;
         }
     }
     return true;
 }