453 lines
20 KiB
PHP
453 lines
20 KiB
PHP
|
<?
|
||
|
|
switch ($_GET["action"]) {
|
||
|
|
case 'edit':
|
||
|
|
edit_mandant();
|
||
|
|
break;
|
||
|
|
case 'delete':
|
||
|
|
delete_mandant($mandant['id']);
|
||
|
|
break;
|
||
|
|
case 'new':
|
||
|
|
require_once("edit_mandant_cardform.inc.php");
|
||
|
|
break;
|
||
|
|
case 'save':
|
||
|
|
save_mandant();
|
||
|
|
break;
|
||
|
|
case 'import_csv':
|
||
|
|
import_from_csv();
|
||
|
|
break;
|
||
|
|
case 'import':
|
||
|
|
import_email_from_csv();
|
||
|
|
break;
|
||
|
|
case 'delete_image':
|
||
|
|
delete_mandant_image();
|
||
|
|
break;
|
||
|
|
default:
|
||
|
|
require_once("edit_mandant_listform.inc.php");
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
|
||
|
|
function edit_mandant( $_id = "", $messages = array() ) {
|
||
|
|
if ((int)$_id > 0) {
|
||
|
|
$input_id = $_id;
|
||
|
|
} else {
|
||
|
|
$input_id = $_POST["input_id"];
|
||
|
|
}
|
||
|
|
|
||
|
|
if ($input_id <> '') {
|
||
|
|
|
||
|
|
$pdoHost = getenv('MAIN_MYSQL_DB_HOST');
|
||
|
|
$pdoPort = getenv('MAIN_MYSQL_DB_PORT');
|
||
|
|
$pdoUser = getenv('MAIN_MYSQL_DB_USER');
|
||
|
|
$pdoPass = getenv('MAIN_MYSQL_DB_PASS');
|
||
|
|
$pdoSchema = getenv('MAIN_MYSQL_DB_SCHEMA');
|
||
|
|
|
||
|
|
$pdo = new \DynCom\mysyde\common\classes\PDOQueryWrapper($pdoHost, $pdoPort, $pdoSchema, $pdoUser, $pdoPass);
|
||
|
|
|
||
|
|
$prepStatement = " SELECT * FROM main_mandant WHERE id = :id LIMIT 1 ";
|
||
|
|
$params = [
|
||
|
|
[':id', $input_id, PDO::PARAM_STR],
|
||
|
|
];
|
||
|
|
$pdo->setQuery($prepStatement);
|
||
|
|
$pdo->prepareQuery();
|
||
|
|
$pdo->bindParameters($params);
|
||
|
|
$pdo->executePreparedStatement();
|
||
|
|
$result = $pdo->getResultArray();
|
||
|
|
|
||
|
|
if (count($result) == 1) {
|
||
|
|
$input_mandant = $result[0];
|
||
|
|
$input_mandant["password"] = "nochange";
|
||
|
|
require_once("edit_mandant_cardform.inc.php");
|
||
|
|
}
|
||
|
|
} else {
|
||
|
|
$input_mandant = array();
|
||
|
|
require_once("edit_mandant_cardform.inc.php");
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
function delete_mandant($curr_mandant_id) {
|
||
|
|
$translation = \DynCom\mysyde\common\classes\Registry::get("translation");
|
||
|
|
if ($_POST["input_id"] <> '') {
|
||
|
|
if ($_POST["input_id"] <> $curr_mandant_id) {
|
||
|
|
$query = "DELETE FROM main_mandant WHERE id = '" . $_POST["input_id"] . "' LIMIT 1";
|
||
|
|
@mysqli_query($GLOBALS['mysql_con'], $query);
|
||
|
|
$deleteCollection = @mysqli_query($GLOBALS["mysql_con"], "DELETE FROM main_collection_mandant_link WHERE main_mandant_id=".$_POST["input_id"]);
|
||
|
|
$deleteCertificate = @mysqli_query($GLOBALS["mysql_con"], "DELETE FROM certificate_mandant_link WHERE main_mandant_id=".$_POST["input_id"]);
|
||
|
|
$deleteKnowledgecenterCategory = @mysqli_query($GLOBALS["mysql_con"], "DELETE FROM knowledgecenter_category_mandant_link WHERE main_mandant_id=".$_POST["input_id"]);
|
||
|
|
$deleteLearningCategory = @mysqli_query($GLOBALS["mysql_con"], "DELETE FROM learning_category_mandant_link WHERE main_mandant_id=".$_POST["input_id"]);
|
||
|
|
$deleteLearningUnit = @mysqli_query($GLOBALS["mysql_con"], "DELETE FROM learning_unit_mandant_link WHERE main_mandant_id=".$_POST["input_id"]);
|
||
|
|
$deleteDepartment = @mysqli_query($GLOBALS["mysql_con"], "DELETE FROM main_department_mandant_link WHERE main_mandant_id=".$_POST["input_id"]);
|
||
|
|
$deleteContactDepartment = @mysqli_query($GLOBALS["mysql_con"], "DELETE FROM main_contact_department WHERE main_mandant_id=".$_POST["input_id"]);
|
||
|
|
$deleteContactPermission = @mysqli_query($GLOBALS["mysql_con"], "DELETE FROM main_contact_permission WHERE main_mandant_id=".$_POST["input_id"]);
|
||
|
|
$deleteNavigation = @mysqli_query($GLOBALS["mysql_con"], "DELETE FROM main_navigation_permission WHERE type = 'mandant' AND permission_type_id=".$_POST["input_id"]);
|
||
|
|
} else {
|
||
|
|
header('EDIT_ERROR: 1');
|
||
|
|
$messages[] = "<div class=\"errorbox\">" . $translation->get("mandant_error5") . "</div>";
|
||
|
|
edit_mandant($_POST["input_id"], $messages);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
require_once("edit_mandant_listform.inc.php");
|
||
|
|
}
|
||
|
|
|
||
|
|
function save_mandant() {
|
||
|
|
$messages = array();
|
||
|
|
$error = FALSE;
|
||
|
|
$input_id = "";
|
||
|
|
$translation = \DynCom\mysyde\common\classes\Registry::get("translation");
|
||
|
|
|
||
|
|
if ($_POST["input_id"] <> '') {
|
||
|
|
$inserted = FALSE;
|
||
|
|
$input_id = $_POST["input_id"];
|
||
|
|
$query = "UPDATE main_mandant
|
||
|
|
SET description = '" . $_POST['input_description'] . "',
|
||
|
|
address = '" . $_POST['input_address'] . "',
|
||
|
|
number_central = '".$_POST['input_number_central']."',
|
||
|
|
number_support = '" . $_POST['input_number_support'] . "',
|
||
|
|
number_fax = '" . $_POST['input_number_fax'] . "',
|
||
|
|
link_support = '" . $_POST['input_link_support'] . "',
|
||
|
|
link_website = '" . $_POST['input_link_website'] . "',
|
||
|
|
mail_team = '" . $_POST['input_mail_team'] . "',
|
||
|
|
mandant_color = '" . $_POST['input_mandant_color'] . "',
|
||
|
|
mandant_border_color = '" . $_POST['input_mandant_border_color'] . "',
|
||
|
|
mandant_border_radius = '" . $_POST['input_border_radius'] . "',
|
||
|
|
main_navigation_id = '" . $_POST['input_navigation_id'] . "',
|
||
|
|
mandant_bg_light = '" . $_POST['input_mandant_bg_light'] . "'
|
||
|
|
WHERE id = '" . $_POST["input_id"] . "' LIMIT 1";
|
||
|
|
createMySydeLog('main_mandant', $_POST["input_id"], 'UPDATE');
|
||
|
|
} else {
|
||
|
|
$query = "INSERT INTO main_mandant (id, description, address, number_central, number_support, number_fax, link_support, link_website, mail_team, mandant_color, mandant_border_color, mandant_border_radius, main_navigation_id, mandant_bg_light) VALUES (
|
||
|
|
NULL,
|
||
|
|
'" . $_POST['input_description'] . "',
|
||
|
|
'" . $_POST['input_address'] . "',
|
||
|
|
'" . $_POST['input_number_central'] . "',
|
||
|
|
'" . $_POST['input_number_support'] . "',
|
||
|
|
'" . $_POST['input_number_fax'] . "',
|
||
|
|
'" . $_POST['input_link_support'] . "',
|
||
|
|
'" . $_POST['input_link_website'] . "',
|
||
|
|
'" . $_POST['input_mail_team'] . "',
|
||
|
|
'" . $_POST['input_mandant_color'] . "',
|
||
|
|
'" . $_POST['input_mandant_border_color'] . "',
|
||
|
|
'" . $_POST['input_border_radius'] . "',
|
||
|
|
'" . $_POST['input_navigation_id'] . "',
|
||
|
|
'" . $_POST['input_mandant_bg_light'] . "'
|
||
|
|
)";
|
||
|
|
$inserted = TRUE;
|
||
|
|
}
|
||
|
|
|
||
|
|
if (!$error) {
|
||
|
|
@mysqli_query($GLOBALS['mysql_con'], $query);
|
||
|
|
if ($inserted == TRUE) {
|
||
|
|
$input_id = mysqli_insert_id($GLOBALS['mysql_con']);
|
||
|
|
createMySydeLog('main_mandant', $input_id, 'INSERT');
|
||
|
|
$messages[] = '<div class="successbox">' . $translation->get("mandant_msg_success1") . '</div>';
|
||
|
|
} else {
|
||
|
|
$messages[] = '<div class="successbox">' . $translation->get("mandant_msg_success2") . '</div>';
|
||
|
|
}
|
||
|
|
if(isset($_FILES) && $_FILES['input_image']['size'] != 0 ){
|
||
|
|
$fileName = saveImage('input_image', $input_id);
|
||
|
|
$query = "UPDATE main_mandant SET
|
||
|
|
image = '" . $fileName. "'
|
||
|
|
WHERE id = '" . $input_id . "' LIMIT 1";
|
||
|
|
@mysqli_query($GLOBALS['mysql_con'],$query);
|
||
|
|
}
|
||
|
|
if(isset($_FILES) && $_FILES['input_bg_image']['size'] != 0 ){
|
||
|
|
$fileName = saveImage('input_bg_image', $input_id);
|
||
|
|
$query = "UPDATE main_mandant SET
|
||
|
|
bg_image = '" . $fileName. "'
|
||
|
|
WHERE id = '" . $input_id . "' LIMIT 1";
|
||
|
|
@mysqli_query($GLOBALS['mysql_con'],$query);
|
||
|
|
}
|
||
|
|
if(isset($_FILES) && $_FILES['input_favicon']['size'] != 0 ){
|
||
|
|
$fileName = saveImage('input_favicon', $input_id);
|
||
|
|
$query = "UPDATE main_mandant SET
|
||
|
|
favicon = '" . $fileName. "'
|
||
|
|
WHERE id = '" . $input_id . "' LIMIT 1";
|
||
|
|
@mysqli_query($GLOBALS['mysql_con'],$query);
|
||
|
|
}
|
||
|
|
edit_mandant($input_id, $messages);
|
||
|
|
} else {
|
||
|
|
header('EDIT_ERROR: 1');
|
||
|
|
// $input_mandant["id"] = $_POST["input_id"];
|
||
|
|
// $input_mandant["name"] = $_POST["input_name"];
|
||
|
|
// $input_mandant["email"] = $_POST["input_email"];
|
||
|
|
// $input_mandant["login"] = $_POST["input_login"];
|
||
|
|
require_once("edit_mandant_cardform.inc.php");
|
||
|
|
}
|
||
|
|
}
|
||
|
|
function saveImage($filekey, $input_id){
|
||
|
|
$erlaubte_endungen = array("jpg", "jpeg", "gif", "png", "svg", "ico");
|
||
|
|
$filename = strtolower($_FILES[$filekey]['name']);
|
||
|
|
$filename_expl = explode(".", $filename);
|
||
|
|
if (!in_array($filename_expl[count($filename_expl) - 1], $erlaubte_endungen)) {
|
||
|
|
$messages = "<div class='errorbox'>" . $translation->get("error_slideshow_line2") . "</div>\n";
|
||
|
|
$error = TRUE;
|
||
|
|
}
|
||
|
|
|
||
|
|
$saveFilename = $input_id . "_" . time() . "_" . cleanFilename($_FILES[$filekey]['name']);
|
||
|
|
|
||
|
|
require_once("intranet_config.inc.php");
|
||
|
|
move_uploaded_file($_FILES[$filekey]['tmp_name'], PATH_MANDANT . $saveFilename);
|
||
|
|
|
||
|
|
return $saveFilename;
|
||
|
|
}
|
||
|
|
function delete_mandant_image(){
|
||
|
|
$mandant_id = $_POST['input_id'];
|
||
|
|
$query = "SELECT * FROM main_mandant WHERE id =".$mandant_id." LIMIT 1";
|
||
|
|
$result = @mysqli_query($GLOBALS['mysql_con'], $query);
|
||
|
|
$mandant = @mysqli_fetch_array($result);
|
||
|
|
|
||
|
|
if(!empty($mandant)){
|
||
|
|
$filename = $mandant['image'];
|
||
|
|
require_once("intranet_config.inc.php");
|
||
|
|
|
||
|
|
if (file_exists(PATH_MANDANT . $filename)) {
|
||
|
|
unlink(PATH_MANDANT . $filename);
|
||
|
|
}
|
||
|
|
$query = "UPDATE main_mandant SET
|
||
|
|
image = ''
|
||
|
|
WHERE id = '" . $mandant_id . "' LIMIT 1";
|
||
|
|
@mysqli_query($GLOBALS['mysql_con'], $query);
|
||
|
|
}
|
||
|
|
edit_mandant();
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
function import_from_csv() {
|
||
|
|
$csv_file = __DIR__ . "/awouser.csv"; // Pfad zur CSV-Datei
|
||
|
|
if (!file_exists($csv_file)) {
|
||
|
|
die("CSV file not found.");
|
||
|
|
}
|
||
|
|
|
||
|
|
$handle = fopen($csv_file, "r");
|
||
|
|
if (!$handle) {
|
||
|
|
die("Could not open CSV file.");
|
||
|
|
}
|
||
|
|
|
||
|
|
// CSV-Spaltenüberschriften überspringen
|
||
|
|
$headers = fgetcsv($handle, 1000, ";");
|
||
|
|
|
||
|
|
while (($row = fgetcsv($handle, 1000, ";")) !== false) {
|
||
|
|
// CSV-Spalten: Name, Vorname, Mandant, Bereich, Rollen, Fachbereich, Einrichtung
|
||
|
|
$name = trim($row[0]);
|
||
|
|
$vorname = trim($row[1]);
|
||
|
|
$mandant = trim($row[4]);
|
||
|
|
$bereich = trim($row[5]);
|
||
|
|
$rolle = trim($row[7]);
|
||
|
|
$fachbereich = trim($row[6]);
|
||
|
|
$einrichtung = trim($row[8]);
|
||
|
|
|
||
|
|
$full_name = $name . " " . $vorname;
|
||
|
|
$master_mandant_id = $mandant === "Hauptamt" ? 1 : 16;
|
||
|
|
$current_mandant_id = $master_mandant_id;
|
||
|
|
|
||
|
|
// Prüfen, ob main_contact existiert
|
||
|
|
$query = "SELECT id FROM main_contact WHERE name = '" . mysqli_real_escape_string($GLOBALS['mysql_con'], $full_name) . "' LIMIT 1";
|
||
|
|
$result = @mysqli_query($GLOBALS['mysql_con'], $query);
|
||
|
|
$row_contact = mysqli_fetch_assoc($result);
|
||
|
|
|
||
|
|
if ($row_contact) {
|
||
|
|
// Kontakt existiert bereits
|
||
|
|
$main_contact_id = $row_contact['id'];
|
||
|
|
} else {
|
||
|
|
// Neuer Kontakt hinzufügen
|
||
|
|
$query = "INSERT INTO main_contact (name, active, master_mandant_id, current_mandant_id)
|
||
|
|
VALUES ('$full_name', 1, $master_mandant_id, $current_mandant_id)";
|
||
|
|
@mysqli_query($GLOBALS['mysql_con'], $query);
|
||
|
|
$main_contact_id = mysqli_insert_id($GLOBALS['mysql_con']);
|
||
|
|
}
|
||
|
|
|
||
|
|
// Bereich prüfen und ggf. hinzufügen
|
||
|
|
$main_department_id = get_or_create_id("main_department", "description", $bereich);
|
||
|
|
|
||
|
|
// Rolle prüfen und ggf. hinzufügen
|
||
|
|
$main_role_id = get_or_create_id("main_role", "description", $rolle);
|
||
|
|
|
||
|
|
// Fachbereich prüfen und ggf. hinzufügen
|
||
|
|
$main_bereich_id = get_or_create_id("organogramm_space", "description", $fachbereich);
|
||
|
|
|
||
|
|
// Einrichtung prüfen und ggf. hinzufügen
|
||
|
|
$main_einricht_id = get_or_create_id("organigramm_einricht", "description", $einrichtung);
|
||
|
|
|
||
|
|
// Zählen der vorhandenen Einträge für den Kontakt, um den Wert für sorting zu bestimmen
|
||
|
|
$query = "SELECT COUNT(*) as count FROM main_contact_department WHERE main_contact_id = $main_contact_id";
|
||
|
|
$result = @mysqli_query($GLOBALS['mysql_con'], $query);
|
||
|
|
$row_count = mysqli_fetch_assoc($result);
|
||
|
|
$sorting = $row_count['count'] + 1; // Neue Reihenfolge ist Anzahl vorhandener Einträge + 1
|
||
|
|
|
||
|
|
// Prüfen, ob dieser Eintrag in main_contact_department bereits existiert
|
||
|
|
$query = "SELECT id FROM main_contact_department
|
||
|
|
WHERE main_contact_id = $main_contact_id
|
||
|
|
AND main_department_id = $main_department_id
|
||
|
|
AND main_role_id = $main_role_id
|
||
|
|
AND main_bereich_id = $main_bereich_id
|
||
|
|
AND main_einricht_id = $main_einricht_id LIMIT 1";
|
||
|
|
$result = @mysqli_query($GLOBALS['mysql_con'], $query);
|
||
|
|
|
||
|
|
if (!mysqli_fetch_assoc($result)) {
|
||
|
|
// Eintrag hinzufügen, wenn er noch nicht existiert
|
||
|
|
$query = "INSERT INTO main_contact_department (main_contact_id, main_mandant_id, main_department_id, main_role_id, main_bereich_id, main_einricht_id, sorting, active)
|
||
|
|
VALUES ($main_contact_id, $master_mandant_id, $main_department_id, $main_role_id, $main_bereich_id, $main_einricht_id, $sorting, 1)";
|
||
|
|
@mysqli_query($GLOBALS['mysql_con'], $query);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
fclose($handle);
|
||
|
|
echo "CSV Import completed.";
|
||
|
|
}
|
||
|
|
|
||
|
|
function get_or_create_id($table, $column, $value) {
|
||
|
|
// Prüfen, ob der Wert bereits existiert
|
||
|
|
$query = "SELECT id FROM $table WHERE $column = '" . mysqli_real_escape_string($GLOBALS['mysql_con'], $value) . "' LIMIT 1";
|
||
|
|
$result = @mysqli_query($GLOBALS['mysql_con'], $query);
|
||
|
|
|
||
|
|
if ($row = mysqli_fetch_assoc($result)) {
|
||
|
|
return $row['id']; // Existierende ID zurückgeben
|
||
|
|
}
|
||
|
|
|
||
|
|
// Wenn nicht existiert, neuen Eintrag erstellen
|
||
|
|
$query = "INSERT INTO $table ($column) VALUES ('" . mysqli_real_escape_string($GLOBALS['mysql_con'], $value) . "')";
|
||
|
|
@mysqli_query($GLOBALS['mysql_con'], $query);
|
||
|
|
|
||
|
|
return mysqli_insert_id($GLOBALS['mysql_con']); // Neue ID zurückgeben
|
||
|
|
}
|
||
|
|
|
||
|
|
function update_emails_from_old_table() {
|
||
|
|
// Hole alle Einträge aus main_contact_old
|
||
|
|
$query = "SELECT name, email FROM main_contact_old";
|
||
|
|
$result = @mysqli_query($GLOBALS['mysql_con'], $query);
|
||
|
|
|
||
|
|
if (!$result) {
|
||
|
|
die("Error fetching data from main_contact_old.");
|
||
|
|
}
|
||
|
|
|
||
|
|
$updated_count = 0; // Zähler für aktualisierte Einträge
|
||
|
|
$skipped_entries = []; // Liste für übersprungene Einträge
|
||
|
|
|
||
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
||
|
|
$old_name = trim($row['name']);
|
||
|
|
$email = trim($row['email']);
|
||
|
|
|
||
|
|
if (empty($old_name) || empty($email)) {
|
||
|
|
$skipped_entries[] = $row; // Überspringe unvollständige Einträge
|
||
|
|
continue;
|
||
|
|
}
|
||
|
|
|
||
|
|
// Namensteile aufteilen und sortieren
|
||
|
|
$name_parts = explode(" ", $old_name);
|
||
|
|
sort($name_parts); // Teile alphabetisch sortieren
|
||
|
|
$sorted_old_name = implode(" ", $name_parts);
|
||
|
|
|
||
|
|
// Suche nach einem Match in main_contact
|
||
|
|
$query = "SELECT id, name FROM main_contact";
|
||
|
|
$main_contact_result = @mysqli_query($GLOBALS['mysql_con'], $query);
|
||
|
|
|
||
|
|
$found_match = false;
|
||
|
|
|
||
|
|
while ($main_contact_row = mysqli_fetch_assoc($main_contact_result)) {
|
||
|
|
$main_contact_name = trim($main_contact_row['name']);
|
||
|
|
$contact_id = $main_contact_row['id'];
|
||
|
|
|
||
|
|
// Namen aus main_contact sortieren
|
||
|
|
$contact_name_parts = explode(" ", $main_contact_name);
|
||
|
|
sort($contact_name_parts);
|
||
|
|
$sorted_main_contact_name = implode(" ", $contact_name_parts);
|
||
|
|
|
||
|
|
// Prüfen, ob die sortierten Namen übereinstimmen
|
||
|
|
if ($sorted_old_name === $sorted_main_contact_name) {
|
||
|
|
// Update der E-Mail
|
||
|
|
$update_query = "UPDATE main_contact SET email = '" . mysqli_real_escape_string($GLOBALS['mysql_con'], $email) . "'
|
||
|
|
WHERE id = $contact_id";
|
||
|
|
if (@mysqli_query($GLOBALS['mysql_con'], $update_query)) {
|
||
|
|
$updated_count++;
|
||
|
|
} else {
|
||
|
|
$skipped_entries[] = $row; // Fehler beim Update
|
||
|
|
}
|
||
|
|
$found_match = true;
|
||
|
|
break; // Match gefunden, weiter zum nächsten Eintrag
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
if (!$found_match) {
|
||
|
|
$skipped_entries[] = $row; // Kein Match gefunden
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
// Debug-Informationen ausgeben
|
||
|
|
echo "Emails updated successfully from main_contact_old.\n";
|
||
|
|
echo "Total updated: $updated_count\n";
|
||
|
|
echo "Skipped entries: " . count($skipped_entries) . "\n";
|
||
|
|
|
||
|
|
if (!empty($skipped_entries)) {
|
||
|
|
echo "Skipped entries:\n";
|
||
|
|
foreach ($skipped_entries as $entry) {
|
||
|
|
echo "Name: {$entry['name']}, Email: {$entry['email']}\n";
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
function import_email_from_csv() {
|
||
|
|
$csv_file = __DIR__ . "/awousermail.csv"; // Pfad zur CSV-Datei
|
||
|
|
if (!file_exists($csv_file)) {
|
||
|
|
die("CSV file not found.");
|
||
|
|
}
|
||
|
|
|
||
|
|
$handle = fopen($csv_file, "r");
|
||
|
|
if (!$handle) {
|
||
|
|
die("Could not open CSV file.");
|
||
|
|
}
|
||
|
|
|
||
|
|
echo "<table border='1'>";
|
||
|
|
echo "<tr><th>Display Name</th><th>Email</th><th>Status</th><th>Details</th></tr>";
|
||
|
|
|
||
|
|
while (($row = fgetcsv($handle, 1000, ";")) !== false) {
|
||
|
|
$display_name = trim($row[1]);
|
||
|
|
$email = trim($row[3]);
|
||
|
|
|
||
|
|
if (empty($email)) {
|
||
|
|
echo "<tr><td>$display_name</td><td>N/A</td><td>Skipped</td><td>No email provided</td></tr>";
|
||
|
|
continue;
|
||
|
|
}
|
||
|
|
|
||
|
|
// Sonderzeichen normalisieren
|
||
|
|
$normalized_name = str_replace(["ä", "ö", "ü", "ß"], ["ae", "oe", "ue", "ss"], $display_name);
|
||
|
|
|
||
|
|
// Flexible LIKE-Abfrage
|
||
|
|
$query = "
|
||
|
|
SELECT id, name, email
|
||
|
|
FROM main_contact
|
||
|
|
WHERE (name LIKE '%" . mysqli_real_escape_string($GLOBALS['mysql_con'], $display_name) . "%'
|
||
|
|
OR name LIKE '%" . mysqli_real_escape_string($GLOBALS['mysql_con'], $normalized_name) . "%')
|
||
|
|
AND (email IS NULL OR email = '')
|
||
|
|
LIMIT 2"; // Begrenze auf 2, um Mehrfachtreffer zu erkennen
|
||
|
|
$result = @mysqli_query($GLOBALS['mysql_con'], $query);
|
||
|
|
|
||
|
|
$matches = [];
|
||
|
|
while ($row_contact = mysqli_fetch_assoc($result)) {
|
||
|
|
$matches[] = $row_contact;
|
||
|
|
}
|
||
|
|
|
||
|
|
if (count($matches) === 1) {
|
||
|
|
// Exakter Treffer, Email aktualisieren
|
||
|
|
$contact = $matches[0];
|
||
|
|
$main_contact_id = $contact['id'];
|
||
|
|
$db_name = $contact['name'];
|
||
|
|
|
||
|
|
$update_query = "UPDATE main_contact SET email = '" . mysqli_real_escape_string($GLOBALS['mysql_con'], $email) . "' WHERE id = $main_contact_id";
|
||
|
|
if (@mysqli_query($GLOBALS['mysql_con'], $update_query)) {
|
||
|
|
echo "<tr><td>$display_name</td><td>$email</td><td>Updated</td><td>Email updated for contact '$db_name' (ID: $main_contact_id)</td></tr>";
|
||
|
|
} else {
|
||
|
|
echo "<tr><td>$display_name</td><td>$email</td><td>Error</td><td>Failed to update email for contact '$db_name' (ID: $main_contact_id)</td></tr>";
|
||
|
|
}
|
||
|
|
} elseif (count($matches) > 1) {
|
||
|
|
// Mehrere Treffer, protokollieren
|
||
|
|
echo "<tr><td>$display_name</td><td>$email</td><td>Conflict</td><td>Multiple matching contacts found</td></tr>";
|
||
|
|
} else {
|
||
|
|
// Kein Treffer
|
||
|
|
echo "<tr><td>$display_name</td><td>$email</td><td>Not Found</td><td>No matching contact found</td></tr>";
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
fclose($handle);
|
||
|
|
echo "</table>";
|
||
|
|
echo "CSV Email Import completed.";
|
||
|
|
}
|