mahnung/class/mahnungtrackingpattern.class.php

<?php
/* Copyright (C) 2026  Eduard Wisch  <data@data-it-solution.de>
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License, version 3.
 */

/**
 * \file       htdocs/custom/mahnung/class/mahnungtrackingpattern.class.php
 * \ingroup    mahnung
 * \brief      CRUD + Lookup für konfigurierbare Tracking-Patterns (Regex + URL-Template).
 *
 * Patterns werden in llx_mahnung_trackingpattern gehalten. Beim Upload eines
 * Sendebelegs werden alle aktiven Patterns nach priority DESC durchprobiert;
 * der erste Match gewinnt.
 */

class MahnungTrackingPattern
{
	/** @var DoliDB */
	public $db;

	/** @var int */
	public $id;

	/** @var int */
	public $entity;

	/** @var string Provider-Slug, z.B. 'dhl', 'dpag', 'hermes', 'dpd', 'ups', 'custom' */
	public $provider;

	/** @var string Anzeige-Label, z.B. "DHL Paket 20-stellig" */
	public $label;

	/** @var string Regex inkl. Delimiter, z.B. '/\b(\d{20})\b/' */
	public $regex;

	/** @var string URL-Template mit Platzhalter {nr}, z.B. 'https://www.dhl.de/...?piececode={nr}' */
	public $url_template;

	/** @var int höher = wichtiger (zuerst geprüft) */
	public $priority = 100;

	/** @var int 0/1 */
	public $active = 1;

	/** @var int Unix-Zeit */
	public $datec;

	/** @var int Unix-Zeit */
	public $tms;

	/**
	 * @param DoliDB $db
	 */
	public function __construct($db)
	{
		global $conf;
		$this->db = $db;
		$this->entity = $conf->entity;
	}

	/**
	 * Pattern anlegen.
	 *
	 * @return int <0 Fehler, sonst neue rowid
	 */
	public function create()
	{
		if (!self::isValidRegex($this->regex)) {
			$this->error = 'Invalid regex';
			return -2;
		}

		$now = dol_now();
		$sql = "INSERT INTO ".MAIN_DB_PREFIX."mahnung_trackingpattern ";
		$sql .= "(entity, provider, label, regex, url_template, priority, active, datec) VALUES (";
		$sql .= ((int) $this->entity).",";
		$sql .= "'".$this->db->escape((string) $this->provider)."',";
		$sql .= "'".$this->db->escape((string) $this->label)."',";
		$sql .= "'".$this->db->escape((string) $this->regex)."',";
		$sql .= "'".$this->db->escape((string) $this->url_template)."',";
		$sql .= ((int) $this->priority).",";
		$sql .= ((int) $this->active).",";
		$sql .= "'".$this->db->idate($now)."'";
		$sql .= ")";

		$resql = $this->db->query($sql);
		if (!$resql) {
			$this->error = $this->db->lasterror();
			return -1;
		}
		$this->id = $this->db->last_insert_id(MAIN_DB_PREFIX.'mahnung_trackingpattern');
		return $this->id;
	}

	/**
	 * @param  int $id
	 * @return int -1 Fehler, 0 nicht gefunden, >0 OK
	 */
	public function fetch($id)
	{
		$sql = "SELECT * FROM ".MAIN_DB_PREFIX."mahnung_trackingpattern WHERE rowid = ".((int) $id);
		$resql = $this->db->query($sql);
		if (!$resql) {
			return -1;
		}
		if (!$this->db->num_rows($resql)) {
			$this->db->free($resql);
			return 0;
		}
		$obj = $this->db->fetch_object($resql);
		$this->id = (int) $obj->rowid;
		$this->entity = (int) $obj->entity;
		$this->provider = $obj->provider;
		$this->label = $obj->label;
		$this->regex = $obj->regex;
		$this->url_template = $obj->url_template;
		$this->priority = (int) $obj->priority;
		$this->active = (int) $obj->active;
		$this->datec = $this->db->jdate($obj->datec);
		$this->tms = $this->db->jdate($obj->tms);
		$this->db->free($resql);
		return 1;
	}

