session_import.php 14 KB

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