244 lines
10 KiB
PHP
244 lines
10 KiB
PHP
#!/usr/bin/php
|
|
<?php
|
|
|
|
// --- SCRIPT SETUP ---
|
|
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");
|
|
|
|
/**
|
|
* Creates a preorder record using logic similar to the PreorderIFrameController.
|
|
* @param array $payload The data for the new preorder.
|
|
* @return PreorderModel|null The saved preorder object on success, null on failure.
|
|
*/
|
|
function createPreorderEntry(array $payload) {
|
|
$h = new ADBHausnummer($payload['hausnummer_id']);
|
|
if (!$h->id) return null;
|
|
|
|
$w = new ADBWohneinheit($payload['wohneinheit_id']);
|
|
if (!$w->id) return null;
|
|
|
|
// Determine status based on the address/unit status, like in the controller
|
|
$status_code = max($w->status->code, $h->status->code);
|
|
$new_status = PreorderstatusModel::getFirst(["code" => $status_code]);
|
|
|
|
$data = [
|
|
'preordercampaign_id' => $payload['campaign_id'],
|
|
'adb_hausnummer_id' => $h->id,
|
|
'adb_wohneinheit_id' => $w->id,
|
|
'status_id' => $new_status ? $new_status->id : 1,
|
|
'type' => 'interest',
|
|
'connection_type' => 'single-dwelling',
|
|
'accept_agb' => 1,
|
|
'accept_dsgvo' => 1,
|
|
'company' => $payload['customer']['company'],
|
|
'firstname' => $payload['customer']['firstname'],
|
|
'lastname' => $payload['customer']['lastname'],
|
|
'street' => $payload['customer']['street'],
|
|
'housenumber' => $payload['customer']['housenumber'],
|
|
'zip' => $payload['customer']['zip'],
|
|
'city' => $payload['customer']['city'],
|
|
'phone' => $payload['customer']['phone'],
|
|
'email' => $payload['customer']['email'],
|
|
'submit_type' => 'import', // MODIFIED: Shortened value again to prevent truncation error
|
|
'create_by' => $payload['user_id'],
|
|
'edit_by' => $payload['user_id'],
|
|
];
|
|
|
|
$preorder = PreorderModel::create($data);
|
|
$preorder->createUcode();
|
|
|
|
if ($preorder->save()) {
|
|
return $preorder;
|
|
}
|
|
return null;
|
|
}
|
|
|
|
/**
|
|
* Generates variations of a street name to improve search matching.
|
|
*/
|
|
function generateStreetVariations($street_name) {
|
|
$variations = [$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);
|
|
}
|
|
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);
|
|
if(strpos($street_name, '.-') !== false) $variations[] = str_replace('.-', '.', $street_name);
|
|
|
|
$base_variations = $variations;
|
|
foreach($base_variations as $variation) {
|
|
if(strpos($variation, 'ß') !== false) $variations[] = str_replace('ß', 'ss', $variation);
|
|
if(strpos($variation, 'ss') !== false) $variations[] = str_replace('ss', 'ß', $variation);
|
|
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);
|
|
}
|
|
return array_unique($variations);
|
|
}
|
|
|
|
// --- SCRIPT INITIALIZATION ---
|
|
$me = new User(1);
|
|
$adb = FronkDB::singleton(ADDRESSDB_DBHOST, ADDRESSDB_DBUSER, ADDRESSDB_DBPASS, ADDRESSDB_DBNAME);
|
|
$db = FronkDB::singleton();
|
|
$log = mfLoghandler::singleton();
|
|
|
|
$inputFilename = __DIR__."/Kundenliste Leibnitz Markierung Zentrum.csv";
|
|
$outputFilename = __DIR__."/leibnitz-import-results.csv";
|
|
|
|
$inputFile = fopen($inputFilename, "r");
|
|
$outputFile = fopen($outputFilename, "w");
|
|
|
|
if (!$inputFile) die("Error: Could not open input file: $inputFilename\n");
|
|
if (!$outputFile) die("Error: Could not open output file: $outputFilename\n");
|
|
|
|
$preorder_campaign_id = 99;
|
|
$campaign_check = new Preordercampaign($preorder_campaign_id);
|
|
if (!$campaign_check->id) {
|
|
die("Error: Preorder Campaign with ID $preorder_campaign_id does not exist. Aborting.\n");
|
|
}
|
|
|
|
echo "Starting preorder import for Campaign ID: $preorder_campaign_id...\n";
|
|
|
|
// --- MAIN PROCESSING LOOP ---
|
|
$rowCount = 0; $successCount = 0; $skippedCount = 0;
|
|
$header = fgetcsv($inputFile);
|
|
$header[] = 'Import-Status';
|
|
fputcsv($outputFile, $header);
|
|
|
|
while (($csv = fgetcsv($inputFile, 0, ",")) !== FALSE) {
|
|
$rowCount++;
|
|
$statusMessage = '';
|
|
|
|
$anschluss_adresse_raw = trim($csv[11]);
|
|
|
|
if (empty($anschluss_adresse_raw)) {
|
|
$statusMessage = "Skipped: 'Objekt' column (Anschlussadresse) is empty.";
|
|
$csv[] = $statusMessage; fputcsv($outputFile, $csv); $skippedCount++; continue;
|
|
}
|
|
|
|
$strasse_name = ''; $hausnummer_name = ''; $plz_name = ''; $ort_name = '';
|
|
if (preg_match('/^(.+?)\s+([\d\/a-zA-Z\.\s-]+)[\s,]+(\d{4})[\s,]+(.+)$/i', $anschluss_adresse_raw, $m)) {
|
|
$strasse_name = trim($m[1]); $hausnummer_name = trim($m[2]); $plz_name = trim($m[3]); $ort_name = trim($m[4]);
|
|
} else {
|
|
$statusMessage = "Skipped: Could not parse Anschlussadresse from 'Objekt': '$anschluss_adresse_raw'";
|
|
$csv[] = $statusMessage; fputcsv($outputFile, $csv); $skippedCount++; continue;
|
|
}
|
|
|
|
$sql_gemeinde = "SELECT DISTINCT g.id FROM Gemeinde g JOIN Plz p ON p.gemeinde_id = g.id JOIN Ortschaft o ON o.gemeinde_id = g.id WHERE p.plz = '".$adb->escape($plz_name)."' AND o.name = '".$adb->escape($ort_name)."'";
|
|
$res_gemeinde = $adb->query($sql_gemeinde);
|
|
|
|
if (!$adb->num_rows($res_gemeinde)) {
|
|
$statusMessage = "Skipped: Gemeinde not found for PLZ: $plz_name, Ort: $ort_name";
|
|
$csv[] = $statusMessage; fputcsv($outputFile, $csv); $skippedCount++; continue;
|
|
}
|
|
$gemeinde_data = $adb->fetch_object($res_gemeinde);
|
|
$gemeinde_id = $gemeinde_data->id;
|
|
|
|
$strasse_variations = generateStreetVariations($strasse_name);
|
|
$escaped_variations = array_map(fn($var) => $adb->escape($var), $strasse_variations);
|
|
|
|
$sql_haus = "SELECT * FROM view_hausnummer WHERE gemeinde_id = $gemeinde_id AND strasse IN ('". implode("', '", $escaped_variations)."') AND hausnummer = '".$adb->escape($hausnummer_name)."'";
|
|
$res_haus = $adb->query($sql_haus);
|
|
|
|
if (!$adb->num_rows($res_haus)) {
|
|
$statusMessage = "Skipped: Anschlussadresse not found in DB: '$strasse_name $hausnummer_name'";
|
|
$csv[] = $statusMessage; fputcsv($outputFile, $csv); $skippedCount++; continue;
|
|
}
|
|
$hausnummer_data = $adb->fetch_object($res_haus);
|
|
|
|
$wohneinheiten = ADBWohneinheitModel::search(['hausnummer_id' => $hausnummer_data->hausnummer_id]);
|
|
$available_unit = null;
|
|
if (count($wohneinheiten) > 0) {
|
|
foreach($wohneinheiten as $unit) {
|
|
if (!PreorderModel::getFirst(['adb_wohneinheit_id' => $unit->id])) {
|
|
$available_unit = $unit; break;
|
|
}
|
|
}
|
|
} else {
|
|
$statusMessage = "Skipped: No Wohneinheiten found for Hausnummer ID ".$hausnummer_data->hausnummer_id;
|
|
$csv[] = $statusMessage; fputcsv($outputFile, $csv); $skippedCount++; continue;
|
|
}
|
|
|
|
if (!$available_unit) {
|
|
$statusMessage = "Skipped: All units at this address already have a preorder.";
|
|
$csv[] = $statusMessage; fputcsv($outputFile, $csv); $skippedCount++; continue;
|
|
}
|
|
|
|
$kunde_name_firma = trim($csv[0]);
|
|
$kunde_vorname = trim($csv[2]);
|
|
$kunde_strasse_raw = trim($csv[4]);
|
|
|
|
$kunde_strasse = $kunde_strasse_raw;
|
|
$kunde_hausnummer = null;
|
|
if (preg_match('/^(.+?)\s+([\d\w\/.-]+)$/', $kunde_strasse_raw, $m_customer_addr)) {
|
|
$kunde_strasse = trim($m_customer_addr[1]);
|
|
$kunde_hausnummer = trim($m_customer_addr[2]);
|
|
}
|
|
|
|
$m_email = [];
|
|
// MODIFIED: Corrected the regex by removing an extra hyphen to fix the compilation warning
|
|
preg_match('/[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}/i', trim($csv[8]), $m_email);
|
|
|
|
// Assemble payload and call the new creation function
|
|
$payload = [
|
|
'campaign_id' => $preorder_campaign_id,
|
|
'hausnummer_id' => $hausnummer_data->hausnummer_id,
|
|
'wohneinheit_id' => $available_unit->id,
|
|
'user_id' => $me->id,
|
|
'customer' => [
|
|
'company' => ($kunde_name_firma && !$kunde_vorname) ? $kunde_name_firma : null,
|
|
'firstname' => $kunde_vorname,
|
|
'lastname' => ($kunde_vorname) ? $kunde_name_firma : '',
|
|
'street' => $kunde_strasse,
|
|
'housenumber' => $kunde_hausnummer,
|
|
'zip' => trim($csv[5]),
|
|
'city' => trim($csv[6]),
|
|
'phone' => trim($csv[19]),
|
|
'email' => $m_email[0] ?? '',
|
|
]
|
|
];
|
|
|
|
$newPreorder = createPreorderEntry($payload);
|
|
|
|
if ($newPreorder) {
|
|
$statusMessage = "Success: Preorder created with code " . $newPreorder->ucode;
|
|
$successCount++;
|
|
} else {
|
|
$statusMessage = "Error: Failed to save preorder to the database.";
|
|
$skippedCount++;
|
|
}
|
|
|
|
echo "Row $rowCount: $statusMessage\n";
|
|
$csv[] = $statusMessage;
|
|
fputcsv($outputFile, $csv);
|
|
}
|
|
|
|
// --- CLEANUP ---
|
|
fclose($inputFile);
|
|
fclose($outputFile);
|
|
|
|
echo "\n========================================\n";
|
|
echo "Import completed!\n";
|
|
echo "Total rows processed: $rowCount\n";
|
|
echo "Successfully created: $successCount\n";
|
|
echo "Skipped or failed: $skippedCount\n";
|
|
echo "Results saved to: $outputFilename\n";
|
|
echo "========================================\n";
|
|
?>
|