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