session_import.php 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445
  1. <?php // $Id: session_import.php,v 1.1 2006/04/20 09:58:01 elixir_inter Exp $
  2. /*
  3. ==============================================================================
  4. Dokeos - elearning and course management software
  5. Copyright (c) 2004 Dokeos S.A.
  6. Copyright (c) 2003 University of Ghent (UGent)
  7. Copyright (c) 2001 Universite catholique de Louvain (UCL)
  8. Copyright (c) Olivier Brouckaert
  9. For a full list of contributors, see "credits.txt".
  10. The full license can be read in "license.txt".
  11. This program is free software; you can redistribute it and/or
  12. modify it under the terms of the GNU General Public License
  13. as published by the Free Software Foundation; either version 2
  14. of the License, or (at your option) any later version.
  15. See the GNU General Public License for more details.
  16. Contact: Dokeos, 181 rue Royale, B-1000 Brussels, Belgium, info@dokeos.com
  17. ==============================================================================
  18. */
  19. /**
  20. ==============================================================================
  21. * @package dokeos.admin
  22. ==============================================================================
  23. */
  24. // name of the language file that needs to be included
  25. $language_file = array('admin','registration');
  26. $cidReset=true;
  27. include('../inc/global.inc.php');
  28. api_protect_admin_script();
  29. include(api_get_library_path().'/fileManage.lib.php');
  30. include(api_get_library_path().'/xmllib.php');
  31. $formSent=0;
  32. $errorMsg='';
  33. // Database table definitions
  34. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  35. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  36. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  37. $tbl_session_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  38. $tbl_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  39. $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  40. $tool_name=get_lang('ImportSessionListXMLCSV');
  41. $interbreadcrumb[]=array("url" => "index.php","name" => get_lang('AdministrationTools'));
  42. set_time_limit(0);
  43. if($_POST['formSent'])
  44. {
  45. $formSent=$_POST['formSent'];
  46. $file_type=$_POST['file_type'];
  47. $sendMail=$_POST['sendMail']?1:0;
  48. /*if($_FILES['import_file']['size'])
  49. {
  50. $content=file($_FILES['import_file']['tmp_name']);
  51. }
  52. else
  53. {
  54. $content=array('');
  55. }*/
  56. $sessions=array();
  57. ///////////////////////
  58. //XML/////////////////
  59. /////////////////////
  60. if($file_type == 'xml')
  61. {
  62. $xmlparser = new XmlLib_xmlParser($_FILES['import_file']['tmp_name']);
  63. $racine = $xmlparser->getDocument();
  64. $sessionNodes = $racine->children('Session');
  65. foreach ($sessionNodes as $sessionNode){ // foreach session
  66. $countCourses = 0;
  67. $countUsers = 0;
  68. list($SessionName) = $sessionNode->children('SessionName');
  69. $SessionName = $SessionName->nodeValue();
  70. list($Coach) = $sessionNode->children('Coach');
  71. if(!empty($Coach)){
  72. $Coach = $Coach->nodeValue();
  73. $sqlCoach = "SELECT user_id FROM $tbl_user WHERE username='$Coach'";
  74. $rsCoach = api_sql_query($sqlCoach);
  75. list($Coach) = (mysql_fetch_array($rsCoach));
  76. }
  77. else {
  78. $Coach = '';
  79. }
  80. list($DateStart) = $sessionNode->children('DateStart');
  81. $DateStart = $DateStart->nodeValue();
  82. list($DateEnd) = $sessionNode->children('DateEnd');
  83. $DateEnd = $DateEnd->nodeValue();
  84. $sqlSession = "INSERT IGNORE INTO $tbl_session SET
  85. name = '$SessionName',
  86. id_coach = '$Coach',
  87. date_start = '$DateStart',
  88. date_end = '$DateEnd'";
  89. $rsSession = api_sql_query($sqlSession, __FILE__, __LINE__);
  90. $update = false;
  91. if(!mysql_affected_rows($rsSession)){
  92. $update = true;
  93. $sqlSession = "UPDATE $tbl_session SET
  94. id_coach = '$Coach',
  95. date_start = '$DateStart',
  96. date_end = '$DateEnd'
  97. WHERE name = '$SessionName'";
  98. $rsSession = api_sql_query($sqlSession, __FILE__, __LINE__);
  99. $session_id = api_sql_query("SELECT id FROM $tbl_session WHERE name='$SessionName'",__FILE__,__LINE__);
  100. list($session_id) = mysql_fetch_array($session_id);
  101. api_sql_query("DELETE FROM $tbl_session_user WHERE id_session='$session_id'",__FILE__,__LINE__);
  102. api_sql_query("DELETE FROM $tbl_session_course WHERE id_session='$session_id'",__FILE__,__LINE__);
  103. api_sql_query("DELETE FROM $tbl_session_course_user WHERE id_session='$session_id'",__FILE__,__LINE__);
  104. }
  105. else {
  106. $session_id = mysql_insert_id($rsSession);
  107. }
  108. $userNodes = $sessionNode->children('User');
  109. foreach ($userNodes as $userNode){
  110. $sqlUser = "SELECT user_id FROM $tbl_user WHERE username='".$userNode->nodeValue()."'";
  111. $rsUser = api_sql_query($sqlUser);
  112. list($user_id) = (mysql_fetch_array($rsUser));
  113. if(!empty($user_id)){
  114. $sql = "INSERT INTO $tbl_session_user SET
  115. id_user='$user_id',
  116. id_session = '$session_id'";
  117. $rsUser = api_sql_query($sql,__FILE__,__LINE__);
  118. if(mysql_affected_rows()){
  119. $countUsers++;
  120. }
  121. }
  122. }
  123. $courseNodes = $sessionNode->children('Course');
  124. foreach($courseNodes as $courseNode){
  125. list($CourseCode) = $courseNode->children('CourseCode');
  126. $CourseCode = $CourseCode->nodeValue();
  127. list($Coach) = $courseNode->children('Coach');
  128. if(!empty($Coach)){
  129. $Coach = $Coach->nodeValue();
  130. $sqlCoach = "SELECT user_id FROM $tbl_user WHERE username='$Coach'";
  131. $rsCoach = api_sql_query($sqlCoach,__FILE__,__LINE__);
  132. list($Coach) = (mysql_fetch_array($rsCoach));
  133. }
  134. else {
  135. $Coach = '';
  136. }
  137. $sqlCourse = "INSERT INTO $tbl_session_course SET
  138. course_code = '$CourseCode',
  139. id_coach='$Coach',
  140. id_session='$session_id'";
  141. $rsCourse = api_sql_query($sqlCourse,__FILE__,__LINE__);
  142. if(mysql_affected_rows()){
  143. $countCourses++;
  144. $userNodes = $courseNode->children('User');
  145. $countUsersCourses = 0;
  146. foreach ($userNodes as $userNode){
  147. $sqlUser = "SELECT user_id FROM $tbl_user WHERE username='".$userNode->nodeValue()."'";
  148. $rsUser = api_sql_query($sqlUser);
  149. list($user_id) = (mysql_fetch_array($rsUser));
  150. $sql = "INSERT INTO $tbl_session_course_user SET
  151. id_user='$user_id',
  152. course_code='$CourseCode',
  153. id_session = '$session_id'";
  154. $rsUsers = api_sql_query($sql,__FILE__,__LINE__);
  155. if(mysql_affected_rows())
  156. $countUsersCourses++;
  157. }
  158. api_sql_query("UPDATE $tbl_session_course SET nbr_users='$countUsersCourses' WHERE course_code='$CourseCode'",__FILE__,__LINE__);
  159. }
  160. }
  161. api_sql_query("UPDATE $tbl_session SET nbr_users='$countUsers', nbr_courses='$countCourses' WHERE id='$session_id'",__FILE__,__LINE__);
  162. }
  163. }
  164. /////////////////////
  165. // CSV /////////////
  166. ///////////////////
  167. else
  168. {
  169. $content=file($_FILES['import_file']['tmp_name']);
  170. if(!strstr($content[0],';'))
  171. {
  172. $errorMsg=get_lang('NotCSV');
  173. }
  174. else
  175. {
  176. $tag_names=array();
  177. foreach($content as $key=>$enreg)
  178. {
  179. $enreg=explode(';',trim($enreg));
  180. if($key)
  181. {
  182. foreach($tag_names as $tag_key=>$tag_name)
  183. {
  184. $sessions[$key-1][$tag_name]=$enreg[$tag_key];
  185. }
  186. }
  187. else
  188. {
  189. foreach($enreg as $tag_name)
  190. {
  191. $tag_names[]=eregi_replace('[^a-z0-9_-]','',$tag_name);
  192. }
  193. if(!in_array('SessionName',$tag_names) || !in_array('DateStart',$tag_names) || !in_array('DateEnd',$tag_names))
  194. {
  195. $errorMsg=get_lang('NoNeededData');
  196. break;
  197. }
  198. }
  199. }
  200. foreach($sessions as $enreg) {
  201. $SessionName = $enreg['SessionName'];
  202. $DateStart = $enreg['DateStart'];
  203. $DateEnd = $enreg['DateEnd'];
  204. if(!empty($enreg['Coach'])){
  205. $sqlCoach = "SELECT user_id FROM $tbl_user WHERE username='".$enreg['Coach']."'";
  206. $rsCoach = api_sql_query($sqlCoach);
  207. list($Coach) = (mysql_fetch_array($rsCoach));
  208. }
  209. else {
  210. $Coach = '';
  211. }
  212. $sqlSession = "INSERT IGNORE INTO $tbl_session SET
  213. name = '$SessionName',
  214. id_coach = '$Coach',
  215. date_start = '$DateStart',
  216. date_end = '$DateEnd'";
  217. $rsSession = api_sql_query($sqlSession, __FILE__, __LINE__);
  218. $update = false;
  219. if(!mysql_affected_rows($rsSession)){
  220. $update = true;
  221. $sqlSession = "UPDATE $tbl_session SET
  222. id_coach = '$Coach',
  223. date_start = '$DateStart',
  224. date_end = '$DateEnd'
  225. WHERE name = '$SessionName'";
  226. $rsSession = api_sql_query($sqlSession, __FILE__, __LINE__);
  227. $session_id = api_sql_query("SELECT id FROM $tbl_session WHERE name='$SessionName'",__FILE__,__LINE__);
  228. list($session_id) = mysql_fetch_array($session_id);
  229. api_sql_query("DELETE FROM $tbl_session_user WHERE id_session='$session_id'",__FILE__,__LINE__);
  230. api_sql_query("DELETE FROM $tbl_session_course WHERE id_session='$session_id'",__FILE__,__LINE__);
  231. api_sql_query("DELETE FROM $tbl_session_course_user WHERE id_session='$session_id'",__FILE__,__LINE__);
  232. }
  233. else {
  234. $session_id = mysql_insert_id($rsSession);
  235. }
  236. $users = explode('|',$enreg['Users']);
  237. foreach ($users as $user){
  238. $sqlUser = "SELECT user_id FROM $tbl_user WHERE username='".$user."'";
  239. $rsUser = api_sql_query($sqlUser);
  240. list($user_id) = (mysql_fetch_array($rsUser));
  241. $sql = "INSERT INTO $tbl_session_user SET
  242. id_user='$user_id',
  243. id_session = '$session_id'";
  244. $rsUser = api_sql_query($sql,__FILE__,__LINE__);
  245. if(mysql_affected_rows()){
  246. $countUsers++;
  247. }
  248. }
  249. $courses = explode('|',$enreg['Courses']);
  250. foreach($courses as $course){
  251. $CourseCode = substr($course,0,strpos($course,'['));
  252. $Coach = strstr($course,'[');
  253. $Coach = substr($Coach,1,strpos($Coach,']')-1);
  254. if(!empty($Coach)){
  255. $sqlCoach = "SELECT user_id FROM $tbl_user WHERE username='$Coach'";
  256. $rsCoach = api_sql_query($sqlCoach,__FILE__,__LINE__);
  257. list($Coach) = (mysql_fetch_array($rsCoach));
  258. }
  259. else {
  260. $Coach = '';
  261. }
  262. $sqlCourse = "INSERT INTO $tbl_session_course SET
  263. course_code = '$CourseCode',
  264. id_coach='$Coach',
  265. id_session='$session_id'";
  266. $rsCourse = api_sql_query($sqlCourse,__FILE__,__LINE__);
  267. if(mysql_affected_rows()){
  268. $countCourses++;
  269. $users = substr($course , strpos($course,'[',1)+1 , strpos($course,']',1));
  270. $users = explode('|',$enreg['Users']);
  271. $countUsersCourses = 0;
  272. foreach ($users as $user){
  273. $sqlUser = "SELECT user_id FROM $tbl_user WHERE username='".$user."'";
  274. $rsUser = api_sql_query($sqlUser);
  275. list($user_id) = (mysql_fetch_array($rsUser));
  276. $sql = "INSERT INTO $tbl_session_course_user SET
  277. id_user='$user_id',
  278. course_code='$CourseCode',
  279. id_session = '$session_id'";
  280. $rsUsers = api_sql_query($sql,__FILE__,__LINE__);
  281. if(mysql_affected_rows())
  282. $countUsersCourses++;
  283. }
  284. api_sql_query("UPDATE $tbl_session_course SET nbr_users='$countUsersCourses' WHERE course_code='$CourseCode'",__FILE__,__LINE__);
  285. }
  286. }
  287. api_sql_query("UPDATE $tbl_session SET nbr_users='$countUsers', nbr_courses='$countCourses' WHERE id='$session_id'",__FILE__,__LINE__);
  288. }
  289. }
  290. }
  291. header('Location: session_list.php?action=show_message&message='.urlencode(get_lang('FileImported')));
  292. }
  293. Display::display_header($tool_name);
  294. api_display_tool_title($tool_name);
  295. ?>
  296. <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>" enctype="multipart/form-data" style="margin:0px;">
  297. <input type="hidden" name="formSent" value="1">
  298. <table border="0" cellpadding="5" cellspacing="0">
  299. <?php
  300. if(!empty($errorMsg))
  301. {
  302. ?>
  303. <tr>
  304. <td colspan="2">
  305. <?php
  306. Display::display_normal_message($errorMsg); //main API
  307. ?>
  308. </td>
  309. </tr>
  310. <?php
  311. }
  312. ?>
  313. <tr>
  314. <td nowrap="nowrap"><?php echo get_lang('ImportFileLocation'); ?> :</td>
  315. <td><input type="file" name="import_file" size="30"></td>
  316. </tr>
  317. <tr>
  318. <td nowrap="nowrap" valign="top"><?php echo get_lang('FileType'); ?> :</td>
  319. <td>
  320. <input class="checkbox" type="radio" name="file_type" id="file_type_xml" value="xml" <?php if($formSent && $file_type == 'xml') echo 'checked="checked"'; ?>> <label for="file_type_xml">XML</label> (<a href="exemple.xml" target="_blank"><?php echo get_lang('ExampleXMLFile'); ?></a>)<br>
  321. <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="exemple.csv" target="_blank"><?php echo get_lang('ExampleCSVFile'); ?></a>)<br>
  322. </td>
  323. </tr>
  324. <tr>
  325. <td>&nbsp;</td>
  326. <td><input type="submit" value="<?php echo get_lang('Ok'); ?>"></td>
  327. </tr>
  328. </table>
  329. </form>
  330. <font color="gray">
  331. <p><?php echo get_lang('CSVMustLookLike').' ('.get_lang('MandatoryFields').')'; ?> :</p>
  332. <blockquote>
  333. <pre>
  334. <b>SessionName</b>;Coach;<b>DateStart</b>;<b>DateEnd</b>;Users;Courses
  335. <b>xxx</b>;xxx;<b>xxx;xxx</b>;username1|username2;course1[coach1][username1,username2,...]|course2[coach1][username1,username2,...]
  336. </pre>
  337. </blockquote>
  338. <p><?php echo get_lang('XMLMustLookLike').' ('.get_lang('MandatoryFields').')'; ?> :</p>
  339. <blockquote>
  340. <pre>
  341. &lt;?xml version=&quot;1.0&quot; encoding=&quot;ISO-8859-1&quot;?&gt;
  342. &lt;Sessions&gt;
  343. &lt;Session&gt;
  344. <b>&lt;SessionName&gt;xxx&lt;/SessionName&gt;</b>
  345. &lt;Coach&gt;xxx&lt;/Coach&gt;
  346. <b>&lt;DateStart&gt;xxx&lt;/DateStart&gt;</b>
  347. <b>&lt;DateEnd&gt;xxx&lt;/DateEnd&gt;</b>
  348. &lt;User&gt;xxx&lt;/User&gt;
  349. &lt;User&gt;xxx&lt;/User&gt;
  350. &lt;Course&gt;
  351. &lt;CourseCode&gt;coursecode1&lt;/CourseCode&gt;
  352. &lt;Coach&gt;coach1&lt;/Coach&gt;
  353. &lt;User&gt;username1&lt;/User&gt;
  354. &lt;User&gt;username2&lt;/User&gt;
  355. &lt;/Course&gt;
  356. &lt;/Session&gt;
  357. &lt;/Sessions&gt;
  358. </pre>
  359. </blockquote>
  360. </font>
  361. <?php
  362. /*
  363. ==============================================================================
  364. FOOTER
  365. ==============================================================================
  366. */
  367. Display::display_footer();
  368. ?>