buchhaltungswidget/invoice_category_stats.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       htdocs/custom/buchaltungswidget/invoice_category_stats.php
 * \ingroup    buchaltungswidget
 * \brief      Invoice statistics filtered by invoice category (Schlagwort/Kategorie Rechnung)
 */

// 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 && file_exists("../../../main.inc.php")) {
	$res = @include "../../../main.inc.php";
}
if (!$res) {
	die("Include of main fails");
}

require_once DOL_DOCUMENT_ROOT.'/core/class/dolgraph.class.php';
require_once DOL_DOCUMENT_ROOT.'/core/class/html.formcompany.class.php';
require_once DOL_DOCUMENT_ROOT.'/core/class/html.formother.class.php';
require_once DOL_DOCUMENT_ROOT.'/categories/class/categorie.class.php';

// Security check
if (!$user->hasRight('facture', 'lire')) {
	accessforbidden();
}

$langs->loadLangs(array("buchaltungswidget@buchaltungswidget", "bills", "companies", "categories"));

$WIDTH = DolGraph::getDefaultGraphSizeForStats('width');
$HEIGHT = DolGraph::getDefaultGraphSizeForStats('height');

// Get parameters
$socid = GETPOSTINT('socid');
$userid = GETPOSTINT('userid');
$categ_invoice_id = GETPOST('categ_invoice_id', 'array');
$object_status = GETPOST('object_status', 'intcomma');

if ($user->socid > 0) {
	$socid = $user->socid;
}

$nowyear = dol_print_date(dol_now('gmt'), "%Y", 'gmt');
$year = GETPOST('year') > 0 ? GETPOSTINT('year') : $nowyear;
$startyear = $year - (!getDolGlobalString('MAIN_STATS_GRAPHS_SHOW_N_YEARS') ? 2 : max(1, min(10, getDolGlobalString('MAIN_STATS_GRAPHS_SHOW_N_YEARS'))));
$endyear = $year;

/*
 * View
 */
$form = new Form($db);
$formcompany = new FormCompany($db);
$formother = new FormOther($db);

$title = $langs->trans("InvoiceCategoryStatistics");
$dir = $conf->facture->dir_temp;

llxHeader('', $title);

print load_fiche_titre($title, '', 'bill');

dol_mkdir($dir);

// Build WHERE clause for invoice category filter
$sql_category_join = '';
$sql_category_where = '';
if (is_array($categ_invoice_id) && !empty($categ_invoice_id) && !in_array('0', $categ_invoice_id)) {
	// Sanitize category IDs as integers
	$categ_ids_sanitized = array_map('intval', $categ_invoice_id);
	$categ_ids_sanitized = array_filter($categ_ids_sanitized, function($v) { return $v > 0; });
	if (!empty($categ_ids_sanitized)) {
		// Table is llx_categorie_invoice (not categorie_facture!)
		$sql_category_join = ' INNER JOIN '.MAIN_DB_PREFIX.'categorie_invoice as cf ON cf.fk_invoice = f.rowid';
		$sql_category_where = ' AND cf.fk_categorie IN ('.implode(',', $categ_ids_sanitized).')';
	}
}

// Get statistics data
$data_nb = getInvoiceStatsByMonth($db, $conf, $startyear, $endyear, $socid, $userid, $object_status, $sql_category_join, $sql_category_where, 'nb');
$data_amount = getInvoiceStatsByMonth($db, $conf, $startyear, $endyear, $socid, $userid, $object_status, $sql_category_join, $sql_category_where, 'amount');
$data_avg = getInvoiceStatsByMonth($db, $conf, $startyear, $endyear, $socid, $userid, $object_status, $sql_category_join, $sql_category_where, 'avg');
$data_yearly = getInvoiceStatsByYear($db, $conf, $socid, $userid, $object_status, $sql_category_join, $sql_category_where);

// Build graphs
$filenamenb = $dir."/invoicescatnbinyear-".$year.".png";
$fileurlnb = DOL_URL_ROOT.'/viewimage.php?modulepart=billstats&file=invoicescatnbinyear-'.$year.'.png';

$px1 = new DolGraph();
$mesg = $px1->isGraphKo();
if (!$mesg && !empty($data_nb)) {
	$px1->SetData($data_nb);
	$legend = array();
	for ($i = $startyear; $i <= $endyear; $i++) {
		$legend[] = $i;
	}
	$px1->SetLegend($legend);
	$px1->SetMaxValue($px1->GetCeilMaxValue());
	$px1->SetWidth($WIDTH);
	$px1->SetHeight($HEIGHT);
	$px1->SetYLabel($langs->trans("NumberOfBills"));
	$px1->SetShading(3);
	$px1->SetHorizTickIncrement(1);
	$px1->mode = 'depth';
	$px1->SetTitle($langs->trans("NumberOfBillsByMonth"));
	$px1->draw($filenamenb, $fileurlnb);
}

