PHP sql_concat函数代码示例

 function get_records($filter)
 {
     global $CURMAN, $USER;
     $id = $this->required_param('id', PARAM_INT);
     $sort = $this->optional_param('sort', 'name', PARAM_ALPHA);
     $dir = $this->optional_param('dir', 'ASC', PARAM_ALPHA);
     $pagenum = $this->optional_param('page', 0, PARAM_INT);
     $FULLNAME = sql_concat('usr.firstname', "' '", 'usr.lastname');
     $sql = "  FROM {$CURMAN->db->prefix_table(USRTABLE)} usr\n       LEFT OUTER JOIN {$CURMAN->db->prefix_table(CLSTASSTABLE)} ca ON ca.userid = usr.id AND ca.clusterid = {$id} AND ca.plugin = 'manual'\n                 WHERE ca.userid IS NULL";
     $extrasql = $filter->get_sql_filter();
     if ($extrasql) {
         $sql .= " AND {$extrasql}";
     }
     if (!clusterpage::_has_capability('block/curr_admin:cluster:enrol')) {
         //perform SQL filtering for the more "conditional" capability
         //get the context for the "indirect" capability
         $context = cm_context_set::for_user_with_capability('cluster', 'block/curr_admin:cluster:enrol_cluster_user', $USER->id);
         $allowed_clusters = cluster::get_allowed_clusters($id);
         if (empty($allowed_clusters)) {
             $sql .= ' AND 0=1';
         } else {
             $cluster_filter = implode(',', $allowed_clusters);
             $sql .= " AND usr.id IN (\n                            SELECT userid FROM " . $CURMAN->db->prefix_table(CLSTUSERTABLE) . "\n                            WHERE clusterid IN ({$cluster_filter}))";
         }
     }
     $count = $CURMAN->db->count_records_sql('SELECT COUNT(usr.id) ' . $sql);
     if ($sort) {
         if ($sort == 'name') {
             $sort = 'lastname';
         }
         $sql .= " ORDER BY {$sort} {$dir}";
     }
     $users = $CURMAN->db->get_records_sql("SELECT usr.*, {$FULLNAME} AS name" . $sql, $pagenum * 30, 30);
     return array($users, $count);
 }

 function get_records_from_selection($selection)
 {
     global $CURMAN;
     $id = $this->required_param('id', PARAM_INT);
     $FULLNAME = sql_concat('usr.firstname', "' '", 'usr.lastname');
     $sql = "SELECT watlst.id, usr.id as uid, {$FULLNAME} as name, usr.idnumber, usr.country, usr.language, watlst.timecreated\n                  FROM {$CURMAN->db->prefix_table(WAITLISTTABLE)} watlst\n                  JOIN {$CURMAN->db->prefix_table(USRTABLE)} usr ON watlst.userid = usr.id\n                 WHERE watlst.classid = {$id}\n                   AND watlst.id IN (" . implode(',', $selection) . ')';
     return $CURMAN->db->get_records_sql($sql);
 }

