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; }