<?php
if (php_sapi_name() != "cli") {
    
include_once ("../lib/common.inc.php");
auth();
 
  // display existing
 
$unspscresult= $conn->prepare('select * from "UNSPSCcategories";');
$unspscresult->execute();
foreach ($unspscresult->fetchAll() as $row) {
  $unspsc[$row['UNSPSC']] = $row['Title'];
 
}
$catsresult = $conn->prepare('SELECT substr( "categoryUNSPSC"::text, 0, 2 ) as cat , SUM( "value" ) as value
FROM contractnotice
GROUP BY cat ;');
echo "<table>";
$catsresult->execute();
foreach ($catsresult->fetchAll() as $row) {
  $catName = $unspsc[$row['cat']."0000000"].$row['cat'];
  if ($row['cat'] == "") $catName = "null";
  
  echo "<tr><td>$catName</td><td>".$row['value']."</td></tr>";
}
// import new from file
      $success = 0;
//      $fname = "UNSPSC_ECCMA_V13.2_UNDP_V7.csv";
      $fname = "AusTenderCustomisedUNSPSCCodeSet-August2012.csv";
      echo " ============== $fname  ============== <br>";
      flush();
      $row = 1;
      $handle = fopen($path . $fname, "r");
      $headers;
      
      while (($data = fgetcsv($handle, 1000)) !== false) {
          $num = count($data);
          
/*          if ($row == 3) {
              $headers = $data;
          } elseif ($row > 3) {
//print_r($data);
$query = 'insert into "UNSPSCcategories" values(".$data[1].","$data[2]");';             */
$query = 'delete from  "UNSPSCcategories" where "UNSPSC" = '.$data[0].'; insert into "UNSPSCcategories" values('.$data[0].',\''.trim($data[1]).'\');';  
echo $query."<br>\n";           
$conn->exec($query);
print_r($conn->errorInfo());    
 
              flush();
              
              //echo "<hr>\n";
/*          }*/
          
          $row++;
      }
      echo "<br> $success records successfully created";
      flush();
      fclose($handle);
}
?>