$filenameamount = $dir."/invoicescatamountinyear-".$year.".png";
$fileurlamount = DOL_URL_ROOT.'/viewimage.php?modulepart=billstats&file=invoicescatamountinyear-'.$year.'.png';

$px2 = new DolGraph();
if (!$mesg && !empty($data_amount)) {
	$px2->SetData($data_amount);
	$legend = array();
	for ($i = $startyear; $i <= $endyear; $i++) {
		$legend[] = $i;
	}
	$px2->SetLegend($legend);
	$px2->SetMaxValue($px2->GetCeilMaxValue());
	$px2->SetMinValue(min(0, $px2->GetFloorMinValue()));
	$px2->SetWidth($WIDTH);
	$px2->SetHeight($HEIGHT);
	$px2->SetYLabel($langs->trans("AmountOfBills"));
	$px2->SetShading(3);
	$px2->SetHorizTickIncrement(1);
	$px2->mode = 'depth';
	$px2->SetTitle($langs->trans("AmountOfBillsByMonthHT"));
	$px2->draw($filenameamount, $fileurlamount);
}

$filename_avg = $dir."/invoicescatavginyear-".$year.".png";
$fileurl_avg = DOL_URL_ROOT.'/viewimage.php?modulepart=billstats&file=invoicescatavginyear-'.$year.'.png';

$px3 = new DolGraph();
if (!$mesg && !empty($data_avg)) {
	$px3->SetData($data_avg);
	$legend = array();
	for ($i = $startyear; $i <= $endyear; $i++) {
		$legend[] = $i;
	}
	$px3->SetLegend($legend);
	$px3->SetYLabel($langs->trans("AmountAverage"));
	$px3->SetMaxValue($px3->GetCeilMaxValue());
	$px3->SetMinValue((int) $px3->GetFloorMinValue());
	$px3->SetWidth($WIDTH);
	$px3->SetHeight($HEIGHT);
	$px3->SetShading(3);
	$px3->SetHorizTickIncrement(1);
	$px3->mode = 'depth';
	$px3->SetTitle($langs->trans("AmountAverage"));
	$px3->draw($filename_avg, $fileurl_avg);
}

// Get ALL available years from invoices (without category filter)
$arrayyears = array();
$sql_years = "SELECT DISTINCT YEAR(f.datef) as year FROM ".MAIN_DB_PREFIX."facture as f";
$sql_years .= " WHERE f.entity = ".((int) $conf->entity);
$sql_years .= " AND f.fk_statut > 0";
$sql_years .= " ORDER BY year DESC";
$resql_years = $db->query($sql_years);
if ($resql_years) {
	while ($obj_year = $db->fetch_object($resql_years)) {
		if ($obj_year->year) {
			$arrayyears[$obj_year->year] = $obj_year->year;
		}
	}
	$db->free($resql_years);
}
if (!count($arrayyears)) {
	$arrayyears[$nowyear] = $nowyear;
}

print '<div class="fichecenter"><div class="fichethirdleft">';

// Filter form
print '<form name="stats" method="POST" action="'.$_SERVER["PHP_SELF"].'">';
print '<input type="hidden" name="token" value="'.newToken().'">';

print '<table class="noborder centpercent">';
print '<tr class="liste_titre"><td class="liste_titre" colspan="2">'.$langs->trans("Filter").'</td></tr>';

// Invoice Category - THE NEW FILTER
print '<tr><td>'.$langs->trans("Category").' '.$langs->trans("Invoice").'</td><td>';
$cate_arbo = $form->select_all_categories(Categorie::TYPE_INVOICE, '', 'parent', 0, 0, 1);
print img_picto('', 'category', 'class="pictofixedwidth"');
print $form->multiselectarray('categ_invoice_id', $cate_arbo, $categ_invoice_id, 0, 0, 'widthcentpercentminusx maxwidth300');
print '</td></tr>';

// Company
print '<tr><td>'.$langs->trans("ThirdParty").'</td><td>';
print img_picto('', 'company', 'class="pictofixedwidth"');
print $form->select_company($socid, 'socid', '(s.client:IN:1,2,3)', 1, 0, 0, array(), 0, 'widthcentpercentminusx maxwidth300');
print '</td></tr>';

// User
print '<tr><td>'.$langs->trans("CreatedBy").'</td><td>';
print img_picto('', 'user', 'class="pictofixedwidth"');
print $form->select_dolusers($userid ? $userid : -1, 'userid', 1, null, 0, '', '', '0', 0, 0, '', 0, '', 'widthcentpercentminusx maxwidth300');
print '</td></tr>';

