webAdministracion/questionBreakdown.php

<?php 


// This script fetches how many people, for every question, have answered a given score
// and spits out a json with an array of question ID, question premise, score and count

// 	require_once 'processes/config.php';
	require_once 'processes/dbh.inc.php';
// 	require_once 'processes/checkLogin.php';



$experienceID = mysqli_real_escape_string($connection, trim($_POST['experienceID'])); // 26
$momentID = mysqli_real_escape_string($connection, trim($_POST['momentID'])); // 196
$studentID = mysqli_real_escape_string($connection, trim($_POST['studentID']));


// WARNING MAYBE AÑADIR/QUITAR momentID
// Count the number of questions for the experience
$sqlCountQuestion = "SELECT DISTINCT Q.id
						FROM question as Q
						JOIN subquestionnair_question AS SQ
						JOIN experience_subquestionnair AS ES
							WHERE SQ.id_question = Q.id
							AND SQ.id_subquestionnair = ES.id_subquestionnair
							AND SQ.id_subquestionnair = '$momentID'
							AND ES.id_experience = '$experienceID'";
$resultCountQuestion = mysqli_query($connection, $sqlCountQuestion);
$countQuestions = $resultCountQuestion->num_rows;



// Fetch all the results, depending if all students or a specific one
if($studentID === "all") {

	$sql = "SELECT Q.id AS questionID,
		   	Q.premise AS questionPremise,
	       	A.value AS score,
    	   	COUNT(id_student) AS `count`
			FROM question AS Q
			JOIN answer AS A
    	    JOIN subquestionnair_question AS SQ
        	JOIN experience_subquestionnair AS ES
		        WHERE A.id_question = Q.id
	    	    AND A.id_question = SQ.id_question
    	    	AND A.id_subquestionnair = SQ.id_subquestionnair
				AND SQ.id_subquestionnair = ES.id_subquestionnair
				AND ES.id_experience = '$experienceID'
    	        AND SQ.id_subquestionnair = '$momentID'
					GROUP BY score, questionID
					ORDER BY score, questionID";

} else {

	$sql = "SELECT Q.id AS questionID,
		   	Q.premise AS questionPremise,
	       	A.value AS score,
    	   	COUNT(id_student) AS `count`
			FROM question AS Q
			JOIN answer AS A
    	    JOIN subquestionnair_question AS SQ
        	JOIN experience_subquestionnair AS ES
		        WHERE A.id_question = Q.id
	    	    AND A.id_question = SQ.id_question
    	    	AND A.id_subquestionnair = SQ.id_subquestionnair
				AND SQ.id_subquestionnair = ES.id_subquestionnair
				AND ES.id_experience = '$experienceID'
    	        AND SQ.id_subquestionnair = '$momentID'
        	    AND A.id_student = '$studentID'
					GROUP BY score, questionID
					ORDER BY score, questionID";

}

$result = mysqli_query($connection, $sql);



// Initialize Arrays
$datasets = [];
$dataLabels = [];
$scoreToIndex = [];
$i = 0;
$questionIDToIndex = [];
$j = 0;



// Prepare result
while($row = mysqli_fetch_object($result)) {


	// Parse score (cause if might come from an open question)
	// or it might be a float, while we should expect an int
	// then cast it to string to use it as a map key
	
	$pointLabel = ($studentID === "all") ? " # of people who answered '" : " # of times student answered '";
	
	if(is_numeric($row->score)) {
		$score = $pointLabel . strval((int)$row->score) . "'"; // answers may be 5.0 or 5, so first we take out the .
	} else {
		continue;
	}


	// Create new label if new question is detected
	if($questionIDToIndex[$row->questionID] || $questionIDToIndex[$row->questionID] === 0) { // index 0 should be skipped
		
		// nothing
		
	} else {
	
		// Create new question
		$dataLabels[] = $row->questionID; //substr($row->questionPremise, 0, 30);//questionPremise;
		
		// Store question index for $data array (inside dataset)
		$questionIDToIndex[$row->questionID] = $j;
		$j++;
		
	}

	
	
	// Create dataset for corresponding score
	if($scoreToIndex[$score] || $scoreToIndex[$score] === 0) { // index 0 should be skipped
	
		// nothing
	
	} else {

		// serves as a tally to check
		// which scores have been already
		// processed AND as a map of
		// score to index of the
		// $dataset array
		$scoreToIndex[$score] = $i;
		$i++;


		// Create new dataset for this label
		$datasets[] = [
			'label' => $score,
			'data' => array_fill(0, $countQuestions, 0), // array of countQuestions 0's
			'backgroundColor' => '#' . substr(sha1($score), 0, 6),
// 			'borderWidth' => 3,
// 			'borderColor' => '#' . substr(sha1($score), 0, 6),
			'stack' => $score
// 			'fill' => false
		];
		
	}


	// Append data point to corresponding question
	$datasets[$scoreToIndex[$score]]['data'][$questionIDToIndex[$row->questionID]] = (int)$row->count;	


}




/*
	let data = {
		labels: dataLabels,
		datasets: [{
			label: auxiliaries['myChart2'].label,
			data: dataPoints,
			backgroundColor: auxiliaries['myChart2'].backgroundColor,
			borderColor: auxiliaries['myChart2'].borderColor,
			fill: false
		}]
	};
*/


$arr = [
	'datasets' => $datasets,
	'dataLabels' => $dataLabels
];


// Spit out response JSON
echo header("Content-Type: application/json; charset=utf-8");
echo json_encode($arr);