[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: grille-export.php,v 1.1 2005/05/12 11:37:19 rodolphe Exp $
  19   * $Source: /cvsroot/dolibarr/dolibarr/htdocs/telephonie/tarifs/grille-export.php,v $
  20   *
  21   */
  22  require ("./pre.inc.php");
  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  
  26  /*
  27   * Sécurité accés client
  28   */
  29  if ($user->societe_id > 0) 
  30  {
  31    $action = '';
  32    $socidp = $user->societe_id;
  33  }
  34  
  35  $date = time();
  36  
  37  $fname = ("/tmp/tarifs-".strftime("%Y-%m-%d", $date).".xls");
  38  $workbook = &new writeexcel_workbook($fname);
  39  
  40  $worksheet = &$workbook->addworksheet("Tarif");
  41  
  42  $worksheet->set_column('A:A', 20);
  43  $worksheet->set_column('B:B', 40);
  44  $worksheet->set_column('C:E', 10);
  45  
  46  $num1_format =& $workbook->addformat(array(num_format => '#0.0000'));
  47  
  48  $formatcc =& $workbook->addformat();
  49  $formatcc->set_align('center');
  50  $formatcc->set_align('vcenter');  
  51  
  52  $worksheet->write(2, 0,  "Grille");
  53  $worksheet->write(2, 1,  "Tarif");
  54  $worksheet->write(2, 2,  "/min");
  55  $worksheet->write(2, 3,  "Fixe");
  56  $worksheet->write(2, 4,  "Type", $formatcc);
  57  $worksheet->write(2, 6,  "ID", $formatcc);
  58  
  59  $types = array('NAT','MOB','INT');
  60  
  61  $j = 3;
  62  
  63  foreach ($types as $type)
  64  {
  65    $sql = "SELECT t.libelle, d.libelle as grille, m.temporel, m.fixe, t.rowid, t.type";
  66    $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_tarif_grille as d";
  67    $sql .= "," . MAIN_DB_PREFIX."telephonie_tarif_montant as m";
  68    $sql .= "," . MAIN_DB_PREFIX."telephonie_tarif as t";
  69    
  70    $sql .= " WHERE d.rowid = m.fk_tarif_desc";
  71    $sql .= " AND m.fk_tarif = t.rowid";
  72    
  73    $sql .= " AND d.rowid = '".$_GET["id"]."'";
  74    $sql .= " AND t.type = '".$type."'";
  75    $sql .= " ORDER BY d.libelle ASC";
  76    
  77  
  78    $result = $db->query($sql);
  79    if ($result)
  80      {
  81        $num = $db->num_rows();
  82        $i = 0;
  83        
  84        while ($i < $num)
  85      {
  86        $obj = $db->fetch_object($i);    
  87        
  88        $worksheet->write($j, 0,  $obj->grille);
  89        $worksheet->write($j, 1,  $obj->libelle);
  90        $worksheet->write($j, 2,  $obj->temporel, $num1_format);
  91        $worksheet->write($j, 3,  $obj->fixe, $num1_format);
  92        $worksheet->write($j, 4,  $obj->type, $formatcc);
  93        $worksheet->write($j, 6,  $obj->rowid, $formatcc);
  94  
  95        $j++;      
  96        $i++;
  97      }
  98        
  99        $db->free();
 100      }
 101    else 
 102      {
 103        print $db->error() . ' ' . $sql;
 104      }  
 105    $j++;
 106  }
 107  
 108  /*
 109   * Comparatif
 110   *
 111   */
 112  
 113  
 114  $sheetcomp = &$workbook->addworksheet("Comparatif");
 115  
 116  $sheetcomp->set_column('A:A', 40);
 117  $sheetcomp->set_column('B:F', 10);
 118  
 119  
 120  $num1_format =& $workbook->addformat(array(num_format => '#0.0000'));
 121  $num1_format->set_align('center');
 122  $num1_format->set_align('vcenter');
 123  
 124  $num2_format =& $workbook->addformat(array(num_format => '#0.0000'));
 125  $num2_format->set_right(1);
 126  $num2_format->set_align('center');
 127  $num2_format->set_align('vcenter');
 128  
 129  $num3_format =& $workbook->addformat(array(num_format => '#0.0000'));
 130  $num3_format->set_left(1);
 131  $num3_format->set_align('center');
 132  $num3_format->set_align('vcenter');
 133  
 134  
 135  $formatcc =& $workbook->addformat();
 136  $formatcc->set_align('center');
 137  $formatcc->set_align('vcenter');  
 138  $formatcc->set_border(1);
 139  
 140  $sql = "SELECT d.libelle as grille";
 141  $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_tarif_grille as d";
 142  $sql .= " WHERE d.rowid IN (1,".$_GET["id"].")";
 143  $sql .= " ORDER BY d.rowid ASC";
 144  $resql = $db->query($sql);
 145  if ($resql)
 146  {
 147    $num = $db->num_rows($resql);
 148    $i = 0;
 149    $a = 1;  
 150  
 151    while ($i < $num)
 152      {
 153        $obj = $db->fetch_object($resql);
 154        
 155        $sheetcomp->write(1, $a, $obj->grille);
 156  
 157        $a = $a + 2;
 158        $i++;
 159      }
 160  }
 161  else
 162  {
 163    print $db->error();
 164  }
 165  
 166  
 167  $sheetcomp->write(2, 0,  "Tarif");
 168  $sheetcomp->write(2, 1,  "/min", $formatcc);
 169  $sheetcomp->write(2, 2,  "Fixe", $formatcc);
 170  $sheetcomp->write(2, 3,  "/min", $formatcc);
 171  $sheetcomp->write(2, 4,  "Fixe", $formatcc);
 172  
 173  $types = array('NAT','MOB','INT');
 174  
 175  $j = 3;
 176  
 177  foreach ($types as $type)
 178  {
 179    $tarifs = array();
 180  
 181  
 182    $sql = "SELECT t.libelle, d.libelle as grille, t.rowid, t.type";
 183    $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_tarif_grille as d";
 184    $sql .= "," . MAIN_DB_PREFIX."telephonie_tarif as t";
 185    
 186    $sql .= " WHERE d.rowid = '".$_GET["id"]."'";
 187    $sql .= " AND t.type = '".$type."'";
 188    $sql .= " ORDER BY d.libelle ASC";
 189    
 190    $resql = $db->query($sql);
 191    if ($resql)
 192      {
 193        $num = $db->num_rows($resql);
 194        $i = 0;
 195        
 196        while ($i < $num)
 197      {
 198        $obj = $db->fetch_object($i);    
 199      
 200        $tid = $obj->rowid;
 201  
 202        $tarifs[$tid][0] = $obj->libelle;
 203        $i++;
 204      }
 205      }
 206  
 207    $sql = "SELECT t.libelle, d.libelle as grille, m.temporel, m.fixe, t.rowid, t.type";
 208    $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_tarif_grille as d";
 209    $sql .= "," . MAIN_DB_PREFIX."telephonie_tarif_montant as m";
 210    $sql .= "," . MAIN_DB_PREFIX."telephonie_tarif as t";
 211    $sql .= " WHERE d.rowid = m.fk_tarif_desc";
 212    $sql .= " AND m.fk_tarif = t.rowid"; 
 213    $sql .= " AND d.rowid = 1";
 214    $sql .= " AND t.type = '".$type."'";
 215    $sql .= " ORDER BY d.libelle ASC";
 216    
 217    $resql = $db->query($sql);
 218    if ($resql)
 219      {
 220        $num = $db->num_rows($resql);
 221        $i = 0;
 222        
 223        while ($i < $num)
 224      {
 225        $obj = $db->fetch_object($i);    
 226  
 227        $tid = $obj->rowid;
 228  
 229        $tarifs[$tid][1] = $obj->temporel;
 230        $tarifs[$tid][2] = $obj->fixe;
 231        $i++;
 232      }
 233      }
 234  
 235  
 236    $sql = "SELECT t.rowid, m.temporel, m.fixe";
 237    $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_tarif_grille as d";
 238    $sql .= "," . MAIN_DB_PREFIX."telephonie_tarif_montant as m";
 239    $sql .= "," . MAIN_DB_PREFIX."telephonie_tarif as t";
 240    $sql .= " WHERE d.rowid = m.fk_tarif_desc";
 241    $sql .= " AND m.fk_tarif = t.rowid"; 
 242    $sql .= " AND d.rowid = ".$_GET["id"];
 243    $sql .= " AND t.type = '".$type."'";
 244    $sql .= " ORDER BY d.libelle ASC";
 245    
 246    $resql = $db->query($sql);
 247    if ($resql)
 248      {
 249        $num = $db->num_rows($resql);
 250        $i = 0;
 251        
 252        while ($i < $num)
 253      {
 254        $obj = $db->fetch_object($i);    
 255  
 256        $tid = $obj->rowid;
 257  
 258        $tarifs[$tid][3] = $obj->temporel;
 259        $tarifs[$tid][4] = $obj->fixe;
 260        $i++;
 261      }
 262      }
 263  
 264  
 265    foreach($tarifs as $tarif)
 266      {
 267        $sheetcomp->write($j, 0,  $tarif[0]);
 268        $sheetcomp->write($j, 1,  $tarif[1], $num3_format);
 269        $sheetcomp->write($j, 2,  $tarif[2], $num2_format);
 270        $sheetcomp->write($j, 3,  $tarif[3], $num1_format);
 271        $sheetcomp->write($j, 4,  $tarif[4], $num2_format);
 272        
 273        $j++;      
 274      } 
 275  
 276    $sheetcomp->write_blank($j, 1, $num3_format);
 277    $sheetcomp->write_blank($j, 2, $num2_format);
 278    $sheetcomp->write_blank($j, 4, $num2_format);
 279  
 280    $j++;
 281  }
 282  
 283  
 284  $sheetcomp->write(0, 0,  "Tarifs comparés au ".strftime("%d %m %Y",$date) );
 285  $worksheet->write(0, 0,  "Tarifs au ".strftime("%d %m %Y",$date) );
 286  
 287  
 288  $workbook->close();
 289  $db->close();
 290  
 291  Header("Content-Disposition: attachment; filename=$fname");
 292  header("Content-Type: application/x-msexcel");
 293  $fh=fopen($fname, "rb");
 294  fpassthru($fh);
 295  //unlink($fname);
 296  ?>