session_import.php 40 KB

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