dolibarr.produktverwaltung/export.php

<?php
/* Copyright (C) 2026		Eduard Wisch				<data@data-it-solution.de>
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 */

/**
 * \file       produktverwaltung/export.php
 * \brief      Export Kategorie-Baum als CSV oder PDF
 */

// Load Dolibarr environment
$res = 0;
if (!$res && !empty($_SERVER["CONTEXT_DOCUMENT_ROOT"])) {
	$res = @include $_SERVER["CONTEXT_DOCUMENT_ROOT"]."/main.inc.php";
}
$tmp = empty($_SERVER['SCRIPT_FILENAME']) ? '' : $_SERVER['SCRIPT_FILENAME'];
$tmp2 = realpath(__FILE__);
$i = strlen($tmp) - 1;
$j = strlen($tmp2) - 1;
while ($i > 0 && $j > 0 && isset($tmp[$i]) && isset($tmp2[$j]) && $tmp[$i] == $tmp2[$j]) {
	$i--;
	$j--;
}
if (!$res && $i > 0 && file_exists(substr($tmp, 0, ($i + 1))."/main.inc.php")) {
	$res = @include substr($tmp, 0, ($i + 1))."/main.inc.php";
}
if (!$res && $i > 0 && file_exists(dirname(substr($tmp, 0, ($i + 1)))."/main.inc.php")) {
	$res = @include dirname(substr($tmp, 0, ($i + 1)))."/main.inc.php";
}
if (!$res && file_exists("../main.inc.php")) {
	$res = @include "../main.inc.php";
}
if (!$res && file_exists("../../main.inc.php")) {
	$res = @include "../../main.inc.php";
}
if (!$res) {
	die("Include of main fails");
}

require_once DOL_DOCUMENT_ROOT.'/categories/class/categorie.class.php';
require_once DOL_DOCUMENT_ROOT.'/core/lib/functions.lib.php';

$langs->loadLangs(array("produktverwaltung@produktverwaltung", "products"));

// Security
if (!$user->hasRight('produktverwaltung', 'export')) {
	accessforbidden();
}

$format = GETPOST('format', 'alpha');

// Check if preisbot_margin extrafield exists
$hasMarginField = false;
$sql = "SELECT name FROM ".MAIN_DB_PREFIX."extrafields WHERE elementtype = 'product' AND name = 'preisbot_margin'";
$resql = $db->query($sql);
if ($resql && $db->num_rows($resql) > 0) {
	$hasMarginField = true;
}

// Load category tree
$categorie = new Categorie($db);
$fullTree = $categorie->get_full_arbo(Categorie::TYPE_PRODUCT);

$catChildren = array();
$catData = array();
if (is_array($fullTree)) {
	foreach ($fullTree as $cat) {
		$catData[$cat['id']] = $cat;
		$parentId = isset($cat['fk_parent']) ? (int) $cat['fk_parent'] : 0;
		if (!isset($catChildren[$parentId])) {
			$catChildren[$parentId] = array();
		}
		$catChildren[$parentId][] = $cat;
	}
}

// Load products per category
$productsPerCat = array();
$sql = "SELECT p.rowid, p.ref, p.label, p.price, p.cost_price, p.pmp";
if ($hasMarginField) {
	$sql .= ", pe.preisbot_margin";
}
$sql .= ", cp.fk_categorie";
$sql .= " FROM ".MAIN_DB_PREFIX."product as p";
$sql .= " LEFT JOIN ".MAIN_DB_PREFIX."categorie_product as cp ON cp.fk_product = p.rowid";
if ($hasMarginField) {
	$sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product_extrafields as pe ON pe.fk_object = p.rowid";
}
$sql .= " WHERE p.entity IN (".getEntity('product').")";
$sql .= " ORDER BY p.ref ASC";

$resql = $db->query($sql);
if ($resql) {
	while ($obj = $db->fetch_object($resql)) {
		if (!empty($obj->fk_categorie)) {
			$catId = (int) $obj->fk_categorie;
			if (!isset($productsPerCat[$catId])) {
				$productsPerCat[$catId] = array();
			}
			$productsPerCat[$catId][$obj->rowid] = array(
				'ref' => $obj->ref,
				'label' => $obj->label,
				'cost_price' => !empty($obj->cost_price) ? $obj->cost_price : $obj->pmp,
				'sell_price' => $obj->price,
				'margin' => $hasMarginField ? $obj->preisbot_margin : null,
			);
		}
	}
}

