se/SE/se-lib/Route/GeoreferencesManager.php

<?php

Lib::loadClass('RouteBase');
Lib::loadClass('EpsgConversion');
Lib::loadClass('RawSql');

class Route_GeoreferencesManager extends RouteBase {

	protected $ACTION, $REFERER;
	const TABLE = "WMS_MAP_GEOREFERENCES";

	public function handleAuth() {
		if (!User::logged()) {
			User::authByRequest();
		}
		$this->REFERER = $_SERVER['HTTP_REFERER'];
		$this->ACTION = V::get('action', '', $_GET, '');
	}

	public function defaultAction() {
		SE_Layout::gora();
		SE_Layout::menu();
?>
<div class="container" style="margin-top:20px">
  <legend>Zarządzanie punktami georeferencyjnymi</legend>
  <div class="form-group">
    <div class="col-sm-12">
      <a href="?_route=GeoreferencesManager&action=verifyPoints" class="btn-sm btn-primary">Zweryfikuj punkty</a>
      <a href="?_route=GeoreferencesManager&action=uploadPoints" class="btn-sm btn-primary">Wgraj punkty</a><?=($this->ACTION ? "<hr/>" : "")?>
    </div>
    <div class="col-sm-12">
<?php
		switch ($this->ACTION) {
			case "verifyPoints":
				$this->verifyPoints();
				break;
			case "repairPoints":
				$this->repairPoints();
				break;
			case "uploadPoints":
				$this->uploadPoints();
				break;
		}
?>
    </div>
  </div>
</div>
<?php
		SE_Layout::dol();
	}

	public function reinstallAction() {
		$this->reinstall();
		die('OK');
	}

	public function reinstall() {
	}

	private static function getBadPoints() {
		$points = DB::getPDO()->fetchall("select `ID`, `EPSG`, x(`the_geom`) as `gx`, y(`the_geom`) as `gy`, `x`, `y` from `" . self::TABLE . "`");
		$errors = [];
		foreach ($points as $point) {
			if ($point['x'] > 0 && $point['y'] > 0) {
				if ($point['gx'] && $point['gy']) {	
					try {
						$test = epsgConversion::LonLatToPUWGWGS84($point['gx'], $point['gy']);
						if (abs($point['x'] - $test->x) > 0.001) $errors["unfuckable"][$point["ID"]]["errors"][] = "x";
						if (abs($point['y'] - $test->y) > 0.001) $errors["unfuckable"][$point["ID"]]["errors"][] = "y";
						if ($point['EPSG'] != $test->epsg) $errors["unfuckable"][$point["ID"]]["errors"][] = "epsg";
						if (isset($errors["unfuckable"][$point["ID"]])) $errors["unfuckable"][$point["ID"]]["data"] = $point;
					} catch (Exception $e) {
						$errors["nonunfuckable"][$point["ID"]]["data"] = $point;
					}
				} else $errors["nonunfuckable"][$point["ID"]]["data"] = $point;
			} elseif (!($point['gx'] && $point['gy'])) $errors["nonunfuckable"][$point["ID"]]["data"] = $point;
		}
		return $errors;
	}

	private function verifyPoints() {
		$errorsData = self::getBadPoints();

		if (isset($errorsData["unfuckable"])) {
			$echo = '<b>Znalezione błędy, które można automatycznie naprawić:</b></br/><pre>';
			foreach ($errorsData["unfuckable"] as $error) {
				$ok = epsgConversion::PUWGToLonLatWGS84($error['data']['x'], $error['data']['y']);
				$echo .= "[{$error['data']['ID']}] x = <span style='color:" . ((in_array("x", $error["errors"])) ? "red" : "green") .
					";'>{$error['data']['gx']}</span> ({$ok->x}), y = <span style='color:" . ((in_array("y", $error["errors"])) ? "red" : "green") .
					";'>{$error['data']['gy']}</span> ({$ok->y}), epsg = <span style='color:" . ((in_array("epsg", $error["errors"])) ? "red" : "green") .
					";'>{$error['data']['EPSG']}</span>" . ((in_array("epsg", $error["errors"])) ? " ({$ok->epsg})" : "") . "<br/>";
			}
			$echo .= '</pre><div style="text-align:center;"><a href="?_route=GeoreferencesManager&action=repairPoints" class="btn btn-primary">Napraw wszystkie</a></div>';
			SE_Layout::alert('warning', $echo);
		}

		if (isset($errorsData["nonunfuckable"])) {
			$echo = '<b>Znalezione błędy, których nie można automatycznie naprawić:</b></br/><pre>';
			foreach ($errorsData["nonunfuckable"] as $error) {
				$echo .= "[{$error['data']['ID']}] the_geom = " . ((!($error['data']['gx'] || $error['data']['gy'])) ? "NULL" : "POINT({$error['data']['gx']} {$error['data']['gy']})") .
					", x = {$error['data']['x']}, y = {$error['data']['y']}, epsg = {$error['data']['EPSG']}<br/>";
			}
			$echo .= '</pre>';
			SE_Layout::alert('danger', $echo);
		}

		if (!$errorsData) {
			SE_Layout::alert('success', "Wszystko OK");
		}
	}

