[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: commission.php,v 1.14 2005/11/16 12:13:16 rodolphe Exp $
  19   * $Source: /cvsroot/dolibarr/dolibarr/htdocs/telephonie/script/commission.php,v $
  20   *
  21   * Calcul des commissions des distributeurs
  22   */
  23  
  24  require  ("../../master.inc.php");
  25  require_once (DOL_DOCUMENT_ROOT."/telephonie/distributeurtel.class.php");
  26  
  27  $error = 0;
  28  $nbcommit = 0;
  29  $datetime = time();
  30  $space = str_repeat(" ",28);
  31  $user = new User($db, 1);
  32  $user->login = "Rodo";
  33  
  34  $opt = getopt("m:");
  35  
  36  if ($opt['m'] > 0)
  37  {
  38    $datetime = mktime(10,10,10,$opt['m'],10,2005);
  39  }
  40  
  41  $date = strftime("%d%h%Y%Hh%Mm%S",$datetime);
  42  
  43  /*
  44   * On facture les communications du mois précédent
  45   */
  46  
  47  $month = strftime("%m", $datetime);
  48  $year = strftime("%Y", $datetime);
  49  
  50  if ($month == 1)
  51  {
  52    $month_prev = "12";
  53    $year_prev = $year - 1;
  54  }
  55  else
  56  {
  57    $month_prev = $month - 1;
  58    $year_prev = $year ;
  59  }
  60  
  61  $month_prev = substr("00".$month_prev, -2) ;
  62  
  63  
  64  $dir = DOL_DATA_ROOT.'/telephonie/log/';
  65  if (!file_exists($dir))
  66    create_dir($dir);
  67  
  68  $dir = DOL_DATA_ROOT.'/telephonie/log/commission/';
  69  if (!file_exists($dir))
  70    create_dir($dir);
  71  
  72  function create_dir($dir)
  73  {
  74    if (! file_exists($dir))
  75      {
  76        umask(0);
  77        if (! @mkdir($dir, 0755))
  78      {
  79        die ("Erreur: Le répertoire ".$dir." n'existe pas et Dolibarr n'a pu le créer.");
  80      }
  81      }
  82  }
  83  
  84  if (! $db->begin()) die ;
  85  
  86  $fp = fopen($dir."/$month.$year.log","w+");
  87  fputs($fp,"Commissions $month/$year\n");
  88  /********************************************************
  89   *
  90   * Verification des données
  91   *
  92   *
  93   *********************************************************/
  94  
  95  $distributeurs = array();
  96  
  97  $sql = "SELECT distinct fk_distributeur";
  98  $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_contrat_priseordre";
  99  $sql .= " WHERE fk_distributeur <> 0";
 100    
 101  $resql = $db->query($sql);
 102    
 103  if ( $resql )
 104  {
 105    while ($row = $db->fetch_row($resql))
 106      {
 107        array_push($distributeurs, $row[0]);
 108      }
 109    $db->free($resql);
 110  }
 111  else
 112  {
 113    $error = 1;
 114    dolibarr_syslog("Verification Erreur ".$error);
 115  }
 116  
 117  /********************************************************
 118   *
 119   * Calcul des avances
 120   *
 121   *
 122   ********************************************************/
 123  dolibarr_syslog("Calcul avance");
 124  $sql = "SELECT rowid, fk_distributeur, fk_contrat, datepo, montant";
 125  $sql .= " , avance_pourcent, rem_pour_prev, rem_pour_autr, mode_paiement";
 126  $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_contrat_priseordre";
 127  $sql .= " WHERE date_format(datepo, '%Y%m') = '".$year_prev.$month_prev."'";
 128  $sql .= " AND fk_distributeur > 0";
 129  
 130  $resql = $db->query($sql);
 131  
 132  if ( $resql )
 133  {
 134    $num = $db->num_rows($resql);
 135    $i = 0;
 136    
 137    while ($i < $num)
 138      {
 139        $obj = $db->fetch_object($resql);
 140        
 141        if ($obj->mode_paiement == 'pre')
 142      {
 143        $pourcent = $obj->rem_pour_prev;
 144      }
 145        else
 146      {
 147        $pourcent = $obj->rem_pour_autr;
 148      }
 149        $avance_pourcent = $obj->avance_pourcent;
 150        
 151        $avance = $obj->montant * 12 * $avance_pourcent * $pourcent;
 152        
 153        $avance = round($avance  * 0.0001, 2);
 154  
 155        fputs($fp, "DIS : ".$obj->fk_distributeur);
 156        fputs($fp, " av avance po ".substr($space.$obj->rowid,-4));
 157        fputs($fp, " : ".substr($space.$avance,-8)."\n");
 158  
 159        $sqli = "INSERT INTO ".MAIN_DB_PREFIX."telephonie_commission_avance";
 160        $sqli .= " (date, fk_distributeur, fk_po,fk_contrat, montant, pourcentage, avance)";
 161        $sqli .= " VALUES ('".$year_prev.$month_prev."'";
 162        $sqli .= ",".$obj->fk_distributeur.",".$obj->rowid.",".$obj->fk_contrat;
 163        $sqli .= ",".ereg_replace(",",".",$avance);
 164        $sqli .= ",".ereg_replace(",",".",$pourcent);
 165        $sqli .= ",1)";
 166        
 167        if (! $db->query($sqli))
 168      {
 169        $error = 2;
 170        dolibarr_syslog("Calcul avance Erreur ".$db->error());
 171      }
 172        
 173        $i++;
 174      }
 175    $db->free($resql);
 176  }
 177  else
 178  {
 179    $error = 3;
 180    dolibarr_syslog("Erreur ".$db->error());
 181  }
 182   
 183  
 184  /********************************************************
 185   *
 186   * Calculs des commissions basées sur les consommations
 187   *
 188   *
 189   *********************************************************/
 190  
 191  $sql = "SELECT p.rowid,  p.fk_contrat,  p.fk_distributeur";
 192  $sql .= " , p.avance_pourcent, p.rem_pour_prev, p.rem_pour_autr";
 193  $sql .= " , p.avance_duree";
 194  $sql .= " , date_format(p.datepo + INTERVAL p.avance_duree MONTH, '%Y%m') as date_regul";
 195  $sql .= " , f.cout_vente,l.rowid as ligne";
 196  $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_contrat_priseordre as p";
 197  $sql .= " , ".MAIN_DB_PREFIX."telephonie_contrat as c";
 198  $sql .= " , ".MAIN_DB_PREFIX."telephonie_societe_ligne as l";
 199  $sql .= " , ".MAIN_DB_PREFIX."telephonie_facture as f";
 200  
 201  $sql .= " WHERE p.fk_contrat = c.rowid";
 202  $sql .= " AND l.fk_contrat = c.rowid";
 203  $sql .= " AND f.fk_ligne = l.rowid";
 204  $sql .= " AND date_format(f.date, '%Y%m') = '".$year_prev.$month_prev."'";
 205  $sql .= " AND date_format(p.datepo, '%Y%m') <= '".$year_prev.$month_prev."'";
 206  $sql .= " AND fk_distributeur > 0";
 207  
 208  $resql = $db->query($sql);
 209  //print $sql;
 210  if ( $resql )
 211  {
 212    $num = $db->num_rows($resql);
 213    $i = 0;
 214    
 215    while ($i < $num)
 216      {
 217        $obj = $db->fetch_object($resql);
 218    
 219        if ($obj->mode_paiement == 'pre')
 220      {
 221        $pourcent = $obj->rem_pour_prev;
 222      }
 223        else
 224      {
 225        $pourcent = $obj->rem_pour_autr;
 226      }
 227  
 228        $comm = round($obj->cout_vente * $pourcent * 0.01, 2) ;
 229       
 230        $sqli = "INSERT INTO ".MAIN_DB_PREFIX."telephonie_commission_conso";
 231        $sqli .= " (date, fk_distributeur, fk_contrat, fk_ligne, montant, pourcentage, avance)";
 232        $sqli .= " VALUES ('".$year_prev.$month_prev."'";
 233        $sqli .= ",".$obj->fk_distributeur.",".$obj->fk_contrat.",".$obj->ligne;
 234        $sqli .= ",".ereg_replace(",",".",$comm);
 235        $sqli .= ",".ereg_replace(",",".",$pourcent);
 236  
 237        if ($obj->date_regul < $year_prev.$month_prev)
 238      {
 239        $sqli .= ",0)";
 240        $avan = 0;
 241      }
 242        else
 243      {
 244        $sqli .= ",1)";
 245        $avan = 1;
 246      }
 247  
 248        fputs($fp, "DIS : ".$obj->fk_distributeur);
 249        fputs($fp, " CON : ".$obj->fk_contrat);
 250        fputs($fp, " conso  : $comm avance $avan\n");
 251        
 252        if (! $db->query($sqli))
 253      {
 254        $error = 4;
 255        dolibarr_syslog("Calcul conso Erreur ".$db->error());
 256      }
 257  
 258        //dolibarr_syslog("Conso po : ".$obj->rowid . " ".$comm);
 259                
 260        $i++;
 261      }
 262    $db->free($resql);
 263  }
 264  else
 265  {
 266    $error = 5;
 267    dolibarr_syslog("Erreur ".$db->error());
 268  }
 269  
 270  /********************************************************
 271   *
 272   * Régulation sur contrats annulés
 273   *
 274   *
 275   *********************************************************/
 276  
 277  
 278  /********************************************************
 279   *
 280   * Régulation des avances
 281   *
 282   *
 283   *********************************************************/
 284  $distri_av = array();
 285  
 286  $sql = "SELECT distinct fk_distributeur";
 287  $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_commission_avance";
 288  $sql .= " WHERE fk_distributeur <> 0";
 289    
 290  $resql = $db->query($sql);
 291    
 292  if ( $resql )
 293  {
 294    while ($row = $db->fetch_row($resql))
 295      {
 296        array_push($distri_av, $row[0]);
 297      }
 298    $db->free($resql);
 299  }
 300  else
 301  {
 302    $error = 6;
 303    dolibarr_syslog("Erreur regul avances ".$error);
 304  }
 305  
 306  $avan_regul = array();
 307  $comm_regul = array();
 308  
 309  foreach ($distri_av as $distributeur_id)
 310  {
 311    $avan_regul[$distributeur_id] = 0;
 312    $comm_regul[$distributeur_id] = 0;
 313  
 314    $sqla = "SELECT rowid, ".$db->pdate("datepo").", avance_duree";
 315    $sqla .= " FROM ".MAIN_DB_PREFIX."telephonie_contrat_priseordre";
 316    $sqla .= " WHERE fk_distributeur = ".$distributeur_id;
 317    $sqla .= " AND date_format(datepo + INTERVAL avance_duree MONTH, '%Y%m')='".$year_prev.$month_prev."';";
 318  
 319    $resqla = $db->query($sqla);
 320    
 321    if ( $resqla )
 322      {
 323        $numa = $db->num_rows($resqla);
 324        $ia = 0;
 325        
 326        while ($ia < $numa)
 327      {
 328        $rowa = $db->fetch_row($resqla);
 329         dolibarr_syslog("* Regul des avances de la po " .$rowa[0] . " ".strftime("%Y%m",$rowa[1]));
 330        $ia++;
 331  
 332        /* Calcul des sommes avancées */
 333        $sql = "SELECT a.montant, a.fk_contrat, c.statut";
 334        $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_commission_avance as a";
 335        $sql .= " , ".MAIN_DB_PREFIX."telephonie_contrat as c";
 336        $sql .= " WHERE a.fk_distributeur = ".$distributeur_id;
 337        $sql .= " AND c.rowid = a.fk_contrat";
 338        $sql .= " AND a.fk_po = ".$rowa[0];
 339        
 340        $resql = $db->query($sql);
 341        
 342        if ( $resql )
 343          {
 344            $num = $db->num_rows($resql);
 345            dolibarr_syslog("* Regul des avances de la po ".$rowa[0]." ".strftime("%Y%m",$rowa[1]).", $num avances");
 346            $i = 0;          
 347            while ($i < $num)
 348          {
 349            $row = $db->fetch_row($resql);
 350            
 351            $avan_regul[$distributeur_id] = $avan_regul[$distributeur_id] + $row[0];
 352            
 353            fputs($fp, "DIS : ".$distributeur_id);
 354            fputs($fp, " av regul  po ".substr($space.$rowa[0],-4));
 355            fputs($fp, " : ".substr($space.$row[0],-8)."\n");
 356  
 357            $sqlir = "INSERT INTO ".MAIN_DB_PREFIX."telephonie_commission_regul";
 358            $sqlir .= " (date, fk_distributeur, fk_contrat, montant, type)";
 359            $sqlir .= " VALUES ('".$year_prev.$month_prev."'";
 360            $sqlir .= ",'".$distributeur_id."','".$row[1];
 361            $sqlir .= "','-".ereg_replace(",",".",$row[0]);
 362            $sqlir .= "','avan')";
 363            $resqlir = $db->query($sqlir);
 364            if (!$resqlir)
 365              {
 366                $error = 32;
 367                dolibarr_syslog("Erreur insertion regul avances (error $error)");
 368                dolibarr_syslog($sqlir);
 369              }
 370  
 371            dolibarr_syslog("* Avance ".$row[0] . " statut : ".$row[2]);
 372            
 373            /* Communications relatives */
 374            $datup = $year_prev.$month_prev;
 375            $datdo = strftime("%Y%m",$rowa[1]);
 376            if ($row[2] <> 6)
 377              {
 378                dolibarr_syslog("* Communications <= $datup >= $datdo ");
 379                $sqlc = "SELECT sum(montant)";
 380                $sqlc .= " FROM ".MAIN_DB_PREFIX."telephonie_commission_conso";
 381                $sqlc .= " WHERE fk_contrat = ". $row[1];
 382                $sqlc .= " AND date <= '".$datup."' AND date >= '".$datdo."'";
 383                
 384                $resqlc = $db->query($sqlc);
 385                
 386                if ( $resqlc )
 387              {              
 388                while ($rowc = $db->fetch_row($resqlc))
 389                  {
 390                    $comm_regul[$distributeur_id] = $comm_regul[$distributeur_id] + $rowc[0];
 391                    dolibarr_syslog("* Conso générée ".$rowc[0]);
 392  
 393                    $sqlir = "INSERT INTO ".MAIN_DB_PREFIX."telephonie_commission_regul";
 394                    $sqlir .= " (date, fk_distributeur, fk_contrat, montant, type)";
 395                    $sqlir .= " VALUES ('".$year_prev.$month_prev."'";
 396                    $sqlir .= ",'".$distributeur_id."','".$row[1];
 397                    $sqlir .= "','".ereg_replace(",",".",$rowc[0]);
 398                    $sqlir .= "','comm')";
 399                    $resqlir = $db->query($sqlir);
 400                    if (!$resqlir)
 401                  {
 402                    $error = 31;
 403                    dolibarr_syslog("Erreur insertion regul avances conso (error $error)");
 404                    dolibarr_syslog($sqlir);
 405                  }
 406                  }
 407                $db->free($resqlc);
 408              }
 409                else
 410              {
 411                $error = 10;
 412                dolibarr_syslog("Erreur regul avances conso ".$error);
 413              }
 414              }
 415            else
 416              {
 417                /*
 418                 * Contrats résiliés
 419                 *
 420                 */
 421                fputs($fp, "DIS : ".$obj->fk_distributeur);
 422                fputs($fp, " CON : ".$row[1] . " ANNULE\n");
 423  
 424                $sqlc = "UPDATE ".MAIN_DB_PREFIX."telephonie_commission_conso";
 425                $sqlc .= " SET annul = '".$year_prev.$month_prev."'";
 426                $sqlc .= " WHERE fk_contrat = ".$row[1];
 427                $sqlc .= " AND date <= '".$datup."' AND date >= '".$datdo."'";
 428  
 429                $resqlc = $db->query($sqlc);
 430  
 431                if (! $resqlc )
 432              {
 433                $error = 11;
 434                dolibarr_syslog("Erreur regul avances conso ".$error);
 435              }
 436              }
 437            
 438            $i++;
 439          }
 440            $db->free($resql);
 441          }
 442        else
 443          {
 444            $error = 12;
 445            dolibarr_syslog("Erreur regul avances ".$db->error());
 446          }                             
 447      }
 448      }
 449    else
 450      {
 451        $error = 13;
 452        dolibarr_syslog("Erreur regul avances aaaa".$db->error());
 453        dolibarr_syslog($sqla);
 454      }
 455  }
 456  
 457  /********************************************************
 458   *
 459   * Calcul des consos
 460   *
 461   *
 462   *********************************************************/
 463  $distri_co = array();
 464  
 465  $sql = "SELECT distinct fk_distributeur";
 466  $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_commission_conso";
 467  $sql .= " WHERE fk_distributeur <> 0";
 468    
 469  $resql = $db->query($sql);
 470    
 471  if ( $resql )
 472  {
 473    while ($row = $db->fetch_row($resql))
 474      {
 475        array_push($distri_co, $row[0]);
 476      }
 477    $db->free($resql);
 478  }
 479  else
 480  {
 481    $error = 14;
 482    dolibarr_syslog("Erreur calcul des commission sur conso ".$error);
 483  }
 484  
 485  $comm_conso = array();
 486  
 487  foreach ($distri_co as $distributeur_id)
 488  {
 489    $comm_conso[$distributeur_id] = 0;
 490  
 491    $sqla = "SELECT rowid, ".$db->pdate("datepo").", fk_contrat";
 492    $sqla .= " FROM ".MAIN_DB_PREFIX."telephonie_contrat_priseordre";
 493    $sqla .= " WHERE fk_distributeur = ".$distributeur_id;
 494    $sqla .= " AND date_format(datepo + INTERVAL avance_duree MONTH, '%Y%m')<'".$year_prev.$month_prev."';";
 495  
 496    $resqla = $db->query($sqla);
 497    
 498    if ( $resqla )
 499      {
 500        $numa = $db->num_rows($resqla);
 501        $ia = 0;
 502        
 503        while ($ia < $numa)
 504      {
 505        $rowa = $db->fetch_row($resqla);
 506        dolibarr_syslog("** Calcul des consos la po " .$rowa[0] . " ".strftime("%Y%m",$rowa[1]));
 507        $ia++;
 508  
 509        /* Communications relatives */
 510  
 511        $datup = $year_prev.$month_prev;
 512        
 513        dolibarr_syslog("** Communications  $datup");
 514            
 515        $sqlc = "SELECT sum(montant)";
 516        $sqlc .= " FROM ".MAIN_DB_PREFIX."telephonie_commission_conso";
 517        $sqlc .= " WHERE fk_contrat = ". $rowa[2];
 518        $sqlc .= " AND date = '".$datup."';";
 519        
 520        $resqlc = $db->query($sqlc);
 521        
 522        if ( $resqlc )
 523          {
 524            if ( $rowc = $db->fetch_row($resqlc) )
 525          {
 526            $comm_conso[$distributeur_id] = $comm_conso[$distributeur_id] + $rowc[0];          
 527            dolibarr_syslog("** Conso générée ".$rowc[0]);
 528          }
 529            else
 530          {
 531            $error = 151;
 532            dolibarr_syslog("Erreur regul conso");
 533          }
 534            $db->free($resqlc);
 535          }
 536        else
 537          {
 538            $error = 15;
 539            dolibarr_syslog("Erreur regul conso");
 540          }
 541      }                    
 542      }
 543    else
 544      {
 545        $error = 16;
 546        dolibarr_syslog("Erreur regul conso".$db->error());
 547        dolibarr_syslog($sqla);
 548      }
 549  }
 550  
 551  /********************************************************
 552   *
 553   *
 554   *
 555   ********************************************************/
 556  
 557  foreach ($distributeurs as $distributeur_id)
 558  {
 559    $distributeur = new DistributeurTelephonie($db);
 560    $distributeur->fetch($distributeur_id);
 561  
 562    dolibarr_syslog($distributeur->nom . " : ".$month_prev."-".$year_prev);
 563  
 564    $amount = 0;
 565  
 566    $amount = $amount + $comm_regul[$distributeur_id];
 567    $amount = $amount - $avan_regul[$distributeur_id];
 568    $amount = $amount + $comm_conso[$distributeur_id];
 569  
 570    fputs($fp, "DIS : ".$distributeur_id);
 571    fputs($fp, " ".str_repeat("-",35)."\n");
 572  
 573    fputs($fp, "DIS : ".$distributeur_id);
 574    fputs($fp, " Comm Regul : ".substr($space.$comm_regul[$distributeur_id],-15)."\n");
 575  
 576    fputs($fp, "DIS : ".$distributeur_id);
 577    fputs($fp, " Comm Conso : ".substr($space.$comm_conso[$distributeur_id],-15)."\n");
 578  
 579    /********************************************************
 580     *
 581     * Somme des commissions
 582     *
 583     *
 584     *********************************************************/
 585    
 586    $sql = "SELECT sum(montant)";
 587    $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_commission_avance";
 588    $sql .= " WHERE fk_distributeur = ".$distributeur->id; 
 589    $sql .= " AND date = '".$year_prev.$month_prev."';";
 590  
 591    $resql = $db->query($sql);
 592    
 593    if ( $resql )
 594      {
 595        if ($row = $db->fetch_row($resql))
 596      {
 597        fputs($fp, "DIS : ".$distributeur_id);
 598        fputs($fp, " Avances    : ".substr($space.$row[0],-15)."\n");
 599  
 600        fputs($fp, "DIS : ".$distributeur_id);
 601        fputs($fp, " Avan Regul : ".substr($space."-".$avan_regul[$distributeur_id],-15)."\n");
 602  
 603        $amount = $amount + $row[0];
 604        /* commission finale */
 605        $sqli = "INSERT INTO ".MAIN_DB_PREFIX."telephonie_commission";
 606        $sqli .= " (date, fk_distributeur, montant)";
 607        $sqli .= " VALUES ('".$year_prev.$month_prev."'";
 608        $sqli .= ",".$distributeur->id;
 609        $sqli .= ",".ereg_replace(",",".",$amount).")";
 610  
 611        if (! $db->query($sqli))
 612          {
 613            $error = 17;
 614            dolibarr_syslog("Erreur insertion Commission finale");
 615          }
 616  
 617        dolibarr_syslog("Commission finale ".$amount);
 618  
 619        fputs($fp, "DIS : ".$distributeur_id);
 620        fputs($fp, " Comm final : ".substr($space.$amount,-15)."\n");
 621      }
 622        else
 623      {
 624        $error = 18;
 625        dolibarr_syslog("Erreur lecture avances");
 626      }
 627        $db->free($resql);
 628      }
 629    else
 630      {
 631        $error = 19;
 632        dolibarr_syslog("Erreur ".$error);
 633      }
 634  }
 635  
 636  if ($error == 0)
 637  {
 638    $db->commit();
 639    dolibarr_syslog("Commit");
 640  }
 641  else
 642  {
 643    $db->rollback();
 644    dolibarr_syslog("Rollback", LOG_ERR);
 645  }
 646  dolibarr_syslog("----------------");
 647  fclose($fp);
 648  ?>