[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: recap-commande.php,v 1.6 2005/02/28 13:04:48 rodolphe Exp $
  19   * $Source: /cvsroot/dolibarr/dolibarr/htdocs/telephonie/script/recap-commande.php,v $
  20   *
  21   *
  22   * Génére un tableur des lignes commandées dans le mois
  23   *
  24   *
  25   */
  26  require  "../../master.inc.php";
  27  require_once DOL_DOCUMENT_ROOT."/telephonie/lignetel.class.php";
  28  require_once DOL_DOCUMENT_ROOT."/includes/php_writeexcel/class.writeexcel_workbook.inc.php";
  29  require_once DOL_DOCUMENT_ROOT."/includes/php_writeexcel/class.writeexcel_worksheet.inc.php";
  30  require_once DOL_DOCUMENT_ROOT."/lib/dolibarrmail.class.php";
  31  
  32  define ('COMMANDETABLEUR_NOEMAIL', -3);
  33  
  34  $opt = getopt("e:");
  35  
  36  $email = $opt['e'];
  37  
  38  $date = time();
  39  $date_now = $date;
  40  
  41  Generate($date, $db, $date_now, $email);
  42  
  43  if (strftime("%d", $date_now) < 7)
  44  {
  45    $date_prev = $date - (86400 * 9);
  46    Generate($date_prev, $db, $date_now, $email);
  47  }
  48  
  49  /*
  50   *
  51   *
  52   */
  53  
  54  function Generate($date_gen, $db, $date_now, $email)
  55  {
  56    $datef = strftime("%Y-%m", $date_gen);
  57  
  58    $shortname = "recap-".$datef.".xls";
  59    $fname = DOL_DATA_ROOT ."/telephonie/ligne/commande/".$shortname;
  60    
  61    $ligne = new LigneTel($db);
  62    
  63    $workbook = &new writeexcel_workbook($fname);
  64    
  65    $worksheet = &$workbook->addworksheet();
  66    
  67    $worksheet->write(0, 0,  "Récapitulatif des commandes du mois de ".strftime("%B %Y",$date_gen)." (généré le ".strftime("%d %B %Y %HH%M", $date_now) . ")");
  68    
  69    $worksheet->set_column('A:A', 12);
  70    $worksheet->set_column('B:B', 42);
  71    $worksheet->set_column('C:C', 15);
  72    $worksheet->set_column('D:D', 14);
  73    $worksheet->set_column('E:E', 28);
  74  
  75    $formatcc =& $workbook->addformat();
  76    $formatcc->set_align('center');
  77    $formatcc->set_align('vcenter');
  78  
  79    $format[2] =& $workbook->addformat();
  80    $format[2]->set_align('center');
  81    $format[2]->set_align('vcenter');
  82    $format[2]->set_color('blue');
  83  
  84    $format[3] =& $workbook->addformat();
  85    $format[3]->set_align('center');
  86    $format[3]->set_align('vcenter');
  87    $format[3]->set_color('green');
  88  
  89    $format[4] =& $workbook->addformat();
  90    $format[4]->set_align('center');
  91    $format[4]->set_align('vcenter');
  92    $format[4]->set_color('pink');
  93  
  94    $format[5] =& $workbook->addformat();
  95    $format[5]->set_align('center');
  96    $format[5]->set_align('vcenter');
  97    $format[5]->set_color('orange');
  98  
  99    $format[6] =& $workbook->addformat();
 100    $format[6]->set_align('center');
 101    $format[6]->set_align('vcenter');
 102    $format[6]->set_color('red');
 103    $format[6]->set_bold();
 104  
 105    $format[7] =& $workbook->addformat();
 106    $format[7]->set_align('center');
 107    $format[7]->set_align('vcenter');
 108    $format[7]->set_color('red');
 109    $format[7]->set_bold();
 110  
 111    $format_left[2] =& $workbook->addformat();
 112    $format_left[2]->set_align('vcenter');
 113    $format_left[2]->set_color('blue');
 114  
 115    $format_left[3] =& $workbook->addformat();
 116    $format_left[3]->set_align('vcenter');
 117    $format_left[3]->set_color('green');
 118  
 119    $format_left[4] =& $workbook->addformat();
 120    $format_left[4]->set_align('vcenter');
 121    $format_left[4]->set_color('pink');
 122  
 123    $format_left[5] =& $workbook->addformat();
 124    $format_left[5]->set_align('vcenter');
 125    $format_left[5]->set_color('orange');
 126  
 127    $format_left[6] =& $workbook->addformat();
 128    $format_left[6]->set_align('vcenter');
 129    $format_left[6]->set_color('red');
 130    $format_left[6]->set_bold();
 131  
 132    $format_left[7] =& $workbook->addformat();
 133    $format_left[7]->set_align('vcenter');
 134    $format_left[7]->set_color('red');
 135    $format_left[7]->set_bold();
 136  
 137  
 138    $formatccb =& $workbook->addformat();
 139    $formatccb->set_align('center');
 140    $formatccb->set_align('vcenter');
 141    $formatccb->set_bold();
 142    
 143    $formatccbr =& $workbook->addformat();
 144    $formatccbr->set_align('center');
 145    $formatccbr->set_align('vcenter');
 146    $formatccbr->set_color('red');
 147    $formatccbr->set_bold();
 148  
 149    $formatc =& $workbook->addformat();
 150    $formatc->set_align('vcenter');
 151  
 152    $formatcb =& $workbook->addformat();
 153    $formatcb->set_align('vcenter');
 154    $formatcb->set_bold();
 155  
 156    $i = 0;
 157  
 158    $ligneids = array();
 159  
 160    $sqlall = "SELECT s.code_client, s.nom, s.idp as socid, l.ligne, f.nom as fournisseur, l.statut, l.rowid";
 161    $sqlall .= " , comm.name, comm.firstname, l.remise";
 162    $sqlall .= " , ".$db->pdate("l.date_commande")." as date_commande";
 163    $sqlall .= " FROM ".MAIN_DB_PREFIX."societe as s";
 164    $sqlall .= " , ".MAIN_DB_PREFIX."telephonie_societe_ligne as l";
 165    $sqlall .= " , ".MAIN_DB_PREFIX."societe as r";
 166    $sqlall .= " , ".MAIN_DB_PREFIX."user as comm";
 167    $sqlall .= " , ".MAIN_DB_PREFIX."telephonie_fournisseur as f";
 168    $sqlall .= " WHERE l.fk_soc = s.idp AND l.fk_fournisseur = f.rowid";
 169    $sqlall .= " AND l.fk_soc_facture = r.idp ";
 170    $sqlall .= " AND l.fk_commercial = comm.rowid ";
 171    $sqlall .= " AND date_format(l.date_commande,'%Y%m') = '".strftime("%Y%m", $date_gen)."'";
 172    /*
 173     *
 174     */
 175  
 176    $sql = $sqlall;
 177  
 178    $sql .= " ORDER BY l.date_commande DESC";
 179  
 180    $result = $db->query($sql);
 181  
 182    if ($result)
 183      {
 184        $num = $db->num_rows();
 185    
 186        $worksheet->write(1, 0,  "Code", $formatc);
 187        $worksheet->write(1, 1,  "Client", $formatc);
 188        $worksheet->write(1, 2,  "Numéro", $formatcc);
 189        $worksheet->write(1, 3,  "Date commande", $formatcc);
 190        $worksheet->write(1, 4,  "Statut actuel", $formatcc);
 191    
 192        while ($i < $num)
 193      {
 194        $obj = $db->fetch_object();    
 195        
 196        $j = $i + 3;
 197        
 198        $soc = new Societe($db);
 199        $soc->fetch($obj->socid);
 200        
 201        $worksheet->write($j, 0,  $obj->code_client, $format_left[$obj->statut]);
 202  
 203        $worksheet->write($j, 1,  $obj->nom, $format_left[$obj->statut]);
 204        
 205        $worksheet->write_string($j, 2,  "$obj->ligne", $format[$obj->statut]);
 206        
 207        $worksheet->write($j, 3,  strftime("%d/%m/%y",$obj->date_commande), $format[$obj->statut]);
 208  
 209        $worksheet->write($j, 4,  $ligne->statuts[$obj->statut], $format[$obj->statut]);
 210    
 211        $i++;
 212      }
 213      
 214        $db->free();
 215      }
 216    else 
 217      {
 218        print $db->error() . ' ' . $sql;
 219      }
 220  
 221    /*
 222     *
 223     *
 224     */
 225  
 226    $workbook->close();
 227    /*
 228     *
 229     */
 230    if ($date_gen == $date_now && strlen($email))
 231      {
 232        MailFile($fname, $shortname, $email);
 233      }
 234    
 235  }
 236  
 237  
 238  
 239  function MailFile($filename, $shortname, $to)
 240  {
 241    $subject = "Recapitulatif mensuel des commandes";
 242  
 243    $sendto = $to;
 244  
 245    $from = TELEPHONIE_LIGNE_COMMANDE_EMAIL_BCC;
 246    
 247    $message = "Bonjour,\n\nVeuillez trouver ci-joint le dernier récapitulatif des commandes.\n\n";
 248    $message .= "\n\nCordialement,\n";
 249    
 250    $mailfile = new DolibarrMail($subject,
 251                     $sendto,
 252                     $from,
 253                     $message);
 254  
 255    $mailfile->PrepareFile(array($filename),
 256               array("application/msexcel"),
 257               array($shortname));
 258    
 259    if ( $mailfile->sendfile() )
 260      {
 261        return 0;
 262      }  
 263  }