po/Dokumenten-Check
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
feature/externe-referenzen
Dokumenten-Check/Scripts/deploy_complete_handler.sql

218 lines
12 KiB
MySQL
Raw Permalink Normal View History

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
-- =============================================================================
-- Deploys only the POST /api/dc/projects/:project_id/complete handler.
-- Uses CLOB construction to avoid SQLcl bind-variable pre-processing of
-- :status_code / :body / :project_id inside the handler source string.
-- =============================================================================
DECLARE
v_src CLOB;
PROCEDURE a(p IN VARCHAR2) IS
BEGIN DBMS_LOB.APPEND(v_src, TO_CLOB(p)); END;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_src, TRUE);
a(' DECLARE');
a(CHR(10)||' v_status dc_projects.status%TYPE;');
a(CHR(10)||' v_now DATE := SYSDATE;');
a(CHR(10)||' v_report_md CLOB;');
a(CHR(10)||' v_doc_pdfs JSON_ARRAY_T;');
a(CHR(10));
a(CHR(10)||' FUNCTION body_to_clob(p_blob IN BLOB) RETURN CLOB IS');
a(CHR(10)||' v_clob CLOB;');
a(CHR(10)||' v_dest INTEGER := 1;');
a(CHR(10)||' v_src INTEGER := 1;');
a(CHR(10)||' v_lang INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;');
a(CHR(10)||' v_warning INTEGER;');
a(CHR(10)||' BEGIN');
a(CHR(10)||' IF p_blob IS NULL OR DBMS_LOB.GETLENGTH(p_blob) = 0 THEN RETURN NULL; END IF;');
a(CHR(10)||' DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);');
a(CHR(10)||' DBMS_LOB.CONVERTTOCLOB(v_clob, p_blob, DBMS_LOB.LOBMAXSIZE,');
a(CHR(10)||' v_dest, v_src, NLS_CHARSET_ID(''AL32UTF8''), v_lang, v_warning);');
a(CHR(10)||' RETURN v_clob;');
a(CHR(10)||' END body_to_clob;');
a(CHR(10));
a(CHR(10)||' FUNCTION base64_to_blob(p_b64 IN CLOB) RETURN BLOB IS');
a(CHR(10)||' v_blob BLOB;');
a(CHR(10)||' v_raw RAW(32767);');
a(CHR(10)||' v_len INTEGER;');
a(CHR(10)||' v_chunk INTEGER := 24000;');
a(CHR(10)||' v_offset INTEGER := 1;');
a(CHR(10)||' v_sub VARCHAR2(32767);');
a(CHR(10)||' BEGIN');
a(CHR(10)||' DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);');
a(CHR(10)||' v_len := DBMS_LOB.GETLENGTH(p_b64);');
a(CHR(10)||' WHILE v_offset <= v_len LOOP');
a(CHR(10)||' v_sub := DBMS_LOB.SUBSTR(p_b64, v_chunk, v_offset);');
a(CHR(10)||' v_raw := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(v_sub));');
a(CHR(10)||' DBMS_LOB.WRITEAPPEND(v_blob, UTL_RAW.LENGTH(v_raw), v_raw);');
a(CHR(10)||' v_offset := v_offset + v_chunk;');
a(CHR(10)||' END LOOP;');
a(CHR(10)||' RETURN v_blob;');
a(CHR(10)||' END base64_to_blob;');
a(CHR(10));
a(CHR(10)||' BEGIN');
a(CHR(10)||' DECLARE');
a(CHR(10)||' v_body_clob CLOB;');
a(CHR(10)||' v_json JSON_OBJECT_T;');
a(CHR(10)||' BEGIN');
-- Use concatenation to embed the bind variable names so SQLcl doesn't pre-process them
a(CHR(10)||' v_body_clob := body_to_clob(' || CHR(58) || 'body);');
a(CHR(10)||' IF v_body_clob IS NOT NULL THEN');
a(CHR(10)||' v_json := JSON_OBJECT_T.parse(v_body_clob);');
a(CHR(10)||' v_report_md := v_json.get_clob(''report_markdown'');');
a(CHR(10)||' BEGIN');
a(CHR(10)||' v_doc_pdfs := v_json.get_array(''document_pdfs'');');
a(CHR(10)||' EXCEPTION WHEN OTHERS THEN v_doc_pdfs := NULL; END;');
a(CHR(10)||' DBMS_LOB.FREETEMPORARY(v_body_clob);');
a(CHR(10)||' END IF;');
a(CHR(10)||' EXCEPTION WHEN OTHERS THEN NULL;');
a(CHR(10)||' END;');
a(CHR(10));
a(CHR(10)||' SELECT status');
a(CHR(10)||' INTO v_status');
a(CHR(10)||' FROM dc_projects');
a(CHR(10)||' WHERE id = ' || CHR(58) || 'project_id');
a(CHR(10)||' FOR UPDATE NOWAIT;');
a(CHR(10));
a(CHR(10)||' IF v_status = ''COMPLETED'' THEN');
a(CHR(10)||' ' || CHR(58) || 'status_code := 409;');
a(CHR(10)||' HTP.P(''{"error":"Projekt ist bereits COMPLETED","project_id":'' || ' || CHR(58) || 'project_id || ''}'');');
a(CHR(10)||' RETURN;');
a(CHR(10)||' END IF;');
a(CHR(10));
a(CHR(10)||' UPDATE dc_projects');
a(CHR(10)||' SET status = ''COMPLETED'',');
a(CHR(10)||' completed_at = v_now,');
a(CHR(10)||' progress = 100,');
a(CHR(10)||' current_phase = ''COMPLETED'',');
a(CHR(10)||' current_doc_id = NULL,');
a(CHR(10)||' current_question_id = NULL,');
a(CHR(10)||' estimated_completion_at = NULL');
a(CHR(10)||' WHERE id = ' || CHR(58) || 'project_id;');
a(CHR(10));
a(CHR(10)||' IF v_report_md IS NOT NULL AND DBMS_LOB.GETLENGTH(v_report_md) > 0 THEN');
a(CHR(10)||' UPDATE dc_projects SET report_markdown = v_report_md WHERE id = ' || CHR(58) || 'project_id;');
a(CHR(10));
a(CHR(10)||' BEGIN');
a(CHR(10)||' DECLARE');
a(CHR(10)||' v_email dc_projects.notification_email%TYPE;');
a(CHR(10)||' v_name dc_projects.name%TYPE;');
a(CHR(10)||' v_done dc_projects.completed_at%TYPE;');
a(CHR(10)||' v_mail_id NUMBER;');
a(CHR(10)||' v_ok_cnt NUMBER := 0;');
a(CHR(10)||' v_warn_cnt NUMBER := 0;');
a(CHR(10)||' v_dev_cnt NUMBER := 0;');
a(CHR(10)||' v_total NUMBER := 0;');
a(CHR(10)||' C_OK CONSTANT VARCHAR2(10 CHAR) := UNISTR(''\2705'');');
a(CHR(10)||' C_NOK CONSTANT VARCHAR2(10 CHAR) := UNISTR(''\274C'');');
a(CHR(10)||' C_WARN CONSTANT VARCHAR2(10 CHAR) := UNISTR(''\26A0\FE0F'');');
a(CHR(10)||' CRLF CONSTANT VARCHAR2(2) := CHR(13)||CHR(10);');
a(CHR(10)||' v_body VARCHAR2(4000);');
a(CHR(10)||' v_doc_obj JSON_OBJECT_T;');
a(CHR(10)||' v_pdf_b64 CLOB;');
a(CHR(10)||' v_pdf_blob BLOB;');
a(CHR(10)||' v_fname VARCHAR2(500);');
a(CHR(10)||' BEGIN');
a(CHR(10)||' SELECT notification_email, name, completed_at');
a(CHR(10)||' INTO v_email, v_name, v_done');
a(CHR(10)||' FROM dc_projects WHERE id = ' || CHR(58) || 'project_id;');
a(CHR(10));
a(CHR(10)||' IF v_email IS NOT NULL AND TRIM(v_email) IS NOT NULL THEN');
a(CHR(10)||' SELECT COUNT(*),');
a(CHR(10)||' SUM(CASE WHEN deviation = 1 THEN 1 ELSE 0 END),');
a(CHR(10)||' SUM(CASE WHEN warning = 1 AND deviation = 0 THEN 1 ELSE 0 END)');
a(CHR(10)||' INTO v_total, v_dev_cnt, v_warn_cnt');
a(CHR(10)||' FROM dc_results WHERE project_id = ' || CHR(58) || 'project_id;');
a(CHR(10)||' v_ok_cnt := v_total - v_dev_cnt - v_warn_cnt;');
a(CHR(10));
a(CHR(10)||' v_body :=');
a(CHR(10)||' ''Sehr geehrte Damen und Herren,'' || CRLF ||');
a(CHR(10)||' CRLF ||');
a(CHR(10)||' ''die automatische Dokumentenprüfung für das Projekt'' || CRLF ||');
a(CHR(10)||' ''„'' || v_name || ''"'' || CRLF ||');
a(CHR(10)||' ''wurde am '' || TO_CHAR(NVL(v_done, SYSDATE), ''DD.MM.YYYY'') ||');
a(CHR(10)||' '' erfolgreich abgeschlossen.'' || CRLF ||');
a(CHR(10)||' CRLF ||');
a(CHR(10)||' ''Zusammenfassung der Prüfergebnisse:'' || CRLF ||');
a(CHR(10)||' C_OK || '' OK: '' || v_ok_cnt || CRLF ||');
a(CHR(10)||' C_WARN || '' Hinweise: '' || v_warn_cnt || CRLF ||');
a(CHR(10)||' C_NOK || '' Abweichungen: '' || v_dev_cnt || CRLF ||');
a(CHR(10)||' '' Gesamt: '' || v_total || '' Prüfpunkte'' || CRLF ||');
a(CHR(10)||' CRLF ||');
a(CHR(10)||' ''Die Prüfberichte der einzelnen Dokumente finden Sie als PDF-Anhänge.'' || CRLF ||');
a(CHR(10)||' CRLF ||');
a(CHR(10)||' ''Mit freundlichen Grüßen'' || CRLF ||');
a(CHR(10)||' ''Ihr Dokumenten-Check-System'' || CRLF ||');
a(CHR(10)||' ''Frigosped Express GmbH'';');
a(CHR(10));
a(CHR(10)||' v_mail_id := APEX_MAIL.SEND(');
a(CHR(10)||' p_to => v_email,');
a(CHR(10)||' p_from => DC_UTILS_PKG.c_mail_from,');
a(CHR(10)||' p_subj => ''Prüfbericht: '' || v_name,');
a(CHR(10)||' p_body => v_body');
a(CHR(10)||' );');
a(CHR(10));
a(CHR(10)||' IF v_doc_pdfs IS NOT NULL AND v_doc_pdfs.get_size > 0 THEN');
a(CHR(10)||' FOR i IN 0 .. v_doc_pdfs.get_size - 1 LOOP');
a(CHR(10)||' BEGIN');
a(CHR(10)||' v_doc_obj := TREAT(v_doc_pdfs.get(i) AS JSON_OBJECT_T);');
a(CHR(10)||' v_fname := v_doc_obj.get_string(''filename'');');
a(CHR(10)||' v_pdf_b64 := v_doc_obj.get_clob(''pdf_base64'');');
a(CHR(10)||' IF v_pdf_b64 IS NOT NULL AND DBMS_LOB.GETLENGTH(v_pdf_b64) > 0 THEN');
a(CHR(10)||' v_pdf_blob := base64_to_blob(v_pdf_b64);');
a(CHR(10)||' APEX_MAIL.ADD_ATTACHMENT(');
a(CHR(10)||' p_mail_id => v_mail_id,');
a(CHR(10)||' p_attachment => v_pdf_blob,');
a(CHR(10)||' p_filename => NVL(v_fname, ''Pruefbericht.pdf''),');
a(CHR(10)||' p_mime_type => ''application/pdf''');
a(CHR(10)||' );');
a(CHR(10)||' DBMS_LOB.FREETEMPORARY(v_pdf_blob);');
a(CHR(10)||' DBMS_LOB.FREETEMPORARY(v_pdf_b64);');
a(CHR(10)||' END IF;');
a(CHR(10)||' EXCEPTION WHEN OTHERS THEN NULL;');
a(CHR(10)||' END;');
a(CHR(10)||' END LOOP;');
a(CHR(10)||' END IF;');
a(CHR(10)||' END IF;');
a(CHR(10)||' EXCEPTION WHEN OTHERS THEN NULL;');
a(CHR(10)||' END;');
a(CHR(10)||' END;');
a(CHR(10)||' ELSE');
a(CHR(10)||' BEGIN');
a(CHR(10)||' DC_UTILS_PKG.generate_report(' || CHR(58) || 'project_id);');
a(CHR(10)||' EXCEPTION WHEN OTHERS THEN NULL;');
a(CHR(10)||' END;');
a(CHR(10)||' END IF;');
a(CHR(10));
a(CHR(10)||' ' || CHR(58) || 'status_code := 200;');
a(CHR(10)||' HTP.P(''{"project_id":'' || ' || CHR(58) || 'project_id');
a(CHR(10)||' || '',\"status\":\"COMPLETED\"''');
a(CHR(10)||' || '',\"completed_at\":\"'' || TO_CHAR(v_now, ''YYYY-MM-DD"T"HH24:MI:SS'') || ''\"''');
a(CHR(10)||' || ''}'');');
a(CHR(10));
a(CHR(10)||' EXCEPTION');
a(CHR(10)||' WHEN NO_DATA_FOUND THEN');
a(CHR(10)||' ' || CHR(58) || 'status_code := 404;');
a(CHR(10)||' HTP.P(''{"error":"Projekt nicht gefunden","project_id":'' || ' || CHR(58) || 'project_id || ''}'');');
a(CHR(10)||' WHEN OTHERS THEN');
a(CHR(10)||' ROLLBACK;');
a(CHR(10)||' ' || CHR(58) || 'status_code := 500;');
a(CHR(10)||' HTP.P(''{"error":"'' || REPLACE(SQLERRM, ''"'', '''''''') || ''"}'');');
a(CHR(10)||' END;');
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, sendet pro Dokument ein PDF per E-Mail',
p_source => v_src
);
DBMS_LOB.FREETEMPORARY(v_src);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Handler erfolgreich deployed.');
END;
/
Reference in New Issue Copy Permalink