po/Dokumenten-Check
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
feature/externe-referenzen
Dokumenten-Check/Scripts/ORDS REST Services.sql
Philipp Ostmeyer c75f460ec5 feat: Externe Dokument-Referenzen erkennen, herunterladen & mitbewerten 
Nach der Transkription eines Dokuments werden referenzierte externe
Dokumente (AGB, Verhaltenskodex) erkannt, sicher heruntergeladen und ihr
Text dem Hauptdokument fuer die "Summen"-Bewertung angehaengt (Option D).
Die Referenzen werden zusaetzlich als eigene, herunterladbare Zeilen
gespeichert.

- DB: dc_project_documents um parent_document_id, is_reference, source_url
  erweitert (DC_REFERENCES_ALTER.sql)
- ORDS: Dokumentenliste um die Felder erweitert; neue Endpunkte
  POST /documents/:id/references und DELETE /projects/:id/references;
  q-Quote-Parserfehler im ai-costs-Handler ('[]') behoben
- Backend: ReferenceDownloadService (SSRF-sicher via DNS-/Public-IP-
  Validierung, Redirect-Pruefung, Groessen-/Timeout-Limits, jsoup fuer HTML),
  EvaluationService.extractReferenceUrls (LLM-basierte URL-Erkennung),
  Orchestrierung (Discovery in Phase A, Referenz-Skip + combinedText in Phase B)
- Config: dc.references.* in application.properties

Verifiziert an Projekt 144: AGB-PDF/-HTML heruntergeladen, verknuepft,
"Summen"-Bewertung zitiert AGB-Klauseln aus dem Referenzdokument.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
2026-06-22 12:45:55 +02:00

