1<?php
2/* Copyright (c) 1998-2013 ILIAS open source, Extended GPL, see docs/LICENSE */
3
4define("SCORE_LAST_PASS", 0);
5define("SCORE_BEST_PASS", 1);
6
7/**
8 * @author		Björn Heyser <bheyser@databay.de>
9 * @version		$Id$
10 *
11 * @package     Modules/Test
12 */
13class DBUpdateTestResultCalculator
14{
15    /**
16     * type setting value for fixed question set
17     */
18    const QUESTION_SET_TYPE_FIXED = 'FIXED_QUEST_SET';
19
20    /**
21     * type setting value for random question set
22     */
23    const QUESTION_SET_TYPE_RANDOM = 'RANDOM_QUEST_SET';
24
25    /**
26     * type setting value for dynamic question set (continues testing mode)
27     */
28    const QUESTION_SET_TYPE_DYNAMIC = 'DYNAMIC_QUEST_SET';
29
30    public static function _updateTestPassResults($active_id, $pass, $obligationsEnabled, $questionSetType, $objId)
31    {
32        global $ilDB;
33
34        $data = self::_getQuestionCountAndPointsForPassOfParticipant($active_id, $pass, $questionSetType);
35        $time = self::_getWorkingTimeOfParticipantForPass($active_id, $pass);
36
37        // update test pass results
38
39        $result = $ilDB->queryF(
40            "
41			SELECT		SUM(points) reachedpoints,
42						SUM(hint_count) hint_count,
43						SUM(hint_points) hint_points,
44						COUNT(DISTINCT(question_fi)) answeredquestions
45			FROM		tst_test_result
46			WHERE		active_fi = %s
47			AND			pass = %s
48			",
49            array('integer','integer'),
50            array($active_id, $pass)
51        );
52
53        if ($result->numRows() > 0) {
54            if ($obligationsEnabled) {
55                $query = '
56					SELECT		count(*) cnt,
57								min( answered ) answ
58					FROM		tst_test_question
59					INNER JOIN	tst_active
60					ON			active_id = %s
61					AND			tst_test_question.test_fi = tst_active.test_fi
62					LEFT JOIN	tst_test_result
63					ON			tst_test_result.active_fi = %s
64					AND			tst_test_result.pass = %s
65					AND			tst_test_question.question_fi = tst_test_result.question_fi
66					WHERE		obligatory = 1';
67
68                $result_obligatory = $ilDB->queryF(
69                    $query,
70                    array('integer','integer','integer'),
71                    array($active_id, $active_id, $pass)
72                );
73
74                $row_obligatory = $ilDB->fetchAssoc($result_obligatory);
75
76                if ($row_obligatory['cnt'] == 0) {
77                    $obligations_answered = 1;
78                } else {
79                    $obligations_answered = (int) $row_obligatory['answ'];
80                }
81            } else {
82                $obligations_answered = 1;
83            }
84
85            $row = $ilDB->fetchAssoc($result);
86
87            if ($row['hint_count'] === null) {
88                $row['hint_count'] = 0;
89            }
90            if ($row['hint_points'] === null) {
91                $row['hint_points'] = 0;
92            }
93
94            $exam_identifier = self::getExamId($active_id, $pass, $objId);
95
96            /** @var $ilDB ilDB */
97            $ilDB->replace(
98                'tst_pass_result',
99                array(
100                    'active_fi' => array('integer', $active_id),
101                    'pass' => array('integer', strlen($pass) ? $pass : 0)),
102                array(
103                    'points' => array('float', 	$row['reachedpoints'] ? $row['reachedpoints'] : 0),
104                    'maxpoints' => array('float', 	$data['points']),
105                    'questioncount' => array('integer', $data['count']),
106                    'answeredquestions' => array('integer', $row['answeredquestions']),
107                    'workingtime' => array('integer', $time),
108                    'tstamp' => array('integer', time()),
109                    'hint_count' => array('integer', $row['hint_count']),
110                    'hint_points' => array('float', 	$row['hint_points']),
111                    'obligations_answered' => array('integer', $obligations_answered),
112                    'exam_id' => array('text', 	$exam_identifier)
113                )
114            );
115        }
116    }
117
118    private static function _getQuestionCountAndPointsForPassOfParticipant($active_id, $pass, $questionSetType)
119    {
120        global $ilDB;
121
122        switch ($questionSetType) {
123            case self::QUESTION_SET_TYPE_DYNAMIC:
124
125                $res = $ilDB->queryF(
126                    "
127						SELECT		COUNT(qpl_questions.question_id) qcount,
128									SUM(qpl_questions.points) qsum
129						FROM		tst_active
130						INNER JOIN	tst_tests
131						ON			tst_tests.test_id = tst_active.test_fi
132						INNER JOIN	tst_dyn_quest_set_cfg
133						ON          tst_dyn_quest_set_cfg.test_fi = tst_tests.test_id
134						INNER JOIN  qpl_questions
135						ON          qpl_questions.obj_fi = tst_dyn_quest_set_cfg.source_qpl_fi
136						AND         qpl_questions.original_id IS NULL
137						AND         qpl_questions.complete = %s
138						WHERE		tst_active.active_id = %s
139					",
140                    array('integer', 'integer'),
141                    array(1, $active_id)
142                );
143
144                break;
145
146            case self::QUESTION_SET_TYPE_RANDOM:
147
148                $res = $ilDB->queryF(
149                    "
150						SELECT		tst_test_rnd_qst.pass,
151									COUNT(tst_test_rnd_qst.question_fi) qcount,
152									SUM(qpl_questions.points) qsum
153
154						FROM		tst_test_rnd_qst,
155									qpl_questions
156
157						WHERE		tst_test_rnd_qst.question_fi = qpl_questions.question_id
158						AND			tst_test_rnd_qst.active_fi = %s
159						AND			pass = %s
160
161						GROUP BY	tst_test_rnd_qst.active_fi,
162									tst_test_rnd_qst.pass
163					",
164                    array('integer', 'integer'),
165                    array($active_id, $pass)
166                );
167
168                break;
169
170            case self::QUESTION_SET_TYPE_FIXED:
171
172                $res = $ilDB->queryF(
173                    "
174						SELECT		COUNT(tst_test_question.question_fi) qcount,
175									SUM(qpl_questions.points) qsum
176
177						FROM		tst_test_question,
178									qpl_questions,
179									tst_active
180
181						WHERE		tst_test_question.question_fi = qpl_questions.question_id
182						AND			tst_test_question.test_fi = tst_active.test_fi
183						AND			tst_active.active_id = %s
184
185						GROUP BY	tst_test_question.test_fi
186					",
187                    array('integer'),
188                    array($active_id)
189                );
190
191                break;
192
193            default:
194
195                throw new ilTestException("not supported question set type: $questionSetType");
196        }
197
198        $row = $ilDB->fetchAssoc($res);
199
200        if (is_array($row)) {
201            return array("count" => $row["qcount"], "points" => $row["qsum"]);
202        }
203
204        return array("count" => 0, "points" => 0);
205    }
206
207    private static function _getWorkingTimeOfParticipantForPass($active_id, $pass)
208    {
209        global $ilDB;
210
211        $result = $ilDB->queryF(
212            "SELECT * FROM tst_times WHERE active_fi = %s AND pass = %s ORDER BY started",
213            array('integer','integer'),
214            array($active_id, $pass)
215        );
216        $time = 0;
217        while ($row = $ilDB->fetchAssoc($result)) {
218            preg_match("/(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})/", $row["started"], $matches);
219            $epoch_1 = mktime($matches[4], $matches[5], $matches[6], $matches[2], $matches[3], $matches[1]);
220            preg_match("/(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})/", $row["finished"], $matches);
221            $epoch_2 = mktime($matches[4], $matches[5], $matches[6], $matches[2], $matches[3], $matches[1]);
222            $time += ($epoch_2 - $epoch_1);
223        }
224        return $time;
225    }
226
227    private static function getExamId($active_id, $pass, $obj_id)
228    {
229        /** @TODO Move this to a proper place. */
230        global $ilDB;
231
232        $ilSetting = new ilSetting();
233
234        $exam_id_query = 'SELECT exam_id FROM tst_pass_result WHERE active_fi = %s AND pass = %s';
235        $exam_id_result = $ilDB->queryF($exam_id_query, array( 'integer', 'integer' ), array( $active_id, $pass ));
236        if ($ilDB->numRows($exam_id_result) == 1) {
237            $exam_id_row = $ilDB->fetchAssoc($exam_id_result);
238
239            if ($exam_id_row['exam_id'] != null) {
240                return $exam_id_row['exam_id'];
241            }
242        }
243
244        $inst_id = $ilSetting->get('inst_id', null);
245        return 'I' . $inst_id . '_T' . $obj_id . '_A' . $active_id . '_P' . $pass;
246    }
247
248    public static function _updateTestResultCache($active_id, $passScoring)
249    {
250        global $ilDB;
251
252        $pass = self::_getResultPass($active_id, $passScoring);
253
254        $query = "
255			SELECT		tst_pass_result.*
256			FROM		tst_pass_result
257			WHERE		active_fi = %s
258			AND			pass = %s
259		";
260
261        $result = $ilDB->queryF(
262            $query,
263            array('integer','integer'),
264            array($active_id, $pass)
265        );
266
267        $row = $ilDB->fetchAssoc($result);
268
269        $max = $row['maxpoints'];
270        $reached = $row['points'];
271
272        $obligationsAnswered = (int) $row['obligations_answered'];
273
274        $percentage = (!$max) ? 0 : ($reached / $max) * 100.0;
275
276        $mark = self::_getMatchingMarkFromActiveId($active_id, $percentage);
277
278        $isPassed = ($mark["passed"] ? 1 : 0);
279        $isFailed = (!$mark["passed"] ? 1 : 0);
280
281        $query = "
282			DELETE FROM		tst_result_cache
283			WHERE			active_fi = %s
284		";
285
286        $affectedRows = $ilDB->manipulateF(
287            $query,
288            array('integer'),
289            array($active_id)
290        );
291
292        $ilDB->insert('tst_result_cache', array(
293            'active_fi' => array('integer', $active_id),
294            'pass' => array('integer', strlen($pass) ? $pass : 0),
295            'max_points' => array('float', strlen($max) ? $max : 0),
296            'reached_points' => array('float', strlen($reached) ? $reached : 0),
297            'mark_short' => array('text', strlen($mark["short_name"]) ? $mark["short_name"] : " "),
298            'mark_official' => array('text', strlen($mark["official_name"]) ? $mark["official_name"] : " "),
299            'passed' => array('integer', $isPassed),
300            'failed' => array('integer', $isFailed),
301            'tstamp' => array('integer', time()),
302            'hint_count' => array('integer', $row['hint_count']),
303            'hint_points' => array('float', $row['hint_points']),
304            'obligations_answered' => array('integer', $obligationsAnswered)
305        ));
306    }
307
308    private static function _getResultPass($active_id, $passScoring)
309    {
310        $counted_pass = null;
311        if ($passScoring == SCORE_BEST_PASS) {
312            $counted_pass = self::_getBestPass($active_id);
313        } else {
314            $counted_pass = self::_getMaxPass($active_id);
315        }
316        return $counted_pass;
317    }
318
319    private static function _getBestPass($active_id)
320    {
321        global $ilDB;
322
323        $result = $ilDB->queryF(
324            "SELECT * FROM tst_pass_result WHERE active_fi = %s",
325            array('integer'),
326            array($active_id)
327        );
328        if ($result->numRows()) {
329            $bestrow = null;
330            $bestfactor = 0;
331            while ($row = $ilDB->fetchAssoc($result)) {
332                if ($row["maxpoints"] > 0) {
333                    $factor = $row["points"] / $row["maxpoints"];
334                } else {
335                    $factor = 0;
336                }
337
338                if ($factor > $bestfactor) {
339                    $bestrow = $row;
340                    $bestfactor = $factor;
341                }
342            }
343            if (is_array($bestrow)) {
344                return $bestrow["pass"];
345            } else {
346                return 0;
347            }
348        } else {
349            return 0;
350        }
351    }
352
353    private static function _getMaxPass($active_id)
354    {
355        global $ilDB;
356        $result = $ilDB->queryF(
357            "SELECT MAX(pass) maxpass FROM tst_test_result WHERE active_fi = %s",
358            array('integer'),
359            array($active_id)
360        );
361        if ($result->numRows()) {
362            $row = $ilDB->fetchAssoc($result);
363            $max = $row["maxpass"];
364        } else {
365            $max = null;
366        }
367        return $max;
368    }
369
370    private static function _getMatchingMarkFromActiveId($active_id, $percentage)
371    {
372        /** @var $ilDB ilDB */
373        global $ilDB;
374        $result = $ilDB->queryF(
375            "SELECT tst_mark.* FROM tst_active, tst_mark, tst_tests WHERE tst_mark.test_fi = tst_tests.test_id AND tst_tests.test_id = tst_active.test_fi AND tst_active.active_id = %s ORDER BY minimum_level DESC",
376            array('integer'),
377            array($active_id)
378        );
379
380        /** @noinspection PhpAssignmentInConditionInspection */
381        while ($row = $ilDB->fetchAssoc($result)) {
382            if ($percentage >= $row["minimum_level"]) {
383                return $row;
384            }
385        }
386        return false;
387    }
388}
389