| <?php | <?php |
| if (php_sapi_name() != "cli") { | if (php_sapi_name() != "cli") { |
| include_once ("../lib/common.inc.php"); | include_once ("../lib/common.inc.php"); |
| auth(); | 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"; |
| $fname = "AusTenderCustomisedUNSPSCCodeSet-August2012.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"; | $query = 'delete from "UNSPSCcategories" where "UNSPSC" = '.$data[0].'; insert into "UNSPSCcategories" values('.$data[0].',\''.trim($data[1]).'\');'; |
| echo $query."<br>\n"; | |
| $conn->exec($query); | $conn->exec($query); |
| print_r($conn->errorInfo()); | |
| 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); |
| } | } |
| ?> | ?> |