/**
* 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_user_custom_fields() {
// Test variables.
$userid1 = 1;
$userid2 = 42;
$fieldstoinclude1 = TRUE;
// Test for valid case.
$this->assertTrue((bool)facetoface_get_user_customfields($userid1, $fieldstoinclude1), $this->msgtrue);
$this->assertTrue((bool)facetoface_get_user_customfields($userid1), $this->msgtrue);
//TODO invalid case
// Test for invalid case.
$this->resetAfterTest(true);
}
