skill.lib.php 32 KB

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