2486 lines
102 KiB
PHP
2486 lines
102 KiB
PHP
<?php
|
||
// ROI.php – Umsatz/Peak/Histogramm pro Artikel inkl. Bundleanteil + Extern-Umsatz
|
||
// + "Slot-Umsatz" (auftragsbasiert, nicht tagesbasiert) inkl. Bundleanteil in Modal
|
||
// liegt in /dist
|
||
|
||
require('../config.php');
|
||
require('../EpiApi.php');
|
||
require_once __DIR__ . '/../vendor/autoload.php';
|
||
date_default_timezone_set('Europe/Berlin');
|
||
|
||
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
||
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
||
use PhpOffice\PhpSpreadsheet\Style\Alignment;
|
||
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
|
||
use PhpOffice\PhpSpreadsheet\Cell\DataType;
|
||
|
||
$Epi = new Epirent();
|
||
|
||
/* =========================
|
||
Filter (Jahres-Range)
|
||
– ROI wird nur ausgeführt, wenn der User einen Zeitraum gewählt hat
|
||
oder explizit "Alles" anfordert. Das spart bei großen Datenbeständen
|
||
teure API-Calls.
|
||
========================= */
|
||
$filterMode = strtolower((string)($_GET['mode'] ?? ''));
|
||
$filterFrom = isset($_GET['from']) ? (int)$_GET['from'] : 0;
|
||
$filterTo = isset($_GET['to']) ? (int)$_GET['to'] : 0;
|
||
|
||
if ($filterFrom > 0 && $filterTo > 0 && $filterTo < $filterFrom) {
|
||
[$filterFrom, $filterTo] = [$filterTo, $filterFrom];
|
||
}
|
||
|
||
$computeRoi = in_array($filterMode, ['range', 'all'], true);
|
||
if ($filterMode === 'range' && ($filterFrom <= 0 || $filterTo <= 0)) {
|
||
$computeRoi = false;
|
||
$filterMode = '';
|
||
}
|
||
|
||
/* =========================
|
||
ROI-Cache: Konfiguration & Zeitraum-Auflösung
|
||
– Aktiv, wenn ROI_Cache_YearRange in der Config definiert ist.
|
||
– Semantik:
|
||
negative Zahl -N → alles bis inkl. currentYear-|N| (also älter oder gleich)
|
||
0 → nur aktuelles Jahr (bewusst; ändert sich täglich)
|
||
>= 1900 → nur exakt dieses Kalenderjahr
|
||
========================= */
|
||
$cacheEnabled = defined('ROI_Cache_YearRange');
|
||
$cacheFrom = null; // int|null (null = -unendlich, kein untere Grenze)
|
||
$cacheTo = null; // int|null (null = +unendlich, keine obere Grenze)
|
||
$cacheDir = __DIR__ . '/cache';
|
||
$cacheFile = $cacheDir . '/roi_mandant_' . (defined('Epirent_Mandant') ? (string)Epirent_Mandant : '0') . '.cache';
|
||
$cacheRefreshRequested = isset($_GET['refresh_cache']) && (string)$_GET['refresh_cache'] === '1';
|
||
|
||
/**
|
||
* Parst die Cache-Range-Konfiguration.
|
||
* Unterstützte Formate:
|
||
* Integer: -N (relative Vorjahre), 0 (aktuelles), >=1900 (konkretes Jahr)
|
||
* String: "<YYYY", "<=YYYY", ">YYYY", ">=YYYY", "YYYY-YYYY", oder numerisch
|
||
* Return: ['from' => int|null, 'to' => int|null] oder null wenn nicht parsbar.
|
||
*/
|
||
function parseCacheRangeConfig($val, int $currentYear): ?array {
|
||
if (is_int($val) || (is_string($val) && preg_match('/^-?\d+$/', trim((string)$val)))) {
|
||
$r = (int)$val;
|
||
if ($r >= 1900) return ['from' => $r, 'to' => $r];
|
||
return ['from' => null, 'to' => $currentYear + $r]; // r <= 0
|
||
}
|
||
if (is_string($val)) {
|
||
$v = trim($val);
|
||
if (preg_match('/^<(=?)\s*(\d{4})$/', $v, $m)) {
|
||
$incl = ($m[1] === '='); $y = (int)$m[2];
|
||
return ['from' => null, 'to' => $incl ? $y : $y - 1];
|
||
}
|
||
if (preg_match('/^>(=?)\s*(\d{4})$/', $v, $m)) {
|
||
$incl = ($m[1] === '='); $y = (int)$m[2];
|
||
return ['from' => $incl ? $y : $y + 1, 'to' => null];
|
||
}
|
||
if (preg_match('/^(\d{4})\s*-\s*(\d{4})$/', $v, $m)) {
|
||
$a = (int)$m[1]; $b = (int)$m[2];
|
||
if ($a > $b) [$a, $b] = [$b, $a];
|
||
return ['from' => $a, 'to' => $b];
|
||
}
|
||
}
|
||
return null;
|
||
}
|
||
|
||
if ($cacheEnabled) {
|
||
$currentYear = (int)date('Y');
|
||
$parsed = parseCacheRangeConfig(ROI_Cache_YearRange, $currentYear);
|
||
if ($parsed === null) {
|
||
// Ungültiger Wert → Cache deaktivieren, damit die Seite nicht kaputt geht
|
||
$cacheEnabled = false;
|
||
} else {
|
||
$cacheFrom = $parsed['from'];
|
||
$cacheTo = $parsed['to'];
|
||
}
|
||
}
|
||
|
||
if ($cacheEnabled) {
|
||
if (!is_dir($cacheDir)) {
|
||
@mkdir($cacheDir, 0775, true);
|
||
}
|
||
// .htaccess: Cache-Ordner nicht direkt aus dem Web zugänglich machen
|
||
if (is_dir($cacheDir) && !file_exists($cacheDir . '/.htaccess')) {
|
||
@file_put_contents($cacheDir . '/.htaccess', "Require all denied\nDeny from all\n");
|
||
}
|
||
// .gitignore, damit die Cache-Dateien nicht ins Repo wandern
|
||
if (is_dir($cacheDir) && !file_exists($cacheDir . '/.gitignore')) {
|
||
@file_put_contents($cacheDir . '/.gitignore', "*\n!.gitignore\n!.htaccess\n");
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Prüft, ob ein User-Filter komplett vom Cache abgedeckt wird.
|
||
* $cf = cacheFrom (int|null), $ct = cacheTo (int|null), $uf = userFrom (int), $ut = userTo (int)
|
||
*/
|
||
function filterCoveredByCache(?int $cf, ?int $ct, int $uf, int $ut): bool {
|
||
if ($ct !== null && $ut > $ct) return false;
|
||
if ($cf !== null && $uf < $cf) return false;
|
||
return true;
|
||
}
|
||
|
||
/**
|
||
* Normalisiert eine Liste von Ranges: sortiert, mergt überlappende und angrenzende Bereiche.
|
||
* Ranges: [ ['from' => int|null, 'to' => int|null], ... ]
|
||
* null bedeutet -∞ (from) bzw. +∞ (to).
|
||
*/
|
||
function normalizeCachedRanges(array $ranges): array {
|
||
$ranges = array_values(array_filter($ranges, fn($r) => is_array($r)));
|
||
usort($ranges, function($a, $b) {
|
||
$af = $a['from'] ?? PHP_INT_MIN;
|
||
$bf = $b['from'] ?? PHP_INT_MIN;
|
||
return $af <=> $bf;
|
||
});
|
||
$out = [];
|
||
foreach ($ranges as $r) {
|
||
$rf = $r['from'] ?? null;
|
||
$rt = $r['to'] ?? null;
|
||
if (empty($out)) { $out[] = ['from' => $rf, 'to' => $rt]; continue; }
|
||
$last = &$out[count($out) - 1];
|
||
$lastToNum = $last['to'] ?? PHP_INT_MAX;
|
||
$curFromNum = $rf ?? PHP_INT_MIN;
|
||
if ($curFromNum <= $lastToNum + 1) {
|
||
if ($rt === null || $lastToNum < ($rt ?? PHP_INT_MAX)) {
|
||
$last['to'] = $rt;
|
||
}
|
||
} else {
|
||
$out[] = ['from' => $rf, 'to' => $rt];
|
||
}
|
||
unset($last);
|
||
}
|
||
return $out;
|
||
}
|
||
|
||
function addToCachedRanges(array $ranges, ?int $from, ?int $to): array {
|
||
$ranges[] = ['from' => $from, 'to' => $to];
|
||
return normalizeCachedRanges($ranges);
|
||
}
|
||
|
||
function isYearCovered(array $cachedRanges, int $year): bool {
|
||
foreach ($cachedRanges as $r) {
|
||
$f = $r['from'] ?? PHP_INT_MIN;
|
||
$t = $r['to'] ?? PHP_INT_MAX;
|
||
if ($year >= $f && $year <= $t) return true;
|
||
}
|
||
return false;
|
||
}
|
||
|
||
/**
|
||
* Berechnet innerhalb [askFrom, askTo] die zusammenhängenden Jahres-Segmente,
|
||
* die NICHT von $cachedRanges abgedeckt sind. Return: [['from', 'to'], ...].
|
||
*/
|
||
function computeMissingRanges(array $cachedRanges, int $askFrom, int $askTo): array {
|
||
if ($askFrom > $askTo) return [];
|
||
$missing = [];
|
||
$y = $askFrom;
|
||
while ($y <= $askTo) {
|
||
if (!isYearCovered($cachedRanges, $y)) {
|
||
$start = $y;
|
||
while ($y <= $askTo && !isYearCovered($cachedRanges, $y)) $y++;
|
||
$missing[] = ['from' => $start, 'to' => $y - 1];
|
||
} else {
|
||
$y++;
|
||
}
|
||
}
|
||
return $missing;
|
||
}
|
||
|
||
/**
|
||
* Formatiert cached_ranges für die UI als lesbaren String, z.B. "2017–2020, 2022–2025"
|
||
* oder "−∞–2025".
|
||
*/
|
||
function formatCachedRangesHuman(array $cachedRanges): string {
|
||
if (empty($cachedRanges)) return '—';
|
||
$parts = [];
|
||
foreach ($cachedRanges as $r) {
|
||
$f = $r['from'] === null ? '−∞' : (string)(int)$r['from'];
|
||
$t = $r['to'] === null ? '+∞' : (string)(int)$r['to'];
|
||
$parts[] = ($f === $t) ? $f : ($f . '–' . $t);
|
||
}
|
||
return implode(', ', $parts);
|
||
}
|
||
|
||
const ROI_CACHE_VERSION = 4;
|
||
|
||
/**
|
||
* Cache atomar schreiben (temp-file + rename).
|
||
* Speichert die ROHDATEN aus dem Invoice-Loop, damit sich beim Load Cache + Live-Anteil
|
||
* (für Jahre > cacheTo) sauber zusammenführen lassen. Aggregate/Slot-Berechnung passiert
|
||
* IMMER erst nach dem Load auf den kombinierten Daten.
|
||
*/
|
||
function saveRoiCache(string $path, ?int $cacheFrom, ?int $cacheTo, array $cachedRanges): bool {
|
||
global $rows, $slotItemsDirect, $slotItemsIncl, $meta,
|
||
$pivot, $pivotB, $pivotExt, $pivotExtB,
|
||
$eventsDirect, $eventsIncl, $customerAgg, $allYears,
|
||
$productCache, $bundleLeafCache, $rentPriceCache,
|
||
$journalCache, $orderCustomerCache;
|
||
|
||
$data = [
|
||
'ts' => time(),
|
||
'cache_from' => $cacheFrom, // Config-Wert (informational)
|
||
'cache_to' => $cacheTo, // Config-Wert (informational)
|
||
'cached_ranges' => $cachedRanges, // welche Jahre tatsächlich abgedeckt sind
|
||
'roi_ver' => ROI_CACHE_VERSION,
|
||
// Rohdaten aus dem Invoice-Loop
|
||
'rows' => $rows,
|
||
'slotItemsDirect' => $slotItemsDirect,
|
||
'slotItemsIncl' => $slotItemsIncl,
|
||
'meta' => $meta,
|
||
'pivot' => $pivot,
|
||
'pivotB' => $pivotB,
|
||
'pivotExt' => $pivotExt,
|
||
'pivotExtB' => $pivotExtB,
|
||
'eventsDirect' => $eventsDirect,
|
||
'eventsIncl' => $eventsIncl,
|
||
'customerAgg' => $customerAgg,
|
||
'allYears' => $allYears,
|
||
// API-Caches – sparen beim Live-Loop viele Detail-Requests
|
||
'productCache' => $productCache,
|
||
'bundleLeafCache' => $bundleLeafCache,
|
||
'rentPriceCache' => $rentPriceCache,
|
||
'journalCache' => $journalCache,
|
||
'orderCustomerCache' => $orderCustomerCache,
|
||
];
|
||
$blob = serialize($data);
|
||
$tmp = $path . '.tmp.' . bin2hex(random_bytes(4));
|
||
if (@file_put_contents($tmp, $blob, LOCK_EX) === false) return false;
|
||
if (!@rename($tmp, $path)) { @unlink($tmp); return false; }
|
||
return true;
|
||
}
|
||
|
||
/**
|
||
* Cache laden und alle globalen Datenstrukturen befüllen.
|
||
* Return: ['ts', 'cache_from', 'cache_to'] wenn erfolgreich, null sonst.
|
||
*/
|
||
function loadRoiCache(string $path): ?array {
|
||
global $rows, $slotItemsDirect, $slotItemsIncl, $meta,
|
||
$pivot, $pivotB, $pivotExt, $pivotExtB,
|
||
$eventsDirect, $eventsIncl, $customerAgg, $allYears,
|
||
$productCache, $bundleLeafCache, $rentPriceCache,
|
||
$journalCache, $orderCustomerCache;
|
||
|
||
$blob = @file_get_contents($path);
|
||
if ($blob === false || $blob === '') return null;
|
||
$data = @unserialize($blob);
|
||
if (!is_array($data) || (int)($data['roi_ver'] ?? 0) !== ROI_CACHE_VERSION) return null;
|
||
|
||
$rows = $data['rows'] ?? [];
|
||
$slotItemsDirect = $data['slotItemsDirect'] ?? [];
|
||
$slotItemsIncl = $data['slotItemsIncl'] ?? [];
|
||
$meta = $data['meta'] ?? [];
|
||
$pivot = $data['pivot'] ?? [];
|
||
$pivotB = $data['pivotB'] ?? [];
|
||
$pivotExt = $data['pivotExt'] ?? [];
|
||
$pivotExtB = $data['pivotExtB'] ?? [];
|
||
$eventsDirect = $data['eventsDirect'] ?? [];
|
||
$eventsIncl = $data['eventsIncl'] ?? [];
|
||
$customerAgg = $data['customerAgg'] ?? [];
|
||
$allYears = $data['allYears'] ?? [];
|
||
$productCache = $data['productCache'] ?? [];
|
||
$bundleLeafCache = $data['bundleLeafCache'] ?? [];
|
||
$rentPriceCache = $data['rentPriceCache'] ?? [];
|
||
$journalCache = $data['journalCache'] ?? [];
|
||
$orderCustomerCache = $data['orderCustomerCache'] ?? [];
|
||
|
||
return [
|
||
'ts' => (int)($data['ts'] ?? 0),
|
||
'cache_from' => $data['cache_from'] ?? null,
|
||
'cache_to' => $data['cache_to'] ?? null,
|
||
'cached_ranges' => is_array($data['cached_ranges'] ?? null) ? $data['cached_ranges'] : [],
|
||
];
|
||
}
|
||
|
||
/**
|
||
* Liest nur die Cache-Meta ohne die Rohdaten in die Globals zu schreiben.
|
||
* Nützlich, um vor dem eigentlichen Load zu entscheiden, ob überhaupt geladen werden muss.
|
||
*/
|
||
function peekRoiCacheMeta(string $path): ?array {
|
||
$blob = @file_get_contents($path);
|
||
if ($blob === false || $blob === '') return null;
|
||
$data = @unserialize($blob);
|
||
if (!is_array($data) || (int)($data['roi_ver'] ?? 0) !== ROI_CACHE_VERSION) return null;
|
||
return [
|
||
'ts' => (int)($data['ts'] ?? 0),
|
||
'cache_from' => $data['cache_from'] ?? null,
|
||
'cache_to' => $data['cache_to'] ?? null,
|
||
'cached_ranges' => is_array($data['cached_ranges'] ?? null) ? $data['cached_ranges'] : [],
|
||
];
|
||
}
|
||
|
||
/**
|
||
* Filtert die ROHDATEN in den globalen Aggregations-Strukturen auf einen Jahres-Range.
|
||
* Diese Funktion wird VOR der Aggregation aufgerufen, damit Peak/Histogramm/Slots/Kunden
|
||
* automatisch nur den gewünschten Zeitraum abbilden.
|
||
* $from = 0 → kein Untergrenze; $to = 0 → keine Obergrenze.
|
||
*/
|
||
function applyRangeFilterToRawData(int $from, int $to): void {
|
||
global $rows, $slotItemsDirect, $slotItemsIncl,
|
||
$pivot, $pivotB, $pivotExt, $pivotExtB,
|
||
$eventsDirect, $eventsIncl, $customerAgg, $allYears;
|
||
|
||
$yearMatches = function(int $y) use ($from, $to): bool {
|
||
if ($y <= 0) return false;
|
||
if ($from > 0 && $y < $from) return false;
|
||
if ($to > 0 && $y > $to) return false;
|
||
return true;
|
||
};
|
||
$ymdMatches = function(string $ymd) use ($yearMatches): bool {
|
||
if (!$ymd || strlen($ymd) < 4) return false;
|
||
return $yearMatches((int)substr($ymd, 0, 4));
|
||
};
|
||
|
||
// Debug-Rows: nach year
|
||
$rows = array_values(array_filter($rows, fn($r) => $yearMatches((int)($r['year'] ?? 0))));
|
||
|
||
// Pivot-Aggregate: Jahr-Schlüssel filtern
|
||
foreach ([&$pivot, &$pivotB, &$pivotExt, &$pivotExtB] as &$agg) {
|
||
foreach ($agg as $pk => &$yearMap) {
|
||
foreach (array_keys($yearMap) as $y) {
|
||
if (!$yearMatches((int)$y)) unset($yearMap[$y]);
|
||
}
|
||
}
|
||
unset($yearMap);
|
||
}
|
||
unset($agg);
|
||
|
||
// allYears
|
||
foreach (array_keys($allYears) as $y) {
|
||
if (!$yearMatches((int)$y)) unset($allYears[$y]);
|
||
}
|
||
|
||
// Peak/Histogramm-Events (ymd-basiert)
|
||
foreach ([&$eventsDirect, &$eventsIncl] as &$ev) {
|
||
foreach ($ev as $pk => &$ymdMap) {
|
||
foreach (array_keys($ymdMap) as $ymd) {
|
||
if (!$ymdMatches((string)$ymd)) unset($ymdMap[$ymd]);
|
||
}
|
||
}
|
||
unset($ymdMap);
|
||
}
|
||
unset($ev);
|
||
|
||
// Slot-Items: nach start_ymd year
|
||
foreach ([&$slotItemsDirect, &$slotItemsIncl] as &$sit) {
|
||
foreach ($sit as $pk => &$items) {
|
||
$items = array_values(array_filter($items, fn($it) => $ymdMatches((string)($it['start_ymd'] ?? ''))));
|
||
}
|
||
unset($items);
|
||
}
|
||
unset($sit);
|
||
|
||
// Customer-Aggregation: by_year filtern
|
||
foreach ($customerAgg as $pk => &$byCustomer) {
|
||
foreach ($byCustomer as $cpk => &$c) {
|
||
if (isset($c['by_year'])) {
|
||
foreach (array_keys($c['by_year']) as $y) {
|
||
if (!$yearMatches((int)$y)) unset($c['by_year'][$y]);
|
||
}
|
||
}
|
||
}
|
||
unset($c);
|
||
}
|
||
unset($byCustomer);
|
||
}
|
||
|
||
|
||
/* =========================
|
||
Helpers
|
||
========================= */
|
||
|
||
function formatEuro(float $amount): string {
|
||
$formatted = number_format(abs($amount), 2, ',', '.') . ' €';
|
||
return $amount < 0 ? '-' . $formatted : $formatted;
|
||
}
|
||
|
||
function yearFromDate(?string $ymd): ?int {
|
||
if (!$ymd || $ymd === '0000-00-00') return null;
|
||
return (int)substr($ymd, 0, 4);
|
||
}
|
||
|
||
function safeYmd(?string $ymd): ?string {
|
||
if (!$ymd || $ymd === '0000-00-00') return null;
|
||
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $ymd)) return null;
|
||
return $ymd;
|
||
}
|
||
|
||
function ymdToTs(string $ymd): int {
|
||
return (new DateTime($ymd . ' 00:00:00', new DateTimeZone('Europe/Berlin')))->getTimestamp();
|
||
}
|
||
|
||
function tsToYmd(int $ts): string {
|
||
return (new DateTime('@' . $ts))->setTimezone(new DateTimeZone('Europe/Berlin'))->format('Y-m-d');
|
||
}
|
||
|
||
function base64Json($data): string {
|
||
return base64_encode(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES));
|
||
}
|
||
|
||
function apiJsonDecode($raw) {
|
||
if (is_object($raw)) {
|
||
if (method_exists($raw, 'getContents')) {
|
||
$raw = $raw->getContents();
|
||
} else {
|
||
$raw = (string)$raw;
|
||
}
|
||
}
|
||
if (!is_string($raw)) $raw = (string)$raw;
|
||
return json_decode($raw);
|
||
}
|
||
|
||
/* =========================
|
||
API Caches
|
||
========================= */
|
||
|
||
$productCache = []; // [pk => productObj]
|
||
$bundleLeafCache = []; // [bundlePk => [leafPk => amount]]
|
||
$rentPriceCache = []; // [pk => float]
|
||
|
||
function getProduct(Epirent $Epi, int $productPk) {
|
||
global $productCache;
|
||
if ($productPk <= 0) return null;
|
||
if (isset($productCache[$productPk])) return $productCache[$productPk];
|
||
|
||
$res = apiJsonDecode($Epi->requestEpiApi('/v1/product/' . $productPk . '?cl=' . Epirent_Mandant));
|
||
$prod = ($res && ($res->success ?? false) && !empty($res->payload[0])) ? $res->payload[0] : null;
|
||
$productCache[$productPk] = $prod;
|
||
return $prod;
|
||
}
|
||
|
||
function getRentPrice(Epirent $Epi, int $productPk): float {
|
||
global $rentPriceCache;
|
||
if (isset($rentPriceCache[$productPk])) return $rentPriceCache[$productPk];
|
||
|
||
$p = getProduct($Epi, $productPk);
|
||
$price = 0.0;
|
||
if ($p && isset($p->pricing) && isset($p->pricing->price_rent)) {
|
||
$price = (float)$p->pricing->price_rent;
|
||
}
|
||
$rentPriceCache[$productPk] = $price;
|
||
return $price;
|
||
}
|
||
|
||
/**
|
||
* WICHTIG: Bundle-Auflösung NUR für virtuelle Bundles.
|
||
* Ergebnis: [leafProductPk => amount]
|
||
*/
|
||
function resolveBundleLeafMap(Epirent $Epi, int $productPk, array &$stack = []): array {
|
||
global $bundleLeafCache;
|
||
|
||
if ($productPk <= 0) return [];
|
||
if (isset($bundleLeafCache[$productPk])) return $bundleLeafCache[$productPk];
|
||
|
||
if (isset($stack[$productPk])) {
|
||
return [];
|
||
}
|
||
$stack[$productPk] = true;
|
||
|
||
$p = getProduct($Epi, $productPk);
|
||
if (!$p) {
|
||
unset($stack[$productPk]);
|
||
return [];
|
||
}
|
||
|
||
// NUR virtuelle Bundles zerlegen
|
||
$isVirtual = (bool)($p->is_virtual ?? false);
|
||
if (!$isVirtual) {
|
||
unset($stack[$productPk]);
|
||
$bundleLeafCache[$productPk] = [$productPk => 1.0];
|
||
return $bundleLeafCache[$productPk];
|
||
}
|
||
|
||
$mats = [];
|
||
if (!empty($p->materials_ext) && !empty($p->materials_ext->rent_fix) && is_array($p->materials_ext->rent_fix)) {
|
||
$mats = $p->materials_ext->rent_fix;
|
||
} elseif (!empty($p->materials) && is_array($p->materials)) {
|
||
$mats = $p->materials;
|
||
}
|
||
|
||
// virtuell aber ohne Komponenten => leaf
|
||
if (empty($mats)) {
|
||
unset($stack[$productPk]);
|
||
$bundleLeafCache[$productPk] = [$productPk => 1.0];
|
||
return $bundleLeafCache[$productPk];
|
||
}
|
||
|
||
$leaf = [];
|
||
foreach ($mats as $m) {
|
||
$childPk = (int)($m->mat_product_pk ?? 0);
|
||
$amt = (float)($m->amount ?? 1);
|
||
if ($childPk <= 0 || $amt <= 0) continue;
|
||
|
||
$childLeaf = resolveBundleLeafMap($Epi, $childPk, $stack);
|
||
foreach ($childLeaf as $leafPk => $leafAmt) {
|
||
if (!isset($leaf[$leafPk])) $leaf[$leafPk] = 0.0;
|
||
$leaf[$leafPk] += $amt * (float)$leafAmt;
|
||
}
|
||
}
|
||
|
||
unset($stack[$productPk]);
|
||
|
||
if (empty($leaf)) $leaf = [$productPk => 1.0];
|
||
|
||
$bundleLeafCache[$productPk] = $leaf;
|
||
return $leaf;
|
||
}
|
||
|
||
/**
|
||
* Bundle-Umsatz anteilig auf leaf-Produkte verteilen.
|
||
* Gewichtung: rentPrice(leaf) * amount
|
||
* Fallback: gleichverteilt nach amount
|
||
*/
|
||
function allocateBundleRevenue(Epirent $Epi, int $bundlePk, float $bundleRevenueNet): array {
|
||
$leafMap = resolveBundleLeafMap($Epi, $bundlePk);
|
||
if (empty($leafMap)) return [];
|
||
|
||
// wenn NICHT virtuell (leafMap == self) => dann keine Allocation (weil kein Bundle)
|
||
if (count($leafMap) === 1 && isset($leafMap[$bundlePk])) {
|
||
return [];
|
||
}
|
||
|
||
$weights = [];
|
||
$sumW = 0.0;
|
||
|
||
foreach ($leafMap as $leafPk => $amt) {
|
||
$rp = getRentPrice($Epi, (int)$leafPk);
|
||
$w = $rp * (float)$amt;
|
||
$weights[$leafPk] = $w;
|
||
$sumW += $w;
|
||
}
|
||
|
||
if ($sumW <= 0.0) {
|
||
$sumAmt = array_sum($leafMap);
|
||
if ($sumAmt <= 0.0) $sumAmt = (float)count($leafMap);
|
||
$alloc = [];
|
||
foreach ($leafMap as $leafPk => $amt) {
|
||
$alloc[$leafPk] = $bundleRevenueNet * ((float)$amt / $sumAmt);
|
||
}
|
||
return $alloc;
|
||
}
|
||
|
||
$alloc = [];
|
||
foreach ($weights as $leafPk => $w) {
|
||
$alloc[$leafPk] = $bundleRevenueNet * ($w / $sumW);
|
||
}
|
||
return $alloc;
|
||
}
|
||
|
||
/* =========================
|
||
Customer Cache
|
||
– Zieht den Kunden aus dem Invoice-Objekt, fällt bei Bedarf auf den Order-Endpoint zurück.
|
||
– Ergebnis pro Order gecached, damit mehrere Rechnungen desselben Auftrags nur 1x API-Kosten haben.
|
||
========================= */
|
||
|
||
$orderCustomerCache = []; // [orderPk => ['pk'=>int, 'name'=>string]]
|
||
|
||
function resolveInvoiceCustomer(Epirent $Epi, $invObj, int $orderPk): array {
|
||
global $orderCustomerCache;
|
||
|
||
$pk = 0; $name = '';
|
||
|
||
$pickInt = function($obj, array $fields) {
|
||
foreach ($fields as $f) {
|
||
$v = (int)($obj->{$f} ?? 0);
|
||
if ($v > 0) return $v;
|
||
}
|
||
return 0;
|
||
};
|
||
$pickStr = function($obj, array $fields) {
|
||
foreach ($fields as $f) {
|
||
$v = trim((string)($obj->{$f} ?? ''));
|
||
if ($v !== '') return $v;
|
||
}
|
||
return '';
|
||
};
|
||
|
||
if (is_object($invObj)) {
|
||
$pk = $pickInt($invObj, ['customer_pk','contact_pk','pk_customer','kunde_pk']);
|
||
$name = $pickStr($invObj, ['customer_name','contact_name','kunde_name','name_customer']);
|
||
|
||
foreach (['address_invoice','address_delivery','customer','contact'] as $af) {
|
||
if ($pk > 0 && $name !== '') break;
|
||
$sub = $invObj->{$af} ?? null;
|
||
if (is_object($sub)) {
|
||
if ($pk <= 0) $pk = $pickInt($sub, ['contact_pk','primary_key','pk','id']);
|
||
if ($name === '') $name = $pickStr($sub, ['company','name','display_name','name1']);
|
||
}
|
||
}
|
||
}
|
||
|
||
if (($pk <= 0 || $name === '') && $orderPk > 0) {
|
||
if (!array_key_exists($orderPk, $orderCustomerCache)) {
|
||
$res = apiJsonDecode($Epi->requestEpiApi('/v1/order/' . $orderPk . '?cl=' . Epirent_Mandant));
|
||
$ord = ($res && ($res->success ?? false) && !empty($res->payload[0])) ? $res->payload[0] : null;
|
||
$c = ['pk' => 0, 'name' => ''];
|
||
if (is_object($ord)) {
|
||
$c['pk'] = $pickInt($ord, ['customer_pk','contact_pk','pk_customer']);
|
||
$c['name'] = $pickStr($ord, ['customer_name','contact_name']);
|
||
foreach (['address_invoice','address_delivery','customer','contact'] as $af) {
|
||
if ($c['pk'] > 0 && $c['name'] !== '') break;
|
||
$sub = $ord->{$af} ?? null;
|
||
if (is_object($sub)) {
|
||
if ($c['pk'] <= 0) $c['pk'] = $pickInt($sub, ['contact_pk','primary_key','pk','id']);
|
||
if ($c['name'] === '') $c['name'] = $pickStr($sub, ['company','name','display_name','name1']);
|
||
}
|
||
}
|
||
}
|
||
$orderCustomerCache[$orderPk] = $c;
|
||
}
|
||
$c = $orderCustomerCache[$orderPk];
|
||
if ($pk <= 0) $pk = (int)$c['pk'];
|
||
if ($name === '') $name = (string)$c['name'];
|
||
}
|
||
|
||
if ($pk <= 0) $pk = 0;
|
||
if ($name === '') $name = $pk > 0 ? ('Kunde #' . $pk) : 'Unbekannt';
|
||
|
||
return ['pk' => $pk, 'name' => $name];
|
||
}
|
||
|
||
/* =========================
|
||
Journal Cache
|
||
========================= */
|
||
|
||
$journalCache = [];
|
||
function getJournalByChapter(Epirent $Epi, int $chapterId): array {
|
||
global $journalCache;
|
||
|
||
if ($chapterId <= 0) return [];
|
||
if (isset($journalCache[$chapterId])) return $journalCache[$chapterId];
|
||
|
||
$url = '/v1/journal/filter?chid=' . $chapterId . '&cl=' . Epirent_Mandant;
|
||
$res = apiJsonDecode($Epi->requestEpiApi($url));
|
||
$payload = ($res && ($res->success ?? false) && isset($res->payload) && is_array($res->payload)) ? $res->payload : [];
|
||
|
||
$journalCache[$chapterId] = $payload;
|
||
return $payload;
|
||
}
|
||
|
||
/* =========================
|
||
Interval aggregation (Peak/Histogram) – tagesbasiert
|
||
========================= */
|
||
|
||
$eventsDirect = []; // eventsDirect[productPk][ymd] += deltaQty
|
||
$eventsIncl = []; // direct + bundle
|
||
|
||
function addIntervalEvent(array &$events, int $productPk, string $startYmd, string $endYmd, float $qty): void {
|
||
if ($productPk <= 0) return;
|
||
$startYmd = safeYmd($startYmd);
|
||
$endYmd = safeYmd($endYmd);
|
||
if (!$startYmd || !$endYmd) return;
|
||
if ($qty == 0.0) return;
|
||
|
||
$startTs = ymdToTs($startYmd);
|
||
$endTs = ymdToTs($endYmd);
|
||
if ($endTs < $startTs) return;
|
||
|
||
$endPlusTs = $endTs + 86400;
|
||
$endPlusYmd = tsToYmd($endPlusTs);
|
||
|
||
if (!isset($events[$productPk])) $events[$productPk] = [];
|
||
if (!isset($events[$productPk][$startYmd])) $events[$productPk][$startYmd] = 0.0;
|
||
if (!isset($events[$productPk][$endPlusYmd])) $events[$productPk][$endPlusYmd] = 0.0;
|
||
|
||
$events[$productPk][$startYmd] += $qty;
|
||
$events[$productPk][$endPlusYmd] -= $qty;
|
||
}
|
||
|
||
function computePeakAndHistogram(array $eventMap): array {
|
||
if (empty($eventMap)) return ['peak' => 0, 'hist' => []];
|
||
|
||
ksort($eventMap);
|
||
$dates = array_keys($eventMap);
|
||
|
||
$level = 0.0;
|
||
$peak = 0.0;
|
||
$hist = [];
|
||
|
||
for ($i = 0; $i < count($dates); $i++) {
|
||
$d = $dates[$i];
|
||
$level += (float)$eventMap[$d];
|
||
|
||
// clamp, damit kein negativer Bestand "Tage" erzeugt
|
||
if ($level < 0) $level = 0;
|
||
|
||
$intLevel = (int)round($level);
|
||
if ($intLevel > $peak) $peak = $intLevel;
|
||
|
||
if ($i < count($dates) - 1) {
|
||
$dTs = ymdToTs($d);
|
||
$nTs = ymdToTs($dates[$i + 1]);
|
||
$days = ($nTs - $dTs) / 86400.0;
|
||
if ($days > 0 && $intLevel > 0) {
|
||
if (!isset($hist[$intLevel])) $hist[$intLevel] = 0.0;
|
||
$hist[$intLevel] += $days;
|
||
}
|
||
}
|
||
}
|
||
|
||
ksort($hist);
|
||
foreach ($hist as $k => $v) $hist[$k] = (int)round($v);
|
||
|
||
return ['peak' => (int)$peak, 'hist' => $hist];
|
||
}
|
||
|
||
/* =========================
|
||
Data aggregation
|
||
========================= */
|
||
|
||
$rows = []; // Debug rows (HTML only)
|
||
$pivot = []; // direct revenue per year (net)
|
||
$pivotB = []; // bundle revenue per year allocated to leaf (net)
|
||
$pivotExt = []; // extern revenue per year (net) direct
|
||
$pivotExtB = []; // extern revenue per year allocated (net) for virtual bundles
|
||
|
||
$meta = []; // product meta [pk=>['product_no'=>..,'title'=>..]]
|
||
$allYears = [];
|
||
|
||
// Auftragsbasierte "Slots" (direct/incl) pro Produkt
|
||
$slotAgg = [];
|
||
|
||
// Kunden-Aggregation pro Produkt: [productPk][customerPk] = ['pk','name','direct','incl','ext_direct','ext_incl']
|
||
// Semantik analog zu $pivot / $pivotB:
|
||
// direct = Umsatz aus Rechnungen die diesen productPk DIREKT abgerechnet haben
|
||
// incl = direct + Umsatz aus virtuellen Bundles, deren Leaf dieser productPk ist
|
||
$customerAgg = [];
|
||
|
||
function addCustomerRevenue(int $productPk, array $customer, int $year, float $direct, float $incl, float $extDirect, float $extIncl): void {
|
||
global $customerAgg;
|
||
if ($productPk <= 0 || $year <= 0) return;
|
||
if ($direct == 0.0 && $incl == 0.0 && $extDirect == 0.0 && $extIncl == 0.0) return;
|
||
|
||
$cpk = (int)($customer['pk'] ?? 0);
|
||
$name = (string)($customer['name'] ?? '');
|
||
|
||
if (!isset($customerAgg[$productPk])) $customerAgg[$productPk] = [];
|
||
if (!isset($customerAgg[$productPk][$cpk])) {
|
||
$customerAgg[$productPk][$cpk] = [
|
||
'pk' => $cpk,
|
||
'name' => $name,
|
||
// Aufschlüsselung pro Jahr, damit Zeitraum-Filter sauber angewendet werden können
|
||
'by_year' => [],
|
||
];
|
||
} elseif ($customerAgg[$productPk][$cpk]['name'] === '' && $name !== '') {
|
||
$customerAgg[$productPk][$cpk]['name'] = $name;
|
||
}
|
||
|
||
if (!isset($customerAgg[$productPk][$cpk]['by_year'][$year])) {
|
||
$customerAgg[$productPk][$cpk]['by_year'][$year] = [
|
||
'direct' => 0.0, 'incl' => 0.0, 'ext_direct' => 0.0, 'ext_incl' => 0.0,
|
||
];
|
||
}
|
||
$customerAgg[$productPk][$cpk]['by_year'][$year]['direct'] += $direct;
|
||
$customerAgg[$productPk][$cpk]['by_year'][$year]['incl'] += $incl;
|
||
$customerAgg[$productPk][$cpk]['by_year'][$year]['ext_direct'] += $extDirect;
|
||
$customerAgg[$productPk][$cpk]['by_year'][$year]['ext_incl'] += $extIncl;
|
||
}
|
||
|
||
function ensureMeta(Epirent $Epi, int $productPk, int $productNo, string $title, string $invoiceDate = ''): void {
|
||
global $meta;
|
||
if (!isset($meta[$productPk])) {
|
||
$p = getProduct($Epi, $productPk);
|
||
$meta[$productPk] = [
|
||
'product_pk' => $productPk,
|
||
'product_no' => $productNo ?: (int)($p->product_no ?? 0),
|
||
'title' => $title ?: (string)($p->name ?? ''),
|
||
// titles: [title => letztes invoice_date, an dem dieser Titel auftauchte]
|
||
'titles' => [],
|
||
];
|
||
}
|
||
|
||
// Titel-Historie: nur Titel erfassen, die tatsächlich auf einer Rechnung standen,
|
||
// damit Umbenennungen (gleiche Artikelnummer, neuer Name) sichtbar bleiben.
|
||
if ($title !== '' && $invoiceDate !== '') {
|
||
$prev = $meta[$productPk]['titles'][$title] ?? '';
|
||
if ($invoiceDate > $prev) {
|
||
$meta[$productPk]['titles'][$title] = $invoiceDate;
|
||
}
|
||
}
|
||
}
|
||
|
||
function addRevenue(array &$pivotRef, int $productPk, int $year, float $revenueNet): void {
|
||
if ($productPk <= 0 || !$year) return;
|
||
if (!isset($pivotRef[$productPk])) $pivotRef[$productPk] = [];
|
||
if (!isset($pivotRef[$productPk][$year])) $pivotRef[$productPk][$year] = 0.0;
|
||
$pivotRef[$productPk][$year] += $revenueNet;
|
||
}
|
||
|
||
/**
|
||
* Extern-Logik:
|
||
* - Amount External ist eine ANZAHL (nicht Umsatz).
|
||
* - Wir berechnen extRevenue = sum_total_net * (amount_external / amount_total) (wenn amount_total>0)
|
||
*/
|
||
function calcExternalRevenueNet(object $li): float {
|
||
$amountTotal = (float)($li->amount_total ?? 0);
|
||
$amountExternal = (float)($li->amount_external ?? 0);
|
||
$net = (float)($li->sum_total_net ?? 0);
|
||
|
||
if ($net == 0.0) return 0.0;
|
||
if ($amountTotal <= 0.0) return 0.0;
|
||
if ($amountExternal <= 0.0) return 0.0;
|
||
|
||
if ($amountExternal > $amountTotal) $amountExternal = $amountTotal;
|
||
|
||
return $net * ($amountExternal / $amountTotal);
|
||
}
|
||
|
||
/* =========================
|
||
Slot (auftragsbasiert) – Storno-sicher (SIGNED)
|
||
========================= */
|
||
|
||
/**
|
||
* Intervall-Item für Slotting (ein "Auftragsteil" pro Produkt)
|
||
* qty kann positiv oder negativ sein (negativ = Storno/Rückbuchung)
|
||
*/
|
||
function addSlotItem(
|
||
array &$slotItems,
|
||
int $productPk,
|
||
int $invoicePk,
|
||
int $orderPk,
|
||
int $invoiceNo,
|
||
string $startYmd,
|
||
string $endYmd,
|
||
float $qty,
|
||
float $revenueNet,
|
||
float $extRevenueNet
|
||
): void {
|
||
$startYmd = safeYmd($startYmd);
|
||
$endYmd = safeYmd($endYmd);
|
||
if ($productPk <= 0) return;
|
||
if (!$startYmd || !$endYmd) return;
|
||
if ($qty == 0.0) return;
|
||
|
||
if (!isset($slotItems[$productPk])) $slotItems[$productPk] = [];
|
||
|
||
$slotItems[$productPk][] = [
|
||
'product_pk' => $productPk,
|
||
'invoice_pk' => $invoicePk,
|
||
'order_pk' => $orderPk,
|
||
'invoice_no' => $invoiceNo,
|
||
'start_ts' => ymdToTs($startYmd),
|
||
'end_ts' => ymdToTs($endYmd),
|
||
'qty' => (float)$qty, // signed erlaubt
|
||
'rev' => (float)$revenueNet,
|
||
'rev_ext' => (float)$extRevenueNet,
|
||
'start_ymd' => $startYmd,
|
||
'end_ymd' => $endYmd,
|
||
];
|
||
}
|
||
|
||
/**
|
||
* Slotting (SIGNED, Storno gibt Slots wieder frei):
|
||
* - Positive qty belegt Slots.
|
||
* - Negative qty bucht Umsatz zurück UND gibt Slots frei.
|
||
* - Priorität bei Storno: zuerst Slots mit gleichem Zeitraum (gleicher release_ts) abbauen.
|
||
*
|
||
* Ergebnis:
|
||
* - 'direct' => [slot => revenue]
|
||
* - 'direct_ext' => [slot => extRevenue]
|
||
*/
|
||
function computeOrderBasedSlots(array $itemsForProduct): array {
|
||
if (empty($itemsForProduct)) {
|
||
return ['direct' => [], 'direct_ext' => []];
|
||
}
|
||
|
||
// Sortierung: start ASC, end DESC, invoice_no ASC, invoice_pk ASC
|
||
usort($itemsForProduct, function($a, $b){
|
||
if (($a['start_ts'] ?? 0) !== ($b['start_ts'] ?? 0)) return ($a['start_ts'] ?? 0) <=> ($b['start_ts'] ?? 0);
|
||
if (($a['end_ts'] ?? 0) !== ($b['end_ts'] ?? 0)) return ($b['end_ts'] ?? 0) <=> ($a['end_ts'] ?? 0);
|
||
if (($a['invoice_no'] ?? 0) !== ($b['invoice_no'] ?? 0)) return ($a['invoice_no'] ?? 0) <=> ($b['invoice_no'] ?? 0);
|
||
return ($a['invoice_pk'] ?? 0) <=> ($b['invoice_pk'] ?? 0);
|
||
});
|
||
|
||
// Active slots: slotIndex => release_ts
|
||
$active = [];
|
||
// release_ts => [slotIndex, ...]
|
||
$activeByRelease = [];
|
||
// free slot indices
|
||
$freeSlots = [];
|
||
|
||
// Revenues per slot
|
||
$slotRevenue = [];
|
||
$slotRevenueExt = [];
|
||
|
||
$releaseTsFor = function(int $endTs): int {
|
||
// Ende inklusiv -> frei am Folgetag 00:00
|
||
return $endTs + 86400;
|
||
};
|
||
|
||
$freeExpired = function(int $currentStartTs) use (&$active, &$activeByRelease, &$freeSlots) {
|
||
if (empty($active)) return;
|
||
foreach ($active as $slot => $relTs) {
|
||
if ($relTs <= $currentStartTs) {
|
||
unset($active[$slot]);
|
||
if (isset($activeByRelease[$relTs])) {
|
||
$activeByRelease[$relTs] = array_values(array_filter(
|
||
$activeByRelease[$relTs],
|
||
fn($s) => (int)$s !== (int)$slot
|
||
));
|
||
if (empty($activeByRelease[$relTs])) unset($activeByRelease[$relTs]);
|
||
}
|
||
$freeSlots[] = (int)$slot;
|
||
}
|
||
}
|
||
};
|
||
|
||
$takeFreeSlot = function() use (&$freeSlots, &$active): int {
|
||
if (!empty($freeSlots)) {
|
||
sort($freeSlots);
|
||
return (int)array_shift($freeSlots);
|
||
}
|
||
if (empty($active)) return 1;
|
||
$keys = array_keys($active);
|
||
return (int)(max($keys) + 1);
|
||
};
|
||
|
||
foreach ($itemsForProduct as $it) {
|
||
$startTs = (int)($it['start_ts'] ?? 0);
|
||
$endTs = (int)($it['end_ts'] ?? 0);
|
||
if ($startTs <= 0 || $endTs <= 0) continue;
|
||
if ($endTs < $startTs) continue;
|
||
|
||
// vor jeder Aktion: abgelaufene Slots freigeben
|
||
$freeExpired($startTs);
|
||
|
||
$qtySigned = (float)($it['qty'] ?? 0.0);
|
||
if ($qtySigned == 0.0) continue;
|
||
|
||
$qtyAbs = (int)round(abs($qtySigned));
|
||
if ($qtyAbs <= 0) continue;
|
||
|
||
$rev = (float)($it['rev'] ?? 0.0);
|
||
$ext = (float)($it['rev_ext'] ?? 0.0);
|
||
|
||
$revPer = $rev / $qtyAbs;
|
||
$extPer = $ext / $qtyAbs;
|
||
|
||
$relTs = $releaseTsFor($endTs);
|
||
|
||
if ($qtySigned > 0) {
|
||
// --- Belegen ---
|
||
for ($k = 0; $k < $qtyAbs; $k++) {
|
||
$slot = $takeFreeSlot();
|
||
|
||
$active[$slot] = $relTs;
|
||
if (!isset($activeByRelease[$relTs])) $activeByRelease[$relTs] = [];
|
||
$activeByRelease[$relTs][] = $slot;
|
||
|
||
if (!isset($slotRevenue[$slot])) $slotRevenue[$slot] = 0.0;
|
||
if (!isset($slotRevenueExt[$slot])) $slotRevenueExt[$slot] = 0.0;
|
||
|
||
$slotRevenue[$slot] += $revPer;
|
||
$slotRevenueExt[$slot] += $extPer;
|
||
}
|
||
} else {
|
||
// --- Storno / Freigeben ---
|
||
// erst gleiche release_ts abbauen (damit "Rechnung+Storno im gleichen Zeitraum" sauber 0 Slots macht)
|
||
$candidates = $activeByRelease[$relTs] ?? [];
|
||
|
||
for ($k = 0; $k < $qtyAbs; $k++) {
|
||
$slot = null;
|
||
|
||
if (!empty($candidates)) {
|
||
rsort($candidates); // höchste Slotnummer zuerst abbauen
|
||
$slot = (int)array_shift($candidates);
|
||
} else {
|
||
if (!empty($active)) {
|
||
$keys = array_keys($active);
|
||
rsort($keys);
|
||
$slot = (int)$keys[0];
|
||
}
|
||
}
|
||
|
||
if ($slot === null) break;
|
||
|
||
// Umsatz auf Slot zurückbuchen
|
||
if (!isset($slotRevenue[$slot])) $slotRevenue[$slot] = 0.0;
|
||
if (!isset($slotRevenueExt[$slot])) $slotRevenueExt[$slot] = 0.0;
|
||
$slotRevenue[$slot] += $revPer; // revPer ist i.d.R. negativ
|
||
$slotRevenueExt[$slot] += $extPer; // extPer ist i.d.R. negativ
|
||
|
||
// Slot freigeben
|
||
$oldRel = $active[$slot] ?? null;
|
||
unset($active[$slot]);
|
||
|
||
if ($oldRel !== null && isset($activeByRelease[$oldRel])) {
|
||
$activeByRelease[$oldRel] = array_values(array_filter(
|
||
$activeByRelease[$oldRel],
|
||
fn($s) => (int)$s !== (int)$slot
|
||
));
|
||
if (empty($activeByRelease[$oldRel])) unset($activeByRelease[$oldRel]);
|
||
}
|
||
|
||
$freeSlots[] = $slot;
|
||
}
|
||
}
|
||
}
|
||
|
||
// --- Cleanup: Slots ohne Umsatz entfernen + neu durchnummerieren ---
|
||
$eps = 0.00001;
|
||
$allSlots = array_unique(array_merge(array_keys($slotRevenue), array_keys($slotRevenueExt)));
|
||
sort($allSlots);
|
||
|
||
$kept = [];
|
||
foreach ($allSlots as $s) {
|
||
$rev = (float)($slotRevenue[$s] ?? 0.0);
|
||
$ext = (float)($slotRevenueExt[$s] ?? 0.0);
|
||
if (abs($rev) < $eps && abs($ext) < $eps) continue;
|
||
$kept[] = $s;
|
||
}
|
||
|
||
$newRev = [];
|
||
$newExt = [];
|
||
$idx = 1;
|
||
foreach ($kept as $old) {
|
||
$newRev[$idx] = (float)($slotRevenue[$old] ?? 0.0);
|
||
$newExt[$idx] = (float)($slotRevenueExt[$old] ?? 0.0);
|
||
$idx++;
|
||
}
|
||
|
||
return ['direct' => $newRev, 'direct_ext' => $newExt];
|
||
}
|
||
|
||
/* =========================
|
||
Prozessierung einer Artikelzeile
|
||
========================= */
|
||
|
||
function processLineItem(
|
||
Epirent $Epi,
|
||
array &$rows,
|
||
array &$slotItemsDirect,
|
||
array &$slotItemsIncl,
|
||
int $orderPk,
|
||
int $invoicePk,
|
||
int $invoiceNo,
|
||
string $invoiceDate,
|
||
int $invoiceYear,
|
||
?int $chapterId,
|
||
object $li,
|
||
bool $isFromJournal,
|
||
bool $invoiceIsCredit, // <-- wichtig: Storno-Rechnung (z.B. sum_net < 0)
|
||
array $customer = ['pk'=>0, 'name'=>'Unbekannt']
|
||
): void {
|
||
global $pivot, $pivotB, $pivotExt, $pivotExtB, $allYears, $eventsDirect, $eventsIncl;
|
||
|
||
$type = (int)($li->type ?? -1);
|
||
$productPk = (int)($li->product_pk ?? 0);
|
||
if ($type !== 0 || $productPk <= 0) return; // nur echte Artikelpositionen
|
||
|
||
$title = (string)($li->title ?? '');
|
||
$productNo = (int)($li->product_no ?? 0);
|
||
|
||
$qtyRaw = (float)($li->amount_total ?? 0);
|
||
if ($qtyRaw == 0.0) $qtyRaw = 0.0;
|
||
|
||
// Umsatz bleibt wie geliefert (kann negativ sein)
|
||
$revenueNet = (float)($li->sum_total_net ?? 0);
|
||
$extRevenueNet = calcExternalRevenueNet($li);
|
||
|
||
// Für Auslastung/Slots: wenn Storno-Rechnung, Menge "negativ signieren"
|
||
// (weil Epi bei Storno typischerweise Menge positiv lässt, aber Preise negativ macht)
|
||
$qtySigned = $qtyRaw;
|
||
if ($invoiceIsCredit && $qtySigned != 0.0) {
|
||
$qtySigned = -abs($qtySigned);
|
||
}
|
||
|
||
$dateStart = safeYmd((string)($li->date_start ?? '')) ?: null;
|
||
$dateEnd = safeYmd((string)($li->date_end ?? '')) ?: null;
|
||
|
||
ensureMeta($Epi, $productPk, $productNo, $title, $invoiceDate);
|
||
|
||
// Jahr: Rechnungsdatum
|
||
$year = $invoiceYear;
|
||
if ($year) $allYears[$year] = true;
|
||
|
||
// Direct Umsatz
|
||
if ($revenueNet != 0.0 && $year) addRevenue($pivot, $productPk, $year, $revenueNet);
|
||
|
||
// Direct Extern-Umsatz
|
||
if ($extRevenueNet != 0.0 && $year) addRevenue($pivotExt, $productPk, $year, $extRevenueNet);
|
||
|
||
// Kunden-Aggregation für direkten Umsatz (direct wird IMMER auch in incl gezählt)
|
||
if ($year && ($revenueNet != 0.0 || $extRevenueNet != 0.0)) {
|
||
addCustomerRevenue($productPk, $customer, (int)$year, $revenueNet, $revenueNet, $extRevenueNet, $extRevenueNet);
|
||
}
|
||
|
||
// Peak/Histogram direct + incl (tagesbasiert)
|
||
// -> qtySigned sorgt dafür, dass Storno zeitgleich aufhebt
|
||
if ($dateStart && $dateEnd && $qtySigned != 0.0) {
|
||
addIntervalEvent($eventsDirect, $productPk, $dateStart, $dateEnd, $qtySigned);
|
||
addIntervalEvent($eventsIncl, $productPk, $dateStart, $dateEnd, $qtySigned);
|
||
}
|
||
|
||
// Slotting: direct (signed qty)
|
||
if ($dateStart && $dateEnd && $qtySigned != 0.0) {
|
||
addSlotItem($slotItemsDirect, $productPk, $invoicePk, $orderPk, $invoiceNo, $dateStart, $dateEnd, $qtySigned, $revenueNet, $extRevenueNet);
|
||
addSlotItem($slotItemsIncl, $productPk, $invoicePk, $orderPk, $invoiceNo, $dateStart, $dateEnd, $qtySigned, $revenueNet, $extRevenueNet);
|
||
}
|
||
|
||
// Debug Row
|
||
$rows[] = [
|
||
'year' => $year,
|
||
'invoice_pk' => $invoicePk,
|
||
'invoice_no' => $invoiceNo,
|
||
'invoice_date'=> $invoiceDate,
|
||
'chapter_id' => $chapterId ?? 0,
|
||
'source' => $isFromJournal ? 'journal' : 'direct',
|
||
'product_pk' => $productPk,
|
||
'product_no' => $productNo,
|
||
'title' => $title,
|
||
'date_start' => $dateStart ?? '',
|
||
'date_end' => $dateEnd ?? '',
|
||
'qty' => $qtyRaw,
|
||
'qty_signed' => $qtySigned,
|
||
'revenue_net' => $revenueNet,
|
||
'ext_rev_net' => $extRevenueNet,
|
||
'amount_ext' => (float)($li->amount_external ?? 0),
|
||
'amount_total'=> (float)($li->amount_total ?? 0),
|
||
'is_credit' => $invoiceIsCredit ? 1 : 0,
|
||
];
|
||
|
||
/* ===== Bundle-Auflösung (nur virtuelle Bundles!) ===== */
|
||
$leafMap = resolveBundleLeafMap($Epi, $productPk);
|
||
$isVirtualBundle = !(count($leafMap) === 1 && isset($leafMap[$productPk]) && (float)$leafMap[$productPk] === 1.0);
|
||
|
||
if ($isVirtualBundle) {
|
||
// Umsatz allokieren (inkl Bundle-Anteil)
|
||
$allocForCustomer = ($revenueNet != 0.0) ? allocateBundleRevenue($Epi, $productPk, $revenueNet) : [];
|
||
$allocExtForCustomer = ($extRevenueNet != 0.0) ? allocateBundleRevenue($Epi, $productPk, $extRevenueNet) : [];
|
||
|
||
if ($revenueNet != 0.0 && $year) {
|
||
foreach ($allocForCustomer as $leafPk => $leafRevenue) {
|
||
$leafPk = (int)$leafPk;
|
||
ensureMeta($Epi, $leafPk, 0, '');
|
||
addRevenue($pivotB, $leafPk, $year, (float)$leafRevenue);
|
||
}
|
||
}
|
||
|
||
// Extern-Umsatz allokieren (inkl Bundle-Anteil)
|
||
if ($extRevenueNet != 0.0 && $year) {
|
||
foreach ($allocExtForCustomer as $leafPk => $leafRevenue) {
|
||
$leafPk = (int)$leafPk;
|
||
ensureMeta($Epi, $leafPk, 0, '');
|
||
addRevenue($pivotExtB, $leafPk, $year, (float)$leafRevenue);
|
||
}
|
||
}
|
||
|
||
// Kunden-Aggregation für Leafs (nur "incl", direct ist 0, da nicht direkt abgerechnet)
|
||
if ($year && (!empty($allocForCustomer) || !empty($allocExtForCustomer))) {
|
||
$leafKeys = array_unique(array_merge(array_keys($allocForCustomer), array_keys($allocExtForCustomer)));
|
||
foreach ($leafKeys as $leafPk) {
|
||
$leafPk = (int)$leafPk;
|
||
if ($leafPk <= 0) continue;
|
||
$leafRev = (float)($allocForCustomer[$leafPk] ?? 0.0);
|
||
$leafExt = (float)($allocExtForCustomer[$leafPk] ?? 0.0);
|
||
addCustomerRevenue($leafPk, $customer, (int)$year, 0.0, $leafRev, 0.0, $leafExt);
|
||
}
|
||
}
|
||
|
||
// Auslastung allokieren (Peak/Histogramm inkl Bundle) – signed qty!
|
||
if ($dateStart && $dateEnd && $qtySigned != 0.0) {
|
||
foreach ($leafMap as $leafPk => $amt) {
|
||
$leafPk = (int)$leafPk;
|
||
$amt = (float)$amt;
|
||
if ($leafPk <= 0 || $amt <= 0) continue;
|
||
ensureMeta($Epi, $leafPk, 0, '');
|
||
addIntervalEvent($eventsIncl, $leafPk, $dateStart, $dateEnd, $qtySigned * $amt);
|
||
}
|
||
}
|
||
|
||
// Slotting allokieren (inkl Bundle) – signed qty!
|
||
if ($dateStart && $dateEnd && $qtySigned != 0.0) {
|
||
// Bundle-Allokationen aus dem Umsatz-Block wiederverwenden
|
||
foreach ($leafMap as $leafPk => $amt) {
|
||
$leafPk = (int)$leafPk;
|
||
$amt = (float)$amt;
|
||
if ($leafPk <= 0 || $amt <= 0) continue;
|
||
|
||
$leafRev = (float)($allocForCustomer[$leafPk] ?? 0.0);
|
||
$leafExt = (float)($allocExtForCustomer[$leafPk] ?? 0.0);
|
||
|
||
$qtyLeafSigned = $qtySigned * $amt;
|
||
|
||
ensureMeta($Epi, $leafPk, 0, '');
|
||
addSlotItem($slotItemsIncl, $leafPk, $invoicePk, $orderPk, $invoiceNo, $dateStart, $dateEnd, $qtyLeafSigned, $leafRev, $leafExt);
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
/* =========================
|
||
1) Invoices holen + Daten sammeln
|
||
========================= */
|
||
|
||
// Defaults für die HTML-Ausgabe, wenn keine Berechnung läuft
|
||
$years = [];
|
||
$pivotRows = [];
|
||
$excelFileName = '';
|
||
$excelDownloadUrl = '';
|
||
|
||
/* =========================
|
||
Cache-Entscheidung (inkrementell)
|
||
– Cache-Datei enthält Rohdaten + eine Liste "cached_ranges" (welche Jahre bereits gecacht).
|
||
– Bei einer Abfrage werden nur die Jahre im Cache-Config-Range gefetcht, die NOCH NICHT
|
||
abgedeckt sind. Diese neuen Jahre werden anschließend an den Cache angehängt.
|
||
– Jahre außerhalb der Cache-Config-Range werden IMMER live geladen (nie in den Cache).
|
||
– Reset per Reload-Button (?refresh_cache=1) wirft die Cache-Datei komplett weg
|
||
und baut sie im vollen Cache-Config-Range neu.
|
||
|
||
Setzt: $loadedFromCache, $rebuildCache, $doLiveLoop, $liveFrom, $liveTo,
|
||
$missingCacheRanges (Liste von Ranges, die ins Cache nachgeladen werden),
|
||
$cachedRanges (bereits abgedeckte Ranges, wird beim Loop erweitert).
|
||
========================= */
|
||
$loadedFromCache = false;
|
||
$rebuildCache = false;
|
||
$doLiveLoop = false;
|
||
$liveFrom = 0;
|
||
$liveTo = 0;
|
||
$cacheDataMeta = null;
|
||
$cacheFilterApplied = null;
|
||
$cachedRanges = []; // aktuell abgedeckte Bereiche
|
||
$missingCacheRanges = []; // Bereiche, die noch fetched werden müssen
|
||
$liveBelowFrom = 0; $liveBelowTo = 0;
|
||
$doLiveLoopBelow = false;
|
||
|
||
if ($cacheEnabled) {
|
||
$cacheExists = file_exists($cacheFile);
|
||
|
||
// Cache-Meta vorab lesen (Config-Wechsel-Erkennung, cached_ranges)
|
||
if ($cacheExists) {
|
||
$peek = peekRoiCacheMeta($cacheFile);
|
||
if ($peek !== null) {
|
||
$cachedRanges = $peek['cached_ranges'];
|
||
} else {
|
||
// veraltet/defekt → wie kein Cache
|
||
$cacheExists = false;
|
||
}
|
||
}
|
||
|
||
// Anzeige-Zeitraum bestimmen (0 = kein Limit)
|
||
if ($filterMode === 'range') {
|
||
$viewFrom = (int)$filterFrom;
|
||
$viewTo = (int)$filterTo;
|
||
} else {
|
||
$viewFrom = 0;
|
||
$viewTo = 0;
|
||
}
|
||
|
||
if ($cacheRefreshRequested) {
|
||
// Kompletter Neu-Build: alten Cache verwerfen, konfigurierte Cache-Range komplett fetchen
|
||
$rebuildCache = true;
|
||
$cachedRanges = [];
|
||
$missingCacheRanges = [['from' => $cacheFrom, 'to' => $cacheTo]];
|
||
// Live-Anteile (immer)
|
||
if ($cacheTo !== null && ($viewTo === 0 || $viewTo > $cacheTo)) {
|
||
$liveFrom = $cacheTo + 1;
|
||
$liveTo = $viewTo;
|
||
$doLiveLoop = true;
|
||
}
|
||
if ($cacheFrom !== null && $viewFrom > 0 && $viewFrom < $cacheFrom) {
|
||
$liveBelowFrom = $viewFrom;
|
||
$liveBelowTo = min($cacheFrom - 1, $viewTo > 0 ? $viewTo : $cacheFrom - 1);
|
||
$doLiveLoopBelow = true;
|
||
}
|
||
$computeRoi = true;
|
||
} elseif ($computeRoi) {
|
||
// Cache-Interested Range = User-Range ∩ Cache-Config-Range
|
||
// ("null" auf Cache-Seite bedeutet unbeschränkt)
|
||
$askFromCache = ($cacheFrom !== null && ($viewFrom === 0 || $viewFrom < $cacheFrom))
|
||
? $cacheFrom
|
||
: (($viewFrom === 0) ? null : $viewFrom);
|
||
$askToCache = ($cacheTo !== null && ($viewTo === 0 || $viewTo > $cacheTo))
|
||
? $cacheTo
|
||
: (($viewTo === 0) ? null : $viewTo);
|
||
|
||
$hasCachePart = true;
|
||
if ($askFromCache !== null && $askToCache !== null && $askFromCache > $askToCache) {
|
||
$hasCachePart = false;
|
||
}
|
||
// Cache-Config-Range komplett unterhalb der Anzeige?
|
||
if ($cacheTo !== null && $viewFrom > 0 && $viewFrom > $cacheTo) $hasCachePart = false;
|
||
// Cache-Config-Range komplett oberhalb der Anzeige?
|
||
if ($cacheFrom !== null && $viewTo > 0 && $viewTo < $cacheFrom) $hasCachePart = false;
|
||
|
||
if ($hasCachePart) {
|
||
$loadedFromCache = true; // wird geladen (auch wenn Datei leer ist)
|
||
// Fehlende Jahre im Cache-Anteil bestimmen
|
||
if ($askFromCache !== null && $askToCache !== null) {
|
||
// Beide Grenzen bounded → Set-Filter für Missing Years
|
||
$missingCacheRanges = computeMissingRanges($cachedRanges, (int)$askFromCache, (int)$askToCache);
|
||
} else {
|
||
// Mindestens eine Seite unbounded → wir müssen einen Range-Loop machen.
|
||
// Falls bereits ein Range im Cache existiert der uns komplett abdeckt: keine Aktion.
|
||
$needFetch = true;
|
||
foreach ($cachedRanges as $r) {
|
||
$rf = $r['from'] ?? null;
|
||
$rt = $r['to'] ?? null;
|
||
$covers = true;
|
||
if ($askFromCache !== null && ($rf !== null && $rf > $askFromCache)) $covers = false;
|
||
if ($askToCache !== null && ($rt !== null && $rt < $askToCache)) $covers = false;
|
||
if ($askFromCache === null && $rf !== null) $covers = false;
|
||
if ($askToCache === null && $rt !== null) $covers = false;
|
||
if ($covers) { $needFetch = false; break; }
|
||
}
|
||
if ($needFetch) {
|
||
$missingCacheRanges = [['from' => $askFromCache, 'to' => $askToCache]];
|
||
}
|
||
}
|
||
}
|
||
|
||
// Live-Anteile berechnen (immer live, nie gecacht)
|
||
if ($cacheTo !== null && ($viewTo === 0 || $viewTo > $cacheTo)) {
|
||
$liveFrom = max($cacheTo + 1, (int)$viewFrom);
|
||
$liveTo = (int)$viewTo;
|
||
$doLiveLoop = true;
|
||
}
|
||
if ($cacheFrom !== null && $viewFrom > 0 && $viewFrom < $cacheFrom) {
|
||
$liveBelowFrom = (int)$viewFrom;
|
||
$liveBelowTo = min($cacheFrom - 1, $viewTo > 0 ? (int)$viewTo : $cacheFrom - 1);
|
||
$doLiveLoopBelow = true;
|
||
}
|
||
// Wenn User-Range komplett außerhalb Cache-Range → nur Live (keine Cache-Aktion)
|
||
if (!$hasCachePart) {
|
||
$loadedFromCache = false;
|
||
$missingCacheRanges = [];
|
||
// Live-Loop nutzt User-Range direkt
|
||
if (!$doLiveLoop && !$doLiveLoopBelow) {
|
||
$doLiveLoop = true;
|
||
$liveFrom = (int)$viewFrom;
|
||
$liveTo = (int)$viewTo;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Führt den Invoice-Loop für einen Jahres-Filter aus.
|
||
* Filter-Modi:
|
||
* - Range: $filterFromEff / $filterToEff, jeweils 0 = kein Limit. (0,0) = alles.
|
||
* - Set: $yearSet = [year => true, ...]. Wenn nicht null: nur diese Jahre werden verarbeitet;
|
||
* $filterFromEff / $filterToEff werden dann ignoriert.
|
||
* Nutzt die globalen Datenstrukturen (rows, slotItems*, meta, pivot* etc.).
|
||
* Ruft /v1/invoice/all nur einmal auf; die Liste wird via static gecached.
|
||
*/
|
||
function runInvoiceLoop(Epirent $Epi, int $filterFromEff, int $filterToEff, ?array $yearSet = null): void {
|
||
global $rows, $slotItemsDirect, $slotItemsIncl;
|
||
static $invoiceListCached = null;
|
||
|
||
if ($invoiceListCached === null) {
|
||
$invoiceAll = apiJsonDecode($Epi->requestEpiApi('/v1/invoice/all?ir=true&ib=true&cl=' . Epirent_Mandant));
|
||
$invoiceListCached = ($invoiceAll && ($invoiceAll->success ?? false) && is_array($invoiceAll->payload ?? null))
|
||
? $invoiceAll->payload : [];
|
||
}
|
||
|
||
$useSet = ($yearSet !== null && !empty($yearSet));
|
||
|
||
foreach ($invoiceListCached as $inv) {
|
||
$invoicePk = (int)($inv->primary_key ?? 0);
|
||
if ($invoicePk <= 0) continue;
|
||
|
||
// Vorab-Filter aus Listen-Item (spart Detail-Call, wenn invoice_date schon dabei)
|
||
$listYear = yearFromDate((string)($inv->invoice_date ?? '')) ?? 0;
|
||
if ($listYear > 0) {
|
||
if ($useSet) {
|
||
if (!isset($yearSet[$listYear])) continue;
|
||
} else {
|
||
if ($filterFromEff > 0 && $listYear < $filterFromEff) continue;
|
||
if ($filterToEff > 0 && $listYear > $filterToEff) continue;
|
||
}
|
||
}
|
||
|
||
$invRes = apiJsonDecode($Epi->requestEpiApi('/v1/invoice/' . $invoicePk . '?cl=' . Epirent_Mandant));
|
||
$invObj = ($invRes && ($invRes->success ?? false) && !empty($invRes->payload[0])) ? $invRes->payload[0] : null;
|
||
if (!$invObj) continue;
|
||
|
||
$invoiceDate = (string)($invObj->invoice_date ?? '');
|
||
$invoiceYear = yearFromDate($invoiceDate) ?? 0;
|
||
$invoiceNo = (int)($invObj->invoice_no ?? 0);
|
||
$orderPk = (int)($invObj->order_pk ?? 0);
|
||
|
||
// Endgültiger Year-Filter (falls Listen-Antwort kein Datum hatte)
|
||
if ($invoiceYear <= 0) continue;
|
||
if ($useSet) {
|
||
if (!isset($yearSet[$invoiceYear])) continue;
|
||
} else {
|
||
if ($filterFromEff > 0 && $invoiceYear < $filterFromEff) continue;
|
||
if ($filterToEff > 0 && $invoiceYear > $filterToEff) continue;
|
||
}
|
||
|
||
$invoiceIsCredit = ((float)($invObj->sum_net ?? 0.0)) < 0.0;
|
||
$customer = resolveInvoiceCustomer($Epi, $invObj, $orderPk);
|
||
|
||
$orderItems = $invObj->order_items ?? [];
|
||
if (!is_array($orderItems)) $orderItems = [];
|
||
|
||
foreach ($orderItems as $oi) {
|
||
$oiType = (int)($oi->type ?? -1);
|
||
$oiPk = (int)($oi->primary_key ?? 0);
|
||
|
||
if ($oiType === 5 && $oiPk > 0) {
|
||
$chapterId = $oiPk;
|
||
$journalItems = getJournalByChapter($Epi, $chapterId);
|
||
foreach ($journalItems as $ji) {
|
||
processLineItem($Epi, $rows, $slotItemsDirect, $slotItemsIncl,
|
||
$orderPk, $invoicePk, $invoiceNo, $invoiceDate, $invoiceYear,
|
||
$chapterId, $ji, true, $invoiceIsCredit, $customer);
|
||
}
|
||
continue;
|
||
}
|
||
|
||
if ($oiType === 0 && (int)($oi->product_pk ?? 0) > 0) {
|
||
processLineItem($Epi, $rows, $slotItemsDirect, $slotItemsIncl,
|
||
$orderPk, $invoicePk, $invoiceNo, $invoiceDate, $invoiceYear,
|
||
null, $oi, false, $invoiceIsCredit, $customer);
|
||
continue;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
if ($computeRoi) {
|
||
|
||
// Alle Aggregations-Globals unconditional initialisieren. loadRoiCache() überschreibt sie
|
||
// im Erfolgsfall aus der Cache-Datei; ohne Datei bleiben sie leer und die Loops füllen sie.
|
||
$rows = [];
|
||
$slotItemsDirect = [];
|
||
$slotItemsIncl = [];
|
||
$meta = [];
|
||
$pivot = []; $pivotB = []; $pivotExt = []; $pivotExtB = [];
|
||
$eventsDirect = []; $eventsIncl = [];
|
||
$customerAgg = []; $allYears = [];
|
||
$productCache = []; $bundleLeafCache = []; $rentPriceCache = [];
|
||
$journalCache = []; $orderCustomerCache = [];
|
||
|
||
// Cache-Load-Phase: Rohdaten aus Datei in Globals holen (nur wenn nicht rebuild)
|
||
if ($loadedFromCache && !$rebuildCache && file_exists($cacheFile)) {
|
||
$cacheDataMeta = loadRoiCache($cacheFile);
|
||
if ($cacheDataMeta === null) {
|
||
// Cache defekt/veraltet: als nicht-vorhanden behandeln (Globals bleiben leer)
|
||
$loadedFromCache = false;
|
||
$cachedRanges = [];
|
||
} else {
|
||
$cachedRanges = $cacheDataMeta['cached_ranges'];
|
||
}
|
||
}
|
||
|
||
// Cache-Add-Phase: fehlende Cache-Ranges nachladen und in cachedRanges eintragen
|
||
if (!empty($missingCacheRanges)) {
|
||
// Bounded Ranges können in einem einzigen Set-Loop erledigt werden.
|
||
$boundedSet = [];
|
||
$unboundedRanges = [];
|
||
foreach ($missingCacheRanges as $r) {
|
||
$from = $r['from']; $to = $r['to'];
|
||
if ($from !== null && $to !== null) {
|
||
for ($y = (int)$from; $y <= (int)$to; $y++) $boundedSet[$y] = true;
|
||
} else {
|
||
$unboundedRanges[] = $r;
|
||
}
|
||
}
|
||
if (!empty($boundedSet)) {
|
||
runInvoiceLoop($Epi, 0, 0, $boundedSet);
|
||
}
|
||
foreach ($unboundedRanges as $r) {
|
||
$from = $r['from']; $to = $r['to'];
|
||
runInvoiceLoop($Epi, $from !== null ? (int)$from : 0, $to !== null ? (int)$to : 0);
|
||
}
|
||
// Neu abgedeckte Bereiche in cachedRanges übernehmen
|
||
foreach ($missingCacheRanges as $r) {
|
||
$cachedRanges = addToCachedRanges($cachedRanges, $r['from'], $r['to']);
|
||
}
|
||
// Cache-Datei jetzt speichern (VOR dem Live-Loop, damit dessen Daten nicht mit reinkommen)
|
||
@saveRoiCache($cacheFile, $cacheFrom, $cacheTo, $cachedRanges);
|
||
$cacheDataMeta = [
|
||
'ts' => time(),
|
||
'cache_from' => $cacheFrom,
|
||
'cache_to' => $cacheTo,
|
||
'cached_ranges' => $cachedRanges,
|
||
];
|
||
}
|
||
|
||
// Live-Add-Phase: Loop für den Live-Range außerhalb der Cache-Config-Range
|
||
if ($doLiveLoopBelow) {
|
||
runInvoiceLoop($Epi, (int)$liveBelowFrom, (int)$liveBelowTo);
|
||
}
|
||
if ($doLiveLoop) {
|
||
runInvoiceLoop($Epi, (int)$liveFrom, (int)$liveTo);
|
||
}
|
||
|
||
/* =========================
|
||
1b) Rohdaten-Filter (User-Zeitraum)
|
||
– Wenn der User einen konkreten Zeitraum gewählt hat, werden hier die Rohdaten
|
||
(rows, pivot*, events*, slotItems*, customerAgg, allYears) auf den Range gefiltert.
|
||
– Die anschließende Aggregation (Slots, Peak/Histogramm, Pivot-Rows, Kunden) läuft
|
||
dadurch AUTOMATISCH nur über den gewählten Zeitraum – kein Post-Filter nötig.
|
||
========================= */
|
||
if ($filterMode === 'range' && $filterFrom > 0 && $filterTo > 0) {
|
||
applyRangeFilterToRawData((int)$filterFrom, (int)$filterTo);
|
||
$cacheFilterApplied = ['from' => (int)$filterFrom, 'to' => (int)$filterTo];
|
||
}
|
||
|
||
/* =========================
|
||
2) Slot Aggregation (auftragsbasiert)
|
||
========================= */
|
||
|
||
foreach ($meta as $productPk => $m) {
|
||
$productPk = (int)$productPk;
|
||
|
||
$resD = computeOrderBasedSlots($slotItemsDirect[$productPk] ?? []);
|
||
$resI = computeOrderBasedSlots($slotItemsIncl[$productPk] ?? []);
|
||
|
||
$slotAgg[$productPk] = [
|
||
'direct' => $resD['direct'] ?? [],
|
||
'incl' => $resI['direct'] ?? [],
|
||
'direct_ext' => $resD['direct_ext'] ?? [],
|
||
'incl_ext' => $resI['direct_ext'] ?? [],
|
||
];
|
||
}
|
||
|
||
/* =========================
|
||
3) Years + Pivot Rows bauen
|
||
========================= */
|
||
|
||
$years = array_keys($allYears);
|
||
sort($years);
|
||
|
||
$pivotRows = [];
|
||
|
||
foreach ($meta as $productPk => $m) {
|
||
$productPk = (int)$productPk;
|
||
|
||
// Titel-Historie auswerten: neuester Titel als Hauptname, restliche als "früher"-Liste
|
||
$titlesMap = $m['titles'] ?? [];
|
||
if (!empty($titlesMap)) {
|
||
arsort($titlesMap); // nach letztem invoice_date absteigend
|
||
$orderedTitles = array_keys($titlesMap);
|
||
$newestTitle = (string)$orderedTitles[0];
|
||
$olderTitles = array_slice($orderedTitles, 1);
|
||
} else {
|
||
$newestTitle = (string)($m['title'] ?? '');
|
||
$olderTitles = [];
|
||
}
|
||
|
||
$row = [
|
||
'product_pk' => $productPk,
|
||
'product_no' => (int)($m['product_no'] ?? 0),
|
||
'title' => $newestTitle,
|
||
'older_titles' => $olderTitles,
|
||
'years' => [],
|
||
'years_ext' => [],
|
||
'total_direct'=> 0.0,
|
||
'total_incl' => 0.0,
|
||
'total_ext_direct'=> 0.0,
|
||
'total_ext_incl' => 0.0,
|
||
'peak_direct' => 0,
|
||
'peak_incl' => 0,
|
||
'hist_direct' => [],
|
||
'hist_incl' => [],
|
||
'slots_direct' => $slotAgg[$productPk]['direct'] ?? [],
|
||
'slots_incl' => $slotAgg[$productPk]['incl'] ?? [],
|
||
'slots_direct_ext' => $slotAgg[$productPk]['direct_ext'] ?? [],
|
||
'slots_incl_ext' => $slotAgg[$productPk]['incl_ext'] ?? [],
|
||
'customers' => [],
|
||
];
|
||
|
||
// Kundenliste: pro Kunde die 'by_year'-Werte zu Totalen summieren,
|
||
// dann absteigend nach "incl" sortieren – größte Kunden zuerst.
|
||
$custList = [];
|
||
foreach ($customerAgg[$productPk] ?? [] as $cpk => $c) {
|
||
$sumD = 0.0; $sumI = 0.0; $sumED = 0.0; $sumEI = 0.0;
|
||
foreach (($c['by_year'] ?? []) as $y => $v) {
|
||
$sumD += (float)($v['direct'] ?? 0.0);
|
||
$sumI += (float)($v['incl'] ?? 0.0);
|
||
$sumED += (float)($v['ext_direct'] ?? 0.0);
|
||
$sumEI += (float)($v['ext_incl'] ?? 0.0);
|
||
}
|
||
if ($sumD == 0.0 && $sumI == 0.0 && $sumED == 0.0 && $sumEI == 0.0) continue;
|
||
$custList[] = [
|
||
'pk' => (int)($c['pk'] ?? 0),
|
||
'name' => (string)($c['name'] ?? ''),
|
||
'direct' => $sumD,
|
||
'incl' => $sumI,
|
||
'ext_direct' => $sumED,
|
||
'ext_incl' => $sumEI,
|
||
];
|
||
}
|
||
usort($custList, fn($a,$b) => ($b['incl'] <=> $a['incl']));
|
||
$row['customers'] = $custList;
|
||
|
||
$sumDirect = 0.0;
|
||
$sumIncl = 0.0;
|
||
$sumExtDirect = 0.0;
|
||
$sumExtIncl = 0.0;
|
||
|
||
foreach ($years as $y) {
|
||
$d = (float)($pivot[$productPk][$y] ?? 0.0);
|
||
$b = (float)($pivotB[$productPk][$y] ?? 0.0);
|
||
$incl = $d + $b;
|
||
|
||
$de = (float)($pivotExt[$productPk][$y] ?? 0.0);
|
||
$be = (float)($pivotExtB[$productPk][$y] ?? 0.0);
|
||
$inclE = $de + $be;
|
||
|
||
$row['years'][$y] = ['direct'=>$d, 'incl'=>$incl];
|
||
$row['years_ext'][$y] = ['direct'=>$de, 'incl'=>$inclE];
|
||
|
||
$sumDirect += $d;
|
||
$sumIncl += $incl;
|
||
$sumExtDirect += $de;
|
||
$sumExtIncl += $inclE;
|
||
}
|
||
|
||
$row['total_direct'] = $sumDirect;
|
||
$row['total_incl'] = $sumIncl;
|
||
$row['total_ext_direct'] = $sumExtDirect;
|
||
$row['total_ext_incl'] = $sumExtIncl;
|
||
|
||
$pd = computePeakAndHistogram($GLOBALS['eventsDirect'][$productPk] ?? []);
|
||
$pi = computePeakAndHistogram($GLOBALS['eventsIncl'][$productPk] ?? []);
|
||
$row['peak_direct'] = (int)$pd['peak'];
|
||
$row['peak_incl'] = (int)$pi['peak'];
|
||
$row['hist_direct'] = $pd['hist'];
|
||
$row['hist_incl'] = $pi['hist'];
|
||
|
||
$hasAny = (
|
||
$row['total_direct'] != 0.0 ||
|
||
$row['total_incl'] != 0.0 ||
|
||
$row['total_ext_direct'] != 0.0 ||
|
||
$row['total_ext_incl'] != 0.0 ||
|
||
$row['peak_direct'] != 0 ||
|
||
$row['peak_incl'] != 0
|
||
);
|
||
if (!$hasAny) continue;
|
||
|
||
$pivotRows[] = $row;
|
||
}
|
||
|
||
usort($pivotRows, fn($a,$b) => ($b['total_incl'] <=> $a['total_incl']));
|
||
|
||
/* =========================
|
||
4) Excel Export (nur Pivot)
|
||
========================= */
|
||
|
||
$exportDir = __DIR__ . '/exports';
|
||
if (!is_dir($exportDir)) {
|
||
@mkdir($exportDir, 0775, true);
|
||
}
|
||
$timestamp = date('Ymd_His');
|
||
$excelFileName = "ROI_Artikel_Jahre_{$timestamp}.xlsx";
|
||
$excelFilePath = $exportDir . '/' . $excelFileName;
|
||
$excelDownloadUrl = 'exports/' . $excelFileName;
|
||
|
||
$spreadsheet = new Spreadsheet();
|
||
$sheet = $spreadsheet->getActiveSheet();
|
||
$sheet->setTitle('Pivot');
|
||
|
||
$cols = [
|
||
'Produkt-PK','Artikel-Nr','Artikel',
|
||
'Peak','Peak (inkl. Bundle)',
|
||
'Summe','Summe (inkl. Bundle)',
|
||
'davon extern','davon extern (inkl. Bundle)'
|
||
];
|
||
|
||
foreach ($years as $y) {
|
||
$cols[] = (string)$y;
|
||
$cols[] = (string)$y . ' (inkl. Bundle)';
|
||
$cols[] = (string)$y . ' ext';
|
||
$cols[] = (string)$y . ' ext (inkl. Bundle)';
|
||
}
|
||
|
||
$colCount = count($cols);
|
||
$lastColLetter = Coordinate::stringFromColumnIndex($colCount);
|
||
|
||
// Header
|
||
$sheet->setCellValue('A1', 'EpiWebview – ROI / Umsatz pro Artikel & Jahr');
|
||
$sheet->mergeCells('A1:' . $lastColLetter . '1');
|
||
$sheet->getStyle('A1:' . $lastColLetter . '1')->getFont()->setBold(true)->setSize(14);
|
||
$sheet->getStyle('A1:' . $lastColLetter . '1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
|
||
|
||
$sheet->setCellValue('A2', 'Exportdatum: ' . date('d.m.Y H:i') . ' | Mandant: ' . (defined('Epirent_Mandant') ? Epirent_Mandant : ''));
|
||
$sheet->mergeCells('A2:' . $lastColLetter . '2');
|
||
$sheet->getStyle('A2:' . $lastColLetter . '2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
|
||
|
||
$headerRow = 4;
|
||
for ($i=0; $i<$colCount; $i++) {
|
||
$addr = Coordinate::stringFromColumnIndex($i+1) . $headerRow;
|
||
$sheet->setCellValue($addr, $cols[$i]);
|
||
$sheet->getStyle($addr)->getFont()->setBold(true);
|
||
$sheet->getStyle($addr)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
|
||
$sheet->getColumnDimension(Coordinate::stringFromColumnIndex($i+1))->setAutoSize(true);
|
||
}
|
||
|
||
$rowIdx = $headerRow + 1;
|
||
foreach ($pivotRows as $r) {
|
||
$c = 1;
|
||
$sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($c++).$rowIdx, (int)$r['product_pk'], DataType::TYPE_NUMERIC);
|
||
$sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($c++).$rowIdx, (int)$r['product_no'], DataType::TYPE_NUMERIC);
|
||
$sheet->setCellValue(Coordinate::stringFromColumnIndex($c++).$rowIdx, (string)$r['title']);
|
||
|
||
$sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($c++).$rowIdx, (int)$r['peak_direct'], DataType::TYPE_NUMERIC);
|
||
$sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($c++).$rowIdx, (int)$r['peak_incl'], DataType::TYPE_NUMERIC);
|
||
|
||
$sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($c).$rowIdx, (float)$r['total_direct'], DataType::TYPE_NUMERIC);
|
||
$sheet->getStyle(Coordinate::stringFromColumnIndex($c).$rowIdx)->getNumberFormat()->setFormatCode('#,##0.00 [$€-de-DE]');
|
||
$c++;
|
||
|
||
$sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($c).$rowIdx, (float)$r['total_incl'], DataType::TYPE_NUMERIC);
|
||
$sheet->getStyle(Coordinate::stringFromColumnIndex($c).$rowIdx)->getNumberFormat()->setFormatCode('#,##0.00 [$€-de-DE]');
|
||
$c++;
|
||
|
||
$sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($c).$rowIdx, (float)$r['total_ext_direct'], DataType::TYPE_NUMERIC);
|
||
$sheet->getStyle(Coordinate::stringFromColumnIndex($c).$rowIdx)->getNumberFormat()->setFormatCode('#,##0.00 [$€-de-DE]');
|
||
$c++;
|
||
|
||
$sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($c).$rowIdx, (float)$r['total_ext_incl'], DataType::TYPE_NUMERIC);
|
||
$sheet->getStyle(Coordinate::stringFromColumnIndex($c).$rowIdx)->getNumberFormat()->setFormatCode('#,##0.00 [$€-de-DE]');
|
||
$c++;
|
||
|
||
foreach ($years as $y) {
|
||
$d = (float)$r['years'][$y]['direct'];
|
||
$iVal = (float)$r['years'][$y]['incl'];
|
||
|
||
$de = (float)$r['years_ext'][$y]['direct'];
|
||
$ie = (float)$r['years_ext'][$y]['incl'];
|
||
|
||
$sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($c).$rowIdx, $d, DataType::TYPE_NUMERIC);
|
||
$sheet->getStyle(Coordinate::stringFromColumnIndex($c).$rowIdx)->getNumberFormat()->setFormatCode('#,##0.00 [$€-de-DE]');
|
||
$c++;
|
||
|
||
$sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($c).$rowIdx, $iVal, DataType::TYPE_NUMERIC);
|
||
$sheet->getStyle(Coordinate::stringFromColumnIndex($c).$rowIdx)->getNumberFormat()->setFormatCode('#,##0.00 [$€-de-DE]');
|
||
$c++;
|
||
|
||
$sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($c).$rowIdx, $de, DataType::TYPE_NUMERIC);
|
||
$sheet->getStyle(Coordinate::stringFromColumnIndex($c).$rowIdx)->getNumberFormat()->setFormatCode('#,##0.00 [$€-de-DE]');
|
||
$c++;
|
||
|
||
$sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($c).$rowIdx, $ie, DataType::TYPE_NUMERIC);
|
||
$sheet->getStyle(Coordinate::stringFromColumnIndex($c).$rowIdx)->getNumberFormat()->setFormatCode('#,##0.00 [$€-de-DE]');
|
||
$c++;
|
||
}
|
||
|
||
$rowIdx++;
|
||
}
|
||
|
||
$sheet->setAutoFilter("A{$headerRow}:{$lastColLetter}{$headerRow}");
|
||
$sheet->freezePane("A" . ($headerRow + 1));
|
||
|
||
$writer = new Xlsx($spreadsheet);
|
||
$writer->save($excelFilePath);
|
||
|
||
} // end if ($computeRoi)
|
||
|
||
/* =========================
|
||
5) HTML Ausgabe
|
||
========================= */
|
||
|
||
function slotsToDisplay(array $slotMap): array {
|
||
ksort($slotMap);
|
||
$out = [];
|
||
foreach ($slotMap as $slot => $rev) {
|
||
$slot = (int)$slot;
|
||
if ($slot <= 0) continue;
|
||
$v = (float)$rev;
|
||
if (abs($v) < 0.0000001) $v = 0.0;
|
||
$out[] = ['slot'=>$slot, 'rev'=>$v];
|
||
}
|
||
return $out;
|
||
}
|
||
|
||
?>
|
||
<!DOCTYPE html>
|
||
<html lang="de">
|
||
<head>
|
||
<meta charset="utf-8" />
|
||
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
|
||
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no" />
|
||
<title>ROI – Umsatz pro Artikel / Jahr</title>
|
||
|
||
<link href="css/styles.css" rel="stylesheet" />
|
||
<link href="https://cdn.datatables.net/1.10.20/css/dataTables.bootstrap4.min.css" rel="stylesheet" crossorigin="anonymous" />
|
||
<link href="https://cdn.datatables.net/fixedcolumns/3.3.3/css/fixedColumns.bootstrap4.min.css" rel="stylesheet" crossorigin="anonymous" />
|
||
|
||
<script src="js/jquery-3.5.1.min.js"></script>
|
||
<script src="https://kit.fontawesome.com/93d71de8bc.js" crossorigin="anonymous"></script>
|
||
|
||
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/js/bootstrap.bundle.min.js" crossorigin="anonymous"></script>
|
||
|
||
<script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js" crossorigin="anonymous"></script>
|
||
<script src="https://cdn.datatables.net/1.10.20/js/dataTables.bootstrap4.min.js" crossorigin="anonymous"></script>
|
||
<script src="https://cdn.datatables.net/fixedcolumns/3.3.3/js/dataTables.fixedColumns.min.js" crossorigin="anonymous"></script>
|
||
|
||
<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.1/dist/chart.umd.min.js" crossorigin="anonymous"></script>
|
||
|
||
<style>
|
||
.mono { font-family: ui-monospace, SFMono-Regular, Menlo, Monaco, Consolas, "Liberation Mono", "Courier New", monospace; }
|
||
.btn-xs { padding:.15rem .4rem; font-size:.78rem; }
|
||
.nowrap { white-space: nowrap; }
|
||
</style>
|
||
|
||
<script>
|
||
$(function () {
|
||
$('#layoutSidenav_nav').load('../sources/getSidenav.php');
|
||
$('#footerholder').load('../sources/getFooter.php');
|
||
});
|
||
</script>
|
||
</head>
|
||
|
||
<body class="sb-nav-fixed">
|
||
<nav class="sb-topnav navbar navbar-expand navbar-dark bg-dark">
|
||
<a class="navbar-brand" href="index.php">Epi Webview</a>
|
||
<button class="btn btn-link btn-sm order-1 order-lg-0" id="sidebarToggle"><i class="fas fa-bars"></i></button>
|
||
</nav>
|
||
|
||
<div id="layoutSidenav">
|
||
<div id="layoutSidenav_nav"></div>
|
||
<div id="layoutSidenav_content">
|
||
<main>
|
||
<div class="container-fluid">
|
||
<h1 class="mt-4">ROI</h1>
|
||
<ol class="breadcrumb mb-4">
|
||
<li class="breadcrumb-item active">Umsatz / Peak / Histogramm pro Artikel</li>
|
||
</ol>
|
||
|
||
<!-- Zeitraum-Filter -->
|
||
<div class="card mb-4">
|
||
<div class="card-header"><i class="fas fa-filter mr-1"></i> Zeitraum wählen</div>
|
||
<div class="card-body">
|
||
<form method="get" class="form-row align-items-end">
|
||
<div class="form-group col-md-3 mb-2">
|
||
<label for="from">Startjahr</label>
|
||
<input type="number" name="from" id="from" class="form-control" min="2000" max="2100"
|
||
value="<?php echo $filterFrom > 0 ? (int)$filterFrom : ((int)date('Y') - 2); ?>">
|
||
</div>
|
||
<div class="form-group col-md-3 mb-2">
|
||
<label for="to">Endjahr</label>
|
||
<input type="number" name="to" id="to" class="form-control" min="2000" max="2100"
|
||
value="<?php echo $filterTo > 0 ? (int)$filterTo : (int)date('Y'); ?>">
|
||
</div>
|
||
<div class="form-group col-md-3 mb-2">
|
||
<button type="submit" name="mode" value="range" class="btn btn-primary btn-block">
|
||
<i class="fas fa-play"></i> Zeitraum berechnen
|
||
</button>
|
||
</div>
|
||
<div class="form-group col-md-3 mb-2">
|
||
<button type="submit" name="mode" value="all" class="btn btn-secondary btn-block">
|
||
<i class="fas fa-infinity"></i> Alles berechnen
|
||
</button>
|
||
</div>
|
||
</form>
|
||
<small class="text-muted">
|
||
<?php if ($computeRoi && $filterMode === 'range'): ?>
|
||
<br><b>Aktuell:</b> <?php echo (int)$filterFrom; ?> – <?php echo (int)$filterTo; ?>
|
||
<?php elseif ($computeRoi && $filterMode === 'all'): ?>
|
||
<br><b>Aktuell:</b> alle Jahre
|
||
<?php endif; ?>
|
||
</small>
|
||
|
||
<?php if ($cacheEnabled): ?>
|
||
<?php
|
||
$cacheFromLabel = $cacheFrom === null ? '−∞' : (string)(int)$cacheFrom;
|
||
$cacheToLabel = $cacheTo === null ? '+∞' : (string)(int)$cacheTo;
|
||
$cacheTsLabel = ($cacheDataMeta && !empty($cacheDataMeta['ts']))
|
||
? date('d.m.Y H:i', (int)$cacheDataMeta['ts'])
|
||
: ($rebuildCache || !empty($missingCacheRanges) ? 'gerade aktualisiert' : 'noch keiner');
|
||
$refreshUrl = '?refresh_cache=1'
|
||
. ($filterMode !== '' ? '&mode=' . urlencode($filterMode) : '')
|
||
. ($filterFrom > 0 ? '&from=' . (int)$filterFrom : '')
|
||
. ($filterTo > 0 ? '&to=' . (int)$filterTo : '');
|
||
$coveredHuman = formatCachedRangesHuman($cachedRanges);
|
||
$incrementalAdded = !$rebuildCache && !empty($missingCacheRanges);
|
||
$usedCache = ($loadedFromCache || $rebuildCache);
|
||
$usedLive = !empty($doLiveLoop) || !empty($doLiveLoopBelow);
|
||
?>
|
||
<hr class="my-3" />
|
||
<div class="d-flex justify-content-between align-items-center flex-wrap">
|
||
<div>
|
||
<b><i class="fas fa-database mr-1"></i> Cache-Modus aktiv</b>
|
||
| Konfiguriert: <span class="mono"><?php echo $cacheFromLabel; ?> – <?php echo $cacheToLabel; ?></span>
|
||
| Abgedeckt: <span class="mono"><?php echo htmlspecialchars($coveredHuman); ?></span>
|
||
| Stand: <span class="mono"><?php echo htmlspecialchars($cacheTsLabel); ?></span>
|
||
<?php if ($rebuildCache): ?>
|
||
<span class="badge badge-warning">komplett neu gebaut</span>
|
||
<?php elseif ($incrementalAdded): ?>
|
||
<span class="badge badge-warning">Cache erweitert</span>
|
||
<?php elseif ($usedCache && $usedLive): ?>
|
||
<span class="badge badge-primary">Cache + Live</span>
|
||
<?php elseif ($loadedFromCache): ?>
|
||
<span class="badge badge-success">aus Cache</span>
|
||
<?php elseif ($usedLive): ?>
|
||
<span class="badge badge-info">nur Live (außerhalb Cache-Zeitraum)</span>
|
||
<?php endif; ?>
|
||
</div>
|
||
<a class="btn btn-sm btn-outline-warning"
|
||
href="<?php echo htmlspecialchars($refreshUrl); ?>"
|
||
onclick="return confirm('Cache komplett neu bauen? Bestehende Daten werden verworfen und der volle Config-Zeitraum wird neu gefetcht.');">
|
||
<i class="fas fa-sync-alt"></i> Cache neu laden
|
||
</a>
|
||
</div>
|
||
<?php if ($cacheFilterApplied): ?>
|
||
<small class="text-muted d-block mt-2">
|
||
<i class="fas fa-info-circle"></i>
|
||
Zeitraum <span class="mono"><?php echo (int)$cacheFilterApplied['from']; ?> – <?php echo (int)$cacheFilterApplied['to']; ?></span>
|
||
ist auf alle Kennzahlen angewendet – auch Peak, Histogramm, Slots und Kunden.
|
||
</small>
|
||
<?php endif; ?>
|
||
<?php endif; ?>
|
||
</div>
|
||
</div>
|
||
|
||
<?php if (!$computeRoi): ?>
|
||
<div class="alert alert-info">
|
||
Bitte oben einen Zeitraum auswählen und auf <b>Zeitraum berechnen</b> klicken,
|
||
oder <b>Alles berechnen</b> für die vollständige Auswertung.
|
||
</div>
|
||
<?php else: ?>
|
||
|
||
<div class="card mb-4">
|
||
<div class="card-header d-flex justify-content-between align-items-center">
|
||
<div><i class="fas fa-table mr-1"></i> Pivot: Artikel × Rechnungsjahr</div>
|
||
<a class="btn btn-sm btn-success" href="<?php echo htmlspecialchars($excelDownloadUrl); ?>">
|
||
<i class="fas fa-file-excel"></i> Export nach Excel
|
||
</a>
|
||
</div>
|
||
<div class="card-body">
|
||
<table class="table table-bordered" id="pivotTable" width="100%" cellspacing="0">
|
||
<thead>
|
||
<tr>
|
||
<th class="nowrap">Produkt-PK</th>
|
||
<th class="nowrap">Artikel-Nr</th>
|
||
<th>Artikel</th>
|
||
<th class="nowrap">Peak</th>
|
||
<th class="nowrap">Summe</th>
|
||
<th class="nowrap">davon extern</th>
|
||
<?php foreach ($years as $y): ?>
|
||
<th class="nowrap"><?php echo (int)$y; ?></th>
|
||
<?php endforeach; ?>
|
||
<th class="nowrap">Histogramm</th>
|
||
<th class="nowrap">Slots</th>
|
||
<th class="nowrap">Kunden</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<?php foreach ($pivotRows as $r): ?>
|
||
<?php
|
||
$peakDirect = (int)$r['peak_direct'];
|
||
$peakIncl = (int)$r['peak_incl'];
|
||
|
||
$histDirectB64 = base64Json($r['hist_direct']);
|
||
$histInclB64 = base64Json($r['hist_incl']);
|
||
|
||
$sumDirect = (float)$r['total_direct'];
|
||
$sumIncl = (float)$r['total_incl'];
|
||
|
||
$extDirect = (float)$r['total_ext_direct'];
|
||
$extIncl = (float)$r['total_ext_incl'];
|
||
|
||
$slotsDirect = slotsToDisplay($r['slots_direct'] ?? []);
|
||
$slotsIncl = slotsToDisplay($r['slots_incl'] ?? []);
|
||
$slotsDirectExt = slotsToDisplay($r['slots_direct_ext'] ?? []);
|
||
$slotsInclExt = slotsToDisplay($r['slots_incl_ext'] ?? []);
|
||
|
||
$slotsPayload = [
|
||
'direct' => $slotsDirect,
|
||
'incl' => $slotsIncl,
|
||
'direct_ext' => $slotsDirectExt,
|
||
'incl_ext' => $slotsInclExt,
|
||
];
|
||
$slotsB64 = base64Json($slotsPayload);
|
||
|
||
// Kunden-Payload für Modal (bereits nach 'incl' desc sortiert)
|
||
$customersB64 = base64Json($r['customers'] ?? []);
|
||
$customersCount = is_array($r['customers'] ?? null) ? count($r['customers']) : 0;
|
||
?>
|
||
<tr>
|
||
<td class="mono"><?php echo (int)$r['product_pk']; ?></td>
|
||
<td class="mono"><?php echo (int)$r['product_no']; ?></td>
|
||
<td>
|
||
<?php echo htmlspecialchars((string)$r['title']); ?>
|
||
<?php if (!empty($r['older_titles'])):
|
||
$ttHtml = 'Frühere Namen:<br>• ' .
|
||
implode('<br>• ', array_map(
|
||
fn($t) => htmlspecialchars((string)$t, ENT_QUOTES),
|
||
$r['older_titles']
|
||
));
|
||
?>
|
||
<i class="fas fa-info-circle text-muted ml-1 js-name-history"
|
||
style="cursor: help;"
|
||
data-toggle="tooltip"
|
||
data-html="true"
|
||
title="<?php echo htmlspecialchars($ttHtml, ENT_QUOTES); ?>"></i>
|
||
<?php endif; ?>
|
||
</td>
|
||
|
||
<td class="nowrap">
|
||
<?php echo $peakDirect; ?>
|
||
<span class="text-muted">(<?php echo $peakIncl; ?>)</span>
|
||
</td>
|
||
|
||
<td class="nowrap">
|
||
<?php echo formatEuro($sumDirect); ?>
|
||
<span class="text-muted">(<?php echo formatEuro($sumIncl); ?>)</span>
|
||
</td>
|
||
|
||
<td class="nowrap">
|
||
<?php echo formatEuro($extDirect); ?>
|
||
<span class="text-muted">(<?php echo formatEuro($extIncl); ?>)</span>
|
||
</td>
|
||
|
||
<?php foreach ($years as $y): ?>
|
||
<?php
|
||
$d = (float)$r['years'][$y]['direct'];
|
||
$iVal = (float)$r['years'][$y]['incl'];
|
||
?>
|
||
<td class="nowrap">
|
||
<?php echo formatEuro($d); ?>
|
||
<span class="text-muted">(<?php echo formatEuro($iVal); ?>)</span>
|
||
</td>
|
||
<?php endforeach; ?>
|
||
|
||
<td class="nowrap">
|
||
<button
|
||
class="btn btn-outline-primary btn-xs js-hist"
|
||
data-title="<?php echo htmlspecialchars((string)$r['title']); ?>"
|
||
data-hist="<?php echo htmlspecialchars($histDirectB64); ?>"
|
||
data-peak="<?php echo (int)$peakDirect; ?>"
|
||
data-mode="direct">
|
||
Histogramm
|
||
</button>
|
||
|
||
<button
|
||
class="btn btn-outline-secondary btn-xs js-hist"
|
||
data-title="<?php echo htmlspecialchars((string)$r['title']); ?>"
|
||
data-hist="<?php echo htmlspecialchars($histInclB64); ?>"
|
||
data-peak="<?php echo (int)$peakIncl; ?>"
|
||
data-mode="incl">
|
||
inkl. Bundle
|
||
</button>
|
||
</td>
|
||
|
||
<td class="nowrap">
|
||
<button
|
||
class="btn btn-outline-dark btn-xs js-slots"
|
||
data-title="<?php echo htmlspecialchars((string)$r['title']); ?>"
|
||
data-slots="<?php echo htmlspecialchars($slotsB64); ?>">
|
||
Slots
|
||
</button>
|
||
</td>
|
||
|
||
<td class="nowrap">
|
||
<button
|
||
class="btn btn-outline-info btn-xs js-customers"
|
||
data-title="<?php echo htmlspecialchars((string)$r['title']); ?>"
|
||
data-customers="<?php echo htmlspecialchars($customersB64); ?>"
|
||
<?php if ($customersCount === 0) echo 'disabled'; ?>>
|
||
Kunden <?php if ($customersCount > 0) echo '(' . (int)$customersCount . ')'; ?>
|
||
</button>
|
||
</td>
|
||
</tr>
|
||
<?php endforeach; ?>
|
||
</tbody>
|
||
</table>
|
||
|
||
<small class="text-muted">
|
||
Jahr-Zuordnung Umsatz: <span class="mono">invoice_date</span>.
|
||
Peak/Histogramm: Zeitraum <span class="mono">date_start..date_end</span> (tagesbasiert).
|
||
Werte in Klammern = inkl. Bundle-Anteil (nur <b>virtuelle</b> Bundles werden zerlegt).
|
||
Extern-Umsatz: <span class="mono">sum_total_net * (amount_external/amount_total)</span>.
|
||
Slots: auftragsbasiert, SIGNED (Storno gibt Slots frei und bucht auf die gleichen Zeiträume zurück).
|
||
Storno-Erkennung: Rechnung mit <span class="mono">sum_net < 0</span>.
|
||
</small>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="card mb-4">
|
||
<div class="card-header">
|
||
<i class="fas fa-list mr-1"></i> Debug: verarbeitete Mietpositionen
|
||
</div>
|
||
<div class="card-body">
|
||
<table class="table table-bordered" id="detailTable" width="100%" cellspacing="0">
|
||
<thead>
|
||
<tr>
|
||
<th>Jahr</th>
|
||
<th>Invoice</th>
|
||
<th>Rechnungsnr</th>
|
||
<th>Rechnungsdatum</th>
|
||
<th>Quelle</th>
|
||
<th>Chapter</th>
|
||
<th>Produkt-PK</th>
|
||
<th>Artikel-Nr</th>
|
||
<th>Artikel</th>
|
||
<th>Start</th>
|
||
<th>Ende</th>
|
||
<th>Menge</th>
|
||
<th>Menge (signed)</th>
|
||
<th>Ext-Menge</th>
|
||
<th>Umsatz netto</th>
|
||
<th>Umsatz extern</th>
|
||
<th>Credit</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<?php foreach ($rows as $r): ?>
|
||
<tr>
|
||
<td><?php echo (int)$r['year']; ?></td>
|
||
<td class="mono"><?php echo (int)$r['invoice_pk']; ?></td>
|
||
<td class="mono"><?php echo (int)$r['invoice_no']; ?></td>
|
||
<td class="mono"><?php echo htmlspecialchars((string)$r['invoice_date']); ?></td>
|
||
<td><?php echo htmlspecialchars((string)$r['source']); ?></td>
|
||
<td class="mono"><?php echo (int)$r['chapter_id']; ?></td>
|
||
<td class="mono"><?php echo (int)$r['product_pk']; ?></td>
|
||
<td class="mono"><?php echo (int)$r['product_no']; ?></td>
|
||
<td><?php echo htmlspecialchars((string)$r['title']); ?></td>
|
||
<td class="mono"><?php echo htmlspecialchars((string)$r['date_start']); ?></td>
|
||
<td class="mono"><?php echo htmlspecialchars((string)$r['date_end']); ?></td>
|
||
<td class="mono"><?php echo htmlspecialchars((string)$r['qty']); ?></td>
|
||
<td class="mono"><?php echo htmlspecialchars((string)($r['qty_signed'] ?? $r['qty'])); ?></td>
|
||
<td class="mono"><?php echo htmlspecialchars((string)($r['amount_ext'] ?? '0')); ?></td>
|
||
<td class="nowrap"><?php echo formatEuro((float)$r['revenue_net']); ?></td>
|
||
<td class="nowrap"><?php echo formatEuro((float)$r['ext_rev_net']); ?></td>
|
||
<td class="mono"><?php echo (int)($r['is_credit'] ?? 0); ?></td>
|
||
</tr>
|
||
<?php endforeach; ?>
|
||
</tbody>
|
||
</table>
|
||
<small class="text-muted">
|
||
Ext-Menge ist Anzahl; Umsatz extern ist anteilig berechnet.
|
||
Menge(signed) ist für Peak/Slots relevant (Storno-Rechnung -> negativ).
|
||
</small>
|
||
</div>
|
||
</div>
|
||
|
||
<?php endif; // $computeRoi ?>
|
||
|
||
</div>
|
||
</main>
|
||
<div id="footerholder"></div>
|
||
</div>
|
||
</div>
|
||
|
||
<!-- Histogram Modal -->
|
||
<div class="modal fade" id="histModal" tabindex="-1" role="dialog" aria-hidden="true">
|
||
<div class="modal-dialog modal-lg" role="document">
|
||
<div class="modal-content">
|
||
<div class="modal-header">
|
||
<h5 class="modal-title">Histogramm</h5>
|
||
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
|
||
<span aria-hidden="true">×</span>
|
||
</button>
|
||
</div>
|
||
<div class="modal-body">
|
||
<canvas id="histChart" height="140"></canvas>
|
||
<div class="mt-2 text-muted">
|
||
X-Achse: gleichzeitig vermietete Stückzahl (1..Peak) | Y-Achse: Tage
|
||
</div>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
|
||
<!-- Slots Modal -->
|
||
<div class="modal fade" id="slotsModal" tabindex="-1" role="dialog" aria-hidden="true">
|
||
<div class="modal-dialog modal-lg" role="document">
|
||
<div class="modal-content">
|
||
<div class="modal-header">
|
||
<h5 class="modal-title">Slots</h5>
|
||
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
|
||
<span aria-hidden="true">×</span>
|
||
</button>
|
||
</div>
|
||
<div class="modal-body">
|
||
<div class="mb-2 text-muted">
|
||
Slot-Umsatz ist <b>auftragsbasiert</b>: Umsatz einer Position wird gleichmäßig auf die belegten Slots verteilt.
|
||
Werte in Klammern = inkl. Bundle-Anteil (nur virtuelle Bundles).
|
||
Storno bucht Umsatz auf die gleichen Slots zurück und gibt die Slots frei (SIGNED).
|
||
</div>
|
||
|
||
<div class="table-responsive">
|
||
<table class="table table-sm table-bordered" id="slotsTable">
|
||
<thead>
|
||
<tr>
|
||
<th class="nowrap">Slot</th>
|
||
<th class="nowrap">Umsatz</th>
|
||
<th class="nowrap">Umsatz inkl. Bundle</th>
|
||
<th class="nowrap">davon extern</th>
|
||
<th class="nowrap">davon extern inkl. Bundle</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody></tbody>
|
||
</table>
|
||
</div>
|
||
|
||
<div class="mt-2">
|
||
<canvas id="slotsChart" height="130"></canvas>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
|
||
<!-- Customers Modal -->
|
||
<div class="modal fade" id="customersModal" tabindex="-1" role="dialog" aria-hidden="true">
|
||
<div class="modal-dialog modal-xl" role="document">
|
||
<div class="modal-content">
|
||
<div class="modal-header">
|
||
<h5 class="modal-title">Kunden-Umsatz</h5>
|
||
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
|
||
<span aria-hidden="true">×</span>
|
||
</button>
|
||
</div>
|
||
<div class="modal-body">
|
||
<div class="mb-2 text-muted">
|
||
Umsatz pro Kunde für diesen Artikel, gefiltert auf den oben gewählten Zeitraum.
|
||
<b>Umsatz</b> = direkt auf diesem Artikel abgerechnet.
|
||
<b>inkl. Bundle</b> = zusätzlich der Anteil, wenn der Artikel Bestandteil eines virtuellen Bundles war.
|
||
</div>
|
||
<div class="table-responsive">
|
||
<table class="table table-sm table-bordered" id="customersTable">
|
||
<thead>
|
||
<tr>
|
||
<th class="nowrap">#</th>
|
||
<th class="nowrap">Kunden-PK</th>
|
||
<th>Kunde</th>
|
||
<th class="nowrap text-right">Umsatz</th>
|
||
<th class="nowrap text-right">Umsatz inkl. Bundle</th>
|
||
<th class="nowrap text-right">davon extern</th>
|
||
<th class="nowrap text-right">davon extern inkl. Bundle</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody></tbody>
|
||
<tfoot>
|
||
<tr class="font-weight-bold" style="background:#f8f9fa;">
|
||
<td colspan="3" class="text-right">Summe:</td>
|
||
<td class="nowrap text-right" id="cust_sum_direct"></td>
|
||
<td class="nowrap text-right" id="cust_sum_incl"></td>
|
||
<td class="nowrap text-right" id="cust_sum_ext_direct"></td>
|
||
<td class="nowrap text-right" id="cust_sum_ext_incl"></td>
|
||
</tr>
|
||
</tfoot>
|
||
</table>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
|
||
<script>
|
||
let histChart = null;
|
||
let slotsChart = null;
|
||
|
||
function decodeB64Json(b64) {
|
||
try {
|
||
// atob() liefert Bytes als Latin-1-String zurück. Wir müssen die Rohbytes
|
||
// extrahieren und explizit als UTF-8 dekodieren, sonst wird "ü" zu "ü".
|
||
const binary = atob(b64);
|
||
const bytes = new Uint8Array(binary.length);
|
||
for (let i = 0; i < binary.length; i++) bytes[i] = binary.charCodeAt(i);
|
||
const json = new TextDecoder('utf-8').decode(bytes);
|
||
return JSON.parse(json);
|
||
} catch (e) {
|
||
return {};
|
||
}
|
||
}
|
||
|
||
function openHistogram(title, mode, peak, histObj) {
|
||
const labels = [];
|
||
const data = [];
|
||
const p = parseInt(peak || 0, 10);
|
||
|
||
for (let i = 1; i <= p; i++) {
|
||
labels.push(String(i));
|
||
data.push(histObj[String(i)] ? parseInt(histObj[String(i)], 10) : 0);
|
||
}
|
||
|
||
$('#histModal .modal-title').text(`${title} – ${mode === 'incl' ? 'inkl. Bundle' : 'ohne Bundle'}`);
|
||
|
||
const ctx = document.getElementById('histChart');
|
||
if (histChart) { histChart.destroy(); histChart = null; }
|
||
|
||
histChart = new Chart(ctx, {
|
||
type: 'bar',
|
||
data: { labels, datasets: [{ label: 'Tage', data }] },
|
||
options: {
|
||
responsive: true,
|
||
animation: false,
|
||
scales: { y: { beginAtZero: true, ticks: { precision: 0 } } }
|
||
}
|
||
});
|
||
|
||
$('#histModal').modal('show');
|
||
}
|
||
|
||
function fmtEuro(n) {
|
||
const sign = n < 0 ? '-' : '';
|
||
n = Math.abs(n);
|
||
return sign + n.toLocaleString('de-DE', { minimumFractionDigits: 2, maximumFractionDigits: 2 }) + ' €';
|
||
}
|
||
|
||
function openSlots(title, payload) {
|
||
$('#slotsModal .modal-title').text(`${title} – Slot-Umsatz (auftragsbasiert)`);
|
||
|
||
const direct = payload.direct || [];
|
||
const incl = payload.incl || [];
|
||
const directExt = payload.direct_ext || [];
|
||
const inclExt = payload.incl_ext || [];
|
||
|
||
const mapD = {};
|
||
const mapI = {};
|
||
const mapDE = {};
|
||
const mapIE = {};
|
||
|
||
direct.forEach(x => mapD[String(x.slot)] = Number(x.rev || 0));
|
||
incl.forEach(x => mapI[String(x.slot)] = Number(x.rev || 0));
|
||
directExt.forEach(x => mapDE[String(x.slot)] = Number(x.rev || 0));
|
||
inclExt.forEach(x => mapIE[String(x.slot)] = Number(x.rev || 0));
|
||
|
||
const slots = new Set([
|
||
...Object.keys(mapD), ...Object.keys(mapI),
|
||
...Object.keys(mapDE), ...Object.keys(mapIE)
|
||
]);
|
||
|
||
const slotNums = Array.from(slots).map(s => parseInt(s,10)).filter(n => n>0).sort((a,b)=>a-b);
|
||
|
||
const $tb = $('#slotsTable tbody');
|
||
$tb.empty();
|
||
|
||
const labels = [];
|
||
const dataD = [];
|
||
const dataI = [];
|
||
|
||
slotNums.forEach(s => {
|
||
const sd = mapD[String(s)] || 0;
|
||
const si = mapI[String(s)] || 0;
|
||
const sde = mapDE[String(s)] || 0;
|
||
const sie = mapIE[String(s)] || 0;
|
||
|
||
$tb.append(`
|
||
<tr>
|
||
<td class="mono">${s}</td>
|
||
<td class="nowrap">${fmtEuro(sd)}</td>
|
||
<td class="nowrap text-muted">(${fmtEuro(si)})</td>
|
||
<td class="nowrap">${fmtEuro(sde)}</td>
|
||
<td class="nowrap text-muted">(${fmtEuro(sie)})</td>
|
||
</tr>
|
||
`);
|
||
|
||
labels.push(String(s));
|
||
dataD.push(sd);
|
||
dataI.push(si);
|
||
});
|
||
|
||
const ctx = document.getElementById('slotsChart');
|
||
if (slotsChart) { slotsChart.destroy(); slotsChart = null; }
|
||
|
||
slotsChart = new Chart(ctx, {
|
||
type: 'bar',
|
||
data: {
|
||
labels,
|
||
datasets: [
|
||
{ label: 'Umsatz', data: dataD },
|
||
{ label: 'Umsatz inkl. Bundle', data: dataI }
|
||
]
|
||
},
|
||
options: {
|
||
responsive: true,
|
||
animation: false,
|
||
scales: { y: { beginAtZero: true } }
|
||
}
|
||
});
|
||
|
||
$('#slotsModal').modal('show');
|
||
}
|
||
|
||
function attachTooltips(context) {
|
||
$(context || document).find('[data-toggle="tooltip"]').each(function() {
|
||
if (!$(this).data('bs.tooltip')) {
|
||
$(this).tooltip({ container: 'body', html: true, boundary: 'window' });
|
||
}
|
||
});
|
||
}
|
||
|
||
$(function() {
|
||
// scrollX: DataTables kümmert sich selbst um horizontales Scrollen der Tabelle,
|
||
// damit Search/Length/Pagination NICHT mit dem Scrollbalken mitwandern.
|
||
// fixedColumns: linke Spalten bis inkl. "Summe" bleiben beim horizontalen Scrollen stehen.
|
||
const pivotDT = $('#pivotTable').DataTable({
|
||
pageLength: 50,
|
||
order: [[4,'desc']],
|
||
scrollX: true,
|
||
fixedColumns: { leftColumns: 5 }
|
||
});
|
||
const detailDT = $('#detailTable').DataTable({ pageLength: 50, order: [[1,'desc']], scrollX: true });
|
||
|
||
// Tooltips (z.B. für Namens-Historie) auch nach Pagination/Sortierung erneut anhängen.
|
||
// Ohne Kontext-Selector werden auch die FixedColumns-Overlay-Tabellen erfasst.
|
||
pivotDT.on('draw', () => attachTooltips());
|
||
attachTooltips();
|
||
|
||
$(document).on('click', '.js-hist', function() {
|
||
const title = $(this).data('title') || '';
|
||
const mode = $(this).data('mode') || 'direct';
|
||
const peak = $(this).data('peak') || 0;
|
||
const b64 = $(this).data('hist') || '';
|
||
const histObj = decodeB64Json(b64);
|
||
openHistogram(title, mode, peak, histObj);
|
||
});
|
||
|
||
$(document).on('click', '.js-slots', function() {
|
||
const title = $(this).data('title') || '';
|
||
const b64 = $(this).data('slots') || '';
|
||
const payload = decodeB64Json(b64);
|
||
openSlots(title, payload);
|
||
});
|
||
|
||
$(document).on('click', '.js-customers', function() {
|
||
const title = $(this).data('title') || '';
|
||
const b64 = $(this).data('customers') || '';
|
||
const payload = decodeB64Json(b64);
|
||
openCustomers(title, payload);
|
||
});
|
||
});
|
||
|
||
function openCustomers(title, rows) {
|
||
$('#customersModal .modal-title').text(title + ' – Umsatz pro Kunde');
|
||
|
||
const list = Array.isArray(rows) ? rows.slice() : [];
|
||
list.sort((a, b) => Number(b.incl || 0) - Number(a.incl || 0));
|
||
|
||
let sumD = 0, sumI = 0, sumED = 0, sumEI = 0;
|
||
const $tb = $('#customersTable tbody').empty();
|
||
|
||
if (list.length === 0) {
|
||
$tb.append('<tr><td colspan="7" class="text-muted text-center">Keine Kunden-Daten vorhanden.</td></tr>');
|
||
} else {
|
||
list.forEach((c, idx) => {
|
||
const d = Number(c.direct || 0);
|
||
const i = Number(c.incl || 0);
|
||
const ed = Number(c.ext_direct || 0);
|
||
const ei = Number(c.ext_incl || 0);
|
||
sumD += d; sumI += i; sumED += ed; sumEI += ei;
|
||
|
||
const pk = c.pk ? String(c.pk) : '';
|
||
const name = c.name ? String(c.name) : (pk ? ('Kunde #' + pk) : 'Unbekannt');
|
||
|
||
$tb.append(`
|
||
<tr>
|
||
<td class="mono">${idx + 1}</td>
|
||
<td class="mono">${pk}</td>
|
||
<td>${$('<div>').text(name).html()}</td>
|
||
<td class="nowrap text-right">${fmtEuro(d)}</td>
|
||
<td class="nowrap text-right">${fmtEuro(i)}</td>
|
||
<td class="nowrap text-right">${fmtEuro(ed)}</td>
|
||
<td class="nowrap text-right">${fmtEuro(ei)}</td>
|
||
</tr>
|
||
`);
|
||
});
|
||
}
|
||
|
||
$('#cust_sum_direct').text(fmtEuro(sumD));
|
||
$('#cust_sum_incl').text(fmtEuro(sumI));
|
||
$('#cust_sum_ext_direct').text(fmtEuro(sumED));
|
||
$('#cust_sum_ext_incl').text(fmtEuro(sumEI));
|
||
|
||
$('#customersModal').modal('show');
|
||
}
|
||
</script>
|
||
|
||
</body>
|
||
</html>
|