TimeKeeper/PreHeaderFunctions.php

<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of ExcelFunctions
 *
 * @author gregory
 */
class ExcelFunctions {

    public function GeneratePdf() {
        $user = filter_input(INPUT_GET, 'user');
        if (!is_string($user) || !preg_match('/^[A-Za-z0-9_-]{1,50}$/', $user)) {
            return;
        }  // before any query

        $whereDate = $this->whereDate();
        if ($user == "") {
            return;
        }
        $text = '<h1>TimeKeeper Report</h1>';
        $sql = e107::getDb();

        // $text .= $this->coverPage($sql,$user,$whereDate);

        $text .= $this->questionsTable($sql, $user, $whereDate);

        $text .= "\nMemory use: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MiB.\nGeneration time " . (microtime(true) - $_SERVER["REQUEST_TIME_FLOAT"]) . ' seconds.';

        $sql->close();

        $output = filter_input(INPUT_GET, 'output');
        if ($output == 'html') {
            echo $text;
            exit;
        } elseif ($output == 'pdf') {
            // pdf branch:
            $base = tempnam(sys_get_temp_dir(), 'tk');
            $html = $base . '.html';
            $pdf = $base . '.pdf';
            file_put_contents($html, $text);
            exec('wkhtmltopdf ' . escapeshellarg($html) . ' ' . escapeshellarg($pdf));
            header('Content-Type: application/pdf');
            header('Content-Length: ' . filesize($pdf));
            readfile($pdf);
            unlink($html);
            unlink($pdf);
            unlink($base);
            exit;
        }
    }

