TimeKeeper/e_cron.php

<?php

/**
 * e107 website system
 *
 * Copyright (C) 2008-2016 e107 Inc (e107.org)
 * Released under the terms and conditions of the
 * GNU General Public License (http://www.gnu.org/licenses/gpl.txt)
 *
 * Daily TimeKeeper report emails.
 *
 * Changes from the current repo version:
 *  - the shared DB connection is no longer closed ($sql->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 <img> 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 '<span style="color:#33cc33" title="Correct">&#10004;</span>';
        }
        return '<span style="color:#ff4444" title="Incorrect">&#10008;</span>';
    }

    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 = '<!DOCTYPE html><html><head><meta charset="utf-8"><style>' . $this->css . '</style></head><body>';

        if ($coverPageData['Questions'] === 0) {
            $emailTemplate .= '<div id="sum">' . $user . ' did not answer any questions yesterday.</div></body></html>';
            return $emailTemplate;
        }

        $minutes = round($questionsPageData['activeSeconds'] / 60, 1);

        $emailTemplate .= '<div id="sum">' . $user . '\'s overall score for yesterday is ' . $this->esc($coverPageData['TotalPercent']) . '%.</div>'
                . '<div id="styTi">About ' . $this->esc($minutes) . ' minutes were spent studying yesterday (breaks over ' . (int) ($this->breakSeconds / 60) . ' minutes not counted).</div>'
                . '<br/><table id="coverP"><tr><th>Subject</th><th>Count</th><th>Percentage</th></tr>';

        foreach ($coverPageData['Scores'] as $score) {
            $emailTemplate .= '<tr><td>' . $this->esc(ucfirst($score['subject'])) . '</td><td>' . (int) $score['total'] . '</td><td>' . $this->esc($score['percent']) . '%</td></tr>';
        }

        $emailTemplate .= '</table><br/><table id="questions"><tr><th>Question</th><th>Answered</th><th>Correct?</th></tr>';

        foreach ($questionsPageData['questions'] as $subject => $subjectQuestions) {
            $emailTemplate .= '<tr><td colspan="3" style="text-align: center; font-size: 16px; background-color: #333; color: #ff9900;">' . $this->esc(ucfirst($subject)) . '</td></tr>';

            foreach ($subjectQuestions as $question) {
                $emailTemplate .= '<tr><td>' . $this->esc($question['question']) . '</td><td>' . $this->esc($question['userAnswer']) . '</td><td>' . $this->correctOrNot($question['correct']) . '</td></tr>';
            }
        }

        $emailTemplate .= '</table></body></html>';

        return $emailTemplate;
    }
}