function tao_print_related_learning_paths() { global $USER, $CFG; if (isguest()) { //don't print anything if this is a guest user. return; } echo '<h2>' . get_string('recommendedlearningpaths', 'local') . '</h2>'; $submitted = get_field('course_approval_status', 'id', 'shortname', 'published'); $sql = "SELECT c.id, c.fullname, c.summary, count(c.id) " . sql_as() . " matches, \n sum((select max(ordering) from mdl_tag_instance where itemid = {$USER->id})-t.ordering) as weight\n FROM mdl_course c, mdl_tag_instance t\n WHERE t.tagid in ( select tagid from mdl_tag_instance where itemid = {$USER->id} and (itemtype = 'user' or itemtype = 'userclassify') )\n AND t.itemid = c.id\n AND ( t.itemtype = 'course' or t.itemtype = 'courseclassification' )\n AND c.format = 'learning'\n AND c.approval_status_id = {$submitted}\n GROUP BY c.id, c.fullname, c.summary\n ORDER BY weight desc"; if ($courses = get_records_sql($sql)) { $count = 0; //print_simple_box_start('center', '100%', '', 5, "coursebox"); echo '<table id="my-learning-paths-list" width="100%" border=0>'; foreach ($courses as $course) { // only display those that you don't have a relationship with already if (has_capability('moodle/local:hasdirectlprelationship', get_context_instance(CONTEXT_COURSE, $course->id))) { continue; } // only display up to 3 learning paths $count++; if ($count > 3) { break; } echo '<tr>'; echo '<td>'; echo '<a title="' . format_string($course->fullname) . '" href="' . $CFG->wwwroot . '/course/view.php?id=' . $course->id . '"> ' . format_string($course->fullname) . '</a>'; $summary = $course->summary; if (strlen($summary) > 150) { $summary = substr($summary, 0, 150) . '...'; } echo '<br/>' . $summary; echo '</td>'; echo '</tr>'; echo '<tr><td> </td></tr>'; } echo '</table>'; //print_simple_box_end(); echo '<p>' . get_string('recommendedlearningpathsdescription', 'local') . '</p>'; } else { echo '<p>' . get_string('nomatchinglearningpaths', 'local') . '</p>'; } echo '<p><a href="' . $CFG->wwwroot . '/local/user/classify.php?id=' . $USER->id . '">>> ' . get_string('updateyourinterests', 'local') . '</a></p>'; }
function tao_print_need_publishing() { global $CFG; echo '<h2>' . get_string('learningpathsneedpublish', 'local') . '</h2>'; $pf = $CFG->prefix; $as = sql_as(); $sql = "SELECT c.id, c.fullname, c.shortname, s.displayname {$as} status\n FROM {$pf}course c, {$pf}course_approval_status s\n WHERE c.approval_status_id = " . COURSE_STATUS_APPROVED . "\n AND c.approval_status_id = s.id"; $courses = get_records_sql($sql); if (!empty($courses)) { $html = '<table id="unpublished_learning_paths" width="100%" border="0" cellspacing="0" cellpadding="0">'; $html .= '<tr><th align="left">' . get_string('course') . '</th><th align="left">' . get_string('status') . '</th><th></th>'; foreach ($courses as $course) { $html .= '<tr>'; $html .= '<td><a href="' . $CFG->wwwroot . '/course/view.php?id=' . $course->id . '">' . $course->fullname . '</a></td>'; $html .= '<td>' . $course->status . '</td>'; $html .= '<td align="right"> </td>'; $html .= '</tr>'; } $html .= '</table>'; } else { $html = '<p>' . get_string('nolearningpaths', 'local') . '</p>'; } echo $html; }
/** * Same functionality as blocks_execute_url_action() * Handle all block actions ourselves. * * @param boolean $redirect (Optional) Redirect after action * @return void **/ function page_blocks_execute_url_action($redirect = true) { global $CFG, $COURSE, $PAGE; $pageitemid = optional_param('pageitemid', 0, PARAM_INT); $blockaction = optional_param('blockaction', '', PARAM_ALPHA); // Reasons to stop right meow if (empty($blockaction) || !$PAGE->user_allowed_editing() || !confirm_sesskey()) { return; } // Make sure if we have a valid pageitem. if ($pageitemid and !($pageitem = get_record('format_page_items', 'id', $pageitemid))) { return; } switch ($blockaction) { case 'config': if (empty($pageitem->blockinstance) and !empty($pageitem->cmid)) { // Its a module - go to module update redirect("{$CFG->wwwroot}/course/mod.php?update={$pageitem->cmid}&sesskey=" . sesskey()); } else { if (!empty($pageitem->blockinstance)) { // Its a block instance - allow core routine to handle redirect($PAGE->url_build('instanceid', $pageitem->blockinstance, 'blockaction', 'config', 'sesskey', sesskey())); } else { error('Invalid page item to configure'); } } break; case 'toggle': $update = new stdClass(); $update->id = $pageitem->id; if (empty($pageitem->visible)) { $update->visible = 1; } else { $update->visible = 0; } update_record('format_page_items', $update); break; case 'delete': page_block_delete($pageitem); break; case 'moveup': page_block_move($pageitem, $pageitem->position, $pageitem->sortorder - 1); break; case 'movedown': page_block_move($pageitem, $pageitem->position, $pageitem->sortorder + 1); break; case 'moveright': $destposition = $PAGE->blocks_move_position($pageitem, BLOCK_MOVE_RIGHT); $destweight = page_get_next_weight($pageitem->pageid, $destposition); page_block_move($pageitem, $destposition, $destweight); break; case 'moveleft': $destposition = $PAGE->blocks_move_position($pageitem, BLOCK_MOVE_LEFT); $destweight = page_get_next_weight($pageitem->pageid, $destposition); page_block_move($pageitem, $destposition, $destweight); break; case 'addmod': // Right now, modules are added differently $instance = required_param('instance', PARAM_INT); $record = new stdClass(); $record->pageid = $PAGE->formatpage->id; $record->cmid = $instance; $record->blockinstance = 0; $record->position = $PAGE->blocks_default_position(); $record->sortorder = page_get_next_weight($record->pageid, $record->position); insert_record('format_page_items', $record); break; case 'add': // Add a block instance and a pageitem $blockid = required_param('blockid', PARAM_INT); $block = blocks_get_record($blockid); if (empty($block) or !$block->visible) { break; } if (!block_method_result($block->name, 'user_can_addto', $PAGE)) { break; } // Add a block instance if one does not already exist or if the block allows multiple block instances $exists = record_exists('block_instance', 'pageid', $PAGE->get_id(), 'pagetype', $PAGE->get_type(), 'blockid', $blockid); if ($block->multiple || !$exists) { // Get the next weight value NOTE: hard code left position $weight = get_record_sql('SELECT 1, MAX(weight) + 1 ' . sql_as() . ' nextfree FROM ' . $CFG->prefix . 'block_instance WHERE pageid = ' . $PAGE->get_id() . ' AND pagetype = \'' . $PAGE->get_type() . '\' AND position = \'' . BLOCK_POS_LEFT . '\''); if (empty($weight->nextfree)) { $weight->nextfree = 0; } $newinstance = new stdClass(); $newinstance->blockid = $blockid; $newinstance->pageid = $PAGE->get_id(); $newinstance->pagetype = $PAGE->get_type(); $newinstance->position = BLOCK_POS_LEFT; // Make sure we keep them all in same column $newinstance->weight = $weight->nextfree; $newinstance->visible = 1; $newinstance->configdata = ''; $instanceid = $newinstance->id = insert_record('block_instance', $newinstance); if ($newinstance and $obj = block_instance($block->name, $newinstance)) { // Return value ignored $obj->instance_create(); } } else { if ($exists) { // Get the existing blockinstance as the block only allows one instance. $instanceid = get_field('block_instance', 'id', 'pageid', $PAGE->get_id(), 'pagetype', $PAGE->get_type(), 'blockid', $blockid); } } if (!empty($instanceid)) { // Create a new page item that links to the instance $record = new stdClass(); $record->pageid = $PAGE->formatpage->id; $record->cmid = 0; $record->blockinstance = $instanceid; $record->position = $PAGE->blocks_default_position(); $record->sortorder = page_get_next_weight($record->pageid, $record->position); insert_record('format_page_items', $record); } break; } if ($redirect) { // In order to prevent accidental duplicate actions, redirect to a page with a clean url redirect($PAGE->url_get_full()); } }
} else { $section = 0; } } $PAGE->print_tabs('activities'); print_box_start('boxwidthwide boxaligncenter pageeditingtable', 'editing-table'); print_section_add_menus($course, $section, $modnames); if (!empty($modnamesused)) { $modinfo = get_fast_modinfo($course); $vars = new stdClass(); $vars->delete = get_string("delete"); $vars->update = get_string("update"); $vars->sesskey = sesskey(); foreach ($modnamesused as $module => $modnamestr) { $orderby = 'i.name'; $fields = 'i.id, i.name, m.name ' . sql_as() . ' module, c.id ' . sql_as() . ' cmid, c.visible'; // Going to be executing SQL that could fail on purpose - avoid debug messages $debug = $CFG->debug; // Store current value $CFG->debug = false; // disable_debugging() doesn't seem to work // Check for field named type if (execute_sql("SELECT type FROM {$CFG->prefix}{$module} WHERE 1 = 2", false)) { // Has type - incorperate it into the sql $orderby = "i.type, {$orderby}"; $fields = "{$fields}, i.type"; } // Restore debug value $CFG->debug = $debug; $instances = get_records_sql("SELECT {$fields}\n FROM {$CFG->prefix}course_modules c,\n {$CFG->prefix}modules m,\n {$CFG->prefix}{$module} i\n WHERE i.course = {$course->id}\n AND m.name = '{$module}'\n AND c.instance = i.id\n AND c.module = m.id\n ORDER BY {$orderby}"); if (!empty($instances)) {
function tao_add_learningpath_block($type = 'html', $pageid, $pagetype, $title, $text = '') { global $CFG; $block = get_record('block', 'name', $type); // get the next weight value $weight = get_record_sql('SELECT 1, MAX(weight) + 1 ' . sql_as() . ' nextfree FROM ' . $CFG->prefix . 'block_instance WHERE pageid = ' . $pageid . ' AND pagetype = \'' . $pagetype . '\' AND position = \'' . BLOCK_POS_LEFT . '\''); if (empty($weight->nextfree)) { $weight->nextfree = 0; } // insert the block instance record $newinstance = new stdClass(); $newinstance->blockid = $block->id; $newinstance->pageid = $pageid; $newinstance->pagetype = $pagetype; $newinstance->position = BLOCK_POS_LEFT; // Make sure we keep them all in same column $newinstance->weight = $weight->nextfree; $newinstance->visible = 1; $newinstance->configdata = ''; $instanceid = $newinstance->id = insert_record('block_instance', $newinstance); if ($newinstance and $obj = block_instance($block->name, $newinstance)) { // Return value ignored $obj->instance_create(); // update the configdata for the block (i.e. content) $blockdata->title = $title; $blockdata->text = $text; if (!$obj->instance_config_save($blockdata)) { error('Error saving block configuration'); } } return $instanceid; }
function display() { global $CFG, $PAGE, $COURSE; require_capability('moodle/course:manageactivities', $this->context); get_all_mods($COURSE->id, $mods, $modnames, $modnamesplural, $modnamesused); // Right now storing modules in a section corresponding to the current // page - probably should all be section 0 though if ($COURSE->id == SITEID) { $section = 1; // Front page only has section 1 - so use 1 as default } else { if (isset($page->id)) { $section = $page->id; } else { $section = 0; } } $PAGE->print_tabs('activities'); print_box_start('boxwidthwide boxaligncenter pageeditingtable', 'editing-table'); print_section_add_menus($COURSE, $section, $modnames); if (!empty($modnamesused)) { $modinfo = get_fast_modinfo($COURSE); $vars = new stdClass(); $vars->delete = get_string("delete"); $vars->update = get_string("update"); $vars->sesskey = sesskey(); foreach ($modnamesused as $module => $modnamestr) { $orderby = 'i.name'; $fields = 'i.id, i.name, m.name ' . sql_as() . ' module, c.id ' . sql_as() . 'cmid, c.visible'; // Going to be executing SQL that could fail on purpose - avoid debug messages $debug = $CFG->debug; // Store current value $CFG->debug = false; // disable_debugging() doesn't seem to work // Check for field named type if (execute_sql("SELECT type FROM {$CFG->prefix}{$module} WHERE 1 = 2", false)) { // Has type - incorperate it into the sql $orderby = "i.type, {$orderby}"; $fields = "{$fields}, i.type"; } // Restore debug value $CFG->debug = $debug; $instances = get_records_sql("SELECT {$fields}\n FROM {$CFG->prefix}course_modules c,\n {$CFG->prefix}modules m,\n {$CFG->prefix}{$module} i\n WHERE i.course = {$COURSE->id}\n AND m.name = '{$module}'\n AND c.instance = i.id\n AND c.module = m.id\n ORDER BY {$orderby}"); if (!empty($instances)) { $lasttype = ''; print "<h2><a href=\"{$CFG->wwwroot}/mod/{$module}/index.php?id={$COURSE->id}\">{$modnamestr}</a></h2>\n"; print "<ul class=\"activity-list\">\n"; foreach ($instances as $instance) { if (!empty($instance->type) and $lasttype != $instance->type) { if (!empty($lasttype)) { // Switching types and it isn't the first time, close previously opened list print "</ul>\n</li>\n"; } // Try to get a name for the type (check module first) $strtype = get_string($instance->type, $module); if (strpos($strtype, '[') !== false) { $strtype = get_string($module . ':' . $instance->type, 'format_page'); } print "<li><div class=\"plus-minus\"><p><span class=\"no-space\"><strong>{$strtype}</strong></span></p></div>\n<ul>\n"; $lasttype = $instance->type; } if (!empty($modinfo->cms[$instance->cmid]->icon)) { $icon = "{$CFG->pixpath}/" . urldecode($modinfo->cms[$instance->cmid]->icon); } else { $icon = "{$CFG->modpixpath}/{$module}/icon.gif"; } if (empty($instance->visible)) { $linkclass = ' class="dimmed"'; } else { $linkclass = ''; } print '<li>'; print '<img src="' . $icon . '" class="icon" />'; print "<a{$linkclass} href=\"{$CFG->wwwroot}/mod/{$module}/view.php?id={$instance->cmid}\">" . format_string(strip_tags($instance->name), true, $COURSE->id) . '</a> '; print '<span class="commands">'; print "<a title=\"{$vars->update}\" href=\"{$CFG->wwwroot}/course/mod.php?update={$instance->cmid}&sesskey={$vars->sesskey}\">"; print "<img src=\"{$CFG->pixpath}/t/edit.gif\" class=\"icon-edit\" alt=\"{$vars->update}\" /></a> "; print "<a title=\"{$vars->delete}\" href=\"{$CFG->wwwroot}/course/format/page/format.php?id={$COURSE->id}&action=deletemod&sesskey={$vars->sesskey}&cmid={$instance->cmid}\">"; print "<img src=\"{$CFG->pixpath}/t/delete.gif\" class=\"icon-edit\" alt=\"{$vars->delete}\" /></a></span>"; print "</li>\n"; } if (!empty($lasttype)) { // Close type list since we know it was opened print "</ul>\n</li>\n"; } print "</ul>\n"; } } } else { print_box(get_string('noacivitiesfound', 'format_page')); } print_box_end(); }
case 'left': if (blocks_have_content($pageblocks, BLOCK_POS_LEFT) || $PAGE->user_is_editing()) { echo '<td style="vertical-align: top; width: ' . $blocks_preferred_width . 'px;" id="left-column">'; print_container_start(); blocks_print_group($PAGE, $pageblocks, BLOCK_POS_LEFT); print_container_end(); echo '</td>'; } else { echo '<td id="left-column"></td>'; } break; case 'middle': echo '<td valign="top" id="middle-column">'; echo '<h2>' . get_string('mygroups', 'local') . '</h2>'; /*** Start Groups Listing ***/ $sql = "SELECT g.id, g.groupleader, g.name, g.courseid, c.id " . sql_as() . " courseid, c.fullname " . sql_as() . " coursename\n FROM {$CFG->prefix}groups g, {$CFG->prefix}groups_members m, {$CFG->prefix}course c\n WHERE m.userid = {$USER->id}\n AND c.id = g.courseid\n AND m.groupid = g.id"; if ($groups = get_records_sql($sql)) { //print_object($groups); foreach ($groups as $group) { $uri = $CFG->wwwroot . '/course/view.php?id=' . $group->courseid; $members = groups_get_members($group->id); if (isset($group->groupleader)) { $leader = $members[$group->groupleader]; } echo '<div class="my_collaboration_group_box">'; // define links $messagelink = '<a href="' . $CFG->wwwroot . '/blocks/tao_team_groups/messagegroup.php?id=' . $group->courseid . '&groupid=' . $group->id . '">» ' . strtolower(get_string('messageall', 'local')) . '</a> '; $invitelink = '<a href="' . $CFG->wwwroot . '/blocks/tao_team_groups/managegroup.php?id=' . $group->courseid . '&groupid=' . $group->id . '">» ' . get_string('invite', 'local') . '</a> '; $viewlink = '<a href="' . $CFG->wwwroot . '/course/view.php?id=' . $group->courseid . '">» ' . strtolower(get_string('view')) . '</a> '; // print quick function links echo '<span class="my_collaboration_group_tools">';
/** * Common functions. * * ELIS(TM): Enterprise Learning Intelligence Suite * Copyright (C) 2008-2012 Remote Learner.net Inc http://www.remote-learner.net * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. * * @package elis * @subpackage curriculummanagement * @author Remote-Learner.net Inc * @license http://www.gnu.org/copyleft/gpl.html GNU GPL * @copyright (C) 2008-2012 Remote Learner.net Inc http://www.remote-learner.net * */ function create_views($viewprefix = false) { global $CFG, $db; $result = true; // if no prefix specified, use the default Moodle prefix if ($viewprefix === false) { $viewprefix = $CFG->prefix; } $AS = sql_as(); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}GradesListing4Transcript AS\n SELECT c.fullname {$AS} coursename, c.shortname {$AS} courseABBR, i.itemname {$AS} itemname,\n i.itemtype {$AS} itemtype, i.gradetype {$AS} gradetype, i.grademax {$AS} grademax,\n i.grademin {$AS} grademin, i.scaleid {$AS} scaleid, i.outcomeid {$AS} outcomeid,\n i.sortorder {$AS} sortorder, g.rawgrade {$AS} rawgrade, i.courseid {$AS} courseid,\n g.itemid {$AS} itemid, g.userid {$AS} userid, g.rawgrademax {$AS} rawgrademax,\n g.rawgrademin {$AS} rawgrademin, g.finalgrade {$AS} finalgrade, c.idnumber AS idnumber,\n u.username {$AS} username, u.firstname {$AS} firstname, u.lastname {$AS} lastname,\n u.address {$AS} address, u.city {$AS} city, u.country {$AS} country, u.institution {$AS} institution,\n u.department {$AS} department, u.firstaccess {$AS} firstaccess, u.lastaccess {$AS} lastaccess,\n u.lastlogin {$AS} lastlogin, u.firstname || ' ' || u.lastname {$AS} FullBy1st,\n u.lastname || ', ' || u.firstname {$AS} FullByLast\n FROM {$CFG->prefix}course c\n JOIN {$CFG->prefix}grade_items i ON c.id = i.courseid\n JOIN {$CFG->prefix}grade_grades g ON i.id = g.itemid\n JOIN {$CFG->prefix}user u ON g.userid = u.id\n WHERE i.itemtype = 'course'"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}GradesListing4Transcript AS\n SELECT c.fullname {$AS} coursename, c.shortname {$AS} courseABBR, i.itemname {$AS} itemname,\n i.itemtype {$AS} itemtype, i.gradetype {$AS} gradetype, i.grademax {$AS} grademax,\n i.grademin {$AS} grademin, i.scaleid {$AS} scaleid, i.outcomeid {$AS} outcomeid,\n i.sortorder {$AS} sortorder, g.rawgrade {$AS} rawgrade, i.courseid {$AS} courseid,\n g.itemid {$AS} itemid, g.userid {$AS} userid, g.rawgrademax {$AS} rawgrademax,\n g.rawgrademin {$AS} rawgrademin, g.finalgrade {$AS} finalgrade, c.idnumber AS idnumber,\n u.username {$AS} username, u.firstname {$AS} firstname, u.lastname {$AS} lastname,\n u.address {$AS} address, u.city {$AS} city, u.country {$AS} country, u.institution {$AS} institution,\n u.department {$AS} department, u.firstaccess {$AS} firstaccess, u.lastaccess {$AS} lastaccess,\n u.lastlogin {$AS} lastlogin, concat(u.firstname, _utf8' ', u.lastname) {$AS} FullBy1st,\n concat(u.lastname, _utf8', ', u.firstname) {$AS} FullByLast\n FROM {$CFG->prefix}course c\n JOIN {$CFG->prefix}grade_items i ON c.id = i.courseid\n JOIN {$CFG->prefix}grade_grades g ON i.id = g.itemid\n JOIN {$CFG->prefix}user u ON g.userid = u.id\n WHERE i.itemtype = _utf8'course'"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}UserExt AS\n SELECT u.id {$AS} id, u.auth {$AS} auth, u.confirmed {$AS} confirmed, u.policyagreed {$AS} policyagreed,\n u.deleted {$AS} deleted, u.mnethostid {$AS} mnethostid, u.username {$AS} username,\n u.password {$AS} password, u.idnumber {$AS} idnumber, u.firstname {$AS} firstname,\n u.lastname {$AS} lastname, u.firstname || ' ' || u.lastname {$AS} FullBy1st,\n u.lastname || ', ' || u.firstname {$AS} FullByLast, u.email AS email,\n u.emailstop {$AS} emailstop, u.icq {$AS} icq,u. skype {$AS} skype, u.yahoo {$AS} yahoo,\n u.aim {$AS} aim, u.msn {$AS} msn, u.phone1 {$AS} phone1, u.phone2 {$AS} phone2,\n u.institution {$AS} institution, u.department {$AS} department, u.address {$AS} address,\n u.city {$AS} city, u.country {$AS} country, u.lang {$AS} lang, u.theme {$AS} theme,\n u.timezone {$AS} timezone, u.firstaccess {$AS} firstaccess, u.lastaccess {$AS} lastaccess,\n u.lastlogin {$AS} lastlogin, u.currentlogin {$AS} currentlogin, u.lastip {$AS} lastip,\n u.secret {$AS} secret, u.picture {$AS} picture, u.url {$AS} url, u.description {$AS} description,\n u.mailformat {$AS} mailformat, u.maildigest {$AS} maildigest, u.maildisplay {$AS} maildisplay,\n u.htmleditor {$AS} htmleditor, u.ajax {$AS} ajax, u.autosubscribe {$AS} autosubscribe,\n u.trackforums {$AS} trackforums, u.timemodified {$AS} timemodified, u.trustbitmask AS trustbitmask,\n u.imagealt {$AS} imagealt, u.screenreader {$AS} screenreader\n FROM {$CFG->prefix}user u\n ORDER BY u.lastname, u.firstname"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}UserExt AS\n SELECT u.id {$AS} id, u.auth {$AS} auth, u.confirmed {$AS} confirmed, u.policyagreed {$AS} policyagreed,\n u.deleted {$AS} deleted, u.mnethostid {$AS} mnethostid, u.username {$AS} username,\n u.password {$AS} password, u.idnumber {$AS} idnumber, u.firstname {$AS} firstname,\n u.lastname {$AS} lastname, concat(u.firstname, _utf8' ', u.lastname) {$AS} FullBy1st,\n concat(u.lastname, _utf8', ', u.firstname) {$AS} FullByLast, u.email AS email,\n u.emailstop {$AS} emailstop, u.icq {$AS} icq,u. skype {$AS} skype, u.yahoo {$AS} yahoo,\n u.aim {$AS} aim, u.msn {$AS} msn, u.phone1 {$AS} phone1, u.phone2 {$AS} phone2,\n u.institution {$AS} institution, u.department {$AS} department, u.address {$AS} address,\n u.city {$AS} city, u.country {$AS} country, u.lang {$AS} lang, u.theme {$AS} theme,\n u.timezone {$AS} timezone, u.firstaccess {$AS} firstaccess, u.lastaccess {$AS} lastaccess,\n u.lastlogin {$AS} lastlogin, u.currentlogin {$AS} currentlogin, u.lastip {$AS} lastip,\n u.secret {$AS} secret, u.picture {$AS} picture, u.url {$AS} url, u.description {$AS} description,\n u.mailformat {$AS} mailformat, u.maildigest {$AS} maildigest, u.maildisplay {$AS} maildisplay,\n u.htmleditor {$AS} htmleditor, u.ajax {$AS} ajax, u.autosubscribe {$AS} autosubscribe,\n u.trackforums {$AS} trackforums, u.timemodified {$AS} timemodified, u.trustbitmask AS trustbitmask,\n u.imagealt {$AS} imagealt, u.screenreader {$AS} screenreader\n FROM {$CFG->prefix}user u\n ORDER BY u.lastname, u.firstname"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}Top5ForumUserTest AS\n SELECT f.scale {$AS} forumScale, r.rating {$AS} postRating, p.userid {$AS} postUserid,\n r.userid {$AS} ratingUserid, d.userid {$AS} DiscUserid, d.name {$AS} DiscName, f.course {$AS} course,\n f.type {$AS} forumType, f.name {$AS} forumName, f.assessed {$AS} assessed,d.firstpost {$AS} firstpost,\n p.totalscore {$AS} postTotalScore, p.created {$AS} created, p.modified {$AS} modified,\n d.forum {$AS} forumid, c.shortname {$AS} courseABBR, u.FullByLast {$AS} Participant\n FROM {$CFG->prefix}forum f\n JOIN {$CFG->prefix}forum_discussions d ON f.id = d.forum\n JOIN {$CFG->prefix}forum_posts p ON d.id = p.discussion\n LEFT JOIN {$CFG->prefix}forum_ratings r ON p.id = r.post\n JOIN {$CFG->prefix}course c ON f.course = c.id\n JOIN {$viewprefix}UserExt u ON p.userid = u.id"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}Top5ForumUserTest AS\n SELECT f.scale {$AS} forumScale, r.rating {$AS} postRating, p.userid {$AS} postUserid,\n r.userid {$AS} ratingUserid, d.userid {$AS} DiscUserid, d.name {$AS} DiscName, f.course {$AS} course,\n f.type {$AS} forumType, f.name {$AS} forumName, f.assessed {$AS} assessed,d.firstpost {$AS} firstpost,\n p.totalscore {$AS} postTotalScore, p.created {$AS} created, p.modified {$AS} modified,\n d.forum {$AS} forumid, c.shortname {$AS} courseABBR, u.FullByLast {$AS} Participant\n FROM {$CFG->prefix}forum f\n JOIN {$CFG->prefix}forum_discussions d ON f.id = d.forum\n JOIN {$CFG->prefix}forum_posts p ON d.id = p.discussion\n LEFT JOIN {$CFG->prefix}forum_ratings r ON p.id = r.post\n JOIN {$CFG->prefix}course c ON f.course = c.id\n JOIN {$viewprefix}UserExt u ON p.userid = u.id"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}Role2RoleAssignments AS\n SELECT a.roleid {$AS} roleid, c.id {$AS} courseid, a.userid {$AS} userid, u.username {$AS} username,\n u.firstname {$AS} firstname, u.lastname {$AS} lastname, u.FullBy1st {$AS} FullBy1st,\n c.fullname {$AS} coursename, c.shortname {$AS} courseABBR, r1.name {$AS} roleName\n FROM {$CFG->prefix}role_assignments a\n JOIN {$viewprefix}UserExt u ON a.userid = u.id\n JOIN {$CFG->prefix}context ctx ON ctx.id = a.contextid AND ctx.contextlevel = " . CONTEXT_COURSE . "\n JOIN {$CFG->prefix}course c ON c.id = ctx.instanceid\n JOIN {$CFG->prefix}role r1 ON r1.id = a.roleid"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}Role2RoleAssignments AS\n SELECT a.roleid {$AS} roleid, c.id {$AS} courseid, a.userid {$AS} userid, u.username {$AS} username,\n u.firstname {$AS} firstname, u.lastname {$AS} lastname, u.FullBy1st {$AS} FullBy1st,\n c.fullname {$AS} coursename, c.shortname {$AS} courseABBR, r1.name {$AS} roleName\n FROM {$CFG->prefix}role_assignments a\n JOIN {$viewprefix}UserExt u ON a.userid = u.id\n JOIN {$CFG->prefix}context ctx ON ctx.id = a.contextid AND ctx.contextlevel = " . CONTEXT_COURSE . "\n JOIN {$CFG->prefix}course c ON c.id = ctx.instanceid\n JOIN {$CFG->prefix}role r1 ON r1.id = a.roleid"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}Role2RoleAssignments5 AS\n SELECT a.roleid {$AS} roleid, a.contextid {$AS} courseid, a.userid {$AS} userid, u.username {$AS} username,\n u.firstname {$AS} firstname, u.lastname {$AS} lastname, u.FullBy1st {$AS} FullBy1st,\n c.fullname {$AS} coursename, c.shortname {$AS} courseABBR, a.timestart {$AS} timestart,\n a.timeend {$AS} timeend, TO_TIMESTAMP(a.timeend) {$AS} EndDate,\n TO_TIMESTAMP(a.timestart) {$AS} StartDate, u.FullByLast {$AS} FullByLast,\n a.timemodified {$AS} timemodified\n FROM {$CFG->prefix}role_assignments a\n JOIN {$CFG->prefix}role role\n ON a.roleid = role.id\n JOIN {$viewprefix}UserExt u ON a.userid = u.id\n JOIN {$CFG->prefix}context ctx ON ctx.id = a.contextid AND ctx.contextlevel = " . CONTEXT_COURSE . "\n JOIN {$CFG->prefix}course c ON c.id = ctx.instanceid\n WHERE role.shortname = 'student'"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}Role2RoleAssignments5 AS\n SELECT a.roleid {$AS} roleid, a.contextid {$AS} courseid, a.userid {$AS} userid, u.username {$AS} username,\n u.firstname {$AS} firstname, u.lastname {$AS} lastname, u.FullBy1st {$AS} FullBy1st,\n c.fullname {$AS} coursename, c.shortname {$AS} courseABBR, a.timestart {$AS} timestart,\n a.timeend {$AS} timeend, FROM_UNIXTIME(a.timeend) {$AS} EndDate,\n FROM_UNIXTIME(a.timestart) {$AS} StartDate, u.FullByLast {$AS} FullByLast,\n a.timemodified {$AS} timemodified\n FROM {$CFG->prefix}role_assignments a\n JOIN {$CFG->prefix}role role\n ON a.roleid = role.id\n JOIN {$viewprefix}UserExt u ON a.userid = u.id\n JOIN {$CFG->prefix}context ctx ON ctx.id = a.contextid AND ctx.contextlevel = " . CONTEXT_COURSE . "\n JOIN {$CFG->prefix}course c ON c.id = ctx.instanceid\n WHERE role.shortname = 'student'"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}grade_grades_with_outcomes_ext AS\n SELECT gg.userid {$AS} userid, gg.itemid {$AS} itemid, i.courseid {$AS} courseid, gg.rawgrade {$AS} rawgrade,\n gg.rawgrademax {$AS} rawgrademax, gg.rawgrademin {$AS} rawgrademin, gg.rawscaleid {$AS} rawscaleid,\n o.shortname {$AS} outcomeABBR, o.fullname {$AS} outcomename, gg.finalgrade {$AS} finalgrade,\n i.itemname {$AS} itemname, i.itemtype {$AS} itemtype, i.itemmodule {$AS} itemmodule,\n i.calculation {$AS} calculation, i.gradetype {$AS} gradetype, i.grademax {$AS} grademax,\n i.grademin {$AS} grademin, i.scaleid {$AS} scaleid, i.gradepass {$AS} gradepass,\n i.multfactor {$AS} multfactor, i.plusfactor {$AS} plusfactor, i.categoryid {$AS} categoryid,\n cc.name {$AS} cat, cc.description {$AS} catDesc, cc.parent {$AS} catParent, cc.sortorder {$AS} catSort,\n cc.coursecount {$AS} CatCourseCount, u.FullBy1st {$AS} FullBy1st, u.FullByLast {$AS} FullByLast,\n u.institution {$AS} institution, u.department {$AS} department, u.email {$AS} email,\n gg.excluded {$AS} excluded, i.sortorder {$AS} itemSort, c.sortorder {$AS} courseSort,\n c.fullname {$AS} coursename, c.shortname {$AS} courseABBR, i.outcomeid {$AS} outcomeid,\n u.username {$AS} username, o.scaleid {$AS} oscaleid, s.scale {$AS} scale\n FROM {$CFG->prefix}grade_items i\n JOIN {$CFG->prefix}grade_grades gg ON i.id = gg.itemid\n JOIN {$CFG->prefix}grade_outcomes o ON i.outcomeid = o.id\n LEFT JOIN {$CFG->prefix}course_categories cc ON i.categoryid = cc.id\n JOIN {$viewprefix}UserExt u ON gg.userid = u.id\n JOIN {$CFG->prefix}course c ON i.courseid = c.id\n JOIN {$CFG->prefix}scale s ON o.scaleid = s.id"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}grade_grades_with_outcomes_ext AS\n SELECT gg.userid {$AS} userid, gg.itemid {$AS} itemid, i.courseid {$AS} courseid, gg.rawgrade {$AS} rawgrade,\n gg.rawgrademax {$AS} rawgrademax, gg.rawgrademin {$AS} rawgrademin, gg.rawscaleid {$AS} rawscaleid,\n o.shortname {$AS} outcomeABBR, o.fullname {$AS} outcomename, gg.finalgrade {$AS} finalgrade,\n i.itemname {$AS} itemname, i.itemtype {$AS} itemtype, i.itemmodule {$AS} itemmodule,\n i.calculation {$AS} calculation, i.gradetype {$AS} gradetype, i.grademax {$AS} grademax,\n i.grademin {$AS} grademin, i.scaleid {$AS} scaleid, i.gradepass {$AS} gradepass,\n i.multfactor {$AS} multfactor, i.plusfactor {$AS} plusfactor, i.categoryid {$AS} categoryid,\n cc.name {$AS} cat, cc.description {$AS} catDesc, cc.parent {$AS} catParent, cc.sortorder {$AS} catSort,\n cc.coursecount {$AS} CatCourseCount, u.FullBy1st {$AS} FullBy1st, u.FullByLast {$AS} FullByLast,\n u.institution {$AS} institution, u.department {$AS} department, u.email {$AS} email,\n gg.excluded {$AS} excluded, i.sortorder {$AS} itemSort, c.sortorder {$AS} courseSort,\n c.fullname {$AS} coursename, c.shortname {$AS} courseABBR, i.outcomeid {$AS} outcomeid,\n u.username {$AS} username, o.scaleid {$AS} oscaleid, s.scale {$AS} scale\n FROM {$CFG->prefix}grade_items i\n JOIN {$CFG->prefix}grade_grades gg ON i.id = gg.itemid\n JOIN {$CFG->prefix}grade_outcomes o ON i.outcomeid = o.id\n LEFT JOIN {$CFG->prefix}course_categories cc ON i.categoryid = cc.id\n JOIN {$viewprefix}UserExt u ON gg.userid = u.id\n JOIN {$CFG->prefix}course c ON i.courseid = c.id\n JOIN {$CFG->prefix}scale s ON o.scaleid = s.id"; } $result = $result && execute_sql($sql); require_once $CFG->libdir . '/gradelib.php'; if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}grade_grades_with_outcome_counts AS\n SELECT SUM(CASE WHEN m.finalgrade = 1 THEN m.finalgrade ELSE 0 END) {$AS} Level1s,\n SUM(CASE WHEN m.finalgrade = 2 THEN m.finalgrade ELSE 0 END) {$AS} Level2s,\n SUM(CASE WHEN m.finalgrade = 3 THEN m.finalgrade ELSE 0 END) {$AS} Level3s,\n SUM(CASE WHEN m.finalgrade = 4 THEN m.finalgrade ELSE 0 END) {$AS} Level4s,\n SUM(CASE WHEN m.finalgrade = 5 THEN m.finalgrade ELSE 0 END) {$AS} Level5s,\n SUM(CASE WHEN m.finalgrade = 6 THEN m.finalgrade ELSE 0 END) {$AS} Level6s,\n SUM(CASE WHEN m.finalgrade = 1 THEN 1 ELSE 0 END) {$AS} Level1,\n SUM(CASE WHEN m.finalgrade = 2 THEN 1 ELSE 0 END) {$AS} Level2,\n SUM(CASE WHEN m.finalgrade = 3 THEN 1 ELSE 0 END) {$AS} Level3,\n SUM(CASE WHEN m.finalgrade = 4 THEN 1 ELSE 0 END) {$AS} Level4,\n SUM(CASE WHEN m.finalgrade = 5 THEN 1 ELSE 0 END) {$AS} Level5,\n SUM(CASE WHEN m.finalgrade = 6 THEN 1 ELSE 0 END) {$AS} Level6,\n SUM(m.finalgrade) {$AS} SumTotal, COUNT(m.finalgrade) {$AS} CountTotal,\n SUM(m.finalgrade) / COUNT(m.finalgrade) {$AS} ScaleAVG, m.userid {$AS} userid,\n COUNT(m.itemid) {$AS} itemCount, m.outcomeid {$AS} outcomeid, m.username {$AS} username,\n m.outcomeABBR {$AS} outcomeABBR, m.outcomename {$AS} outcomename, m.finalgrade {$AS} finalgrade,\n ROUND(m.grademin, 0) || '-' || ROUND(m.grademax, 0) {$AS} ScaleExp,\n m.FullByLast {$AS} FullByLast, m.itemid {$AS} itemid, m.scale {$AS} scale\n FROM {$viewprefix}grade_grades_with_outcomes_ext m\n WHERE m.gradetype = " . GRADE_TYPE_SCALE . "\n GROUP BY m.userid, m.outcomeid, m.username, m.outcomeABBR, m.outcomename, m.finalgrade,\n m.grademin, m.grademax, m.FullByLast, m.itemid, m.scale"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}grade_grades_with_outcome_counts AS\n SELECT SUM(IF((m.finalgrade = 1), m.finalgrade, 0)) {$AS} Level1s,\n SUM(IF((m.finalgrade = 2), m.finalgrade, 0)) {$AS} Level2s,\n SUM(IF((m.finalgrade = 3), m.finalgrade, 0)) {$AS} Level3s,\n SUM(IF((m.finalgrade = 4), m.finalgrade, 0)) {$AS} Level4s,\n SUM(IF((m.finalgrade = 5), m.finalgrade, 0)) {$AS} Level5s,\n SUM(IF((m.finalgrade = 6), m.finalgrade, 0)) {$AS} Level6s,\n SUM(IF((m.finalgrade = 1), 1, 0)) {$AS} Level1, SUM(IF((m.finalgrade = 2), 1, 0)) {$AS} Level2,\n SUM(IF((m.finalgrade = 3), 1, 0)) {$AS} Level3, SUM(IF((m.finalgrade = 4), 1, 0)) {$AS} Level4,\n SUM(IF((m.finalgrade = 5), 1, 0)) {$AS} Level5, SUM(IF((m.finalgrade = 6), 1, 0)) {$AS} Level6,\n SUM(m.finalgrade) {$AS} SumTotal, COUNT(m.finalgrade) {$AS} CountTotal,\n (SUM(m.finalgrade) / COUNT(m.finalgrade)) {$AS} ScaleAVG, m.userid {$AS} userid,\n COUNT(m.itemid) {$AS} itemCount, m.outcomeid {$AS} outcomeid, m.username {$AS} username,\n m.outcomeABBR {$AS} outcomeABBR, m.outcomename {$AS} outcomename, m.finalgrade {$AS} finalgrade,\n CONCAT(FORMAT(m.grademin, 0), _utf8'-', FORMAT(m.grademax, 0)) {$AS} ScaleExp,\n m.FullByLast {$AS} FullByLast, m.itemid {$AS} itemid, m.scale {$AS} scale\n FROM {$viewprefix}grade_grades_with_outcomes_ext m\n WHERE m.gradetype = " . GRADE_TYPE_SCALE . "\n GROUP BY m.userid, m.outcomeid, m.itemid"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}GroupsNMembers AS\n SELECT g.courseid {$AS} courseid, g.name {$AS} groupname, gm.groupid {$AS} groupid, gm.userid {$AS} userid,\n c.fullname {$AS} courseName, c.shortname {$AS} CourseABBR,\n c.shortname || ' - ' || COALESCE(g.name, '<No Group>') {$AS} courseGroupExp,\n gm.timeadded {$AS} timeadded\n FROM {$CFG->prefix}groups g\n JOIN {$CFG->prefix}groups_members gm ON g.id = gm.groupid\n JOIN {$CFG->prefix}course c ON g.courseid = c.id\n ORDER BY c.shortname, g.name, gm.userid"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}GroupsNMembers AS\n SELECT g.courseid {$AS} courseid, g.name {$AS} groupname, gm.groupid {$AS} groupid, gm.userid {$AS} userid,\n c.fullname {$AS} courseName, c.shortname {$AS} CourseABBR,\n CONCAT(c.shortname, _utf8' - ', IFNULL(g.name, _utf8'<No Group>')) {$AS} courseGroupExp,\n gm.timeadded {$AS} timeadded\n FROM {$CFG->prefix}groups g\n JOIN {$CFG->prefix}groups_members gm ON g.id = gm.groupid\n JOIN {$CFG->prefix}course c ON g.courseid = c.id\n ORDER BY c.shortname, g.name, gm.userid"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}moodleLog AS\n SELECT l.id {$AS} id, l.time {$AS} time,\n TO_CHAR(TO_TIMESTAMP(l.time), 'Dy') {$AS} LogWeekDay,\n TO_CHAR(TO_TIMESTAMP(l.time), 'D') {$AS} LogWeekDaynum,\n TO_CHAR(TO_TIMESTAMP(l.time), 'YYYY') {$AS} LogYear,\n TO_CHAR(TO_TIMESTAMP(l.time), 'MM') {$AS} LogMonth,\n TO_CHAR(TO_TIMESTAMP(l.time), 'Month') {$AS} LogMonthName,\n TO_CHAR(TO_TIMESTAMP(l.time), 'DD') {$AS} LogDay,\n l.userid {$AS} userid, l.ip {$AS} ip, l.course {$AS} course, l.module {$AS} module,\n l.cmid {$AS} cmid, l.action {$AS} action, l.url {$AS} url, l.info {$AS} info,\n c.fullname {$AS} CourseName, c.shortname {$AS} CourseABBR,\n u.firstname || ' ' || u.lastname {$AS} UserNameBy1st,\n u.lastname || ', ' || u.firstname {$AS} UserNameByLast,\n u.username {$AS} username, ra.roleid {$AS} roleid, r.name {$AS} rolename\n FROM {$CFG->prefix}log l\n JOIN {$CFG->prefix}course c ON l.course = c.id\n JOIN {$CFG->prefix}user u ON l.userid = u.id\n LEFT JOIN {$CFG->prefix}role_assignments ra ON ra.contextid = l.course AND ra.userid = l.userid\n LEFT JOIN {$CFG->prefix}role r ON ra.roleid = r.id\n ORDER BY l.userid, l.time"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}moodleLog AS\n SELECT l.id {$AS} id, l.time {$AS} time,\n DATE_FORMAT(FROM_UNIXTIME(l.time), _utf8'%a') {$AS} LogWeekDay,\n DATE_FORMAT(FROM_UNIXTIME(l.time), _utf8'%w') {$AS} LogWeekDaynum,\n DATE_FORMAT(FROM_UNIXTIME(l.time), _utf8'%Y') {$AS} LogYear,\n DATE_FORMAT(FROM_UNIXTIME(l.time), _utf8'%m') {$AS} LogMonth,\n DATE_FORMAT(FROM_UNIXTIME(l.time), _utf8'%M') {$AS} LogMonthName,\n DATE_FORMAT(FROM_UNIXTIME(l.time), _utf8'%d') {$AS} LogDay,\n l.userid {$AS} userid, l.ip {$AS} ip, l.course {$AS} course, l.module {$AS} module,\n l.cmid {$AS} cmid, l.action {$AS} action, l.url {$AS} url, l.info {$AS} info,\n c.fullname {$AS} CourseName, c.shortname {$AS} CourseABBR,\n CONCAT((u.firstname + _utf8' ') + u.lastname) {$AS} UserNameBy1st,\n CONCAT((u.lastname + _utf8', ') + u.firstname) {$AS} UserNameByLast,\n u.username {$AS} username, ra.roleid {$AS} roleid, r.name {$AS} rolename\n FROM {$CFG->prefix}log l\n JOIN {$CFG->prefix}course c ON l.course = c.id\n JOIN {$CFG->prefix}user u ON l.userid = u.id\n LEFT JOIN {$CFG->prefix}role_assignments ra ON ra.contextid = l.course AND ra.userid = l.userid\n LEFT JOIN {$CFG->prefix}role r ON ra.roleid = r.id\n ORDER BY l.userid, l.time"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}moodleLogwDate AS\n SELECT DATE (m.LogMonthName || ' ' || m.LogDay || ', ' || m.LogYear) {$AS} LogDate,\n m.id {$AS} id, m.time {$AS} time, m.LogWeekDay {$AS} LogWeekDay, m.LogWeekDaynum {$AS} LogWeekDaynum,\n m.LogYear {$AS} LogYear, m.LogMonth {$AS} LogMonth, m.LogMonthName {$AS} LogMonthName,\n m.LogDay {$AS} LogDay, m.userid {$AS} userid, m.ip {$AS} ip, m.course {$AS} course,\n m.module {$AS} module, m.cmid {$AS} cmid, m.action {$AS} action, m.url {$AS} url, m.info {$AS} info,\n m.CourseName {$AS} CourseName, m.CourseABBR {$AS} CourseABBR, m.UserNameBy1st {$AS} UserNameBy1st,\n m.UserNameByLast {$AS} UserNameByLast, m.username {$AS} username, m.roleid {$AS} roleid,\n m.rolename {$AS} rolename\n FROM {$viewprefix}moodleLog m"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}moodleLogwDate AS\n SELECT STR_TO_DATE(CONCAT(m.LogMonth, '/', m.LogDay, '/', m.LogYear), _utf8'%m/%d/%Y') {$AS} LogDate,\n m.id {$AS} id, m.time {$AS} time, m.LogWeekDay {$AS} LogWeekDay, m.LogWeekDaynum {$AS} LogWeekDaynum,\n m.LogYear {$AS} LogYear, m.LogMonth {$AS} LogMonth, m.LogMonthName {$AS} LogMonthName,\n m.LogDay {$AS} LogDay, m.userid {$AS} userid, m.ip {$AS} ip, m.course {$AS} course,\n m.module {$AS} module, m.cmid {$AS} cmid, m.action {$AS} action, m.url {$AS} url, m.info {$AS} info,\n m.CourseName {$AS} CourseName, m.CourseABBR {$AS} CourseABBR, m.UserNameBy1st {$AS} UserNameBy1st,\n m.UserNameByLast {$AS} UserNameByLast, m.username {$AS} username, m.roleid {$AS} roleid,\n m.rolename {$AS} rolename\n FROM {$viewprefix}moodleLog m"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}LogwDateSummary AS\n SELECT COUNT(m.id) {$AS} CountClicks, m.LogWeekDay {$AS} LogWeekDay, m.LogYear {$AS} LogYear,\n m.LogMonth {$AS} LogMonth, m.LogMonthName {$AS} LogMonthName, m.LogDay {$AS} LogDay,\n m.LogDate {$AS} LogDate, TO_CHAR(m.LogDate, 'WW') {$AS} WeekNum, m.userid {$AS} userid,\n m.course {$AS} course, m.CourseName {$AS} CourseName,m .UserNameBy1st {$AS} UserNameBy1st,\n m.UserNameByLast {$AS} UserNameByLast, m.username {$AS} username, m.CourseABBR {$AS} CourseABBR,\n m.LogWeekDaynum {$AS} LogWeekDaynum, m.roleid {$AS} roleid, m.rolename {$AS} rolename\n FROM {$viewprefix}moodleLogwDate m\n GROUP BY m.course, m.LogDate, m.LogWeekDay, m.LogYear, m.LogMonth, m.LogMonthName, m.LogDay,\n m.userid, m.CourseName, m.UserNameBy1st, m.UserNameByLast, m.username, m.CourseABBR,\n m.LogWeekDaynum, m.roleid, m.rolename\n ORDER BY m.course, m.LogDate"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}LogwDateSummary AS\n SELECT COUNT(m.id) {$AS} CountClicks, m.LogWeekDay {$AS} LogWeekDay, m.LogYear {$AS} LogYear,\n m.LogMonth {$AS} LogMonth, m.LogMonthName {$AS} LogMonthName, m.LogDay {$AS} LogDay,\n m.LogDate {$AS} LogDate, WEEK(m.LogDate, 2) {$AS} WeekNum, m.userid {$AS} userid, m.course {$AS} course,\n m.CourseName {$AS} CourseName,m .UserNameBy1st {$AS} UserNameBy1st,\n m.UserNameByLast {$AS} UserNameByLast, m.username {$AS} username, m.CourseABBR {$AS} CourseABBR,\n m.LogWeekDaynum {$AS} LogWeekDaynum, m.roleid {$AS} roleid, m.rolename {$AS} rolename\n FROM {$viewprefix}moodleLogwDate m\n GROUP BY m.course, m.LogDate, m.userid, m.roleid\n ORDER BY m.course, m.LogDate"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}LogSummaryWithGroups AS\n SELECT SUM(CASE WHEN L.LogWeekDay = 'Mon' THEN L.CountClicks ELSE 0 END) {$AS} Mon,\n SUM(CASE WHEN L.LogWeekDay = 'Tue' THEN L.CountClicks ELSE 0 END) {$AS} Tue,\n SUM(CASE WHEN L.LogWeekDay = 'Wed' THEN L.CountClicks ELSE 0 END) {$AS} Wed,\n SUM(CASE WHEN L.LogWeekDay = 'Thu' THEN L.CountClicks ELSE 0 END) {$AS} Thu,\n SUM(CASE WHEN L.LogWeekDay = 'Fri' THEN L.CountClicks ELSE 0 END) {$AS} Fri,\n SUM(CASE WHEN L.LogWeekDay = 'Sat' THEN L.CountClicks ELSE 0 END) {$AS} Sat,\n SUM(CASE WHEN L.LogWeekDay = 'Sun' THEN L.CountClicks ELSE 0 END) {$AS} Sun,\n SUM(L.CountClicks) {$AS} Total, L.LogWeekDay {$AS} LogWeekDay, L.LogYear {$AS} LogYear,\n L.LogMonth {$AS} LogMonth, L.LogDay {$AS} LogDay, L.LogDate {$AS} LogDate,\n L.LogDate - CAST(TO_CHAR(L.LogDate, 'D days') AS INTERVAL) {$AS} MyMonday, L.WeekNum {$AS} WeekNum,\n L.userid {$AS} userid, L.course {$AS} course, L.CourseName {$AS} CourseName,\n L.UserNameBy1st {$AS} UserNameBy1st, L.UserNameByLast {$AS} UserNameByLast,\n L.username {$AS} username, L.CourseABBR {$AS} CourseABBR, G.groupname {$AS} groupname,\n COALESCE(G.courseGroupExp, L.CourseABBR || ' <Not Grouped>') {$AS} courseGroupExp,\n G.groupid {$AS} groupid\n FROM {$viewprefix}LogwDateSummary L\n LEFT JOIN {$viewprefix}GroupsNMembers G ON L.userid = G.userid AND G.courseid = L.course\n GROUP BY L.course, G.courseGroupExp, L.LogDate - CAST(TO_CHAR(L.LogDate, 'D days') AS INTERVAL),\n L.LogWeekDay, L.LogYear, L.LogMonth, L.LogDay, L.LogDate, L.WeekNum, L.userid,\n L.CourseName, L.UserNameBy1st, L.UserNameByLast, L.username, L.CourseABBR, G.groupname,\n g.groupid\n ORDER BY L.course, COALESCE(G.courseGroupExp, L.CourseABBR || ' <Not Grouped>'), L.username,\n L.LogDate - CAST(TO_CHAR(L.LogDate, 'D days') AS INTERVAL)"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}LogSummaryWithGroups AS\n SELECT SUM(IF((L.LogWeekDay = _utf8'Mon'), L.CountClicks, 0)) {$AS} Mon,\n SUM(IF((L.LogWeekDay = _utf8'Tue'), L.CountClicks, 0)) {$AS} Tue,\n SUM(IF((L.LogWeekDay = _utf8'Wed'), L.CountClicks, 0)) {$AS} Wed,\n SUM(IF((L.LogWeekDay = _utf8'Thu'), L.CountClicks, 0)) {$AS} Thu,\n SUM(IF((L.LogWeekDay = _utf8'Fri'), L.CountClicks, 0)) {$AS} Fri,\n SUM(IF((L.LogWeekDay = _utf8'Sat'), L.CountClicks, 0)) {$AS} Sat,\n SUM(IF((L.LogWeekDay = _utf8'Sun'), L.CountClicks, 0)) {$AS} Sun,\n SUM(L.CountClicks) {$AS} Total, L.LogWeekDay {$AS} LogWeekDay, L.LogYear {$AS} LogYear,\n L.LogMonth {$AS} LogMonth, L.LogDay {$AS} LogDay, L.LogDate {$AS} LogDate,\n (L.LogDate - INTERVAL WEEKDAY(L.LogDate) DAY) {$AS} MyMonday, L.WeekNum {$AS} WeekNum,\n L.userid {$AS} userid, L.course {$AS} course, L.CourseName {$AS} CourseName,\n L.UserNameBy1st {$AS} UserNameBy1st, L.UserNameByLast {$AS} UserNameByLast,\n L.username {$AS} username, L.CourseABBR {$AS} CourseABBR, G.groupname {$AS} groupname,\n IFNULL(G.courseGroupExp, CONCAT(L.CourseABBR, _utf8' <Not Grouped>')) {$AS} courseGroupExp,\n G.groupid {$AS} groupid\n FROM {$viewprefix}LogwDateSummary L\n LEFT JOIN {$viewprefix}GroupsNMembers G ON L.userid = G.userid AND G.courseid = L.course\n GROUP BY L.course, G.courseGroupExp, L.username, (L.LogDate - INTERVAL WEEKDAY(L.LogDate) DAY)\n ORDER BY L.course, IFNULL(G.courseGroupExp, CONCAT(L.CourseABBR, _utf8' <Not Grouped>')), L.username,\n (L.LogDate - INTERVAL WEEKDAY(L.LogDate) DAY)"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}LoginDurationByUserNDate AS\n SELECT MAX(m.time) {$AS} maxSec, MIN(m.time) {$AS} MinSec,\n (MAX(m.time) - MIN(m.time)) / 60 {$AS} DurationNMinutes,\n ((MAX(m.time) - MIN(m.time)) / 60) / 60 {$AS} DurationNHrs,\n m.LogDate {$AS} LogDate, m.LogWeekDay {$AS} LogWeekDay, m.LogYear {$AS} LogYear,\n m.LogMonth {$AS} LogMonth, m.LogDay {$AS} LogDay, COUNT(DISTINCT m.userid) {$AS} CountUserid,\n COUNT(m.id) {$AS} CountClicks, TO_CHAR(m.LogDate, 'WW') {$AS} WeekNum\n FROM {$viewprefix}moodleLogwDate m\n GROUP BY m.LogDate, m.LogWeekDay, m.LogYear, m.LogMonth, m.LogDay, m.userid\n ORDER BY m.userid, m.LogDate"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}LoginDurationByUserNDate AS\n SELECT MAX(m.time) {$AS} maxSec, MIN(m.time) {$AS} MinSec,\n ((MAX(m.time) - MIN(m.time)) / 60) {$AS} DurationNMinutes,\n (((MAX(m.time) - MIN(m.time)) / 60) / 60) {$AS} DurationNHrs,\n m.LogDate {$AS} LogDate, m.LogWeekDay {$AS} LogWeekDay, m.LogYear {$AS} LogYear,\n m.LogMonth {$AS} LogMonth, m.LogDay {$AS} LogDay, COUNT(DISTINCT m.userid) {$AS} CountUserid,\n COUNT(m.id) {$AS} CountClicks, WEEK(m.LogDate, 5) {$AS} WeekNum\n FROM {$viewprefix}moodleLogwDate m\n GROUP BY m.LogDate, m.LogWeekDay, m.LogYear, m.LogMonth, m.LogDay, m.userid\n ORDER BY m.userid, m.LogDate"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}LoginDurationByUserNDateExt AS\n SELECT m.maxSec {$AS} maxSec, m.MinSec {$AS} MinSec, m.DurationNMinutes {$AS} DurationNMinutes,\n m.DurationNHrs {$AS} DurationNHrs, m.LogDate {$AS} LogDate,\n TO_CHAR(m.LogDate, 'D DD Month YYYY') {$AS} MyDateTxt, m.LogWeekDay {$AS} LogWeekDay,\n m.LogYear {$AS} LogYear, m.LogMonth {$AS} LogMonth, m.LogDay {$AS} LogDay,\n m.CountClicks {$AS} CountClicks, m.WeekNum {$AS} WeekNum,\n m.CountClicks / m.DurationNHrs {$AS} ClicksPerHr, m.CountUserid {$AS} CountUserid\n FROM {$viewprefix}LoginDurationByUserNDate m"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}LoginDurationByUserNDateExt AS\n SELECT m.maxSec {$AS} maxSec, m.MinSec {$AS} MinSec, m.DurationNMinutes {$AS} DurationNMinutes,\n m.DurationNHrs {$AS} DurationNHrs, m.LogDate {$AS} LogDate,\n DATE_FORMAT(m.LogDate, _utf8'%W %e %M %Y') {$AS} MyDateTxt, m.LogWeekDay {$AS} LogWeekDay,\n m.LogYear {$AS} LogYear, m.LogMonth {$AS} LogMonth, m.LogDay {$AS} LogDay,\n m.CountClicks {$AS} CountClicks, m.WeekNum {$AS} WeekNum,\n (m.CountClicks / m.DurationNHrs) {$AS} ClicksPerHr, m.CountUserid {$AS} CountUserid\n FROM {$viewprefix}LoginDurationByUserNDate m"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}testweekdayCalc AS\n SELECT DISTINCT m.LogDate AS LogDate,\n m.LogDate - CAST (TO_CHAR(m.LogDate, 'D days') AS INTERVAL) AS MyMonday,\n m.WeekNum AS WeekNum, m.LogWeekDay AS LogWeekDay\n FROM {$viewprefix}LoginDurationByUserNDateExt m\n ORDER BY m.LogDate"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}testweekdayCalc AS\n SELECT DISTINCT m.LogDate {$AS} LogDate, (m.LogDate - INTERVAL WEEKDAY(m.LogDate) DAY) {$AS} MyMonday,\n m.WeekNum {$AS} WeekNum, m.LogWeekDay {$AS} LogWeekDay\n FROM {$viewprefix}LoginDurationByUserNDateExt m\n ORDER BY m.LogDate"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}LoginDurationbyUserNDate1 AS\n SELECT MAX(m.time) {$AS} maxSec, MIN(m.time) {$AS} MinSec,\n (MAX(m.time) - MIN(m.time)) / 60 {$AS} DurationNMinutes,\n ((MAX(m.time) - MIN(m.time)) / 60) / 60 {$AS} DurationNHrs, m.LogWeekDay {$AS} LogWeekDay,\n m.LogYear {$AS} LogYear, m.LogMonth {$AS} LogMonth, m.LogDay {$AS} LogDay,\n COUNT(DISTINCT m.userid) {$AS} CountUserid, COUNT(m.id) {$AS} CountClicks,\n TO_CHAR(m.LogDate, 'WW') {$AS} WeekNum, t.MyMonday {$AS} MyMonday,\n m.LogDate {$AS} LogDate\n FROM {$viewprefix}moodleLogwDate m\n JOIN {$viewprefix}testweekdayCalc t ON m.LogDate = t.LogDate\n GROUP BY m.LogDate,m.LogWeekDay, m.LogYear, m.LogMonth, m.LogDay, t.MyMonday,\n m.userid\n ORDER BY m.userid, m.LogDate"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}LoginDurationbyUserNDate1 AS\n SELECT MAX(m.time) {$AS} maxSec, MIN(m.time) {$AS} MinSec,\n ((MAX(m.time) - MIN(m.time)) / 60) {$AS} DurationNMinutes,\n (((MAX(m.time) - MIN(m.time)) / 60) / 60) {$AS} DurationNHrs, m.LogWeekDay {$AS} LogWeekDay,\n m.LogYear {$AS} LogYear, m.LogMonth {$AS} LogMonth, m.LogDay {$AS} LogDay,\n COUNT(DISTINCT m.userid) {$AS} CountUserid, COUNT(m.id) {$AS} CountClicks,\n WEEK(m.LogDate,5) {$AS} WeekNum, t.MyMonday {$AS} MyMonday, m.LogDate {$AS} LogDate\n FROM {$viewprefix}moodleLogwDate m\n JOIN {$viewprefix}testweekdayCalc t ON m.LogDate = t.LogDate\n GROUP BY m.LogDate,m.LogWeekDay, m.LogYear, m.LogMonth, m.LogDay, m.userid\n ORDER BY m.userid, m.LogDate"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}LoginDurationByUserNDate1Ext AS\n SELECT TO_CHAR(m.LogDate, 'D DD Month YYYY') {$AS} MyDateTxt,\n m.CountClicks / m.DurationNHrs {$AS} ClicksPerHr, m.maxSec {$AS} maxSec, m.MinSec {$AS} MinSec,\n m.DurationNMinutes {$AS} DurationNMinutes, m.DurationNHrs {$AS} DurationNHrs,\n m.LogWeekDay {$AS} LogWeekDay, m.LogYear {$AS} LogYear, m.LogMonth {$AS} LogMonth,\n m.LogDay {$AS} LogDay, m.CountUserid {$AS} CountUserid, m.CountClicks {$AS} CountClicks,\n m.WeekNum {$AS} WeekNum, m.MyMonday {$AS} MyMonday, m.LogDate {$AS} LogDate\n FROM {$viewprefix}LoginDurationbyUserNDate1 m"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}LoginDurationByUserNDate1Ext AS\n SELECT DATE_FORMAT(m.LogDate, _utf8'%W %e %M %Y') {$AS} MyDateTxt,\n (m.CountClicks / m.DurationNHrs) {$AS} ClicksPerHr, m.maxSec {$AS} maxSec, m.MinSec {$AS} MinSec,\n m.DurationNMinutes {$AS} DurationNMinutes, m.DurationNHrs {$AS} DurationNHrs,\n m.LogWeekDay {$AS} LogWeekDay, m.LogYear {$AS} LogYear, m.LogMonth {$AS} LogMonth,\n m.LogDay {$AS} LogDay, m.CountUserid {$AS} CountUserid, m.CountClicks {$AS} CountClicks,\n m.WeekNum {$AS} WeekNum, m.MyMonday {$AS} MyMonday, m.LogDate {$AS} LogDate\n FROM {$viewprefix}LoginDurationbyUserNDate1 m"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}SiteWideTimeStats AS\n SELECT SUM(CASE WHEN L.LogWeekDay = 'Mon' THEN L.CountClicks ELSE 0 END) {$AS} Mon,\n SUM(CASE WHEN L.LogWeekDay = 'Tue' THEN L.CountClicks ELSE 0 END) {$AS} Tue,\n SUM(CASE WHEN L.LogWeekDay = 'Wed' THEN L.CountClicks ELSE 0 END) {$AS} Wed,\n SUM(CASE WHEN L.LogWeekDay = 'Thu' THEN L.CountClicks ELSE 0 END) {$AS} Thu,\n SUM(CASE WHEN L.LogWeekDay = 'Fri' THEN L.CountClicks ELSE 0 END) {$AS} Fri,\n SUM(CASE WHEN L.LogWeekDay = 'Sat' THEN L.CountClicks ELSE 0 END) {$AS} Sat,\n SUM(CASE WHEN L.LogWeekDay = 'Sun' THEN L.CountClicks ELSE 0 END) {$AS} Sun,\n SUM(L.CountClicks) {$AS} Total, SUM(L.ClicksPerHr) {$AS} TtlCPH, L.MyDateTxt {$AS} MyDateTxt,\n L.ClicksPerHr {$AS} ClicksPerHr, L.DurationNHrs {$AS} DurationNHrs, L.CountUserid {$AS} CountUserid,\n L.CountClicks {$AS} CountClicks, L.WeekNum {$AS} WeekNum, L.MyMonday {$AS} MyMonday\n FROM {$viewprefix}LoginDurationByUserNDate1Ext L\n GROUP BY L.WeekNum, L.MyDateTxt, L.ClicksPerHr, L.DurationNHrs, L.CountUserid, L.CountClicks,\n L.MyMonday\n ORDER BY L.MyMonday DESC"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}SiteWideTimeStats AS\n SELECT SUM(IF((L.LogWeekDay = _utf8'Mon'), L.CountClicks, 0)) {$AS} Mon,\n SUM(IF((L.LogWeekDay = _utf8'Tue'), L.CountClicks, 0)) {$AS} Tue,\n SUM(IF((L.LogWeekDay = _utf8'Wed'), L.CountClicks, 0)) {$AS} Wed,\n SUM(IF((L.LogWeekDay = _utf8'Thu'), L.CountClicks, 0)) {$AS} Thu,\n SUM(IF((L.LogWeekDay = _utf8'Fri'), L.CountClicks, 0)) {$AS} Fri,\n SUM(IF((L.LogWeekDay = _utf8'Sat'), L.CountClicks, 0)) {$AS} Sat,\n SUM(IF((L.LogWeekDay = _utf8'Sun'), L.CountClicks, 0)) {$AS} Sun,\n SUM(L.CountClicks) {$AS} Total, SUM(L.ClicksPerHr) {$AS} TtlCPH, L.MyDateTxt {$AS} MyDateTxt,\n L.ClicksPerHr {$AS} ClicksPerHr, L.DurationNHrs {$AS} DurationNHrs, L.CountUserid {$AS} CountUserid,\n L.CountClicks {$AS} CountClicks, L.WeekNum {$AS} WeekNum, L.MyMonday {$AS} MyMonday\n FROM {$viewprefix}LoginDurationByUserNDate1Ext L\n GROUP BY L.WeekNum, L.MyDateTxt\n ORDER BY L.MyMonday DESC"; } $result = $result && execute_sql($sql); if ($db->databaseType == 'postgres7') { $sql = "CREATE OR REPLACE VIEW {$viewprefix}courseNforums AS\n SELECT f.id {$AS} forumid, c.shortname || ' | ' || f.name {$AS} courseNforumname\n FROM {$CFG->prefix}forum f\n JOIN {$CFG->prefix}course c ON c.id = f.course\n ORDER BY c.shortname, f.name"; } else { $sql = "CREATE OR REPLACE VIEW {$viewprefix}courseNforums AS\n SELECT f.id {$AS} forumid, CONCAT(c.shortname, ' | ', f.name) {$AS} courseNforumname\n FROM {$CFG->prefix}forum f\n JOIN {$CFG->prefix}course c ON c.id = f.course\n ORDER BY c.shortname, f.name"; } $result = $result && execute_sql($sql); return $result; }