[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-achat.php,v 1.1 2005/05/12 14:14:19 rodolphe Exp $
  19   * $Source: /cvsroot/dolibarr/dolibarr/htdocs/telephonie/tarifs/grille-export-achat.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/achat-".strftime("%Y-%m-%d", $date).".xls");
  38  $fna = ("achat-".strftime("%Y-%m-%d", $date).".xls");
  39  $workbook = &new writeexcel_workbook($fname);
  40  
  41  /*
  42   * Comparatif
  43   *
  44   */
  45  
  46  $sheetcomp = &$workbook->addworksheet("Comparatif");
  47  
  48  $sheetcomp->set_column('A:A', 40);
  49  $sheetcomp->set_column('B:F', 10);
  50  
  51  
  52  $num1_format =& $workbook->addformat(array(num_format => '#0.0000'));
  53  $num1_format->set_align('center');
  54  $num1_format->set_align('vcenter');
  55  
  56  $num2_format =& $workbook->addformat(array(num_format => '#0.0000'));
  57  $num2_format->set_right(1);
  58  $num2_format->set_align('center');
  59  $num2_format->set_align('vcenter');
  60  
  61  $num3_format =& $workbook->addformat(array(num_format => '#0.0000'));
  62  $num3_format->set_left(1);
  63  $num3_format->set_align('center');
  64  $num3_format->set_align('vcenter');
  65  
  66  $num3_format_best =& $workbook->addformat(array(num_format => '#0.0000'));
  67  $num3_format_best->set_left(1);
  68  $num3_format_best->set_align('center');
  69  $num3_format_best->set_align('vcenter');
  70  $num3_format_best->set_color('green');
  71  
  72  
  73  $formatcc =& $workbook->addformat();
  74  $formatcc->set_align('center');
  75  $formatcc->set_align('vcenter');  
  76  $formatcc->set_border(1);
  77  
  78  $sheetcomp->write(2, 0, "Tarif");
  79  
  80  $sql = "SELECT d.rowid, d.libelle as grille";
  81  $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_tarif_grille as d";
  82  $sql .= " WHERE d.type_tarif = 'achat'";
  83  $sql .= " ORDER BY d.rowid ASC";
  84  $resql = $db->query($sql);
  85  if ($resql)
  86  {
  87    $num = $db->num_rows($resql);
  88    $i = 0;
  89    $a = 1;  
  90  
  91    while ($i < $num)
  92      {
  93        $obj = $db->fetch_object($resql);
  94        
  95        $grilles[$i] = $obj->rowid;
  96  
  97        $sheetcomp->write(1, $a, $obj->grille);
  98  
  99        $sheetcomp->write(2, $a,  "/min", $formatcc);
 100        $sheetcomp->write(2, ($a+1),  "Fixe", $formatcc);
 101  
 102        $a = $a + 2;
 103        $i++;
 104      }
 105  }
 106  else
 107  {
 108    print $db->error();
 109  }
 110  
 111  $types = array('NAT','MOB','INT');
 112  
 113  $j = 3;
 114  
 115  foreach ($types as $type)
 116  {
 117    $tarifs = array();
 118  
 119    $sql = "SELECT t.libelle, t.rowid, t.type";
 120    $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_tarif as t";
 121    $sql .= " WHERE t.type = '".$type."' ORDER BY t.libelle ASC";
 122    
 123    $resql = $db->query($sql);
 124    if ($resql)
 125      {
 126        $num = $db->num_rows($resql);
 127        $i = 0;
 128        
 129        while ($i < $num)
 130      {
 131        $obj = $db->fetch_object($i);    
 132      
 133        $tid = $obj->rowid;
 134  
 135        $tarifs[$tid][0] = $obj->libelle;
 136        $i++;
 137      }
 138      }
 139  
 140    $ig = 0;
 141  
 142    foreach($grilles as $grille)
 143      {
 144        $sql = "SELECT t.libelle, d.libelle as grille, m.temporel, m.fixe, t.rowid, t.type";
 145        $sql .= " FROM ".MAIN_DB_PREFIX."telephonie_tarif_grille as d";
 146        $sql .= "," . MAIN_DB_PREFIX."telephonie_tarif_montant as m";
 147        $sql .= "," . MAIN_DB_PREFIX."telephonie_tarif as t";
 148        $sql .= " WHERE d.rowid = m.fk_tarif_desc";
 149        $sql .= " AND m.fk_tarif = t.rowid AND d.rowid = ".$grille;
 150        $sql .= " AND t.type = '".$type."' ORDER BY d.libelle ASC";
 151  
 152        $resql = $db->query($sql);
 153        if ($resql)
 154      {
 155        $num = $db->num_rows($resql);
 156        $i = 0;
 157        
 158        while ($i < $num)
 159          {
 160            $obj = $db->fetch_object($i);    
 161            
 162            $tid = $obj->rowid;
 163            
 164            $tarifs[$tid][$ig+1] = $obj->temporel;
 165            $tarifs[$tid][$ig+2] = $obj->fixe;
 166            $i++;
 167          }
 168      }
 169        $ig = $ig + 2;      
 170      }
 171  
 172    foreach($tarifs as $tarif)
 173      {
 174        $sheetcomp->write($j, 0,  $tarif[0]);
 175  
 176        $ig = 0;
 177  
 178        $min = $tarif[$ig+1];
 179        $igmin = 0;
 180  
 181        foreach($grilles as $grille)
 182      {      
 183        $format = $num3_format;
 184  
 185        if ($tarif[$ig+1] < $min)
 186          {
 187            $min = $tarif[$ig+1];
 188            $igmin = $ig;
 189          }
 190  
 191        $ig = $ig + 2;
 192      }
 193  
 194        $ig = 0;
 195  
 196        foreach($grilles as $grille)
 197      {      
 198        $format = $num3_format;
 199  
 200        if ($igmin == $ig)
 201          {
 202            $format = $num3_format_best;
 203          }
 204  
 205        $sheetcomp->write($j, ($ig+1),  $tarif[$ig+1], $format);
 206  
 207        if ($tarif[$ig+2] > 0)
 208          {
 209            $sheetcomp->write($j, ($ig+2),  $tarif[$ig+2], $num2_format);
 210          }
 211        else
 212          {
 213            $sheetcomp->write_string($j, ($ig+2),  "-", $num2_format);
 214          }
 215        $ig = $ig + 2;      
 216      }
 217       
 218        $j++;      
 219      } 
 220  
 221    $sheetcomp->write_blank($j, 1, $num3_format);
 222    $sheetcomp->write_blank($j, 2, $num2_format);
 223    $sheetcomp->write_blank($j, 4, $num2_format);
 224  
 225    $j++;
 226  }
 227  
 228  
 229  $sheetcomp->write(0, 0,  "Tarifs d'achats comparés au ".strftime("%d %m %Y",$date) );
 230  
 231  $workbook->close();
 232  $db->close();
 233  
 234  Header("Content-Disposition: attachment; filename=$fna");
 235  Header("Content-Type: application/x-msexcel");
 236  $fh=fopen($fname, "rb");
 237  fpassthru($fh);
 238  //unlink($fname);
 239  ?>