From 5983dcb1515b27acb5e8fed4b77e0ccea5f31287 Mon Sep 17 00:00:00 2001 From: "Ole B. Rosentreter" Date: Thu, 14 Aug 2025 10:22:23 +0200 Subject: [PATCH] Bugfixes: - ics jetzt RFC-4454 kompatibel Features: - Einzel-ics - uuid als DB-Feld - Webservice-Upload in DAViCal --- ticket-db/schema.sql | 12 +- ticket-web/src/webui/domain.php | 2 +- ticket-web/src/webui/index.php | 2 +- ticket-web/src/webui/issue.php | 4 + .../src/webui/lib/util/util_functions.php | 183 +++++++++++++++++- ticket-web/src/webui/newissue.php | 11 +- ticket-web/src/webui/project.php | 2 +- 7 files changed, 206 insertions(+), 10 deletions(-) diff --git a/ticket-db/schema.sql b/ticket-db/schema.sql index ae4f835..0bcdc3d 100644 --- a/ticket-db/schema.sql +++ b/ticket-db/schema.sql @@ -83,7 +83,8 @@ create table dt_issue ( fk_project integer, fk_user integer not null, fk_assignee integer not null, - fk_status integer not null + fk_status integer not null, + uuid varchar not null ); alter table only dt_issue @@ -331,7 +332,8 @@ select da.id as user_id, di.id as issue_id, di.edited, - di.created + di.created, + di.uuid from dt_issue di join dt_domain dd on @@ -378,7 +380,8 @@ select di.effort as aufwand, di.executiondate as geplant, di.fk_status as status, - di.keywords + di.keywords, + di.uuid from dt_issue di join dt_domain dd on @@ -417,7 +420,8 @@ select du.name_user as author, da.name_user as assignee, di.fk_status as status, - di.keywords + di.keywords, + di.uuid from dt_issue di join dt_domain dd on diff --git a/ticket-web/src/webui/domain.php b/ticket-web/src/webui/domain.php index cef7b48..71f8963 100644 --- a/ticket-web/src/webui/domain.php +++ b/ticket-web/src/webui/domain.php @@ -11,7 +11,7 @@ require_once 'lib/header.php'; if (isset($_POST) && count(array_keys($_POST)) > 0) { - $issue = handleQuickUpdate($_POST, $connect_dbms_handle, $current_user); + $issue = handleQuickUpdate($_POST, $connect_dbms_handle, $current_user, $page); //echo $issue; $domain = getDomainIdOfIssue($connect_dbms_handle, $issue); //echo $domain; diff --git a/ticket-web/src/webui/index.php b/ticket-web/src/webui/index.php index 065d020..ab1130d 100755 --- a/ticket-web/src/webui/index.php +++ b/ticket-web/src/webui/index.php @@ -13,7 +13,7 @@ require_once 'lib/header.php'; if (isset($_POST) && count(array_keys($_POST)) > 0) { - handleQuickUpdate($_POST, $connect_dbms_handle, $current_user); + handleQuickUpdate($_POST, $connect_dbms_handle, $current_user, $page); } diff --git a/ticket-web/src/webui/issue.php b/ticket-web/src/webui/issue.php index 735b354..ea12706 100644 --- a/ticket-web/src/webui/issue.php +++ b/ticket-web/src/webui/issue.php @@ -24,6 +24,7 @@ if (isset($_GET)) { } } if (isset($_POST)) { + $issue = -1; if (isset($_POST['change'])) { //echo "ja: POST['change']"; @@ -111,6 +112,9 @@ if (isset($_POST)) { $comments_arr = getData($connect_dbms_handle, generateSelectWhereOrder(array("*"), "ticket.view_comments_users", array("fk_issue_of_comment=".$issue), array("erstellt asc"))); //var_dump($comments_arr); } + //echo $issue; + make_single_ics($connect_dbms_handle, $current_user, $issue); + } else { ?> diff --git a/ticket-web/src/webui/lib/util/util_functions.php b/ticket-web/src/webui/lib/util/util_functions.php index 34fb3af..34ececc 100644 --- a/ticket-web/src/webui/lib/util/util_functions.php +++ b/ticket-web/src/webui/lib/util/util_functions.php @@ -68,7 +68,7 @@ function checkDeadlineAfterExecutionDate ($newissue_executiondate, $newissue_dea return true; } -function handleQuickUpdate ($ref_POST, $connect_dbms_handle, $current_user) { +function handleQuickUpdate ($ref_POST, $connect_dbms_handle, $current_user, $page) { $update_arr = array(); $issue = 0; @@ -81,7 +81,7 @@ function handleQuickUpdate ($ref_POST, $connect_dbms_handle, $current_user) { } $issue = $issue / count(array_keys($ref_POST)); - echo $issue; + //echo $issue; $issue_deadline = $ref_POST['issue_deadline_'.$issue]; if ($issue_deadline != "" && strlen($issue_deadline) > 0) { @@ -120,6 +120,185 @@ function handleQuickUpdate ($ref_POST, $connect_dbms_handle, $current_user) { $sqllog_result = getBooleanData($connect_dbms_handle, $sqllog); //echo $sqllog_result; + //make_ics($connect_dbms_handle, $current_user); + //make_multi_ics($connect_dbms_handle, $current_user); + make_single_ics($connect_dbms_handle, $current_user, $issue); + return $issue; } + +function make_single_ics ($connect_dbms_handle, $current_user, $ref_issue) { + + $selectFromViewTodoShortOverview = "SELECT * FROM ticket.view_todo_short_overview WHERE id=".$ref_issue.";"; + $selectFromDtStatus = "SELECT * FROM ticket.dt_status;"; + + $issues = getData($connect_dbms_handle, $selectFromViewTodoShortOverview); + + $stati_arr = getData($connect_dbms_handle, $selectFromDtStatus); + + $eol = "\r\n"; + $eol_descr = "\\n"; + + $issue = $issues[0]; + //var_dump($issues); + //foreach ($issues as $issue) { + + $ics_content = + 'BEGIN:VCALENDAR'.$eol. + 'VERSION:2.0'.$eol. + //'X-WR-CALNAME:ticket.laktatnebel.de'.$eol. + 'PRODID:webcal://ticket.laktatnebel.de/dl/'.$eol. + 'CALSCALE:GREGORIAN'.$eol; + + $ics_content .= 'BEGIN:VEVENT'.$eol; + + $ical_start = $issue[8]; + //echo strtotime($datum_data); + if (strtotime($ical_start) == strtotime("31.12.9999 00:00:00")) { + return; + } + $ics_content .= 'DTSTART:'.make_ics_timestamp(strtotime($ical_start)).$eol; + + $aufwand_data = $issue[7]; + $ical_end = make_ics_timestamp(strtotime($ical_start) + strtotime($aufwand_data) - strtotime(date("Y-m-d"))); + $ics_content .= 'DTEND:'.make_ics_timestamp(strtotime($ical_end)).$eol; + + $ical_create = make_ics_timestamp(time()); + $ics_content .= 'DTSTAMP:'.$ical_create.$eol; + + $titel = $issue[2]; + $ics_content .= 'SUMMARY:'.$titel.$eol; + + $id = $issue[0]; + $ics_content .= 'URL;VALUE=URI:https://ticket.laktatnebel.de/issue.php?issuedetail='.$id.$eol; + + + + $wichtigkeit = " Wichtigkeit: ".$issue[4].$eol_descr; + $dringlichkeit = " Dringlichkeit: ".$issue[5].$eol_descr; + $status = " Status: ".$issue[9].$eol_descr; + $keywords = " Keywords: ".$issue[10].$eol_descr; + $domain = " Domain: ".$issue[1].$eol_descr; + $project = " Projekt: ".getProjectNameOfIssue($connect_dbms_handle, $id).$eol_descr; + + $descr = $wichtigkeit.$dringlichkeit.$status.$keywords.$domain.$project; + + $comments_arr = getData($connect_dbms_handle, generateSelectWhereOrder(array("*"), "ticket.view_comments_users", array("fk_issue_of_comment=".$id), array("erstellt asc"))); + foreach ($comments_arr as $comment) { + $text = str_replace("\r\n", "\n", $comment[2]); + $text = str_replace("\n", $eol_descr, $text); + $author = $comment[3]; + $edited = $comment[5]; + + $descr .= " ".make_rfc5545_compliant($text."(".$author.", ".$edited.")"); + } + + $ics_content .= 'DESCRIPTION:'.$descr.$eol; + + $uuid = $issue[11]; + + $ics_content .= 'UID:'.$uuid.$eol; + + $ics_content .= 'END:VEVENT'.$eol; + $ics_content .= 'END:VCALENDAR'; + + + make_ical_file ($ics_content, $uuid); + + // Beispielaufruf + $icalFile = "dl/".$uuid.".ics"; // Lokale iCal-Datei + $davicalUrl = "https://cal.laktatnebel.de/caldav.php/tickets/calendar/".$uuid; // Ziel-URL in DAViCal + $username = "oleb"; // DAViCal-Benutzername + $password = "859im"; // DAViCal-Passwort + + importIcalToDavical($icalFile, $davicalUrl, $username, $password); + + + unlink('dl/'.$uuid.'.ics'); + + //} +} + + +function make_rfc5545_compliant ($ref_string) { + + $max_len = 73; + + // Teilt den Text in Wörter auf + $words = explode(' ', $ref_string); + $return_str = ""; + $currentLine = ""; + + foreach ($words as $word) { + // Überprüfen, ob das Hinzufügen des'dl/'.$file_name.'.ics' nächsten Wortes die Zeilenlänge überschreitet + if (strlen($currentLine) + strlen($word) + 1 > $max_len) { + // Wenn ja, füge die aktuelle Zeile zum umgebrochenen Text hinzu + $return_str .= " ".rtrim($currentLine) . "\r\n"; + $currentLine = ""; // Setze die aktuelle Zeile zurück + } + // Füge das Wort zur aktuellen Zeile hinzu + $currentLine .= $word." "; + } + + // Füge die letzte Zeile hinzu, falls sie nicht leer ist + if (!empty($currentLine)) { + $return_str .= " ".rtrim($currentLine); + } + + return $return_str; +} + +function make_ical_file ($content, $file_name) { + $ical = fopen('dl/'.$file_name.'.ics', 'w') or die ('Datei kann nicht gespeichert werden!'); + + fwrite($ical, $content); + + fclose($ical); +} + + +function make_ics_timestamp ($tmstmp) { + return strftime("%Y%m%dT%H%M%S", $tmstmp); +} + + +function importIcalToDavical($icalFile, $davicalUrl, $username, $password) { + // Lese die iCal-Datei ein + $icalData = file_get_contents($icalFile); + if ($icalData === false) { + die("Fehler: iCal-Datei konnte nicht gelesen werden."); + } + + // cURL-Initialisierung + $ch = curl_init(); + curl_setopt($ch, CURLOPT_URL, $davicalUrl); + curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); + curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "PUT"); + curl_setopt($ch, CURLOPT_POSTFIELDS, $icalData); + curl_setopt($ch, CURLOPT_USERPWD, "$username:$password"); + curl_setopt($ch, CURLOPT_HTTPHEADER, [ + "Content-Type: text/calendar", + ]); + + // Request ausführen + $response = curl_exec($ch); + $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE); + + // Fehlerbehandlung + /* + if ($httpCode === 201 || $httpCode === 204) { + echo "iCal-Datei erfolgreich importiert/aktualisiert!"; + } else { + echo "Fehler beim Import: HTTP-Code $httpCode"; + if (curl_error($ch)) { + echo " | cURL-Fehler: " . curl_error($ch); + } + } + */ + + curl_close($ch); +} + + + ?> \ No newline at end of file diff --git a/ticket-web/src/webui/newissue.php b/ticket-web/src/webui/newissue.php index 4c780f6..90e830d 100644 --- a/ticket-web/src/webui/newissue.php +++ b/ticket-web/src/webui/newissue.php @@ -53,6 +53,8 @@ if (isset($_POST)) { $newissue_aufwand_h = $newissue_aufwand_min = "00"; } + $newissue_uuid = vsprintf( '%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex(random_bytes(16)), 4) ); + $insertArray = array("DEFAULT", "'".htmlentities($newissue_titel)."'", $newissue_importance, @@ -67,7 +69,9 @@ if (isset($_POST)) { $newissue_project, $newissue_user, $newissue_assignee, - $newissue_status); + $newissue_status, + "'".$newissue_uuid."'" + ); $sqlinsert = generateInsertSchemaReturn("ticket", "dt_issue", $insertArray, " RETURNING id"); //echo $sqlinsert; @@ -94,6 +98,11 @@ if (isset($_POST)) { $sqllog = generateInsertSchema("ticket", "dt_sqllog", array("DEFAULT", "now()", "'".addslashes(str_replace("'", "\"", $sqlinsert))."'", $current_user, "'".uniqid()."'", "'".$page."'")); $sqllog_result = getBooleanData($connect_dbms_handle, $sqllog); //echo $sqllog_result; + + //make_ics($connect_dbms_handle, $current_user); + //make_multi_ics($connect_dbms_handle, $current_user); + make_single_ics($connect_dbms_handle, $current_user, $sqlinsert_result_id); + } else { echo "unzulässige Eingaben!"; } diff --git a/ticket-web/src/webui/project.php b/ticket-web/src/webui/project.php index 6db0268..451d635 100644 --- a/ticket-web/src/webui/project.php +++ b/ticket-web/src/webui/project.php @@ -11,7 +11,7 @@ require_once 'lib/header.php'; if (isset($_POST) && count(array_keys($_POST)) > 0) { - $issue = handleQuickUpdate($_POST, $connect_dbms_handle, $current_user); + $issue = handleQuickUpdate($_POST, $connect_dbms_handle, $current_user, $page); //echo $issue; $project = getProjectIdOfIssue($connect_dbms_handle, $issue); //echo $project; -- 2.39.5