	private function repairPoints() {
		$errorsData = self::getBadPoints()["unfuckable"];
		$updateErrors = [];
		$updateSuccess = 0;
		foreach ($errorsData as $ID => $error) {
			$ok = epsgConversion::PUWGToLonLatWGS84($error['data']['x'], $error['data']['y']);
			$sqlArr = [
				"ID" => $ID,
				"the_geom" => "GeomFromText('POINT({$ok->x} {$ok->y})')",
				"EPSG" => $ok->epsg
			];
			try {
				if (DB::getDB()->UPDATE_OBJ(self::TABLE, $sqlArr) < 1) $updateErrors[] = $ID;
				else $updateSuccess++;
			} catch (Exception $e) {
				$updateErrors[] = $ID;
			}
		}
		$echo = "Zaktualizowano {$updateSuccess} z " . count($errorsData) . " rekordów.";
		if ($updateErrors) {
			$echo .= "<br/>Nie zaktualizowano rekordów ID: " . implode(", ", $updateErrors);
			SE_Layout::alert('danger', $echo);
		} else SE_Layout::alert('success', $echo);
	}

	private function uploadPoints() {
		$subActions = ["uploadPointsConfirm", "uploadPointsSave"];
		if (!in_array($subAction = V::get('subAction','',$_POST), $subActions)) $subAction = "uploadPointsForm";
		$this->$subAction();
	}

