skill.lib.php 50 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. use Chamilo\UserBundle\Entity\User;
  4. use Chamilo\UserBundle\Entity\Repository\UserRepository;
  5. /**
  6. * Class SkillProfile
  7. * @package chamilo.library
  8. */
  9. class SkillProfile extends Model
  10. {
  11. public $columns = array('id', 'name', 'description');
  12. /**
  13. * Constructor
  14. */
  15. public function __construct()
  16. {
  17. $this->table = Database::get_main_table(TABLE_MAIN_SKILL_PROFILE);
  18. $this->table_rel_profile = Database::get_main_table(TABLE_MAIN_SKILL_REL_PROFILE);
  19. }
  20. /**
  21. * @return array
  22. */
  23. public function get_profiles()
  24. {
  25. $sql = "SELECT * FROM $this->table p
  26. INNER JOIN $this->table_rel_profile sp
  27. ON(p.id = sp.profile_id) ";
  28. $result = Database::query($sql);
  29. $profiles = Database::store_result($result, 'ASSOC');
  30. return $profiles;
  31. }
  32. /**
  33. * This function is for editing profile info from profile_id.
  34. * @param int $profileId
  35. * @param string $name
  36. * @param string $description
  37. */
  38. public function updateProfileInfo($profileId, $name, $description)
  39. {
  40. $profileId = intval($profileId);
  41. $sql = "UPDATE $this->table SET
  42. name = '$name',
  43. description = '$description'
  44. WHERE id = $profileId ";
  45. $result = Database::query($sql);
  46. return $result;
  47. }
  48. /**
  49. * Call the save method of the parent class and the SkillRelProfile object
  50. * @param array $params
  51. * @param bool $show_query Whether to show the query in parent save() method
  52. *
  53. * @return mixed Profile ID or false if incomplete params
  54. */
  55. public function save($params, $show_query = false)
  56. {
  57. if (!empty($params)) {
  58. $profile_id = parent::save($params, $show_query);
  59. if ($profile_id) {
  60. $skill_rel_profile = new SkillRelProfile();
  61. if (isset($params['skills'])) {
  62. foreach ($params['skills'] as $skill_id) {
  63. $attributes = array(
  64. 'skill_id' => $skill_id,
  65. 'profile_id' => $profile_id,
  66. );
  67. $skill_rel_profile->save($attributes);
  68. }
  69. }
  70. return $profile_id;
  71. }
  72. }
  73. return false;
  74. }
  75. /**
  76. * Delete a skill profile
  77. * @param int $id The skill profile id
  78. * @return boolean Whether delete a skill profile
  79. */
  80. public function delete($id)
  81. {
  82. Database::delete(
  83. $this->table_rel_profile,
  84. array(
  85. 'profile_id' => $id
  86. )
  87. );
  88. return parent::delete($id);
  89. }
  90. }
  91. /**
  92. * Class SkillRelProfile
  93. */
  94. class SkillRelProfile extends Model
  95. {
  96. public $columns = array('id', 'skill_id', 'profile_id');
  97. /**
  98. * Constructor
  99. */
  100. public function __construct()
  101. {
  102. $this->table = Database::get_main_table(TABLE_MAIN_SKILL_REL_PROFILE);
  103. $this->tableProfile = Database::get_main_table(TABLE_MAIN_SKILL_PROFILE);
  104. }
  105. /**
  106. * @param int $profileId
  107. * @return array
  108. */
  109. public function get_skills_by_profile($profileId)
  110. {
  111. $profileId = intval($profileId);
  112. $skills = $this->get_all(array('where' => array('profile_id = ? ' => $profileId)));
  113. $return = array();
  114. if (!empty($skills)) {
  115. foreach ($skills as $skill_data) {
  116. $return[] = $skill_data['skill_id'];
  117. }
  118. }
  119. return $return;
  120. }
  121. /**
  122. * This function is for getting profile info from profile_id.
  123. * @param int $profileId
  124. * @return array
  125. */
  126. public function getProfileInfo($profileId)
  127. {
  128. $sql = "SELECT * FROM $this->table p
  129. INNER JOIN $this->tableProfile pr
  130. ON (pr.id = p.profile_id)
  131. WHERE p.profile_id = ".intval($profileId);
  132. $result = Database::query($sql);
  133. $profileData = Database::fetch_array($result, 'ASSOC');
  134. return $profileData;
  135. }
  136. }
  137. /**
  138. * Class SkillRelSkill
  139. */
  140. class SkillRelSkill extends Model
  141. {
  142. public $columns = array('skill_id', 'parent_id', 'relation_type', 'level');
  143. /**
  144. * Constructor
  145. */
  146. public function __construct()
  147. {
  148. $this->table = Database::get_main_table(TABLE_MAIN_SKILL_REL_SKILL);
  149. }
  150. /**
  151. * Gets an element
  152. * @param int $id
  153. * @return array
  154. */
  155. public function get_skill_info($id)
  156. {
  157. if (empty($id)) {
  158. return array();
  159. }
  160. $result = Database::select(
  161. '*',
  162. $this->table,
  163. array('where' => array('skill_id = ?' => intval($id))),
  164. 'first'
  165. );
  166. return $result;
  167. }
  168. /**
  169. * @param int $skill_id
  170. * @param bool $add_child_info
  171. * @return array
  172. */
  173. public function get_skill_parents($skill_id, $add_child_info = true)
  174. {
  175. $skill_id = intval($skill_id);
  176. $sql = 'SELECT child.* FROM '.$this->table.' child
  177. LEFT JOIN '.$this->table.' parent
  178. ON child.parent_id = parent.skill_id
  179. WHERE child.skill_id = '.$skill_id.' ';
  180. $result = Database::query($sql);
  181. $skill = Database::store_result($result, 'ASSOC');
  182. $skill = isset($skill[0]) ? $skill[0] : null;
  183. $parents = array();
  184. if (!empty($skill)) {
  185. if ($skill['parent_id'] != null) {
  186. $parents = self::get_skill_parents($skill['parent_id']);
  187. }
  188. if ($add_child_info) {
  189. $parents[] = $skill;
  190. }
  191. }
  192. return $parents;
  193. }
  194. /**
  195. * @param int $skill_id
  196. * @return array
  197. */
  198. public function get_direct_parents($skill_id)
  199. {
  200. $skill_id = intval($skill_id);
  201. $sql = 'SELECT parent_id as skill_id
  202. FROM '.$this->table.'
  203. WHERE skill_id = '.$skill_id.' ';
  204. $result = Database::query($sql);
  205. $skill = Database::store_result($result, 'ASSOC');
  206. $skill = isset($skill[0]) ? $skill[0] : null;
  207. $parents = array();
  208. if (!empty($skill)) {
  209. $parents[] = $skill;
  210. }
  211. return $parents;
  212. }
  213. /**
  214. * @param int $skill_id
  215. * @param bool $load_user_data
  216. * @param bool $user_id
  217. * @return array
  218. */
  219. public function get_children(
  220. $skill_id,
  221. $load_user_data = false,
  222. $user_id = false
  223. ) {
  224. $skills = $this->find(
  225. 'all',
  226. array('where' => array('parent_id = ? ' => $skill_id))
  227. );
  228. $skill_obj = new Skill();
  229. $skill_rel_user = new SkillRelUser();
  230. if ($load_user_data) {
  231. $passed_skills = $skill_rel_user->getUserSkills($user_id);
  232. $done_skills = array();
  233. foreach ($passed_skills as $done_skill) {
  234. $done_skills[] = $done_skill['skill_id'];
  235. }
  236. }
  237. if (!empty($skills)) {
  238. foreach ($skills as &$skill) {
  239. $skill['data'] = $skill_obj->get($skill['skill_id']);
  240. if (isset($skill['data']) && !empty($skill['data'])) {
  241. if (!empty($done_skills)) {
  242. $skill['data']['passed'] = 0;
  243. if (in_array($skill['skill_id'], $done_skills)) {
  244. $skill['data']['passed'] = 1;
  245. }
  246. }
  247. } else {
  248. $skill = null;
  249. }
  250. }
  251. }
  252. return $skills;
  253. }
  254. /**
  255. * @param array $params
  256. * @return bool
  257. */
  258. public function update_by_skill($params)
  259. {
  260. $result = Database::update(
  261. $this->table,
  262. $params,
  263. array('skill_id = ? ' => $params['skill_id'])
  264. );
  265. if ($result) {
  266. return true;
  267. }
  268. return false;
  269. }
  270. /**
  271. * @param int $skill_id
  272. * @param int $parent_id
  273. * @return bool
  274. */
  275. public function relation_exists($skill_id, $parent_id)
  276. {
  277. $result = $this->find(
  278. 'all',
  279. array(
  280. 'where' => array(
  281. 'skill_id = ? AND parent_id = ?' => array(
  282. $skill_id,
  283. $parent_id
  284. )
  285. )
  286. )
  287. );
  288. if (!empty($result)) {
  289. return true;
  290. }
  291. return false;
  292. }
  293. }
  294. /**
  295. * Class SkillRelGradebook
  296. */
  297. class SkillRelGradebook extends Model
  298. {
  299. public $columns = array('id', 'gradebook_id', 'skill_id');
  300. /**
  301. * SkillRelGradebook constructor.
  302. */
  303. public function __construct()
  304. {
  305. $this->table = Database::get_main_table(TABLE_MAIN_SKILL_REL_GRADEBOOK);
  306. }
  307. /**
  308. * @param int $gradebook_id
  309. * @param int $skill_id
  310. * @return bool
  311. */
  312. public function exists_gradebook_skill($gradebook_id, $skill_id)
  313. {
  314. $result = $this->find(
  315. 'all',
  316. array(
  317. 'where' => array(
  318. 'gradebook_id = ? AND skill_id = ?' => array(
  319. $gradebook_id,
  320. $skill_id
  321. )
  322. )
  323. )
  324. );
  325. if (!empty($result)) {
  326. return true;
  327. }
  328. return false;
  329. }
  330. /**
  331. * Gets an element
  332. */
  333. public function get_skill_info($skill_id, $gradebook_id)
  334. {
  335. if (empty($skill_id)) {
  336. return array();
  337. }
  338. $result = Database::select(
  339. '*',
  340. $this->table,
  341. array(
  342. 'where' => array(
  343. 'skill_id = ? AND gradebook_id = ? ' => array(
  344. $skill_id,
  345. $gradebook_id
  346. )
  347. )
  348. ),
  349. 'first'
  350. );
  351. return $result;
  352. }
  353. /**
  354. * @param int $skill_id
  355. * @param array $gradebook_list
  356. */
  357. public function updateGradeBookListBySkill($skill_id, $gradebook_list)
  358. {
  359. $original_gradebook_list = $this->find(
  360. 'all',
  361. array('where' => array('skill_id = ?' => array($skill_id)))
  362. );
  363. $gradebooks_to_remove = array();
  364. $gradebooks_to_add = array();
  365. $original_gradebook_list_ids = array();
  366. if (!empty($original_gradebook_list)) {
  367. foreach ($original_gradebook_list as $gradebook) {
  368. if (!in_array($gradebook['gradebook_id'], $gradebook_list)) {
  369. $gradebooks_to_remove[] = $gradebook['id'];
  370. }
  371. }
  372. foreach ($original_gradebook_list as $gradebook_item) {
  373. $original_gradebook_list_ids[] = $gradebook_item['gradebook_id'];
  374. }
  375. }
  376. if (!empty($gradebook_list)) {
  377. foreach ($gradebook_list as $gradebook_id) {
  378. if (!in_array($gradebook_id, $original_gradebook_list_ids)) {
  379. $gradebooks_to_add[] = $gradebook_id;
  380. }
  381. }
  382. }
  383. if (!empty($gradebooks_to_remove)) {
  384. foreach ($gradebooks_to_remove as $id) {
  385. $this->delete($id);
  386. }
  387. }
  388. if (!empty($gradebooks_to_add)) {
  389. foreach ($gradebooks_to_add as $gradebook_id) {
  390. $attributes = array(
  391. 'skill_id' => $skill_id,
  392. 'gradebook_id' => $gradebook_id
  393. );
  394. $this->save($attributes);
  395. }
  396. }
  397. }
  398. /**
  399. * @param array $params
  400. * @return bool|void
  401. */
  402. public function update_by_skill($params)
  403. {
  404. $skill_info = $this->exists_gradebook_skill(
  405. $params['gradebook_id'],
  406. $params['skill_id']
  407. );
  408. if ($skill_info) {
  409. return;
  410. } else {
  411. $result = $this->save($params);
  412. }
  413. if ($result) {
  414. return true;
  415. }
  416. return false;
  417. }
  418. }
  419. /**
  420. * Class SkillRelUser
  421. */
  422. class SkillRelUser extends Model
  423. {
  424. public $columns = array(
  425. 'id',
  426. 'user_id',
  427. 'skill_id',
  428. 'acquired_skill_at',
  429. 'assigned_by',
  430. 'course_id',
  431. 'session_id',
  432. );
  433. /**
  434. * Constructor
  435. */
  436. public function __construct()
  437. {
  438. $this->table = Database::get_main_table(TABLE_MAIN_SKILL_REL_USER);
  439. }
  440. /**
  441. * @param array $skill_list
  442. * @return array
  443. */
  444. public function get_user_by_skills($skill_list)
  445. {
  446. $users = array();
  447. if (!empty($skill_list)) {
  448. $skill_list = array_map('intval', $skill_list);
  449. $skill_list = implode("', '", $skill_list);
  450. $sql = "SELECT user_id FROM {$this->table}
  451. WHERE skill_id IN ('$skill_list') ";
  452. $result = Database::query($sql);
  453. $users = Database::store_result($result, 'ASSOC');
  454. }
  455. return $users;
  456. }
  457. /**
  458. * Get the achieved skills for the user
  459. * @param int $userId
  460. * @param int $courseId Optional. The course id
  461. * @param int $sessionId Optional. The session id
  462. * @return array The skill list. Otherwise return false
  463. */
  464. public function getUserSkills($userId, $courseId = 0, $sessionId = 0)
  465. {
  466. if (empty($userId)) {
  467. return array();
  468. }
  469. $courseId = intval($courseId);
  470. $sessionId = $sessionId ? intval($sessionId) : null;
  471. $whereConditions = array(
  472. 'user_id = ? ' => intval($userId)
  473. );
  474. if ($courseId > 0) {
  475. $whereConditions['AND course_id = ? '] = $courseId;
  476. $whereConditions['AND session_id = ?'] = $sessionId;
  477. }
  478. $result = Database::select(
  479. 'skill_id',
  480. $this->table,
  481. array(
  482. 'where' => $whereConditions
  483. ),
  484. 'all'
  485. );
  486. return $result;
  487. }
  488. /**
  489. * Get the relation data between user and skill
  490. * @param int $userId The user id
  491. * @param int $skillId The skill id
  492. * @param int $courseId The course id
  493. * @param int $sessionId Optional. The session id
  494. * @return array The relation data. Otherwise return false
  495. */
  496. public function getByUserAndSkill($userId, $skillId, $courseId, $sessionId = 0)
  497. {
  498. $where = array(
  499. 'user_id = ? AND skill_id = ? AND course_id = ? AND session_id = ?' => array(
  500. intval($userId),
  501. intval($skillId),
  502. intval($courseId),
  503. $sessionId ? intval($sessionId) : null
  504. )
  505. );
  506. return Database::select('*', $this->table, array(
  507. 'where' => $where
  508. ), 'first');
  509. }
  510. }
  511. /**
  512. * Class Skill
  513. */
  514. class Skill extends Model
  515. {
  516. public $columns = array(
  517. 'id',
  518. 'name',
  519. 'description',
  520. 'access_url_id',
  521. 'short_code',
  522. 'icon',
  523. 'criteria'
  524. );
  525. public $required = array('name');
  526. /** Array of colours by depth, for the coffee wheel. Each depth has 4 col */
  527. /*var $colours = array(
  528. 0 => array('#f9f0ab', '#ecc099', '#e098b0', '#ebe378'),
  529. 1 => array('#d5dda1', '#4a5072', '#8dae43', '#72659d'),
  530. 2 => array('#b28647', '#2e6093', '#393e64', '#1e8323'),
  531. 3 => array('#9f6652', '#9f6652', '#9f6652', '#9f6652'),
  532. 4 => array('#af643c', '#af643c', '#af643c', '#af643c'),
  533. 5 => array('#72659d', '#72659d', '#72659d', '#72659d'),
  534. 6 => array('#8a6e9e', '#8a6e9e', '#8a6e9e', '#8a6e9e'),
  535. 7 => array('#92538c', '#92538c', '#92538c', '#92538c'),
  536. 8 => array('#2e6093', '#2e6093', '#2e6093', '#2e6093'),
  537. 9 => array('#3a5988', '#3a5988', '#3a5988', '#3a5988'),
  538. 10 => array('#393e64', '#393e64', '#393e64', '#393e64'),
  539. );*/
  540. public function __construct()
  541. {
  542. $this->table = Database::get_main_table(TABLE_MAIN_SKILL);
  543. $this->table_user = Database::get_main_table(TABLE_MAIN_USER);
  544. $this->table_skill_rel_gradebook = Database::get_main_table(TABLE_MAIN_SKILL_REL_GRADEBOOK);
  545. $this->table_skill_rel_user = Database::get_main_table(TABLE_MAIN_SKILL_REL_USER);
  546. $this->table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  547. $this->table_skill_rel_skill = Database::get_main_table(TABLE_MAIN_SKILL_REL_SKILL);
  548. $this->table_gradebook = Database::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY);
  549. $this->sessionTable = Database::get_main_table(TABLE_MAIN_SESSION);
  550. }
  551. /**
  552. * Gets an element
  553. * @param int $id
  554. *
  555. * @return array|mixed
  556. */
  557. public function get($id)
  558. {
  559. $result = parent::get($id);
  560. $result['web_icon_path'] = api_get_path(WEB_UPLOAD_PATH).'badges/'.$result['icon'];
  561. return $result;
  562. }
  563. /**
  564. * @param int $id
  565. * @return array
  566. */
  567. public function get_skill_info($id)
  568. {
  569. $skill_rel_skill = new SkillRelSkill();
  570. $skill_info = $this->get($id);
  571. if (!empty($skill_info)) {
  572. $skill_info['extra'] = $skill_rel_skill->get_skill_info($id);
  573. $skill_info['gradebooks'] = self::get_gradebooks_by_skill($id);
  574. }
  575. return $skill_info;
  576. }
  577. /**
  578. * @param array $skill_list
  579. * @return array
  580. */
  581. public function get_skills_info($skill_list)
  582. {
  583. $skill_list = array_map('intval', $skill_list);
  584. $skill_list = implode("', '", $skill_list);
  585. $sql = "SELECT * FROM {$this->table}
  586. WHERE id IN ('$skill_list') ";
  587. $result = Database::query($sql);
  588. $users = Database::store_result($result, 'ASSOC');
  589. foreach ($users as &$user) {
  590. if (!$user['icon']) {
  591. continue;
  592. }
  593. $user['icon_small'] = sprintf(
  594. "badges/%s-small.png",
  595. sha1($user['name'])
  596. );
  597. }
  598. return $users;
  599. }
  600. /**
  601. * @param bool $load_user_data
  602. * @param bool $user_id
  603. * @param int $id
  604. * @param int $parent_id
  605. * @return array
  606. */
  607. public function get_all(
  608. $load_user_data = false,
  609. $user_id = false,
  610. $id = null,
  611. $parent_id = null
  612. ) {
  613. $id_condition = '';
  614. if (!empty($id)) {
  615. $id = intval($id);
  616. $id_condition = " WHERE s.id = $id";
  617. }
  618. if (!empty($parent_id)) {
  619. $parent_id = intval($parent_id);
  620. if (empty($id_condition)) {
  621. $id_condition = " WHERE ss.parent_id = $parent_id";
  622. } else {
  623. $id_condition = " AND ss.parent_id = $parent_id";
  624. }
  625. }
  626. $sql = "SELECT
  627. s.id,
  628. s.name,
  629. s.description,
  630. ss.parent_id,
  631. ss.relation_type,
  632. s.icon,
  633. s.short_code,
  634. s.status
  635. FROM {$this->table} s
  636. INNER JOIN {$this->table_skill_rel_skill} ss
  637. ON (s.id = ss.skill_id) $id_condition
  638. ORDER BY ss.id, ss.parent_id";
  639. $result = Database::query($sql);
  640. $skills = array();
  641. $webPath = api_get_path(WEB_UPLOAD_PATH);
  642. if (Database::num_rows($result)) {
  643. while ($row = Database::fetch_array($result, 'ASSOC')) {
  644. $skill_rel_skill = new SkillRelSkill();
  645. $parents = $skill_rel_skill->get_skill_parents($row['id']);
  646. $row['level'] = count($parents) - 1;
  647. $row['gradebooks'] = self::get_gradebooks_by_skill($row['id']);
  648. $row['web_icon_path'] = $webPath.'badges/'.$row['icon'];
  649. $skills[$row['id']] = $row;
  650. }
  651. }
  652. // Load all children of the parent_id
  653. if (!empty($skills) && !empty($parent_id)) {
  654. foreach ($skills as $skill) {
  655. $children = self::get_all($load_user_data, $user_id, $id, $skill['id']);
  656. if (!empty($children)) {
  657. //$skills = array_merge($skills, $children);
  658. $skills = $skills + $children;
  659. }
  660. }
  661. }
  662. return $skills;
  663. }
  664. /**
  665. * @param int $skill_id
  666. * @return array|resource
  667. */
  668. public function get_gradebooks_by_skill($skill_id)
  669. {
  670. $skill_id = intval($skill_id);
  671. $sql = "SELECT g.* FROM {$this->table_gradebook} g
  672. INNER JOIN {$this->table_skill_rel_gradebook} sg
  673. ON g.id = sg.gradebook_id
  674. WHERE sg.skill_id = $skill_id";
  675. $result = Database::query($sql);
  676. $result = Database::store_result($result, 'ASSOC');
  677. return $result;
  678. }
  679. /**
  680. * Get one level childrens
  681. *
  682. * @param int $skill_id
  683. * @param bool $load_user_data
  684. * @return array
  685. */
  686. public function get_children($skill_id, $load_user_data = false)
  687. {
  688. $skill_rel_skill = new SkillRelSkill();
  689. if ($load_user_data) {
  690. $user_id = api_get_user_id();
  691. $skills = $skill_rel_skill->get_children($skill_id, true, $user_id);
  692. } else {
  693. $skills = $skill_rel_skill->get_children($skill_id);
  694. }
  695. return $skills;
  696. }
  697. /**
  698. * Get all children of the current node (recursive)
  699. * @param int $skillId
  700. * @return array
  701. */
  702. public function get_all_children($skillId)
  703. {
  704. $skill_rel_skill = new SkillRelSkill();
  705. $children = $skill_rel_skill->get_children($skillId);
  706. foreach ($children as $child) {
  707. $subChildren = $this->get_all_children($child['skill_id']);
  708. }
  709. if (!empty($subChildren)) {
  710. $children = array_merge($children, $subChildren);
  711. }
  712. return $children;
  713. }
  714. /**
  715. * Gets all parents from from the wanted skill
  716. */
  717. public function get_parents($skill_id)
  718. {
  719. $skill_rel_skill = new SkillRelSkill();
  720. $skills = $skill_rel_skill->get_skill_parents($skill_id, true);
  721. foreach ($skills as &$skill) {
  722. $skill['data'] = self::get($skill['skill_id']);
  723. }
  724. return $skills;
  725. }
  726. /**
  727. * All direct parents
  728. */
  729. public function get_direct_parents($skill_id)
  730. {
  731. $skill_rel_skill = new SkillRelSkill();
  732. $skills = $skill_rel_skill->get_direct_parents($skill_id, true);
  733. foreach ($skills as &$skill) {
  734. $skill['data'] = self::get($skill['skill_id']);
  735. $skill_info2 = $skill_rel_skill->get_skill_info($skill['skill_id']);
  736. $skill['data']['parent_id'] = $skill_info2['parent_id'];
  737. }
  738. return $skills;
  739. }
  740. /**
  741. * Adds a new skill
  742. * @param array $params
  743. * @return bool|null
  744. */
  745. public function add($params)
  746. {
  747. if (!isset($params['parent_id'])) {
  748. $params['parent_id'] = 1;
  749. }
  750. if (!is_array($params['parent_id'])) {
  751. $params['parent_id'] = array($params['parent_id']);
  752. }
  753. $skill_rel_skill = new SkillRelSkill();
  754. $skill_rel_gradebook = new SkillRelGradebook();
  755. // Saving name, description
  756. $skill_id = $this->save($params);
  757. if ($skill_id) {
  758. //Saving skill_rel_skill (parent_id, relation_type)
  759. foreach ($params['parent_id'] as $parent_id) {
  760. $relation_exists = $skill_rel_skill->relation_exists($skill_id, $parent_id);
  761. if (!$relation_exists) {
  762. $attributes = array(
  763. 'skill_id' => $skill_id,
  764. 'parent_id' => $parent_id,
  765. 'relation_type' => (isset($params['relation_type']) ? $params['relation_type'] : 0),
  766. //'level' => $params['level'],
  767. );
  768. $skill_rel_skill->save($attributes);
  769. }
  770. }
  771. if (!empty($params['gradebook_id'])) {
  772. foreach ($params['gradebook_id'] as $gradebook_id) {
  773. $attributes = array();
  774. $attributes['gradebook_id'] = $gradebook_id;
  775. $attributes['skill_id'] = $skill_id;
  776. $skill_rel_gradebook->save($attributes);
  777. }
  778. }
  779. return $skill_id;
  780. }
  781. return null;
  782. }
  783. /**
  784. * @param int $user_id
  785. * @param int $gradebook_id
  786. * @param int $courseId
  787. * @param int $sessionId
  788. */
  789. public function addSkillToUser(
  790. $user_id,
  791. $gradebook_id,
  792. $courseId = 0,
  793. $sessionId = 0
  794. ) {
  795. $skill_gradebook = new SkillRelGradebook();
  796. $skill_rel_user = new SkillRelUser();
  797. $skill_gradebooks = $skill_gradebook->get_all(
  798. array('where' => array('gradebook_id = ?' => $gradebook_id))
  799. );
  800. if (!empty($skill_gradebooks)) {
  801. foreach ($skill_gradebooks as $skill_gradebook) {
  802. $user_has_skill = $this->userHasSkill(
  803. $user_id,
  804. $skill_gradebook['skill_id'],
  805. $courseId,
  806. $sessionId
  807. );
  808. if (!$user_has_skill) {
  809. $params = array(
  810. 'user_id' => $user_id,
  811. 'skill_id' => $skill_gradebook['skill_id'],
  812. 'acquired_skill_at' => api_get_utc_datetime(),
  813. 'course_id' => intval($courseId),
  814. 'session_id' => $sessionId ? intval($sessionId) : null
  815. );
  816. $skill_rel_user->save($params);
  817. }
  818. }
  819. }
  820. }
  821. /* Deletes a skill */
  822. public function delete($skill_id)
  823. {
  824. /*$params = array('skill_id' => $skill_id);
  825. $skill_rel_skill = new SkillRelSkill();
  826. $skills = $skill_rel_skill->get_all(array('where'=>array('skill_id = ?' =>$skill_id)));
  827. $skill_rel_profile = new SkillRelProfile();
  828. $skill_rel_gradebook = new SkillRelGradebook();
  829. $skill_rel_user = new SkillRelUser();
  830. $this->delete($skill_id);
  831. $skill_rel_gradebook->delete($params);*/
  832. }
  833. /**
  834. * @param array $params
  835. * @return null
  836. */
  837. public function edit($params)
  838. {
  839. if (!isset($params['parent_id'])) {
  840. $params['parent_id'] = 1;
  841. }
  842. $skill_rel_skill = new SkillRelSkill();
  843. $skill_rel_gradebook = new SkillRelGradebook();
  844. //Saving name, description
  845. $this->update($params);
  846. $skill_id = $params['id'];
  847. if ($skill_id) {
  848. //Saving skill_rel_skill (parent_id, relation_type)
  849. if (!is_array($params['parent_id'])) {
  850. $params['parent_id'] = array($params['parent_id']);
  851. }
  852. foreach ($params['parent_id'] as $parent_id) {
  853. $relation_exists = $skill_rel_skill->relation_exists($skill_id, $parent_id);
  854. if (!$relation_exists) {
  855. $attributes = array(
  856. 'skill_id' => $skill_id,
  857. 'parent_id' => $parent_id,
  858. 'relation_type' => $params['relation_type'],
  859. //'level' => $params['level'],
  860. );
  861. $skill_rel_skill->update_by_skill($attributes);
  862. }
  863. }
  864. $skill_rel_gradebook->updateGradeBookListBySkill(
  865. $skill_id,
  866. $params['gradebook_id']
  867. );
  868. return $skill_id;
  869. }
  870. return null;
  871. }
  872. /**
  873. * Get user's skills
  874. *
  875. * @param int $userId User's id
  876. * @param bool $get_skill_data
  877. *
  878. * @return array
  879. */
  880. public function getUserSkills($userId, $get_skill_data = false)
  881. {
  882. $userId = intval($userId);
  883. $sql = 'SELECT DISTINCT s.id, s.name, s.icon, u.id as issue
  884. FROM '.$this->table_skill_rel_user.' u
  885. INNER JOIN '.$this->table.' s
  886. ON u.skill_id = s.id
  887. WHERE user_id = '.$userId;
  888. $result = Database::query($sql);
  889. $skills = Database::store_result($result, 'ASSOC');
  890. $uploadPath = api_get_path(WEB_UPLOAD_PATH);
  891. $clean_skill = array();
  892. if (!empty($skills)) {
  893. foreach ($skills as $skill) {
  894. if ($get_skill_data) {
  895. $iconThumb = null;
  896. $iconPath = null;
  897. if (!empty($skill['icon'])) {
  898. $iconThumb = sprintf(
  899. "badges/%s-small.png",
  900. sha1($skill['name'])
  901. );
  902. $iconPath = sprintf(
  903. "badges/%s.png",
  904. sha1($skill['name'])
  905. );
  906. }
  907. $clean_skill[$skill['id']] = array_merge(
  908. $skill,
  909. array(
  910. 'web_icon_thumb_path' => $uploadPath.$iconThumb,
  911. 'web_icon_path' => $uploadPath.$iconPath
  912. )
  913. );
  914. } else {
  915. $clean_skill[$skill['id']] = $skill['id'];
  916. }
  917. }
  918. }
  919. return $clean_skill;
  920. }
  921. /**
  922. * @param int $user_id
  923. * @param int $skill_id
  924. * @param bool $return_flat_array
  925. * @param bool $add_root
  926. * @return array|null
  927. */
  928. public function get_skills_tree(
  929. $user_id = null,
  930. $skill_id = null,
  931. $return_flat_array = false,
  932. $add_root = false
  933. ) {
  934. if ($skill_id == 1) {
  935. $skill_id = 0;
  936. }
  937. if (isset($user_id) && !empty($user_id)) {
  938. $skills = $this->get_all(true, $user_id, null, $skill_id);
  939. } else {
  940. $skills = $this->get_all(false, false, null, $skill_id);
  941. }
  942. $original_skill = $this->list = $skills;
  943. // Show 1 item
  944. if (!empty($skill_id)) {
  945. if ($add_root) {
  946. if (!empty($skill_id)) {
  947. // Default root node
  948. $skills[1] = array(
  949. 'id' => '1',
  950. 'name' => get_lang('Root'),
  951. 'parent_id' => '0'
  952. );
  953. $skill_info = $this->get_skill_info($skill_id);
  954. // 2nd node
  955. $skills[$skill_id] = $skill_info;
  956. // Uncomment code below to hide the searched skill
  957. $skills[$skill_id]['data']['parent_id'] = $skill_info['extra']['parent_id'];
  958. $skills[$skill_id]['parent_id'] = 1;
  959. }
  960. }
  961. }
  962. $refs = array();
  963. $skills_tree = null;
  964. // Create references for all nodes
  965. $flat_array = array();
  966. $family = array();
  967. if (!empty($skills)) {
  968. foreach ($skills as &$skill) {
  969. if ($skill['parent_id'] == 0) {
  970. $skill['parent_id'] = 'root';
  971. }
  972. // because except main keys (id, name, children) others keys
  973. // are not saved while in the space tree
  974. $skill['data'] = array('parent_id' => $skill['parent_id']);
  975. // If a short code was defined, send the short code to replace
  976. // skill name (to shorten the text in the wheel)
  977. if (!empty($skill['short_code']) &&
  978. api_get_setting('show_full_skill_name_on_skill_wheel') === 'false'
  979. ) {
  980. $skill['data']['short_code'] = $skill['short_code'];
  981. }
  982. $skill['data']['name'] = $skill['name'];
  983. $skill['data']['status'] = $skill['status'];
  984. // In order to paint all members of a family with the same color
  985. if (empty($skill_id)) {
  986. if ($skill['parent_id'] == 1) {
  987. $family[$skill['id']] = $this->get_all_children($skill['id']);
  988. }
  989. } else {
  990. if ($skill['parent_id'] == $skill_id) {
  991. $family[$skill['id']] = $this->get_all_children($skill['id']);
  992. }
  993. /*if ($skill_id == $skill['id']) {
  994. $skill['parent_id'] = 1;
  995. }*/
  996. }
  997. if (!isset($skill['data']['real_parent_id'])) {
  998. $skill['data']['real_parent_id'] = $skill['parent_id'];
  999. }
  1000. // User achieved the skill (depends in the gradebook with certification)
  1001. $skill['data']['achieved'] = false;
  1002. if ($user_id) {
  1003. $skill['data']['achieved'] = $this->userHasSkill(
  1004. $user_id,
  1005. $skill['id']
  1006. );
  1007. }
  1008. // Check if the skill has related gradebooks
  1009. $skill['data']['skill_has_gradebook'] = false;
  1010. if (isset($skill['gradebooks']) && !empty($skill['gradebooks'])) {
  1011. $skill['data']['skill_has_gradebook'] = true;
  1012. }
  1013. $refs[$skill['id']] = &$skill;
  1014. $flat_array[$skill['id']] = &$skill;
  1015. }
  1016. // Checking family value
  1017. $family_id = 1;
  1018. $new_family_array = array();
  1019. foreach ($family as $main_family_id => $family_items) {
  1020. if (!empty($family_items)) {
  1021. foreach ($family_items as $item) {
  1022. $new_family_array[$item['skill_id']] = $family_id;
  1023. }
  1024. }
  1025. $new_family_array[$main_family_id] = $family_id;
  1026. $family_id++;
  1027. }
  1028. if (empty($original_skill)) {
  1029. $refs['root']['children'][0] = $skills[1];
  1030. $skills[$skill_id]['data']['family_id'] = 1;
  1031. $refs['root']['children'][0]['children'][0] = $skills[$skill_id];
  1032. $flat_array[$skill_id] = $skills[$skill_id];
  1033. } else {
  1034. // Moving node to the children index of their parents
  1035. foreach ($skills as $my_skill_id => &$skill) {
  1036. if (isset($new_family_array[$skill['id']])) {
  1037. $skill['data']['family_id'] = $new_family_array[$skill['id']];
  1038. }
  1039. $refs[$skill['parent_id']]['children'][] = &$skill;
  1040. $flat_array[$my_skill_id] = $skill;
  1041. }
  1042. }
  1043. $skills_tree = array(
  1044. 'name' => get_lang('SkillRootName'),
  1045. 'id' => 'root',
  1046. 'children' => $refs['root']['children'],
  1047. 'data' => array()
  1048. );
  1049. }
  1050. if ($return_flat_array) {
  1051. return $flat_array;
  1052. }
  1053. unset($skills);
  1054. return $skills_tree;
  1055. }
  1056. /**
  1057. * Get skills tree as a simplified JSON structure
  1058. * @param int user id
  1059. * @param int skill id
  1060. * @param bool return a flat array or not
  1061. * @param int depth of the skills
  1062. * @return json
  1063. */
  1064. public function get_skills_tree_json(
  1065. $user_id = null,
  1066. $skill_id = null,
  1067. $return_flat_array = false,
  1068. $main_depth = 2
  1069. ) {
  1070. $tree = $this->get_skills_tree(
  1071. $user_id,
  1072. $skill_id,
  1073. $return_flat_array,
  1074. true
  1075. );
  1076. $simple_tree = array();
  1077. if (!empty($tree['children'])) {
  1078. foreach ($tree['children'] as $element) {
  1079. $children = [];
  1080. if (isset($element['children'])) {
  1081. $children = $this->get_skill_json($element['children'], 1, $main_depth);
  1082. }
  1083. $simple_tree[] = array(
  1084. 'name' => $element['name'],
  1085. 'children' => $children
  1086. );
  1087. }
  1088. }
  1089. return json_encode($simple_tree[0]['children']);
  1090. }
  1091. /**
  1092. * Get JSON element
  1093. * @param array $subtree
  1094. * @param int $depth
  1095. * @param int $max_depth
  1096. * @return array|null
  1097. */
  1098. public function get_skill_json($subtree, $depth = 1, $max_depth = 2)
  1099. {
  1100. $simple_sub_tree = array();
  1101. if (is_array($subtree)) {
  1102. $counter = 1;
  1103. foreach ($subtree as $elem) {
  1104. $tmp = array();
  1105. $tmp['name'] = $elem['name'];
  1106. $tmp['id'] = $elem['id'];
  1107. $tmp['isSearched'] = self::isSearched($elem['id']);
  1108. if (isset($elem['children']) && is_array($elem['children'])) {
  1109. $tmp['children'] = $this->get_skill_json(
  1110. $elem['children'],
  1111. $depth + 1,
  1112. $max_depth
  1113. );
  1114. } else {
  1115. //$tmp['colour'] = $this->colours[$depth][rand(0,3)];
  1116. }
  1117. if ($depth > $max_depth) {
  1118. continue;
  1119. }
  1120. $tmp['depth'] = $depth;
  1121. $tmp['counter'] = $counter;
  1122. $counter++;
  1123. if (isset($elem['data']) && is_array($elem['data'])) {
  1124. foreach ($elem['data'] as $key => $item) {
  1125. $tmp[$key] = $item;
  1126. }
  1127. }
  1128. $simple_sub_tree[] = $tmp;
  1129. }
  1130. return $simple_sub_tree;
  1131. }
  1132. return null;
  1133. }
  1134. /**
  1135. * @param int $user_id
  1136. * @return bool
  1137. */
  1138. public function get_user_skill_ranking($user_id)
  1139. {
  1140. $user_id = intval($user_id);
  1141. $sql = "SELECT count(skill_id) count FROM {$this->table} s
  1142. INNER JOIN {$this->table_skill_rel_user} su
  1143. ON (s.id = su.skill_id)
  1144. WHERE user_id = $user_id";
  1145. $result = Database::query($sql);
  1146. if (Database::num_rows($result)) {
  1147. $result = Database::fetch_row($result);
  1148. return $result[0];
  1149. }
  1150. return false;
  1151. }
  1152. /**
  1153. * @param $start
  1154. * @param $limit
  1155. * @param $sidx
  1156. * @param $sord
  1157. * @param $where_condition
  1158. * @return array
  1159. */
  1160. public function get_user_list_skill_ranking(
  1161. $start,
  1162. $limit,
  1163. $sidx,
  1164. $sord,
  1165. $where_condition
  1166. ) {
  1167. $start = intval($start);
  1168. $limit = intval($limit);
  1169. /* ORDER BY $sidx $sord */
  1170. $sql = "SELECT *, @rownum:=@rownum+1 rank FROM (
  1171. SELECT u.user_id, firstname, lastname, count(username) skills_acquired
  1172. FROM {$this->table} s INNER JOIN {$this->table_skill_rel_user} su ON (s.id = su.skill_id)
  1173. INNER JOIN {$this->table_user} u ON u.user_id = su.user_id, (SELECT @rownum:=0) r
  1174. WHERE 1=1 $where_condition
  1175. GROUP BY username
  1176. ORDER BY skills_acquired desc
  1177. LIMIT $start , $limit) AS T1, (SELECT @rownum:=0) r";
  1178. $result = Database::query($sql);
  1179. if (Database::num_rows($result)) {
  1180. return Database::store_result($result, 'ASSOC');
  1181. }
  1182. return array();
  1183. }
  1184. /**
  1185. * @return int
  1186. */
  1187. public function get_user_list_skill_ranking_count()
  1188. {
  1189. $sql = "SELECT count(*) FROM (
  1190. SELECT count(distinct 1)
  1191. FROM {$this->table} s
  1192. INNER JOIN {$this->table_skill_rel_user} su
  1193. ON (s.id = su.skill_id)
  1194. INNER JOIN {$this->table_user} u
  1195. ON u.user_id = su.user_id
  1196. GROUP BY username
  1197. ) as T1";
  1198. $result = Database::query($sql);
  1199. if (Database::num_rows($result)) {
  1200. $result = Database::fetch_row($result);
  1201. return $result[0];
  1202. }
  1203. return 0;
  1204. }
  1205. /**
  1206. * @param string $courseCode
  1207. * @return int
  1208. */
  1209. public function get_count_skills_by_course($courseCode)
  1210. {
  1211. $courseCode = Database::escape_string($courseCode);
  1212. $sql = "SELECT count(skill_id) as count
  1213. FROM {$this->table_gradebook} g
  1214. INNER JOIN {$this->table_skill_rel_gradebook} sg
  1215. ON g.id = sg.gradebook_id
  1216. WHERE course_code = '$courseCode'";
  1217. $result = Database::query($sql);
  1218. if (Database::num_rows($result)) {
  1219. $result = Database::fetch_row($result);
  1220. return $result[0];
  1221. }
  1222. return 0;
  1223. }
  1224. /**
  1225. * @param int $skill_id
  1226. * @return array
  1227. */
  1228. public function get_courses_by_skill($skill_id)
  1229. {
  1230. $skill_id = intval($skill_id);
  1231. $sql = "SELECT c.title, c.code
  1232. FROM {$this->table_gradebook} g
  1233. INNER JOIN {$this->table_skill_rel_gradebook} sg
  1234. ON g.id = sg.gradebook_id
  1235. INNER JOIN {$this->table_course} c
  1236. ON c.code = g.course_code
  1237. WHERE sg.skill_id = $skill_id
  1238. AND (g.session_id IS NULL OR g.session_id = 0)";
  1239. $result = Database::query($sql);
  1240. return Database::store_result($result, 'ASSOC');
  1241. }
  1242. /**
  1243. * Check if the user has the skill
  1244. * @param int $userId The user id
  1245. * @param int $skillId The skill id
  1246. * @param int $courseId Optional. The course id
  1247. * @param int $sessionId Optional. The session id
  1248. * @return boolean Whether the user has the skill return true. Otherwise return false
  1249. */
  1250. public function userHasSkill($userId, $skillId, $courseId = 0, $sessionId = 0)
  1251. {
  1252. $courseId = intval($courseId);
  1253. $sessionId = intval($sessionId);
  1254. $whereConditions = array(
  1255. 'user_id = ? ' => intval($userId),
  1256. 'AND skill_id = ? ' => intval($skillId)
  1257. );
  1258. if ($courseId > 0) {
  1259. $whereConditions['AND course_id = ? '] = $courseId;
  1260. $whereConditions['AND session_id = ? '] = $sessionId ? $sessionId : null;
  1261. }
  1262. $result = Database::select(
  1263. 'COUNT(1) AS qty',
  1264. $this->table_skill_rel_user,
  1265. array(
  1266. 'where' => $whereConditions
  1267. ),
  1268. 'first'
  1269. );
  1270. if ($result != false) {
  1271. if ($result['qty'] > 0) {
  1272. return true;
  1273. }
  1274. }
  1275. return false;
  1276. }
  1277. /**
  1278. * Check if a skill is searched
  1279. * @param int $id The skill id
  1280. * @return boolean Whether el skill is searched return true. Otherwise return false
  1281. */
  1282. public static function isSearched($id)
  1283. {
  1284. $id = intval($id);
  1285. if (empty($id)) {
  1286. return false;
  1287. }
  1288. $skillRelProfileTable = Database::get_main_table(TABLE_MAIN_SKILL_REL_PROFILE);
  1289. $result = Database::select(
  1290. 'COUNT( DISTINCT `skill_id`) AS qty',
  1291. $skillRelProfileTable,
  1292. array(
  1293. 'where' => array(
  1294. 'skill_id = ?' => $id
  1295. )
  1296. ),
  1297. 'first'
  1298. );
  1299. if ($result === false) {
  1300. return false;
  1301. }
  1302. if ($result['qty'] > 0) {
  1303. return true;
  1304. }
  1305. return false;
  1306. }
  1307. /**
  1308. * Get the achieved skills by course
  1309. * @param int $courseId The course id
  1310. * @return array The skills list
  1311. */
  1312. public function listAchievedByCourse($courseId)
  1313. {
  1314. $courseId = intval($courseId);
  1315. if ($courseId == 0) {
  1316. return array();
  1317. }
  1318. $list = array();
  1319. $sql = "SELECT
  1320. course.id c_id,
  1321. course.title c_name,
  1322. course.directory c_directory,
  1323. user.user_id,
  1324. user.lastname,
  1325. user.firstname,
  1326. user.username,
  1327. skill.id skill_id,
  1328. skill.name skill_name,
  1329. sru.acquired_skill_at
  1330. FROM {$this->table_skill_rel_user} AS sru
  1331. INNER JOIN {$this->table_course}
  1332. ON sru.course_id = course.id
  1333. INNER JOIN {$this->table_user}
  1334. ON sru.user_id = user.user_id
  1335. INNER JOIN {$this->table}
  1336. ON sru.skill_id = skill.id
  1337. WHERE course.id = $courseId";
  1338. $result = Database::query($sql);
  1339. while ($row = Database::fetch_assoc($result)) {
  1340. $list[] = $row;
  1341. }
  1342. return $list;
  1343. }
  1344. /**
  1345. * Get the users list who achieved a skill
  1346. * @param int $skillId The skill id
  1347. *
  1348. * @return array The users list
  1349. */
  1350. public function listUsersWhoAchieved($skillId)
  1351. {
  1352. $skillId = intval($skillId);
  1353. if ($skillId == 0) {
  1354. return array();
  1355. }
  1356. $list = array();
  1357. $sql = "SELECT
  1358. course.id c_id,
  1359. course.title c_name,
  1360. course.directory c_directory,
  1361. user.user_id,
  1362. user.lastname,
  1363. user.firstname,
  1364. user.username,
  1365. skill.id skill_id,
  1366. skill.name skill_name,
  1367. sru.acquired_skill_at
  1368. FROM {$this->table_skill_rel_user} AS sru
  1369. INNER JOIN {$this->table_course}
  1370. ON sru.course_id = course.id
  1371. INNER JOIN {$this->table_user}
  1372. ON sru.user_id = user.user_id
  1373. INNER JOIN {$this->table}
  1374. ON sru.skill_id = skill.id
  1375. WHERE skill.id = $skillId ";
  1376. $result = Database::query($sql);
  1377. while ($row = Database::fetch_assoc($result)) {
  1378. $list[] = $row;
  1379. }
  1380. return $list;
  1381. }
  1382. /**
  1383. * Get the session list where the user can achieve a skill
  1384. * @param int $skillId The skill id
  1385. * @return array
  1386. */
  1387. public function getSessionsBySkill($skillId)
  1388. {
  1389. $skillId = intval($skillId);
  1390. $sql = "SELECT s.id, s.name
  1391. FROM {$this->table_gradebook} g
  1392. INNER JOIN {$this->table_skill_rel_gradebook} sg
  1393. ON g.id = sg.gradebook_id
  1394. INNER JOIN {$this->sessionTable} s
  1395. ON g.session_id = s.id
  1396. WHERE sg.skill_id = $skillId
  1397. AND g.session_id > 0";
  1398. $result = Database::query($sql);
  1399. return Database::store_result($result, 'ASSOC');
  1400. }
  1401. /**
  1402. * Check if the $fromUser can comment the $toUser skill issue
  1403. * @param User $fromUser
  1404. * @param User $toUser
  1405. * @return boolean
  1406. */
  1407. public static function userCanAddFeedbackToUser($fromUser, $toUser)
  1408. {
  1409. if (api_is_platform_admin()) {
  1410. return true;
  1411. }
  1412. $entityManager = Database::getManager();
  1413. /** @var UserRepository $userRepo */
  1414. $userRepo = $entityManager->getRepository('ChamiloUserBundle:User');
  1415. $fromUserStatus = $fromUser->getStatus();
  1416. switch ($fromUserStatus) {
  1417. case SESSIONADMIN:
  1418. if (api_get_setting('allow_session_admins_to_manage_all_sessions') === 'true') {
  1419. if ($toUser->getCreatorId() === $fromUser->getId()) {
  1420. return true;
  1421. }
  1422. }
  1423. $sessionAdmins = $userRepo->getSessionAdmins($toUser);
  1424. foreach ($sessionAdmins as $sessionAdmin) {
  1425. if ($sessionAdmin->getId() !== $fromUser->getId()) {
  1426. continue;
  1427. }
  1428. return true;
  1429. }
  1430. break;
  1431. case STUDENT_BOSS:
  1432. $studentBosses = $userRepo->getStudentBosses($toUser);
  1433. foreach ($studentBosses as $studentBoss) {
  1434. if ($studentBoss->getId() !== $fromUser->getId()) {
  1435. continue;
  1436. }
  1437. return true;
  1438. }
  1439. break;
  1440. case DRH:
  1441. return UserManager::is_user_followed_by_drh(
  1442. $toUser->getId(),
  1443. $fromUser->getId()
  1444. );
  1445. }
  1446. return false;
  1447. }
  1448. /**
  1449. * If $studentId is set then check if current user has the right to see
  1450. * the page.
  1451. * @param int $studentId check if current user has access to see $studentId
  1452. * @param bool $blockPage raise a api_not_allowed()
  1453. *
  1454. * @return bool
  1455. */
  1456. public static function isAllow($studentId = 0, $blockPage = true)
  1457. {
  1458. if (self::isToolAvailable()) {
  1459. if (api_is_platform_admin()) {
  1460. return true;
  1461. }
  1462. if (!empty($studentId)) {
  1463. $currentUserId = api_get_user_id();
  1464. if ((int) $currentUserId === (int) $studentId) {
  1465. return true;
  1466. }
  1467. $haveAccess = self::hasAccessToUserSkill(
  1468. $currentUserId,
  1469. $studentId
  1470. );
  1471. if ($haveAccess) {
  1472. return true;
  1473. }
  1474. }
  1475. }
  1476. if ($blockPage) {
  1477. api_not_allowed(true);
  1478. }
  1479. return false;
  1480. }
  1481. /**
  1482. * @return bool
  1483. */
  1484. public static function isToolAvailable()
  1485. {
  1486. $allowTool = api_get_setting('allow_skills_tool');
  1487. if ($allowTool == 'true') {
  1488. return true;
  1489. }
  1490. return false;
  1491. }
  1492. /**
  1493. * @param $currentUserId
  1494. * @param $studentId
  1495. * @return bool
  1496. */
  1497. public static function hasAccessToUserSkill($currentUserId, $studentId)
  1498. {
  1499. if (self::isToolAvailable()) {
  1500. if (api_is_platform_admin()) {
  1501. return true;
  1502. }
  1503. $allow = api_get_configuration_value('allow_private_skills');
  1504. if ($allow === true) {
  1505. if (api_is_teacher()) {
  1506. return UserManager::isTeacherOfStudent(
  1507. $currentUserId,
  1508. $studentId
  1509. );
  1510. }
  1511. if (api_is_drh()) {
  1512. return UserManager::is_user_followed_by_drh(
  1513. $studentId,
  1514. $currentUserId
  1515. );
  1516. }
  1517. }
  1518. }
  1519. return false;
  1520. }
  1521. }