NarKampen/dbFiles/OnlineGames.php

<?php
/* Games.php */
	$hostname = 'nordh.xyz';
	$username = 'narKampen';
	$pass = '9Bq.6[AcTc2ADwN-';
	$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 User where username = '$userName'")->fetch_assoc()['id'];

	if ($callFunction === "list"){
		$sql = "SELECT g.*, u.id as userId, u.username as username, gp.userLockedQuestions, gp.status playerStatus, gp.playerRound FROM Game g
		INNER JOIN GamePlayers gp ON gp.gameId = g.id
		INNER JOIN User u ON u.id = playerId
		WHERE g.id IN (SELECT gameId FROM gp WHERE playerId = $userId)
		AND g.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 User 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 q " .
						"INNER JOIN QuestionToCategory qtc ON qtc.questionId = q.id " .
						"WHERE qtc.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 User 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 === "SetPlayerRound") {
		$player = $_POST['player'];
		$sql = "UPDATE GamePlayers SET playerRound = playerRound + 1 WHERE playerId = (Select id FROM User WHERE username = '" . $player . "' AND gameId = $gameId";
		$conn->query($sql);
	} 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 gp INNER JOIN User ON users.id = gp.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 User WHERE username = '$userName' AND gameId = $gameId)";
		$conn->query($sql);
	} else if ($callFunction === "UpdatePlayerToken") {

		$messageToken = $_POST['token'];
		$userId = $_POST['userId'];
		$updateTokenSql = "UPDATE User 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);
	} else if ($callFunction === "IncreasePlayerRound") {
		$playerName = $_POST['player'];

		$sql = "UPDATE GamePlayers SET playerRound = playerRound + 1 WHERE gameId = $gameId AND playerId = (SELECT id FROM User WHERE username = '$playerName'";
		$conn->query($sql);
	}


	$conn->close();

?>