skill.lib.php 32 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947
  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. define ('SKILL_TYPE_REQUIREMENT', 'required');
  12. define ('SKILL_TYPE_ACQUIRED', 'acquired');
  13. define ('SKILL_TYPE_BOTH', 'both');
  14. require_once api_get_path(LIBRARY_PATH).'model.lib.php';
  15. class SkillProfile extends Model
  16. {
  17. public $columns = array('id', 'name', 'description');
  18. public function __construct()
  19. {
  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. {
  25. $sql = "SELECT * FROM $this->table p INNER JOIN $this->table_rel_profile sp ON(p.id = sp.profile_id) ";
  26. $result = Database::query($sql);
  27. $profiles = Database::store_result($result, 'ASSOC');
  28. return $profiles;
  29. }
  30. public function save($params, $show_query = false)
  31. {
  32. if (!empty($params)) {
  33. $profile_id = parent::save($params, $show_query);
  34. if ($profile_id) {
  35. $skill_rel_profile = new SkillRelProfile();
  36. if (isset($params['skills'])) {
  37. foreach ($params['skills'] as $skill_id) {
  38. $attributes = array('skill_id' => $skill_id, 'profile_id' => $profile_id);
  39. $skill_rel_profile->save($attributes);
  40. }
  41. }
  42. return $profile_id;
  43. }
  44. }
  45. return false;
  46. }
  47. }
  48. class SkillRelProfile extends Model
  49. {
  50. public $columns = array('id', 'skill_id', 'profile_id');
  51. public function __construct()
  52. {
  53. $this->table = Database::get_main_table(TABLE_MAIN_SKILL_REL_PROFILE);
  54. }
  55. public function get_skills_by_profile($profile_id)
  56. {
  57. $skills = $this->get_all(array('where' => array('profile_id = ? ' => $profile_id)));
  58. $return_array = array();
  59. if (!empty($skills)) {
  60. foreach ($skills as $skill_data) {
  61. $return_array[] = $skill_data['skill_id'];
  62. }
  63. }
  64. return $return_array;
  65. }
  66. }
  67. class SkillRelSkill extends Model
  68. {
  69. public $columns = array('skill_id', 'parent_id', 'relation_type', 'level');
  70. public function __construct()
  71. {
  72. $this->table = Database::get_main_table(TABLE_MAIN_SKILL_REL_SKILL);
  73. }
  74. /**
  75. * Gets an element
  76. */
  77. public function get_skill_info($id)
  78. {
  79. if (empty($id)) {
  80. return array();
  81. }
  82. $result = Database::select('*', $this->table, array('where' => array('skill_id = ?' => intval($id))), 'first');
  83. return $result;
  84. }
  85. public function get_skill_parents($skill_id, $add_child_info = true)
  86. {
  87. $skill_id = intval($skill_id);
  88. $sql = 'SELECT child.* FROM '.$this->table.' child LEFT JOIN '.$this->table.' parent
  89. ON child.parent_id = parent.skill_id
  90. WHERE child.skill_id = '.$skill_id.' ';
  91. $result = Database::query($sql);
  92. $skill = Database::store_result($result, 'ASSOC');
  93. $skill = isset($skill[0]) ? $skill[0] : null;
  94. $parents = array();
  95. if (!empty($skill)) {
  96. if ($skill['parent_id'] != null) {
  97. $parents = self::get_skill_parents($skill['parent_id']);
  98. }
  99. if ($add_child_info) {
  100. $parents[] = $skill;
  101. }
  102. }
  103. return $parents;
  104. }
  105. public function get_direct_parents($skill_id)
  106. {
  107. $skill_id = intval($skill_id);
  108. $sql = 'SELECT parent_id as skill_id FROM '.$this->table.'
  109. WHERE skill_id = '.$skill_id.' ';
  110. $result = Database::query($sql);
  111. $skill = Database::store_result($result, 'ASSOC');
  112. $skill = isset($skill[0]) ? $skill[0] : null;
  113. $parents = array();
  114. if (!empty($skill)) {
  115. $parents[] = $skill;
  116. }
  117. return $parents;
  118. }
  119. public function get_children($skill_id, $load_user_data = false, $user_id = false)
  120. {
  121. $skills = $this->find('all', array('where' => array('parent_id = ? ' => $skill_id)));
  122. $skill_obj = new Skill();
  123. $skill_rel_user = new SkillRelUser();
  124. if ($load_user_data) {
  125. $passed_skills = $skill_rel_user->get_user_skills($user_id);
  126. $done_skills = array();
  127. foreach ($passed_skills as $done_skill) {
  128. $done_skills[] = $done_skill['skill_id'];
  129. }
  130. }
  131. if (!empty($skills)) {
  132. foreach ($skills as &$skill) {
  133. $skill['data'] = $skill_obj->get($skill['skill_id']);
  134. if (isset($skill['data']) && !empty($skill['data'])) {
  135. if (!empty($done_skills)) {
  136. $skill['data']['passed'] = 0;
  137. if (in_array($skill['skill_id'], $done_skills)) {
  138. $skill['data']['passed'] = 1;
  139. }
  140. }
  141. } else {
  142. $skill = null;
  143. }
  144. }
  145. }
  146. return $skills;
  147. }
  148. function update_by_skill($params)
  149. {
  150. $result = Database::update($this->table, $params, array('skill_id = ? ' => $params['skill_id']));
  151. if ($result) {
  152. return true;
  153. }
  154. return false;
  155. }
  156. function relation_exists($skill_id, $parent_id)
  157. {
  158. $result = $this->find(
  159. 'all',
  160. array('where' => array('skill_id = ? AND parent_id = ?' => array($skill_id, $parent_id)))
  161. );
  162. if (!empty($result)) {
  163. return true;
  164. }
  165. return false;
  166. }
  167. }
  168. /**
  169. * @package chamilo.library
  170. */
  171. class SkillRelGradebook extends Model
  172. {
  173. public $columns = array('id', 'gradebook_id', 'skill_id');
  174. public function __construct()
  175. {
  176. $this->table = Database::get_main_table(TABLE_MAIN_SKILL_REL_GRADEBOOK);
  177. }
  178. public function exists_gradebook_skill($gradebook_id, $skill_id)
  179. {
  180. $result = $this->find(
  181. 'all',
  182. array('where' => array('gradebook_id = ? AND skill_id = ?' => array($gradebook_id, $skill_id)))
  183. );
  184. if (!empty($result)) {
  185. return true;
  186. }
  187. return false;
  188. }
  189. /**
  190. * Gets an element
  191. */
  192. public function get_skill_info($skill_id, $gradebook_id)
  193. {
  194. if (empty($skill_id)) {
  195. return array();
  196. }
  197. $result = Database::select(
  198. '*',
  199. $this->table,
  200. array('where' => array('skill_id = ? AND gradebook_id = ? ' => array($skill_id, $gradebook_id))),
  201. 'first'
  202. );
  203. return $result;
  204. }
  205. public function update_gradebooks_by_skill($skill_id, $gradebook_list)
  206. {
  207. $original_gradebook_list = $this->find('all', array('where' => array('skill_id = ?' => array($skill_id))));
  208. $gradebooks_to_remove = array();
  209. $gradebooks_to_add = array();
  210. $original_gradebook_list_ids = array();
  211. if (!empty($original_gradebook_list)) {
  212. foreach ($original_gradebook_list as $gradebook) {
  213. if (!in_array($gradebook['gradebook_id'], $gradebook_list)) {
  214. $gradebooks_to_remove[] = $gradebook['id'];
  215. }
  216. }
  217. foreach ($original_gradebook_list as $gradebook_item) {
  218. $original_gradebook_list_ids[] = $gradebook_item['gradebook_id'];
  219. }
  220. }
  221. if (!empty($gradebook_list)) {
  222. foreach ($gradebook_list as $gradebook_id) {
  223. if (!in_array($gradebook_id, $original_gradebook_list_ids)) {
  224. $gradebooks_to_add[] = $gradebook_id;
  225. }
  226. }
  227. }
  228. //var_dump($gradebooks_to_add, $gradebooks_to_remove);
  229. if (!empty($gradebooks_to_remove)) {
  230. foreach ($gradebooks_to_remove as $id) {
  231. $this->delete($id);
  232. }
  233. }
  234. if (!empty($gradebooks_to_add)) {
  235. foreach ($gradebooks_to_add as $gradebook_id) {
  236. $attributes = array('skill_id' => $skill_id, 'gradebook_id' => $gradebook_id);
  237. $this->save($attributes);
  238. }
  239. }
  240. }
  241. function update_by_skill($params)
  242. {
  243. $skill_info = $this->exists_gradebook_skill($params['gradebook_id'], $params['skill_id']);
  244. if ($skill_info) {
  245. return;
  246. } else {
  247. $result = $this->save($params);
  248. }
  249. if ($result) {
  250. return true;
  251. }
  252. return false;
  253. }
  254. }
  255. /**
  256. * @package chamilo.library
  257. */
  258. class SkillRelUser extends Model
  259. {
  260. public $columns = array('id', 'user_id', 'skill_id', 'acquired_skill_at', 'assigned_by');
  261. public function __construct()
  262. {
  263. $this->table = Database::get_main_table(TABLE_MAIN_SKILL_REL_USER);
  264. //$this->table_user = Database::get_main_table(TABLE_MAIN_USER);
  265. }
  266. public function get_user_by_skills($skill_list)
  267. {
  268. $users = array();
  269. if (!empty($skill_list)) {
  270. $skill_list = array_map('intval', $skill_list);
  271. $skill_list = implode("', '", $skill_list);
  272. $sql = "SELECT user_id FROM {$this->table} WHERE skill_id IN ('$skill_list') ";
  273. $result = Database::query($sql);
  274. $users = Database::store_result($result, 'ASSOC');
  275. }
  276. return $users;
  277. }
  278. public function get_user_skills($user_id)
  279. {
  280. if (empty($user_id)) {
  281. return array();
  282. }
  283. $result = Database::select(
  284. 'skill_id',
  285. $this->table,
  286. array('where' => array('user_id = ?' => intval($user_id))),
  287. 'all'
  288. );
  289. return $result;
  290. }
  291. }
  292. class Skill extends Model
  293. {
  294. public $columns = array('id', 'name', 'description', 'access_url_id', 'short_code');
  295. public $required = array('name');
  296. /** Array of colours by depth, for the coffee wheel. Each depth has 4 col */
  297. /*var $colours = array(
  298. 0 => array('#f9f0ab', '#ecc099', '#e098b0', '#ebe378'),
  299. 1 => array('#d5dda1', '#4a5072', '#8dae43', '#72659d'),
  300. 2 => array('#b28647', '#2e6093', '#393e64', '#1e8323'),
  301. 3 => array('#9f6652', '#9f6652', '#9f6652', '#9f6652'),
  302. 4 => array('#af643c', '#af643c', '#af643c', '#af643c'),
  303. 5 => array('#72659d', '#72659d', '#72659d', '#72659d'),
  304. 6 => array('#8a6e9e', '#8a6e9e', '#8a6e9e', '#8a6e9e'),
  305. 7 => array('#92538c', '#92538c', '#92538c', '#92538c'),
  306. 8 => array('#2e6093', '#2e6093', '#2e6093', '#2e6093'),
  307. 9 => array('#3a5988', '#3a5988', '#3a5988', '#3a5988'),
  308. 10 => array('#393e64', '#393e64', '#393e64', '#393e64'),
  309. );*/
  310. public function __construct()
  311. {
  312. $this->table = Database::get_main_table(TABLE_MAIN_SKILL);
  313. $this->table_user = Database::get_main_table(TABLE_MAIN_USER);
  314. $this->table_skill_rel_gradebook = Database::get_main_table(TABLE_MAIN_SKILL_REL_GRADEBOOK);
  315. $this->table_skill_rel_user = Database::get_main_table(TABLE_MAIN_SKILL_REL_USER);
  316. $this->table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  317. $this->table_skill_rel_skill = Database::get_main_table(TABLE_MAIN_SKILL_REL_SKILL);
  318. $this->table_gradebook = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY);
  319. }
  320. function get_skill_info($id)
  321. {
  322. $skill_rel_skill = new SkillRelSkill();
  323. $skill_info = $this->get($id);
  324. if (!empty($skill_info)) {
  325. $skill_info['extra'] = $skill_rel_skill->get_skill_info($id);
  326. $skill_info['gradebooks'] = self::get_gradebooks_by_skill($id);
  327. }
  328. return $skill_info;
  329. }
  330. function get_skills_info($skill_list)
  331. {
  332. $skill_list = array_map('intval', $skill_list);
  333. $skill_list = implode("', '", $skill_list);
  334. $sql = "SELECT * FROM {$this->table} WHERE id IN ('$skill_list') ";
  335. $result = Database::query($sql);
  336. $users = Database::store_result($result, 'ASSOC');
  337. return $users;
  338. }
  339. function get_all($load_user_data = false, $user_id = false, $id = null, $parent_id = null)
  340. {
  341. $id_condition = '';
  342. if (isset($id) && !empty($id)) {
  343. $id = intval($id);
  344. $id_condition = " WHERE s.id = $id";
  345. }
  346. if (isset($parent_id) && !empty($parent_id)) {
  347. $parent_id = intval($parent_id);
  348. if (empty($id_condition)) {
  349. $id_condition = "WHERE ss.parent_id = $parent_id";
  350. } else {
  351. $id_condition = " AND ss.parent_id = $parent_id";
  352. }
  353. }
  354. $sql = "SELECT s.id, s.name, s.description, ss.parent_id, ss.relation_type
  355. FROM {$this->table} s INNER JOIN {$this->table_skill_rel_skill} ss ON (s.id = ss.skill_id) $id_condition
  356. ORDER BY ss.id, ss.parent_id";
  357. $result = Database::query($sql);
  358. $skills = array();
  359. if (Database::num_rows($result)) {
  360. while ($row = Database::fetch_array($result, 'ASSOC')) {
  361. $skill_rel_skill = new SkillRelSkill();
  362. $a = $skill_rel_skill->get_skill_parents($row['id']);
  363. $row['level'] = count($a) - 1;
  364. $row['gradebooks'] = self::get_gradebooks_by_skill($row['id']);
  365. $skills[$row['id']] = $row;
  366. }
  367. }
  368. /*
  369. if ($load_user_data && $user_id) {
  370. $passed_skills = $this->get_user_skills($user_id);
  371. foreach ($skills as &$skill) {
  372. $skill['done_by_user'] = 0;
  373. if (in_array($skill['id'], $passed_skills)) {
  374. $skill['done_by_user'] = 1;
  375. }
  376. }
  377. }*/
  378. //Load all children of the parent_id
  379. if (!empty($skills) && !empty($parent_id)) {
  380. foreach ($skills as $skill) {
  381. $children = self::get_all($load_user_data, $user_id, $id, $skill['id']);
  382. if (!empty($children)) {
  383. //$skills = array_merge($skills, $children);
  384. $skills = $skills + $children;
  385. }
  386. }
  387. }
  388. return $skills;
  389. }
  390. function get_gradebooks_by_skill($skill_id)
  391. {
  392. $skill_id = intval($skill_id);
  393. $sql = "SELECT g.* FROM {$this->table_gradebook} g INNER JOIN {$this->table_skill_rel_gradebook} sg
  394. ON g.id = sg.gradebook_id
  395. WHERE sg.skill_id = $skill_id";
  396. $result = Database::query($sql);
  397. $result = Database::store_result($result, 'ASSOC');
  398. return $result;
  399. }
  400. /* Get one level childrens */
  401. function get_children($skill_id, $load_user_data = false)
  402. {
  403. $skill_rel_skill = new SkillRelSkill();
  404. if ($load_user_data) {
  405. $user_id = api_get_user_id();
  406. $skills = $skill_rel_skill->get_children($skill_id, true, $user_id);
  407. } else {
  408. $skills = $skill_rel_skill->get_children($skill_id);
  409. }
  410. return $skills;
  411. }
  412. /* Get all children of the current node (recursive)*/
  413. function get_all_children($skill_id)
  414. {
  415. $skill_rel_skill = new SkillRelSkill();
  416. $children = $skill_rel_skill->get_children($skill_id);
  417. foreach ($children as $child) {
  418. $sub_children = $this->get_all_children($child['skill_id']);
  419. }
  420. if (!empty($sub_children)) {
  421. $children = array_merge($children, $sub_children);
  422. }
  423. return $children;
  424. }
  425. /**
  426. * Gets all parents from from the wanted skill
  427. */
  428. function get_parents($skill_id)
  429. {
  430. $skill_rel_skill = new SkillRelSkill();
  431. $skills = $skill_rel_skill->get_skill_parents($skill_id, true);
  432. foreach ($skills as &$skill) {
  433. $skill['data'] = self::get($skill['skill_id']);
  434. }
  435. return $skills;
  436. }
  437. /**
  438. * All direct parents
  439. */
  440. function get_direct_parents($skill_id)
  441. {
  442. $skill_rel_skill = new SkillRelSkill();
  443. $skills = $skill_rel_skill->get_direct_parents($skill_id, true);
  444. foreach ($skills as &$skill) {
  445. $skill['data'] = self::get($skill['skill_id']);
  446. $skill_info2 = $skill_rel_skill->get_skill_info($skill['skill_id']);
  447. $skill['data']['parent_id'] = $skill_info2['parent_id'];
  448. }
  449. return $skills;
  450. }
  451. /*
  452. * Adds a new skill
  453. */
  454. public function add($params)
  455. {
  456. if (!isset($params['parent_id'])) {
  457. $params['parent_id'] = 1;
  458. }
  459. if (!is_array($params['parent_id'])) {
  460. $params['parent_id'] = array($params['parent_id']);
  461. }
  462. $skill_rel_skill = new SkillRelSkill();
  463. $skill_rel_gradebook = new SkillRelGradebook();
  464. //Saving name, description
  465. $skill_id = $this->save($params);
  466. if ($skill_id) {
  467. //Saving skill_rel_skill (parent_id, relation_type)
  468. foreach ($params['parent_id'] as $parent_id) {
  469. $relation_exists = $skill_rel_skill->relation_exists($skill_id, $parent_id);
  470. if (!$relation_exists) {
  471. $attributes = array(
  472. 'skill_id' => $skill_id,
  473. 'parent_id' => $parent_id,
  474. 'relation_type' => $params['relation_type'],
  475. //'level' => $params['level'],
  476. );
  477. $skill_rel_skill->save($attributes);
  478. }
  479. }
  480. if (!empty($params['gradebook_id'])) {
  481. foreach ($params['gradebook_id'] as $gradebook_id) {
  482. $attributes = array();
  483. $attributes['gradebook_id'] = $gradebook_id;
  484. $attributes['skill_id'] = $skill_id;
  485. $skill_rel_gradebook->save($attributes);
  486. }
  487. }
  488. return $skill_id;
  489. }
  490. return null;
  491. }
  492. public function add_skill_to_user($user_id, $gradebook_id)
  493. {
  494. $skill_gradebook = new SkillRelGradebook();
  495. $skill_rel_user = new SkillRelUser();
  496. $skill_gradebooks = $skill_gradebook->get_all(array('where' => array('gradebook_id = ?' => $gradebook_id)));
  497. if (!empty($skill_gradebooks)) {
  498. foreach ($skill_gradebooks as $skill_gradebook) {
  499. $user_has_skill = $this->user_has_skill($user_id, $skill_gradebook['skill_id']);
  500. if (!$user_has_skill) {
  501. $params = array(
  502. 'user_id' => $user_id,
  503. 'skill_id' => $skill_gradebook['skill_id'],
  504. 'acquired_skill_at' => api_get_utc_datetime(),
  505. );
  506. $skill_rel_user->save($params);
  507. }
  508. }
  509. }
  510. }
  511. /* Deletes a skill */
  512. public function delete($skill_id)
  513. {
  514. /*$params = array('skill_id' => $skill_id);
  515. $skill_rel_skill = new SkillRelSkill();
  516. $skills = $skill_rel_skill->get_all(array('where'=>array('skill_id = ?' =>$skill_id)));
  517. $skill_rel_profile = new SkillRelProfile();
  518. $skill_rel_gradebook = new SkillRelGradebook();
  519. $skill_rel_user = new SkillRelUser();
  520. $this->delete($skill_id);
  521. $skill_rel_gradebook->delete($params);*/
  522. }
  523. public function edit($params)
  524. {
  525. if (!isset($params['parent_id'])) {
  526. $params['parent_id'] = 1;
  527. }
  528. $skill_rel_skill = new SkillRelSkill();
  529. $skill_rel_gradebook = new SkillRelGradebook();
  530. //Saving name, description
  531. $this->update($params);
  532. $skill_id = $params['id'];
  533. if ($skill_id) {
  534. //Saving skill_rel_skill (parent_id, relation_type)
  535. if (!is_array($params['parent_id'])) {
  536. $params['parent_id'] = array($params['parent_id']);
  537. }
  538. foreach ($params['parent_id'] as $parent_id) {
  539. $relation_exists = $skill_rel_skill->relation_exists($skill_id, $parent_id);
  540. if (!$relation_exists) {
  541. $attributes = array(
  542. 'skill_id' => $skill_id,
  543. 'parent_id' => $parent_id,
  544. 'relation_type' => $params['relation_type'],
  545. //'level' => $params['level'],
  546. );
  547. $skill_rel_skill->update_by_skill($attributes);
  548. }
  549. }
  550. $skill_rel_gradebook->update_gradebooks_by_skill($skill_id, $params['gradebook_id']);
  551. return $skill_id;
  552. }
  553. return null;
  554. }
  555. /**
  556. * Get user's skills
  557. *
  558. * @param int $userId User's id
  559. */
  560. public function get_user_skills($user_id, $get_skill_data = false)
  561. {
  562. $user_id = intval($user_id);
  563. $sql = 'SELECT DISTINCT s.id, s.name FROM '.$this->table_skill_rel_user.' u INNER JOIN '.$this->table.' s
  564. ON u.skill_id = s.id
  565. WHERE user_id = '.$user_id;
  566. $result = Database::query($sql);
  567. $skills = Database::store_result($result, 'ASSOC');
  568. $clean_skill = array();
  569. if (!empty($skills)) {
  570. foreach ($skills as $skill) {
  571. if ($get_skill_data) {
  572. $clean_skill[$skill['id']] = $skill;
  573. } else {
  574. $clean_skill[$skill['id']] = $skill['id'];
  575. }
  576. }
  577. }
  578. return $clean_skill;
  579. }
  580. public function get_skills_tree($user_id = null, $skill_id = null, $return_flat_array = false, $add_root = false)
  581. {
  582. if ($skill_id == 1) {
  583. $skill_id = 0;
  584. }
  585. if (isset($user_id) && !empty($user_id)) {
  586. $skills = $this->get_all(true, $user_id, null, $skill_id);
  587. } else {
  588. $skills = $this->get_all(false, false, null, $skill_id);
  589. }
  590. $original_skill = $skills;
  591. //var_dump($skills);
  592. //Show 1 item
  593. if (!empty($skill_id)) {
  594. if ($add_root) {
  595. if (!empty($skill_id)) {
  596. //Default root node
  597. $skills[1] = array('id' => '1', 'name' => get_lang('Root'), 'parent_id' => '0');
  598. $skill_info = $this->get_skill_info($skill_id);
  599. //2nd node
  600. $skills[$skill_id] = $skill_info;
  601. //Uncomment code below to hide the searched skill
  602. $skills[$skill_id]['data']['parent_id'] = $skill_info['parent_id'];
  603. $skills[$skill_id]['parent_id'] = 1;
  604. }
  605. }
  606. }
  607. $refs = array();
  608. $skills_tree = null;
  609. // Create references for all nodes
  610. $flat_array = array();
  611. $family = array();
  612. if (!empty($skills)) {
  613. foreach ($skills as &$skill) {
  614. if ($skill['parent_id'] == 0) {
  615. $skill['parent_id'] = 'root';
  616. }
  617. // because except main keys (id, name, children) others keys are not saved while in the space tree
  618. $skill['data'] = array('parent_id' => $skill['parent_id']);
  619. //In order to paint all members of a family with the same color
  620. if (empty($skill_id)) {
  621. if ($skill['parent_id'] == 1) {
  622. $family[$skill['id']] = $this->get_all_children($skill['id']);
  623. }
  624. } else {
  625. if ($skill['parent_id'] == $skill_id) {
  626. $family[$skill['id']] = $this->get_all_children($skill['id']);
  627. }
  628. /*if ($skill_id == $skill['id']) {
  629. $skill['parent_id'] = 1;
  630. }*/
  631. }
  632. if (!isset($skill['data']['real_parent_id'])) {
  633. $skill['data']['real_parent_id'] = $skill['parent_id'];
  634. }
  635. //User achieved the skill (depends in the gradebook with certification)
  636. $skill['data']['achieved'] = false;
  637. if ($user_id) {
  638. $skill['data']['achieved'] = $this->user_has_skill($user_id, $skill['id']);
  639. }
  640. //Check if the skill has related gradebooks
  641. $skill['data']['skill_has_gradebook'] = false;
  642. if (isset($skill['gradebooks']) && !empty($skill['gradebooks'])) {
  643. $skill['data']['skill_has_gradebook'] = true;
  644. }
  645. $refs[$skill['id']] = & $skill;
  646. $flat_array[$skill['id']] = & $skill;
  647. }
  648. //var_dump($skills);
  649. //var_dump($refs);
  650. //Checking family value
  651. $family_id = 1;
  652. $new_family_array = array();
  653. foreach ($family as $main_family_id => $family_items) {
  654. if (!empty($family_items)) {
  655. foreach ($family_items as $item) {
  656. $new_family_array[$item['skill_id']] = $family_id;
  657. }
  658. }
  659. $new_family_array[$main_family_id] = $family_id;
  660. $family_id++;
  661. }
  662. if (empty($original_skill)) {
  663. $refs['root']['children'][0] = $skills[1];
  664. $skills[$skill_id]['data']['family_id'] = 1;
  665. $refs['root']['children'][0]['children'][0] = $skills[$skill_id];
  666. $flat_array[$skill_id] = $skills[$skill_id];
  667. } else {
  668. // Moving node to the children index of their parents
  669. foreach ($skills as $my_skill_id => &$skill) {
  670. $skill['data']['family_id'] = $new_family_array[$skill['id']];
  671. $refs[$skill['parent_id']]['children'][] = & $skill;
  672. $flat_array[$my_skill_id] = $skill;
  673. }
  674. }
  675. $skills_tree = array(
  676. 'name' => get_lang('SkillRootName'),
  677. 'id' => 'root',
  678. 'children' => $refs['root']['children'],
  679. 'data' => array()
  680. );
  681. }
  682. if ($return_flat_array) {
  683. return $flat_array;
  684. }
  685. unset($skills);
  686. return $skills_tree;
  687. }
  688. /**
  689. * Get skills tree as a simplified JSON structure
  690. * @param int user id
  691. * @param int skill id
  692. * @param bool return a flat array or not
  693. * @param int depth of the skills
  694. *
  695. */
  696. public function get_skills_tree_json($user_id = null, $skill_id = null, $return_flat_array = false, $main_depth = 2)
  697. {
  698. $tree = $this->get_skills_tree($user_id, $skill_id, $return_flat_array, true);
  699. $simple_tree = array();
  700. if (!empty($tree['children'])) {
  701. foreach ($tree['children'] as $element) {
  702. $simple_tree[] = array(
  703. 'name' => $element['name'],
  704. 'children' => $this->get_skill_json($element['children'], 1, $main_depth),
  705. );
  706. }
  707. }
  708. //var_dump($simple_tree[0]['children']);
  709. return json_encode($simple_tree[0]['children']);
  710. }
  711. /**
  712. * Get JSON element
  713. */
  714. public function get_skill_json($subtree, $depth = 1, $max_depth = 2)
  715. {
  716. $simple_sub_tree = array();
  717. if (is_array($subtree)) {
  718. $counter = 1;
  719. foreach ($subtree as $elem) {
  720. $tmp = array();
  721. $tmp['name'] = $elem['name'];
  722. $tmp['id'] = $elem['id'];
  723. if (is_array($elem['children'])) {
  724. $tmp['children'] = $this->get_skill_json($elem['children'], $depth + 1, $max_depth);
  725. } else {
  726. //$tmp['colour'] = $this->colours[$depth][rand(0,3)];
  727. }
  728. if ($depth > $max_depth) {
  729. continue;
  730. }
  731. $tmp['depth'] = $depth;
  732. $tmp['counter'] = $counter;
  733. $counter++;
  734. if (isset($elem['data']) && is_array($elem['data'])) {
  735. foreach ($elem['data'] as $key => $item) {
  736. $tmp[$key] = $item;
  737. }
  738. }
  739. $simple_sub_tree[] = $tmp;
  740. }
  741. return $simple_sub_tree;
  742. }
  743. return null;
  744. }
  745. function get_user_skill_ranking($user_id)
  746. {
  747. $user_id = intval($user_id);
  748. $sql = "SELECT count(skill_id) count FROM {$this->table} s INNER JOIN {$this->table_skill_rel_user} su ON (s.id = su.skill_id)
  749. WHERE user_id = $user_id";
  750. $result = Database::query($sql);
  751. if (Database::num_rows($result)) {
  752. $result = Database::fetch_row($result);
  753. return $result[0];
  754. }
  755. return false;
  756. }
  757. function get_user_list_skill_ranking($start, $limit, $sidx, $sord, $where_condition)
  758. {
  759. $start = intval($start);
  760. $limit = intval($limit);
  761. /* ORDER BY $sidx $sord */
  762. $sql = "SELECT *, @rownum:=@rownum+1 rank FROM (
  763. SELECT u.user_id, firstname, lastname, count(username) skills_acquired
  764. FROM {$this->table} s INNER JOIN {$this->table_skill_rel_user} su ON (s.id = su.skill_id)
  765. INNER JOIN {$this->table_user} u ON u.user_id = su.user_id, (SELECT @rownum:=0) r
  766. WHERE 1=1 $where_condition
  767. GROUP BY username
  768. ORDER BY skills_acquired desc
  769. LIMIT $start , $limit) AS T1, (SELECT @rownum:=0) r";
  770. $result = Database::query($sql);
  771. if (Database::num_rows($result)) {
  772. return Database::store_result($result, 'ASSOC');
  773. }
  774. return array();
  775. }
  776. function get_user_list_skill_ranking_count()
  777. {
  778. $sql = "SELECT count(*) FROM (
  779. SELECT count(distinct 1)
  780. FROM {$this->table} s INNER JOIN {$this->table_skill_rel_user} su ON (s.id = su.skill_id)
  781. INNER JOIN {$this->table_user} u ON u.user_id = su.user_id
  782. GROUP BY username
  783. ) as T1";
  784. $result = Database::query($sql);
  785. if (Database::num_rows($result)) {
  786. $result = Database::fetch_row($result);
  787. return $result[0];
  788. }
  789. return 0;
  790. }
  791. function get_count_skills_by_course($course_code)
  792. {
  793. $sql = "SELECT count(skill_id) as count FROM {$this->table_gradebook} g INNER JOIN {$this->table_skill_rel_gradebook} sg ON g.id = sg.gradebook_id
  794. WHERE course_code = '$course_code'";
  795. $result = Database::query($sql);
  796. if (Database::num_rows($result)) {
  797. $result = Database::fetch_row($result);
  798. return $result[0];
  799. }
  800. return 0;
  801. }
  802. function get_courses_by_skill($skill_id)
  803. {
  804. $skill_id = intval($skill_id);
  805. $sql = "SELECT c.title, c.code FROM {$this->table_gradebook} g INNER JOIN {$this->table_skill_rel_gradebook} sg ON g.id = sg.gradebook_id
  806. INNER JOIN {$this->table_course} c ON c.code = g.course_code
  807. WHERE sg.skill_id = $skill_id";
  808. $result = Database::query($sql);
  809. return Database::store_result($result, 'ASSOC');
  810. }
  811. /**
  812. * Return true if the user has the skill
  813. *
  814. * @param int $userId User's id
  815. * @param int $skillId Skill's id
  816. * @param int $checkInParents if true, function will search also in parents of the given skill id
  817. *
  818. * @return bool
  819. */
  820. public function user_has_skill($user_id, $skill_id)
  821. {
  822. $skills = $this->get_user_skills($user_id);
  823. foreach ($skills as $my_skill_id) {
  824. if ($my_skill_id == $skill_id) {
  825. return true;
  826. }
  827. }
  828. return false;
  829. }
  830. }