Add standing offers
[contractdashboard.git] / admin / updateUNSPSC.php
Alex Sadleir








Alex Sadleir
Alex Sadleir
Alex Sadleir
Alex Sadleir
Alex Sadleir


Alex Sadleir




































Alex Sadleir
Alex Sadleir
Alex Sadleir

Alex Sadleir





Alex Sadleir
Alex Sadleir

Alex Sadleir

Alex Sadleir
Alex Sadleir
Alex Sadleir























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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
<?php
 
error_reporting(E_ALL);
 
include_once("../lib/common.inc.php");
 
$unspscresult = $conn->prepare('select * from "UNSPSCcategories";');
$unspscresult->execute();
foreach ($unspscresult->fetchAll() as $row) {
    $row['Title'] = strtolower($row['Title']);
    $unspsc[strtolower($row['Title'])] = $row['UNSPSC'];
    // some Australian spellings
    $isiz = str_replace("iz", "is", $row['Title']);
    $unspsc[$isiz] = $row['UNSPSC'];
    $iseize = str_replace("ize", "ise", $row['Title']);
    $unspsc[$iseize] = $row['UNSPSC'];
    $filfill = str_replace("fill", "fil", $row['Title']);
    $unspsc[$filfill] = $row['UNSPSC'];
    $ampersand = str_replace("&", "and", $row['Title']);
    $unspsc[$ampersand] = $row['UNSPSC'];
    $defence = str_replace("efense", "efence", $row['Title']);
    $unspsc[$defence] = $row['UNSPSC'];
    $armor = str_replace("rmored", "rmoured", $row['Title']);
    $unspsc[$armor] = $row['UNSPSC'];
    $labour = str_replace("labor", "labour", $row['Title']);
    $unspsc[$labour] = $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
        $services = str_replace( " services", "", $row['Title']);
    $unspsc[$services] = $row['UNSPSC'];
        $microa = str_replace( "micro assemblies", "microassemblies", $row['Title']);
    $unspsc[$microa] = $row['UNSPSC'];
        $lightb = str_replace( "light bulbs", "lightbulbs", $row['Title']);
    $unspsc[$lightb] = $row['UNSPSC'];
        $kitchen = str_replace( "domestic kitchenware and kitchen supplies", "domestic kitchenware", $row['Title']);
    $unspsc[$kitchen] = $row['UNSPSC'];
      $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'];
        $powercable = str_replace("Power cable", "Power cable installation and supply", $row['Title']);
    $unspsc[$powercable] = $row['UNSPSC'];
        $unemployment = str_replace("Unemployment services", "Employment services", $row['Title']);
    $unspsc[$unemployment] = $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'];
}
//print_r($unspsc);
$query = 'SELECT "CNID",lower("category") as 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 (!isset($unspsc[$record['category']]) || $unspsc[$record['category']] == "") {
        $missing[$record['category']] = (isset($missing[$record['category']]) ? $missing[$record['category']] : 0) + $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<br>";
            print_r($conn->errorInfo());
        }
    }
}
asort($missing, SORT_NUMERIC);
print_r($missing);
?>