1<?php
2
3/* Copyright (c) 1998-2012 ILIAS open source, Extended GPL, see docs/LICENSE */
4
5include_once("./Services/Skill/interfaces/interface.ilSkillUsageInfo.php");
6
7/**
8 * Skill profile
9 *
10 * @author Alex Killing <alex.killing@gmx.de>
11 * @version $Id$
12 * @ingroup Services/Skill
13 */
14class ilSkillProfile implements ilSkillUsageInfo
15{
16    /**
17     * @var ilDB
18     */
19    protected $db;
20
21    /**
22     * @var ilLanguage
23     */
24    protected $lng;
25
26    /**
27     * @var ilRbacReview
28     */
29    protected $review;
30
31    protected $id;
32    protected $title;
33    protected $description;
34    protected $skill_level = array();
35
36    /**
37     * Constructor
38     *
39     * @param int $a_id profile id
40     */
41    public function __construct($a_id = 0)
42    {
43        global $DIC;
44
45        $this->db = $DIC->database();
46        $this->lng = $DIC->language();
47        $this->review = $DIC->rbac()->review();
48        if ($a_id > 0) {
49            $this->setId($a_id);
50            $this->read();
51        }
52    }
53
54    /**
55     * Set id
56     *
57     * @param int $a_val id
58     */
59    public function setId($a_val)
60    {
61        $this->id = $a_val;
62    }
63
64    /**
65     * Get id
66     *
67     * @return int id
68     */
69    public function getId()
70    {
71        return $this->id;
72    }
73
74    /**
75     * Set title
76     *
77     * @param string $a_val title
78     */
79    public function setTitle($a_val)
80    {
81        $this->title = $a_val;
82    }
83
84    /**
85     * Get title
86     *
87     * @return string title
88     */
89    public function getTitle()
90    {
91        return $this->title;
92    }
93
94    /**
95     * Set description
96     *
97     * @param string $a_val description
98     */
99    public function setDescription($a_val)
100    {
101        $this->description = $a_val;
102    }
103
104    /**
105     * Get description
106     *
107     * @return string description
108     */
109    public function getDescription()
110    {
111        return $this->description;
112    }
113
114    /**
115     * Add skill level
116     *
117     * @param
118     * @return
119     */
120    public function addSkillLevel($a_base_skill_id, $a_tref_id, $a_level_id)
121    {
122        //echo "-".$a_base_skill_id."-";
123        $this->skill_level[] = array(
124            "base_skill_id" => $a_base_skill_id,
125            "tref_id" => $a_tref_id,
126            "level_id" => $a_level_id
127            );
128    }
129
130    /**
131     * Remove skill level
132     *
133     * @param
134     * @return
135     */
136    public function removeSkillLevel($a_base_skill_id, $a_tref_id, $a_level_id)
137    {
138        foreach ($this->skill_level as $k => $sl) {
139            if ((int) $sl["base_skill_id"] == (int) $a_base_skill_id &&
140                (int) $sl["tref_id"] == (int) $a_tref_id &&
141                (int) $sl["level_id"] == (int) $a_level_id) {
142                unset($this->skill_level[$k]);
143            }
144        }
145    }
146
147    /**
148     * Get skill levels
149     *
150     * @param
151     * @return
152     */
153    public function getSkillLevels()
154    {
155        return $this->skill_level;
156    }
157
158    /**
159     * Read skill profile from db
160     *
161     * @param
162     * @return
163     */
164    public function read()
165    {
166        $ilDB = $this->db;
167
168        $set = $ilDB->query(
169            "SELECT * FROM skl_profile " .
170            " WHERE id = " . $ilDB->quote($this->getId(), "integer")
171            );
172        $rec = $ilDB->fetchAssoc($set);
173        $this->setTitle($rec["title"]);
174        $this->setDescription($rec["description"]);
175
176        $set = $ilDB->query(
177            "SELECT * FROM skl_profile_level " .
178            " WHERE profile_id = " . $ilDB->quote($this->getId(), "integer")
179            );
180        while ($rec = $ilDB->fetchAssoc($set)) {
181            $this->addSkillLevel(
182                (int) $rec["base_skill_id"],
183                (int) $rec["tref_id"],
184                (int) $rec["level_id"]
185            );
186        }
187    }
188
189    /**
190     * Create skill profile
191     */
192    public function create()
193    {
194        $ilDB = $this->db;
195
196        // profile
197        $this->setId($ilDB->nextId("skl_profile"));
198        $ilDB->manipulate("INSERT INTO skl_profile " .
199            "(id, title, description) VALUES (" .
200            $ilDB->quote($this->getId(), "integer") . "," .
201            $ilDB->quote($this->getTitle(), "text") . "," .
202            $ilDB->quote($this->getDescription(), "text") .
203            ")");
204
205        // profile levels
206        foreach ($this->skill_level as $level) {
207            $ilDB->replace(
208                "skl_profile_level",
209                array("profile_id" => array("integer", $this->getId()),
210                    "tref_id" => array("integer", (int) $level["tref_id"]),
211                    "base_skill_id" => array("integer", (int) $level["base_skill_id"])
212                    ),
213                array("level_id" => array("integer", (int) $level["level_id"]))
214                );
215        }
216    }
217
218    /**
219     * Update skill profile
220     */
221    public function update()
222    {
223        $ilDB = $this->db;
224
225        // profile
226        $ilDB->manipulate(
227            "UPDATE skl_profile SET " .
228            " title = " . $ilDB->quote($this->getTitle(), "text") . "," .
229            " description = " . $ilDB->quote($this->getDescription(), "text") .
230            " WHERE id = " . $ilDB->quote($this->getId(), "integer")
231            );
232
233        // profile levels
234        $ilDB->manipulate(
235            "DELETE FROM skl_profile_level WHERE " .
236            " profile_id = " . $ilDB->quote($this->getId(), "integer")
237            );
238        foreach ($this->skill_level as $level) {
239            $ilDB->replace(
240                "skl_profile_level",
241                array("profile_id" => array("integer", $this->getId()),
242                    "tref_id" => array("integer", (int) $level["tref_id"]),
243                    "base_skill_id" => array("integer", (int) $level["base_skill_id"])
244                    ),
245                array("level_id" => array("integer", (int) $level["level_id"]))
246                );
247
248            /*$ilDB->manipulate("INSERT INTO skl_profile_level ".
249                "(profile_id, base_skill_id, tref_id, level_id) VALUES (".
250                $ilDB->quote($this->getId(), "integer").",".
251                $ilDB->quote((int) $level["base_skill_id"], "integer").",".
252                $ilDB->quote((int) $level["tref_id"], "integer").",".
253                $ilDB->quote((int) $level["level_id"], "integer").
254                ")");*/
255        }
256    }
257
258    /**
259     * Delete skill profile
260     */
261    public function delete()
262    {
263        $ilDB = $this->db;
264
265        // TODO: Split the deletions when refactoring to repository pattern
266
267        // profile levels
268        $ilDB->manipulate(
269            "DELETE FROM skl_profile_level WHERE " .
270            " profile_id = " . $ilDB->quote($this->getId(), "integer")
271            );
272
273        // profile users
274        $ilDB->manipulate(
275            "DELETE FROM skl_profile_user WHERE " .
276            " profile_id = " . $ilDB->quote($this->getId(), "integer")
277        );
278
279        // profile roles
280        $ilDB->manipulate(
281            "DELETE FROM skl_profile_role WHERE " .
282            " profile_id = " . $ilDB->quote($this->getId(), "integer")
283        );
284
285        // profile
286        $ilDB->manipulate(
287            "DELETE FROM skl_profile WHERE " .
288            " id = " . $ilDB->quote($this->getId(), "integer")
289            );
290    }
291
292    /**
293     * Get profiles
294     *
295     * @param
296     * @return
297     */
298    public static function getProfiles()
299    {
300        global $DIC;
301
302        $ilDB = $DIC->database();
303
304        $set = $ilDB->query(
305            "SELECT * FROM skl_profile " .
306            " ORDER BY title "
307            );
308        $profiles = array();
309        while ($rec = $ilDB->fetchAssoc($set)) {
310            $profiles[$rec["id"]] = $rec;
311        }
312
313        return $profiles;
314    }
315
316    /**
317     * Lookup
318     *
319     * @param
320     * @return
321     */
322    protected static function lookup($a_id, $a_field)
323    {
324        global $DIC;
325
326        $ilDB = $DIC->database();
327
328        $set = $ilDB->query(
329            "SELECT " . $a_field . " FROM skl_profile " .
330            " WHERE id = " . $ilDB->quote($a_id, "integer")
331            );
332        $rec = $ilDB->fetchAssoc($set);
333        return $rec[$a_field];
334    }
335
336    /**
337     * Lookup title
338     *
339     * @param
340     * @return
341     */
342    public static function lookupTitle($a_id)
343    {
344        return self::lookup($a_id, "title");
345    }
346
347    ////
348    //// Skill user assignment
349    ////
350
351    /**
352     * Get all assignments (users and roles)
353     */
354    public function getAssignments()
355    {
356        $assignments = array();
357
358        $users = $this->getAssignedUsers();
359        $roles = $this->getAssignedRoles();
360        $assignments = $users + $roles;
361        ksort($assignments);
362
363        return $assignments;
364    }
365
366    /**
367     * Get assigned users
368     */
369    public function getAssignedUsers()
370    {
371        $ilDB = $this->db;
372        $lng = $this->lng;
373
374        $set = $ilDB->query(
375            "SELECT * FROM skl_profile_user " .
376            " WHERE profile_id = " . $ilDB->quote($this->getId(), "integer")
377            );
378        $users = array();
379        while ($rec = $ilDB->fetchAssoc($set)) {
380            $name = ilUserUtil::getNamePresentation($rec["user_id"]);
381            $type = $lng->txt("user");
382            $users[$rec["user_id"]] = array(
383                "type" => $type,
384                "name" => $name,
385                "id" => $rec["user_id"]
386                );
387        }
388        return $users;
389    }
390
391    /**
392     * Add user to profile
393     *
394     * @param int $a_user_id user id
395     */
396    public function addUserToProfile($a_user_id)
397    {
398        $ilDB = $this->db;
399
400        $ilDB->replace(
401            "skl_profile_user",
402            array("profile_id" => array("integer", $this->getId()),
403                "user_id" => array("integer", (int) $a_user_id),
404                ),
405            array()
406            );
407    }
408
409    /**
410     * Remove user from profile
411     *
412     * @param int $a_user_id user id
413     */
414    public function removeUserFromProfile($a_user_id)
415    {
416        $ilDB = $this->db;
417
418        $ilDB->manipulate(
419            "DELETE FROM skl_profile_user WHERE " .
420            " profile_id = " . $ilDB->quote($this->getId(), "integer") .
421            " AND user_id = " . $ilDB->quote($a_user_id, "integer")
422            );
423    }
424
425    /**
426     * Remove user from all profiles
427     *
428     * @param int $a_user_id
429     */
430    public static function removeUserFromAllProfiles($a_user_id)
431    {
432        global $DIC;
433        $ilDB = $DIC->database();
434
435        $ilDB->manipulate(
436            "DELETE FROM skl_profile_user WHERE " .
437            " user_id = " . $ilDB->quote($a_user_id, "integer")
438        );
439    }
440
441
442    /**
443     * Get profiles of a user
444     *
445     * @param int $a_user_id user id
446     */
447    public static function getProfilesOfUser($a_user_id)
448    {
449        global $DIC;
450
451        $ilDB = $DIC->database();
452        $rbacreview = $DIC->rbac()->review();
453
454        $all_profiles = array();
455
456        // competence profiles coming from user assignments
457        $user_profiles = array();
458        $set = $ilDB->query(
459            "SELECT p.id, p.title FROM skl_profile_user u JOIN skl_profile p " .
460            " ON (u.profile_id = p.id) " .
461            " WHERE user_id = " . $ilDB->quote($a_user_id, "integer") .
462            " ORDER BY p.title ASC"
463            );
464        while ($rec = $ilDB->fetchAssoc($set)) {
465            $user_profiles[] = $rec;
466        }
467
468        // competence profiles coming from role assignments
469        $role_profiles = array();
470        $user_roles = $rbacreview->assignedRoles($a_user_id);
471        foreach ($user_roles as $role) {
472            $profiles = self::getProfilesOfRole($role);
473            foreach ($profiles as $profile) {
474                $role_profiles[] = $profile;
475            }
476        }
477
478        // merge competence profiles and remove multiple occurrences
479        $all_profiles = array_merge($user_profiles, $role_profiles);
480        $temp_profiles = array();
481        foreach ($all_profiles as &$v) {
482            if (!isset($temp_profiles[$v["id"]])) {
483                $temp_profiles[$v["id"]] = &$v;
484            }
485        }
486        $all_profiles = array_values($temp_profiles);
487        return $all_profiles;
488    }
489
490    /**
491     * Get assigned users
492     */
493    public static function countUsers($a_profile_id)
494    {
495        global $DIC;
496
497        $ilDB = $DIC->database();
498
499        $set = $ilDB->query(
500            "SELECT count(*) ucnt FROM skl_profile_user " .
501            " WHERE profile_id = " . $ilDB->quote($a_profile_id, "integer")
502            );
503        $rec = $ilDB->fetchAssoc($set);
504        return (int) $rec["ucnt"];
505    }
506
507    /**
508     * Get assigned roles
509     *
510     * @return array
511     */
512    public function getAssignedRoles()
513    {
514        $ilDB = $this->db;
515        $lng = $this->lng;
516        $review = $this->review;
517
518        $set = $ilDB->query(
519            "SELECT * FROM skl_profile_role " .
520            " WHERE profile_id = " . $ilDB->quote($this->getId(), "integer")
521        );
522        $roles = array();
523        while ($rec = $ilDB->fetchAssoc($set)) {
524            $name = ilObjRole::_getTranslation(ilObjRole::_lookupTitle($rec["role_id"]));
525            $type = $lng->txt("role");
526            // get object of role
527            $obj = ilObject::_lookupObjectId($review->getObjectReferenceOfRole($rec["role_id"]));
528            // get title of object if course or group
529            if (ilObject::_lookupType($obj) == "crs" || ilObject::_lookupType($obj) == "grp") {
530                $obj_title = ilObject::_lookupTitle($obj);
531            }
532
533            $roles[$rec["role_id"]] = array(
534                "type" => $type,
535                "name" => $name,
536                "id" => $rec["role_id"],
537                "object" => $obj_title
538            );
539        }
540
541        return $roles;
542    }
543
544    /**
545     * Add role to profile
546     *
547     * @param int $a_role_id role id
548     */
549    public function addRoleToProfile(int $a_role_id)
550    {
551        $ilDB = $this->db;
552
553        $ilDB->replace(
554            "skl_profile_role",
555            array("profile_id" => array("integer", $this->getId()),
556                "role_id" => array("integer", (int) $a_role_id),
557            ),
558            array()
559        );
560    }
561
562    /**
563     * Remove role from profile
564     *
565     * @param int $a_role_id role id
566     */
567    public function removeRoleFromProfile(int $a_role_id)
568    {
569        $ilDB = $this->db;
570
571        $ilDB->manipulate(
572            "DELETE FROM skl_profile_role WHERE " .
573            " profile_id = " . $ilDB->quote($this->getId(), "integer") .
574            " AND role_id = " . $ilDB->quote($a_role_id, "integer")
575        );
576    }
577
578    /**
579     * Remove role from all profiles
580     *
581     * @param int $a_role_id
582     */
583    public static function removeRoleFromAllProfiles(int $a_role_id)
584    {
585        global $DIC;
586        $ilDB = $DIC->database();
587
588        $ilDB->manipulate(
589            "DELETE FROM skl_profile_role WHERE " .
590            " role_id = " . $ilDB->quote($a_role_id, "integer")
591        );
592    }
593
594    /**
595     * Get profiles of a role
596     *
597     * @param int $a_role_id role id
598     * @return array
599     */
600    public static function getProfilesOfRole(int $a_role_id)
601    {
602        global $DIC;
603
604        $ilDB = $DIC->database();
605
606        $profiles = array();
607        $set = $ilDB->query(
608            "SELECT p.id, p.title FROM skl_profile_role r JOIN skl_profile p " .
609            " ON (r.profile_id = p.id) " .
610            " WHERE role_id = " . $ilDB->quote($a_role_id, "integer") .
611            " ORDER BY p.title ASC"
612        );
613        while ($rec = $ilDB->fetchAssoc($set)) {
614            $profiles[] = $rec;
615        }
616        return $profiles;
617    }
618
619    /**
620     * Count assigned roles of a profile
621     *
622     * @param int $a_profile_id
623     * @return int
624     */
625    public static function countRoles(int $a_profile_id)
626    {
627        global $DIC;
628
629        $ilDB = $DIC->database();
630
631        $set = $ilDB->query(
632            "SELECT count(*) rcnt FROM skl_profile_role " .
633            " WHERE profile_id = " . $ilDB->quote($a_profile_id, "integer")
634        );
635        $rec = $ilDB->fetchAssoc($set);
636        return (int) $rec["rcnt"];
637    }
638
639    /**
640     * Get usage info
641     *
642     * @param
643     * @return
644     */
645    public static function getUsageInfo($a_cskill_ids, &$a_usages)
646    {
647        global $DIC;
648
649        $ilDB = $DIC->database();
650
651        include_once("./Services/Skill/classes/class.ilSkillUsage.php");
652        ilSkillUsage::getUsageInfoGeneric(
653            $a_cskill_ids,
654            $a_usages,
655            ilSkillUsage::PROFILE,
656            "skl_profile_level",
657            "profile_id",
658            "base_skill_id"
659        );
660    }
661}
662