412 lines
14 KiB
PHP
Executable file
412 lines
14 KiB
PHP
Executable file
<?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/rentabilitaet_detail.php
|
|
* \ingroup buchaltungswidget
|
|
* \brief Detail page for Profitability analysis with full charts and year selection
|
|
*/
|
|
|
|
// 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/lib/admin.lib.php';
|
|
|
|
// Load translation files
|
|
$langs->loadLangs(array("buchaltungswidget@buchaltungswidget", "bills", "compta"));
|
|
|
|
// Access control
|
|
if (!$user->hasRight('facture', 'lire') && !$user->hasRight('fournisseur', 'facture', 'lire')) {
|
|
accessforbidden();
|
|
}
|
|
|
|
// Get parameters
|
|
$selectedYear = GETPOST('year', 'int');
|
|
if (empty($selectedYear)) {
|
|
$selectedYear = date('Y');
|
|
}
|
|
|
|
$currentYear = date('Y');
|
|
$years = range($currentYear - 5, $currentYear + 1);
|
|
|
|
/*
|
|
* View
|
|
*/
|
|
|
|
$title = $langs->trans("Rentabilitaet");
|
|
llxHeader('', $title, '', '', 0, 0, array('/includes/nnnick/chartjs/dist/Chart.min.js'), array('/buchaltungswidget/css/buchaltungswidget.css'));
|
|
|
|
print load_fiche_titre($title, '', 'accountancy');
|
|
|
|
// Year selector
|
|
print '<div class="fichecenter">';
|
|
print '<form method="GET" action="'.$_SERVER['PHP_SELF'].'">';
|
|
print '<div class="buchaltung-filter-bar">';
|
|
print '<label for="year">'.$langs->trans("Year").':</label>';
|
|
print '<select name="year" id="year" class="flat" onchange="this.form.submit()">';
|
|
foreach ($years as $y) {
|
|
$selected = ($y == $selectedYear) ? ' selected' : '';
|
|
print '<option value="'.$y.'"'.$selected.'>'.$y.'</option>';
|
|
}
|
|
print '</select>';
|
|
print '</div>';
|
|
print '</form>';
|
|
print '</div>';
|
|
|
|
// Get data
|
|
$currentData = getProfitabilityByMonth($db, $selectedYear);
|
|
$lastYearData = getProfitabilityByMonth($db, $selectedYear - 1);
|
|
|
|
// Charts
|
|
print '<div class="fichecenter">';
|
|
print '<div class="fichethirdleft">';
|
|
print '<div class="buchaltung-detail-chart" style="height: 300px;">';
|
|
print '<h4>'.$langs->trans("ProfitMarginTrend").'</h4>';
|
|
print '<div style="height: 240px; position: relative;">';
|
|
print '<canvas id="marginChart"></canvas>';
|
|
print '</div>';
|
|
print '</div>';
|
|
print '</div>';
|
|
|
|
print '<div class="fichethirdright">';
|
|
print '<div class="buchaltung-detail-chart" style="height: 300px;">';
|
|
print '<h4>'.$langs->trans("PurchasedVsInvoiced").'</h4>';
|
|
print '<div style="height: 240px; position: relative;">';
|
|
print '<canvas id="comparisonChart"></canvas>';
|
|
print '</div>';
|
|
print '</div>';
|
|
print '</div>';
|
|
print '</div>';
|
|
|
|
print '<div class="clearboth" style="margin-bottom: 20px;"></div>';
|
|
|
|
// Data table
|
|
print '<div class="div-table-responsive">';
|
|
print '<table class="noborder centpercent">';
|
|
|
|
// Header
|
|
print '<tr class="liste_titre">';
|
|
print '<th>'.$langs->trans("Month").'</th>';
|
|
print '<th class="right">'.$langs->trans("MaterialsPurchasedForCustomers").'</th>';
|
|
print '<th class="right">'.$langs->trans("MaterialsServicesInvoiced").'</th>';
|
|
print '<th class="right">'.$langs->trans("GrossProfit").'</th>';
|
|
print '<th class="right">'.$langs->trans("ProfitMargin").'</th>';
|
|
print '<th class="right">'.($selectedYear - 1).' '.$langs->trans("ProfitMargin").'</th>';
|
|
print '</tr>';
|
|
|
|
$months = array(1 => 'Januar', 2 => 'Februar', 3 => 'Maerz', 4 => 'April', 5 => 'Mai', 6 => 'Juni',
|
|
7 => 'Juli', 8 => 'August', 9 => 'September', 10 => 'Oktober', 11 => 'November', 12 => 'Dezember');
|
|
$monthsShort = array(1 => 'Jan', 2 => 'Feb', 3 => 'Mar', 4 => 'Apr', 5 => 'Mai', 6 => 'Jun',
|
|
7 => 'Jul', 8 => 'Aug', 9 => 'Sep', 10 => 'Okt', 11 => 'Nov', 12 => 'Dez');
|
|
|
|
$totalPurchased = 0;
|
|
$totalInvoiced = 0;
|
|
$totalPurchasedLast = 0;
|
|
$totalInvoicedLast = 0;
|
|
|
|
$labels = array();
|
|
$purchasedData = array();
|
|
$invoicedData = array();
|
|
$marginData = array();
|
|
$marginLastData = array();
|
|
$marginColors = array();
|
|
|
|
foreach ($months as $m => $name) {
|
|
$purchased = isset($currentData['purchased'][$m]) ? $currentData['purchased'][$m] : 0;
|
|
$invoiced = isset($currentData['invoiced'][$m]) ? $currentData['invoiced'][$m] : 0;
|
|
$grossProfit = $invoiced - $purchased;
|
|
$margin = ($purchased > 0) ? (($invoiced - $purchased) / $purchased) * 100 : 0;
|
|
|
|
$purchasedLast = isset($lastYearData['purchased'][$m]) ? $lastYearData['purchased'][$m] : 0;
|
|
$invoicedLast = isset($lastYearData['invoiced'][$m]) ? $lastYearData['invoiced'][$m] : 0;
|
|
$marginLast = ($purchasedLast > 0) ? (($invoicedLast - $purchasedLast) / $purchasedLast) * 100 : 0;
|
|
|
|
$totalPurchased += $purchased;
|
|
$totalInvoiced += $invoiced;
|
|
$totalPurchasedLast += $purchasedLast;
|
|
$totalInvoicedLast += $invoicedLast;
|
|
|
|
$colorProfit = $grossProfit >= 0 ? 'buchaltung-positive' : 'buchaltung-negative';
|
|
$colorMargin = $margin >= 0 ? 'buchaltung-positive' : 'buchaltung-negative';
|
|
$colorMarginLast = $marginLast >= 0 ? 'buchaltung-positive' : 'buchaltung-negative';
|
|
|
|
print '<tr class="oddeven">';
|
|
print '<td>'.$name.'</td>';
|
|
print '<td class="right">'.price($purchased, 0, $langs, 1, 2, 2, $conf->currency).'</td>';
|
|
print '<td class="right">'.price($invoiced, 0, $langs, 1, 2, 2, $conf->currency).'</td>';
|
|
print '<td class="right '.$colorProfit.'"><strong>'.price($grossProfit, 0, $langs, 1, 2, 2, $conf->currency).'</strong></td>';
|
|
print '<td class="right '.$colorMargin.'"><strong>'.number_format($margin, 1, ',', '.').' %</strong></td>';
|
|
print '<td class="right buchaltung-lastyear '.$colorMarginLast.'">'.number_format($marginLast, 1, ',', '.').' %</td>';
|
|
print '</tr>';
|
|
|
|
// Chart data
|
|
$labels[] = $monthsShort[$m];
|
|
$purchasedData[] = round($purchased, 2);
|
|
$invoicedData[] = round($invoiced, 2);
|
|
$marginData[] = round($margin, 1);
|
|
$marginLastData[] = round($marginLast, 1);
|
|
$marginColors[] = $margin >= 0 ? 'rgba(40, 167, 69, 1)' : 'rgba(220, 53, 69, 1)';
|
|
}
|
|
|
|
// Total row
|
|
$totalGrossProfit = $totalInvoiced - $totalPurchased;
|
|
$totalMargin = ($totalPurchased > 0) ? (($totalInvoiced - $totalPurchased) / $totalPurchased) * 100 : 0;
|
|
$totalMarginLast = ($totalPurchasedLast > 0) ? (($totalInvoicedLast - $totalPurchasedLast) / $totalPurchasedLast) * 100 : 0;
|
|
|
|
$colorProfit = $totalGrossProfit >= 0 ? 'buchaltung-positive' : 'buchaltung-negative';
|
|
$colorMargin = $totalMargin >= 0 ? 'buchaltung-positive' : 'buchaltung-negative';
|
|
$colorMarginLast = $totalMarginLast >= 0 ? 'buchaltung-positive' : 'buchaltung-negative';
|
|
|
|
print '<tr class="liste_total">';
|
|
print '<td><strong>'.$langs->trans("Total").'</strong></td>';
|
|
print '<td class="right"><strong>'.price($totalPurchased, 0, $langs, 1, 2, 2, $conf->currency).'</strong></td>';
|
|
print '<td class="right"><strong>'.price($totalInvoiced, 0, $langs, 1, 2, 2, $conf->currency).'</strong></td>';
|
|
print '<td class="right '.$colorProfit.'"><strong>'.price($totalGrossProfit, 0, $langs, 1, 2, 2, $conf->currency).'</strong></td>';
|
|
print '<td class="right '.$colorMargin.'"><strong>'.number_format($totalMargin, 1, ',', '.').' %</strong></td>';
|
|
print '<td class="right buchaltung-lastyear '.$colorMarginLast.'"><strong>'.number_format($totalMarginLast, 1, ',', '.').' %</strong></td>';
|
|
print '</tr>';
|
|
|
|
print '</table>';
|
|
print '</div>';
|
|
|
|
// Productivity rating
|
|
$rating = getProductivityRating($totalMargin, $langs);
|
|
print '<div class="buchaltung-rating-detail '.$rating['class'].'">';
|
|
print '<h3>'.$langs->trans("ProductivityRating").': '.$rating['text'].'</h3>';
|
|
print '<p>'.$rating['description'].'</p>';
|
|
print '<div class="rating-scale">';
|
|
print '<span class="rating-critical">'.$langs->trans("Critical").'</span>';
|
|
print '<span class="rating-low">'.$langs->trans("Low").'</span>';
|
|
print '<span class="rating-average">'.$langs->trans("Average").'</span>';
|
|
print '<span class="rating-good">'.$langs->trans("Good").'</span>';
|
|
print '<span class="rating-excellent">'.$langs->trans("Excellent").'</span>';
|
|
print '</div>';
|
|
print '<div class="rating-marker" style="left: '.min(100, max(0, ($totalMargin + 50) / 1.5)).'%"></div>';
|
|
print '</div>';
|
|
|
|
// Info box
|
|
print '<div class="buchaltung-info-box">';
|
|
print '<strong>'.$langs->trans("Note").':</strong> '.$langs->trans("OnlyCustomerMaterialsNote");
|
|
print '</div>';
|
|
|
|
// Charts JavaScript
|
|
print '<script>
|
|
document.addEventListener("DOMContentLoaded", function() {
|
|
// Margin trend chart
|
|
var ctx1 = document.getElementById("marginChart").getContext("2d");
|
|
new Chart(ctx1, {
|
|
type: "line",
|
|
data: {
|
|
labels: '.json_encode($labels).',
|
|
datasets: [{
|
|
label: "'.$selectedYear.' '.$langs->trans("ProfitMargin").'",
|
|
data: '.json_encode($marginData).',
|
|
borderColor: "rgba(40, 167, 69, 1)",
|
|
backgroundColor: "rgba(40, 167, 69, 0.1)",
|
|
fill: true,
|
|
tension: 0.3,
|
|
borderWidth: 3,
|
|
pointRadius: 5,
|
|
pointBackgroundColor: '.json_encode($marginColors).'
|
|
}, {
|
|
label: "'.($selectedYear-1).' '.$langs->trans("ProfitMargin").'",
|
|
data: '.json_encode($marginLastData).',
|
|
borderColor: "rgba(108, 117, 125, 0.7)",
|
|
backgroundColor: "transparent",
|
|
borderDash: [5, 5],
|
|
fill: false,
|
|
tension: 0.3,
|
|
borderWidth: 2
|
|
}]
|
|
},
|
|
options: {
|
|
responsive: true,
|
|
maintainAspectRatio: false,
|
|
plugins: {
|
|
legend: { display: true, position: "bottom" },
|
|
tooltip: {
|
|
callbacks: {
|
|
label: function(context) {
|
|
return context.dataset.label + ": " + context.raw + "%";
|
|
}
|
|
}
|
|
}
|
|
},
|
|
scales: {
|
|
y: {
|
|
beginAtZero: true,
|
|
ticks: {
|
|
callback: function(value) { return value + "%"; }
|
|
},
|
|
grid: {
|
|
color: function(context) {
|
|
return context.tick.value === 0 ? "rgba(0,0,0,0.5)" : "rgba(0,0,0,0.1)";
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
});
|
|
|
|
// Comparison chart
|
|
var ctx2 = document.getElementById("comparisonChart").getContext("2d");
|
|
new Chart(ctx2, {
|
|
type: "bar",
|
|
data: {
|
|
labels: '.json_encode($labels).',
|
|
datasets: [{
|
|
label: "'.$langs->trans("MaterialsPurchasedForCustomers").'",
|
|
data: '.json_encode($purchasedData).',
|
|
backgroundColor: "rgba(220, 53, 69, 0.6)"
|
|
}, {
|
|
label: "'.$langs->trans("MaterialsServicesInvoiced").'",
|
|
data: '.json_encode($invoicedData).',
|
|
backgroundColor: "rgba(0, 123, 255, 0.6)"
|
|
}]
|
|
},
|
|
options: {
|
|
responsive: true,
|
|
maintainAspectRatio: false,
|
|
plugins: {
|
|
legend: { display: true, position: "bottom" },
|
|
tooltip: {
|
|
callbacks: {
|
|
label: function(context) {
|
|
return context.dataset.label + ": " + new Intl.NumberFormat("de-DE", {style: "currency", currency: "EUR"}).format(context.raw);
|
|
}
|
|
}
|
|
}
|
|
},
|
|
scales: {
|
|
y: { beginAtZero: true }
|
|
}
|
|
}
|
|
});
|
|
});
|
|
</script>';
|
|
|
|
llxFooter();
|
|
$db->close();
|
|
|
|
/**
|
|
* Get profitability data by month
|
|
*/
|
|
function getProfitabilityByMonth($db, $year)
|
|
{
|
|
global $conf;
|
|
|
|
$result = array(
|
|
'purchased' => array_fill(1, 12, 0),
|
|
'invoiced' => array_fill(1, 12, 0),
|
|
);
|
|
|
|
// Materials purchased FOR CUSTOMERS only
|
|
$sql = "SELECT MONTH(f.datef) as month, SUM(fd.total_ht) as total";
|
|
$sql .= " FROM ".MAIN_DB_PREFIX."facture_fourn as f";
|
|
$sql .= " INNER JOIN ".MAIN_DB_PREFIX."facture_fourn_det as fd ON fd.fk_facture_fourn = f.rowid";
|
|
$sql .= " INNER JOIN ".MAIN_DB_PREFIX."product as p ON p.rowid = fd.fk_product";
|
|
$sql .= " WHERE f.fk_statut > 0 AND f.entity = ".((int) $conf->entity);
|
|
$sql .= " AND YEAR(f.datef) = ".((int) $year);
|
|
$sql .= " AND p.fk_product_type = 0";
|
|
$sql .= " AND (p.tobuy = 1 OR p.tosell = 1)";
|
|
$sql .= " GROUP BY MONTH(f.datef)";
|
|
|
|
$resql = $db->query($sql);
|
|
if ($resql) {
|
|
while ($obj = $db->fetch_object($resql)) {
|
|
$result['purchased'][$obj->month] = (float) $obj->total;
|
|
}
|
|
$db->free($resql);
|
|
}
|
|
|
|
// All invoiced to customers
|
|
$sql = "SELECT MONTH(f.datef) as month, SUM(fd.total_ht) as total";
|
|
$sql .= " FROM ".MAIN_DB_PREFIX."facture as f";
|
|
$sql .= " INNER JOIN ".MAIN_DB_PREFIX."facturedet as fd ON fd.fk_facture = f.rowid";
|
|
$sql .= " WHERE f.fk_statut > 0 AND f.entity = ".((int) $conf->entity);
|
|
$sql .= " AND YEAR(f.datef) = ".((int) $year);
|
|
$sql .= " GROUP BY MONTH(f.datef)";
|
|
|
|
$resql = $db->query($sql);
|
|
if ($resql) {
|
|
while ($obj = $db->fetch_object($resql)) {
|
|
$result['invoiced'][$obj->month] = (float) $obj->total;
|
|
}
|
|
$db->free($resql);
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Get productivity rating
|
|
*/
|
|
function getProductivityRating($marginPercent, $langs)
|
|
{
|
|
if ($marginPercent >= 100) {
|
|
return array(
|
|
'class' => 'rating-excellent',
|
|
'text' => $langs->trans("Excellent"),
|
|
'description' => $langs->trans("RatingExcellentDesc"),
|
|
);
|
|
} elseif ($marginPercent >= 50) {
|
|
return array(
|
|
'class' => 'rating-good',
|
|
'text' => $langs->trans("Good"),
|
|
'description' => $langs->trans("RatingGoodDesc"),
|
|
);
|
|
} elseif ($marginPercent >= 20) {
|
|
return array(
|
|
'class' => 'rating-average',
|
|
'text' => $langs->trans("Average"),
|
|
'description' => $langs->trans("RatingAverageDesc"),
|
|
);
|
|
} elseif ($marginPercent >= 0) {
|
|
return array(
|
|
'class' => 'rating-low',
|
|
'text' => $langs->trans("Low"),
|
|
'description' => $langs->trans("RatingLowDesc"),
|
|
);
|
|
} else {
|
|
return array(
|
|
'class' => 'rating-critical',
|
|
'text' => $langs->trans("Critical"),
|
|
'description' => $langs->trans("RatingCriticalDesc"),
|
|
);
|
|
}
|
|
}
|