265 lines
8.7 KiB
PHP
265 lines
8.7 KiB
PHP
#!/usr/bin/php
|
|
<?php
|
|
|
|
//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");
|
|
|
|
|
|
$freigabe_default = json_encode(["interest", "provision", "order", "reorder"]);
|
|
$adb_gemeinde_id = 1462;
|
|
|
|
$me = new User(1);
|
|
|
|
$folder = __DIR__."/import/";
|
|
$csvname = "RIMO_230307_Adressen_Cluster_Lieboch.csv";
|
|
$filename = $folder.$csvname;
|
|
|
|
$db = FronkDB::singleton(ADDRESSDB_DBHOST, ADDRESSDB_DBUSER, ADDRESSDB_DBPASS, ADDRESSDB_DBNAME);
|
|
$log = mfLoghandler::singleton();
|
|
|
|
$input = fopen($filename, "r");
|
|
|
|
$h_extrefs = [];
|
|
|
|
$l = 0;
|
|
$u = 0;
|
|
while($csv = fgetcsv($input, 0, ";")) {
|
|
$l++;
|
|
if($l == 1) continue;
|
|
|
|
$hausnummer = false;
|
|
|
|
if(!trim($csv[0])) {
|
|
continue;
|
|
}
|
|
|
|
$hausnummer_extref = trim($csv[48]);
|
|
$adrcd = trim($csv[65]);
|
|
$netzgebiet_extref = trim($csv[55]);
|
|
$grund_nr = trim($csv[74]);
|
|
//gps_long, gps_lat
|
|
|
|
//$rollout_time = trim($csv[17]);
|
|
//$rollout_info = trim($csv[18]);
|
|
//$freigabe = trim($csv[19])
|
|
$unit_count = trim($csv[5]);
|
|
$gdaeigenschaft = trim($csv[67]);
|
|
$fcp_name = trim($csv[50]);
|
|
|
|
$execution_state = trim($csv[9]);
|
|
$btype = trim($csv[31]);
|
|
|
|
if(!$netzgebiet_extref) {
|
|
continue;
|
|
}
|
|
|
|
|
|
$netzgebiet = ADBNetzgebietModel::getFirst(["extref" => $netzgebiet_extref]);
|
|
if(!$netzgebiet) {
|
|
echo "netzgebiet not found\n";
|
|
exit;
|
|
}
|
|
|
|
if($execution_state == "Canceled") {
|
|
continue;
|
|
}
|
|
|
|
if($btype == "Greenfield") {
|
|
continue;
|
|
}
|
|
|
|
$gem_name = trim($csv[60]);
|
|
$plz_string = trim($csv[88]);
|
|
$ort_name = trim($csv[32]);
|
|
$strasse_hausnummer = trim($csv[23]);
|
|
|
|
$strasse_name = "";
|
|
$hausnummer_name = "";
|
|
|
|
$m = [];
|
|
if(preg_match('/^(.+)\s+(\d+[a-z0-9\/&#._-]*)(.+)?/i', $strasse_hausnummer, $m)) {
|
|
$strasse_name = trim($m[1]);
|
|
$hausnummer_name = trim($m[2]);
|
|
if(array_key_exists(3, $m)) {
|
|
$addresszusatz = trim($m[3]);
|
|
}
|
|
}
|
|
|
|
$lat = str_replace(",",".",trim($csv[10]));
|
|
$long = str_replace(",",".",trim($csv[11]));
|
|
|
|
|
|
if(!$adrcd) {
|
|
continue;
|
|
}
|
|
|
|
if(!is_numeric($adrcd)) {
|
|
echo "Invalid adrcd ($adrcd)\n";
|
|
continue;
|
|
}
|
|
|
|
if(!$hausnummer_extref || !is_numeric($hausnummer_extref)) {
|
|
//echo("Keine Hausnummer Extref\n");
|
|
continue;
|
|
}
|
|
|
|
if($adrcd == "25023176") continue;
|
|
|
|
$existing_hausnummer = ADBHausnummerModel::getFirst(["extref" => $hausnummer_extref, "gemeinde_id" => $adb_gemeinde_id]);
|
|
if(!$existing_hausnummer) {
|
|
$existing_hausnummer = ADBHausnummerModel::getFirst(["adrcd" => $adrcd]);
|
|
}
|
|
|
|
|
|
|
|
if(!$existing_hausnummer) {
|
|
//echo "$hausnummer_extref not found $strasse_name $hausnummer_name\n";
|
|
|
|
// find hausnummer
|
|
//echo "$strasse_name $hausnummer_name\n";
|
|
|
|
//if($strasse_name == "Toepferring") $strasse_name = "Töpferring";
|
|
//if($strasse_name == "Foehrenweg") $strasse_name = "Föhrenweg";
|
|
|
|
if($strasse_name == "Hans-Thalhammer-Straße") $strasse_name = "H. Thalhammer-Straße";
|
|
if($strasse_name == "Packerstraße") $strasse_name = "Packer Straße";
|
|
|
|
$strasse_name = $db->escape($strasse_name);
|
|
$hausnummer_name = $db->escape($hausnummer_name);
|
|
|
|
$strasse_search = [$strasse_name];
|
|
|
|
if(strpos($strasse_name, ' ') !== false) $strasse_search[] = str_replace(' ', '-', $strasse_name);
|
|
if(strpos($strasse_name, '-') !== false) $strasse_search[] = str_replace('-', ' ', $strasse_name);
|
|
if(strpos($strasse_name, '.') !== false) $strasse_search[] = str_replace('.', '. ', $strasse_name);
|
|
if(strpos($strasse_name, '.') !== false) $strasse_search[] = str_replace('.', '.-', $strasse_name);
|
|
if(strpos($strasse_name, '. ') !== false) $strasse_search[] = str_replace('. ', '.', $strasse_name);
|
|
if(strpos($strasse_name, '. ') !== false) $strasse_search[] = str_replace('. ', '.-', $strasse_name);
|
|
if(strpos($strasse_name, '.') !== false) $strasse_search[] = str_replace('.-', '.', $strasse_name);
|
|
if(strpos($strasse_name, '.') !== false) $strasse_search[] = str_replace('.-', '. ', $strasse_name);
|
|
|
|
foreach($strasse_search as $search) {
|
|
if(strpos($search, 'ß') !== false) $strasse_search[] = str_replace('ß', 'ss', $search);
|
|
if(strpos($search, 'ä') !== false) $strasse_search[] = str_replace('ä', 'ae', $search);
|
|
if(strpos($search, 'ö') !== false) $strasse_search[] = str_replace('ö', 'oe', $search);
|
|
if(strpos($search, 'ü') !== false) $strasse_search[] = str_replace('ü', 'ue', $search);
|
|
|
|
if(strpos($search, 'ss') !== false) $strasse_search[] = str_replace('ss', 'ß', $search);
|
|
if(strpos($search, 'ae') !== false) $strasse_search[] = str_replace('ae', 'ä', $search);
|
|
if(strpos($search, 'oe') !== false) $strasse_search[] = str_replace('oe', 'ö', $search);
|
|
if(strpos($search, 'ue') !== false) $strasse_search[] = str_replace('ue', 'ü', $search);
|
|
}
|
|
|
|
|
|
$sql = "SELECT * FROM view_hausnummer WHERE gemeinde_id = $adb_gemeinde_id AND strasse IN ('". implode("', '", $strasse_search)."') AND hausnummer='$hausnummer_name'";
|
|
$res = $db->query($sql);
|
|
|
|
if($db->num_rows($res)) {
|
|
$data = $db->fetch_object($res);
|
|
$existing_hausnummer = new ADBHausnummer($data->hausnummer_id);
|
|
$existing_hausnummer->netzgebiet_id = $netzgebiet->id;
|
|
|
|
if(!$existing_hausnummer->adrcd) {
|
|
$existing_hausnummer->adrcd = $adrcd;
|
|
}
|
|
|
|
$existing_hausnummer->extref = $hausnummer_extref;
|
|
$existing_hausnummer->grund_nr = $grund_nr;
|
|
|
|
if($lat && $long) {
|
|
$existing_hausnummer->gps_lat = $lat;
|
|
$existing_hausnummer->gps_long = $long;
|
|
}
|
|
|
|
$existing_hausnummer->gdaeigenschaft = $gdaeigenschaft;
|
|
$existing_hausnummer->rimo_fcp_name = $fcp_name;
|
|
if(!$existing_hausnummer->oaid) {
|
|
$existing_hausnummer->oaid = $existing_hausnummer->getNewOAID();
|
|
}
|
|
$existing_hausnummer->save();
|
|
|
|
} else {
|
|
echo "Address not found (straße: $strasse_name, hausnummer: $hausnummer_name, adrcd: $adrcd, extref: $hausnummer_extref)\n";
|
|
continue;
|
|
}
|
|
} else {
|
|
|
|
// found hausnummer
|
|
$existing_hausnummer->netzgebiet_id = $netzgebiet->id;
|
|
if(!$existing_hausnummer->adrcd) {
|
|
$existing_hausnummer->adrcd = $adrcd;
|
|
}
|
|
|
|
$existing_hausnummer->extref = $hausnummer_extref;
|
|
$existing_hausnummer->grund_nr = $grund_nr;
|
|
if($lat && $long) {
|
|
$existing_hausnummer->gps_lat = $lat;
|
|
$existing_hausnummer->gps_long = $long;
|
|
}
|
|
$existing_hausnummer->gdaeigenschaft = $gdaeigenschaft;
|
|
$existing_hausnummer->rimo_fcp_name = $fcp_name;
|
|
if(!$existing_hausnummer->oaid) {
|
|
$existing_hausnummer->oaid = $existing_hausnummer->getNewOAID();
|
|
}
|
|
$existing_hausnummer->save();
|
|
}
|
|
//continue;
|
|
|
|
|
|
|
|
// add new units
|
|
$existing_units_count = ADBWohneinheitModel::count(['hausnummer_id' => $existing_hausnummer->id]);
|
|
if($existing_units_count != $unit_count) {
|
|
/*if(($existing_units_count - $unit_count) > 1) {
|
|
echo "========================================================================\n";
|
|
echo "=============== [".$existing_hausnummer->id."] Need to delete ".($existing_units_count - $unit_count)." units\n";
|
|
echo "========================================================================\n";
|
|
}*/
|
|
if($existing_units_count < $unit_count) {
|
|
echo "=============== Adding ".($unit_count - $existing_units_count)." units\n";
|
|
|
|
$new_units_count = $unit_count - $existing_units_count;
|
|
$last_unit_num = 0;
|
|
|
|
|
|
foreach(ADBWohneinheitModel::search(['hausnummer_id' => $existing_hausnummer->id]) as $tmp_unit) {
|
|
if($tmp_unit->num > $last_unit_num) {
|
|
$last_unit_num = $tmp_unit->num;
|
|
}
|
|
}
|
|
|
|
// create wohneinheiten
|
|
for($i = 1; $i <= $new_units_count; $i++) {
|
|
$num = $last_unit_num + $i;
|
|
//echo "$existing_units_count create wohneinheit $num\n";
|
|
$unit_data = [
|
|
'hausnummer_id' => $existing_hausnummer->id,
|
|
'num' => $num,
|
|
];
|
|
$wohneinheit = ADBWohneinheitModel::create($unit_data);
|
|
$wohneinheit_id = $wohneinheit->save();
|
|
if(!$wohneinheit_id) {
|
|
die("Cannot save Wohneinheit\n");
|
|
}
|
|
$wohneinheit->oaid = $wohneinheit->getNewOAID();
|
|
if(!$wohneinheit->oaid) {
|
|
die("Error generating OAID for wohneinheit ".$wohneinheit->id);
|
|
}
|
|
//var_dump($wohneinheit);
|
|
$wohneinheit->save();
|
|
$w++;
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
//echo "$hausnummer_extref: $adrcd, $netzgebiet_extref, $grund_nr, $unit_count, $gdaeigenschaft\n";
|
|
|
|
} |