<?php  
	
	/*
		
		06/27/2011
		David Book: buzztouch.com, info@buzztouch.com
		------------------------------
		Save this file with a .PHP extension and put in on your PHP enabled backend. Like sample-scoreboard.php
		
		This script does two things. 
		1) It accepts a series of quiz results passed in the querystring and enters them in a database.
		2) It retrieves the last 50 scores from the same database and outputs JSON data for the mobile app.			
		
		The Quiz Scoreboard URL in the advanced settings of the Quiz Screen in the buzztouch control panel should look
		like this sample. The values in the URL inside the [square brackets] will be merged by the mobile app. 
		http://www.domain.com/appdata/sample-scoreboard.php?command=getQuizScoreboardData&appGuid=[buzztouchAppId]&apiKey=[buzztouchAPIKey]&screenId=[screenId]&deviceModel=[deviceModel]&deviceLatitude=[deviceLatitude]&deviceLongitude=[deviceLongitude]
		
		mySQL Database
		Your mySQL database will need a table to hold the quiz scores. Copy and paste this sample script in phpMyAdmin
		(or whatever panel you use to run SQL commands) to create the table.
		
		CREATE TABLE tbl_app_quiz_scores (
	  		id int(11) NOT NULL auto_increment,
	  		guid varchar(50) collate utf8_unicode_ci default NULL,
	  		appGuid varchar(50) collate utf8_unicode_ci default NULL,
	  		screenGuid varchar(50) collate utf8_unicode_ci default NULL,
	  		userGuid varchar(50) collate utf8_unicode_ci default NULL,
	  		userDisplayName varchar(50) collate utf8_unicode_ci default NULL,
	  		deviceId varchar(75) collate utf8_unicode_ci default NULL,
	  		deviceModel varchar(75) collate utf8_unicode_ci default NULL,
	  		deviceLatitude float default NULL,
	  		deviceLongitude float default NULL,
	  		totalPoints float default NULL,
	  		totalSeconds float default NULL,
	  		numberQuestions int(11) default NULL,
	  		numberCorrect int(11) default NULL,
	  		numberIncorrect int(11) default NULL,
	  		dateStampUTC datetime default NULL,
	  		PRIMARY KEY  (id)
		) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
	
		
		
		//////////////////////////////////////////////////////////////////
		//															 	//
		//	IMPORTANT.	This script has not been designed to protect	//
		//				against known security threats. Threats may		//
		//				include SQL injection attacks, and other		//
		//				common hacker-tricks. If you're unsure what		//
		//				the implecations of this are, contact your		//
		//				webmaster before continuing.					//
		//																//
		//////////////////////////////////////////////////////////////////
	
	
	*/
	
	//enter your database credentials here
	define("APP_DB_HOST", "localhost");
	define("APP_DB_NAME", "your database name");
	define("APP_DB_USER", "your database user name");
	define("APP_DB_PASS", "your database password");
	
	
	/* 
		The appVars array is filled with values passed from mobile application. These values may arrive in
		an HTTP GET Request in the querystring. Or, these values may arrive in an HTTP POST
		Request. Either use is fine. 
	*/
	$appVars = array();
	
	//misc info
	$appVars["command"] = "";
	$appVars["appGuid"] = "";
	$appVars["screenGuid"] = "";
	
	//device info
	$appVars["deviceId"] = "";
	$appVars["deviceModel"] = "";
	$appVars["deviceLatitude"] = "";
	$appVars["deviceLongitude"] = "";
	$appVars["numberOfQuestions"] = "";
	$appVars["totalPoints"] = "";
	$appVars["totalSeconds"] = "";
	$appVars["numberQuestions"] = "";
	$appVars["numberCorrect"] = "";
	$appVars["numberIncorrect"] = "";
	
	//login or register
	$appVars["userGuid"] = "";
	$appVars["userDisplayName"] = "";
	
	//todays date to the second for inserts
	$dtNow = date("Y-m-d H:i:s", time());
		
	//request method, POST or GET? 
	$reqMethod = "";
	if(isset($_SERVER["REQUEST_METHOD"])) $reqMethod = $_SERVER['REQUEST_METHOD'];
	if(strtoupper($reqMethod) == "POST"){
		foreach($_POST as $key => $value){
			if(array_key_exists($key, $appVars)){
				$appVars[$key] = fnFormInput($value);
			}
		}//end for
	}else{ //must be GET
		if(strtoupper($reqMethod) == "GET"){
			foreach($_GET as $key => $value){
				if(array_key_exists($key, $appVars)){
					$appVars[$key] = fnFormInput($value);
				}
			}//end for
		}//end if get
	}//not post
	
	//function to remove last character of a string
	function fnRemoveLastChar($theVal, $theChar){
		$theVal = trim($theVal);
		//if the last char is the char parameter
		if(substr(strtoupper($theVal), (strlen($theVal) - 1), 1) == strtoupper($theChar)){
			return substr($theVal, 0, strlen($theVal) - 1);
		}else{
			return $theVal;
		}
	}	
		
	//function to connect to database
	function db_connect($host, $db, $user, $pass){
		$conn  = mysql_connect($host, $user, $pass);
		if(!$conn){
			die("Oops, something went wrong processing your request? (1)");
		}else{
			if(!mysql_select_db($db)){
				die("Oops, something went wrong processing your request? (2)");
			}else{
				return $conn;
			}
		}
	}	
			
	//function to execute a SQL statement. Return 1 on success.
	function fnExecuteNonQuery($theSql, $host, $db, $user, $pass){
		$conn = db_connect($host, $db, $user, $pass);
		if(!$conn ){
			die("Oops, something went wrong processing your request? (5)");
		}else{
			$result = mysqli_query($theSql, $conn);
			if($result){
				return 1;
			}else{
				die("Oops, something went wrong processing your request? (6)");
			}
		}
	}			
		
	//function returns to an array of database results.
	function fnDbGetResult($theSql, $host, $db, $user, $pass){
		$conn = db_connect($host, $db, $user, $pass);
		if(!$conn ){
			die("Oops, something went wrong processing your request? (3)");
		}else{ 
			$result = mysqli_query($theSql, $conn);
			if($result){
				return $result;
			}else{
				die("Oops, something went wrong processing your request? (4)");
			}
		}
	}		
		
		
	//##########################################################
	//Command value determines what JSON result string to return
	//This approach allows this script to be extended by passing
	//different command in the querystring for differnet functions.
	$JSON = "";
	
	switch (strtoupper($appVars["command"])){
	
		//get quiz scores for scoreboard
		case "GETQUIZSCOREBOARDDATA":

			//we will be returning the latest quiz results as "childItems"
			$JSON = "{\"childItems\":[";

		
			if($appVars["appGuid"] != "" && $appVars["screenGuid"] != "" && $appVars["deviceId"] != ""){
				
				//must be numeric..
				if(!is_numeric($appVars["totalPoints"])) $appVars["totalPoints"] = "0";
				if(!is_numeric($appVars["totalSeconds"])) $appVars["totalSeconds"] = "0";
				if(!is_numeric($appVars["numberQuestions"])) $appVars["numberQuestions"] = "0";
				if(!is_numeric($appVars["numberCorrect"])) $appVars["numberCorrect"] = "0";
				if(!is_numeric($appVars["numberIncorrect"])) $appVars["numberIncorrect"] = "0";

				//use n/a if no display name was entered and no userGuid was sent
				if($appVars["userGuid"] == "" && $appVars["userDisplayName"] == "") $appVars["userDisplayName"] = "anonymous ";

				//remove duplicate rows. This happens when user goes "back-n-forth" asking for the scoreboard
				$strSql = "DELETE FROM tbl_app_quiz_scores WHERE appGuid = '" . $appVars["appGuid"] . "'";
				$strSql .= " AND screenGuid = '" . $appVars["screenGuid"] . "' AND deviceId = '" . $appVars["deviceId"] . "'";
				$strSql .= " AND totalPoints = '" . $appVars["totalPoints"] . "' AND totalSeconds = '" . $appVars["totalSeconds"] . "'";
				fnExecuteNonQuery($strSql, APP_DB_HOST, APP_DB_NAME, APP_DB_USER, APP_DB_PASS); 
				
				//insert new row
				$strSql = "INSERT INTO tbl_app_quiz_scores (guid, appGuid, screenGuid, userGuid, userDisplayName, ";
				$strSql .= "deviceId, deviceModel, deviceLatitude, deviceLongitude, totalPoints, totalSeconds, ";
				$strSql .= "numberQuestions, numberCorrect, numberIncorrect, dateStampUTC ) VALUES (";
				$strSql .= "'" . strtoupper(fnCreateGuid()) . "','" . $appVars["appGuid"] . "','" . $appVars["screenGuid"] . "',";
				$strSql .= "'" . $appVars["userGuid"] . "','" . $appVars["userDisplayName"] . "','" . $appVars["deviceId"] . "',";
				$strSql .= "'" . $appVars["deviceModel"] . "','" . $appVars["deviceLatitude"] . "','" . $appVars["deviceLongitude"] . "',";
				$strSql .= "'" . $appVars["totalPoints"] . "','" . $appVars["totalSeconds"] . "','" . $appVars["numberQuestions"] . "',";
				$strSql .= "'" . $appVars["numberCorrect"] . "','" . $appVars["numberIncorrect"] . "','" . $dtNow . "')";
				fnExecuteNonQuery($strSql, APP_DB_HOST, APP_DB_NAME, APP_DB_USER, APP_DB_PASS); 
					
				//get the highest 50 scores..
				$strSql = "SELECT S.totalPoints, S.totalSeconds, S.deviceModel, S.userDisplayName, S.dateStampUTC ";
				$strSql .= "FROM tbl_app_quiz_scores AS S ";
				$strSql .= " WHERE S.appGuid = '" . $appVars["appGuid"] . "'";
				$strSql .= " AND S.screenGuid = '" . $appVars["screenGuid"] . "'";
				$strSql .= " ORDER BY S.totalPoints DESC ";
				$strSql .= " LIMIT 0, 50 ";
				$res = fnDbGetResult($strSql, APP_DB_HOST, APP_DB_NAME, APP_DB_USER, APP_DB_PASS); 
				$cnt = 0;
				if($res){
					$numRows = mysqli_num_rows($res);	
					if($numRows > 0){
						while($row = mysqli_fetch_array($res)){
							$cnt++;
								$userDisplayName = fnFormOutput($row["userDisplayName"]);
								if($userDisplayName == "") $userDisplayName = "anonymous";
								$quizDate = gmdate("m-d-Y H:i:s", strtotime($row["dateStampUTC"])) . " (gmt)";
								$JSON .= "\n{\"itemId\":\"" . $cnt . "\", \"titleText\":\"" . $userDisplayName  . "\", \"descriptionText\":\"" . $quizDate . " " . $row["totalPoints"] . " points in " . $row["totalSeconds"] . " sec. " . $row["deviceModel"] . "\", \"rowAccessoryType\":\"none\", \"loadScreenWithItemId\":\"none\"},";
						}//end while
					}//if num rows
				}//end res
					
					
					
			}
			
			//remove last comma
			$JSON = fnRemoveLastChar($JSON, ",");
			$JSON .= "\n]}";					
			
			break;
		
	}//end switch
	//##########################################################
	
	
//print JSON string	
if($JSON != ""){
	echo $JSON;
}else{
	echo "{\"invalid\":\"request\"}";
}


exit();
	


	
?>