course.lib.php 71 KB

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