session_import.php 54 KB

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