// Status
print '<tr><td>'.$langs->trans("Status").'</td><td>';
$liststatus = array(
	'0' => $langs->trans("BillStatusDraft"),
	'1' => $langs->trans("BillStatusNotPaid"),
	'2' => $langs->trans("BillStatusPaid"),
	'1,2' => $langs->trans("BillStatusNotPaid").' / '.$langs->trans("BillStatusPaid"),
	'3' => $langs->trans("BillStatusCanceled")
);
print $form->selectarray('object_status', $liststatus, $object_status, 1);
print '</td></tr>';

// Year
print '<tr><td>'.$langs->trans("Year").'</td><td>';
if (!in_array($year, $arrayyears)) {
	$arrayyears[$year] = $year;
}
if (!in_array($nowyear, $arrayyears)) {
	$arrayyears[$nowyear] = $nowyear;
}
arsort($arrayyears);
print $form->selectarray('year', $arrayyears, $year, 0, 0, 0, '', 0, 0, 0, '', 'width75');
print '</td></tr>';

print '<tr><td class="center" colspan="2"><input type="submit" name="submit" class="button small" value="'.$langs->trans("Refresh").'"></td></tr>';
print '</table>';
print '</form>';
print '<br><br>';

// Yearly table
print '<div class="div-table-responsive-no-min">';
print '<table class="noborder centpercent">';
print '<tr class="liste_titre" height="24">';
print '<td class="center">'.$langs->trans("Year").'</td>';
print '<td class="right">'.$langs->trans("NumberOfBills").'</td>';
print '<td class="right">%</td>';
print '<td class="right">'.$langs->trans("AmountTotal").'</td>';
print '<td class="right">%</td>';
print '<td class="right">'.$langs->trans("AmountAverage").'</td>';
print '<td class="right">%</td>';
print '</tr>';

$oldyear = 0;
foreach ($data_yearly as $val) {
	$year_row = (int) $val['year'];
	while ($year_row && $oldyear > $year_row + 1) {
		$oldyear--;
		print '<tr class="oddeven" height="24">';
		print '<td align="center">'.$oldyear.'</td>';
		print '<td class="right">0</td>';
		print '<td class="right"></td>';
		print '<td class="right amount">0</td>';
		print '<td class="right"></td>';
		print '<td class="right amount">0</td>';
		print '<td class="right"></td>';
		print '</tr>';
	}

	$greennb = (empty($val['nb_diff']) || $val['nb_diff'] >= 0);
	$greentotal = (empty($val['total_diff']) || $val['total_diff'] >= 0);
	$greenavg = (empty($val['avg_diff']) || $val['avg_diff'] >= 0);

	print '<tr class="oddeven" height="24">';
	print '<td align="center"><a href="'.$_SERVER["PHP_SELF"].'?year='.$year_row.($socid > 0 ? '&socid='.$socid : '').($userid > 0 ? '&userid='.$userid : '').'">'.$year_row.'</a></td>';
	print '<td class="right">'.$val['nb'].'</td>';
	print '<td class="right opacitylow" style="'.($greennb ? 'color: green;' : 'color: red;').'">'.(!empty($val['nb_diff']) && $val['nb_diff'] < 0 ? '' : '+').round(!empty($val['nb_diff']) ? $val['nb_diff'] : 0).'%</td>';
	print '<td class="right"><span class="amount">'.price(price2num($val['total'], 'MT'), 1).'</span></td>';
	print '<td class="right opacitylow" style="'.($greentotal ? 'color: green;' : 'color: red;').'">'.(!empty($val['total_diff']) && $val['total_diff'] < 0 ? '' : '+').round(!empty($val['total_diff']) ? $val['total_diff'] : 0).'%</td>';
	print '<td class="right"><span class="amount">'.price(price2num($val['avg'], 'MT'), 1).'</span></td>';
	print '<td class="right opacitylow" style="'.($greenavg ? 'color: green;' : 'color: red;').'">'.(!empty($val['avg_diff']) && $val['avg_diff'] < 0 ? '' : '+').round(!empty($val['avg_diff']) ? $val['avg_diff'] : 0).'%</td>';
	print '</tr>';
	$oldyear = $year_row;
}

print '</table>';
print '</div>';

print '</div><div class="fichetwothirdright">';

// Show graphs
print '<table class="border centpercent"><tr class="pair nohover"><td align="center">';
if ($mesg) {
	print $mesg;
} else {
	print $px1->show();
	print "<br>\n";
	print $px2->show();
	print "<br>\n";
	print $px3->show();
}
print '</td></tr></table>';

print '</div></div>';
print '<div class="clearboth"></div>';

llxFooter();
$db->close();


/**
 * Get invoice statistics by month
 */
