ILIAS  release_4-3 Revision
 All Data Structures Namespaces Files Functions Variables Groups Pages
class.ilBasicSkill.php
Go to the documentation of this file.
1 <?php
2 
3 /* Copyright (c) 1998-2010 ILIAS open source, Extended GPL, see docs/LICENSE */
4 
5 include_once("./Services/Skill/classes/class.ilSkillTreeNode.php");
6 
15 class ilBasicSkill extends ilSkillTreeNode
16 {
17  const ACHIEVED = 1;
18  const NOT_ACHIEVED = 0;
19 
20  var $id;
21 
26  function __construct($a_id = 0)
27  {
28  parent::ilSkillTreeNode($a_id);
29  $this->setType("skll");
30  }
31 
35  function read()
36  {
37  parent::read();
38  }
39 
44  function create()
45  {
46  parent::create();
47  }
48 
52  function delete()
53  {
54  global $ilDB;
55 
56  $ilDB->manipulate("DELETE FROM skl_level WHERE "
57  ." skill_id = ".$ilDB->quote($this->getId(), "integer")
58  );
59 
60  $ilDB->manipulate("DELETE FROM skl_user_has_level WHERE "
61  ." skill_id = ".$ilDB->quote($this->getId(), "integer")
62  );
63 
64  parent::delete();
65  }
66 
70  function copy()
71  {
72  $skill = new ilBasicSkill();
73  $skill->setTitle($this->getTitle());
74  $skill->setType($this->getType());
75  $skill->setSelfEvaluation($this->getSelfEvaluation());
76  $skill->setOrderNr($this->getOrderNr());
77  $skill->create();
78 
79  $levels = $this->getLevelData();
80  if (sizeof($levels))
81  {
82  foreach($levels as $item)
83  {
84  $skill->addLevel($item["title"], $item["description"]);
85  }
86  }
87  $skill->update();
88 
89  return $skill;
90  }
91 
92  //
93  //
94  // Skill level related methods
95  //
96  //
97 
104  function addLevel($a_title, $a_description)
105  {
106  global $ilDB;
107 
108  $nr = $this->getMaxLevelNr();
109  $nid = $ilDB->nextId("skl_level");
110  $ilDB->insert("skl_level", array(
111  "id" => array("integer", $nid),
112  "skill_id" => array("integer", $this->getId()),
113  "nr" => array("integer", $nr+1),
114  "title" => array("text", $a_title),
115  "description" => array("clob", $a_description)
116  ));
117 
118  }
119 
125  function getMaxLevelNr()
126  {
127  global $ilDB;
128 
129  $set = $ilDB->query("SELECT MAX(nr) mnr FROM skl_level WHERE ".
130  " skill_id = ".$ilDB->quote($this->getId(), "integer")
131  );
132  $rec = $ilDB->fetchAssoc($set);
133  return (int) $rec["mnr"];
134  }
135 
141  function getLevelData($a_id = 0)
142  {
143  global $ilDB;
144 
145  if ($a_id > 0)
146  {
147  $and = " AND id = ".$ilDB->quote($a_id, "integer");
148  }
149 
150  $set = $ilDB->query("SELECT * FROM skl_level WHERE ".
151  " skill_id = ".$ilDB->quote($this->getId(), "integer").
152  $and.
153  " ORDER BY nr"
154  );
155  $levels = array();
156  while ($rec = $ilDB->fetchAssoc($set))
157  {
158  if ($a_id > 0)
159  {
160  return $rec;
161  }
162  $levels[] = $rec;
163  }
164  return $levels;
165  }
166 
173  protected static function lookupLevelProperty($a_id, $a_prop)
174  {
175  global $ilDB;
176 
177  $set = $ilDB->query("SELECT $a_prop FROM skl_level WHERE ".
178  " id = ".$ilDB->quote($a_id, "integer")
179  );
180  $rec = $ilDB->fetchAssoc($set);
181  return $rec[$a_prop];
182  }
183 
190  static function lookupLevelTitle($a_id)
191  {
192  return ilBasicSkill::lookupLevelProperty($a_id, "title");
193  }
194 
201  static function lookupLevelDescription($a_id)
202  {
203  return ilBasicSkill::lookupLevelProperty($a_id, "description");
204  }
205 
212  static function lookupLevelTrigger($a_id)
213  {
214  $ref_id = ilBasicSkill::lookupLevelProperty($a_id, "trigger_ref_id");
215  $obj_id = ilBasicSkill::lookupLevelProperty($a_id, "trigger_obj_id");
216  return array("ref_id" => $ref_id, "obj_id" => $obj_id);
217  }
218 
225  static function lookupLevelSkillId($a_id)
226  {
227  return ilBasicSkill::lookupLevelProperty($a_id, "skill_id");
228  }
229 
236  static protected function writeLevelProperty($a_id, $a_prop, $a_value, $a_type)
237  {
238  global $ilDB;
239 
240  $ilDB->update("skl_level", array(
241  $a_prop => array($a_type, $a_value),
242  ), array(
243  "id" => array("integer", $a_id),
244  ));
245  }
246 
253  static function writeLevelTitle($a_id, $a_title)
254  {
255  ilBasicSkill::writeLevelProperty($a_id, "title", $a_title, "text");
256  }
257 
264  static function writeLevelDescription($a_id, $a_description)
265  {
266  ilBasicSkill::writeLevelProperty($a_id, "description", $a_description, "clob");
267  }
268 
275  static function writeLevelTrigger($a_id, $a_trigger_ref_id)
276  {
277  $a_trigger_obj_id = 0;
278  if ($a_trigger_ref_id > 0)
279  {
280  $a_trigger_obj_id = ilObject::_lookupObjId($a_trigger_ref_id);
281  }
282  ilBasicSkill::writeLevelProperty($a_id, "trigger_ref_id", $a_trigger_ref_id, "integer");
283  ilBasicSkill::writeLevelProperty($a_id, "trigger_obj_id", $a_trigger_obj_id, "integer");
284  }
285 
292  function updateLevelOrder($order)
293  {
294  global $ilDB;
295 
296  asort($order);
297 
298  $cnt = 1;
299  foreach ($order as $id => $o)
300  {
301  $ilDB->manipulate("UPDATE skl_level SET ".
302  " nr = ".$ilDB->quote($cnt, "integer").
303  " WHERE id = ".$ilDB->quote($id, "integer")
304  );
305  $cnt++;
306  }
307  }
308 
315  function deleteLevel($a_id)
316  {
317  global $ilDB;
318 
319  $ilDB->manipulate("DELETE FROM skl_level WHERE "
320  ." id = ".$ilDB->quote($a_id, "integer")
321  );
322 
323  }
324 
331  function fixLevelNumbering()
332  {
333  global $ilDB;
334 
335  $set = $ilDB->query("SELECT id, nr FROM skl_level WHERE ".
336  " skill_id = ".$ilDB->quote($this->getId(), "integer").
337  " ORDER BY nr ASC"
338  );
339  $cnt = 1;
340  while ($rec = $ilDB->fetchAssoc($set))
341  {
342  $ilDB->manipulate("UPDATE skl_level SET ".
343  " nr = ".$ilDB->quote($cnt, "integer").
344  " WHERE id = ".$ilDB->quote($rec["id"], "integer")
345  );
346  $cnt++;
347  }
348  }
349 
356  function lookupLevelsForTriggerRefId($a_ref_id)
357  {
358  global $ilDB;
359 
360  $set = $ilDB->query("SELECT id FROM skl_level WHERE ".
361  " trigger_ref_id = ".$ilDB->quote($a_ref_id, "integer")
362  );
363  $levels = array();
364  while ($rec = $ilDB->fetchAssoc($set))
365  {
366  $levels[] = $rec["id"];
367  }
368  return $levels;
369  }
370 
377  function getSkillForLevelId($a_level_id)
378  {
379  global $ilDB;
380 
381  $set = $ilDB->query("SELECT * FROM skl_level WHERE ".
382  " id = ".$ilDB->quote($a_level_id, "integer")
383  );
384  $skill = null;
385  if ($rec = $ilDB->fetchAssoc($set))
386  {
387  if (ilSkillTreeNode::isInTree($rec["skill_id"]))
388  {
389  $skill = new ilBasicSkill($rec["skill_id"]);
390  }
391  }
392  return $skill;
393  }
394 
395  //
396  //
397  // User skill (level) related methods
398  //
399  //
400 
401 
406  public static function updateAllUserSkillLevels()
407  {
408  global $ilDB;
409 
410  $set = $ilDB->query("SELECT id, trigger_obj_id FROM skl_level WHERE ".
411  " trigger_obj_id > ".$ilDB->quote(0, "integer")
412  );
413  while ($rec = $ilDB->fetchAssoc($set)) // all trigger objects
414  {
415  $skill_level_id = $rec["id"];
416  $tr_obj_id = $rec["trigger_obj_id"];
417 
418  if (ilObject::_hasUntrashedReference($tr_obj_id))
419  {
420  include_once("./Services/Tracking/classes/class.ilLPStatusWrapper.php");
421  $completed = ilLPStatusWrapper::_lookupCompletedForObject($tr_obj_id);
422  foreach ($completed as $user_id)
423  {
424  ilBasicSkill::writeUserSkillLevelStatus($skill_level_id,
425  $user_id, ilBasicSkill::ACHIEVED);
426  }
427  }
428  }
429  }
430 
435  public static function updateSkillLevelsByTriggerRef($a_user_id, $a_ref_id)
436  {
437  global $ilDB;
438 
439  $set = $ilDB->query("SELECT id, trigger_obj_id FROM skl_level WHERE ".
440  " trigger_ref_id = ".$ilDB->quote($a_ref_id, "integer")
441  );
442  while ($rec = $ilDB->fetchAssoc($set)) // all trigger objects
443  {
444  $skill_level_id = $rec["id"];
445  $tr_obj_id = $rec["trigger_obj_id"];
446 
447  if (ilObject::_hasUntrashedReference($tr_obj_id))
448  {
449  include_once("./Services/Tracking/classes/class.ilLPStatusWrapper.php");
450  $completed = ilLPStatusWrapper::_lookupCompletedForObject($tr_obj_id);
451  foreach ($completed as $user_id)
452  {
453  if ($a_user_id == $user_id)
454  {
455  ilBasicSkill::writeUserSkillLevelStatus($skill_level_id,
456  $user_id, ilBasicSkill::ACHIEVED);
457  }
458  }
459  }
460  }
461  }
462 
470  static function writeUserSkillLevelStatus($a_level_id, $a_user_id,
471  $a_status = ilBasicSkill::ACHIEVED, $a_force = false)
472  {
473  global $ilDB;
474 
475  $skill_id = ilBasicSkill::lookupLevelSkillId($a_level_id);
476  $trigger = ilBasicSkill::lookupLevelTrigger($a_level_id);
477  $trigger_ref_id = $trigger["ref_id"];
478  $trigger_obj_id = $trigger["obj_id"];
479  $trigger_title = ilObject::_lookupTitle($trigger["obj_id"]);
480 
481  $save = false;
482 
483  if ($a_force)
484  {
485  $save = true;
486  }
487  else
488  {
489  // check whether current skill user level is identical
490  // to the one that should be set (-> no change required)
491  $ilDB->setLimit(1);
492  $set = $ilDB->query("SELECT status, valid FROM skl_user_skill_level WHERE ".
493  "level_id = ".$ilDB->quote($a_level_id, "integer")." AND ".
494  "user_id = ".$ilDB->quote($a_user_id, "integer").
495  " ORDER BY status_date DESC"
496  );
497  $rec = $ilDB->fetchAssoc($set);
498  if (!$rec["valid"] || $rec["status"] != $a_status)
499  {
500  $save = true;
501  }
502  }
503 
504  if ($save)
505  {
506  $now = ilUtil::now();
507  $ilDB->manipulate("INSERT INTO skl_user_skill_level ".
508  "(level_id, user_id, status_date, skill_id, status, valid, trigger_ref_id,".
509  "trigger_obj_id, trigger_title) VALUES (".
510  $ilDB->quote($a_level_id, "integer").",".
511  $ilDB->quote($a_user_id, "integer").",".
512  $ilDB->quote($now, "timestamp").",".
513  $ilDB->quote($skill_id, "integer").",".
514  $ilDB->quote($a_status, "integer").",".
515  $ilDB->quote(1, "integer").",".
516  $ilDB->quote($trigger_ref_id, "integer").",".
517  $ilDB->quote($trigger_obj_id, "integer").",".
518  $ilDB->quote($trigger_title, "text").
519  ")");
520 
521  $ilDB->manipulate("DELETE FROM skl_user_has_level WHERE "
522  ." user_id = ".$ilDB->quote($a_user_id, "integer")
523  ." AND level_id = ".$ilDB->quote($a_level_id, "integer")
524  );
525 
526  if ($a_status == ilBasicSkill::ACHIEVED)
527  {
528  $ilDB->manipulate("INSERT INTO skl_user_has_level ".
529  "(level_id, user_id, status_date, skill_id, trigger_ref_id, trigger_obj_id, trigger_title) VALUES (".
530  $ilDB->quote($a_level_id, "integer").",".
531  $ilDB->quote($a_user_id, "integer").",".
532  $ilDB->quote($now, "timestamp").",".
533  $ilDB->quote($skill_id, "integer").",".
534  $ilDB->quote($trigger_ref_id, "integer").",".
535  $ilDB->quote($trigger_obj_id, "integer").",".
536  $ilDB->quote($trigger_title, "text").
537  ")");
538  }
539  }
540  }
541 
548  static function getCompletionDateForTriggerRefId($a_user_id, $a_ref_id = null)
549  {
550  global $ilDB;
551 
552  if ($a_ref_id == "")
553  {
554  $a_ref_id = null;
555  }
556  else if (!is_array($a_ref_id))
557  {
558  $a_ref_id = array($a_ref_id);
559  }
560  if (!is_array($a_user_id))
561  {
562  $a_user_id = array($a_user_id);
563  }
564 
565  if ($a_ref_id != null)
566  {
567  if (!is_array($a_ref_id))
568  {
569  $a_ref_id = array($a_ref_id);
570  }
571 
572  $set = $ilDB->query($q = "SELECT user_id, status_date, trigger_ref_id FROM skl_user_has_level WHERE ".
573  $ilDB->in("user_id", $a_user_id, false, "integer")." AND ".
574  $ilDB->in("trigger_ref_id", $a_ref_id, false, "integer")
575  );
576 
577  $completion = array();
578  while ($rec = $ilDB->fetchAssoc($set))
579  {
580  if (!isset($completion[$rec["user_id"]][$rec["trigger_ref_id"]]))
581  {
582  $completion[$rec["user_id"]][$rec["trigger_ref_id"]] = $rec["status_date"];
583  }
584  else if ($rec["status_date"] < $completion[$rec["user_id"]][$rec["trigger_ref_id"]])
585  {
586  $completion[$rec["user_id"]][$rec["trigger_ref_id"]] = $rec["status_date"];
587  }
588  }
589 
590  foreach ($a_ref_id as $ref_id)
591  {
592  foreach ($a_user_id as $user_id)
593  {
594  if (!isset($completion[$user_id][$ref_id]))
595  {
596  $completion[$user_id][$ref_id] = false;
597  }
598  }
599  }
600  }
601  else
602  {
603  $completion = array();
604  foreach ($a_user_id as $user_id)
605  {
606  include_once 'Services/Membership/classes/class.ilParticipants.php';
607  $a_ref_id = ilParticipants::_getMembershipByType($user_id, 'crs', true);
608 
609  $set = $ilDB->query($q = "SELECT user_id, status_date, trigger_ref_id FROM skl_user_has_level WHERE ".
610  "user_id = ".$ilDB->quote($user_id, "integer")." AND ".
611  $ilDB->in("trigger_ref_id", $a_ref_id, false, "integer")
612  );
613 
614  while ($rec = $ilDB->fetchAssoc($set))
615  {
616  if (!isset($completion[$user_id][$rec["trigger_ref_id"]]))
617  {
618  $completion[$user_id][$rec["trigger_ref_id"]] = $rec["status_date"];
619  }
620  else if ($rec["status_date"] < $completion[$user_id][$rec["trigger_ref_id"]])
621  {
622  $completion[$user_id][$rec["trigger_ref_id"]] = $rec["status_date"];
623  }
624  }
625  foreach ($a_ref_id as $ref_id)
626  {
627  if (!isset($completion[$user_id][$ref_id]))
628  {
629  $completion[$user_id][$ref_id] = false;
630  }
631  }
632  }
633 
634  }
635  return $completion;
636  }
637 
644  static function checkUserCertificateForTriggerRefId($a_user_id, $a_ref_id)
645  {
646  global $ilDB;
647 
648  if (!is_array($a_ref_id))
649  {
650  $a_ref_id = array($a_ref_id);
651  }
652  if (!is_array($a_user_id))
653  {
654  $a_user_id = array($a_user_id);
655  }
656  $set = $ilDB->query($q = "SELECT user_id, status_date, trigger_ref_id, level_id, skill_id FROM skl_user_has_level WHERE ".
657  $ilDB->in("user_id", $a_user_id, false, "integer")." AND ".
658  $ilDB->in("trigger_ref_id", $a_ref_id, false, "integer")
659  );
660 
661  $completion = array();
662 
663  $completion = array();
664  while ($rec = $ilDB->fetchAssoc($set))
665  {
666  if (!isset($completion[$rec["user_id"]][$rec["trigger_ref_id"]])
667  && ilBasicSkill::_lookupCertificate($rec["skill_id"], $rec["level_id"]))
668  {
669  $completion[$rec["user_id"]][$rec["trigger_ref_id"]] = true;
670  }
671  }
672 
673  foreach ($a_ref_id as $ref_id)
674  {
675  foreach ($a_user_id as $user_id)
676  {
677  if (!isset($completion[$user_id][$ref_id]))
678  {
679  $completion[$user_id][$ref_id] = false;
680  }
681  }
682  }
683  return $completion;
684 
685  }
686 
693  function lookupLevelAchievementDate($a_user_id, $a_level_id)
694  {
695  global $ilDB;
696 
697  $set = $ilDB->query("SELECT user_id, status_date, trigger_ref_id, level_id, skill_id FROM skl_user_has_level WHERE ".
698  " user_id = ".$ilDB->quote($a_user_id, "integer").
699  " AND user_id = ".$ilDB->quote($a_user_id, "integer")
700  );
701  if ($rec = $ilDB->fetchAssoc($set))
702  {
703  return $rec["status_date"];
704  }
705 
706  return false;
707  }
708 
715  static function getTriggerOfAllCertificates($a_user_id)
716  {
717  global $ilDB, $tree;
718 
719  if (!is_array($a_user_id))
720  {
721  $a_user_id = array($a_user_id);
722  }
723 
724  $set = $ilDB->query($q = "SELECT user_id, status_date, trigger_ref_id, level_id, skill_id FROM skl_user_has_level WHERE ".
725  $ilDB->in("user_id", $a_user_id, false, "integer")
726  );
727 
728  $completion = array();
729 
730  while ($rec = $ilDB->fetchAssoc($set))
731  {
732  if ((!is_array($completion[$rec["user_id"]]) || !in_array($rec["trigger_ref_id"], $completion[$rec["user_id"]]))
733  && ilBasicSkill::_lookupCertificate($rec["skill_id"], $rec["level_id"]))
734  {
735  if ($tree->isInTree($rec["trigger_ref_id"]))
736  {
737  $completion[$rec["user_id"]][] = $rec["trigger_ref_id"];
738  }
739  }
740  }
741 
742  foreach ($a_user_id as $user_id)
743  {
744  if (!is_array($completion[$user_id]))
745  {
746  $completion[$user_id] = array();
747  }
748  }
749  return $completion;
750  }
751 
758  static function getSkillLevelsForTrigger($a_ref_id)
759  {
760  global $ilDB;
761 
762  $set = $ilDB->query($q = "SELECT id FROM skl_level".
763  " WHERE trigger_ref_id = ".$ilDB->quote($a_ref_id, "integer"));
764 
765  $skill_levels = array();
766  while ($rec = $ilDB->fetchAssoc($set))
767  {
768  $skill_levels[] = $rec["id"];
769  }
770 
771  return $skill_levels;
772 
773  }
774 
775  //
776  //
777  // Certificate related methods
778  //
779  //
780 
787  function getTitleForCertificate()
788  {
789  return $this->getTitle();
790  }
791 
798  function getLevelTitleForCertificate($a_level_id)
799  {
800  return ilBasicSkill::lookupLevelTitle($a_level_id);
801  }
802 
809  function getTriggerTitleForCertificate($a_level_id)
810  {
811  $tr = ilBasicSkill::lookupLevelTrigger($a_level_id);
812  return ilObject::_lookupTitle($tr["obj_id"]);
813  }
814 
821  function getShortTitleForCertificate()
822  {
823  return "Skill";
824  }
825 
832  public static function _lookupCertificate($a_skill_id, $a_skill_level_id)
833  {
834  $certificatefile = CLIENT_WEB_DIR."/certificates/skill/".
835  ((int)$a_skill_id)."/".((int) $a_skill_level_id)."/certificate.xml";
836  if (@file_exists($certificatefile))
837  {
838  return true;
839  }
840  else
841  {
842  return false;
843  }
844  }
845 
846 
847 
848 }
849 ?>