easy-osm2city-podman/managment/www/api.php

<?php
session_start();
include("config.php");
$API_VERSION = 0.2;

function quit()
{
	$ret = new stdClass;
	$ret->success = False;
	$ret->version = $GLOBALS["API_VERSION"];
	echo(json_encode($ret));
	exit();
}

$ret = new stdClass;
$ret->success = True;
$ret->version = $API_VERSION;

// Make sure we've got at least auth and an action in the request
if (((isset($_POST['auth']) && $_POST['auth'] != "") || (isset($_SESSION['token']) && $_SESSION['token'] != "")) && isset($_POST['action']) && $_POST['action'] != "")
{
	if(isset($_POST['auth']))
	{
		$auth = $_POST['auth'];
	}
	else
	{
		$auth = $_SESSION['token'];
	}
	$action = $_POST['action'];
}
else
{
	quit();
}

// Connect to DB
$con = new mysqli($SQL_SERVER, $SQL_USER, $SQL_PASSWORD, $SQL_DATABASE, $SQL_PORT);
if ($con->connect_error)
{
	quit();
}

// Check auth
$stmt = $con->prepare("SELECT id FROM auth WHERE token = ?");
$stmt->bind_param("s", $auth);
$stmt->execute();
$result = $stmt->get_result();
$res = $result->fetch_assoc();
$stmt->close();
if ($res == Null)
{
	quit();
}

$_SESSION['token'] = $auth;

// Setup helper functions
function get_lock($con)
{
	$sql = "SELECT GET_LOCK('tile-status-lock', 10)";
	$ret = $con->query($sql)->fetch_array()[0];
	if ($ret == 0)
	{
		quit();
	}
}

function rel_lock($con)
{
	$con->query("SELECT RELEASE_LOCK('tile-status-lock')");
}

function set_status($con, $name, $status, $type)
{
	if ($type == "area")
	{
		// Get status ID
		$sql = "SELECT id FROM status WHERE name = ?";
		$stmt = $con->prepare($sql);
		$stmt->bind_param("s", $status);
		$stmt->execute();
		$result = $stmt->get_result();
		$stmt->close();
		if ($result == False)
		{
			rel_lock($con);
			quit();
		}
		$sid = $result->fetch_array()[0];

		$sql = 'SELECT * FROM information_schema.tables WHERE table_name = "tile" AND table_schema = "' . $SQL_DATABASE . '"';
		$ret = $con->query($sql)->fetch_all(MYSQLI_ASSOC);
		if (!empty($ret))
		{
			// Get parent ID
			$sql = "SELECT id FROM secondLevel WHERE name = ?";
			$stmt = $con->prepare($sql);
			$stmt->bind_param("s", $name);
			$stmt->execute();
			$result = $stmt->get_result();
			$stmt->close();
			if ($result == False)
			{
				rel_lock($con);
				quit();
			}
			$pid = $result->fetch_array()[0];

			// Update tiles in area
			$sql = "UPDATE tile SET status_id = ? WHERE parent_id = ?";
			$stmt = $con->prepare($sql);
			$stmt->bind_param("ii", $sid, $pid);
			$stmt->execute();
			$stmt->close();
		}
		else
		{
			$sql = "UPDATE secondLevel SET status_id = ? WHERE name = ?";
			$stmt = $con->prepare($sql);
			$stmt->bind_param("is", $sid, $name);
			$stmt->execute();
			$stmt->close();
		}
	}
	else
	{
		$sql = "UPDATE tile SET status_id = (SELECT id FROM status WHERE name = ?) WHERE id = ?";
		$stmt = $con->prepare($sql);
		$stmt->bind_param("si", $status, $name);
		$stmt->execute();
		$stmt->close();
	}
}

