[Sommaire] [Imprimer]

   1  <?PHP
   2  /* Copyright (C) 2004-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: commande.tableur.class.php,v 1.5 2005/06/15 14:04:51 rodolphe Exp $
  19   * $Source: /cvsroot/dolibarr/dolibarr/htdocs/telephonie/fournisseur/commande/commande.tableur.class.php,v $
  20   *
  21   */
  22  
  23  require_once DOL_DOCUMENT_ROOT."/includes/php_writeexcel/class.writeexcel_workbook.inc.php";
  24  require_once DOL_DOCUMENT_ROOT."/includes/php_writeexcel/class.writeexcel_worksheet.inc.php";
  25  require_once DOL_DOCUMENT_ROOT."/lib/dolibarrmail.class.php";
  26  require_once DOL_DOCUMENT_ROOT."/telephonie/fournisseur/commande/methode.commande.class.php";
  27  
  28  define ('COMMANDETABLEUR_NOEMAIL', -3);
  29  
  30  class CommandeMethodeTableur extends CommandeMethode
  31  {
  32  
  33    function CommandeMethodeTableur ($DB, $USER=0, $fourn=0)
  34    {
  35      dolibarr_syslog("CommandeMethodeTableur::CommandeMethodeTableur");
  36  
  37  
  38      $this->nom = "Méthode Tableur joint";
  39      $this->db = $DB;
  40      $this->user = $USER;
  41      $this->fourn = $fourn;
  42    }
  43  
  44    function info()
  45    {
  46      return "Envoi un fichier tableur contenant la liste des lignes à commander";
  47    }
  48  
  49    function Create()
  50    {
  51      dolibarr_syslog("CommandeMethodeTableur::Create Fournisseur id : ".$this->fourn->id);
  52      dolibarr_syslog("CommandeMethodeTableur::Create Fournisseur email : ".$this->fourn->email_commande);
  53  
  54      $this->date = time();
  55  
  56      $this->datef = "commande-".strftime("%d%b%y-%HH%M", $this->date);
  57  
  58      $this->filename = $this->datef.".xls";
  59  
  60      $fname = DOL_DATA_ROOT ."/telephonie/ligne/commande/".$this->filename;
  61  
  62      if (strlen(trim($this->fourn->email_commande)) == 0)
  63        {
  64      $res = -3;
  65        }
  66  
  67      if (file_exists($fname))
  68        {
  69      $res = 2 ;
  70        }
  71      else
  72        {
  73      $res = $this->CreateFile($fname);
  74      $res = $res + $this->LogSql();
  75      $res = $res + $this->MailFile($fname);
  76        }
  77  
  78      dolibarr_syslog("CommandeMethodeTableur::CommandeMethodeTableur Return $res");
  79  
  80      return $res;
  81    }
  82    /**
  83     *
  84     *
  85     *
  86     *
  87     */
  88    function MailFile($filename)
  89    {
  90      $sql = "SELECT l.ligne";
  91      $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_societe_ligne as l";
  92      $sql .= " WHERE l.statut = 2";
  93      $sql .= " AND l.fk_fournisseur =".$this->fourn->id;
  94      
  95      $result = $this->db->query($sql);
  96  
  97      if ($result)
  98        {
  99      $num = $this->db->num_rows();
 100        }
 101  
 102      $subject = "Commande de Lignes N° ".$this->commande_id;
 103  
 104      $sendto = $this->fourn->email_commande;
 105  
 106      $from = TELEPHONIE_LIGNE_COMMANDE_EMAIL_BCC;
 107  
 108      $message = "Bonjour,\n\nVeuillez trouver ci-joint notre dernière commande.\n\n";
 109      $message .= "Nous avons à ce jour $num ligne(s) commandée(s) pour lesquelles nous attendons la confirmation de présélection.\n";
 110      $message .= "\nCordialement,\n\n";
 111  
 112      $message .= "-- \n";
 113      $message .= $this->user->fullname."\n";
 114  
 115      $mailfile = new DolibarrMail($subject,
 116                   $sendto,
 117                   $from,
 118                   $message);
 119  
 120      $mailfile->addr_bcc = TELEPHONIE_LIGNE_COMMANDE_EMAIL_BCC;
 121  
 122      $mailfile->PrepareFile(array($filename),
 123                 array("application/msexcel"),
 124                 array($this->datef.".xls"));
 125  
 126  
 127  
 128      $mailfile->write_to_file();
 129  
 130      if ( $mailfile->sendfile() )
 131        {
 132      return 0;
 133        }
 134      else
 135        {
 136      dolibarr_syslog("CommandeMethodeTableur::MailFile Erreur send");
 137        }
 138  
 139    }
 140  
 141    /*
 142     * Création du fichier
 143     *
 144     */
 145  
 146    function CreateFile($fname)
 147    {
 148      $ligne = new LigneTel($db);
 149  
 150      $workbook = &new writeexcel_workbook($fname);
 151  
 152      $worksheet = &$workbook->addworksheet();
 153      
 154      $worksheet->write(0, 0,  "Commande du ".strftime("%d %B %Y %HH%M", $this->date));
 155      
 156      $worksheet->set_column('A:A', 20);
 157      $worksheet->set_column('B:B', 40);
 158      $worksheet->set_column('C:C', 15);
 159      $worksheet->set_column('D:D', 9);
 160      $worksheet->set_column('E:E', 16);
 161      $worksheet->set_column('F:F', 18);   
 162      $worksheet->set_column('G:G', 24);
 163      
 164      $formatcc =& $workbook->addformat();
 165      $formatcc->set_align('center');
 166      $formatcc->set_align('vcenter');
 167      
 168      $formatccb =& $workbook->addformat();
 169      $formatccb->set_align('center');
 170      $formatccb->set_align('vcenter');
 171      $formatccb->set_bold();
 172  
 173      $formatccbr =& $workbook->addformat();
 174      $formatccbr->set_align('center');
 175      $formatccbr->set_align('vcenter');
 176      $formatccbr->set_color('red');
 177      $formatccbr->set_bold();
 178  
 179      $formatc =& $workbook->addformat();
 180      $formatc->set_align('vcenter');
 181      
 182      $formatcb =& $workbook->addformat();
 183      $formatcb->set_align('vcenter');
 184      $formatcb->set_bold();
 185  
 186      $i = 0;
 187  
 188      $this->ligneids = array();
 189      
 190      $sqlall = "SELECT s.nom, s.idp as socid, f.nom as fournisseur";
 191      $sqlall .= ", l.ligne, l.statut, l.rowid, l.remise";
 192      $sqlall .= ",".$this->db->pdate("l.date_commande") . " as date_commande";
 193      $sqlall .= " , comm.name, comm.firstname";
 194      $sqlall .= " FROM ".MAIN_DB_PREFIX."societe as s";
 195      $sqlall .= " , ".MAIN_DB_PREFIX."telephonie_societe_ligne as l";
 196      $sqlall .= " , ".MAIN_DB_PREFIX."societe as r";
 197      $sqlall .= " , ".MAIN_DB_PREFIX."user as comm";
 198      $sqlall .= " , ".MAIN_DB_PREFIX."telephonie_fournisseur as f";
 199      $sqlall .= " WHERE l.fk_soc = s.idp AND l.fk_fournisseur = f.rowid";
 200      $sqlall .= " AND l.fk_soc_facture = r.idp ";
 201      $sqlall .= " AND l.fk_commercial = comm.rowid ";
 202      $sqlall .= " AND f.rowid =".$this->fourn->id;
 203      /*
 204       *
 205       */
 206  
 207      $sql = $sqlall;
 208  
 209      $sql .= " AND l.statut in (1,4,8)";
 210      $sql .= " ORDER BY l.statut ASC";
 211      
 212      $result = $this->db->query($sql);
 213  
 214      if ($result)
 215        {
 216      $num = $this->db->num_rows();
 217      
 218      $worksheet->write(1, 0,  "Clients", $formatc);
 219      $worksheet->write(1, 1,  "Adresses", $formatc);
 220      $worksheet->write(1, 2,  "CLI", $formatcc);
 221      $worksheet->write(1, 3,  "Préfixe", $formatcc);
 222      $worksheet->write(1, 4,  "Présélection", $formatcc);
 223      $worksheet->write(1, 5,  "Connexion", $formatcc);
 224      $worksheet->write(1, 6,  "Date de la demande", $formatcc);
 225          
 226      while ($i < $num)
 227        {
 228          $obj = $this->db->fetch_object();    
 229  
 230          if (strlen($obj->ligne)== 10)
 231            {
 232          
 233          $j = $i + 2;
 234          $k = $j + 1;
 235          
 236          $soc = new Societe($this->db);
 237          $soc->fetch($obj->socid);
 238          
 239          $worksheet->write($j, 0,  $obj->nom, $formatc);
 240          $worksheet->write($j, 1,  $soc->adresse. " " . $soc->cp . " " . $soc->ville, $formatc);
 241          
 242          $worksheet->write_string($j, 2,  "$obj->ligne", $formatcc);
 243          
 244          $worksheet->write_string($j, 3,  "Non", $formatcc);
 245          $worksheet->write_string($j, 4,  "Oui", $formatcc);
 246          
 247          if ($obj->statut == 1)
 248            {
 249              $worksheet->write($j, 5,  "Ajouter", $formatccb);
 250              $worksheet->write($j, 6,  strftime("%d/%m/%y",$this->date), $formatcc);
 251            }
 252          elseif ($obj->statut == 8)
 253            {
 254              $worksheet->write($j, 5,  "Ajouter", $formatccb);
 255              $worksheet->write($j, 6,  strftime("%d/%m/%y",$this->date), $formatcc);
 256            }
 257          elseif($obj->statut == 4)
 258            {
 259              $worksheet->write($j, 5,  "A Résilier", $formatccbr);
 260              $worksheet->write($j, 6,  strftime("%d/%m/%y",$this->date), $formatcc);
 261            }
 262          else
 263            {
 264              $worksheet->write($j, 5,  "", $formatccb);
 265              $worksheet->write($j, 6,  "", $formatccb);
 266            }
 267          
 268          
 269          array_push($this->ligneids, $obj->rowid);
 270            }
 271          $i++;
 272        }
 273      
 274      $this->db->free();
 275        }
 276      else 
 277        {
 278      dolibarr_syslog("CommandeMethodeTableur::CreateFile Erreur SQL 1");
 279        }
 280  
 281      /*
 282       * Archives
 283       * Insertion des anciennes lignes dans le fichier Excell
 284       */
 285      
 286      $sql = $sqlall;
 287  
 288      //    $sql .= "AND l.statut > 0 AND l.statut <> 1 AND l.statut <> 4";
 289      // Modification on ajoute au fichier seulement les lignes en commandes
 290      $sql .= " AND l.statut = 2";
 291      $sql .= " ORDER BY l.date_commande ASC";
 292      
 293      $result = $this->db->query($sql);
 294  
 295      if ($result)
 296        {
 297      $num = $this->db->num_rows();
 298      $i = 0;
 299      while ($i < $num)
 300        {
 301          $obj = $this->db->fetch_object($i);    
 302          
 303          $jj = $i + $j + 2;
 304    
 305          $soc = new Societe($this->db);
 306          $soc->fetch($obj->socid);
 307  
 308          $worksheet->write($jj, 0,  $obj->nom, $formatc);
 309          $worksheet->write($jj, 1,  $soc->adresse. " " . $soc->cp . " " . $soc->ville, $formatc);
 310  
 311          $worksheet->write_string($jj, 2,  "$obj->ligne", $formatcc);
 312  
 313          $worksheet->write_string($jj, 3,  "Non", $formatcc);
 314          $worksheet->write_string($jj, 4,  "Oui", $formatcc);
 315  
 316          $worksheet->write($jj, 5,  "", $formatccb);
 317          $worksheet->write($jj, 6,  "Commandée le ".strftime("%d/%m/%y",$obj->date_commande), $formatccb);
 318          
 319  
 320          $i++;
 321        }
 322      
 323      $this->db->free();
 324        }
 325      else 
 326        {
 327      dolibarr_syslog("CommandeMethodeTableur::CreateFile Erreur SQL 2");
 328        }
 329     
 330      $workbook->close();
 331  
 332  
 333      /*
 334       * Modifie le statut des lignes commandées
 335       *
 336       */
 337  
 338      foreach ($this->ligneids as $lid)
 339        {
 340      $lint = new LigneTel($this->db);
 341      $lint->fetch_by_id($lid);
 342      if ($lint->statut == 1)
 343        {
 344          $lint->set_statut($this->user, 2);
 345        }
 346  
 347      if ($lint->statut == 4)
 348        {
 349          $lint->set_statut($this->user, 5);
 350        }
 351  
 352      if ($lint->statut == 8)
 353        {
 354          $lint->set_statut($this->user, 2);
 355        }
 356        }
 357  
 358      return 0;
 359  
 360    }
 361  }