session_export.php 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353
  1. <?php // $Id: session_export.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 Ghent University (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 = 'admin';
  26. $cidReset = true;
  27. include '../inc/global.inc.php';
  28. // setting the section (for the tabs)
  29. $this_section = SECTION_PLATFORM_ADMIN;
  30. api_protect_admin_script(true);
  31. include api_get_path(LIBRARY_PATH).'/fileManage.lib.php';
  32. $session_id = $_GET['session_id'];
  33. $formSent = 0;
  34. $errorMsg = '';
  35. // Database Table Definitions
  36. $tbl_user = Database::get_main_table(TABLE_MAIN_USER);
  37. $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
  38. $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  39. $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
  40. $tbl_session_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
  41. $tbl_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
  42. $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
  43. $archivePath = api_get_path(SYS_ARCHIVE_PATH);
  44. $archiveURL = api_get_path(WEB_CODE_PATH).'course_info/download.php?archive=';
  45. $tool_name = get_lang('ExportSessionListXMLCSV');
  46. $interbreadcrumb[] = array('url' => 'index.php',"name" => get_lang('PlatformAdmin'));
  47. set_time_limit(0);
  48. if ($_POST['formSent'] )
  49. {
  50. $formSent = $_POST['formSent'];
  51. $file_type = ($_POST['file_type'] == 'csv')?'csv':'xml';
  52. $session_id = $_POST['session_id'];
  53. if (empty($session_id))
  54. {
  55. $sql = "SELECT id,name,id_coach,username,date_start,date_end,visibility,session_category_id FROM $tbl_session INNER JOIN $tbl_user
  56. ON $tbl_user.user_id = $tbl_session.id_coach ORDER BY id";
  57. global $_configuration;
  58. if ($_configuration['multiple_access_urls']==true) {
  59. $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  60. $access_url_id = api_get_current_access_url_id();
  61. if ($access_url_id != -1){
  62. $sql = "SELECT id, name,id_coach,username,date_start,date_end,visibility,session_category_id FROM $tbl_session s INNER JOIN $tbl_session_rel_access_url as session_rel_url
  63. ON (s.id= session_rel_url.session_id) INNER JOIN $tbl_user u ON (u.user_id = s.id_coach)
  64. WHERE access_url_id = $access_url_id
  65. ORDER BY id";
  66. }
  67. }
  68. $result=Database::query($sql);
  69. }
  70. else
  71. {
  72. $sql = "SELECT id,name,username,date_start,date_end,visibility,session_category_id
  73. FROM $tbl_session
  74. INNER JOIN $tbl_user
  75. ON $tbl_user.user_id = $tbl_session.id_coach
  76. WHERE id='$session_id'";
  77. $result = Database::query($sql);
  78. }
  79. if(Database::num_rows($result))
  80. {
  81. if(!file_exists($archivePath))
  82. {
  83. mkdir($archivePath, api_get_permissions_for_new_directories(), true);
  84. }
  85. if(!file_exists($archivePath.'index.html'))
  86. {
  87. $fp=fopen($archivePath.'index.html','w');
  88. fputs($fp,'<html><head></head><body></body></html>');
  89. fclose($fp);
  90. }
  91. $archiveFile='export_sessions_'.$session_id.'_'.date('Y-m-d_H-i-s').'.'.$file_type;
  92. while( file_exists($archivePath.$archiveFile))
  93. {
  94. $archiveFile='export_users_'.$session_id.'_'.date('Y-m-d_H-i-s').'_'.uniqid('').'.'.$file_type;
  95. }
  96. $fp=fopen($archivePath.$archiveFile,'w');
  97. if($file_type == 'csv')
  98. {
  99. $cvs = true;
  100. fputs($fp,"SessionName;Coach;DateStart;DateEnd;Visibility;SessionCategory;Users;Courses;\n");
  101. }
  102. else
  103. {
  104. $cvs = false;
  105. fputs($fp, "<?xml version=\"1.0\" encoding=\"".api_get_system_encoding()."\"?>\n<Sessions>\n");
  106. }
  107. while($row=Database::fetch_array($result))
  108. {
  109. $add = '';
  110. $row['name'] = str_replace(';',',',$row['name']);
  111. $row['username'] = str_replace(';',',',$row['username']);
  112. $row['date_start'] = str_replace(';',',',$row['date_start']);
  113. $row['date_end'] = str_replace(';',',',$row['date_end']);
  114. $row['visibility'] = str_replace(';',',',$row['visibility']);
  115. $row['session_category'] = str_replace(';',',',$row['session_category_id']);
  116. if($cvs){
  117. $add.= $row['name'].';'.$row['username'].';'.$row['date_start'].';'.$row['date_end'].';'.$row['visibility'].';'.$row['session_category'].';';
  118. }
  119. else {
  120. $add = "\t<Session>\n"
  121. ."\t\t<SessionName>$row[name]</SessionName>\n"
  122. ."\t\t<Coach>$row[username]</Coach>\n"
  123. ."\t\t<DateStart>$row[date_start]</DateStart>\n"
  124. ."\t\t<DateEnd>$row[date_end]</DateEnd>\n"
  125. ."\t\t<Visibility>$row[visibility]</Visibility>\n"
  126. ."\t\t<SessionCategory>$row[session_category]</SessionCategory>\n";
  127. }
  128. //users
  129. $sql = "SELECT DISTINCT $tbl_user.username FROM $tbl_user
  130. INNER JOIN $tbl_session_user
  131. ON $tbl_user.user_id = $tbl_session_user.id_user AND $tbl_session_user.relation_type<>".SESSION_RELATION_TYPE_RRHH."
  132. AND $tbl_session_user.id_session = '".$row['id']."'";
  133. $rsUsers = Database::query($sql);
  134. $users = '';
  135. while($rowUsers = Database::fetch_array($rsUsers)){
  136. if($cvs){
  137. $users .= str_replace(';',',',$rowUsers['username']).'|';
  138. }
  139. else {
  140. $users .= "\t\t<User>$rowUsers[username]</User>\n";
  141. }
  142. }
  143. if(!empty($users) && $cvs)
  144. $users = api_substr($users , 0, api_strlen($users)-1);
  145. if($cvs)
  146. $users .= ';';
  147. $add .= $users;
  148. //courses
  149. $sql = "SELECT DISTINCT $tbl_course.code
  150. FROM $tbl_course
  151. INNER JOIN $tbl_session_course_user
  152. ON $tbl_course.code = $tbl_session_course_user.course_code
  153. AND $tbl_session_course_user.id_session = '".$row['id']."'";
  154. $rsCourses = Database::query($sql);
  155. $courses = '';
  156. while($rowCourses = Database::fetch_array($rsCourses)){
  157. // get coachs from a course
  158. $sql = "SELECT u.username
  159. FROM $tbl_session_course_user scu
  160. INNER JOIN $tbl_user u ON u.user_id = scu.id_user
  161. WHERE scu.course_code = '{$rowCourses['code']}'
  162. AND scu.id_session = '".$row['id']."' AND scu.status = 2 ";
  163. $rs_coachs = Database::query($sql);
  164. $coachs = array();
  165. while ($row_coachs = Database::fetch_array($rs_coachs)) {
  166. $coachs[] = $row_coachs['username'];
  167. }
  168. $coachs = implode(",",$coachs);
  169. if($cvs){
  170. $courses .= str_replace(';',',',$rowCourses['code']);
  171. $courses .= '['.str_replace(';',',',$coachs).'][';
  172. }
  173. else {
  174. $courses .= "\t\t<Course>\n";
  175. $courses .= "\t\t\t<CourseCode>$rowCourses[code]</CourseCode>\n";
  176. $courses .= "\t\t\t<Coach>$coachs</Coach>\n";
  177. }
  178. // rel user courses
  179. $sql = "SELECT DISTINCT u.username
  180. FROM $tbl_session_course_user scu
  181. INNER JOIN $tbl_session_user su ON scu.id_user = su.id_user AND scu.id_session = su.id_session AND su.relation_type<>".SESSION_RELATION_TYPE_RRHH."
  182. INNER JOIN $tbl_user u
  183. ON scu.id_user = u.user_id
  184. AND scu.course_code='".$rowCourses['code']."'
  185. AND scu.id_session='".$row['id']."'";
  186. $rsUsersCourse = Database::query($sql);
  187. $userscourse = '';
  188. while($rowUsersCourse = Database::fetch_array($rsUsersCourse)){
  189. if($cvs){
  190. $userscourse .= str_replace(';',',',$rowUsersCourse['username']).',';
  191. }
  192. else {
  193. $courses .= "\t\t\t<User>$rowUsersCourse[username]</User>\n";
  194. }
  195. }
  196. if($cvs){
  197. if(!empty($userscourse))
  198. $userscourse = api_substr($userscourse , 0, api_strlen($userscourse)-1);
  199. $courses .= $userscourse.']|';
  200. }
  201. else {
  202. $courses .= "\t\t</Course>\n";
  203. }
  204. }
  205. if(!empty($courses) && $cvs)
  206. $courses = api_substr($courses , 0, api_strlen($courses)-1);
  207. $add .= $courses;
  208. if($cvs) {
  209. $breakline = api_is_windows_os()?"\r\n":"\n";
  210. $add .= ";$breakline";
  211. } else {
  212. $add .= "\t</Session>\n";
  213. }
  214. fputs($fp, $add);
  215. }
  216. if(!$cvs)
  217. fputs($fp,"</Sessions>\n");
  218. fclose($fp);
  219. $errorMsg=get_lang('UserListHasBeenExported').'<br/><a href="'.$archiveURL.$archiveFile.'">'.get_lang('ClickHereToDownloadTheFile').'</a>';
  220. }
  221. }
  222. // display the header
  223. Display::display_header($tool_name);
  224. // display the tool title
  225. // api_display_tool_title($tool_name);
  226. //select of sessions
  227. $sql = "SELECT id, name FROM $tbl_session ORDER BY name";
  228. global $_configuration;
  229. if ($_configuration['multiple_access_urls']==true) {
  230. $tbl_session_rel_access_url= Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION);
  231. $access_url_id = api_get_current_access_url_id();
  232. if ($access_url_id != -1){
  233. $sql = "SELECT id, name FROM $tbl_session s INNER JOIN $tbl_session_rel_access_url as session_rel_url
  234. ON (s.id= session_rel_url.session_id)
  235. WHERE access_url_id = $access_url_id
  236. ORDER BY name";
  237. }
  238. }
  239. $result=Database::query($sql);
  240. $Sessions=Database::store_result($result);
  241. ?>
  242. <?php
  243. if(!empty($errorMsg))
  244. {
  245. Display::display_normal_message($errorMsg, false); //main API
  246. }
  247. ?>
  248. <form method="post" action="<?php echo api_get_self(); ?>" style="margin:0px;">
  249. <input type="hidden" name="formSent" value="1">
  250. <div class="row"><div class="form_header"><?php echo $tool_name; ?></div></div>
  251. <table border="0" cellpadding="5" cellspacing="0">
  252. <tr>
  253. <td nowrap="nowrap" valign="top"><?php echo get_lang('OutputFileType'); ?> :</td>
  254. <td>
  255. <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><br>
  256. <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><br>
  257. </td>
  258. </tr>
  259. <tr>
  260. <td><?php echo get_lang('WhichSessionToExport'); ?> :</td>
  261. <td><select name="session_id">
  262. <option value=""><?php echo get_lang('AllSessions') ?></option>
  263. <?php
  264. foreach($Sessions as $enreg)
  265. {
  266. ?>
  267. <option value="<?php echo $enreg['id']; ?>" <?php if($session_id == $enreg['id']) echo 'selected="selected"'; ?>><?php echo $enreg['name']; ?></option>
  268. <?php
  269. }
  270. unset($Courses);
  271. ?>
  272. </select></td>
  273. </tr>
  274. <tr>
  275. <td>&nbsp;</td>
  276. <td>
  277. <button class="save" type="submit" name="name" value="<?php echo get_lang('ExportSession') ?>"><?php echo get_lang('ExportSession') ?></button>
  278. </td>
  279. </tr>
  280. </table>
  281. </form>
  282. <?php
  283. /*
  284. ==============================================================================
  285. FOOTER
  286. ==============================================================================
  287. */
  288. Display::display_footer();
  289. ?>