1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | <?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; $fname = "UNSPSC_ECCMA_V13.2_UNDP_V7.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]");'; //echo $query."<br>\n"; $conn->exec($query); flush(); //echo "<hr>\n"; } $row++; } echo "<br> $success records successfully created"; flush(); fclose($handle); ?> |