Keine Beschreibung

categoryBreakdown.php 5.0KB

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