[Sommaire] [Imprimer]

   1  #!/usr/bin/php
   2  <?PHP
   3  /* Copyright (C) 2005 Rodolphe Quiedeville <rodolphe@quiedeville.org>
   4   *
   5   * This program is free software; you can redistribute it and/or modify
   6   * it under the terms of the GNU General Public License as published by
   7   * the Free Software Foundation; either version 2 of the License, or
   8   * (at your option) any later version.
   9   *
  10   * This program is distributed in the hope that it will be useful,
  11   * but WITHOUT ANY WARRANTY; without even the implied warranty of
  12   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13   * GNU General Public License for more details.
  14   *
  15   * You should have received a copy of the GNU General Public License
  16   * along with this program; if not, write to the Free Software
  17   * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
  18   *
  19   * $Id: graph-solde.php,v 1.12 2005/11/13 17:09:00 rodolphe Exp $
  20   * $Source: /cvsroot/dolibarr/dolibarr/scripts/banque/graph-solde.php,v $
  21   */
  22  
  23  /**
  24          \file       scripts/banque/graph-solde.php
  25          \ingroup    banque
  26          \brief      Script de génération des images des soldes des comptes
  27  */
  28  
  29  
  30  // Test si mode batch
  31  $sapi_type = php_sapi_name();
  32  if (substr($sapi_type, 0, 3) == 'cgi') {
  33      echo "Erreur: Vous utilisez l'interpreteur PHP pour le mode CGI. Pour executer mailing-send.php en ligne de commande, vous devez utiliser l'interpreteur PHP pour le mode CLI.\n";
  34      exit;
  35  }
  36  
  37  
  38  // Recupere root dolibarr
  39  $path=eregi_replace('graph-solde.php','',$_SERVER["PHP_SELF"]);
  40  
  41  require_once ($path."../../htdocs/master.inc.php");
  42  
  43  // Vérifie que chemin vers JPGRAHP est connu et defini $jpgraph
  44  if (! defined('JPGRAPH_DIR') && ! defined('JPGRAPH_PATH'))
  45  {
  46      print 'Erreur: Définissez la constante JPGRAPH_PATH sur la valeur du répertoire contenant JPGraph';
  47      exit;
  48  }    
  49  if (! defined('JPGRAPH_DIR')) define('JPGRAPH_DIR', JPGRAPH_PATH);
  50  $jpgraphdir=JPGRAPH_DIR;
  51  if (! eregi('[\\\/]$',$jpgraphdir)) $jpgraphdir.='/';
  52  
  53  
  54  include_once($jpgraphdir."jpgraph.php");
  55  include_once($jpgraphdir."jpgraph_line.php");
  56  include_once($jpgraphdir."jpgraph_bar.php");
  57  include_once($jpgraphdir."jpgraph_pie.php");
  58  include_once($jpgraphdir."jpgraph_error.php");
  59  include_once($jpgraphdir."jpgraph_canvas.php");
  60  
  61  $error = 0;
  62  
  63  // Initialise opt, tableau des parametres
  64  if (function_exists("getopt"))
  65  {
  66      // getopt existe sur ce PHP
  67      $opt = getopt("m:y:");
  68  }
  69  else
  70  {
  71      // getopt n'existe sur ce PHP
  72      $opt=array('m'=>$argv[1]);
  73  }    
  74  
  75  if (!$conf->banque->dir_images)
  76  {
  77    $conf->banque->dir_images = DOL_DATA_ROOT."/graph/banque/";
  78  }
  79  
  80  // Crée répertoire accueil
  81  create_exdir($conf->banque->dir_images);
  82  
  83  
  84  $datetime = time();
  85  
  86  if ($opt['m'] > 0)
  87  {
  88    $month = $opt['m'];
  89  }
  90  else
  91  {
  92    $month = strftime("%m", $datetime);
  93  }
  94  $year = strftime("%Y", $datetime);
  95  
  96  if ($month == 1)
  97  {
  98    $monthprev = "12";
  99    $yearprev = $year - 1;
 100  }
 101  else
 102  {
 103    $monthprev = substr("00".($month - 1), -2) ;
 104    $yearprev = $year ;
 105  }
 106  
 107  if ($month == 12)
 108  {
 109    $monthnext = "01";
 110    $yearnext = $year + 1;
 111  }
 112  else
 113  {
 114    $monthnext = substr("00".($month + 1), -2) ;
 115  }
 116  
 117  $sql = "SELECT distinct(fk_account)";
 118  $sql .= " FROM ".MAIN_DB_PREFIX."bank";
 119  $sql .= " WHERE fk_account IS NOT NULL";
 120  
 121  $resql = $db->query($sql);
 122  
 123  $accounts = array();
 124  
 125  if ($resql)
 126  {
 127    $num = $db->num_rows($resql);
 128    $i = 0;
 129  
 130    while ($i < $num)
 131      {
 132        $row = $db->fetch_row($resql);
 133        array_push($accounts, $row[0]);
 134        $i++;
 135      }
 136  
 137  }
 138    
 139  $account = 1; 
 140  
 141  foreach ($accounts as $account)
 142  {
 143    $labels = array();
 144    $datas = array();
 145    $amounts = array();
 146    
 147    $sql = "SELECT sum(amount)";
 148    $sql .= " FROM ".MAIN_DB_PREFIX."bank";
 149    $sql .= " WHERE fk_account = ".$account;
 150    $sql .= " AND datev < '".$year."-".$month."-01';";
 151    
 152    $resql = $db->query($sql);
 153    if ($resql)
 154      {
 155        $row = $db->fetch_row($resql);
 156        $solde = $row[0];
 157      }
 158    else
 159      {
 160        print $sql ;
 161      }
 162  
 163  
 164    $sql = "SELECT date_format(datev,'%Y%m%d'), sum(amount)";
 165    $sql .= " FROM ".MAIN_DB_PREFIX."bank";
 166    $sql .= " WHERE fk_account = ".$account;
 167    $sql .= " AND date_format(datev,'%Y%m') = '".$year.$month."'";
 168    $sql .= " GROUP BY date_format(datev,'%Y%m%d');";
 169  
 170    $resql = $db->query($sql);
 171    
 172    $amounts = array();
 173    
 174    if ($resql)
 175      {
 176        $num = $db->num_rows($resql);
 177        $i = 0;
 178        
 179        while ($i < $num)
 180      {
 181        $row = $db->fetch_row($resql);
 182        $amounts[$row[0]] = $row[1];
 183        $i++;
 184      }      
 185      }
 186    else
 187      {
 188        print $sql ;
 189      }
 190    
 191    $subtotal = 0;
 192    
 193    $day = mktime(1,1,1,$month,1,$year);
 194    
 195    $xmonth = substr("00".strftime("%m",$day), -2);
 196    $i = 0;
 197    while ($xmonth == $month)
 198      {
 199        //print strftime ("%e %d %m %y",$day)."\n";
 200        
 201        $subtotal = $subtotal + $amounts[strftime("%Y%m%d",$day)];
 202  
 203        if ($day > time())
 204      {      
 205        $datas[$i] = 0;
 206      }
 207        else
 208      {
 209        $datas[$i] = $solde + $subtotal;
 210      }
 211  
 212        $labels[$i] = strftime("%d",$day);
 213        
 214        $day += 86400;
 215        $xmonth = substr("00".strftime("%m",$day), -2);
 216        $i++;
 217      }
 218      
 219    $width = 750;
 220    $height = 350;
 221    
 222    $graph = new Graph($width, $height,"auto");    
 223    $graph->SetScale("textlin");
 224    
 225    $graph->yaxis->scale->SetGrace(2);
 226    $graph->SetFrame(1);
 227    $graph->img->SetMargin(60,20,20,35);
 228    
 229    $b2plot = new BarPlot($datas);
 230    
 231    $b2plot->SetColor("blue");
 232    //$b2plot->SetWeight(2);
 233    
 234    $graph->title->Set("Solde $month $year");
 235    
 236    $graph->xaxis->SetTickLabels($labels);   
 237    //$graph->xaxis->title->Set(strftime("%d/%m/%y %H:%M:%S", time()));
 238    
 239    $graph->Add($b2plot);
 240    $graph->img->SetImgFormat("png");
 241    
 242    $file= $conf->banque->dir_images."/solde.$account.$year.$month.png";
 243    
 244    $graph->Stroke($file);
 245  }
 246  /*
 247   * Graph annuels
 248   *
 249   */
 250  foreach ($accounts as $account)
 251  {
 252    $labels = array();
 253    $datas = array();
 254    $amounts = array();
 255    
 256    $sql = "SELECT sum(amount)";
 257    $sql .= " FROM ".MAIN_DB_PREFIX."bank";
 258    $sql .= " WHERE fk_account = ".$account;
 259    $sql .= " AND datev < '".$year."-01-01';";
 260    
 261    $resql = $db->query($sql);
 262    if ($resql)
 263      {
 264        $row = $db->fetch_row($resql);
 265        $solde = $row[0];
 266      }
 267    else
 268      {
 269        print $sql ;
 270      }
 271  
 272    $sql = "SELECT date_format(datev,'%Y%m%d'), sum(amount)";
 273    $sql .= " FROM ".MAIN_DB_PREFIX."bank";
 274    $sql .= " WHERE fk_account = ".$account;
 275    $sql .= " AND date_format(datev,'%Y') = '".$year."'";
 276    $sql .= " GROUP BY date_format(datev,'%Y%m%d');";
 277  
 278    $resql = $db->query($sql);
 279    
 280    $amounts = array();
 281    
 282    if ($resql)
 283      {
 284        $num = $db->num_rows($resql);
 285        $i = 0;
 286        
 287        while ($i < $num)
 288      {
 289        $row = $db->fetch_row($resql);
 290        $amounts[$row[0]] = $row[1];
 291        $i++;
 292      }      
 293      }
 294    else
 295      {
 296        dolibarr_syslog("graph-solde.php Error");
 297      }
 298    
 299    $subtotal = 0;
 300    
 301    $day = mktime(1,1,1,1,1,$year);
 302    
 303    $xyear = strftime("%Y",$day);
 304    $i = 0;
 305    while ($xyear == $year)
 306      {
 307        //print strftime ("%e %d %m %y",$day)."\n";
 308        
 309        $subtotal = $subtotal + $amounts[strftime("%Y%m%d",$day)];
 310  
 311        if ($day > time())
 312      {      
 313        $datas[$i] = 'x'; // Valeur spéciale permettant de ne pas tracer le graph
 314      }
 315        else
 316      {
 317        $datas[$i] = $solde + $subtotal;
 318      }
 319  
 320        if (strftime("%d",$day) == 1)
 321      {
 322        $labels[$i] = strftime("%d",$day);
 323      }
 324        else
 325      {
 326  
 327      }
 328        
 329        $day += 86400;
 330        $xyear = strftime("%Y",$day);
 331        $i++;
 332      }
 333      
 334    $width = 750;
 335    $height = 350;
 336    
 337    $graph = new Graph($width, $height,"auto");    
 338    $graph->SetScale("textlin");
 339    
 340    $graph->yaxis->scale->SetGrace(2);
 341    $graph->SetFrame(1);
 342    $graph->img->SetMargin(60,20,20,35);
 343    
 344    $b2plot = new LinePlot($datas);
 345    
 346    $b2plot->SetColor("blue");
 347    //$b2plot->SetWeight(2);
 348    
 349    $graph->title->Set("Solde $year");
 350    
 351    $graph->xaxis->SetTickLabels($labels);
 352  
 353    $graph->xaxis->Hide();
 354    //$graph->xaxis->HideTicks(); 
 355  
 356  
 357    //$graph->xaxis->title->Set(strftime("%d/%m/%y %H:%M:%S", time()));
 358    
 359    $graph->Add($b2plot);
 360    $graph->img->SetImgFormat("png");
 361    
 362    $file= $conf->banque->dir_images."/solde.$account.$year.png";
 363    
 364    $graph->Stroke($file);
 365  }
 366  
 367  /*
 368   * Graph annuels
 369   *
 370   */
 371  foreach ($accounts as $account)
 372  {
 373    $labels = array();
 374    $datas = array();
 375    $amounts = array();
 376    
 377    $sql = "SELECT min(".$db->pdate("datev")."),max(".$db->pdate("datev").")";
 378    $sql .= " FROM ".MAIN_DB_PREFIX."bank";
 379    $sql .= " WHERE fk_account = ".$account;
 380  
 381    $resql = $db->query($sql);
 382  
 383    if ($resql)
 384      {
 385        $num = $db->num_rows($resql);
 386        $row = $db->fetch_row($resql);
 387        $min = $row[0];
 388        $max = $row[1];
 389      }
 390    else
 391      {
 392        dolibarr_syslog("graph-solde.php Error");
 393      }
 394  
 395  
 396    $sql = "SELECT date_format(datev,'%Y%m%d'), sum(amount)";
 397    $sql .= " FROM ".MAIN_DB_PREFIX."bank";
 398    $sql .= " WHERE fk_account = ".$account;
 399    $sql .= " GROUP BY date_format(datev,'%Y%m%d');";
 400  
 401    $resql = $db->query($sql);
 402    
 403    $amounts = array();
 404    
 405    if ($resql)
 406      {
 407        $num = $db->num_rows($resql);
 408        $i = 0;
 409        
 410        while ($i < $num)
 411      {
 412        $row = $db->fetch_row($resql);
 413        $amounts[$row[0]] = $row[1];
 414        $i++;
 415      }      
 416      }
 417    else
 418      {
 419        dolibarr_syslog("graph-solde.php Error");
 420      }
 421    
 422    $subtotal = 0;
 423    
 424    $day = $min;
 425    
 426    $i = 0;
 427    while ($day <= $max)
 428      {
 429        //print strftime ("%e %d %m %y",$day)."\n";
 430        
 431        $subtotal = $subtotal + $amounts[strftime("%Y%m%d",$day)];
 432  
 433        $datas[$i] = $solde + $subtotal;
 434  
 435        $labels[$i] = strftime("%d",$day);
 436  
 437        $day += 86400;
 438        $i++;
 439      }
 440  
 441    if (sizeof($amounts) > 3)
 442      {    
 443        $width = 750;
 444        $height = 350;
 445  
 446        $graph = new Graph($width, $height,"auto");    
 447        $graph->SetScale("textlin");
 448    
 449        $graph->yaxis->scale->SetGrace(2);
 450        $graph->SetFrame(1);
 451        $graph->img->SetMargin(60,20,20,35);
 452        
 453        $b2plot = new LinePlot($datas);
 454        
 455        $b2plot->SetColor("blue");
 456        
 457        $graph->title->Set("Solde");
 458        
 459        $graph->xaxis->SetTickLabels($labels);
 460        
 461        $graph->xaxis->Hide();
 462        
 463        $graph->Add($b2plot);
 464        $graph->img->SetImgFormat("png");
 465        
 466        $file= $conf->banque->dir_images."/solde.$account.png";
 467        
 468        $graph->Stroke($file);
 469      }
 470  }
 471  
 472  foreach ($accounts as $account)
 473  {
 474    $labels = array();
 475    $datas = array();
 476    $amounts = array();
 477    $credits = array();
 478    $debits = array();
 479  
 480    $sql = "SELECT date_format(datev,'%m'), sum(amount)";
 481    $sql .= " FROM ".MAIN_DB_PREFIX."bank";
 482    $sql .= " WHERE fk_account = ".$account;
 483    $sql .= " AND date_format(datev,'%Y') = '".$year."'";
 484    $sql .= " AND amount > 0";
 485    $sql .= " GROUP BY date_format(datev,'%m');";
 486  
 487    $resql = $db->query($sql);
 488    
 489    $amounts = array();
 490    
 491    if ($resql)
 492      {
 493        $num = $db->num_rows($resql);
 494        $i = 0;
 495        
 496        while ($i < $num)
 497      {
 498        $row = $db->fetch_row($resql);
 499        $credits[$row[0]] = $row[1];
 500        $i++;
 501      }      
 502      }
 503    else
 504      {
 505        print $sql ;
 506      }
 507  
 508    $sql = "SELECT date_format(datev,'%m'), sum(amount)";
 509    $sql .= " FROM ".MAIN_DB_PREFIX."bank";
 510    $sql .= " WHERE fk_account = ".$account;
 511    $sql .= " AND date_format(datev,'%Y') = '".$year."'";
 512    $sql .= " AND amount < 0";
 513    $sql .= " GROUP BY date_format(datev,'%m');";
 514  
 515    $resql = $db->query($sql);  
 516    if ($resql)
 517      {
 518        while ($row = $db->fetch_row($resql))
 519      {
 520        $debits[$row[0]] = abs($row[1]);
 521      }      
 522      }
 523    else
 524      {
 525        print $sql ;
 526      }
 527  
 528    for ($i = 0 ; $i < 12 ; $i++)
 529      {
 530        $data_credit[$i] = $credits[substr("0".($i+1),-2)];
 531        $data_debit[$i] = $debits[substr("0".($i+1),-2)];
 532        $labels[$i] = $i+1;      
 533      }
 534  
 535    $width = 750;
 536    $height = 350;
 537    
 538    $graph = new Graph($width, $height,"auto");    
 539    $graph->SetScale("textlin");
 540    
 541    $graph->yaxis->scale->SetGrace(2);
 542    //$graph->SetFrame(1);
 543    $graph->img->SetMargin(60,20,20,35);
 544    
 545    $bsplot = new BarPlot($data_debit);  
 546    $bsplot->SetColor("red");
 547    
 548    $beplot = new BarPlot($data_credit);  
 549    $beplot->SetColor("green");
 550  
 551    $bg = new GroupBarPlot(array($beplot, $bsplot));
 552  
 553    $graph->title->Set("Mouvements $year");
 554    
 555    $graph->xaxis->SetTickLabels($labels);   
 556    
 557    $graph->Add($bg);
 558    $graph->img->SetImgFormat("png");
 559    
 560    $file= DOL_DATA_ROOT."/graph/banque/mouvement.$account.$year.png";
 561    
 562    $graph->Stroke($file);
 563  }
 564  
 565  ?>