    private function coverPage($sql, $user, $whereDate) {
        if (false) {
            $sql->gen("SELECT `subject`,
                ANY_VALUE(`percent`) as percent, ANY_VALUE(`quantity`) as quantity
            FROM e107.`e107_tkStats` WHERE `username` = '$user' GROUP BY (`subject`);");

            $text = '<table border="1" cellspacing="3" cellpadding="2">'
                    . '<tr><td style="text-align:left">Subject</td><td>Score</td><td>Questions Remaining</td></tr>';
            while ($row = $sql->fetch()) {
                $text .= '<tr><td style="text-align:left">' . ucfirst($row['subject']) . '</td><td>' . $row['percent'] . '%</td><td>' . (1000 - $row['quantity']) . '</td></tr>';
            }
        } else {
            $sql->gen("SELECT COUNT(_id) as cntr, CAST((SUM(correct)/COUNT(id)*100) as DECIMAL(5,2)) as percent
            FROM e107.`e107_tkReport` WHERE username = '$user' 
            $whereDate");
            while ($row = $sql->fetch()) {
                if (filter_input(INPUT_GET, 'range') == "" || filter_input(INPUT_GET, 'range') == "today") {
                    $text .= '<p>' . $row['cntr'] . ' question(s) have been answered for today with ' . $row['percent'] . '% accuracy.</p>';
                } else {
                    $text .= '<p>' . $row['cntr'] . ' question(s) have been answered for this ' . filter_input(INPUT_GET, 'range') . ' with ' . $row['percent'] . '% accuracy.</p>';
                }
            }
        }
        $text .= '</table>';
        return $text;
    }

    private function questionsTable($sql, $user, $whereDate) {

        $sql->gen("SELECT * FROM e107.e107_tkReport WHERE username='$user' $whereDate ORDER BY subject;");
        $head = "";
        $text = "";

        while ($row = $sql->fetch()) {
            if ($head == "") {
                $head = $row["subject"];
                $text .= '<h3>' . ucfirst($head) . '</h3>'
                        . '<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>';
            } elseif ($head != $row["subject"]) {
                $head = $row["subject"];
                $text .= '</table><h3>' . ucfirst($head) . '</h3><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>';
            }
            $text .= "<tr><td>" . $row["question"] . '</td><td>' . $row["userAnswer"] . '</td><td>' . $row["answer"] . '</td><td>' . $this->correctOrNot($row["correct"]) . '</td></tr>';
        }
        $text .= '</table>';
        return $text;
    }

    private function whereDate() {
        $returnRange = filter_input(INPUT_GET, 'range');
        $unixTimeTodayEnd = mktime(23, 59, 59, date('n'), date('j'));
        $returnValues = ['today' => ['start' => strtotime('- 24 hours', $unixTimeTodayEnd), 'end' => $unixTimeTodayEnd]
            , 'yesterday' => ['start' => strtotime('- 48 hours', $unixTimeTodayEnd), 'end' => strtotime('- 24 hours', $unixTimeTodayEnd)]
            , 'pastWeek' => ['start' => strtotime('- 7 days', $unixTimeTodayEnd), 'end' => $unixTimeTodayEnd]
            , 'pastMonth' => ['start' => strtotime('- 1 month', $unixTimeTodayEnd), 'end' => $unixTimeTodayEnd]
            , 'pastQuarter' => ['start' => strtotime('- 3 month', $unixTimeTodayEnd), 'end' => $unixTimeTodayEnd]
            , 'pastYear' => ['start' => strtotime('- 1 year', $unixTimeTodayEnd), 'end' => $unixTimeTodayEnd]
        ];

        if (!isset($returnValues[$returnRange])) {
            $returnRange = 'today';
        }

        return "AND answereddatestamp>='" . $returnValues[$returnRange]['start'] . "' AND answereddatestamp<='" . $returnValues[$returnRange]['end'] . "'";
    }

    private function correctOrNot($state) {
        if ($state) {
            return '<img src="' . e_IMAGE . 'admin_images/true_16.png"/>';
        } else {
            return '<img src="' . e_IMAGE . 'admin_images/false_16.png"/>';
        }
    }

    public function GenerateExcel() {
        require_once('phar://lib/PHPExcel.phar/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 = filter_input(INPUT_POST, 'numberOfQuestions');
        if (!$numberOfQuestions) {
            $numberOfQuestions = 5;
        }
        $objPHPExcel->getActiveSheet()->setTitle("Questions");
        for ($i = 0; $i <= $numberOfQuestions; $i++) {
            $counter = 0;
            foreach ($this->buildQuestion($i) as $value) {
                $objPHPExcel->setActiveSheetIndex(0)->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
        while (true) {
            switch (rand(1, 4)) {
                case 1:// addition
                    $enabledSubject = filter_has_var(INPUT_POST, 'addition__switch');
                    if ($enabledSubject) {
                        return $this->addition($i);
                    }
                    break;
                case 2:// subtraction
                    $enabledSubject = filter_has_var(INPUT_POST, 'subtraction__switch');
                    if ($enabledSubject) {
                        return $this->subtraction($i);
                    }
                    break;
                case 3:// multiplication
                    $enabledSubject = filter_has_var(INPUT_POST, 'multiplication__switch');
                    if ($enabledSubject) {
                        return $this->multiplication($i);
                    }
                    break;
                case 4:// division
                    $enabledSubject = filter_has_var(INPUT_POST, 'division__switch');
                    if ($enabledSubject) {
                        return $this->division($i);
                    }
                    break;
            }
        }
    }

    private function addition($i) {
        $question = [rand(1, 1000)];
        for ($int = 1; $int <= rand(1, 3); $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)];
        for ($int = 1; $int <= rand(1, 2); $int++) {
            array_push($question, '-');
            array_push($question, rand(1, $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;
        for ($int = 1; $int <= rand(1, 1); $int++) {
            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++;
            }
        }
        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 (Excel2007)
        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
    }
}

/**
 * Description of Summary
 *
 * @author gregory
 */
class Summary {

    public function GenerateSummary() {//base64_encode
        $user = filter_input(INPUT_GET, 'user');
        $whereDate = $this->whereDate();
        if ($user == "") {
            return;
        }

        $sql = e107::getDb();

        $text = '<body style="background-color:#31363b;"><div style="font-size:250%;color:#fcfcfc;">';

        $text .= $this->coverPage($sql, $user, $whereDate);

        //$text .= "\nMemory use: ".(memory_get_peak_usage(true)/1024/1024)." MiB.\nGeneration time " . (microtime(true) - $_SERVER["REQUEST_TIME_FLOAT"]).' seconds.';
        //print_a($whereDate);
        $sql->close();
        echo $text . '</div></body>';
        //$pdf->makePDF([$text, 'e107site', 'Gregory', 'Timekeeper Report', 'Timekeeper Report', 'Timekeeper Report', e_PLUGIN.'/timekeeper/timekeeper.php']);

        exit;
    }

    private function coverPage($sql, $user, $whereDate) {
        if (false) {
            $sql->gen("SELECT MAX(`timekeeper_yearLevel`) as timekeeper_yearLevel, `timekeeper_subject`,
                ANY_VALUE(`timekeeper_percent`) as timekeeper_percent, ANY_VALUE(`timekeeper_quantity`) as timekeeper_quantity
            FROM e107.`e107_timekeeperStats` WHERE `timekeeper_username` = '$user' GROUP BY (`timekeeper_subject`);");

            $text = '<table border="1" cellspacing="3" cellpadding="2">'
                    . '<tr><td style="text-align:left">Year</td><td style="text-align:left">Subject</td><td>Score</td><td>Questions Remaining</td></tr>';
            while ($row = $sql->fetch()) {
                $text .= '<tr><td>' . $row['timekeeper_yearLevel'] . '</td><td style="text-align:left">' . ucfirst($row['timekeeper_subject']) . '</td><td>' . $row['timekeeper_percent'] . '%</td><td>' . (1000 - $row['timekeeper_quantity']) . '</td></tr>';
            }
        } else {
            $sql->gen("SELECT COUNT(id) as cntr, CAST((SUM(correct)/COUNT(id)*100) as DECIMAL(5,2)) as percent
            FROM e107.`e107_tkReport` WHERE username = '$user' 
            $whereDate");
            while ($row = $sql->fetch()) {
                if (filter_input(INPUT_GET, 'range') == "" || filter_input(INPUT_GET, 'range') == "today") {
                    $text .= 'Questions ' . filter_input(INPUT_GET, 'range') . '<br/><br/>' . $row['cntr'] . '<br/>' . $row['percent'] . '% correct.';
                } else {
                    $text .= 'Questions ' . filter_input(INPUT_GET, 'range') . ' ' . $row['cntr'] . '<br/><br/>' . $row['percent'] . '% correct.';
                }
            }
        }
        $text .= '</table>';
        return $text;
    }

    private function whereDate() {
        $returnRange = filter_input(INPUT_GET, 'range');
        $unixTimeTodayEnd = mktime(23, 59, 59, date('n'), date('j'));
        $returnValues = ['today' => ['start' => strtotime('- 24 hours', $unixTimeTodayEnd), 'end' => $unixTimeTodayEnd]
            , 'yesterday' => ['start' => strtotime('- 48 hours', $unixTimeTodayEnd), 'end' => strtotime('- 24 hours', $unixTimeTodayEnd)]
            , 'pastWeek' => ['start' => strtotime('- 7 days', $unixTimeTodayEnd), 'end' => $unixTimeTodayEnd]
            , 'pastMonth' => ['start' => strtotime('- 1 month', $unixTimeTodayEnd), 'end' => $unixTimeTodayEnd]
            , 'pastQuarter' => ['start' => strtotime('- 3 month', $unixTimeTodayEnd), 'end' => $unixTimeTodayEnd]
            , 'pastYear' => ['start' => strtotime('- 1 year', $unixTimeTodayEnd), 'end' => $unixTimeTodayEnd]
        ];

        if (!isset($returnValues[$returnRange])) {
            $returnRange = 'today';
        }

        return "AND answereddatestamp>='" . $returnValues[$returnRange]['start'] . "' AND answereddatestamp<='" . $returnValues[$returnRange]['end'] . "'";
    }
}