dolibarr 21.0.0-alpha
annuel.php
Go to the documentation of this file.
1<?php
2/* Copyright (C) 2005 Rodolphe Quiedeville <rodolphe@quiedeville.org>
3 * Copyright (C) 2004-2017 Laurent Destailleur <eldy@users.sourceforge.net>
4 * Copyright (C) 2005-2012 Regis Houssin <regis.houssin@inodbox.com>
5 * Copyright (C) 2013-2023 Charlene BENKE <charlene@patas-monkey.com>
6 * Copyright (C) 2024 MDW <mdeweerd@users.noreply.github.com>
7 * Copyright (C) 2024 Frédéric France <frederic.france@free.fr>
8 *
9 * This program is free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 3 of the License, or
12 * (at your option) any later version.
13 *
14 * This program is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 * GNU General Public License for more details.
18 *
19 * You should have received a copy of the GNU General Public License
20 * along with this program. If not, see <https://www.gnu.org/licenses/>.
21 */
22
29// Load Dolibarr environment
30require '../../main.inc.php';
31require_once DOL_DOCUMENT_ROOT.'/core/lib/bank.lib.php';
32require_once DOL_DOCUMENT_ROOT.'/compta/bank/class/account.class.php';
33require_once DOL_DOCUMENT_ROOT.'/core/class/dolgraph.class.php';
34
35// Load translation files required by the page
36$langs->loadLangs(array('banks', 'categories'));
37
38$WIDTH = DolGraph::getDefaultGraphSizeForStats('width', '380'); // Large for one graph in a smarpthone.
39$HEIGHT = DolGraph::getDefaultGraphSizeForStats('height', '160');
40
41$id = GETPOST('account') ? GETPOST('account', 'alpha') : GETPOST('id');
42$ref = GETPOST('ref');
43
44// Initialize a technical object to manage hooks of page. Note that conf->hooks_modules contains an array of hook context
45$hookmanager->initHooks(array('bankannualreport', 'globalcard'));
46
47// Security check
48$fieldvalue = (!empty($id) ? $id : (!empty($ref) ? $ref : ''));
49$fieldtype = (!empty($ref) ? 'ref' : 'rowid');
50if ($user->socid) {
51 $socid = $user->socid;
52}
53$result = restrictedArea($user, 'banque', $fieldvalue, 'bank_account&bank_account', '', '', $fieldtype);
54
55$year_start = GETPOST('year_start');
56//$year_current = strftime("%Y", time());
57$year_current = (int) dol_print_date(time(), "%Y");
58if (!$year_start) {
59 $year_start = $year_current - 2;
60 $year_end = $year_current;
61} else {
62 $year_end = $year_start + 2;
63}
64
65
66/*
67 * View
68 */
69
70$form = new Form($db);
71
72// Get account information
73$object = new Account($db);
74if ($id > 0 && !preg_match('/,/', $id)) { // if for a particular account and not a list
75 $result = $object->fetch($id);
76 $id = $object->id;
77}
78if (!empty($ref)) {
79 $result = $object->fetch(0, $ref);
80 $id = $object->id;
81}
82
83$annee = '';
84$totentrees = array();
85$totsorties = array();
86
87$title = $object->ref.' - '.$langs->trans("IOMonthlyReporting");
88$helpurl = "";
89llxHeader('', $title, $helpurl);
90
91// Ce rapport de tresorerie est base sur llx_bank (car doit inclure les transactions sans facture)
92// plutot que sur llx_paiement + llx_paiementfourn
93
94$sql = "SELECT SUM(b.amount)";
95$sql .= ", date_format(b.dateo,'%Y-%m') as dm";
96$sql .= " FROM ".MAIN_DB_PREFIX."bank as b";
97$sql .= ", ".MAIN_DB_PREFIX."bank_account as ba";
98$sql .= " WHERE b.fk_account = ba.rowid";
99$sql .= " AND ba.entity IN (".getEntity('bank_account').")";
100$sql .= " AND b.amount >= 0";
101if (!empty($id)) {
102 $sql .= " AND b.fk_account IN (".$db->sanitize($db->escape($id)).")";
103}
104$sql .= " GROUP BY dm";
105
106$resql = $db->query($sql);
107$encaiss = array();
108$decaiss = array();
109if ($resql) {
110 $num = $db->num_rows($resql);
111 $i = 0;
112 while ($i < $num) {
113 $row = $db->fetch_row($resql);
114 $encaiss[$row[1]] = $row[0];
115 $i++;
116 }
117} else {
118 dol_print_error($db);
119}
120
121$sql = "SELECT SUM(b.amount)";
122$sql .= ", date_format(b.dateo,'%Y-%m') as dm";
123$sql .= " FROM ".MAIN_DB_PREFIX."bank as b";
124$sql .= ", ".MAIN_DB_PREFIX."bank_account as ba";
125$sql .= " WHERE b.fk_account = ba.rowid";
126$sql .= " AND ba.entity IN (".getEntity('bank_account').")";
127$sql .= " AND b.amount <= 0";
128if (!empty($id)) {
129 $sql .= " AND b.fk_account IN (".$db->sanitize($db->escape($id)).")";
130}
131$sql .= " GROUP BY dm";
132
133$resql = $db->query($sql);
134if ($resql) {
135 $num = $db->num_rows($resql);
136 $i = 0;
137 while ($i < $num) {
138 $row = $db->fetch_row($resql);
139 $decaiss[$row[1]] = -$row[0];
140 $i++;
141 }
142} else {
143 dol_print_error($db);
144}
145
146
147// Onglets
148$head = bank_prepare_head($object);
149print dol_get_fiche_head($head, 'annual', $langs->trans("FinancialAccount"), 0, 'account');
150
151$title = $langs->trans("FinancialAccount")." : ".$object->label;
152$link = ($year_start ? '<a href="'.$_SERVER["PHP_SELF"].'?account='.$object->id.'&year_start='.($year_start - 1).'">'.img_previous('', 'class="valignbottom"')."</a> ".$langs->trans("Year").' <a href="'.$_SERVER["PHP_SELF"].'?account='.$object->id.'&year_start='.($year_start + 1).'">'.img_next('', 'class="valignbottom"').'</a>' : '');
153
154$linkback = '<a href="'.DOL_URL_ROOT.'/compta/bank/list.php?restore_lastsearch_values=1">'.$langs->trans("BackToList").'</a>';
155
156$morehtmlref = '';
157
158if (!empty($id)) {
159 if (!preg_match('/,/', $id)) {
160 dol_banner_tab($object, 'ref', $linkback, 1, 'ref', 'ref', $morehtmlref, '', 0, '', '', 1);
161 } else {
162 $bankaccount = new Account($db);
163 $listid = explode(',', $id);
164 foreach ($listid as $key => $aId) {
165 $bankaccount->fetch($aId);
166 $bankaccount->label = $bankaccount->ref;
167 print $bankaccount->getNomUrl(1);
168 if ($key < (count($listid) - 1)) {
169 print ', ';
170 }
171 }
172 }
173} else {
174 print $langs->trans("AllAccounts");
175}
176
177print dol_get_fiche_end();
178
179
180// Affiche tableau
181print load_fiche_titre('', $link, '');
182
183print '<div class="div-table-responsive">'; // You can use div-table-responsive-no-min if you don't need reserved height for your table
184print '<table class="noborder centpercent">';
185
186print '<tr class="liste_titre"><td class="liste_titre">'.$langs->trans("Month").'</td>';
187for ($annee = $year_start; $annee <= $year_end; $annee++) {
188 print '<td align="center" width="20%" colspan="2" class="liste_titre borderrightlight">'.$annee.'</td>';
189}
190print '</tr>';
191
192print '<tr class="liste_titre">';
193print '<td class="liste_titre">&nbsp;</td>';
194for ($annee = $year_start; $annee <= $year_end; $annee++) {
195 print '<td class="liste_titre" align="center">'.$langs->trans("Debit").'</td><td class="liste_titre" align="center">'.$langs->trans("Credit").'</td>';
196}
197print '</tr>';
198
199for ($annee = $year_start; $annee <= $year_end; $annee++) {
200 $totsorties[$annee] = 0;
201 $totentrees[$annee] = 0;
202}
203
204for ($mois = 1; $mois < 13; $mois++) {
205 print '<tr class="oddeven">';
206 print "<td>".dol_print_date(dol_mktime(1, 1, 1, $mois, 1, 2000), "%B")."</td>";
207
208 for ($annee = $year_start; $annee <= $year_end; $annee++) {
209 $case = sprintf("%04d-%02d", $annee, $mois);
210
211 print '<td class="right" width="10%">&nbsp;';
212 if (isset($decaiss[$case]) && $decaiss[$case] > 0) {
213 print price($decaiss[$case]);
214 $totsorties[$annee] += $decaiss[$case];
215 }
216 print "</td>";
217
218 print '<td class="right borderrightlight" width="10%">&nbsp;';
219 if (isset($encaiss[$case]) && $encaiss[$case] > 0) {
220 print price($encaiss[$case]);
221 $totentrees[$annee] += $encaiss[$case];
222 }
223 print "</td>";
224 }
225 print '</tr>';
226}
227
228// Total debit-credit
229print '<tr class="liste_total"><td><b>'.$langs->trans("Total")."</b></td>";
230for ($annee = $year_start; $annee <= $year_end; $annee++) {
231 print '<td class="right nowraponall"><b>'. (isset($totsorties[$annee]) ? price($totsorties[$annee]) : '') .'</b></td>';
232 print '<td class="right nowraponall"><b>'. (isset($totentrees[$annee]) ? price($totentrees[$annee]) : '') .'</b></td>';
233}
234print "</tr>\n";
235
236print "</table>";
237print "</div>";
238
239print '<br>';
240
241
242// Current balance
243$balance = 0;
244
245$sql = "SELECT SUM(b.amount) as total";
246$sql .= " FROM ".MAIN_DB_PREFIX."bank as b";
247$sql .= ", ".MAIN_DB_PREFIX."bank_account as ba";
248$sql .= " WHERE b.fk_account = ba.rowid";
249$sql .= " AND ba.entity IN (".getEntity('bank_account').")";
250if (!empty($id)) {
251 $sql .= " AND b.fk_account IN (".$db->sanitize($db->escape($id)).")";
252}
253
254$resql = $db->query($sql);
255if ($resql) {
256 $obj = $db->fetch_object($resql);
257 if ($obj) {
258 $balance = $obj->total;
259 }
260} else {
261 dol_print_error($db);
262}
263
264print '<table class="noborder centpercent">';
265
266$nbcol = '';
267print '<tr class="liste_total"><td><b>'.$langs->trans("CurrentBalance")."</b></td>";
268print '<td colspan="'.($nbcol).'" class="right">'.price($balance).'</td>';
269print "</tr>\n";
270
271print "</table>";
272
273// BUILDING GRAPHICS
274
275$year = $year_end;
276
277$result = dol_mkdir($conf->bank->dir_temp);
278if ($result < 0) {
279 $langs->load("errors");
280 $error++;
281 setEventMessages($langs->trans("ErrorFailedToCreateDir"), null, 'errors');
282} else {
283 // Calcul de $min et $max
284 $sql = "SELECT MIN(b.datev) as min, MAX(b.datev) as max";
285 $sql .= " FROM ".MAIN_DB_PREFIX."bank as b";
286 $sql .= ", ".MAIN_DB_PREFIX."bank_account as ba";
287 $sql .= " WHERE b.fk_account = ba.rowid";
288 $sql .= " AND ba.entity IN (".getEntity('bank_account').")";
289 if ($id && GETPOST("option") != 'all') {
290 $sql .= " AND b.fk_account IN (".$db->sanitize($id).")";
291 }
292
293 $resql = $db->query($sql);
294 if ($resql) {
295 $num = $db->num_rows($resql);
296 $obj = $db->fetch_object($resql);
297 $min = $db->jdate($obj->min);
298 $max = $db->jdate($obj->max);
299 } else {
300 dol_print_error($db);
301 }
302 $log = "graph.php: min=".$min." max=".$max;
303 dol_syslog($log);
304
305 // CRED PART
306 // Chargement du tableau des années
307 $tblyear = array();
308 '@phan-var-force array<array<string,int|float>> $tblyear';
309 $tblyear[0] = array();
310 $tblyear[1] = array();
311 $tblyear[2] = array();
312
313 for ($annee = 0; $annee < 3; $annee++) {
314 $sql = "SELECT date_format(b.datev,'%m')";
315 $sql .= ", SUM(b.amount)";
316 $sql .= " FROM ".MAIN_DB_PREFIX."bank as b";
317 $sql .= ", ".MAIN_DB_PREFIX."bank_account as ba";
318 $sql .= " WHERE b.fk_account = ba.rowid";
319 $sql .= " AND ba.entity IN (".getEntity('bank_account').")";
320 $sql .= " AND b.datev >= '".($year - $annee)."-01-01 00:00:00'";
321 $sql .= " AND b.datev <= '".($year - $annee)."-12-31 23:59:59'";
322 $sql .= " AND b.amount > 0";
323 if ($id && GETPOST("option") != 'all') {
324 $sql .= " AND b.fk_account IN (".$db->sanitize($id).")";
325 }
326 $sql .= " GROUP BY date_format(b.datev,'%m');";
327
328 $resql = $db->query($sql);
329 if ($resql) {
330 $num = $db->num_rows($resql);
331 $i = 0;
332 while ($i < $num) {
333 $row = $db->fetch_row($resql);
334 $tblyear[$annee][$row[0]] = $row[1];
335 $i++;
336 }
337 $db->free($resql);
338 } else {
339 dol_print_error($db);
340 }
341 }
342 // Chargement de labels et data_xxx pour tableau 4 Movements
343 $labels = array();
344 $data_year_0 = array();
345 $data_year_1 = array();
346 $data_year_2 = array();
347 $datamin = array();
348
349 for ($i = 0; $i < 12; $i++) {
350 $data_year_0[$i] = isset($tblyear[0][substr("0".($i + 1), -2)]) ? $tblyear[0][substr("0".($i + 1), -2)] : 0;
351 $data_year_1[$i] = isset($tblyear[1][substr("0".($i + 1), -2)]) ? $tblyear[1][substr("0".($i + 1), -2)] : 0;
352 $data_year_2[$i] = isset($tblyear[2][substr("0".($i + 1), -2)]) ? $tblyear[2][substr("0".($i + 1), -2)] : 0;
353 $labels[$i] = $langs->transnoentitiesnoconv("MonthVeryShort".sprintf("%02d", $i + 1));
354 $datamin[$i] = 0;
355 }
356
357 // Fabrication tableau 4b
358 $file = $conf->bank->dir_temp."/credmovement".$id."-".$year.".png";
359 $fileurl = DOL_URL_ROOT.'/viewimage.php?modulepart=banque_temp&file='."/credmovement".$id."-".$year.".png";
360 $title = $langs->transnoentities("Credit").' - '.$langs->transnoentities("Year").': '.($year - 2).' - '.($year - 1)." - ".$year;
361 $graph_datas = array();
362 for ($i = 0; $i < 12; $i++) {
363 $graph_datas[$i] = array($labels[$i], $data_year_0[$i], $data_year_1[$i], $data_year_2[$i]);
364 }
365
366 $px1 = new DolGraph();
367 $px1->SetData($graph_datas);
368 $px1->SetLegend(array(($year), ($year - 1), ($year - 2)));
369 $px1->SetLegendWidthMin(180);
370 $px1->SetMaxValue($px1->GetCeilMaxValue() < 0 ? 0 : $px1->GetCeilMaxValue());
371 $px1->SetMinValue($px1->GetFloorMinValue() > 0 ? 0 : $px1->GetFloorMinValue());
372 $px1->SetTitle($title);
373 $px1->SetWidth($WIDTH);
374 $px1->SetHeight($HEIGHT);
375 $px1->SetType(array('line', 'line', 'line'));
376 $px1->SetShading(3);
377 $px1->setBgColor('onglet');
378 $px1->setBgColorGrid(array(255, 255, 255));
379 $px1->SetHorizTickIncrement(1);
380 $px1->draw($file, $fileurl);
381
382 $show1 = $px1->show();
383
384 unset($graph_datas);
385 unset($px1);
386 unset($tblyear[0]);
387 unset($tblyear[1]);
388 unset($tblyear[2]);
389
390 // DEDBT PART
391 // Chargement du tableau des années
392 $tblyear[0] = array();
393 $tblyear[1] = array();
394 $tblyear[2] = array();
395
396 for ($annee = 0; $annee < 3; $annee++) {
397 $sql = "SELECT date_format(b.datev,'%m')";
398 $sql .= ", SUM(b.amount)";
399 $sql .= " FROM ".MAIN_DB_PREFIX."bank as b";
400 $sql .= ", ".MAIN_DB_PREFIX."bank_account as ba";
401 $sql .= " WHERE b.fk_account = ba.rowid";
402 $sql .= " AND ba.entity IN (".getEntity('bank_account').")";
403 $sql .= " AND b.datev >= '".($year - $annee)."-01-01 00:00:00'";
404 $sql .= " AND b.datev <= '".($year - $annee)."-12-31 23:59:59'";
405 $sql .= " AND b.amount < 0";
406 if ($id && GETPOST("option") != 'all') {
407 $sql .= " AND b.fk_account IN (".$db->sanitize($id).")";
408 }
409 $sql .= " GROUP BY date_format(b.datev,'%m');";
410
411 $resql = $db->query($sql);
412 if ($resql) {
413 $num = $db->num_rows($resql);
414 $i = 0;
415 while ($i < $num) {
416 $row = $db->fetch_row($resql);
417 $tblyear[$annee][$row[0]] = abs($row[1]);
418 $i++;
419 }
420 $db->free($resql);
421 } else {
422 dol_print_error($db);
423 }
424 }
425 // Chargement de labels et data_xxx pour tableau 4 Movements
426 $labels = array();
427 $data_year_0 = array();
428 $data_year_1 = array();
429 $data_year_2 = array();
430
431 for ($i = 0; $i < 12; $i++) {
432 $data_year_0[$i] = isset($tblyear[0][substr("0".($i + 1), -2)]) ? $tblyear[0][substr("0".($i + 1), -2)] : 0;
433 $data_year_1[$i] = isset($tblyear[1][substr("0".($i + 1), -2)]) ? $tblyear[1][substr("0".($i + 1), -2)] : 0;
434 $data_year_2[$i] = isset($tblyear[2][substr("0".($i + 1), -2)]) ? $tblyear[2][substr("0".($i + 1), -2)] : 0;
435 $labels[$i] = $langs->transnoentitiesnoconv("MonthVeryShort".sprintf("%02d", $i + 1));
436 $datamin[$i] = 0;
437 }
438
439 $file = $conf->bank->dir_temp."/debmovement".$id."-".$year.".png";
440 $fileurl = DOL_URL_ROOT.'/viewimage.php?modulepart=banque_temp&file='."/debmovement".$id."-".$year.".png";
441 $title = $langs->transnoentities("Debit").' - '.$langs->transnoentities("Year").': '.($year - 2).' - '.($year - 1)." - ".$year;
442 $graph_datas = array();
443 for ($i = 0; $i < 12; $i++) {
444 $graph_datas[$i] = array($labels[$i], $data_year_0[$i], $data_year_1[$i], $data_year_2[$i]);
445 }
446
447 $px2 = new DolGraph();
448 $px2->SetData($graph_datas);
449 $px2->SetLegend(array(($year), ($year - 1), ($year - 2)));
450 $px2->SetLegendWidthMin(180);
451 $px2->SetMaxValue($px2->GetCeilMaxValue() < 0 ? 0 : $px2->GetCeilMaxValue());
452 $px2->SetMinValue($px2->GetFloorMinValue() > 0 ? 0 : $px2->GetFloorMinValue());
453 $px2->SetTitle($title);
454 $px2->SetWidth($WIDTH);
455 $px2->SetHeight($HEIGHT);
456 $px2->SetType(array('line', 'line', 'line'));
457 $px2->SetShading(3);
458 $px2->setBgColor('onglet');
459 $px2->setBgColorGrid(array(255, 255, 255));
460 $px2->SetHorizTickIncrement(1);
461 $px2->draw($file, $fileurl);
462
463 $show2 = $px2->show();
464
465 unset($graph_datas);
466 unset($px2);
467 unset($tblyear[0]);
468 unset($tblyear[1]);
469 unset($tblyear[2]);
470
471 print '<div class="fichecenter"><div class="fichehalfleft"><div align="center">'; // do not use class="center" here, it will have no effect for the js graph inside.
472 print $show1;
473 print '</div></div><div class="fichehalfright"><div align="center">'; // do not use class="center" here, it will have no effect for the js graph inside.
474 print $show2;
475 print '</div></div></div>';
476 print '<div class="clearboth"></div>';
477}
478
479
480print "\n</div><br>\n";
481
482// End of page
483llxFooter();
484$db->close();
$id
Definition account.php:39
if( $user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Definition card.php:58
if(!defined('NOREQUIRESOC')) if(!defined( 'NOREQUIRETRAN')) if(!defined('NOTOKENRENEWAL')) if(!defined( 'NOREQUIREMENU')) if(!defined('NOREQUIREHTML')) if(!defined( 'NOREQUIREAJAX')) llxHeader($head='', $title='', $help_url='', $target='', $disablejs=0, $disablehead=0, $arrayofjs='', $arrayofcss='', $morequerystring='', $morecssonbody='', $replacemainareaby='', $disablenofollow=0, $disablenoindex=0)
Empty header.
Definition wrapper.php:70
bank_prepare_head(Account $object)
Prepare array with list of tabs.
Definition bank.lib.php:39
Class to manage bank accounts.
Class to build graphs.
static getDefaultGraphSizeForStats($direction, $defaultsize='')
getDefaultGraphSizeForStats
Class to manage generation of HTML components Only common components must be here.
llxFooter()
Footer empty.
Definition document.php:107
dol_mktime($hour, $minute, $second, $month, $day, $year, $gm='auto', $check=1)
Return a timestamp date built from detailed information (by default a local PHP server timestamp) Rep...
load_fiche_titre($title, $morehtmlright='', $picto='generic', $pictoisfullpath=0, $id='', $morecssontable='', $morehtmlcenter='')
Load a title with picto.
setEventMessages($mesg, $mesgs, $style='mesgs', $messagekey='', $noduplicate=0, $attop=0)
Set event messages in dol_events session object.
dol_get_fiche_head($links=array(), $active='', $title='', $notab=0, $picto='', $pictoisfullpath=0, $morehtmlright='', $morecss='', $limittoshow=0, $moretabssuffix='', $dragdropfile=0)
Show tabs of a record.
dol_get_fiche_end($notab=0)
Return tab footer of a card.
price($amount, $form=0, $outlangs='', $trunc=1, $rounding=-1, $forcerounding=-1, $currency_code='')
Function to format a value into an amount for visual output Function used into PDF and HTML pages.
dol_print_date($time, $format='', $tzoutput='auto', $outputlangs=null, $encodetooutput=false)
Output date in a string format according to outputlangs (or langs if not defined).
img_previous($titlealt='default', $moreatt='')
Show previous logo.
GETPOST($paramname, $check='alphanohtml', $method=0, $filter=null, $options=null, $noreplace=0)
Return value of a param into GET or POST supervariable.
dol_print_error($db=null, $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
img_next($titlealt='default', $moreatt='')
Show next logo.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
dol_mkdir($dir, $dataroot='', $newmask='')
Creation of a directory (this can create recursive subdir)
restrictedArea(User $user, $features, $object=0, $tableandshare='', $feature2='', $dbt_keyfield='fk_soc', $dbt_select='rowid', $isdraft=0, $mode=0)
Check permissions of a user to show a page and an object.