dolibarr  7.0.0-beta
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@capnetworks.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 $langs->load("banks");
33 $langs->load("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 $var=true;
204 for ($mois = 1 ; $mois < 13 ; $mois++)
205 {
206 
207  print '<tr class="oddeven">';
208  print "<td>".dol_print_date(dol_mktime(1,1,1,$mois,1,2000),"%B")."</td>";
209  for ($annee = $year_start ; $annee <= $year_end ; $annee++)
210  {
211  $case = sprintf("%04s-%02s",$annee,$mois);
212 
213  print '<td align="right" width="10%">&nbsp;';
214  if ($decaiss[$case]>0)
215  {
216  print price($decaiss[$case]);
217  $totsorties[$annee]+=$decaiss[$case];
218  }
219  print "</td>";
220 
221  print '<td align="right" class="borderrightlight" width="10%">&nbsp;';
222  if ($encaiss[$case]>0)
223  {
224  print price($encaiss[$case]);
225  $totentrees[$annee]+=$encaiss[$case];
226  }
227  print "</td>";
228  }
229  print '</tr>';
230 }
231 
232 // Total debit-credit
233 print '<tr class="liste_total"><td><b>'.$langs->trans("Total")."</b></td>";
234 for ($annee = $year_start ; $annee <= $year_end ; $annee++)
235 {
236  print '<td align="right"><b>'.price($totsorties[$annee]).'</b></td><td align="right"><b>'.price($totentrees[$annee]).'</b></td>';
237 }
238 print "</tr>\n";
239 
240 print "</table>";
241 print "</div>";
242 
243 print '<br>';
244 
245 
246 // Current balance
247 $balance=0;
248 
249 $sql = "SELECT SUM(b.amount) as total";
250 $sql.= " FROM ".MAIN_DB_PREFIX."bank as b";
251 $sql.= ", ".MAIN_DB_PREFIX."bank_account as ba";
252 $sql.= " WHERE b.fk_account = ba.rowid";
253 $sql.= " AND ba.entity IN (".getEntity('bank_account').")";
254 if (! empty($id))
255  $sql.= " AND b.fk_account IN (".$db->escape($id).")";
256 
257 $resql=$db->query($sql);
258 if ($resql)
259 {
260  $obj = $db->fetch_object($resql);
261  if ($obj) $balance=$obj->total;
262 }
263 else {
264  dol_print_error($db);
265 }
266 
267 print '<table class="noborder" width="100%">';
268 
269 print '<tr class="liste_total"><td><b>'.$langs->trans("CurrentBalance")."</b></td>";
270 print '<td colspan="'.($nbcol).'" align="right">'.price($balance).'</td>';
271 print "</tr>\n";
272 
273 print "</table>";
274 
275 // BUILDING GRAPHICS
276 
277 $year = $year_end;
278 
279 $result=dol_mkdir($conf->bank->dir_temp);
280 if ($result < 0)
281 {
282  $langs->load("errors");
283  $error++;
284  setEventMessages($langs->trans("ErrorFailedToCreateDir"), null, 'errors');
285 }
286 else
287 {
288  // Calcul de $min et $max
289  $sql = "SELECT MIN(b.datev) as min, MAX(b.datev) as max";
290  $sql.= " FROM ".MAIN_DB_PREFIX."bank as b";
291  $sql.= ", ".MAIN_DB_PREFIX."bank_account as ba";
292  $sql.= " WHERE b.fk_account = ba.rowid";
293  $sql.= " AND ba.entity IN (".getEntity('bank_account').")";
294  if ($id && $_GET["option"]!='all') $sql.= " AND b.fk_account IN (".$id.")";
295 
296  $resql = $db->query($sql);
297  if ($resql)
298  {
299  $num = $db->num_rows($resql);
300  $obj = $db->fetch_object($resql);
301  $min = $db->jdate($obj->min);
302  $max = $db->jdate($obj->max);
303  }
304  else
305  {
306  dol_print_error($db);
307  }
308  $log="graph.php: min=".$min." max=".$max;
309  dol_syslog($log);
310 
311 // CRED PART
312  // Chargement du tableau des années
313  $tblyear[0] = array();
314  $tblyear[1] = array();
315  $tblyear[2] = array();
316 
317  for ($annee=0;$annee<3;$annee++)
318  {
319  $sql = "SELECT date_format(b.datev,'%m')";
320  $sql.= ", SUM(b.amount)";
321  $sql.= " FROM ".MAIN_DB_PREFIX."bank as b";
322  $sql.= ", ".MAIN_DB_PREFIX."bank_account as ba";
323  $sql.= " WHERE b.fk_account = ba.rowid";
324  $sql.= " AND ba.entity IN (".getEntity('bank_account').")";
325  $sql.= " AND b.datev >= '".($year-$annee)."-01-01 00:00:00'";
326  $sql.= " AND b.datev <= '".($year-$annee)."-12-31 23:59:59'";
327  $sql.= " AND b.amount > 0";
328  if ($id && $_GET["option"]!='all') $sql.= " AND b.fk_account IN (".$id.")";
329  $sql .= " GROUP BY date_format(b.datev,'%m');";
330 
331  $resql = $db->query($sql);
332  if ($resql)
333  {
334  $num = $db->num_rows($resql);
335  $i = 0;
336  while ($i < $num)
337  {
338  $row = $db->fetch_row($resql);
339  $tblyear[$annee][$row[0]] = $row[1];
340  $i++;
341  }
342  $db->free($resql);
343 
344  }
345  else
346  {
347  dol_print_error($db);
348  }
349  }
350  // Chargement de labels et data_xxx pour tableau 4 Mouvements
351  $labels = array();
352  $data_year_0 = array();
353  $data_year_1 = array();
354  $data_year_2 = array();
355 
356  for ($i = 0 ; $i < 12 ; $i++)
357  {
358  $data_year_0[$i] = isset($tblyear[0][substr("0".($i+1),-2)]) ? $tblyear[0][substr("0".($i+1),-2)] : 0;
359  $data_year_1[$i] = isset($tblyear[1][substr("0".($i+1),-2)]) ? $tblyear[1][substr("0".($i+1),-2)] : 0;
360  $data_year_2[$i] = isset($tblyear[2][substr("0".($i+1),-2)]) ? $tblyear[2][substr("0".($i+1),-2)] : 0;
361  $labels[$i] = $langs->transnoentitiesnoconv("MonthVeryShort".sprintf("%02d", $i+1));
362  $datamin[$i] = 0;
363  }
364 
365  // Fabrication tableau 4b
366  $file= $conf->bank->dir_temp."/credmovement".$id."-".$year.".png";
367  $fileurl=DOL_URL_ROOT.'/viewimage.php?modulepart=banque_temp&file='."/credmovement".$id."-".$year.".png";
368  $title=$langs->transnoentities("Credit").' - '.$langs->transnoentities("Year").': '.($year-2).' - '.($year-1)." - ".$year;
369  $graph_datas=array();
370  for($i=0;$i<12;$i++)
371  {
372  $graph_datas[$i]=array($labels[$i], $data_year_0[$i], $data_year_1[$i], $data_year_2[$i]);
373  }
374 
375  $px1 = new DolGraph();
376  $px1->SetData($graph_datas);
377  $px1->SetLegend(array(($year),($year-1),($year-2)));
378  $px1->SetLegendWidthMin(180);
379  $px1->SetMaxValue($px1->GetCeilMaxValue()<0?0:$px1->GetCeilMaxValue());
380  $px1->SetMinValue($px1->GetFloorMinValue()>0?0:$px1->GetFloorMinValue());
381  $px1->SetTitle($title);
382  $px1->SetWidth($WIDTH);
383  $px1->SetHeight($HEIGHT);
384  $px1->SetType(array('line','line','line'));
385  $px1->SetShading(3);
386  $px1->setBgColor('onglet');
387  $px1->setBgColorGrid(array(255,255,255));
388  $px1->SetHorizTickIncrement(1);
389  $px1->SetPrecisionY(0);
390  $px1->draw($file,$fileurl);
391 
392  $show1 = $px1->show();
393 
394  unset($graph_datas);
395  unset($px1);
396  unset($tblyear[0]);
397  unset($tblyear[1]);
398  unset($tblyear[2]);
399 
400 // DEDBT PART
401  // Chargement du tableau des années
402  $tblyear[0] = array();
403  $tblyear[1] = array();
404  $tblyear[2] = array();
405 
406  for ($annee=0;$annee<3;$annee++)
407  {
408  $sql = "SELECT date_format(b.datev,'%m')";
409  $sql.= ", SUM(b.amount)";
410  $sql.= " FROM ".MAIN_DB_PREFIX."bank as b";
411  $sql.= ", ".MAIN_DB_PREFIX."bank_account as ba";
412  $sql.= " WHERE b.fk_account = ba.rowid";
413  $sql.= " AND ba.entity IN (".getEntity('bank_account').")";
414  $sql.= " AND b.datev >= '".($year-$annee)."-01-01 00:00:00'";
415  $sql.= " AND b.datev <= '".($year-$annee)."-12-31 23:59:59'";
416  $sql.= " AND b.amount < 0";
417  if ($id && $_GET["option"]!='all') $sql.= " AND b.fk_account IN (".$id.")";
418  $sql .= " GROUP BY date_format(b.datev,'%m');";
419 
420  $resql = $db->query($sql);
421  if ($resql)
422  {
423  $num = $db->num_rows($resql);
424  $i = 0;
425  while ($i < $num)
426  {
427  $row = $db->fetch_row($resql);
428  $tblyear[$annee][$row[0]] = abs($row[1]);
429  $i++;
430  }
431  $db->free($resql);
432  }
433  else
434  {
435  dol_print_error($db);
436  }
437  }
438  // Chargement de labels et data_xxx pour tableau 4 Mouvements
439  $labels = array();
440  $data_year_0 = array();
441  $data_year_1 = array();
442  $data_year_2 = array();
443 
444  for ($i = 0 ; $i < 12 ; $i++)
445  {
446  $data_year_0[$i] = isset($tblyear[0][substr("0".($i+1),-2)]) ? $tblyear[0][substr("0".($i+1),-2)] : 0;
447  $data_year_1[$i] = isset($tblyear[1][substr("0".($i+1),-2)]) ? $tblyear[1][substr("0".($i+1),-2)] : 0;
448  $data_year_2[$i] = isset($tblyear[2][substr("0".($i+1),-2)]) ? $tblyear[2][substr("0".($i+1),-2)] : 0;
449  $labels[$i] = $langs->transnoentitiesnoconv("MonthVeryShort".sprintf("%02d", $i+1));
450  $datamin[$i] = 0;
451  }
452 
453  $file= $conf->bank->dir_temp."/debmovement".$id."-".$year.".png";
454  $fileurl= DOL_URL_ROOT.'/viewimage.php?modulepart=banque_temp&file='."/debmovement".$id."-".$year.".png";
455  $title=$langs->transnoentities("Debit").' - '.$langs->transnoentities("Year").': '.($year-2).' - '.($year-1)." - ".$year;
456  $graph_datas=array();
457  for($i=0;$i<12;$i++)
458  {
459  $graph_datas[$i]=array($labels[$i], $data_year_0[$i], $data_year_1[$i], $data_year_2[$i]);
460  }
461 
462  $px2 = new DolGraph();
463  $px2->SetData($graph_datas);
464  $px2->SetLegend(array(($year),($year-1),($year-2)));
465  $px2->SetLegendWidthMin(180);
466  $px2->SetMaxValue($px2->GetCeilMaxValue()<0?0:$px2->GetCeilMaxValue());
467  $px2->SetMinValue($px2->GetFloorMinValue()>0?0:$px2->GetFloorMinValue());
468  $px2->SetTitle($title);
469  $px2->SetWidth($WIDTH);
470  $px2->SetHeight($HEIGHT);
471  $px2->SetType(array('line','line','line'));
472  $px2->SetShading(3);
473  $px2->setBgColor('onglet');
474  $px2->setBgColorGrid(array(255,255,255));
475  $px2->SetHorizTickIncrement(1);
476  $px2->SetPrecisionY(0);
477  $px2->draw($file,$fileurl);
478 
479  $show2 = $px2->show();
480 
481  unset($graph_datas);
482  unset($px2);
483  unset($tblyear[0]);
484  unset($tblyear[1]);
485  unset($tblyear[2]);
486 
487  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.
488  print $show1;
489  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.
490  print $show2;
491  print '</div></div></div></div>';
492  print '<div style="clear:both"></div>';
493 }
494 
495 
496 print "\n</div><br>\n";
497 
498 llxFooter();
499 $db->close();
llxFooter()
Empty footer.
Definition: wrapper.php:58
setEventMessages($mesg, $mesgs, $style='mesgs')
Set event messages in dol_events session object.
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_fiche_head($links=array(), $active='0', $title='', $notab=0, $picto='', $pictoisfullpath=0, $morehtmlright='')
Show tab header of a card.
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.
img_previous($titlealt= 'default', $moreatt='')
Show previous logo.
img_next($titlealt= 'default', $moreatt='')
Show next logo.
dol_print_error($db='', $error='', $errors=null)
Affiche message erreur system avec toutes les informations pour faciliter le diagnostic et la remonte...
if(empty($reshook)) $form
View.
Definition: perms.php:103
Class to manage bank accounts.
GETPOST($paramname, $check='none', $method=0, $filter=NULL, $options=NULL, $noreplace=0)
Return value of a param into GET or POST supervariable.
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
if($_POST["cancel"]==$langs->trans("Cancel")&&!$id) if($action== 'setdatev'&&$user->rights->tax->charges->creer) if($action== 'add'&&$_POST["cancel"]<> $langs->trans("Cancel")) if($action== 'delete') $title
Actions.
Definition: card.php:183
dol_fiche_end($notab=0)
Show tab footer of a card.
load_fiche_titre($titre, $morehtmlright='', $picto='title_generic.png', $pictoisfullpath=0, $id=0, $morecssontable='', $morehtmlcenter='')
Load a title with picto.
llxHeader()
Empty header.
Definition: wrapper.php:46
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...
print
Draft customers invoices.
Definition: index.php:91
Class to build graphs.
dol_mkdir($dir, $dataroot='', $newmask=null)
Creation of a directory (this can create recursive subdir)
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:1013
restrictedArea($user, $features, $objectid=0, $tableandshare='', $feature2='', $dbt_keyfield='fk_soc', $dbt_select='rowid', $objcanvas=null)
Check permissions of a user to show a page and an object.