course.lib.php 63 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762
  1. <?php
  2. /*
  3. ==============================================================================
  4. Dokeos - elearning and course management software
  5. Copyright (c) 2004-2008 Dokeos SPRL
  6. Copyright (c) 2001 Universite catholique de Louvain (UCL)
  7. Copyright (c) Roan Embrechts, Vrije Universiteit Brussel
  8. Copyright (c) Bart Mollet, Hogeschool Gent
  9. Copyright (c) Yannick Warnier, Dokeos S.A.
  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 address: Dokeos, rue du Corbeau, 108, B-1030 Brussels, Belgium
  18. Mail: info@dokeos.com
  19. ==============================================================================
  20. */
  21. /**
  22. ==============================================================================
  23. * This is the course library for Dokeos.
  24. *
  25. * All main course functions should be placed here.
  26. * Many functions of this library deal with providing support for
  27. * virtual/linked/combined courses (this was already used in several universities
  28. * but not available in standard Dokeos).
  29. *
  30. * The implementation changed, initially a course was a real course
  31. * if target_course_code was 0 , this was changed to NULL.
  32. * There are probably some places left with the wrong code.
  33. *
  34. * @package dokeos.library
  35. ==============================================================================
  36. */
  37. /*
  38. ==============================================================================
  39. DOCUMENTATION
  40. (list not up to date, you can auto generate documentation with phpDocumentor)
  41. CourseManager::get_real_course_code_select_html($element_name, $has_size=true, $only_current_user_courses=true)
  42. CourseManager::check_parameter($parameter, $error_message)
  43. CourseManager::check_parameter_or_fail($parameter, $error_message)
  44. CourseManager::is_existing_course_code($wanted_course_code)
  45. CourseManager::get_real_course_list()
  46. CourseManager::get_virtual_course_list()
  47. GENERAL COURSE FUNCTIONS
  48. CourseManager::get_access_settings($course_code)
  49. CourseManager::set_course_tool_visibility($tool_table_id, $visibility)
  50. CourseManager::get_user_in_course_status($user_id, $course_code)
  51. CourseManager::add_user_to_course($user_id, $course_code)
  52. CourseManager::get_virtual_course_info($real_course_code)
  53. CourseManager::is_virtual_course_from_visual_code($visual_code)
  54. CourseManager::is_virtual_course_from_system_code($system_code)
  55. CourseManager::get_virtual_courses_linked_to_real_course($real_course_code)
  56. CourseManager::get_list_of_virtual_courses_for_specific_user_and_real_course($user_id, $real_course_code)
  57. CourseManager::has_virtual_courses_from_code($real_course_code, $user_id)
  58. CourseManager::get_target_of_linked_course($virtual_course_code)
  59. TITLE AND CODE FUNCTIONS
  60. CourseManager::determine_course_title_from_course_info($user_id, $course_info)
  61. CourseManager::create_combined_name($user_is_registered_in_real_course, $real_course_name, $virtual_course_list)
  62. CourseManager::create_combined_code($user_is_registered_in_real_course, $real_course_code, $virtual_course_list)
  63. USER FUNCTIONS
  64. CourseManager::get_real_course_list_of_user_as_course_admin($user_id)
  65. CourseManager::get_course_list_of_user_as_course_admin($user_id)
  66. CourseManager::is_user_subscribed_in_course($user_id, $course_code)
  67. CourseManager::is_user_subscribed_in_real_or_linked_course($user_id, $course_code)
  68. CourseManager::get_user_list_from_course_code($course_code)
  69. CourseManager::get_real_and_linked_user_list($course_code);
  70. GROUP FUNCTIONS
  71. CourseManager::get_group_list_of_course($course_code)
  72. CREATION FUNCTIONS
  73. CourseManager::attempt_create_virtual_course($real_course_code, $course_title, $wanted_course_code, $course_language, $course_category)
  74. ==============================================================================
  75. */
  76. /*
  77. ==============================================================================
  78. INIT SECTION
  79. ==============================================================================
  80. */
  81. /*
  82. -----------------------------------------------------------
  83. Configuration files
  84. -----------------------------------------------------------
  85. */
  86. include_once (api_get_path(CONFIGURATION_PATH).'add_course.conf.php');
  87. /*
  88. -----------------------------------------------------------
  89. Libraries
  90. we assume main_api is also included...
  91. -----------------------------------------------------------
  92. */
  93. include_once (api_get_path(LIBRARY_PATH).'database.lib.php');
  94. include_once (api_get_path(LIBRARY_PATH).'add_course.lib.inc.php');
  95. /*
  96. -----------------------------------------------------------
  97. Constants
  98. -----------------------------------------------------------
  99. */
  100. //LOGIC: course visibility and registration settings
  101. /*
  102. COURSE VISIBILITY
  103. MAPPING OLD SETTINGS TO NEW SETTINGS
  104. -----------------------
  105. NOT_VISIBLE_NO_SUBSCRIPTION_ALLOWED
  106. --> COURSE_VISIBILITY_REGISTERED, SUBSCRIBE_NOT_ALLOWED
  107. NOT_VISIBLE_SUBSCRIPTION_ALLOWED
  108. --> COURSE_VISIBILITY_REGISTERED, SUBSCRIBE_ALLOWED
  109. VISIBLE_SUBSCRIPTION_ALLOWED
  110. --> COURSE_VISIBILITY_OPEN_PLATFORM, SUBSCRIBE_ALLOWED
  111. VISIBLE_NO_SUBSCRIPTION_ALLOWED
  112. --> COURSE_VISIBILITY_OPEN_PLATFORM, SUBSCRIBE_NOT_ALLOWED
  113. */
  114. //OLD SETTINGS
  115. define("NOT_VISIBLE_NO_SUBSCRIPTION_ALLOWED", 0);
  116. define("NOT_VISIBLE_SUBSCRIPTION_ALLOWED", 1);
  117. define("VISIBLE_SUBSCRIPTION_ALLOWED", 2);
  118. define("VISIBLE_NO_SUBSCRIPTION_ALLOWED", 3);
  119. //NEW SETTINGS
  120. //these are now defined in the main_api.lib.php
  121. /*
  122. COURSE_VISIBILITY_CLOSED
  123. COURSE_VISIBILITY_REGISTERED
  124. COURSE_VISIBILITY_OPEN_PLATFORM
  125. COURSE_VISIBILITY_OPEN_WORLD
  126. SUBSCRIBE_ALLOWED
  127. SUBSCRIBE_NOT_ALLOWED
  128. UNSUBSCRIBE_ALLOWED
  129. UNSUBSCRIBE_NOT_ALLOWED
  130. */
  131. /*
  132. -----------------------------------------------------------
  133. Variables
  134. -----------------------------------------------------------
  135. */
  136. $TABLECOURSE = Database :: get_main_table(TABLE_MAIN_COURSE);
  137. $TABLECOURSDOMAIN = Database :: get_main_table(TABLE_MAIN_CATEGORY);
  138. $TABLEUSER = Database :: get_main_table(TABLE_MAIN_USER);
  139. $TABLECOURSUSER = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  140. $TABLEANNOUNCEMENTS = "announcement";
  141. $coursesRepositories = $_configuration['root_sys'];
  142. /*
  143. ==============================================================================
  144. CourseManager CLASS
  145. ==============================================================================
  146. */
  147. /**
  148. * @package dokeos.library
  149. */
  150. class CourseManager
  151. {
  152. /**
  153. * Returns all the information of a given coursecode
  154. * @param string $course_code, the course code
  155. * @return an array with all the fields of the course table
  156. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  157. */
  158. function get_course_information($course_code)
  159. {
  160. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  161. $sql="SELECT * FROM ".$course_table." WHERE code='".$course_code."'";
  162. $sql_result = api_sql_query($sql, __FILE__, __LINE__);
  163. $result = Database::fetch_array($sql_result);
  164. return $result;
  165. }
  166. /**
  167. * Returns a list of courses. Should work with quickform syntax
  168. * @param integer Offset (from the 7th = '6'). Optional.
  169. * @param integer Number of results we want. Optional.
  170. * @param string The column we want to order it by. Optional, defaults to first column.
  171. * @param string The direction of the order (ASC or DESC). Optional, defaults to ASC.
  172. * @param string The visibility of the course, or all by default.
  173. * @param string If defined, only return results for which the course *title* begins with this string
  174. */
  175. function get_courses_list($from=0,$howmany=0,$orderby=1,$orderdirection='ASC',$visibility=-1,$startwith='')
  176. {
  177. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  178. $sql = "SELECT code, title " .
  179. "FROM $tbl_course ";
  180. if(!empty($startwith))
  181. {
  182. $sql .= "WHERE LIKE title '".Database::escape_string($startwith)."%' ";
  183. }
  184. else
  185. {
  186. $sql .= "WHERE 1 ";
  187. }
  188. if(!empty($orderby))
  189. {
  190. $sql .= " ORDER BY ".Database::escape_string($orderby)." ";
  191. }
  192. else
  193. {
  194. $sql .= " ORDER BY 1 ";
  195. }
  196. if(!empty($orderdirection))
  197. {
  198. $sql .= Database::escape_string($orderdirection);
  199. }
  200. else
  201. {
  202. $sql .= 'ASC';
  203. }
  204. if(!empty($howmany) and is_int($howmany) and $howmany>0)
  205. {
  206. $sql .= ' LIMIT '.Database::escape_string($howmany);
  207. }
  208. else
  209. {
  210. $sql .= ' LIMIT 1000000'; //virtually no limit
  211. }
  212. if(!empty($from))
  213. {
  214. $sql .= ' OFFSET '.Database::escape_string($from);
  215. }
  216. else
  217. {
  218. $sql .= ' OFFSET 0';
  219. }
  220. $res = api_sql_query($sql,__FILE__,__LINE__);
  221. return api_store_result($res);
  222. }
  223. /**
  224. * Returns the access settings of the course:
  225. * which visibility;
  226. * wether subscribing is allowed;
  227. * wether unsubscribing is allowed.
  228. *
  229. * @param string $course_code, the course code
  230. * @todo for more consistency: use course_info call from database API
  231. * @return an array with int fields "visibility", "subscribe", "unsubscribe"
  232. */
  233. function get_access_settings($course_code)
  234. {
  235. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  236. $sql = "SELECT `visibility`, `subscribe`, `unsubscribe` from ".$course_table." where `code` = '".$course_code."'";
  237. $sql_result = api_sql_query($sql, __FILE__, __LINE__);
  238. $result = Database::fetch_array($sql_result);
  239. return $result;
  240. }
  241. /**
  242. * Returns the status of a user in a course, which is COURSEMANAGER or STUDENT.
  243. *
  244. * @return int the status of the user in that course
  245. */
  246. function get_user_in_course_status($user_id, $course_code)
  247. {
  248. $course_user_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  249. $sql_query = "SELECT * FROM $course_user_table WHERE `course_code` = '$course_code' AND `user_id` = '$user_id'";
  250. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  251. $result = Database::fetch_array($sql_result);
  252. return $result["status"];
  253. }
  254. /**
  255. * Unsubscribe one or more users from a course
  256. * @param int|array $user_id
  257. * @param string $course_code
  258. */
  259. function unsubscribe_user($user_id, $course_code)
  260. {
  261. if(!is_array($user_id))
  262. {
  263. $user_id = array($user_id);
  264. }
  265. if(count($user_id) == 0)
  266. {
  267. return;
  268. }
  269. $user_ids = implode(',', $user_id);
  270. $table_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  271. $table_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  272. // Unsubscribe user from all groups in the course
  273. $sql = "SELECT * FROM $table_course WHERE code = '".$course_code."'";
  274. $res = api_sql_query($sql, __FILE__, __LINE__);
  275. $course = Database::fetch_object($res);
  276. $table_group = Database :: get_course_table(TABLE_GROUP_USER, $course->db_name);
  277. $sql = "DELETE FROM $table_group WHERE user_id IN (".$user_ids.")";
  278. api_sql_query($sql, __FILE__, __LINE__);
  279. // Unsubscribe user from all blogs in the course
  280. $table_blog_user = Database::get_course_table(TABLE_BLOGS_REL_USER, $course->db_name);
  281. $sql = "DELETE FROM ".$table_blog_user." WHERE user_id IN (".$user_ids.")";
  282. api_sql_query($sql,__FILE__,__LINE__);
  283. $table_blogtask_user = Database::get_course_table(TABLE_BLOGS_TASKS_REL_USER, $course->db_name);
  284. $sql = "DELETE FROM ".$table_blogtask_user." WHERE user_id IN (".$user_ids.")";
  285. api_sql_query($sql,__FILE__,__LINE__);
  286. // Unsubscribe user from the course
  287. $sql = "DELETE FROM $table_course_user WHERE user_id IN (".$user_ids.") AND course_code = '".$course_code."'";
  288. api_sql_query($sql, __FILE__, __LINE__);
  289. }
  290. /**
  291. * Subscribe a user to a course. No checks are performed here to see if
  292. * course subscription is allowed.
  293. * @see add_user_to_course
  294. */
  295. function subscribe_user($user_id, $course_code, $status = STUDENT)
  296. {
  297. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  298. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  299. $course_user_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  300. $location_table = Database :: get_main_table(MAIN_LOCATION_TABLE);
  301. $user_role_table = Database :: get_main_table(MAIN_USER_ROLE_TABLE);
  302. $status = ($status == STUDENT || $status == COURSEMANAGER) ? $status : STUDENT;
  303. $role_id = ($status == COURSEMANAGER) ? COURSE_ADMIN : NORMAL_COURSE_MEMBER;
  304. if (empty ($user_id) || empty ($course_code))
  305. {
  306. return false;
  307. }
  308. else
  309. {
  310. // previously check if the user are already registered on the platform
  311. $handle = api_sql_query("SELECT status FROM ".$user_table."
  312. WHERE `user_id` = '$user_id' ", __FILE__, __LINE__);
  313. if (Database::num_rows($handle) == 0)
  314. {
  315. return false; // the user isn't registered to the platform
  316. }
  317. else
  318. {
  319. //check if user isn't already subscribed to the course
  320. $handle = api_sql_query("SELECT * FROM ".$course_user_table."
  321. WHERE `user_id` = '$user_id'
  322. AND `course_code` ='$course_code'", __FILE__, __LINE__);
  323. if (Database::num_rows($handle) > 0)
  324. {
  325. return false; // the user is already subscribed to the course
  326. }
  327. else
  328. {
  329. $course_sort = CourseManager :: userCourseSort($user_id,$course_code);
  330. $add_course_user_entry_sql = "INSERT INTO ".$course_user_table."
  331. SET `course_code` = '$course_code',
  332. `user_id` = '$user_id',
  333. `status` = '".$status."',
  334. `sort` = '". ($course_sort)."'";
  335. $result = api_sql_query($add_course_user_entry_sql, __FILE__, __LINE__);
  336. if ($result)
  337. {
  338. return true;
  339. }
  340. else
  341. {
  342. return false;
  343. }
  344. }
  345. }
  346. }
  347. }
  348. /**
  349. * Subscribe a user $user_id to a course $course_code.
  350. * @author Hugues Peeters
  351. * @author Roan Embrechts
  352. *
  353. * @param int $user_id the id of the user
  354. * @param string $course_code the course code
  355. * @param string $status (optional) The user's status in the course
  356. *
  357. * @return boolean true if subscription succeeds, boolean false otherwise.
  358. * @todo script has ugly ifelseifelseifelseif structure, improve
  359. */
  360. function add_user_to_course($user_id, $course_code, $status = STUDENT)
  361. {
  362. $user_table = Database :: get_main_table(TABLE_MAIN_USER);
  363. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  364. $course_user_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  365. $status = ($status == STUDENT || $status == COURSEMANAGER) ? $status : STUDENT;
  366. if (empty($user_id) || empty ($course_code))
  367. {
  368. return false;
  369. }
  370. else
  371. {
  372. // previously check if the user are already registered on the platform
  373. $handle = api_sql_query("SELECT status FROM ".$user_table."
  374. WHERE `user_id` = '$user_id' ", __FILE__, __LINE__);
  375. if (Database::num_rows($handle) == 0)
  376. {
  377. return false; // the user isn't registered to the platform
  378. }
  379. else
  380. {
  381. //check if user isn't already subscribed to the course
  382. $handle = api_sql_query("SELECT * FROM ".$course_user_table."
  383. WHERE `user_id` = '$user_id'
  384. AND `course_code` ='$course_code'", __FILE__, __LINE__);
  385. if (Database::num_rows($handle) > 0)
  386. {
  387. return false; // the user is already subscribed to the course
  388. }
  389. else
  390. {
  391. // previously check if subscription is allowed for this course
  392. $handle = api_sql_query("SELECT code, visibility FROM ".$course_table."
  393. WHERE `code` = '$course_code'
  394. AND `subscribe` = '".SUBSCRIBE_NOT_ALLOWED."'", __FILE__, __LINE__);
  395. if (Database::num_rows($handle) > 0)
  396. {
  397. return false; // subscription not allowed for this course
  398. }
  399. else
  400. {
  401. $max_sort = api_max_sort_value('0', $user_id);
  402. $add_course_user_entry_sql = "INSERT INTO ".$course_user_table."
  403. SET `course_code` = '$course_code',
  404. `user_id` = '$user_id',
  405. `status` = '".$status."',
  406. `sort` = '". ($max_sort +1)."'";
  407. $result=api_sql_query($add_course_user_entry_sql, __FILE__, __LINE__);
  408. if ($result)
  409. {
  410. return true;
  411. }
  412. else
  413. {
  414. return false;
  415. }
  416. }
  417. }
  418. }
  419. }
  420. }
  421. /**
  422. * This code creates a select form element to let the user
  423. * choose a real course to link to.
  424. *
  425. * A good non-display library should not use echo statements, but just return text/html
  426. * so users of the library can choose when to display.
  427. *
  428. * We display the course code, but internally store the course id.
  429. *
  430. * @param boolean $has_size, true the select tag gets a size element, false it stays a dropdownmenu
  431. * @param boolean $only_current_user_courses, true only the real courses of which the
  432. * current user is course admin are displayed, false all real courses are shown.
  433. * @param string $element_name the name of the select element
  434. * @return a string containing html code for a form select element.
  435. * @deprecated Function not in use
  436. */
  437. function get_real_course_code_select_html($element_name, $has_size = true, $only_current_user_courses = true, $user_id)
  438. {
  439. if ($only_current_user_courses == true)
  440. {
  441. $real_course_list = CourseManager :: get_real_course_list_of_user_as_course_admin($user_id);
  442. }
  443. else
  444. {
  445. $real_course_list = CourseManager :: get_real_course_list();
  446. }
  447. if ($has_size == true)
  448. {
  449. $size_element = "size=\"".SELECT_BOX_SIZE."\"";
  450. }
  451. else
  452. {
  453. $size_element = "";
  454. }
  455. $html_code = "<select name=\"$element_name\" $size_element >\n";
  456. foreach ($real_course_list as $real_course)
  457. {
  458. $course_code = $real_course["code"];
  459. $html_code .= "<option value=\"".$course_code."\">";
  460. $html_code .= $course_code;
  461. $html_code .= "</option>\n";
  462. }
  463. $html_code .= "</select>\n";
  464. return $html_code;
  465. }
  466. /**
  467. * Checks wether a parameter exists.
  468. * If it doesn't, the function displays an error message.
  469. *
  470. * @return true if parameter is set and not empty, false otherwise
  471. * @todo move function to better place, main_api ?
  472. */
  473. function check_parameter($parameter, $error_message)
  474. {
  475. if (!isset ($parameter) || empty ($parameter))
  476. {
  477. Display :: display_normal_message($error_message);
  478. return false;
  479. }
  480. return true;
  481. }
  482. /**
  483. * Lets the script die when a parameter check fails.
  484. * @todo move function to better place, main_api ?
  485. */
  486. function check_parameter_or_fail($parameter, $error_message)
  487. {
  488. if (!CourseManager :: check_parameter($parameter, $error_message))
  489. die();
  490. }
  491. /**
  492. * @return true if there already are one or more courses
  493. * with the same code OR visual_code (visualcode), false otherwise
  494. */
  495. function is_existing_course_code($wanted_course_code)
  496. {
  497. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  498. $sql_query = "SELECT COUNT(*) as number FROM ".$course_table."WHERE `code` = '$wanted_course_code' OR `visual_code` = '$wanted_course_code' ";
  499. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  500. $result = Database::fetch_array($sql_result);
  501. if ($result["number"] > 0)
  502. {
  503. return true;
  504. }
  505. else
  506. {
  507. return false;
  508. }
  509. }
  510. /**
  511. * @return an array with the course info of all real courses on the platform
  512. */
  513. function get_real_course_list()
  514. {
  515. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  516. $sql_query = "SELECT * FROM $course_table WHERE `target_course_code` IS NULL";
  517. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  518. while ($result = Database::fetch_array($sql_result))
  519. {
  520. $real_course_list[] = $result;
  521. }
  522. return $real_course_list;
  523. }
  524. /**
  525. * @return an array with the course info of all virtual courses on the platform
  526. */
  527. function get_virtual_course_list()
  528. {
  529. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  530. $sql_query = "SELECT * FROM $course_table WHERE `target_course_code` IS NOT NULL";
  531. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  532. while ($result = Database::fetch_array($sql_result))
  533. {
  534. $virtual_course_list[] = $result;
  535. }
  536. return $virtual_course_list;
  537. }
  538. /**
  539. * @return an array with the course info of the real courses of which
  540. * the current user is course admin
  541. */
  542. function get_real_course_list_of_user_as_course_admin($user_id)
  543. {
  544. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  545. $course_user_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  546. $sql_query = " SELECT *
  547. FROM $course_table course
  548. LEFT JOIN $course_user_table course_user
  549. ON course.`code` = course_user.`course_code`
  550. WHERE course.`target_course_code` IS NULL
  551. AND course_user.`user_id` = '$user_id'
  552. AND course_user.`status` = '1'";
  553. //api_display_debug_info($sql_query);
  554. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  555. while ($result = Database::fetch_array($sql_result))
  556. {
  557. $result_array[] = $result;
  558. }
  559. return $result_array;
  560. }
  561. /**
  562. * @return an array with the course info of all the courses (real and virtual) of which
  563. * the current user is course admin
  564. */
  565. function get_course_list_of_user_as_course_admin($user_id)
  566. {
  567. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  568. $course_user_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  569. $sql_query = " SELECT *
  570. FROM $course_table course
  571. LEFT JOIN $course_user_table course_user
  572. ON course.`code` = course_user.`course_code`
  573. WHERE course_user.`user_id` = '$user_id'
  574. AND course_user.`status` = '1'";
  575. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  576. while ($result = Database::fetch_array($sql_result))
  577. {
  578. $result_array[] = $result;
  579. }
  580. return $result_array;
  581. }
  582. /**
  583. * Find out for which courses the user is registered and determine a visual course code and course title from that.
  584. * Takes virtual courses into account
  585. *
  586. * Default case: the name and code stay what they are.
  587. *
  588. * Scenarios:
  589. * - User is registered in real course and virtual courses; name / code become a mix of all
  590. * - User is registered in real course only: name stays that of real course
  591. * - User is registered in virtual course only: name becomes that of virtual course
  592. * - user is not registered to any of the real/virtual courses: name stays that of real course
  593. * (I'm not sure about the last case, but this seems not too bad)
  594. *
  595. * @author Roan Embrechts
  596. * @param $user_id, the id of the user
  597. * @param $course_info, an array with course info that you get using Database::get_course_info($course_system_code);
  598. * @return an array with indices
  599. * $return_result["title"] - the course title of the combined courses
  600. * $return_result["code"] - the course code of the combined courses
  601. */
  602. function determine_course_title_from_course_info($user_id, $course_info)
  603. {
  604. $real_course_id = $course_info['system_code'];
  605. $real_course_info = Database :: get_course_info($real_course_id);
  606. $real_course_name = $real_course_info["title"];
  607. $real_course_visual_code = $real_course_info["visual_code"];
  608. $real_course_real_code = $course_info['system_code'];
  609. //is the user registered in the real course?
  610. $table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  611. $sql_query = "SELECT * FROM $table WHERE `user_id` = '$user_id' AND `course_code` = '$real_course_real_code'";
  612. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  613. $result = Database::fetch_array($sql_result);
  614. if (!isset ($result) || empty ($result))
  615. {
  616. $user_is_registered_in_real_course = false;
  617. }
  618. else
  619. {
  620. $user_is_registered_in_real_course = true;
  621. }
  622. //get a list of virtual courses linked to the current real course
  623. //and to which the current user is subscribed
  624. $user_subscribed_virtual_course_list = CourseManager :: get_list_of_virtual_courses_for_specific_user_and_real_course($user_id, $real_course_id);
  625. if (count($user_subscribed_virtual_course_list) > 0)
  626. {
  627. $virtual_courses_exist = true;
  628. }
  629. else
  630. {
  631. $virtual_courses_exist = false;
  632. }
  633. //now determine course code and name
  634. if ($user_is_registered_in_real_course && $virtual_courses_exist)
  635. {
  636. $course_info["name"] = CourseManager :: create_combined_name($user_is_registered_in_real_course, $real_course_name, $user_subscribed_virtual_course_list);
  637. $course_info['official_code'] = CourseManager :: create_combined_code($user_is_registered_in_real_course, $real_course_visual_code, $user_subscribed_virtual_course_list);
  638. }
  639. else
  640. if ($user_is_registered_in_real_course)
  641. {
  642. //course name remains real course name
  643. $course_info["name"] = $real_course_name;
  644. $course_info['official_code'] = $real_course_visual_code;
  645. }
  646. else
  647. if ($virtual_courses_exist)
  648. {
  649. $course_info["name"] = CourseManager :: create_combined_name($user_is_registered_in_real_course, $real_course_name, $user_subscribed_virtual_course_list);
  650. $course_info['official_code'] = CourseManager :: create_combined_code($user_is_registered_in_real_course, $real_course_visual_code, $user_subscribed_virtual_course_list);
  651. }
  652. else
  653. {
  654. //course name remains real course name
  655. $course_info["name"] = $real_course_name;
  656. $course_info['official_code'] = $real_course_visual_code;
  657. }
  658. $return_result["title"] = $course_info["name"];
  659. $return_result["code"] = $course_info['official_code'];
  660. return $return_result;
  661. }
  662. /**
  663. * Create a course title based on all real and virtual courses the user is registered in.
  664. * @param boolean $user_is_registered_in_real_course
  665. * @param string $real_course_name, the title of the real course
  666. * @param array $virtual_course_list, the list of virtual courses
  667. */
  668. function create_combined_name($user_is_registered_in_real_course, $real_course_name, $virtual_course_list)
  669. {
  670. if ($user_is_registered_in_real_course || count($virtual_course_list) > 1)
  671. {
  672. $complete_course_name_before = get_lang("CombinedCourse")." "; //from course_home lang file
  673. }
  674. if ($user_is_registered_in_real_course)
  675. {
  676. //add real name to result
  677. $complete_course_name[] = $real_course_name;
  678. }
  679. //add course titles of all virtual courses to the list
  680. foreach ($virtual_course_list as $current_course)
  681. {
  682. $complete_course_name[] = $current_course["title"];
  683. }
  684. $complete_course_name = $complete_course_name_before.implode(' &amp; ', $complete_course_name);
  685. return $complete_course_name;
  686. }
  687. /**
  688. * Create a course code based on all real and virtual courses the user is registered in.
  689. */
  690. function create_combined_code($user_is_registered_in_real_course, $real_course_code, $virtual_course_list)
  691. {
  692. $complete_course_code .= "";
  693. if ($user_is_registered_in_real_course)
  694. {
  695. //add real name to result
  696. $complete_course_code[] = $real_course_code;
  697. }
  698. //add course titles of all virtual courses to the list
  699. foreach ($virtual_course_list as $current_course)
  700. {
  701. $complete_course_code[] = $current_course["visual_code"];
  702. }
  703. $complete_course_code = implode(' &amp; ', $complete_course_code);
  704. return $complete_course_code;
  705. }
  706. /**
  707. * Return course info array of virtual course
  708. *
  709. * Note this is different from getting information about a real course!
  710. *
  711. * @param $real_course_code, the id of the real course which the virtual course is linked to
  712. */
  713. function get_virtual_course_info($real_course_code)
  714. {
  715. $table = Database :: get_main_table(TABLE_MAIN_COURSE);
  716. $sql_query = "SELECT * FROM $table WHERE `target_course_code` = '$real_course_code'";
  717. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  718. $result = array ();
  719. while ($virtual_course = Database::fetch_array($sql_result))
  720. {
  721. $result[] = $virtual_course;
  722. }
  723. return $result;
  724. }
  725. /**
  726. * @param string $system_code, the system code of the course
  727. * @return true if the course is a virtual course, false otherwise
  728. */
  729. function is_virtual_course_from_system_code($system_code)
  730. {
  731. $table = Database :: get_main_table(TABLE_MAIN_COURSE);
  732. $sql_query = "SELECT * FROM $table WHERE `code` = '$system_code'";
  733. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  734. $result = Database::fetch_array($sql_result);
  735. $target_number = $result["target_course_code"];
  736. if ($target_number == NULL)
  737. {
  738. return false; //this is not a virtual course
  739. }
  740. else
  741. {
  742. return true; //this is a virtual course
  743. }
  744. }
  745. /**
  746. * What's annoying is that you can't overload functions in PHP.
  747. * @return true if the course is a virtual course, false otherwise
  748. */
  749. function is_virtual_course_from_visual_code($visual_code)
  750. {
  751. $table = Database :: get_main_table(TABLE_MAIN_COURSE);
  752. $sql_query = "SELECT * FROM $table WHERE `visual_code` = '$visual_code'";
  753. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  754. $result = Database::fetch_array($sql_result);
  755. $target_number = $result["target_course_code"];
  756. if ($target_number == NULL)
  757. {
  758. return false; //this is not a virtual course
  759. }
  760. else
  761. {
  762. return true; //this is a virtual course
  763. }
  764. }
  765. /**
  766. * @return true if the real course has virtual courses that the user is subscribed to, false otherwise
  767. */
  768. function has_virtual_courses_from_code($real_course_code, $user_id)
  769. {
  770. $user_subscribed_virtual_course_list = CourseManager :: get_list_of_virtual_courses_for_specific_user_and_real_course($user_id, $real_course_code);
  771. $number_of_virtual_courses = count($user_subscribed_virtual_course_list);
  772. if (count($user_subscribed_virtual_course_list) > 0)
  773. {
  774. return true;
  775. }
  776. else
  777. {
  778. return false;
  779. }
  780. }
  781. /**
  782. * Return an array of arrays, listing course info of all virtual course
  783. * linked to the real course ID $real_course_code
  784. *
  785. * @param $real_course_code, the id of the real course which the virtual courses are linked to
  786. */
  787. function get_virtual_courses_linked_to_real_course($real_course_code)
  788. {
  789. $table = Database :: get_main_table(TABLE_MAIN_COURSE);
  790. $sql_query = "SELECT * FROM $table WHERE `target_course_code` = '$real_course_code'";
  791. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  792. $result_array = array ();
  793. while ($result = Database::fetch_array($sql_result))
  794. {
  795. $result_array[] = $result;
  796. }
  797. return $result_array;
  798. }
  799. /**
  800. * This function returns the course code of the real course
  801. * to which a virtual course is linked.
  802. *
  803. * @param the course code of the virtual course
  804. * @return the course code of the real course
  805. */
  806. function get_target_of_linked_course($virtual_course_code)
  807. {
  808. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  809. //get info about the virtual course
  810. $sql_query = "SELECT * FROM $course_table WHERE `code` = '$virtual_course_code'";
  811. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  812. $result = Database::fetch_array($sql_result);
  813. $target_course_code = $result["target_course_code"];
  814. return $target_course_code;
  815. }
  816. /*
  817. ==============================================================================
  818. USER FUNCTIONS
  819. ==============================================================================
  820. */
  821. /**
  822. * Return course info array of virtual course
  823. *
  824. * @param $user_id, the id (int) of the user
  825. * @param $course_info, array with info about the course (comes from course table)
  826. *
  827. * @return true if the user is registered in the course, false otherwise
  828. */
  829. function is_user_subscribed_in_course($user_id, $course_code, $in_a_session=false)
  830. {
  831. $user_id = intval($user_id);
  832. $course_code = addslashes($course_code);
  833. $table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  834. $sql_query = "SELECT * FROM $table WHERE `user_id` = '$user_id' AND `course_code` = '$course_code'";
  835. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  836. $result = Database::fetch_array($sql_result);
  837. if (!isset ($result) || empty ($result))
  838. {
  839. if($in_a_session)
  840. {
  841. $sql = 'SELECT 1 FROM '.Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER).'
  842. WHERE id_user = '.$user_id.' AND course_code="'.$course_code.'"';
  843. $rs = api_sql_query($sql, __FILE__, __LINE__);
  844. if(Database::num_rows($rs)>0)
  845. {
  846. return true;
  847. }
  848. else{
  849. $sql = 'SELECT 1 FROM '.Database :: get_main_table(TABLE_MAIN_SESSION_COURSE).'
  850. WHERE id_coach = '.$user_id.' AND course_code="'.$course_code.'"';
  851. $rs = api_sql_query($sql, __FILE__, __LINE__);
  852. if(Database::num_rows($rs)>0)
  853. {
  854. return true;
  855. }
  856. else
  857. {
  858. $sql = 'SELECT 1 FROM '.Database
  859. ::get_main_table(TABLE_MAIN_SESSION).' WHERE id='.intval($_SESSION['id_session']).' AND id_coach='.$user_id;
  860. $rs = api_sql_query($sql, __FILE__, __LINE__);
  861. if(Database::num_rows($rs)>0)
  862. {
  863. return true;
  864. }
  865. }
  866. }
  867. }
  868. else
  869. return false; //user is not registered in course
  870. }
  871. else
  872. {
  873. return true; //user is registered in course
  874. }
  875. }
  876. /**
  877. * Is the user a teacher in the given course?
  878. *
  879. * @param $user_id, the id (int) of the user
  880. * @param $course_code, the course code
  881. *
  882. * @return true if the user is a teacher in the course, false otherwise
  883. */
  884. function is_course_teacher($user_id,$course_code)
  885. {
  886. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  887. $sql_query='SELECT status FROM '.$tbl_course_user.' WHERE course_code="'.$course_code.'" and user_id="'.$user_id.'"';
  888. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  889. if(Database::num_rows($sql_result)>0)
  890. {
  891. $status=Database::result($sql_result,0,'status');
  892. if($status==1) return true; else return false;
  893. }
  894. return false;
  895. }
  896. /**
  897. * Is the user subscribed in the real course or linked courses?
  898. *
  899. * @param $user_id, the id (int) of the user
  900. * @param $course_info, array with info about the course (comes from course table, see database lib)
  901. *
  902. * @return true if the user is registered in the real course or linked courses, false otherwise
  903. */
  904. function is_user_subscribed_in_real_or_linked_course($user_id, $course_code, $session_id='')
  905. {
  906. if($session_id==''){
  907. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  908. $course_user_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  909. $sql_query = " SELECT *
  910. FROM $course_table course
  911. LEFT JOIN $course_user_table course_user
  912. ON course.`code` = course_user.`course_code`
  913. WHERE course_user.`user_id` = '$user_id' AND ( course.`code` = '$course_code' OR `target_course_code` = '$course_code') ";
  914. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  915. $result = Database::fetch_array($sql_result);
  916. if (!isset ($result) || empty ($result))
  917. {
  918. return false; //user is not registered in course
  919. }
  920. else
  921. {
  922. return true; //user is registered in course
  923. }
  924. }
  925. else {
  926. // is he subscribed to the course of the session ?
  927. // Database Table Definitions
  928. $tbl_sessions = Database::get_main_table(TABLE_MAIN_SESSION);
  929. $tbl_sessions_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  930. $tbl_session_course_user= Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  931. //users
  932. $sql = "SELECT id_user
  933. FROM $tbl_session_course_user
  934. WHERE id_session='".$_SESSION['id_session']."'
  935. AND id_user='$user_id'";
  936. $result = api_sql_query($sql,__FILE__,__LINE__);
  937. if(Database::num_rows($result))
  938. return true;
  939. // is it a course coach ?
  940. $sql = "SELECT id_coach
  941. FROM $tbl_sessions_course AS session_course
  942. WHERE id_session='".$_SESSION['id_session']."'
  943. AND id_coach = '$user_id'
  944. AND course_code='$course_code'";
  945. $result = api_sql_query($sql,__FILE__,__LINE__);
  946. if(Database::num_rows($result))
  947. return true;
  948. // is it a session coach ?
  949. $sql = "SELECT id_coach
  950. FROM $tbl_sessions AS session
  951. WHERE session.id='".$_SESSION['id_session']."'
  952. AND id_coach='$user_id'";
  953. $result = api_sql_query($sql,__FILE__,__LINE__);
  954. if(Database::num_rows($result))
  955. return true;
  956. return false;
  957. }
  958. }
  959. /**
  960. * Return user info array of all users registered in the specified real or virtual course
  961. * This only returns the users that are registered in this actual course, not linked courses.
  962. *
  963. * @param string $course_code
  964. * @return array with user info
  965. */
  966. function get_user_list_from_course_code($course_code, $with_session=true, $session_id=0, $limit='', $order_by='')
  967. {
  968. $session_id = intval($session_id);
  969. $a_users = array();
  970. $table_users = Database :: get_main_table(TABLE_MAIN_USER);
  971. $where = array();
  972. $sql = 'SELECT DISTINCT user.user_id ';
  973. if ( $session_id == 0 ) {
  974. $sql .= ', course_rel_user.role, course_rel_user.tutor_id ';
  975. }
  976. $sql .= ' FROM '.$table_users.' as user ';
  977. if(api_get_setting('use_session_mode')=='true' && $with_session)
  978. {
  979. $table_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  980. $sql .= ' LEFT JOIN '.$table_session_course_user.' as session_course_user
  981. ON user.user_id = session_course_user.id_user
  982. AND session_course_user.course_code="'.Database::escape_string($course_code).'"';
  983. if($session_id!=0)
  984. {
  985. $sql .= ' AND session_course_user.id_session = '.$session_id;
  986. }
  987. $where[] = ' session_course_user.course_code IS NOT NULL ';
  988. }
  989. if($session_id == 0)
  990. {
  991. $table_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  992. $sql .= ' LEFT JOIN '.$table_course_user.' as course_rel_user
  993. ON user.user_id = course_rel_user.user_id
  994. AND course_rel_user.course_code="'.Database::escape_string($course_code).'"';
  995. $where[] = ' course_rel_user.course_code IS NOT NULL ';
  996. }
  997. $sql .= ' WHERE '.implode(' OR ',$where);
  998. $sql .= ' '.$order_by;
  999. $sql .= ' '.$limit;
  1000. $rs = api_sql_query($sql, __FILE__, __LINE__);
  1001. while($user = Database::fetch_array($rs))
  1002. {
  1003. $user_infos = Database :: get_user_info_from_id($user['user_id']);
  1004. //$user_infos['status'] = $user['status'];
  1005. if ( isset($user['role']) ) {
  1006. $user_infos['role'] = $user['role'];
  1007. }
  1008. if ( isset($user['tutor_id']) ) {
  1009. $user_infos['tutor_id'] = $user['tutor_id'];
  1010. }
  1011. $a_users[$user['user_id']] = $user_infos;
  1012. }
  1013. return $a_users;
  1014. }
  1015. function get_coach_list_from_course_code($course_code,$session_id){
  1016. $table_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
  1017. $table_session = Database :: get_main_table(TABLE_MAIN_SESSION);
  1018. $a_users=array();
  1019. //We get the coach for the given course in a given session
  1020. $sql = 'SELECT id_coach FROM '.$table_session_course.' WHERE id_session="'.$session_id.'" AND course_code="'.$course_code.'"';
  1021. $rs = api_sql_query($sql, __FILE__, __LINE__);
  1022. while($user = Database::fetch_array($rs))
  1023. {
  1024. $user_infos = Database :: get_user_info_from_id($user['id_coach']);
  1025. $user_infos["status"] = $user["status"];
  1026. $user_infos["role"] = $user["role"];
  1027. $user_infos["tutor_id"] = $user["tutor_id"];
  1028. $user_infos['email'] = $user['email'];
  1029. $a_users[$user['id_coach']] = $user_infos;
  1030. }
  1031. //We get the session coach
  1032. $sql = 'SELECT id_coach FROM '.$table_session.' WHERE id="'.$session_id.'"';
  1033. $rs = api_sql_query($sql, __FILE__, __LINE__);
  1034. $user_infos=array();
  1035. $session_id_coach = Database::result($rs,0,'id_coach');
  1036. $user_infos = Database :: get_user_info_from_id($session_id_coach);
  1037. $user_infos["status"] = $user["status"];
  1038. $user_infos["role"] = $user["role"];
  1039. $user_infos["tutor_id"] = $user["tutor_id"];
  1040. $user_infos['email'] = $user['email'];
  1041. $a_users[$session_id_coach] = $user_infos;
  1042. return $a_users;
  1043. }
  1044. /**
  1045. * Return user info array of all users registered in the specified real or virtual course
  1046. * This only returns the users that are registered in this actual course, not linked courses.
  1047. *
  1048. * @param string $course_code
  1049. * @param boolean $full list to true if we want sessions students too
  1050. * @return array with user id
  1051. */
  1052. function get_student_list_from_course_code($course_code, $with_session=false, $session_id=0)
  1053. {
  1054. $a_students = array();
  1055. $session_id = intval($session_id);
  1056. if($session_id == 0)
  1057. {
  1058. // students directly subscribed to the course
  1059. $table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1060. $sql_query = "SELECT * FROM $table WHERE `course_code` = '$course_code' AND `status` = 5";
  1061. $rs = api_sql_query($sql_query, __FILE__, __LINE__);
  1062. while($student = Database::fetch_array($rs))
  1063. {
  1064. $a_students[$student['user_id']] = $student;
  1065. }
  1066. }
  1067. // students subscribed to the course through a session
  1068. if(api_get_setting('use_session_mode')=='true' && $with_session)
  1069. {
  1070. $table = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1071. $sql_query = "SELECT * FROM $table WHERE `course_code` = '$course_code'";
  1072. if($session_id!=0)
  1073. $sql_query .= ' AND id_session = '.$session_id;
  1074. $rs = api_sql_query($sql_query, __FILE__, __LINE__);
  1075. while($student = Database::fetch_array($rs))
  1076. {
  1077. $a_students[$student['id_user']] = $student;
  1078. }
  1079. }
  1080. return $a_students;
  1081. }
  1082. /**
  1083. * Return user info array of all teacher-users registered in the specified real or virtual course
  1084. * This only returns the users that are registered in this actual course, not linked courses.
  1085. *
  1086. * @param string $course_code
  1087. * @return array with user id
  1088. */
  1089. function get_teacher_list_from_course_code($course_code)
  1090. {
  1091. $a_teachers = array();
  1092. // teachers directly subscribed to the course
  1093. $t_cu = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1094. $t_u = Database :: get_main_table(TABLE_MAIN_USER);
  1095. $sql_query = "SELECT u.user_id, u.lastname, u.firstname, u.email, u.username, u.status " .
  1096. "FROM $t_cu cu, $t_u u " .
  1097. "WHERE cu.course_code = '$course_code' " .
  1098. "AND cu.status = 1 " .
  1099. "AND cu.user_id = u.user_id";
  1100. $rs = api_sql_query($sql_query, __FILE__, __LINE__);
  1101. while($teacher = Database::fetch_array($rs))
  1102. {
  1103. $a_students[$teacher['user_id']] = $teacher;
  1104. }
  1105. return $a_students;
  1106. }
  1107. /**
  1108. * Return user info array of all users registered in the specified course
  1109. * this includes the users of the course itsel and the users of all linked courses.
  1110. *
  1111. * @param array $course_info
  1112. * @return array with user info
  1113. */
  1114. function get_real_and_linked_user_list($course_code, $with_sessions = true, $session_id=0)
  1115. {
  1116. //get list of virtual courses
  1117. $virtual_course_list = CourseManager :: get_virtual_courses_linked_to_real_course($course_code);
  1118. //get users from real course
  1119. $user_list = CourseManager :: get_user_list_from_course_code($course_code, $with_sessions, $session_id);
  1120. foreach ($user_list as $this_user)
  1121. {
  1122. $complete_user_list[] = $this_user;
  1123. }
  1124. //get users from linked courses
  1125. foreach ($virtual_course_list as $this_course)
  1126. {
  1127. $course_code = $this_course["code"];
  1128. $user_list = CourseManager :: get_user_list_from_course_code($course_code, $with_sessions, $session_id);
  1129. foreach ($user_list as $this_user)
  1130. {
  1131. $complete_user_list[] = $this_user;
  1132. }
  1133. }
  1134. return $complete_user_list;
  1135. }
  1136. /**
  1137. * Return an array of arrays, listing course info of all courses in the list
  1138. * linked to the real course $real_course_code, to which the user $user_id is subscribed.
  1139. *
  1140. * @param $user_id, the id (int) of the user
  1141. * @param $real_course_code, the id (char) of the real course
  1142. *
  1143. * @return array of course info arrays
  1144. */
  1145. function get_list_of_virtual_courses_for_specific_user_and_real_course($user_id, $real_course_code)
  1146. {
  1147. $result_array = array();
  1148. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  1149. $course_user_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1150. $sql_query = " SELECT *
  1151. FROM $course_table course
  1152. LEFT JOIN $course_user_table course_user
  1153. ON course.`code` = course_user.`course_code`
  1154. WHERE course.`target_course_code` = '$real_course_code' AND course_user.`user_id` = '$user_id'";
  1155. $sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
  1156. while ($result = Database::fetch_array($sql_result))
  1157. {
  1158. $result_array[] = $result;
  1159. }
  1160. return $result_array;
  1161. }
  1162. /*
  1163. ==============================================================================
  1164. GROUP FUNCTIONS
  1165. ==============================================================================
  1166. */
  1167. function get_group_list_of_course($course_code)
  1168. {
  1169. $course_info = Database :: get_course_info($course_code);
  1170. $database_name = $course_info['db_name'];
  1171. $group_table = Database :: get_course_table(TABLE_GROUP, $database_name);
  1172. $group_user_table = Database :: get_course_table(TABLE_GROUP_USER, $database_name);
  1173. $sql = "SELECT g.id, g.name, COUNT(gu.id) userNb
  1174. FROM $group_table AS g LEFT JOIN $group_user_table gu
  1175. ON g.id = gu.group_id
  1176. GROUP BY g.id
  1177. ORDER BY g.name";
  1178. $result = api_sql_query($sql, __FILE__, __LINE__) or die(mysql_error());
  1179. while ($group_data = Database::fetch_array($result))
  1180. {
  1181. $group_list[$group_data['id']] = $group_data;
  1182. }
  1183. return $group_list;
  1184. }
  1185. /**
  1186. * Checks all parameters needed to create a virtual course.
  1187. * If they are all set, the virtual course creation procedure is called.
  1188. *
  1189. * Call this function instead of create_virtual_course
  1190. */
  1191. function attempt_create_virtual_course($real_course_code, $course_title, $wanted_course_code, $course_language, $course_category)
  1192. {
  1193. //better: create parameter list, check the entire list, when false display errormessage
  1194. CourseManager :: check_parameter_or_fail($real_course_code, "Unspecified parameter: real course id.");
  1195. CourseManager :: check_parameter_or_fail($course_title, "Unspecified parameter: course title.");
  1196. CourseManager :: check_parameter_or_fail($wanted_course_code, "Unspecified parameter: wanted course code.");
  1197. CourseManager :: check_parameter_or_fail($course_language, "Unspecified parameter: course language.");
  1198. CourseManager :: check_parameter_or_fail($course_category, "Unspecified parameter: course category.");
  1199. $creation_success = CourseManager :: create_virtual_course($real_course_code, $course_title, $wanted_course_code, $course_language, $course_category);
  1200. return $creation_success;
  1201. }
  1202. /**
  1203. * This function creates a virtual course.
  1204. * It assumes all parameters have been checked and are not empty.
  1205. * It checks wether a course with the $wanted_course_code already exists.
  1206. *
  1207. * Users of this library should consider this function private,
  1208. * please call attempt_create_virtual_course instead of this one.
  1209. *
  1210. * NOTE:
  1211. * The virtual course 'owner' id (the first course admin) is set to the CURRENT user id.
  1212. * @return true if the course creation succeeded, false otherwise
  1213. * @todo research: expiration date of a course
  1214. */
  1215. function create_virtual_course($real_course_code, $course_title, $wanted_course_code, $course_language, $course_category)
  1216. {
  1217. global $firstExpirationDelay;
  1218. $course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
  1219. $user_id = api_get_user_id();
  1220. $real_course_info = Database :: get_course_info($real_course_code);
  1221. $real_course_code = $real_course_info["system_code"];
  1222. //check: virtual course creation fails if another course has the same
  1223. //code, real or fake.
  1224. if (CourseManager :: is_existing_course_code($wanted_course_code))
  1225. {
  1226. Display :: display_error_message($wanted_course_code." - ".get_lang("CourseCodeAlreadyExists"));
  1227. return false;
  1228. }
  1229. //add data to course table, course_rel_user
  1230. $course_sys_code = $wanted_course_code;
  1231. $course_screen_code = $wanted_course_code;
  1232. $course_repository = $real_course_info["directory"];
  1233. $course_db_name = $real_course_info["db_name"];
  1234. $responsible_teacher = $real_course_info["tutor_name"];
  1235. $faculty_shortname = $course_category;
  1236. // $course_title = $course_title;
  1237. // $course_language = $course_language;
  1238. $teacher_id = $user_id;
  1239. //HACK ----------------------------------------------------------------
  1240. $expiration_date = time() + $firstExpirationDelay;
  1241. //END HACK ------------------------------------------------------------
  1242. register_course($course_sys_code, $course_screen_code, $course_repository, $course_db_name, $responsible_teacher, $faculty_shortname, $course_title, $course_language, $teacher_id, $expiration_date);
  1243. //above was the normal course creation table update call,
  1244. //now one more thing: fill in the target_course_code field
  1245. $sql_query = "UPDATE $course_table SET `target_course_code` = '$real_course_code' WHERE `code` = '$course_sys_code' LIMIT 1 ";
  1246. api_sql_query($sql_query, __FILE__, __LINE__);
  1247. return true;
  1248. }
  1249. /**
  1250. * Delete a course
  1251. * This function deletes a whole course-area from the platform. When the
  1252. * given course is a virtual course, the database and directory will not be
  1253. * deleted.
  1254. * When the given course is a real course, also all virtual courses refering
  1255. * to the given course will be deleted.
  1256. * Considering the fact that we remove all traces of the course in the main
  1257. * database, it makes sense to remove all tracking as well (if stats databases exist)
  1258. * so that a new course created with this code would not use the remains of an older
  1259. * course.
  1260. *
  1261. * @param string $code The code of the course to delete
  1262. * @todo When deleting a virtual course: unsubscribe users from that virtual
  1263. * course from the groups in the real course if they are not subscribed in
  1264. * that real course.
  1265. * @todo Remove globals
  1266. */
  1267. function delete_course($code)
  1268. {
  1269. global $_configuration;
  1270. $table_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  1271. $table_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1272. $table_course_class = Database :: get_main_table(TABLE_MAIN_COURSE_CLASS);
  1273. $user_role_table = Database :: get_main_table(MAIN_USER_ROLE_TABLE);
  1274. $location_table = Database::get_main_table(MAIN_LOCATION_TABLE);
  1275. $role_right_location_table = Database::get_main_table(MAIN_ROLE_RIGHT_LOCATION_TABLE);
  1276. $table_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  1277. $table_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1278. $table_course_survey = Database::get_main_table(TABLE_MAIN_SHARED_SURVEY);
  1279. $table_course_survey_question = Database::get_main_table(TABLE_MAIN_SHARED_SURVEY_QUESTION);
  1280. $table_course_survey_question_option = Database::get_main_table(TABLE_MAIN_SHARED_SURVEY_QUESTION_OPTION);
  1281. $stats = false;
  1282. if(Database::get_statistic_database() != ''){
  1283. $stats = true;
  1284. $table_stats_hotpots = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
  1285. $table_stats_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1286. $table_stats_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  1287. $table_stats_access = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
  1288. $table_stats_lastaccess = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
  1289. $table_stats_course_access = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  1290. $table_stats_online = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ONLINE);
  1291. $table_stats_default = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
  1292. $table_stats_downloads = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
  1293. $table_stats_links = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LINKS);
  1294. $table_stats_uploads = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_UPLOADS);
  1295. }
  1296. $sql = "SELECT * FROM $table_course WHERE code='".$code."'";
  1297. $res = api_sql_query($sql, __FILE__, __LINE__);
  1298. if (Database::num_rows($res) == 0)
  1299. {
  1300. return;
  1301. }
  1302. $this_course = Database::fetch_array($res);
  1303. $db_name = $this_course['db_name'];
  1304. CourseManager :: create_database_dump($code);
  1305. if (!CourseManager :: is_virtual_course_from_system_code($code))
  1306. {
  1307. // If this is not a virtual course, look for virtual courses that depend on this one, if any
  1308. $virtual_courses = CourseManager :: get_virtual_courses_linked_to_real_course($code);
  1309. foreach ($virtual_courses as $index => $virtual_course)
  1310. {
  1311. // Unsubscribe all classes from the virtual course
  1312. $sql = "DELETE FROM $table_course_class WHERE course_code='".$virtual_course['code']."'";
  1313. api_sql_query($sql, __FILE__, __LINE__);
  1314. // Unsubscribe all users from the virtual course
  1315. $sql = "DELETE FROM $table_course_user WHERE course_code='".$virtual_course['code']."'";
  1316. api_sql_query($sql, __FILE__, __LINE__);
  1317. // Delete the course from the sessions tables
  1318. $sql = "DELETE FROM $table_session_course WHERE course_code='".$virtual_course['code']."'";
  1319. api_sql_query($sql,__FILE__,__LINE__);
  1320. $sql = "DELETE FROM $table_session_course_user WHERE course_code='".$virtual_course['code']."'";
  1321. api_sql_query($sql,__FILE__,__LINE__);
  1322. // Delete the course from the survey tables
  1323. $sql = "DELETE FROM $table_course_survey WHERE course_code='".$virtual_course['code']."'";
  1324. api_sql_query($sql,__FILE__,__LINE__);
  1325. $sql = "DELETE FROM $table_course_survey_user WHERE db_name='".$virtual_course['db_name']."'";
  1326. api_sql_query($sql,__FILE__,__LINE__);
  1327. $sql = "DELETE FROM $table_course_survey_reminder WHERE db_name='".$virtual_course['db_name']."'";
  1328. api_sql_query($sql,__FILE__,__LINE__);
  1329. // Delete the course from the stats tables
  1330. if($stats)
  1331. {
  1332. $sql = "DELETE FROM $table_stats_hotpots WHERE exe_cours_id = '".$virtual_course['code']."'";
  1333. api_sql_query($sql,__FILE__,__LINE__);
  1334. $sql = "DELETE FROM $table_stats_attempt WHERE course_code = '".$virtual_course['code']."'";
  1335. api_sql_query($sql,__FILE__,__LINE__);
  1336. $sql = "DELETE FROM $table_stats_exercises WHERE exe_cours_id = '".$virtual_course['code']."'";
  1337. api_sql_query($sql,__FILE__,__LINE__);
  1338. $sql = "DELETE FROM $table_stats_access WHERE access_cours_code = '".$virtual_course['code']."'";
  1339. api_sql_query($sql,__FILE__,__LINE__);
  1340. $sql = "DELETE FROM $table_stats_lastaccess WHERE access_cours_code = '".$virtual_course['code']."'";
  1341. api_sql_query($sql,__FILE__,__LINE__);
  1342. $sql = "DELETE FROM $table_stats_course_access WHERE course_code = '".$virtual_course['code']."'";
  1343. api_sql_query($sql,__FILE__,__LINE__);
  1344. $sql = "DELETE FROM $table_stats_online WHERE course = '".$virtual_course['code']."'";
  1345. api_sql_query($sql,__FILE__,__LINE__);
  1346. $sql = "DELETE FROM $table_stats_default WHERE default_cours_code = '".$virtual_course['code']."'";
  1347. api_sql_query($sql,__FILE__,__LINE__);
  1348. $sql = "DELETE FROM $table_stats_downloads WHERE down_cours_id = '".$virtual_course['code']."'";
  1349. api_sql_query($sql,__FILE__,__LINE__);
  1350. $sql = "DELETE FROM $table_stats_links WHERE links_cours_id = '".$virtual_course['code']."'";
  1351. api_sql_query($sql,__FILE__,__LINE__);
  1352. $sql = "DELETE FROM $table_stats_uploads WHERE upload_cours_id = '".$virtual_course['code']."'";
  1353. api_sql_query($sql,__FILE__,__LINE__);
  1354. }
  1355. // Delete the course from the course table
  1356. $sql = "DELETE FROM $table_course WHERE code='".$virtual_course['code']."'";
  1357. api_sql_query($sql, __FILE__, __LINE__);
  1358. }
  1359. $sql = "SELECT * FROM $table_course WHERE code='".$code."'";
  1360. $res = api_sql_query($sql, __FILE__, __LINE__);
  1361. $course = Database::fetch_array($res);
  1362. if (!$_configuration['single_database'])
  1363. {
  1364. $sql = "DROP DATABASE IF EXISTS ".$course['db_name'];
  1365. api_sql_query($sql, __FILE__, __LINE__);
  1366. }
  1367. else
  1368. {
  1369. //TODO Clean the following code as currently it would probably delete another course
  1370. //similarly named, by mistake...
  1371. $db_pattern = $_configuration['table_prefix'].$course['db_name'].$_configuration['db_glue'];
  1372. $sql = "SHOW TABLES LIKE '$db_pattern%'";
  1373. $result = api_sql_query($sql, __FILE__, __LINE__);
  1374. while (list ($courseTable) = Database::fetch_array($result))
  1375. {
  1376. api_sql_query("DROP TABLE `$courseTable`", __FILE__, __LINE__);
  1377. }
  1378. }
  1379. $course_dir = api_get_path(SYS_COURSE_PATH).$course['directory'];
  1380. $garbage_dir = api_get_path(GARBAGE_PATH).$course['directory'].'_'.time();
  1381. rename($course_dir, $garbage_dir);
  1382. }
  1383. // Unsubscribe all classes from the course
  1384. $sql = "DELETE FROM $table_course_class WHERE course_code='".$code."'";
  1385. api_sql_query($sql, __FILE__, __LINE__);
  1386. // Unsubscribe all users from the course
  1387. $sql = "DELETE FROM $table_course_user WHERE course_code='".$code."'";
  1388. api_sql_query($sql, __FILE__, __LINE__);
  1389. // Delete the course from the sessions tables
  1390. $sql = "DELETE FROM $table_session_course WHERE course_code='".$code."'";
  1391. api_sql_query($sql,__FILE__,__LINE__);
  1392. $sql = "DELETE FROM $table_session_course_user WHERE course_code='".$code."'";
  1393. api_sql_query($sql,__FILE__,__LINE__);
  1394. $sql='SELECT survey_id FROM '.$table_course_survey.' WHERE course_code="'.$code.'"';
  1395. $result_surveys=api_sql_query($sql);
  1396. while($surveys=Database::fetch_array($result_surveys)){
  1397. $survey_id=$surveys[0];
  1398. $sql='DELETE FROM '.$table_course_survey_question.' WHERE survey_id="'.$survey_id.'"';
  1399. api_sql_query($sql,__FILE__,__LINE__);
  1400. $sql='DELETE FROM '.$table_course_survey_question_option.' WHERE survey_id="'.$survey_id.'"';
  1401. api_sql_query($sql,__FILE__,__LINE__);
  1402. $sql='DELETE FROM '.$table_course_survey.' WHERE survey_id="'.$survey_id.'"';
  1403. api_sql_query($sql,__FILE__,__LINE__);
  1404. }
  1405. // Delete the course from the stats tables
  1406. if($stats)
  1407. {
  1408. $sql = "DELETE FROM $table_stats_hotpots WHERE exe_cours_id = '".$code."'";
  1409. api_sql_query($sql,__FILE__,__LINE__);
  1410. $sql = "DELETE FROM $table_stats_attempt WHERE course_code = '".$code."'";
  1411. api_sql_query($sql,__FILE__,__LINE__);
  1412. $sql = "DELETE FROM $table_stats_exercises WHERE exe_cours_id = '".$code."'";
  1413. api_sql_query($sql,__FILE__,__LINE__);
  1414. $sql = "DELETE FROM $table_stats_access WHERE access_cours_code = '".$code."'";
  1415. api_sql_query($sql,__FILE__,__LINE__);
  1416. $sql = "DELETE FROM $table_stats_lastaccess WHERE access_cours_code = '".$code."'";
  1417. api_sql_query($sql,__FILE__,__LINE__);
  1418. $sql = "DELETE FROM $table_stats_course_access WHERE course_code = '".$code."'";
  1419. api_sql_query($sql,__FILE__,__LINE__);
  1420. $sql = "DELETE FROM $table_stats_online WHERE course = '".$code."'";
  1421. api_sql_query($sql,__FILE__,__LINE__);
  1422. $sql = "DELETE FROM $table_stats_default WHERE default_cours_code = '".$code."'";
  1423. api_sql_query($sql,__FILE__,__LINE__);
  1424. $sql = "DELETE FROM $table_stats_downloads WHERE down_cours_id = '".$code."'";
  1425. api_sql_query($sql,__FILE__,__LINE__);
  1426. $sql = "DELETE FROM $table_stats_links WHERE links_cours_id = '".$code."'";
  1427. api_sql_query($sql,__FILE__,__LINE__);
  1428. $sql = "DELETE FROM $table_stats_uploads WHERE upload_cours_id = '".$code."'";
  1429. api_sql_query($sql,__FILE__,__LINE__);
  1430. }
  1431. // Delete the course from the database
  1432. $sql = "DELETE FROM $table_course WHERE code='".$code."'";
  1433. api_sql_query($sql, __FILE__, __LINE__);
  1434. }
  1435. /**
  1436. * Creates a file called mysql_dump.sql in the course folder
  1437. * @param $course_code The code of the course
  1438. * @todo Implementation for single database
  1439. */
  1440. function create_database_dump($course_code)
  1441. {
  1442. global $_configuration;
  1443. if ($_configuration['single_database'])
  1444. {
  1445. return;
  1446. }
  1447. $sql_dump = '';
  1448. $table_course = Database :: get_main_table(TABLE_MAIN_COURSE);
  1449. $sql = "SELECT * FROM $table_course WHERE code = '$course_code'";
  1450. $res = api_sql_query($sql, __FILE__, __LINE__);
  1451. $course = Database::fetch_array($res);
  1452. $sql = "SHOW TABLES FROM ".$course['db_name'];
  1453. $res = api_sql_query($sql, __FILE__, __LINE__);
  1454. while ($table = Database::fetch_array($res))
  1455. {
  1456. $sql = "SELECT * FROM `".$course['db_name']."`.`".$table[0]."`";
  1457. $res3 = api_sql_query($sql, __FILE__, __LINE__);
  1458. while ($row = Database::fetch_array($res3))
  1459. {
  1460. foreach ($row as $key => $value)
  1461. {
  1462. $row[$key] = $key."='".addslashes($row[$key])."'";
  1463. }
  1464. $sql_dump .= "\nINSERT INTO $table[0] SET ".implode(', ', $row).';';
  1465. }
  1466. }
  1467. $file_name = api_get_path(SYS_COURSE_PATH).$course['directory'].'/mysql_dump.sql';
  1468. $handle = fopen($file_name, 'a+');
  1469. if($handle!==false){
  1470. fwrite($handle, $sql_dump);
  1471. fclose($handle);
  1472. }else{
  1473. //TODO trigger exception in a try-catch
  1474. }
  1475. }
  1476. function userCourseSort($user_id,$course_code){
  1477. $TABLECOURSE = Database :: get_main_table(TABLE_MAIN_COURSE);
  1478. $TABLECOURSUSER = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
  1479. $sql = 'SELECT title FROM '.$TABLECOURSE.' WHERE code="'.$course_code.'"';
  1480. $result = api_sql_query($sql, __FILE__, __LINE__);
  1481. $course_title = Database::result($result,0,0);
  1482. $sql = 'SELECT course.code as code, course.title as title, cu.sort as sort FROM '.$TABLECOURSUSER.' as cu, '.$TABLECOURSE.' as course
  1483. WHERE course.code = cu.course_code
  1484. AND user_id = "'.$user_id.'"
  1485. AND user_course_cat=0 ORDER BY cu.sort';
  1486. $result = api_sql_query($sql, __FILE__, __LINE__);
  1487. $s_course_title_precedent = '';
  1488. $counter = 0;
  1489. $b_find_course = false;
  1490. $i_course_sort = 1;
  1491. while($courses=Database::fetch_array($result)){
  1492. if($s_course_title_precedent == ''){
  1493. $s_course_title_precedent = $courses['title'];
  1494. }
  1495. if(strcasecmp($s_course_title_precedent,$course_title)<0){
  1496. $b_find_course = true;
  1497. $i_course_sort = $courses['sort'];
  1498. $s_course_code = $courses['code'];
  1499. if($counter == 0){
  1500. $sql = 'UPDATE '.$TABLECOURSUSER.' SET sort = sort+1 WHERE user_id= "'.$user_id.'" AND user_course_cat="0" AND sort > "'.$i_course_sort.'"';
  1501. $i_course_sort++;
  1502. }
  1503. else{
  1504. $sql = 'UPDATE '.$TABLECOURSUSER.' SET sort = sort+1 WHERE user_id= "'.$user_id.'" AND user_course_cat="0" AND sort >= "'.$i_course_sort.'"';
  1505. }
  1506. api_sql_query($sql, __FILE__, __LINE__);
  1507. break;
  1508. }
  1509. else{
  1510. $s_course_title_precedent = $courses['title'];
  1511. }
  1512. $counter++;
  1513. }
  1514. //We must register the course in the beginning of the list
  1515. if(Database::num_rows($result)>0 && !$b_find_course)
  1516. {
  1517. $sql_max = 'SELECT min(sort) as min_sort FROM '.$TABLECOURSUSER.' WHERE user_id="'.$user_id.'" AND user_course_cat="0"';
  1518. $result_min_sort=api_sql_query($sql_max, __FILE__, __LINE__);
  1519. $i_course_sort = Database::result($result_min_sort,0,0);
  1520. $sql = 'UPDATE '.$TABLECOURSUSER.' SET sort = sort+1 WHERE user_id= "'.$user_id.'" AND user_course_cat="0"';
  1521. api_sql_query($sql, __FILE__, __LINE__);
  1522. }
  1523. return $i_course_sort;
  1524. }
  1525. /**
  1526. * create recursively all categories as option of the select passed in paramater.
  1527. *
  1528. * @param object $select_element the quickform select where the options will be added
  1529. * @param string $category_selected_code the option value to select by default (used mainly for edition of courses)
  1530. * @param string $parent_code the parent category of the categories added (default=null for root category)
  1531. * @param string $padding the indent param (you shouldn't indicate something here)
  1532. */
  1533. function select_and_sort_categories($select_element, $category_selected_code="", $parent_code=null , $padding="")
  1534. {
  1535. $table_course_category = Database :: get_main_table(TABLE_MAIN_CATEGORY);
  1536. $sql = "SELECT code, name, auth_course_child, auth_cat_child
  1537. FROM ".$table_course_category."
  1538. WHERE parent_id ".(is_null($parent_code) ? "IS NULL" : "='".Database::escape_string($parent_code)."'")."
  1539. ORDER BY code";
  1540. $res = api_sql_query($sql, __FILE__, __LINE__);
  1541. $new_padding = $padding.str_repeat('-',3);
  1542. while ($cat = Database::fetch_array($res))
  1543. {
  1544. $params = $cat['auth_course_child'] == 'TRUE' ? '' : 'disabled';
  1545. $params .= ($cat['code'] == $category_selected_code) ? ' selected' : '';
  1546. $select_element->addOption($padding.'('.$cat['code'].') '.$cat['name'], $cat['code'], $params);
  1547. if($cat['auth_cat_child'])
  1548. {
  1549. CourseManager::select_and_sort_categories($select_element, $cat['code'], $category_selected_code, $new_padding);
  1550. }
  1551. }
  1552. }
  1553. /**
  1554. * check if course exists
  1555. * @param string course_code
  1556. * @return true if exists, false else
  1557. */
  1558. function course_exists($course_code)
  1559. {
  1560. $sql = 'SELECT 1 FROM '.Database :: get_main_table(TABLE_MAIN_COURSE).' WHERE code="'.Database::escape_string($course_code).'"';
  1561. $rs = api_sql_query($sql,__FILE__,__LINE__);
  1562. return Database::num_rows($rs);
  1563. }
  1564. } //end class CourseManager
  1565. ?>