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