usermanager.lib.php 51 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418
  1. <?php // $Id: usermanager.lib.php 15821 2008-07-18 12:15:35Z pcool $
  2. /*
  3. ==============================================================================
  4. Dokeos - elearning and course management software
  5. Copyright (c) 2004-2008 Dokeos SPRL
  6. Copyright (c) 2003 Ghent University (UGent)
  7. Copyright (c) 2001 Universite catholique de Louvain (UCL)
  8. Copyright (c) various contributors
  9. Copyright (c) Bart Mollet, Hogeschool Gent
  10. For a full list of contributors, see "credits.txt".
  11. The full license can be read in "license.txt".
  12. This program is free software; you can redistribute it and/or
  13. modify it under the terms of the GNU General Public License
  14. as published by the Free Software Foundation; either version 2
  15. of the License, or (at your option) any later version.
  16. See the GNU General Public License for more details.
  17. Contact: Dokeos, rue du Corbeau, 108, B-1030 Brussels, Belgium, info@dokeos.com
  18. ==============================================================================
  19. */
  20. /**
  21. ==============================================================================
  22. * This library provides functions for user management.
  23. * Include/require it in your code to use its functionality.
  24. *
  25. * @package dokeos.library
  26. ==============================================================================
  27. */
  28. // define constants for user extra field types
  29. define('USER_FIELD_TYPE_TEXT',1);
  30. define('USER_FIELD_TYPE_TEXTAREA',2);
  31. define('USER_FIELD_TYPE_RADIO',3);
  32. define('USER_FIELD_TYPE_SELECT',4);
  33. define('USER_FIELD_TYPE_SELECT_MULTIPLE',5);
  34. define('USER_FIELD_TYPE_DATE',6);
  35. define('USER_FIELD_TYPE_DATETIME',7);
  36. define('USER_FIELD_TYPE_DOUBLE_SELECT',8);
  37. define('USER_FIELD_TYPE_DIVIDER',9);
  38. class UserManager
  39. {
  40. /**
  41. * Creates a new user for the platform
  42. * @author Hugues Peeters <peeters@ipm.ucl.ac.be>,
  43. * Roan Embrechts <roan_embrechts@yahoo.com>
  44. *
  45. * @param string Firstname
  46. * @param string Lastname
  47. * @param int Status (1 for course tutor, 5 for student, 6 for anonymous)
  48. * @param string e-mail address
  49. * @param string Login
  50. * @param string Password
  51. * @param string Any official code (optional)
  52. * @param int User language (optional)
  53. * @param string Phone number (optional)
  54. * @param string Picture URI (optional)
  55. * @param string Authentication source (optional, defaults to 'platform', dependind on constant)
  56. * @param string Account expiration date (optional, defaults to '0000-00-00 00:00:00')
  57. * @param int Whether the account is enabled or disabled by default
  58. * @param int The user ID of the person who registered this user (optional, defaults to null)
  59. * @param int The department of HR in which the user is registered (optional, defaults to 0)
  60. * @return int new user id - if the new user creation succeeds
  61. * boolean false otherwise
  62. *
  63. * @desc The function tries to retrieve $_user['user_id'] from the global space.
  64. * if it exists, $_user['user_id'] is the creator id If a problem arises,
  65. * it stores the error message in global $api_failureList
  66. */
  67. function create_user($firstName, $lastName, $status, $email, $loginName, $password, $official_code = '', $language='', $phone = '', $picture_uri = '', $auth_source = PLATFORM_AUTH_SOURCE, $expiration_date = '0000-00-00 00:00:00', $active = 1, $hr_dept_id=0, $extra=null)
  68. {
  69. global $_user, $userPasswordCrypted;
  70. // database table definition
  71. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  72. // default langauge
  73. if ($language=='')
  74. {
  75. $language = api_get_setting('platformLanguage');
  76. }
  77. if ($_user['user_id'])
  78. {
  79. $creator_id = $_user['user_id'];
  80. }
  81. else
  82. {
  83. $creator_id = '';
  84. }
  85. // First check wether the login already exists
  86. if (! UserManager::is_username_available($loginName))
  87. return api_set_failure('login-pass already taken');
  88. //$password = "PLACEHOLDER";
  89. $password = ($userPasswordCrypted ? md5($password) : $password);
  90. $sql = "INSERT INTO $table_user
  91. SET lastname = '".Database::escape_string($lastName)."',
  92. firstname = '".Database::escape_string($firstName)."',
  93. username = '".Database::escape_string($loginName)."',
  94. status = '".Database::escape_string($status)."',
  95. password = '".Database::escape_string($password)."',
  96. email = '".Database::escape_string($email)."',
  97. official_code = '".Database::escape_string($official_code)."',
  98. picture_uri = '".Database::escape_string($picture_uri)."',
  99. creator_id = '".Database::escape_string($creator_id)."',
  100. auth_source = '".Database::escape_string($auth_source)."',
  101. phone = '".Database::escape_string($phone)."',
  102. language = '".Database::escape_string($language)."',
  103. registration_date = now(),
  104. expiration_date = '".Database::escape_string($expiration_date)."',
  105. hr_dept_id = '".Database::escape_string($hr_dept_id)."',
  106. active = '".Database::escape_string($active)."'";
  107. $result = api_sql_query($sql);
  108. if ($result)
  109. {
  110. //echo "id returned";
  111. $return=Database::get_last_insert_id();
  112. }
  113. else
  114. {
  115. //echo "false - failed" ;
  116. $return=false;
  117. }
  118. if(is_array($extra) AND count($extra)>0)
  119. {
  120. $res = true;
  121. foreach($extra as $fname => $fvalue)
  122. {
  123. $res = $res && UserManager::update_extra_field($return,$fname,$fvalue);
  124. }
  125. }
  126. return $return;
  127. }
  128. /**
  129. * Can user be deleted?
  130. * This functions checks if there's a course in which the given user is the
  131. * only course administrator. If that is the case, the user can't be
  132. * deleted because the course would remain without a course admin.
  133. * @param int $user_id The user id
  134. * @return boolean true if user can be deleted
  135. */
  136. function can_delete_user($user_id)
  137. {
  138. $table_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  139. $sql = "SELECT * FROM $table_course_user WHERE status = '1' AND user_id = '".$user_id."'";
  140. $res = api_sql_query($sql,__FILE__,__LINE__);
  141. while ($course = Database::fetch_object($res))
  142. {
  143. $sql = "SELECT user_id FROM $table_course_user WHERE status='1' AND course_code ='".$course->course_code."'";
  144. $res2 = api_sql_query($sql,__FILE__,__LINE__);
  145. if (Database::num_rows($res2) == 1)
  146. {
  147. return false;
  148. }
  149. }
  150. return true;
  151. }
  152. /**
  153. * Delete a user from the platform
  154. * @param int $user_id The user id
  155. * @return boolean true if user is succesfully deleted, false otherwise
  156. */
  157. function delete_user($user_id)
  158. {
  159. if (!UserManager :: can_delete_user($user_id))
  160. {
  161. return false;
  162. }
  163. $table_user = Database :: get_main_table(TABLE_MAIN_USER);
  164. $table_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  165. $table_class_user = Database :: get_main_table(TABLE_MAIN_CLASS_USER);
  166. $table_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  167. $table_admin = Database :: get_main_table(TABLE_MAIN_ADMIN);
  168. $table_session_user = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
  169. $table_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  170. // Unsubscribe the user from all groups in all his courses
  171. $sql = "SELECT * FROM $table_course c, $table_course_user cu WHERE cu.user_id = '".$user_id."' AND c.code = cu.course_code";
  172. $res = api_sql_query($sql,__FILE__,__LINE__);
  173. while ($course = Database::fetch_object($res))
  174. {
  175. $table_group = Database :: get_course_table(TABLE_GROUP_USER, $course->db_name);
  176. $sql = "DELETE FROM $table_group WHERE user_id = '".$user_id."'";
  177. api_sql_query($sql,__FILE__,__LINE__);
  178. }
  179. // Unsubscribe user from all classes
  180. $sql = "DELETE FROM $table_class_user WHERE user_id = '".$user_id."'";
  181. api_sql_query($sql,__FILE__,__LINE__);
  182. // Unsubscribe user from all courses
  183. $sql = "DELETE FROM $table_course_user WHERE user_id = '".$user_id."'";
  184. api_sql_query($sql,__FILE__,__LINE__);
  185. // Unsubscribe user from all courses in sessions
  186. $sql = "DELETE FROM $table_session_course_user WHERE id_user = '".$user_id."'";
  187. api_sql_query($sql,__FILE__,__LINE__);
  188. // Unsubscribe user from all sessions
  189. $sql = "DELETE FROM $table_session_user WHERE id_user = '".$user_id."'";
  190. api_sql_query($sql,__FILE__,__LINE__);
  191. // Delete user picture
  192. $user_info = api_get_user_info($user_id);
  193. if(strlen($user_info['picture_uri']) > 0)
  194. {
  195. $img_path = api_get_path(SYS_CODE_PATH).'upload/users/'.$user_info['picture_uri'];
  196. unlink($img_path);
  197. }
  198. // Delete the personal course categories
  199. $course_cat_table = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  200. $sql = "DELETE FROM $course_cat_table WHERE user_id = '".$user_id."'";
  201. api_sql_query($sql,__FILE__,__LINE__);
  202. // Delete user from database
  203. $sql = "DELETE FROM $table_user WHERE user_id = '".$user_id."'";
  204. api_sql_query($sql,__FILE__,__LINE__);
  205. // Delete user from the admin table
  206. $sql = "DELETE FROM $table_admin WHERE user_id = '".$user_id."'";
  207. api_sql_query($sql,__FILE__,__LINE__);
  208. // Delete the personal agenda-items from this user
  209. $agenda_table = Database :: get_user_personal_table(TABLE_PERSONAL_AGENDA);
  210. $sql = "DELETE FROM $agenda_table WHERE user = '".$user_id."'";
  211. api_sql_query($sql,__FILE__,__LINE__);
  212. $gradebook_results_table = Database :: get_main_table(TABLE_MAIN_GRADEBOOK_RESULT);
  213. $sql = 'DELETE FROM '.$gradebook_results_table.' WHERE user_id = '.$user_id;
  214. api_sql_query($sql, __FILE__, __LINE__);
  215. $user = Database::fetch_array($res);
  216. $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  217. $sqlv = "DELETE FROM $t_ufv WHERE user_id = $user_id";
  218. $resv = api_sql_query($sqlv,__FILE__,__LINE__);
  219. return true;
  220. }
  221. /**
  222. * Update user information with new openid
  223. * @param int $user_id
  224. * @param string $openid
  225. * @return boolean true if the user information was updated
  226. */
  227. function update_openid($user_id, $openid)
  228. {
  229. $table_user = Database :: get_main_table(TABLE_MAIN_USER);
  230. $sql = "UPDATE $table_user SET
  231. openid='".Database::escape_string($openid)."'";
  232. $sql .= " WHERE user_id='$user_id'";
  233. return api_sql_query($sql,__FILE__,__LINE__);
  234. }
  235. /**
  236. * Update user information
  237. * @param int $user_id
  238. * @param string $firstname
  239. * @param string $lastname
  240. * @param string $username
  241. * @param string $password
  242. * @param string $auth_source
  243. * @param string $email
  244. * @param int $status
  245. * @param string $official_code
  246. * @param string $phone
  247. * @param string $picture_uri
  248. * @param int The user ID of the person who registered this user (optional, defaults to null)
  249. * @param int The department of HR in which the user is registered (optional, defaults to 0)
  250. * @param array A series of additional fields to add to this user as extra fields (optional, defaults to null)
  251. * @return boolean true if the user information was updated
  252. */
  253. function update_user($user_id, $firstname, $lastname, $username, $password = null, $auth_source = null, $email, $status, $official_code, $phone, $picture_uri, $expiration_date, $active, $creator_id= null, $hr_dept_id=0, $extra=null)
  254. {
  255. global $userPasswordCrypted;
  256. $table_user = Database :: get_main_table(TABLE_MAIN_USER);
  257. $sql = "UPDATE $table_user SET
  258. lastname='".Database::escape_string($lastname)."',
  259. firstname='".Database::escape_string($firstname)."',
  260. username='".Database::escape_string($username)."',";
  261. if(!is_null($password))
  262. {
  263. $password = $userPasswordCrypted ? md5($password) : $password;
  264. $sql .= " password='".Database::escape_string($password)."',";
  265. }
  266. if(!is_null($auth_source))
  267. {
  268. $sql .= " auth_source='".Database::escape_string($auth_source)."',";
  269. }
  270. $sql .= "
  271. email='".Database::escape_string($email)."',
  272. status='".Database::escape_string($status)."',
  273. official_code='".Database::escape_string($official_code)."',
  274. phone='".Database::escape_string($phone)."',
  275. picture_uri='".Database::escape_string($picture_uri)."',
  276. expiration_date='".Database::escape_string($expiration_date)."',
  277. active='".Database::escape_string($active)."',
  278. hr_dept_id=".intval($hr_dept_id);
  279. if(!is_null($creator_id))
  280. {
  281. $sql .= ", creator_id='".Database::escape_string($creator_id)."'";
  282. }
  283. $sql .= " WHERE user_id='$user_id'";
  284. $return = api_sql_query($sql,__FILE__,__LINE__);
  285. if(is_array($extra) and count($extra)>0)
  286. {
  287. $res = true;
  288. foreach($extra as $fname => $fvalue)
  289. {
  290. $res = $res && UserManager::update_extra_field($user_id,$fname,$fvalue);
  291. }
  292. }
  293. return $return;
  294. }
  295. /**
  296. * Check if a username is available
  297. * @param string the wanted username
  298. * @return boolean true if the wanted username is available
  299. */
  300. function is_username_available($username)
  301. {
  302. $table_user = Database :: get_main_table(TABLE_MAIN_USER);
  303. $sql = "SELECT username FROM $table_user WHERE username = '".addslashes($username)."'";
  304. $res = api_sql_query($sql,__FILE__,__LINE__);
  305. return Database::num_rows($res) == 0;
  306. }
  307. /**
  308. * @param array $conditions a list of condition (exemple : status=>STUDENT)
  309. * @param array $order_by a list of fields on which sort
  310. * @return array An array with all users of the platform.
  311. * @todo optional course code parameter, optional sorting parameters...
  312. */
  313. function get_user_list($conditions = array(), $order_by = array())
  314. {
  315. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  316. $return_array = array();
  317. $sql_query = "SELECT * FROM $user_table";
  318. if(count($conditions)>0)
  319. {
  320. $sql_query .= ' WHERE ';
  321. foreach($conditions as $field=>$value)
  322. {
  323. $sql_query .= $field.' = '.$value;
  324. }
  325. }
  326. if(count($order_by)>0)
  327. {
  328. $sql_query .= ' ORDER BY '.implode(',',$order_by);
  329. }
  330. $sql_result = api_sql_query($sql_query,__FILE__,__LINE__);
  331. while ($result = Database::fetch_array($sql_result))
  332. {
  333. $return_array[] = $result;
  334. }
  335. return $return_array;
  336. }
  337. /**
  338. * Get user information
  339. * @param string The username
  340. * @return array All user information as an associative array
  341. */
  342. function get_user_info($username)
  343. {
  344. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  345. $sql = "SELECT * FROM $user_table WHERE username='".$username."'";
  346. $res = api_sql_query($sql,__FILE__,__LINE__);
  347. if(Database::num_rows($res)>0)
  348. {
  349. $user = Database::fetch_array($res);
  350. }
  351. else
  352. {
  353. $user = false;
  354. }
  355. return $user;
  356. }
  357. /**
  358. * Get user information
  359. * @param string The id
  360. * @param boolean Whether to return the user's extra fields (defaults to false)
  361. * @return array All user information as an associative array
  362. */
  363. function get_user_info_by_id($user_id,$user_fields=false)
  364. {
  365. $user_id = intval($user_id);
  366. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  367. $sql = "SELECT * FROM $user_table WHERE user_id=".$user_id;
  368. $res = api_sql_query($sql,__FILE__,__LINE__);
  369. if(Database::num_rows($res)>0)
  370. {
  371. $user = Database::fetch_array($res);
  372. $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  373. $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  374. $sqlf = "SELECT * FROM $t_uf ORDER BY field_order";
  375. $resf = api_sql_query($sqlf,__FILE__,__LINE__);
  376. if(Database::num_rows($resf)>0)
  377. {
  378. while($rowf = Database::fetch_array($resf))
  379. {
  380. $sqlv = "SELECT * FROM $t_ufv WHERE field_id = ".$rowf['id']." AND user_id = ".$user['user_id']." ORDER BY id DESC";
  381. $resv = api_sql_query($sqlv,__FILE__,__LINE__);
  382. if(Database::num_rows($resv)>0)
  383. {
  384. //There should be only one value for a field and a user
  385. $rowv = Database::fetch_array($resv);
  386. $user['extra'][$rowf['field_variable']] = $rowv['field_value'];
  387. }
  388. else
  389. {
  390. $user['extra'][$rowf['field_variable']] = '';
  391. }
  392. }
  393. }
  394. }
  395. else
  396. {
  397. $user = false;
  398. }
  399. return $user;
  400. }
  401. //for survey
  402. function get_teacher_list($course_id, $sel_teacher='')
  403. {
  404. $user_course_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  405. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  406. $sql_query = "SELECT * FROM $user_table a, $user_course_table b where a.user_id=b.user_id AND b.status=1 AND b.course_code='$course_id'";
  407. $sql_result = api_sql_query($sql_query,__FILE__,__LINE__);
  408. echo "<select name=\"author\">";
  409. while ($result = Database::fetch_array($sql_result))
  410. {
  411. if($sel_teacher==$result['user_id']) $selected ="selected";
  412. echo "\n<option value=\"".$result['user_id']."\" $selected>".$result['firstname']."</option>";
  413. }
  414. echo "</select>";
  415. }
  416. /**
  417. * Get user picture URL or path from user ID (returns an array).
  418. * The return format is a complete path, enabling recovery of the directory
  419. * with dirname() or the file with basename(). This also works for the
  420. * functions dealing with the user's productions, as they are located in
  421. * the same directory.
  422. * @param integer User ID
  423. * @param string Type of path to return (can be 'none','system','rel','web')
  424. * @param bool Whether we want to have the directory name returned 'as if' there was a file or not (in the case we want to know which directory to create - otherwise no file means no split subdir)
  425. * @param bool If we want that the function returns the /main/img/unknown.jpg image set it at true
  426. * @return array Array of 2 elements: 'dir' and 'file' which contain the dir and file as the name implies if image does not exist it will return the unknow image if anonymous parameter is true if not it returns an empty array
  427. */
  428. function get_user_picture_path_by_id($id,$type='none',$preview=false,$anonymous=false)
  429. {
  430. if(empty($id) or empty($type))
  431. {
  432. if ($anonymous)
  433. {
  434. $dir='';
  435. switch($type)
  436. {
  437. case 'system': //return the complete path to the file, from root
  438. $dir = api_get_path(SYS_CODE_PATH).'img/';
  439. break;
  440. case 'rel': //return the relative path to the file, from the Dokeos base dir
  441. $dir = api_get_path(REL_CODE_PATH).'img/';
  442. break;
  443. case 'web': //return the complete web URL to the file
  444. $dir = api_get_path(WEB_CODE_PATH).'img/';
  445. break;
  446. case 'none': //return only the picture_uri (as is, without subdir)
  447. default:
  448. break;
  449. }
  450. $file_anonymous='unknown.jpg';
  451. return array('dir'=>$dir,'file'=>$file_anonymous);
  452. }
  453. else
  454. {
  455. return array('dir'=>'','file'=>'');
  456. }
  457. }
  458. $user_id = intval($id);
  459. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  460. $sql = "SELECT picture_uri FROM $user_table WHERE user_id=".$user_id;
  461. $res = api_sql_query($sql,__FILE__,__LINE__);
  462. $user=array();
  463. if(Database::num_rows($res)>0)
  464. {
  465. $user = Database::fetch_array($res);
  466. }
  467. else
  468. {
  469. if ($anonymous)
  470. {
  471. $dir='';
  472. switch($type)
  473. {
  474. case 'system': //return the complete path to the file, from root
  475. $dir = api_get_path(SYS_CODE_PATH).'img/';
  476. break;
  477. case 'rel': //return the relative path to the file, from the Dokeos base dir
  478. $dir = api_get_path(REL_CODE_PATH).'img/';
  479. break;
  480. case 'web': //return the complete web URL to the file
  481. $dir = api_get_path(WEB_CODE_PATH).'img/';
  482. break;
  483. case 'none': //return only the picture_uri (as is, without subdir)
  484. default:
  485. break;
  486. }
  487. $file_anonymous='unknown.jpg';
  488. return array('dir'=>$dir,'file'=>$file_anonymous);
  489. }
  490. else
  491. {
  492. return array('dir'=>'','file'=>'');
  493. }
  494. }
  495. $path = trim($user['picture_uri']);
  496. if (empty($path))
  497. {
  498. if ($anonymous)
  499. {
  500. switch($type)
  501. {
  502. case 'system': //return the complete path to the file, from root
  503. $dir = api_get_path(SYS_CODE_PATH).'img/';
  504. break;
  505. case 'rel': //return the relative path to the file, from the Dokeos base dir
  506. $dir = api_get_path(REL_CODE_PATH).'img/';
  507. break;
  508. case 'web': //return the complete web URL to the file
  509. $dir = api_get_path(WEB_CODE_PATH).'img/';
  510. break;
  511. case 'none': //return only the picture_uri (as is, without subdir)
  512. default:
  513. break;
  514. }
  515. $file_anonymous='unknown.jpg';
  516. return array('dir'=>$dir,'file'=>$file_anonymous);
  517. }
  518. }
  519. $dir = '';
  520. $first = '';
  521. if(api_get_setting('split_users_upload_directory') === 'true')
  522. {
  523. if(!empty($path))
  524. {
  525. $first = substr($path,0,1).'/';
  526. }
  527. elseif($preview==true)
  528. {
  529. $first = substr(''.$user_id,0,1).'/';
  530. }
  531. }
  532. else
  533. {
  534. $first = $user_id.'/';
  535. }
  536. switch($type)
  537. {
  538. case 'system': //return the complete path to the file, from root
  539. $dir = api_get_path(SYS_CODE_PATH).'upload/users/'.$first;
  540. break;
  541. case 'rel': //return the relative path to the file, from the Dokeos base dir
  542. $dir = api_get_path(REL_CODE_PATH).'upload/users/'.$first;
  543. break;
  544. case 'web': //return the complete web URL to the file
  545. $dir = api_get_path(WEB_CODE_PATH).'upload/users/'.$first;
  546. break;
  547. case 'none': //return only the picture_uri (as is, without subdir)
  548. default:
  549. break;
  550. }
  551. return array('dir'=>$dir,'file'=>$path);
  552. }
  553. /*
  554. -----------------------------------------------------------
  555. PRODUCTIONS FUNCTIONS
  556. -----------------------------------------------------------
  557. */
  558. /**
  559. * Returns an XHTML formatted list of productions for a user, or FALSE if he
  560. * doesn't have any.
  561. *
  562. * If there has been a request to remove a production, the function will return
  563. * without building the list unless forced to do so by the optional second
  564. * parameter. This increases performance by avoiding to read through the
  565. * productions on the filesystem before the removal request has been carried
  566. * out because they'll have to be re-read afterwards anyway.
  567. *
  568. * @param $user_id User id
  569. * @param $force Optional parameter to force building after a removal request
  570. * @return A string containing the XHTML code to dipslay the production list, or FALSE
  571. */
  572. function build_production_list($user_id, $force = false, $showdelete=false)
  573. {
  574. if (!$force && !empty($_POST['remove_production']))
  575. return true; // postpone reading from the filesystem
  576. $productions = UserManager::get_user_productions($user_id);
  577. if (empty($productions))
  578. return false;
  579. $production_path = UserManager::get_user_picture_path_by_id($user_id,'web',true);
  580. $production_dir = $production_path['dir'].$user_id.'/';
  581. $del_image = api_get_path(WEB_CODE_PATH).'img/delete.gif';
  582. $del_text = get_lang('Delete');
  583. $production_list = '<ul id="productions">';
  584. foreach ($productions as $file)
  585. {
  586. $production_list .= '<li><a href="'.$production_dir.urlencode($file).'" target="_blank">'.htmlentities($file).'</a>';
  587. if ($showdelete)
  588. {
  589. $production_list .= '<input type="image" name="remove_production['.urlencode($file).']" src="'.$del_image.'" alt="'.$del_text.'" title="'.$del_text.' '.htmlentities($file).'" onclick="return confirmation(\''.htmlentities($file).'\');" /></li>';
  590. }
  591. }
  592. $production_list .= '</ul>';
  593. return $production_list;
  594. }
  595. /**
  596. * Returns an array with the user's productions.
  597. *
  598. * @param $user_id User id
  599. * @return An array containing the user's productions
  600. */
  601. function get_user_productions($user_id)
  602. {
  603. $production_path = UserManager::get_user_picture_path_by_id($user_id,'system',true);
  604. $production_repository = $production_path['dir'].$user_id.'/';
  605. $productions = array();
  606. if (is_dir($production_repository))
  607. {
  608. $handle = opendir($production_repository);
  609. while ($file = readdir($handle))
  610. {
  611. if ($file == '.' || $file == '..' || $file == '.htaccess')
  612. continue; // skip current/parent directory and .htaccess
  613. $productions[] = $file;
  614. }
  615. }
  616. return $productions; // can be an empty array
  617. }
  618. /**
  619. * Remove a user production.
  620. *
  621. * @param $user_id User id
  622. * @param $production The production to remove
  623. */
  624. function remove_user_production($user_id, $production)
  625. {
  626. $production_path = UserManager::get_user_picture_path_by_id($user_id,'system',true);
  627. unlink($production_path['dir'].$user_id.'/'.$production);
  628. }
  629. /**
  630. * Update an extra field. This function is called when a user changes his/her profile
  631. * and by consequence fills or edits his/her extra fields.
  632. *
  633. * @param integer Field ID
  634. * @param array Database columns and their new value
  635. * @return boolean true if field updated, false otherwise
  636. */
  637. function update_extra_field($fid,$columns)
  638. {
  639. //TODO check that values added are values proposed for enumerated field types
  640. $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  641. $fid = Database::escape_string($fid);
  642. $sqluf = "UPDATE $t_uf SET ";
  643. $known_fields = array('id','field_variable','field_type','field_display_text','field_default_value','field_order','field_visible','field_changeable');
  644. $safecolumns = array();
  645. foreach($columns as $index => $newval)
  646. {
  647. if(in_array($index,$known_fields))
  648. {
  649. $safecolumns[$index] = Database::escape_string($newval);
  650. $sqluf .= $index." = '".$safecolumns[$index]."', ";
  651. }
  652. }
  653. $time = time();
  654. $sqluf .= " tms = FROM_UNIXTIME($time) WHERE id='$fid'";
  655. $resuf = api_sql_query($sqluf,__FILE__,__LINE__);
  656. return $resuf;
  657. }
  658. /**
  659. * Update an extra field value for a given user
  660. * @param integer User ID
  661. * @param string Field variable name
  662. * @param string Field value
  663. * @return boolean true if field updated, false otherwise
  664. */
  665. function update_extra_field_value($user_id,$fname,$fvalue='')
  666. {
  667. //TODO check that values added are values proposed for enumerated field types
  668. $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  669. $t_ufo = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  670. $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  671. $fname = Database::escape_string($fname);
  672. $fvalues = '';
  673. if(is_array($fvalue))
  674. {
  675. foreach($fvalue as $val)
  676. {
  677. $fvalues .= Database::escape_string($val).';';
  678. }
  679. if(!empty($fvalues))
  680. {
  681. $fvalues = substr($fvalues,0,-1);
  682. }
  683. }
  684. else
  685. {
  686. $fvalues = Database::escape_string($fvalue);
  687. }
  688. $sqluf = "SELECT * FROM $t_uf WHERE field_variable='$fname'";
  689. $resuf = api_sql_query($sqluf,__FILE__,__LINE__);
  690. if(Database::num_rows($resuf)==1)
  691. { //ok, the field exists
  692. // Check if enumerated field, if the option is available
  693. $rowuf = Database::fetch_array($resuf);
  694. switch($rowuf['field_type'])
  695. {
  696. case 3:
  697. case 4:
  698. case 5:
  699. $sqluo = "SELECT * FROM $t_ufo WHERE field_id = ".$rowuf['id'];
  700. $resuo = api_sql_query($sqluo,__FILE__,__LINE__);
  701. $values = split(';',$fvalues);
  702. if(Database::num_rows($resuo)>0)
  703. {
  704. $check = false;
  705. while($rowuo = Database::fetch_array($resuo))
  706. {
  707. if(in_array($rowuo['option_value'],$values))
  708. {
  709. $check = true;
  710. break;
  711. }
  712. }
  713. if($check == false)
  714. {
  715. return false; //option value not found
  716. }
  717. }
  718. else
  719. {
  720. return false; //enumerated type but no option found
  721. }
  722. break;
  723. case 1:
  724. case 2:
  725. default:
  726. break;
  727. }
  728. $tms = time();
  729. $sqlufv = "SELECT * FROM $t_ufv WHERE user_id = $user_id AND field_id = ".$rowuf['id']." ORDER BY id";
  730. $resufv = api_sql_query($sqlufv,__FILE__,__LINE__);
  731. $n = Database::num_rows($resufv);
  732. if($n>1)
  733. {
  734. //problem, we already have to values for this field and user combination - keep last one
  735. while($rowufv = Database::fetch_array($resufv))
  736. {
  737. if($n > 1)
  738. {
  739. $sqld = "DELETE FROM $t_ufv WHERE id = ".$rowufv['id'];
  740. $resd = api_sql_query($sqld,__FILE__,__LINE__);
  741. $n--;
  742. }
  743. $rowufv = Database::fetch_array($resufv);
  744. if($rowufv['field_value'] != $fvalues)
  745. {
  746. $sqlu = "UPDATE $t_ufv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms) WHERE id = ".$rowufv['id'];
  747. $resu = api_sql_query($sqlu,__FILE__,__LINE__);
  748. return($resu?true:false);
  749. }
  750. return true;
  751. }
  752. }
  753. elseif($n==1)
  754. {
  755. //we need to update the current record
  756. $rowufv = Database::fetch_array($resufv);
  757. if($rowufv['field_value'] != $fvalues)
  758. {
  759. $sqlu = "UPDATE $t_ufv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms) WHERE id = ".$rowufv['id'];
  760. //error_log('UM::update_extra_field_value: '.$sqlu);
  761. $resu = api_sql_query($sqlu,__FILE__,__LINE__);
  762. return($resu?true:false);
  763. }
  764. return true;
  765. }
  766. else
  767. {
  768. $sqli = "INSERT INTO $t_ufv (user_id,field_id,field_value,tms) " .
  769. "VALUES ($user_id,".$rowuf['id'].",'$fvalues',FROM_UNIXTIME($tms))";
  770. //error_log('UM::update_extra_field_value: '.$sqli);
  771. $resi = api_sql_query($sqli,__FILE__,__LINE__);
  772. return($resi?true:false);
  773. }
  774. }
  775. else
  776. {
  777. return false; //field not found
  778. }
  779. }
  780. /**
  781. * Get an array of extra fieds with field details (type, default value and options)
  782. * @param integer Offset (from which row)
  783. * @param integer Number of items
  784. * @param integer Column on which sorting is made
  785. * @param string Sorting direction
  786. * @param boolean Optional. Whether we get all the fields or just the visible ones
  787. * @return array Extra fields details (e.g. $list[2]['type'], $list[4]['options'][2]['title']
  788. */
  789. function get_extra_fields($from=0, $number_of_items=0, $column=5, $direction='ASC', $all_visibility=true)
  790. {
  791. $fields = array();
  792. $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
  793. $t_ufo = Database :: get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  794. $columns = array('id','field_variable','field_type','field_display_text','field_default_value','field_order','tms');
  795. $sort_direction = '';
  796. if(in_array(strtoupper($direction),array('ASC','DESC')))
  797. {
  798. $sort_direction = strtoupper($direction);
  799. }
  800. $sqlf = "SELECT * FROM $t_uf ";
  801. if($all_visibility==false)
  802. {
  803. $sqlf .= " WHERE field_visible = 1 ";
  804. }
  805. $sqlf .= " ORDER BY ".$columns[$column]." $sort_direction " ;
  806. if($number_of_items != 0)
  807. {
  808. $sqlf .= " LIMIT ".Database::escape_string($from).','.Database::escape_string($number_of_items);
  809. }
  810. $resf = api_sql_query($sqlf,__FILE__,__LINE__);
  811. if(Database::num_rows($resf)>0)
  812. {
  813. while($rowf = Database::fetch_array($resf))
  814. {
  815. $fields[$rowf['id']] = array(
  816. 0=>$rowf['id'],
  817. 1=>$rowf['field_variable'],
  818. 2=>$rowf['field_type'],
  819. //3=>(empty($rowf['field_display_text'])?'':get_lang($rowf['field_display_text'],'')),
  820. //temporarily removed auto-translation. Need update to get_lang() to know if translation exists (todo)
  821. 3=>(empty($rowf['field_display_text'])?'':$rowf['field_display_text']),
  822. 4=>$rowf['field_default_value'],
  823. 5=>$rowf['field_order'],
  824. 6=>$rowf['field_visible'],
  825. 7=>$rowf['field_changeable'],
  826. 8=>array()
  827. );
  828. $sqlo = "SELECT * FROM $t_ufo WHERE field_id = ".$rowf['id']." ORDER BY option_order ASC";
  829. $reso = api_sql_query($sqlo,__FILE__,__LINE__);
  830. if(Database::num_rows($reso)>0)
  831. {
  832. while($rowo = Database::fetch_array($reso))
  833. {
  834. $fields[$rowf['id']][8][$rowo['id']] = array(
  835. 0=>$rowo['id'],
  836. 1=>$rowo['option_value'],
  837. //2=>(empty($rowo['option_display_text'])?'':get_lang($rowo['option_display_text'],'')),
  838. 2=>(empty($rowo['option_display_text'])?'':$rowo['option_display_text']),
  839. 3=>$rowo['option_order']
  840. );
  841. }
  842. }
  843. }
  844. }
  845. return $fields;
  846. }
  847. /**
  848. * Get the list of options attached to an extra field
  849. * @param string $fieldname the name of the field
  850. * @return array the list of options
  851. */
  852. function get_extra_field_options($field_name)
  853. {
  854. $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
  855. $t_ufo = Database :: get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  856. $sql = 'SELECT options.*
  857. FROM '.$t_ufo.' options
  858. INNER JOIN '.$t_uf.' fields
  859. ON fields.id = options.field_id
  860. AND fields.field_variable="'.Database::escape_string($field_name).'"';
  861. $rs = api_sql_query($sql, __FILE__, __LINE__);
  862. return api_store_result($rs);
  863. }
  864. /**
  865. * Get the number of extra fields currently recorded
  866. * @param boolean Optional switch. true (default) returns all fields, false returns only visible fields
  867. * @return integer Number of fields
  868. */
  869. function get_number_of_extra_fields($all_visibility=true)
  870. {
  871. $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
  872. $sqlf = "SELECT * FROM $t_uf ";
  873. if($all_visibility == false)
  874. {
  875. $sqlf .= " WHERE field_visible = 1 ";
  876. }
  877. $sqlf .= " ORDER BY field_order";
  878. $resf = api_sql_query($sqlf,__FILE__,__LINE__);
  879. return Database::num_rows($resf);
  880. }
  881. /**
  882. * Creates a new extra field
  883. * @param string Field's internal variable name
  884. * @param int Field's type
  885. * @param string Field's language var name
  886. * @param string Field's default value
  887. * @param string Optional comma-separated list of options to provide for select and radio
  888. * @return int new user id - if the new user creation succeeds, false otherwise
  889. */
  890. function create_extra_field($fieldvarname, $fieldtype, $fieldtitle, $fielddefault, $fieldoptions='')
  891. {
  892. // database table definition
  893. $table_field = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  894. $table_field_options= Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  895. // First check wether the login already exists
  896. if (! UserManager::is_extra_field_available($fieldvarname))
  897. return api_set_failure('login-pass already taken');
  898. $sql = "SELECT MAX(field_order) FROM $table_field";
  899. $res = api_sql_query($sql,__FILE__,__LINE__);
  900. $order = 0;
  901. if(Database::num_rows($res)>0)
  902. {
  903. $row = Database::fetch_array($res);
  904. $order = $row[0]+1;
  905. }
  906. $time = time();
  907. $sql = "INSERT INTO $table_field
  908. SET field_type = '".Database::escape_string($fieldtype)."',
  909. field_variable = '".Database::escape_string($fieldvarname)."',
  910. field_display_text = '".Database::escape_string($fieldtitle)."',
  911. field_default_value = '".Database::escape_string($fielddefault)."',
  912. field_order = '$order',
  913. tms = FROM_UNIXTIME($time)";
  914. $result = api_sql_query($sql);
  915. if ($result)
  916. {
  917. //echo "id returned";
  918. $return=Database::get_last_insert_id();
  919. }
  920. else
  921. {
  922. //echo "false - failed" ;
  923. return false;
  924. }
  925. if(!empty($fieldoptions) && in_array($fieldtype,array(USER_FIELD_TYPE_RADIO,USER_FIELD_TYPE_SELECT,USER_FIELD_TYPE_SELECT_MULTIPLE,USER_FIELD_TYPE_DOUBLE_SELECT)))
  926. {
  927. if($fieldtype == USER_FIELD_TYPE_DOUBLE_SELECT)
  928. {
  929. $twolist = explode('|', $fieldoptions);
  930. $counter = 0;
  931. foreach ($twolist as $individual_list)
  932. {
  933. $splitted_individual_list = split(';',$individual_list);
  934. foreach ($splitted_individual_list as $individual_list_option)
  935. {
  936. //echo 'counter:'.$counter;
  937. if ($counter == 0)
  938. {
  939. $list[] = $individual_list_option;
  940. }
  941. else
  942. {
  943. $list[] = str_repeat('*',$counter).$individual_list_option;
  944. }
  945. }
  946. $counter++;
  947. }
  948. }
  949. else
  950. {
  951. $list = split(';',$fieldoptions);
  952. }
  953. foreach($list as $option)
  954. {
  955. $option = Database::escape_string($option);
  956. $sql = "SELECT * FROM $table_field_options WHERE field_id = $return AND option_value = '".$option."'";
  957. $res = api_sql_query($sql,__FILE__,__LINE__);
  958. if(Database::num_rows($res)>0)
  959. {
  960. //the option already exists, do nothing
  961. }
  962. else
  963. {
  964. $sql = "SELECT MAX(option_order) FROM $table_field_options WHERE field_id = $return";
  965. $res = api_sql_query($sql,__FILE__,__LINE__);
  966. $max = 1;
  967. if(Database::num_rows($res)>0)
  968. {
  969. $row = Database::fetch_array($res);
  970. $max = $row[0]+1;
  971. }
  972. $time = time();
  973. $sql = "INSERT INTO $table_field_options (field_id,option_value,option_display_text,option_order,tms) VALUES ($return,'$option','$option',$max,FROM_UNIXTIME($time))";
  974. $res = api_sql_query($sql,__FILE__,__LINE__);
  975. if($res === false)
  976. {
  977. $return = false;
  978. }
  979. }
  980. }
  981. }
  982. return $return;
  983. }
  984. /**
  985. * Save the changes in the definition of the extra user profile field
  986. * The function is called after you (as admin) decide to store the changes you have made to one of the fields you defined
  987. *
  988. * There is quite some logic in this field
  989. * 1. store the changes to the field (tupe, name, label, default text)
  990. * 2. remove the options and the choices of the users from the database that no longer occur in the form field 'possible values'. We should only remove
  991. * the options (and choices) that do no longer have to appear. We cannot remove all options and choices because if you remove them all
  992. * and simply re-add them all then all the user who have already filled this form will loose their selected value.
  993. * 3. we add the options that are newly added
  994. *
  995. * @example current options are a;b;c and the user changes this to a;b;x (removing c and adding x)
  996. * we first remove c (and also the entry in the option_value table for the users who have chosen this)
  997. * we then add x
  998. * a and b are neither removed nor added
  999. *
  1000. * @param integer $fieldid the id of the field we are editing
  1001. * @param string $fieldvarname the internal variable name of the field
  1002. * @param int $fieldtype the type of the field
  1003. * @param string $fieldtitle the title of the field
  1004. * @param string $fielddefault the default value of the field
  1005. * @param string $fieldoptions Optional comma-separated list of options to provide for select and radio
  1006. * @return boolean true
  1007. *
  1008. *
  1009. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1010. * @version July 2008
  1011. * @since Dokeos 1.8.6
  1012. */
  1013. function save_extra_field_changes($fieldid, $fieldvarname, $fieldtype, $fieldtitle, $fielddefault, $fieldoptions='')
  1014. {
  1015. // database table definition
  1016. $table_field = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1017. $table_field_options = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  1018. $table_field_options_values = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  1019. // we first update the field definition with the new values
  1020. $time = time();
  1021. $sql = "UPDATE $table_field
  1022. SET field_type = '".Database::escape_string($fieldtype)."',
  1023. field_variable = '".Database::escape_string($fieldvarname)."',
  1024. field_display_text = '".Database::escape_string($fieldtitle)."',
  1025. field_default_value = '".Database::escape_string($fielddefault)."',
  1026. tms = FROM_UNIXTIME($time)
  1027. WHERE field_id = '".Database::escape_string($fieldid)."'";
  1028. //$result = api_sql_query($sql,__FILE__,__LINE__);
  1029. // we create an array with all the options (will be used later in the script)
  1030. if($fieldtype == USER_FIELD_TYPE_DOUBLE_SELECT)
  1031. {
  1032. $twolist = explode('|', $fieldoptions);
  1033. $counter = 0;
  1034. foreach ($twolist as $individual_list)
  1035. {
  1036. $splitted_individual_list = split(';',$individual_list);
  1037. foreach ($splitted_individual_list as $individual_list_option)
  1038. {
  1039. //echo 'counter:'.$counter;
  1040. if ($counter == 0)
  1041. {
  1042. $list[] = trim($individual_list_option);
  1043. }
  1044. else
  1045. {
  1046. $list[] = str_repeat('*',$counter).trim($individual_list_option);
  1047. }
  1048. }
  1049. $counter++;
  1050. }
  1051. }
  1052. else
  1053. {
  1054. $templist = split(';',$fieldoptions);
  1055. $list = array_map('trim', $templist);
  1056. }
  1057. // Remove all the field options (and also the choices of the user) that are NOT in the new list of options
  1058. $sql = "SELECT * FROM $table_field_options WHERE option_value NOT IN ('".implode("','", $list)."') AND field_id = '".Database::escape_string($fieldid)."'";
  1059. $result = api_sql_query($sql,__FILE__,__LINE__);
  1060. while ($row = Database::fetch_array($result))
  1061. {
  1062. // deleting the option
  1063. $sql_delete_option = "DELETE FROM $table_field_options WHERE id='".Database::escape_string($row['id'])."'";
  1064. $result_delete_option = api_sql_query($sql_delete_option,__FILE__,__LINE__);
  1065. $return['deleted_options']++;
  1066. // deleting the answer of the user who has chosen this option
  1067. $sql_delete_option_value = "DELETE FROM $table_field_options_values WHERE field_id = '".Database::escape_string($fieldid)."' AND field_value = '".Database::escape_string($row['option_value'])."'";
  1068. $result_delete_option_value = api_sql_query($sql_delete_option_value,__FILE__,__LINE__);
  1069. $return['deleted_option_values'] = $return['deleted_option_values'] + Database::affected_rows();
  1070. }
  1071. // we now try to find the field options that are newly added
  1072. $sql = "SELECT * FROM $table_field_options WHERE field_id = '".Database::escape_string($fieldid)."'";
  1073. $result = api_sql_query($sql,__FILE__,__LINE__);
  1074. while ($row = Database::fetch_array($result))
  1075. {
  1076. // we remove every option that is already in the database from the $list
  1077. if (in_array(trim($row['option_display_text']),$list))
  1078. {
  1079. $key = array_search(trim($row['option_display_text']),$list);
  1080. unset($list[$key]);
  1081. echo 'unset '.$key.$list[$key];
  1082. }
  1083. }
  1084. // we store the new field options in the database
  1085. foreach ($list as $key=>$option)
  1086. {
  1087. $sql = "SELECT MAX(option_order) FROM $table_field_options WHERE field_id = '".Database::escape_string($fieldid)."'";
  1088. $res = api_sql_query($sql,__FILE__,__LINE__);
  1089. $max = 1;
  1090. if(Database::num_rows($res)>0)
  1091. {
  1092. $row = Database::fetch_array($res);
  1093. $max = $row[0]+1;
  1094. }
  1095. $time = time();
  1096. $sql = "INSERT INTO $table_field_options (field_id,option_value,option_display_text,option_order,tms) VALUES ('".Database::escape_string($fieldid)."','".Database::escape_string($option)."','".Database::escape_string($option)."',$max,FROM_UNIXTIME($time))";
  1097. $result = api_sql_query($sql,__FILE__,__LINE__);
  1098. }
  1099. return true;
  1100. }
  1101. /**
  1102. * Check if a field is available
  1103. * @param string the wanted username
  1104. * @return boolean true if the wanted username is available
  1105. */
  1106. function is_extra_field_available($fieldname)
  1107. {
  1108. $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
  1109. $sql = "SELECT * FROM $t_uf WHERE field_variable = '".Database::escape_string($fieldname)."'";
  1110. $res = api_sql_query($sql,__FILE__,__LINE__);
  1111. return Database::num_rows($res) <= 0;
  1112. }
  1113. /**
  1114. * Gets user extra fields data
  1115. * @param integer User ID
  1116. * @param boolean Whether to prefix the fields indexes with "extra_" (might be used by formvalidator)
  1117. * @param boolean Whether to return invisible fields as well
  1118. * @param boolean Whether to split multiple-selection fields or not
  1119. * @return array Array of fields => value for the given user
  1120. */
  1121. function get_extra_user_data($user_id, $prefix=false, $all_visibility = true, $splitmultiple=false)
  1122. {
  1123. $extra_data = array();
  1124. $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1125. $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  1126. $user_id = Database::escape_string($user_id);
  1127. $sql = "SELECT f.id as id, f.field_variable as fvar, f.field_type as type FROM $t_uf f ";
  1128. if($all_visibility == false)
  1129. {
  1130. $sql .= " WHERE f.field_visible = 1 ";
  1131. }
  1132. $sql .= " ORDER BY f.field_order";
  1133. $res = api_sql_query($sql,__FILE__,__LINE__);
  1134. if(Database::num_rows($res)>0)
  1135. {
  1136. while($row = Database::fetch_array($res))
  1137. {
  1138. $sqlu = "SELECT field_value as fval " .
  1139. " FROM $t_ufv " .
  1140. " WHERE field_id=".$row['id']."" .
  1141. " AND user_id=".$user_id;
  1142. $resu = api_sql_query($sqlu,__FILE__,__LINE__);
  1143. $fval = '';
  1144. if(Database::num_rows($resu)>0)
  1145. {
  1146. $rowu = Database::fetch_array($resu);
  1147. $fval = $rowu['fval'];
  1148. if($row['type'] == USER_FIELD_TYPE_SELECT_MULTIPLE)
  1149. {
  1150. $fval = split(';',$rowu['fval']);
  1151. }
  1152. }
  1153. if($prefix)
  1154. {
  1155. $extra_data['extra_'.$row['fvar']] = $fval;
  1156. }
  1157. else
  1158. {
  1159. $extra_data[$row['fvar']] = $fval;
  1160. }
  1161. }
  1162. }
  1163. return $extra_data;
  1164. }
  1165. /**
  1166. * Get all the extra field information of a certain field (also the options)
  1167. *
  1168. * @param integer $field_id the id of the field we want to know everything of
  1169. * @return array $return containing all th information about the extra profile field
  1170. *
  1171. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1172. * @version July 2008
  1173. * @since Dokeos 1.8.6
  1174. */
  1175. function get_extra_field_information($field_id)
  1176. {
  1177. // database table definition
  1178. $table_field = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1179. $table_field_options = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  1180. // all the information of the field
  1181. $sql = "SELECT * FROM $table_field WHERE id='".Database::escape_string($field_id)."'";
  1182. $result = api_sql_query($sql,__FILE__,__LINE__);
  1183. $return = Database::fetch_array($result);
  1184. // all the options of the field
  1185. $sql = "SELECT * FROM $table_field_options WHERE field_id='".Database::escape_string($field_id)."' ORDER BY option_order ASC";
  1186. $result = api_sql_query($sql,__FILE__,__LINE__);
  1187. while ($row = Database::fetch_array($result))
  1188. {
  1189. $return['options'][$row['id']] = $row;
  1190. }
  1191. return $return;
  1192. }
  1193. /**
  1194. * Gives a list of [session_id-course_code] => [status] for the current user.
  1195. * @param integer $user_id
  1196. * @return array list of statuses (session_id-course_code => status)
  1197. */
  1198. function get_personal_session_course_list($user_id)
  1199. {
  1200. // Database Table Definitions
  1201. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1202. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  1203. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  1204. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  1205. $tbl_session_user = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
  1206. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1207. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  1208. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1209. // variable initialisation
  1210. $personal_course_list_sql = '';
  1211. $personal_course_list = array();
  1212. //Courses in which we suscribed out of any session
  1213. /*$personal_course_list_sql = "SELECT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i,
  1214. course.tutor_name t, course.course_language l, course_rel_user.status s, course_rel_user.sort sort,
  1215. course_rel_user.user_course_cat user_course_cat
  1216. FROM ".$tbl_course." course,".$main_course_user_table." course_rel_user
  1217. WHERE course.code = course_rel_user.course_code"."
  1218. AND course_rel_user.user_id = '".$user_id."'
  1219. ORDER BY course_rel_user.user_course_cat, course_rel_user.sort ASC,i";*/
  1220. $tbl_user_course_category = Database :: get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  1221. $personal_course_list_sql = "SELECT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, course.tutor_name t, course.course_language l, course_rel_user.status s, course_rel_user.sort sort, course_rel_user.user_course_cat user_course_cat
  1222. FROM ".$tbl_course_user." course_rel_user
  1223. LEFT JOIN ".$tbl_course." course
  1224. ON course.code = course_rel_user.course_code
  1225. LEFT JOIN ".$tbl_user_course_category." user_course_category
  1226. ON course_rel_user.user_course_cat = user_course_category.id
  1227. WHERE course_rel_user.user_id = '".$user_id."'
  1228. ORDER BY user_course_category.sort, course_rel_user.sort ASC, i";
  1229. $course_list_sql_result = api_sql_query($personal_course_list_sql, __FILE__, __LINE__);
  1230. while ($result_row = Database::fetch_array($course_list_sql_result))
  1231. {
  1232. $personal_course_list[] = $result_row;
  1233. }
  1234. // get the list of sessions where the user is subscribed as student
  1235. $sessions_sql = "SELECT DISTINCT id, name, date_start, date_end
  1236. FROM $tbl_session_user, $tbl_session
  1237. WHERE id_session=id AND id_user=$user_id
  1238. AND (date_start <= NOW() AND date_end >= NOW() OR date_start='0000-00-00')
  1239. ORDER BY date_start, date_end, name";
  1240. $result = api_sql_query($sessions_sql,__FILE__,__LINE__);
  1241. $sessions=api_store_result($result);
  1242. $sessions = array_merge($sessions , api_store_result($result));
  1243. // get the list of sessions where the user is subscribed as coach in a course
  1244. $sessions_sql = "SELECT DISTINCT id, name, date_start, date_end
  1245. FROM $tbl_session as session
  1246. INNER JOIN $tbl_session_course as session_rel_course
  1247. ON session_rel_course.id_coach = $user_id
  1248. AND (date_start <= NOW() AND date_end >= NOW() OR date_start='0000-00-00')
  1249. ORDER BY date_start, date_end, name";
  1250. $result = api_sql_query($sessions_sql,__FILE__,__LINE__);
  1251. $session_is_coach = api_store_result($result);
  1252. $sessions = array_merge($sessions , $session_is_coach);
  1253. // get the list of sessions where the user is subscribed as coach
  1254. $sessions_sql = "SELECT DISTINCT id, name, date_start, date_end
  1255. FROM $tbl_session as session
  1256. WHERE session.id_coach = $user_id
  1257. AND (date_start <= NOW() AND date_end >= NOW() OR date_start='0000-00-00')
  1258. ORDER BY date_start, date_end, name";
  1259. $result = api_sql_query($sessions_sql,__FILE__,__LINE__);
  1260. $sessions = array_merge($sessions , api_store_result($result));
  1261. if(api_is_allowed_to_create_course())
  1262. {
  1263. foreach($sessions as $enreg)
  1264. {
  1265. $id_session = $enreg['id'];
  1266. $personal_course_list_sql = "SELECT DISTINCT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, CONCAT(user.lastname,' ',user.firstname) t, email, course.course_language l, 1 sort, category_code user_course_cat, date_start, date_end, session.id as id_session, session.name as session_name
  1267. FROM $tbl_session_course as session_course
  1268. INNER JOIN $tbl_course AS course
  1269. ON course.code = session_course.course_code
  1270. INNER JOIN $tbl_session as session
  1271. ON session.id = session_course.id_session
  1272. LEFT JOIN $tbl_user as user
  1273. ON user.user_id = session_course.id_coach
  1274. WHERE session_course.id_session = $id_session
  1275. AND (session_course.id_coach=$user_id OR session.id_coach=$user_id)
  1276. ORDER BY i";
  1277. $course_list_sql_result = api_sql_query($personal_course_list_sql, __FILE__, __LINE__);
  1278. while ($result_row = Database::fetch_array($course_list_sql_result))
  1279. {
  1280. $result_row['s'] = 2;
  1281. $key = $result_row['id_session'].' - '.$result_row['k'];
  1282. $personal_course_list[$key] = $result_row;
  1283. }
  1284. }
  1285. }
  1286. foreach($sessions as $enreg)
  1287. {
  1288. $id_session = $enreg['id'];
  1289. $personal_course_list_sql = "SELECT DISTINCT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, CONCAT(user.lastname,' ',user.firstname) t, email, course.course_language l, 1 sort, category_code user_course_cat, date_start, date_end, session.id as id_session, session.name as session_name, IF(session_course.id_coach = ".$user_id.",'2', '5')
  1290. FROM $tbl_session_course as session_course
  1291. INNER JOIN $tbl_course AS course
  1292. ON course.code = session_course.course_code
  1293. LEFT JOIN $tbl_user as user
  1294. ON user.user_id = session_course.id_coach
  1295. INNER JOIN $tbl_session_course_user
  1296. ON $tbl_session_course_user.id_session = $id_session
  1297. AND $tbl_session_course_user.id_user = $user_id
  1298. INNER JOIN $tbl_session as session
  1299. ON session_course.id_session = session.id
  1300. WHERE session_course.id_session = $id_session
  1301. ORDER BY i";
  1302. $course_list_sql_result = api_sql_query($personal_course_list_sql, __FILE__, __LINE__);
  1303. while ($result_row = Database::fetch_array($course_list_sql_result))
  1304. {
  1305. $key = $result_row['id_session'].' - '.$result_row['k'];
  1306. $result_row['s'] = $result_row['14'];
  1307. if(!isset($personal_course_list[$key]))
  1308. {
  1309. $personal_course_list[$key] = $result_row;
  1310. }
  1311. }
  1312. }
  1313. //print_r($personal_course_list);
  1314. return $personal_course_list;
  1315. }
  1316. }
  1317. ?>