	private function uploadPointsConfirm() {
		try {
			$maxDistance = V::get('maxDistance','ERROR',$_POST);
			if (!is_numeric($maxDistance)) throw new Exception("Błąd formularza #1");
			if (!isset($_FILES['file'])) throw new Exception("Błąd formularza #2");
			if (!file_exists($_FILES['file']['tmp_name'])) throw new Exception("Wystąpił problem z przesłaniem pliku");
			$fileTypes = [
				'text/csv',
				'application/vnd.ms-excel',
			];
			if (!in_array($_FILES['file']['type'], $fileTypes)) throw new Exception("Błędny typ pliku - {$_FILES['file']['type']}");
			$file = file($_FILES['file']['tmp_name']);

			$points = [];
			foreach ($file as $line) {
				if (!preg_match('/^[[:alnum:]]+,([[:digit:]\.]+,){3}[[:alnum:] ]+$/', trim($line))) throw new Exception("Plik zawiera niepoprawne dane #1 - {$line}");
				list($lp, $y, $x, $z, $type) = explode(',', $line);
				if (!($lp && $x && $y && $z && $type)) throw new Exception("Plik zawiera niepoprawne dane #2");
				if (trim($type) == "Pikieta") {
					foreach ($points as $key => $point) {
						if (sqrt((pow($point['x'] - $x, 2) + pow($point['y'] - $y, 2)) <= $maxDistance)) {
							$lp = $key;
							break;
						}
					}
					$points[$lp] = ['x' => $x, 'y' => $y, 'z' => $z];
				}
			}

			if (!$points) throw new Exception("Plik zawiera niepoprawne dane");

			$tempTbl = self::TABLE . "_temp";
			DB::getPDO()->query("CREATE TEMPORARY TABLE `{$tempTbl}` (SELECT * FROM `" . self::TABLE . "`)");
			$result = DB::getPDO()->fetchall("SELECT ID, x(the_geom) AS gx, y(the_geom) AS gy FROM `{$tempTbl}` WHERE ST_IsEmpty(the_geom) = 0 AND x = 0 AND y = 0");
			foreach ($result as $row) {
				$puwg = epsgConversion::LonLatToPUWGWGS84($row['gx'], $row['gy']);
				DB::getPDO()->query("UPDATE `{$tempTbl}` SET x = '{$puwg->x}', y = '{$puwg->y}' WHERE ID='{$row['ID']}'");
			}

			$duplicates = [];
			$closePoints = [];
			foreach ($points as $lp => $point) {
				$result = DB::getPDO()->fetchall("SELECT ID, SQRT(POW('{$point['x']}' - x, 2) + POW('{$point['y']}' - y, 2)) as distance, z FROM `{$tempTbl}` ORDER BY distance LIMIT 1");
				if ($result) {
					if (in_array($result[0]['ID'], $closePoints)) $duplicates[$lp] = array_search($result[0]['ID'], $closePoints);
					($duplicates[$lp] = array_search($result[0]['ID'], $closePoints)) ?: $closePointsDetail[$lp] = $result[0];
					$closePoints[$lp] = $result[0]['ID'];
				}
				else $closePointsDetail[$lp] = false;
				$wgs84[$lp] = epsgConversion::PUWGToLonLatWGS84($point['x'], $point['y']);
			}
?>
    <style>
      <!--
      .table > tbody > tr > td {vertical-align: middle};
      -->
    </style>
    <form class="form-horizontal" method="post" enctype="multipart/form-data">
      <div class="form-group">
        <div class="col-sm-12">
          <table class="table table-bordered table-hover table-striped">
            <thead>
              <tr style="text-align:center; background-color:lightgray"><td>Lp.</td><td>X</td><td>Y</td><td>Z (m)</td><td>EPSG</td><td>Znaleziony najbliższy punkt (w odległości w metrach)</td><td>Działanie</td></tr>
            </thead>
            <tbody>
<?php
			foreach ($points as $lp => $point) {
				$disabled = false;
				$checked = true;
				echo "<tr><td nowrap align='right'>" . $lp . "</td><td nowrap>{$point['x']} ({$wgs84[$lp]->x})</td><td nowrap>{$point['y']} ({$wgs84[$lp]->y})</td><td nowrap>{$point['z']}</td><td nowrap align='center'>{$wgs84[$lp]->epsg}</td><td nowrap>";
				if ($closePointsDetail[$lp]) {
					if ($closePointsDetail[$lp]['distance'] == 0) {
						if ($closePointsDetail[$lp]['z'] > 0) {
							if ($closePointsDetail[$lp]['z'] == $point['z']) {
								echo "ID:{$closePoints[$lp]} - to ten sam punkt, nie ma co aktualiować";
								$disabled = true;
							} else echo "ID:{$closePoints[$lp]} - to ten sam punkt, ale z inną wartością Z -<br/>należy zaktualizować";
						} else echo "ID:{$closePoints[$lp]} - to ten sam punkt, ale wcześniej nie został zweryfikowany -<br/>należy zaktualiować";
					} elseif ($closePointsDetail[$lp]['distance'] <= $maxDistance) echo "ID:{$closePoints[$lp]} ({$closePointsDetail[$lp]['distance']}) - należy zaktualizować";
					else {
						echo "ID:{$closePoints[$lp]} ({$closePointsDetail[$lp]['distance']}) - za daleko, zdecyduj co zrobić";
						$checked = false;
					}
				} elseif (isset($duplicates[$lp])) {
					echo "ID:{$closePoints[$lp]} - ten sam punkt został znaleziony dla punktu Lp. {$duplicates[$lp]} -<br/>nie można zaktualizować z powodu konfliktu<br/>(zalecia się ponowne wgranie tego samego pliku po dodaniu pozostałych punktów)";
					$disabled = true;
				} else echo "Nie znaleziono żadnego punktu - należy dodać nowy";
				echo "</td><td nowrap>";
				if ($closePointsDetail[$lp]['distance'] <= $maxDistance) echo "<div class='checkbox'><label><input type='checkbox' name='points[{$lp}]'" .
					($disabled ? " disabled" : ($checked ? " checked" : "")) . "/>" . ($closePoints[$lp] ? "Zaktualizuj" : "Dodaj nowy") . "</label></div>";
				else {
?>
<div class='radio'><label><input type='radio' name='points[<?=$lp?>]' value='new' checked/>Dodaj nowy</label></div>
<div class='radio'><label><input type='radio' name='points[<?=$lp?>]' value='on'/>Zaktualizuj</label></div>
<div class='radio'><label><input type='radio' name='points[<?=$lp?>]' value='off'/>Zignoruj</label></div>
<?php
				}
				echo "</td></tr>";
			}
?>
            </tbody>
          </table>
          <div style="text-align:center;"><button type="submit" class="btn btn-primary" name="subAction" value="uploadPointsSave">Zapisz</button></div>
        </div>
      </div>
    </form>
<?php
			$_SESSION['uploadPointsData'] = gzcompress(json_encode(['points' => $points, 'closePoints' => $closePoints]));

		} catch (Exception $e) {
			SE_Layout::alert('danger', $e->getMessage());
			$this->uploadPointsForm();
		}
	}