// Products without category
$productsWithoutCat = array();
$sql2 = "SELECT p.rowid, p.ref, p.label, p.price, p.cost_price, p.pmp";
if ($hasMarginField) {
	$sql2 .= ", pe.preisbot_margin";
}
$sql2 .= " FROM ".MAIN_DB_PREFIX."product as p";
if ($hasMarginField) {
	$sql2 .= " LEFT JOIN ".MAIN_DB_PREFIX."product_extrafields as pe ON pe.fk_object = p.rowid";
}
$sql2 .= " WHERE p.entity IN (".getEntity('product').")";
$sql2 .= " AND p.rowid NOT IN (SELECT fk_product FROM ".MAIN_DB_PREFIX."categorie_product)";
$sql2 .= " ORDER BY p.ref ASC";

$resql2 = $db->query($sql2);
if ($resql2) {
	while ($obj = $db->fetch_object($resql2)) {
		$productsWithoutCat[$obj->rowid] = array(
			'ref' => $obj->ref,
			'label' => $obj->label,
			'cost_price' => !empty($obj->cost_price) ? $obj->cost_price : $obj->pmp,
			'sell_price' => $obj->price,
			'margin' => $hasMarginField ? $obj->preisbot_margin : null,
		);
	}
}

// Collect all rows for export
$exportRows = array();
collectExportRows(0, $catChildren, $catData, $productsPerCat, $hasMarginField, $exportRows, 0);

// Add without-category products
if (!empty($productsWithoutCat)) {
	$exportRows[] = array('type' => 'category', 'level' => 0, 'label' => '*** '.$langs->trans("ProductsWithoutCategory").' ***', 'count' => count($productsWithoutCat));
	foreach ($productsWithoutCat as $prod) {
		$row = array('type' => 'product', 'level' => 1);
		$row = array_merge($row, $prod);
		$exportRows[] = $row;
	}
}


if ($format === 'csv') {
	exportCSV($exportRows, $hasMarginField, $langs);
} elseif ($format === 'pdf') {
	exportPDF($exportRows, $hasMarginField, $langs, $conf);
} else {
	header('Location: '.dol_buildpath('/produktverwaltung/produktverwaltungindex.php', 1));
	exit;
}

$db->close();


// ========== Functions ==========

function collectExportRows($parentId, &$catChildren, &$catData, &$productsPerCat, $hasMarginField, &$rows, $level)
{
	if (!isset($catChildren[$parentId])) {
		return;
	}

	foreach ($catChildren[$parentId] as $cat) {
		$catId = (int) $cat['id'];
		$products = isset($productsPerCat[$catId]) ? $productsPerCat[$catId] : array();

		$rows[] = array('type' => 'category', 'level' => $level, 'label' => $cat['label'], 'count' => count($products));

		foreach ($products as $prod) {
			$row = array('type' => 'product', 'level' => $level + 1);
			$row = array_merge($row, $prod);
			$rows[] = $row;
		}

		if (isset($catChildren[$catId])) {
			collectExportRows($catId, $catChildren, $catData, $productsPerCat, $hasMarginField, $rows, $level + 1);
		}
	}
}

