<?php
/*
 * Created 03-2016 by Marcel Minke (marcel.minke@limesurvey.org)
 *
 * This script is from within a survey using an Ajax call and gets data from DB to push it to a charting tool.
 */

// SETTINGS
$surveytable = 'lime_old_survey_834857_20161123121133';

//if re-activated
$tokentable = 'lime_old_tokens_834857_20161123121133';

//SETTINGS END


//check if debug mode is enabled
if(isset($_GET['debug']))
{
	$debug = intval($_GET['debug']);
}
else
{
	$debug = 0;
}
//check if panel details and to be used column name was passed
if(!(isset($_GET['token']) AND $_GET['token'] != "" AND isset($_GET['panelid']) AND $_GET['panelid'] != "" AND isset($_GET['datacolumn']) and $_GET['datacolumn'] != ""))
{
	//log error
	debug_to_console($debug, "Not all required parameters were passed for token ".$_GET['token']." Panel: ".$_GET['panelid']." - Column: ".$_GET['datacolumn'] );
	exit();
}



//error reporting
ini_set('display_errors',1);
error_reporting(E_ALL  & ~E_NOTICE);

/*
	echo "<pre>";
	print_r($dataarray);
	echo "</pre>";
 */

/*
 * ------------ START WORKFLOW --------------
 */

//basepath needs to be defined in order to include config file (there is a check for this!)
define("BASEPATH", dirname(dirname(__FILE__)));

//push all config details (config.details returns them as array) into this array
$config = require_once("application/config/config.php");



// DB CONNECTION DETAILS
$username = $config['components']['db']['username'];
$password = $config['components']['db']['password'];
$dbprefix = $config['components']['db']['tablePrefix'];
//the DB connection string looks like this:
//'mysql:host=localhost;port=3306;dbname=etcvps88_devm;'
//so we need to check for the last "=", add 1 and get the string without the ending ";" character:
$database = substr($config['components']['db']['connectionString'],strrpos($config['components']['db']['connectionString'],"=")+1,-1);

//connect to DB
$connect = mysqli_connect("localhost",$username,$password,$database) or die( "Unable connect to database '$database'");

//set charset to deal with special characters
mysqli_set_charset($connect,'utf8');


//sanitize passed data
$panelid = sanitizeInputDB($_GET['panelid'], $connect);
$datacolumn = sanitizeInputDB($_GET['datacolumn'], $connect);
$token = sanitizeInputDB($_GET['token'], $connect);

//adjust panel information for query -> sometimes we need to check for > 1 value!
$panelquery = "";
if($panelid == "su" OR $panelid == "ne" OR $panelid == "fe" OR $panelid == "nu" OR $panelid == "anp" )
{
	$panelquery = " (attribute_1='su' OR attribute_1='ne' OR attribute_1='fe' OR attribute_1='nu' OR attribute_1='anp') ";
}
elseif($panelid == "ga" OR $panelid == "r" OR $panelid == "di")
{
	$panelquery = " (attribute_1='ga' OR attribute_1='r' OR attribute_1='di') ";
}
elseif($panelid == "par" OR $panelid == "pat")
{
	$panelquery = " (attribute_1='par' OR attribute_1='pat') ";
}
else
{
	$panelquery = " (attribute_1='$panelid') ";
}

/*
 * Overview:
 * Panel 1 (neonatal panel) = ne, fe and s = " surgeons, nurses, neonatologists and fetal medics"
 * Panel 2 (non-neonatal panel) = nu pae and r = "paediatricians, dieticians and researchers"
 * Panel 3 (lay panel) = par and pat = "parents and people who were born with gastroschisis"
 */

//get data for current TOKEN
$tokenquery = "SELECT $datacolumn FROM $surveytable
WHERE token='$token' AND submitdate IS NOT NULL";
$tokenresult = mysqli_query($connect, $tokenquery) or die("Couldn't query token data: <br/>$tokenquery<br />");

