course.lib.php 89 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762176317641765176617671768176917701771177217731774177517761777177817791780178117821783178417851786178717881789179017911792179317941795179617971798179918001801180218031804180518061807180818091810181118121813181418151816181718181819182018211822182318241825182618271828182918301831183218331834183518361837183818391840184118421843184418451846184718481849185018511852185318541855185618571858185918601861186218631864186518661867186818691870187118721873187418751876187718781879188018811882188318841885188618871888188918901891189218931894189518961897189818991900190119021903190419051906190719081909191019111912191319141915191619171918191919201921192219231924192519261927192819291930193119321933193419351936193719381939194019411942194319441945194619471948194919501951195219531954195519561957195819591960196119621963196419651966196719681969197019711972197319741975197619771978197919801981198219831984198519861987198819891990199119921993199419951996199719981999200020012002200320042005200620072008200920102011201220132014201520162017201820192020202120222023202420252026202720282029203020312032203320342035203620372038203920402041204220432044204520462047204820492050205120522053205420552056205720582059206020612062206320642065206620672068206920702071207220732074207520762077207820792080208120822083208420852086208720882089209020912092209320942095209620972098209921002101210221032104210521062107210821092110211121122113211421152116211721182119212021212122212321242125212621272128212921302131213221332134213521362137213821392140214121422143214421452146214721482149215021512152215321542155215621572158215921602161216221632164216521662167216821692170217121722173217421752176217721782179218021812182
  1. <?php //$id: $
  2. /* For licensing terms, see /dokeos_license.txt
  3. Copyright (c) Roan Embrechts, Vrije Universiteit Brussel
  4. Copyright (c) Bart Mollet, Hogeschool Gent
  5. Copyright (c) Yannick Warnier, Dokeos SPRL
  6. */
  7. /**
  8. ==============================================================================
  9. * This is the course library for Dokeos.
  10. *
  11. * All main course functions should be placed here.
  12. * Many functions of this library deal with providing support for
  13. * virtual/linked/combined courses (this was already used in several universities
  14. * but not available in standard Dokeos).
  15. *
  16. * The implementation changed, initially a course was a real course
  17. * if target_course_code was 0 , this was changed to NULL.
  18. * There are probably some places left with the wrong code.
  19. *
  20. * @package dokeos.library
  21. ==============================================================================
  22. */
  23. /*
  24. ==============================================================================
  25. DOCUMENTATION
  26. (list not up to date, you can auto generate documentation with phpDocumentor)
  27. CourseManager::get_real_course_code_select_html($element_name, $has_size=true, $only_current_user_courses=true)
  28. CourseManager::check_parameter($parameter, $error_message)
  29. CourseManager::check_parameter_or_fail($parameter, $error_message)
  30. CourseManager::is_existing_course_code($wanted_course_code)
  31. CourseManager::get_real_course_list()
  32. CourseManager::get_virtual_course_list()
  33. GENERAL COURSE FUNCTIONS
  34. CourseManager::get_access_settings($course_code)
  35. CourseManager::set_course_tool_visibility($tool_table_id, $visibility)
  36. CourseManager::get_user_in_course_status($user_id, $course_code)
  37. CourseManager::add_user_to_course($user_id, $course_code)
  38. CourseManager::get_virtual_course_info($real_course_code)
  39. CourseManager::is_virtual_course_from_visual_code($visual_code)
  40. CourseManager::is_virtual_course_from_system_code($system_code)
  41. CourseManager::get_virtual_courses_linked_to_real_course($real_course_code)
  42. CourseManager::get_list_of_virtual_courses_for_specific_user_and_real_course($user_id, $real_course_code)
  43. CourseManager::has_virtual_courses_from_code($real_course_code, $user_id)
  44. CourseManager::get_target_of_linked_course($virtual_course_code)
  45. TITLE AND CODE FUNCTIONS
  46. CourseManager::determine_course_title_from_course_info($user_id, $course_info)
  47. CourseManager::create_combined_name($user_is_registered_in_real_course, $real_course_name, $virtual_course_list)
  48. CourseManager::create_combined_code($user_is_registered_in_real_course, $real_course_code, $virtual_course_list)
  49. USER FUNCTIONS
  50. CourseManager::get_real_course_list_of_user_as_course_admin($user_id)
  51. CourseManager::get_course_list_of_user_as_course_admin($user_id)
  52. CourseManager::is_user_subscribed_in_course($user_id, $course_code)
  53. CourseManager::is_user_subscribed_in_real_or_linked_course($user_id, $course_code)
  54. CourseManager::get_user_list_from_course_code($course_code)
  55. CourseManager::get_real_and_linked_user_list($course_code);
  56. GROUP FUNCTIONS
  57. CourseManager::get_group_list_of_course($course_code)
  58. CREATION FUNCTIONS
  59. CourseManager::attempt_create_virtual_course($real_course_code, $course_title, $wanted_course_code, $course_language, $course_category)
  60. ==============================================================================
  61. */
  62. /*
  63. ==============================================================================
  64. INIT SECTION
  65. ==============================================================================
  66. */
  67. /*
  68. -----------------------------------------------------------
  69. Configuration files
  70. -----------------------------------------------------------
  71. */
  72. require_once api_get_path(CONFIGURATION_PATH).'add_course.conf.php';
  73. /*
  74. -----------------------------------------------------------
  75. Libraries
  76. we assume main_api is also included...
  77. -----------------------------------------------------------
  78. */
  79. require_once api_get_path(LIBRARY_PATH).'database.lib.php';
  80. require_once api_get_path(LIBRARY_PATH).'add_course.lib.inc.php';
  81. /*
  82. -----------------------------------------------------------
  83. Constants
  84. -----------------------------------------------------------
  85. */
  86. //LOGIC: course visibility and registration settings
  87. /*
  88. COURSE VISIBILITY
  89. MAPPING OLD SETTINGS TO NEW SETTINGS
  90. -----------------------
  91. NOT_VISIBLE_NO_SUBSCRIPTION_ALLOWED
  92. --> COURSE_VISIBILITY_REGISTERED, SUBSCRIBE_NOT_ALLOWED
  93. NOT_VISIBLE_SUBSCRIPTION_ALLOWED
  94. --> COURSE_VISIBILITY_REGISTERED, SUBSCRIBE_ALLOWED
  95. VISIBLE_SUBSCRIPTION_ALLOWED
  96. --> COURSE_VISIBILITY_OPEN_PLATFORM, SUBSCRIBE_ALLOWED
  97. VISIBLE_NO_SUBSCRIPTION_ALLOWED
  98. --> COURSE_VISIBILITY_OPEN_PLATFORM, SUBSCRIBE_NOT_ALLOWED
  99. */
  100. //OLD SETTINGS
  101. define('NOT_VISIBLE_NO_SUBSCRIPTION_ALLOWED', 0);
  102. define('NOT_VISIBLE_SUBSCRIPTION_ALLOWED', 1);
  103. define('VISIBLE_SUBSCRIPTION_ALLOWED', 2);
  104. define('VISIBLE_NO_SUBSCRIPTION_ALLOWED', 3);
  105. /*
  106. -----------------------------------------------------------
  107. Variables
  108. -----------------------------------------------------------
  109. */
  110. $TABLECOURSE = Database::get_main_table(TABLE_MAIN_COURSE);
  111. $TABLECOURSDOMAIN = Database::get_main_table(TABLE_MAIN_CATEGORY);
  112. $TABLEUSER = Database::get_main_table(TABLE_MAIN_USER);
  113. $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  114. $TABLEANNOUNCEMENTS = 'announcement';
  115. $coursesRepositories = $_configuration['root_sys'];
  116. /*
  117. ==============================================================================
  118. CourseManager CLASS
  119. ==============================================================================
  120. */
  121. /**
  122. * @package dokeos.library
  123. */
  124. class CourseManager {
  125. /**
  126. * Returns all the information of a given coursecode
  127. * @param string $course_code, the course code
  128. * @return an array with all the fields of the course table
  129. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  130. */
  131. public static function get_course_information($course_code) {
  132. return Database::fetch_array(Database::query(
  133. "SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  134. WHERE code='".Database::escape_string($course_code)."'", __FILE__, __LINE__)
  135. );
  136. }
  137. /**
  138. * Returns a list of courses. Should work with quickform syntax
  139. * @param integer Offset (from the 7th = '6'). Optional.
  140. * @param integer Number of results we want. Optional.
  141. * @param string The column we want to order it by. Optional, defaults to first column.
  142. * @param string The direction of the order (ASC or DESC). Optional, defaults to ASC.
  143. * @param string The visibility of the course, or all by default.
  144. * @param string If defined, only return results for which the course *title* begins with this string
  145. */
  146. public static function get_courses_list($from = 0, $howmany = 0, $orderby = 1, $orderdirection = 'ASC', $visibility = -1, $startwith = '') {
  147. $sql = "SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE)." ";
  148. if (!empty($startwith)) {
  149. $sql .= "WHERE LIKE title '".Database::escape_string($startwith)."%' ";
  150. if ($visibility !== -1 && $visibility == strval(intval($visibility))) {
  151. $sql .= " AND visibility = $visibility ";
  152. }
  153. } else {
  154. $sql .= "WHERE 1 ";
  155. if ($visibility !== -1 && $visibility == strval(intval($visibility))) {
  156. $sql .= " AND visibility = $visibility ";
  157. }
  158. }
  159. if (!empty($orderby)) {
  160. $sql .= " ORDER BY ".Database::escape_string($orderby)." ";
  161. } else {
  162. $sql .= " ORDER BY 1 ";
  163. }
  164. if (!in_array($orderdirection, array('ASC', 'DESC'))) {
  165. $sql .= 'ASC';
  166. } else {
  167. $sql .= Database::escape_string($orderdirection);
  168. }
  169. if (!empty($howmany) && is_int($howmany) and $howmany > 0) {
  170. $sql .= ' LIMIT '.Database::escape_string($howmany);
  171. } else {
  172. $sql .= ' LIMIT 1000000'; //virtually no limit
  173. }
  174. if (!empty($from)) {
  175. $from = intval($from);
  176. $sql .= ' OFFSET '.Database::escape_string($from);
  177. } else {
  178. $sql .= ' OFFSET 0';
  179. }
  180. return Database::store_result(Database::query($sql, __FILE__, __LINE__));
  181. }
  182. /**
  183. * Returns the access settings of the course:
  184. * which visibility;
  185. * wether subscribing is allowed;
  186. * wether unsubscribing is allowed.
  187. *
  188. * @param string $course_code, the course code
  189. * @todo for more consistency: use course_info call from database API
  190. * @return an array with int fields "visibility", "subscribe", "unsubscribe"
  191. */
  192. public static function get_access_settings($course_code) {
  193. return Database::fetch_array(Database::query(
  194. "SELECT visibility, subscribe, unsubscribe from ".Database::get_main_table(TABLE_MAIN_COURSE)."
  195. WHERE code = '".Database::escape_string($course_code)."'", __FILE__, __LINE__)
  196. );
  197. }
  198. /**
  199. * Returns the status of a user in a course, which is COURSEMANAGER or STUDENT.
  200. * @param int User ID
  201. * @param string Course code
  202. * @return int the status of the user in that course
  203. */
  204. public static function get_user_in_course_status($user_id, $course_code) {
  205. $result = Database::fetch_array(Database::query(
  206. "SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  207. WHERE course_code = '".Database::escape_string($course_code)."' AND user_id = ".Database::escape_string($user_id), __FILE__, __LINE__)
  208. );
  209. return $result['status'];
  210. }
  211. /**
  212. * Unsubscribe one or more users from a course
  213. * @param int|array $user_id
  214. * @param string $course_code
  215. */
  216. public static function unsubscribe_user($user_id, $course_code) {
  217. if (!is_array($user_id)) {
  218. $user_id = array($user_id);
  219. }
  220. if (count($user_id) == 0) {
  221. return;
  222. }
  223. $table_user = Database :: get_main_table(TABLE_MAIN_USER);
  224. //Cleaning the $user_id variable
  225. if (is_array($user_id)) {
  226. $new_user_id_list = array();
  227. foreach($user_id as $my_user_id) {
  228. $new_user_id_list[]= intval($my_user_id);
  229. }
  230. $new_user_id_list = array_filter($new_user_id_list);
  231. $user_ids = implode(',', $new_user_id_list);
  232. } else {
  233. $user_ids = intval($user_id);
  234. }
  235. $course_code = Database::escape_string($course_code);
  236. $course = Database::fetch_object(Database::query("SELECT db_name FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  237. WHERE code = '".$course_code."'", __FILE__, __LINE__));
  238. // Unsubscribe user from all groups in the course.
  239. Database::query("DELETE FROM ".Database::get_course_table(TABLE_GROUP_USER, $course->db_name)."
  240. WHERE user_id IN (".$user_ids.")", __FILE__, __LINE__);
  241. Database::query("DELETE FROM ".Database::get_course_table(TABLE_GROUP_TUTOR, $course->db_name)."
  242. WHERE user_id IN (".$user_ids.")", __FILE__, __LINE__);
  243. // Erase user student publications (works) in the course - by André Boivin
  244. //@todo field student_publication.author should be the user id
  245. $table_course_user_publication = Database :: get_course_table(TABLE_STUDENT_PUBLICATION, $course->db_name);
  246. $sqlu = "SELECT * FROM $table_user WHERE user_id IN (".$user_ids.")";
  247. $resu = Database::query($sqlu, __FILE__, __LINE__);
  248. $username = Database::fetch_array($resu,'ASSOC');
  249. $userfirstname = $username['firstname'];
  250. $userlastname = $username['lastname'];
  251. $publication_name = $userfirstname.' '.$userlastname ;
  252. $sql = "DELETE FROM $table_course_user_publication WHERE author = '".Database::escape_string($publication_name)."'";
  253. Database::query($sql, __FILE__, __LINE__);
  254. // Unsubscribe user from all blogs in the course.
  255. Database::query("DELETE FROM ".Database::get_course_table(TABLE_BLOGS_REL_USER, $course->db_name)."
  256. WHERE user_id IN (".$user_ids.")", __FILE__, __LINE__);
  257. Database::query("DELETE FROM ".Database::get_course_table(TABLE_BLOGS_TASKS_REL_USER, $course->db_name)."
  258. WHERE user_id IN (".$user_ids.")", __FILE__, __LINE__);
  259. // Unsubscribe user from the course.
  260. if (!empty($_SESSION['id_session'])) { // We suppose the session is safe!
  261. // Delete in table session_rel_course_rel_user
  262. $my_session_id = intval ($_SESSION['id_session']);
  263. Database::query("DELETE FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  264. WHERE id_session ='".$my_session_id."'
  265. AND course_code = '".Database::escape_string($_SESSION['_course']['id'])."'
  266. AND id_user IN ($user_ids)", __FILE__, __LINE__);
  267. foreach ($user_id as $uid) {
  268. // check if a user is register in the session with other course
  269. $sql = "SELECT id_user FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)." WHERE id_session='$my_session_id' AND id_user='$uid'";
  270. $rs = Database::query($sql, __FILE__, __LINE__);
  271. if (Database::num_rows($rs) == 0) {
  272. // Delete in table session_rel_user
  273. Database::query("DELETE FROM ".Database::get_main_table(TABLE_MAIN_SESSION_USER)."
  274. WHERE id_session ='".$my_session_id."'
  275. AND id_user='$uid'", __FILE__, __LINE__);
  276. }
  277. }
  278. // Update the table session
  279. $row = Database::fetch_array(Database::query("SELECT COUNT(*) FROM ".Database::get_main_table(TABLE_MAIN_SESSION_USER)."
  280. WHERE id_session = '".$my_session_id."'", __FILE__, __LINE__));
  281. $count = $row[0]; // number of users by session
  282. $result = Database::query("UPDATE ".Database::get_main_table(TABLE_MAIN_SESSION)." SET nbr_users = '$count'
  283. WHERE id = '".$my_session_id."'", __FILE__, __LINE__);
  284. } else {
  285. Database::query("DELETE FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  286. WHERE user_id IN (".$user_ids.") AND course_code = '".$course_code."'", __FILE__, __LINE__);
  287. // add event to system log
  288. $time = time();
  289. $user_id = api_get_user_id();
  290. event_system(LOG_UNSUBSCRIBE_USER_FROM_COURSE, LOG_COURSE_CODE, $course_code, $time, $user_id);
  291. }
  292. }
  293. /**
  294. * Subscribe a user to a course. No checks are performed here to see if
  295. * course subscription is allowed.
  296. * @param int User ID
  297. * @param string Course code
  298. * @param int Status (STUDENT, COURSEMANAGER, COURSE_ADMIN, NORMAL_COURSE_MEMBER)
  299. * @return bool True on success, false on failure
  300. * @see add_user_to_course
  301. */
  302. public static function subscribe_user($user_id, $course_code, $status = STUDENT) {
  303. if ($user_id != strval(intval($user_id))) {
  304. return false; //detected possible SQL injection
  305. }
  306. $course_code = Database::escape_string($course_code);
  307. if (empty ($user_id) || empty ($course_code)) {
  308. return false;
  309. }
  310. $status = ($status == STUDENT || $status == COURSEMANAGER) ? $status : STUDENT;
  311. $role_id = ($status == COURSEMANAGER) ? COURSE_ADMIN : NORMAL_COURSE_MEMBER;
  312. // A preliminary check whether the user has bben already registered on the platform.
  313. if (Database::num_rows(@Database::query("SELECT status FROM ".Database::get_main_table(TABLE_MAIN_USER)."
  314. WHERE user_id = '$user_id' ", __FILE__, __LINE__)) == 0) {
  315. return false; // The user has not been registered to the platform.
  316. }
  317. // Check whether the user has not been already subscribed to the course.
  318. if (empty($_SESSION['id_session'])) {
  319. if (Database::num_rows(@Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  320. WHERE user_id = '$user_id' AND course_code = '$course_code'", __FILE__, __LINE__)) > 0) {
  321. return false; // The user has been already subscribed to the course.
  322. }
  323. }
  324. if (!empty($_SESSION['id_session'])) {
  325. // Check whether the user has not already been stored in the session_rel_course_user table
  326. if (Database::num_rows(@Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  327. WHERE course_code = '".$_SESSION['_course']['id']."'
  328. AND id_session ='".$_SESSION['id_session']."'
  329. AND id_user = '".$user_id."'", __FILE__, __LINE__)) > 0) {
  330. return false;
  331. }
  332. // check if the user is registered in the session with other course
  333. $sql = "SELECT id_user FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)." WHERE id_session='".$_SESSION['id_session']."' AND id_user='$user_id'";
  334. $rs = Database::query($sql, __FILE__, __LINE__);
  335. if (Database::num_rows($rs) == 0) {
  336. // Check whether the user has not already been stored in the session_rel_user table
  337. if (Database::num_rows(@Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_SESSION_USER)."
  338. WHERE id_session ='".$_SESSION['id_session']."'
  339. AND id_user = '".$user_id."'", __FILE__, __LINE__)) > 0) {
  340. return false;
  341. }
  342. }
  343. // Add him/her in the table session_rel_course_rel_user
  344. @Database::query("INSERT INTO ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  345. SET id_session ='".$_SESSION['id_session']."',
  346. course_code = '".$_SESSION['_course']['id']."',
  347. id_user = '".$user_id."'", __FILE__, __LINE__);
  348. // Add him/her in the table session_rel_user
  349. @Database::query("INSERT INTO ".Database::get_main_table(TABLE_MAIN_SESSION_USER)."
  350. SET id_session ='".$_SESSION['id_session']."',
  351. id_user = '".$user_id."'", __FILE__, __LINE__);
  352. // Update the table session
  353. $row = Database::fetch_array(@Database::query("SELECT COUNT(*) FROM ".Database::get_main_table(TABLE_MAIN_SESSION_USER)." WHERE id_session = '".$_SESSION['id_session']."'", __FILE__, __LINE__));
  354. $count = $row[0]; // number of users by session
  355. $result = @Database::query("UPDATE ".Database::get_main_table(TABLE_MAIN_SESSION)." SET nbr_users = '$count' WHERE id = '".$_SESSION['id_session']."'", __FILE__, __LINE__);
  356. } else {
  357. $course_sort = self::userCourseSort($user_id, $course_code);
  358. $result = @Database::query("INSERT INTO ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  359. SET course_code = '$course_code',
  360. user_id = '$user_id',
  361. status = '".$status."',
  362. sort = '". ($course_sort)."'", __FILE__, __LINE__);
  363. // Add event to the system log.
  364. $time = time();
  365. $user_id = api_get_user_id();
  366. event_system(LOG_SUBSCRIBE_USER_TO_COURSE, LOG_COURSE_CODE, $course_code, $time, $user_id);
  367. }
  368. return (bool)$result;
  369. }
  370. /**
  371. * Subscribe a user $user_id to a course $course_code.
  372. * @author Hugues Peeters
  373. * @author Roan Embrechts
  374. *
  375. * @param int $user_id the id of the user
  376. * @param string $course_code the course code
  377. * @param string $status (optional) The user's status in the course
  378. *
  379. * @return boolean true if subscription succeeds, boolean false otherwise.
  380. */
  381. public static function add_user_to_course($user_id, $course_code, $status = STUDENT) {
  382. $user_table = Database::get_main_table(TABLE_MAIN_USER);
  383. $course_table = Database::get_main_table(TABLE_MAIN_COURSE);
  384. $course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  385. $status = ($status == STUDENT || $status == COURSEMANAGER) ? $status : STUDENT;
  386. if (empty($user_id) || empty($course_code) || ($user_id != strval(intval($user_id)))) {
  387. return false;
  388. }
  389. $course_code = Database::escape_string($course_code);
  390. // Check in advance whether the user has already been registered on the platform.
  391. if (Database::num_rows(Database::query("SELECT status FROM ".$user_table." WHERE user_id = '$user_id' ", __FILE__, __LINE__)) == 0) {
  392. return false; // Thehe user has not been registered to the platform.
  393. }
  394. // Check whether the user has already been subscribed to this course.
  395. if (Database::num_rows(Database::query("SELECT * FROM ".$course_user_table." WHERE user_id = '$user_id' AND course_code = '$course_code'", __FILE__, __LINE__)) > 0) {
  396. return false; // The user has been subscribed to the course.
  397. }
  398. // Check in advance whether subscription is allowed or not for this course.
  399. if (Database::num_rows(Database::query("SELECT code, visibility FROM ".$course_table." WHERE code = '$course_code' AND subscribe = '".SUBSCRIBE_NOT_ALLOWED."'", __FILE__, __LINE__)) > 0) {
  400. return false; // Subscription is not allowed for this course.
  401. }
  402. // Ok, subscribe the user.
  403. $max_sort = api_max_sort_value('0', $user_id);
  404. return (bool)Database::query("INSERT INTO ".$course_user_table."
  405. SET course_code = '$course_code',
  406. user_id = '$user_id',
  407. status = '".$status."',
  408. sort = '". ($max_sort + 1)."'", __FILE__, __LINE__);
  409. }
  410. /**
  411. * Checks wether a parameter exists.
  412. * If it doesn't, the function displays an error message.
  413. *
  414. * @return true if parameter is set and not empty, false otherwise
  415. * @todo move function to better place, main_api ?
  416. */
  417. public static function check_parameter($parameter, $error_message) {
  418. if (empty($parameter)) {
  419. Display::display_normal_message($error_message);
  420. return false;
  421. }
  422. return true;
  423. }
  424. /**
  425. * Lets the script die when a parameter check fails.
  426. * @todo move function to better place, main_api ?
  427. */
  428. public static function check_parameter_or_fail($parameter, $error_message) {
  429. if (!self::check_parameter($parameter, $error_message)) {
  430. die();
  431. }
  432. }
  433. /**
  434. * @return true if there already are one or more courses
  435. * with the same code OR visual_code (visualcode), false otherwise
  436. */
  437. // TODO: course_code_exists() is a better name.
  438. public static function is_existing_course_code($wanted_course_code) {
  439. $wanted_course_code = Database::escape_string($wanted_course_code);
  440. $result = Database::fetch_array(Database::query("SELECT COUNT(*) as number FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."WHERE code = '$wanted_course_code' OR visual_code = '$wanted_course_code'", __FILE__, __LINE__));
  441. return $result['number'] > 0;
  442. }
  443. /**
  444. * @return an array with the course info of all real courses on the platform
  445. */
  446. public static function get_real_course_list() {
  447. $sql_result = Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE)." WHERE target_course_code IS NULL", __FILE__, __LINE__);
  448. while ($result = Database::fetch_array($sql_result)) {
  449. $real_course_list[] = $result;
  450. }
  451. return $real_course_list;
  452. }
  453. /**
  454. * @return an array with the course info of all virtual courses on the platform
  455. */
  456. public static function get_virtual_course_list() {
  457. $sql_result = Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE)." WHERE target_course_code IS NOT NULL", __FILE__, __LINE__);
  458. while ($result = Database::fetch_array($sql_result)) {
  459. $virtual_course_list[] = $result;
  460. }
  461. return $virtual_course_list;
  462. }
  463. /**
  464. * @return an array with the course info of the real courses of which
  465. * the current user is course admin
  466. */
  467. public static function get_real_course_list_of_user_as_course_admin($user_id) {
  468. if ($user_id != strval(intval($user_id))) {
  469. return array();
  470. }
  471. $sql_result = Database::query("SELECT *
  472. FROM ".Database::get_main_table(TABLE_MAIN_COURSE)." course
  473. LEFT JOIN ".Database::get_main_table(TABLE_MAIN_COURSE_USER)." course_user
  474. ON course.code = course_user.course_code
  475. WHERE course.target_course_code IS NULL
  476. AND course_user.user_id = '$user_id'
  477. AND course_user.status = '1'", __FILE__, __LINE__);
  478. while ($result = Database::fetch_array($sql_result)) {
  479. $result_array[] = $result;
  480. }
  481. return $result_array;
  482. }
  483. /**
  484. * @return an array with the course info of all the courses (real and virtual) of which
  485. * the current user is course admin
  486. */
  487. public static function get_course_list_of_user_as_course_admin($user_id) {
  488. if ($user_id != strval(intval($user_id))) {
  489. return array();
  490. }
  491. $sql_result = Database::query("SELECT *
  492. FROM ".Database::get_main_table(TABLE_MAIN_COURSE)." course
  493. LEFT JOIN ".Database::get_main_table(TABLE_MAIN_COURSE_USER)." course_user
  494. ON course.code = course_user.course_code
  495. WHERE course_user.user_id = '$user_id'
  496. AND course_user.status = '1'", __FILE__, __LINE__);
  497. while ($result = Database::fetch_array($sql_result)) {
  498. $result_array[] = $result;
  499. }
  500. return $result_array;
  501. }
  502. /**
  503. * Find out for which courses the user is registered and determine a visual course code and course title from that.
  504. * Takes virtual courses into account
  505. *
  506. * Default case: the name and code stay what they are.
  507. *
  508. * Scenarios:
  509. * - User is registered in real course and virtual courses; name / code become a mix of all
  510. * - User is registered in real course only: name stays that of real course
  511. * - User is registered in virtual course only: name becomes that of virtual course
  512. * - user is not registered to any of the real/virtual courses: name stays that of real course
  513. * (I'm not sure about the last case, but this seems not too bad)
  514. *
  515. * @author Roan Embrechts
  516. * @param $user_id, the id of the user
  517. * @param $course_info, an array with course info that you get using Database::get_course_info($course_system_code);
  518. * @return an array with indices
  519. * $return_result['title'] - the course title of the combined courses
  520. * $return_result['code'] - the course code of the combined courses
  521. */
  522. public static function determine_course_title_from_course_info($user_id, $course_info) {
  523. if ($user_id != strval(intval($user_id))) {
  524. return array();
  525. }
  526. $real_course_id = $course_info['system_code'];
  527. $real_course_info = Database::get_course_info($real_course_id);
  528. $real_course_name = $real_course_info['title'];
  529. $real_course_visual_code = $real_course_info['visual_code'];
  530. $real_course_real_code = Database::escape_string($course_info['system_code']);
  531. //is the user registered in the real course?
  532. $result = Database::fetch_array(Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  533. WHERE user_id = '$user_id' AND course_code = '$real_course_real_code'", __FILE__, __LINE__));
  534. $user_is_registered_in_real_course = !empty($result);
  535. //get a list of virtual courses linked to the current real course and to which the current user is subscribed
  536. $user_subscribed_virtual_course_list = self::get_list_of_virtual_courses_for_specific_user_and_real_course($user_id, $real_course_id);
  537. $virtual_courses_exist = count($user_subscribed_virtual_course_list) > 0;
  538. //now determine course code and name
  539. if ($user_is_registered_in_real_course && $virtual_courses_exist) {
  540. $course_info['name'] = self::create_combined_name($user_is_registered_in_real_course, $real_course_name, $user_subscribed_virtual_course_list);
  541. $course_info['official_code'] = self::create_combined_code($user_is_registered_in_real_course, $real_course_visual_code, $user_subscribed_virtual_course_list);
  542. }
  543. elseif ($user_is_registered_in_real_course) {
  544. //course name remains real course name
  545. $course_info['name'] = $real_course_name;
  546. $course_info['official_code'] = $real_course_visual_code;
  547. }
  548. elseif ($virtual_courses_exist) {
  549. $course_info['name'] = self::create_combined_name($user_is_registered_in_real_course, $real_course_name, $user_subscribed_virtual_course_list);
  550. $course_info['official_code'] = self::create_combined_code($user_is_registered_in_real_course, $real_course_visual_code, $user_subscribed_virtual_course_list);
  551. } else {
  552. //course name remains real course name
  553. $course_info['name'] = $real_course_name;
  554. $course_info['official_code'] = $real_course_visual_code;
  555. }
  556. $return_result['title'] = $course_info['name'];
  557. $return_result['code'] = $course_info['official_code'];
  558. return $return_result;
  559. }
  560. /**
  561. * Create a course title based on all real and virtual courses the user is registered in.
  562. * @param boolean $user_is_registered_in_real_course
  563. * @param string $real_course_name, the title of the real course
  564. * @param array $virtual_course_list, the list of virtual courses
  565. */
  566. public static function create_combined_name($user_is_registered_in_real_course, $real_course_name, $virtual_course_list) {
  567. $complete_course_name = array();
  568. if ($user_is_registered_in_real_course) {
  569. // Add the real name to the result.
  570. $complete_course_name[] = $real_course_name;
  571. }
  572. // Add course titles of all virtual courses.
  573. foreach ($virtual_course_list as $current_course) {
  574. $complete_course_name[] = $current_course['title'];
  575. }
  576. // 'CombinedCourse' is from course_home language file.
  577. return (($user_is_registered_in_real_course || count($virtual_course_list) > 1) ? get_lang('CombinedCourse').' ' : '').implode(' &amp; ', $complete_course_name);
  578. }
  579. /**
  580. * Create a course code based on all real and virtual courses the user is registered in.
  581. */
  582. public static function create_combined_code($user_is_registered_in_real_course, $real_course_code, $virtual_course_list) {
  583. $complete_course_code = array();
  584. if ($user_is_registered_in_real_course) {
  585. // Add the real code to the result
  586. $complete_course_code[] = $real_course_code;
  587. }
  588. // Add codes of all virtual courses.
  589. foreach ($virtual_course_list as $current_course) {
  590. $complete_course_code[] = $current_course['visual_code'];
  591. }
  592. return implode(' &amp; ', $complete_course_code);
  593. }
  594. /**
  595. * Return course info array of virtual course
  596. *
  597. * Note this is different from getting information about a real course!
  598. *
  599. * @param $real_course_code, the id of the real course which the virtual course is linked to
  600. */
  601. public static function get_virtual_course_info($real_course_code) {
  602. $sql_result = Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  603. WHERE target_course_code = '".Database::escape_string($real_course_code)."'", __FILE__, __LINE__);
  604. $result = array();
  605. while ($virtual_course = Database::fetch_array($sql_result)) {
  606. $result[] = $virtual_course;
  607. }
  608. return $result;
  609. }
  610. /**
  611. * @param string $system_code, the system code of the course
  612. * @return true if the course is a virtual course, false otherwise
  613. */
  614. public static function is_virtual_course_from_system_code($system_code) {
  615. $result = Database::fetch_array(Database::query("SELECT target_course_code FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  616. WHERE code = '".Database::escape_string($system_code)."'", __FILE__, __LINE__));
  617. return !empty($result['target_course_code']);
  618. }
  619. /**
  620. * Returns whether the course code given is a visual code
  621. * @param string Visual course code
  622. * @return true if the course is a virtual course, false otherwise
  623. */
  624. public static function is_virtual_course_from_visual_code($visual_code) {
  625. $result = Database::fetch_array(Database::query("SELECT target_course_code FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  626. WHERE visual_code = '".Database::escape_string($visual_code)."'", __FILE__, __LINE__));
  627. return !empty($result['target_course_code']);
  628. }
  629. /**
  630. * @return true if the real course has virtual courses that the user is subscribed to, false otherwise
  631. */
  632. public static function has_virtual_courses_from_code($real_course_code, $user_id) {
  633. return count(self::get_list_of_virtual_courses_for_specific_user_and_real_course($user_id, $real_course_code)) > 0;
  634. }
  635. /**
  636. * Return an array of arrays, listing course info of all virtual course
  637. * linked to the real course ID $real_course_code
  638. *
  639. * @param string The id of the real course which the virtual courses are linked to
  640. * @return array List of courses details
  641. */
  642. public static function get_virtual_courses_linked_to_real_course($real_course_code) {
  643. $sql_result = Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  644. WHERE target_course_code = '".Database::get_main_table(TABLE_MAIN_COURSE)."'", __FILE__, __LINE__);
  645. $result_array = array();
  646. while ($result = Database::fetch_array($sql_result)) {
  647. $result_array[] = $result;
  648. }
  649. return $result_array;
  650. }
  651. /**
  652. * This function returns the course code of the real course
  653. * to which a virtual course is linked.
  654. *
  655. * @param the course code of the virtual course
  656. * @return the course code of the real course
  657. */
  658. public static function get_target_of_linked_course($virtual_course_code) {
  659. //get info about the virtual course
  660. $result = Database::fetch_array(Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  661. WHERE code = '".Database::escape_string($virtual_course_code)."'", __FILE__, __LINE__));
  662. return $result['target_course_code'];
  663. }
  664. /*
  665. ==============================================================================
  666. USER FUNCTIONS
  667. ==============================================================================
  668. */
  669. /**
  670. * Return course info array of virtual course
  671. *
  672. * @param $user_id, the id (int) of the user
  673. * @param $course_info, array with info about the course (comes from course table)
  674. *
  675. * @return true if the user is registered in the course, false otherwise
  676. */
  677. public static function is_user_subscribed_in_course($user_id, $course_code, $in_a_session = false) {
  678. $user_id = intval($user_id);
  679. $course_code = Database::escape_string($course_code);
  680. $result = Database::fetch_array(Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  681. WHERE user_id = $user_id AND course_code = '$course_code'", __FILE__, __LINE__));
  682. if (!empty($result)) {
  683. return true; // The user has been registered in this course.
  684. }
  685. if (!$in_a_session) {
  686. return false; // The user has not been registered in this course.
  687. }
  688. if (Database::num_rows(Database::query('SELECT 1 FROM '.Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER).
  689. ' WHERE id_user = '.$user_id.' AND course_code="'.$course_code.'"', __FILE__, __LINE__)) > 0) {
  690. return true;
  691. }
  692. if (Database::num_rows(Database::query('SELECT 1 FROM '.Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER).
  693. ' WHERE id_user = '.$user_id.' AND status=2 AND course_code="'.$course_code.'"', __FILE__, __LINE__)) > 0) {
  694. return true;
  695. }
  696. if (Database::num_rows(Database::query('SELECT 1 FROM '.Database::get_main_table(TABLE_MAIN_SESSION).
  697. ' WHERE id='.intval($_SESSION['id_session']).' AND id_coach='.$user_id, __FILE__, __LINE__)) > 0) {
  698. return true;
  699. }
  700. return false;
  701. }
  702. /**
  703. * Is the user a teacher in the given course?
  704. *
  705. * @param $user_id, the id (int) of the user
  706. * @param $course_code, the course code
  707. *
  708. * @return true if the user is a teacher in the course, false otherwise
  709. */
  710. public static function is_course_teacher($user_id, $course_code) {
  711. if ($user_id != strval(intval($user_id))) {
  712. return false;
  713. }
  714. $sql_result = Database::query('SELECT status FROM '.Database::get_main_table(TABLE_MAIN_COURSE_USER).
  715. ' WHERE course_code="'.Database::escape_string($course_code).'" and user_id="'.$user_id.'"', __FILE__, __LINE__);
  716. if (Database::num_rows($sql_result) > 0) {
  717. return Database::result($sql_result, 0, 'status') == 1;
  718. }
  719. return false;
  720. }
  721. /**
  722. * Is the user subscribed in the real course or linked courses?
  723. *
  724. * @param int the id of the user
  725. * @param array info about the course (comes from course table, see database lib)
  726. *
  727. * @return true if the user is registered in the real course or linked courses, false otherwise
  728. */
  729. public static function is_user_subscribed_in_real_or_linked_course ($user_id, $course_code, $session_id = '') {
  730. if ($user_id != strval(intval($user_id))) {
  731. return false;
  732. }
  733. $course_code = Database::escape_string($course_code);
  734. if ($session_id == '') {
  735. $result = Database::fetch_array(Database::query("SELECT *
  736. FROM ".Database::get_main_table(TABLE_MAIN_COURSE)." course
  737. LEFT JOIN ".Database::get_main_table(TABLE_MAIN_COURSE_USER)." course_user
  738. ON course.code = course_user.course_code
  739. WHERE course_user.user_id = '$user_id' AND ( course.code = '$course_code' OR target_course_code = '$course_code')", __FILE__, __LINE__));
  740. return !empty($result);
  741. }
  742. // From here we trust session id.
  743. // Is he/she subscribed to the session's course?
  744. // A user?
  745. if (Database::num_rows(Database::query("SELECT id_user
  746. FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  747. WHERE id_session='".$_SESSION['id_session']."'
  748. AND id_user='$user_id'", __FILE__, __LINE__))) {
  749. return true;
  750. }
  751. // A course coach?
  752. if (Database::num_rows(Database::query("SELECT id_user
  753. FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)."
  754. WHERE id_session='".$_SESSION['id_session']."'
  755. AND id_user = '$user_id' AND status = 2
  756. AND course_code='$course_code'", __FILE__, __LINE__))) {
  757. return true;
  758. }
  759. // A session coach?
  760. if (Database::num_rows(Database::query("SELECT id_coach
  761. FROM ".Database::get_main_table(TABLE_MAIN_SESSION)." AS session
  762. WHERE session.id='".$_SESSION['id_session']."'
  763. AND id_coach='$user_id'", __FILE__, __LINE__))) {
  764. return true;
  765. }
  766. return false;
  767. }
  768. /**
  769. * Return user info array of all users registered in the specified real or virtual course
  770. * This only returns the users that are registered in this actual course, not linked courses.
  771. *
  772. * @param string $course_code the code of the course
  773. * @param boolean $with_session determines if the course is used in a session or not
  774. * @param integer $session_id the id of the session
  775. * @param string $limit the LIMIT statement of the sql statement
  776. * @param string $order_by the field to order the users by. Valid values are 'lastname', 'firstname', 'username', 'email', 'official_code' OR a part of a SQL statement that starts with ORDER BY ...
  777. * @return array
  778. */
  779. public static function get_user_list_from_course_code($course_code, $with_session = true, $session_id = 0, $limit = '', $order_by = '') {
  780. // variable initialisation
  781. $session_id = intval($session_id);
  782. $users = array();
  783. $course_code = Database::escape_string($course_code);
  784. $where = array();
  785. // if the $order_by does not contain 'ORDER BY' we have to check if it is a valid field that can be sorted on
  786. if (!strstr($order_by,'ORDER BY')) {
  787. if (!empty($order_by) AND in_array($order_by, array('lastname', 'firstname', 'username', 'email', 'official_code'))){
  788. $order_by = 'ORDER BY user.'.$order_by;
  789. } else {
  790. $order_by = '';
  791. }
  792. }
  793. $sql = $session_id == 0
  794. ? 'SELECT DISTINCT course_rel_user.status, user.user_id, course_rel_user.role, course_rel_user.tutor_id, user.* '
  795. : 'SELECT DISTINCT user.user_id, user.status, user.* ';
  796. $sql .= ' FROM '.Database::get_main_table(TABLE_MAIN_USER).' as user ';
  797. if (api_get_setting('use_session_mode')=='true' && $with_session) {
  798. $sql .= ' LEFT JOIN '.Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER).' as session_course_user
  799. ON user.user_id = session_course_user.id_user
  800. AND session_course_user.course_code="'.$course_code.'"';
  801. if ($session_id != 0) {
  802. $sql .= ' AND session_course_user.id_session = '.$session_id;
  803. }
  804. $where[] = ' session_course_user.course_code IS NOT NULL ';
  805. }
  806. if ($session_id == 0) {
  807. $sql .= ' LEFT JOIN '.Database::get_main_table(TABLE_MAIN_COURSE_USER).' as course_rel_user
  808. ON user.user_id = course_rel_user.user_id
  809. AND course_rel_user.course_code="'.$course_code.'"';
  810. $where[] = ' course_rel_user.course_code IS NOT NULL ';
  811. }
  812. $sql .= ' WHERE '.implode(' OR ', $where);
  813. $sql .= ' '.$order_by.' '.$limit;
  814. $rs = Database::query($sql, __FILE__, __LINE__);
  815. while ($user = Database::fetch_array($rs)) {
  816. //$user_info = Database::get_user_info_from_id($user['user_id']);
  817. $user_info = $user;
  818. $user_info['status'] = $user['status'];
  819. if (isset($user['role'])) {
  820. $user_info['role'] = $user['role'];
  821. }
  822. if (isset($user['tutor_id'])) {
  823. $user_info['tutor_id'] = $user['tutor_id'];
  824. }
  825. if (!empty($session_id)) {
  826. $user_info['status_session'] = $user['status_session'];
  827. }
  828. $users[$user['user_id']] = $user_info;
  829. }
  830. return $users;
  831. }
  832. /**
  833. * Get a list of coaches of a course and a session
  834. * @param string Course code
  835. * @param int Session ID
  836. * @return array List of users
  837. */
  838. public static function get_coach_list_from_course_code($course_code, $session_id) {
  839. if ($session_id != strval(intval($session_id))) {
  840. return array();
  841. }
  842. $course_code = Database::escape_string($course_code);
  843. $users = array();
  844. // We get the coach for the given course in a given session.
  845. $rs = Database::query('SELECT id_user FROM '.Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER).
  846. ' WHERE id_session="'.$session_id.'" AND course_code="'.$course_code.'" AND status = 2', __FILE__, __LINE__);
  847. while ($user = Database::fetch_array($rs)) {
  848. $user_info = Database::get_user_info_from_id($user['id_user']);
  849. $user_info['status'] = $user['status'];
  850. $user_info['role'] = $user['role'];
  851. $user_info['tutor_id'] = $user['tutor_id'];
  852. $user_info['email'] = $user['email'];
  853. $users[$user['id_user']] = $user_info;
  854. }
  855. // We get the session coach.
  856. $rs = Database::query('SELECT id_coach FROM '.Database::get_main_table(TABLE_MAIN_SESSION).
  857. ' WHERE id="'.$session_id.'"', __FILE__, __LINE__);
  858. $user_info = array();
  859. $session_id_coach = Database::result($rs, 0, 'id_coach');
  860. $user_info = Database::get_user_info_from_id($session_id_coach);
  861. $user_info['status'] = $user['status'];
  862. $user_info['role'] = $user['role'];
  863. $user_info['tutor_id'] = $user['tutor_id'];
  864. $user_info['email'] = $user['email'];
  865. $users[$session_id_coach] = $user_info;
  866. return $users;
  867. }
  868. /**
  869. * Return user info array of all users registered in the specified real or virtual course
  870. * This only returns the users that are registered in this actual course, not linked courses.
  871. *
  872. * @param string $course_code
  873. * @param boolean $full list to true if we want sessions students too
  874. * @return array with user id
  875. */
  876. public static function get_student_list_from_course_code($course_code, $with_session = false, $session_id = 0) {
  877. $session_id = intval($session_id);
  878. $course_code = Database::escape_string($course_code);
  879. $students = array();
  880. if ($session_id == 0) {
  881. // students directly subscribed to the course
  882. $rs = Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  883. WHERE course_code = '$course_code' AND status = 5", __FILE__, __LINE__);
  884. while ($student = Database::fetch_array($rs)) {
  885. $students[$student['user_id']] = $student;
  886. }
  887. }
  888. // students subscribed to the course through a session
  889. if (api_get_setting('use_session_mode') == 'true' && $with_session) {
  890. $sql_query = "SELECT * FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)." WHERE course_code = '$course_code'";
  891. if ($session_id != 0) {
  892. $sql_query .= ' AND id_session = '.$session_id;
  893. }
  894. $rs = Database::query($sql_query, __FILE__, __LINE__);
  895. while($student = Database::fetch_array($rs)) {
  896. $students[$student['id_user']] = $student;
  897. }
  898. }
  899. return $students;
  900. }
  901. /**
  902. * Return user info array of all teacher-users registered in the specified real or virtual course
  903. * This only returns the users that are registered in this actual course, not linked courses.
  904. *
  905. * @param string $course_code
  906. * @return array with user id
  907. */
  908. public static function get_teacher_list_from_course_code($course_code) {
  909. $course_code = Database::escape_string($course_code);
  910. // teachers directly subscribed to the course
  911. $teachers = array();
  912. // TODO: This query is not optimal.
  913. $rs = Database::query("SELECT u.user_id, u.lastname, u.firstname, u.email, u.username, u.status " .
  914. "FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)." cu, ".Database::get_main_table(TABLE_MAIN_USER)." u ".
  915. "WHERE cu.course_code = '$course_code' " .
  916. "AND cu.status = 1 " .
  917. "AND cu.user_id = u.user_id", __FILE__, __LINE__);
  918. while ($teacher = Database::fetch_array($rs)) {
  919. $teachers[$teacher['user_id']] = $teacher;
  920. }
  921. return $teachers;
  922. }
  923. /**
  924. * Return user info array of all users registered in the specified course
  925. * this includes the users of the course itsel and the users of all linked courses.
  926. *
  927. * @param array $course_info
  928. * @return array with user info
  929. */
  930. public static function get_real_and_linked_user_list($course_code, $with_sessions = true, $session_id = 0) {
  931. //get list of virtual courses
  932. $virtual_course_list = self::get_virtual_courses_linked_to_real_course($course_code);
  933. //get users from real course
  934. $user_list = self::get_user_list_from_course_code($course_code, $with_sessions, $session_id);
  935. foreach ($user_list as $this_user) {
  936. $complete_user_list[] = $this_user;
  937. }
  938. //get users from linked courses
  939. foreach ($virtual_course_list as $this_course) {
  940. $course_code = $this_course['code'];
  941. $user_list = self::get_user_list_from_course_code($course_code, $with_sessions, $session_id);
  942. foreach ($user_list as $this_user) {
  943. $complete_user_list[] = $this_user;
  944. }
  945. }
  946. return $complete_user_list;
  947. }
  948. /**
  949. * Return an array of arrays, listing course info of all courses in the list
  950. * linked to the real course $real_course_code, to which the user $user_id is subscribed.
  951. *
  952. * @param $user_id, the id (int) of the user
  953. * @param $real_course_code, the id (char) of the real course
  954. *
  955. * @return array of course info arrays
  956. */
  957. public static function get_list_of_virtual_courses_for_specific_user_and_real_course($user_id, $real_course_code) {
  958. $result_array = array();
  959. if ($user_id != strval(intval($user_id))) {
  960. return $result_array;
  961. }
  962. $course_code = Database::escape_string($course_code);
  963. $sql_result = Database::query("SELECT *
  964. FROM ".Database::get_main_table(TABLE_MAIN_COURSE)." course
  965. LEFT JOIN ".Database::get_main_table(TABLE_MAIN_COURSE_USER)." course_user
  966. ON course.code = course_user.course_code
  967. WHERE course.target_course_code = '$real_course_code' AND course_user.user_id = '$user_id'", __FILE__, __LINE__);
  968. while ($result = Database::fetch_array($sql_result)) {
  969. $result_array[] = $result;
  970. }
  971. return $result_array;
  972. }
  973. /*
  974. ==============================================================================
  975. GROUP FUNCTIONS
  976. ==============================================================================
  977. */
  978. /**
  979. * Get the list of groups from the course
  980. * @param string Course code
  981. * @param int Session ID (optional)
  982. * @return array List of groups info
  983. */
  984. public static function get_group_list_of_course($course_code, $session_id = 0) {
  985. $course_info = Database::get_course_info($course_code);
  986. $database_name = $course_info['db_name'];
  987. $group_list = array();
  988. $session_condition = $session_id == 0 ? '' : ' WHERE g.session_id IN(0,'.intval($session_id).')';
  989. $result = Database::query("SELECT g.id, g.name, COUNT(gu.id) userNb
  990. FROM ".Database::get_course_table(TABLE_GROUP, $database_name)." AS g
  991. LEFT JOIN ".Database::get_course_table(TABLE_GROUP_USER, $database_name)." gu
  992. ON g.id = gu.group_id
  993. $session_condition
  994. GROUP BY g.id
  995. ORDER BY g.name", __FILE__, __LINE__);
  996. while ($group_data = Database::fetch_array($result)) {
  997. $group_list[$group_data['id']] = $group_data;
  998. }
  999. return $group_list;
  1000. }
  1001. /**
  1002. * Checks all parameters needed to create a virtual course.
  1003. * If they are all set, the virtual course creation procedure is called.
  1004. *
  1005. * Call this function instead of create_virtual_course
  1006. * @param string Course code
  1007. * @param string Course title
  1008. * @param string Wanted course code
  1009. * @param string Course language
  1010. * @param string Course category
  1011. * @return bool True on success, false on error
  1012. */
  1013. public static function attempt_create_virtual_course($real_course_code, $course_title, $wanted_course_code, $course_language, $course_category) {
  1014. //better: create parameter list, check the entire list, when false display errormessage
  1015. self::check_parameter_or_fail($real_course_code, 'Unspecified parameter: real course id.');
  1016. self::check_parameter_or_fail($course_title, 'Unspecified parameter: course title.');
  1017. self::check_parameter_or_fail($wanted_course_code, 'Unspecified parameter: wanted course code.');
  1018. self::check_parameter_or_fail($course_language, 'Unspecified parameter: course language.');
  1019. self::check_parameter_or_fail($course_category, 'Unspecified parameter: course category.');
  1020. return self::create_virtual_course($real_course_code, $course_title, $wanted_course_code, $course_language, $course_category);
  1021. }
  1022. /**
  1023. * This function creates a virtual course.
  1024. * It assumes all parameters have been checked and are not empty.
  1025. * It checks wether a course with the $wanted_course_code already exists.
  1026. *
  1027. * Users of this library should consider this function private,
  1028. * please call attempt_create_virtual_course instead of this one.
  1029. *
  1030. * @note The virtual course 'owner' id (the first course admin) is set to the CURRENT user id.
  1031. * @param string Course code
  1032. * @param string Course title
  1033. * @param string Wanted course code
  1034. * @param string Course language
  1035. * @param string Course category
  1036. * @return true if the course creation succeeded, false otherwise
  1037. * @todo research: expiration date of a course
  1038. */
  1039. public static function create_virtual_course($real_course_code, $course_title, $wanted_course_code, $course_language, $course_category) {
  1040. global $firstExpirationDelay;
  1041. $user_id = api_get_user_id();
  1042. $real_course_info = Database::get_course_info($real_course_code);
  1043. $real_course_code = $real_course_info['system_code'];
  1044. //check: virtual course creation fails if another course has the same
  1045. //code, real or fake.
  1046. if (self::is_existing_course_code($wanted_course_code)) {
  1047. Display::display_error_message($wanted_course_code.' - '.get_lang('CourseCodeAlreadyExists'));
  1048. return false;
  1049. }
  1050. //add data to course table, course_rel_user
  1051. $course_sys_code = $wanted_course_code;
  1052. $course_screen_code = $wanted_course_code;
  1053. $course_repository = $real_course_info['directory'];
  1054. $course_db_name = $real_course_info['db_name'];
  1055. $responsible_teacher = $real_course_info['tutor_name'];
  1056. $faculty_shortname = $course_category;
  1057. // $course_title = $course_title;
  1058. // $course_language = $course_language;
  1059. $teacher_id = $user_id;
  1060. //HACK ----------------------------------------------------------------
  1061. $expiration_date = time() + $firstExpirationDelay;
  1062. //END HACK ------------------------------------------------------------
  1063. 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);
  1064. //above was the normal course creation table update call,
  1065. //now one more thing: fill in the target_course_code field
  1066. Database::query("UPDATE ".Database::get_main_table(TABLE_MAIN_COURSE)." SET target_course_code = '$real_course_code'
  1067. WHERE code = '".Database::escape_string($course_sys_code)."' LIMIT 1 ", __FILE__, __LINE__);
  1068. return true;
  1069. }
  1070. /**
  1071. * Delete a course
  1072. * This function deletes a whole course-area from the platform. When the
  1073. * given course is a virtual course, the database and directory will not be
  1074. * deleted.
  1075. * When the given course is a real course, also all virtual courses refering
  1076. * to the given course will be deleted.
  1077. * Considering the fact that we remove all traces of the course in the main
  1078. * database, it makes sense to remove all tracking as well (if stats databases exist)
  1079. * so that a new course created with this code would not use the remains of an older
  1080. * course.
  1081. *
  1082. * @param string The code of the course to delete
  1083. * @todo When deleting a virtual course: unsubscribe users from that virtual
  1084. * course from the groups in the real course if they are not subscribed in
  1085. * that real course.
  1086. * @todo Remove globals
  1087. */
  1088. public static function delete_course($code) {
  1089. global $_configuration;
  1090. $table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  1091. $table_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1092. $table_course_class = Database::get_main_table(TABLE_MAIN_COURSE_CLASS);
  1093. $user_role_table = Database::get_main_table(MAIN_USER_ROLE_TABLE);
  1094. $location_table = Database::get_main_table(MAIN_LOCATION_TABLE);
  1095. $role_right_location_table = Database::get_main_table(MAIN_ROLE_RIGHT_LOCATION_TABLE);
  1096. $table_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  1097. $table_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1098. $table_course_survey = Database::get_main_table(TABLE_MAIN_SHARED_SURVEY);
  1099. $table_course_survey_question = Database::get_main_table(TABLE_MAIN_SHARED_SURVEY_QUESTION);
  1100. $table_course_survey_question_option = Database::get_main_table(TABLE_MAIN_SHARED_SURVEY_QUESTION_OPTION);
  1101. $stats = false;
  1102. if (Database::get_statistic_database() != ''){
  1103. $stats = true;
  1104. $table_stats_hotpots = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
  1105. $table_stats_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
  1106. $table_stats_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
  1107. $table_stats_access = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
  1108. $table_stats_lastaccess = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
  1109. $table_stats_course_access = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
  1110. $table_stats_online = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ONLINE);
  1111. $table_stats_default = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
  1112. $table_stats_downloads = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
  1113. $table_stats_links = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LINKS);
  1114. $table_stats_uploads = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_UPLOADS);
  1115. }
  1116. $code = Database::escape_string($code);
  1117. $sql = "SELECT * FROM $table_course WHERE code='".$code."'";
  1118. $res = Database::query($sql, __FILE__, __LINE__);
  1119. if (Database::num_rows($res) == 0) {
  1120. return;
  1121. }
  1122. $this_course = Database::fetch_array($res);
  1123. $db_name = $this_course['db_name'];
  1124. self::create_database_dump($code);
  1125. if (!self::is_virtual_course_from_system_code($code)) {
  1126. // If this is not a virtual course, look for virtual courses that depend on this one, if any
  1127. $virtual_courses = self::get_virtual_courses_linked_to_real_course($code);
  1128. foreach ($virtual_courses as $index => $virtual_course) {
  1129. // Unsubscribe all classes from the virtual course
  1130. $sql = "DELETE FROM $table_course_class WHERE course_code='".$virtual_course['code']."'";
  1131. Database::query($sql, __FILE__, __LINE__);
  1132. // Unsubscribe all users from the virtual course
  1133. $sql = "DELETE FROM $table_course_user WHERE course_code='".$virtual_course['code']."'";
  1134. Database::query($sql, __FILE__, __LINE__);
  1135. // Delete the course from the sessions tables
  1136. $sql = "DELETE FROM $table_session_course WHERE course_code='".$virtual_course['code']."'";
  1137. Database::query($sql, __FILE__, __LINE__);
  1138. $sql = "DELETE FROM $table_session_course_user WHERE course_code='".$virtual_course['code']."'";
  1139. Database::query($sql, __FILE__, __LINE__);
  1140. // Delete the course from the survey tables
  1141. $sql = "DELETE FROM $table_course_survey WHERE course_code='".$virtual_course['code']."'";
  1142. Database::query($sql, __FILE__, __LINE__);
  1143. $sql = "DELETE FROM $table_course_survey_user WHERE db_name='".$virtual_course['db_name']."'";
  1144. Database::query($sql, __FILE__, __LINE__);
  1145. $sql = "DELETE FROM $table_course_survey_reminder WHERE db_name='".$virtual_course['db_name']."'";
  1146. Database::query($sql, __FILE__, __LINE__);
  1147. // Delete the course from the stats tables
  1148. if ($stats) {
  1149. $sql = "DELETE FROM $table_stats_hotpots WHERE exe_cours_id = '".$virtual_course['code']."'";
  1150. Database::query($sql, __FILE__, __LINE__);
  1151. $sql = "DELETE FROM $table_stats_attempt WHERE course_code = '".$virtual_course['code']."'";
  1152. Database::query($sql, __FILE__, __LINE__);
  1153. $sql = "DELETE FROM $table_stats_exercises WHERE exe_cours_id = '".$virtual_course['code']."'";
  1154. Database::query($sql, __FILE__, __LINE__);
  1155. $sql = "DELETE FROM $table_stats_access WHERE access_cours_code = '".$virtual_course['code']."'";
  1156. Database::query($sql, __FILE__, __LINE__);
  1157. $sql = "DELETE FROM $table_stats_lastaccess WHERE access_cours_code = '".$virtual_course['code']."'";
  1158. Database::query($sql, __FILE__, __LINE__);
  1159. $sql = "DELETE FROM $table_stats_course_access WHERE course_code = '".$virtual_course['code']."'";
  1160. Database::query($sql, __FILE__, __LINE__);
  1161. $sql = "DELETE FROM $table_stats_online WHERE course = '".$virtual_course['code']."'";
  1162. Database::query($sql, __FILE__, __LINE__);
  1163. $sql = "DELETE FROM $table_stats_default WHERE default_cours_code = '".$virtual_course['code']."'";
  1164. Database::query($sql, __FILE__, __LINE__);
  1165. $sql = "DELETE FROM $table_stats_downloads WHERE down_cours_id = '".$virtual_course['code']."'";
  1166. Database::query($sql, __FILE__, __LINE__);
  1167. $sql = "DELETE FROM $table_stats_links WHERE links_cours_id = '".$virtual_course['code']."'";
  1168. Database::query($sql, __FILE__, __LINE__);
  1169. $sql = "DELETE FROM $table_stats_uploads WHERE upload_cours_id = '".$virtual_course['code']."'";
  1170. Database::query($sql, __FILE__, __LINE__);
  1171. }
  1172. // Delete the course from the course table
  1173. $sql = "DELETE FROM $table_course WHERE code='".$virtual_course['code']."'";
  1174. Database::query($sql, __FILE__, __LINE__);
  1175. }
  1176. $sql = "SELECT * FROM $table_course WHERE code='".$code."'";
  1177. $res = Database::query($sql, __FILE__, __LINE__);
  1178. $course = Database::fetch_array($res);
  1179. if (!$_configuration['single_database']) {
  1180. $sql = "DROP DATABASE IF EXISTS ".$course['db_name'];
  1181. Database::query($sql, __FILE__, __LINE__);
  1182. } else {
  1183. //TODO Clean the following code as currently it would probably delete another course
  1184. //similarly named, by mistake...
  1185. $db_pattern = $_configuration['table_prefix'].$course['db_name'].$_configuration['db_glue'];
  1186. $sql = "SHOW TABLES LIKE '$db_pattern%'";
  1187. $result = Database::query($sql, __FILE__, __LINE__);
  1188. while (list ($courseTable) = Database::fetch_array($result)) {
  1189. Database::query("DROP TABLE $courseTable", __FILE__, __LINE__);
  1190. }
  1191. }
  1192. $course_dir = api_get_path(SYS_COURSE_PATH).$course['directory'];
  1193. $archive_dir = api_get_path(SYS_ARCHIVE_PATH).$course['directory'].'_'.time();
  1194. if (is_dir($course_dir)) {
  1195. rename($course_dir, $archive_dir);
  1196. }
  1197. }
  1198. // Unsubscribe all classes from the course
  1199. $sql = "DELETE FROM $table_course_class WHERE course_code='".$code."'";
  1200. Database::query($sql, __FILE__, __LINE__);
  1201. // Unsubscribe all users from the course
  1202. $sql = "DELETE FROM $table_course_user WHERE course_code='".$code."'";
  1203. Database::query($sql, __FILE__, __LINE__);
  1204. // Delete the course from the sessions tables
  1205. $sql = "DELETE FROM $table_session_course WHERE course_code='".$code."'";
  1206. Database::query($sql, __FILE__, __LINE__);
  1207. $sql = "DELETE FROM $table_session_course_user WHERE course_code='".$code."'";
  1208. Database::query($sql, __FILE__, __LINE__);
  1209. $sql = 'SELECT survey_id FROM '.$table_course_survey.' WHERE course_code="'.$code.'"';
  1210. $result_surveys = Database::query($sql, __FILE__, __LINE__);
  1211. while($surveys = Database::fetch_array($result_surveys)) {
  1212. $survey_id = $surveys[0];
  1213. $sql = 'DELETE FROM '.$table_course_survey_question.' WHERE survey_id="'.$survey_id.'"';
  1214. Database::query($sql, __FILE__, __LINE__);
  1215. $sql = 'DELETE FROM '.$table_course_survey_question_option.' WHERE survey_id="'.$survey_id.'"';
  1216. Database::query($sql, __FILE__, __LINE__);
  1217. $sql = 'DELETE FROM '.$table_course_survey.' WHERE survey_id="'.$survey_id.'"';
  1218. Database::query($sql, __FILE__, __LINE__);
  1219. }
  1220. // Delete the course from the stats tables
  1221. if ($stats) {
  1222. $sql = "DELETE FROM $table_stats_hotpots WHERE exe_cours_id = '".$code."'";
  1223. Database::query($sql, __FILE__, __LINE__);
  1224. $sql = "DELETE FROM $table_stats_attempt WHERE course_code = '".$code."'";
  1225. Database::query($sql, __FILE__, __LINE__);
  1226. $sql = "DELETE FROM $table_stats_exercises WHERE exe_cours_id = '".$code."'";
  1227. Database::query($sql, __FILE__, __LINE__);
  1228. $sql = "DELETE FROM $table_stats_access WHERE access_cours_code = '".$code."'";
  1229. Database::query($sql, __FILE__, __LINE__);
  1230. $sql = "DELETE FROM $table_stats_lastaccess WHERE access_cours_code = '".$code."'";
  1231. Database::query($sql, __FILE__, __LINE__);
  1232. $sql = "DELETE FROM $table_stats_course_access WHERE course_code = '".$code."'";
  1233. Database::query($sql, __FILE__, __LINE__);
  1234. $sql = "DELETE FROM $table_stats_online WHERE course = '".$code."'";
  1235. Database::query($sql, __FILE__, __LINE__);
  1236. $sql = "DELETE FROM $table_stats_default WHERE default_cours_code = '".$code."'";
  1237. Database::query($sql, __FILE__, __LINE__);
  1238. $sql = "DELETE FROM $table_stats_downloads WHERE down_cours_id = '".$code."'";
  1239. Database::query($sql, __FILE__, __LINE__);
  1240. $sql = "DELETE FROM $table_stats_links WHERE links_cours_id = '".$code."'";
  1241. Database::query($sql, __FILE__, __LINE__);
  1242. $sql = "DELETE FROM $table_stats_uploads WHERE upload_cours_id = '".$code."'";
  1243. Database::query($sql, __FILE__, __LINE__);
  1244. }
  1245. global $_configuration;
  1246. if ($_configuration['multiple_access_urls'] == true) {
  1247. require_once api_get_path(LIBRARY_PATH).'urlmanager.lib.php';
  1248. $url_id = 1;
  1249. if (api_get_current_access_url_id() != -1) {
  1250. $url_id = api_get_current_access_url_id();
  1251. }
  1252. UrlManager::delete_url_rel_course($code, $url_id);
  1253. }
  1254. // Delete the course from the database
  1255. $sql = "DELETE FROM $table_course WHERE code='".$code."'";
  1256. Database::query($sql, __FILE__, __LINE__);
  1257. // delete extra course fields
  1258. $t_cf = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
  1259. $t_cfv = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  1260. $sql = "SELECT distinct field_id FROM $t_cfv WHERE course_code = '$code'";
  1261. $res_field_ids = @Database::query($sql, __FILE__, __LINE__);
  1262. while($row_field_id = Database::fetch_row($res_field_ids)){
  1263. $field_ids[] = $row_field_id[0];
  1264. }
  1265. //delete from table_course_field_value from a given course_code
  1266. $sql_course_field_value = "DELETE FROM $t_cfv WHERE course_code = '$code'";
  1267. @Database::query($sql_course_field_value, __FILE__, __LINE__);
  1268. $sql = "SELECT distinct field_id FROM $t_cfv";
  1269. $res_field_all_ids = @Database::query($sql, __FILE__, __LINE__);
  1270. while($row_field_all_id = Database::fetch_row($res_field_all_ids)){
  1271. $field_all_ids[] = $row_field_all_id[0];
  1272. }
  1273. if (is_array($field_ids) && count($field_ids) > 0) {
  1274. foreach ($field_ids as $field_id) {
  1275. // check if field id is used into table field value
  1276. if (is_array($field_all_ids)) {
  1277. if (in_array($field_id, $field_all_ids)) {
  1278. continue;
  1279. } else {
  1280. $sql_course_field = "DELETE FROM $t_cf WHERE id = '$field_id'";
  1281. Database::query($sql_course_field, __FILE__, __LINE__);
  1282. }
  1283. }
  1284. }
  1285. }
  1286. // add event to system log
  1287. $time = time();
  1288. $user_id = api_get_user_id();
  1289. event_system(LOG_COURSE_DELETE, LOG_COURSE_CODE, $code, $time, $user_id, $code);
  1290. }
  1291. /**
  1292. * Creates a file called mysql_dump.sql in the course folder
  1293. * @param $course_code The code of the course
  1294. * @todo Implementation for single database
  1295. */
  1296. public static function create_database_dump($course_code) {
  1297. global $_configuration;
  1298. if ($_configuration['single_database']) {
  1299. return;
  1300. }
  1301. $sql_dump = '';
  1302. $course_code = Database::escape_string($course_code);
  1303. $table_course = Database::get_main_table(TABLE_MAIN_COURSE);
  1304. $sql = "SELECT * FROM $table_course WHERE code = '$course_code'";
  1305. $res = Database::query($sql, __FILE__, __LINE__);
  1306. $course = Database::fetch_array($res);
  1307. $sql = "SHOW TABLES FROM ".$course['db_name'];
  1308. $res = Database::query($sql, __FILE__, __LINE__);
  1309. while ($table = Database::fetch_array($res)) {
  1310. $sql = "SELECT * FROM ".$course['db_name'].".".$table[0]."";
  1311. $res3 = Database::query($sql, __FILE__, __LINE__);
  1312. while ($row = Database::fetch_array($res3)) {
  1313. foreach ($row as $key => $value) {
  1314. $row[$key] = $key."='".addslashes($row[$key])."'";
  1315. }
  1316. $sql_dump .= "\nINSERT INTO $table[0] SET ".implode(', ', $row).';';
  1317. }
  1318. }
  1319. if (is_dir(api_get_path(SYS_COURSE_PATH).$course['directory'])) {
  1320. $file_name = api_get_path(SYS_COURSE_PATH).$course['directory'].'/mysql_dump.sql';
  1321. $handle = fopen($file_name, 'a+');
  1322. if ($handle !== false) {
  1323. fwrite($handle, $sql_dump);
  1324. fclose($handle);
  1325. } else {
  1326. //TODO trigger exception in a try-catch
  1327. }
  1328. }
  1329. }
  1330. /**
  1331. * Sort courses for a specific user ??
  1332. * @param int User ID
  1333. * @param string Course code
  1334. * @return int Minimum course order
  1335. * @todo Review documentation
  1336. */
  1337. public static function userCourseSort($user_id, $course_code) {
  1338. if ($user_id != strval(intval($user_id))) {
  1339. return false;
  1340. }
  1341. $course_code = Database::escape_string($course_code);
  1342. $TABLECOURSE = Database::get_main_table(TABLE_MAIN_COURSE);
  1343. $TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1344. $course_title = Database::result(Database::query('SELECT title FROM '.$TABLECOURSE.
  1345. ' WHERE code="'.$course_code.'"', __FILE__, __LINE__), 0, 0);
  1346. $result = Database::query('SELECT course.code as code, course.title as title, cu.sort as sort FROM '.$TABLECOURSUSER.' as cu, '.$TABLECOURSE.' as course
  1347. WHERE course.code = cu.course_code
  1348. AND user_id = "'.$user_id.'"
  1349. AND user_course_cat=0 ORDER BY cu.sort', __FILE__, __LINE__);
  1350. $course_title_precedent = '';
  1351. $counter = 0;
  1352. $course_found = false;
  1353. $course_sort = 1;
  1354. while ($courses = Database::fetch_array($result)){
  1355. if ($course_title_precedent == '') {
  1356. $course_title_precedent = $courses['title'];
  1357. }
  1358. if (api_strcasecmp($course_title_precedent, $course_title) < 0) {
  1359. $course_found = true;
  1360. $course_sort = $courses['sort'];
  1361. if ($counter == 0) {
  1362. $sql = 'UPDATE '.$TABLECOURSUSER.' SET sort = sort+1 WHERE user_id= "'.$user_id.'" AND user_course_cat="0" AND sort > "'.$course_sort.'"';
  1363. $course_sort++;
  1364. } else {
  1365. $sql = 'UPDATE '.$TABLECOURSUSER.' SET sort = sort+1 WHERE user_id= "'.$user_id.'" AND user_course_cat="0" AND sort >= "'.$course_sort.'"';
  1366. }
  1367. Database::query($sql, __FILE__, __LINE__);
  1368. break;
  1369. } else {
  1370. $course_title_precedent = $courses['title'];
  1371. }
  1372. $counter++;
  1373. }
  1374. // We must register the course in the beginning of the list
  1375. if (Database::num_rows($result) > 0 && !$course_found) {
  1376. $course_sort = Database::result(Database::query('SELECT min(sort) as min_sort FROM '.$TABLECOURSUSER.
  1377. ' WHERE user_id="'.$user_id.'" AND user_course_cat="0"', __FILE__, __LINE__), 0, 0);
  1378. Database::query('UPDATE '.$TABLECOURSUSER.' SET sort = sort+1
  1379. WHERE user_id= "'.$user_id.'" AND user_course_cat="0"', __FILE__, __LINE__);
  1380. }
  1381. return $course_sort;
  1382. }
  1383. /**
  1384. * create recursively all categories as option of the select passed in paramater.
  1385. *
  1386. * @param object $select_element the quickform select where the options will be added
  1387. * @param string $category_selected_code the option value to select by default (used mainly for edition of courses)
  1388. * @param string $parent_code the parent category of the categories added (default=null for root category)
  1389. * @param string $padding the indent param (you shouldn't indicate something here)
  1390. */
  1391. public static function select_and_sort_categories($select_element, $category_selected_code = '', $parent_code = null , $padding = '') {
  1392. $res = Database::query("SELECT code, name, auth_course_child, auth_cat_child
  1393. FROM ".Database::get_main_table(TABLE_MAIN_CATEGORY)."
  1394. WHERE parent_id ".(is_null($parent_code) ? "IS NULL" : "='".Database::escape_string($parent_code)."'")."
  1395. ORDER BY code", __FILE__, __LINE__);
  1396. while ($cat = Database::fetch_array($res)) {
  1397. $params = $cat['auth_course_child'] == 'TRUE' ? '' : 'disabled';
  1398. $params .= ($cat['code'] == $category_selected_code) ? ' selected' : '';
  1399. $select_element->addOption($padding.'('.$cat['code'].') '.$cat['name'], $cat['code'], $params);
  1400. if ($cat['auth_cat_child']) {
  1401. self::select_and_sort_categories($select_element, $category_selected_code, $cat['code'], $padding.' - ');
  1402. }
  1403. }
  1404. }
  1405. /**
  1406. * check if course exists
  1407. * @param string course_code
  1408. * @param string whether to accept virtual course codes or not
  1409. * @return true if exists, false else
  1410. */
  1411. public static function course_exists($course_code, $accept_virtual = false) {
  1412. if ($accept_virtual === true) {
  1413. $sql = 'SELECT 1 FROM '.Database::get_main_table(TABLE_MAIN_COURSE).' WHERE code="'.Database::escape_string($course_code).'" OR visual_code="'.Database::escape_string($course_code).'"';
  1414. } else {
  1415. $sql = 'SELECT 1 FROM '.Database::get_main_table(TABLE_MAIN_COURSE).' WHERE code="'.Database::escape_string($course_code).'"';
  1416. }
  1417. return Database::num_rows(Database::query($sql, __FILE__, __LINE__));
  1418. }
  1419. /**
  1420. * Send an email to tutor after the auth-suscription of a student in your course
  1421. * @author Carlos Vargas <carlos.vargas@dokeos.com>, Dokeos Latino
  1422. * @param int $user_id the id of the user
  1423. * @param string $course_code the course code
  1424. * @param string $send_to_tutor_also
  1425. * @return string we return the message that is displayed when the action is succesfull
  1426. */
  1427. public static function email_to_tutor($user_id, $course_code, $send_to_tutor_also = false) {
  1428. if ($user_id != strval(intval($user_id))) {
  1429. return false;
  1430. }
  1431. $course_code = Database::escape_string($course_code);
  1432. $student = Database::fetch_array(Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_USER)."
  1433. WHERE user_id='".$user_id."'", __FILE__, __LINE__));
  1434. $information = self::get_course_information($course_code);
  1435. $name_course = $information['title'];
  1436. $sql = "SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)." WHERE course_code='".$course_code."'";
  1437. // TODO: Ivan: This is a mistake, please, have a look at it. Intention here is diffcult to be guessed.
  1438. //if ($send_to_tutor_also = true) {
  1439. // Proposed change:
  1440. if ($send_to_tutor_also) {
  1441. //
  1442. $sql .= " AND tutor_id=1";
  1443. } else {
  1444. $sql .= " AND status=1";
  1445. }
  1446. $result = Database::query($sql, __FILE__, __LINE__);
  1447. while ($row = Database::fetch_array($result)) {
  1448. $tutor = Database::fetch_array(Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_USER)."
  1449. WHERE user_id='".$row['user_id']."'", __FILE__, __LINE__));
  1450. $emailto = $tutor['email'];
  1451. $emailsubject = get_lang('NewUserInTheCourse').': '.$name_course;
  1452. $emailbody = get_lang('Dear').': '. api_get_person_name($tutor['firstname'], $tutor['lastname'])."\n";
  1453. $emailbody .= get_lang('MessageNewUserInTheCourse').': '.$name_course."\n";
  1454. $emailbody .= get_lang('UserName').': '.$student['username']."\n";
  1455. if (api_is_western_name_order()) {
  1456. $emailbody .= get_lang('FirstName').': '.$student['firstname']."\n";
  1457. $emailbody .= get_lang('LastName').': '.$student['lastname']."\n";
  1458. } else {
  1459. $emailbody .= get_lang('LastName').': '.$student['lastname']."\n";
  1460. $emailbody .= get_lang('FirstName').': '.$student['firstname']."\n";
  1461. }
  1462. $emailbody .= get_lang('Email').': '.$student['email']."\n\n";
  1463. $recipient_name = api_get_person_name($tutor['firstname'], $tutor['lastname'], null, PERSON_NAME_EMAIL_ADDRESS);
  1464. $sender_name = api_get_person_name(api_get_setting('administratorName'), api_get_setting('administratorSurname'), null, PERSON_NAME_EMAIL_ADDRESS);
  1465. $email_admin = api_get_setting('emailAdministrator');
  1466. @api_mail($recipient_name, $emailto, $emailsubject, $emailbody, $sender_name,$email_admin);
  1467. }
  1468. }
  1469. /**
  1470. * Get list of courses for a given user
  1471. * @param int user ID
  1472. * @param boolean Whether to include courses from session or not
  1473. * @return array List of codes and db names
  1474. * @author isaac flores paz
  1475. */
  1476. public static function get_courses_list_by_user_id($user_id, $include_sessions = false) {
  1477. $user_id = intval($user_id);
  1478. $course_list = array();
  1479. $codes = array();
  1480. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  1481. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1482. $tbl_course_field = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD);
  1483. $tbl_course_field_value = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  1484. $tbl_user_course_category = Database :: get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
  1485. // get course list auto-register
  1486. $sql = "SELECT course_code FROM $tbl_course_field_value tcfv INNER JOIN $tbl_course_field tcf ON " .
  1487. " tcfv.field_id = tcf.id WHERE tcf.field_variable = 'special_course' AND tcfv.field_value = 1 ";
  1488. $special_course_result = Database::query($sql, __FILE__, __LINE__);
  1489. if(Database::num_rows($special_course_result)>0) {
  1490. $special_course_list = array();
  1491. while ($result_row = Database::fetch_array($special_course_result)) {
  1492. $special_course_list[] = '"'.$result_row['course_code'].'"';
  1493. }
  1494. }
  1495. $with_special_courses = $without_special_courses = '';
  1496. if (!empty($special_course_list)) {
  1497. $with_special_courses = ' course.code IN ('.implode(',',$special_course_list).')';
  1498. $without_special_courses = ' AND course.code NOT IN ('.implode(',',$special_course_list).')';
  1499. }
  1500. if (!empty($with_special_courses)) {
  1501. $sql = "SELECT course.code, course.db_name db_name, course.title
  1502. FROM ".$tbl_course_user." course_rel_user
  1503. LEFT JOIN ".$tbl_course." course
  1504. ON course.code = course_rel_user.course_code
  1505. LEFT JOIN ".$tbl_user_course_category." user_course_category
  1506. ON course_rel_user.user_course_cat = user_course_category.id
  1507. WHERE $with_special_courses
  1508. GROUP BY course.code
  1509. ORDER BY user_course_category.sort,course.title,course_rel_user.sort ASC";
  1510. $rs_special_course = api_sql_query($sql, __FILE__, __LINE__);
  1511. if (Database::num_rows($rs_special_course) > 0) {
  1512. while ($result_row = Database::fetch_array($rs_special_course)) {
  1513. $result_row['special_course'] = 1;
  1514. $course_list[] = $result_row;
  1515. $codes[] = $result_row['code'];
  1516. }
  1517. }
  1518. }
  1519. // get course list not auto-register
  1520. $sql = "SELECT course.code,course.db_name,course.title
  1521. FROM $tbl_course course
  1522. INNER JOIN $tbl_course_user cru
  1523. ON course.code=cru.course_code
  1524. WHERE cru.user_id='$user_id' $without_special_courses";
  1525. $result = Database::query($sql, __FILE__, __LINE__);
  1526. if (Database::num_rows($result)) {
  1527. while ($row = Database::fetch_array($result, 'ASSOC')) {
  1528. $course_list[] = $row;
  1529. $codes[] = $row['code'];
  1530. }
  1531. }
  1532. if ($include_sessions === true) {
  1533. $r = Database::query("SELECT distinct(c.code),c.db_name,c.title
  1534. FROM ".Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER)." s, ".Database::get_main_table(TABLE_MAIN_COURSE)." c
  1535. WHERE id_user = $user_id AND s.course_code=c.code", __FILE__, __LINE__);
  1536. while ($row = Database::fetch_array($r, 'ASSOC')) {
  1537. if (!in_array($row['code'], $codes)) {
  1538. $course_list[] = $row;
  1539. }
  1540. }
  1541. }
  1542. return $course_list;
  1543. }
  1544. /**
  1545. * Get course ID from a given course directory name
  1546. * @param string Course directory (without any slash)
  1547. * @return string Course code, or false if not found
  1548. */
  1549. public static function get_course_id_from_path ($path) {
  1550. $path = Database::escape_string(str_replace('.', '', str_replace('/', '', $path)));
  1551. $res = Database::query("SELECT code FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  1552. WHERE directory LIKE BINARY '$path'", __FILE__, __LINE__);
  1553. if ($res === false) {
  1554. return false;
  1555. }
  1556. if (Database::num_rows($res) != 1) {
  1557. return false;
  1558. }
  1559. $row = Database::fetch_array($res);
  1560. return $row['code'];
  1561. }
  1562. /**
  1563. * Get course code(s) from visual code
  1564. * @param string Visual code
  1565. * @return array List of codes for the given visual code
  1566. */
  1567. public static function get_courses_info_from_visual_code($code) {
  1568. $result = array();
  1569. $sql_result = Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_COURSE)."
  1570. WHERE visual_code = '".Database::escape_string($code)."'", __FILE__, __LINE__);
  1571. while ($virtual_course = Database::fetch_array($sql_result)) {
  1572. $result[] = $virtual_course;
  1573. }
  1574. return $result;
  1575. }
  1576. /**
  1577. * Get emails of tutors to course
  1578. * @param string Visual code
  1579. * @return array List of emails of tutors to course
  1580. * @author @author Carlos Vargas <carlos.vargas@dokeos.com>, Dokeos Latino
  1581. * */
  1582. public static function get_emails_of_tutors_to_course($code) {
  1583. $list = array();
  1584. $res = Database::query("SELECT user_id FROM ".Database::get_main_table(TABLE_MAIN_COURSE_USER)."
  1585. WHERE course_code='".Database::escape_string($code)."' AND status=1", __FILE__, __LINE__);
  1586. while ($list_users = Database::fetch_array($res)) {
  1587. $result = Database::query("SELECT * FROM ".Database::get_main_table(TABLE_MAIN_USER)."
  1588. WHERE user_id=".$list_users['user_id'], __FILE__, __LINE__);
  1589. while ($row_user = Database::fetch_array($result)){
  1590. $name_teacher = api_get_person_name($row_user['firstname'], $row_user['lastname'], null, PERSON_NAME_EMAIL_ADDRESS);
  1591. $list[] = array($row_user['email'] => $name_teacher);
  1592. }
  1593. }
  1594. return $list;
  1595. }
  1596. /**
  1597. * Get coachs' emails by session
  1598. * @param int session id
  1599. * @param string course code
  1600. * @return array array(email => name_tutor) by coach
  1601. * @author Carlos Vargas <carlos.vargas@dokeos.com>
  1602. */
  1603. public static function get_email_of_tutor_to_session($session_id,$course_code) {
  1604. $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  1605. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1606. $coachs_emails = array();
  1607. $course_code = Database::escape_string($course_code);
  1608. $session_id = intval($session_id);
  1609. $sql = "SELECT id_user FROM $tbl_session_course_user WHERE id_session='$session_id' AND course_code='$course_code' AND status =2";
  1610. $rs = Database::query($sql,__FILE__,__LINE__);
  1611. if (Database::num_rows($rs) > 0) {
  1612. $user_ids = array();
  1613. while ($row = Database::fetch_array($rs)) {
  1614. $user_ids[] = $row['id_user'];
  1615. }
  1616. $sql = "SELECT firstname,lastname,email FROM $tbl_user WHERE user_id IN (".implode(",",$user_ids).")";
  1617. $rs_user = Database::query($sql,__FILE__,__LINE__);
  1618. while ($row_emails = Database::fetch_array($rs_user)) {
  1619. $name_tutor = api_get_person_name($row_emails['firstname'], $row_emails['lastname'], null, PERSON_NAME_EMAIL_ADDRESS);
  1620. $mail_tutor = array($row_emails['email'] => $name_tutor);
  1621. $coachs_emails[] = $mail_tutor;
  1622. }
  1623. }
  1624. return $coachs_emails;
  1625. }
  1626. /**
  1627. * Creates a new extra field for a given course
  1628. * @param string Field's internal variable name
  1629. * @param int Field's type
  1630. * @param string Field's language var name
  1631. * @return int new extra field id
  1632. */
  1633. public static function create_course_extra_field($fieldvarname, $fieldtype, $fieldtitle) {
  1634. // database table definition
  1635. $t_cfv = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  1636. $t_cf = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
  1637. $fieldvarname = Database::escape_string($fieldvarname);
  1638. $fieldtitle = Database::escape_string($fieldtitle);
  1639. $fieldtype = (int)$fieldtype;
  1640. $time = time();
  1641. $sql_field = "SELECT id FROM $t_cf WHERE field_variable = '$fieldvarname'";
  1642. $res_field = Database::query($sql_field, __FILE__, __LINE__);
  1643. $r_field = Database::fetch_row($res_field);
  1644. if (Database::num_rows($res_field) > 0) {
  1645. return $r_field[0];
  1646. }
  1647. // save new fieldlabel into course_field table
  1648. $sql = "SELECT MAX(field_order) FROM $t_cf";
  1649. $res = Database::query($sql, __FILE__, __LINE__);
  1650. $order = 0;
  1651. if (Database::num_rows($res) > 0) {
  1652. $row = Database::fetch_row($res);
  1653. $order = $row[0] + 1;
  1654. }
  1655. $sql = "INSERT INTO $t_cf
  1656. SET field_type = '$fieldtype',
  1657. field_variable = '$fieldvarname',
  1658. field_display_text = '$fieldtitle',
  1659. field_order = '$order',
  1660. tms = FROM_UNIXTIME($time)";
  1661. Database::query($sql, __FILE__, __LINE__);
  1662. return Database::insert_id();
  1663. }
  1664. /**
  1665. * Update an extra field value for a given course
  1666. * @param integer Course ID
  1667. * @param string Field variable name
  1668. * @param string Field value
  1669. * @return boolean true if field updated, false otherwise
  1670. */
  1671. public static function update_course_extra_field_value($course_code, $fname, $fvalue = '') {
  1672. $t_cfv = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  1673. $t_cf = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
  1674. $fname = Database::escape_string($fname);
  1675. $course_code = Database::escape_string($course_code);
  1676. $fvalues = '';
  1677. if (is_array($fvalue)) {
  1678. foreach ($fvalue as $val) {
  1679. $fvalues .= Database::escape_string($val).';';
  1680. }
  1681. if (!empty($fvalues)) {
  1682. $fvalues = substr($fvalues, 0, -1);
  1683. }
  1684. } else {
  1685. $fvalues = Database::escape_string($fvalue);
  1686. }
  1687. $sqlcf = "SELECT * FROM $t_cf WHERE field_variable='$fname'";
  1688. $rescf = Database::query($sqlcf, __FILE__, __LINE__);
  1689. if (Database::num_rows($rescf) == 1) {
  1690. // Ok, the field exists
  1691. // Check if enumerated field, if the option is available
  1692. $rowcf = Database::fetch_array($rescf);
  1693. $tms = time();
  1694. $sqlcfv = "SELECT * FROM $t_cfv WHERE course_code = '$course_code' AND field_id = '".$rowcf['id']."' ORDER BY id";
  1695. $rescfv = Database::query($sqlcfv, __FILE__, __LINE__);
  1696. $n = Database::num_rows($rescfv);
  1697. if ($n > 1) {
  1698. //problem, we already have to values for this field and user combination - keep last one
  1699. while ($rowcfv = Database::fetch_array($rescfv)) { // See the TODO note below.
  1700. if ($n > 1) {
  1701. $sqld = "DELETE FROM $t_cfv WHERE id = ".$rowcfv['id'];
  1702. $resd = Database::query($sqld, __FILE__, __LINE__);
  1703. $n--;
  1704. }
  1705. $rowcfv = Database::fetch_array($rescfv);
  1706. if ($rowcfv['field_value'] != $fvalues) {
  1707. $sqlu = "UPDATE $t_cfv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms) WHERE id = ".$rowcfv['id'];
  1708. $resu = Database::query($sqlu, __FILE__, __LINE__);
  1709. return ($resu ? true : false);
  1710. }
  1711. return true; // TODO: Sure exit from the function occures in this "while" cycle. Logic should checked. Maybe "if" instead of "while"? It is not clear...
  1712. }
  1713. } elseif ($n == 1) {
  1714. //we need to update the current record
  1715. $rowcfv = Database::fetch_array($rescfv);
  1716. if ($rowcfv['field_value'] != $fvalues) {
  1717. $sqlu = "UPDATE $t_cfv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms) WHERE id = ".$rowcfv['id'];
  1718. //error_log('UM::update_extra_field_value: '.$sqlu);
  1719. $resu = Database::query($sqlu, __FILE__, __LINE__);
  1720. return ($resu ? true : false);
  1721. }
  1722. return true;
  1723. } else {
  1724. $sqli = "INSERT INTO $t_cfv (course_code,field_id,field_value,tms) " .
  1725. "VALUES ('$course_code',".$rowcf['id'].",'$fvalues',FROM_UNIXTIME($tms))";
  1726. //error_log('UM::update_extra_field_value: '.$sqli);
  1727. $resi = Database::query($sqli, __FILE__, __LINE__);
  1728. return ($resi ? true : false);
  1729. }
  1730. } else {
  1731. return false; //field not found
  1732. }
  1733. }
  1734. /**
  1735. * Get the course id of an course by the database name
  1736. * @param string The database name
  1737. * @return string The course id
  1738. */
  1739. public static function get_course_id_by_database_name($db_name) {
  1740. return Database::result(Database::query('SELECT code FROM '.Database::get_main_table(TABLE_MAIN_COURSE).
  1741. ' WHERE db_name="'.Database::escape_string($db_name).'"', __FILE__, __LINE__), 0, 'code');
  1742. }
  1743. public static function get_session_category_id_by_session_id($session_id) {
  1744. return Database::result(Database::query('SELECT sc.id session_category
  1745. FROM '.Database::get_main_table(TABLE_MAIN_SESSION_CATEGORY).' sc
  1746. INNER JOIN '.Database::get_main_table(TABLE_MAIN_SESSION).' s
  1747. ON sc.id=s.session_category_id WHERE s.id="'.Database::escape_string($session_id).'"', __FILE__, __LINE__),
  1748. 0, 'session_category');
  1749. }
  1750. /**
  1751. * Get the course id of an course by the database name
  1752. * @param string The database name
  1753. * @return string The course id
  1754. */
  1755. public static function get_course_extra_field_list($code) {
  1756. $tbl_course_field = Database::get_main_table(TABLE_MAIN_COURSE_FIELD);
  1757. $tbl_course_field_value = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
  1758. $sql_field = "SELECT id, field_type, field_variable, field_display_text, field_default_value
  1759. FROM $tbl_course_field WHERE field_visible = '1' ";
  1760. $res_field = api_sql_query($sql_field,__FILE__,__LINE__);
  1761. $extra_fields = array();
  1762. while($rowcf = Database::fetch_array($res_field)) {
  1763. $extra_field_id = $rowcf['id'];
  1764. $sql_field_value = "SELECT field_value FROM $tbl_course_field_value WHERE course_code = '$code' AND field_id = '$extra_field_id' ";
  1765. $res_field_value = api_sql_query($sql_field_value, __FILE__, __LINE__);
  1766. if(Database::num_rows($res_field_value) > 0 ) {
  1767. $r_field_value = Database::fetch_row($res_field_value);
  1768. $rowcf['extra_field_value'] = $r_field_value[0];
  1769. }
  1770. $extra_fields[] = $rowcf;
  1771. }
  1772. return $extra_fields;
  1773. }
  1774. /**
  1775. * Get the database name of a course by the code
  1776. * @param string The course code
  1777. * @return string The database name
  1778. */
  1779. public static function get_name_database_course($course_code) {
  1780. return Database::result(Database::query('SELECT db_name FROM '.Database::get_main_table(TABLE_MAIN_COURSE).
  1781. ' WHERE code="'.Database::escape_string($course_code).'"', __FILE__, __LINE__), 0, 'db_name');
  1782. }
  1783. /**
  1784. * Lists details of the course description
  1785. * @param array The course description
  1786. * @param string The encoding
  1787. * @param bool If true is displayed if false is hidden
  1788. * @return string The course description in html
  1789. */
  1790. public static function get_details_course_description_html($descriptions, $charset, $action_show = true) {
  1791. if (isset($descriptions) && count($descriptions) > 0) {
  1792. $data = '';
  1793. foreach ($descriptions as $id => $description) {
  1794. $data .= '<div class="sectiontitle">';
  1795. if (api_is_allowed_to_edit() && $action_show == true) {
  1796. //delete
  1797. $data .= '<a href="'.api_get_self().'?'.api_get_cidreq().'&amp;action=delete&amp;description_id='.$description->id.'" onclick="javascript:if(!confirm(\''.addslashes(api_htmlentities(get_lang('ConfirmYourChoice'), ENT_QUOTES, $charset)).'\')) return false;">';
  1798. $data .= Display::return_icon('delete.gif', get_lang('Delete'), array('style' => 'vertical-align:middle;float:right;'));
  1799. $data .= '</a> ';
  1800. //edit
  1801. $data .= '<a href="'.api_get_self().'?'.api_get_cidreq().'&amp;description_id='.$description->id.'">';
  1802. $data .= Display::return_icon('edit.gif', get_lang('Edit'), array('style' => 'vertical-align:middle;float:right; padding-right:4px;'));
  1803. $data .= '</a> ';
  1804. }
  1805. $data .= $description->title;
  1806. $data .= '</div>';
  1807. $data .= '<div class="sectioncomment">';
  1808. $data .= text_filter($description->content);
  1809. $data .= '</div>';
  1810. }
  1811. } else {
  1812. $data .= '<em>'.get_lang('ThisCourseDescriptionIsEmpty').'</em>';
  1813. }
  1814. return $data;
  1815. }
  1816. /*
  1817. ==============================================================================
  1818. DEPRECATED METHODS
  1819. ==============================================================================
  1820. */
  1821. /**
  1822. * This code creates a select form element to let the user
  1823. * choose a real course to link to.
  1824. *
  1825. * A good non-display library should not use echo statements, but just return text/html
  1826. * so users of the library can choose when to display.
  1827. *
  1828. * We display the course code, but internally store the course id.
  1829. *
  1830. * @param boolean $has_size, true the select tag gets a size element, false it stays a dropdownmenu
  1831. * @param boolean $only_current_user_courses, true only the real courses of which the
  1832. * current user is course admin are displayed, false all real courses are shown.
  1833. * @param string $element_name the name of the select element
  1834. * @return a string containing html code for a form select element.
  1835. * @deprecated Function not in use
  1836. */
  1837. public static function get_real_course_code_select_html($element_name, $has_size = true, $only_current_user_courses = true, $user_id) {
  1838. if ($only_current_user_courses) {
  1839. $real_course_list = self::get_real_course_list_of_user_as_course_admin($user_id);
  1840. } else {
  1841. $real_course_list = self::get_real_course_list();
  1842. }
  1843. if ($has_size) {
  1844. $size_element = "size=\"".SELECT_BOX_SIZE."\"";
  1845. } else {
  1846. $size_element = "";
  1847. }
  1848. $html_code = "<select name=\"$element_name\" $size_element >\n";
  1849. foreach ($real_course_list as $real_course) {
  1850. $course_code = $real_course["code"];
  1851. $html_code .= "<option value=\"".$course_code."\">";
  1852. $html_code .= $course_code;
  1853. $html_code .= "</option>\n";
  1854. }
  1855. $html_code .= "</select>\n";
  1856. return $html_code;
  1857. }
  1858. /**
  1859. * Get count rows of a table inside a course database
  1860. * @param string The table of which the rows should be counted
  1861. * @param int optionally count rows by session id
  1862. * @return int The number of rows in the given table.
  1863. */
  1864. public static function count_rows_course_table($table, $session_id = '') {
  1865. $condition_session = '';
  1866. if ($session_id !== '') {
  1867. $session_id = intval($session_id);
  1868. $condition_session = " WHERE session_id = '$session_id' ";
  1869. }
  1870. $sql = "SELECT COUNT(*) AS n FROM $table $condition_session ";
  1871. $rs = Database::query($sql, __FILE__, __LINE__);
  1872. $row = Database::fetch_row($rs);
  1873. return $row[0];
  1874. }
  1875. /**
  1876. * Subscribes "courses" to user (Dashboard feature)
  1877. * @param int user id
  1878. * @param string Course code
  1879. * @param int relation type
  1880. * @return
  1881. **/
  1882. public static function suscribe_courses_to_user($user_id, $course_list, $relation_stype) {
  1883. if ($user_id!= strval(intval($user_id))) return false;
  1884. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  1885. $tbl_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  1886. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  1887. $sql = "SELECT course_code FROM $tbl_course_rel_user WHERE user_id = $user_id AND relation_type = 1 ";
  1888. $result = Database::query($sql,__FILE__,__LINE__);
  1889. $existing_courses = array();
  1890. while($row = Database::fetch_array($result)){
  1891. $existing_courses[] = $row['course_code'];
  1892. }
  1893. //Deleting existing session_rel_user with relation ship = 1 only
  1894. foreach ($existing_courses as $existing_course) {
  1895. $existing_course = Database::escape_string($existing_course);
  1896. $sql = "DELETE FROM $tbl_course_rel_user WHERE course_code = '$existing_course' AND user_id = $user_id AND relation_type = 1 ";
  1897. Database::query($sql,__FILE__,__LINE__);
  1898. }
  1899. foreach ($course_list as $course_code) {
  1900. // for each session
  1901. $course_code = Database::escape_string($course_code);
  1902. $insert_sql = "INSERT IGNORE INTO $tbl_course_rel_user(course_code,user_id,relation_type) VALUES('$course_code','$user_id','1')";
  1903. Database::query($insert_sql,__FILE__,__LINE__);
  1904. }
  1905. }
  1906. } //end class CourseManager