[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-calcul.php,v 1.16 2005/11/08 14:08:09 rodolphe Exp $
  19   * $Source: /cvsroot/dolibarr/dolibarr/htdocs/telephonie/script/facturation-calcul.php,v $
  20   *
  21   *
  22   * Script de calcul de la facturation
  23   * - Lit les entrées dans la table import_cdr
  24   * - Verifie que tous les tarifs sont dispos
  25   * - Importe les lignes dans llx_communications_details
  26   * - Calcul la facture téléphonique par ligne
  27   */
  28  
  29  /**
  30     \file       htdocs/telephonie/script/facturation-calcul.php
  31     \ingroup    telephonie
  32     \brief      Calcul des factures
  33     \version    $Revision: 1.16 $
  34  */
  35  
  36  
  37  require  ("../../master.inc.php");
  38  
  39  require_once  (DOL_DOCUMENT_ROOT."/societe.class.php");
  40  require_once (DOL_DOCUMENT_ROOT."/telephonie/lignetel.class.php");
  41  require_once (DOL_DOCUMENT_ROOT."/telephonie/facturetel.class.php");
  42  require_once (DOL_DOCUMENT_ROOT."/telephonie/communication.class.php");
  43  
  44  //require_once (DOL_DOCUMENT_ROOT."/telephonie/telephonie-tarif.class.php");
  45  require_once (DOL_DOCUMENT_ROOT."/telephonie/telephonie.tarif.class.php");
  46  
  47  
  48  $error = 0;
  49  $nbcommit = 0;
  50  $datetime = time();
  51  
  52  $date = strftime("%d%h%Y%Hh%Mm%S",$datetime);
  53  
  54  /*
  55   * On facture les communications du mois précédent
  56   */
  57  
  58  $month = strftime("%m", $datetime);
  59  $year = strftime("%Y", $datetime);
  60  
  61  if ($month == 1)
  62  {
  63    $month = "12";
  64    $year = $year - 1;
  65  }
  66  else
  67  {
  68    $month = substr("00".($month - 1), -2) ;
  69  }
  70  
  71  /********************************************************
  72   *
  73   * Affiche le nombre de comunications a traiter
  74   *
  75   *********************************************************/
  76  
  77  $sql = "SELECT count(*)";
  78  $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_import_cdr";;
  79    
  80  $resql = $db->query($sql);
  81    
  82  if ( $resql )
  83  {
  84    $num = $db->num_rows($resql);
  85    $row = $db->fetch_row($resql);
  86  
  87    dolibarr_syslog("Communications à traiter ".$row[0]);
  88    $db->free($resql);
  89  }
  90  else
  91  {
  92    $error = 1;
  93    dolibarr_syslog("Erreur ".$error);
  94  }
  95  
  96  /**********************************************************
  97  *
  98  *
  99  *
 100  ***********************************************************/
 101  
 102  $sql = "SELECT MAX(rowid) FROM ".MAIN_DB_PREFIX."telephonie_facture";
 103  
 104  $resql = $db->query($sql);
 105    
 106  if ( $resql )
 107  {
 108    $row = $db->fetch_row($resql);
 109  
 110    dolibarr_syslog("Max rowid avant facture ".$row[0]);
 111    $db->free($resql);
 112  }
 113  else
 114  {
 115    $error = 2;
 116    dolibarr_syslog("Erreur ".$error);
 117  }
 118  
 119  /**
 120   *
 121   * Lectures des différentes lignes dans la table d'import
 122   *
 123   */
 124  
 125  if (!$error)
 126  {
 127    $user = new user($db,1);
 128    
 129    $sql = "SELECT distinct(t.fk_ligne)";
 130    $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_import_cdr as t";
 131    $sql .= " ORDER BY fk_ligne ASC";
 132    
 133    $lines_keys = array();
 134    
 135    if ( $db->query($sql) )
 136      {
 137        $num = $db->num_rows();
 138        
 139        $i = 0;
 140        
 141        while ($i < $num)
 142      {
 143        $row = $db->fetch_row();
 144        
 145        $lines_keys[$i] = $row[0];
 146        
 147        $i++;
 148      }            
 149        $db->free();
 150        dolibarr_syslog(sizeof($lines_keys)." lignes trouvées");
 151      }
 152    else
 153      {
 154        $error = 3;
 155        dolibarr_syslog("Erreur ".$error);
 156      }
 157  }
 158  
 159  
 160  
 161  
 162  /**********************************************************
 163  *
 164  * Création d'un batch de facturation
 165  *
 166  ***********************************************************/
 167  
 168  if (sizeof($lines_keys) > 0)
 169  {
 170    $sql = "INSERT INTO ".MAIN_DB_PREFIX."telephonie_facturation_batch";
 171    $sql .= " (date_batch) VALUES (now())";
 172    $resql = $db->query($sql);
 173    
 174    if ( $resql )
 175      {
 176        $batch_id = $db->last_insert_id(MAIN_DB_PREFIX."telephonie_facturation_batch");
 177        
 178        dolibarr_syslog("Batch ID ".$batch_id);
 179      }
 180    else
 181      {
 182        $error = 20;
 183        dolibarr_syslog("Erreur ".$error);
 184      }  
 185  }
 186  
 187  /* ***************************************************** */
 188  /*                                                       */
 189  /* Traitements                                           */
 190  /*                                                       */
 191  /*                                                       */
 192  /* ***************************************************** */
 193  
 194  if (!$error)
 195  {
 196  
 197    foreach ($lines_keys as $line_key)
 198      {
 199        $error = 0;
 200        $ligne = new LigneTel($db);
 201  
 202        if ( $db->query("BEGIN") )
 203      {
 204        if ($ligne->fetch_by_id($line_key) > 0 )
 205          {
 206            if ($ligne->socid == 0)
 207          {
 208            $error = 4;
 209            dolibarr_syslog("Error ($error)");
 210          }            
 211          }
 212        else
 213          {
 214  
 215            $error = 5;      
 216            dolibarr_syslog("Error ($error): Aucune société rattachée à la ligne : $line_key");
 217          }
 218  
 219        
 220        /*
 221         * Récupération des infos sur la sociétés
 222         *
 223         */      
 224        if (!$error )
 225          {          
 226            $soc = new Societe($db);
 227            if ( $soc->fetch($ligne->socid) )
 228          {
 229            
 230          }
 231            else
 232          {
 233            $error = 6;
 234            dolibarr_syslog("Error ($error)");
 235          }
 236          }
 237        
 238        /*
 239         *
 240         * Création d'une facture de telephonie si la ligne est facturable
 241         *
 242         */
 243        
 244        if (!$error)
 245          {
 246            if ($ligne->facturable == 1)
 247          {
 248            $facturable = 'oui';
 249          }
 250            else
 251          {
 252            $facturable = 'non';
 253          }
 254            
 255            $sql = "INSERT INTO ".MAIN_DB_PREFIX."telephonie_facture";
 256            $sql .= " (fk_ligne, ligne, date, isfacturable, fk_batch, fk_contrat)";
 257            $sql .= " VALUES (".$ligne->id.",";
 258            $sql .= " '$ligne->numero','".$year."-".$month."-01'";
 259            $sql .= ", '$facturable',".$batch_id;
 260            $sql .= ", ".$ligne->contrat.")";
 261            
 262            if ($db->query($sql))
 263          {
 264            $facid = $db->last_insert_id(MAIN_DB_PREFIX."telephonie_facture");
 265          }
 266            else
 267          {
 268            $error++;
 269            dolibarr_syslog("Erreur d'insertion dans llx_telephonie_facture");
 270            dolibarr_syslog($db->error());
 271            dolibarr_syslog($sql);
 272          }
 273          }     
 274        /*
 275         *
 276         * Calcul de la facture
 277         *
 278         */
 279        if (!$error)
 280          {
 281            $total_achat = 0;
 282            $total_vente = 0;
 283            $total_fourn = 0;
 284  
 285            if (calcul($db, $ligne, $facid, $total_achat, $total_vente, $total_fourn) <> 0)
 286          {
 287            $error++;
 288            dolibarr_syslog("Erreur de calcul de la facture pour la ligne $line_key $ligne->numero");
 289          }      
 290          }      
 291        
 292        /*
 293         *
 294         * Insertion des données dans la base
 295         *
 296         */
 297             
 298        if (!$error)
 299          {
 300            $total_vente_remise = $total_vente;
 301            
 302            $total_vente_remise = ereg_replace(",",".", $total_vente_remise);
 303            
 304            $gain = ($total_vente_remise - $total_fourn);
 305            
 306            $total_achat = ereg_replace(",",".", $total_achat);
 307            $total_vente = ereg_replace(",",".", $total_vente);
 308            $total_fourn = ereg_replace(",",".", $total_fourn);
 309            
 310            $gain = ereg_replace(",",".", $gain);
 311            
 312            $sql = "UPDATE ".MAIN_DB_PREFIX."telephonie_facture";
 313            
 314            $sql .= " SET ";
 315            $sql .= " fourn_montant = $total_fourn";
 316            $sql .= " , cout_achat = $total_achat";
 317            $sql .= " , cout_vente = $total_vente";
 318            $sql .= " , remise = $ligne->remise";
 319            $sql .= " , cout_vente_remise = $total_vente_remise";
 320            $sql .= " , gain = $gain";
 321            
 322            $sql .= " WHERE rowid =".$facid;
 323            
 324            if ($db->query($sql))
 325          {
 326            
 327          }
 328            else
 329          {
 330            $error++;
 331            dolibarr_syslog("Erreur de mise à jour dans llx_telephonie_facture");
 332            dolibarr_syslog($db->error());
 333            dolibarr_syslog($sql);
 334          }
 335          }
 336        
 337        /*
 338         * Suppression des données de la table d'import
 339         *
 340         */
 341        
 342        if (!$error)
 343          {
 344            $sql = "DELETE FROM ".MAIN_DB_PREFIX."telephonie_import_cdr";
 345            $sql .= " WHERE fk_ligne = $line_key ";
 346            
 347            if (! $db->query($sql))
 348          {
 349            $error++;
 350            dolibarr_syslog("Erreur de suppression dans llx_telephonie_import_cdr");
 351          }
 352          }
 353        
 354        /*
 355         * Commit / Rollback SQL
 356         *
 357         */      
 358        
 359        if (!$error)
 360          {
 361            $db->query("COMMIT");
 362            $nbcommit++;
 363            dolibarr_syslog("Ligne $ligne->numero - COMMIT");
 364          }
 365        else
 366          {
 367            $db->query("ROLLBACK");
 368            dolibarr_syslog("Ligne $ligne->numero - ROLLBACK de la transaction");          
 369          }
 370      }
 371        else
 372      {
 373        dolibarr_syslog("Erreur ouverture Transaction SQL");
 374      }
 375      } /* fin de la boucle */
 376  
 377    /*
 378     *
 379     *
 380     */
 381  }
 382  
 383  /**********************************************************
 384  *
 385  *
 386  *
 387  ***********************************************************/
 388  $sql = "SELECT MAX(rowid) FROM ".MAIN_DB_PREFIX."telephonie_facture";
 389  
 390  $resql = $db->query($sql);
 391    
 392  if ( $resql )
 393  {
 394    $row = $db->fetch_row($resql);
 395  
 396    dolibarr_syslog("Max rowid après facture ".$row[0]);
 397    $db->free($resql);
 398  }
 399  else
 400  {
 401    $error++;
 402  }
 403  
 404  /**********************************************************
 405  *
 406  *
 407  *
 408  ***********************************************************/
 409  
 410  dolibarr_syslog($nbcommit." facture émises");
 411  
 412  /**********************************************************
 413  *
 414  *
 415  *
 416  ***********************************************************/
 417  $sql = "SELECT count(*) FROM ".MAIN_DB_PREFIX."telephonie_import_cdr";
 418  
 419  $resql = $db->query($sql);
 420    
 421  if ( $resql )
 422  {
 423    $row = $db->fetch_row($resql);
 424  
 425    dolibarr_syslog($row[0]. " communications restantes dans la table d'import");
 426    $db->free($resql);
 427  }
 428  else
 429  {
 430    $error++;
 431  }
 432  
 433  
 434  $db->close();
 435  
 436  dolibarr_syslog("Fin Batch ID ".$batch_id);
 437  
 438  dolibarr_syslog("Conso mémoire ".memory_get_usage() );
 439  
 440  // FIN
 441  
 442  /******************************************************************************
 443   *
 444   * Fonction de calcul de la facture
 445   *
 446   ******************************************************************************/
 447  
 448  function calcul($db, $ligne, $facture_id, &$total_cout_achat, &$total_cout_vente, &$total_cout_fourn)
 449  {
 450    $error = 0;
 451  
 452    $total   = 0;
 453    $nbinter = 0;
 454    $nbmob   = 0;
 455    $nbnat   = 0;
 456    $duree   = 0;
 457  
 458    $fournisseur_id = 1;
 459  
 460    $tarif_spec = 1 ;
 461  
 462    $sql = "SELECT d.grille_tarif";
 463  
 464    $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_distributeur as d";
 465    $sql .= " , ".MAIN_DB_PREFIX."telephonie_distributeur_commerciaux as dc";
 466    $sql .= " , ".MAIN_DB_PREFIX."telephonie_societe_ligne as l";
 467  
 468    $sql .= " WHERE l.rowid = ".$ligne->id;
 469    $sql .= " AND d.rowid = dc.fk_distributeur";
 470    $sql .= " AND l.fk_commercial_sign = dc.fk_user";
 471      
 472    $resql = $db->query($sql);
 473  
 474    if ( $resql )
 475      {
 476        $num_sql = $db->num_rows($resql);
 477  
 478        if ($num_sql > 0)
 479      {
 480        $row = $db->fetch_row($resql);
 481        $tarif_spec = $row[0];
 482      }
 483        $db->free($resql);
 484      }
 485    
 486    dolibarr_syslog("Utilisation du tarif ".$tarif_spec." pour la ligne ".$ligne->id);
 487  
 488  
 489    $tarif_achat = new TelephonieTarif($db, $fournisseur_id, "achat");
 490    $tarif_vente = new TelephonieTarif($db, $fournisseur_id, "vente", $tarif_spec, $ligne->client_comm_id);
 491  
 492    $comms = array();
 493  
 494    $sql = "SELECT t.idx, t.fk_ligne, t.ligne, t.montant, t.duree, t.num, t.date, t.heure, t.dest";
 495    $sql .= " , t.fichier, t.fk_fournisseur";
 496    $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_import_cdr as t";
 497    $sql .= " WHERE t.fk_ligne = ".$ligne->id;
 498      
 499    $resql = $db->query($sql);
 500  
 501    if ($resql)
 502      {
 503        $num_sql = $db->num_rows($resql);
 504        $i = 0;
 505        
 506        while ($i < $num_sql && $error == 0)
 507      {
 508        $objp = $db->fetch_object($resql);
 509  
 510        $comm = new CommunicationTelephonique();
 511  
 512        $comm->index       = $objp->idx;
 513        $comm->fk_ligne    = $objp->fk_ligne;
 514        $comm->ligne       = $objp->ligne;
 515        $comm->date        = $objp->date;
 516        $comm->heure       = $objp->heure;
 517        $comm->duree       = $objp->duree;
 518        $comm->dest        = $objp->dest;
 519        $comm->numero      = $objp->num;
 520        $comm->montant     = $objp->montant;
 521        $comm->fichier_cdr = $objp->fichier;
 522        $comm->fournisseur = $objp->fk_fournisseur;
 523        $comm->facture_id  = $facture_id;
 524       
 525        $comms[$i] = $comm;
 526  
 527        $i++;
 528      }
 529  
 530        $db->free($resql);
 531      }
 532    else
 533      {
 534        $error++;
 535        dolibarr_syslog("Erreur dans Calcul() Problème SQL");
 536      }
 537  
 538    for ($ii = 0 ; $ii < $num_sql ; $ii++)
 539      {
 540        $comm = $comms[$ii];
 541  
 542        $error = $error + $comm->cout($tarif_achat, $tarif_vente, $ligne, $db);
 543  
 544        $total_cout_fourn = $total_cout_fourn + $comm->montant;
 545        $total_cout_achat = $total_cout_achat + $comm->cout_achat;
 546        $total_cout_vente = $total_cout_vente + $comm->cout_vente;
 547  
 548        $error = $error + $comm->logsql($db);
 549      }
 550  
 551    return $error;
 552  }
 553  ?>