userInfoLib.php 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622
  1. <?php
  2. /*
  3. ==============================================================================
  4. Dokeos - elearning and course management software
  5. Copyright (c) 2004 Dokeos S.A.
  6. Copyright (c) 2003 Ghent University (UGent)
  7. Copyright (c) 2001 Universite catholique de Louvain (UCL)
  8. For a full list of contributors, see "credits.txt".
  9. The full license can be read in "license.txt".
  10. This program is free software; you can redistribute it and/or
  11. modify it under the terms of the GNU General Public License
  12. as published by the Free Software Foundation; either version 2
  13. of the License, or (at your option) any later version.
  14. See the GNU General Public License for more details.
  15. Contact: Dokeos, 181 rue Royale, B-1000 Brussels, Belgium, info@dokeos.com
  16. ==============================================================================
  17. */
  18. /**
  19. ==============================================================================
  20. * @package dokeos.user
  21. ==============================================================================
  22. */
  23. /*----------------------------------------
  24. CATEGORIES DEFINITION TREATMENT
  25. --------------------------------------*/
  26. /**
  27. * create a new category definition for the user information
  28. *
  29. * @author - Hugues peeters <peeters@ipm.ucl.ac.be>
  30. * @author - Christophe Gesch� <gesche@ipm.ucl.ac.be>
  31. * @param - string $title - category title
  32. * @param - string $comment - title comment
  33. * @param - int$nbline - lines number for the field the user will fill.
  34. * @return - bollean true if succeed, else bolean false
  35. */
  36. function create_cat_def($title="", $comment="", $nbline="5")
  37. {
  38. global $TBL_USERINFO_DEF; //taken from userInfo.php
  39. $title = Database::escape_string(trim($title));
  40. $comment = Database::escape_string(trim($comment));
  41. $nbline = strval(intval($nbline));
  42. if ( 0 == (int) $nbline || empty($title))
  43. {
  44. return false;
  45. }
  46. $sql = "SELECT MAX(rank) as maxRank FROM ".$TBL_USERINFO_DEF;
  47. $result = Database::query($sql,__FILE__,__LINE__);
  48. if ($result) $maxRank = Database::fetch_array($result);
  49. $maxRank = $maxRank['maxRank'];
  50. $thisRank = $maxRank + 1;
  51. $sql = "INSERT INTO $TBL_USERINFO_DEF SET
  52. title = '$title',
  53. comment = '$comment',
  54. line_count = '$nbline',
  55. rank = '$thisRank'";
  56. Database::query($sql,__FILE__,__LINE__);
  57. return true;
  58. }
  59. /**
  60. * modify the definition of a user information category
  61. *
  62. * @author - Hugues peeters <peeters@ipm.ucl.ac.be>
  63. * @author - Christophe Gesch� <gesche@ipm.ucl.ac.be>
  64. * @param - int $id - id of the category
  65. * @param - string $title - category title
  66. * @param - string $comment - title comment
  67. * @param - int$nbline - lines number for the field the user will fill.
  68. * @return - boolean true if succeed, else otherwise
  69. */
  70. function edit_cat_def($id, $title, $comment, $nbline)
  71. {
  72. global $TBL_USERINFO_DEF;
  73. if ( 0 == $nbline || 0 == $id )
  74. {
  75. return false;
  76. }
  77. $id = strval(intval($id)); //make sure id is integer
  78. $title = Database::escape_string(trim($title));
  79. $comment = Database::escape_string(trim($comment));
  80. $nbline = strval(intval($nbline));
  81. $sql = "UPDATE ".$TBL_USERINFO_DEF." SET
  82. title = '$title',
  83. comment = '$comment',
  84. line_count = '$nbline'
  85. WHERE id = '$id'";
  86. Database::query($sql,__FILE__,__LINE__);
  87. return true;
  88. }
  89. /**
  90. * remove a category from the category list
  91. *
  92. * @author - Hugues peeters <peeters@ipm.ucl.ac.be>
  93. * @author - Christophe Gesche <gesche@ipm.ucl.ac.be>
  94. *
  95. * @param - int $id - id of the category
  96. * or "ALL" for all category
  97. * @param - boolean $force - FALSE (default) : prevents removal if users have
  98. * already fill this category
  99. * TRUE : bypass user content existence check
  100. * @param - int $nbline - lines number for the field the user will fill.
  101. * @return - bollean - TRUE if succeed, ELSE otherwise
  102. */
  103. function remove_cat_def($id, $force = false)
  104. {
  105. global $TBL_USERINFO_CONTENT, $TBL_USERINFO_DEF;
  106. $id = strval(intval($id));
  107. if ( (0 == (int) $id || $id == "ALL") || ! is_bool($force))
  108. {
  109. return false;
  110. }
  111. $sqlCondition = " WHERE id = '$id'";
  112. if ($force == false)
  113. {
  114. $sql = "SELECT * FROM $TBL_USERINFO_CONTENT $sqlCondition";
  115. $result = Database::query($sql,__FILE__,__LINE__);
  116. if ( Database::num_rows($result) > 0)
  117. {
  118. return false;
  119. }
  120. }
  121. $sql = "DELETE FROM $TBL_USERINFO_DEF $sqlCondition";
  122. Database::query($sql,__FILE__,__LINE__);
  123. }
  124. /**
  125. * move a category in the category list
  126. *
  127. * @author - Hugues peeters <peeters@ipm.ucl.ac.be>
  128. * @author - Christophe Gesch� <gesche@ipm.ucl.ac.be>
  129. *
  130. * @param - int $id - id of the category
  131. * @param - direction "up" or "down" :
  132. * "up" decrease the rank of gived $id by switching rank with the just lower
  133. * "down" increase the rank of gived $id by switching rank with the just upper
  134. *
  135. * @return - boolean true if succeed, else bolean false
  136. */
  137. function move_cat_rank($id, $direction) // up & down.
  138. {
  139. global $TBL_USERINFO_DEF;
  140. $id = strval(intval($id));
  141. if ( 0 == (int) $id || ! ($direction == "up" || $direction == "down") )
  142. {
  143. return false;
  144. }
  145. $sql = "SELECT rank FROM $TBL_USERINFO_DEF WHERE id = '$id'";
  146. $result = Database::query($sql,__FILE__,__LINE__);
  147. if (Database::num_rows($result) < 1)
  148. {
  149. return false;
  150. }
  151. $cat = Database::fetch_array($result);
  152. $rank = (int) $cat['rank'];
  153. return move_cat_rank_by_rank($rank, $direction);
  154. }
  155. /**
  156. * move a category in the category list
  157. *
  158. * @author - Hugues peeters <peeters@ipm.ucl.ac.be>
  159. * @author - Christophe Gesche <gesche@ipm.ucl.ac.be>
  160. *
  161. * @param - int $rank - actual rank of the category
  162. * @param - direction "up" or "down" :
  163. * "up" decrease the rank of gived $rank by switching rank with the just lower
  164. * "down" increase the rank of gived $rank by switching rank with the just upper
  165. *
  166. * @return - boolean true if succeed, else bolean false
  167. */
  168. function move_cat_rank_by_rank($rank, $direction) // up & down.
  169. {
  170. global $TBL_USERINFO_DEF;
  171. if ( 0 == (int) $rank || ! ($direction == "up" || $direction == "down") )
  172. {
  173. return false;
  174. }
  175. if ($direction == "down") // thus increase rank ...
  176. {
  177. $sort = "ASC";
  178. $compOp = ">=";
  179. }
  180. else // thus decrease rank ...
  181. {
  182. $sort = "DESC";
  183. $compOp = "<=";
  184. }
  185. // this request find the 2 line to be switched (on rank value)
  186. $sql = "SELECT id, rank FROM ".$TBL_USERINFO_DEF." WHERE rank $compOp $rank
  187. ORDER BY rank $sort LIMIT 2";
  188. $result = Database::query($sql,__FILE__,__LINE__);
  189. if (Database::num_rows($result) < 2)
  190. {
  191. return false;
  192. }
  193. $thisCat = Database::fetch_array($result);
  194. $nextCat = Database::fetch_array($result);
  195. $sql1 = "UPDATE ".$TBL_USERINFO_DEF." SET rank ='".$nextCat['rank'].
  196. "' WHERE id = '".$thisCat['id']."'";
  197. $sql2 = "UPDATE ".$TBL_USERINFO_DEF." SET rank ='".$thisCat['rank'].
  198. "' WHERE id = '".$nextCat['id']."'";
  199. Database::query($sql1,__FILE__,__LINE__);
  200. Database::query($sql2,__FILE__,__LINE__);
  201. return true;
  202. }
  203. /**
  204. * @author Hugues Peeters - peeters@ipm.ucl.ac.be
  205. * @param int $user_id
  206. * @param string $course_code
  207. * @param array $properties - should contain 'role', 'status', 'tutor_id'
  208. * @return boolean true if succeed false otherwise
  209. */
  210. function update_user_course_properties($user_id, $course_code, $properties)
  211. {
  212. global $tbl_coursUser,$_user;
  213. $sqlChangeStatus = "";
  214. $user_id = strval(intval($user_id));//filter integer
  215. $course_code = Database::escape_string($course_code);
  216. if ($user_id != $_user['user_id'])
  217. {
  218. $sqlChangeStatus = "status = '".Database::escape_string($properties['status'])."',";
  219. }
  220. //feature deprecated tutor_id = '".Database::escape_string($properties['tutor'])."'
  221. $sql = "UPDATE $tbl_coursUser
  222. SET ".$sqlChangeStatus."
  223. role = '".Database::escape_string($properties['role'])."',
  224. tutor_id = '".Database::escape_string($properties['tutor'])."'
  225. WHERE user_id = '".$user_id."'
  226. AND course_code = '".$course_code."'";
  227. $result = Database::query($sql,__FILE__,__LINE__);
  228. if (Database::affected_rows() > 0)
  229. {
  230. return true;
  231. }
  232. else
  233. {
  234. return false;
  235. }
  236. }
  237. /*----------------------------------------
  238. CATEGORIES CONTENT TREATMENT
  239. --------------------------------------*/
  240. /**
  241. * fill a bloc for information category
  242. *
  243. * @author - Hugues peeters <peeters@ipm.ucl.ac.be>
  244. * @author - Christophe Gesche <gesche@ipm.ucl.ac.be>
  245. * @param - $definition_id,
  246. * @param - $user_id,
  247. * @param - $user_ip,
  248. * @param - $content
  249. * @return - boolean true if succeed, else bolean false
  250. */
  251. function fill_new_cat_content($definition_id, $user_id, $content="", $user_ip="")
  252. {
  253. global $TBL_USERINFO_CONTENT;
  254. if (empty($user_ip))
  255. {
  256. $user_ip = $_SERVER['REMOTE_ADDR'];
  257. }
  258. $definition_id = strval(intval($definition_id));
  259. $user_id = strval(intval($user_id));
  260. $content = Database::escape_string(trim($content));
  261. $user_ip = Database::escape_string(trim($user_ip));
  262. if ( 0 == $definition_id || 0 == $user_id || $content == "")
  263. {
  264. // Here we should introduce an error handling system...
  265. return false;
  266. }
  267. // Do not create if already exist
  268. $sql = "SELECT id FROM ".$TBL_USERINFO_CONTENT."
  269. WHERE definition_id = '$definition_id'
  270. AND user_id = '$user_id'";
  271. $result = Database::query($sql,__FILE__,__LINE__);
  272. if (Database::num_rows($result) > 0)
  273. {
  274. return false;
  275. }
  276. $sql = "INSERT INTO ".$TBL_USERINFO_CONTENT." SET
  277. content = '$content',
  278. definition_id = '$definition_id',
  279. user_id = '$user_id',
  280. editor_ip = '$user_ip',
  281. edition_time = now()";
  282. Database::query($sql,__FILE__,__LINE__);
  283. return true;
  284. }
  285. /**
  286. * Edit a bloc for information category
  287. *
  288. * @author - Hugues peeters <peeters@ipm.ucl.ac.be>
  289. * @author - Christophe Gesche <gesche@ipm.ucl.ac.be>
  290. * @param - $definition_id,
  291. * @param - $user_id,
  292. * @param - $user_ip, DEFAULT $REMOTE_ADDR
  293. * @param - $content ; if empty call delete the bloc
  294. * @return - boolean true if succeed, else bolean false
  295. */
  296. function edit_cat_content($definition_id, $user_id, $content ="", $user_ip="")
  297. {
  298. global $TBL_USERINFO_CONTENT;
  299. $definition_id = strval(intval($definition_id));
  300. $user_id = strval(intval($user_id));
  301. $content = Database::escape_string(trim($content));
  302. if (empty($user_ip))
  303. {
  304. $user_ip = $_SERVER['REMOTE_ADDR'];
  305. }
  306. $user_ip = Database::escape_string($user_ip);
  307. if (0 == $user_id || 0 == $definition_id)
  308. {
  309. return false;
  310. }
  311. if ( $content == "")
  312. {
  313. return cleanout_cat_content($user_id, $definition_id);
  314. }
  315. $sql= "UPDATE ".$TBL_USERINFO_CONTENT." SET
  316. content = '$content',
  317. editor_ip = '$user_ip',
  318. edition_time = now()
  319. WHERE definition_id = '$definition_id' AND user_id = '$user_id'";
  320. Database::query($sql,__FILE__,__LINE__);
  321. return true;
  322. }
  323. /**
  324. * clean the content of a bloc for information category
  325. *
  326. * @author - Hugues peeters <peeters@ipm.ucl.ac.be>
  327. * @author - Christophe Gesche <gesche@ipm.ucl.ac.be>
  328. * @param - $definition_id,
  329. * @param - $user_id
  330. * @return - boolean true if succeed, else bolean false
  331. */
  332. function cleanout_cat_content($user_id, $definition_id)
  333. {
  334. global $TBL_USERINFO_CONTENT;
  335. $user_id = strval(intval($user_id));
  336. $definition_id = strval(intval($definition_id));
  337. if (0 == $user_id || 0 == $definition_id)
  338. {
  339. return false;
  340. }
  341. $sql = "DELETE FROM ".$TBL_USERINFO_CONTENT."
  342. WHERE user_id = '$user_id' AND definition_id = '$definition_id'";
  343. Database::query($sql,__FILE__,__LINE__);
  344. return true;
  345. }
  346. /*----------------------------------------
  347. SHOW USER INFORMATION TREATMENT
  348. --------------------------------------*/
  349. /**
  350. * get the user info from the user id
  351. * @author - Hugues Peeters <peeters@ipm.ucl.ac.be>
  352. * @author - Christophe Gesche <gesche@ipm.ucl.ac.be>
  353. * @param - int $user_id user id as stored in the Dokeos main db
  354. * @return - array containg user info sort by categories rank
  355. * each rank contains 'title', 'comment', 'content', 'cat_id'
  356. */
  357. function get_course_user_info($user_id)
  358. {
  359. global $TBL_USERINFO_CONTENT, $TBL_USERINFO_DEF;
  360. $sql = "SELECT cat.id catId, cat.title,
  361. cat.comment , content.content
  362. FROM ".$TBL_USERINFO_DEF." cat LEFT JOIN ".$TBL_USERINFO_CONTENT." content
  363. ON cat.id = content.definition_id AND content.user_id = '$user_id'
  364. ORDER BY cat.rank, content.id";
  365. $result = Database::query($sql,__FILE__,__LINE__);
  366. if (Database::num_rows($result) > 0)
  367. {
  368. while ($userInfo = Database::fetch_array($result, 'ASSOC'))
  369. {
  370. $userInfos[]=$userInfo;
  371. }
  372. return $userInfos;
  373. }
  374. return false;
  375. }
  376. /**
  377. * get the main user information
  378. * @author - Hugues Peeters <peeters@ipm.ucl.ac.be>
  379. * @author - Christophe Gesche <gesche@ipm.ucl.ac.be>
  380. * @param - int $user_id user id as stored in the Dokeos main db
  381. * @return - array containing user info as 'lastName', 'firstName'
  382. * 'email', 'role'
  383. */
  384. function get_main_user_info($user_id, $courseCode)
  385. {
  386. $user_id = strval(intval($user_id));
  387. $courseCode = Database::escape_string($courseCode);
  388. if (0 == $user_id)
  389. {
  390. return false;
  391. }
  392. $table_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  393. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  394. $sql = "SELECT u.*, u.lastname lastName, u.firstname firstName,
  395. u.email, u.picture_uri picture, cu.role,
  396. cu.status status, cu.tutor_id
  397. FROM $table_user u, $table_course_user cu
  398. WHERE u.user_id = cu.user_id
  399. AND u.user_id = '$user_id'
  400. AND cu.course_code = '$courseCode'";
  401. $result = Database::query($sql,__FILE__,__LINE__);
  402. if (Database::num_rows($result) > 0)
  403. {
  404. $userInfo = Database::fetch_array($result, 'ASSOC');
  405. $userInfo['password']='';
  406. return $userInfo;
  407. }
  408. return false;
  409. }
  410. /**
  411. * get the user content of a categories plus the categories definition
  412. * @author - Hugues Peeters <peeters@ipm.ucl.ac.be>
  413. * @author - Christophe Gesche <gesche@ipm.ucl.ac.be>
  414. * @param - int $userId - id of the user
  415. * @param - int $catId - id of the categories
  416. * @return - array containing 'catId', 'title', 'comment',
  417. * 'nbline', 'contentId' and 'content'
  418. */
  419. function get_cat_content($userId, $catId)
  420. {
  421. global $TBL_USERINFO_CONTENT, $TBL_USERINFO_DEF;
  422. $userId = strval(intval($userId));
  423. $catId = strval(intval($catId));
  424. $sql = "SELECT cat.id catId, cat.title,
  425. cat.comment , cat.line_count,
  426. content.id contentId, content.content
  427. FROM ".$TBL_USERINFO_DEF." cat LEFT JOIN ".$TBL_USERINFO_CONTENT." content
  428. ON cat.id = content.definition_id
  429. AND content.user_id = '$userId'
  430. WHERE cat.id = '$catId' ";
  431. $result = Database::query($sql,__FILE__,__LINE__);
  432. if (Database::num_rows($result) > 0)
  433. {
  434. $catContent = Database::fetch_array($result, 'ASSOC');
  435. $catContent['nbline'] = $catContent['line_count'];
  436. return $catContent;
  437. }
  438. return false;
  439. }
  440. /**
  441. * get the definition of a category
  442. *
  443. * @author - Christophe Gesche <gesche@ipm.ucl.ac.be>
  444. * @author - Hugues Peeters <peeters@ipm.ucl.ac.be>
  445. * @param - int $catId - id of the categories
  446. * @return - array containing 'id', 'title', 'comment', and 'nbline',
  447. */
  448. function get_cat_def($catId)
  449. {
  450. global $TBL_USERINFO_DEF;
  451. $catId = strval(intval($catId));
  452. $sql = "SELECT id, title, comment, line_count, rank FROM ".$TBL_USERINFO_DEF." WHERE id = '$catId'";
  453. $result = Database::query($sql,__FILE__,__LINE__);
  454. if (Database::num_rows($result) > 0)
  455. {
  456. $catDef = Database::fetch_array($result, 'ASSOC');
  457. $catDef['nbline'] = $catDef['line_count'];
  458. return $catDef;
  459. }
  460. return false;
  461. }
  462. /**
  463. * get list of all this course categories
  464. *
  465. * @author - Christophe Gesche <gesche@ipm.ucl.ac.be>
  466. * @author - Hugues Peeters <peeters@ipm.ucl.ac.be>
  467. * @return - array containing a list of arrays.
  468. * And each of these arrays contains
  469. * 'catId', 'title', 'comment', and 'nbline',
  470. */
  471. function get_cat_def_list()
  472. {
  473. global $TBL_USERINFO_DEF;
  474. $sql = "SELECT id catId, title, comment , line_count
  475. FROM ".$TBL_USERINFO_DEF."
  476. ORDER BY rank";
  477. $result = Database::query($sql,__FILE__,__LINE__);
  478. if (Database::num_rows($result) > 0)
  479. {
  480. while ($cat_def = Database::fetch_array($result, 'ASSOC'))
  481. {
  482. $cat_def_list[]=$cat_def;
  483. }
  484. return $cat_def_list;
  485. }
  486. return false;
  487. }
  488. /**
  489. * transform content in a html display
  490. * @author - Hugues Peeters <peeters@ipm.ucl.ac.be>
  491. * @param - string $string string to htmlize
  492. * @ return - string htmlized
  493. */
  494. function htmlize($phrase)
  495. {
  496. global $charset;
  497. return nl2br(htmlspecialchars($phrase,ENT_QUOTES,$charset));
  498. }