*
* 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/ust_detail.php
* \ingroup buchaltungswidget
* \brief Detail page for VAT (Umsatzsteuer) 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';
require_once DOL_DOCUMENT_ROOT.'/core/class/html.formfile.class.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');
}
$viewMode = GETPOST('view', 'alpha');
if (empty($viewMode)) {
$viewMode = 'quarterly';
}
$currentYear = date('Y');
$years = range($currentYear - 5, $currentYear + 1);
/*
* Actions
*/
// None for now
/*
* View
*/
$form = new Form($db);
$title = $langs->trans("UStUebersicht");
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 and view mode
print '
';
// Get data
$vatData = getVatData($db, $selectedYear, $viewMode);
$lastYearData = getVatData($db, $selectedYear - 1, $viewMode);
// Main chart
print '';
print '
';
print '
';
print '';
print '
';
print '
';
print '
';
print '';
// Data table
print '';
print '
';
// Header
print '';
print '| '.$langs->trans("Period").' | ';
print ''.$langs->trans("VATCollected").' | ';
print ''.$langs->trans("VATPaid").' | ';
print ''.$langs->trans("VATBalance").' | ';
print ''.$selectedYear - 1 .' '.$langs->trans("VATBalance").' | ';
print '
';
$totalCollected = 0;
$totalPaid = 0;
$totalCollectedLast = 0;
$totalPaidLast = 0;
$labels = array();
$collectedData = array();
$paidData = array();
$balanceData = array();
$balanceColors = array();
$lastBalanceData = array();
foreach ($vatData['periods'] as $period => $data) {
$collected = $data['collected'];
$paid = $data['paid'];
$balance = $collected - $paid;
$lastCollected = isset($lastYearData['periods'][$period]) ? $lastYearData['periods'][$period]['collected'] : 0;
$lastPaid = isset($lastYearData['periods'][$period]) ? $lastYearData['periods'][$period]['paid'] : 0;
$lastBalance = $lastCollected - $lastPaid;
$totalCollected += $collected;
$totalPaid += $paid;
$totalCollectedLast += $lastCollected;
$totalPaidLast += $lastPaid;
$colorClass = $balance > 0 ? 'buchaltung-negative' : ($balance < 0 ? 'buchaltung-positive' : '');
$colorClassLast = $lastBalance > 0 ? 'buchaltung-negative' : ($lastBalance < 0 ? 'buchaltung-positive' : '');
print '';
print '| '.$period.' | ';
print ''.price($collected, 0, $langs, 1, 2, 2, $conf->currency).' | ';
print ''.price($paid, 0, $langs, 1, 2, 2, $conf->currency).' | ';
print ''.price($balance, 0, $langs, 1, 2, 2, $conf->currency).' | ';
print ''.price($lastBalance, 0, $langs, 1, 2, 2, $conf->currency).' | ';
print '
';
// Chart data
$labels[] = $period;
$collectedData[] = round($collected, 2);
$paidData[] = round($paid, 2);
$balanceData[] = round($balance, 2);
$balanceColors[] = $balance > 0 ? 'rgba(220, 53, 69, 0.7)' : 'rgba(40, 167, 69, 0.7)';
$lastBalanceData[] = round($lastBalance, 2);
}
// Total row
$totalBalance = $totalCollected - $totalPaid;
$totalLastBalance = $totalCollectedLast - $totalPaidLast;
$colorClass = $totalBalance > 0 ? 'buchaltung-negative' : 'buchaltung-positive';
$colorClassLast = $totalLastBalance > 0 ? 'buchaltung-negative' : 'buchaltung-positive';
print '';
print '| '.$langs->trans("Total").' | ';
print ''.price($totalCollected, 0, $langs, 1, 2, 2, $conf->currency).' | ';
print ''.price($totalPaid, 0, $langs, 1, 2, 2, $conf->currency).' | ';
print ''.price($totalBalance, 0, $langs, 1, 2, 2, $conf->currency).' | ';
print ''.price($totalLastBalance, 0, $langs, 1, 2, 2, $conf->currency).' | ';
print '
';
print '
';
print '
';
// Legend
print '';
print ''.$langs->trans("VATToPayLegend").'';
print ' | ';
print ''.$langs->trans("VATRefundLegend").'';
print '
';
// Chart JavaScript
print '';
llxFooter();
$db->close();
/**
* Get VAT data for a year
*/
function getVatData($db, $year, $mode = 'quarterly')
{
global $conf;
$result = array('periods' => array());
if ($mode == 'monthly') {
$months = 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');
foreach ($months as $m => $name) {
$result['periods'][$name] = array('collected' => 0, 'paid' => 0);
}
$groupBy = "MONTH(f.datef)";
$periodField = "month";
} else {
for ($q = 1; $q <= 4; $q++) {
$result['periods']['Q'.$q] = array('collected' => 0, 'paid' => 0);
}
$groupBy = "QUARTER(f.datef)";
$periodField = "quarter";
}
// VAT collected
$sql = "SELECT ".$groupBy." as period, SUM(fd.total_tva) as tva_amount";
$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 ".$groupBy;
$resql = $db->query($sql);
if ($resql) {
while ($obj = $db->fetch_object($resql)) {
$key = ($mode == 'monthly') ? array_keys($result['periods'])[$obj->period - 1] : 'Q'.$obj->period;
if (isset($result['periods'][$key])) {
$result['periods'][$key]['collected'] = (float) $obj->tva_amount;
}
}
$db->free($resql);
}
// VAT paid
$sql = "SELECT ".$groupBy." as period, SUM(fd.total_tva) as tva_amount";
$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 .= " WHERE f.fk_statut > 0 AND f.entity = ".((int) $conf->entity);
$sql .= " AND YEAR(f.datef) = ".((int) $year);
$sql .= " GROUP BY ".$groupBy;
$resql = $db->query($sql);
if ($resql) {
while ($obj = $db->fetch_object($resql)) {
$key = ($mode == 'monthly') ? array_keys($result['periods'])[$obj->period - 1] : 'Q'.$obj->period;
if (isset($result['periods'][$key])) {
$result['periods'][$key]['paid'] = (float) $obj->tva_amount;
}
}
$db->free($resql);
}
return $result;
}