function addSchoolCoordinates()
{
global $db;
$onlyComplete = true;
// set false to replace all (about 25 minutes in total)
$execRequests = true;
// set false for debugging
$query = "SELECT `school`.* FROM `school` WHERE 1";
if ($onlyComplete) {
$query .= " AND `school`.coords = '0,0,0'";
}
// for step by step
// $query .= " LIMIT 5";
$stmt = $db->prepare($query);
$stmt->execute();
$all = array();
while ($row = $stmt->fetchObject()) {
$all[] = $row;
}
echo "Number of queries to be made: " . count($all) . "\n<br>";
flush();
ob_flush();
foreach ($all as $id => $row) {
list($lat, $lng, $msg) = getCoordinatesSchool((array) $row);
$coords = "{$lng},{$lat},0";
if ($execRequests) {
$query = "UPDATE school SET coords = :coords, saniMsg = CONCAT(saniMsg, :msg) WHERE ID = :ID";
$stmt = $db->prepare($query);
$stmt->execute(array(':ID' => $row->ID, ':coords' => $coords, ':msg' => $msg));
}
sleep(1);
echo "Completed query: " . ($id + 1) . " for ID=" . $row->ID . " [" . $msg . "]<br>";
flush();
ob_flush();
}
}
}
$query = "SELECT `school`.ID, `school`.name FROM `school` ORDER BY name ASC";
$stmt = $db->prepare($query);
$stmt->execute();
$all = array();
while ($row = $stmt->fetchObject()) {
$all[] = $row;
}
output(array("success" => true, "schools" => $all));
}
if ($_GET["action"] == "computeCoordinates") {
if (!$_SESSION["isAdmin"]) {
error();
}
$query = "SELECT `school`.* FROM `school` WHERE ID = :schoolID ORDER BY name ASC";
$stmt = $db->prepare($query);
$stmt->execute(array("schoolID" => $_GET["schoolID"]));
$row = $stmt->fetchObject();
if (is_null($row)) {
output(array("success" => true, "msg" => "No school " . $_GET["schoolID"]));
}
list($lat, $lng, $msg) = getCoordinatesSchool((array) $row);
$coords = "{$lng},{$lat},0";
$query = "UPDATE school SET coords = :coords, saniMsg = CONCAT(saniMsg, :msg) WHERE ID = :ID";
$stmt = $db->prepare($query);
$stmt->execute(array(':ID' => $row->ID, ':coords' => $coords, ':msg' => $msg));
output(array("success" => true, "msg" => "Coordinates computed for " . $_GET["schoolID"]));
}
error();
?>
function checkRequestSchool($db, &$request, &$record, $operation, &$roles)
{
// Generated fields
list($record["name"], $record["city"], $record["country"], $record["saniValid"], $record["saniMsg"]) = DataSanitizer::formatSchool($record["name"], $record["city"], $record["country"]);
$roles[] = "generator";
list($lat, $lng, $msg) = getCoordinatesSchool($record);
$record["saniMsg"] .= $msg;
$record['coords'] = $lng . "," . $lat . ",0";
if (!$_SESSION["isAdmin"] || $operation === "insert") {
$record["userID"] = $_SESSION["userID"];
}
// Filters
if (!$_SESSION["isAdmin"]) {
$request["filters"]["accessUserID"] = array('values' => array('userID' => $_SESSION["userID"]));
$request["filters"]["userID"] = $_SESSION["userID"];
}
return true;
}
