session_import.php 51 KB

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