close()) between the
* two students' reports, which would likely have broken the second one
* - reporting window is explicit integer timestamps (yesterday, midnight to
* midnight; see $windowStart/$windowEnd). The old FROM_UNIXTIME(col) >=
* DATE_ADD(CURDATE(), INTERVAL -1 DAY) condition had no upper bound, could
* overlap the previous email, and couldn't use an index
* - "minutes studied" is now the sum of the gaps between answers, ignoring
* breaks over $breakSeconds. It used last-minus-first, which counted a
* morning and an evening session as ten hours, and on a day with no
* answers produced about 166 million minutes
* - the overall percentage is correct answers / questions, not the average
* of the per-subject percentages (1 question at 100% outweighed 50 at 50%)
* - questions, answers, subjects and names are HTML-escaped in the email
* - correct/incorrect are text symbols (the ![]()
paths were relative, which
* never worked in an email client)
* - names and addresses are in one config block instead of repeated; the kid
* is addressed by their own name
* - `e107.e107_tkReport` -> MPREFIX, user escaped
*/
class timekeeper_cron {
// Who gets which report. The parent address receives every report.
private $parentEmail = 'gregory@pfip.au';
private $parentName = 'Gregory';
private $children = [
'elliot' => 'elliot@pfip.au',
'silas' => 'silas@pfip.au',
];
// Reporting window (strtotime strings). Default: yesterday, midnight to midnight.
// If you send the email in the evening and want "today so far", use
// 'today midnight' and 'tomorrow midnight' and adjust the wording below.
private $windowStart = 'yesterday midnight';
private $windowEnd = 'today midnight';
// A gap between two answers longer than this (seconds) counts as a break.
private $breakSeconds = 600;
function config() { // Setup
$cron = array();
$cron[] = array(
'name' => "Send Reports", // Displayed in admin area. .
'function' => "sendReportsEmail", // Name of the function which is defined below.
'category' => 'mail', // Choose between: mail, user, content, notify, or backup
'description' => "Send Timekeeper reports out to receivers." // Displayed in admin area.
);
return $cron;
}
function sendReportsEmail() {
require_once(e_HANDLER . "mail.php");
foreach ($this->children as $user => $childEmail) {
$mailContent = $this->generateEmail($user);
$eml = array(
'subject' => $mailContent['subject'],
'body' => $mailContent['message'],
'sender_email' => 'service@pfip.au'
);
e107::getEmail()->sendEmail($mailContent['recipients'], $this->parentName, $eml);
e107::getEmail()->sendEmail($childEmail, ucfirst($user), $eml);
}
}
public function generateEmail($user) {
$returnArray['subject'] = 'TimeKeeper Report for ' . $user;
$returnArray['recipients'] = $this->parentEmail;
$sql = e107::getDb();
$start = (int) strtotime($this->windowStart);
$end = (int) strtotime($this->windowEnd);
$coverPageData = $this->coverPage($sql, $user, $start, $end);
$questionsPageData = $this->questionsTable($sql, $user, $start, $end);
$returnArray['message'] = $this->template($coverPageData, $questionsPageData);
return $returnArray;
}
private function coverPage($sql, $user, $start, $end) {
$sql->gen("SELECT `subject`, COUNT(`correct`) AS total, SUM(`correct`) AS num_correct,
CAST((SUM(correct)/COUNT(id)*100) AS DECIMAL(5,2)) AS percent
FROM `" . MPREFIX . "tkReport`
WHERE `answereddatestamp` >= $start AND `answereddatestamp` < $end
AND `username` = '" . $sql->escape($user) . "'
GROUP BY `subject` ORDER BY `subject`");
$scores = [];
$questions = 0;
$correct = 0;
while ($row = $sql->fetch()) {
$scores[] = ['subject' => $row['subject'], 'total' => (int) $row['total'], 'percent' => $row['percent']];
$questions += (int) $row['total'];
$correct += (int) $row['num_correct'];
}
$totalPercent = ($questions > 0) ? round($correct / $questions * 100, 1) : 0;
return ['user' => $user, 'TotalPercent' => $totalPercent, 'Questions' => $questions, 'Scores' => $scores];
}
private function questionsTable($sql, $user, $start, $end) {
$sql->gen("SELECT `question`, `answereddatestamp`, `userAnswer`, `correct`, `subject`
FROM `" . MPREFIX . "tkReport`
WHERE username = '" . $sql->escape($user) . "'
AND `answereddatestamp` >= $start AND `answereddatestamp` < $end
ORDER BY subject, answereddatestamp");
$questionsData = ['questions' => []];
$times = [];
while ($row = $sql->fetch()) {
$questionsData['questions'][$row['subject']][] = [
'question' => $row['question'],
'userAnswer' => $row['userAnswer'],
'correct' => $row['correct'],
];
$times[] = (int) $row['answereddatestamp'];
}
// Active study time: gaps between answers, ignoring breaks.
sort($times);
$active = 0;
for ($i = 1; $i < count($times); $i++) {
$active += min($times[$i] - $times[$i - 1], $this->breakSeconds);
}
$questionsData['activeSeconds'] = $active;
return $questionsData;
}
private function esc($value) {
return htmlspecialchars((string) $value, ENT_QUOTES, 'UTF-8');
}
private function correctOrNot($state) {
if ($state) {
return '✔';
}
return '✘';
}
private $css = 'body {
background-color: #1a1a1a;
color: #ffffff;
font-family: Arial, sans-serif;
}
#sum {
font-size: 32px;
text-align: center;
color: #ff9900;
}
#styTi {
font-size: 14px;
text-align: center;
color: #cccccc;
}
#coverP, #questions {
font-family: Arial, sans-serif;
border-collapse: collapse;
width: 100%;
margin-top: 20px;
}
#coverP td, #coverP th, #questions td, #questions th {
border: 1px solid #444;
padding: 12px;
text-align: left;
}
#coverP tr:nth-child(even), #questions tr:nth-child(even) {
background-color: #2a2a2a;
}
#coverP tr:hover, #questions tr:hover {
background-color: #333;
}
#coverP th, #questions th {
padding-top: 12px;
padding-bottom: 12px;
text-align: left;
background-color: #ff9900;
color: #1a1a1a;
font-weight: bold;
}
#coverP td, #questions td {
color: #ffffff;
}';
private function template($coverPageData, $questionsPageData) {
$user = $this->esc(ucfirst($coverPageData['user']));
$emailTemplate = '';
if ($coverPageData['Questions'] === 0) {
$emailTemplate .= '' . $user . ' did not answer any questions yesterday.
';
return $emailTemplate;
}
$minutes = round($questionsPageData['activeSeconds'] / 60, 1);
$emailTemplate .= '' . $user . '\'s overall score for yesterday is ' . $this->esc($coverPageData['TotalPercent']) . '%.
'
. 'About ' . $this->esc($minutes) . ' minutes were spent studying yesterday (breaks over ' . (int) ($this->breakSeconds / 60) . ' minutes not counted).
'
. '
| Subject | Count | Percentage |
|---|
';
foreach ($coverPageData['Scores'] as $score) {
$emailTemplate .= '| ' . $this->esc(ucfirst($score['subject'])) . ' | ' . (int) $score['total'] . ' | ' . $this->esc($score['percent']) . '% |
';
}
$emailTemplate .= '
| Question | Answered | Correct? |
|---|
';
foreach ($questionsPageData['questions'] as $subject => $subjectQuestions) {
$emailTemplate .= '| ' . $this->esc(ucfirst($subject)) . ' |
';
foreach ($subjectQuestions as $question) {
$emailTemplate .= '| ' . $this->esc($question['question']) . ' | ' . $this->esc($question['userAnswer']) . ' | ' . $this->correctOrNot($question['correct']) . ' |
';
}
}
$emailTemplate .= '