[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-alerte.php,v 1.2 2005/05/09 14:46:30 rodolphe Exp $
  19   * $Source: /cvsroot/dolibarr/dolibarr/htdocs/telephonie/script/facturation-alerte.php,v $
  20   *
  21   *
  22   * Script de facturation
  23   * Verification des factures négatives
  24   *
  25   */
  26  
  27  /**
  28     \file       htdocs/telephonie/script/facturation-emission.php
  29     \ingroup    telephonie
  30     \brief      Emission des factures
  31     \version    $Revision: 1.2 $
  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  require_once  (DOL_DOCUMENT_ROOT."/societe.class.php");
  43  require_once (DOL_DOCUMENT_ROOT."/lib/dolibarrmail.class.php");
  44  require_once (DOL_DOCUMENT_ROOT."/telephonie/lignetel.class.php");
  45  require_once (DOL_DOCUMENT_ROOT."/telephonie/facturetel.class.php");
  46  require_once (DOL_DOCUMENT_ROOT."/telephonie/telephonie.contrat.class.php");
  47  require_once (DOL_DOCUMENT_ROOT."/includes/php_writeexcel/class.writeexcel_workbook.inc.php");
  48  require_once (DOL_DOCUMENT_ROOT."/includes/php_writeexcel/class.writeexcel_worksheet.inc.php");
  49  
  50  
  51  $error = 0;
  52  
  53  /*
  54   * Lecture du batch
  55   *
  56   */
  57  
  58  $sql = "SELECT MAX(rowid) FROM ".MAIN_DB_PREFIX."telephonie_facturation_batch";
  59  
  60  $resql = $db->query($sql);
  61    
  62  if ( $resql )
  63  {
  64    $row = $db->fetch_row($resql);
  65  
  66    $batch_id = $row[0];
  67  
  68    $db->free($resql);
  69  }
  70  else
  71  {
  72    $error = 1;
  73    dolibarr_syslog("Erreur ".$error);
  74  }
  75  
  76  /*
  77   * Traitements
  78   *
  79   */
  80  
  81  
  82  
  83  $dir = "/tmp/";
  84  $error = 0;
  85  
  86  $fname = $dir . "alertes-factures.xls";
  87  
  88  dolibarr_syslog("Open $fname");
  89      
  90  $workbook = &new writeexcel_workbook($fname);
  91  
  92  $page = &$workbook->addworksheet("Pertes");
  93    
  94  $fnb =& $workbook->addformat();
  95  $fnb->set_align('vcenter');
  96  $fnb->set_align('right');
  97  
  98  $fns =& $workbook->addformat();
  99  $fns->set_align('vcenter');
 100  $fns->set_align('left');
 101  
 102  $fnc =& $workbook->addformat();
 103  $fnc->set_align('vcenter');
 104  $fnc->set_align('center');
 105  
 106  $fn =& $workbook->addformat();
 107  $fn->set_align('vcenter');
 108  
 109  $page->set_column(0,0,36); // A
 110  $page->set_column(1,1,16); // A
 111  $page->set_column(2,2,20); // A
 112  
 113  $page->write(1, 0,  "Client", $fns);
 114  $page->write(1, 1,  "Contrat", $fnc);
 115  $page->write(1, 2,  "Ligne", $fnc);
 116  $page->write(1, 3,  "Perte", $fn);
 117  $page->write(1, 4,  "Fournisseur", $fn);
 118  $page->write(1, 5,  "iBreizh", $fn);
 119  
 120  if (!$error)
 121  {
 122    $sql = "SELECT f.fourn_montant, f.cout_vente";
 123    $sql .= " , c.ref, s.nom, l.ligne as numero";
 124    $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_facture as f";
 125    $sql .= " ,    ".MAIN_DB_PREFIX."telephonie_societe_ligne as l";
 126    $sql .= " ,    ".MAIN_DB_PREFIX."telephonie_contrat as c";
 127    $sql .= " ,    ".MAIN_DB_PREFIX."societe as s";
 128  
 129    $sql .= " WHERE f.fk_facture IS NOT NULL";
 130    $sql .= " AND f.fk_batch = ".$batch_id;
 131    $sql .= " AND f.isfacturable = 'oui'"; 
 132    $sql .= " AND f.fk_ligne = l.rowid ";
 133    $sql .= " AND l.fk_contrat = c.rowid";  
 134    $sql .= " AND c.fk_client_comm = s.idp";
 135    $sql .= " AND f.fourn_montant > f.cout_vente";
 136    $sql .= " ORDER BY s.idp ASC, c.rowid ASC";
 137      
 138    $resql = $db->query($sql) ;
 139  
 140    if ( $resql )
 141      {
 142        $num = $db->num_rows($resql);      
 143        $i = 0;
 144        $j = 2;
 145        
 146        while ($i < $num)
 147      {
 148        $obj = $db->fetch_object($resql);
 149  
 150        if ($i == 0)
 151          {
 152            $oldc = $obj->ref;
 153            $total = 0;
 154          }
 155  
 156        if ($oldc <> $obj->ref)
 157          {
 158            $page->write($j, 3,  $total, $fnb);
 159            $total = 0;
 160            $j++;
 161            $oldc = $obj->ref;
 162          }
 163  
 164        $page->write_string($j, 0,  $obj->nom, $fns);
 165        $page->write_string($j, 1,  $obj->ref, $fnc);
 166        $page->write_string($j, 2,  $obj->numero, $fnc);
 167  
 168        $perte = ($obj->fourn_montant - $obj->cout_vente);
 169        $total += $perte;
 170  
 171        $page->write($j, 3,  $perte, $fn);
 172        $page->write($j, 4,  $obj->fourn_montant, $fn);
 173        $page->write($j, 5,  $obj->cout_vente, $fn);
 174  
 175  
 176        $j++;
 177        $i++;
 178  
 179        print $obj->nom . " " . $perte ."\n";
 180  
 181      }            
 182        $db->free();
 183      }
 184    else
 185      {
 186        $error = 2;
 187        dolibarr_syslog("Erreur $error ".$db->error());
 188      }
 189  }
 190  /*
 191   *
 192   *
 193   */
 194  
 195  if (!$error)
 196  {
 197    $sql = "SELECT distinct(s.idp), s.nom";
 198    $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_facture as f";
 199    $sql .= " ,    ".MAIN_DB_PREFIX."telephonie_societe_ligne as l";
 200    $sql .= " ,    ".MAIN_DB_PREFIX."telephonie_contrat as c";
 201    $sql .= " ,    ".MAIN_DB_PREFIX."societe as s";
 202  
 203    $sql .= " WHERE f.fk_facture IS NOT NULL";
 204    $sql .= " AND f.fk_batch = ".$batch_id;
 205    $sql .= " AND f.isfacturable = 'oui'"; 
 206    $sql .= " AND f.fk_ligne = l.rowid ";
 207    $sql .= " AND l.fk_contrat = c.rowid";  
 208    $sql .= " AND c.fk_client_comm = s.idp";
 209    $sql .= " AND f.fourn_montant > f.cout_vente";
 210    $sql .= " GROUP BY s.nom ASC";
 211  
 212    $resql = $db->query($sql) ;
 213  
 214    if ( $resql )
 215      {
 216        $num = $db->num_rows($resql);      
 217        $i = 0;
 218        
 219        while ($i < $num)
 220      {
 221        $row = $db->fetch_row($resql);
 222  
 223        $page = &$workbook->addworksheet($row[1]);
 224    
 225        $fnb =& $workbook->addformat();
 226        $fnb->set_align('vcenter');
 227        $fnb->set_align('right');
 228        
 229        $fns =& $workbook->addformat();
 230        $fns->set_align('vcenter');
 231        $fns->set_align('left');
 232        
 233        $fnc =& $workbook->addformat();
 234        $fnc->set_align('vcenter');
 235        $fnc->set_align('center');
 236        
 237        $fn =& $workbook->addformat();
 238        $fn->set_align('vcenter');
 239        
 240        $page->set_column(0,0,10); // A
 241        $page->set_column(1,1,16); // A
 242        $page->set_column(2,2,22); // A
 243        
 244        $clients = array();
 245        
 246        $page->write(1, 0,  "Date", $fnc);
 247        $page->write(1, 1,  "Contrat", $fnc);
 248        $page->write(1, 2,  "Ligne", $fnc);
 249        $page->write(1, 3,  "Perte", $fn);
 250        $page->write(1, 4,  "Fournisseur", $fn);
 251        $page->write(1, 5,  "iBreizh", $fn);
 252  
 253  
 254        $sql = "SELECT f.fourn_montant, f.cout_vente, f.date";
 255        $sql .= " , c.ref, s.nom, l.ligne as numero";
 256        $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_facture as f";
 257        $sql .= " ,    ".MAIN_DB_PREFIX."telephonie_societe_ligne as l";
 258        $sql .= " ,    ".MAIN_DB_PREFIX."telephonie_contrat as c";
 259        $sql .= " ,    ".MAIN_DB_PREFIX."societe as s";
 260        
 261        $sql .= " WHERE f.fk_facture IS NOT NULL";
 262        $sql .= " AND s.idp = ".$row[0];
 263        $sql .= " AND f.isfacturable = 'oui'"; 
 264        $sql .= " AND f.fk_ligne = l.rowid ";
 265        $sql .= " AND l.fk_contrat = c.rowid";  
 266        $sql .= " AND c.fk_client_comm = s.idp";
 267        $sql .= " ORDER BY f.date DESC";
 268        
 269        $re2sql = $db->query($sql) ;
 270        
 271        if ( $re2sql )
 272          {
 273            $nu2m = $db->num_rows($re2sql);      
 274            $j = 0;
 275            $k=2;
 276            while ($j < $nu2m)
 277          {
 278            $obj = $db->fetch_object($re2sql);
 279            
 280            $page->write_string($k, 0,  $obj->date, $fns);
 281            $page->write_string($k, 1,  $obj->ref, $fnc);
 282            $page->write_string($k, 2,  $obj->numero, $fnc);
 283            
 284            $perte = ($obj->cout_vente - $obj->fourn_montant );
 285            $total += $perte;
 286            
 287            $ki = $k+1;
 288  
 289            $page->write($k, 3,  "=E$ki-F$ki", $fn);
 290            $page->write($k, 4,  $obj->fourn_montant, $fn);
 291            $page->write($k, 5,  $obj->cout_vente, $fn);
 292            
 293            $k++;
 294            $j++;
 295          }
 296          }
 297  
 298        
 299        $i++;
 300  
 301        print $obj->nom . " " . $perte ."\n";
 302  
 303      }            
 304  
 305      }
 306    else
 307      {
 308        $error = 2;
 309        dolibarr_syslog("Erreur $error ".$db->error());
 310      }
 311  }
 312  
 313  
 314  
 315  
 316  /*
 317   *
 318   *
 319   */
 320  $workbook->close();
 321  $db->close();
 322  ?>