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