1<?php
2/* Copyright (c) 1998-2010 ILIAS open source, Extended GPL, see docs/LICENSE */
3
4/**
5 * LO courses user results
6 *
7 * @author Jörg Lützenkirchen <luetzenkirchen@leifos.com>
8 * @version $Id$
9 * @package ModulesCourse
10 */
11class ilLOUserResults
12{
13    protected $course_obj_id; // [int]
14    protected $user_id; // [int]
15
16    const TYPE_INITIAL = 1;
17    const TYPE_QUALIFIED = 2;
18
19    const STATUS_COMPLETED = 1;
20    const STATUS_FAILED = 2;
21
22    /**
23     * Constructor
24     *
25     * @param int $a_course_obj_id
26     * @param int $a_user_id
27     * @return ilLOUserResults
28     */
29    public function __construct($a_course_obj_id, $a_user_id)
30    {
31        $this->course_obj_id = (int) $a_course_obj_id;
32        $this->user_id = (int) $a_user_id;
33    }
34
35
36    /**
37     * Lookup user result
38     */
39    public static function lookupResult($a_course_obj_id, $a_user_id, $a_objective_id, $a_tst_type)
40    {
41        global $DIC;
42
43        $ilDB = $DIC['ilDB'];
44
45        $query = 'SELECT * FROM loc_user_results ' .
46                'WHERE user_id = ' . $ilDB->quote($a_user_id, 'integer') . ' ' .
47                'AND course_id = ' . $ilDB->quote($a_course_obj_id, 'integer') . ' ' .
48                'AND objective_id = ' . $ilDB->quote($a_objective_id, 'integer') . ' ' .
49                'AND type = ' . $ilDB->quote($a_tst_type, 'integer');
50        $res = $ilDB->query($query);
51        $ur = array(
52            'status' => self::STATUS_FAILED,
53            'result_perc' => 0,
54            'limit_perc' => 0,
55            'tries' => 0,
56            'is_final' => 0,
57            'has_result' => false
58        );
59        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
60            $ur['status'] = $row->status;
61            $ur['result_perc'] = $row->result_perc;
62            $ur['limit_perc'] = $row->limit_perc;
63            $ur['tries'] = $row->tries;
64            $ur['is_final'] = $row->is_final;
65            $ur['has_result'] = true;
66        }
67        return $ur;
68    }
69
70    public static function resetFinalByObjective($a_objective_id)
71    {
72        $query = 'UPDATE loc_user_results ' .
73                'SET is_final = ' . $GLOBALS['DIC']['ilDB']->quote(0, 'integer') . ' ' .
74                'WHERE objective_id = ' . $GLOBALS['DIC']['ilDB']->quote($a_objective_id, 'integer');
75        $GLOBALS['DIC']['ilDB']->manipulate($query);
76    }
77
78
79    /**
80     * Is given type valid?
81     *
82     * @param int $a_type
83     * @return bool
84     */
85    protected static function isValidType($a_type)
86    {
87        return in_array((int) $a_type, array(self::TYPE_INITIAL, self::TYPE_QUALIFIED));
88    }
89
90    /**
91     * Is given status valid?
92     *
93     * @param int $a_status
94     * @return bool
95     */
96    protected static function isValidStatus($a_status)
97    {
98        return in_array((int) $a_status, array(self::STATUS_COMPLETED, self::STATUS_FAILED));
99    }
100
101    /**
102     * Delete all result entries for user
103     *
104     * @param int $a_user_id
105     * @return bool
106     */
107    public static function deleteResultsForUser($a_user_id)
108    {
109        global $DIC;
110
111        $ilDB = $DIC['ilDB'];
112
113        if (!(int) $a_user_id) {
114            return false;
115        }
116
117        $ilDB->manipulate("DELETE FROM loc_user_results" .
118            " WHERE user_id = " . $ilDB->quote($a_user_id, "integer"));
119        return true;
120    }
121
122
123    /**
124     * Delete all result entries for course
125     *
126     * @param int $a_course_id
127     * @return bool
128     */
129    public static function deleteResultsForCourse($a_course_id)
130    {
131        global $DIC;
132
133        $ilDB = $DIC['ilDB'];
134
135        if (!(int) $a_course_id) {
136            return false;
137        }
138
139        $ilDB->manipulate("DELETE FROM loc_user_results" .
140            " WHERE course_id = " . $ilDB->quote($a_course_id, "integer"));
141        return true;
142    }
143
144    /**
145     * Delete for user and course
146     * @global type $ilDB
147     */
148    public function delete()
149    {
150        global $DIC;
151
152        $ilDB = $DIC['ilDB'];
153
154        $query = 'DELETE FROM loc_user_results ' .
155                'WHERE course_id = ' . $ilDB->quote($this->course_obj_id) . ' ' .
156                'AND user_id = ' . $ilDB->quote($this->user_id);
157        $ilDB->manipulate($query);
158    }
159
160    /**
161     * Delete all (qualified) result entries for course members
162     *
163     * @param int $a_course_id
164     * @param array $a_user_ids
165     * @param bool $a_remove_initial
166     * @param bool $a_remove_qualified
167     * @param array $a_objective_ids
168     * @return bool
169     */
170    public static function deleteResultsFromLP($a_course_id, array $a_user_ids, $a_remove_initial, $a_remove_qualified, array $a_objective_ids)
171    {
172        global $DIC;
173
174        $ilDB = $DIC['ilDB'];
175
176        if (!(int) $a_course_id ||
177            !sizeof($a_user_ids)) {
178            return false;
179        }
180
181        $base_sql = "DELETE FROM loc_user_results" .
182            " WHERE course_id = " . $ilDB->quote($a_course_id, "integer") .
183            " AND " . $ilDB->in("user_id", $a_user_ids, "", "integer");
184
185        if ((bool) $a_remove_initial) {
186            $sql = $base_sql .
187                " AND type = " . $ilDB->quote(self::TYPE_INITIAL, "integer");
188            $ilDB->manipulate($sql);
189        }
190
191        if ((bool) $a_remove_qualified) {
192            $sql = $base_sql .
193                " AND type = " . $ilDB->quote(self::TYPE_QUALIFIED, "integer");
194            $ilDB->manipulate($sql);
195        }
196
197        if (is_array($a_objective_ids)) {
198            $sql = $base_sql .
199                " AND " . $ilDB->in("objective_id", $a_objective_ids, "", "integer");
200            $ilDB->manipulate($sql);
201        }
202
203        $ilDB->manipulate($sql);
204        return true;
205    }
206
207
208    /**
209     * Save objective result
210     *
211     * @param int $a_objective_id
212     * @param int $a_type
213     * @param int $a_status
214     * @param int $a_result_percentage
215     * @param int $a_limit_percentage
216     * @param int $a_tries
217     * @param bool $a_is_final
218     * @return bool
219     */
220    public function saveObjectiveResult($a_objective_id, $a_type, $a_status, $a_result_percentage, $a_limit_percentage, $a_tries, $a_is_final)
221    {
222        global $DIC;
223
224        $ilDB = $DIC['ilDB'];
225
226        if (!self::isValidType($a_type) ||
227            !self::isValidStatus($a_status)) {
228            return false;
229        }
230        $ilDB->replace(
231            "loc_user_results",
232            array(
233                "course_id" => array("integer", $this->course_obj_id),
234                "user_id" => array("integer", $this->user_id),
235                "objective_id" => array("integer", $a_objective_id),
236                "type" => array("integer", $a_type)
237            ),
238            array(
239                "status" => array("integer", $a_status),
240                "result_perc" => array("integer", $a_result_percentage),
241                "limit_perc" => array("integer", $a_limit_percentage),
242                "tries" => array("integer", $a_tries),
243                "is_final" => array("integer", $a_is_final),
244                "tstamp" => array("integer", time()),
245            )
246        );
247        return true;
248    }
249
250    /**
251     * Find objective ids by type and/or status
252     *
253     * @param int $a_type
254     * @param int $a_status
255     * @param bool $a_is_final
256     * @return array
257     */
258    protected function findObjectiveIds($a_type = null, $a_status = null, $a_is_final = null)
259    {
260        global $DIC;
261
262        $ilDB = $DIC['ilDB'];
263
264        $res = array();
265
266        $sql = "SELECT objective_id" .
267            " FROM loc_user_results" .
268            " WHERE course_id = " . $ilDB->quote($this->course_obj_id, "integer") .
269            " AND user_id = " . $ilDB->quote($this->user_id, "integer");
270
271        if ($this->isValidType($a_type)) {
272            $sql .= " AND type = " . $ilDB->quote($a_type, "integer");
273        }
274        if ($this->isValidStatus($a_status)) {
275            $sql .= " AND status = " . $ilDB->quote($a_status, "integer");
276        }
277        if ($a_is_final !== null) {
278            $sql .= " AND is_final = " . $ilDB->quote($a_is_final, "integer");
279        }
280
281        $set = $ilDB->query($sql);
282        while ($row = $ilDB->fetchAssoc($set)) {
283            $res[] = $row["objective_id"];
284        }
285
286        return $res;
287    }
288
289    /**
290     * All completed objectives by type
291     * @param type $a_type
292     * @return type
293     */
294    public function getCompletedObjectiveIdsByType($a_type)
295    {
296        return $this->findObjectiveIds($a_type, self::STATUS_COMPLETED);
297    }
298
299    /**
300     * Get all objectives where the user failed the initial test
301     *
302     * @return array objective-ids
303     */
304    public function getSuggestedObjectiveIds()
305    {
306        return $this->findObjectiveIds(self::TYPE_INITIAL, self::STATUS_FAILED);
307    }
308
309    /**
310     * Get all objectives where the user completed the qualified test
311     *
312     * @return array objective-ids
313     */
314    public function getCompletedObjectiveIds()
315    {
316        include_once './Modules/Course/classes/Objectives/class.ilLOSettings.php';
317        $settings = ilLOSettings::getInstanceByObjId($this->course_obj_id);
318
319        if (!$settings->isInitialTestQualifying() or !$settings->worksWithInitialTest()) {
320            return $this->findObjectiveIds(self::TYPE_QUALIFIED, self::STATUS_COMPLETED);
321        }
322
323        // status of final final test overwrites initial qualified.
324        if (
325            $settings->isInitialTestQualifying() &&
326            $settings->worksWithInitialTest()
327        ) {
328            $completed = array();
329            $completed_candidates = array_unique(
330                array_merge(
331                    $this->findObjectiveIds(self::TYPE_INITIAL, self::STATUS_COMPLETED),
332                    $this->findObjectiveIds(self::TYPE_QUALIFIED, self::STATUS_COMPLETED)
333            )
334            );
335            $failed_final = (array) $this->findObjectiveIds(self::TYPE_QUALIFIED, self::STATUS_FAILED);
336
337            foreach ($completed_candidates as $objective_completed) {
338                if (!in_array($objective_completed, $failed_final)) {
339                    $completed[] = $objective_completed;
340                }
341            }
342            return $completed;
343        }
344    }
345
346    /**
347     * Get all objectives where the user failed the qualified test
348     *
349     * @param bool $a_is_final
350     * @return array objective-ids
351     */
352    public function getFailedObjectiveIds($a_is_final = true)
353    {
354        return $this->findObjectiveIds(self::TYPE_QUALIFIED, self::STATUS_FAILED, $a_is_final);
355    }
356
357    /**
358     * Get all results for course and user
359     *
360     * @return array
361     */
362    public function getCourseResultsForUserPresentation()
363    {
364        global $DIC;
365
366        $ilDB = $DIC['ilDB'];
367
368        $res = array();
369
370        include_once("./Modules/Course/classes/Objectives/class.ilLOSettings.php");
371        $settings = ilLOSettings::getInstanceByObjId($this->course_obj_id);
372
373        $set = $ilDB->query("SELECT *" .
374            " FROM loc_user_results" .
375            " WHERE course_id = " . $ilDB->quote($this->course_obj_id, "integer") .
376            " AND user_id = " . $ilDB->quote($this->user_id, "integer"));
377        while ($row = $ilDB->fetchAssoc($set)) {
378            // do not read initial test results, if disabled.
379            if (
380                $row['type'] == self::TYPE_INITIAL &&
381                !$settings->worksWithInitialTest()
382            ) {
383                continue;
384            }
385
386            $objective_id = $row["objective_id"];
387            $type = $row["type"];
388            unset($row["objective_id"]);
389            unset($row["type"]);
390            $res[$objective_id][$type] = $row;
391        }
392
393        return $res;
394    }
395
396    public static function getObjectiveStatusForLP($a_user_id, $a_obj_id, array $a_objective_ids)
397    {
398        global $DIC;
399
400        $ilDB = $DIC['ilDB'];
401
402        // are initital test(s) qualifying?
403        include_once "Modules/Course/classes/Objectives/class.ilLOSettings.php";
404        $lo_set = ilLOSettings::getInstanceByObjId($a_obj_id);
405        $initial_qualifying = $lo_set->isInitialTestQualifying();
406
407        // this method returns LP status codes!
408        include_once "Services/Tracking/classes/class.ilLPStatus.php";
409
410        $res = array();
411
412        $sql = "SELECT lor.objective_id, lor.user_id, lor.status, lor.is_final" .
413            " FROM loc_user_results lor" .
414            " JOIN crs_objectives cobj ON (cobj.objective_id = lor.objective_id)" .
415            " WHERE " . $ilDB->in("lor.objective_id", $a_objective_ids, "", "integer");
416        if (!(bool) $initial_qualifying) {
417            $sql .= " AND lor.type = " . $ilDB->quote(self::TYPE_QUALIFIED, "integer");
418        }
419        $sql .= " AND lor.user_id = " . $ilDB->quote($a_user_id, "integer") .
420            " AND cobj.active = " . $ilDB->quote(1, "integer") .
421            " ORDER BY lor.type"; // qualified must come last!
422        $set = $ilDB->query($sql);
423        while ($row = $ilDB->fetchAssoc($set)) {
424            switch ($row["status"]) {
425                case self::STATUS_FAILED:
426                    if ((bool) $row["is_final"]) {
427                        $status = ilLPStatus::LP_STATUS_FAILED_NUM;
428                    } else {
429                        // #15379
430                        $status = ilLPStatus::LP_STATUS_IN_PROGRESS_NUM;
431                    }
432                    break;
433
434                case self::STATUS_COMPLETED:
435                    $status = ilLPStatus::LP_STATUS_COMPLETED_NUM;
436                    break;
437
438                default:
439                    continue 2;
440            }
441
442            // if both initial and qualified, qualified will overwrite initial
443            $res[$row["objective_id"]] = $status;
444        }
445
446        return $res;
447    }
448
449    public static function getSummarizedObjectiveStatusForLP($a_obj_id, array $a_objective_ids, $a_user_id = null)
450    {
451        global $DIC;
452
453        $ilDB = $DIC['ilDB'];
454
455        $GLOBALS['DIC']->logger()->trac()->debug('Get summorized objective status');
456
457        // change event is NOT parsed here!
458
459        // are initital test(s) qualifying?
460        include_once "Modules/Course/classes/Objectives/class.ilLOSettings.php";
461        $lo_set = ilLOSettings::getInstanceByObjId($a_obj_id);
462        $initial_qualifying = $lo_set->isInitialTestQualifying();
463
464        // this method returns LP status codes!
465        include_once "Services/Tracking/classes/class.ilLPStatus.php";
466
467        $res = $tmp_completed = array();
468
469        $sql = "SELECT lor.objective_id, lor.user_id, lor.status, lor.type, lor.is_final" .
470            " FROM loc_user_results lor" .
471            " JOIN crs_objectives cobj ON (cobj.objective_id = lor.objective_id)" .
472            " WHERE " . $ilDB->in("lor.objective_id", $a_objective_ids, "", "integer") .
473            " AND cobj.active = " . $ilDB->quote(1, "integer");
474        if (!(bool) $initial_qualifying) {
475            $sql .= " AND lor.type = " . $ilDB->quote(self::TYPE_QUALIFIED, "integer");
476        }
477        if ($a_user_id) {
478            $sql .= " AND lor.user_id = " . $ilDB->quote($a_user_id, "integer");
479        }
480        $sql .= " ORDER BY lor.type DESC"; // qualified must come first!
481        $set = $ilDB->query($sql);
482
483        $has_final_result = array();
484        while ($row = $ilDB->fetchAssoc($set)) {
485            if ($row['type'] == self::TYPE_QUALIFIED) {
486                $has_final_result[$row['objective_id']] = $row['user_id'];
487            }
488
489            $user_id = (int) $row["user_id"];
490            $status = (int) $row["status"];
491
492            // initial tests only count if no qualified test
493            if (
494                $row["type"] == self::TYPE_INITIAL &&
495                in_array($row['user_id'], (array) $has_final_result[$row['objective_id']])
496            ) {
497                continue;
498            }
499
500            // user did do something
501            $res[$user_id] = ilLPStatus::LP_STATUS_IN_PROGRESS_NUM;
502
503            switch ($status) {
504                case self::STATUS_COMPLETED:
505                    $tmp_completed[$user_id]++;
506                    break;
507
508                case self::STATUS_FAILED:
509                    if ((bool) $row["is_final"]) {
510                        // object is failed when at least 1 objective is failed without any tries left
511                        $res[$user_id] = ilLPStatus::LP_STATUS_FAILED_NUM;
512                    }
513                    break;
514            }
515        }
516
517        $all_nr = sizeof($a_objective_ids);
518        foreach ($tmp_completed as $user_id => $counter) {
519            // if used as precondition object should be completed ASAP, status can be lost on subsequent tries
520            if ($counter == $all_nr) {
521                $res[$user_id] = ilLPStatus::LP_STATUS_COMPLETED_NUM;
522            }
523        }
524
525        if ($a_user_id) {
526            // might return null!
527            return $res[$a_user_id];
528        } else {
529            return $res;
530        }
531    }
532
533    public static function hasResults($a_container_id, $a_user_id)
534    {
535        global $DIC;
536
537        $ilDB = $DIC['ilDB'];
538
539        $query = 'SELECT objective_id FROM loc_user_results ' .
540                'WHERE course_id = ' . $ilDB->quote($a_container_id, 'integer') . ' ' .
541                'AND user_id = ' . $ilDB->quote($a_user_id, 'integer');
542
543        $res = $ilDB->query($query);
544        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
545            return true;
546        }
547        return false;
548    }
549
550    /**
551     * Get completed learning objectives for user and time frame
552     * @param int $a_user_id
553     * @param int $a_from_ts
554     * @param int $a_to_ts
555     * @return array
556     */
557    public static function getCompletionsOfUser($a_user_id, $a_from_ts, $a_to_ts)
558    {
559        global $DIC;
560
561        $ilDB = $DIC['ilDB'];
562
563        $res = array();
564
565        $sql = "SELECT lor.objective_id, lor.user_id, lor.status, lor.is_final, lor.tstamp, lor.course_id, cobj.title" .
566            " FROM loc_user_results lor" .
567            " JOIN crs_objectives cobj ON (cobj.objective_id = lor.objective_id)" .
568            " WHERE lor.user_id = " . $ilDB->quote($a_user_id, "integer") .
569            " AND lor.type = " . $ilDB->quote(self::TYPE_QUALIFIED, "integer") .
570            " AND lor.tstamp >= " . $ilDB->quote($a_from_ts, "integer") .
571            " AND lor.tstamp <= " . $ilDB->quote($a_to_ts, "integer") .
572            " AND lor.status = " . $ilDB->quote(self::STATUS_COMPLETED, "integer");
573
574        $set = $ilDB->query($sql);
575        while ($row = $ilDB->fetchAssoc($set)) {
576            $res[$row["objective_id"]] = $row;
577        }
578        return $res;
579    }
580}
581