usermanager.lib.php 74 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962
  1. <?php // $Id: usermanager.lib.php 20641 2009-05-14 16:13:21Z iflorespaz $
  2. /*
  3. ==============================================================================
  4. Dokeos - elearning and course management software
  5. Copyright (c) 2004-2009 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 mixed new user id - if the new user creation succeeds, false otherwise
  61. *
  62. * @desc The function tries to retrieve $_user['user_id'] from the global space.
  63. * if it exists, $_user['user_id'] is the creator id If a problem arises,
  64. * it stores the error message in global $api_failureList
  65. */
  66. 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)
  67. {
  68. global $_user, $userPasswordCrypted;
  69. $firstName=Security::remove_XSS($firstName);
  70. $lastName=Security::remove_XSS($lastName);
  71. $loginName=Security::remove_XSS($loginName);
  72. $phone=Security::remove_XSS($phone);
  73. // database table definition
  74. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  75. // default langauge
  76. if ($language=='')
  77. {
  78. $language = api_get_setting('platformLanguage');
  79. }
  80. if ($_user['user_id'])
  81. {
  82. $creator_id = $_user['user_id'];
  83. }
  84. else
  85. {
  86. $creator_id = '';
  87. }
  88. // First check wether the login already exists
  89. if (! UserManager::is_username_available($loginName))
  90. return api_set_failure('login-pass already taken');
  91. //$password = "PLACEHOLDER";
  92. $password = api_get_encrypted_password($password);
  93. //$password = ($userPasswordCrypted ? md5($password) : $password);
  94. $current_date=date('Y-m-d H:i:s',time());
  95. $sql = "INSERT INTO $table_user
  96. SET lastname = '".Database::escape_string(trim($lastName))."',
  97. firstname = '".Database::escape_string(trim($firstName))."',
  98. username = '".Database::escape_string(trim($loginName))."',
  99. status = '".Database::escape_string($status)."',
  100. password = '".Database::escape_string($password)."',
  101. email = '".Database::escape_string($email)."',
  102. official_code = '".Database::escape_string($official_code)."',
  103. picture_uri = '".Database::escape_string($picture_uri)."',
  104. creator_id = '".Database::escape_string($creator_id)."',
  105. auth_source = '".Database::escape_string($auth_source)."',
  106. phone = '".Database::escape_string($phone)."',
  107. language = '".Database::escape_string($language)."',
  108. registration_date = '".$current_date."',
  109. expiration_date = '".Database::escape_string($expiration_date)."',
  110. hr_dept_id = '".Database::escape_string($hr_dept_id)."',
  111. active = '".Database::escape_string($active)."'";
  112. $result = api_sql_query($sql, __FILE__, __LINE__);
  113. if ($result) {
  114. //echo "id returned";
  115. $return=Database::get_last_insert_id();
  116. global $_configuration;
  117. require_once (api_get_path(LIBRARY_PATH).'urlmanager.lib.php');
  118. if ($_configuration['multiple_access_urls']==true) {
  119. if (api_get_current_access_url_id()!=-1)
  120. UrlManager::add_user_to_url($return, api_get_current_access_url_id());
  121. else
  122. UrlManager::add_user_to_url($return, 1);
  123. } else {
  124. //we are adding by default the access_url_user table with access_url_id = 1
  125. UrlManager::add_user_to_url($return, 1);
  126. }
  127. } else {
  128. //echo "false - failed" ;
  129. $return=false;
  130. }
  131. if(is_array($extra) AND count($extra)>0) {
  132. $res = true;
  133. foreach($extra as $fname => $fvalue) {
  134. $res = $res && UserManager::update_extra_field($return,$fname,$fvalue);
  135. }
  136. }
  137. return $return;
  138. }
  139. /**
  140. * Can user be deleted?
  141. * This functions checks if there's a course in which the given user is the
  142. * only course administrator. If that is the case, the user can't be
  143. * deleted because the course would remain without a course admin.
  144. * @param int $user_id The user id
  145. * @return boolean true if user can be deleted
  146. */
  147. function can_delete_user($user_id)
  148. {
  149. $table_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  150. $sql = "SELECT * FROM $table_course_user WHERE status = '1' AND user_id = '".$user_id."'";
  151. $res = api_sql_query($sql,__FILE__,__LINE__);
  152. while ($course = Database::fetch_object($res))
  153. {
  154. $sql = "SELECT user_id FROM $table_course_user WHERE status='1' AND course_code ='".$course->course_code."'";
  155. $res2 = api_sql_query($sql,__FILE__,__LINE__);
  156. if (Database::num_rows($res2) == 1)
  157. {
  158. return false;
  159. }
  160. }
  161. return true;
  162. }
  163. /**
  164. * Delete a user from the platform
  165. * @param int $user_id The user id
  166. * @return boolean true if user is succesfully deleted, false otherwise
  167. */
  168. function delete_user($user_id)
  169. {
  170. if (!UserManager :: can_delete_user($user_id))
  171. {
  172. return false;
  173. }
  174. $table_user = Database :: get_main_table(TABLE_MAIN_USER);
  175. $table_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  176. $table_class_user = Database :: get_main_table(TABLE_MAIN_CLASS_USER);
  177. $table_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  178. $table_admin = Database :: get_main_table(TABLE_MAIN_ADMIN);
  179. $table_session_user = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
  180. $table_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  181. // Unsubscribe the user from all groups in all his courses
  182. $sql = "SELECT * FROM $table_course c, $table_course_user cu WHERE cu.user_id = '".$user_id."' AND c.code = cu.course_code";
  183. $res = api_sql_query($sql,__FILE__,__LINE__);
  184. while ($course = Database::fetch_object($res))
  185. {
  186. $table_group = Database :: get_course_table(TABLE_GROUP_USER, $course->db_name);
  187. $sql = "DELETE FROM $table_group WHERE user_id = '".$user_id."'";
  188. api_sql_query($sql,__FILE__,__LINE__);
  189. }
  190. // Unsubscribe user from all classes
  191. $sql = "DELETE FROM $table_class_user WHERE user_id = '".$user_id."'";
  192. api_sql_query($sql,__FILE__,__LINE__);
  193. // Unsubscribe user from all courses
  194. $sql = "DELETE FROM $table_course_user WHERE user_id = '".$user_id."'";
  195. api_sql_query($sql,__FILE__,__LINE__);
  196. // Unsubscribe user from all courses in sessions
  197. $sql = "DELETE FROM $table_session_course_user WHERE id_user = '".$user_id."'";
  198. api_sql_query($sql,__FILE__,__LINE__);
  199. // Unsubscribe user from all sessions
  200. $sql = "DELETE FROM $table_session_user WHERE id_user = '".$user_id."'";
  201. api_sql_query($sql,__FILE__,__LINE__);
  202. // Delete user picture
  203. $user_info = api_get_user_info($user_id);
  204. if(strlen($user_info['picture_uri']) > 0)
  205. {
  206. $img_path = api_get_path(SYS_CODE_PATH).'upload/users/'.$user_id.'/'.$user_info['picture_uri'];
  207. unlink($img_path);
  208. }
  209. // Delete the personal course categories
  210. $course_cat_table = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  211. $sql = "DELETE FROM $course_cat_table WHERE user_id = '".$user_id."'";
  212. api_sql_query($sql,__FILE__,__LINE__);
  213. // Delete user from database
  214. $sql = "DELETE FROM $table_user WHERE user_id = '".$user_id."'";
  215. api_sql_query($sql,__FILE__,__LINE__);
  216. // Delete user from the admin table
  217. $sql = "DELETE FROM $table_admin WHERE user_id = '".$user_id."'";
  218. api_sql_query($sql,__FILE__,__LINE__);
  219. // Delete the personal agenda-items from this user
  220. $agenda_table = Database :: get_user_personal_table(TABLE_PERSONAL_AGENDA);
  221. $sql = "DELETE FROM $agenda_table WHERE user = '".$user_id."'";
  222. api_sql_query($sql,__FILE__,__LINE__);
  223. $gradebook_results_table = Database :: get_main_table(TABLE_MAIN_GRADEBOOK_RESULT);
  224. $sql = 'DELETE FROM '.$gradebook_results_table.' WHERE user_id = '.$user_id;
  225. api_sql_query($sql, __FILE__, __LINE__);
  226. $user = Database::fetch_array($res);
  227. $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  228. $sqlv = "DELETE FROM $t_ufv WHERE user_id = $user_id";
  229. $resv = api_sql_query($sqlv,__FILE__,__LINE__);
  230. global $_configuration;
  231. if ($_configuration['multiple_access_urls']) {
  232. require_once (api_get_path(LIBRARY_PATH).'urlmanager.lib.php');
  233. $url_id=1;
  234. if (api_get_current_access_url_id()!=-1)
  235. $url_id=api_get_current_access_url_id();
  236. UrlManager::delete_url_rel_user($user_id,$url_id);
  237. }
  238. return true;
  239. }
  240. /**
  241. * Update user information with new openid
  242. * @param int $user_id
  243. * @param string $openid
  244. * @return boolean true if the user information was updated
  245. */
  246. function update_openid($user_id, $openid)
  247. {
  248. $table_user = Database :: get_main_table(TABLE_MAIN_USER);
  249. $sql = "UPDATE $table_user SET
  250. openid='".Database::escape_string($openid)."'";
  251. $sql .= " WHERE user_id='$user_id'";
  252. return api_sql_query($sql,__FILE__,__LINE__);
  253. }
  254. /**
  255. * Update user information
  256. * @param int $user_id
  257. * @param string $firstname
  258. * @param string $lastname
  259. * @param string $username
  260. * @param string $password
  261. * @param string $auth_source
  262. * @param string $email
  263. * @param int $status
  264. * @param string $official_code
  265. * @param string $phone
  266. * @param string $picture_uri
  267. * @param int The user ID of the person who registered this user (optional, defaults to null)
  268. * @param int The department of HR in which the user is registered (optional, defaults to 0)
  269. * @param array A series of additional fields to add to this user as extra fields (optional, defaults to null)
  270. * @return boolean true if the user information was updated
  271. */
  272. 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,$language='english')
  273. {
  274. global $userPasswordCrypted;
  275. $table_user = Database :: get_main_table(TABLE_MAIN_USER);
  276. $sql = "UPDATE $table_user SET
  277. lastname='".Database::escape_string($lastname)."',
  278. firstname='".Database::escape_string($firstname)."',
  279. username='".Database::escape_string($username)."',
  280. language='".Database::escape_string($language)."',";
  281. if(!is_null($password))
  282. {
  283. //$password = $userPasswordCrypted ? md5($password) : $password;
  284. $password = api_get_encrypted_password($password);
  285. $sql .= " password='".Database::escape_string($password)."',";
  286. }
  287. if(!is_null($auth_source))
  288. {
  289. $sql .= " auth_source='".Database::escape_string($auth_source)."',";
  290. }
  291. $sql .= "
  292. email='".Database::escape_string($email)."',
  293. status='".Database::escape_string($status)."',
  294. official_code='".Database::escape_string($official_code)."',
  295. phone='".Database::escape_string($phone)."',
  296. picture_uri='".Database::escape_string($picture_uri)."',
  297. expiration_date='".Database::escape_string($expiration_date)."',
  298. active='".Database::escape_string($active)."',
  299. hr_dept_id=".intval($hr_dept_id);
  300. if(!is_null($creator_id))
  301. {
  302. $sql .= ", creator_id='".Database::escape_string($creator_id)."'";
  303. }
  304. $sql .= " WHERE user_id='$user_id'";
  305. $return = api_sql_query($sql,__FILE__,__LINE__);
  306. if(is_array($extra) and count($extra)>0)
  307. {
  308. $res = true;
  309. foreach($extra as $fname => $fvalue)
  310. {
  311. $res = $res && UserManager::update_extra_field($user_id,$fname,$fvalue);
  312. }
  313. }
  314. return $return;
  315. }
  316. /**
  317. * Check if a username is available
  318. * @param string the wanted username
  319. * @return boolean true if the wanted username is available
  320. */
  321. function is_username_available($username)
  322. {
  323. $table_user = Database :: get_main_table(TABLE_MAIN_USER);
  324. $sql = "SELECT username FROM $table_user WHERE username = '".addslashes($username)."'";
  325. $res = api_sql_query($sql,__FILE__,__LINE__);
  326. return Database::num_rows($res) == 0;
  327. }
  328. /**
  329. * Get a list of users of which the given conditions match with an = 'cond'
  330. * @param array $conditions a list of condition (exemple : status=>STUDENT)
  331. * @param array $order_by a list of fields on which sort
  332. * @return array An array with all users of the platform.
  333. * @todo optional course code parameter, optional sorting parameters...
  334. */
  335. function get_user_list($conditions = array(), $order_by = array()) {
  336. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  337. $return_array = array();
  338. $sql_query = "SELECT * FROM $user_table";
  339. if (count($conditions)>0) {
  340. $sql_query .= ' WHERE ';
  341. foreach ($conditions as $field=>$value) {
  342. $field = Database::escape_string($field);
  343. $value = Database::escape_string($value);
  344. $sql_query .= $field.' = '.$value;
  345. }
  346. }
  347. if (count($order_by)>0) {
  348. $sql_query .= ' ORDER BY '.Database::escape_string(implode(',',$order_by));
  349. }
  350. $sql_result = api_sql_query($sql_query,__FILE__,__LINE__);
  351. while ($result = Database::fetch_array($sql_result)) {
  352. $return_array[] = $result;
  353. }
  354. return $return_array;
  355. }
  356. /**
  357. * Get a list of users of which the given conditions match with a LIKE '%cond%'
  358. * @param array $conditions a list of condition (exemple : status=>STUDENT)
  359. * @param array $order_by a list of fields on which sort
  360. * @return array An array with all users of the platform.
  361. * @todo optional course code parameter, optional sorting parameters...
  362. */
  363. function get_user_list_like($conditions = array(), $order_by = array()) {
  364. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  365. $return_array = array();
  366. $sql_query = "SELECT * FROM $user_table";
  367. if (count($conditions)>0) {
  368. $sql_query .= ' WHERE ';
  369. foreach ($conditions as $field=>$value) {
  370. $field = Database::escape_string($field);
  371. $value = Database::escape_string($value);
  372. $sql_query .= $field.' LIKE \'%'.$value.'%\'';
  373. }
  374. }
  375. if (count($order_by)>0) {
  376. $sql_query .= ' ORDER BY '.Database::escape_string(implode(',',$order_by));
  377. }
  378. $sql_result = api_sql_query($sql_query,__FILE__,__LINE__);
  379. while ($result = Database::fetch_array($sql_result)) {
  380. $return_array[] = $result;
  381. }
  382. return $return_array;
  383. }
  384. /**
  385. * Get user information
  386. * @param string The username
  387. * @return array All user information as an associative array
  388. */
  389. function get_user_info($username)
  390. {
  391. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  392. $sql = "SELECT * FROM $user_table WHERE username='".$username."'";
  393. $res = api_sql_query($sql,__FILE__,__LINE__);
  394. if(Database::num_rows($res)>0)
  395. {
  396. $user = Database::fetch_array($res);
  397. }
  398. else
  399. {
  400. $user = false;
  401. }
  402. return $user;
  403. }
  404. /**
  405. * Get user information
  406. * @param string The id
  407. * @param boolean Whether to return the user's extra fields (defaults to false)
  408. * @return array All user information as an associative array
  409. */
  410. function get_user_info_by_id($user_id,$user_fields=false)
  411. {
  412. $user_id = intval($user_id);
  413. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  414. $sql = "SELECT * FROM $user_table WHERE user_id=".$user_id;
  415. $res = api_sql_query($sql,__FILE__,__LINE__);
  416. if(Database::num_rows($res)>0)
  417. {
  418. $user = Database::fetch_array($res);
  419. $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  420. $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  421. $sqlf = "SELECT * FROM $t_uf ORDER BY field_order";
  422. $resf = api_sql_query($sqlf,__FILE__,__LINE__);
  423. if(Database::num_rows($resf)>0)
  424. {
  425. while($rowf = Database::fetch_array($resf))
  426. {
  427. $sqlv = "SELECT * FROM $t_ufv WHERE field_id = ".$rowf['id']." AND user_id = ".$user['user_id']." ORDER BY id DESC";
  428. $resv = api_sql_query($sqlv,__FILE__,__LINE__);
  429. if(Database::num_rows($resv)>0)
  430. {
  431. //There should be only one value for a field and a user
  432. $rowv = Database::fetch_array($resv);
  433. $user['extra'][$rowf['field_variable']] = $rowv['field_value'];
  434. }
  435. else
  436. {
  437. $user['extra'][$rowf['field_variable']] = '';
  438. }
  439. }
  440. }
  441. }
  442. else
  443. {
  444. $user = false;
  445. }
  446. return $user;
  447. }
  448. //for survey
  449. function get_teacher_list($course_id, $sel_teacher='')
  450. {
  451. $user_course_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  452. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  453. $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'";
  454. $sql_result = api_sql_query($sql_query,__FILE__,__LINE__);
  455. echo "<select name=\"author\">";
  456. while ($result = Database::fetch_array($sql_result))
  457. {
  458. if($sel_teacher==$result['user_id']) $selected ="selected";
  459. echo "\n<option value=\"".$result['user_id']."\" $selected>".$result['firstname']."</option>";
  460. }
  461. echo "</select>";
  462. }
  463. /**
  464. * Get user picture URL or path from user ID (returns an array).
  465. * The return format is a complete path, enabling recovery of the directory
  466. * with dirname() or the file with basename(). This also works for the
  467. * functions dealing with the user's productions, as they are located in
  468. * the same directory.
  469. * @param integer User ID
  470. * @param string Type of path to return (can be 'none','system','rel','web')
  471. * @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)
  472. * @param bool If we want that the function returns the /main/img/unknown.jpg image set it at true
  473. * @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
  474. */
  475. function get_user_picture_path_by_id($id,$type='none',$preview=false,$anonymous=false)
  476. {
  477. if(empty($id) or empty($type))
  478. {
  479. if ($anonymous)
  480. {
  481. $dir='';
  482. switch($type)
  483. {
  484. case 'system': //return the complete path to the file, from root
  485. $dir = api_get_path(SYS_CODE_PATH).'img/';
  486. break;
  487. case 'rel': //return the relative path to the file, from the Dokeos base dir
  488. $dir = api_get_path(REL_CODE_PATH).'img/';
  489. break;
  490. case 'web': //return the complete web URL to the file
  491. $dir = api_get_path(WEB_CODE_PATH).'img/';
  492. break;
  493. case 'none': //return only the picture_uri (as is, without subdir)
  494. default:
  495. break;
  496. }
  497. $file_anonymous='unknown.jpg';
  498. return array('dir'=>$dir,'file'=>$file_anonymous);
  499. }
  500. else
  501. {
  502. return array('dir'=>'','file'=>'');
  503. }
  504. }
  505. $user_id = intval($id);
  506. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  507. $sql = "SELECT picture_uri FROM $user_table WHERE user_id=".$user_id;
  508. $res = api_sql_query($sql,__FILE__,__LINE__);
  509. $user=array();
  510. if(Database::num_rows($res)>0)
  511. {
  512. $user = Database::fetch_array($res);
  513. }
  514. else
  515. {
  516. if ($anonymous)
  517. {
  518. $dir='';
  519. switch($type)
  520. {
  521. case 'system': //return the complete path to the file, from root
  522. $dir = api_get_path(SYS_CODE_PATH).'img/';
  523. break;
  524. case 'rel': //return the relative path to the file, from the Dokeos base dir
  525. $dir = api_get_path(REL_CODE_PATH).'img/';
  526. break;
  527. case 'web': //return the complete web URL to the file
  528. $dir = api_get_path(WEB_CODE_PATH).'img/';
  529. break;
  530. case 'none': //return only the picture_uri (as is, without subdir)
  531. default:
  532. break;
  533. }
  534. $file_anonymous='unknown.jpg';
  535. return array('dir'=>$dir,'file'=>$file_anonymous);
  536. }
  537. else
  538. {
  539. return array('dir'=>'','file'=>'');
  540. }
  541. }
  542. $path = trim($user['picture_uri']);
  543. if (empty($path))
  544. {
  545. if ($anonymous)
  546. {
  547. switch($type)
  548. {
  549. case 'system': //return the complete path to the file, from root
  550. $dir = api_get_path(SYS_CODE_PATH).'img/';
  551. break;
  552. case 'rel': //return the relative path to the file, from the Dokeos base dir
  553. $dir = api_get_path(REL_CODE_PATH).'img/';
  554. break;
  555. case 'web': //return the complete web URL to the file
  556. $dir = api_get_path(WEB_CODE_PATH).'img/';
  557. break;
  558. case 'none': //return only the picture_uri (as is, without subdir)
  559. default:
  560. break;
  561. }
  562. $file_anonymous='unknown.jpg';
  563. return array('dir'=>$dir,'file'=>$file_anonymous);
  564. }
  565. }
  566. $dir = '';
  567. $first = '';
  568. if(api_get_setting('split_users_upload_directory') === 'true')
  569. {
  570. if(!empty($path))
  571. {
  572. $first = substr($path,0,1).'/';
  573. }
  574. elseif($preview==true)
  575. {
  576. $first = substr(''.$user_id,0,1).'/';
  577. }
  578. }
  579. $first .= $user_id.'/';
  580. switch($type)
  581. {
  582. case 'system': //return the complete path to the file, from root
  583. $dir = api_get_path(SYS_CODE_PATH).'upload/users/'.$first;
  584. break;
  585. case 'rel': //return the relative path to the file, from the Dokeos base dir
  586. $dir = api_get_path(REL_CODE_PATH).'upload/users/'.$first;
  587. break;
  588. case 'web': //return the complete web URL to the file
  589. $dir = api_get_path(WEB_CODE_PATH).'upload/users/'.$first;
  590. break;
  591. case 'none': //return only the picture_uri (as is, without subdir)
  592. default:
  593. break;
  594. }
  595. return array('dir'=>$dir,'file'=>$path);
  596. }
  597. /*
  598. -----------------------------------------------------------
  599. PRODUCTIONS FUNCTIONS
  600. -----------------------------------------------------------
  601. */
  602. /**
  603. * Returns an XHTML formatted list of productions for a user, or FALSE if he
  604. * doesn't have any.
  605. *
  606. * If there has been a request to remove a production, the function will return
  607. * without building the list unless forced to do so by the optional second
  608. * parameter. This increases performance by avoiding to read through the
  609. * productions on the filesystem before the removal request has been carried
  610. * out because they'll have to be re-read afterwards anyway.
  611. *
  612. * @param $user_id User id
  613. * @param $force Optional parameter to force building after a removal request
  614. * @return A string containing the XHTML code to dipslay the production list, or FALSE
  615. */
  616. function build_production_list($user_id, $force = false, $showdelete=false)
  617. {
  618. if (!$force && !empty($_POST['remove_production']))
  619. return true; // postpone reading from the filesystem
  620. $productions = UserManager::get_user_productions($user_id);
  621. if (empty($productions))
  622. return false;
  623. $production_path = UserManager::get_user_picture_path_by_id($user_id,'web',true);
  624. $production_dir = $production_path['dir'].$user_id.'/';
  625. $del_image = api_get_path(WEB_CODE_PATH).'img/delete.gif';
  626. $del_text = get_lang('Delete');
  627. $production_list = '';
  628. if (count($productions)>0) {
  629. $production_list = '<ul id="productions">';
  630. foreach ($productions as $file) {
  631. $production_list .= '<li><a href="'.$production_dir.urlencode($file).'" target="_blank">'.htmlentities($file).'</a>';
  632. if ($showdelete) {
  633. $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>';
  634. }
  635. }
  636. $production_list .= '</ul>';
  637. }
  638. return $production_list;
  639. }
  640. /**
  641. * Returns an array with the user's productions.
  642. *
  643. * @param $user_id User id
  644. * @return An array containing the user's productions
  645. */
  646. function get_user_productions($user_id)
  647. {
  648. $production_path = UserManager::get_user_picture_path_by_id($user_id,'system',true);
  649. $production_repository = $production_path['dir'].$user_id.'/';
  650. $productions = array();
  651. if (is_dir($production_repository))
  652. {
  653. $handle = opendir($production_repository);
  654. while ($file = readdir($handle))
  655. {
  656. if ($file == '.' || $file == '..' || $file == '.htaccess' || is_dir($production_repository.$file))
  657. continue; // skip current/parent directory and .htaccess
  658. $productions[] = $file;
  659. }
  660. }
  661. return $productions; // can be an empty array
  662. }
  663. /**
  664. * Remove a user production.
  665. *
  666. * @param $user_id User id
  667. * @param $production The production to remove
  668. */
  669. function remove_user_production($user_id, $production)
  670. {
  671. $production_path = UserManager::get_user_picture_path_by_id($user_id,'system',true);
  672. unlink($production_path['dir'].$user_id.'/'.$production);
  673. }
  674. /**
  675. * Update an extra field. This function is called when a user changes his/her profile
  676. * and by consequence fills or edits his/her extra fields.
  677. *
  678. * @param integer Field ID
  679. * @param array Database columns and their new value
  680. * @return boolean true if field updated, false otherwise
  681. */
  682. function update_extra_field($fid,$columns)
  683. {
  684. //TODO check that values added are values proposed for enumerated field types
  685. $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  686. $fid = Database::escape_string($fid);
  687. $sqluf = "UPDATE $t_uf SET ";
  688. $known_fields = array('id','field_variable','field_type','field_display_text','field_default_value','field_order','field_visible','field_changeable','field_filter');
  689. $safecolumns = array();
  690. foreach ($columns as $index => $newval) {
  691. if (in_array($index,$known_fields)) {
  692. $safecolumns[$index] = Database::escape_string($newval);
  693. $sqluf .= $index." = '".$safecolumns[$index]."', ";
  694. }
  695. }
  696. $time = time();
  697. $sqluf .= " tms = FROM_UNIXTIME($time) WHERE id='$fid'";
  698. $resuf = api_sql_query($sqluf,__FILE__,__LINE__);
  699. return $resuf;
  700. }
  701. /**
  702. * Update an extra field value for a given user
  703. * @param integer User ID
  704. * @param string Field variable name
  705. * @param string Field value
  706. * @return boolean true if field updated, false otherwise
  707. */
  708. function update_extra_field_value($user_id,$fname,$fvalue='')
  709. {
  710. //TODO check that values added are values proposed for enumerated field types
  711. $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  712. $t_ufo = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  713. $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  714. $fname = Database::escape_string($fname);
  715. $fvalues = '';
  716. if(is_array($fvalue))
  717. {
  718. foreach($fvalue as $val)
  719. {
  720. $fvalues .= Database::escape_string($val).';';
  721. }
  722. if(!empty($fvalues))
  723. {
  724. $fvalues = substr($fvalues,0,-1);
  725. }
  726. }
  727. else
  728. {
  729. $fvalues = Database::escape_string($fvalue);
  730. }
  731. $sqluf = "SELECT * FROM $t_uf WHERE field_variable='$fname'";
  732. $resuf = api_sql_query($sqluf,__FILE__,__LINE__);
  733. if(Database::num_rows($resuf)==1)
  734. { //ok, the field exists
  735. // Check if enumerated field, if the option is available
  736. $rowuf = Database::fetch_array($resuf);
  737. switch($rowuf['field_type'])
  738. {
  739. case 3:
  740. case 4:
  741. case 5:
  742. $sqluo = "SELECT * FROM $t_ufo WHERE field_id = ".$rowuf['id'];
  743. $resuo = api_sql_query($sqluo,__FILE__,__LINE__);
  744. $values = split(';',$fvalues);
  745. if(Database::num_rows($resuo)>0)
  746. {
  747. $check = false;
  748. while($rowuo = Database::fetch_array($resuo))
  749. {
  750. if(in_array($rowuo['option_value'],$values))
  751. {
  752. $check = true;
  753. break;
  754. }
  755. }
  756. if($check == false)
  757. {
  758. return false; //option value not found
  759. }
  760. }
  761. else
  762. {
  763. return false; //enumerated type but no option found
  764. }
  765. break;
  766. case 1:
  767. case 2:
  768. default:
  769. break;
  770. }
  771. $tms = time();
  772. $sqlufv = "SELECT * FROM $t_ufv WHERE user_id = $user_id AND field_id = ".$rowuf['id']." ORDER BY id";
  773. $resufv = api_sql_query($sqlufv,__FILE__,__LINE__);
  774. $n = Database::num_rows($resufv);
  775. if($n>1)
  776. {
  777. //problem, we already have to values for this field and user combination - keep last one
  778. while($rowufv = Database::fetch_array($resufv))
  779. {
  780. if($n > 1)
  781. {
  782. $sqld = "DELETE FROM $t_ufv WHERE id = ".$rowufv['id'];
  783. $resd = api_sql_query($sqld,__FILE__,__LINE__);
  784. $n--;
  785. }
  786. $rowufv = Database::fetch_array($resufv);
  787. if($rowufv['field_value'] != $fvalues)
  788. {
  789. $sqlu = "UPDATE $t_ufv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms) WHERE id = ".$rowufv['id'];
  790. $resu = api_sql_query($sqlu,__FILE__,__LINE__);
  791. return($resu?true:false);
  792. }
  793. return true;
  794. }
  795. }
  796. elseif($n==1)
  797. {
  798. //we need to update the current record
  799. $rowufv = Database::fetch_array($resufv);
  800. if($rowufv['field_value'] != $fvalues)
  801. {
  802. $sqlu = "UPDATE $t_ufv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms) WHERE id = ".$rowufv['id'];
  803. //error_log('UM::update_extra_field_value: '.$sqlu);
  804. $resu = api_sql_query($sqlu,__FILE__,__LINE__);
  805. return($resu?true:false);
  806. }
  807. return true;
  808. }
  809. else
  810. {
  811. $sqli = "INSERT INTO $t_ufv (user_id,field_id,field_value,tms) " .
  812. "VALUES ($user_id,".$rowuf['id'].",'$fvalues',FROM_UNIXTIME($tms))";
  813. //error_log('UM::update_extra_field_value: '.$sqli);
  814. $resi = api_sql_query($sqli,__FILE__,__LINE__);
  815. return($resi?true:false);
  816. }
  817. }
  818. else
  819. {
  820. return false; //field not found
  821. }
  822. }
  823. /**
  824. * Get an array of extra fieds with field details (type, default value and options)
  825. * @param integer Offset (from which row)
  826. * @param integer Number of items
  827. * @param integer Column on which sorting is made
  828. * @param string Sorting direction
  829. * @param boolean Optional. Whether we get all the fields or just the visible ones
  830. * @return array Extra fields details (e.g. $list[2]['type'], $list[4]['options'][2]['title']
  831. */
  832. function get_extra_fields($from=0, $number_of_items=0, $column=5, $direction='ASC', $all_visibility=true)
  833. {
  834. $fields = array();
  835. $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
  836. $t_ufo = Database :: get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  837. $columns = array('id','field_variable','field_type','field_display_text','field_default_value','field_order','field_filter','tms');
  838. $sort_direction = '';
  839. if (in_array(strtoupper($direction),array('ASC','DESC'))) {
  840. $sort_direction = strtoupper($direction);
  841. }
  842. $sqlf = "SELECT * FROM $t_uf ";
  843. if ($all_visibility==false) {
  844. $sqlf .= " WHERE field_visible = 1 ";
  845. }
  846. $sqlf .= " ORDER BY ".$columns[$column]." $sort_direction " ;
  847. if ($number_of_items != 0) {
  848. $sqlf .= " LIMIT ".Database::escape_string($from).','.Database::escape_string($number_of_items);
  849. }
  850. $resf = api_sql_query($sqlf,__FILE__,__LINE__);
  851. if(Database::num_rows($resf)>0) {
  852. while($rowf = Database::fetch_array($resf)) {
  853. $fields[$rowf['id']] = array(
  854. 0=>$rowf['id'],
  855. 1=>$rowf['field_variable'],
  856. 2=>$rowf['field_type'],
  857. //3=>(empty($rowf['field_display_text'])?'':get_lang($rowf['field_display_text'],'')),
  858. //temporarily removed auto-translation. Need update to get_lang() to know if translation exists (todo)
  859. 3=>(empty($rowf['field_display_text'])?'':$rowf['field_display_text']),
  860. 4=>$rowf['field_default_value'],
  861. 5=>$rowf['field_order'],
  862. 6=>$rowf['field_visible'],
  863. 7=>$rowf['field_changeable'],
  864. 8=>$rowf['field_filter'],
  865. 9=>array()
  866. );
  867. $sqlo = "SELECT * FROM $t_ufo WHERE field_id = ".$rowf['id']." ORDER BY option_order ASC";
  868. $reso = api_sql_query($sqlo,__FILE__,__LINE__);
  869. if (Database::num_rows($reso)>0) {
  870. while ($rowo = Database::fetch_array($reso)) {
  871. $fields[$rowf['id']][9][$rowo['id']] = array(
  872. 0=>$rowo['id'],
  873. 1=>$rowo['option_value'],
  874. //2=>(empty($rowo['option_display_text'])?'':get_lang($rowo['option_display_text'],'')),
  875. 2=>(empty($rowo['option_display_text'])?'':$rowo['option_display_text']),
  876. 3=>$rowo['option_order']
  877. );
  878. }
  879. }
  880. }
  881. }
  882. return $fields;
  883. }
  884. /**
  885. * Get the list of options attached to an extra field
  886. * @param string $fieldname the name of the field
  887. * @return array the list of options
  888. */
  889. function get_extra_field_options($field_name)
  890. {
  891. $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
  892. $t_ufo = Database :: get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  893. $sql = 'SELECT options.*
  894. FROM '.$t_ufo.' options
  895. INNER JOIN '.$t_uf.' fields
  896. ON fields.id = options.field_id
  897. AND fields.field_variable="'.Database::escape_string($field_name).'"';
  898. $rs = api_sql_query($sql, __FILE__, __LINE__);
  899. return api_store_result($rs);
  900. }
  901. /**
  902. * Get the number of extra fields currently recorded
  903. * @param boolean Optional switch. true (default) returns all fields, false returns only visible fields
  904. * @return integer Number of fields
  905. */
  906. function get_number_of_extra_fields($all_visibility=true)
  907. {
  908. $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
  909. $sqlf = "SELECT * FROM $t_uf ";
  910. if($all_visibility == false)
  911. {
  912. $sqlf .= " WHERE field_visible = 1 ";
  913. }
  914. $sqlf .= " ORDER BY field_order";
  915. $resf = api_sql_query($sqlf,__FILE__,__LINE__);
  916. return Database::num_rows($resf);
  917. }
  918. /**
  919. * Creates a new extra field
  920. * @param string Field's internal variable name
  921. * @param int Field's type
  922. * @param string Field's language var name
  923. * @param string Field's default value
  924. * @param string Optional comma-separated list of options to provide for select and radio
  925. * @return int new user id - if the new user creation succeeds, false otherwise
  926. */
  927. function create_extra_field($fieldvarname, $fieldtype, $fieldtitle, $fielddefault, $fieldoptions='')
  928. {
  929. // database table definition
  930. $table_field = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  931. $table_field_options= Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  932. // First check wether the login already exists
  933. if (UserManager::is_extra_field_available($fieldvarname))
  934. return api_set_failure('login-pass already taken');
  935. $sql = "SELECT MAX(field_order) FROM $table_field";
  936. $res = api_sql_query($sql,__FILE__,__LINE__);
  937. $order = 0;
  938. if(Database::num_rows($res)>0)
  939. {
  940. $row = Database::fetch_array($res);
  941. $order = $row[0]+1;
  942. }
  943. $time = time();
  944. $sql = "INSERT INTO $table_field
  945. SET field_type = '".Database::escape_string($fieldtype)."',
  946. field_variable = '".Database::escape_string($fieldvarname)."',
  947. field_display_text = '".Database::escape_string($fieldtitle)."',
  948. field_default_value = '".Database::escape_string($fielddefault)."',
  949. field_order = '$order',
  950. tms = FROM_UNIXTIME($time)";
  951. $result = api_sql_query($sql);
  952. if ($result)
  953. {
  954. //echo "id returned";
  955. $return=Database::get_last_insert_id();
  956. }
  957. else
  958. {
  959. //echo "false - failed" ;
  960. return false;
  961. }
  962. 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)))
  963. {
  964. if($fieldtype == USER_FIELD_TYPE_DOUBLE_SELECT)
  965. {
  966. $twolist = explode('|', $fieldoptions);
  967. $counter = 0;
  968. foreach ($twolist as $individual_list)
  969. {
  970. $splitted_individual_list = split(';',$individual_list);
  971. foreach ($splitted_individual_list as $individual_list_option)
  972. {
  973. //echo 'counter:'.$counter;
  974. if ($counter == 0)
  975. {
  976. $list[] = $individual_list_option;
  977. }
  978. else
  979. {
  980. $list[] = str_repeat('*',$counter).$individual_list_option;
  981. }
  982. }
  983. $counter++;
  984. }
  985. }
  986. else
  987. {
  988. $list = split(';',$fieldoptions);
  989. }
  990. foreach($list as $option)
  991. {
  992. $option = Database::escape_string($option);
  993. $sql = "SELECT * FROM $table_field_options WHERE field_id = $return AND option_value = '".$option."'";
  994. $res = api_sql_query($sql,__FILE__,__LINE__);
  995. if(Database::num_rows($res)>0)
  996. {
  997. //the option already exists, do nothing
  998. }
  999. else
  1000. {
  1001. $sql = "SELECT MAX(option_order) FROM $table_field_options WHERE field_id = $return";
  1002. $res = api_sql_query($sql,__FILE__,__LINE__);
  1003. $max = 1;
  1004. if(Database::num_rows($res)>0)
  1005. {
  1006. $row = Database::fetch_array($res);
  1007. $max = $row[0]+1;
  1008. }
  1009. $time = time();
  1010. $sql = "INSERT INTO $table_field_options (field_id,option_value,option_display_text,option_order,tms) VALUES ($return,'$option','$option',$max,FROM_UNIXTIME($time))";
  1011. $res = api_sql_query($sql,__FILE__,__LINE__);
  1012. if($res === false)
  1013. {
  1014. $return = false;
  1015. }
  1016. }
  1017. }
  1018. }
  1019. return $return;
  1020. }
  1021. /**
  1022. * Save the changes in the definition of the extra user profile field
  1023. * The function is called after you (as admin) decide to store the changes you have made to one of the fields you defined
  1024. *
  1025. * There is quite some logic in this field
  1026. * 1. store the changes to the field (tupe, name, label, default text)
  1027. * 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
  1028. * the options (and choices) that do no longer have to appear. We cannot remove all options and choices because if you remove them all
  1029. * and simply re-add them all then all the user who have already filled this form will loose their selected value.
  1030. * 3. we add the options that are newly added
  1031. *
  1032. * @example current options are a;b;c and the user changes this to a;b;x (removing c and adding x)
  1033. * we first remove c (and also the entry in the option_value table for the users who have chosen this)
  1034. * we then add x
  1035. * a and b are neither removed nor added
  1036. *
  1037. * @param integer $fieldid the id of the field we are editing
  1038. * @param string $fieldvarname the internal variable name of the field
  1039. * @param int $fieldtype the type of the field
  1040. * @param string $fieldtitle the title of the field
  1041. * @param string $fielddefault the default value of the field
  1042. * @param string $fieldoptions Optional comma-separated list of options to provide for select and radio
  1043. * @return boolean true
  1044. *
  1045. *
  1046. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University, Belgium
  1047. * @version July 2008
  1048. * @since Dokeos 1.8.6
  1049. */
  1050. function save_extra_field_changes($fieldid, $fieldvarname, $fieldtype, $fieldtitle, $fielddefault, $fieldoptions='')
  1051. {
  1052. // database table definition
  1053. $table_field = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1054. $table_field_options = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  1055. $table_field_options_values = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  1056. // we first update the field definition with the new values
  1057. $time = time();
  1058. $sql = "UPDATE $table_field
  1059. SET field_type = '".Database::escape_string($fieldtype)."',
  1060. field_variable = '".Database::escape_string($fieldvarname)."',
  1061. field_display_text = '".Database::escape_string($fieldtitle)."',
  1062. field_default_value = '".Database::escape_string($fielddefault)."',
  1063. tms = FROM_UNIXTIME($time)
  1064. WHERE id = '".Database::escape_string($fieldid)."'";
  1065. $result = api_sql_query($sql,__FILE__,__LINE__);
  1066. // we create an array with all the options (will be used later in the script)
  1067. if ($fieldtype == USER_FIELD_TYPE_DOUBLE_SELECT) {
  1068. $twolist = explode('|', $fieldoptions);
  1069. $counter = 0;
  1070. foreach ($twolist as $individual_list) {
  1071. $splitted_individual_list = split(';',$individual_list);
  1072. foreach ($splitted_individual_list as $individual_list_option) {
  1073. //echo 'counter:'.$counter;
  1074. if ($counter == 0) {
  1075. $list[] = trim($individual_list_option);
  1076. } else {
  1077. $list[] = str_repeat('*',$counter).trim($individual_list_option);
  1078. }
  1079. }
  1080. $counter++;
  1081. }
  1082. }
  1083. else
  1084. {
  1085. $templist = split(';',$fieldoptions);
  1086. $list = array_map('trim', $templist);
  1087. }
  1088. // Remove all the field options (and also the choices of the user) that are NOT in the new list of options
  1089. $sql = "SELECT * FROM $table_field_options WHERE option_value NOT IN ('".implode("','", $list)."') AND field_id = '".Database::escape_string($fieldid)."'";
  1090. $result = api_sql_query($sql,__FILE__,__LINE__);
  1091. $return['deleted_options'] = 0;
  1092. while ($row = Database::fetch_array($result))
  1093. {
  1094. // deleting the option
  1095. $sql_delete_option = "DELETE FROM $table_field_options WHERE id='".Database::escape_string($row['id'])."'";
  1096. $result_delete_option = api_sql_query($sql_delete_option,__FILE__,__LINE__);
  1097. $return['deleted_options']++;
  1098. // deleting the answer of the user who has chosen this option
  1099. $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'])."'";
  1100. $result_delete_option_value = api_sql_query($sql_delete_option_value,__FILE__,__LINE__);
  1101. $return['deleted_option_values'] = $return['deleted_option_values'] + Database::affected_rows();
  1102. }
  1103. // we now try to find the field options that are newly added
  1104. $sql = "SELECT * FROM $table_field_options WHERE field_id = '".Database::escape_string($fieldid)."'";
  1105. $result = api_sql_query($sql,__FILE__,__LINE__);
  1106. while ($row = Database::fetch_array($result))
  1107. {
  1108. // we remove every option that is already in the database from the $list
  1109. if (in_array(trim($row['option_display_text']),$list))
  1110. {
  1111. $key = array_search(trim($row['option_display_text']),$list);
  1112. unset($list[$key]);
  1113. }
  1114. }
  1115. // we store the new field options in the database
  1116. foreach ($list as $key=>$option)
  1117. {
  1118. $sql = "SELECT MAX(option_order) FROM $table_field_options WHERE field_id = '".Database::escape_string($fieldid)."'";
  1119. $res = api_sql_query($sql,__FILE__,__LINE__);
  1120. $max = 1;
  1121. if(Database::num_rows($res)>0)
  1122. {
  1123. $row = Database::fetch_array($res);
  1124. $max = $row[0]+1;
  1125. }
  1126. $time = time();
  1127. $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))";
  1128. $result = api_sql_query($sql,__FILE__,__LINE__);
  1129. }
  1130. return true;
  1131. }
  1132. /**
  1133. * Check if a field is available
  1134. * @param string the wanted fieldname
  1135. * @return boolean true if the wanted username is available
  1136. */
  1137. function is_extra_field_available($fieldname)
  1138. {
  1139. $t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
  1140. $sql = "SELECT * FROM $t_uf WHERE field_variable = '".Database::escape_string($fieldname)."'";
  1141. $res = api_sql_query($sql,__FILE__,__LINE__);
  1142. return Database::num_rows($res) > 0;
  1143. }
  1144. /**
  1145. * Gets user extra fields data
  1146. * @param integer User ID
  1147. * @param boolean Whether to prefix the fields indexes with "extra_" (might be used by formvalidator)
  1148. * @param boolean Whether to return invisible fields as well
  1149. * @param boolean Whether to split multiple-selection fields or not
  1150. * @return array Array of fields => value for the given user
  1151. */
  1152. function get_extra_user_data($user_id, $prefix=false, $all_visibility = true, $splitmultiple=false)
  1153. {
  1154. $extra_data = array();
  1155. $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1156. $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  1157. $user_id = Database::escape_string($user_id);
  1158. $sql = "SELECT f.id as id, f.field_variable as fvar, f.field_type as type FROM $t_uf f ";
  1159. if($all_visibility == false)
  1160. {
  1161. $sql .= " WHERE f.field_visible = 1 ";
  1162. }
  1163. $sql .= " ORDER BY f.field_order";
  1164. $res = api_sql_query($sql,__FILE__,__LINE__);
  1165. if(Database::num_rows($res)>0)
  1166. {
  1167. while($row = Database::fetch_array($res))
  1168. {
  1169. $sqlu = "SELECT field_value as fval " .
  1170. " FROM $t_ufv " .
  1171. " WHERE field_id=".$row['id']."" .
  1172. " AND user_id=".$user_id;
  1173. $resu = api_sql_query($sqlu,__FILE__,__LINE__);
  1174. $fval = '';
  1175. // get default value
  1176. $sql_df = "SELECT field_default_value as fval_df " .
  1177. " FROM $t_uf " .
  1178. " WHERE id=".$row['id'];
  1179. $res_df = api_sql_query($sql_df,__FILE__,__LINE__);
  1180. if(Database::num_rows($resu)>0)
  1181. {
  1182. $rowu = Database::fetch_array($resu);
  1183. $fval = $rowu['fval'];
  1184. if($row['type'] == USER_FIELD_TYPE_SELECT_MULTIPLE)
  1185. {
  1186. $fval = split(';',$rowu['fval']);
  1187. }
  1188. } else {
  1189. $row_df = Database::fetch_array($res_df);
  1190. $fval = $row_df['fval_df'];
  1191. }
  1192. if($prefix)
  1193. {
  1194. if ($row['type'] == USER_FIELD_TYPE_RADIO)
  1195. {
  1196. $extra_data['extra_'.$row['fvar']]['extra_'.$row['fvar']] = $fval;
  1197. }
  1198. else
  1199. {
  1200. $extra_data['extra_'.$row['fvar']] = $fval;
  1201. }
  1202. }
  1203. else
  1204. {
  1205. if ($row['type'] == USER_FIELD_TYPE_RADIO)
  1206. {
  1207. $extra_data['extra_'.$row['fvar']]['extra_'.$row['fvar']] = $fval;
  1208. }
  1209. else
  1210. {
  1211. $extra_data[$row['fvar']] = $fval;
  1212. }
  1213. }
  1214. }
  1215. }
  1216. return $extra_data;
  1217. }
  1218. function get_extra_user_data_by_field($user_id, $field_variable, $prefix=false, $all_visibility = true, $splitmultiple=false)
  1219. {
  1220. $extra_data = array();
  1221. $t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1222. $t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  1223. $user_id = Database::escape_string($user_id);
  1224. $sql = "SELECT f.id as id, f.field_variable as fvar, f.field_type as type FROM $t_uf f ";
  1225. $sql.=" WHERE f.field_variable = '$field_variable' ";
  1226. if($all_visibility == false)
  1227. {
  1228. $sql .= " AND f.field_visible = 1 ";
  1229. }
  1230. $sql .= " ORDER BY f.field_order";
  1231. $res = api_sql_query($sql,__FILE__,__LINE__);
  1232. if(Database::num_rows($res)>0)
  1233. {
  1234. while($row = Database::fetch_array($res))
  1235. {
  1236. $sqlu = "SELECT field_value as fval " .
  1237. " FROM $t_ufv " .
  1238. " WHERE field_id=".$row['id']."" .
  1239. " AND user_id=".$user_id;
  1240. $resu = api_sql_query($sqlu,__FILE__,__LINE__);
  1241. $fval = '';
  1242. if(Database::num_rows($resu)>0)
  1243. {
  1244. $rowu = Database::fetch_array($resu);
  1245. $fval = $rowu['fval'];
  1246. if($row['type'] == USER_FIELD_TYPE_SELECT_MULTIPLE)
  1247. {
  1248. $fval = split(';',$rowu['fval']);
  1249. }
  1250. }
  1251. if($prefix)
  1252. {
  1253. $extra_data['extra_'.$row['fvar']] = $fval;
  1254. }
  1255. else
  1256. {
  1257. $extra_data[$row['fvar']] = $fval;
  1258. }
  1259. }
  1260. }
  1261. return $extra_data;
  1262. }
  1263. /**
  1264. * Get all the extra field information of a certain field (also the options)
  1265. *
  1266. * @param integer $field_name the name of the field we want to know everything of
  1267. * @return array $return containing all th information about the extra profile field
  1268. * @author Julio Montoya
  1269. * @since Dokeos 1.8.6
  1270. */
  1271. function get_extra_field_information_by_name($field_variable)
  1272. {
  1273. // database table definition
  1274. $table_field = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1275. $table_field_options = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  1276. // all the information of the field
  1277. $sql = "SELECT * FROM $table_field WHERE field_variable='".Database::escape_string($field_variable)."'";
  1278. $result = api_sql_query($sql,__FILE__,__LINE__);
  1279. $return = Database::fetch_array($result);
  1280. // all the options of the field
  1281. $sql = "SELECT * FROM $table_field_options WHERE field_id='".Database::escape_string($return['id'])."' ORDER BY option_order ASC";
  1282. $result = api_sql_query($sql,__FILE__,__LINE__);
  1283. while ($row = Database::fetch_array($result)) {
  1284. $return['options'][$row['id']] = $row;
  1285. }
  1286. return $return;
  1287. }
  1288. /**
  1289. * Get all the extra field information of a certain field (also the options)
  1290. *
  1291. * @param integer $field_name the name of the field we want to know everything of
  1292. * @return array $return containing all th information about the extra profile field
  1293. * @author Julio Montoya
  1294. * @since Dokeos 1.8.6
  1295. */
  1296. function get_extra_field_information($field_id)
  1297. {
  1298. // database table definition
  1299. $table_field = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1300. $table_field_options = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  1301. // all the information of the field
  1302. $sql = "SELECT * FROM $table_field WHERE id='".Database::escape_string($field_id)."'";
  1303. $result = api_sql_query($sql,__FILE__,__LINE__);
  1304. $return = Database::fetch_array($result);
  1305. // all the options of the field
  1306. $sql = "SELECT * FROM $table_field_options WHERE field_id='".Database::escape_string($field_id)."' ORDER BY option_order ASC";
  1307. $result = api_sql_query($sql,__FILE__,__LINE__);
  1308. while ($row = Database::fetch_array($result)) {
  1309. $return['options'][$row['id']] = $row;
  1310. }
  1311. return $return;
  1312. }
  1313. function get_extra_user_data_by_value($field_variable, $field_value, $all_visibility = true)
  1314. {
  1315. $extra_data = array();
  1316. $table_user_field = Database::get_main_table(TABLE_MAIN_USER_FIELD);
  1317. $table_user_field_values = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
  1318. $table_user_field_options= Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
  1319. $where='';
  1320. /*
  1321. if (is_array($field_variable_array) && is_array($field_value_array)) {
  1322. if (count($field_variable_array) == count($field_value_array)) {
  1323. $field_var_count = count($field_variable_array);
  1324. for ($i = 0; $i<$field_var_count ; $i++) {
  1325. if ($i!=0 && $i!=$field_var_count){
  1326. $where.= ' AND ';
  1327. }
  1328. $where.= "field_variable='".Database::escape_string($field_variable_array[$i])."' AND user_field_options.id='".Database::escape_string($field_value_array[$i])."'";
  1329. }
  1330. }
  1331. }*/
  1332. $where= "field_variable='".Database::escape_string($field_variable)."' AND field_value='".Database::escape_string($field_value)."'";
  1333. $sql = "SELECT user_id FROM $table_user_field user_field INNER JOIN $table_user_field_values user_field_values
  1334. ON (user_field.id = user_field_values.field_id)
  1335. WHERE $where";
  1336. if($all_visibility == true) {
  1337. $sql .= " AND user_field.field_visible = 1 ";
  1338. } else {
  1339. $sql .= " AND user_field.field_visible = 0 ";
  1340. }
  1341. $res = api_sql_query($sql,__FILE__,__LINE__);
  1342. $result_data = array();
  1343. if (Database::num_rows($res)>0) {
  1344. while ($row = Database::fetch_array($res)) {
  1345. $result_data[]=$row['user_id'];
  1346. }
  1347. }
  1348. return $result_data;
  1349. }
  1350. /**
  1351. * Gives a list of [session_id-course_code] => [status] for the current user.
  1352. * @param integer $user_id
  1353. * @return array list of statuses (session_id-course_code => status)
  1354. */
  1355. function get_personal_session_course_list($user_id)
  1356. {
  1357. // Database Table Definitions
  1358. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1359. $tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  1360. $tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
  1361. $tbl_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  1362. $tbl_session_user = Database :: get_main_table(TABLE_MAIN_SESSION_USER);
  1363. $tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1364. $tbl_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  1365. $tbl_session_course_user = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1366. $user_id = Database::escape_string($user_id);
  1367. //we filter the courses from the URL
  1368. $join_access_url=$where_access_url='';
  1369. global $_configuration;
  1370. if ($_configuration['multiple_access_urls']==true) {
  1371. $access_url_id = api_get_current_access_url_id();
  1372. if($access_url_id!=-1) {
  1373. $tbl_url_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
  1374. $join_access_url= "LEFT JOIN $tbl_url_course url_rel_course ON url_rel_course.course_code= course.code";
  1375. $where_access_url=" AND access_url_id = $access_url_id ";
  1376. }
  1377. }
  1378. // variable initialisation
  1379. $personal_course_list_sql = '';
  1380. $personal_course_list = array();
  1381. //Courses in which we suscribed out of any session
  1382. /*$personal_course_list_sql = "SELECT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i,
  1383. course.tutor_name t, course.course_language l, course_rel_user.status s, course_rel_user.sort sort,
  1384. course_rel_user.user_course_cat user_course_cat
  1385. FROM ".$tbl_course." course,".$main_course_user_table." course_rel_user
  1386. WHERE course.code = course_rel_user.course_code"."
  1387. AND course_rel_user.user_id = '".$user_id."'
  1388. ORDER BY course_rel_user.user_course_cat, course_rel_user.sort ASC,i";*/
  1389. $tbl_user_course_category = Database :: get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  1390. $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
  1391. FROM ".$tbl_course_user." course_rel_user
  1392. LEFT JOIN ".$tbl_course." course
  1393. ON course.code = course_rel_user.course_code
  1394. LEFT JOIN ".$tbl_user_course_category." user_course_category
  1395. ON course_rel_user.user_course_cat = user_course_category.id
  1396. $join_access_url
  1397. WHERE course_rel_user.user_id = '".$user_id."' $where_access_url
  1398. ORDER BY i, user_course_category.sort, course_rel_user.sort ASC";
  1399. $course_list_sql_result = api_sql_query($personal_course_list_sql, __FILE__, __LINE__);
  1400. //var_dump($course_list_sql_result); exit;
  1401. while ($result_row = Database::fetch_array($course_list_sql_result)) {
  1402. $personal_course_list[] = $result_row;
  1403. }
  1404. // get the list of sessions where the user is subscribed as student
  1405. $sessions_sql = "SELECT DISTINCT id, name, date_start, date_end
  1406. FROM $tbl_session_user, $tbl_session
  1407. WHERE id_session=id AND id_user=$user_id
  1408. AND (date_start <= CURDATE() AND date_end >= CURDATE() OR date_start='0000-00-00')
  1409. ORDER BY date_start, date_end, name";
  1410. $result = api_sql_query($sessions_sql,__FILE__,__LINE__);
  1411. $sessions=api_store_result($result);
  1412. $sessions = array_merge($sessions , api_store_result($result));
  1413. // get the list of sessions where the user is subscribed as coach in a course
  1414. $sessions_sql = "SELECT DISTINCT id, name, date_start, date_end, DATE_SUB(date_start, INTERVAL nb_days_access_before_beginning DAY), ADDDATE(date_end, INTERVAL nb_days_access_after_end DAY)
  1415. FROM $tbl_session as session
  1416. INNER JOIN $tbl_session_course as session_rel_course
  1417. ON session_rel_course.id_session = session.id
  1418. AND session_rel_course.id_coach = $user_id
  1419. WHERE
  1420. ( CURDATE() >= DATE_SUB(date_start, INTERVAL nb_days_access_before_beginning DAY) AND
  1421. CURDATE() <= ADDDATE(date_end, INTERVAL nb_days_access_after_end DAY) OR
  1422. date_start='0000-00-00')
  1423. ORDER BY date_start, date_end, name";
  1424. $result = api_sql_query($sessions_sql,__FILE__,__LINE__);
  1425. $session_is_coach = api_store_result($result);
  1426. $sessions = array_merge($sessions , $session_is_coach);
  1427. // get the list of sessions where the user is subscribed as coach
  1428. $sessions_sql = "SELECT DISTINCT id, name, date_start, date_end
  1429. FROM $tbl_session as session
  1430. WHERE session.id_coach = $user_id
  1431. AND
  1432. ( CURDATE() >= DATE_SUB(date_start, INTERVAL nb_days_access_before_beginning DAY) AND
  1433. CURDATE() <= ADDDATE(date_end, INTERVAL nb_days_access_after_end DAY) OR
  1434. date_start='0000-00-00')
  1435. ORDER BY date_start, date_end, name";
  1436. $result = api_sql_query($sessions_sql,__FILE__,__LINE__);
  1437. $sessions = array_merge($sessions , api_store_result($result));
  1438. if(api_is_allowed_to_create_course()) {
  1439. foreach($sessions as $enreg) {
  1440. $id_session = $enreg['id'];
  1441. $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
  1442. FROM $tbl_session_course as session_course
  1443. INNER JOIN $tbl_course AS course
  1444. ON course.code = session_course.course_code
  1445. INNER JOIN $tbl_session as session
  1446. ON session.id = session_course.id_session
  1447. LEFT JOIN $tbl_user as user
  1448. ON user.user_id = session_course.id_coach
  1449. WHERE session_course.id_session = $id_session
  1450. AND (session_course.id_coach=$user_id OR session.id_coach=$user_id)
  1451. ORDER BY i";
  1452. $course_list_sql_result = api_sql_query($personal_course_list_sql, __FILE__, __LINE__);
  1453. while ($result_row = Database::fetch_array($course_list_sql_result)) {
  1454. $result_row['s'] = 2;
  1455. $key = $result_row['id_session'].' - '.$result_row['k'];
  1456. $personal_course_list[$key] = $result_row;
  1457. }
  1458. }
  1459. }
  1460. foreach($sessions as $enreg) {
  1461. $id_session = $enreg['id'];
  1462. $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')
  1463. FROM $tbl_session_course as session_course
  1464. INNER JOIN $tbl_course AS course
  1465. ON course.code = session_course.course_code
  1466. LEFT JOIN $tbl_user as user
  1467. ON user.user_id = session_course.id_coach
  1468. INNER JOIN $tbl_session_course_user
  1469. ON $tbl_session_course_user.id_session = $id_session
  1470. AND $tbl_session_course_user.id_user = $user_id
  1471. INNER JOIN $tbl_session as session
  1472. ON session_course.id_session = session.id
  1473. WHERE session_course.id_session = $id_session
  1474. ORDER BY i";
  1475. $course_list_sql_result = api_sql_query($personal_course_list_sql, __FILE__, __LINE__);
  1476. while ($result_row = Database::fetch_array($course_list_sql_result)) {
  1477. $key = $result_row['id_session'].' - '.$result_row['k'];
  1478. $result_row['s'] = $result_row['14'];
  1479. if(!isset($personal_course_list[$key])) {
  1480. $personal_course_list[$key] = $result_row;
  1481. }
  1482. }
  1483. }
  1484. //print_r($personal_course_list);
  1485. return $personal_course_list;
  1486. }
  1487. /**
  1488. * Get user id from a username
  1489. * @param string Username
  1490. * @return int User ID (or false if not found)
  1491. */
  1492. function get_user_id_from_username($username) {
  1493. $username = Database::escape_string($username);
  1494. $t_user = Database::get_main_table(TABLE_MAIN_USER);
  1495. $sql = "SELECT user_id FROM $t_user WHERE username = '$username'";
  1496. $res = api_sql_query($sql,__FILE__,__LINE__);
  1497. if ($res===false) { return false; }
  1498. if (Database::num_rows($res)!==1) { return false; }
  1499. $row = Database::fetch_array($res);
  1500. return $row['user_id'];
  1501. }
  1502. /**
  1503. * Get the users files upload from his share_folder
  1504. * @param string Username
  1505. * @return int User ID (or false if not found)
  1506. */
  1507. function get_user_upload_files_by_course($user_id, $course, $column=2)
  1508. {
  1509. $path = api_get_path(SYS_COURSE_PATH).$course.'/document/shared_folder/sf_user_'.$user_id.'/';
  1510. $web_path = api_get_path(WEB_COURSE_PATH).$course.'/document/shared_folder/sf_user_'.$user_id.'/';
  1511. $file_list= array();
  1512. $return = '';
  1513. if (is_dir($path)) {
  1514. $handle = opendir($path);
  1515. while ($file = readdir($handle)) {
  1516. if ($file == '.' || $file == '..' || $file == '.htaccess' || is_dir($path.$file))
  1517. continue; // skip current/parent directory and .htaccess
  1518. $file_list[] = $file;
  1519. }
  1520. if (count($file_list)>0) {
  1521. $return = $course;
  1522. $return .= '<ul>';
  1523. }
  1524. foreach ($file_list as $file) {
  1525. $return .= '<li><a href="'.$web_path.urlencode($file).'" target="_blank">'.htmlentities($file).'</a>';
  1526. }
  1527. $return .= '</ul>';
  1528. }
  1529. return $return;
  1530. }
  1531. /**
  1532. * Gets the API key (or keys) and return them into an array
  1533. * @param int Optional user id (defaults to the result of api_get_user_id())
  1534. * @result array Non-indexed array containing the list of API keys for this user, or FALSE on error
  1535. */
  1536. function get_api_keys($user_id=null,$api_service='dokeos') {
  1537. if ($user_id != strval(intval($user_id))) return false;
  1538. if (empty($user_id)) { $user_id = api_get_user_id(); }
  1539. if ($user_id === false) return false;
  1540. $service_name=Database::escape_string($api_service);
  1541. if (is_string($service_name)===false) { return false;}
  1542. $t_api = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
  1543. $sql = "SELECT id, api_key FROM $t_api WHERE user_id = ".$user_id." AND api_service='".$api_service."';";
  1544. $res = api_sql_query($sql,__FILE__,__LINE__);
  1545. if ($res === false) return false; //error during query
  1546. $num = Database::num_rows($res);
  1547. if ($num == 0) return false;
  1548. $list = array();
  1549. while ($row = Database::fetch_array($res)) {
  1550. $list[$row['id']] = $row['api_key'];
  1551. }
  1552. return $list;
  1553. }
  1554. /**
  1555. * Adds a new API key to the users' account
  1556. * @param int Optional user ID (defaults to the results of api_get_user_id())
  1557. * @return boolean True on success, false on failure
  1558. */
  1559. function add_api_key($user_id=null,$api_service='dokeos') {
  1560. if ($user_id != strval(intval($user_id))) return false;
  1561. if (empty($user_id)) { $user_id = api_get_user_id(); }
  1562. if ($user_id === false) return false;
  1563. $service_name=Database::escape_string($api_service);
  1564. if (is_string($service_name)===false) { return false;}
  1565. $t_api = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
  1566. $md5 = md5((time()+($user_id*5))-rand(10000,10000)); //generate some kind of random key
  1567. $sql = "INSERT INTO $t_api (user_id, api_key,api_service) VALUES ($user_id,'$md5','$service_name')";
  1568. $res = api_sql_query($sql,__FILE__,__LINE__);
  1569. if ($res === false) return false; //error during query
  1570. $num = Database::insert_id();
  1571. if ($num == 0) return false;
  1572. return $num;
  1573. }
  1574. /**
  1575. * Deletes an API key from the user's account
  1576. * @param int API key's internal ID
  1577. * @return boolean True on success, false on failure
  1578. */
  1579. function delete_api_key($key_id) {
  1580. if ($key_id != strval(intval($key_id))) return false;
  1581. if ($key_id === false) return false;
  1582. $t_api = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
  1583. $sql = "SELECT * FROM $t_api WHERE id = ".$key_id;
  1584. $res = api_sql_query($sql,__FILE__,__LINE__);
  1585. if ($res === false) return false; //error during query
  1586. $num = Database::num_rows($res);
  1587. if ($num !== 1) return false;
  1588. $sql = "DELETE FROM $t_api WHERE id = ".$key_id;
  1589. $res = api_sql_query($sql,__FILE__,__LINE__);
  1590. if ($res === false) return false; //error during query
  1591. return true;
  1592. }
  1593. /**
  1594. * Regenerate an API key from the user's account
  1595. * @param int API key's internal ID
  1596. * @param string
  1597. * @return int
  1598. */
  1599. function update_api_key($user_id,$api_service) {
  1600. if ($user_id != strval(intval($user_id))) return false;
  1601. if ($user_id === false) return false;
  1602. $service_name=Database::escape_string($api_service);
  1603. if (is_string($service_name)===false) { return false;}
  1604. $t_api = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
  1605. $sql="SELECT id FROM $t_api WHERE user_id=".$user_id." AND api_service='".$api_service."'";
  1606. $res=api_sql_query($sql,__FILE__,__LINE__);
  1607. $num = Database::num_rows($res);
  1608. if ($num==1) {
  1609. $id_key=Database::fetch_array($res,'ASSOC');
  1610. self::delete_api_key($id_key['id']);
  1611. $num=self::add_api_key($user_id,$api_service);
  1612. } elseif ($num==0) {
  1613. $num=self::add_api_key($user_id);
  1614. }
  1615. return $num;
  1616. }
  1617. /**
  1618. * @param int API key's internal ID
  1619. * @param string
  1620. * @return int
  1621. */
  1622. function get_api_key_id($user_id,$api_service) {
  1623. if ($user_id != strval(intval($user_id))) return false;
  1624. if ($user_id === false) return false;
  1625. $service_name=Database::escape_string($api_service);
  1626. if (is_string($service_name)===false) { return false;}
  1627. $t_api = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
  1628. $sql="SELECT id FROM $t_api WHERE user_id=".$user_id." AND api_service='".$api_service."'";
  1629. $res=api_sql_query($sql,__FILE__,__LINE__);
  1630. $row=Database::fetch_array($res,'ASSOC');
  1631. return $row['id'];
  1632. }
  1633. /**
  1634. * Subscribes users to the given session and optionally (default) unsubscribes previous users
  1635. * @param int Session ID
  1636. * @param array List of user IDs
  1637. * @param bool Whether to unsubscribe existing users (true, default) or not (false)
  1638. * @return void Nothing, or false on error
  1639. */
  1640. function suscribe_users_to_session($id_session,$UserList,$empty_users=true){
  1641. if ($id_session!= strval(intval($id_session))) return false;
  1642. foreach($UserList as $intUser){
  1643. if ($intUser!= strval(intval($intUser))) return false;
  1644. }
  1645. $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  1646. $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1647. $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  1648. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  1649. $sql = "SELECT id_user FROM $tbl_session_rel_user WHERE id_session='$id_session'";
  1650. $result = api_sql_query($sql,__FILE__,__LINE__);
  1651. $existingUsers = array();
  1652. while($row = Database::fetch_array($result)){
  1653. $existingUsers[] = $row['id_user'];
  1654. }
  1655. $sql = "SELECT course_code FROM $tbl_session_rel_course WHERE id_session='$id_session'";
  1656. $result=api_sql_query($sql,__FILE__,__LINE__);
  1657. $CourseList=array();
  1658. while($row=Database::fetch_array($result)) {
  1659. $CourseList[]=$row['course_code'];
  1660. }
  1661. foreach ($CourseList as $enreg_course) {
  1662. // for each course in the session
  1663. $nbr_users=0;
  1664. $enreg_course = Database::escape_string($enreg_course);
  1665. // delete existing users
  1666. if ($empty_users!==false) {
  1667. foreach ($existingUsers as $existing_user) {
  1668. if(!in_array($existing_user, $UserList)) {
  1669. $sql = "DELETE FROM $tbl_session_rel_course_rel_user WHERE id_session='$id_session' AND course_code='$enreg_course' AND id_user='$existing_user'";
  1670. api_sql_query($sql,__FILE__,__LINE__);
  1671. if(Database::affected_rows()) {
  1672. $nbr_users--;
  1673. }
  1674. }
  1675. }
  1676. }
  1677. // insert new users into session_rel_course_rel_user and ignore if they already exist
  1678. foreach ($UserList as $enreg_user) {
  1679. if(!in_array($enreg_user, $existingUsers)) {
  1680. $enreg_user = Database::escape_string($enreg_user);
  1681. $insert_sql = "INSERT IGNORE INTO $tbl_session_rel_course_rel_user(id_session,course_code,id_user) VALUES('$id_session','$enreg_course','$enreg_user')";
  1682. api_sql_query($insert_sql,__FILE__,__LINE__);
  1683. if(Database::affected_rows()) {
  1684. $nbr_users++;
  1685. }
  1686. }
  1687. }
  1688. // count users in this session-course relation
  1689. $sql = "SELECT COUNT(id_user) as nbUsers FROM $tbl_session_rel_course_rel_user WHERE id_session='$id_session' AND course_code='$enreg_course'";
  1690. $rs = api_sql_query($sql, __FILE__, __LINE__);
  1691. list($nbr_users) = Database::fetch_array($rs);
  1692. // update the session-course relation to add the users total
  1693. $update_sql = "UPDATE $tbl_session_rel_course SET nbr_users=$nbr_users WHERE id_session='$id_session' AND course_code='$enreg_course'";
  1694. api_sql_query($update_sql,__FILE__,__LINE__);
  1695. }
  1696. // delete users from the session
  1697. if ($empty_users!==false){
  1698. api_sql_query("DELETE FROM $tbl_session_rel_user WHERE id_session = $id_session",__FILE__,__LINE__);
  1699. }
  1700. // insert missing users into session
  1701. $nbr_users = 0;
  1702. foreach ($UserList as $enreg_user) {
  1703. $enreg_user = Database::escape_string($enreg_user);
  1704. $nbr_users++;
  1705. $insert_sql = "INSERT IGNORE INTO $tbl_session_rel_user(id_session, id_user) VALUES('$id_session','$enreg_user')";
  1706. api_sql_query($insert_sql,__FILE__,__LINE__);
  1707. }
  1708. // update number of users in the session
  1709. $nbr_users = count($UserList);
  1710. $update_sql = "UPDATE $tbl_session SET nbr_users= $nbr_users WHERE id='$id_session' ";
  1711. api_sql_query($update_sql,__FILE__,__LINE__);
  1712. }
  1713. /**
  1714. * Checks if a user_id is platform admin
  1715. * @param int user ID
  1716. * @return boolean True if is admin, false otherwise
  1717. * @see main_api.lib.php::api_is_platform_admin() for a context-based check
  1718. */
  1719. function is_admin($user_id) {
  1720. if (empty($user_id) or $user_id != strval(intval($user_id))) { return false; }
  1721. $admin_table = Database::get_main_table(TABLE_MAIN_ADMIN);
  1722. $sql = "SELECT * FROM $admin_table WHERE user_id = $user_id";
  1723. $res = Database::query($sql);
  1724. if (Database::num_rows($res) === 1) {
  1725. return true;
  1726. }
  1727. return false;
  1728. }
  1729. /**
  1730. * Get the total count of users
  1731. * @return mixed Number of users or false on error
  1732. */
  1733. function get_number_of_users() {
  1734. $t_u = Database::get_main_table(TABLE_MAIN_USER);
  1735. $sql = "SELECT count(*) FROM $t_u";
  1736. $res = Database::query($sql);
  1737. if (Database::num_rows($res) === 1) {
  1738. return (int) Database::result($res,0,0);
  1739. }
  1740. return false;
  1741. }
  1742. /**
  1743. * Resize a picture
  1744. *
  1745. * @param string file picture
  1746. * @param int size in pixels
  1747. * @return obj image object
  1748. */
  1749. function resize_picture($file, $max_size_for_picture)
  1750. {
  1751. $temp = new image($file);
  1752. $picture_infos=getimagesize($file);
  1753. if ($picture_infos[0]>$max_size_for_picture) {
  1754. $thumbwidth = $max_size_for_picture;
  1755. if (empty($thumbwidth) or $thumbwidth==0) {
  1756. $thumbwidth=$max_size_for_picture;
  1757. }
  1758. $new_height = round(($thumbwidth/$picture_infos[0])*$picture_infos[1]);
  1759. if($new_height > $max_size_for_picture)
  1760. $new_height = $thumbwidth;
  1761. $temp->resize($thumbwidth,$new_height,0);
  1762. }
  1763. return $temp;
  1764. }
  1765. /**
  1766. * Gets the current user image
  1767. * @param string user id
  1768. * @param string picture user name
  1769. * @param string height
  1770. * @param string picture size it can be small_, medium_ or big_
  1771. * @param string style css
  1772. * @return array with the file and the style of an image i.e $array['file'] $array['style']
  1773. */
  1774. function get_picture_user($user_id, $picture_file, $height, $size_picture = 'medium_', $style = '') {
  1775. $patch_profile = 'upload/users/';
  1776. $picture = array();
  1777. $picture['style'] = $style;
  1778. if ($picture_file == 'unknown.jpg') {
  1779. $picture['file'] = api_get_path(WEB_CODE_PATH).'img/'.$picture_file;
  1780. return $picture;
  1781. }
  1782. $file = api_get_path(SYS_CODE_PATH).$patch_profile.$user_id.'/'.$size_picture.$picture_file;
  1783. if(file_exists($file)) {
  1784. $picture['file'] = api_get_path(WEB_CODE_PATH).$patch_profile.$user_id.'/'.$size_picture.$picture_file;
  1785. $picture['style']='';
  1786. if ($height > 0) {
  1787. $dimension = getimagesize($picture['file']);
  1788. $margin = (($height - $dimension[1])/2);
  1789. $picture['style'] = ' style="padding-top:'.$margin.'px; width:'.$dimension[0].'px; height:'.$dimension[1].';" ';
  1790. }
  1791. } else {
  1792. $file = api_get_path(SYS_CODE_PATH).$patch_profile.$user_id.'/'.$picture_file;
  1793. if (file_exists($file)) {
  1794. $picture['file'] = api_get_path(WEB_CODE_PATH).$patch_profile.$user_id.'/'.$picture_file;
  1795. } else {
  1796. $picture['file'] = api_get_path(WEB_CODE_PATH).'img/unknown.jpg';
  1797. }
  1798. }
  1799. return $picture;
  1800. }
  1801. /**
  1802. * @author Isaac flores <isaac.flores@dokeos.com>
  1803. * @param string The email administrator
  1804. * @param integer The user id
  1805. * @param string The message title
  1806. * @param string The content message
  1807. */
  1808. function send_message_in_outbox ($email_administrator,$user_id,$title, $content) {
  1809. global $charset;
  1810. $table_message = Database::get_main_table(TABLE_MESSAGE);
  1811. $table_user = Database::get_main_table(TABLE_MAIN_USER);
  1812. $title = mb_convert_encoding($title,$charset,'UTF-8');
  1813. $content = mb_convert_encoding($content,$charset,'UTF-8');
  1814. //message in inbox
  1815. $sql_message_outbox='SELECT user_id from '.$table_user.' WHERE email="'.$email_administrator.'" ';
  1816. //$num_row_query=Database::num_rows($sql_message_outbox);
  1817. $res_message_outbox=Database::query($sql_message_outbox,__FILE__,__LINE__);
  1818. $array_users_administrator=array();
  1819. while ($row_message_outbox=Database::fetch_array($res_message_outbox,'ASSOC')) {
  1820. $array_users_administrator[]=$row_message_outbox['user_id'];
  1821. }
  1822. //allow to insert messages in outbox
  1823. for ($i=0;$i<count($array_users_administrator);$i++) {
  1824. $sql_insert_outbox = "INSERT INTO $table_message(user_sender_id, user_receiver_id, msg_status, send_date, title, content ) ".
  1825. " VALUES (".
  1826. "'".(int)$user_id."', '".(int)($array_users_administrator[$i])."', '4', '".date('Y-m-d H:i:s')."','".Database::escape_string($title)."','".Database::escape_string($content)."'".
  1827. ")";
  1828. $rs = Database::query($sql_insert_outbox,__FILE__,__LINE__);
  1829. }
  1830. }
  1831. }