1683 lines
60 KiB
MySQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- =============================================================================
-- ORDS REST Services: Dokumenten-Check
-- Modul: frigosped.dc
-- Base-Path: /api/dc/
-- =============================================================================
-- Voraussetzungen:
-- - Oracle Database 23c mit installiertem ORDS (>= 23.x)
-- - Script wird als Schema-Owner ausgeführt (hat ORDS_METADATA-Rolle)
-- - Datenbankschema bereits angelegt via "Datenmodell DocumentCheck.sql"
--
-- Deployment:
-- sqlplus user/pass@db @"Scripts/ORDS REST Services.sql"
--
-- Das Skript ist idempotent: bestehende Modul-Definition wird gelöscht und
-- neu erstellt. Bestehende Daten bleiben unberührt.
--
-- Endpunkt-Übersicht:
-- 1. GET /api/dc/catalogs/ Alle Fragenkataloge
-- 2. GET /api/dc/catalogs/:id/questions/ Fragen eines Katalogs (flach, nach order_nr sortiert)
-- 3. GET /api/dc/projects/:id Projektdetail (inkl. Detailstatus + ETA)
-- 4. POST /api/dc/projects/:id/start → IN_PROGRESS (setzt processing_started_at)
-- 5. PUT /api/dc/projects/:id/progress Fortschritt + Phase/Doc/Question/ETA
-- 6. POST /api/dc/projects/:id/complete → COMPLETED (setzt current_phase=COMPLETED)
-- 7. GET /api/dc/projects/:id/documents/ Dokument-Liste (kein BLOB)
-- 8. GET /api/dc/documents/:id/file BLOB-Download (PLSQL-Workaround)
-- 9. GET /api/dc/documents/:id/texts OCR-Text + Übersetzung lesen (für Resume)
-- 9b. PUT /api/dc/documents/:id/texts OCR-Text + Übersetzung schreiben
-- 10. POST /api/dc/projects/:id/results Prüfergebnis einfügen
-- 11. DELETE /api/dc/projects/:id/results Alle Ergebnisse löschen
-- =============================================================================
-- =============================================================================
-- BLOCK 1: Modul, Templates und Handler
-- =============================================================================
BEGIN
-- -------------------------------------------------------------------------
-- Idempotenz: Modul löschen falls vorhanden
-- -------------------------------------------------------------------------
BEGIN
ORDS.DELETE_MODULE(p_module_name => 'frigosped.dc');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
-- -------------------------------------------------------------------------
-- Modul definieren
-- -------------------------------------------------------------------------
ORDS.DEFINE_MODULE(
p_module_name => 'frigosped.dc',
p_base_path => '/api/dc/',
p_items_per_page => 0, -- Pagination deaktiviert (Quarkus steuert selbst)
p_status => 'PUBLISHED',
p_comments => 'Dokumenten-Check Backend-API fuer den Quarkus-Verarbeitungsserver'
);
-- ===========================================================================
-- TEMPLATE 1: catalogs/
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'catalogs/',
p_priority => 0,
p_etag_type => 'HASH',
p_comments => 'Alle Fragenkataloge mit Dokumenttyp-Name'
);
-- 1. GET /api/dc/catalogs/
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'catalogs/',
p_method => 'GET',
p_source_type => ORDS.SOURCE_TYPE_COLLECTION_FEED,
p_items_per_page => 0,
p_comments => 'Gibt alle Kataloge mit Dokumenttyp-Name zurueck',
p_source => q'[
SELECT
c.id,
c.name,
c.description,
c.document_type_id,
dt.name AS document_type_name,
c.row_version,
c.created,
c.updated
FROM dc_question_catalogs c
JOIN dc_document_types dt ON dt.id = c.document_type_id
ORDER BY c.name
]'
);
-- ===========================================================================
-- TEMPLATE 2: catalogs/:catalog_id/questions/
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'catalogs/:catalog_id/questions/',
p_priority => 0,
p_etag_type => 'HASH',
p_comments => 'Alle Fragen eines Katalogs (flach mit Kategorie-Info)'
);
-- 2. GET /api/dc/catalogs/:catalog_id/questions/
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'catalogs/:catalog_id/questions/',
p_method => 'GET',
p_source_type => ORDS.SOURCE_TYPE_COLLECTION_FEED,
p_items_per_page => 0,
p_comments => 'Flacher Join: Kategorie + Frage-Spalten, nach Kategorie/ID sortiert',
p_source => q'[
SELECT
q.id AS question_id,
cat.id AS category_id,
cat.name AS category_name,
cat.description AS category_description,
cat.order_nr AS category_order_nr,
q.question_text,
q.evaluation_type,
q.threshold,
q.result_handling,
q.example_0_percent,
q.example_100_percent,
q.order_nr,
q.row_version
FROM dc_questions q
JOIN dc_question_categories cat ON cat.id = q.category_id
WHERE cat.catalog_id = :catalog_id
ORDER BY cat.order_nr, q.order_nr, q.id
]'
);
-- ===========================================================================
-- TEMPLATE 3: projects/:project_id (kein Trailing-Slash = Einzel-Ressource)
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id',
p_priority => 0,
p_etag_type => 'HASH',
p_comments => 'Einzelnes Projekt'
);
-- 3. GET /api/dc/projects/:project_id
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id',
p_method => 'GET',
p_source_type => ORDS.SOURCE_TYPE_COLLECTION_ITEM,
p_items_per_page => 1,
p_comments => 'Projektdetail mit Status-Details und ETA; 404 wenn nicht gefunden',
p_source => q'[
SELECT
p.id,
p.name,
p.description,
p.catalog_id,
p.created_by_user,
p.status,
p.progress,
p.completed_at,
p.notification_email,
p.row_version,
p.created,
p.updated,
p.processing_started_at,
p.current_phase,
p.current_doc_id,
p.current_question_id,
p.estimated_completion_at,
p.notify_ok,
p.notify_hinweis,
p.notify_abweichend
FROM dc_projects p
WHERE p.id = :project_id
]'
);
-- ===========================================================================
-- TEMPLATE 4: projects/:project_id/start
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id/start',
p_priority => 0,
p_comments => 'Projekt-Status auf IN_PROGRESS setzen'
);
-- 4. POST /api/dc/projects/:project_id/start
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id/start',
p_method => 'POST',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => '200 OK; 409 wenn IN_PROGRESS/COMPLETED; 404 wenn Projekt fehlt',
p_source => q'[
DECLARE
v_status dc_projects.status%TYPE;
BEGIN
SELECT status
INTO v_status
FROM dc_projects
WHERE id = :project_id
FOR UPDATE NOWAIT;
IF v_status IN ('IN_PROGRESS', 'COMPLETED') THEN
:status_code := 409;
HTP.P('{"error":"Projekt ist bereits ' || v_status || '",'
|| '"current_status":"' || v_status || '"}');
RETURN;
END IF;
UPDATE dc_projects
SET status = 'IN_PROGRESS',
progress = 0,
processing_started_at = SYSTIMESTAMP,
current_phase = 'OCR',
current_doc_id = NULL,
current_question_id = NULL,
estimated_completion_at = NULL
WHERE id = :project_id;
:status_code := 200;
HTP.P('{"project_id":' || :project_id
|| ',"status":"IN_PROGRESS"}');
EXCEPTION
WHEN NO_DATA_FOUND THEN
:status_code := 404;
HTP.P('{"error":"Projekt nicht gefunden","project_id":' || :project_id || '}');
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- ===========================================================================
-- TEMPLATE 5: projects/:project_id/progress
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id/progress',
p_priority => 0,
p_comments => 'Verarbeitungsfortschritt aktualisieren'
);
-- 5. PUT /api/dc/projects/:project_id/progress
-- Body: {"progress":45,"current_phase":"QUESTIONS","current_doc_id":2,
-- "current_question_id":15,"estimated_completion_at":"2026-04-27T16:30:00"}
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id/progress',
p_method => 'PUT',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => 'Setzt progress (0-100) + Phase/Dokument/Frage/ETA; 400 bei ungueltigem Wert',
p_source => q'[
DECLARE
v_body CLOB;
v_progress NUMBER;
v_phase VARCHAR2(30);
v_doc_id NUMBER;
v_question_id NUMBER;
v_eta_str VARCHAR2(30);
v_eta TIMESTAMP;
BEGIN
v_body := :body_text;
v_progress := TO_NUMBER(JSON_VALUE(v_body, '$.progress'));
v_phase := JSON_VALUE(v_body, '$.current_phase');
v_doc_id := TO_NUMBER(JSON_VALUE(v_body, '$.current_doc_id'));
v_question_id := TO_NUMBER(JSON_VALUE(v_body, '$.current_question_id'));
v_eta_str := JSON_VALUE(v_body, '$.estimated_completion_at');
IF v_progress IS NULL OR v_progress < 0 OR v_progress > 100 THEN
:status_code := 400;
HTP.P('{"error":"progress muss eine Zahl zwischen 0 und 100 sein"}');
RETURN;
END IF;
IF v_eta_str IS NOT NULL THEN
BEGIN
v_eta := TO_TIMESTAMP(v_eta_str, 'YYYY-MM-DD"T"HH24:MI:SS');
EXCEPTION WHEN OTHERS THEN
v_eta := NULL;
END;
END IF;
UPDATE dc_projects
SET progress = v_progress,
current_phase = NVL(v_phase, current_phase),
current_doc_id = v_doc_id,
current_question_id = v_question_id,
estimated_completion_at = NVL(v_eta, estimated_completion_at)
WHERE id = :project_id;
IF SQL%ROWCOUNT = 0 THEN
:status_code := 404;
HTP.P('{"error":"Projekt nicht gefunden","project_id":' || :project_id || '}');
RETURN;
END IF;
:status_code := 200;
HTP.P('{"project_id":' || :project_id
|| ',"progress":' || v_progress || '}');
EXCEPTION
WHEN VALUE_ERROR THEN
:status_code := 400;
HTP.P('{"error":"progress muss eine gueltige Zahl sein"}');
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- ===========================================================================
-- TEMPLATE 6: projects/:project_id/complete
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id/complete',
p_priority => 0,
p_comments => 'Projekt auf COMPLETED setzen'
);
-- 6. POST /api/dc/projects/:project_id/complete
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id/complete',
p_method => 'POST',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => 'Setzt status=COMPLETED, speichert Java-Markdown, sendet pro Dokument ein PDF per E-Mail',
p_source => q'[
DECLARE
v_status dc_projects.status%TYPE;
v_now DATE := SYSDATE;
v_report_md CLOB;
v_doc_pdfs JSON_ARRAY_T;
-- Konvertiert den BLOB-Request-Body (UTF-8 JSON) in einen CLOB
FUNCTION body_to_clob(p_blob IN BLOB) RETURN CLOB IS
v_clob CLOB;
v_dest INTEGER := 1;
v_src INTEGER := 1;
v_lang INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
v_warning INTEGER;
BEGIN
IF p_blob IS NULL OR DBMS_LOB.GETLENGTH(p_blob) = 0 THEN RETURN NULL; END IF;
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
DBMS_LOB.CONVERTTOCLOB(v_clob, p_blob, DBMS_LOB.LOBMAXSIZE,
v_dest, v_src, NLS_CHARSET_ID('AL32UTF8'), v_lang, v_warning);
RETURN v_clob;
END body_to_clob;
-- Dekodiert einen Base64-CLOB in einen BLOB (chunk-weise, 24000 Zeichen pro Schritt)
FUNCTION base64_to_blob(p_b64 IN CLOB) RETURN BLOB IS
v_blob BLOB;
v_raw RAW(32767);
v_len INTEGER;
v_chunk INTEGER := 24000;
v_offset INTEGER := 1;
v_sub VARCHAR2(32767);
BEGIN
DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);
v_len := DBMS_LOB.GETLENGTH(p_b64);
WHILE v_offset <= v_len LOOP
v_sub := DBMS_LOB.SUBSTR(p_b64, v_chunk, v_offset);
v_raw := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(v_sub));
DBMS_LOB.WRITEAPPEND(v_blob, UTL_RAW.LENGTH(v_raw), v_raw);
v_offset := v_offset + v_chunk;
END LOOP;
RETURN v_blob;
END base64_to_blob;
BEGIN
-- JSON-Body parsen: report_markdown + document_pdfs (Array)
DECLARE
v_body_clob CLOB;
v_json JSON_OBJECT_T;
BEGIN
v_body_clob := body_to_clob(:body);
IF v_body_clob IS NOT NULL THEN
v_json := JSON_OBJECT_T.parse(v_body_clob);
v_report_md := v_json.get_clob('report_markdown');
BEGIN
v_doc_pdfs := v_json.get_array('document_pdfs');
EXCEPTION WHEN OTHERS THEN v_doc_pdfs := NULL; END;
DBMS_LOB.FREETEMPORARY(v_body_clob);
END IF;
EXCEPTION WHEN OTHERS THEN NULL;
END;
SELECT status
INTO v_status
FROM dc_projects
WHERE id = :project_id
FOR UPDATE NOWAIT;
IF v_status = 'COMPLETED' THEN
:status_code := 409;
HTP.P('{"error":"Projekt ist bereits COMPLETED","project_id":' || :project_id || '}');
RETURN;
END IF;
UPDATE dc_projects
SET status = 'COMPLETED',
completed_at = v_now,
progress = 100,
current_phase = 'COMPLETED',
current_doc_id = NULL,
current_question_id = NULL,
estimated_completion_at = NULL
WHERE id = :project_id;
IF v_report_md IS NOT NULL AND DBMS_LOB.GETLENGTH(v_report_md) > 0 THEN
-- Kombinierten Markdown-Bericht speichern
UPDATE dc_projects SET report_markdown = v_report_md WHERE id = :project_id;
-- E-Mail mit je einem PDF-Anhang pro Dokument versenden
BEGIN
DECLARE
v_email dc_projects.notification_email%TYPE;
v_name dc_projects.name%TYPE;
v_done dc_projects.completed_at%TYPE;
v_mail_id NUMBER;
v_ok_cnt NUMBER := 0;
v_warn_cnt NUMBER := 0;
v_dev_cnt NUMBER := 0;
v_total NUMBER := 0;
C_OK CONSTANT VARCHAR2(10 CHAR) := UNISTR('\2705');
C_NOK CONSTANT VARCHAR2(10 CHAR) := UNISTR('\274C');
C_WARN CONSTANT VARCHAR2(10 CHAR) := UNISTR('\26A0\FE0F');
CRLF CONSTANT VARCHAR2(2) := CHR(13)||CHR(10);
v_body VARCHAR2(4000);
v_doc_obj JSON_OBJECT_T;
v_pdf_b64 CLOB;
v_pdf_blob BLOB;
v_fname VARCHAR2(500);
BEGIN
SELECT notification_email, name, completed_at
INTO v_email, v_name, v_done
FROM dc_projects WHERE id = :project_id;
IF v_email IS NULL OR TRIM(v_email) IS NULL THEN
NULL; -- keine Empfängeradresse → abbrechen
ELSE
SELECT COUNT(*),
SUM(CASE WHEN deviation = 1 THEN 1 ELSE 0 END),
SUM(CASE WHEN warning = 1 AND deviation = 0 THEN 1 ELSE 0 END)
INTO v_total, v_dev_cnt, v_warn_cnt
FROM dc_results WHERE project_id = :project_id;
v_ok_cnt := v_total - v_dev_cnt - v_warn_cnt;
v_body :=
'Sehr geehrte Damen und Herren,' || CRLF ||
CRLF ||
'die automatische Dokumentenprüfung für das Projekt' || CRLF ||
'„' || v_name || '"' || CRLF ||
'wurde am ' || TO_CHAR(NVL(v_done, SYSDATE), 'DD.MM.YYYY') ||
' erfolgreich abgeschlossen.' || CRLF ||
CRLF ||
'Zusammenfassung der Prüfergebnisse:' || CRLF ||
C_OK || ' OK: ' || v_ok_cnt || CRLF ||
C_WARN || ' Hinweise: ' || v_warn_cnt || CRLF ||
C_NOK || ' Abweichungen: ' || v_dev_cnt || CRLF ||
' Gesamt: ' || v_total || ' Prüfpunkte' || CRLF ||
CRLF ||
'Die Prüfberichte der einzelnen Dokumente finden Sie als PDF-Anhänge.' || CRLF ||
CRLF ||
'Mit freundlichen Grüßen' || CRLF ||
'Ihr Dokumenten-Check-System' || CRLF ||
'Frigosped Express GmbH';
v_mail_id := APEX_MAIL.SEND(
p_to => v_email,
p_from => DC_UTILS_PKG.c_mail_from,
p_subj => 'Prüfbericht: ' || v_name,
p_body => v_body
);
-- Einen PDF-Anhang pro Dokument hinzufügen
IF v_doc_pdfs IS NOT NULL AND v_doc_pdfs.get_size > 0 THEN
FOR i IN 0 .. v_doc_pdfs.get_size - 1 LOOP
BEGIN
v_doc_obj := TREAT(v_doc_pdfs.get(i) AS JSON_OBJECT_T);
v_fname := v_doc_obj.get_string('filename');
v_pdf_b64 := v_doc_obj.get_clob('pdf_base64');
IF v_pdf_b64 IS NOT NULL AND DBMS_LOB.GETLENGTH(v_pdf_b64) > 0 THEN
v_pdf_blob := base64_to_blob(v_pdf_b64);
APEX_MAIL.ADD_ATTACHMENT(
p_mail_id => v_mail_id,
p_attachment => v_pdf_blob,
p_filename => NVL(v_fname, 'Pruefbericht.pdf'),
p_mime_type => 'application/pdf'
);
DBMS_LOB.FREETEMPORARY(v_pdf_blob);
DBMS_LOB.FREETEMPORARY(v_pdf_b64);
END IF;
EXCEPTION WHEN OTHERS THEN NULL;
END;
END LOOP;
END IF;
END IF;
EXCEPTION WHEN OTHERS THEN NULL;
END;
END;
ELSE
-- Fallback: Bericht in DB generieren (z.B. bei leerem Body)
BEGIN
DC_UTILS_PKG.generate_report(:project_id);
EXCEPTION WHEN OTHERS THEN NULL;
END;
END IF;
:status_code := 200;
HTP.P('{"project_id":' || :project_id
|| ',"status":"COMPLETED"'
|| ',"completed_at":"' || TO_CHAR(v_now, 'YYYY-MM-DD"T"HH24:MI:SS') || '"'
|| '}');
EXCEPTION
WHEN NO_DATA_FOUND THEN
:status_code := 404;
HTP.P('{"error":"Projekt nicht gefunden","project_id":' || :project_id || '}');
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- ===========================================================================
-- TEMPLATE 12: projects/:project_id/report → Bericht generieren / abrufen
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id/report',
p_priority => 0,
p_comments => 'Markdown-Bericht eines Projekts'
);
-- 12. POST /api/dc/projects/:project_id/report – (neu) generieren
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id/report',
p_method => 'POST',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => 'Generiert report_markdown neu (z.B. nach Konfigurationsänderung)',
p_source => q'[
BEGIN
DC_UTILS_PKG.generate_report(:project_id);
:status_code := 200;
HTP.P('{"project_id":' || :project_id || ',"report_generated":true}');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- ===========================================================================
-- TEMPLATE 7: projects/:project_id/documents/
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id/documents/',
p_priority => 0,
p_etag_type => 'HASH',
p_comments => 'Dokument-Liste eines Projekts (ohne BLOB-Payload)'
);
-- 7. GET /api/dc/projects/:project_id/documents/
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id/documents/',
p_method => 'GET',
p_source_type => ORDS.SOURCE_TYPE_COLLECTION_FEED,
p_items_per_page => 0,
p_comments => 'Metadaten aller Dokumente; has_original_text/has_translated_text als 0/1',
p_source => q'[
SELECT
d.id,
d.filename,
d.mime_type,
d.document_type_id,
d.catalog_id,
d.uploaded_at,
d.status,
d.progress,
d.parent_document_id,
NVL(d.is_reference, 0) AS is_reference,
d.source_url,
CASE WHEN d.original_text IS NOT NULL THEN 1 ELSE 0 END AS has_original_text,
CASE WHEN d.translated_text IS NOT NULL THEN 1 ELSE 0 END AS has_translated_text,
d.row_version,
d.created,
d.updated
FROM dc_project_documents d
WHERE d.project_id = :project_id
ORDER BY d.uploaded_at
]'
);
-- ===========================================================================
-- TEMPLATE 8: documents/:doc_id/file
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'documents/:doc_id/file',
p_priority => 0,
p_comments => 'BLOB-Download: original_file mit korrektem Content-Type'
);
-- 8. GET /api/dc/documents/:doc_id/file
-- PLSQL-Workaround: SOURCE_TYPE_MEDIA ruft getString() auf BLOB auf → ORA-17004 in dieser ORDS-Version
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'documents/:doc_id/file',
p_method => 'GET',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => 'Streamt original_file BLOB mit korrektem Content-Type (PLSQL-Workaround fuer ORDS-Bug)',
p_source => q'[
DECLARE
v_blob BLOB;
v_mime VARCHAR2(200);
v_filename VARCHAR2(500);
BEGIN
SELECT original_file, mime_type, filename
INTO v_blob, v_mime, v_filename
FROM dc_project_documents
WHERE id = :doc_id;
IF v_blob IS NULL THEN
:status_code := 404;
HTP.P('{"error":"Datei nicht vorhanden","doc_id":' || :doc_id || '}');
RETURN;
END IF;
OWA_UTIL.MIME_HEADER(NVL(v_mime, 'application/octet-stream'), FALSE);
HTP.P('Content-Disposition: attachment; filename="' || v_filename || '"');
OWA_UTIL.HTTP_HEADER_CLOSE;
WPG_DOCLOAD.DOWNLOAD_FILE(v_blob);
:status_code := 200;
EXCEPTION
WHEN NO_DATA_FOUND THEN
:status_code := 404;
HTP.P('{"error":"Dokument nicht gefunden","doc_id":' || :doc_id || '}');
WHEN OTHERS THEN
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- ===========================================================================
-- TEMPLATE 9: documents/:doc_id/texts (GET + PUT)
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'documents/:doc_id/texts',
p_priority => 0,
p_comments => 'OCR-Ergebnis und Uebersetzung lesen/schreiben'
);
-- 9a. GET /api/dc/documents/:doc_id/texts (fuer Resume nach Phase A)
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'documents/:doc_id/texts',
p_method => 'GET',
p_source_type => ORDS.SOURCE_TYPE_COLLECTION_ITEM,
p_items_per_page => 1,
p_comments => 'Gibt original_text und translated_text zurueck',
p_source => q'[
SELECT
d.id,
d.original_text,
d.translated_text,
CASE WHEN d.original_text IS NOT NULL THEN 1 ELSE 0 END AS has_original_text,
CASE WHEN d.translated_text IS NOT NULL THEN 1 ELSE 0 END AS has_translated_text
FROM dc_project_documents d
WHERE d.id = :doc_id
]'
);
-- 9b. PUT /api/dc/documents/:doc_id/texts
-- Body: {"original_text": "## ...", "translated_text": "## ..."}
-- Beide Felder sind optional: NULL-Wert ueberschreibt nicht (nur wenn im Body vorhanden)
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'documents/:doc_id/texts',
p_method => 'PUT',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => 'Schreibt original_text und/oder translated_text als CLOB',
p_source => q'[
DECLARE
v_body CLOB;
v_original_text CLOB;
v_translated CLOB;
v_rows INTEGER;
BEGIN
v_body := :body_text;
-- RETURNING CLOB erlaubt Werte groesser als 32767 Bytes (wichtig fuer OCR-Output)
v_original_text := JSON_VALUE(v_body, '$.original_text' RETURNING CLOB);
v_translated := JSON_VALUE(v_body, '$.translated_text' RETURNING CLOB);
UPDATE dc_project_documents
SET original_text = CASE
WHEN JSON_EXISTS(v_body, '$.original_text')
THEN v_original_text
ELSE original_text
END,
translated_text = CASE
WHEN JSON_EXISTS(v_body, '$.translated_text')
THEN v_translated
ELSE translated_text
END
WHERE id = :doc_id;
v_rows := SQL%ROWCOUNT;
IF v_rows = 0 THEN
:status_code := 404;
HTP.P('{"error":"Dokument nicht gefunden","doc_id":' || :doc_id || '}');
RETURN;
END IF;
:status_code := 200;
HTP.P('{"doc_id":' || :doc_id || ',"updated":true}');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- ===========================================================================
-- TEMPLATE 9c: documents/:doc_id/progress (PUT)
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'documents/:doc_id/progress',
p_priority => 0,
p_comments => 'Dokument-Fortschritt und -Status schreiben'
);
-- 9c. PUT /api/dc/documents/:doc_id/progress
-- Body: {"progress":45,"status":"IN_PROGRESS"}
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'documents/:doc_id/progress',
p_method => 'PUT',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => 'Setzt progress (0-100) und/oder status des Dokuments',
p_source => q'[
DECLARE
v_body CLOB;
v_progress NUMBER;
v_status VARCHAR2(20);
v_rows INTEGER;
BEGIN
v_body := :body_text;
v_progress := TO_NUMBER(JSON_VALUE(v_body, '$.progress'));
v_status := JSON_VALUE(v_body, '$.status');
IF v_progress IS NOT NULL AND (v_progress < 0 OR v_progress > 100) THEN
:status_code := 400;
HTP.P('{"error":"progress muss zwischen 0 und 100 liegen"}');
RETURN;
END IF;
UPDATE dc_project_documents
SET progress = CASE WHEN JSON_EXISTS(v_body, '$.progress') THEN v_progress ELSE progress END,
status = CASE WHEN JSON_EXISTS(v_body, '$.status') THEN v_status ELSE status END
WHERE id = :doc_id;
v_rows := SQL%ROWCOUNT;
IF v_rows = 0 THEN
:status_code := 404;
HTP.P('{"error":"Dokument nicht gefunden","doc_id":' || :doc_id || '}');
RETURN;
END IF;
:status_code := 200;
HTP.P('{"doc_id":' || :doc_id || ',"progress":' || NVL(v_progress, -1)
|| ',"status":"' || NVL(v_status, '') || '"}');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- ===========================================================================
-- TEMPLATE 10+11: projects/:project_id/results
-- (POST = Einfuegen, DELETE = Alle loeschen)
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id/results',
p_priority => 0,
p_comments => 'Prüfergebnisse: POST einfuegen, DELETE alle loeschen'
);
-- 10. POST /api/dc/projects/:project_id/results
-- Body: {
-- "question_id": 5,
-- "doc_id": 101,
-- "answer": "Ja, §305 BGB ist erfuellt.",
-- "score": 85,
-- "result_type": "OK",
-- "deviation": 0,
-- "warning": 0,
-- "the_comment": "Klare Einbeziehungsklausel vorhanden"
-- }
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id/results',
p_method => 'POST',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => '201 Created mit Location-Header; 400 bei fehlendem question_id/doc_id/result_type',
p_source => q'[
DECLARE
v_body CLOB;
v_question_id NUMBER;
v_doc_id NUMBER;
v_answer CLOB;
v_score NUMBER;
v_result_type VARCHAR2(20);
v_deviation NUMBER;
v_warning NUMBER;
v_comment CLOB;
v_new_id NUMBER;
BEGIN
v_body := :body_text;
v_question_id := TO_NUMBER(JSON_VALUE(v_body, '$.question_id'));
v_doc_id := TO_NUMBER(JSON_VALUE(v_body, '$.doc_id'));
v_answer := JSON_VALUE(v_body, '$.answer' RETURNING CLOB);
v_score := TO_NUMBER(JSON_VALUE(v_body, '$.score'));
v_result_type := JSON_VALUE(v_body, '$.result_type');
v_deviation := NVL(TO_NUMBER(JSON_VALUE(v_body, '$.deviation')), 0);
v_warning := NVL(TO_NUMBER(JSON_VALUE(v_body, '$.warning')), 0);
v_comment := JSON_VALUE(v_body, '$.the_comment' RETURNING CLOB);
-- Pflichtfelder prüfen
IF v_question_id IS NULL OR v_doc_id IS NULL THEN
:status_code := 400;
HTP.P('{"error":"question_id und doc_id sind Pflichtfelder"}');
RETURN;
END IF;
IF v_result_type NOT IN ('OK', 'UNKLAR', 'NOK') THEN
:status_code := 400;
HTP.P('{"error":"result_type muss OK, UNKLAR oder NOK sein",'
|| '"received":"' || NVL(v_result_type, 'NULL') || '"}');
RETURN;
END IF;
INSERT INTO dc_results (
project_id,
question_id,
doc_id,
answer,
score,
result_type,
deviation,
warning,
the_comment,
evaluated_at
) VALUES (
:project_id,
v_question_id,
v_doc_id,
v_answer,
v_score,
v_result_type,
v_deviation,
v_warning,
v_comment,
SYSDATE
)
RETURNING id INTO v_new_id;
:status_code := 201;
HTP.P('{"id":' || v_new_id
|| ',"project_id":' || :project_id
|| ',"question_id":' || v_question_id
|| ',"doc_id":' || v_doc_id
|| ',"result_type":"' || v_result_type || '"'
|| ',"score":' || NVL(TO_CHAR(v_score), 'null')
|| '}');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- 11. DELETE /api/dc/projects/:project_id/results
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id/results',
p_method => 'DELETE',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => 'Loescht alle Ergebnisse eines Projekts (fuer Re-Processing)',
p_source => q'[
DECLARE
v_deleted INTEGER;
BEGIN
DELETE FROM dc_results
WHERE project_id = :project_id;
v_deleted := SQL%ROWCOUNT;
:status_code := 200;
HTP.P('{"project_id":' || :project_id
|| ',"deleted_count":' || v_deleted || '}');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- ===========================================================================
-- TEMPLATE 13: catalogs/ – POST (Katalog anlegen)
-- Ergaenzt das bestehende catalogs/-Template um eine Schreib-Operation.
-- ===========================================================================
-- 13. POST /api/dc/catalogs/
-- Body: {"name":"ADSp 2017","description":"...","document_type_id":1}
-- Legt den Katalog mit generation_status=GENERATING an (async Flow).
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'catalogs/',
p_method => 'POST',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => '201 Created mit neuer catalog_id; 400 bei fehlendem name/document_type_id',
p_source => q'[
DECLARE
v_body CLOB;
v_name VARCHAR2(100);
v_desc CLOB;
v_dt_id NUMBER;
v_new_id NUMBER;
BEGIN
v_body := :body_text;
v_name := JSON_VALUE(v_body, '$.name');
v_desc := JSON_VALUE(v_body, '$.description' RETURNING CLOB);
v_dt_id := TO_NUMBER(JSON_VALUE(v_body, '$.document_type_id'));
IF v_name IS NULL OR v_dt_id IS NULL THEN
:status_code := 400;
HTP.P('{"error":"name und document_type_id sind Pflichtfelder"}');
RETURN;
END IF;
INSERT INTO dc_question_catalogs (name, description, document_type_id, generation_status)
VALUES (v_name, v_desc, v_dt_id, 'GENERATING')
RETURNING id INTO v_new_id;
:status_code := 201;
HTP.P('{"id":' || v_new_id
|| ',"name":"' || REPLACE(v_name, '"', '''') || '"'
|| ',"document_type_id":' || v_dt_id
|| ',"generation_status":"GENERATING"}');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- ===========================================================================
-- TEMPLATE 16: catalogs/:catalog_id/generation-status (GET + PUT)
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'catalogs/:catalog_id/generation-status',
p_priority => 0,
p_comments => 'Status der asynchronen Katalog-Generierung lesen/schreiben'
);
-- 16a. GET /api/dc/catalogs/:catalog_id/generation-status
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'catalogs/:catalog_id/generation-status',
p_method => 'GET',
p_source_type => ORDS.SOURCE_TYPE_COLLECTION_ITEM,
p_items_per_page => 1,
p_comments => 'Gibt generation_status und generation_error zurueck',
p_source => q'[
SELECT
c.id AS catalog_id,
c.name,
c.description,
c.generation_status,
c.generation_error
FROM dc_question_catalogs c
WHERE c.id = :catalog_id
]'
);
-- 16b. PUT /api/dc/catalogs/:catalog_id/generation-status
-- Body: {"generation_status":"READY"} oder {"generation_status":"ERROR","generation_error":"..."}
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'catalogs/:catalog_id/generation-status',
p_method => 'PUT',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => 'Setzt generation_status (GENERATING|READY|ERROR) und optionalen Fehlertext',
p_source => q'[
DECLARE
v_body CLOB;
v_status VARCHAR2(20);
v_error VARCHAR2(4000);
v_rows INTEGER;
BEGIN
v_body := :body_text;
v_status := JSON_VALUE(v_body, '$.generation_status');
v_error := JSON_VALUE(v_body, '$.generation_error');
IF v_status NOT IN ('GENERATING', 'READY', 'ERROR') THEN
:status_code := 400;
HTP.P('{"error":"generation_status muss GENERATING, READY oder ERROR sein"}');
RETURN;
END IF;
UPDATE dc_question_catalogs
SET generation_status = v_status,
generation_error = v_error
WHERE id = :catalog_id;
v_rows := SQL%ROWCOUNT;
IF v_rows = 0 THEN
:status_code := 404;
HTP.P('{"error":"Katalog nicht gefunden","catalog_id":' || :catalog_id || '}');
RETURN;
END IF;
:status_code := 200;
HTP.P('{"catalog_id":' || :catalog_id
|| ',"generation_status":"' || v_status || '"'
|| ',"updated":true}');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- ===========================================================================
-- TEMPLATE 14: catalogs/:catalog_id/categories/ (POST)
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'catalogs/:catalog_id/categories/',
p_priority => 0,
p_comments => 'Fragenkategorien eines Katalogs anlegen'
);
-- 14. POST /api/dc/catalogs/:catalog_id/categories/
-- Body: {"name":"Haftung","description":"...","order_nr":1}
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'catalogs/:catalog_id/categories/',
p_method => 'POST',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => '201 Created mit neuer category_id; 400 bei fehlendem name',
p_source => q'[
DECLARE
v_body CLOB;
v_name VARCHAR2(100);
v_desc CLOB;
v_order NUMBER;
v_new_id NUMBER;
BEGIN
v_body := :body_text;
v_name := JSON_VALUE(v_body, '$.name');
v_desc := JSON_VALUE(v_body, '$.description' RETURNING CLOB);
v_order := NVL(TO_NUMBER(JSON_VALUE(v_body, '$.order_nr')), 0);
IF v_name IS NULL THEN
:status_code := 400;
HTP.P('{"error":"name ist ein Pflichtfeld"}');
RETURN;
END IF;
INSERT INTO dc_question_categories (catalog_id, name, description, order_nr)
VALUES (:catalog_id, v_name, v_desc, v_order)
RETURNING id INTO v_new_id;
:status_code := 201;
HTP.P('{"id":' || v_new_id
|| ',"catalog_id":' || :catalog_id
|| ',"name":"' || REPLACE(v_name, '"', '''') || '"'
|| ',"order_nr":' || v_order || '}');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- ===========================================================================
-- TEMPLATE 15: categories/:category_id/questions/ (POST)
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'categories/:category_id/questions/',
p_priority => 0,
p_comments => 'Fragen einer Kategorie anlegen'
);
-- 15. POST /api/dc/categories/:category_id/questions/
-- Body: {
-- "question_text": "...",
-- "evaluation_type": "SCORE",
-- "threshold": 70,
-- "result_handling": "ABWEICHUNG",
-- "example_0_percent": "...",
-- "example_100_percent": "...",
-- "order_nr": 1
-- }
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'categories/:category_id/questions/',
p_method => 'POST',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => '201 Created mit neuer question_id; 400 bei fehlendem question_text',
p_source => q'[
DECLARE
v_body CLOB;
v_text CLOB;
v_eval_type VARCHAR2(20);
v_threshold NUMBER;
v_handling VARCHAR2(20);
v_ex0 CLOB;
v_ex100 CLOB;
v_order NUMBER;
v_new_id NUMBER;
BEGIN
v_body := :body_text;
v_text := JSON_VALUE(v_body, '$.question_text' RETURNING CLOB);
v_eval_type := NVL(JSON_VALUE(v_body, '$.evaluation_type'), 'SCORE');
v_threshold := NVL(TO_NUMBER(JSON_VALUE(v_body, '$.threshold')), 70);
v_handling := NVL(JSON_VALUE(v_body, '$.result_handling'), 'ABWEICHUNG');
v_ex0 := JSON_VALUE(v_body, '$.example_0_percent' RETURNING CLOB);
v_ex100 := JSON_VALUE(v_body, '$.example_100_percent' RETURNING CLOB);
v_order := NVL(TO_NUMBER(JSON_VALUE(v_body, '$.order_nr')), 0);
IF v_text IS NULL THEN
:status_code := 400;
HTP.P('{"error":"question_text ist ein Pflichtfeld"}');
RETURN;
END IF;
IF v_eval_type NOT IN ('SCORE', 'BINARY') THEN
:status_code := 400;
HTP.P('{"error":"evaluation_type muss SCORE oder BINARY sein"}');
RETURN;
END IF;
IF v_handling NOT IN ('ABWEICHUNG', 'HINWEIS') THEN
:status_code := 400;
HTP.P('{"error":"result_handling muss ABWEICHUNG oder HINWEIS sein"}');
RETURN;
END IF;
INSERT INTO dc_questions (
category_id,
question_text,
evaluation_type,
threshold,
result_handling,
example_0_percent,
example_100_percent,
order_nr
) VALUES (
:category_id,
v_text,
v_eval_type,
v_threshold,
v_handling,
v_ex0,
v_ex100,
v_order
)
RETURNING id INTO v_new_id;
:status_code := 201;
HTP.P('{"id":' || v_new_id
|| ',"category_id":' || :category_id
|| ',"evaluation_type":"' || v_eval_type || '"'
|| ',"threshold":' || v_threshold
|| ',"order_nr":' || v_order || '}');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- ===========================================================================
-- TEMPLATE 17: ai-costs (POST = Eintrag speichern, GET = Auswertung)
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'ai-costs',
p_priority => 0,
p_comments => 'KI-Kostenprotokoll: POST speichert Aufruf, GET liefert Auswertung'
);
-- 17a. POST /api/dc/ai-costs
-- Body: {"provider":"mistral","model_name":"mistral-small-latest","operation":"TRANSLATE",
-- "project_id":42,"catalog_id":null,
-- "prompt_tokens":1234,"completion_tokens":567,"total_tokens":1801,"cost_eur":0.000157}
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'ai-costs',
p_method => 'POST',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => '201 Created mit neuer cost_log id',
p_source => q'[
DECLARE
v_body CLOB;
v_new_id NUMBER;
BEGIN
v_body := :body_text;
INSERT INTO dc_ai_cost_log (
provider, model_name, operation,
project_id, catalog_id,
prompt_tokens, completion_tokens, total_tokens,
cost_eur
) VALUES (
JSON_VALUE(v_body, '$.provider'),
JSON_VALUE(v_body, '$.model_name'),
JSON_VALUE(v_body, '$.operation'),
TO_NUMBER(JSON_VALUE(v_body, '$.project_id')),
TO_NUMBER(JSON_VALUE(v_body, '$.catalog_id')),
NVL(TO_NUMBER(JSON_VALUE(v_body, '$.prompt_tokens')), 0),
NVL(TO_NUMBER(JSON_VALUE(v_body, '$.completion_tokens')), 0),
NVL(TO_NUMBER(JSON_VALUE(v_body, '$.total_tokens')), 0),
NVL(TO_NUMBER(JSON_VALUE(v_body, '$.cost_eur')), 0)
)
RETURNING id INTO v_new_id;
:status_code := 201;
HTP.P('{"id":' || v_new_id || '}');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- 17b. GET /api/dc/ai-costs
-- Liefert: Gesamtkosten, laufender Monat, Monats-Aufschlüsselung (12 Monate)
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'ai-costs',
p_method => 'GET',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => 'Kostenauswertung: total, current_month, monatliche Aufschluesselung via DC_COSTS_PKG',
p_source => q'[
DECLARE
v_total_eur NUMBER;
v_total_tokens NUMBER;
v_total_calls NUMBER;
v_month_eur NUMBER;
v_month_tokens NUMBER;
v_month_calls NUMBER;
v_monthly_json CLOB;
BEGIN
DC_COSTS_PKG.get_summary(
v_total_eur, v_total_tokens, v_total_calls,
v_month_eur, v_month_tokens, v_month_calls
);
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'month' VALUE month,
'cost_eur' VALUE ROUND(cost_eur, 6),
'prompt_tokens' VALUE prompt_tokens,
'completion_tokens' VALUE completion_tokens,
'total_tokens' VALUE total_tokens,
'call_count' VALUE call_count
)
ORDER BY month DESC
RETURNING CLOB
)
INTO v_monthly_json
FROM (
SELECT
TO_CHAR(called_at, 'YYYY-MM') AS month,
SUM(cost_eur) AS cost_eur,
SUM(prompt_tokens) AS prompt_tokens,
SUM(completion_tokens) AS completion_tokens,
SUM(total_tokens) AS total_tokens,
COUNT(*) AS call_count
FROM dc_ai_cost_log
WHERE called_at >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -11)
GROUP BY TO_CHAR(called_at, 'YYYY-MM')
);
:status_code := 200;
HTP.P(
'{"total":{'
|| '"cost_eur":' || ROUND(NVL(v_total_eur, 0), 6)
|| ',"total_tokens":'|| NVL(v_total_tokens, 0)
|| ',"call_count":' || NVL(v_total_calls, 0)
|| '},"current_month":{'
|| '"month":"' || TO_CHAR(SYSDATE, 'YYYY-MM') || '"'
|| ',"cost_eur":' || ROUND(NVL(v_month_eur, 0), 6)
|| ',"total_tokens":'|| NVL(v_month_tokens, 0)
|| ',"call_count":' || NVL(v_month_calls, 0)
|| '},"monthly":' || NVL(v_monthly_json, '[' || CHR(93))
|| '}'
);
EXCEPTION
WHEN OTHERS THEN
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- ===========================================================================
-- TEMPLATE 18: documents/:parent_id/references (POST)
-- Legt eine heruntergeladene externe Referenz als neue dc_project_documents-
-- Zeile an (is_reference=1, verknuepft mit dem Originaldokument).
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'documents/:parent_id/references',
p_priority => 0,
p_comments => 'Heruntergeladene externe Referenz zu einem Dokument speichern'
);
-- 18. POST /api/dc/documents/:parent_id/references
-- Body: {"source_url":"https://...","filename":"AGB.pdf",
-- "mime_type":"application/pdf","file_base64":"JVBERi0x..."}
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'documents/:parent_id/references',
p_method => 'POST',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => '201 Created mit neuer doc-id; legt is_reference=1-Zeile mit BLOB an',
p_source => q'[
DECLARE
v_body_clob CLOB;
v_json JSON_OBJECT_T;
v_source_url VARCHAR2(2000);
v_filename VARCHAR2(255);
v_mime VARCHAR2(100);
v_file_b64 CLOB;
v_blob BLOB;
v_project_id NUMBER;
v_doc_type_id NUMBER;
v_new_id NUMBER;
-- Wandelt den (ggf. grossen) Request-BLOB in einen CLOB
FUNCTION body_to_clob(p_body IN BLOB) RETURN CLOB IS
v_clob CLOB;
v_dest INTEGER := 1;
v_src INTEGER := 1;
v_lang INTEGER := 0;
v_warning INTEGER := 0;
BEGIN
IF p_body IS NULL OR DBMS_LOB.GETLENGTH(p_body) = 0 THEN
RETURN NULL;
END IF;
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
DBMS_LOB.CONVERTTOCLOB(v_clob, p_body, DBMS_LOB.LOBMAXSIZE,
v_dest, v_src, NLS_CHARSET_ID('AL32UTF8'), v_lang, v_warning);
RETURN v_clob;
END body_to_clob;
-- Dekodiert einen Base64-CLOB in einen BLOB (chunk-weise)
FUNCTION base64_to_blob(p_b64 IN CLOB) RETURN BLOB IS
v_blob BLOB;
v_raw RAW(32767);
v_chunk INTEGER := 24000;
v_offset INTEGER := 1;
v_len INTEGER;
v_sub VARCHAR2(32767);
BEGIN
DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);
v_len := DBMS_LOB.GETLENGTH(p_b64);
WHILE v_offset <= v_len LOOP
v_sub := DBMS_LOB.SUBSTR(p_b64, v_chunk, v_offset);
v_raw := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(v_sub));
DBMS_LOB.WRITEAPPEND(v_blob, UTL_RAW.LENGTH(v_raw), v_raw);
v_offset := v_offset + v_chunk;
END LOOP;
RETURN v_blob;
END base64_to_blob;
BEGIN
v_body_clob := body_to_clob(:body);
IF v_body_clob IS NULL THEN
:status_code := 400;
HTP.P('{"error":"Leerer Request-Body"}');
RETURN;
END IF;
v_json := JSON_OBJECT_T.parse(v_body_clob);
v_source_url := v_json.get_string('source_url');
v_filename := v_json.get_string('filename');
v_mime := v_json.get_string('mime_type');
BEGIN
v_file_b64 := v_json.get_clob('file_base64');
EXCEPTION WHEN OTHERS THEN v_file_b64 := NULL; END;
DBMS_LOB.FREETEMPORARY(v_body_clob);
IF v_file_b64 IS NULL OR DBMS_LOB.GETLENGTH(v_file_b64) = 0 THEN
:status_code := 400;
HTP.P('{"error":"file_base64 ist Pflichtfeld"}');
RETURN;
END IF;
-- Projekt + Dokumenttyp vom Originaldokument uebernehmen
SELECT project_id, document_type_id
INTO v_project_id, v_doc_type_id
FROM dc_project_documents
WHERE id = :parent_id;
v_blob := base64_to_blob(v_file_b64);
INSERT INTO dc_project_documents (
project_id,
document_type_id,
parent_document_id,
is_reference,
source_url,
original_file,
mime_type,
filename,
status,
progress,
uploaded_at
) VALUES (
v_project_id,
v_doc_type_id,
:parent_id,
1,
v_source_url,
v_blob,
NVL(v_mime, 'application/octet-stream'),
NVL(v_filename, 'referenz'),
'IN_PROGRESS',
0,
SYSDATE
)
RETURNING id INTO v_new_id;
:status_code := 201;
HTP.P('{"id":' || v_new_id
|| ',"parent_document_id":' || :parent_id
|| ',"project_id":' || v_project_id || '}');
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
:status_code := 404;
HTP.P('{"error":"Originaldokument nicht gefunden","parent_id":' || :parent_id || '}');
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
-- ===========================================================================
-- TEMPLATE 19: projects/:project_id/references (DELETE)
-- Loescht alle heruntergeladenen Referenzen eines Projekts (Re-Processing).
-- ===========================================================================
ORDS.DEFINE_TEMPLATE(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id/references',
p_priority => 0,
p_comments => 'Heruntergeladene Referenzen eines Projekts verwalten'
);
-- 19. DELETE /api/dc/projects/:project_id/references
ORDS.DEFINE_HANDLER(
p_module_name => 'frigosped.dc',
p_pattern => 'projects/:project_id/references',
p_method => 'DELETE',
p_source_type => ORDS.SOURCE_TYPE_PLSQL,
p_comments => 'Loescht alle is_reference=1-Zeilen eines Projekts (fuer Re-Processing)',
p_source => q'[
DECLARE
v_deleted INTEGER;
BEGIN
DELETE FROM dc_project_documents
WHERE project_id = :project_id
AND is_reference = 1;
v_deleted := SQL%ROWCOUNT;
:status_code := 200;
HTP.P('{"project_id":' || :project_id
|| ',"deleted_count":' || v_deleted || '}');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
:status_code := 500;
HTP.P('{"error":"' || REPLACE(SQLERRM, '"', '''') || '"}');
END;
]'
);
COMMIT;
END;
/
-- =============================================================================
-- BLOCK 2: Auth / Privilege (fuer Produktion)
-- =============================================================================
-- In der Entwicklung kann dieser Block auskommentiert bleiben – das Modul
-- ist dann ohne Auth-Pruefung erreichbar (STATUS = PUBLISHED genuegt).
--
-- Fuer Produktion einkommentieren und danach den OAuth2-Client anlegen:
--
-- SELECT client_id, client_secret
-- FROM user_ords_clients
-- WHERE name = 'quarkus_dc_backend';
--
-- Den client_id/client_secret in die Quarkus application.properties eintragen.
-- =============================================================================
/*
BEGIN
-- Rolle anlegen (falls noch nicht vorhanden)
BEGIN
ORDS.CREATE_ROLE(p_role_name => 'dc_api_user');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
-- Privilege loeschen falls vorhanden
BEGIN
ORDS.DROP_PRIVILEGE(p_name => 'dc.api.privilege');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
-- Privilege definieren: mappt Rolle auf alle /api/dc/-Endpunkte
ORDS.DEFINE_PRIVILEGE(
p_privilege_name => 'dc.api.privilege',
p_roles => ORDS_TYPES.T_ORDS_VARCHARS('dc_api_user'),
p_patterns => ORDS_TYPES.T_ORDS_VARCHARS('/api/dc/*'),
p_module_name => 'frigosped.dc',
p_label => 'Dokumenten-Check API',
p_description => 'Zugriff auf alle /api/dc/-Endpunkte',
p_comments => 'Wird dem OAuth2-Client quarkus_dc_backend zugewiesen'
);
COMMIT;
END;
/
-- OAuth2-Client fuer den Quarkus-Server (einmalig ausfuehren)
BEGIN
OAUTH.CREATE_CLIENT(
p_name => 'quarkus_dc_backend',
p_grant_type => 'CLIENT_CREDENTIALS',
p_owner => 'Frigosped',
p_description => 'Quarkus-Verarbeitungsserver Service-Account',
p_redirect_uri => NULL,
p_support_email => 'admin@frigosped.de',
p_privilege_names => 'dc.api.privilege'
);
COMMIT;
END;
/
*/
-- =============================================================================
-- Test-Aufrufe (curl)
-- =============================================================================
-- Ersetze <BASE> und <SCHEMA> mit deinen ORDS-Werten, z.B.:
-- BASE="https://apex.example.com/ords/FRIGOSPED_APP"
--
-- Ohne Auth (Entwicklung):
--
-- # 1. Alle Kataloge
-- curl -sS "$BASE/api/dc/catalogs/" | jq .
--
-- # 2. Fragen fuer Katalog 1
-- curl -sS "$BASE/api/dc/catalogs/1/questions/" | jq .
--
-- # 3. Projektdetail
-- curl -sS "$BASE/api/dc/projects/42" | jq .
--
-- # 4. Projekt starten
-- curl -sS -X POST "$BASE/api/dc/projects/42/start" | jq .
--
-- # 5. Fortschritt setzen
-- curl -sS -X PUT -H "Content-Type: application/json" \
-- -d '{"progress":35}' "$BASE/api/dc/projects/42/progress" | jq .
--
-- # 6. Projekt abschliessen
-- curl -sS -X POST "$BASE/api/dc/projects/42/complete" | jq .
--
-- # 7. Dokumente auflisten
-- curl -sS "$BASE/api/dc/projects/42/documents/" | jq .
--
-- # 8. Datei herunterladen
-- curl -sS -o original.pdf "$BASE/api/dc/documents/101/file"
--
-- # 9. OCR-Text + Uebersetzung zurueckschreiben
-- curl -sS -X PUT -H "Content-Type: application/json" \
-- -d '{"original_text":"## Vertrag\n\nAbsatz 1...",
-- "translated_text":"## Vertrag (DE)\n\nAbsatz 1..."}' \
-- "$BASE/api/dc/documents/101/texts" | jq .
--
-- # 10. Ergebnis einfuegen
-- curl -sS -X POST -H "Content-Type: application/json" \
-- -d '{"question_id":5,"doc_id":101,"answer":"Ja, §305 erfuellt.",
-- "score":85,"result_type":"OK","deviation":0,"warning":0,
-- "the_comment":"Klare Einbeziehungsklausel vorhanden"}' \
-- "$BASE/api/dc/projects/42/results" | jq .
--
-- # 11. Alle Ergebnisse loeschen (Re-Processing)
-- curl -sS -X DELETE "$BASE/api/dc/projects/42/results" | jq .
-- =============================================================================
Reference in New Issue View Git Blame Copy Permalink