function getInvoiceStatsByMonth($db, $conf, $startyear, $endyear, $socid, $userid, $object_status, $sql_category_join, $sql_category_where, $mode = 'nb')
{
	$data = array();

	// Initialize data array with month labels
	$months = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
	for ($m = 0; $m < 12; $m++) {
		$data[$m] = array($months[$m]);
		for ($y = $startyear; $y <= $endyear; $y++) {
			$data[$m][] = 0;
		}
	}

	for ($year = $startyear; $year <= $endyear; $year++) {
		$yearIndex = $year - $startyear + 1;

		if ($mode == 'nb') {
			$sql = "SELECT MONTH(f.datef) as month, COUNT(DISTINCT f.rowid) as val";
		} elseif ($mode == 'amount') {
			$sql = "SELECT MONTH(f.datef) as month, SUM(f.total_ht) as val";
		} else {
			$sql = "SELECT MONTH(f.datef) as month, AVG(f.total_ht) as val";
		}
		$sql .= " FROM ".MAIN_DB_PREFIX."facture as f";
		$sql .= $sql_category_join;
		$sql .= " WHERE f.entity = ".((int) $conf->entity);
		$sql .= " AND YEAR(f.datef) = ".((int) $year);
		$sql .= " AND f.fk_statut > 0";
		if ($socid > 0) {
			$sql .= " AND f.fk_soc = ".((int) $socid);
		}
		if ($userid > 0) {
			$sql .= " AND f.fk_user_author = ".((int) $userid);
		}
		if ($object_status != '' && $object_status >= 0) {
			$sql .= " AND f.fk_statut IN (".$db->sanitize($object_status).")";
		}
		$sql .= $sql_category_where;
		$sql .= " GROUP BY MONTH(f.datef)";

		$resql = $db->query($sql);
		if ($resql) {
			while ($obj = $db->fetch_object($resql)) {
				$monthIndex = $obj->month - 1;
				if ($monthIndex >= 0 && $monthIndex < 12) {
					$data[$monthIndex][$yearIndex] = round((float) $obj->val, 2);
				}
			}
			$db->free($resql);
		}
	}

	return $data;
}

/**
 * Get invoice statistics by year
 */
function getInvoiceStatsByYear($db, $conf, $socid, $userid, $object_status, $sql_category_join, $sql_category_where)
{
	$data = array();

	$sql = "SELECT YEAR(f.datef) as year, COUNT(DISTINCT f.rowid) as nb, SUM(f.total_ht) as total, AVG(f.total_ht) as avg";
	$sql .= " FROM ".MAIN_DB_PREFIX."facture as f";
	$sql .= $sql_category_join;
	$sql .= " WHERE f.entity = ".((int) $conf->entity);
	$sql .= " AND f.fk_statut > 0";
	if ($socid > 0) {
		$sql .= " AND f.fk_soc = ".((int) $socid);
	}
	if ($userid > 0) {
		$sql .= " AND f.fk_user_author = ".((int) $userid);
	}
	if ($object_status != '' && $object_status >= 0) {
		$sql .= " AND f.fk_statut IN (".$db->sanitize($object_status).")";
	}
	$sql .= $sql_category_where;
	$sql .= " GROUP BY YEAR(f.datef)";
	$sql .= " ORDER BY year DESC";

	$resql = $db->query($sql);
	if ($resql) {
		// Zuerst alle Zeilen einlesen (absteigend sortiert)
		$rows = array();
		while ($obj = $db->fetch_object($resql)) {
			$rows[] = array(
				'year' => $obj->year,
				'nb' => (int) $obj->nb,
				'total' => (float) $obj->total,
				'avg' => (float) $obj->avg,
			);
		}
		$db->free($resql);

		// Prozentuale Veraenderung zum Vorjahr berechnen (jedes Jahr mit dem aelteren vergleichen)
		for ($i = 0; $i < count($rows); $i++) {
			$rows[$i]['nb_diff'] = 0;
			$rows[$i]['total_diff'] = 0;
			$rows[$i]['avg_diff'] = 0;

			// Das naechste Element im Array ist das aeltere Jahr (DESC-Sortierung)
			if (isset($rows[$i + 1])) {
				$prev = $rows[$i + 1];
				if ($prev['nb'] > 0) {
					$rows[$i]['nb_diff'] = (($rows[$i]['nb'] - $prev['nb']) / $prev['nb']) * 100;
				}
				if ($prev['total'] > 0) {
					$rows[$i]['total_diff'] = (($rows[$i]['total'] - $prev['total']) / $prev['total']) * 100;
				}
				if ($prev['avg'] > 0) {
					$rows[$i]['avg_diff'] = (($rows[$i]['avg'] - $prev['avg']) / $prev['avg']) * 100;
				}
			}
		}

		$data = $rows;
	}

	return $data;
}