/**
* Return list of marked submissions that have not been mailed out for currently enrolled students
*/
function facetoface_get_unmailed_reminders() {
global $CFG, $DB;
$submissions = $DB->get_records_sql("
SELECT
su.*,
f.course,
f.id as facetofaceid,
f.name as facetofacename,
se.duration,
se.normalcost,
se.discountcost,
se.details,
se.datetimeknown
FROM
{facetoface_signups} su
INNER JOIN
{facetoface_signups_status} sus
ON su.id = sus.signupid
AND sus.superceded = 0
AND sus.statuscode = ?
JOIN
{facetoface_sessions} se
ON su.sessionid = se.id
JOIN
{facetoface} f
ON se.facetoface = f.id
WHERE
su.mailedreminder = 0
AND se.datetimeknown = 1
", array(MDL_F2F_STATUS_BOOKED));
if ($submissions) {
foreach ($submissions as $key => $value) {
$submissions[$key]->duration = facetoface_minutes_to_hours($submissions[$key]->duration);
$submissions[$key]->sessiondates = facetoface_get_session_dates($value->sessionid);
}
}
return $submissions;
}
/**
* Get all records from facetoface_sessions for a given facetoface activity and location
*
* @param integer $facetofaceid ID of the activity
* @param string $location location filter (optional)
*/
function facetoface_get_sessions($facetofaceid, $location='') {
global $CFG,$DB;
$fromclause = "FROM {facetoface_sessions} s";
$locationwhere = '';
$locationparams = array();
if (!empty($location)) {
$fromclause = "FROM {facetoface_session_data} d
JOIN {facetoface_sessions} s ON s.id = d.sessionid";
$locationwhere .= " AND d.data = ?";
$locationparams[] = $location;
}
$sessions = $DB->get_records_sql("SELECT s.*
$fromclause
LEFT OUTER JOIN (SELECT sessionid, min(timestart) AS mintimestart
FROM {facetoface_sessions_dates} GROUP BY sessionid) m ON m.sessionid = s.id
WHERE s.facetoface = ?
$locationwhere
ORDER BY s.datetimeknown, m.mintimestart", array_merge(array($facetofaceid), $locationparams));
if ($sessions) {
foreach ($sessions as $key => $value) {
$sessions[$key]->duration = facetoface_minutes_to_hours($sessions[$key]->duration);
$sessions[$key]->sessiondates = facetoface_get_session_dates($value->id);
}
}
return $sessions;
}
/**
* Return list of marked submissions that have not been mailed out for currently enrolled students
*/
function facetoface_get_unmailed_reminders()
{
global $CFG, $DB;
$submissions = $DB->get_records_sql("\n SELECT\n su.*,\n f.course,\n f.id as facetofaceid,\n f.name as facetofacename,\n f.reminderperiod,\n se.duration,\n se.normalcost,\n se.discountcost,\n se.details,\n se.datetimeknown\n FROM\n {facetoface_signups} su\n INNER JOIN\n {facetoface_signups_status} sus\n ON su.id = sus.signupid\n AND sus.superceded = 0\n AND sus.statuscode = ?\n JOIN\n {facetoface_sessions} se\n ON su.sessionid = se.id\n JOIN\n {facetoface} f\n ON se.facetoface = f.id\n WHERE\n su.mailedreminder = 0\n AND se.datetimeknown = 1\n ", array(MDL_F2F_STATUS_BOOKED));
if ($submissions) {
foreach ($submissions as $key => $value) {
$submissions[$key]->duration = facetoface_minutes_to_hours($submissions[$key]->duration);
$submissions[$key]->sessiondates = facetoface_get_session_dates($value->sessionid);
}
}
return $submissions;
}
public function test_facetoface_minutes_to_hours()
{
// Test for positive minutes value.
$this->assertEqual(facetoface_minutes_to_hours('11'), '0:11');
// Test for positive hours & minutes value.
$this->assertEqual(facetoface_minutes_to_hours('67'), '1:7');
// Test for negative minutes value.
$this->assertEqual(facetoface_minutes_to_hours('-42'), '-42');
// Test for negative hours and minutes value.
$this->assertEqual(facetoface_minutes_to_hours('-7:19'), '-7:19');
// Test for invalid characters value.
$this->assertEqual(facetoface_minutes_to_hours('invalid_string'), '0');
}
function facetoface_get_custom_sessions($facetofaceid, $location='') {
global $CFG,$DB,$USER;
$fromclause = "FROM {facetoface} fa,{facetoface_sessions} s";
$locationwhere = '';
$locationparams = array();
if (!empty($location)) {
$fromclause = "FROM {facetoface_session_data} d
JOIN {facetoface_sessions} s ON s.id = d.sessionid";
$locationwhere .= " AND d.data = ?";
$locationparams[] = $location;
}
$sessions = $DB->get_records_sql("SELECT s.*,fa.name
$fromclause
LEFT OUTER JOIN (SELECT sessionid, min(timestart) AS mintimestart
FROM {facetoface_sessions_dates} GROUP BY sessionid) m ON m.sessionid = s.id
LEFT JOIN {facetoface_signups} fs ON fs.sessionid=s.id LEFT JOIN {facetoface_signups_status} fss ON fss.signupid=fs.id WHERE s.facetoface=fa.id AND fs.userid={$USER->id} AND fss.statuscode=70
AND fss.superceded=0 $locationwhere
ORDER BY s.datetimeknown, m.mintimestart");
if ($sessions) {
foreach ($sessions as $key => $value) {
$sessions[$key]->duration = facetoface_minutes_to_hours($sessions[$key]->duration);
$sessions[$key]->sessiondates = facetoface_get_session_dates($value->id);
}
}
return $sessions;
}
