388 lines
15 KiB
PHP
388 lines
15 KiB
PHP
<?php
|
|
|
|
if (!function_exists('vacationSyncTableExists')) {
|
|
function vacationSyncTableExists(PDO $pdo, string $table): bool
|
|
{
|
|
$stmt = $pdo->prepare(
|
|
"SELECT COUNT(*)
|
|
FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = :table_name"
|
|
);
|
|
$stmt->execute(['table_name' => $table]);
|
|
return (int)$stmt->fetchColumn() > 0;
|
|
}
|
|
}
|
|
|
|
if (!function_exists('vacationSyncTableHasColumn')) {
|
|
function vacationSyncTableHasColumn(PDO $pdo, string $table, string $column): bool
|
|
{
|
|
$stmt = $pdo->prepare(
|
|
"SELECT COUNT(*)
|
|
FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = :table_name
|
|
AND COLUMN_NAME = :column_name"
|
|
);
|
|
$stmt->execute([
|
|
'table_name' => $table,
|
|
'column_name' => $column,
|
|
]);
|
|
return (int)$stmt->fetchColumn() > 0;
|
|
}
|
|
}
|
|
|
|
if (!function_exists('vacationSyncEnsureSchema')) {
|
|
function vacationSyncEnsureSchema(PDO $pdo): void
|
|
{
|
|
$urlaubExists = vacationSyncTableExists($pdo, 'urlaub');
|
|
$companyHolidaysExists = vacationSyncTableExists($pdo, 'company_holidays');
|
|
|
|
if (!$urlaubExists && !$companyHolidaysExists) {
|
|
return;
|
|
}
|
|
|
|
if ($urlaubExists && !vacationSyncTableHasColumn($pdo, 'urlaub', 'company_holiday_id')) {
|
|
$pdo->exec("ALTER TABLE urlaub ADD COLUMN company_holiday_id INT NULL AFTER vertreterurl");
|
|
}
|
|
|
|
if (!$companyHolidaysExists) {
|
|
return;
|
|
}
|
|
|
|
if (!vacationSyncTableHasColumn($pdo, 'company_holidays', 'urlaub_id')) {
|
|
$pdo->exec("ALTER TABLE company_holidays ADD COLUMN urlaub_id INT NULL AFTER created_by");
|
|
}
|
|
|
|
if (!vacationSyncTableHasColumn($pdo, 'company_holidays', 'vertretung')) {
|
|
$pdo->exec("ALTER TABLE company_holidays ADD COLUMN vertretung VARCHAR(255) NOT NULL DEFAULT '' AFTER description");
|
|
}
|
|
|
|
if (!vacationSyncTableHasColumn($pdo, 'company_holidays', 'vertretertelefon')) {
|
|
$pdo->exec("ALTER TABLE company_holidays ADD COLUMN vertretertelefon VARCHAR(255) NOT NULL DEFAULT '' AFTER vertretung");
|
|
}
|
|
|
|
if (!vacationSyncTableHasColumn($pdo, 'company_holidays', 'vertreteradresse')) {
|
|
$pdo->exec("ALTER TABLE company_holidays ADD COLUMN vertreteradresse VARCHAR(1000) NOT NULL DEFAULT '' AFTER vertretertelefon");
|
|
}
|
|
|
|
if (!vacationSyncTableHasColumn($pdo, 'company_holidays', 'vertreterurl')) {
|
|
$pdo->exec("ALTER TABLE company_holidays ADD COLUMN vertreterurl VARCHAR(255) NOT NULL DEFAULT '' AFTER vertreteradresse");
|
|
}
|
|
}
|
|
}
|
|
|
|
if (!function_exists('vacationSyncFindCompanyHolidayIdForUrlaub')) {
|
|
function vacationSyncFindCompanyHolidayIdForUrlaub(PDO $pdo, int $urlaubId): int
|
|
{
|
|
if ($urlaubId <= 0) {
|
|
return 0;
|
|
}
|
|
|
|
if (!vacationSyncTableExists($pdo, 'urlaub') || !vacationSyncTableExists($pdo, 'company_holidays')) {
|
|
return 0;
|
|
}
|
|
|
|
vacationSyncEnsureSchema($pdo);
|
|
|
|
$stmt = $pdo->prepare("SELECT company_holiday_id FROM urlaub WHERE urlaubid = :urlaub_id LIMIT 1");
|
|
$stmt->execute(['urlaub_id' => $urlaubId]);
|
|
$linkedId = (int)($stmt->fetchColumn() ?: 0);
|
|
if ($linkedId > 0) {
|
|
return $linkedId;
|
|
}
|
|
|
|
$stmt = $pdo->prepare("SELECT id FROM company_holidays WHERE urlaub_id = :urlaub_id LIMIT 1");
|
|
$stmt->execute(['urlaub_id' => $urlaubId]);
|
|
$linkedId = (int)($stmt->fetchColumn() ?: 0);
|
|
if ($linkedId > 0) {
|
|
return $linkedId;
|
|
}
|
|
|
|
$stmt = $pdo->prepare("SELECT start, ende FROM urlaub WHERE urlaubid = :urlaub_id LIMIT 1");
|
|
$stmt->execute(['urlaub_id' => $urlaubId]);
|
|
$urlaub = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
if (!$urlaub) {
|
|
return 0;
|
|
}
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT id
|
|
FROM company_holidays
|
|
WHERE start_date = :start_date
|
|
AND end_date = :end_date
|
|
ORDER BY id ASC
|
|
LIMIT 1
|
|
");
|
|
$stmt->execute([
|
|
'start_date' => $urlaub['start'],
|
|
'end_date' => $urlaub['ende'],
|
|
]);
|
|
return (int)($stmt->fetchColumn() ?: 0);
|
|
}
|
|
}
|
|
|
|
if (!function_exists('vacationSyncFindUrlaubIdForCompanyHoliday')) {
|
|
function vacationSyncFindUrlaubIdForCompanyHoliday(PDO $pdo, int $companyHolidayId): int
|
|
{
|
|
if ($companyHolidayId <= 0) {
|
|
return 0;
|
|
}
|
|
|
|
if (!vacationSyncTableExists($pdo, 'urlaub') || !vacationSyncTableExists($pdo, 'company_holidays')) {
|
|
return 0;
|
|
}
|
|
|
|
vacationSyncEnsureSchema($pdo);
|
|
|
|
$stmt = $pdo->prepare("SELECT urlaub_id FROM company_holidays WHERE id = :company_holiday_id LIMIT 1");
|
|
$stmt->execute(['company_holiday_id' => $companyHolidayId]);
|
|
$linkedId = (int)($stmt->fetchColumn() ?: 0);
|
|
if ($linkedId > 0) {
|
|
return $linkedId;
|
|
}
|
|
|
|
$stmt = $pdo->prepare("SELECT urlaubid FROM urlaub WHERE company_holiday_id = :company_holiday_id LIMIT 1");
|
|
$stmt->execute(['company_holiday_id' => $companyHolidayId]);
|
|
$linkedId = (int)($stmt->fetchColumn() ?: 0);
|
|
if ($linkedId > 0) {
|
|
return $linkedId;
|
|
}
|
|
|
|
$stmt = $pdo->prepare("SELECT start_date, end_date FROM company_holidays WHERE id = :company_holiday_id LIMIT 1");
|
|
$stmt->execute(['company_holiday_id' => $companyHolidayId]);
|
|
$holiday = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
if (!$holiday) {
|
|
return 0;
|
|
}
|
|
|
|
$stmt = $pdo->prepare("
|
|
SELECT urlaubid
|
|
FROM urlaub
|
|
WHERE start = :start_date
|
|
AND ende = :end_date
|
|
ORDER BY urlaubid ASC
|
|
LIMIT 1
|
|
");
|
|
$stmt->execute([
|
|
'start_date' => $holiday['start_date'],
|
|
'end_date' => $holiday['end_date'],
|
|
]);
|
|
return (int)($stmt->fetchColumn() ?: 0);
|
|
}
|
|
}
|
|
|
|
if (!function_exists('vacationSyncCompanyHolidayFromUrlaub')) {
|
|
function vacationSyncCompanyHolidayFromUrlaub(PDO $pdo, int $urlaubId, ?int $createdBy = null): int
|
|
{
|
|
if ($urlaubId <= 0) {
|
|
return 0;
|
|
}
|
|
|
|
if (!vacationSyncTableExists($pdo, 'urlaub') || !vacationSyncTableExists($pdo, 'company_holidays')) {
|
|
return 0;
|
|
}
|
|
|
|
vacationSyncEnsureSchema($pdo);
|
|
|
|
$stmtUrlaub = $pdo->prepare("
|
|
SELECT urlaubid, start, ende, vertretung, vertretertelefon, vertreteradresse, vertreterurl, company_holiday_id
|
|
FROM urlaub
|
|
WHERE urlaubid = :urlaub_id
|
|
LIMIT 1
|
|
");
|
|
$stmtUrlaub->execute(['urlaub_id' => $urlaubId]);
|
|
$urlaub = $stmtUrlaub->fetch(PDO::FETCH_ASSOC);
|
|
if (!$urlaub) {
|
|
return 0;
|
|
}
|
|
|
|
$companyHolidayId = (int)($urlaub['company_holiday_id'] ?? 0);
|
|
if ($companyHolidayId <= 0) {
|
|
$companyHolidayId = vacationSyncFindCompanyHolidayIdForUrlaub($pdo, $urlaubId);
|
|
}
|
|
|
|
$description = 'Betriebsurlaub';
|
|
if ($companyHolidayId > 0) {
|
|
$stmtExisting = $pdo->prepare("SELECT description FROM company_holidays WHERE id = :company_holiday_id LIMIT 1");
|
|
$stmtExisting->execute(['company_holiday_id' => $companyHolidayId]);
|
|
$existingDescription = $stmtExisting->fetchColumn();
|
|
if ($existingDescription !== false && trim((string)$existingDescription) !== '') {
|
|
$description = (string)$existingDescription;
|
|
}
|
|
}
|
|
|
|
if ($companyHolidayId > 0) {
|
|
$stmtUpdate = $pdo->prepare("
|
|
UPDATE company_holidays
|
|
SET start_date = :start_date,
|
|
end_date = :end_date,
|
|
vertretung = :vertretung,
|
|
vertretertelefon = :vertretertelefon,
|
|
vertreteradresse = :vertreteradresse,
|
|
vertreterurl = :vertreterurl,
|
|
urlaub_id = :urlaub_id
|
|
WHERE id = :company_holiday_id
|
|
");
|
|
$stmtUpdate->execute([
|
|
'start_date' => $urlaub['start'],
|
|
'end_date' => $urlaub['ende'],
|
|
'vertretung' => (string)$urlaub['vertretung'],
|
|
'vertretertelefon' => (string)$urlaub['vertretertelefon'],
|
|
'vertreteradresse' => (string)$urlaub['vertreteradresse'],
|
|
'vertreterurl' => (string)$urlaub['vertreterurl'],
|
|
'urlaub_id' => $urlaubId,
|
|
'company_holiday_id' => $companyHolidayId,
|
|
]);
|
|
} else {
|
|
$stmtInsert = $pdo->prepare("
|
|
INSERT INTO company_holidays (
|
|
start_date, end_date, description, vertretung, vertretertelefon, vertreteradresse, vertreterurl, created_by, urlaub_id
|
|
)
|
|
VALUES (
|
|
:start_date, :end_date, :description, :vertretung, :vertretertelefon, :vertreteradresse, :vertreterurl, :created_by, :urlaub_id
|
|
)
|
|
");
|
|
$stmtInsert->execute([
|
|
'start_date' => $urlaub['start'],
|
|
'end_date' => $urlaub['ende'],
|
|
'description' => $description,
|
|
'vertretung' => (string)$urlaub['vertretung'],
|
|
'vertretertelefon' => (string)$urlaub['vertretertelefon'],
|
|
'vertreteradresse' => (string)$urlaub['vertreteradresse'],
|
|
'vertreterurl' => (string)$urlaub['vertreterurl'],
|
|
'created_by' => $createdBy,
|
|
'urlaub_id' => $urlaubId,
|
|
]);
|
|
$companyHolidayId = (int)$pdo->lastInsertId();
|
|
}
|
|
|
|
if ($companyHolidayId > 0) {
|
|
$stmtLink = $pdo->prepare("UPDATE urlaub SET company_holiday_id = :company_holiday_id WHERE urlaubid = :urlaub_id");
|
|
$stmtLink->execute([
|
|
'company_holiday_id' => $companyHolidayId,
|
|
'urlaub_id' => $urlaubId,
|
|
]);
|
|
}
|
|
|
|
return $companyHolidayId;
|
|
}
|
|
}
|
|
|
|
if (!function_exists('vacationSyncUrlaubFromCompanyHoliday')) {
|
|
function vacationSyncUrlaubFromCompanyHoliday(PDO $pdo, int $companyHolidayId): int
|
|
{
|
|
if ($companyHolidayId <= 0) {
|
|
return 0;
|
|
}
|
|
|
|
if (!vacationSyncTableExists($pdo, 'company_holidays') || !vacationSyncTableExists($pdo, 'urlaub')) {
|
|
return 0;
|
|
}
|
|
|
|
vacationSyncEnsureSchema($pdo);
|
|
|
|
$stmtHoliday = $pdo->prepare("
|
|
SELECT id, start_date, end_date, vertretung, vertretertelefon, vertreteradresse, vertreterurl, urlaub_id
|
|
FROM company_holidays
|
|
WHERE id = :company_holiday_id
|
|
LIMIT 1
|
|
");
|
|
$stmtHoliday->execute(['company_holiday_id' => $companyHolidayId]);
|
|
$holiday = $stmtHoliday->fetch(PDO::FETCH_ASSOC);
|
|
if (!$holiday) {
|
|
return 0;
|
|
}
|
|
|
|
$urlaubId = (int)($holiday['urlaub_id'] ?? 0);
|
|
if ($urlaubId <= 0) {
|
|
$urlaubId = vacationSyncFindUrlaubIdForCompanyHoliday($pdo, $companyHolidayId);
|
|
}
|
|
|
|
if ($urlaubId > 0) {
|
|
$stmtUpdate = $pdo->prepare("
|
|
UPDATE urlaub
|
|
SET start = :start_date,
|
|
ende = :end_date,
|
|
vertretung = :vertretung,
|
|
vertretertelefon = :vertretertelefon,
|
|
vertreteradresse = :vertreteradresse,
|
|
vertreterurl = :vertreterurl,
|
|
company_holiday_id = :company_holiday_id
|
|
WHERE urlaubid = :urlaub_id
|
|
");
|
|
$stmtUpdate->execute([
|
|
'start_date' => $holiday['start_date'],
|
|
'end_date' => $holiday['end_date'],
|
|
'vertretung' => (string)$holiday['vertretung'],
|
|
'vertretertelefon' => (string)$holiday['vertretertelefon'],
|
|
'vertreteradresse' => (string)$holiday['vertreteradresse'],
|
|
'vertreterurl' => (string)$holiday['vertreterurl'],
|
|
'company_holiday_id' => $companyHolidayId,
|
|
'urlaub_id' => $urlaubId,
|
|
]);
|
|
} else {
|
|
$stmtInsert = $pdo->prepare("
|
|
INSERT INTO urlaub
|
|
(vertretung, start, ende, vertretertelefon, vertreteradresse, vertreterurl, company_holiday_id)
|
|
VALUES
|
|
(:vertretung, :start_date, :end_date, :vertretertelefon, :vertreteradresse, :vertreterurl, :company_holiday_id)
|
|
");
|
|
$stmtInsert->execute([
|
|
'vertretung' => (string)$holiday['vertretung'],
|
|
'start_date' => $holiday['start_date'],
|
|
'end_date' => $holiday['end_date'],
|
|
'vertretertelefon' => (string)$holiday['vertretertelefon'],
|
|
'vertreteradresse' => (string)$holiday['vertreteradresse'],
|
|
'vertreterurl' => (string)$holiday['vertreterurl'],
|
|
'company_holiday_id' => $companyHolidayId,
|
|
]);
|
|
$urlaubId = (int)$pdo->lastInsertId();
|
|
}
|
|
|
|
if ($urlaubId > 0) {
|
|
$stmtLink = $pdo->prepare("UPDATE company_holidays SET urlaub_id = :urlaub_id WHERE id = :company_holiday_id");
|
|
$stmtLink->execute([
|
|
'urlaub_id' => $urlaubId,
|
|
'company_holiday_id' => $companyHolidayId,
|
|
]);
|
|
}
|
|
|
|
return $urlaubId;
|
|
}
|
|
}
|
|
|
|
if (!function_exists('vacationSyncDeleteCompanyHolidayByUrlaub')) {
|
|
function vacationSyncDeleteCompanyHolidayByUrlaub(PDO $pdo, int $urlaubId): void
|
|
{
|
|
if (!vacationSyncTableExists($pdo, 'urlaub') || !vacationSyncTableExists($pdo, 'company_holidays')) {
|
|
return;
|
|
}
|
|
|
|
$companyHolidayId = vacationSyncFindCompanyHolidayIdForUrlaub($pdo, $urlaubId);
|
|
if ($companyHolidayId <= 0) {
|
|
return;
|
|
}
|
|
|
|
$stmt = $pdo->prepare("DELETE FROM company_holidays WHERE id = :company_holiday_id");
|
|
$stmt->execute(['company_holiday_id' => $companyHolidayId]);
|
|
}
|
|
}
|
|
|
|
if (!function_exists('vacationSyncDeleteUrlaubByCompanyHoliday')) {
|
|
function vacationSyncDeleteUrlaubByCompanyHoliday(PDO $pdo, int $companyHolidayId): void
|
|
{
|
|
if (!vacationSyncTableExists($pdo, 'urlaub') || !vacationSyncTableExists($pdo, 'company_holidays')) {
|
|
return;
|
|
}
|
|
|
|
$urlaubId = vacationSyncFindUrlaubIdForCompanyHoliday($pdo, $companyHolidayId);
|
|
if ($urlaubId <= 0) {
|
|
return;
|
|
}
|
|
|
|
$stmt = $pdo->prepare("DELETE FROM urlaub WHERE urlaubid = :urlaub_id");
|
|
$stmt->execute(['urlaub_id' => $urlaubId]);
|
|
}
|
|
}
|