1<?php 2 3/* Copyright (c) 1998-2013 ILIAS open source, Extended GPL, see docs/LICENSE */ 4 5include_once("./Services/Skill/classes/class.ilSkillTreeNode.php"); 6include_once("./Services/Skill/interfaces/interface.ilSkillUsageInfo.php"); 7 8/** 9 * Basic Skill 10 * 11 * @author Alex Killing <alex.killing@gmx.de> 12 * @version $Id$ 13 * 14 * @ingroup ServicesSkill 15 */ 16class ilBasicSkill extends ilSkillTreeNode implements ilSkillUsageInfo 17{ 18 /** 19 * @var ilDB 20 */ 21 protected $db; 22 23 /** 24 * @var ilObjUser 25 */ 26 protected $user; 27 28 const ACHIEVED = 1; 29 const NOT_ACHIEVED = 0; 30 31 const EVAL_BY_OTHERS_ = 0; 32 const EVAL_BY_SELF = 1; 33 const EVAL_BY_ALL = 2; 34 35 public $id; 36 37 /** 38 * Constructor 39 * @access public 40 */ 41 public function __construct($a_id = 0) 42 { 43 global $DIC; 44 45 $this->db = $DIC->database(); 46 $this->user = $DIC->user(); 47 parent::__construct($a_id); 48 $this->setType("skll"); 49 } 50 51 /** 52 * Read data from database 53 */ 54 public function read() 55 { 56 parent::read(); 57 } 58 59 /** 60 * Create skill 61 * 62 */ 63 public function create() 64 { 65 parent::create(); 66 } 67 68 /** 69 * Delete skill 70 */ 71 public function delete() 72 { 73 $ilDB = $this->db; 74 75 $ilDB->manipulate( 76 "DELETE FROM skl_level WHERE " 77 . " skill_id = " . $ilDB->quote($this->getId(), "integer") 78 ); 79 80 $ilDB->manipulate( 81 "DELETE FROM skl_user_has_level WHERE " 82 . " skill_id = " . $ilDB->quote($this->getId(), "integer") 83 ); 84 85 parent::delete(); 86 } 87 88 /** 89 * Copy basic skill 90 */ 91 public function copy() 92 { 93 $skill = new ilBasicSkill(); 94 $skill->setTitle($this->getTitle()); 95 $skill->setDescription($this->getDescription()); 96 $skill->setType($this->getType()); 97 $skill->setSelfEvaluation($this->getSelfEvaluation()); 98 $skill->setOrderNr($this->getOrderNr()); 99 $skill->create(); 100 101 $levels = $this->getLevelData(); 102 if (sizeof($levels)) { 103 foreach ($levels as $item) { 104 $skill->addLevel($item["title"], $item["description"]); 105 } 106 } 107 $skill->update(); 108 109 return $skill; 110 } 111 112 // 113 // 114 // Skill level related methods 115 // 116 // 117 118 /** 119 * Add new level 120 * 121 * @param string title 122 * @param string description 123 */ 124 public function addLevel($a_title, $a_description, $a_import_id = "") 125 { 126 $ilDB = $this->db; 127 128 $nr = $this->getMaxLevelNr(); 129 $nid = $ilDB->nextId("skl_level"); 130 $ilDB->insert("skl_level", array( 131 "id" => array("integer", $nid), 132 "skill_id" => array("integer", $this->getId()), 133 "nr" => array("integer", $nr + 1), 134 "title" => array("text", $a_title), 135 "description" => array("clob", $a_description), 136 "import_id" => array("text", $a_import_id), 137 "creation_date" => array("timestamp", ilUtil::now()) 138 )); 139 } 140 141 /** 142 * Get maximum level nr 143 * 144 * @return int maximum level nr of skill 145 */ 146 public function getMaxLevelNr() 147 { 148 $ilDB = $this->db; 149 150 $set = $ilDB->query( 151 "SELECT MAX(nr) mnr FROM skl_level WHERE " . 152 " skill_id = " . $ilDB->quote($this->getId(), "integer") 153 ); 154 $rec = $ilDB->fetchAssoc($set); 155 return (int) $rec["mnr"]; 156 } 157 158 /** 159 * Get level data 160 * 161 * @return array level data 162 */ 163 public function getLevelData($a_id = 0) 164 { 165 $ilDB = $this->db; 166 167 if ($a_id > 0) { 168 $and = " AND id = " . $ilDB->quote($a_id, "integer"); 169 } 170 171 $set = $ilDB->query( 172 "SELECT * FROM skl_level WHERE " . 173 " skill_id = " . $ilDB->quote($this->getId(), "integer") . 174 $and . 175 " ORDER BY nr" 176 ); 177 $levels = array(); 178 while ($rec = $ilDB->fetchAssoc($set)) { 179 if ($a_id > 0) { 180 return $rec; 181 } 182 $levels[] = $rec; 183 } 184 return $levels; 185 } 186 187 /** 188 * Lookup level property 189 * 190 * @param id level id 191 * @return mixed property value 192 */ 193 protected static function lookupLevelProperty($a_id, $a_prop) 194 { 195 global $DIC; 196 197 $ilDB = $DIC->database(); 198 199 $set = $ilDB->query( 200 "SELECT $a_prop FROM skl_level WHERE " . 201 " id = " . $ilDB->quote($a_id, "integer") 202 ); 203 $rec = $ilDB->fetchAssoc($set); 204 return $rec[$a_prop]; 205 } 206 207 /** 208 * Lookup level title 209 * 210 * @param int level id 211 * @return string level title 212 */ 213 public static function lookupLevelTitle($a_id) 214 { 215 return ilBasicSkill::lookupLevelProperty($a_id, "title"); 216 } 217 218 /** 219 * Lookup level description 220 * 221 * @param int level id 222 * @return string level description 223 */ 224 public static function lookupLevelDescription($a_id) 225 { 226 return ilBasicSkill::lookupLevelProperty($a_id, "description"); 227 } 228 229 /** 230 * Lookup level skill id 231 * 232 * @param int level id 233 * @return string skill id 234 */ 235 public static function lookupLevelSkillId($a_id) 236 { 237 return ilBasicSkill::lookupLevelProperty($a_id, "skill_id"); 238 } 239 240 /** 241 * Write level property 242 * 243 * @param 244 * @return 245 */ 246 protected static function writeLevelProperty($a_id, $a_prop, $a_value, $a_type) 247 { 248 global $DIC; 249 250 $ilDB = $DIC->database(); 251 252 $ilDB->update("skl_level", array( 253 $a_prop => array($a_type, $a_value), 254 ), array( 255 "id" => array("integer", $a_id), 256 )); 257 } 258 259 /** 260 * Write level title 261 * 262 * @param int level id 263 * @param text level title 264 */ 265 public static function writeLevelTitle($a_id, $a_title) 266 { 267 ilBasicSkill::writeLevelProperty($a_id, "title", $a_title, "text"); 268 } 269 270 /** 271 * Write level description 272 * 273 * @param int level id 274 * @param text level description 275 */ 276 public static function writeLevelDescription($a_id, $a_description) 277 { 278 ilBasicSkill::writeLevelProperty($a_id, "description", $a_description, "clob"); 279 } 280 281 /** 282 * Update level order 283 * 284 * @param 285 * @return 286 */ 287 public function updateLevelOrder($order) 288 { 289 $ilDB = $this->db; 290 291 asort($order); 292 293 $cnt = 1; 294 foreach ($order as $id => $o) { 295 $ilDB->manipulate( 296 "UPDATE skl_level SET " . 297 " nr = " . $ilDB->quote($cnt, "integer") . 298 " WHERE id = " . $ilDB->quote($id, "integer") 299 ); 300 $cnt++; 301 } 302 } 303 304 /** 305 * Delete level 306 * 307 * @param 308 * @return 309 */ 310 public function deleteLevel($a_id) 311 { 312 $ilDB = $this->db; 313 314 $ilDB->manipulate( 315 "DELETE FROM skl_level WHERE " 316 . " id = " . $ilDB->quote($a_id, "integer") 317 ); 318 } 319 320 /** 321 * Fix level numbering 322 * 323 * @param 324 * @return 325 */ 326 public function fixLevelNumbering() 327 { 328 $ilDB = $this->db; 329 330 $set = $ilDB->query( 331 "SELECT id, nr FROM skl_level WHERE " . 332 " skill_id = " . $ilDB->quote($this->getId(), "integer") . 333 " ORDER BY nr ASC" 334 ); 335 $cnt = 1; 336 while ($rec = $ilDB->fetchAssoc($set)) { 337 $ilDB->manipulate( 338 "UPDATE skl_level SET " . 339 " nr = " . $ilDB->quote($cnt, "integer") . 340 " WHERE id = " . $ilDB->quote($rec["id"], "integer") 341 ); 342 $cnt++; 343 } 344 } 345 346 /** 347 * Get skill for level id 348 * 349 * @param 350 * @return 351 */ 352 public function getSkillForLevelId($a_level_id) 353 { 354 $ilDB = $this->db; 355 356 $set = $ilDB->query( 357 "SELECT * FROM skl_level WHERE " . 358 " id = " . $ilDB->quote($a_level_id, "integer") 359 ); 360 $skill = null; 361 if ($rec = $ilDB->fetchAssoc($set)) { 362 if (ilSkillTreeNode::isInTree($rec["skill_id"])) { 363 $skill = new ilBasicSkill($rec["skill_id"]); 364 } 365 } 366 return $skill; 367 } 368 369 // 370 // 371 // User skill (level) related methods 372 // 373 // 374 375 376 /** 377 * Reset skill level status. This is currently only used for self evaluations with a "no competence" level. 378 * It has to be discussed, how this should be provided for non-self-evaluations. 379 * 380 * @param int $a_user_id user id 381 * @param int $a_skill_id skill id 382 * @param int $a_tref_id skill tref id 383 * @param int $a_trigger_ref_id triggering repository object ref id 384 * @param bool $a_self_eval currently needs to be set to true 385 * 386 * @throws ilSkillException 387 */ 388 public static function resetUserSkillLevelStatus($a_user_id, $a_skill_id, $a_tref_id = 0, $a_trigger_ref_id = 0, $a_self_eval = false) 389 { 390 global $DIC; 391 392 $db = $DIC->database(); 393 394 if (!$a_self_eval) { 395 include_once("./Services/Skill/exceptions/class.ilSkillException.php"); 396 throw new ilSkillException("resetUserSkillLevelStatus currently only provided for self evaluations."); 397 } 398 399 $trigger_obj_id = ($a_trigger_ref_id > 0) 400 ? ilObject::_lookupObjId($a_trigger_ref_id) 401 : 0; 402 403 $update = false; 404 $status_date = self::hasRecentSelfEvaluation($a_user_id, $a_skill_id, $a_tref_id, $a_trigger_ref_id); 405 if ($status_date != "") { 406 $update = true; 407 } 408 409 if ($update) { 410 // this will only be set in self eval case, means this will always have a $rec 411 $now = ilUtil::now(); 412 $db->manipulate( 413 "UPDATE skl_user_skill_level SET " . 414 " level_id = " . $db->quote(0, "integer") . "," . 415 " status_date = " . $db->quote($now, "timestamp") . 416 " WHERE user_id = " . $db->quote($a_user_id, "integer") . 417 " AND status_date = " . $db->quote($status_date, "timestamp") . 418 " AND skill_id = " . $db->quote($a_skill_id, "integer") . 419 " AND status = " . $db->quote(self::ACHIEVED, "integer") . 420 " AND trigger_obj_id = " . $db->quote($trigger_obj_id, "integer") . 421 " AND tref_id = " . $db->quote((int) $a_tref_id, "integer") . 422 " AND self_eval = " . $db->quote($a_self_eval, "integer") 423 ); 424 } else { 425 $now = ilUtil::now(); 426 $db->manipulate("INSERT INTO skl_user_skill_level " . 427 "(level_id, user_id, tref_id, status_date, skill_id, status, valid, trigger_ref_id," . 428 "trigger_obj_id, trigger_obj_type, trigger_title, self_eval, unique_identifier) VALUES (" . 429 $db->quote(0, "integer") . "," . 430 $db->quote($a_user_id, "integer") . "," . 431 $db->quote((int) $a_tref_id, "integer") . "," . 432 $db->quote($now, "timestamp") . "," . 433 $db->quote($a_skill_id, "integer") . "," . 434 $db->quote(self::ACHIEVED, "integer") . "," . 435 $db->quote(1, "integer") . "," . 436 $db->quote($a_trigger_ref_id, "integer") . "," . 437 $db->quote($trigger_obj_id, "integer") . "," . 438 $db->quote("", "text") . "," . 439 $db->quote("", "text") . "," . 440 $db->quote($a_self_eval, "integer") . "," . 441 $db->quote("", "text") . 442 ")"); 443 } 444 445 $db->manipulate( 446 "DELETE FROM skl_user_has_level WHERE " 447 . " user_id = " . $db->quote($a_user_id, "integer") 448 . " AND skill_id = " . $db->quote($a_skill_id, "integer") 449 . " AND tref_id = " . $db->quote((int) $a_tref_id, "integer") 450 . " AND trigger_obj_id = " . $db->quote($trigger_obj_id, "integer") 451 . " AND self_eval = " . $db->quote($a_self_eval, "integer") 452 ); 453 } 454 455 /** 456 * Has recent self evaluation. Check if self evaluation for user/object has been done on the same day 457 * already 458 * 459 * @param 460 * @return 461 */ 462 protected static function hasRecentSelfEvaluation($a_user_id, $a_skill_id, $a_tref_id = 0, $a_trigger_ref_id = 0) 463 { 464 global $DIC; 465 466 $db = $DIC->database(); 467 468 $trigger_obj_id = ($a_trigger_ref_id > 0) 469 ? ilObject::_lookupObjId($a_trigger_ref_id) 470 : 0; 471 472 $recent = ""; 473 474 $db->setLimit(1); 475 $set = $db->query( 476 "SELECT * FROM skl_user_skill_level WHERE " . 477 "skill_id = " . $db->quote($a_skill_id, "integer") . " AND " . 478 "user_id = " . $db->quote($a_user_id, "integer") . " AND " . 479 "tref_id = " . $db->quote((int) $a_tref_id, "integer") . " AND " . 480 "trigger_obj_id = " . $db->quote($trigger_obj_id, "integer") . " AND " . 481 "self_eval = " . $db->quote(1, "integer") . 482 " ORDER BY status_date DESC" 483 ); 484 $rec = $db->fetchAssoc($set); 485 $status_day = substr($rec["status_date"], 0, 10); 486 $today = substr(ilUtil::now(), 0, 10); 487 if ($rec["valid"] && $rec["status"] == ilBasicSkill::ACHIEVED && $status_day == $today) { 488 $recent = $rec["status_date"]; 489 } 490 491 return $recent; 492 } 493 494 /** 495 * Get new achievements 496 * 497 * @param string $a_timestamp 498 * @return array 499 */ 500 public static function getNewAchievementsPerUser($a_timestamp, $a_timestamp_to = null, $a_user_id = 0, $a_self_eval = 0) 501 { 502 global $DIC; 503 504 $db = $DIC->database(); 505 506 $to = (!is_null($a_timestamp_to)) 507 ? " AND status_date <= " . $db->quote($a_timestamp_to, "timestamp") 508 : ""; 509 510 $user = ($a_user_id > 0) 511 ? " AND user_id = " . $db->quote($a_user_id, "integer") 512 : ""; 513 514 $set = $db->query("SELECT * FROM skl_user_skill_level " . 515 " WHERE status_date >= " . $db->quote($a_timestamp, "timestamp") . 516 " AND valid = " . $db->quote(1, "integer") . 517 " AND status = " . $db->quote(ilBasicSkill::ACHIEVED, "integer") . 518 " AND self_eval = " . $db->quote($a_self_eval, "integer") . 519 $to . 520 $user . 521 " ORDER BY user_id, status_date ASC "); 522 $achievments = array(); 523 while ($rec = $db->fetchAssoc($set)) { 524 $achievments[$rec["user_id"]][] = $rec; 525 } 526 527 return $achievments; 528 } 529 530 531 /** 532 * Write skill level status 533 * 534 * @param int $a_level_id skill level id 535 * @param int $a_user_id user id 536 * @param int $a_trigger_ref_id trigger repository object ref id 537 * @param int $a_tref_id skill tref id 538 * @param int $a_status DEPRECATED, always use ilBasicSkill::ACHIEVED 539 * @param bool $a_force DEPRECATED 540 * @param bool $a_self_eval self evaluation 541 * @param string $a_unique_identifier a unique identifier (should be used with trigger_ref_id > 0) 542 */ 543 public static function writeUserSkillLevelStatus( 544 $a_level_id, 545 $a_user_id, 546 $a_trigger_ref_id, 547 $a_tref_id = 0, 548 $a_status = ilBasicSkill::ACHIEVED, 549 $a_force = false, 550 $a_self_eval = false, 551 $a_unique_identifier = "" 552 ) { 553 global $DIC; 554 555 $ilDB = $DIC->database(); 556 557 $skill_id = ilBasicSkill::lookupLevelSkillId($a_level_id); 558 $trigger_ref_id = $a_trigger_ref_id; 559 $trigger_obj_id = ilObject::_lookupObjId($trigger_ref_id); 560 $trigger_title = ilObject::_lookupTitle($trigger_obj_id); 561 $trigger_type = ilObject::_lookupType($trigger_obj_id); 562 563 $update = false; 564 565 // self evaluations will update, if the last self evaluation is on the same day 566 if ($a_self_eval && self::hasRecentSelfEvaluation($a_user_id, $skill_id, $a_tref_id, $trigger_ref_id)) { 567 $status_date = self::hasRecentSelfEvaluation($a_user_id, $skill_id, $a_tref_id, $trigger_ref_id); 568 if ($status_date != "") { 569 $update = true; 570 } 571 } 572 573 if ($update) { 574 // this will only be set in self eval case, means this will always have a $rec 575 $now = ilUtil::now(); 576 $ilDB->manipulate( 577 "UPDATE skl_user_skill_level SET " . 578 " level_id = " . $ilDB->quote($a_level_id, "integer") . "," . 579 " status_date = " . $ilDB->quote($now, "timestamp") . 580 " WHERE user_id = " . $ilDB->quote($a_user_id, "integer") . 581 " AND status_date = " . $ilDB->quote($status_date, "timestamp") . 582 " AND skill_id = " . $ilDB->quote($skill_id, "integer") . 583 " AND status = " . $ilDB->quote($a_status, "integer") . 584 " AND trigger_obj_id = " . $ilDB->quote($trigger_obj_id, "integer") . 585 " AND tref_id = " . $ilDB->quote((int) $a_tref_id, "integer") . 586 " AND self_eval = " . $ilDB->quote($a_self_eval, "integer") 587 ); 588 } else { 589 if ($a_unique_identifier != "") { 590 $ilDB->manipulate( 591 "DELETE FROM skl_user_skill_level WHERE " . 592 " user_id = " . $ilDB->quote($a_user_id, "integer") . 593 " AND tref_id = " . $ilDB->quote($a_tref_id, "integer") . 594 " AND skill_id = " . $ilDB->quote($skill_id, "integer") . 595 " AND trigger_ref_id = " . $ilDB->quote($trigger_ref_id, "integer") . 596 " AND trigger_obj_id = " . $ilDB->quote($trigger_obj_id, "integer") . 597 " AND self_eval = " . $ilDB->quote($a_self_eval, "integer") . 598 " AND unique_identifier = " . $ilDB->quote($a_unique_identifier, "text") 599 ); 600 } 601 602 $now = ilUtil::now(); 603 $ilDB->manipulate("INSERT INTO skl_user_skill_level " . 604 "(level_id, user_id, tref_id, status_date, skill_id, status, valid, trigger_ref_id," . 605 "trigger_obj_id, trigger_obj_type, trigger_title, self_eval, unique_identifier) VALUES (" . 606 $ilDB->quote($a_level_id, "integer") . "," . 607 $ilDB->quote($a_user_id, "integer") . "," . 608 $ilDB->quote((int) $a_tref_id, "integer") . "," . 609 $ilDB->quote($now, "timestamp") . "," . 610 $ilDB->quote($skill_id, "integer") . "," . 611 $ilDB->quote($a_status, "integer") . "," . 612 $ilDB->quote(1, "integer") . "," . 613 $ilDB->quote($trigger_ref_id, "integer") . "," . 614 $ilDB->quote($trigger_obj_id, "integer") . "," . 615 $ilDB->quote($trigger_type, "text") . "," . 616 $ilDB->quote($trigger_title, "text") . "," . 617 $ilDB->quote($a_self_eval, "integer") . "," . 618 $ilDB->quote($a_unique_identifier, "text") . 619 ")"); 620 } 621 622 // fix (removed level_id and added skill id, since table should hold only 623 // one entry per skill) 624 $ilDB->manipulate( 625 "DELETE FROM skl_user_has_level WHERE " 626 . " user_id = " . $ilDB->quote($a_user_id, "integer") 627 . " AND skill_id = " . $ilDB->quote($skill_id, "integer") 628 . " AND tref_id = " . $ilDB->quote((int) $a_tref_id, "integer") 629 . " AND trigger_obj_id = " . $ilDB->quote($trigger_obj_id, "integer") 630 . " AND self_eval = " . $ilDB->quote($a_self_eval, "integer") 631 ); 632 633 if ($a_status == ilBasicSkill::ACHIEVED) { 634 $ilDB->manipulate("INSERT INTO skl_user_has_level " . 635 "(level_id, user_id, tref_id, status_date, skill_id, trigger_ref_id, trigger_obj_id, trigger_obj_type, trigger_title, self_eval) VALUES (" . 636 $ilDB->quote($a_level_id, "integer") . "," . 637 $ilDB->quote($a_user_id, "integer") . "," . 638 $ilDB->quote($a_tref_id, "integer") . "," . 639 $ilDB->quote($now, "timestamp") . "," . 640 $ilDB->quote($skill_id, "integer") . "," . 641 $ilDB->quote($trigger_ref_id, "integer") . "," . 642 $ilDB->quote($trigger_obj_id, "integer") . "," . 643 $ilDB->quote($trigger_type, "text") . "," . 644 $ilDB->quote($trigger_title, "text") . "," . 645 $ilDB->quote($a_self_eval, "integer") . 646 ")"); 647 } 648 } 649 650 /** 651 * Remove a user skill completely 652 * 653 * @param int $a_user_id user id 654 * @param int $a_trigger_obj_id triggering repository object obj id 655 * @param bool $a_self_eval currently needs to be set to true 656 * @param string $a_unique_identifier unique identifier string 657 * @return bool true, if entries have been deleted, otherwise false 658 */ 659 public static function removeAllUserSkillLevelStatusOfObject($a_user_id, $a_trigger_obj_id, $a_self_eval = false, $a_unique_identifier = "") 660 { 661 global $DIC; 662 663 $db = $DIC->database(); 664 665 if ($a_trigger_obj_id == 0) { 666 return false; 667 } 668 669 $changed = false; 670 671 $aff_rows = $db->manipulate( 672 "DELETE FROM skl_user_skill_level WHERE " 673 . " user_id = " . $db->quote($a_user_id, "integer") 674 . " AND trigger_obj_id = " . $db->quote($a_trigger_obj_id, "integer") 675 . " AND self_eval = " . $db->quote($a_self_eval, "integer") 676 . " AND unique_identifier = " . $db->quote($a_unique_identifier, "text") 677 ); 678 if ($aff_rows > 0) { 679 $changed = true; 680 } 681 682 $aff_rows = $db->manipulate( 683 "DELETE FROM skl_user_has_level WHERE " 684 . " user_id = " . $db->quote($a_user_id, "integer") 685 . " AND trigger_obj_id = " . $db->quote($a_trigger_obj_id, "integer") 686 . " AND self_eval = " . $db->quote($a_self_eval, "integer") 687 ); 688 if ($aff_rows > 0) { 689 $changed = true; 690 } 691 return $changed; 692 } 693 694 /** 695 * Remove all data of a user 696 * 697 * @param int $a_user_id 698 */ 699 public static function removeAllUserData($a_user_id) 700 { 701 global $DIC; 702 703 $db = $DIC->database(); 704 705 $db->manipulate( 706 "DELETE FROM skl_user_skill_level WHERE " 707 . " user_id = " . $db->quote($a_user_id, "integer") 708 ); 709 $db->manipulate( 710 "DELETE FROM skl_user_has_level WHERE " 711 . " user_id = " . $db->quote($a_user_id, "integer") 712 ); 713 } 714 715 716 /** 717 * Get max levels per type 718 * 719 * @param 720 * @return 721 */ 722 public function getMaxLevelPerType($a_tref_id, $a_type, $a_user_id = 0, $a_self_eval = 0) 723 { 724 $ilDB = $this->db; 725 $ilUser = $this->user; 726 727 if ($a_user_id == 0) { 728 $a_user_id = $ilUser->getId(); 729 } 730 731 $set = $ilDB->query( 732 $q = "SELECT level_id FROM skl_user_has_level " . 733 " WHERE trigger_obj_type = " . $ilDB->quote($a_type, "text") . 734 " AND skill_id = " . $ilDB->quote($this->getId(), "integer") . 735 " AND tref_id = " . $ilDB->quote((int) $a_tref_id, "integer") . 736 " AND user_id = " . $ilDB->quote($a_user_id, "integer") . 737 " AND self_eval = " . $ilDB->quote($a_self_eval, "integer") 738 ); 739 740 $has_level = array(); 741 while ($rec = $ilDB->fetchAssoc($set)) { 742 $has_level[$rec["level_id"]] = true; 743 } 744 $max_level = 0; 745 foreach ($this->getLevelData() as $l) { 746 if (isset($has_level[$l["id"]])) { 747 $max_level = $l["id"]; 748 } 749 } 750 return $max_level; 751 } 752 753 /** 754 * Get all level entries 755 * 756 * @param 757 * @return 758 */ 759 public function getAllLevelEntriesOfUser($a_tref_id, $a_user_id = 0, $a_self_eval = 0) 760 { 761 $ilDB = $this->db; 762 $ilUser = $this->user; 763 764 if ($a_user_id == 0) { 765 $a_user_id = $ilUser->getId(); 766 } 767 768 $set = $ilDB->query( 769 $q = "SELECT * FROM skl_user_has_level " . 770 " WHERE skill_id = " . $ilDB->quote($this->getId(), "integer") . 771 " AND tref_id = " . $ilDB->quote((int) $a_tref_id, "integer") . 772 " AND user_id = " . $ilDB->quote($a_user_id, "integer") . 773 " AND self_eval = " . $ilDB->quote($a_self_eval, "integer") . 774 " ORDER BY status_date DESC" 775 ); 776 777 $levels = array(); 778 while ($rec = $ilDB->fetchAssoc($set)) { 779 $levels[] = $rec; 780 } 781 return $levels; 782 } 783 784 /** 785 * Get all historic level entries 786 * 787 * @param 788 * @return 789 */ 790 public function getAllHistoricLevelEntriesOfUser($a_tref_id, $a_user_id = 0, $a_eval_by = 0) 791 { 792 $ilDB = $this->db; 793 $ilUser = $this->user; 794 795 if ($a_user_id == 0) { 796 $a_user_id = $ilUser->getId(); 797 } 798 799 $by = ($a_eval_by != self::EVAL_BY_ALL) 800 ? " AND self_eval = " . $ilDB->quote($a_self_eval, "integer") 801 : ""; 802 803 $set = $ilDB->query( 804 $q = "SELECT * FROM skl_user_skill_level " . 805 " WHERE skill_id = " . $ilDB->quote($this->getId(), "integer") . 806 " AND tref_id = " . $ilDB->quote((int) $a_tref_id, "integer") . 807 " AND user_id = " . $ilDB->quote($a_user_id, "integer") . 808 $by . 809 " ORDER BY status_date DESC" 810 ); 811 $levels = array(); 812 while ($rec = $ilDB->fetchAssoc($set)) { 813 $levels[] = $rec; 814 } 815 return $levels; 816 } 817 818 819 /** 820 * Get max levels per object 821 * 822 * @param 823 * @return 824 */ 825 public function getMaxLevelPerObject($a_tref_id, $a_object_id, $a_user_id = 0, $a_self_eval = 0) 826 { 827 $ilDB = $this->db; 828 $ilUser = $this->user; 829 830 if ($a_user_id == 0) { 831 $a_user_id = $ilUser->getId(); 832 } 833 834 $set = $ilDB->query( 835 $q = "SELECT level_id FROM skl_user_has_level " . 836 " WHERE trigger_obj_id = " . $ilDB->quote($a_object_id, "integer") . 837 " AND skill_id = " . $ilDB->quote($this->getId(), "integer") . 838 " AND tref_id = " . $ilDB->quote((int) $a_tref_id, "integer") . 839 " AND user_id = " . $ilDB->quote($a_user_id, "integer") . 840 " AND self_eval = " . $ilDB->quote($a_self_eval, "integer") 841 ); 842 843 $has_level = array(); 844 while ($rec = $ilDB->fetchAssoc($set)) { 845 $has_level[$rec["level_id"]] = true; 846 } 847 $max_level = 0; 848 foreach ($this->getLevelData() as $l) { 849 if (isset($has_level[$l["id"]])) { 850 $max_level = $l["id"]; 851 } 852 } 853 return $max_level; 854 } 855 856 /** 857 * Get max levels per object 858 * 859 * @param 860 * @return 861 */ 862 public function getMaxLevel($a_tref_id, $a_user_id = 0, $a_self_eval = 0) 863 { 864 $ilDB = $this->db; 865 $ilUser = $this->user; 866 867 if ($a_user_id == 0) { 868 $a_user_id = $ilUser->getId(); 869 } 870 871 $set = $ilDB->query( 872 $q = "SELECT level_id FROM skl_user_has_level " . 873 " WHERE skill_id = " . $ilDB->quote($this->getId(), "integer") . 874 " AND tref_id = " . $ilDB->quote((int) $a_tref_id, "integer") . 875 " AND user_id = " . $ilDB->quote($a_user_id, "integer") . 876 " AND self_eval = " . $ilDB->quote($a_self_eval, "integer") 877 ); 878 879 $has_level = array(); 880 while ($rec = $ilDB->fetchAssoc($set)) { 881 $has_level[$rec["level_id"]] = true; 882 } 883 $max_level = 0; 884 foreach ($this->getLevelData() as $l) { 885 if (isset($has_level[$l["id"]])) { 886 $max_level = $l["id"]; 887 } 888 } 889 return $max_level; 890 } 891 892 893 /** 894 * Has use self evaluated a skill? 895 * 896 * @param int $a_user_id 897 * @param int $a_skill_id 898 * @param int $a_tref_id 899 * @return bool 900 */ 901 public static function hasSelfEvaluated($a_user_id, $a_skill_id, $a_tref_id) 902 { 903 global $DIC; 904 905 $db = $DIC->database(); 906 907 $set = $db->query( 908 $q = "SELECT level_id FROM skl_user_has_level " . 909 " WHERE skill_id = " . $db->quote((int) $a_skill_id, "integer") . 910 " AND tref_id = " . $db->quote((int) $a_tref_id, "integer") . 911 " AND user_id = " . $db->quote($a_user_id, "integer") . 912 " AND self_eval = " . $db->quote(1, "integer") 913 ); 914 915 if ($rec = $db->fetchAssoc($set)) { 916 return true; 917 } 918 return false; 919 } 920 921 /** 922 * Get last level set per object 923 * 924 * @param 925 * @return 926 */ 927 public function getLastLevelPerObject($a_tref_id, $a_object_id, $a_user_id = 0, $a_self_eval = 0) 928 { 929 $ilDB = $this->db; 930 $ilUser = $this->user; 931 932 if ($a_user_id == 0) { 933 $a_user_id = $ilUser->getId(); 934 } 935 936 $ilDB->setLimit(1); 937 $set = $ilDB->query( 938 $q = "SELECT level_id FROM skl_user_has_level " . 939 " WHERE trigger_obj_id = " . $ilDB->quote($a_object_id, "integer") . 940 " AND skill_id = " . $ilDB->quote($this->getId(), "integer") . 941 " AND tref_id = " . $ilDB->quote((int) $a_tref_id, "integer") . 942 " AND user_id = " . $ilDB->quote($a_user_id, "integer") . 943 " AND self_eval = " . $ilDB->quote($a_self_eval, "integer") . 944 " ORDER BY status_date DESC" 945 ); 946 947 $rec = $ilDB->fetchAssoc($set); 948 949 return $rec["level_id"]; 950 } 951 952 /** 953 * Get last update per object 954 * 955 * @param 956 * @return 957 */ 958 public function getLastUpdatePerObject($a_tref_id, $a_object_id, $a_user_id = 0, $a_self_eval = 0) 959 { 960 $ilDB = $this->db; 961 $ilUser = $this->user; 962 963 if ($a_user_id == 0) { 964 $a_user_id = $ilUser->getId(); 965 } 966 967 $ilDB->setLimit(1); 968 $set = $ilDB->query( 969 $q = "SELECT status_date FROM skl_user_has_level " . 970 " WHERE trigger_obj_id = " . $ilDB->quote($a_object_id, "integer") . 971 " AND skill_id = " . $ilDB->quote($this->getId(), "integer") . 972 " AND tref_id = " . $ilDB->quote((int) $a_tref_id, "integer") . 973 " AND user_id = " . $ilDB->quote($a_user_id, "integer") . 974 " AND self_eval = " . $ilDB->quote($a_self_eval, "integer") . 975 " ORDER BY status_date DESC" 976 ); 977 978 $rec = $ilDB->fetchAssoc($set); 979 980 return $rec["status_date"]; 981 } 982 983 // 984 // 985 // Certificate related methods 986 // 987 // 988 989 /** 990 * Get title for certificate 991 * 992 * @param 993 * @return 994 */ 995 public function getTitleForCertificate() 996 { 997 return $this->getTitle(); 998 } 999 1000 /** 1001 * Get short title for certificate 1002 * 1003 * @param 1004 * @return 1005 */ 1006 public function getShortTitleForCertificate() 1007 { 1008 return "Skill"; 1009 } 1010 1011 /** 1012 * Checks whether a skill level has a certificate or not 1013 * @param int skill id 1014 * @param int skill level id 1015 * @return true/false 1016 */ 1017 public static function _lookupCertificate($a_skill_id, $a_skill_level_id) 1018 { 1019 $certificatefile = CLIENT_WEB_DIR . "/certificates/skill/" . 1020 ((int) $a_skill_id) . "/" . ((int) $a_skill_level_id) . "/certificate.xml"; 1021 if (@file_exists($certificatefile)) { 1022 return true; 1023 } else { 1024 return false; 1025 } 1026 } 1027 1028 /** 1029 * Get usage info 1030 * 1031 * @param 1032 * @return 1033 */ 1034 public static function getUsageInfo($a_cskill_ids, &$a_usages) 1035 { 1036 global $DIC; 1037 1038 $ilDB = $DIC->database(); 1039 1040 include_once("./Services/Skill/classes/class.ilSkillUsage.php"); 1041 ilSkillUsage::getUsageInfoGeneric( 1042 $a_cskill_ids, 1043 $a_usages, 1044 ilSkillUsage::USER_ASSIGNED, 1045 "skl_user_skill_level", 1046 "user_id" 1047 ); 1048 } 1049 1050 /** 1051 * Get common skill ids for import IDs (newest first) 1052 * 1053 * @param int $a_source_inst_id source installation id, must be <>0 1054 * @param int $a_skill_import_id source skill id (type basic skill ("skll") or basic skill template ("sktp")) 1055 * @param int $a_tref_import_id source template reference id (if > 0 skill_import_id will be of type "sktp") 1056 * @return array array of common skill ids, keys are "skill_id", "tref_id", "creation_date" 1057 */ 1058 public static function getCommonSkillIdForImportId($a_source_inst_id, $a_skill_import_id, $a_tref_import_id = 0) 1059 { 1060 global $DIC; 1061 1062 $ilDB = $DIC->database(); 1063 1064 include_once("./Services/Skill/classes/class.ilSkillTree.php"); 1065 include_once("./Services/Skill/classes/class.ilSkillTemplateReference.php"); 1066 $tree = new ilSkillTree(); 1067 1068 if ($a_source_inst_id == 0) { 1069 return array(); 1070 } 1071 1072 $template_ids = array(); 1073 if ($a_tref_import_id > 0) { 1074 $skill_node_type = "sktp"; 1075 1076 // get all matching tref nodes 1077 $set = $ilDB->query("SELECT * FROM skl_tree_node n JOIN skl_tree t ON (n.obj_id = t.child) " . 1078 " WHERE n.import_id = " . $ilDB->quote("il_" . ((int) $a_source_inst_id) . "_sktr_" . $a_tref_import_id, "text") . 1079 " ORDER BY n.creation_date DESC "); 1080 while ($rec = $ilDB->fetchAssoc($set)) { 1081 if (($t = ilSkillTemplateReference::_lookupTemplateId($rec["obj_id"])) > 0) { 1082 $template_ids[$t] = $rec["obj_id"]; 1083 } 1084 } 1085 } else { 1086 $skill_node_type = "skll"; 1087 } 1088 $set = $ilDB->query("SELECT * FROM skl_tree_node n JOIN skl_tree t ON (n.obj_id = t.child) " . 1089 " WHERE n.import_id = " . $ilDB->quote("il_" . ((int) $a_source_inst_id) . "_" . $skill_node_type . "_" . $a_skill_import_id, "text") . 1090 " ORDER BY n.creation_date DESC "); 1091 $results = array(); 1092 while ($rec = $ilDB->fetchAssoc($set)) { 1093 $matching_trefs = array(); 1094 if ($a_tref_import_id > 0) { 1095 $skill_template_id = $tree->getTopParentNodeId($rec["obj_id"]); 1096 1097 // check of skill is in template 1098 foreach ($template_ids as $templ => $tref) { 1099 if ($skill_template_id == $templ) { 1100 $matching_trefs[] = $tref; 1101 } 1102 } 1103 } else { 1104 $matching_trefs = array(0); 1105 } 1106 1107 foreach ($matching_trefs as $t) { 1108 $results[] = array("skill_id" => $rec["obj_id"], "tref_id" => $t, "creation_date" => $rec["creation_date"]); 1109 } 1110 } 1111 return $results; 1112 } 1113 1114 /** 1115 * Get level ids for import IDs (newest first) 1116 * 1117 * @param int $a_source_inst_id source installation id, must be <>0 1118 * @param int $a_skill_import_id source skill id (type basic skill ("skll") or basic skill template ("sktp")) 1119 * @return array array of common skill ids, keys are "level_id", "creation_date" 1120 */ 1121 public static function getLevelIdForImportId($a_source_inst_id, $a_level_import_id) 1122 { 1123 global $DIC; 1124 1125 $ilDB = $DIC->database(); 1126 1127 $set = $ilDB->query("SELECT * FROM skl_level l JOIN skl_tree t ON (l.skill_id = t.child) " . 1128 " WHERE l.import_id = " . $ilDB->quote("il_" . ((int) $a_source_inst_id) . "_sklv_" . $a_level_import_id, "text") . 1129 " ORDER BY l.creation_date DESC "); 1130 $results = array(); 1131 while ($rec = $ilDB->fetchAssoc($set)) { 1132 $results[] = array("level_id" => $rec["id"], "creation_date" => $rec["creation_date"]); 1133 } 1134 return $results; 1135 } 1136 1137 /** 1138 * Get level ids for import Ids matching common skills 1139 * 1140 * @param 1141 * @return 1142 */ 1143 public static function getLevelIdForImportIdMatchSkill($a_source_inst_id, $a_level_import_id, $a_skill_import_id, $a_tref_import_id = 0) 1144 { 1145 $level_id_data = self::getLevelIdForImportId($a_source_inst_id, $a_level_import_id); 1146 $skill_data = self::getCommonSkillIdForImportId($a_source_inst_id, $a_skill_import_id, $a_tref_import_id); 1147 $matches = array(); 1148 foreach ($level_id_data as $l) { 1149 reset($skill_data); 1150 foreach ($skill_data as $s) { 1151 if (ilBasicSkill::lookupLevelSkillId($l["level_id"]) == $s["skill_id"]) { 1152 $matches[] = array( 1153 "level_id" => $l["level_id"], 1154 "creation_date" => $l["creation_date"], 1155 "skill_id" => $s["skill_id"], 1156 "tref_id" => $s["tref_id"] 1157 ); 1158 } 1159 } 1160 } 1161 return $matches; 1162 } 1163} 1164