| <?php | <?php |
| include_once("../lib/common.inc.php"); | include_once("../lib/common.inc.php"); |
| // 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; | $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); |
| ?> | ?> |