skill.lib.php 32 KB

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