--- a/admin/updateUNSPSC.php
+++ b/admin/updateUNSPSC.php
@@ -1,50 +1,76 @@
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'];
+ $center = str_replace("center", "centre", $row['Title']);
+ $unspsc[$center] = $row['UNSPSC'];
+ $accessory = str_replace("accesor", "accessor", $row['Title']);
+ $unspsc[$accessory] = $row['UNSPSC'];
+ $lyslyz = str_replace("lyz", "lys", $row['Title']);
+ $unspsc[$lyslyz] = $row['UNSPSC'];
+ $tire = str_replace("ire", "yre", $row['Title']);
+ $unspsc[$tire] = $row['UNSPSC'];
+
+ $pe = str_replace("pe", "pae", $row['Title']);
+ $unspsc[$pe] = $row['UNSPSC'];
+ $ane = str_replace("ane", "anae", $row['Title']);
+ $unspsc[$ane] = $row['UNSPSC'];
+ $airo = str_replace("airplane", "aeroplane", $row['Title']);
+ $unspsc[$airo] = $row['UNSPSC'];
+ // some divergence from standard
+ $forensicit = str_replace("Information technology consultation services", "Forensic IT Services", $row['Title']);
+ $unspsc[$forensicit] = $row['UNSPSC'];
+ $powercable = str_replace( "Power cable", "Power cable installation and supply", $row['Title']);
+ $unspsc[$powercable] = $row['UNSPSC'];
+ $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) {
-$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'];
+ if (!isset($unspsc[$record['category']]) || $unspsc[$record['category']] == "") {
+ $missing[$record['category']] = (isset($missing[$record['category']]) ? $missing[$record['category']] : 0) + $record['value'];
// echo "
\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']]) . "
\n";
- else echo "error".mysql_error();
- }
- }
-} else echo "error".mysql_error();
-asort($missing,SORT_NUMERIC);
+ } 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']]) . "
\n";
+ } else {
+ echo "error
";
+ print_r($conn->errorInfo());
+ }
+ }
+}
+asort($missing, SORT_NUMERIC);
print_r($missing);
?>