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