function stats_get_parameters($time, $report, $courseid, $mode, $roleid = 0)
{
    global $CFG, $db;
    $param = new object();
    if ($time < 10) {
        // dailies
        // number of days to go back = 7* time
        $param->table = 'daily';
        $param->timeafter = strtotime("-" . $time * 7 . " days", stats_get_base_daily());
    } elseif ($time < 20) {
        // weeklies
        // number of weeks to go back = time - 10 * 4 (weeks) + base week
        $param->table = 'weekly';
        $param->timeafter = strtotime("-" . ($time - 10) * 4 . " weeks", stats_get_base_weekly());
    } else {
        // monthlies.
        // number of months to go back = time - 20 * months + base month
        $param->table = 'monthly';
        $param->timeafter = strtotime("-" . ($time - 20) . " months", stats_get_base_monthly());
    }
    $param->extras = '';
    // compatibility - if we're in postgres, cast to real for some reports.
    $real = '';
    if ($CFG->dbfamily == 'postgres') {
        $real = '::real';
    }
    switch ($report) {
        // ******************** STATS_MODE_GENERAL ******************** //
        case STATS_REPORT_LOGINS:
            $param->fields = 'timeend,sum(stat1) as line1,sum(stat2) as line2';
            $param->fieldscomplete = true;
            $param->stattype = 'logins';
            $param->line1 = get_string('statslogins');
            $param->line2 = get_string('statsuniquelogins');
            if ($courseid == SITEID) {
                $param->extras = 'GROUP BY timeend';
            }
            break;
        case STATS_REPORT_READS:
            $param->fields = sql_concat('timeend', 'roleid') . ' AS uniqueid, timeend, roleid, stat1 as line1';
            $param->fieldscomplete = true;
            // set this to true to avoid anything adding stuff to the list and breaking complex queries.
            $param->aggregategroupby = 'roleid';
            $param->stattype = 'activity';
            $param->crosstab = true;
            $param->extras = 'GROUP BY timeend,roleid,stat1';
            if ($courseid == SITEID) {
                $param->fields = sql_concat('timeend', 'roleid') . ' AS uniqueid, timeend, roleid, sum(stat1) as line1';
                $param->extras = 'GROUP BY timeend,roleid';
            }
            break;
        case STATS_REPORT_WRITES:
            $param->fields = sql_concat('timeend', 'roleid') . ' AS uniqueid, timeend, roleid, stat2 as line1';
            $param->fieldscomplete = true;
            // set this to true to avoid anything adding stuff to the list and breaking complex queries.
            $param->aggregategroupby = 'roleid';
            $param->stattype = 'activity';
            $param->crosstab = true;
            $param->extras = 'GROUP BY timeend,roleid,stat2';
            if ($courseid == SITEID) {
                $param->fields = sql_concat('timeend', 'roleid') . ' AS uniqueid, timeend, roleid, sum(stat2) as line1';
                $param->extras = 'GROUP BY timeend,roleid';
            }
            break;
        case STATS_REPORT_ACTIVITY:
            $param->fields = sql_concat('timeend', 'roleid') . ' AS uniqueid, timeend, roleid, sum(stat1+stat2) as line1';
            $param->fieldscomplete = true;
            // set this to true to avoid anything adding stuff to the list and breaking complex queries.
            $param->aggregategroupby = 'roleid';
            $param->stattype = 'activity';
            $param->crosstab = true;
            $param->extras = 'GROUP BY timeend,roleid';
            if ($courseid == SITEID) {
                $param->extras = 'GROUP BY timeend,roleid';
            }
            break;
        case STATS_REPORT_ACTIVITYBYROLE:
            $param->fields = 'stat1 AS line1, stat2 AS line2';
            $param->stattype = 'activity';
            $rolename = get_field('role', 'name', 'id', $roleid);
            $param->line1 = $rolename . get_string('statsreads');
            $param->line2 = $rolename . get_string('statswrites');
            if ($courseid == SITEID) {
                $param->extras = 'GROUP BY timeend';
            }
            break;
            // ******************** STATS_MODE_DETAILED ******************** //
        // ******************** STATS_MODE_DETAILED ******************** //
        case STATS_REPORT_USER_ACTIVITY:
            $param->fields = 'statsreads as line1, statswrites as line2';
            $param->line1 = get_string('statsuserreads');
            $param->line2 = get_string('statsuserwrites');
            $param->stattype = 'activity';
            break;
        case STATS_REPORT_USER_ALLACTIVITY:
            $param->fields = 'statsreads+statswrites as line1';
            $param->line1 = get_string('statsuseractivity');
            $param->stattype = 'activity';
            break;
        case STATS_REPORT_USER_LOGINS:
//.........这里部分代码省略.........

/**
 * Lists all roles that have the ability to backup user data, as well as users
 * @param bool $detailed
 * @return object result
 */
function report_security_check_riskbackup($detailed = false)
{
    global $CFG;
    $result = new object();
    $result->issue = 'report_security_check_riskbackup';
    $result->name = get_string('check_riskbackup_name', 'report_security');
    $result->info = null;
    $result->details = null;
    $result->status = null;
    $result->link = null;
    $syscontext = get_context_instance(CONTEXT_SYSTEM);
    $systemroles = get_records_sql("SELECT DISTINCT r.*\n           FROM {$CFG->prefix}role r\n           JOIN {$CFG->prefix}role_capabilities rc ON rc.roleid = r.id\n          WHERE rc.capability = 'moodle/backup:userinfo' AND rc.contextid = {$syscontext->id} AND rc.permission = " . CAP_ALLOW . "");
    $overriddenroles = get_records_sql("SELECT DISTINCT r.*, rc.contextid\n           FROM {$CFG->prefix}role r\n           JOIN {$CFG->prefix}role_capabilities rc ON rc.roleid = r.id\n          WHERE rc.capability = 'moodle/backup:userinfo' AND rc.contextid <> {$syscontext->id} AND rc.permission = " . CAP_ALLOW . "");
    // list of users that are able to backup personal info
    // note: "sc" is context where is role assigned,
    //       "c" is context where is role overriden or system context if in role definition
    $sqluserinfo = "\n        FROM (SELECT rcx.*\n                FROM {$CFG->prefix}role_capabilities rcx\n               WHERE rcx.permission = " . CAP_ALLOW . " AND rcx.capability = 'moodle/backup:userinfo') rc,\n             {$CFG->prefix}context c,\n             {$CFG->prefix}context sc,\n             {$CFG->prefix}role_assignments ra,\n             {$CFG->prefix}user u\n       WHERE c.id = rc.contextid\n             AND (sc.path = c.path OR sc.path LIKE " . sql_concat('c.path', "'/%'") . " OR c.path LIKE " . sql_concat('sc.path', "'/%'") . ")\n             AND u.id = ra.userid AND u.deleted = 0\n             AND ra.contextid = sc.id AND ra.roleid = rc.roleid\n             AND sc.contextlevel <= " . CONTEXT_COURSE . " AND c.contextlevel <= " . CONTEXT_COURSE . "";
    $usercount = count_records_sql("SELECT COUNT('x') FROM (SELECT DISTINCT u.id {$sqluserinfo}) userinfo");
    $systemrolecount = empty($systemroles) ? 0 : count($systemroles);
    $overriddenrolecount = empty($overriddenroles) ? 0 : count($overriddenroles);
    $result->status = REPORT_SECURITY_WARNING;
    // there is always at least one admin
    $a = (object) array('rolecount' => $systemrolecount, 'overridecount' => $overriddenrolecount, 'usercount' => $usercount);
    $result->info = get_string('check_riskbackup_warning', 'report_security', $a);
    if ($detailed) {
        $result->details = '';
        // Will be added to later
        // Make a list of roles
        if ($systemroles) {
            $links = array();
            foreach ($systemroles as $role) {
                $role->url = "{$CFG->wwwroot}/{$CFG->admin}/roles/manage.php?action=edit&amp;roleid={$role->id}";
                $links[] = '<li>' . get_string('check_riskbackup_editrole', 'report_security', $role) . '</li>';
            }
            $links = '<ul>' . implode($links) . '</ul>';
            $result->details .= get_string('check_riskbackup_details_systemroles', 'report_security', $links);
        }
        // Make a list of overrides to roles
        $rolelinks2 = array();
        if ($overriddenroles) {
            $links = array();
            foreach ($overriddenroles as $role) {
                $context = get_context_instance_by_id($role->contextid);
                if ($context->contextlevel == CONTEXT_COURSE) {
                    $role->name = role_get_name($role, $context);
                }
                $role->contextname = print_context_name($context);
                $role->url = "{$CFG->wwwroot}/{$CFG->admin}/roles/override.php?contextid={$role->contextid}&amp;roleid={$role->id}";
                $links[] = '<li>' . get_string('check_riskbackup_editoverride', 'report_security', $role) . '</li>';
            }
            $links = '<ul>' . implode($links) . '</ul>';
            $result->details .= get_string('check_riskbackup_details_overriddenroles', 'report_security', $links);
        }
        // Get a list of affected users as well
        $rs = get_recordset_sql("SELECT DISTINCT u.id, u.firstname, u.lastname, u.picture, u.imagealt, u.email, ra.contextid, ra.roleid\n            {$sqluserinfo} ORDER BY u.lastname, u.firstname");
        $users = array();
        while ($user = rs_fetch_next_record($rs)) {
            $context = get_context_instance_by_id($user->contextid);
            $url = "{$CFG->wwwroot}/{$CFG->admin}/roles/assign.php?contextid={$user->contextid}&amp;roleid={$user->roleid}";
            $a = (object) array('fullname' => fullname($user), 'url' => $url, 'email' => $user->email, 'contextname' => print_context_name($context));
            $users[] = '<li>' . get_string('check_riskbackup_unassign', 'report_security', $a) . '</li>';
        }
        rs_close($rs);
        if (!empty($users)) {
            $users = '<ul>' . implode($users) . '</ul>';
            $result->details .= get_string('check_riskbackup_details_users', 'report_security', $users);
        }
    }
    return $result;
}

//.........这里部分代码省略.........
                 $colnum = 0;
                 foreach ($headers as $item) {
                     $myxls->write(0, $colnum, $item, $formatbc);
                     $colnum++;
                 }
                 $rownum = 1;
             } else {
                 if ($download == 'CSV') {
                     $filename .= ".txt";
                     header("Content-Type: application/download\n");
                     header("Content-Disposition: attachment; filename=\"{$filename}\"");
                     header("Expires: 0");
                     header("Cache-Control: must-revalidate,post-check=0,pre-check=0");
                     header("Pragma: public");
                     $headers = get_string('fullname') . "\t" . get_string('startedon', 'game') . "\t" . get_string('timecompleted', 'game') . "\t" . get_string('attemptduration', 'game');
                     if ($game->grade) {
                         $headers .= "\t" . get_string('grade', 'game') . "/" . $game->grade;
                     }
                     if ($detailedmarks) {
                         foreach ($questionids as $id) {
                             $headers .= "\t#" . $questions[$id]->number;
                         }
                     }
                     if ($hasfeedback) {
                         $headers .= "\t" . get_string('feedback', 'game');
                     }
                     echo $headers . " \n";
                 }
             }
         }
     }
     $contextlists = get_related_contexts_string(get_context_instance(CONTEXT_COURSE, $course->id));
     // Construct the SQL.
     $select = 'SELECT qa.id,' . sql_concat('u.id', '\'#\'', $db->IfNull('qa.attempt', '0')) . ' AS uniqueid, ' . 'qa.id as attemptuniqueid, qa.id AS attempt, u.id AS userid, u.firstname, u.lastname, u.picture, ' . 'qa.score, qa.timefinish, qa.timestart, qa.timefinish - qa.timestart AS duration ';
     if ($course->id != SITEID) {
         // This is too complicated, so just do it for each of the four cases.
         if (!empty($currentgroup) && empty($noattempts)) {
             // We want a particular group and we only want to see students WITH attempts.
             // So join on groups_members and do an inner join on attempts.
             $from = 'FROM {user} u JOIN {role_assignments} ra ON ra.userid = u.id ' . groups_members_join_sql() . 'JOIN {game_attempts} qa ON u.id = qa.userid AND qa.gameid = ' . $game->id;
             $where = ' WHERE ra.contextid ' . $contextlists . ' AND ' . groups_members_where_sql($currentgroup) . ' AND qa.preview = 0';
         } else {
             if (!empty($currentgroup) && !empty($noattempts)) {
                 // We want a particular group and we want to do something funky with attempts.
                 // So join on groups_members and left join on attempts...
                 $from = 'FROM {user} u JOIN {role_assignments} ra ON ra.userid = u.id ' . groups_members_join_sql() . 'LEFT JOIN {game_attempts} qa ON u.id = qa.userid AND qa.gameid = ' . $game->id;
                 $where = ' WHERE ra.contextid ' . $contextlists . ' AND ' . groups_members_where_sql($currentgroup);
                 if ($noattempts == 1) {
                     // Noattempts = 1 means only no attempts, so make the left join ask.
                     // For only records where the right is null (no attempts).
                     $where .= ' AND qa.userid IS NULL';
                     // Show ONLY no attempts.
                 } else {
                     // We are including attempts, so exclude previews.
                     $where .= ' AND qa.preview = 0';
                 }
             } else {
                 if (empty($currentgroup)) {
                     // We don't care about group, and we to do something funky with attempts.
                     // So do a left join on attempts.
                     $from = 'FROM {user} u JOIN {role_assignments} ra ON ra.userid = u.id ' . ' LEFT JOIN {game_attempts} qa ON u.id = qa.userid AND qa.gameid = ' . $game->id;
                     $where = " WHERE ra.contextid {$contextlists}";
                     if (empty($noattempts)) {
                         // Show ONLY students with attempts.
                         $where .= ' AND qa.userid IS NOT NULL AND qa.preview = 0';
                     } else {

 function exists()
 {
     global $CFG;
     $positionexpr = sql_position(sql_concat("','", "q.id", "','"), sql_concat("','", "qma.sequence", "','"));
     return record_exists_sql("\n                SELECT * FROM {$CFG->prefix}question q\n                    JOIN {$CFG->prefix}question_multianswer qma ON {$positionexpr} > 0\n                WHERE qma.question <> q.parent") || record_exists_sql("\n                SELECT * FROM {$CFG->prefix}question q\n                    JOIN {$CFG->prefix}question parent_q ON parent_q.id = q.parent\n                WHERE q.category <> parent_q.category");
 }

 function count_users_avail($namesearch = '', $alpha = '')
 {
     global $CFG, $CURMAN;
     $LIKE = $CURMAN->db->sql_compare();
     $FULLNAME = sql_concat('usr.firstname', "' '", 'usr.lastname');
     $select = 'SELECT COUNT(usr.id) ';
     $tables = 'FROM ' . $CURMAN->db->prefix_table(USRTABLE) . ' usr ';
     $join = 'LEFT JOIN ' . $CURMAN->db->prefix_table(INSTABLE) . ' ins ';
     $on = 'ON ins.userid = usr.id ';
     /// If limiting returns to specific teams, set that up now.
     if (!empty($CFG->curr_configteams)) {
         $where = 'usr.team IN (' . $CFG->curr_configteams . ') ';
     } else {
         $where = '';
     }
     if (!empty($namesearch)) {
         $namesearch = trim($namesearch);
         $where .= (!empty($where) ? ' AND ' : '') . "({$FULLNAME} {$LIKE} '%{$namesearch}%') ";
     }
     if ($alpha) {
         $where .= (!empty($where) ? ' AND ' : '') . "({$FULLNAME} {$LIKE} '{$alpha}%') ";
     }
     /*
             switch ($type) {
                 case 'student':
                     $where .= (!empty($where) ? ' AND ' : '') . 'usr.type = \'Student\' ';
                     break;
     
                 case 'instructor':
                     $where .= (!empty($where) ? ' AND ' : '') . 'usr.type = \'Instructor\' ';
                     break;
     
                 case '':
                     $where .= (!empty($where) ? ' AND ' : '') . '(usr.type = \'Student\' OR usr.type = \'Instructor\') ';
                     break;
             }
     */
     $uids = array();
     $stu = new student();
     if ($users = $stu->get_students()) {
         foreach ($users as $user) {
             $uids[] = $user->id;
         }
     }
     if ($users = $this->get_instructors()) {
         foreach ($users as $user) {
             $uids[] = $user->id;
         }
     }
     if (!empty($uids)) {
         $where .= (!empty($where) ? ' AND ' : '') . 'usr.id NOT IN ( ' . implode(', ', $uids) . ' ) ';
     }
     if (!empty($where)) {
         $where = 'WHERE ' . $where . ' ';
     }
     $sql = $select . $tables . $join . $on . $where;
     return $CURMAN->db->count_records_sql($sql);
 }

 function count_users_enrolled($type = '', $namesearch = '', $alpha = '')
 {
     global $CFG, $CURMAN;
     $LIKE = $CURMAN->db->sql_compare();
     $FULLNAME = sql_concat('usr.firstname', "' '", 'usr.lastname');
     $select = 'SELECT COUNT(usr.id) ';
     $tables = 'FROM ' . $CURMAN->db->prefix_table(USRTABLE) . ' usr ';
     $join = 'LEFT JOIN ' . $CURMAN->db->prefix_table(STUTABLE) . ' stu ';
     $on = 'ON stu.userid = usr.id ';
     /// If limiting returns to specific teams, set that up now.
     if (!empty($CFG->curr_configteams)) {
         $where = 'usr.team IN (' . $CFG->curr_configteams . ') ';
     } else {
         $where = '';
     }
     if (!empty($namesearch)) {
         $namesearch = trim($namesearch);
         $where .= (!empty($where) ? ' AND ' : '') . "({$FULLNAME} {$LIKE} '%{$namesearch}%') OR " . "(usr.idnumber {$LIKE} '%{$namesearch}%') ";
     }
     if ($alpha) {
         $where .= (!empty($where) ? ' AND ' : '') . "({$FULLNAME} {$LIKE} '{$alpha}%') ";
     }
     //        switch ($type) {
     //            case 'student':
     //                $where .= (!empty($where) ? ' AND ' : '') . 'usr.type = \'Student\' ';
     //                break;
     //
     //            case 'instructor':
     //                $where .= (!empty($where) ? ' AND ' : '') . 'usr.type = \'Instructor\' ';
     //                break;
     //
     //            case '':
     //                $where .= (!empty($where) ? ' AND ' : '') . '(usr.type = \'Student\' OR usr.type = \'Instructor\') ';
     //                break;
     //        }
     $where .= (!empty($where) ? ' AND ' : '') . "classid={$this->classid} ";
     $where = "WHERE {$where} ";
     $sql = $select . $tables . $join . $on . $where;
     return $CURMAN->db->count_records_sql($sql);
 }

 /**
  * Creates known user filter if present
  *
  * @uses $CURMAN
  * @uses $USER
  * @param string $fieldname
  * @param boolean $advanced
  * @return object filter
  */
 function get_field($fieldname, $advanced)
 {
     global $CURMAN, $USER;
     switch ($fieldname) {
         case 'username':
             return new user_filter_text('username', get_string('username'), $advanced, 'usr.username');
         case 'realname':
             return new cm_user_filter_text_OR('realname', get_string('fullname'), $advanced, 'fullname', array(sql_concat('usr.firstname', "' '", "COALESCE(usr.mi, '')", "' '", 'usr.lastname'), sql_concat('usr.firstname', "' '", 'usr.lastname')));
         case 'lastname':
             return new user_filter_text('lastname', get_string('lastname'), $advanced, 'usr.lastname');
         case 'firstname':
             return new user_filter_text('firstname', get_string('firstname'), $advanced, 'usr.firstname');
         case 'idnumber':
             return new user_filter_text('idnumber', get_string('idnumber'), $advanced, 'usr.idnumber');
         case 'email':
             return new user_filter_text('email', get_string('email'), $advanced, 'usr.email');
         case 'city':
             return new user_filter_text('city', get_string('city'), $advanced, 'usr.city');
         case 'country':
             return new user_filter_select('country', get_string('country'), $advanced, 'country', cm_get_list_of_countries(), $USER->country);
         case 'timecreated':
             return new user_filter_date('timecreated', get_string('createtime', 'block_curr_admin'), $advanced, 'usr.timecreated');
         case 'language':
             return new user_filter_select('language', get_string('preferredlanguage', 'block_curr_admin'), $advanced, 'usr.language', cm_get_list_of_languages());
         case 'clusterid':
             //obtain a mapping of cluster ids to names for all clusters
             $clusters = cm_get_list_of_clusters();
             //use a special filter class to filter users based on clusters
             return new cm_user_cluster_filter('clusterid', get_string('usercluster', 'block_curr_admin'), $advanced, 'usr.id', $clusters);
         case 'curriculumid':
             //obtain a mapping of curriculum ids to names for all curricula
             $choices = curriculum_get_menu();
             //use a special filter class to filter users based on curricula
             return new cm_user_curriculum_filter('curriculumid', get_string('usercurricula', 'block_curr_admin'), $advanced, 'usr.id', $choices);
         case 'inactive':
             $inactive_options = array(get_string('o_active', 'block_curr_admin'), get_string('all'), get_string('o_inactive', 'block_curr_admin'));
             return new cm_show_inactive_filter('inactive', get_string('showinactive', 'block_curr_admin'), $advanced, 'usr.inactive', $inactive_options);
         default:
             if (strncmp($fieldname, 'field_', 6) === 0) {
                 $f = substr($fieldname, 6);
                 $rec = new field($CURMAN->db->get_record(FIELDTABLE, 'shortname', $f));
                 return new cm_custom_field_filter($fieldname, $rec->shortname, $advanced, $rec);
             }
             return null;
     }
 }

 /**
  * Get a list of the available students curriculum.
  *
  * @uses $CURMAN
  * @param string $search A search filter.
  * @return array An array of user records.
  */
 public static function curriculumstudent_get_students($curid = 0, $enroled = true)
 {
     global $CURMAN;
     if (0 >= $curid) {
         $curid = $CURMAN->id;
     }
     if (empty($CURMAN->db)) {
         return NULL;
     }
     $LIKE = $CURMAN->db->sql_compare();
     $FULLNAME = sql_concat('usr.firstname', "' '", 'usr.lastname');
     $select = 'SELECT curass.id, usr.id as usrid, curass.curriculumid as curid, ' . $FULLNAME . ' as name, usr.idnumber, usr.country, usr.language, curass.timecreated, curass.userid ';
     $tables = 'FROM ' . $CURMAN->db->prefix_table(USRTABLE) . ' usr ';
     $join = 'LEFT JOIN ' . $CURMAN->db->prefix_table(CURASSTABLE) . ' curass ON curass.userid = usr.id ';
     $sort = 'ORDER BY usr.idnumber ASC ';
     $limit = '';
     if ($enroled) {
         $where = 'WHERE curass.curriculumid = ' . $curid . ' ';
     } else {
         $join .= 'LEFT JOIN ' . $CURMAN->db->prefix_table(CURASSTABLE) . ' curass2 ON curass2.userid = usr.id AND curass2.curriculumid = ' . $curid . ' ';
         $where = 'WHERE curass2.curriculumid IS NULL ';
     }
     $sql = $select . $tables . $join . $where . $sort . $limit;
     return $CURMAN->db->get_records_sql($sql);
 }

 /**
  * Returns an array of cluster ids that are children of the supplied cluster and
  * the current user has access to enrol users into
  *
  * @param   int        $clusterid  The cluster whose children we care about
  * @return  int array              The array of accessible cluster ids
  */
 public static function get_allowed_clusters($clusterid)
 {
     global $USER, $CURMAN;
     $context = cm_context_set::for_user_with_capability('cluster', 'block/curr_admin:cluster:enrol_cluster_user', $USER->id);
     $allowed_clusters = array();
     //get the clusters and check the context against them
     $cluster_context_level = context_level_base::get_custom_context_level('cluster', 'block_curr_admin');
     $cluster_context_instance = get_context_instance($cluster_context_level, $clusterid);
     $path = sql_concat('ctxt.path', "'/%'");
     $like = sql_ilike();
     //query to get sub-cluster contexts
     $cluster_permissions_sql = "SELECT clst.* FROM\n                                    {$CURMAN->db->prefix_table(CLSTTABLE)} clst\n                                    JOIN {$CURMAN->db->prefix_table('context')} ctxt\n                                    ON clst.id = ctxt.instanceid\n                                    AND ctxt.contextlevel = {$cluster_context_level}\n                                    AND '{$cluster_context_instance->path}' {$like} {$path}";
     if ($records = get_records_sql($cluster_permissions_sql)) {
         //filter the records based on what contexts have the cluster:enrol_cluster_user capability
         $allowed_clusters = $context->get_allowed_instances($records, 'cluster', 'id');
     }
     return $allowed_clusters;
 }

                if (!empty($CFG->hotpot_showtimes)) {
                    $msg .= ' (' . format_time(sprintf("%0.2f", microtime_diff($hotpotstart, microtime()))) . ')';
                }
                notify($msg);
            }
            notify(get_string('regradecomplete', 'quiz'));
        }
        // end if $confirm
    }
    // end regrade
    // get duplicate hotpot-name questions
    //  - JMatch LHS is longer than 255 bytes
    //  - JQuiz question text is longer than 255 bytes
    //  - other unidentified situations ?!
    $regrade_hotpots = array();
    $concat_field = sql_concat('hotpot', "'_'", 'name');
    if ($concat_field) {
        $records = get_records_sql("\n                SELECT {$concat_field}, COUNT(*), hotpot, name\n                FROM {$CFG->prefix}hotpot_questions\n                WHERE hotpot IN ({$hotpotids})\n                GROUP BY hotpot, name\n                HAVING COUNT(*) >1\n            ");
        if ($records) {
            foreach ($records as $record) {
                $regrade_hotpots[$record->hotpot] = 1;
            }
            ksort($regrade_hotpots);
            $regrade_hotpots = array_keys($regrade_hotpots);
        }
    }
}
// start timer
$start = microtime();
// get total number of attempts, users and details for these hotpots
$tables = "{$CFG->prefix}hotpot_attempts a";

/**
* helper function to return all the filters for learning path classifications
*
* @param boolean $count whether to get the count for each classification or not
* @param int $courseid if given, will just return the values for a given course.
* @param int $status if given, will just return the values courses at the given status.
* @param int $category if given, will just return the values courses in the given category.
*
* @return mixed. if !$count, just return the array of results.
*               if count, will be a standard class with allvalues, filtercounts and secondcounts variables.
*/
function tao_get_classifications($count = true, $courseid = null, $status = null, $category = null)
{
    global $CFG;
    $return = new StdClass();
    $sql = '
        SELECT cv.id, ct.id AS typeid, ct.type, ct.name, cv.value
        FROM ' . $CFG->prefix . 'classification_type ct
        JOIN ' . $CFG->prefix . 'classification_value cv ON cv.type = ct.id
    ' . (!$courseid ? 'LEFT' : '') . ' JOIN ' . $CFG->prefix . 'course_classification cc ON cc.value = cv.id
    ' . ($courseid ? ' WHERE cc.course = ' . $courseid : '') . '
        ORDER BY ct.type, cv.value
    ';
    $return->allvalues = get_records_sql($sql);
    if (empty($count)) {
        return $return->allvalues;
    }
    $countsql = '
        SELECT cc.value, COUNT(cc.id)
        FROM ' . $CFG->prefix . 'course_classification cc
        JOIN ' . $CFG->prefix . 'classification_value cv ON cv.id = cc.value
        JOIN ' . $CFG->prefix . 'classification_type ct ON cv.type = ct.id
        JOIN ' . $CFG->prefix . 'course c ON c.id = cc.course
        WHERE ct.type = \'filter\'
    ' . ($courseid ? ' AND cc.course = ' . $courseid : '') . '
    ' . ($category ? ' AND c.category = ' . $category : '') . '
    ' . ($status ? ' AND c.approval_status_id = ' . $status : '') . '
        GROUP BY cc.value
    ';
    if (!($return->filtercounts = get_records_sql($countsql))) {
        $return->filtercounts = array();
    }
    $concat = sql_concat('cc1.value', "'|'", 'cc2.value');
    $countsql = '
        SELECT ' . $concat . ' AS id , COUNT(cc2.id) AS count
        FROM ' . $CFG->prefix . 'course_classification cc1
        JOIN ' . $CFG->prefix . 'course_classification cc2 ON cc1.course = cc2.course
        JOIN ' . $CFG->prefix . 'classification_value cv1 ON cv1.id = cc1.value
        JOIN ' . $CFG->prefix . 'classification_type ct1 ON cv1.type = ct1.id
        JOIN ' . $CFG->prefix . 'classification_value cv2 ON cv2.id = cc2.value
        JOIN ' . $CFG->prefix . 'classification_type ct2 ON cv2.type = ct2.id
        JOIN ' . $CFG->prefix . 'course c ON c.id = cc2.course
        WHERE ct1.type = \'topcategory\' AND ct2.type = \'secondcategory\'
    ' . ($courseid ? ' AND cc.course = ' . $courseid : '') . '
    ' . ($category ? ' AND c.category = ' . $category : '') . '
    ' . ($status ? ' AND c.approval_status_id = ' . $status : '') . '
        GROUP BY ' . $concat;
    if (!($return->secondcounts = get_records_sql($countsql))) {
        $return->secondcounts = array();
    }
    return $return;
}

function print_log_ods($course, $user, $date, $order = 'l.time DESC', $modname, $modid, $modaction, $groupid)
{
    global $CFG;
    require_once "{$CFG->libdir}/odslib.class.php";
    if (!($logs = build_logs_array($course, $user, $date, $order, '', '', $modname, $modid, $modaction, $groupid))) {
        return false;
    }
    $courses = array();
    if ($course->id == SITEID) {
        $courses[0] = '';
        if ($ccc = get_courses('all', 'c.id ASC', 'c.id,c.shortname')) {
            foreach ($ccc as $cc) {
                $courses[$cc->id] = $cc->shortname;
            }
        }
    } else {
        $courses[$course->id] = $course->shortname;
    }
    $count = 0;
    $ldcache = array();
    $tt = getdate(time());
    $today = mktime(0, 0, 0, $tt["mon"], $tt["mday"], $tt["year"]);
    $strftimedatetime = get_string("strftimedatetime");
    $nroPages = ceil(count($logs) / (EXCELROWS - FIRSTUSEDEXCELROW + 1));
    $filename = 'logs_' . userdate(time(), get_string('backupnameformat'), 99, false);
    $filename .= '.ods';
    $workbook = new MoodleODSWorkbook('-');
    $workbook->send($filename);
    $worksheet = array();
    $headers = array(get_string('course'), get_string('time'), get_string('ip_address'), get_string('fullname'), get_string('action'), get_string('info'));
    // Creating worksheets
    for ($wsnumber = 1; $wsnumber <= $nroPages; $wsnumber++) {
        $sheettitle = get_string('logs') . ' ' . $wsnumber . '-' . $nroPages;
        $worksheet[$wsnumber] =& $workbook->add_worksheet($sheettitle);
        $worksheet[$wsnumber]->set_column(1, 1, 30);
        $worksheet[$wsnumber]->write_string(0, 0, get_string('savedat') . userdate(time(), $strftimedatetime));
        $col = 0;
        foreach ($headers as $item) {
            $worksheet[$wsnumber]->write(FIRSTUSEDEXCELROW - 1, $col, $item, '');
            $col++;
        }
    }
    if (empty($logs['logs'])) {
        $workbook->close();
        return true;
    }
    $formatDate =& $workbook->add_format();
    $formatDate->set_num_format(get_string('log_excel_date_format'));
    $row = FIRSTUSEDEXCELROW;
    $wsnumber = 1;
    $myxls =& $worksheet[$wsnumber];
    foreach ($logs['logs'] as $log) {
        if (isset($ldcache[$log->module][$log->action])) {
            $ld = $ldcache[$log->module][$log->action];
        } else {
            $ld = get_record('log_display', 'module', $log->module, 'action', $log->action);
            $ldcache[$log->module][$log->action] = $ld;
        }
        if ($ld && !empty($log->info)) {
            // ugly hack to make sure fullname is shown correctly
            if ($ld->mtable == 'user' and $ld->field == sql_concat('firstname', "' '", 'lastname')) {
                $log->info = fullname(get_record($ld->mtable, 'id', $log->info), true);
            } else {
                $log->info = get_field($ld->mtable, $ld->field, 'id', $log->info);
            }
        }
        // Filter log->info
        $log->info = format_string($log->info);
        $log->info = strip_tags(urldecode($log->info));
        // Some XSS protection
        if ($nroPages > 1) {
            if ($row > EXCELROWS) {
                $wsnumber++;
                $myxls =& $worksheet[$wsnumber];
                $row = FIRSTUSEDEXCELROW;
            }
        }
        $myxls->write_string($row, 0, $courses[$log->course]);
        $myxls->write_date($row, 1, $log->time);
        $myxls->write_string($row, 2, $log->ip);
        $fullname = fullname($log, has_capability('moodle/site:viewfullnames', get_context_instance(CONTEXT_COURSE, $course->id)));
        $myxls->write_string($row, 3, $fullname);
        $myxls->write_string($row, 4, $log->module . ' ' . $log->action);
        $myxls->write_string($row, 5, $log->info);
        $row++;
    }
    $workbook->close();
    return true;
}

/**
 * Returns a sorted list of categories. Each category object has a context
 * property that is a context object.
 *
 * When asking for $parent='none' it will return all the categories, regardless
 * of depth. Wheen asking for a specific parent, the default is to return
 * a "shallow" resultset. Pass false to $shallow and it will return all
 * the child categories as well.
 *
 *
 * @param string $parent The parent category if any
 * @param string $sort the sortorder
 * @param bool   $shallow - set to false to get the children too
 * @return array of categories
 */
function get_categories($parent = 'none', $sort = NULL, $shallow = true)
{
    global $CFG;
    if ($sort === NULL) {
        $sort = 'ORDER BY cc.sortorder ASC';
    } elseif ($sort === '') {
        // leave it as empty
    } else {
        $sort = "ORDER BY {$sort}";
    }
    if ($parent === 'none') {
        $sql = "SELECT cc.*,\n                      ctx.id AS ctxid, ctx.path AS ctxpath,\n                      ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel\n                FROM {$CFG->prefix}course_categories cc\n                JOIN {$CFG->prefix}context ctx\n                  ON cc.id=ctx.instanceid AND ctx.contextlevel=" . CONTEXT_COURSECAT . "\n                {$sort}";
    } elseif ($shallow) {
        $parent = (int) $parent;
        $sql = "SELECT cc.*,\n                       ctx.id AS ctxid, ctx.path AS ctxpath,\n                       ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel\n                FROM {$CFG->prefix}course_categories cc\n                JOIN {$CFG->prefix}context ctx\n                  ON cc.id=ctx.instanceid AND ctx.contextlevel=" . CONTEXT_COURSECAT . "\n                WHERE cc.parent={$parent}\n                {$sort}";
    } else {
        $parent = (int) $parent;
        $sql = "SELECT cc.*,\n                       ctx.id AS ctxid, ctx.path AS ctxpath,\n                       ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel\n                FROM {$CFG->prefix}course_categories cc\n                JOIN {$CFG->prefix}context ctx\n                  ON cc.id=ctx.instanceid AND ctx.contextlevel=" . CONTEXT_COURSECAT . "\n                JOIN {$CFG->prefix}course_categories ccp\n                     ON (cc.path LIKE " . sql_concat('ccp.path', "'%'") . ")\n                WHERE ccp.id={$parent}\n                {$sort}";
    }
    $categories = array();
    if ($rs = get_recordset_sql($sql)) {
        while ($cat = rs_fetch_next_record($rs)) {
            $cat = make_context_subobj($cat);
            if ($cat->visible || has_capability('moodle/category:visibility', $cat->context)) {
                $categories[$cat->id] = $cat;
            }
        }
    }
    return $categories;
}