public function print_grades()
{
global $CFG;
$export_tracking = $this->track_exports();
$strgrades = get_string('grades');
$profilefields = grade_helper::get_user_profile_fields($this->course->id, $this->usercustomfields);
$shortname = format_string($this->course->shortname, true, array('context' => context_course::instance($this->course->id)));
$downloadfilename = clean_filename("{$shortname} {$strgrades}");
$csvexport = new csv_export_writer($this->separator);
$csvexport->set_filename($downloadfilename);
// Print names of all the fields
$exporttitle = array();
foreach ($profilefields as $field) {
$exporttitle[] = $field->fullname;
}
if (!$this->onlyactive) {
$exporttitle[] = get_string("suspended");
}
// Add a feedback column.
foreach ($this->columns as $grade_item) {
$exporttitle[] = $this->format_column_name($grade_item);
if ($this->export_feedback) {
$exporttitle[] = $this->format_column_name($grade_item, true);
}
}
$csvexport->add_data($exporttitle);
// Print all the lines of data.
$geub = new grade_export_update_buffer();
$gui = new graded_users_iterator($this->course, $this->columns, $this->groupid);
$gui->require_active_enrolment($this->onlyactive);
$gui->allow_user_custom_fields($this->usercustomfields);
$gui->init();
while ($userdata = $gui->next_user()) {
$exportdata = array();
$user = $userdata->user;
foreach ($profilefields as $field) {
$fieldvalue = grade_helper::get_user_field_value($user, $field);
$exportdata[] = $fieldvalue;
}
if (!$this->onlyactive) {
$issuspended = $user->suspendedenrolment ? get_string('yes') : '';
$exportdata[] = $issuspended;
}
foreach ($userdata->grades as $itemid => $grade) {
if ($export_tracking) {
$status = $geub->track($grade);
}
$exportdata[] = $this->format_grade($grade);
if ($this->export_feedback) {
$exportdata[] = $this->format_feedback($userdata->feedbacks[$itemid]);
}
}
$csvexport->add_data($exportdata);
}
$gui->close();
$geub->close();
$csvexport->download_file();
exit;
}
/**
* To be implemented by child classes
*/
public function print_grades()
{
global $CFG;
require_once $CFG->dirroot . '/lib/excellib.class.php';
$export_tracking = $this->track_exports();
$strgrades = get_string('grades');
// Calculate file name
$shortname = format_string($this->course->shortname, true, array('context' => context_course::instance($this->course->id)));
$downloadfilename = clean_filename("{$shortname} {$strgrades}.xls");
// Creating a workbook
$workbook = new MoodleExcelWorkbook("-");
// Sending HTTP headers
$workbook->send($downloadfilename);
// Adding the worksheet
$myxls = $workbook->add_worksheet($strgrades);
// Print names of all the fields
$profilefields = grade_helper::get_user_profile_fields($this->course->id, $this->usercustomfields);
foreach ($profilefields as $id => $field) {
$myxls->write_string(0, $id, $field->fullname);
}
$pos = count($profilefields);
if (!$this->onlyactive) {
$myxls->write_string(0, $pos++, get_string("suspended"));
}
foreach ($this->columns as $grade_item) {
$myxls->write_string(0, $pos++, $this->format_column_name($grade_item));
// Add a column_feedback column
if ($this->export_feedback) {
$myxls->write_string(0, $pos++, $this->format_column_name($grade_item, true));
}
}
// Print all the lines of data.
$i = 0;
$geub = new grade_export_update_buffer();
$gui = new graded_users_iterator($this->course, $this->columns, $this->groupid);
$gui->require_active_enrolment($this->onlyactive);
$gui->allow_user_custom_fields($this->usercustomfields);
$gui->init();
while ($userdata = $gui->next_user()) {
$i++;
$user = $userdata->user;
foreach ($profilefields as $id => $field) {
$fieldvalue = grade_helper::get_user_field_value($user, $field);
$myxls->write_string($i, $id, $fieldvalue);
}
$j = count($profilefields);
if (!$this->onlyactive) {
$issuspended = $user->suspendedenrolment ? get_string('yes') : '';
$myxls->write_string($i, $j++, $issuspended);
}
foreach ($userdata->grades as $itemid => $grade) {
if ($export_tracking) {
$status = $geub->track($grade);
}
$gradestr = $this->format_grade($grade);
if (is_numeric($gradestr)) {
$myxls->write_number($i, $j++, $gradestr);
} else {
$myxls->write_string($i, $j++, $gradestr);
}
// writing feedback if requested
if ($this->export_feedback) {
$myxls->write_string($i, $j++, $this->format_feedback($userdata->feedbacks[$itemid]));
}
}
}
$gui->close();
$geub->close();
/// Close the workbook
$workbook->close();
exit;
}
/**
* Initialise the iterator
*
* @return boolean success
*/
public function init()
{
global $CFG, $DB;
$this->close();
export_verify_grades($this->course->id);
$course_item = grade_item::fetch_course_item($this->course->id);
if ($course_item->needsupdate) {
// Can not calculate all final grades - sorry.
return false;
}
$coursecontext = context_course::instance($this->course->id);
list($relatedctxsql, $relatedctxparams) = $DB->get_in_or_equal($coursecontext->get_parent_context_ids(true), SQL_PARAMS_NAMED, 'relatedctx');
list($gradebookroles_sql, $params) = $DB->get_in_or_equal(explode(',', $CFG->gradebookroles), SQL_PARAMS_NAMED, 'grbr');
list($enrolledsql, $enrolledparams) = get_enrolled_sql($coursecontext, '', 0, $this->onlyactive);
$params = array_merge($params, $enrolledparams, $relatedctxparams);
if ($this->groupid) {
$groupsql = "INNER JOIN {groups_members} gm ON gm.userid = u.id";
$groupwheresql = "AND gm.groupid = :groupid";
// $params contents: gradebookroles
$params['groupid'] = $this->groupid;
} else {
$groupsql = "";
$groupwheresql = "";
}
if (empty($this->sortfield1)) {
// We must do some sorting even if not specified.
$ofields = ", u.id AS usrt";
$order = "usrt ASC";
} else {
$ofields = ", u.{$this->sortfield1} AS usrt1";
$order = "usrt1 {$this->sortorder1}";
if (!empty($this->sortfield2)) {
$ofields .= ", u.{$this->sortfield2} AS usrt2";
$order .= ", usrt2 {$this->sortorder2}";
}
if ($this->sortfield1 != 'id' and $this->sortfield2 != 'id') {
// User order MUST be the same in both queries,
// must include the only unique user->id if not already present.
$ofields .= ", u.id AS usrt";
$order .= ", usrt ASC";
}
}
$userfields = 'u.*';
$customfieldssql = '';
if ($this->allowusercustomfields && !empty($CFG->grade_export_customprofilefields)) {
$customfieldscount = 0;
$customfieldsarray = grade_helper::get_user_profile_fields($this->course->id, $this->allowusercustomfields);
foreach ($customfieldsarray as $field) {
if (!empty($field->customid)) {
$customfieldssql .= "\n LEFT JOIN (SELECT * FROM {user_info_data}\n WHERE fieldid = :cf{$customfieldscount}) cf{$customfieldscount}\n ON u.id = cf{$customfieldscount}.userid";
$userfields .= ", cf{$customfieldscount}.data AS customfield_{$field->shortname}";
$params['cf' . $customfieldscount] = $field->customid;
$customfieldscount++;
}
}
}
$users_sql = "SELECT {$userfields} {$ofields}\n FROM {user} u\n JOIN ({$enrolledsql}) je ON je.id = u.id\n {$groupsql} {$customfieldssql}\n JOIN (\n SELECT DISTINCT ra.userid\n FROM {role_assignments} ra\n WHERE ra.roleid {$gradebookroles_sql}\n AND ra.contextid {$relatedctxsql}\n ) rainner ON rainner.userid = u.id\n WHERE u.deleted = 0\n {$groupwheresql}\n ORDER BY {$order}";
$this->users_rs = $DB->get_recordset_sql($users_sql, $params);
if (!$this->onlyactive) {
$context = context_course::instance($this->course->id);
$this->suspendedusers = get_suspended_userids($context);
} else {
$this->suspendedusers = array();
}
if (!empty($this->grade_items)) {
$itemids = array_keys($this->grade_items);
list($itemidsql, $grades_params) = $DB->get_in_or_equal($itemids, SQL_PARAMS_NAMED, 'items');
$params = array_merge($params, $grades_params);
$grades_sql = "SELECT g.* {$ofields}\n FROM {grade_grades} g\n JOIN {user} u ON g.userid = u.id\n JOIN ({$enrolledsql}) je ON je.id = u.id\n {$groupsql}\n JOIN (\n SELECT DISTINCT ra.userid\n FROM {role_assignments} ra\n WHERE ra.roleid {$gradebookroles_sql}\n AND ra.contextid {$relatedctxsql}\n ) rainner ON rainner.userid = u.id\n WHERE u.deleted = 0\n AND g.itemid {$itemidsql}\n {$groupwheresql}\n ORDER BY {$order}, g.itemid ASC";
$this->grades_rs = $DB->get_recordset_sql($grades_sql, $params);
} else {
$this->grades_rs = false;
}
return true;
}
/**
* Prints preview of exported grades on screen as a feedback mechanism
* @param bool $require_user_idnumber true means skip users without idnumber
* @deprecated since 2.8 MDL-46548. Previews are not useful on export.
*/
public function display_preview($require_user_idnumber = false)
{
global $OUTPUT;
debugging('function grade_export::display_preview is deprecated.', DEBUG_DEVELOPER);
$userprofilefields = grade_helper::get_user_profile_fields($this->course->id, $this->usercustomfields);
$formatoptions = new stdClass();
$formatoptions->para = false;
echo $OUTPUT->heading(get_string('previewrows', 'grades'));
echo '<table>';
echo '<tr>';
foreach ($userprofilefields as $field) {
echo '<th>' . $field->fullname . '</th>';
}
if (!$this->onlyactive) {
echo '<th>' . get_string("suspended") . "</th>";
}
foreach ($this->columns as $grade_item) {
echo '<th>' . $this->format_column_name($grade_item) . '</th>';
/// add a column_feedback column
if ($this->export_feedback) {
echo '<th>' . $this->format_column_name($grade_item, true) . '</th>';
}
}
echo '</tr>';
/// Print all the lines of data.
$i = 0;
$gui = new graded_users_iterator($this->course, $this->columns, $this->groupid);
$gui->require_active_enrolment($this->onlyactive);
$gui->allow_user_custom_fields($this->usercustomfields);
$gui->init();
while ($userdata = $gui->next_user()) {
// number of preview rows
if ($this->previewrows and $this->previewrows <= $i) {
break;
}
$user = $userdata->user;
if ($require_user_idnumber and empty($user->idnumber)) {
// some exports require user idnumber so we can match up students when importing the data
continue;
}
$gradeupdated = false;
// if no grade is update at all for this user, do not display this row
$rowstr = '';
foreach ($this->columns as $itemid => $unused) {
$gradetxt = $this->format_grade($userdata->grades[$itemid]);
// get the status of this grade, and put it through track to get the status
$g = new grade_export_update_buffer();
$grade_grade = new grade_grade(array('itemid' => $itemid, 'userid' => $user->id));
$status = $g->track($grade_grade);
if ($this->updatedgradesonly && ($status == 'nochange' || $status == 'unknown')) {
$rowstr .= '<td>' . get_string('unchangedgrade', 'grades') . '</td>';
} else {
$rowstr .= "<td>{$gradetxt}</td>";
$gradeupdated = true;
}
if ($this->export_feedback) {
$rowstr .= '<td>' . $this->format_feedback($userdata->feedbacks[$itemid]) . '</td>';
}
}
// if we are requesting updated grades only, we are not interested in this user at all
if (!$gradeupdated && $this->updatedgradesonly) {
continue;
}
echo '<tr>';
foreach ($userprofilefields as $field) {
$fieldvalue = grade_helper::get_user_field_value($user, $field);
// @see profile_field_base::display_data().
echo '<td>' . format_text($fieldvalue, FORMAT_MOODLE, $formatoptions) . '</td>';
}
if (!$this->onlyactive) {
$issuspended = $user->suspendedenrolment ? get_string('yes') : '';
echo "<td>{$issuspended}</td>";
}
echo $rowstr;
echo "</tr>";
$i++;
// increment the counter
}
echo '</table>';
$gui->close();
}
public function display_preview($require_user_idnumber = false)
{
global $OUTPUT;
$userprofilefields = grade_helper::get_user_profile_fields($this->course->id, $this->usercustomfields);
$formatoptions = new stdClass();
$formatoptions->para = false;
$exporttitle = array('Client Code', 'Client Name', 'Qualification Code', 'Unit Code', 'Unit Start Date', 'Unit End Date', 'Outcome Name', 'Outcome Code');
echo $OUTPUT->heading(get_string('previewrows', 'grades'));
echo '<table class="csv-table">';
echo '<tr>';
foreach ($exporttitle as $field) {
echo '<th>' . $field . '</th>';
}
if (!$this->onlyactive) {
echo '<th>' . get_string("suspended") . "</th>";
}
// foreach ($this->columns as $grade_item) {
// echo '<th>'.$this->format_column_name($grade_item).'</th>';
// /// add a column_feedback column
// if ($this->export_feedback) {
// echo '<th>'.$this->format_column_name($grade_item, true).'</th>';
// }
// }
echo '</tr>';
/// Print all the lines of data.
$i = 0;
$gui = new graded_users_iterator($this->course, $this->columns, $this->groupid);
$gui->require_active_enrolment($this->onlyactive);
$gui->allow_user_custom_fields($this->usercustomfields);
$gui->init();
while ($userdata = $gui->next_user()) {
// number of preview rows
if ($this->previewrows and $this->previewrows <= $i) {
break;
}
$user = $userdata->user;
if ($require_user_idnumber and empty($user->idnumber)) {
// some exports require user idnumber so we can match up students when importing the data
continue;
}
$gradeupdated = false;
// if no grade is update at all for this user, do not display this row
$rowstr = '';
foreach ($this->columns as $itemid => $grade_item) {
$gradetxt = $this->format_grade($userdata->grades[$itemid]);
$activity_start_date = $this->get_activity_start_date($this->course, $user, $grade_item);
$grade_modified = $userdata->grades[$itemid]->timemodified;
$client_code = $userdata->user->idnumber;
$client_name = $userdata->user->firstname . ' ' . $userdata->user->lastname;
// $course_code = $this->get_course_short_name($grade_item);
$course_code = $this->course->idnumber;
// $unit_code = $grade_item->itemname;
$unit_code = $grade_item->idnumber;
$unit_start = $activity_start_date ? $activity_start_date->format('d-m-Y') : '-';
$unit_end = $grade_modified ? userdate($grade_modified, '%d-%m-%Y') : '-';
$grade_name = $this->get_grade_name($gradetxt);
if ($grade_item->itemtype == 'category') {
$category_start_date = $this->get_category_start_date($this->course, $user, $grade_item);
$unit_start = $category_start_date ? $category_start_date->format('d-m-Y') : '-';
}
// get the status of this grade, and put it through track to get the status
$g = new grade_export_update_buffer();
$grade_grade = new grade_grade(array('itemid' => $itemid, 'userid' => $user->id));
$status = $g->track($grade_grade);
if ($this->updatedgradesonly && ($status == 'nochange' || $status == 'unknown')) {
$rowstr .= '<td>' . get_string('unchangedgrade', 'grades') . '</td>';
} else {
$rowstr .= "<tr>\n <td>{$client_code}</td>\n <td>{$client_name}</td>\n <td>{$course_code}</td>\n <td>{$unit_code}</td>\n <td>{$unit_start}</td>\n <td>{$unit_end}</td>\n <td>{$grade_name}</td>\n <td>{$gradetxt}</td>\n </tr>";
$gradeupdated = true;
}
if ($this->export_feedback) {
$rowstr .= '<td>' . $this->format_feedback($userdata->feedbacks[$itemid]) . '</td>';
}
}
// if we are requesting updated grades only, we are not interested in this user at all
if (!$gradeupdated && $this->updatedgradesonly) {
continue;
}
echo '<tr>';
echo $rowstr;
echo "</tr>";
$i++;
// increment the counter
}
echo '</table>';
$gui->close();
}
/**
* Initialise the iterator
*
* @return boolean success
*/
public function init() {
global $CFG, $DB;
$this->close();
grade_regrade_final_grades($this->course->id);
$course_item = grade_item::fetch_course_item($this->course->id);
if ($course_item->needsupdate) {
// can not calculate all final grades - sorry
return false;
}
$coursecontext = context_course::instance($this->course->id);
$relatedcontexts = get_related_contexts_string($coursecontext);
list($gradebookroles_sql, $params) =
$DB->get_in_or_equal(explode(',', $CFG->gradebookroles), SQL_PARAMS_NAMED, 'grbr');
list($enrolledsql, $enrolledparams) = get_enrolled_sql($coursecontext, '', 0, $this->onlyactive);
$params = array_merge($params, $enrolledparams);
if ($this->groupid) {
$groupsql = "INNER JOIN {groups_members} gm ON gm.userid = u.id";
$groupwheresql = "AND gm.groupid = :groupid";
// $params contents: gradebookroles
$params['groupid'] = $this->groupid;
} else {
$groupsql = "";
$groupwheresql = "";
}
if (empty($this->sortfield1)) {
// we must do some sorting even if not specified
$ofields = ", u.id AS usrt";
$order = "usrt ASC";
} else {
$ofields = ", u.$this->sortfield1 AS usrt1";
$order = "usrt1 $this->sortorder1";
if (!empty($this->sortfield2)) {
$ofields .= ", u.$this->sortfield2 AS usrt2";
$order .= ", usrt2 $this->sortorder2";
}
if ($this->sortfield1 != 'id' and $this->sortfield2 != 'id') {
// user order MUST be the same in both queries,
// must include the only unique user->id if not already present
$ofields .= ", u.id AS usrt";
$order .= ", usrt ASC";
}
}
$userfields = 'u.*';
$customfieldssql = '';
if ($this->allowusercustomfields && !empty($CFG->grade_export_customprofilefields)) {
$customfieldscount = 0;
$customfieldsarray = grade_helper::get_user_profile_fields($this->course->id, $this->allowusercustomfields);
foreach ($customfieldsarray as $field) {
if (!empty($field->customid)) {
$customfieldssql .= "
LEFT JOIN (SELECT * FROM {user_info_data}
WHERE fieldid = :cf$customfieldscount) cf$customfieldscount
ON u.id = cf$customfieldscount.userid";
$userfields .= ", cf$customfieldscount.data AS 'customfield_{$field->shortname}'";
$params['cf'.$customfieldscount] = $field->customid;
$customfieldscount++;
}
}
}
// $params contents: gradebookroles and groupid (for $groupwheresql)
$users_sql = "SELECT $userfields $ofields
FROM {user} u
JOIN ($enrolledsql) je ON je.id = u.id
$groupsql $customfieldssql
JOIN (
SELECT DISTINCT ra.userid
FROM {role_assignments} ra
WHERE ra.roleid $gradebookroles_sql
AND ra.contextid $relatedcontexts
) rainner ON rainner.userid = u.id
WHERE u.deleted = 0
$groupwheresql
ORDER BY $order";
$this->users_rs = $DB->get_recordset_sql($users_sql, $params);
if (!empty($this->grade_items)) {
$itemids = array_keys($this->grade_items);
list($itemidsql, $grades_params) = $DB->get_in_or_equal($itemids, SQL_PARAMS_NAMED, 'items');
$params = array_merge($params, $grades_params);
// $params contents: gradebookroles, enrolledparams, groupid (for $groupwheresql) and itemids
$grades_sql = "SELECT g.* $ofields
FROM {grade_grades} g
JOIN {user} u ON g.userid = u.id
JOIN ($enrolledsql) je ON je.id = u.id
$groupsql
JOIN (
SELECT DISTINCT ra.userid
FROM {role_assignments} ra
WHERE ra.roleid $gradebookroles_sql
AND ra.contextid $relatedcontexts
) rainner ON rainner.userid = u.id
WHERE u.deleted = 0
AND g.itemid $itemidsql
$groupwheresql
ORDER BY $order, g.itemid ASC";
$this->grades_rs = $DB->get_recordset_sql($grades_sql, $params);
} else {
$this->grades_rs = false;
}
return true;
}
public function print_grades()
{
global $CFG;
$exporttracking = $this->track_exports();
$course = $this->course;
$strgrades = get_string('grades');
$profilefields = grade_helper::get_user_profile_fields($this->course->id, $this->usercustomfields);
$courseid = $course->id;
$shortname = format_string($this->course->shortname, true, array('context' => context_course::instance($this->course->id)));
$downloadfilename = clean_filename("{$shortname} {$strgrades}");
$csvexport = new csv_export_writer($this->separator);
$csvexport->filename = clean_filename("{$downloadfilename}.csv");
// Print names of all the fields.
$exporttitle = array();
$shortname = format_string($this->course->shortname, true, array('context' => context_course::instance($this->course->id)));
$exporttitle[] = $shortname . "";
$csvexport->add_data($exporttitle);
$exporttitle = array();
$exporttitle[] = "TERM";
$exporttitle[] = "Class Number";
$exporttitle[] = "ID";
$exporttitle[] = "Final Grade";
$exporttitle[] = "" . get_string("firstname");
$exporttitle[] = "" . get_string("lastname");
$exporttitle[] = "" . get_string("idnumber");
$exporttitle[] = "" . get_string("institution");
$exporttitle[] = "" . get_string("department");
$exporttitle[] = "" . get_string("email");
foreach ($this->columns as $gradeitem) {
$exporttitle[] = trim($gradeitem->get_name());
}
$csvexport->add_data($exporttitle);
$sseat = $this->primcomp($courseid);
// Print all the lines of data.
$geub = new grade_export_update_buffer();
$gui = new graded_users_iterator($this->course, $this->columns, $this->groupid);
$gui->require_active_enrolment($this->onlyactive);
$gui->allow_user_custom_fields($this->usercustomfields);
$gui->init();
while ($userdata = $gui->next_user()) {
$exportdata = array();
$user = $userdata->user;
if (!isset($sseat[$user->username])) {
continue;
}
foreach ($userdata->grades as $itemid => $grade) {
if ($this->finalitemid == $itemid) {
$finalletter = $this->format_grade($userdata->grades[$itemid]);
if ($this->displaytype == 1) {
$userdata->grades[$itemid]->finalgrade = $finalletter;
}
}
}
$coarr = explode('.', $sseat[$user->username]);
$exportdata[] = $coarr[0];
$exportdata[] = $coarr[1];
$exportdata[] = $user->username;
$exportdata[] = $finalletter;
$exportdata[] = $user->firstname;
$exportdata[] = $user->lastname;
$exportdata[] = $user->idnumber;
$exportdata[] = $user->institution;
$exportdata[] = $user->department;
$exportdata[] = $user->email;
foreach ($userdata->grades as $itemid => $grade) {
$gradestr = $userdata->grades[$itemid]->finalgrade;
$exportdata[] = $gradestr;
}
$csvexport->add_data($exportdata);
}
$gui->close();
$geub->close();
$csvexport->download_file();
exit;
}