	/**
	 * Pattern aktualisieren.
	 *
	 * @return int <0 Fehler, sonst id
	 */
	public function update()
	{
		if (empty($this->id)) {
			return -1;
		}
		if (!self::isValidRegex($this->regex)) {
			$this->error = 'Invalid regex';
			return -2;
		}
		$sql = "UPDATE ".MAIN_DB_PREFIX."mahnung_trackingpattern SET ";
		$sql .= "provider = '".$this->db->escape((string) $this->provider)."'";
		$sql .= ", label = '".$this->db->escape((string) $this->label)."'";
		$sql .= ", regex = '".$this->db->escape((string) $this->regex)."'";
		$sql .= ", url_template = '".$this->db->escape((string) $this->url_template)."'";
		$sql .= ", priority = ".((int) $this->priority);
		$sql .= ", active = ".((int) $this->active);
		$sql .= " WHERE rowid = ".((int) $this->id);
		$resql = $this->db->query($sql);
		if (!$resql) {
			$this->error = $this->db->lasterror();
			return -1;
		}
		return $this->id;
	}

	/**
	 * Pattern löschen.
	 *
	 * @return int <0 Fehler, sonst 1
	 */
	public function delete()
	{
		if (empty($this->id)) {
			return -1;
		}
		$resql = $this->db->query("DELETE FROM ".MAIN_DB_PREFIX."mahnung_trackingpattern WHERE rowid = ".((int) $this->id));
		return $resql ? 1 : -1;
	}

	/**
	 * Alle Patterns laden (sortiert nach priority DESC, label ASC).
	 *
	 * @param  bool $onlyActive
	 * @return array<int, array{rowid:int, provider:string, label:string, regex:string, url_template:string, priority:int, active:int}>
	 */
	public function fetchAll($onlyActive = false)
	{
		$sql = "SELECT rowid, entity, provider, label, regex, url_template, priority, active";
		$sql .= " FROM ".MAIN_DB_PREFIX."mahnung_trackingpattern";
		$sql .= " WHERE entity IN (".getEntity('mahnung').")";
		if ($onlyActive) {
			$sql .= " AND active = 1";
		}
		$sql .= " ORDER BY priority DESC, label ASC";

		$resql = $this->db->query($sql);
		if (!$resql) {
			return array();
		}
		$out = array();
		while ($obj = $this->db->fetch_object($resql)) {
			$out[] = array(
				'rowid' => (int) $obj->rowid,
				'provider' => $obj->provider,
				'label' => $obj->label,
				'regex' => $obj->regex,
				'url_template' => $obj->url_template,
				'priority' => (int) $obj->priority,
				'active' => (int) $obj->active,
			);
		}
		$this->db->free($resql);
		return $out;
	}

	/**
	 * Sucht in einem Beispieltext nach der ersten passenden Sendungsnummer.
	 *
	 * @param  string $haystack
	 * @return array|null  array{provider:string, nr:string, label:string, url:string} oder null
	 */
	public function detectFromText($haystack)
	{
		$haystack = (string) $haystack;
		if ($haystack === '') {
			return null;
		}
		foreach ($this->fetchAll(true) as $p) {
			if (!self::isValidRegex($p['regex'])) {
				continue;
			}
			$matches = array();
			$ret = @preg_match($p['regex'], $haystack, $matches);
			if ($ret === 1) {
				$nr = !empty($matches[1]) ? $matches[1] : $matches[0];
				// Leerzeichen entfernen (OCR fügt manchmal Leerzeichen in Nummern ein)
				$nr = preg_replace('/\s+/', '', $nr);
				return array(
					'provider' => $p['provider'],
					'nr' => $nr,
					'label' => $p['label'],
					'url' => str_replace('{nr}', rawurlencode($nr), $p['url_template']),
				);
			}
		}
		return null;
	}

	/**
	 * Robuste Regex-Validierung (kein ReDoS-Schutz, aber Syntax-Check).
	 * Erlaubt nur Delimiter /, #, ~. URL-Template wird hier NICHT geprüft.
	 *
	 * @param  string $regex
	 * @return bool
	 */
	public static function isValidRegex($regex)
	{
		if (!is_string($regex) || strlen($regex) < 3 || strlen($regex) > 255) {
			return false;
		}
		// Erlaubte Delimiter
		$first = $regex[0];
		if (!in_array($first, array('/', '#', '~'), true)) {
			return false;
		}
		// @ unterdrückt Warning, false bei ungültigem Regex
		$ret = @preg_match($regex, '');
		return $ret !== false;
	}