	private function uploadPointsSave() {
		try {
			if (!isset($_SESSION['uploadPointsData'])) throw new Exception("Błąd danych #1");
			$data = json_decode(gzuncompress($_SESSION['uploadPointsData']), true);
			if (!$data) throw new Exception("Błąd danych #2");
			$points = V::get('points','',$_POST);
			if (!is_array($points)) throw new Exception("Błąd danych #3");
			$points = array_diff($points, ["off"]);
			if (!$points) throw new Exception("Nie zdefiniowany żadnych punktów do aktualizacji/dodania");
			foreach ($points as $lp => $action) {
				$x = $data['points'][$lp]['x'];
				$y = $data['points'][$lp]['y'];
				$z = $data['points'][$lp]['z'];
				try {
					$wgs84 = epsgConversion::PUWGToLonLatWGS84($x, $y);
					$gx = $wgs84->x;
					$gy = $wgs84->y;
					$epsg = $wgs84->epsg;
					$the_geom = "GeomFromText('POINT({$gx} {$gy})')";
					$sqlArrs[$lp] = [
						"A_STATUS" => "NORMAL",
						"A_STATUS_INFO" => 'Punkt uzgodniony automatycznie @ ' . date('Y-m-d'),
						"the_geom" => $the_geom,
						"x" => $x,
						"y" => $y,
						"z" => $z,
						"EPSG" => $epsg
					];
					if ($action == "on") {
						if (!isset($data['closePoints'][$lp])) throw new Exception("Błąd danych #3");
						if ($curA_STATUS_INFO = DB::getPDO()->fetchValue("SELECT A_STATUS_INFO FROM " . self::TABLE . " WHERE ID = '{$data['closePoints'][$lp]}'"))
							$sqlArrs[$lp]['A_STATUS_INFO'] .= "; {$curA_STATUS_INFO}";
						$sqlArrs[$lp]['ID'] = $data['closePoints'][$lp];
					} elseif ($action != "new") throw new Exception("Błąd danych #4");
				} catch (Exception $e) {
					SE_Layout::alert('danger'," Wystąpił problem (#1) z dodaniem punktu Lp. {$lp} - {$e->getMessage()}");
				}
			}

			$i = 0;
			foreach ($sqlArrs as $lp => $sqlArr) {
				try {
					if (isset($sqlArr['ID'])) $affected = DB::getDB()->UPDATE_OBJ(self::TABLE, $sqlArr);
					else $affected = DB::getDB()->ADD_NEW_OBJ(self::TABLE, $sqlArr);
					if ($affected < 1) throw new Exception("Błąd bazy danych: " . print_r($sqlArr, true));
					$i++;
				} catch (Exception $e) {
					SE_Layout::alert('danger', "Wystąpił problem (#2) z dodaniem punktu Lp. {$lp} - {$e->getMessage()}");
				}
				
			}

			if ($i > 0) SE_Layout::alert(($i == count($sqlArrs) ? "success" : "warning"), "Pomyślnie dodano/zaktualizowano {$i} z " . count($sqlArrs) . " punktów");
			else SE_Layout::alert('danger', "Nie dodano/zaktualiowano żadnego punktu");
		} catch (Exception $e) {
			SE_Layout::alert('danger', $e->getMessage());
			$this->uploadPointsForm();
		}
		if (isset($_SESSION['uploadPointsData'])) unset($_SESSION['uploadPointsData']);
	}

	private function uploadPointsForm() {
?>
    <form class="form-horizontal" method="post" enctype="multipart/form-data">
      <div class="form-group">
        <label class="col-sm-4 control-label">Wybierz plik z punktami georeferencyjnymi</label>
        <div class="col-sm-8" style="margin-top:7px;">
          <input type="hidden" name="subAction" value="uploadPointsConfirm">
          <input type="file" name="file" required/>
        </div>
      </div>
      <div class="form-group">
        <label class="col-sm-4 control-label">Maksymalna odległość istniejącego punktu (w metrach)</label>
        <div class="col-sm-2">
          <input type="number" class="form-control" name="maxDistance" data-bind="value:replyNumber" min="1" value="50" required>
        </div>
      </div>
      <div class="form-group">
         <div class="col-sm-offset-4 col-sm-8">
           <button type="submit" class="btn btn-primary" name="subAction" value="uploadPointsConfirm">Wgraj plik</button>
         </div>
      </div>
    </form>
<?php
	}
}