TimeKeeper/PreHeaderFunctions.php

<?php

/**
 * Report exports for TimeKeeper:
 *  - ExcelFunctions::GeneratePdf()     question report as HTML or PDF
 *  - ExcelFunctions::GenerateExcel()   worksheet of random maths questions
 *  - Summary::GenerateSummary()        kiosk-style score summary
 *
 * Changes from the current repo version:
 *  - ?user= is validated once, up front, for every entry point (GenerateSummary
 *    had no validation at all, and GeneratePdf only validated inside the pdf
 *    branch, after the SQL had already run with the raw value)
 *  - all SQL escapes the user and uses MPREFIX instead of `e107.e107_`
 *  - everything taken from the database or the URL is HTML-escaped (answers
 *    are typed by the students, so they are untrusted)
 *  - the correct/incorrect icons are text symbols: the <img> paths were
 *    relative, so they broke once the PDF source moved to the temp directory
 *  - PDF: wkhtmltopdf runs with local file access disabled, the PDF is only
 *    sent if it was actually produced, and the temp files are always removed
 *  - removed the debug "Memory use / Generation time" line and the early
 *    $sql->close() (it closed the shared connection before e107 finished)
 *  - Excel: the question loop started at row 0 (invalid, and the formulas
 *    pointed at row 0), multiplication passed an array to rand() (fatal on
 *    PHP 8), buildQuestion() looped forever if no subject was ticked, and the
 *    question count was unbounded
 *  - whereDate()/range handling lives in one trait instead of two copies
 *  - removed the dead, broken coverPage() in ExcelFunctions (COUNT(_id))
 */
trait TkReportRequest {

    /** Validated ?user= value, or null when missing or not a plain username. */
    private function requestUser() {
        $user = filter_input(INPUT_GET, 'user');
        if (is_string($user) && preg_match('/^[A-Za-z0-9_.-]{1,50}$/', $user)) {
            return $user;
        }
        return null;
    }

    private function rangeWindows() {
        $end = mktime(23, 59, 59, date('n'), date('j'));
        return [
            'today' => ['start' => strtotime('- 24 hours', $end), 'end' => $end],
            'yesterday' => ['start' => strtotime('- 48 hours', $end), 'end' => strtotime('- 24 hours', $end)],
            'pastWeek' => ['start' => strtotime('- 7 days', $end), 'end' => $end],
            'pastMonth' => ['start' => strtotime('- 1 month', $end), 'end' => $end],
            'pastQuarter' => ['start' => strtotime('- 3 month', $end), 'end' => $end],
            'pastYear' => ['start' => strtotime('- 1 year', $end), 'end' => $end],
        ];
    }

    /** The requested ?range=, restricted to the known names ('today' otherwise). */
    private function currentRange() {
        $range = filter_input(INPUT_GET, 'range');
        $windows = $this->rangeWindows();
        return (is_string($range) && isset($windows[$range])) ? $range : 'today';
    }

    private function whereDate() {
        $windows = $this->rangeWindows();
        $window = $windows[$this->currentRange()];
        return 'AND answereddatestamp>=' . (int) $window['start'] . ' AND answereddatestamp<=' . (int) $window['end'];
    }

    private function esc($value) {
        return htmlspecialchars((string) $value, ENT_QUOTES, 'UTF-8');
    }
}

class ExcelFunctions {

    use TkReportRequest;