	/**
	 * URL aus Provider + Sendungsnummer bauen — sucht passendes Pattern aus DB.
	 * Fallback: Mahnung::trackingUrl() (hardcoded URLs).
	 *
	 * @param  string $provider
	 * @param  string $nr
	 * @return string
	 */
	public function urlFor($provider, $nr)
	{
		$nr = trim((string) $nr);
		if ($nr === '') {
			return '';
		}
		// Erstes aktives Pattern mit passendem Provider nutzen
		foreach ($this->fetchAll(true) as $p) {
			if ($p['provider'] === $provider) {
				return str_replace('{nr}', rawurlencode($nr), $p['url_template']);
			}
		}
		// Fallback auf Hardcoded (in Mahnung::trackingUrl)
		require_once DOL_DOCUMENT_ROOT.'/custom/mahnung/class/mahnung.class.php';
		return Mahnung::trackingUrl($provider, $nr);
	}

	/**
	 * Default-Patterns seeden (idempotent).
	 *
	 * @param  DoliDB $db
	 * @return int Anzahl eingefuegter Patterns
	 */
	public static function seedDefaults($db)
	{
		global $conf;
		$entity = (int) $conf->entity;

		// Anzahl bestehender Patterns prüfen
		$resql = $db->query("SELECT COUNT(*) AS nb FROM ".MAIN_DB_PREFIX."mahnung_trackingpattern WHERE entity = ".$entity);
		if (!$resql) {
			return 0;
		}
		$obj = $db->fetch_object($resql);
		$db->free($resql);
		if ((int) $obj->nb > 0) {
			return 0; // Schon befüllt
		}

		// Defaults — priority höher = spezifischer (zuerst geprüft)
		$defaults = array(
			array('dhl',    'DHL Paket (20-stellig)',                  '/\b(\d{20})\b/',           'https://www.dhl.de/de/privatkunden/dhl-sendungsverfolgung.html?piececode={nr}', 90),
			array('dpag',   'Deutsche Post Einschreiben',               '/\b(R[A-Z]\d{9}DE)\b/',    'https://www.deutschepost.de/sendung/simpleQuery.html?form.sendungsnummer={nr}', 85),
			array('ups',    'UPS (1Z + 16 Zeichen)',                    '/\b(1Z[A-HJ-NP-Z0-9]{16})\b/i', 'https://www.ups.com/track?tracknum={nr}', 80),
			array('dhl',    'DHL Paket Online-Frankierung (11-stellig)','/\b(\d{11})\b/',           'https://www.dhl.de/de/privatkunden/dhl-sendungsverfolgung.html?piececode={nr}', 30),
			array('hermes', 'Hermes (14-stellig)',                      '/\b([Hh]?\d{14})\b/',      'https://www.myhermes.de/empfangen/sendungsverfolgung/sendungsinformation/#{nr}', 25),
			array('dpd',    'DPD (14-stellig)',                         '/\b(\d{14})\b/',           'https://tracking.dpd.de/status/de_DE/parcel/{nr}', 20),
		);
		$now = dol_now();
		$count = 0;
		foreach ($defaults as $row) {
			list($provider, $label, $regex, $url, $priority) = $row;
			$sql = "INSERT INTO ".MAIN_DB_PREFIX."mahnung_trackingpattern ";
			$sql .= "(entity, provider, label, regex, url_template, priority, active, datec) VALUES (";
			$sql .= $entity.",";
			$sql .= "'".$db->escape($provider)."',";
			$sql .= "'".$db->escape($label)."',";
			$sql .= "'".$db->escape($regex)."',";
			$sql .= "'".$db->escape($url)."',";
			$sql .= ((int) $priority).",";
			$sql .= "1,";
			$sql .= "'".$db->idate($now)."'";
			$sql .= ")";
			if ($db->query($sql)) {
				$count++;
			}
		}
		return $count;
	}
}