if ($action == "set")
{
	if (isset($_POST['tile']) && $_POST['tile'] != "")
	{
		get_lock($con);
		set_status($con, $_POST['tile'], $_POST['status'], "tile");
		rel_lock($con);
	}
	else if (isset($_POST['area']) && $_POST['area'] != "")
	{
		get_lock($con);
		set_status($con, $_POST['area'], $_POST['status'], "area");
		rel_lock($con);
	}
	else
	{
		quit();
	}
}
else if ($action == "get-job" && isset($_POST["status"]) && $_POST["status"] != "" && isset($_POST["new-status"]) && $_POST["new-status"] != "")
{
	if (isset($_POST["level"]) && ($_POST["level"] == "tile" || $_POST["level"] == "area"))
	{
		$level = $_POST["level"];
		$table = "secondLevel";
		$field = "name";
	}
	else
	{
		$level = "tile";
		$table = "tile";
		$field = "id";
	}


	get_lock($con);
	$sql = "SELECT " . $field . ", parent_id FROM " . $table . " WHERE status_id = (SELECT id FROM status WHERE name = ?) ORDER BY parent_id LIMIT 1";
	$stmt = $con->prepare($sql);
	$stmt->bind_param("s", $_POST["status"]);
	$stmt->execute();
	$result = $stmt->get_result();
	$stmt->close();
	if ($result == False)
	{
		quit();
	}
	$res = $result->fetch_array(); // TODO check
	$ret->job = $res[$field];
	if ($ret->job == Null)
	{
		$ret->job = "None";
	}
	else
	{
		set_status($con, $ret->job, $_POST["new-status"], $level);
		if (isset($_POST["all-in-parent"]) && $_POST["all-in-parent"] == 1)
		{
			$ret->jobs = [];
			$ret->jobs[] = $ret->job;
			$sql = "SELECT id FROM " . $table . " WHERE parent_id = ? AND status_id = (SELECT id FROM status WHERE name = ?)";
			$stmt = $con->prepare($sql);
			$stmt->bind_param("ss", $res["parent_id"], $_POST["status"]);
			$stmt->execute();
			$result = $stmt->get_result();
			$stmt->close();
			if ($result != False)
			{
				while ($row = $result->fetch_array())
				{
					set_status($con, $row["id"], $_POST["new-status"], $level);
					$ret->jobs[] = $row["id"];
				}
			}
		}
	}
	rel_lock($con);
}
else if ($action == "get-options")
{
	if (isset($_POST['tile']) && $_POST['tile'] != "")
	{
		$sql = "SELECT tile_options.priority, option FROM tile_options INNER JOIN options ON tile_options.option_id = options.id where tile_id = (SELECT id FROM tile WHERE name = ?) ORDER BY tile_options.priority";
		$pattern = "i";
		$req = $_POST['tile'];
	}
	else if (isset($_POST['area']) && $_POST['area'] != "")
	{
		$sql = "SELECT secondLevel_options.priority, option FROM secondLevel_options INNER JOIN options ON secondLevel_options.option_id = options.id where tile_id = (SELECT id FROM secondLevel WHERE name = ?) ORDER BY secondLevel_options.priority";
		$pattern = "s";
		$req = $_POST['area'];
	}
	else
	{
		quit();
	}
	$stmt = $con->prepare($sql);
	$stmt->bind_param($pattern, $req);
	$stmt->execute();
	$result = $stmt->get_result();
	if ($result == False)
	{
		quit();
	}
	$stmt->close();
	if ($res == Null)
	{
		quit();
	}
	$ret->options = [];
	if ($result != False)
	{
		while ($row = $result->fetch_array())
		{
			$ret->options[] = $row["option"];
		}
	}
}
else if ($action == "status")
{
	if (isset($_POST['tile']) && $_POST['tile'] != "")
	{
		$sql = "SELECT name FROM status WHERE id = (SELECT status_id FROM tile WHERE id = ?)";
		$pattern = "i";
		$req = $_POST['tile'];
	}
	else if (isset($_POST['area']) && $_POST['area'] != "")
	{
		$sql = "SELECT name FROM status WHERE id = (SELECT status_id FROM secondLevel WHERE name = ?)";
		$pattern = "s";
		$req = $_POST['area'];
	}
	else
	{
		quit();
	}
	$stmt = $con->prepare($sql);
	$stmt->bind_param($pattern, $req);
	$stmt->execute();
	$result = $stmt->get_result();
	if ($result == False)
	{
		quit();
	}
	$res = $result->fetch_assoc();
	$stmt->close();
	if ($res == Null)
	{
		quit();
	}
	$ret->status = $res["name"];
}
else
{
	quit();
}

// https://www.w3schools.com/php/php_mysql_prepared_statements.asp

$con->close();
echo(json_encode($ret));
?>