NarKampen/dbFiles/OnlineGames.php

<?php
/* Games.php */ 
	$hostname = 'localhost';
	$username = 'narKampen';
	$pass = 'IfRLzj2HJBXA9eei';
	$database = 'narKampen';
	
	$conn = new mysqli($hostname, $username, $pass, $database);
	if (!$conn) {
		die("Connection Failed. ". mysqli_connect_error());
	}
	mysqli_set_charset($conn,'utf8');
	
	$userId = $conn->real_escape_string(isset($_POST['userId'])?$_POST['userId']:"");
	$callFunction = $conn->real_escape_string(isset($_POST['f'])?$_POST['f']:"");
	$gameId = $conn->real_escape_string(isset($_POST['gameId'])?$_POST['gameId']:"");
	$userName = $conn->real_escape_string(isset($_POST['userName'])?$_POST['userName']:"");
	$userId = $conn->query("SELECT id FROM users where username = '$userName'")->fetch_assoc()['id'];
	
	if ($callFunction === "list"){	
		/* OLD SQL
		 $sql = "SELECT game.*, users.username as playerToAct FROM game " .
				"LEFT JOIN users on currentPlayer = users.id " .
				"WHERE game.id IN (SELECT gameId FROM gamePlayers WHERE playerId = $userId) " .
				"AND game.status != 'FINISHED' ".
				"ORDER BY FIELD(PlayerToAct, '$userName') DESC, lastPlayedDate DESC";
*/

		$sql = "SELECT game.*, users.id as userId, users.username as username, gamePlayers.userLockedQuestions, gamePlayers.status playerStatus, gamePlayers.playerRound FROM game " . 
			"INNER JOIN gamePlayers ON gamePlayers.gameId = game.id " . 
			"INNER JOIN users ON users.id = playerId " .
			"WHERE game.id IN (SELECT gameId FROM gamePlayers WHERE playerId = $userId) " .
			"AND game.status NOT IN ('FINISHED') " .
			"ORDER BY FIELD(currentPlayer, $userId) DESC, lastPlayedDate DESC";
		$result = $conn->query($sql);

		if ($result->num_rows > 0) {
			$returnArray = array();
			$i = 0;
			while ($data = $result->fetch_assoc()) {
				foreach ($data as $key => $value) {
					$returnArray[$i][$key] = $value;
				}
				$i++;
			}
			echo json_encode($returnArray);
			
		} else {
			echo "No games found for user";
		}
	} else if ($callFunction === "decline"){
		$sql = "UPDATE gamePlayers SET status = 'DECLINED' WHERE gameId = $gameId AND playerId = (SELECT id from users WHERE username = '$userName')";
		$result = $conn->query($sql);
		if (!$result) {
			echo "UPDATE player status failed " . $conn->error;
		} else {
			$sql = "SELECT count(*) as c FROM gamePlayers WHERE gameId = $gameId AND status NOT LIKE 'DECLINED'";
			$result = $conn->query($sql);
			if ($result !== false) {
				while ($data = $result->fetch_assoc()) {
					if ($data['c'] == 1) { // Alla utom den som startade spelet har nekat, ta bort spelet från game
						$sql = "DELETE FROM game WHERE id = $gameId";
						$result = $conn->query($sql);
						if (!$result) {
							echo "UPDATE game status failed " . $conn->error;
						}
					}
				}
			}
		}
	} else if ($callFunction === "accept") {
		$sql = "UPDATE gamePlayers SET status = 'ACCEPTED' WHERE gameId = $gameId AND playerId = $userId";
		$result = $conn->query($sql);
		if (!$result) {
			echo $conn->error;
		} else {
			$sql = "SELECT * FROM gamePlayers WHERE gameId = $gameId";
			$result = $conn->query($sql);
			$done = true;
			$playerIds = array();
			while ($data = $result->fetch_assoc()) {
				if ($data['status'] != "ACCEPTED") {
					$done = false;
				} else {
					$playerIds[] = $data['playerId'];
				}
			}
			if ($done) {
				$sql = "UPDATE game SET status = 'ACTIVE' WHERE id = $gameId";
				$result = $conn->query($sql);
				if (!$result) {
					echo "updating game status active failed " . $conn->error;
				}
				
				$questionSql = "SELECT id FROM questions " . 
						"INNER JOIN questionToCategory ON questionToCategory.questionId = questions.id " .
						"WHERE questionToCategory.categoryId IN (SELECT categoryId FROM `gameCategories` where gameId = $gameId) " . 
						"ORDER BY RAND() " . 
						"LIMIT 1";
				$questionIdResult = $conn->query($questionSql);
				$data = $questionIdResult->fetch_assoc();
				$values = "";
				foreach ($playerIds AS $playerId) {
					$values .= "($gameId, $playerId, " . $data['id'] . "),";
				}
				$values = rtrim($values, ",");
				$insertRandomStartQuestion = "INSERT INTO usersLockedQuestions (gameId, playerId, questionId) VALUES $values";
				$result = $conn->query($insertRandomStartQuestion);
				if (!$result) {
					echo "Something wrong with inserting starting question error: " . $conn->error;
				}
			}
		}
	} else if ($callFunction === "SetLastPlayed") {
		$sql = "UPDATE game SET lastPlayedDate = NOW() WHERE id = $gameId";
		$result = $conn->query($sql);
		if (!$result) {
			echo "Updating lastPlayedDate failed " . $conn->error; 
		}
	} else if ($callFunction === "SetQuestionsLost") {
		$questionsLost = $_POST['questionsLost'];
		$sql = "UPDATE gamePlayers SET questionsLost = $questionsLost WHERE gameId = $gameId AND playerId = $userId";
		$result = $conn->query($sql);
		if (!$result) {
			echo "Updating questionsLost resulted in error: " . $conn->error;
		}
	} else if ($callFunction === "DeleteGame") {
		$sql = "DELETE FROM game WHERE id = $gameId";
		$result = $conn->query($sql);
		if (!$result) {
			echo "Failed to delete game with id $gameId - error: " . $conn->error;
		}
		
		$sql = "DELETE FROM gamePlayers WHERE gameId = $gameId";
		$result = $conn->query($sql);
		if (!$result) {
			echo "Failed to delete gamePlayers with id $gameId - error: " . $conn->error;
		}	
	} else if ($callFunction === "CurrentPlayer") {
		$sql = "SELECT username from users WHERE id = (SELECT currentPlayer FROM game WHERE id = $gameId)";
		$result = $conn->query($sql);
		if (!$result) {
			echo "Failed to get current player for game with id $gameId";
		} else {
			if ($result->num_rows > 0) {
				$returnArray = array();
				$i = 0;
				while ($data = $result->fetch_assoc()) {
					$currentPlayer = $data['username'];
				}
				echo $currentPlayer;
			}
		}
		
	} else if ($callFunction === "SetCurrentPlayer") {
		$currentPlayer = $_POST['userName'];
		$sql = "UPDATE game SET currentPlayer = $userId WHERE id = $gameId";
		$result = $conn->query($sql);
		if (!$result) {
			echo "Failed to update current player for game with id $gameId - error: " . $conn->error;
		}		
	} else if ($callFunction === "GetPlayerPoints") {
		$sql = "SELECT userLockedQuestions FROM game WHERE gameId = $gameId AND playerId = $userId";
		$result = $conn->query($sql);
		$data = $result->fetch_assoc();
		
		echo $data['userLockedQuestions'];
	} else if ($callFunction === "SetFinishedDate") {
		$finishedDate = $_POST['finishedDate'];
		$sql = "UPDATE game SET finishedDate = '$finishedDate' WHERE id = $gameId";
		$result = $conn->query($sql);
		if (!$result) {
			echo "Failed to update current player for game with id $gameId - error: " . $conn->error;
		}
	} else if ($callFunction === "SetRound") {
		$round = $_POST['round'];
		$sql = "UPDATE game SET round = '$round' WHERE id = $gameId";
		$result = $conn->query($sql);
		if (!$result) {
			echo "Failed to update current player for game with id $gameId - error: " . $conn->error;
		}
	} else if ($callFunction === "GetRound") {
		$sql = "SELECT round FROM game WHERE id = $gameId";
		$result = $conn->query($sql);
		$data = $result->fetch_assoc();
		
		echo $data['round'];
	} else if ($callFunction === "GetPlayers") {
		$sql = "SELECT username, userLockedQuestions, playerRound FROM gamePlayers INNER JOIN users ON users.id = gamePlayers.playerId WHERE gameId = $gameId";
		$result = $conn->query($sql);
		$returnArray = array();
		$i = 0;
		while ($data = $result->fetch_assoc()) {
			foreach ($data as $key => $value) {
				$returnArray[$i][$key] = $value;
			}
			$i++;
		}
		echo json_encode($returnArray);
	} else if ($callFunction === "GetQuestionsLost") {
		$userName = $conn->real_escape_string($_POST['userName']);
		$sql = "SELECT questionsLost FROM gamePlayers WHERE gameId = $gameId AND playerId = $userId";
		$result = $conn->query($sql);
		$data = $result->fetch_assoc();
		
		echo $data['questionsLost'];
	} else if ($callFunction === "GetWinCondition") {
		$sql = "SELECT winNumber FROM game WHERE id = $gameId";
		$result = $conn->query($sql);
		$data = $result->fetch_assoc();
		 
		echo $data['winNumber'];
	} else if ($callFunction === "SavePlayerQuestions") {
		$userName = $conn->real_escape_string($_POST['userName']);
		
		$questionIds = $conn->real_escape_string($_POST['questionsToSave']);
		$questionIdArray = explode(",",$questionIds);
		$values = "";
		foreach($questionIdArray as $qId) {
			$values .= "($userId, $qId, $gameId),";
		}
		$values = rtrim($values, ',');
		$sql = "INSERT INTO usersLockedQuestions (playerId, questionId, gameId) VALUES $values ON DUPLICATE KEY UPDATE playerId = '$userId', gameId = $gameId";
		$result = $conn->query($sql);
		if (!$result) {
			echo "ERROR while svaing player questions for game $gameId and player $userName, id $userId with error " . $conn->error . " from sql $sql";
		}
		
		$sql = "UPDATE gamePlayers SET userLockedQuestions = (SELECT count(*) FROM usersLockedQuestions WHERE gameId = $gameId AND playerId = '$userId') WHERE playerId = (SELECT id FROM users WHERE username = '$userName' AND gameId = $gameId)";
		$conn->query($sql);
	} else if ($callFunction === "UpdatePlayerToken") {
		
		$messageToken = $_POST['token'];
		$userId = $_POST['userId'];
		$updateTokenSql = "UPDATE users SET messageToken = '$messageToken' WHERE id = " . $userId;
		$conn->query($updateTokenSql);
	} else if ($callFunction === "GameFinished") {
		$setFinishedStatusSql = "UPDATE game SET status = 'FINISHED' WHERE id = $gameId";

		$conn->query($setFinishedStatusSql);
	}
	
	
	$conn->close();
	
?>