<?php
error_reporting(E_ALL);
 
include_once("../lib/common.inc.php");
 
$unspscresult= $conn->prepare('select * from "UNSPSCcategories";');
$unspscresult->execute();
foreach ($unspscresult->fetchAll() as $row) {
        $unspsc[$row['Title']] = $row['UNSPSC'];
        // some Australian spellings
        $isiz = str_replace("iz","is",$row['Title']);
        $unspsc[$isiz] = $row['UNSPSC'];
        $filfill = str_replace("fill","fil",$row['Title']);
        $unspsc[$filfill] = $row['UNSPSC'];
        $defence = str_replace("efense","efence",$row['Title']);
        $unspsc[$defence] = $row['UNSPSC'];
                $armor = str_replace("rmored","rmoured",$row['Title']);
        $unspsc[$armor] = $row['UNSPSC'];
        $erre = str_replace("er","re",$row['Title']);
        $unspsc[$erre] = $row['UNSPSC'];
        $lyslyz = str_replace("lyz","lys",$row['Title']);
        $unspsc[$lyslyz] = $row['UNSPSC'];
        // some divergence from standard
        $tobacco = str_replace("Food Beverage and Tobacco Products","Food and Beverage Products",$row['Title']);
        $unspsc[$tobacco] = $row['UNSPSC'];
        $architect = str_replace("Building and Construction and Maintenance Services","Architectural services",$row['Title']);
        $unspsc[$architect] = $row['UNSPSC'];
        // some just plain wrong
        $noOilRigs = str_replace("Building and Construction and Maintenance Services","Management and provision of all facilities engineering modification and maintenance services for a site or platform",$row['Title']);
        $unspsc[$noOilRigs] = $row['UNSPSC'];
        
}
$query = 'SELECT "CNID","category","value"
FROM contractnotice
WHERE "categoryUNSPSC" IS NULL OR "categoryUNSPSC" = 0';
$emptycatresult = $conn->prepare($query);
$missing = Array();
$emptycatresult->execute();
        foreach ($emptycatresult->fetchAll() as $record) {
                
        if ($unspsc[$record['category']] == "") {
                $missing[$record['category']]= $missing[$record['category']]+ $record['value'];
//              echo "<br>\n Category not found for: \n";
//              print_r($record);
        } else {
        $result = $conn->exec('UPDATE contractnotice SET "categoryUNSPSC" = 
\''.$unspsc[$record['category']].'\' where "CNID" = '.$record['CNID'].';');
        if ($result) echo $record['CNID']. " set to ". ($unspsc[$record['category']]) . " <br>\n";
        else echo "error".$conn->errorInfo();
        }
        } 
asort($missing,SORT_NUMERIC);
print_r($missing);
?>