userInfoLib.php 16 KB

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