<?php
error_reporting(E_ALL);
 
include_once("../lib/common.inc.php");
 
$unspscresult = mysql_query ("select * from UNSPSCcategories;");
while ($row = mysql_fetch_assoc($unspscresult)) {
        $unspsc[$row['Title']] = $row['UNSPSC'];
        // some Australian spellings
        $isiz = str_replace("iz","is",$row['Title']);
        $unspsc[$isiz] = $row['UNSPSC'];
        $defence = str_replace("efense","efence",$row['Title']);
        $unspsc[$defence] = $row['UNSPSC'];
        $center = str_replace("enter","entre",$row['Title']);
        $unspsc[$center] = $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 = mysql_query ($query);
$missing = Array();
if ($emptycatresult){
        while ($record = mysql_fetch_assoc($emptycatresult)) {
        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 = mysql_query("UPDATE contractnotice SET categoryUNSPSC = 
'".mysql_real_escape_string($unspsc[$record['category']])."' where CNID = 
'".mysql_real_escape_string($record['CNID'])."';");
        if ($result) echo $record['CNID']. " set to ". ($unspsc[$record['category']]) . " <br>\n";
        else echo "error".mysql_error();
        }
        } 
} else echo "error".mysql_error();
asort($missing,SORT_NUMERIC);
print_r($missing);
?>