379 lines
15 KiB
PHP
379 lines
15 KiB
PHP
<?php
|
||
// insurance.php – Versicherungsgeräte (liegt in /dist)
|
||
require('../config.php');
|
||
require('../EpiApi.php');
|
||
require_once __DIR__ . '/../vendor/autoload.php'; // PhpSpreadsheet Autoload
|
||
date_default_timezone_set('Europe/Berlin');
|
||
|
||
$Epi = new Epirent();
|
||
|
||
function formatEuro(float $amount): string {
|
||
$formatted = number_format(abs($amount), 2, ',', '.') . ' €';
|
||
return $amount < 0 ? '-' . $formatted : $formatted;
|
||
}
|
||
|
||
/* =========================
|
||
Excel-Export: Konfiguration
|
||
========================= */
|
||
$EXCEL_CUSTOM_HEADER = [
|
||
['EpiWebview – Versicherungsgeräte'],
|
||
['Exportdatum: ' . date('d.m.Y H:i') . ' | Mandant: ' . (defined('Epirent_Mandant') ? Epirent_Mandant : '')]
|
||
];
|
||
$EXCEL_COLS = ['#','Name','Warengruppe(n)','Versicherung netto','Seriennummer','Anschaffung','Kaufpreis','Besitzer'];
|
||
|
||
/* =========================
|
||
1) Daten einmalig holen & Zeilen bauen
|
||
========================= */
|
||
$result = $Epi->requestEpiApi('/v1/product/all?ia=true&ir=true&cl=' . Epirent_Mandant);
|
||
$productList = json_decode($result)->payload;
|
||
|
||
$filteredProductArray = [];
|
||
foreach ($productList as $product) {
|
||
if ($product->is_stock_control && $product->is_stock_single_entry && !$product->is_virtual) {
|
||
if (!empty($product->stock_data[0]->stock_rent) && $product->stock_data[0]->stock_rent > 0) {
|
||
$filteredProductArray[] = $product;
|
||
}
|
||
}
|
||
}
|
||
|
||
$rows = [];
|
||
foreach ($filteredProductArray as $filteredProduct) {
|
||
$detailRes = $Epi->requestEpiApi('/v1/product/' . $filteredProduct->primary_key . '?cl=' . Epirent_Mandant);
|
||
$insuredProduct = json_decode($detailRes)->payload[0] ?? null;
|
||
|
||
if (!$insuredProduct || empty($insuredProduct->pricing) || !isset($insuredProduct->pricing->insurance_net)) {
|
||
continue;
|
||
}
|
||
if ((float)$insuredProduct->pricing->insurance_net == 0.0) {
|
||
continue;
|
||
}
|
||
|
||
// Materials
|
||
$MaterialList = "";
|
||
if (!empty($insuredProduct->materials)) {
|
||
foreach ($insuredProduct->materials as $material) {
|
||
if ($MaterialList !== "") $MaterialList .= ", ";
|
||
if (!empty($material->is_free_material) && $material->is_free_material) {
|
||
$MaterialList .= $material->name;
|
||
} else {
|
||
$materialResult = $Epi->requestEpiApi('/v1/product/' . $material->mat_product_pk . '?cl=' . Epirent_Mandant);
|
||
$materialProductObject = json_decode($materialResult)->payload[0] ?? null;
|
||
$MaterialList .= $materialProductObject ? $materialProductObject->name : '';
|
||
}
|
||
}
|
||
}
|
||
|
||
// Warengruppen
|
||
$groupOfGoodsList = "";
|
||
if (!empty($insuredProduct->group_of_goods)) {
|
||
foreach ($insuredProduct->group_of_goods as $groupOfGoods) {
|
||
if ($groupOfGoodsList !== "") $groupOfGoodsList .= ", ";
|
||
$groupOfGoodsList .= str_replace(";", "->", $groupOfGoods->sequence_name);
|
||
}
|
||
}
|
||
|
||
// Geräte (Stocks) nur einmal je Produkt
|
||
$stockRes = $Epi->requestEpiApi('/v1/stock/filter?ppk=' . $insuredProduct->primary_key . '&cl=' . Epirent_Mandant);
|
||
$deviceStockObjectArray = json_decode($stockRes)->payload ?? [];
|
||
|
||
//Sortiere alle Geräte aus die "Nicht Versichert" im Status stehen haben
|
||
foreach ($deviceStockObjectArray as $device) {
|
||
if($device->condition=="Nicht Versichert"){
|
||
continue;
|
||
}
|
||
//Sortiere jetzt die Geräte aus, die Nicht aussortiert wurden
|
||
if(!$device->is_sorted_out){
|
||
$rows[] = [
|
||
'pk' => $insuredProduct->product_no,
|
||
'name' => $insuredProduct->name,
|
||
'materials' => $MaterialList,
|
||
'groups' => $groupOfGoodsList,
|
||
'insurance_net' => (float)$insuredProduct->pricing->insurance_net,
|
||
'serial_no' => (string)($device->serial_no ?? ''),
|
||
'date_purchase' => (string)($device->date_purchase ?? ''),
|
||
'purchase_price'=> (float)($device->purchase_price ?? 0.0),
|
||
'owner' => (string)($device->owner ?? ''),
|
||
];
|
||
}
|
||
}
|
||
}
|
||
|
||
/* =========================
|
||
2) XLSX sofort erzeugen (ohne setCellValueByColumnAndRow)
|
||
========================= */
|
||
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
||
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
||
use PhpOffice\PhpSpreadsheet\Style\Alignment;
|
||
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
|
||
use PhpOffice\PhpSpreadsheet\Cell\DataType;
|
||
use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;
|
||
use PhpOffice\PhpSpreadsheet\Style\Fill;
|
||
|
||
$exportDir = __DIR__ . '/exports';
|
||
if (!is_dir($exportDir)) {
|
||
@mkdir($exportDir, 0775, true);
|
||
}
|
||
|
||
$timestamp = date('Ymd_His');
|
||
$excelFileName = "Versicherungsgeraete_{$timestamp}.xlsx";
|
||
$excelFilePath = $exportDir . '/' . $excelFileName;
|
||
$excelDownloadUrl = 'exports/' . $excelFileName;
|
||
|
||
$spreadsheet = new Spreadsheet();
|
||
$sheet = $spreadsheet->getActiveSheet();
|
||
$sheet->setTitle('Versicherungsgeräte');
|
||
|
||
// kleine Helfer zum Adressieren per Spaltenbuchstaben
|
||
$cell = function(int $colIndex, int $rowIndex): string {
|
||
return Coordinate::stringFromColumnIndex($colIndex) . $rowIndex;
|
||
};
|
||
|
||
$colCount = count($EXCEL_COLS);
|
||
$lastCol = Coordinate::stringFromColumnIndex($colCount);
|
||
|
||
// Spaltenindex-Konstanten für Summen
|
||
$INSURANCE_COL = 4; // "Versicherung netto"
|
||
$PURCHASE_COL = 7; // "Kaufpreis"
|
||
|
||
// Custom Header
|
||
$rowIdx = 1;
|
||
foreach ($EXCEL_CUSTOM_HEADER as $hdrLine) {
|
||
$text = implode(' ', $hdrLine);
|
||
$sheet->setCellValue($cell(1, $rowIdx), $text);
|
||
$sheet->mergeCells($cell(1, $rowIdx) . ':' . $cell($colCount, $rowIdx));
|
||
$sheet->getStyle($cell(1, $rowIdx) . ':' . $cell($colCount, $rowIdx))
|
||
->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
|
||
$sheet->getStyle($cell(1, $rowIdx) . ':' . $cell($colCount, $rowIdx))
|
||
->getFont()->setBold(true)->setSize($rowIdx === 1 ? 14 : 11);
|
||
$rowIdx++;
|
||
}
|
||
$rowIdx++;
|
||
|
||
// Tabellen-Header
|
||
foreach ($EXCEL_COLS as $i => $label) {
|
||
$addr = $cell($i+1, $rowIdx);
|
||
$sheet->setCellValue($addr, $label);
|
||
$sheet->getStyle($addr)->getFont()->setBold(true);
|
||
$sheet->getStyle($addr)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
|
||
$sheet->getColumnDimension(Coordinate::stringFromColumnIndex($i+1))->setAutoSize(true);
|
||
}
|
||
$headerRow = $rowIdx;
|
||
$rowIdx++;
|
||
|
||
// Daten
|
||
$dataStartRow = $rowIdx;
|
||
foreach ($rows as $r) {
|
||
$c = 1;
|
||
|
||
// #
|
||
$sheet->setCellValue($cell($c++, $rowIdx), $r['pk']);
|
||
|
||
// Name (+ Materials)
|
||
$nameOut = $r['name'] . ($r['materials'] ? " | {$r['materials']}" : "");
|
||
$sheet->setCellValue($cell($c++, $rowIdx), $nameOut);
|
||
|
||
// Warengruppen
|
||
$sheet->setCellValue($cell($c++, $rowIdx), $r['groups']);
|
||
|
||
// Versicherung netto (numerisch, EU-Format)
|
||
$sheet->setCellValueExplicit($cell($c, $rowIdx), (float)$r['insurance_net'], DataType::TYPE_NUMERIC);
|
||
$sheet->getStyle($cell($c, $rowIdx))->getNumberFormat()->setFormatCode('#,##0.00 [$€-de-DE]');
|
||
$c++;
|
||
|
||
// Seriennummer
|
||
$sheet->setCellValue($cell($c++, $rowIdx), $r['serial_no']);
|
||
|
||
// Anschaffung (Datum)
|
||
$excelDate = '';
|
||
if (!empty($r['date_purchase'])) {
|
||
$ts = strtotime($r['date_purchase']);
|
||
if ($ts !== false) {
|
||
$excelDate = ExcelDate::PHPToExcel($ts);
|
||
}
|
||
}
|
||
if ($excelDate !== '') {
|
||
$sheet->setCellValue($cell($c, $rowIdx), $excelDate);
|
||
$sheet->getStyle($cell($c, $rowIdx))->getNumberFormat()->setFormatCode('dd.mm.yyyy');
|
||
} else {
|
||
$sheet->setCellValue($cell($c, $rowIdx), '');
|
||
}
|
||
$c++;
|
||
|
||
// Kaufpreis (numerisch, EU-Format)
|
||
$sheet->setCellValueExplicit($cell($c, $rowIdx), abs((float)$r['purchase_price']), DataType::TYPE_NUMERIC);
|
||
$sheet->getStyle($cell($c, $rowIdx))->getNumberFormat()->setFormatCode('#,##0.00 [$€-de-DE]');
|
||
$c++;
|
||
|
||
// Besitzer
|
||
$sheet->setCellValue($cell($c++, $rowIdx), $r['owner']);
|
||
|
||
$rowIdx++;
|
||
}
|
||
$dataEndRow = $rowIdx - 1;
|
||
|
||
/* === Summenzeile mit grauer Hinterlegung === */
|
||
$totalRow = $rowIdx + 1;
|
||
|
||
// Label in Spalte A
|
||
$sheet->setCellValue($cell(1, $totalRow), 'Gesamtsummen:');
|
||
$sheet->getStyle($cell(1, $totalRow))->getFont()->setBold(true);
|
||
|
||
// SUM-Formel Versicherung netto (Spalte 4)
|
||
$insColLetter = Coordinate::stringFromColumnIndex($INSURANCE_COL);
|
||
$sheet->setCellValue(
|
||
$cell($INSURANCE_COL, $totalRow),
|
||
"=SUM({$insColLetter}{$dataStartRow}:{$insColLetter}{$dataEndRow})"
|
||
);
|
||
$sheet->getStyle($cell($INSURANCE_COL, $totalRow))
|
||
->getNumberFormat()->setFormatCode('#,##0.00 [$€-de-DE]');
|
||
$sheet->getStyle($cell($INSURANCE_COL, $totalRow))->getFont()->setBold(true);
|
||
|
||
// SUM-Formel Kaufpreis (Spalte 7)
|
||
$purColLetter = Coordinate::stringFromColumnIndex($PURCHASE_COL);
|
||
$sheet->setCellValue(
|
||
$cell($PURCHASE_COL, $totalRow),
|
||
"=SUM({$purColLetter}{$dataStartRow}:{$purColLetter}{$dataEndRow})"
|
||
);
|
||
$sheet->getStyle($cell($PURCHASE_COL, $totalRow))
|
||
->getNumberFormat()->setFormatCode('#,##0.00 [$€-de-DE]');
|
||
$sheet->getStyle($cell($PURCHASE_COL, $totalRow))->getFont()->setBold(true);
|
||
|
||
// graue Hinterlegung über die ganze Summenzeile
|
||
$sheet->getStyle($cell(1, $totalRow) . ':' . $cell($colCount, $totalRow))
|
||
->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFEFEFEF');
|
||
|
||
// Filter + Freeze
|
||
$sheet->setAutoFilter("A{$headerRow}:{$lastCol}{$headerRow}");
|
||
$sheet->freezePane("A" . ($headerRow + 1));
|
||
|
||
// Datei speichern
|
||
$writer = new Xlsx($spreadsheet);
|
||
$writer->save($excelFilePath);
|
||
|
||
/* =========================
|
||
3) HTML-Ausgabe (Tabelle aus $rows) + Download-Link
|
||
========================= */
|
||
|
||
// Summen für HTML
|
||
$totalInsurance = array_sum(array_column($rows, 'insurance_net'));
|
||
$totalPurchase = array_sum(array_map(fn($r) => abs($r['purchase_price']), $rows));
|
||
?>
|
||
<!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>Dashboard - EpiWebview</title>
|
||
|
||
<!-- Styles -->
|
||
<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" />
|
||
|
||
<!-- JS -->
|
||
<script src="js/jquery-3.5.1.min.js"></script>
|
||
<script src="https://kit.fontawesome.com/93d71de8bc.js" crossorigin="anonymous"></script>
|
||
|
||
<style>
|
||
.opacity-50 { opacity: .5; }
|
||
.card .h2, .card .display-4 { font-weight: 700; }
|
||
.kpi-updated { font-size: .82rem; opacity: .85; }
|
||
</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">Versicherung</h1>
|
||
<ol class="breadcrumb mb-4">
|
||
<li class="breadcrumb-item active">Versicherung</li>
|
||
</ol>
|
||
|
||
<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> Versicherungsgeräte</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">
|
||
<div class="table-responsive">
|
||
<table class="table table-bordered" id="dataTable" width="100%" cellspacing="0">
|
||
<tr>
|
||
<th scope="col">#</th>
|
||
<th scope="col">Name</th>
|
||
<th scope="col">Warengruppe(n)</th>
|
||
<th scope="col">Versicherung netto</th>
|
||
<th scope="col">Seriennummer</th>
|
||
<th scope="col">Anschaffung</th>
|
||
<th scope="col">Kaufpreis</th>
|
||
<th scope="col">Besitzer</th>
|
||
</tr>
|
||
<?php foreach ($rows as $r): ?>
|
||
<tr>
|
||
<th><?php echo htmlspecialchars((string) $r['pk']); ?></th>
|
||
<th>
|
||
<?php
|
||
echo htmlspecialchars($r['name']);
|
||
if (!empty($r['materials'])) {
|
||
echo "<br><small>" . htmlspecialchars($r['materials']) . "</small>";
|
||
}
|
||
?>
|
||
</th>
|
||
<th><?php echo htmlspecialchars($r['groups']); ?></th>
|
||
<th><?php echo formatEuro((float)$r['insurance_net']); ?></th>
|
||
<th><?php echo htmlspecialchars($r['serial_no']); ?></th>
|
||
<th>
|
||
<?php
|
||
$purchaseDateOut = '';
|
||
if (!empty($r['date_purchase'])) {
|
||
try { $purchaseDateOut = date_format(new \DateTime($r['date_purchase']), 'd.m.Y'); } catch (\Throwable $e) {}
|
||
}
|
||
echo htmlspecialchars($purchaseDateOut);
|
||
?>
|
||
</th>
|
||
<th><?php echo formatEuro(abs((float)$r['purchase_price'])); ?></th>
|
||
<th><?php echo htmlspecialchars($r['owner']); ?></th>
|
||
</tr>
|
||
<?php endforeach; ?>
|
||
|
||
<!-- Summenzeile in HTML -->
|
||
<tr style="font-weight:bold; background:#f8f9fa;">
|
||
<td colspan="3" class="text-right">Gesamtsummen:</td>
|
||
<td><?php echo formatEuro($totalInsurance); ?></td>
|
||
<td></td>
|
||
<td></td>
|
||
<td><?php echo formatEuro($totalPurchase); ?></td>
|
||
<td></td>
|
||
</tr>
|
||
|
||
</table>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
</main>
|
||
<div id="footerholder"></div>
|
||
</div>
|
||
</div>
|
||
|
||
<!-- Bootstrap Bundle -->
|
||
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/js/bootstrap.bundle.min.js" crossorigin="anonymous"></script>
|
||
</body>
|
||
</html>
|