NarKampen/dbFiles/OnlineGames.php

<?php
/* Games.php */
<<<<<<< Updated upstream
	$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();

?>
=======
$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') {
    $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 = [];
        $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';
    }
} elseif ($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;
                    }
                }
            }
        }
    }
} elseif ($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 = [];
        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;
            }
        }
    }
} elseif ($callFunction === 'SetLastPlayed') {
    $sql = "UPDATE game SET lastPlayedDate = NOW() WHERE id = $gameId";
    $result = $conn->query($sql);
    if (!$result) {
        echo 'Updating lastPlayedDate failed ' . $conn->error;
    }
} elseif ($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;
    }
} elseif ($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;
    }
} elseif ($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 = [];
            $i = 0;
            while ($data = $result->fetch_assoc()) {
                $currentPlayer = $data['username'];
            }
            echo $currentPlayer;
        }
    }
} elseif ($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;
    }
} elseif ($callFunction === 'GetPlayerPoints') {
    $sql = "SELECT userLockedQuestions FROM game WHERE gameId = $gameId AND playerId = $userId";
    $result = $conn->query($sql);
    $data = $result->fetch_assoc();

    echo $data['userLockedQuestions'];
} elseif ($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;
    }
} elseif ($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;
    }
} elseif ($callFunction === 'SetPlayerRound') {
    $player = $_POST['player'];
    $sql =
        "UPDATE gamePlayers SET playerRound = playerRound + 1 WHERE playerId = (Select id FROM users WHERE username = '" .
        $player .
        "' AND gameId = $gameId";
    $conn->query($sql);
} elseif ($callFunction === 'GetRound') {
    $sql = "SELECT round FROM game WHERE id = $gameId";
    $result = $conn->query($sql);
    $data = $result->fetch_assoc();

    echo $data['round'];
} elseif ($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 = [];
    $i = 0;
    while ($data = $result->fetch_assoc()) {
        foreach ($data as $key => $value) {
            $returnArray[$i][$key] = $value;
        }
        $i++;
    }
    echo json_encode($returnArray);
} elseif ($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'];
} elseif ($callFunction === 'GetWinCondition') {
    $sql = "SELECT winNumber FROM game WHERE id = $gameId";
    $result = $conn->query($sql);
    $data = $result->fetch_assoc();

    echo $data['winNumber'];
} elseif ($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);
} elseif ($callFunction === 'UpdatePlayerToken') {
    $messageToken = $_POST['token'];
    $userId = $_POST['userId'];
    $updateTokenSql =
        "UPDATE users SET messageToken = '$messageToken' WHERE id = " . $userId;
    $conn->query($updateTokenSql);
} elseif ($callFunction === 'GameFinished') {
    $setFinishedStatusSql = "UPDATE game SET status = 'FINISHED' WHERE id = $gameId";

    $conn->query($setFinishedStatusSql);
} elseif ($callFunction === 'IncreasePlayerRound') {
    $playerName = $_POST['player'];

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

$conn->close();

?>
>>>>>>> Stashed changes