tutor_report.lib.php 9.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * Helper library for weekly reports.
  5. *
  6. * @package chamilo.plugin.ticket
  7. */
  8. /**
  9. * @param $course_code
  10. *
  11. * @return array|bool
  12. */
  13. function initializeReport($course_code)
  14. {
  15. $course_info = api_get_course_info($course_code);
  16. $table_reporte_semanas = Database::get_main_table('rp_reporte_semanas');
  17. $table_students_report = Database::get_main_table('rp_students_report');
  18. $table_semanas_curso = Database::get_main_table('rp_semanas_curso');
  19. $courseTable = Database::get_main_table(TABLE_MAIN_COURSE);
  20. $table_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  21. $table_post = Database::get_course_table(TABLE_FORUM_POST);
  22. $table_work = Database::get_course_table(TABLE_STUDENT_PUBLICATION);
  23. $course_code = Database::escape_string($course_code);
  24. $res = Database::query("SELECT COUNT(*) as cant FROM $table_reporte_semanas WHERE course_code = '".$course_code."'");
  25. $sqlWeeks = "SELECT semanas FROM $table_semanas_curso WHERE course_code = '$course_code'";
  26. $resWeeks = Database::query($sqlWeeks);
  27. $weeks = Database::fetch_object($resWeeks);
  28. $obj = Database::fetch_object($res);
  29. $weeksCount = (!isset($_POST['weeksNumber'])) ? (($weeks->semanas == 0) ? 7 : $weeks->semanas) : (int) $_POST['weeksNumber'];
  30. $weeksCount = Database::escape_string($weeksCount);
  31. Database::query("REPLACE INTO $table_semanas_curso (course_code , semanas) VALUES ('$course_code','$weeksCount')");
  32. if (intval($obj->cant) != $weeksCount) {
  33. if (intval($obj->cant) > $weeksCount) {
  34. $sql = "DELETE FROM $table_reporte_semanas
  35. WHERE week_id > $weeksCount AND course_code = '$course_code'";
  36. Database::query($sql);
  37. } else {
  38. for ($i = $obj->cant + 1; $i <= $weeksCount; $i++) {
  39. if (!Database::query("INSERT INTO $table_reporte_semanas (week_id, course_code, forum_id, work_id, quiz_id, pc_id)
  40. VALUES ($i, '$course_code', '0', '0', '0', '0' )")) {
  41. return false;
  42. }
  43. }
  44. }
  45. }
  46. $sql = "REPLACE INTO $table_students_report (user_id, week_report_id, work_ok , thread_ok , quiz_ok , pc_ok)
  47. SELECT cu.user_id, rs.id, 0, 0, 0, 0
  48. FROM $table_course_rel_user cu
  49. INNER JOIN $courseTable c
  50. ON (c.id = cu.c_id)
  51. LEFT JOIN $table_reporte_semanas rs ON c.code = rs.course_code
  52. WHERE cu.status = 5 AND rs.course_code = '$course_code'
  53. ORDER BY cu.user_id, rs.id";
  54. if (!Database::query($sql)) {
  55. return false;
  56. } else {
  57. $page = (!isset($_GET['page'])) ? 1 : (int) $_GET['page'];
  58. Database::query("UPDATE $table_students_report sr SET sr.work_ok = 1
  59. WHERE CONCAT (sr.user_id,',',sr.week_report_id)
  60. IN (SELECT DISTINCT CONCAT(w.user_id,',',rs.id)
  61. FROM $table_work w JOIN $table_reporte_semanas rs ON w.parent_id = rs.work_id)");
  62. Database::query("UPDATE $table_students_report sr SET sr.thread_ok = 1
  63. WHERE CONCAT (sr.user_id,',',sr.week_report_id)
  64. IN (SELECT DISTINCT CONCAT(f.poster_id,',',rs.id)
  65. FROM $table_post f JOIN $table_reporte_semanas rs ON f.thread_id = rs.forum_id)");
  66. return showResults($course_info, $weeksCount, $page);
  67. }
  68. }
  69. /**
  70. * @param $courseInfo
  71. * @param $weeksCount
  72. * @param $page
  73. *
  74. * @return array
  75. */
  76. function showResults($courseInfo, $weeksCount, $page)
  77. {
  78. $course_code = $courseInfo['code'];
  79. $page = intval($page);
  80. $weeksCount = intval($weeksCount);
  81. $tableWeeklyReport = Database::get_main_table('rp_reporte_semanas');
  82. $tableStudentsReport = Database::get_main_table('rp_students_report');
  83. //$table_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
  84. $tableUser = Database::get_main_table(TABLE_MAIN_USER);
  85. $tableThread = Database::get_course_table(TABLE_FORUM_THREAD);
  86. $tableWork = Database::get_course_table(TABLE_STUDENT_PUBLICATION);
  87. $results = [];
  88. $tableExport = [];
  89. $sqlHeader = "SELECT rs.id as id,rs.week_id, w.title AS work_title, t.thread_title ,'EVALUATION' as eval_title ,'QUIZ' as pc_title
  90. FROM $tableWeeklyReport rs
  91. LEFT JOIN $tableThread t ON t.thread_id = rs.forum_id
  92. LEFT JOIN $tableWork w ON w.id = rs.work_id
  93. WHERE rs.course_code = '$course_code'
  94. ORDER BY rs.week_id";
  95. $resultHeader = Database::query($sqlHeader);
  96. $ids = [];
  97. $line = '<tr>
  98. <th ></th>';
  99. $lineHeaderExport = [null, null];
  100. $lineHeaderExport2 = [null, ull];
  101. while ($rowe = Database::fetch_assoc($resultHeader)) {
  102. $lineHeaderExport[] = utf8_decode('Work'.$rowe['week_id']);
  103. $lineHeaderExport[] = utf8_decode('Forum'.$rowe['week_id']);
  104. //$fila_export_encabezado[] = utf8_decode('Eval'.$rowe['week_id']);
  105. //$fila_export_encabezado[] = utf8_decode('PC'.$rowe['week_id']);
  106. $lineHeaderExport2[] = utf8_decode($rowe['work_title']);
  107. $lineHeaderExport2[] = utf8_decode($rowe['thread_title']);
  108. //$fila_export_encabezado2[] = utf8_decode($rowe['eval_title']);
  109. //$fila_export_encabezado2[] = utf8_decode($rowe['pc_title']);
  110. $fila_export = ['Work'.$rowe['week_id'], 'Forum'.$rowe['week_id'], 'Eval'.$rowe['week_id'], 'PC'.$rowe['week_id']];
  111. if ($rowe['week_id'] > (($page - 1) * 7) && $rowe['week_id'] <= (7 * $page)) {
  112. $ids[$rowe['week_id']] = $rowe['id'];
  113. $line .= '<th>
  114. <a href="#" onClick="showContent('."'tarea".$rowe['week_id']."'".');">Work'.$rowe['week_id'].'
  115. <div class="blackboard_hide" id="tarea'.$rowe['week_id'].'">'.$rowe['work_title'].'</div>
  116. </a></th>';
  117. $line .= '<th>
  118. <a href="#" onClick="showContent('."'foro".$rowe['week_id']."'".');">Forum'.$rowe['week_id'].'
  119. <div class="blackboard_hide" id="foro'.$rowe['week_id'].'">'.$rowe['thread_title'].'</div>
  120. </a>
  121. </th>';
  122. }
  123. }
  124. $tableExport[] = $lineHeaderExport;
  125. $tableExport[] = $lineHeaderExport2;
  126. $line .= '</tr>';
  127. $html = '<form action="tutor.php" name="semanas" id="semanas" method="POST">
  128. <div class="row">
  129. '.get_lang('Select the timespan in weeks').'
  130. <select name="weeksNumber" id="weeksNumber" onChange="submit();">
  131. <option value="7" '.(($weeksCount == 7) ? 'selected="selected"' : "").'>7 weeks</option>
  132. <option value="14" '.(($weeksCount == 14) ? 'selected="selected"' : "").'>14 weeks</option>
  133. </select>';
  134. if ($weeksCount == 14) {
  135. $html .= '<span style="float:right;"><a href="tutor.php?page='.(($page == 1) ? 2 : 1).'">'.(($page == 1) ? "Siguiente" : "Anterior").'</a></span>';
  136. }
  137. $html .= '<span style="float:right;"><a href="'.api_get_self().'?action=export'.$get_parameter.$get_parameter2.'">'.Display::return_icon('export_excel.png', get_lang('Export'), '', '32').'</a></span>';
  138. $html .= '</form>';
  139. $html .= '<table class="reports">';
  140. $html .= '<tr>
  141. <th ></th>';
  142. for ($i = (7 * $page - 6); $i <= $page * 7; $i++) {
  143. $html .= '<th colspan="2">Week '.$i.'<a href="assign_tickets.php?id='.$ids[$i].'" class="ajax">'.Display::return_icon('edit.png', get_lang('Edit'), ['width' => '16', 'height' => '16'], 22).'</a></th>';
  144. }
  145. $html .= '</tr>';
  146. $html .= $line;
  147. $sql = "SELECT u.username , u.user_id , CONCAT(u.lastname,' ', u.firstname ) as fullname , rs.week_id , sr.work_ok ,sr.thread_ok , sr.quiz_ok , sr.pc_ok , rs.course_code
  148. FROM $tableStudentsReport sr
  149. JOIN $tableWeeklyReport rs ON sr.week_report_id = rs.id
  150. JOIN $tableUser u ON u.user_id = sr.user_id
  151. WHERE rs.course_code = '$course_code'
  152. ORDER BY u.lastname , u.username , rs.week_id
  153. ";
  154. $result = Database::query($sql);
  155. while ($row = Database::fetch_assoc($result)) {
  156. $resultadose[$row['username']][$row['week_id']] = $row;
  157. if ($row['week_id'] > (($page - 1) * 7) && $row['week_id'] <= (7 * $page)) {
  158. $results[$row['username']][$row['week_id']] = $row;
  159. if (count($results[$row['username']]) == 7) {
  160. $html .= showStudentResult($results[$row['username']], $page);
  161. }
  162. }
  163. if (count($resultadose[$row['username']]) == $weeksCount) {
  164. $tableExport[] = showStudentResultExport($resultadose[$row['username']], $weeksCount);
  165. }
  166. }
  167. $html .= '
  168. </table>';
  169. return ['show' => $html, 'export' => $tableExport];
  170. }
  171. /**
  172. * @param $datos
  173. * @param $pagina
  174. *
  175. * @return string
  176. */
  177. function showStudentResult($datos, $pagina)
  178. {
  179. $inicio = (7 * $pagina - 6);
  180. $fila = '<tr>';
  181. $fila .= '<td><a href="'.api_get_path(WEB_CODE_PATH).'user/userInfo.php?'.api_get_cidreq().'&uInfo='.$datos[$inicio]['user_id'].'">'.$datos[$inicio]['username'].'</a></td>';
  182. foreach ($datos as $dato) {
  183. $fila .= '<td align="center">'.(($dato['work_ok'] == 1) ? Display::return_icon('check.png') : Display::return_icon('aspa.png')).'</td>';
  184. $fila .= '<td align="center">'.(($dato['thread_ok'] == 1) ? Display::return_icon('check.png') : Display::return_icon('aspa.png')).'</td>';
  185. }
  186. $fila .= '</tr>';
  187. return $fila;
  188. }
  189. /**
  190. * @param $data
  191. * @param $numero_semanas
  192. *
  193. * @return array
  194. */
  195. function showStudentResultExport($data, $numero_semanas)
  196. {
  197. $fila = [];
  198. $fila[] = utf8_decode($data[1]['username']);
  199. $fila[] = utf8_decode($data[1]['fullname']);
  200. foreach ($data as $line) {
  201. $fila[] = ($line['work_ok'] == 1) ? get_lang('Yes') : get_lang('No');
  202. $fila[] = ($line['thread_ok'] == 1) ? get_lang('Yes') : get_lang('No');
  203. }
  204. return $fila;
  205. }