    public function GeneratePdf() {
        $user = $this->requestUser();
        if ($user === null) {
            return;
        }

        $output = filter_input(INPUT_GET, 'output');
        if ($output !== 'html' && $output !== 'pdf') {
            return;
        }

        $sql = e107::getDb();
        $text = '<!DOCTYPE html><html><head><meta charset="utf-8"></head><body><h1>TimeKeeper Report</h1>'
                . $this->questionsTable($sql, $user, $this->whereDate())
                . '</body></html>';

        if ($output === 'html') {
            echo $text;
            exit;
        }

        $base = tempnam(sys_get_temp_dir(), 'tk');
        if ($base === false) {
            return;
        }
        $html = $base . '.html';
        $pdf = $base . '.pdf';

        file_put_contents($html, $text);
        exec('wkhtmltopdf --quiet --disable-local-file-access ' . escapeshellarg($html) . ' ' . escapeshellarg($pdf) . ' 2>&1');

        if (is_file($pdf) && filesize($pdf) > 0) {
            header('Content-Type: application/pdf');
            header('Content-Length: ' . filesize($pdf));
            header('Content-Disposition: inline; filename="timekeeper-report.pdf"');
            readfile($pdf);
        } else {
            header('HTTP/1.1 500 Internal Server Error');
            echo 'The PDF could not be generated.';
        }

        @unlink($html);
        @unlink($pdf);
        @unlink($base);
        exit;
    }

    private function questionsTable($sql, $user, $whereDate) {
        $sql->gen("SELECT * FROM `" . MPREFIX . "tkReport` WHERE username='" . $sql->escape($user) . "' $whereDate ORDER BY subject, answereddatestamp");

        $tableHead = '<table border="1" cellspacing="3" cellpadding="2"><tr>'
                . '<td width="1295">Question</td>'
                . '<td width="75">User Answer</td>'
                . '<td width="75">Correct Answer</td>'
                . '<td width="25"></td>'
                . '</tr>';

        $head = null;
        $text = '';

        while ($row = $sql->fetch()) {
            if ($row['subject'] !== $head) {
                if ($head !== null) {
                    $text .= '</table>';
                }
                $head = $row['subject'];
                $text .= '<h3>' . $this->esc(ucfirst($head)) . '</h3>' . $tableHead;
            }
            $text .= '<tr><td>' . $this->esc($row['question']) . '</td><td>' . $this->esc($row['userAnswer']) . '</td><td>' . $this->esc($row['answer']) . '</td><td>' . $this->correctOrNot($row['correct']) . '</td></tr>';
        }

        if ($head === null) {
            return '<p>No questions were answered in this period.</p>';
        }

        return $text . '</table>';
    }

    private function correctOrNot($state) {
        if ($state) {
            return '<span style="color:green">&#10004;</span>';
        }
        return '<span style="color:red">&#10008;</span>';
    }

    public function GenerateExcel() {
        $lib = __DIR__ . '/lib/PHPExcel.phar';
        if (!is_file($lib)) {
            e107::getMessage()->addError('The Excel library (lib/PHPExcel.phar) is not installed.');
            return;
        }
        require_once('phar://' . $lib . '/PHPExcel.php');

        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("Gregory J Huxley")->setLastModifiedBy("TimeKeeper");

        $this->buildSheet($objPHPExcel);

        $this->setHeader();

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->setPreCalculateFormulas(true);
        $objWriter->save('php://output');
        exit;
    }

    private function buildSheet($objPHPExcel) {
        $numberOfQuestions = (int) filter_input(INPUT_POST, 'numberOfQuestions');
        if ($numberOfQuestions < 1) {
            $numberOfQuestions = 5;
        }
        $numberOfQuestions = min($numberOfQuestions, 500);

        $sheet = $objPHPExcel->setActiveSheetIndex(0);
        $sheet->setTitle("Questions");

        // Rows are 1-based, and the answer-check formulas refer to row $i.
        for ($i = 1; $i <= $numberOfQuestions; $i++) {
            $counter = 0;
            foreach ($this->buildQuestion($i) as $value) {
                $sheet->setCellValueByColumnAndRow($counter, $i, $value);
                $counter++;
            }
        }
    }

    private function buildQuestion($i) {//http://localhost/e107_plugins/timekeeper/timekeeper.php?numberOfQuestions=20&addition=0&addition__switch=0&subtraction=0&multiplication=0&division=0&GetExcel=Generate+Sheet
        $subjects = ['addition', 'subtraction', 'multiplication', 'division'];

        $enabled = [];
        foreach ($subjects as $subject) {
            if (filter_has_var(INPUT_POST, $subject . '__switch')) {
                $enabled[] = $subject;
            }
        }
        if (empty($enabled)) {
            $enabled = $subjects; // nothing ticked: use all of them rather than looping forever
        }

        switch ($enabled[array_rand($enabled)]) {
            case 'addition':
                return $this->addition($i);
            case 'subtraction':
                return $this->subtraction($i);
            case 'multiplication':
                return $this->multiplication($i);
            default:
                return $this->division($i);
        }
    }