function exportCSV($rows, $hasMarginField, $langs)
{
	$filename = 'produktkatalog_'.date('Y-m-d').'.csv';
	header('Content-Type: text/csv; charset=utf-8');
	header('Content-Disposition: attachment; filename="'.$filename.'"');

	$output = fopen('php://output', 'w');

	// BOM for Excel UTF-8
	fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));

	// Header
	$header = array('Typ', 'Ebene', 'Kategorie/Ref', 'Bezeichnung', 'EK netto', 'VK netto');
	if ($hasMarginField) {
		$header[] = 'Gewinnaufschlag %';
	}
	fputcsv($output, $header, ';');

	foreach ($rows as $row) {
		$indent = str_repeat('  ', $row['level']);
		if ($row['type'] === 'category') {
			$line = array('Kategorie', $row['level'], $indent.$row['label'], '('.$row['count'].' Produkte)', '', '');
			if ($hasMarginField) {
				$line[] = '';
			}
		} else {
			$line = array(
				'Produkt',
				$row['level'],
				$indent.$row['ref'],
				$row['label'],
				isset($row['cost_price']) && $row['cost_price'] > 0 ? number_format($row['cost_price'], 2, ',', '.') : '',
				isset($row['sell_price']) && $row['sell_price'] > 0 ? number_format($row['sell_price'], 2, ',', '.') : '',
			);
			if ($hasMarginField) {
				$line[] = !empty($row['margin']) ? number_format($row['margin'], 1, ',', '.') : '';
			}
		}
		fputcsv($output, $line, ';');
	}

	fclose($output);
	exit;
}

function exportPDF($rows, $hasMarginField, $langs, $conf)
{
	require_once DOL_DOCUMENT_ROOT.'/core/lib/pdf.lib.php';

	$pdf = pdf_getInstance('A4', 'mm', 'L'); // Landscape
	$pdf->SetCreator('Dolibarr - Produktverwaltung');
	$pdf->SetTitle($langs->trans('ExportTitle'));
	$pdf->SetAutoPageBreak(true, 15);
	$pdf->AddPage();

	// Title
	$pdf->SetFont('', 'B', 16);
	$pdf->Cell(0, 10, $langs->trans('ExportTitle').' - '.date('d.m.Y'), 0, 1, 'C');
	$pdf->Ln(5);

	// Column widths (Landscape A4 = 277mm usable)
	$colWidths = array(60, 100, 35, 35);
	$headers = array($langs->trans('Ref'), $langs->trans('Label'), $langs->trans('BuyingPriceNet'), $langs->trans('SellingPriceNet'));
	if ($hasMarginField) {
		$colWidths[] = 25;
		$headers[] = 'GA%';
	}

	// Table header
	$pdf->SetFont('', 'B', 8);
	$pdf->SetFillColor(230, 230, 230);

	foreach ($rows as $row) {
		// Check page break
		if ($pdf->GetY() > 185) {
			$pdf->AddPage();
		}

		if ($row['type'] === 'category') {
			$indent = $row['level'] * 4;
			$pdf->SetFont('', 'B', 9 - min($row['level'], 2));
			$label = str_repeat('  ', $row['level']).$row['label'];
			if ($row['count'] > 0) {
				$label .= ' ('.$row['count'].')';
			}
			$pdf->SetFillColor(240 - ($row['level'] * 15), 240 - ($row['level'] * 10), 255 - ($row['level'] * 5));
			$totalWidth = array_sum($colWidths);
			$pdf->Cell($totalWidth, 6, $label, 0, 1, 'L', true);
		} else {
			$pdf->SetFont('', '', 7);
			$indent = $row['level'] * 4;
			$pdf->Cell($colWidths[0], 5, str_repeat(' ', $row['level'] * 2).$row['ref'], 0, 0, 'L');
			$pdf->Cell($colWidths[1], 5, dol_trunc($row['label'], 55), 0, 0, 'L');
			$costStr = isset($row['cost_price']) && $row['cost_price'] > 0 ? number_format($row['cost_price'], 2, ',', '.') : '-';
			$sellStr = isset($row['sell_price']) && $row['sell_price'] > 0 ? number_format($row['sell_price'], 2, ',', '.') : '-';
			$pdf->Cell($colWidths[2], 5, $costStr, 0, 0, 'R');
			$pdf->Cell($colWidths[3], 5, $sellStr, 0, 0, 'R');
			if ($hasMarginField) {
				$marginStr = !empty($row['margin']) ? number_format($row['margin'], 1, ',', '.').'%' : '-';
				$pdf->Cell($colWidths[4], 5, $marginStr, 0, 0, 'R');
			}
			$pdf->Ln();
		}
	}

	$filename = 'produktkatalog_'.date('Y-m-d').'.pdf';
	$pdf->Output($filename, 'D');
	exit;
}