暂无描述

questionBreakdown.php 4.8KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185
  1. <?php
  2. // This script fetches how many people, for every question, have answered a given score
  3. // and spits out a json with an array of question ID, question premise, score and count
  4. // require_once 'processes/config.php';
  5. require_once 'processes/dbh.inc.php';
  6. // require_once 'processes/checkLogin.php';
  7. $experienceID = mysqli_real_escape_string($connection, trim($_POST['experienceID'])); // 26
  8. $momentID = mysqli_real_escape_string($connection, trim($_POST['momentID'])); // 196
  9. $studentID = mysqli_real_escape_string($connection, trim($_POST['studentID']));
  10. // WARNING MAYBE AÑADIR/QUITAR momentID
  11. // Count the number of questions for the experience
  12. $sqlCountQuestion = "SELECT DISTINCT Q.id
  13. FROM question as Q
  14. JOIN subquestionnair_question AS SQ
  15. JOIN experience_subquestionnair AS ES
  16. WHERE SQ.id_question = Q.id
  17. AND SQ.id_subquestionnair = ES.id_subquestionnair
  18. AND SQ.id_subquestionnair = '$momentID'
  19. AND ES.id_experience = '$experienceID'";
  20. $resultCountQuestion = mysqli_query($connection, $sqlCountQuestion);
  21. $countQuestions = $resultCountQuestion->num_rows;
  22. // Fetch all the results, depending if all students or a specific one
  23. if($studentID === "all") {
  24. $sql = "SELECT Q.id AS questionID,
  25. Q.premise AS questionPremise,
  26. A.value AS score,
  27. COUNT(id_student) AS `count`
  28. FROM question AS Q
  29. JOIN answer AS A
  30. JOIN subquestionnair_question AS SQ
  31. JOIN experience_subquestionnair AS ES
  32. WHERE A.id_question = Q.id
  33. AND A.id_question = SQ.id_question
  34. AND A.id_subquestionnair = SQ.id_subquestionnair
  35. AND SQ.id_subquestionnair = ES.id_subquestionnair
  36. AND ES.id_experience = '$experienceID'
  37. AND SQ.id_subquestionnair = '$momentID'
  38. GROUP BY score, questionID
  39. ORDER BY score, questionID";
  40. } else {
  41. $sql = "SELECT Q.id AS questionID,
  42. Q.premise AS questionPremise,
  43. A.value AS score,
  44. COUNT(id_student) AS `count`
  45. FROM question AS Q
  46. JOIN answer AS A
  47. JOIN subquestionnair_question AS SQ
  48. JOIN experience_subquestionnair AS ES
  49. WHERE A.id_question = Q.id
  50. AND A.id_question = SQ.id_question
  51. AND A.id_subquestionnair = SQ.id_subquestionnair
  52. AND SQ.id_subquestionnair = ES.id_subquestionnair
  53. AND ES.id_experience = '$experienceID'
  54. AND SQ.id_subquestionnair = '$momentID'
  55. AND A.id_student = '$studentID'
  56. GROUP BY score, questionID
  57. ORDER BY score, questionID";
  58. }
  59. $result = mysqli_query($connection, $sql);
  60. // Initialize Arrays
  61. $datasets = [];
  62. $dataLabels = [];
  63. $scoreToIndex = [];
  64. $i = 0;
  65. $questionIDToIndex = [];
  66. $j = 0;
  67. // Prepare result
  68. while($row = mysqli_fetch_object($result)) {
  69. // Parse score (cause if might come from an open question)
  70. // or it might be a float, while we should expect an int
  71. // then cast it to string to use it as a map key
  72. $pointLabel = ($studentID === "all") ? " # of people who answered '" : " # of times student answered '";
  73. if(is_numeric($row->score)) {
  74. $score = $pointLabel . strval((int)$row->score) . "'"; // answers may be 5.0 or 5, so first we take out the .
  75. } else {
  76. continue;
  77. }
  78. // Create new label if new question is detected
  79. if($questionIDToIndex[$row->questionID] || $questionIDToIndex[$row->questionID] === 0) { // index 0 should be skipped
  80. // nothing
  81. } else {
  82. // Create new question
  83. $dataLabels[] = $row->questionID; //substr($row->questionPremise, 0, 30);//questionPremise;
  84. // Store question index for $data array (inside dataset)
  85. $questionIDToIndex[$row->questionID] = $j;
  86. $j++;
  87. }
  88. // Create dataset for corresponding score
  89. if($scoreToIndex[$score] || $scoreToIndex[$score] === 0) { // index 0 should be skipped
  90. // nothing
  91. } else {
  92. // serves as a tally to check
  93. // which scores have been already
  94. // processed AND as a map of
  95. // score to index of the
  96. // $dataset array
  97. $scoreToIndex[$score] = $i;
  98. $i++;
  99. // Create new dataset for this label
  100. $datasets[] = [
  101. 'label' => $score,
  102. 'data' => array_fill(0, $countQuestions, 0), // array of countQuestions 0's
  103. 'backgroundColor' => '#' . substr(sha1($score), 0, 6),
  104. // 'borderWidth' => 3,
  105. // 'borderColor' => '#' . substr(sha1($score), 0, 6),
  106. 'stack' => $score
  107. // 'fill' => false
  108. ];
  109. }
  110. // Append data point to corresponding question
  111. $datasets[$scoreToIndex[$score]]['data'][$questionIDToIndex[$row->questionID]] = (int)$row->count;
  112. }
  113. /*
  114. let data = {
  115. labels: dataLabels,
  116. datasets: [{
  117. label: auxiliaries['myChart2'].label,
  118. data: dataPoints,
  119. backgroundColor: auxiliaries['myChart2'].backgroundColor,
  120. borderColor: auxiliaries['myChart2'].borderColor,
  121. fill: false
  122. }]
  123. };
  124. */
  125. $arr = [
  126. 'datasets' => $datasets,
  127. 'dataLabels' => $dataLabels
  128. ];
  129. // Spit out response JSON
  130. echo header("Content-Type: application/json; charset=utf-8");
  131. echo json_encode($arr);