export.php 5.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. use Chamilo\CourseBundle\Entity\CSurveyAnswer;
  4. use Chamilo\CourseBundle\Entity\CSurveyQuestionOption;
  5. require_once __DIR__.'/../../main/inc/global.inc.php';
  6. api_protect_course_script(true);
  7. api_protect_teacher_script();
  8. $surveyId = isset($_GET['survey']) ? (int) $_GET['survey'] : 0;
  9. $surveyData = SurveyManager::get_survey($surveyId);
  10. $courseId = api_get_course_int_id();
  11. if (empty($surveyData)) {
  12. api_not_allowed(true);
  13. }
  14. $plugin = SurveyExportCsvPlugin::create();
  15. $allowExportIncomplete = 'true' === $plugin->get('export_incomplete');
  16. if ($plugin->get('enabled') !== 'true') {
  17. api_not_allowed(true);
  18. }
  19. $questionsData = SurveyManager::get_questions($surveyId, $courseId);
  20. // Sort questions by their "sort" field
  21. $questionsData = array_filter(
  22. $questionsData,
  23. function ($questionData) {
  24. return in_array($questionData['type'], ['yesno', 'multiplechoice', 'open']);
  25. }
  26. );
  27. $numberOfQuestions = count($questionsData);
  28. usort(
  29. $questionsData,
  30. function ($qL, $qR) {
  31. if ($qL['sort'] == $qR['sort']) {
  32. return 0;
  33. }
  34. return $qL['sort'] < $qR['sort'] ? -1 : 1;
  35. }
  36. );
  37. $content = [];
  38. $content[] = firstRow($questionsData);
  39. $surveyAnswers = getSurveyAnswers($courseId, $surveyId);
  40. // Process answers
  41. $i = 1;
  42. foreach ($surveyAnswers as $answer) {
  43. $row = otherRow($questionsData, $answer['user'], $courseId);
  44. if (!$allowExportIncomplete && count($row) < $numberOfQuestions) {
  45. continue;
  46. }
  47. array_unshift($row, $i);
  48. $content[] = $row;
  49. $i++;
  50. }
  51. // Generate file
  52. $fileName = md5($surveyId.time());
  53. Export::arrayToCsv($content, $fileName, false, "'");
  54. /**
  55. * Generate the first row for file.
  56. *
  57. * @param $questions
  58. *
  59. * @return array
  60. */
  61. function firstRow($questions)
  62. {
  63. array_pop($questions);
  64. $positions = array_keys($questions);
  65. $row = ['DATID'];
  66. foreach ($positions as $position) {
  67. $row[] = sprintf("P%02d", $position + 1);
  68. }
  69. $row[] = 'DATOBS';
  70. return $row;
  71. }
  72. /**
  73. * Get unique answer for surveys by users.
  74. *
  75. * @param int $courseId
  76. * @param int $surveyId
  77. *
  78. * @return array
  79. */
  80. function getSurveyAnswers($courseId, $surveyId)
  81. {
  82. $surveyAnswers = Database::getManager()
  83. ->createQuery(
  84. 'SELECT sa.user, MIN(sa.iid) AS id FROM ChamiloCourseBundle:CSurveyAnswer sa
  85. WHERE sa.cId = :course AND sa.surveyId = :survey
  86. GROUP BY sa.user ORDER BY id ASC'
  87. )
  88. ->setParameters(['course' => $courseId, 'survey' => $surveyId])
  89. ->getResult();
  90. return $surveyAnswers;
  91. }
  92. /**
  93. * @param string $user
  94. * @param int $courseId
  95. * @param int $surveyId
  96. * @param int $questionId
  97. *
  98. * @return array
  99. */
  100. function getQuestionOptions($user, $courseId, $surveyId, $questionId)
  101. {
  102. $options = Database::getManager()
  103. ->createQuery(
  104. 'SELECT sqo FROM ChamiloCourseBundle:CSurveyQuestionOption sqo
  105. INNER JOIN ChamiloCourseBundle:CSurveyAnswer sa
  106. WITH
  107. sqo.cId = sa.cId
  108. AND sqo.questionId = sa.questionId
  109. AND sqo.surveyId = sa.surveyId
  110. AND sqo.iid = sa.optionId
  111. WHERE sa.user = :user AND sa.cId = :course AND sa.surveyId = :survey AND sa.questionId = :question'
  112. )
  113. ->setParameters(
  114. [
  115. 'user' => $user,
  116. 'course' => $courseId,
  117. 'survey' => $surveyId,
  118. 'question' => $questionId,
  119. ]
  120. )
  121. ->getResult();
  122. return $options;
  123. }
  124. /**
  125. * @param int $questionId
  126. * @param int $surveyId
  127. * @param int $courseId
  128. * @param string $user
  129. *
  130. * @throws \Doctrine\ORM\NonUniqueResultException
  131. *
  132. * @return CSurveyAnswer|null
  133. */
  134. function getOpenAnswer($questionId, $surveyId, $courseId, $user)
  135. {
  136. $answer = Database::getManager()
  137. ->createQuery(
  138. 'SELECT sa FROM ChamiloCourseBundle:CSurveyAnswer sa
  139. WHERE sa.cId = :course AND sa.surveyId = :survey AND sa.questionId = :question AND sa.user = :user'
  140. )
  141. ->setParameters(['course' => $courseId, 'survey' => $surveyId, 'question' => $questionId, 'user' => $user])
  142. ->getOneOrNullResult();
  143. return $answer;
  144. }
  145. /**
  146. * Generate the content rows for file.
  147. *
  148. * @param array $questions
  149. * @param string $user
  150. * @param int $courseId
  151. *
  152. * @throws \Doctrine\ORM\NonUniqueResultException
  153. *
  154. * @return array
  155. */
  156. function otherRow($questions, $user, $courseId)
  157. {
  158. $row = [];
  159. foreach ($questions as $question) {
  160. if ('open' === $question['type']) {
  161. $answer = getOpenAnswer($question['question_id'], $question['survey_id'], $courseId, $user);
  162. if ($answer) {
  163. $row[] = Security::remove_XSS($answer->getOptionId());
  164. }
  165. } else {
  166. $options = getQuestionOptions(
  167. $user,
  168. $courseId,
  169. $question['survey_id'],
  170. $question['question_id']
  171. );
  172. /** @var CSurveyQuestionOption|null $option */
  173. $option = end($options);
  174. if ($option) {
  175. $value = $option->getSort();
  176. $row[] = '"'.$value.'"';
  177. }
  178. }
  179. }
  180. return $row;
  181. }