/** * Write in the worksheet the given facetoface attendance information * filtered by location. * * This function includes lots of custom SQL because it's otherwise * way too slow. * * @param object $worksheet Currently open worksheet * @param integer $startingrow Index of the starting row (usually 1) * @param integer $facetofaceid ID of the facetoface activity * @param string $location Location to filter by * @param string $coursename Name of the course (optional) * @param string $activityname Name of the facetoface activity (optional) * @param object $dateformat Use to write out dates in the spreadsheet * @returns integer Index of the last row written */ function facetoface_write_activity_attendance(&$worksheet, $startingrow, $facetofaceid, $location, $coursename, $activityname, $dateformat) { global $CFG, $DB; $trainerroles = facetoface_get_trainer_roles(); $userfields = facetoface_get_userfields(); $customsessionfields = facetoface_get_session_customfields(); $timenow = time(); $i = $startingrow; $locationcondition = ''; $locationparam = array(); if (!empty($location)) { $locationcondition = "AND s.location = ?"; $locationparam = array($location); } // Fast version of "facetoface_get_attendees()" for all sessions. $sessionsignups = array(); $signups = $DB->get_records_sql("\n SELECT\n su.id AS submissionid,\n s.id AS sessionid,\n u.*,\n f.course AS courseid,\n ss.grade,\n sign.timecreated\n FROM\n {facetoface} f\n JOIN\n {facetoface_sessions} s\n ON s.facetoface = f.id\n JOIN\n {facetoface_signups} su\n ON s.id = su.sessionid\n JOIN\n {facetoface_signups_status} ss\n ON su.id = ss.signupid\n LEFT JOIN\n (\n SELECT\n ss.signupid,\n MAX(ss.timecreated) AS timecreated\n FROM\n {facetoface_signups_status} ss\n INNER JOIN\n {facetoface_signups} s\n ON s.id = ss.signupid\n INNER JOIN\n {facetoface_sessions} se\n ON s.sessionid = se.id\n AND se.facetoface = {$facetofaceid}\n WHERE\n ss.statuscode IN (?,?)\n GROUP BY\n ss.signupid\n ) sign\n ON su.id = sign.signupid\n JOIN\n {user} u\n ON u.id = su.userid\n WHERE\n f.id = ?\n AND ss.superceded != 1\n AND ss.statuscode >= ?\n ORDER BY\n s.id, u.firstname, u.lastname\n ", array(MDL_F2F_STATUS_BOOKED, MDL_F2F_STATUS_WAITLISTED, $facetofaceid, MDL_F2F_STATUS_APPROVED)); if ($signups) { // Get all grades at once. $userids = array(); foreach ($signups as $signup) { if ($signup->id > 0) { $userids[] = $signup->id; } } $gradinginfo = grade_get_grades(reset($signups)->courseid, 'mod', 'facetoface', $facetofaceid, $userids); foreach ($signups as $signup) { $userid = $signup->id; if ($customuserfields = facetoface_get_user_customfields($userid, $userfields)) { foreach ($customuserfields as $fieldname => $value) { if (!isset($signup->{$fieldname})) { $signup->{$fieldname} = $value; } } } // Set grade. if (!empty($gradinginfo->items) and !empty($gradinginfo->items[0]->grades[$userid])) { $signup->grade = $gradinginfo->items[0]->grades[$userid]->str_grade; } $sessionsignups[$signup->sessionid][$signup->id] = $signup; } } // Fast version of "facetoface_get_sessions($facetofaceid, $location)". $sql = "SELECT d.id as dateid, s.id, s.datetimeknown, s.capacity,\n s.duration, d.timestart, d.timefinish\n FROM {facetoface_sessions} s\n JOIN {facetoface_sessions_dates} d ON s.id = d.sessionid\n WHERE\n s.facetoface = ?\n AND d.sessionid = s.id\n {$locationcondition}\n ORDER BY s.datetimeknown, d.timestart"; $sessions = $DB->get_records_sql($sql, array_merge(array($facetofaceid), $locationparam)); $i = $i - 1; // Will be incremented BEFORE each row is written. foreach ($sessions as $session) { $customdata = $DB->get_records('facetoface_session_data', array('sessionid' => $session->id), '', 'fieldid, data'); $sessiondate = false; $starttime = get_string('wait-listed', 'facetoface'); $finishtime = get_string('wait-listed', 'facetoface'); $status = get_string('wait-listed', 'facetoface'); $sessiontrainers = facetoface_get_trainers($session->id); if ($session->datetimeknown) { // Display only the first date. if (method_exists($worksheet, 'write_date')) { // Needs the patch in MDL-20781. $sessiondate = (int) $session->timestart; } else { $sessiondate = userdate($session->timestart, get_string('strftimedate', 'langconfig')); } $starttime = userdate($session->timestart, get_string('strftimetime', 'langconfig')); $finishtime = userdate($session->timefinish, get_string('strftimetime', 'langconfig')); if ($session->timestart < $timenow) { $status = get_string('sessionover', 'facetoface'); } else { $signupcount = 0; if (!empty($sessionsignups[$session->id])) { $signupcount = count($sessionsignups[$session->id]); } if ($signupcount >= $session->capacity) { $status = get_string('bookingfull', 'facetoface'); } else { $status = get_string('bookingopen', 'facetoface'); } } } if (!empty($sessionsignups[$session->id])) { foreach ($sessionsignups[$session->id] as $attendee) { $i++; $j = 0; // Custom session fields. foreach ($customsessionfields as $field) { if (empty($field->showinsummary)) { continue; // Skip. } $data = '-'; if (!empty($customdata[$field->id])) { if (CUSTOMFIELD_TYPE_MULTISELECT == $field->type) { $data = str_replace(CUSTOMFIELD_DELIMITER, "\n", $customdata[$field->id]->data); } else { $data = $customdata[$field->id]->data; } } $worksheet->write_string($i, $j++, $data); } if (empty($sessiondate)) { $worksheet->write_string($i, $j++, $status); // Session date. } else { if (method_exists($worksheet, 'write_date')) { $worksheet->write_date($i, $j++, $sessiondate, $dateformat); } else { $worksheet->write_string($i, $j++, $sessiondate); } } $worksheet->write_string($i, $j++, $starttime); $worksheet->write_string($i, $j++, $finishtime); $worksheet->write_number($i, $j++, (int) $session->duration); $worksheet->write_string($i, $j++, $status); if ($trainerroles) { foreach (array_keys($trainerroles) as $roleid) { if (!empty($sessiontrainers[$roleid])) { $trainers = array(); foreach ($sessiontrainers[$roleid] as $trainer) { $trainers[] = fullname($trainer); } $trainers = implode(', ', $trainers); } else { $trainers = '-'; } $worksheet->write_string($i, $j++, $trainers); } } foreach ($userfields as $shortname => $fullname) { $value = '-'; if (!empty($attendee->{$shortname})) { $value = $attendee->{$shortname}; } if ('firstaccess' == $shortname || 'lastaccess' == $shortname || 'lastlogin' == $shortname || 'currentlogin' == $shortname) { if (method_exists($worksheet, 'write_date')) { $worksheet->write_date($i, $j++, (int) $value, $dateformat); } else { $worksheet->write_string($i, $j++, userdate($value, get_string('strftimedate', 'langconfig'))); } } else { $worksheet->write_string($i, $j++, $value); } } $worksheet->write_string($i, $j++, $attendee->grade); if (method_exists($worksheet, 'write_date')) { $worksheet->write_date($i, $j++, (int) $attendee->timecreated, $dateformat); } else { $signupdate = userdate($attendee->timecreated, get_string('strftimedatetime', 'langconfig')); if (empty($signupdate)) { $signupdate = '-'; } $worksheet->write_string($i, $j++, $signupdate); } if (!empty($coursename)) { $worksheet->write_string($i, $j++, $coursename); } if (!empty($activityname)) { $worksheet->write_string($i, $j++, $activityname); } } } else { // No one is sign-up, so let's just print the basic info. $i++; $j = 0; // Custom session fields. foreach ($customsessionfields as $field) { if (empty($field->showinsummary)) { continue; // Skip. } $data = '-'; if (!empty($customdata[$field->id])) { if (CUSTOMFIELD_TYPE_MULTISELECT == $field->type) { $data = str_replace(CUSTOMFIELD_DELIMITER, "\n", $customdata[$field->id]->data); } else { $data = $customdata[$field->id]->data; } } $worksheet->write_string($i, $j++, $data); } if (empty($sessiondate)) { $worksheet->write_string($i, $j++, $status); // Session date. } else { if (method_exists($worksheet, 'write_date')) { $worksheet->write_date($i, $j++, $sessiondate, $dateformat); } else { $worksheet->write_string($i, $j++, $sessiondate); } } $worksheet->write_string($i, $j++, $starttime); $worksheet->write_string($i, $j++, $finishtime); $worksheet->write_number($i, $j++, (int) $session->duration); $worksheet->write_string($i, $j++, $status); foreach ($userfields as $unused) { $worksheet->write_string($i, $j++, '-'); } $worksheet->write_string($i, $j++, '-'); if (!empty($coursename)) { $worksheet->write_string($i, $j++, $coursename); } if (!empty($activityname)) { $worksheet->write_string($i, $j++, $activityname); } } } return $i; }
/** * Write in the worksheet the given facetoface attendance information * filtered by location. * * This function includes lots of custom SQL because it's otherwise * way too slow. * * @param object $worksheet Currently open worksheet * @param object $coursecontext context of the course containing this f2f activity * @param integer $startingrow Index of the starting row (usually 1) * @param integer $facetofaceid ID of the facetoface activity * @param string $location Location to filter by * @param string $coursename Name of the course (optional) * @param string $activityname Name of the facetoface activity (optional) * @param object $dateformat Use to write out dates in the spreadsheet * @returns integer Index of the last row written */ function facetoface_write_activity_attendance(&$worksheet, $coursecontext, $startingrow, $facetofaceid, $location, $coursename, $activityname, $dateformat) { global $CFG, $DB; $trainerroles = facetoface_get_trainer_roles($coursecontext); $userfields = facetoface_get_userfields(); $customsessionfields = facetoface_get_session_customfields(); $timenow = time(); $i = $startingrow; $locationcondition = ''; $locationparam = array(); if (!empty($location)) { $locationcondition = "AND s.location = ?"; $locationparam = array($location); } // Fast version of "facetoface_get_attendees()" for all sessions $sessionsignups = array(); $signups = $DB->get_records_sql(" SELECT su.id AS submissionid, s.id AS sessionid, u.*, f.course AS courseid, ss.grade, sign.timecreated, u2.email AS managersemail FROM {facetoface} f JOIN {facetoface_sessions} s ON s.facetoface = f.id JOIN {facetoface_signups} su ON s.id = su.sessionid JOIN {facetoface_signups_status} ss ON su.id = ss.signupid LEFT JOIN ( SELECT ss.signupid, MAX(ss.timecreated) AS timecreated FROM {facetoface_signups_status} ss INNER JOIN {facetoface_signups} s ON s.id = ss.signupid INNER JOIN {facetoface_sessions} se ON s.sessionid = se.id AND se.facetoface = $facetofaceid WHERE ss.statuscode IN (?,?) GROUP BY ss.signupid ) sign ON su.id = sign.signupid JOIN {user} u ON u.id = su.userid JOIN {pos_assignment} pa ON pa.userid = u.id JOIN {user} u2 ON u2.id = pa.managerid WHERE f.id = ? AND ss.superceded != 1 AND ss.statuscode >= ? AND pa.type = ? ORDER BY s.id, u.firstname, u.lastname ", array(MDL_F2F_STATUS_BOOKED, MDL_F2F_STATUS_WAITLISTED, $facetofaceid, MDL_F2F_STATUS_APPROVED, POSITION_TYPE_PRIMARY)); if ($signups) { // Get all grades at once $userids = array(); foreach ($signups as $signup) { if ($signup->id > 0) { $userids[] = $signup->id; } } $usercustomfields = explode(',', $CFG->facetoface_export_customprofilefields); // Figure out which custom fields will need date/time formatting later on. $formatdate = array('firstaccess', 'lastaccess', 'lastlogin', 'currentlogin'); list($cf_sql, $cf_param) = $DB->get_in_or_equal($usercustomfields); $sql = "SELECT " . $DB->sql_concat("'customfield_'", 'shortname') . " AS shortname FROM {user_info_field} WHERE shortname {$cf_sql} AND datatype = 'datetime'"; $usercustomformats = $DB->get_records_sql($sql, $cf_param); $formatdate = array_merge($formatdate, array_keys($usercustomformats)); foreach ($signups as $signup) { $userid = $signup->id; if (!empty($CFG->facetoface_export_customprofilefields)) { $customuserfields = facetoface_get_user_customfields($userid, array_map('trim', $usercustomfields)); foreach ($customuserfields as $fieldname => $value) { if (!isset($signup->$fieldname)) { $signup->$fieldname = $value; } } } $sessionsignups[$signup->sessionid][$signup->id] = $signup; } } // Fast version of "facetoface_get_sessions($facetofaceid, $location)" $sql = "SELECT d.id as dateid, s.id, s.datetimeknown, s.capacity, s.duration, d.timestart, d.timefinish, d.sessiontimezone, r.name as roomname, r.building as building, r.address as address FROM {facetoface_sessions} s JOIN {facetoface_sessions_dates} d ON s.id = d.sessionid LEFT JOIN {facetoface_room} r ON s.roomid = r.id WHERE s.facetoface = ? AND d.sessionid = s.id $locationcondition ORDER BY s.datetimeknown, d.timestart"; $sessions = $DB->get_records_sql($sql, array_merge(array($facetofaceid), $locationparam)); $i = $i - 1; // will be incremented BEFORE each row is written foreach ($sessions as $session) { $customdata = $DB->get_records('facetoface_session_data', array('sessionid' => $session->id), '', 'fieldid, data'); $sessionstartdate = false; $sessionenddate = false; $starttime = get_string('wait-listed', 'facetoface'); $finishtime = get_string('wait-listed', 'facetoface'); $status = get_string('wait-listed', 'facetoface'); $sessiontrainers = facetoface_get_trainers($session->id); if ($session->datetimeknown) { // Display only the first date $sessionobj = facetoface_format_session_times($session->timestart, $session->timefinish, $session->sessiontimezone); $starttime = $sessionobj->starttime . ' ' . $sessionobj->timezone; $finishtime = $sessionobj->endtime . ' ' . $sessionobj->timezone; if (method_exists($worksheet, 'write_date')) { // Needs the patch in MDL-20781 $sessionstartdate = (int)$session->timestart; $sessionenddate = (int)$session->timefinish; } else { $sessionstartdate = $sessionobj->startdate; $sessionenddate = $sessionobj->enddate; } if ($session->timestart < $timenow) { $status = get_string('sessionover', 'facetoface'); } else { $signupcount = 0; if (!empty($sessionsignups[$session->id])) { $signupcount = count($sessionsignups[$session->id]); } if ($signupcount >= $session->capacity) { $status = get_string('bookingfull', 'facetoface'); } else { $status = get_string('bookingopen', 'facetoface'); } } } if (!empty($sessionsignups[$session->id])) { foreach ($sessionsignups[$session->id] as $attendee) { $i++; $j=0; // Custom session fields foreach ($customsessionfields as $field) { if (empty($field->showinsummary)) { continue; // skip } $data = '-'; if (!empty($customdata[$field->id])) { if (CUSTOMFIELD_TYPE_MULTISELECT == $field->type) { $data = str_replace(CUSTOMFIELD_DELIMITER, "\n", $customdata[$field->id]->data); } else { $data = $customdata[$field->id]->data; } } $worksheet->write_string($i, $j++, $data); } if (empty($sessionstartdate)) { $worksheet->write_string($i, $j++, $status); // Session start date. $worksheet->write_string($i, $j++, $status); // Session end date. } else { if (method_exists($worksheet, 'write_date')) { $worksheet->write_date($i, $j++, $sessionstartdate, $dateformat); $worksheet->write_date($i, $j++, $sessionenddate, $dateformat); } else { $worksheet->write_string($i, $j++, $sessionstartdate); $worksheet->write_string($i, $j++, $sessionenddate); } } //Room $roomname = isset($session->roomname) ? $session->roomname . ', ' : ''; $building = isset($session->building) ? $session->building . ', ' : ''; $address = isset($session->address) ? $session->address : ''; $worksheet->write_string($i, $j++, $roomname . $building . $address); $worksheet->write_string($i,$j++,$starttime); $worksheet->write_string($i,$j++,$finishtime); $worksheet->write_number($i,$j++,(int)$session->duration); $worksheet->write_string($i,$j++,$status); if ($trainerroles) { foreach (array_keys($trainerroles) as $roleid) { if (!empty($sessiontrainers[$roleid])) { $trainers = array(); foreach ($sessiontrainers[$roleid] as $trainer) { $trainers[] = fullname($trainer); } $trainers = implode(', ', $trainers); } else { $trainers = '-'; } $worksheet->write_string($i, $j++, $trainers); } } foreach ($userfields as $shortname => $fullname) { $value = '-'; if (!empty($attendee->$shortname)) { $value = $attendee->$shortname; } if (in_array($shortname, $formatdate)) { if (method_exists($worksheet, 'write_date')) { $worksheet->write_date($i, $j++, (int)$value, $dateformat); } else { $worksheet->write_string($i, $j++, userdate($value, get_string('strftimedate', 'langconfig'))); } } else { $worksheet->write_string($i,$j++,$value); } } $worksheet->write_string($i,$j++,$attendee->grade); if (method_exists($worksheet,'write_date')) { $worksheet->write_date($i, $j++, (int)$attendee->timecreated, $dateformat); } else { $signupdate = userdate($attendee->timecreated, get_string('strftimedatetime', 'langconfig')); if (empty($signupdate)) { $signupdate = '-'; } $worksheet->write_string($i,$j++, $signupdate); } if (!empty($coursename)) { $worksheet->write_string($i, $j++, $coursename); } if (!empty($activityname)) { $worksheet->write_string($i, $j++, $activityname); } } } else { // no one is sign-up, so let's just print the basic info $i++; $j=0; // Custom session fields foreach ($customsessionfields as $field) { if (empty($field->showinsummary)) { continue; // skip } $data = '-'; if (!empty($customdata[$field->id])) { if (CUSTOMFIELD_TYPE_MULTISELECT == $field->type) { $data = str_replace(CUSTOMFIELD_DELIMITER, "\n", $customdata[$field->id]->data); } else { $data = $customdata[$field->id]->data; } } $worksheet->write_string($i, $j++, $data); } if (empty($sessionstartdate)) { $worksheet->write_string($i, $j++, $status); // Session start date. $worksheet->write_string($i, $j++, $status); // Session end date. } else { if (method_exists($worksheet, 'write_date')) { $worksheet->write_date($i, $j++, $sessionstartdate, $dateformat); $worksheet->write_date($i, $j++, $sessionenddate, $dateformat); } else { $worksheet->write_string($i, $j++, $sessionstartdate); $worksheet->write_string($i, $j++, $sessionenddate); } } //Room $roomname = isset($session->roomname) ? $session->roomname . ', ' : ''; $building = isset($session->building) ? $session->building . ', ' : ''; $address = isset($session->address) ? $session->address : ''; $worksheet->write_string($i, $j++, $roomname . $building . $address); $worksheet->write_string($i,$j++,$starttime); $worksheet->write_string($i,$j++,$finishtime); $worksheet->write_number($i,$j++,(int)$session->duration); $worksheet->write_string($i,$j++,$status); if ($trainerroles) { foreach (array_keys($trainerroles) as $roleid) { if (!empty($sessiontrainers[$roleid])) { $trainers = array(); foreach ($sessiontrainers[$roleid] as $trainer) { $trainers[] = fullname($trainer); } $trainers = implode(', ', $trainers); } else { $trainers = '-'; } $worksheet->write_string($i, $j++, $trainers); } } foreach ($userfields as $unused) { $worksheet->write_string($i,$j++,'-'); } // Grade/attendance $worksheet->write_string($i,$j++,'-'); // Date signed up $worksheet->write_string($i,$j++,'-'); if (!empty($coursename)) { $worksheet->write_string($i, $j++, $coursename); } if (!empty($activityname)) { $worksheet->write_string($i, $j++, $activityname); } } } return $i; }
function test_facetoface_get_userfields() { $this->assertTrue((bool)facetoface_get_userfields(), $this->msgtrue); $this->resetAfterTest(true); }