debug_to_console($debug, mysqli_real_escape_string($connect, $tokenquery));

//loop through data
while($tokendata = mysqli_fetch_array($tokenresult,MYSQLI_NUM))
{
	$tokenanswer = intval($tokendata[0]);
}


//get AVG data -> NOTE that responses have to be <=9 because 10=don't know option
$avgquery = "SELECT $datacolumn FROM $surveytable, $tokentable
		  WHERE $panelquery AND $datacolumn!='10' AND submitdate IS NOT NULL AND $surveytable.token = $tokentable.token ORDER BY $datacolumn ASC";
$avgresult = mysqli_query($connect, $avgquery) or die("Couldn't query MEDIAN data: <br/>$avgquery<br />");

debug_to_console($debug, mysqli_real_escape_string($connect, $avgquery));

//loop through data
while($avgdata = mysqli_fetch_array($avgresult,MYSQLI_NUM))
{
	$medianarray[] = intval($avgdata[0]);
}
$median = calculate_median($medianarray);




//get RESPONSES
$query = "SELECT $datacolumn FROM $surveytable, $tokentable
		  WHERE $panelquery AND submitdate IS NOT NULL AND $surveytable.token = $tokentable.token";
$result = mysqli_query($connect, $query) or die("Couldn't query panel data: <br/>$query<br />");

debug_to_console($debug, mysqli_real_escape_string($connect, $query));

$dataarray = array();

//pre-populate with zero values
for($i = 1; $i <= 9; $i++)
{
	$dataarray[$i] = 0;
}

//loop through data
while($data = mysqli_fetch_array($result,MYSQLI_NUM))
{
	//only scores from 1 to 9 are valid (10 is "don't know")
	if($data[0] < 10 && is_numeric($data[0]))
	{
		$dataarray[$data[0]]++;
	}

}

$count = count($dataarray);
$total = array_sum($dataarray);


//calculate percentage values
for($i = 1; $i <= 9; $i++)
{
	if($dataarray[$i] != 0 && $total != 0)
	{
		$dataarray[$i] = round(($dataarray[$i] / $total)*100,0);
	}

}

//add token data detail at the very first position
$dataarray[0] = $tokenanswer;

//add median at last position
$dataarray[10] = $median;

ksort($dataarray);
debug_to_console($debug, "We have $count values which sum up to $total and have a median of $median.<br />For token $token answer $tokenanswer was given for question $datacolumn");

debug_to_console($debug, $dataarray);


//free result set
mysqli_free_result($result);

//close connection
mysqli_close($connect);

/*
echo "<pre>";
print_r($dataarray);
echo "</pre>";
*/

echo json_encode($dataarray);



function sanitizeInputDB($data, $connect)
{
    if (!isset($data)) {
        return null;
    }

    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
	$data = mysqli_real_escape_string($connect, $data);

    return $data;
}

function debug_to_console( $debug, $data ) {

	if(isset($debug) && $debug==1)
	{
		if ( is_array( $data ) )
			$output = "<script>console.log( 'Debugging (Array): " . implode( ',', $data) . "' );</script>";
		else
			$output = "<script>console.log( 'Debugging: " . $data . "' );</script>";

		echo $output;
	}
}

function calculate_median($arr) {
    $count = count($arr); //total numbers in array
    $middleval = floor(($count-1)/2); // find the middle value, or the lowest middle value
    if($count % 2) { // odd number, middle is the median

		$median = $arr[$middleval];
    } else { // even number, calculate avg of 2 medians
        $low = $arr[$middleval];
        $high = $arr[$middleval+1];
        $median = (($low+$high)/2);

		//round UP:
		$median = round($median, 0, PHP_ROUND_HALF_UP);
    }
    return $median;
}

/*
function debug($text, $_GET['db'])
{
	if(isset($_GET['debug']) && intval($_GET['debug'])==1)
	{
		echo "<br />$text<br />";
	}
}
*/
?>