session_import.php 47 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * @package chamilo.admin
  5. */
  6. $language_file = array('admin', 'registration');
  7. $cidReset = true;
  8. require '../inc/global.inc.php';
  9. $this_section = SECTION_PLATFORM_ADMIN;
  10. api_protect_admin_script(true);
  11. require_once api_get_path(LIBRARY_PATH).'fileManage.lib.php';
  12. require_once api_get_path(LIBRARY_PATH).'mail.lib.inc.php';
  13. $form_sent = 0;
  14. $error_message = ''; // Avoid conflict with the global variable $error_msg (array type) in add_course.conf.php.
  15. if (isset($_GET['action']) && $_GET['action'] == 'show_message') {
  16. $error_message = Security::remove_XSS($_GET['message']);
  17. }
  18. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  19. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  20. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  21. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  22. $tbl_session_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  23. $tbl_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  24. $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  25. $tool_name = get_lang('ImportSessionListXMLCSV');
  26. $interbreadcrumb[] = array('url' => 'index.php', 'name' => get_lang('PlatformAdmin'));
  27. set_time_limit(0);
  28. // Set this option to true to enforce strict purification for usenames.
  29. $purification_option_for_usernames = false;
  30. $inserted_in_course = array();
  31. global $_configuration;
  32. if ($_POST['formSent']) {
  33. if (isset($_FILES['import_file']['tmp_name']) && !empty($_FILES['import_file']['tmp_name'])) {
  34. $form_sent = $_POST['formSent'];
  35. $file_type = $_POST['file_type'];
  36. $send_mail = $_POST['sendMail'] ? 1 : 0;
  37. //$updatesession = $_POST['updatesession'] ? 1 : 0;
  38. $updatesession = 0;
  39. $sessions = array();
  40. $session_counter = 0;
  41. if ($file_type == 'xml') {
  42. // XML
  43. // SimpleXML for PHP5 deals with various encodings, but how many they are, what are version issues, do we need to waste time with configuration options?
  44. // For avoiding complications we go some sort of "PHP4 way" - we convert the input xml-file into UTF-8 before passing it to the parser.
  45. // Instead of:
  46. // $root = @simplexml_load_file($_FILES['import_file']['tmp_name']);
  47. // we may use the following construct:
  48. // $root = @simplexml_load_string(api_utf8_encode_xml(file_get_contents($_FILES['import_file']['tmp_name'])));
  49. // To ease debugging let us use:
  50. $content = file_get_contents($_FILES['import_file']['tmp_name']);
  51. $content = api_utf8_encode_xml($content);
  52. $root = @simplexml_load_string($content);
  53. unset($content);
  54. if (is_object($root)) {
  55. if (count($root->Users->User) > 0) {
  56. // Creating/updating users from <Sessions> <Users> base node.
  57. foreach ($root->Users->User as $node_user) {
  58. $username = $username_old = trim(api_utf8_decode($node_user->Username));
  59. if (UserManager::is_username_available($username)) {
  60. $password = api_utf8_decode($node_user->Password);
  61. if (empty($password)) {
  62. $password = api_generate_password();
  63. }
  64. switch ($node_user->Status) {
  65. case 'student' :
  66. $status = 5;
  67. break;
  68. case 'teacher' :
  69. $status = 1;
  70. break;
  71. default :
  72. $status = 5;
  73. $error_message .= get_lang('StudentStatusWasGivenTo').' : '.$username.'<br />';
  74. }
  75. $result = UserManager::create_user(
  76. api_utf8_decode($node_user->Firstname),
  77. api_utf8_decode($node_user->Lastname),
  78. $status,
  79. api_utf8_decode($node_user->Email),
  80. $username,
  81. $password,
  82. api_utf8_decode($node_user->OfficialCode),
  83. null,
  84. api_utf8_decode($node_user->Phone),
  85. null,
  86. PLATFORM_AUTH_SOURCE,
  87. null,
  88. 1,
  89. 0,
  90. null,
  91. null,
  92. $send_mail
  93. );
  94. } else {
  95. $lastname = trim(api_utf8_decode($node_user->Lastname));
  96. $firstname = trim(api_utf8_decode($node_user->Firstname));
  97. $password = api_utf8_decode($node_user->Password);
  98. $email = trim(api_utf8_decode($node_user->Email));
  99. $official_code = trim(api_utf8_decode($node_user->OfficialCode));
  100. $phone = trim(api_utf8_decode($node_user->Phone));
  101. $status = trim(api_utf8_decode($node_user->Status));
  102. switch ($status) {
  103. case 'student' : $status = 5; break;
  104. case 'teacher' : $status = 1; break;
  105. default : $status = 5; $error_message .= get_lang('StudentStatusWasGivenTo').' : '.$username.'<br />';
  106. }
  107. $sql = "UPDATE $tbl_user SET
  108. lastname = '".Database::escape_string($lastname)."',
  109. firstname = '".Database::escape_string($firstname)."',
  110. ".(empty($password) ? "" : "password = '".(api_get_encrypted_password($password))."',")."
  111. email = '".Database::escape_string($email)."',
  112. official_code = '".Database::escape_string($official_code)."',
  113. phone = '".Database::escape_string($phone)."',
  114. status = '".Database::escape_string($status)."'
  115. WHERE username = '".Database::escape_string($username)."'";
  116. Database::query($sql);
  117. }
  118. }
  119. }
  120. // Creating courses from <Sessions> <Courses> base node.
  121. if (count($root->Courses->Course) > 0) {
  122. foreach ($root->Courses->Course as $courseNode) {
  123. $params = array();
  124. if (empty($courseNode->CourseTitle)) {
  125. $params['title'] = api_utf8_decode($courseNode->CourseCode);
  126. } else {
  127. $params['title'] = api_utf8_decode($courseNode->CourseTitle);
  128. }
  129. $params['wanted_code'] = api_utf8_decode($courseNode->CourseCode);
  130. $params['tutor_name'] = null;
  131. $params['course_category'] = null;
  132. $params['course_language'] = api_get_valid_language(api_utf8_decode($courseNode->CourseLanguage));
  133. $params['user_id'] = api_get_user_id();
  134. // Looking up for the teacher.
  135. $username = trim(api_utf8_decode($courseNode->CourseTeacher));
  136. $sql = "SELECT user_id, lastname, firstname FROM $tbl_user WHERE username='$username'";
  137. $rs = Database::query($sql);
  138. list($user_id, $lastname, $firstname) = Database::fetch_array($rs);
  139. $params['teachers'] = $user_id;
  140. CourseManager::create_course($params);
  141. }
  142. }
  143. // Creating sessions from <Sessions> base node.
  144. if (count($root->Session) > 0) {
  145. foreach ($root->Session as $node_session) {
  146. $course_counter = 0;
  147. $user_counter = 0;
  148. $session_name = trim(api_utf8_decode($node_session->SessionName));
  149. $coach = UserManager::purify_username(api_utf8_decode($node_session->Coach), $purification_option_for_usernames);
  150. if (!empty($coach)) {
  151. $coach_id = UserManager::get_user_id_from_username($coach);
  152. if ($coach_id === false) {
  153. $error_message .= get_lang('UserDoesNotExist').' : '.$coach.'<br />';
  154. // Forcing the coach id if user does not exist.
  155. $coach_id = api_get_user_id();
  156. }
  157. } else {
  158. // Forcing the coach id.
  159. $coach_id = api_get_user_id();
  160. }
  161. $date_start = trim(api_utf8_decode($node_session->DateStart)); // Just in case - encoding conversion.
  162. if (!empty($date_start)) {
  163. list($year_start, $month_start, $day_start) = explode('/', $date_start);
  164. if(empty($year_start) || empty($month_start) || empty($day_start)) {
  165. $error_message .= get_lang('WrongDate').' : '.$date_start.'<br />';
  166. break;
  167. } else {
  168. $time_start = mktime(0, 0, 0, $month_start, $day_start, $year_start);
  169. }
  170. $date_end = trim(api_utf8_decode($node_session->DateEnd));
  171. if (!empty($date_start)) {
  172. list($year_end, $month_end, $day_end) = explode('/', $date_end);
  173. if (empty($year_end) || empty($month_end) || empty($day_end)) {
  174. $error_message .= get_lang('Error').' : '.$date_end.'<br />';
  175. break;
  176. } else {
  177. $time_end = mktime(0, 0, 0, $month_end, $day_end, $year_end);
  178. }
  179. }
  180. if ($time_end - $time_start < 0) {
  181. $error_message .= get_lang('StartDateShouldBeBeforeEndDate').' : '.$date_end.'<br />';
  182. }
  183. }
  184. $visibility = trim(api_utf8_decode($node_session->Visibility));
  185. $session_category_id = trim(api_utf8_decode($node_session->SessionCategory));
  186. if (!$updatesession) {
  187. // Always create a session.
  188. $unique_name = false; // This MUST be initializead.
  189. $i = 0;
  190. // Change session name, verify that session doesn't exist.
  191. while (!$unique_name) {
  192. if ($i > 1) {
  193. $suffix = ' - '.$i;
  194. }
  195. $sql = 'SELECT 1 FROM '.$tbl_session.' WHERE name="'.Database::escape_string($session_name.$suffix).'"';
  196. $rs = Database::query($sql);
  197. if (Database::result($rs, 0, 0)) {
  198. $i++;
  199. } else {
  200. $unique_name = true;
  201. $session_name .= $suffix;
  202. }
  203. }
  204. // Creating the session.
  205. $sql_session = "INSERT IGNORE INTO $tbl_session SET
  206. name = '".Database::escape_string($session_name)."',
  207. id_coach = '$coach_id',
  208. date_start = '$date_start',
  209. date_end = '$date_end',
  210. visibility = '$visibility',
  211. session_category_id = '$session_category_id',
  212. session_admin_id=".intval($_user['user_id']);
  213. $rs_session = Database::query($sql_session);
  214. $session_id = Database::insert_id();
  215. $session_counter++;
  216. } else {
  217. // Update the session if it is needed.
  218. $my_session_result = SessionManager::get_session_by_name($session_name);
  219. if ($my_session_result === false) {
  220. // Creating the session.
  221. $sql_session = "INSERT IGNORE INTO $tbl_session SET
  222. name = '".Database::escape_string($session_name)."',
  223. id_coach = '$coach_id',
  224. date_start = '$date_start',
  225. date_end = '$date_end',
  226. visibility = '$visibility',
  227. session_category_id = '$session_category_id',
  228. session_admin_id=".intval($_user['user_id']);
  229. $rs_session = Database::query($sql_session);
  230. $session_id = Database::insert_id();
  231. $session_counter++;
  232. } else {
  233. // if the session already exists - update it.
  234. $sql_session = "UPDATE $tbl_session SET
  235. id_coach = '$coach_id',
  236. date_start = '$date_start',
  237. date_end = '$date_end',
  238. visibility = '$visibility',
  239. session_category_id = '$session_category_id'
  240. WHERE name = '$session_name'";
  241. $rs_session = Database::query($sql_session);
  242. $session_id = Database::query("SELECT id FROM $tbl_session WHERE name='$session_name'");
  243. list($session_id) = Database::fetch_array($session_id);
  244. Database::query("DELETE FROM $tbl_session_user WHERE id_session='$session_id'");
  245. Database::query("DELETE FROM $tbl_session_course WHERE id_session='$session_id'");
  246. Database::query("DELETE FROM $tbl_session_course_user WHERE id_session='$session_id'");
  247. }
  248. }
  249. // Associate the session with access_url.
  250. global $_configuration;
  251. if ($_configuration['multiple_access_urls']) {
  252. $access_url_id = api_get_current_access_url_id();
  253. UrlManager::add_session_to_url($session_id, $access_url_id);
  254. } else {
  255. // We fill by default the access_url_rel_session table.
  256. UrlManager::add_session_to_url($session_id, 1);
  257. }
  258. // Adding users to the new session.
  259. foreach ($node_session->User as $node_user) {
  260. $username = UserManager::purify_username(api_utf8_decode($node_user), $purification_option_for_usernames);
  261. $user_id = UserManager::get_user_id_from_username($username);
  262. if ($user_id !== false) {
  263. $sql = "INSERT IGNORE INTO $tbl_session_user SET
  264. id_user='$user_id',
  265. id_session = '$session_id'";
  266. $rs_user = Database::query($sql);
  267. $user_counter++;
  268. }
  269. }
  270. // Adding courses to a session.
  271. foreach ($node_session->Course as $node_course) {
  272. $course_code = Database::escape_string(trim(api_utf8_decode($node_course->CourseCode)));
  273. // Verify that the course pointed by the course code node exists.
  274. if (CourseManager::course_exists($course_code)) {
  275. // If the course exists we continue.
  276. $course_info = CourseManager::get_course_information($course_code);
  277. $session_course_relation = SessionManager::relation_session_course_exist($session_id, $course_code);
  278. if (!$session_course_relation) {
  279. $sql_course = "INSERT INTO $tbl_session_course SET
  280. course_code = '$course_code',
  281. id_session='$session_id'";
  282. $rs_course = Database::query($sql_course);
  283. }
  284. $course_coaches = explode(',', $node_course->Coach);
  285. // Adding coachs to session course user
  286. foreach ($course_coaches as $course_coach) {
  287. $coach_id = UserManager::purify_username(api_utf8_decode($course_coach), $purification_option_for_usernames);
  288. $coach_id = UserManager::get_user_id_from_username($course_coach);
  289. if ($coach_id !== false) {
  290. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  291. id_user='$coach_id',
  292. course_code='$course_code',
  293. id_session = '$session_id',
  294. status = 2 ";
  295. $rs_coachs = Database::query($sql);
  296. } else {
  297. $error_message .= get_lang('UserDoesNotExist').' : '.$user.'<br />';
  298. }
  299. }
  300. // Adding users.
  301. $course_counter++;
  302. $users_in_course_counter = 0;
  303. foreach ($node_course->User as $node_user) {
  304. $username = UserManager::purify_username(api_utf8_decode($node_user), $purification_option_for_usernames);
  305. $user_id = UserManager::get_user_id_from_username($username);
  306. if ($user_id !== false) {
  307. // Adding to session_rel_user table.
  308. $sql = "INSERT IGNORE INTO $tbl_session_user SET
  309. id_user='$user_id',
  310. id_session = '$session_id'";
  311. $rs_user = Database::query($sql);
  312. $user_counter++;
  313. // Adding to session_rel_user_rel_course table.
  314. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  315. id_user='$user_id',
  316. course_code='$course_code',
  317. id_session = '$session_id'";
  318. $rs_users = Database::query($sql);
  319. $users_in_course_counter++;
  320. } else {
  321. $error_message .= get_lang('UserDoesNotExist').' : '.$username.'<br />';
  322. }
  323. }
  324. $update_session_course = "UPDATE $tbl_session_course SET nbr_users='$users_in_course_counter' WHERE course_code='$course_code'";
  325. Database::query($update_session_course);
  326. $inserted_in_course[$course_code] = $course_info['title'];
  327. }
  328. if (CourseManager::course_exists($course_code, true)) {
  329. // If the course exists we continue.
  330. // Also subscribe to virtual courses through check on visual code.
  331. $list = CourseManager :: get_courses_info_from_visual_code($course_code);
  332. foreach ($list as $vcourse) {
  333. if ($vcourse['code'] == $course_code) {
  334. // Ignore, this has already been inserted.
  335. } else {
  336. $sql_course = "INSERT INTO $tbl_session_course SET
  337. course_code = '".$vcourse['code']."',
  338. id_session='$session_id'";
  339. $rs_course = Database::query($sql_course);
  340. $course_coaches = explode(",",$node_course->Coach);
  341. // adding coachs to session course user
  342. foreach ($course_coaches as $course_coach) {
  343. $coach_id = UserManager::purify_username(api_utf8_decode($course_coach), $purification_option_for_usernames);
  344. $coach_id = UserManager::get_user_id_from_username($course_coach);
  345. if ($coach_id !== false) {
  346. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  347. id_user='$coach_id',
  348. course_code='{$vcourse['code']}',
  349. id_session = '$session_id',
  350. status = 2 ";
  351. $rs_coachs = Database::query($sql);
  352. } else {
  353. $error_message .= get_lang('UserDoesNotExist').' : '.$user.'<br />';
  354. }
  355. }
  356. // adding users
  357. $course_counter++;
  358. $users_in_course_counter = 0;
  359. foreach ($node_course->User as $node_user) {
  360. $username = UserManager::purify_username(api_utf8_decode($node_user), $purification_option_for_usernames);
  361. $user_id = UserManager::get_user_id_from_username($username);
  362. if ($user_id !== false) {
  363. // Adding to session_rel_user table.
  364. $sql = "INSERT IGNORE INTO $tbl_session_user SET
  365. id_user='$user_id',
  366. id_session = '$session_id'";
  367. $rs_user = Database::query($sql);
  368. $user_counter++;
  369. // Adding to session_rel_user_rel_course table.
  370. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  371. id_user='$user_id',
  372. course_code='{$vcourse['code']}',
  373. id_session = '$session_id'";
  374. $rs_users = Database::query($sql);
  375. $users_in_course_counter++;
  376. } else {
  377. $error_message .= get_lang('UserDoesNotExist').' : '.$username.'<br />';
  378. }
  379. }
  380. $update_session_course = "UPDATE $tbl_session_course SET nbr_users='$users_in_course_counter' WHERE course_code='$course_code'";
  381. Database::query($update_session_course);
  382. $inserted_in_course[$course_code] = $course_info['title'];
  383. }
  384. $inserted_in_course[$vcourse['code']] = $vcourse['title'];
  385. }
  386. } else {
  387. // Tthe course does not exist.
  388. $error_message .= get_lang('CourseDoesNotExist').' : '.$course_code.'<br />';
  389. }
  390. }
  391. Database::query("UPDATE $tbl_session SET nbr_users='$user_counter', nbr_courses='$course_counter' WHERE id='$session_id'");
  392. }
  393. }
  394. if (empty($root->Users->User) && empty($root->Courses->Course) && empty($root->Session)) {
  395. $error_message = get_lang('NoNeededData');
  396. }
  397. } else {
  398. $error_message .= get_lang('XMLNotValid');
  399. }
  400. } else {
  401. // CSV
  402. $content = file($_FILES['import_file']['tmp_name']);
  403. if (!api_strstr($content[0], ';')) {
  404. $error_message = get_lang('NotCSV');
  405. } else {
  406. $tag_names = array();
  407. foreach ($content as $key => $enreg) {
  408. $enreg = explode(';', trim($enreg));
  409. if ($key) {
  410. foreach ($tag_names as $tag_key => $tag_name) {
  411. $sessions[$key - 1][$tag_name] = $enreg[$tag_key];
  412. }
  413. } else {
  414. foreach ($enreg as $tag_name) {
  415. $tag_names[] = api_preg_replace('/[^a-zA-Z0-9_\-]/', '', $tag_name);
  416. }
  417. if (!in_array('SessionName', $tag_names) || !in_array('DateStart', $tag_names) || !in_array('DateEnd', $tag_names)) {
  418. $error_message = get_lang('NoNeededData');
  419. break;
  420. }
  421. }
  422. }
  423. // Looping the sessions.
  424. foreach ($sessions as $enreg) {
  425. $user_counter = 0;
  426. $course_counter = 0;
  427. $session_name = $enreg['SessionName'];
  428. $date_start = $enreg['DateStart'];
  429. $date_end = $enreg['DateEnd'];
  430. $visibility = $enreg['Visibility'];
  431. $session_category_id = $enreg['SessionCategory'];
  432. // Searching a coach.
  433. if (!empty($enreg['Coach'])) {
  434. $coach_id = UserManager::get_user_id_from_username($enreg['Coach']);
  435. if ($coach_id === false) {
  436. // If the coach-user does not exist - I'm the coach.
  437. $coach_id = api_get_user_id();
  438. }
  439. } else {
  440. $coach_id = api_get_user_id();
  441. }
  442. if (!$updatesession) {
  443. // Always create a session.
  444. $unique_name = false; // This MUST be initializead.
  445. $i = 0;
  446. // Change session name, verify that session doesn't exist.
  447. while (!$unique_name) {
  448. if ($i > 1) {
  449. $suffix = ' - '.$i;
  450. }
  451. $sql = 'SELECT 1 FROM '.$tbl_session.' WHERE name="'.Database::escape_string($session_name.$suffix).'"';
  452. $rs = Database::query($sql);
  453. if (Database::result($rs, 0, 0)) {
  454. $i++;
  455. } else {
  456. $unique_name = true;
  457. $session_name .= $suffix;
  458. }
  459. }
  460. // Creating the session.
  461. $sql_session = "INSERT IGNORE INTO $tbl_session SET
  462. name = '".Database::escape_string($session_name)."',
  463. id_coach = '$coach_id',
  464. date_start = '$date_start',
  465. date_end = '$date_end',
  466. visibility = '$visibility',
  467. session_category_id = '$session_category_id',
  468. session_admin_id=".intval($_user['user_id']);
  469. $rs_session = Database::query($sql_session);
  470. $session_id = Database::insert_id();
  471. $session_counter++;
  472. } else {
  473. $my_session_result = SessionManager::get_session_by_name($session_name);
  474. if ($my_session_result === false) {
  475. // Creating a session.
  476. $sql_session = "INSERT IGNORE INTO $tbl_session SET
  477. name = '$session_name',
  478. id_coach = '$coach_id',
  479. date_start = '$date_start',
  480. date_end = '$date_end',
  481. visibility = '$visibility',
  482. session_category_id = '$session_category_id'";
  483. $rs_session = Database::query($sql_session);
  484. // We get the last insert id.
  485. $my_session_result = SessionManager::get_session_by_name($session_name);
  486. $session_id = $my_session_result['id'];
  487. //echo '<br>';
  488. } else {
  489. // The session already exists, update it then.
  490. $sql_session = "UPDATE $tbl_session SET
  491. id_coach = '$coach_id',
  492. date_start = '$date_start',
  493. date_end = '$date_end',
  494. visibility = '$visibility',
  495. session_category_id = '$session_category_id'
  496. WHERE name = '$session_name'";
  497. $rs_session = Database::query($sql_session);
  498. $session_id = Database::query("SELECT id FROM $tbl_session WHERE name='$session_name'");
  499. list($session_id) = Database::fetch_array($session_id);
  500. Database::query("DELETE FROM $tbl_session_user WHERE id_session='$session_id'");
  501. Database::query("DELETE FROM $tbl_session_course WHERE id_session='$session_id'");
  502. Database::query("DELETE FROM $tbl_session_course_user WHERE id_session='$session_id'");
  503. }
  504. $session_counter++;
  505. }
  506. $users = explode('|', $enreg['Users']);
  507. // Adding the relationship "Session - User".
  508. if (is_array($users)) {
  509. foreach ($users as $user) {
  510. $user_id = UserManager::get_user_id_from_username($user);
  511. if ($user_id !== false) {
  512. // Insert new users.
  513. $sql = "INSERT IGNORE INTO $tbl_session_user SET
  514. id_user='$user_id',
  515. id_session = '$session_id'";
  516. $rs_user = Database::query($sql);
  517. $user_counter++;
  518. }
  519. }
  520. }
  521. $courses = explode('|', $enreg['Courses']);
  522. foreach ($courses as $course) {
  523. $course_code = api_strtoupper(api_substr($course, 0, api_strpos($course, '[')));
  524. if (CourseManager::course_exists($course_code)) {
  525. // If the course exists we continue.
  526. $course_info = CourseManager::get_course_information($course_code);
  527. $coach = api_strstr($course, '[');
  528. $coach = api_substr($coach, 1, api_strpos($coach,']') - 1);
  529. if (!empty($coach)) {
  530. $coach_id = UserManager::get_user_id_from_username($coach);
  531. if ($coach_id === false) {
  532. $coach_id = '';
  533. }
  534. } else {
  535. $coach = '';
  536. }
  537. // Adding the course to a session.
  538. $sql_course = "INSERT IGNORE INTO $tbl_session_course SET
  539. course_code = '$course_code',
  540. id_session='$session_id'";
  541. $rs_course = Database::query($sql_course);
  542. $course_counter++;
  543. $course_split = array();
  544. $pattern = "/\[(.*?)\]/";
  545. preg_match_all($pattern, $course, $matches);
  546. if (isset($matches[1])) {
  547. $course_coaches = $matches[1][0];
  548. $course_users = $matches[1][1];
  549. }
  550. $course_users = explode(',', $course_users);
  551. $course_coaches = explode(',', $course_coaches);
  552. // Adding coaches to session course user
  553. if (!empty($course_coaches)) {
  554. foreach ($course_coaches as $course_coach) {
  555. $coach_id = UserManager::get_user_id_from_username($course_coach);
  556. if ($coach_id !== false) {
  557. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  558. id_user='$coach_id',
  559. course_code='$course_code',
  560. id_session = '$session_id',
  561. status = 2 ";
  562. $rs_coachs = Database::query($sql);
  563. } else {
  564. $error_message .= get_lang('UserDoesNotExist').' : '.$user.'<br />';
  565. }
  566. }
  567. }
  568. $users_in_course_counter = 0;
  569. // Adding the relationship "Session - Course - User".
  570. foreach ($course_users as $user) {
  571. $user_id = UserManager::get_user_id_from_username($user);
  572. if ($user_id !== false) {
  573. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  574. id_user='$user_id',
  575. course_code='$course_code',
  576. id_session = '$session_id'";
  577. $rs_users = Database::query($sql);
  578. $users_in_course_counter++;
  579. } else {
  580. $error_message .= get_lang('UserDoesNotExist').' : '.$user.'<br />';
  581. }
  582. }
  583. $sql = "UPDATE $tbl_session_course SET nbr_users='$users_in_course_counter' WHERE course_code='$course_code'";
  584. Database::query($sql);
  585. $course_info = CourseManager::get_course_information($course_code);
  586. $inserted_in_course[$course_code] = $course_info['title'];
  587. } else {
  588. // TODO: We should create the course as in the XML import.
  589. }
  590. if (CourseManager::course_exists($course_code, true)) {
  591. $list = CourseManager :: get_courses_info_from_visual_code($course_code);
  592. foreach ($list as $vcourse) {
  593. if ($vcourse['code'] == $course_code) {
  594. // Ignore, this has already been inserted.
  595. } else {
  596. $coach = api_strstr($course, '[');
  597. $coach = api_substr($coach, 1, api_strpos($coach,']') - 1);
  598. // Adding the relationship "Session - Course".
  599. $sql_course = "INSERT IGNORE INTO $tbl_session_course SET
  600. course_code = '".$vcourse['code']."',
  601. id_session='$session_id'";
  602. $rs_course = Database::query($sql_course);
  603. // adding coachs to session course user
  604. foreach ($course_coaches as $course_coach) {
  605. $coach_id = UserManager::get_user_id_from_username($course_coach);
  606. if ($coach_id !== false) {
  607. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  608. id_user='$coach_id',
  609. course_code='{$vcourse['code']}',
  610. id_session = '$session_id',
  611. status = 2 ";
  612. $rs_coachs = Database::query($sql);
  613. } else {
  614. $error_message .= get_lang('UserDoesNotExist').' : '.$user.'<br />';
  615. }
  616. }
  617. $users_in_course_counter = 0;
  618. // Adding the relationship "Session - Course - User".
  619. foreach ($course_users as $user) {
  620. $user_id = UserManager::get_user_id_from_username($user);
  621. if ($user_id !== false) {
  622. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  623. id_user='$user_id',
  624. course_code='{$vcourse['code']}',
  625. id_session = '$session_id'";
  626. $rs_users = Database::query($sql);
  627. $users_in_course_counter++;
  628. } else {
  629. $error_message .= get_lang('UserDoesNotExist').' : '.$user.'<br />';
  630. }
  631. }
  632. Database::query("UPDATE $tbl_session_course SET nbr_users='$users_in_course_counter' WHERE course_code='".$vcourse['code']."'");
  633. }
  634. }
  635. $inserted_in_course[$vcourse['code']] = $vcourse['title'];
  636. }
  637. }
  638. $access_url_id = api_get_current_access_url_id();
  639. UrlManager::add_session_to_url($session_id, $access_url_id);
  640. $sql_update_users = "UPDATE $tbl_session SET nbr_users='$user_counter', nbr_courses='$course_counter' WHERE id='$session_id'";
  641. Database::query($sql_update_users);
  642. }
  643. }
  644. }
  645. if (!empty($error_message)) {
  646. $error_message = get_lang('ButProblemsOccured').' :<br />'.$error_message;
  647. }
  648. if (count($inserted_in_course) > 1) {
  649. $warn = get_lang('SeveralCoursesSubscribedToSessionBecauseOfSameVisualCode').': ';
  650. foreach ($inserted_in_course as $code => $title) {
  651. $warn .= ' '.$title.' ('.$code.'),';
  652. }
  653. $warn = substr($warn, 0, -1);
  654. }
  655. if ($session_counter == 1) {
  656. header('Location: resume_session.php?id_session='.$session_id.'&warn='.urlencode($warn));
  657. exit;
  658. } else {
  659. header('Location: session_list.php?action=show_message&message='.urlencode(get_lang('FileImported').' '.$error_message).'&warn='.urlencode($warn));
  660. exit;
  661. }
  662. } else {
  663. $error_message = get_lang('NoInputFile');
  664. }
  665. }
  666. // Display the header.
  667. Display::display_header($tool_name);
  668. if (count($inserted_in_course) > 1) {
  669. $msg = get_lang('SeveralCoursesSubscribedToSessionBecauseOfSameVisualCode').': ';
  670. foreach ($inserted_in_course as $code => $title) {
  671. $msg .= ' '.$title.' ('.$title.'),';
  672. }
  673. $msg = substr($msg, 0, -1);
  674. Display::display_warning_message($msg);
  675. }
  676. echo '<div class="actions">';
  677. echo '<a href="../admin/index.php">'.Display::return_icon('back.png', get_lang('BackTo').' '.get_lang('PlatformAdmin'),'',ICON_SIZE_MEDIUM).'</a>';
  678. echo '</div>';
  679. if (!empty($error_message)) {
  680. Display::display_normal_message($error_message, false);
  681. }
  682. $form = new FormValidator('import_sessions', 'post', api_get_self(), null, array('enctype' => 'multipart/form-data'));
  683. $form->addElement('hidden', 'formSent', 1);
  684. $form->addElement('file', 'import_file', get_lang('ImportFileLocation'));
  685. $form->addElement('radio', 'file_type', array(get_lang('FileType'), '<a href="example_session.xml" target="_blank">'.get_lang('ExampleXMLFile').'</a>'), 'CSV', 'csv');
  686. $form->addElement('radio', 'file_type', array(null, '<a href="example_session.csv" target="_blank">'.get_lang('ExampleCSVFile').'</a>'), 'XML', 'xml');
  687. $form->addElement('checkbox', 'sendMail', null, get_lang('SendMailToUsers'));
  688. $form->addElement('button', 'submit', get_lang('ImportSession'));
  689. $defaults = array('sendMail' => 'true','file_type' => 'csv');
  690. $form->setDefaults($defaults);
  691. $form->display();
  692. ?>
  693. <font color="gray">
  694. <p><?php echo get_lang('CSVMustLookLike').' ('.get_lang('MandatoryFields').')'; ?> :</p>
  695. <blockquote>
  696. <pre>
  697. <strong>SessionName</strong>;Coach;<strong>DateStart</strong>;<strong>DateEnd</strong>;Users;Courses
  698. <strong>xxx1</strong>;xxx;<strong>xxx;xxx</strong>;username1|username2;course1[coach1][username1,username2,...]|course2[coach1][username1,username2,...]
  699. <strong>xxx2</strong>;xxx;<strong>xxx;xxx</strong>;username1|username2;course1[coach1][username1,username2,...]|course2[coach1][username1,username2,...]
  700. </pre>
  701. </blockquote>
  702. <p><?php echo get_lang('XMLMustLookLike').' ('.get_lang('MandatoryFields').')'; ?> :</p>
  703. <blockquote>
  704. <pre>
  705. &lt;?xml version=&quot;1.0&quot; encoding=&quot;<?php echo api_refine_encoding_id(api_get_system_encoding()); ?>&quot;?&gt;
  706. &lt;Sessions&gt;
  707. &lt;Users&gt;
  708. &lt;User&gt;
  709. &lt;Username&gt;<strong>username1</strong>&lt;/Username&gt;
  710. &lt;Lastname&gt;xxx&lt;/Lastname&gt;
  711. &lt;Firstname&gt;xxx&lt;/Firstname&gt;
  712. &lt;Password&gt;xxx&lt;/Password&gt;
  713. &lt;Email&gt;xxx@xx.xx&lt;/Email&gt;
  714. &lt;OfficialCode&gt;xxx&lt;/OfficialCode&gt;
  715. &lt;Phone&gt;xxx&lt;/Phone&gt;
  716. &lt;Status&gt;student|teacher&lt;/Status&gt;
  717. &lt;/User&gt;
  718. &lt;/Users&gt;
  719. &lt;Courses&gt;
  720. &lt;Course&gt;
  721. &lt;CourseCode&gt;<strong>xxx</strong>&lt;/CourseCode&gt;
  722. &lt;CourseTeacher&gt;<strong>teacher_username</strong>&lt;/CourseTeacher&gt;
  723. &lt;CourseLanguage&gt;xxx&lt;/CourseLanguage&gt;
  724. &lt;CourseTitle&gt;xxx&lt;/CourseTitle&gt;
  725. &lt;CourseDescription&gt;xxx&lt;/CourseDescription&gt;
  726. &lt;/Course&gt;
  727. &lt;/Courses&gt;
  728. &lt;Session&gt;
  729. <strong>&lt;SessionName&gt;xxx&lt;/SessionName&gt;</strong>
  730. &lt;Coach&gt;xxx&lt;/Coach&gt;
  731. <strong>&lt;DateStart&gt;xxx&lt;/DateStart&gt;</strong>
  732. <strong>&lt;DateEnd&gt;xxx&lt;/DateEnd&gt;</strong>
  733. &lt;User&gt;xxx&lt;/User&gt;
  734. &lt;User&gt;xxx&lt;/User&gt;
  735. &lt;Course&gt;
  736. &lt;CourseCode&gt;coursecode1&lt;/CourseCode&gt;
  737. &lt;Coach&gt;coach1&lt;/Coach&gt;
  738. &lt;User&gt;username1&lt;/User&gt;
  739. &lt;User&gt;username2&lt;/User&gt;
  740. &lt;/Course&gt;
  741. &lt;/Session&gt;
  742. &lt;Session&gt;
  743. <strong>&lt;SessionName&gt;xxx&lt;/SessionName&gt;</strong>
  744. &lt;Coach&gt;xxx&lt;/Coach&gt;
  745. <strong>&lt;DateStart&gt;xxx&lt;/DateStart&gt;</strong>
  746. <strong>&lt;DateEnd&gt;xxx&lt;/DateEnd&gt;</strong>
  747. &lt;User&gt;xxx&lt;/User&gt;
  748. &lt;User&gt;xxx&lt;/User&gt;
  749. &lt;Course&gt;
  750. &lt;CourseCode&gt;coursecode1&lt;/CourseCode&gt;
  751. &lt;Coach&gt;coach1&lt;/Coach&gt;
  752. &lt;User&gt;username1&lt;/User&gt;
  753. &lt;User&gt;username2&lt;/User&gt;
  754. &lt;/Course&gt;
  755. &lt;/Session&gt;
  756. &lt;/Sessions&gt;
  757. </pre>
  758. </blockquote>
  759. </font>
  760. <?php
  761. /* FOOTER */
  762. Display::display_footer();