    private function addition($i) {
        $question = [rand(1, 1000)];
        $extra = rand(1, 3);
        for ($int = 1; $int <= $extra; $int++) {
            array_push($question, '+');
            array_push($question, rand(1, 1000));
        }
        array_push($question, '=');
        array_push($question, $this->checkFormula($i, $question));
        return $question;
    }

    private function subtraction($i) {
        $question = [rand(100, 1000)];
        $extra = rand(1, 2);
        for ($int = 1; $int <= $extra; $int++) {
            array_push($question, '-');
            array_push($question, rand(1, (int) ($question[0] / 2)));
        }
        array_push($question, '=');
        array_push($question, $this->checkFormula($i, $question));
        return $question;
    }

    private function multiplication($i) {
        $startNumber = rand(1, 99);
        $question = [$startNumber];
        array_push($question, '*');
        array_push($question, rand(1, $startNumber));
        array_push($question, '=');
        array_push($question, $this->checkFormula($i, $question));
        return $question;
    }

    private function division($i) {
        $startNumber = rand(1, 50);
        $endNumber = rand(1, 5);
        $question = [$startNumber * $endNumber];

        array_push($question, '/');
        array_push($question, $endNumber);

        array_push($question, '=');
        array_push($question, $this->checkFormula($i, $question));
        return $question;
    }

    private function checkFormula($i, &$question) {
        $letters = ['A', 'C', 'E', 'G', 'I'];
        for ($int = count($question); $int <= 8; $int++) {
            array_push($question, '');
        }
        $formula = '(';
        $counter = 0;
        foreach ($question as &$value) {
            if ($value == '+' || $value == '-' || $value == '*' || $value == '/') {
                $formula .= $value;
            } elseif ($value == '=') {
                $formula .= ')=' . $letters[$counter] . $i;
                break;
            } else {
                $formula .= $letters[$counter] . $i;
                $counter++;
            }
        }
        unset($value);
        return '=IF(' . substr(strrchr($formula, "="), 1) . '="","",IF(' . $formula . ',"Correct","Wrong"))'; // =IF(L1="","",IF((A1*C1*E1*H1*J1)=L1,"Correct","Wrong"))
    }

    private function setHeader() {
        // Redirect output to a client's web browser (Excel5)
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Questions.xls"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
    }
}

class Summary {

    use TkReportRequest;

    public function GenerateSummary() {
        $user = $this->requestUser();
        if ($user === null) {
            return;
        }

        $sql = e107::getDb();

        echo '<!DOCTYPE html><html><head><meta charset="utf-8"></head>'
        . '<body style="background-color:#31363b;"><div style="font-size:250%;color:#fcfcfc;">'
        . $this->coverPage($sql, $user, $this->whereDate())
        . '</div></body></html>';
        exit;
    }

    private function coverPage($sql, $user, $whereDate) {
        $sql->gen("SELECT COUNT(id) AS cntr, CAST((SUM(correct)/COUNT(id)*100) AS DECIMAL(5,2)) AS percent
            FROM `" . MPREFIX . "tkReport` WHERE username = '" . $sql->escape($user) . "'
            $whereDate");

        $row = $sql->fetch();
        $count = is_array($row) ? (int) $row['cntr'] : 0;
        $percent = (is_array($row) && $row['percent'] !== null) ? $row['percent'] : 0;
        $range = $this->esc($this->currentRange());

        if ($range === 'today') {
            return 'Questions ' . $range . '<br/><br/>' . $count . '<br/>' . $percent . '% correct.';
        }
        return 'Questions ' . $range . ' ' . $count . '<br/><br/>' . $percent . '% correct.';
    }
}