324 lines
11 KiB
PHP
324 lines
11 KiB
PHP
#!/usr/bin/php
|
|
<?php
|
|
|
|
function generateStreetVariations($street_name) {
|
|
$variations = [$street_name];
|
|
|
|
// Handle spaces and hyphens
|
|
if(strpos($street_name, ' ') !== false) {
|
|
$variations[] = str_replace(' ', '-', $street_name);
|
|
$variations[] = str_replace(' ', '', $street_name);
|
|
}
|
|
if(strpos($street_name, '-') !== false) {
|
|
$variations[] = str_replace('-', ' ', $street_name);
|
|
$variations[] = str_replace('-', '', $street_name);
|
|
}
|
|
|
|
// Handle dots
|
|
if(strpos($street_name, '.') !== false) {
|
|
$variations[] = str_replace('.', '. ', $street_name);
|
|
$variations[] = str_replace('.', '.-', $street_name);
|
|
}
|
|
if(strpos($street_name, '. ') !== false) {
|
|
$variations[] = str_replace('. ', '.', $street_name);
|
|
$variations[] = str_replace('. ', '.-', $street_name);
|
|
}
|
|
if(strpos($street_name, '.-') !== false) {
|
|
$variations[] = str_replace('.-', '.', $street_name);
|
|
$variations[] = str_replace('.-', '. ', $street_name);
|
|
}
|
|
|
|
// Handle German special characters
|
|
$base_variations = $variations;
|
|
foreach($base_variations as $variation) {
|
|
// ß variations
|
|
if(strpos($variation, 'ß') !== false) {
|
|
$variations[] = str_replace('ß', 'ss', $variation);
|
|
}
|
|
if(strpos($variation, 'ss') !== false) {
|
|
$variations[] = str_replace('ss', 'ß', $variation);
|
|
}
|
|
|
|
// Umlaut variations
|
|
if(strpos($variation, 'ä') !== false) {
|
|
$variations[] = str_replace('ä', 'ae', $variation);
|
|
}
|
|
if(strpos($variation, 'ae') !== false) {
|
|
$variations[] = str_replace('ae', 'ä', $variation);
|
|
}
|
|
|
|
if(strpos($variation, 'ö') !== false) {
|
|
$variations[] = str_replace('ö', 'oe', $variation);
|
|
}
|
|
if(strpos($variation, 'oe') !== false) {
|
|
$variations[] = str_replace('oe', 'ö', $variation);
|
|
}
|
|
|
|
if(strpos($variation, 'ü') !== false) {
|
|
$variations[] = str_replace('ü', 'ue', $variation);
|
|
}
|
|
if(strpos($variation, 'ue') !== false) {
|
|
$variations[] = str_replace('ue', 'ü', $variation);
|
|
}
|
|
|
|
// Handle "straße" variations
|
|
if(stripos($variation, 'straße') !== false) {
|
|
$variations[] = str_ireplace('straße', 'strasse', $variation);
|
|
$variations[] = str_ireplace('straße', ' straße', $variation);
|
|
$variations[] = str_ireplace('straße', ' strasse', $variation);
|
|
}
|
|
if(stripos($variation, 'strasse') !== false) {
|
|
$variations[] = str_ireplace('strasse', 'straße', $variation);
|
|
$variations[] = str_ireplace('strasse', ' straße', $variation);
|
|
$variations[] = str_ireplace('strasse', ' strasse', $variation);
|
|
}
|
|
}
|
|
|
|
return array_unique($variations);
|
|
}
|
|
|
|
|
|
//require 'vendor/autoload.php';
|
|
require("../../../config/config.php");
|
|
|
|
define('FRONKDB_SQLDEBUG',false);
|
|
error_reporting(E_ALL & ~(E_NOTICE | E_STRICT | E_DEPRECATED));
|
|
|
|
require_once(LIBDIR."/mvcfronk/mfRouter/mfRouter.php");
|
|
require_once(LIBDIR."/mvcfronk/mfBase/mfBaseModel.php");
|
|
require_once(LIBDIR."/mvcfronk/mfBase/mfBaseController.php");
|
|
|
|
$me = new User(1);
|
|
|
|
$filename = __DIR__."/import/qgis_leibnitz_wagna.csv";
|
|
|
|
$db = FronkDB::singleton(ADDRESSDB_DBHOST, ADDRESSDB_DBUSER, ADDRESSDB_DBPASS, ADDRESSDB_DBNAME);
|
|
$log = mfLoghandler::singleton();
|
|
|
|
$input = fopen($filename, "r");
|
|
|
|
$h_extrefs = [];
|
|
|
|
// Updated netzgebiet to 96 as requested
|
|
$netzgebiet = new ADBNetzgebiet(98);
|
|
$default_freigabe = json_encode(["interest", "provision", "order", "reorder"]);
|
|
|
|
$i = 0;
|
|
while($csv = fgetcsv($input, 0, ",")) {
|
|
$i++;
|
|
if($i == 1) continue; // Skip header
|
|
|
|
$hausnummer = false;
|
|
|
|
if(!trim($csv[0])) {
|
|
continue;
|
|
}
|
|
|
|
// Parse CSV columns based on the provided structure
|
|
$adresse = trim($csv[0]); // Adresse
|
|
$ne_count = (int)trim($csv[1]); // NE (Nutzungseinheiten)
|
|
$status = trim($csv[2]); // Status
|
|
$uuid = trim($csv[3]); // UUID
|
|
$tt_object_id = trim($csv[4]); // TTObjectID
|
|
$long = str_replace(",", ".", trim($csv[5])); // X-WGS84
|
|
$lat = str_replace(",", ".", trim($csv[6])); // Y-WGS84
|
|
$plz_name = trim($csv[7]); // PLZ
|
|
$ort_name = trim($csv[8]); // Ort
|
|
|
|
if (strpos($adresse, 'Pelzmannstgraße') !== false) {
|
|
$adresse = str_replace('Pelzmannstgraße', 'Pelzmannstraße', $adresse);
|
|
}
|
|
|
|
// Use UUID as external reference
|
|
$hausnummer_extref = $uuid;
|
|
|
|
if(in_array($hausnummer_extref, $h_extrefs)) {
|
|
echo "Warning: Hausnummer extref $hausnummer_extref already exists, skipping...\n";
|
|
continue;
|
|
}
|
|
$h_extrefs[] = $hausnummer_extref;
|
|
|
|
if(!$ne_count) $ne_count = 1;
|
|
|
|
if(!$adresse) continue;
|
|
|
|
// Parse address to extract street name and house number
|
|
$strasse_name = "";
|
|
$hausnummer_name = "";
|
|
$addresszusatz = "";
|
|
|
|
$m = [];
|
|
// Enhanced regex to handle various address formats
|
|
if(preg_match('/^(.+?)\s+(\d+[a-z0-9\/&#._-]*)(?:\s*(.*))?$/i', $adresse, $m)) {
|
|
$strasse_name = trim($m[1]);
|
|
$hausnummer_name = trim($m[2]);
|
|
if(isset($m[3]) && trim($m[3])) {
|
|
$addresszusatz = trim($m[3]);
|
|
}
|
|
} else {
|
|
echo "Could not parse address format: $adresse\n";
|
|
continue;
|
|
}
|
|
|
|
if(!$strasse_name || !$hausnummer_name) {
|
|
echo "Could not extract street name or house number from: $adresse\n";
|
|
continue;
|
|
}
|
|
|
|
if(!$plz_name || !$ort_name) {
|
|
echo "Missing PLZ or Ort for address: $adresse\n";
|
|
continue;
|
|
}
|
|
|
|
if(!$hausnummer_extref) {
|
|
echo "No UUID for address: $adresse\n";
|
|
continue;
|
|
}
|
|
|
|
// Find Gemeinde by PLZ and Ort
|
|
$sql = "SELECT DISTINCT g.* FROM Gemeinde g
|
|
JOIN Plz p ON p.gemeinde_id = g.id
|
|
JOIN Ortschaft o ON o.gemeinde_id = g.id
|
|
WHERE p.plz = '".$db->escape($plz_name)."'
|
|
AND o.name = '".$db->escape($ort_name)."'";
|
|
$res = $db->query($sql);
|
|
|
|
if(!$db->num_rows($res)) {
|
|
echo "Gemeinde not found for PLZ: $plz_name, Ort: $ort_name\n";
|
|
continue;
|
|
}
|
|
|
|
$gemeinde_data = $db->fetch_object($res);
|
|
$gemeinde_id = $gemeinde_data->id;
|
|
|
|
// Intelligent street name search with variations
|
|
$strasse_variations = generateStreetVariations($strasse_name);
|
|
|
|
// Search for existing Hausnummer
|
|
$escaped_variations = array_map(function($var) use ($db) {
|
|
return $db->escape($var);
|
|
}, $strasse_variations);
|
|
|
|
$hausnummer_name_escaped = $db->escape($hausnummer_name);
|
|
|
|
$sql = "SELECT * FROM view_hausnummer
|
|
WHERE
|
|
gemeinde_id = $gemeinde_id
|
|
AND hausnummer_extref = '$hausnummer_extref'
|
|
OR gemeinde_id = $gemeinde_id
|
|
AND strasse IN ('". implode("', '", $escaped_variations)."')
|
|
AND hausnummer='$hausnummer_name_escaped'";
|
|
|
|
$res = $db->query($sql);
|
|
|
|
if($db->num_rows($res)) {
|
|
// Update existing Hausnummer
|
|
$data = $db->fetch_object($res);
|
|
$hausnummer = new ADBHausnummer($data->hausnummer_id);
|
|
echo "Found existing Hausnummer: $strasse_name $hausnummer_name\n";
|
|
} else {
|
|
// Create new Hausnummer
|
|
echo "Creating new Hausnummer: $strasse_name $hausnummer_name\n";
|
|
|
|
// Find Strasse
|
|
$strasse = null;
|
|
foreach($strasse_variations as $street_var) {
|
|
$strasse = ADBStrasseModel::getFirst(["gemeinde_id" => $gemeinde_id, "name" => $street_var]);
|
|
if($strasse) break;
|
|
}
|
|
|
|
if(!$strasse) {
|
|
echo "Strasse not found for variations of: $strasse_name (gemeinde_id $gemeinde_id), creating new one.\n";
|
|
$strasse_data = [
|
|
"gemeinde_id" => $gemeinde_id,
|
|
"name" => $strasse_name,
|
|
"create" => date("U"),
|
|
"edit" => date("U"),
|
|
];
|
|
$strasse = ADBStrasseModel::create($strasse_data);
|
|
$strasse->save();
|
|
}
|
|
|
|
$plz = ADBPlzModel::getFirst(["gemeinde_id" => $gemeinde_id, "plz" => $plz_name]);
|
|
if(!$plz) {
|
|
echo "PLZ $plz_name not found\n";
|
|
continue;
|
|
}
|
|
|
|
$ortschaft = ADBOrtschaftModel::getFirst(["gemeinde_id" => $gemeinde_id, "name" => $ort_name]);
|
|
if(!$ortschaft) {
|
|
echo "Ortschaft $ort_name not found\n";
|
|
continue;
|
|
}
|
|
|
|
$hausnummer_data = [
|
|
"netzgebiet_id" => $netzgebiet->id,
|
|
"extref" => $hausnummer_extref,
|
|
"ortschaft_id" => $ortschaft->id,
|
|
"plz_id" => $plz->id,
|
|
"strasse_id" => $strasse->id,
|
|
"hausnummer" => $hausnummer_name,
|
|
"zusatz" => ($addresszusatz) ? $addresszusatz : null,
|
|
"gps_lat" => $lat,
|
|
"gps_long" => $long,
|
|
"unit_count" => $ne_count,
|
|
"freigabe" => $default_freigabe,
|
|
];
|
|
|
|
$hausnummer = ADBHausnummerModel::create($hausnummer_data);
|
|
$hausnummer->save();
|
|
}
|
|
|
|
// var_dump($hausnummer->netzgebiet_id);exit;
|
|
// Update Hausnummer properties
|
|
if($hausnummer->netzgebiet_id != $netzgebiet->id) {
|
|
$hausnummer->netzgebiet_id = $netzgebiet->id;
|
|
}
|
|
if($hausnummer->extref != $hausnummer_extref) {
|
|
$hausnummer->extref = $hausnummer_extref;
|
|
}
|
|
if($lat && $long) {
|
|
$hausnummer->gps_lat = (float)$lat;
|
|
$hausnummer->gps_long = (float)$long;
|
|
}
|
|
// if status = Rohr im Haus set $hausnummer->status_id to 6
|
|
if($status == "Rohr im Haus") {
|
|
$hausnummer->status_id = 6; // Assuming 6 is the ID for "Rohr im Haus"
|
|
} else {
|
|
$hausnummer->status_id = 1; // Default status
|
|
}
|
|
|
|
|
|
$hausnummer->freigabe = $default_freigabe;
|
|
$hausnummer->unit_count = $ne_count;
|
|
$hausnummer->save();
|
|
|
|
// Handle Wohneinheiten based on NE count
|
|
$existing_units_count = ADBWohneinheitModel::count(["hausnummer_id" => $hausnummer->id]);
|
|
|
|
if($existing_units_count < $ne_count) {
|
|
|
|
echo "Creating $ne_count Wohneinheiten for: $strasse_name $hausnummer_name\n";
|
|
for($u = 0; $u < $ne_count; $u++) {
|
|
$num = $u + 1;
|
|
$top_name = "Top $num";
|
|
|
|
$wohneinheit_data = [
|
|
'hausnummer_id' => $hausnummer->id,
|
|
'num' => $num,
|
|
'name' => $top_name,
|
|
'tuer' => ($ne_count > 1) ? $num : null
|
|
];
|
|
|
|
$wohneinheit = ADBWohneinheitModel::create($wohneinheit_data);
|
|
$wohneinheit->save();
|
|
|
|
echo " Created Wohneinheit: $top_name\n";
|
|
}
|
|
|
|
echo "Processed: $adresse ($ne_count units)\n\n";
|
|
}
|
|
}
|
|
|
|
fclose($input);
|
|
echo "Import completed!\n"; |