skill.lib.php 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * This class provides methods for the notebook management.
  5. * Include/require it in your code to use its features.
  6. * @package chamilo.library
  7. */
  8. /**
  9. * Code
  10. */
  11. /**
  12. * @package chamilo.library
  13. */
  14. define ('SKILL_TYPE_REQUIREMENT', 'required');
  15. define ('SKILL_TYPE_ACQUIRED', 'acquired');
  16. define ('SKILL_TYPE_BOTH', 'both');
  17. class SkillProfile extends Model {
  18. var $columns = array('id', 'name','description');
  19. public function __construct() {
  20. $this->table = Database::get_main_table(TABLE_MAIN_SKILL_PROFILE);
  21. $this->table_rel_profile = Database::get_main_table(TABLE_MAIN_SKILL_REL_PROFILE);
  22. }
  23. public function get_profiles() {
  24. $sql = "SELECT * FROM $this->table p INNER JOIN $this->table_rel_profile sp ON(p.id = sp.profile_id) ";
  25. $result = Database::query($sql);
  26. $profiles = Database::store_result($result,'ASSOC');
  27. return $profiles;
  28. }
  29. public function save($params) {
  30. if (!empty($params)) {
  31. $profile_id = parent::save($params);
  32. if ($profile_id) {
  33. $skill_rel_profile = new SkillRelProfile();
  34. if (isset($params['skills'])) {
  35. foreach($params['skills'] as $skill_id) {
  36. $attributes = array('skill_id' => $skill_id, 'profile_id'=>$profile_id);
  37. $skill_rel_profile->save($attributes);
  38. }
  39. }
  40. return $profile_id;
  41. }
  42. }
  43. return false;
  44. }
  45. }
  46. class SkillRelProfile extends Model {
  47. var $columns = array('id', 'skill_id', 'profile_id');
  48. public function __construct() {
  49. $this->table = Database::get_main_table(TABLE_MAIN_SKILL_REL_PROFILE);
  50. }
  51. public function get_skills_by_profile($profile_id) {
  52. $skills = $this->get_all(array('where'=>array('profile_id = ? ' => $profile_id)));
  53. $return_array = array();
  54. if (!empty($skills)) {
  55. foreach($skills as $skill_data) {
  56. $return_array[] = $skill_data['skill_id'];
  57. }
  58. }
  59. return $return_array;
  60. }
  61. }
  62. class SkillRelSkill extends Model {
  63. var $columns = array('skill_id', 'parent_id','relation_type', 'level');
  64. public function __construct() {
  65. $this->table = Database::get_main_table(TABLE_MAIN_SKILL_REL_SKILL);
  66. }
  67. /**
  68. * Gets an element
  69. */
  70. public function get_skill_info($id) {
  71. if (empty($id)) { return array(); }
  72. $result = Database::select('*',$this->table, array('where'=>array('skill_id = ?'=>intval($id))), 'first');
  73. return $result;
  74. }
  75. public function get_skill_parents($skill_id, $add_child_info = true) {
  76. $skill_id = intval($skill_id);
  77. $sql = 'SELECT child.* FROM '.$this->table.' child LEFT JOIN '.$this->table.' parent
  78. ON child.parent_id = parent.skill_id
  79. WHERE child.skill_id = '.$skill_id.' ';
  80. $result = Database::query($sql);
  81. $skill = Database::store_result($result,'ASSOC');
  82. $skill = isset($skill[0]) ? $skill[0] : null;
  83. $parents = array();
  84. if (!empty($skill)) {
  85. if ($skill['parent_id'] != null) {
  86. $parents = self::get_skill_parents($skill['parent_id']);
  87. }
  88. if ($add_child_info) {
  89. $parents[] = $skill;
  90. }
  91. }
  92. return $parents;
  93. }
  94. public function get_direct_parents($skill_id) {
  95. $skill_id = intval($skill_id);
  96. $sql = 'SELECT parent_id as skill_id FROM '.$this->table.'
  97. WHERE skill_id = '.$skill_id.' ';
  98. $result = Database::query($sql);
  99. $skill = Database::store_result($result,'ASSOC');
  100. $skill = isset($skill[0]) ? $skill[0] : null;
  101. $parents = array();
  102. if (!empty($skill)) {
  103. $parents[] = $skill;
  104. }
  105. return $parents;
  106. }
  107. public function get_children($skill_id, $load_user_data = false, $user_id = false) {
  108. $skills = $this->find('all', array('where'=> array('parent_id = ? '=> $skill_id)));
  109. $skill_obj = new Skill();
  110. $skill_rel_user = new SkillRelUser();
  111. if ($load_user_data) {
  112. $passed_skills = $skill_rel_user->get_user_skills($user_id);
  113. $done_skills = array();
  114. foreach($passed_skills as $done_skill) {
  115. $done_skills[] = $done_skill['skill_id'];
  116. }
  117. }
  118. if (!empty($skills)) {
  119. foreach ($skills as &$skill) {
  120. $skill['data'] = $skill_obj->get($skill['skill_id']);
  121. if (isset($skill['data']) && !empty($skill['data'])) {
  122. if (!empty($done_skills)) {
  123. $skill['data']['passed'] = 0;
  124. if (in_array($skill['skill_id'], $done_skills)) {
  125. $skill['data']['passed'] = 1;
  126. }
  127. }
  128. } else {
  129. $skill = null;
  130. }
  131. }
  132. }
  133. return $skills;
  134. }
  135. function update_by_skill($params) {
  136. $result = Database::update($this->table, $params, array('skill_id = ? '=> $params['skill_id']));
  137. if ($result) {
  138. return true;
  139. }
  140. return false;
  141. }
  142. function relation_exists($skill_id, $parent_id) {
  143. $result = $this->find('all', array('where'=>array('skill_id = ? AND parent_id = ?' => array($skill_id, $parent_id))));
  144. if (!empty($result)) {
  145. return true;
  146. }
  147. return false;
  148. }
  149. }
  150. /**
  151. * @package chamilo.library
  152. */
  153. class SkillRelGradebook extends Model {
  154. var $columns = array('id', 'gradebook_id','skill_id');
  155. public function __construct() {
  156. $this->table = Database::get_main_table(TABLE_MAIN_SKILL_REL_GRADEBOOK);
  157. }
  158. public function exists_gradebook_skill($gradebook_id, $skill_id) {
  159. $result = $this->find('all', array('where'=>array('gradebook_id = ? AND skill_id = ?' => array($gradebook_id, $skill_id))));
  160. if (!empty($result)) {
  161. return true;
  162. }
  163. return false;
  164. }
  165. /**
  166. * Gets an element
  167. */
  168. public function get_skill_info($skill_id, $gradebook_id) {
  169. if (empty($skill_id)) { return array(); }
  170. $result = Database::select('*',$this->table, array('where'=>array('skill_id = ? AND gradebook_id = ? '=>array($skill_id, $gradebook_id))),'first');
  171. return $result;
  172. }
  173. public function update_gradebooks_by_skill($skill_id, $gradebook_list) {
  174. $original_gradebook_list = $this->find('all', array('where'=>array('skill_id = ?' => array($skill_id))));
  175. $gradebooks_to_remove = array();
  176. $gradebooks_to_add = array();
  177. $original_gradebook_list_ids = array();
  178. if (!empty($original_gradebook_list)) {
  179. foreach ($original_gradebook_list as $gradebook) {
  180. if (!in_array($gradebook['gradebook_id'], $gradebook_list)) {
  181. $gradebooks_to_remove[] = $gradebook['id'];
  182. }
  183. }
  184. foreach($original_gradebook_list as $gradebook_item) {
  185. $original_gradebook_list_ids[] = $gradebook_item['gradebook_id'];
  186. }
  187. }
  188. if (!empty($gradebook_list))
  189. foreach($gradebook_list as $gradebook_id) {
  190. if (!in_array($gradebook_id, $original_gradebook_list_ids)) {
  191. $gradebooks_to_add[] = $gradebook_id;
  192. }
  193. }
  194. //var_dump($gradebooks_to_add, $gradebooks_to_remove);
  195. if (!empty($gradebooks_to_remove)) {
  196. foreach($gradebooks_to_remove as $id) {
  197. $this->delete($id);
  198. }
  199. }
  200. if (!empty($gradebooks_to_add)) {
  201. foreach($gradebooks_to_add as $gradebook_id) {
  202. $attributes = array('skill_id' => $skill_id, 'gradebook_id' => $gradebook_id);
  203. $this->save($attributes);
  204. }
  205. }
  206. }
  207. function update_by_skill($params) {
  208. $skill_info = $this->exists_gradebook_skill($params['gradebook_id'], $params['skill_id']);
  209. if ($skill_info) {
  210. return;
  211. } else {
  212. $result = $this->save($params);
  213. }
  214. if ($result) {
  215. return true;
  216. }
  217. return false;
  218. }
  219. }
  220. /**
  221. * @package chamilo.library
  222. */
  223. class SkillRelUser extends Model {
  224. var $columns = array('id', 'user_id','skill_id','acquired_skill_at','assigned_by');
  225. public function __construct() {
  226. $this->table = Database::get_main_table(TABLE_MAIN_SKILL_REL_USER);
  227. //$this->table_user = Database::get_main_table(TABLE_MAIN_USER);
  228. }
  229. public function get_user_by_skills($skill_list) {
  230. $users = array();
  231. if (!empty($skill_list)) {
  232. $skill_list = array_map('intval', $skill_list);
  233. $skill_list = implode("', '", $skill_list);
  234. $sql = "SELECT user_id FROM {$this->table} WHERE skill_id IN ('$skill_list') ";
  235. $result = Database::query($sql);
  236. $users = Database::store_result($result, 'ASSOC');
  237. }
  238. return $users;
  239. }
  240. public function get_user_skills($user_id) {
  241. if (empty($user_id)) { return array(); }
  242. $result = Database::select('skill_id',$this->table, array('where'=>array('user_id = ?'=>intval($user_id))), 'all');
  243. return $result;
  244. }
  245. }
  246. class Skill extends Model {
  247. var $columns = array('id', 'name','description', 'access_url_id');
  248. var $required = array('name');
  249. public function __construct() {
  250. $this->table = Database::get_main_table(TABLE_MAIN_SKILL);
  251. $this->table_skill_rel_gradebook = Database::get_main_table(TABLE_MAIN_SKILL_REL_GRADEBOOK);
  252. $this->table_skill_rel_user = Database::get_main_table(TABLE_MAIN_SKILL_REL_USER);
  253. $this->table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  254. $this->table_skill_rel_skill = Database::get_main_table(TABLE_MAIN_SKILL_REL_SKILL);
  255. $this->table_gradebook = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY);
  256. }
  257. function get_skill_info($id) {
  258. $skill_rel_skill = new SkillRelSkill();
  259. $skill_info = $this->get($id);
  260. if (!empty($skill_info)) {
  261. $skill_info['extra'] = $skill_rel_skill->get_skill_info($id);
  262. $skill_info['gradebooks'] = self::get_gradebooks_by_skill($id);
  263. }
  264. return $skill_info;
  265. }
  266. function get_skills_info($skill_list) {
  267. $skill_list = array_map('intval', $skill_list);
  268. $skill_list = implode("', '", $skill_list);
  269. $sql = "SELECT * FROM {$this->table} WHERE id IN ('$skill_list') ";
  270. $result = Database::query($sql);
  271. $users = Database::store_result($result, 'ASSOC');
  272. return $users;
  273. }
  274. function get_all($load_user_data = false, $user_id = false, $id = null, $parent_id = null) {
  275. $id_condition = '';
  276. if (isset($id)) {
  277. $id = intval($id);
  278. $id_condition = " WHERE id = $id";
  279. }
  280. if (isset($parent_id)) {
  281. $parent_id = intval($parent_id);
  282. if (empty($id_condition)) {
  283. $id_condition = "WHERE parent_id = $parent_id";
  284. } else {
  285. $id_condition = " AND parent_id = $parent_id";
  286. }
  287. }
  288. $sql = "SELECT id, name, description, parent_id, relation_type
  289. FROM {$this->table} s INNER JOIN {$this->table_skill_rel_skill} ss ON (s.id = ss.skill_id)
  290. $id_condition";
  291. $result = Database::query($sql);
  292. $skills = array();
  293. if (Database::num_rows($result)) {
  294. while ($row = Database::fetch_array($result, 'ASSOC')) {
  295. $skill_rel_skill = new SkillRelSkill();
  296. $a = $skill_rel_skill->get_skill_parents($row['id']);
  297. $row['level'] = count($a)-1;
  298. $row['gradebooks'] = self::get_gradebooks_by_skill($row['id']);
  299. $skills[$row['id']] = $row;
  300. }
  301. }
  302. if ($load_user_data && $user_id) {
  303. $passed_skills = $this->get_user_skills($user_id);
  304. foreach ($skills as &$skill) {
  305. $skill['done_by_user'] = 0;
  306. if (in_array($skill['id'], $passed_skills)) {
  307. $skill['done_by_user'] = 1;
  308. }
  309. }
  310. }
  311. return $skills;
  312. }
  313. function get_gradebooks_by_skill($skill_id) {
  314. $skill_id = intval($skill_id);
  315. $sql = "SELECT g.* FROM {$this->table_gradebook} g INNER JOIN {$this->table_skill_rel_gradebook} sg
  316. ON g.id = sg.gradebook_id
  317. WHERE sg.skill_id = $skill_id";
  318. $result = Database::query($sql);
  319. $result = Database::store_result($result,'ASSOC');
  320. return $result;
  321. }
  322. function get_children($skill_id, $load_user_data = false) {
  323. $skill_rel_skill = new SkillRelSkill();
  324. if ($load_user_data) {
  325. $user_id = api_get_user_id();
  326. $skills = $skill_rel_skill->get_children($skill_id, true, $user_id);
  327. } else {
  328. $skills = $skill_rel_skill->get_children($skill_id);
  329. }
  330. return $skills;
  331. }
  332. /**
  333. * All parents from root to n
  334. */
  335. function get_parents($skill_id) {
  336. $skill_rel_skill = new SkillRelSkill();
  337. $skills = $skill_rel_skill->get_skill_parents($skill_id, true);
  338. foreach($skills as &$skill) {
  339. $skill['data'] = self::get($skill['skill_id']);
  340. }
  341. return $skills;
  342. }
  343. /**
  344. * All direct parents
  345. */
  346. function get_direct_parents($skill_id) {
  347. $skill_rel_skill = new SkillRelSkill();
  348. $skills = $skill_rel_skill->get_direct_parents($skill_id, true);
  349. foreach($skills as &$skill) {
  350. $skill['data'] = self::get($skill['skill_id']);
  351. $skill_info2 = $skill_rel_skill->get_skill_info($skill['skill_id']);
  352. $skill['data']['parent_id'] = $skill_info2['parent_id'];
  353. }
  354. return $skills;
  355. }
  356. public function add($params) {
  357. if (!isset($params['parent_id'])) {
  358. $params['parent_id'] = 1;
  359. }
  360. $skill_rel_skill = new SkillRelSkill();
  361. $skill_rel_gradebook = new SkillRelGradebook();
  362. //Saving name, description
  363. $skill_id = $this->save($params);
  364. if ($skill_id) {
  365. //Saving skill_rel_skill (parent_id, relation_type)
  366. $attributes = array(
  367. 'skill_id' => $skill_id,
  368. 'parent_id' => $params['parent_id'],
  369. 'relation_type' => $params['relation_type'],
  370. //'level' => $params['level'],
  371. );
  372. $skill_rel_skill->save($attributes);
  373. if (!empty($params['gradebook_id'])) {
  374. foreach ($params['gradebook_id'] as $gradebook_id) {
  375. $attributes = array();
  376. $attributes['gradebook_id'] = $gradebook_id;
  377. $attributes['skill_id'] = $skill_id;
  378. $skill_rel_gradebook->save($attributes);
  379. }
  380. }
  381. }
  382. }
  383. public function edit($params) {
  384. if (!isset($params['parent_id'])) {
  385. $params['parent_id'] = 1;
  386. }
  387. $skill_rel_skill = new SkillRelSkill();
  388. $skill_rel_gradebook = new SkillRelGradebook();
  389. //Saving name, description
  390. $this->update($params);
  391. $skill_id = $params['id'];
  392. if ($skill_id) {
  393. //Saving skill_rel_skill (parent_id, relation_type)
  394. $attributes = array(
  395. 'skill_id' => $skill_id,
  396. 'parent_id' => $params['parent_id'],
  397. 'relation_type' => $params['relation_type'],
  398. //'level' => $params['level'],
  399. );
  400. $skill_rel_skill->update_by_skill($attributes);
  401. $skill_rel_gradebook->update_gradebooks_by_skill($skill_id, $params['gradebook_id']);
  402. }
  403. }
  404. /**
  405. * Return true if the user has the skill
  406. *
  407. * @param int $userId User's id
  408. * @param int $skillId Skill's id
  409. * @param int $checkInParents if true, function will search also in parents of the given skill id
  410. *
  411. * @return bool
  412. */
  413. public function user_has_skill($user_id, $skill_id) {
  414. $skills = $this->get_user_skills($user_id);
  415. foreach($skills as $my_skill_id) {
  416. if ($my_skill_id == $skill_id) {
  417. return true;
  418. }
  419. }
  420. return false;
  421. }
  422. public function add_skill_to_user($user_id, $gradebook_id) {
  423. $skill_gradebook = new SkillRelGradebook();
  424. $skill_rel_user = new SkillRelUser();
  425. $skill_gradebooks = $skill_gradebook->get_all(array('where'=>array('gradebook_id = ?' =>$gradebook_id)));
  426. if (!empty($skill_gradebooks)) {
  427. foreach ($skill_gradebooks as $skill_gradebook) {
  428. $user_has_skill = $this->user_has_skill($user_id, $skill_gradebook['skill_id']);
  429. if (!$user_has_skill) {
  430. $params = array( 'user_id' => $user_id,
  431. 'skill_id' => $skill_gradebook['skill_id'],
  432. 'acquired_skill_at' => api_get_utc_datetime(),
  433. );
  434. $skill_rel_user->save($params);
  435. }
  436. }
  437. }
  438. }
  439. /**
  440. * Get user's skills
  441. *
  442. * @param int $userId User's id
  443. */
  444. public function get_user_skills($user_id, $get_skill_data = false) {
  445. $user_id = intval($user_id);
  446. //$sql = 'SELECT skill.*, user.* FROM '.$this->table_skill_rel_user.' user INNER JOIN '.$this->table_skill.' skill
  447. $sql = 'SELECT DISTINCT s.id, s.name FROM '.$this->table_skill_rel_user.' u INNER JOIN '.$this->table.' s
  448. ON u.skill_id = s.id
  449. WHERE user_id = '.$user_id;
  450. $result = Database::query($sql);
  451. $skills = Database::store_result($result, 'ASSOC');
  452. $clean_skill = array();
  453. if (!empty($skills)) {
  454. foreach ($skills as $skill) {
  455. if ($get_skill_data) {
  456. $clean_skill[$skill['id']] = $skill;
  457. } else {
  458. $clean_skill[$skill['id']] = $skill['id'];
  459. }
  460. }
  461. }
  462. return $clean_skill;
  463. }
  464. public function get_skills_tree($user_id = null, $return_flat_array = false) {
  465. if (isset($user_id) && !empty($user_id)) {
  466. $skills = $this->get_all(true, $user_id);
  467. } else {
  468. $skills = $this->get_all();
  469. }
  470. $refs = array();
  471. $skills_tree = null;
  472. // Create references for all nodes
  473. $flat_array = array();
  474. if (!empty($skills)) {
  475. foreach($skills as &$skill) {
  476. if ($skill['parent_id'] == 0) {
  477. $skill['parent_id'] = 'root';
  478. }
  479. $skill['data'] = array('parent_id' => $skill['parent_id']); // because except main keys (id, name, children) others keys are not saved while in the space tree
  480. if ($user_id) {
  481. $skill['data']['achieved'] = $this->user_has_skill($user_id, $skill['id']);
  482. }
  483. $refs[$skill['id']] = &$skill;
  484. $flat_array[$skill['id']] = &$skill;
  485. }
  486. // Moving node to the children index of their parents
  487. foreach($skills as $skillInd => &$skill) {
  488. $refs[$skill['parent_id']]['children'][] = &$skill;
  489. $flat_array[$skillInd] = $skill;
  490. }
  491. $skills_tree = array(
  492. 'name' => get_lang('SkillRootName'),
  493. 'id' => 'root',
  494. 'children' => $refs['root']['children'],
  495. 'data' => array()
  496. );
  497. }
  498. //var_dump($flat_array);exit;
  499. if ($return_flat_array) {
  500. return $flat_array;
  501. }
  502. unset($skills);
  503. return $skills_tree;
  504. }
  505. }