/**
* Get the data to display for this table page.
*
* @param bool $download Flag to not include HTML for report download.
* @return array An array of data records.
*/
function get_data($download = false)
{
global $CURMAN;
$LIKE = $CURMAN->db->sql_compare();
if ($CURMAN->db->_dbconnection->databaseType == 'postgres7') {
$FULLNAME = 'usr.firstname || \' \' || COALESCE(usr.mi, \'\') || \' \' || usr.lastname';
} else {
$FULLNAME = 'CONCAT(usr.firstname,\' \',IFNULL(usr.mi, \'\'),\' \',usr.lastname)';
}
$select = "SELECT cls.id as classid,\n cls.idnumber as classidnumber,\n crs.id as courseid,\n crs.idnumber as courseidnumber,\n crs.name as coursename,\n crs.credits as credits,\n stu.completetime as datecomplete,\n stu.completestatusid as completed,\n stu.grade as classgrade,\n stu.userid as userid\n ";
$tables = "FROM " . $CURMAN->db->prefix_table(STUTABLE) . " stu\n INNER JOIN " . $CURMAN->db->prefix_table(CLSTABLE) . " cls ON cls.id = stu.classid\n INNER JOIN " . $CURMAN->db->prefix_table(CRSTABLE) . " crs ON crs.id = cls.courseid\n ";
$where = "WHERE stu.userid = '{$this->usrid}' AND stu.completestatusid != 0 ";
$sort = "ORDER BY datecomplete ASC ";
/// Get the current 'page' of results.
$sql = $select . $tables . $where . $sort;
$data2 = $CURMAN->db->get_records_sql($sql);
/// Add non-DB info to the records for display.
$this->_maxexams = 0;
if (!empty($data2)) {
foreach ($data2 as $di => $datum) {
if (!is_null($datum->classid) && !is_null($datum->userid)) {
/// Get any course element id's.
$select = 'SELECT cc.id, cc.courseid, cc.idnumber as elementname, ccg.id as ccgid,' . 'ccg.grade as grade,ccg.timegraded as timegraded ' . 'FROM ' . $CURMAN->db->prefix_table(CRSCOMPTABLE) . ' cc ' . 'LEFT JOIN ' . $CURMAN->db->prefix_table(CLSGRTABLE) . ' ccg ' . 'ON ccg.classid = ' . $datum->classid . ' AND ccg.userid = ' . $datum->userid . ' ' . 'AND ccg.completionid = cc.id ' . 'WHERE cc.courseid = ' . $datum->courseid . ' ' . 'ORDER BY id ASC';
} else {
/// Get any course element id's.
$select = 'SELECT cc.id, cc.courseid, cc.idnumber as elementname ' . 'FROM ' . $CURMAN->db->prefix_table(CRSCOMPTABLE) . ' cc ' . 'WHERE cc.courseid = ' . $datum->courseid . ' ' . 'ORDER BY id ASC';
}
if (!($ccs = get_records_sql($select))) {
$ccs = array();
}
$this->_maxexams = MAX(count($ccs), $this->_maxexams);
$i = 1;
foreach ($ccs as $cc) {
$datum->{'ccid' . $i} = $cc->id;
$datum->{'ccelementname' . $i} = $cc->elementname;
$datum->{'ccgid' . $i} = isset($cc->ccgid) ? $cc->ccgid : '';
$datum->{'ccgrade' . $i} = isset($cc->grade) ? $cc->grade : '';
$datum->{'cctimegraded' . $i} = isset($cc->timegraded) ? !empty($cc->timegraded) ? date('M j, Y', $cc->timegraded) : '-' : '';
$i++;
}
if (!empty($datum->datecomplete) && !empty($datum->frequency)) {
switch ($datum->timeperiod) {
case 'year':
$datum->dnextdue = cm_timedelta($datum->datecomplete, $datum->frequency);
break;
case 'month':
$datum->dnextdue = cm_timedelta($datum->datecomplete, 0, $datum->frequency);
break;
case 'week':
$datum->dnextdue = cm_timedelta($datum->datecomplete, 0, 0, $datum->frequency);
break;
case 'day':
$datum->dnextdue = cm_timedelta($datum->datecomplete, 0, 0, 0, $datum->frequency);
break;
default:
$datum->dnextdue = 0;
break;
}
$datum->nextdue = !empty($datum->dnextdue) ? date('M j, Y', $datum->dnextdue) : '-';
} else {
$datum->nextdue = '-';
}
if (!empty($datum->datecomplete)) {
$datum->datecomplete = !empty($datum->datecomplete) ? cm_timestamp_to_date($datum->datecomplete) : '<span style="color: red;">' . get_string('incomplete', 'block_curr_admin') . '</span>';
} else {
$datum->datecomplete = '<span style="color: red;">' . get_string('incomplete', 'block_curr_admin') . '</span>';
}
switch ($datum->completed) {
case STUSTATUS_FAILED:
if (!$download) {
$datum->completestatus = '<span style="color: red;">' . get_string('failed', 'block_curr_admin') . '</span>';
} else {
$datum->completestatus = get_string('failed', 'block_curr_admin');
}
break;
case STUSTATUS_PASSED:
if (!$download) {
$datum->completestatus = '<span style="color: green;">' . get_string('complete', 'block_curr_admin') . '</span>';
} else {
$datum->completestatus = get_string('complete', 'block_curr_admin');
}
break;
case STUSTATUS_NOTCOMPLETE:
default:
$timenow = time();
if (!empty($datum->clsend) && $datum->clsend > $timenow && $datum->clsend - $timenow < 90 * 24 * 60 * 60) {
/// If the course is due within 90 days, colour code it accordingly.
if (!$download) {
$datum->completestatus = '<span style="color: red;">' . get_string('incomplete', 'block_curr_admin') . '</span>';
} else {
$datum->completestatus = get_string('incomplete', 'block_curr_admin');
}
} else {
if (!empty($datum->clsend) && $datum->clsend < $timenow) {
/// If the course is overdue, then display by how many days.
$timedelta = $timenow - $datum->clsend;
$timeday = 24 * 60 * 60;
// Number of seconds in a day.
if ($timedelta < $timeday) {
if (!$download) {
$datum->completestatus = '<span style="colur: red;">' . get_string('overdue_zero', 'block_curr_admin') . '</span>';
} else {
$datum->completestatus = get_string('overdue_zero', 'block_curr_admin');
}
} else {
$daysover = floor($timedelta / $timeday);
if (!$download) {
$datum->completestatus = '<span style="color: red;">' . get_string('overdue', 'block_curr_admin') . ': ' . $daysover . ($daysover > 1 ? get_string('duration_days', 'block_curr_admin') : get_string('duration_day', 'block_curr_admin')) . '</span>';
} else {
$datum->completestatus = 'Overdue: ' . $daysover . ($daysover > 1 ? get_string('duration_days', 'block_curr_admin') : get_string('duration_day', 'block_curr_admin'));
}
}
} else {
if (!$download) {
$datum->completestatus = '<span style="color: red;">' . get_string('incomplete', 'block_curr_admin') . '</span>';
} else {
$datum->completestatus = get_string('incomplete', 'block_curr_admin');
}
}
}
break;
}
$datum->classpercent = $datum->classgrade;
if (empty($datum->classpercent)) {
$datum->classpercent = 'I';
$datum->gradepoints = 0;
} else {
if ($datum->classpercent >= 90) {
$datum->classgrade = 'A';
$datum->gradepoints = 4;
} else {
if ($datum->classpercent >= 80) {
$datum->classgrade = 'B';
$datum->gradepoints = 3;
} else {
if ($datum->classpercent >= 70) {
$datum->classgrade = 'C';
$datum->gradepoints = 2;
} else {
$datum->classgrade = 'I';
$datum->gradepoints = 0;
}
}
}
}
$data2[$di] = $datum;
}
}
/// Organize the data into udergraduate and graduate courses
$this->rawdata = array();
if (!empty($data2)) {
foreach ($data2 as $di2 => $datum2) {
if ($datum2->courseidnumber[0] == 'M') {
$coursecat = 'grad';
$name = get_string('grad_courses', 'block_curr_admin');
} else {
$coursecat = 'undergrad';
$name = get_string('undergrad_courses', 'block_curr_admin');
}
if (!isset($this->rawdata[$coursecat])) {
$this->rawdata[$coursecat] = new Object();
$this->rawdata[$coursecat]->curriculumname = $name;
$this->rawdata[$coursecat]->numcredits = 0;
$this->rawdata[$coursecat]->gpa = 0;
}
if ((int) $datum2->completed > 0) {
$this->rawdata[$coursecat]->numcredits += $datum2->credits;
$this->rawdata[$coursecat]->gpa += $datum2->gradepoints * $datum2->credits;
}
$this->rawdata[$coursecat]->data[] = clone $datum2;
}
}
}
/**
* Get the data to display for this table page.
*
* @param bool $download Flag to not include HTML for report download.
* @return array An array of data records.
*/
function get_data($download = false)
{
global $CURMAN;
$LIKE = $CURMAN->db->sql_compare();
$FULLNAME = sql_concat('usr.firstname', "' '", 'usr.lastname');
$cselect = 'SELECT COUNT(stu.id) ';
$select = "SELECT DISTINCT(stu.id),\n usr.idnumber as idnumber,\n {$FULLNAME} as username,\n crs.name as classname,\n stu.completetime as datecomplete,\n stu.completestatusid as completestatus,\n curcrs.frequency as frequency,\n curcrs.timeperiod as timeperiod ";
$tables = "FROM " . $CURMAN->db->prefix_table(STUTABLE) . " stu\n INNER JOIN " . $CURMAN->db->prefix_table(USRTABLE) . " usr ON usr.id = stu.userid\n INNER JOIN " . $CURMAN->db->prefix_table(CLSTABLE) . " cls ON cls.id = stu.classid\n INNER JOIN " . $CURMAN->db->prefix_table(CRSTABLE) . " crs ON crs.id = cls.courseid\n LEFT JOIN " . $CURMAN->db->prefix_table(CURCRSTABLE) . " curcrs ON curcrs.courseid = crs.id\n LEFT JOIN " . $CURMAN->db->prefix_table(CURTABLE) . " cur ON cur.id = curcrs.curriculumid\n LEFT JOIN " . $CURMAN->db->prefix_table(CURASSTABLE) . " curass ON curass.curriculumid = cur.id ";
$where = "usr.local = '{$this->loc}' ";
/*
$where = "usr.local = '{$this->loc}' AND
stu.completestatusid != '" . STUSTATUS_NOTCOMPLETE . "' ";
*/
if (!empty($this->search)) {
$search = trim($this->search);
$where .= (!empty($where) ? ' AND ' : '') . "(({$FULLNAME} {$LIKE} '%{$this->search}%') " . " OR (usr.idnumber {$LIKE} '%{$this->search}%')) ";
}
if ($this->alpha) {
$where .= (!empty($where) ? ' AND ' : '') . "({$FULLNAME} {$LIKE} '{$this->alpha}%') ";
}
if (!empty($where)) {
$where = 'WHERE ' . $where . ' ';
}
if (!empty($this->sort)) {
$sort = 'ORDER BY ' . $this->sort . ' ' . $this->dir . ' ';
} else {
$sort = '';
}
if (!empty($this->perpage)) {
if ($CURMAN->db->_dbconnection->databaseType == 'postgres7') {
$limit = 'LIMIT ' . $this->perpage . ' OFFSET ' . $this->page * $this->perpage;
} else {
$limit = 'LIMIT ' . $this->page * $this->perpage . ', ' . $this->perpage;
}
} else {
$limit = '';
}
/// Count the total number of results.
$sql = $cselect . $tables . $where;
$this->numrecs = $CURMAN->db->count_records_sql($sql);
/// Get the current 'page' of results.
$sql = $select . $tables . $where . $sort . $limit;
$this->data = $CURMAN->db->get_records_sql($sql);
/// Add non-DB info to the records for display.
if (!empty($this->data)) {
foreach ($this->data as $di => $datum) {
$timenow = time();
if (!empty($datum->frequency)) {
switch ($datum->timeperiod) {
case 'year':
//$datum->dnextdue = $timenow + 365 * 24 * 60 * 60 * $datum->frequency;
$datum->dnextdue = cm_timedelta($timenow, $datum->frequency);
break;
case 'month':
//$datum->dnextdue = $timenow + 30 * 24 * 60 * 60 * $datum->frequency;
$datum->dnextdue = cm_timedelta($timenow, 0, $datum->frequency);
break;
case 'week':
//$datum->dnextdue = $timenow + 24 * 60 * 60 * $datum->frequency;
$datum->dnextdue = cm_timedelta($timenow, 0, 0, $datum->frequency);
break;
case 'day':
//$datum->dnextdue = $timenow + 60 * 60 * $datum->frequency;
$datum->dnextdue = cm_timedelta($timenow, 0, 0, 0, $datum->frequency);
break;
default:
$datum->dnextdue = 0;
break;
}
$datum->nextdue = !empty($datum->dnextdue) ? date('M j, Y', $datum->dnextdue) : '-';
} else {
$datum->nextdue = '-';
}
/// Remove any users here who do not have a course coming up due in 30 days.
if (empty($datum->dnextdue) || $datum->dnextdue - $timenow > 30 * 24 * 60 * 60) {
unset($this->data[$di]);
continue;
}
if (!empty($datum->datecomplete)) {
$datum->datecomplete = !empty($datum->datecomplete) ? date('M j, Y', $datum->datecomplete) : '-';
} else {
$datum->datecomplete = '-';
}
$this->data[$di] = $datum;
}
}
}
/**
* Get the data to display for this table page.
*
* @param bool $download Flag to not include HTML for report download.
* @return array An array of data records.
*/
function get_data($download = false)
{
global $CURMAN;
$LIKE = $CURMAN->db->sql_compare();
$SFULLNAME = sql_concat('susr.firstname', "' '", 'susr.lastname');
$cselect = 'SELECT COUNT(stu.id) ';
$select = 'SELECT DISTINCT(stu.id), stu.classid as classid, stu.userid as userid, ' . 'crs.name as coursename, susr.idnumber as student, susr.firstname, ' . 'stu.completestatusid as completestatus, ' . 'cls.enddate as clsend, crs.syllabus as syllabus ';
$tables = 'FROM ' . $CURMAN->db->prefix_table(USRTABLE) . ' susr ' . 'INNER JOIN ' . $CURMAN->db->prefix_table(STUTABLE) . ' stu ON stu.userid = susr.id ' . 'INNER JOIN ' . $CURMAN->db->prefix_table(CLSTABLE) . ' cls ON cls.id = stu.classid ' . 'LEFT JOIN ' . $CURMAN->db->prefix_table(CRSTABLE) . ' crs ON crs.id = cls.courseid ';
$where = '';
if (!empty($this->searchu)) {
$search = trim($this->searchu);
$where .= (!empty($where) ? ' AND ' : '') . " (susr.idnumber {$LIKE} '%{$search}%') ";
}
if (!empty($this->searchc)) {
$search = trim($this->searchc);
$where .= (!empty($where) ? ' AND ' : '') . "(crs.name {$LIKE} '%{$search}%') ";
}
if (!empty($this->searchl)) {
$search = trim($this->searchl);
$where .= (!empty($where) ? ' AND ' : '') . "(susr.local {$LIKE} '%{$search}%') ";
}
if ($this->alpha) {
$where .= (!empty($where) ? ' AND ' : '') . "(crs.name {$LIKE} '{$this->alpha}%') ";
}
if (!empty($where)) {
$where = 'WHERE ' . $where . ' ';
}
if (!empty($this->sort)) {
$sort = 'ORDER BY ' . $this->sort . ' ' . $this->dir . ' ';
}
// if (!empty($this->perpage)) {
if (!empty($this->perpage)) {
if ($CURMAN->db->_dbconnection->databaseType == 'postgres7') {
$limit = 'LIMIT ' . $this->perpage . ' OFFSET ' . $this->page * $this->perpage;
} else {
$limit = 'LIMIT ' . $this->page * $this->perpage . ', ' . $this->perpage;
}
} else {
$limit = '';
}
/// Count the total number of results.
$sql = $cselect . $tables . $where;
$this->numrecs = $CURMAN->db->count_records_sql($sql);
/// Get the current 'page' of results.
$sql = $select . $tables . $where . $sort . $limit;
//print_object($sql); die;
$this->data = $CURMAN->db->get_records_sql($sql);
/// Add non-DB info to the records for display.
if (!empty($this->data)) {
foreach ($this->data as $di => $datum) {
switch ($datum->completestatus) {
case STUSTATUS_FAILED:
if (!$download) {
$datum->completestatus = '<span style="color: red;">' . get_string('failed', 'block_curr_admin') . '</span>';
} else {
$datum->completestatus = get_string('failed', 'block_curr_admin');
}
break;
case STUSTATUS_PASSED:
if (!$download) {
$datum->completestatus = '<span style="color: green;">' . get_string('passed', 'block_curr_admin') . '</span>';
} else {
$datum->completestatus = get_string('passed', 'block_curr_admin');
}
break;
case STUSTATUS_NOTCOMPLETE:
default:
$timenow = time();
if (!empty($datum->clsend) && $datum->clsend > $timenow && $datum->clsend - $timenow < 90 * 24 * 60 * 60) {
/// If the course is due within 90 days, colour code it accordingly.
if (!$download) {
$datum->completestatus = '<span style="color: yellow;">' . get_string('current', 'block_curr_admin') . '</span>';
} else {
$datum->completestatus = get_string('current', 'block_curr_admin');
}
} else {
if (!empty($datum->clsend) && $datum->clsend < $timenow) {
/// If the course is overdue, then display by how many days.
$timedelta = $timenow - $datum->clsend;
$timeday = 24 * 60 * 60;
// Number of seconds in a day.
if ($timedelta < $timeday) {
if (!$download) {
$datum->completestatus = '<span style="colur: red;">' . get_string('overdue_zero', 'block_curr_admin') . '</span>';
} else {
$datum->completestatus = get_string('overdue_zero', 'block_curr_admin');
}
} else {
$daysover = floor($timedelta / $timeday);
if (!$download) {
$datum->completestatus = '<span style="color: red;">' . get_string('overdue', 'block_curr_admin') . $daysover . ($daysover > 1 ? get_string('duration_days', 'block_curr_admin') : get_string('duration_day', 'block_curr_admin')) . '</span>';
} else {
$datum->completestatus = 'Overdue: ' . $daysover . ($daysover > 1 ? get_string('duration_days', 'block_curr_admin') : get_string('duration_day', 'block_curr_admin'));
}
}
} else {
if (!$download) {
$datum->completestatus = '<span style="color: green;">' . get_string('current', 'block_curr_admin') . '</span>';
} else {
$datum->completestatus = get_string('current', 'block_curr_admin');
}
}
}
break;
}
/// Get any existing frequency and timeperiod values for this class.
$sql = "SELECT curcrs.frequency, curcrs.timeperiod\n FROM " . $CURMAN->db->prefix_table(CLSTABLE) . " cls\n INNER JOIN " . $CURMAN->db->prefix_table(CRSTABLE) . " crs ON crs.id = cls.courseid\n INNER JOIN " . $CURMAN->db->prefix_table(CURCRSTABLE) . " curcrs ON curcrs.courseid = crs.id\n INNER JOIN " . $CURMAN->db->prefix_table(CURASSTABLE) . " curass ON curass.curriculumid = curcrs.curriculumid\n WHERE cls.id = '{$datum->classid}'\n AND curass.userid = '{$datum->userid}'";
if ($rec = $CURMAN->db->get_record_sql($sql)) {
$datum->frequency = $rec->frequency;
$datum->timeperiod = $rec->timeperiod;
} else {
$datum->frequency = 0;
$datum->timeperiod = 0;
}
/// Get recurrant training info.
if ($attendance = cm_get_attendance($datum->classid, $datum->userid)) {
$rtstr = '';
if (!empty($attendance->timeend)) {
$rtstr .= 'Last taken: ' . date('M j, Y', $attendance->timeend);
if (!empty($datum->frequency)) {
$timenext = 0;
switch ($datum->timeperiod) {
case 'year':
//$datum->dnextdue = $timenow + 365 * 24 * 60 * 60 * $datum->frequency;
$datum->dnextdue = cm_timedelta($timenow, $datum->frequency);
break;
case 'month':
//$datum->dnextdue = $timenow + 30 * 24 * 60 * 60 * $datum->frequency;
$datum->dnextdue = cm_timedelta($timenow, 0, $datum->frequency);
break;
case 'week':
//$datum->dnextdue = $timenow + 24 * 60 * 60 * $datum->frequency;
$datum->dnextdue = cm_timedelta($timenow, 0, 0, $datum->frequency);
break;
case 'day':
//$datum->dnextdue = $timenow + 60 * 60 * $datum->frequency;
$datum->dnextdue = cm_timedelta($timenow, 0, 0, 0, $datum->frequency);
break;
}
if (!empty($timenext)) {
$timenext += $attendance->timeend;
if (!$download) {
$rtstr .= '<br /><br />' . get_string('next_due', 'block_curr_admin') . date('M j, Y', $timenext);
} else {
$rtstr .= "\n\n" . get_string('next_due', 'block_curr_admin') . date('M j, Y', $timenext);
}
}
}
}
$datum->recurrenttraining = $rtstr;
} else {
$datum->recurrenttraining = '';
}
/// Format class starting and ending dates.
if (!empty($datum->clsstart)) {
$datum->clsstart = date('M j, Y', $datum->clsstart);
} else {
$datum->clsstart = '-';
}
if (!empty($datum->clsend)) {
$datum->clsend = date('M j, Y', $datum->clsend);
} else {
$datum->clsend = '-';
}
$datum->syllabus = '<img src="pix/instructor.gif" alt="info" title="' . $datum->syllabus . '" />';
$this->data[$di] = $datum;
}
}
}
/**
* Get the data to display for this table page.
*
* @param bool $download Flag to not include HTML for report download.
* @return array An array of data records.
*/
function get_data($download = false)
{
global $CURMAN;
$LIKE = $CURMAN->db->sql_compare();
$FULLNAME = sql_concat('usr.firstname', "' '", 'usr.lastname');
$cselect = 'SELECT COUNT(stu.id) ';
$select = "SELECT DISTINCT(stu.id),\n usr.idnumber as idnumber,\n {$FULLNAME} as crewmember,\n usr.local as location,\n crs.id as crsid,\n cur.id as curid,\n cls.id as clsid,\n crs.name as classname,\n stu.completestatusid as completestatus,\n stu.completetime as datecomplete,\n stu.completestatusid as completestatus,\n curcrs.frequency as frequency,\n curcrs.timeperiod as timeperiod ";
$tables = "FROM " . $CURMAN->db->prefix_table(USRTABLE) . " usr\n INNER JOIN " . $CURMAN->db->prefix_table(STUTABLE) . " stu ON stu.userid = usr.id\n INNER JOIN " . $CURMAN->db->prefix_table(CLSTABLE) . " cls ON cls.id = stu.classid\n INNER JOIN " . $CURMAN->db->prefix_table(CRSTABLE) . " crs ON crs.id = cls.courseid\n LEFT JOIN " . $CURMAN->db->prefix_table(CURCRSTABLE) . " curcrs ON curcrs.courseid = crs.id\n LEFT JOIN " . $CURMAN->db->prefix_table(CURTABLE) . " cur ON cur.id = curcrs.curriculumid\n LEFT JOIN " . $CURMAN->db->prefix_table(CURASSTABLE) . " curass ON curass.curriculumid = cur.id ";
$where = 'curass.userid = stu.userid ';
if (!empty($this->search)) {
$search = trim($this->search);
$where .= (!empty($where) ? ' AND ' : '') . "(({$FULLNAME} {$LIKE} '%{$this->search}%')" . " OR (usr.idnumber {$LIKE} '%{$this->search}%')" . (isset($this->headers['location']) ? " OR (usr.local {$LIKE} '%{$this->search}%')" : '') . ") ";
}
if ($this->alpha) {
$where .= (!empty($where) ? ' AND ' : '') . "({$FULLNAME} {$LIKE} '{$this->alpha}%') ";
}
if (!empty($where)) {
$where = 'WHERE ' . $where . ' ';
}
if (!empty($this->sort)) {
$sort = 'ORDER BY ' . $this->sort . ' ' . $this->dir . ' ';
} else {
$sort = '';
}
if (!empty($this->perpage)) {
if ($CURMAN->db->_dbconnection->databaseType == 'postgres7') {
$limit = 'LIMIT ' . $this->perpage . ' OFFSET ' . $this->page * $this->perpage;
} else {
$limit = 'LIMIT ' . $this->page * $this->perpage . ', ' . $this->perpage;
}
} else {
$limit = '';
}
/// Count the total number of results.
$sql = $cselect . $tables . $where;
$this->numrecs = $CURMAN->db->count_records_sql($sql);
/// Get the current 'page' of results.
$sql = $select . $tables . $where . $sort . $limit;
$this->data = $CURMAN->db->get_records_sql($sql);
/// Add non-DB info to the records for display.
if (!empty($this->data)) {
$timenow = time();
foreach ($this->data as $di => $datum) {
if (!empty($datum->datecomplete) && !empty($datum->frequency)) {
switch ($datum->timeperiod) {
case 'year':
$datum->dueby = cm_timedelta($datum->datecomplete, $datum->frequency);
break;
case 'month':
$datum->dueby = cm_timedelta($datum->datecomplete, 0, $datum->frequency);
break;
case 'week':
$datum->dueby = cm_timedelta($datum->datecomplete, 0, 0, $datum->frequency);
break;
case 'day':
$datum->dueby = cm_timedelta($datum->datecomplete, 0, 0, 0, $datum->frequency);
break;
default:
$datum->dueby = 0;
break;
}
/// Check if the user has a renewal coming up for this course within the specified
/// window of days.
if (empty($datum->dueby) || $datum->dueby - $timenow > $this->daywindow * 24 * 60 * 60) {
unset($this->data[$di]);
continue;
} else {
/// The user DOES have a renewal coming up, see if they have completed another
/// class in the same course at a later date.
$sql = "SELECT COUNT(stu.id)\n FROM " . $CURMAN->db->prefix_table(STUTABLE) . " stu\n INNER JOIN " . $CURMAN->db->prefix_table(CLSTABLE) . " cls ON cls.id = stu.classid\n WHERE cls.courseid = '{$datum->crsid}'\n AND cls.startdate > '{$datum->datecomplete}'\n AND cls.enddate = '0' ";
if (!$CURMAN->db->count_records_sql($sql)) {
$datum->nextdue = floor(($datum->dueby - $timenow) / (24 * 60 * 60));
} else {
unset($this->data[$di]);
continue;
}
$datum->dueby = date('M j, Y', $datum->dueby);
}
} else {
unset($this->data[$di]);
continue;
}
/// This should NEVER be empty.... *glare*
if ($datum->nextdue == 1) {
$datum->nextdue = $datum->strnextdue . ' day';
} else {
$datum->strnextdue = $datum->nextdue . ' days';
}
if ($datum->nextdue > 0) {
$datum->status = 'Qualified';
} else {
$datum->status = 'Non-Qualified';
}
/// Format class starting and ending dates.
if (!empty($datum->completetime)) {
$datum->completetime = date('M j, Y', $datum->completetime);
} else {
$datum->completetime = '-';
}
$this->data[$di] = $datum;
}
}
}
/**
* Get the data to display for this table page.
*
* @param bool $download Flag to not include HTML for report download.
* @return array An array of data records.
*/
function get_data($download = false)
{
global $CURMAN;
$LIKE = $CURMAN->db->sql_compare();
$FULLNAME = sql_concat('usr.firstname', "' '", 'usr.lastname');
$cselect = 'SELECT COUNT(stu.id) ';
$select = "SELECT crs.id as idx,\n cls.idnumber as idnumber,\n cls.id as classid,\n crs.name as coursename,\n crs.id as courseid,\n stu.completetime as datecomplete,\n stu.completestatusid as completestatus,\n stu.grade as classgrade,\n stu.userid as userid,\n cur.name as curriculumname,\n curcrs.frequency as frequency,\n curcrs.timeperiod as timeperiod ";
$tables = "FROM " . $CURMAN->db->prefix_table(CURASSTABLE) . " curass\n INNER JOIN " . $CURMAN->db->prefix_table(CURTABLE) . " cur ON cur.id = curass.curriculumid\n INNER JOIN " . $CURMAN->db->prefix_table(CURCRSTABLE) . " curcrs ON curcrs.curriculumid = cur.id\n INNER JOIN " . $CURMAN->db->prefix_table(CRSTABLE) . " crs ON crs.id = curcrs.courseid\n LEFT JOIN " . $CURMAN->db->prefix_table(CLSTABLE) . " cls ON cls.courseid = crs.id\n INNER JOIN " . $CURMAN->db->prefix_table(STUTABLE) . " stu ON stu.classid = cls.id AND stu.userid = {$this->usrid}\n ";
$where = "WHERE curass.userid = '{$this->usrid}' ";
/// Count the total number of results.
$sql = $cselect . $tables . $where;
$this->numrecs = $CURMAN->db->count_records_sql($sql);
/// Get the current 'page' of results.
$sql = $select . $tables . $where;
$this->data = $CURMAN->db->get_records_sql($sql);
/// Add non-DB info to the records for display.
if (!empty($this->data)) {
$this->_maxexams = 0;
$cids = '';
foreach ($this->data as $di => $datum) {
$cids .= (empty($cids) ? '(' : ',') . $datum->courseid;
if (!is_null($datum->classid) && !is_null($datum->userid)) {
/// Get any course element id's.
$select = 'SELECT cc.id, cc.courseid, cc.idnumber as elementname, ccg.id as ccgid,' . 'ccg.grade as grade,ccg.timegraded as timegraded ' . 'FROM ' . $CURMAN->db->prefix_table(CRSCOMPTABLE) . ' cc ' . 'LEFT JOIN ' . $CURMAN->db->prefix_table(CLSGRTABLE) . ' ccg ' . 'ON ccg.classid = ' . $datum->classid . ' AND ccg.userid = ' . $datum->userid . ' ' . 'AND ccg.completionid = cc.id ' . 'WHERE cc.courseid = ' . $datum->courseid . ' ' . 'ORDER BY id ASC';
} else {
/// Get any course element id's.
$select = 'SELECT cc.id, cc.courseid, cc.idnumber as elementname ' . 'FROM ' . $CURMAN->db->prefix_table(CRSCOMPTABLE) . ' cc ' . 'WHERE cc.courseid = ' . $datum->courseid . ' ' . 'ORDER BY id ASC';
}
if (!($ccs = get_records_sql($select))) {
$ccs = array();
}
$this->_maxexams = MAX(count($ccs), $this->_maxexams);
$i = 1;
foreach ($ccs as $cc) {
$datum->{'ccid' . $i} = $cc->id;
$datum->{'ccelementname' . $i} = $cc->elementname;
$datum->{'ccgid' . $i} = isset($cc->ccgid) ? $cc->ccgid : '';
$datum->{'ccgrade' . $i} = isset($cc->grade) ? $cc->grade : '';
$datum->{'cctimegraded' . $i} = isset($cc->timegraded) ? !empty($cc->timegraded) ? date('M j, Y', $cc->timegraded) : '-' : '';
$i++;
}
if (!empty($datum->datecomplete) && !empty($datum->frequency)) {
switch ($datum->timeperiod) {
case 'year':
$datum->dnextdue = cm_timedelta($datum->datecomplete, $datum->frequency);
break;
case 'month':
$datum->dnextdue = cm_timedelta($datum->datecomplete, 0, $datum->frequency);
break;
case 'week':
$datum->dnextdue = cm_timedelta($datum->datecomplete, 0, 0, $datum->frequency);
break;
case 'day':
$datum->dnextdue = cm_timedelta($datum->datecomplete, 0, 0, 0, $datum->frequency);
break;
default:
$datum->dnextdue = 0;
break;
}
$datum->nextdue = !empty($datum->dnextdue) ? date('M j, Y', $datum->dnextdue) : '-';
} else {
$datum->nextdue = '-';
}
if (!empty($datum->datecomplete)) {
$datum->datecomplete = !empty($datum->datecomplete) ? date('M j, Y', $datum->datecomplete) : '-';
} else {
$datum->datecomplete = '-';
}
switch ($datum->completestatus) {
case STUSTATUS_FAILED:
if (!$download) {
$datum->completestatus = '<span style="color: red;">' . get_string('failed', 'block_curr_admin') . '</span>';
} else {
$datum->completestatus = get_string('failed', 'block_curr_admin');
}
break;
case STUSTATUS_PASSED:
if (!$download) {
$datum->completestatus = '<span style="color: green;">' . get_string('complete', 'block_curr_admin') . '</span>';
} else {
$datum->completestatus = get_string('complete', 'block_curr_admin');
}
break;
case STUSTATUS_NOTCOMPLETE:
default:
$timenow = time();
if (!empty($datum->clsend) && $datum->clsend > $timenow && $datum->clsend - $timenow < 90 * 24 * 60 * 60) {
/// If the course is due within 90 days, colour code it accordingly.
if (!$download) {
$datum->completestatus = '<span style="color: red;">' . get_string('incomplete', 'block_curr_admin') . '</span>';
} else {
$datum->completestatus = get_string('incomplete', 'block_curr_admin');
}
} else {
if (!empty($datum->clsend) && $datum->clsend < $timenow) {
/// If the course is overdue, then display by how many days.
$timedelta = $timenow - $datum->clsend;
$timeday = 24 * 60 * 60;
// Number of seconds in a day.
if ($timedelta < $timeday) {
if (!$download) {
$datum->completestatus = '<span style="colur: red;">' . get_string('overdue_zero', 'block_curr_admin') . '</span>';
} else {
$datum->completestatus = get_string('overdue_zero', 'block_curr_admin');
}
} else {
$daysover = floor($timedelta / $timeday);
if (!$download) {
$datum->completestatus = '<span style="color: red;">' . get_string('overdue', 'block_curr_admin') . '' . $daysover . ($daysover > 1 ? get_string('duration_days', 'block_curr_admin') : get_string('duration_day', 'block_curr_admin')) . '</span>';
} else {
$datum->completestatus = get_string('overdue', 'block_curr_admin') . $daysover . ($daysover > 1 ? get_string('duration_days', 'block_curr_admin') : get_string('duration_day', 'block_curr_admin'));
}
}
} else {
if (!$download) {
$datum->completestatus = '<span style="color: red;">' . get_string('incomplete', 'block_curr_admin') . '</span>';
} else {
$datum->completestatus = get_string('incomplete', 'block_curr_admin');
}
}
}
break;
}
$this->data[$di] = $datum;
}
$cids .= !empty($cids) ? ')' : '';
}
if (!empty($cids)) {
$select = "SELECT crs.id as courseid,\n crs.name as coursename,\n cur.name as curriculumname,\n curcrs.frequency as frequency,\n curcrs.timeperiod as timeperiod ";
$tables = "FROM " . $CURMAN->db->prefix_table(CURASSTABLE) . " curass\n INNER JOIN " . $CURMAN->db->prefix_table(CURTABLE) . " cur ON cur.id = curass.curriculumid\n INNER JOIN " . $CURMAN->db->prefix_table(CURCRSTABLE) . " curcrs ON curcrs.curriculumid = cur.id\n INNER JOIN " . $CURMAN->db->prefix_table(CRSTABLE) . " crs ON crs.id = curcrs.courseid\n ";
$where = "WHERE curass.userid = '{$this->usrid}' AND crs.id NOT IN {$cids} ";
/// Get the current 'page' of results.
$sql = $select . $tables . $where;
$this->data2 = $CURMAN->db->get_records_sql($sql);
}
// $select = "SELECT crs.id as courseid,
// crs.name as coursename,
// cls.idnumber as idnumber,
// cls.id as classid,
// stu.completetime as datecomplete,
// stu.completestatusid as completestatus,
// stu.grade as classgrade,
// stu.userid as userid
// ";
// $tables = "FROM " . $CURMAN->db->prefix_table(STUTABLE) . " stu
// INNER JOIN " . $CURMAN->db->prefix_table(CLSTABLE) . " cls ON cls.id = stu.classid
// INNER JOIN " . $CURMAN->db->prefix_table(CRSTABLE) . " crs ON crs.id = cls.courseid
// ";
//
// $where = "WHERE stu.userid = '{$this->usrid}' ";
// if (!empty($cids)) {
// $where .= "AND crs.id NOT IN $cids ";
// }
//
// /// Get the current 'page' of results.
// $sql = $select . $tables . $where;
// $this->data3 = $CURMAN->db->get_records_sql($sql);
//print_object($sql);
}
