course.lib.php 71 KB

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