Ingen beskrivning

subcategoryBreakdown.php 5.1KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192
  1. <?php
  2. // This script fetches how many people, for every subcategory, have answered a given score
  3. // and spits out a json with an array of subcategory ID, subcategory 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 subcategories for the experience
  11. $sqlCountSubcategories = "SELECT DISTINCT S.id
  12. FROM subcategory AS S
  13. JOIN question as Q
  14. JOIN subquestionnair_question AS SQ
  15. JOIN experience_subquestionnair AS ES
  16. WHERE S.id = Q.id_subcategory
  17. AND SQ.id_question = Q.id
  18. AND SQ.id_subquestionnair = ES.id_subquestionnair
  19. AND ES.id_experience = '$experienceID';";
  20. $resultCountSubcategories = mysqli_query($connection, $sqlCountSubcategories);
  21. $countSubcategories = $resultCountSubcategories->num_rows;
  22. // Fetch all the results, depending if all students or a specific one
  23. if($studentID === "all") {
  24. $sql = "SELECT S.id AS subcategoryID,
  25. S.subcategoria AS subcategoryName,
  26. A.value AS score,
  27. COUNT(id_student) AS `count`
  28. FROM subcategory AS S
  29. LEFT JOIN question AS Q
  30. ON Q.id_subcategory = S.id
  31. JOIN answer AS A
  32. JOIN subquestionnair_question AS SQ
  33. JOIN experience_subquestionnair AS ES
  34. -- WHERE Q.id_subcategory = S.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, subcategoryID
  42. ORDER BY score, subcategoryID";
  43. } else {
  44. $sql = "SELECT S.id AS subcategoryID,
  45. S.subcategoria AS subcategoryName,
  46. A.value AS score,
  47. COUNT(id_student) AS `count`
  48. FROM subcategory AS S
  49. LEFT JOIN question AS Q
  50. ON Q.id_subcategory = S.id
  51. JOIN answer AS A
  52. JOIN subquestionnair_question AS SQ
  53. JOIN experience_subquestionnair AS ES
  54. -- WHERE Q.id_subcategory = S.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'
  60. AND SQ.id_subquestionnair = '$momentID'
  61. AND A.id_student = '$studentID' -- 118
  62. GROUP BY score, subcategoryID
  63. ORDER BY score, subcategoryID";
  64. }
  65. $result = mysqli_query($connection, $sql);
  66. // Initialize Arrays
  67. $datasets = [];
  68. $dataLabels = [];
  69. $scoreToIndex = [];
  70. $i = 0;
  71. $subcategoryIDToIndex = [];
  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 subcategory is detected
  85. if($subcategoryIDToIndex[$row->subcategoryID] || $subcategoryIDToIndex[$row->subcategoryID] === 0) { // index 0 should be skipped
  86. // nothing
  87. } else {
  88. // Create new subcategory
  89. $dataLabels[] = $row->subcategoryName;
  90. // Store subcategory index for $data array (inside dataset)
  91. $subcategoryIDToIndex[$row->subcategoryID] = $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, $countSubcategories, 0), // array of countSubcategories 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 subcategory
  117. $datasets[$scoreToIndex[$score]]['data'][$subcategoryIDToIndex[$row->subcategoryID]] = (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);