session_import.php 39 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986
  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('PlatformAdmin'));
  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);
  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);
  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);
  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);
  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);
  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. $visibility = trim(api_utf8_decode($node_session->Visibility));
  251. $session_category_id = trim(api_utf8_decode($node_session->SessionCategory));
  252. if (!$updatesession) {
  253. // Always create a session.
  254. $unique_name = false; // This MUST be initializead.
  255. $i = 0;
  256. // Change session name, verify that session doesn't exist.
  257. while (!$unique_name) {
  258. if ($i > 1) {
  259. $suffix = ' - '.$i;
  260. }
  261. $sql = 'SELECT 1 FROM '.$tbl_session.' WHERE name="'.Database::escape_string($session_name.$suffix).'"';
  262. $rs = Database::query($sql);
  263. if (Database::result($rs, 0, 0)) {
  264. $i++;
  265. } else {
  266. $unique_name = true;
  267. $session_name .= $suffix;
  268. }
  269. }
  270. // Creating the session.
  271. $sql_session = "INSERT IGNORE INTO $tbl_session SET
  272. name = '".Database::escape_string($session_name)."',
  273. id_coach = '$coach_id',
  274. date_start = '$date_start',
  275. date_end = '$date_end',
  276. visibility = '$visibility',
  277. session_category_id = '$session_category_id',
  278. session_admin_id=".intval($_user['user_id']);
  279. $rs_session = Database::query($sql_session);
  280. $session_id = Database::insert_id();
  281. $session_counter++;
  282. } else {
  283. // Update the session if it is needed.
  284. $my_session_result = SessionManager::get_session_by_name($session_name);
  285. if ($my_session_result === false) {
  286. // Creating the session.
  287. $sql_session = "INSERT IGNORE INTO $tbl_session SET
  288. name = '".Database::escape_string($session_name)."',
  289. id_coach = '$coach_id',
  290. date_start = '$date_start',
  291. date_end = '$date_end',
  292. visibility = '$visibility',
  293. session_category_id = '$session_category_id',
  294. session_admin_id=".intval($_user['user_id']);
  295. $rs_session = Database::query($sql_session);
  296. $session_id = Database::insert_id();
  297. $session_counter++;
  298. } else {
  299. // if the session already exists - update it.
  300. $sql_session = "UPDATE $tbl_session SET
  301. id_coach = '$coach_id',
  302. date_start = '$date_start',
  303. date_end = '$date_end',
  304. visibility = '$visibility',
  305. session_category_id = '$session_category_id'
  306. WHERE name = '$session_name'";
  307. $rs_session = Database::query($sql_session);
  308. $session_id = Database::query("SELECT id FROM $tbl_session WHERE name='$session_name'");
  309. list($session_id) = Database::fetch_array($session_id);
  310. Database::query("DELETE FROM $tbl_session_user WHERE id_session='$session_id'");
  311. Database::query("DELETE FROM $tbl_session_course WHERE id_session='$session_id'");
  312. Database::query("DELETE FROM $tbl_session_course_user WHERE id_session='$session_id'");
  313. }
  314. }
  315. // Associate the session with access_url.
  316. global $_configuration;
  317. require_once api_get_path(LIBRARY_PATH).'urlmanager.lib.php';
  318. if ($_configuration['multiple_access_urls']) {
  319. $tbl_user_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
  320. $access_url_id = api_get_current_access_url_id();
  321. UrlManager::add_session_to_url($session_id, $access_url_id);
  322. } else {
  323. // We fill by default the access_url_rel_session table.
  324. UrlManager::add_session_to_url($session_id, 1);
  325. }
  326. // Adding users to the new session.
  327. foreach ($node_session->User as $node_user) {
  328. $username = UserManager::purify_username(api_utf8_decode($node_user), $purification_option_for_usernames);
  329. $user_id = UserManager::get_user_id_from_username($username);
  330. if ($user_id !== false) {
  331. $sql = "INSERT IGNORE INTO $tbl_session_user SET
  332. id_user='$user_id',
  333. id_session = '$session_id'";
  334. $rs_user = Database::query($sql);
  335. $user_counter++;
  336. }
  337. }
  338. // Adding courses to a session.
  339. foreach ($node_session->Course as $node_course) {
  340. $course_code = Database::escape_string(trim(api_utf8_decode($node_course->CourseCode)));
  341. // Verify that the course pointed by the course code node exists.
  342. if (CourseManager::course_exists($course_code)) {
  343. // If the course exists we continue.
  344. $course_info = CourseManager::get_course_information($course_code);
  345. $session_course_relation = SessionManager::relation_session_course_exist($session_id, $course_code);
  346. if (!$session_course_relation) {
  347. $sql_course = "INSERT INTO $tbl_session_course SET
  348. course_code = '$course_code',
  349. id_session='$session_id'";
  350. $rs_course = Database::query($sql_course);
  351. }
  352. $course_coachs = explode(",",$node_course->Coach);
  353. // adding coachs to session course user
  354. foreach ($course_coachs as $course_coach) {
  355. $coach_id = UserManager::purify_username(api_utf8_decode($course_coach), $purification_option_for_usernames);
  356. $coach_id = UserManager::get_user_id_from_username($course_coach);
  357. if ($coach_id !== false) {
  358. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  359. id_user='$coach_id',
  360. course_code='$course_code',
  361. id_session = '$session_id',
  362. status = 2 ";
  363. $rs_coachs = Database::query($sql);
  364. } else {
  365. $error_message .= get_lang('UserDoesNotExist').' : '.$user.'<br />';
  366. }
  367. }
  368. // adding users
  369. $course_counter++;
  370. $users_in_course_counter = 0;
  371. foreach ($node_course->User as $node_user) {
  372. $username = UserManager::purify_username(api_utf8_decode($node_user), $purification_option_for_usernames);
  373. $user_id = UserManager::get_user_id_from_username($username);
  374. if ($user_id !== false) {
  375. // Adding to session_rel_user table.
  376. $sql = "INSERT IGNORE INTO $tbl_session_user SET
  377. id_user='$user_id',
  378. id_session = '$session_id'";
  379. $rs_user = Database::query($sql);
  380. $user_counter++;
  381. // Adding to session_rel_user_rel_course table.
  382. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  383. id_user='$user_id',
  384. course_code='$course_code',
  385. id_session = '$session_id'";
  386. $rs_users = Database::query($sql);
  387. $users_in_course_counter++;
  388. } else {
  389. $error_message .= get_lang('UserDoesNotExist').' : '.$username.'<br />';
  390. }
  391. }
  392. $update_session_course = "UPDATE $tbl_session_course SET nbr_users='$users_in_course_counter' WHERE course_code='$course_code'";
  393. Database::query($update_session_course);
  394. $inserted_in_course[$course_code] = $course_info['title'];
  395. }
  396. if (CourseManager::course_exists($course_code, true)) {
  397. // If the course exists we continue.
  398. // Also subscribe to virtual courses through check on visual code.
  399. $list = CourseManager :: get_courses_info_from_visual_code($course_code);
  400. foreach ($list as $vcourse) {
  401. if ($vcourse['code'] == $course_code) {
  402. // Ignore, this has already been inserted.
  403. } else {
  404. $sql_course = "INSERT INTO $tbl_session_course SET
  405. course_code = '".$vcourse['code']."',
  406. id_session='$session_id'";
  407. $rs_course = Database::query($sql_course);
  408. $course_coachs = explode(",",$node_course->Coach);
  409. // adding coachs to session course user
  410. foreach ($course_coachs as $course_coach) {
  411. $coach_id = UserManager::purify_username(api_utf8_decode($course_coach), $purification_option_for_usernames);
  412. $coach_id = UserManager::get_user_id_from_username($course_coach);
  413. if ($coach_id !== false) {
  414. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  415. id_user='$coach_id',
  416. course_code='{$vcourse['code']}',
  417. id_session = '$session_id',
  418. status = 2 ";
  419. $rs_coachs = Database::query($sql);
  420. } else {
  421. $error_message .= get_lang('UserDoesNotExist').' : '.$user.'<br />';
  422. }
  423. }
  424. // adding users
  425. $course_counter++;
  426. $users_in_course_counter = 0;
  427. foreach ($node_course->User as $node_user) {
  428. $username = UserManager::purify_username(api_utf8_decode($node_user), $purification_option_for_usernames);
  429. $user_id = UserManager::get_user_id_from_username($username);
  430. if ($user_id !== false) {
  431. // Adding to session_rel_user table.
  432. $sql = "INSERT IGNORE INTO $tbl_session_user SET
  433. id_user='$user_id',
  434. id_session = '$session_id'";
  435. $rs_user = Database::query($sql);
  436. $user_counter++;
  437. // Adding to session_rel_user_rel_course table.
  438. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  439. id_user='$user_id',
  440. course_code='{$vcourse['code']}',
  441. id_session = '$session_id'";
  442. $rs_users = Database::query($sql);
  443. $users_in_course_counter++;
  444. } else {
  445. $error_message .= get_lang('UserDoesNotExist').' : '.$username.'<br />';
  446. }
  447. }
  448. $update_session_course = "UPDATE $tbl_session_course SET nbr_users='$users_in_course_counter' WHERE course_code='$course_code'";
  449. Database::query($update_session_course);
  450. $inserted_in_course[$course_code] = $course_info['title'];
  451. }
  452. $inserted_in_course[$vcourse['code']] = $vcourse['title'];
  453. }
  454. } else {
  455. // Tthe course does not exist.
  456. $error_message .= get_lang('CourseDoesNotExist').' : '.$course_code.'<br />';
  457. }
  458. }
  459. Database::query("UPDATE $tbl_session SET nbr_users='$user_counter', nbr_courses='$course_counter' WHERE id='$session_id'");
  460. }
  461. }
  462. if (empty($root->Users->User) && empty($root->Courses->Course) && empty($root->Session)) {
  463. $error_message = get_lang('NoNeededData');
  464. }
  465. } else {
  466. $error_message .= get_lang('XMLNotValid');
  467. }
  468. } else {
  469. /////////////////////
  470. // CSV /////////////
  471. ///////////////////
  472. $content = file($_FILES['import_file']['tmp_name']);
  473. if (!api_strstr($content[0], ';')) {
  474. $error_message = get_lang('NotCSV');
  475. } else {
  476. $tag_names = array();
  477. foreach ($content as $key => $enreg) {
  478. $enreg = explode(';', trim($enreg));
  479. if ($key) {
  480. foreach ($tag_names as $tag_key => $tag_name) {
  481. $sessions[$key - 1][$tag_name] = $enreg[$tag_key];
  482. }
  483. } else {
  484. foreach ($enreg as $tag_name) {
  485. $tag_names[] = api_preg_replace('/[^a-zA-Z0-9_\-]/', '', $tag_name);
  486. }
  487. if (!in_array('SessionName', $tag_names) || !in_array('DateStart', $tag_names) || !in_array('DateEnd', $tag_names)) {
  488. $error_message = get_lang('NoNeededData');
  489. break;
  490. }
  491. }
  492. }
  493. // looping the sessions
  494. foreach ($sessions as $enreg) {
  495. $user_counter = 0;
  496. $course_counter = 0;
  497. $session_name = $enreg['SessionName'];
  498. $date_start = $enreg['DateStart'];
  499. $date_end = $enreg['DateEnd'];
  500. $visibility = $enreg['Visibility'];
  501. $session_category_id = $enreg['SessionCategory'];
  502. // searching a coach
  503. if (!empty($enreg['Coach'])) {
  504. $coach_id = UserManager::get_user_id_from_username($enreg['Coach']);
  505. if ($coach_id === false) {
  506. // If the coach-user does not exist - I'm the coach.
  507. $coach_id = api_get_user_id();
  508. }
  509. } else {
  510. $coach_id = api_get_user_id();
  511. }
  512. if (!$updatesession) {
  513. // Always create a session.
  514. $unique_name = false; // This MUST be initializead.
  515. $i = 0;
  516. // Change session name, verify that session doesn't exist
  517. while (!$unique_name) {
  518. if ($i > 1) {
  519. $suffix = ' - '.$i;
  520. }
  521. $sql = 'SELECT 1 FROM '.$tbl_session.' WHERE name="'.Database::escape_string($session_name.$suffix).'"';
  522. $rs = Database::query($sql);
  523. if (Database::result($rs, 0, 0)) {
  524. $i++;
  525. } else {
  526. $unique_name = true;
  527. $session_name .= $suffix;
  528. }
  529. }
  530. // Creating the session.
  531. $sql_session = "INSERT IGNORE INTO $tbl_session SET
  532. name = '".Database::escape_string($session_name)."',
  533. id_coach = '$coach_id',
  534. date_start = '$date_start',
  535. date_end = '$date_end',
  536. visibility = '$visibility',
  537. session_category_id = '$session_category_id',
  538. session_admin_id=".intval($_user['user_id']);
  539. $rs_session = Database::query($sql_session);
  540. $session_id = Database::insert_id();
  541. $session_counter++;
  542. } else {
  543. $my_session_result = SessionManager::get_session_by_name($session_name);
  544. if ($my_session_result === false) {
  545. // Creating a session.
  546. $sql_session = "INSERT IGNORE INTO $tbl_session SET
  547. name = '$session_name',
  548. id_coach = '$coach_id',
  549. date_start = '$date_start',
  550. date_end = '$date_end',
  551. visibility = '$visibility',
  552. session_category_id = '$session_category_id'";
  553. $rs_session = Database::query($sql_session);
  554. // We get the last insert id.
  555. $my_session_result = SessionManager::get_session_by_name($session_name);
  556. $session_id = $my_session_result['id'];
  557. //echo '<br>';
  558. } else {
  559. // The session already exists, update it then.
  560. $sql_session = "UPDATE $tbl_session SET
  561. id_coach = '$coach_id',
  562. date_start = '$date_start',
  563. date_end = '$date_end',
  564. visibility = '$visibility',
  565. session_category_id = '$session_category_id'
  566. WHERE name = '$session_name'";
  567. $rs_session = Database::query($sql_session);
  568. $session_id = Database::query("SELECT id FROM $tbl_session WHERE name='$session_name'");
  569. list($session_id) = Database::fetch_array($session_id);
  570. Database::query("DELETE FROM $tbl_session_user WHERE id_session='$session_id'");
  571. Database::query("DELETE FROM $tbl_session_course WHERE id_session='$session_id'");
  572. Database::query("DELETE FROM $tbl_session_course_user WHERE id_session='$session_id'");
  573. }
  574. $session_counter++;
  575. }
  576. $users = explode('|', $enreg['Users']);
  577. // Adding the relationship "Session - User".
  578. if (is_array($users)) {
  579. foreach ($users as $user) {
  580. $user_id = UserManager::get_user_id_from_username($user);
  581. if ($user_id !== false) {
  582. // Insert new users.
  583. $sql = "INSERT IGNORE INTO $tbl_session_user SET
  584. id_user='$user_id',
  585. id_session = '$session_id'";
  586. $rs_user = Database::query($sql);
  587. $user_counter++;
  588. }
  589. }
  590. }
  591. $courses = explode('|', $enreg['Courses']);
  592. foreach ($courses as $course) {
  593. $course_code = api_strtoupper(api_substr($course, 0, api_strpos($course, '[')));
  594. if (CourseManager::course_exists($course_code)) {
  595. // If the course exists we continue
  596. $course_info = CourseManager::get_course_information($course_code);
  597. $coach = api_strstr($course, '[');
  598. $coach = api_substr($coach, 1, api_strpos($coach,']') - 1);
  599. if (!empty($coach)) {
  600. $coach_id = UserManager::get_user_id_from_username($coach);
  601. if ($coach_id === false) {
  602. $coach_id = '';
  603. }
  604. } else {
  605. $coach = '';
  606. }
  607. // Adding the course to a session
  608. $sql_course = "INSERT IGNORE INTO $tbl_session_course SET
  609. course_code = '$course_code',
  610. id_session='$session_id'";
  611. $rs_course = Database::query($sql_course);
  612. $course_counter++;
  613. $course_split = array();
  614. $tok = strtok($course, "[]");
  615. while ($tok !== false) {
  616. $course_split[] = $tok;
  617. $tok = strtok("[]");
  618. }
  619. $course_coachs = explode(",",$course_split[1]);
  620. $course_users = explode(",",$course_split[2]);
  621. // adding coachs to session course user
  622. foreach ($course_coachs as $course_coach) {
  623. $coach_id = UserManager::get_user_id_from_username($course_coach);
  624. if ($coach_id !== false) {
  625. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  626. id_user='$coach_id',
  627. course_code='$course_code',
  628. id_session = '$session_id',
  629. status = 2 ";
  630. $rs_coachs = Database::query($sql);
  631. } else {
  632. $error_message .= get_lang('UserDoesNotExist').' : '.$user.'<br />';
  633. }
  634. }
  635. $users_in_course_counter = 0;
  636. // Adding the relationship "Session - Course - User".
  637. foreach ($course_users as $user) {
  638. $user_id = UserManager::get_user_id_from_username($user);
  639. if ($user_id !== false) {
  640. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  641. id_user='$user_id',
  642. course_code='$course_code',
  643. id_session = '$session_id'";
  644. $rs_users = Database::query($sql);
  645. $users_in_course_counter++;
  646. } else {
  647. $error_message .= get_lang('UserDoesNotExist').' : '.$user.'<br />';
  648. }
  649. }
  650. $sql = "UPDATE $tbl_session_course SET nbr_users='$users_in_course_counter' WHERE course_code='$course_code'";
  651. Database::query($sql);
  652. $course_info = CourseManager::get_course_information($course_code);
  653. $inserted_in_course[$course_code] = $course_info['title'];
  654. } else {
  655. // TODO: We should create the course as in the XML import.
  656. }
  657. if (CourseManager::course_exists($course_code, true)) {
  658. $list = CourseManager :: get_courses_info_from_visual_code($course_code);
  659. foreach ($list as $vcourse) {
  660. if ($vcourse['code'] == $course_code) {
  661. // Ignore, this has already been inserted.
  662. } else {
  663. $coach = api_strstr($course, '[');
  664. $coach = api_substr($coach, 1, api_strpos($coach,']') - 1);
  665. // Adding the relationship "Session - Course".
  666. $sql_course = "INSERT IGNORE INTO $tbl_session_course SET
  667. course_code = '".$vcourse['code']."',
  668. id_session='$session_id'";
  669. $rs_course = Database::query($sql_course);
  670. // adding coachs to session course user
  671. foreach ($course_coachs as $course_coach) {
  672. $coach_id = UserManager::get_user_id_from_username($course_coach);
  673. if ($coach_id !== false) {
  674. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  675. id_user='$coach_id',
  676. course_code='{$vcourse['code']}',
  677. id_session = '$session_id',
  678. status = 2 ";
  679. $rs_coachs = Database::query($sql);
  680. } else {
  681. $error_message .= get_lang('UserDoesNotExist').' : '.$user.'<br />';
  682. }
  683. }
  684. $users_in_course_counter = 0;
  685. // Adding the relationship "Session - Course - User".
  686. foreach ($course_users as $user) {
  687. $user_id = UserManager::get_user_id_from_username($user);
  688. if ($user_id !== false) {
  689. $sql = "INSERT IGNORE INTO $tbl_session_course_user SET
  690. id_user='$user_id',
  691. course_code='{$vcourse['code']}',
  692. id_session = '$session_id'";
  693. $rs_users = Database::query($sql);
  694. $users_in_course_counter++;
  695. } else {
  696. $error_message .= get_lang('UserDoesNotExist').' : '.$user.'<br />';
  697. }
  698. }
  699. Database::query("UPDATE $tbl_session_course SET nbr_users='$users_in_course_counter' WHERE course_code='".$vcourse['code']."'");
  700. }
  701. }
  702. $inserted_in_course[$vcourse['code']] = $vcourse['title'];
  703. }
  704. }
  705. $sql_update_users = "UPDATE $tbl_session SET nbr_users='$user_counter', nbr_courses='$course_counter' WHERE id='$session_id'";
  706. Database::query($sql_update_users);
  707. }
  708. }
  709. }
  710. if (!empty($error_message)) {
  711. $error_message = get_lang('ButProblemsOccured').' :<br />'.$error_message;
  712. }
  713. if (count($inserted_in_course) > 1) {
  714. $warn = get_lang('SeveralCoursesSubscribedToSessionBecauseOfSameVisualCode').': ';
  715. foreach ($inserted_in_course as $code => $title) {
  716. $warn .= ' '.$title.' ('.$code.'),';
  717. }
  718. $warn = substr($warn, 0, -1);
  719. }
  720. if ($session_counter == 1) {
  721. header('Location: resume_session.php?id_session='.$session_id.'&warn='.urlencode($warn));
  722. exit;
  723. } else {
  724. header('Location: session_list.php?action=show_message&message='.urlencode(get_lang('FileImported').' '.$error_message).'&warn='.urlencode($warn));
  725. exit;
  726. }
  727. } else {
  728. $error_message = get_lang('NoInputFile');
  729. }
  730. }
  731. // display the header
  732. Display::display_header($tool_name);
  733. // display the tool title
  734. // api_display_tool_title($tool_name);
  735. if (count($inserted_in_course) > 1) {
  736. $msg = get_lang('SeveralCoursesSubscribedToSessionBecauseOfSameVisualCode').': ';
  737. foreach ($inserted_in_course as $code => $title) {
  738. $msg .= ' '.$title.' ('.$title.'),';
  739. }
  740. $msg = substr($msg, 0, -1);
  741. Display::display_warning_message($msg);
  742. }
  743. /*
  744. update session by default is true
  745. <tr>
  746. <td nowrap="nowrap" valign="top"><?php echo get_lang('UpdateSession'); ?> :</td>
  747. <td>
  748. <input class="checkbox" type="checkbox" name="updatesession" id="updatesession" value="true" />
  749. </td>
  750. </tr>
  751. */
  752. ?>
  753. <form method="post" action="<?php echo api_get_self(); ?>" enctype="multipart/form-data" style="margin: 0px;">
  754. <input type="hidden" name="formSent" value="1">
  755. <div class="row"><div class="form_header"><?php echo $tool_name; ?></div></div>
  756. <table border="0" cellpadding="5" cellspacing="0">
  757. <?php
  758. if (!empty($error_message)) {
  759. ?>
  760. <tr>
  761. <td colspan="2">
  762. <?php
  763. Display::display_normal_message($error_message, false);
  764. ?>
  765. </td>
  766. </tr>
  767. <?php
  768. }
  769. ?>
  770. <tr>
  771. <td nowrap="nowrap"><?php echo get_lang('ImportFileLocation'); ?> :</td>
  772. <td><input type="file" name="import_file" size="30"></td>
  773. </tr>
  774. <tr>
  775. <td nowrap="nowrap" valign="top"><?php echo get_lang('FileType'); ?> :</td>
  776. <td>
  777. <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>
  778. <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>
  779. </td>
  780. </tr>
  781. <tr>
  782. <td nowrap="nowrap" valign="top"><?php echo get_lang('SendMailToUsers'); ?> :</td>
  783. <td>
  784. <input class="checkbox" type="checkbox" name="sendMail" id="sendMail" value="true" />
  785. </td>
  786. </tr>
  787. <tr>
  788. <td>&nbsp;</td>
  789. <td>
  790. <button class="save" type="submit" name="name" value="<?php echo get_lang('ImportSession'); ?>"><?php echo get_lang('ImportSession'); ?></button>
  791. </td>
  792. </tr>
  793. </table>
  794. </form>
  795. <font color="gray">
  796. <p><?php echo get_lang('CSVMustLookLike').' ('.get_lang('MandatoryFields').')'; ?> :</p>
  797. <blockquote>
  798. <pre>
  799. <b>SessionName</b>;Coach;<b>DateStart</b>;<b>DateEnd</b>;Users;Courses
  800. <b>xxx1</b>;xxx;<b>xxx;xxx</b>;username1|username2;course1[coach1][username1,username2,...]|course2[coach1][username1,username2,...]
  801. <b>xxx2</b>;xxx;<b>xxx;xxx</b>;username1|username2;course1[coach1][username1,username2,...]|course2[coach1][username1,username2,...]
  802. </pre>
  803. </blockquote>
  804. <p><?php echo get_lang('XMLMustLookLike').' ('.get_lang('MandatoryFields').')'; ?> :</p>
  805. <blockquote>
  806. <pre>
  807. &lt;?xml version=&quot;1.0&quot; encoding=&quot;<?php echo api_refine_encoding_id(api_get_system_encoding()); ?>&quot;?&gt;
  808. &lt;Sessions&gt;
  809. &lt;Users&gt;
  810. &lt;User&gt;
  811. &lt;Username&gt;<b>username1</b>&lt;/Username&gt;
  812. &lt;Lastname&gt;xxx&lt;/Lastname&gt;
  813. &lt;Firstname&gt;xxx&lt;/Firstname&gt;
  814. &lt;Password&gt;xxx&lt;/Password&gt;
  815. &lt;Email&gt;xxx@xx.xx&lt;/Email&gt;
  816. &lt;OfficialCode&gt;xxx&lt;/OfficialCode&gt;
  817. &lt;Phone&gt;xxx&lt;/Phone&gt;
  818. &lt;Status&gt;student|teacher&lt;/Status&gt;
  819. &lt;/User&gt;
  820. &lt;/Users&gt;
  821. &lt;Courses&gt;
  822. &lt;Course&gt;
  823. &lt;CourseCode&gt;<b>xxx</b>&lt;/CourseCode&gt;
  824. &lt;CourseTeacher&gt;xxx&lt;/CourseTeacher&gt;
  825. &lt;CourseLanguage&gt;xxx&lt;/CourseLanguage&gt;
  826. &lt;CourseTitle&gt;xxx&lt;/CourseTitle&gt;
  827. &lt;CourseDescription&gt;xxx&lt;/CourseDescription&gt;
  828. &lt;/Course&gt;
  829. &lt;/Courses&gt;
  830. &lt;Session&gt;
  831. <b>&lt;SessionName&gt;xxx&lt;/SessionName&gt;</b>
  832. &lt;Coach&gt;xxx&lt;/Coach&gt;
  833. <b>&lt;DateStart&gt;xxx&lt;/DateStart&gt;</b>
  834. <b>&lt;DateEnd&gt;xxx&lt;/DateEnd&gt;</b>
  835. &lt;User&gt;xxx&lt;/User&gt;
  836. &lt;User&gt;xxx&lt;/User&gt;
  837. &lt;Course&gt;
  838. &lt;CourseCode&gt;coursecode1&lt;/CourseCode&gt;
  839. &lt;Coach&gt;coach1&lt;/Coach&gt;
  840. &lt;User&gt;username1&lt;/User&gt;
  841. &lt;User&gt;username2&lt;/User&gt;
  842. &lt;/Course&gt;
  843. &lt;/Session&gt;
  844. &lt;Session&gt;
  845. <b>&lt;SessionName&gt;xxx&lt;/SessionName&gt;</b>
  846. &lt;Coach&gt;xxx&lt;/Coach&gt;
  847. <b>&lt;DateStart&gt;xxx&lt;/DateStart&gt;</b>
  848. <b>&lt;DateEnd&gt;xxx&lt;/DateEnd&gt;</b>
  849. &lt;User&gt;xxx&lt;/User&gt;
  850. &lt;User&gt;xxx&lt;/User&gt;
  851. &lt;Course&gt;
  852. &lt;CourseCode&gt;coursecode1&lt;/CourseCode&gt;
  853. &lt;Coach&gt;coach1&lt;/Coach&gt;
  854. &lt;User&gt;username1&lt;/User&gt;
  855. &lt;User&gt;username2&lt;/User&gt;
  856. &lt;/Course&gt;
  857. &lt;/Session&gt;
  858. &lt;/Sessions&gt;
  859. </pre>
  860. </blockquote>
  861. </font>
  862. <?php
  863. /*
  864. ==============================================================================
  865. FOOTER
  866. ==============================================================================
  867. */
  868. Display::display_footer();