[Sommaire] [Imprimer]

   1  <?PHP
   2  /* Copyright (C) 2005 Rodolphe Quiedeville <rodolphe@quiedeville.org>
   3   *
   4   * This program is free software; you can redistribute it and/or modify
   5   * it under the terms of the GNU General Public License as published by
   6   * the Free Software Foundation; either version 2 of the License, or
   7   * (at your option) any later version.
   8   *
   9   * This program is distributed in the hope that it will be useful,
  10   * but WITHOUT ANY WARRANTY; without even the implied warranty of
  11   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12   * GNU General Public License for more details.
  13   *
  14   * You should have received a copy of the GNU General Public License
  15   * along with this program; if not, write to the Free Software
  16   * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
  17   *
  18   * $Id: facturation-analyse.php,v 1.3 2005/07/27 13:30:59 rodolphe Exp $
  19   * $Source: /cvsroot/dolibarr/dolibarr/htdocs/telephonie/script/facturation-analyse.php,v $
  20   *
  21   *
  22   * Script de facturation
  23   * Verification des factures négatives
  24   *
  25   */
  26  
  27  /**
  28     \file       htdocs/telephonie/script/facturation-analyse.php
  29     \ingroup    telephonie
  30     \brief      Analyse de la facturation
  31     \version    $Revision: 1.3 $
  32  */
  33  
  34  
  35  require  ("../../master.inc.php");
  36  
  37  $opt = getopt("l:c:");
  38  
  39  $limit = $opt['l'];
  40  $optcontrat = $opt['c'];
  41  
  42  /*
  43   * analyse ratio cout fournisseur
  44   *
  45   */
  46  
  47  $datetime = time();
  48  $date = strftime("%d%h%Y%Hh%Mm%S",$datetime);
  49  $month = strftime("%m", $datetime);
  50  $year = strftime("%Y", $datetime);
  51  
  52  if ($month == 1)
  53  {
  54    $month = "12";
  55    $year = $year - 1;
  56  }
  57  else
  58  {
  59    $month = substr("00".($month - 1), -2) ;
  60  }
  61  
  62  
  63  $sql = "SELECT cd.fk_fournisseur, sum(cd.fourn_montant), sum(cd.cout_vente)";
  64  $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_facture as tf";
  65  $sql .= " ,    ".MAIN_DB_PREFIX."telephonie_communications_details as cd";
  66  
  67  $sql .= " WHERE tf.date = '".$year."-".$month."-01'";
  68  $sql .= " AND tf.rowid = cd.fk_telephonie_facture";
  69  $sql .= " GROUP BY cd.fk_fournisseur";
  70  
  71  $re2sql = $db->query($sql) ;
  72  
  73  if ( $re2sql )
  74  {
  75    $nu2m = $db->num_rows($re2sql);      
  76    $j = 0;
  77    while ($j < $nu2m)
  78      {
  79        $row = $db->fetch_row($re2sql);
  80  
  81        $sqli = "INSERT INTO ".MAIN_DB_PREFIX."telephonie_analyse_fournisseur";
  82        $sqli.= " (fk_fournisseur,mois,achat,vente)";
  83        $sqli .= " VALUES (".$row[0].",'".$year.$month."',".$row[1].",".$row[2].")";
  84  
  85        $resqli = $db->query($sqli) ;
  86  
  87        if (! $resqli )
  88      {
  89        print $db->error($resqli);
  90      }
  91  
  92        $j++;
  93      }
  94  }
  95  else
  96  {
  97    print $db->error();
  98  }
  99  
 100  
 101  /*
 102   * Partie 2
 103   *
 104   */
 105  
 106  require_once (DOL_DOCUMENT_ROOT."/includes/php_writeexcel/class.writeexcel_workbook.inc.php");
 107  require_once (DOL_DOCUMENT_ROOT."/includes/php_writeexcel/class.writeexcel_worksheet.inc.php");
 108  
 109  $error = 0;
 110  $dir = "/tmp/";
 111  
 112  $fname = $dir . "facturation-analyse.xls";
 113  
 114  dolibarr_syslog("Open $fname");
 115      
 116  $workbook = &new writeexcel_workbook($fname);
 117  
 118  $page = &$workbook->addworksheet("Analyse");
 119  
 120  $fnb =& $workbook->addformat();
 121  $fnb->set_align('vcenter');
 122  $fnb->set_align('right');
 123  
 124  $fns =& $workbook->addformat();
 125  $fns->set_align('vcenter');
 126  $fns->set_align('left');
 127  
 128  $fnc =& $workbook->addformat();
 129  $fnc->set_align('vcenter');
 130  $fnc->set_align('center');
 131  
 132  $fn =& $workbook->addformat();
 133  $fn->set_align('vcenter');
 134  
 135  $page->set_column(0,0,10); // A
 136  $page->set_column(1,4,16); // A
 137  
 138  $clients = array();
 139  
 140  $page->write(1, 0,  "Date", $fnc);
 141  $page->write(1, 1,  "Gain", $fnc);
 142  $page->write(1, 2,  "CA iBreizh", $fnc);
 143  $page->write(1, 3,  "Coût fournisseurs", $fnc);
 144  
 145  
 146  
 147  $sql = "SELECT sum(f.fourn_montant) as fourn_montant, sum(f.cout_vente) as cout_vente, f.date";
 148  $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_facture as f";
 149  $sql .= " ,    ".MAIN_DB_PREFIX."telephonie_societe_ligne as l";
 150  $sql .= " ,    ".MAIN_DB_PREFIX."telephonie_contrat as c";
 151  
 152  $sql .= " WHERE f.fk_facture IS NOT NULL";
 153  $sql .= " AND f.isfacturable = 'oui'"; 
 154  $sql .= " AND f.fk_ligne = l.rowid ";
 155  $sql .= " AND l.fk_contrat = c.rowid";  
 156  $sql .= " GROUP BY f.date DESC";
 157  
 158  $re2sql = $db->query($sql) ;
 159  
 160  if ( $re2sql )
 161  {
 162    $nu2m = $db->num_rows($re2sql);      
 163    $j = 0;
 164    $k=2;
 165    while ($j < $nu2m)
 166      {
 167        $obj = $db->fetch_object($re2sql);
 168        
 169        $page->write_string($k, 0,  $obj->date, $fns);
 170  
 171        $ki = $k+1;
 172        $page->write($k, 1,  "=C$ki-D$ki", $fn);
 173  
 174        $page->write($k, 2,  $obj->cout_vente, $fn);
 175        $page->write($k, 3,  $obj->fourn_montant, $fn);
 176  
 177        
 178        $k++;
 179        $j++;
 180      }
 181  }
 182  else
 183  {
 184    print $db->error();
 185  }
 186  /*
 187   *
 188   *
 189   */
 190  $workbook->close();
 191  $db->close();
 192  ?>