session_import.php 37 KB

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