session_import.php 54 KB

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