add unspsc code infro for categories
[contractdashboard.git] / displaySON.gexf.php
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
97
98
<?php
include_once("./lib/common.inc.php");
 
// TODO
// color suppliers based on value? static list of high value suppliers?
// TODO sigma
// link on contracts site?
// allow higher zoom levels
// describe methodology and yellow/red color meaning, creative commons attribution
$used_node_ids = Array();
$included_panel_ids = Array();
$used_edge_ids = Array();
function add_node($id, $label, $parent = "", $panel = false)
{
    global $used_node_ids;
    if (!in_array($id, $used_node_ids)) {
        echo "<node id='" . urlencode($id) . "' label=\"" . str_replace('"','',str_replace("&", "and", $label)) . "\" " . ($parent != "" ? "pid='$parent'><viz:size value='" . rand(1, 50) . "'/>" : "><viz:size value='2'/>")
            . "<viz:color b='" . ($panel ? 0 : 0) . "' g='" . ($panel ? 0 : 255) . "' r='" . ($panel ? 255 : 255) . "'/>";
        echo "</node>" . PHP_EOL;
        $used_node_ids[] = $id;
    }
}
 
function add_edge($from, $to, $weight = '1.0')
{
    global $used_edge_ids, $included_panel_ids;
    $id = urlencode($from . '-' . $to);
 
    if (!in_array($id, $used_edge_ids)) {
        echo "<edge id='" . $id . "' source='" . urlencode($from) . "' target='" . urlencode($to) . "' weight='" . $weight . "'>" . PHP_EOL;
        if ($weight != 0.1) {
            echo "<viz:color b='192' g='192' r='192'/>";
    } else {
            echo "<viz:color b='0' g='0' r='255'/>";
        }
        echo "</edge>" . PHP_EOL;
        $used_edge_ids[] = $id;
    }
}
 
$edges = Array();
header('Content-Type: application/gexf+xml');
echo '<?xml version="1.0" encoding="UTF-8"?>
<gexf xmlns="http://www.gexf.net/1.2draft" xmlns:viz="http://www.gexf.net/1.2draft/viz" version="1.2">
    <meta lastmodifieddate="2009-03-20">
        <creator>Gexf.net</creator>
        <description>A hello world! file</description>
    </meta>
    <graph mode="static" defaultedgetype="directed">
 
        <nodes>' . PHP_EOL;
 
$query = '
SELECT "supplierABN", text_mode("supplierName") AS "supplierName", contractnotice."SONID", count(*), sum(value) AS value 
FROM contractnotice 
WHERE "childCN" IS NULL 
AND extract(year from "publishDate") = \'2016\'
AND "supplierABN" in (select distinct "supplierABN" from contractnotice where (left("categoryUNSPSC"::text,2) = \'43\' or left("categoryUNSPSC"::text,2) = \'80\') )
AND contractnotice."SONID" != \'\' AND contractnotice."SONID" IS NOT NULL GROUP BY "supplierABN", contractnotice."SONID"';
$query = $conn->prepare($query);
$query->execute();
databaseError($conn->errorInfo());
foreach ($query->fetchAll() as $row) {
    add_node($row['supplierABN'], $row['supplierName'], "",false);
    $included_panel_ids[] = $row['SONID'];
    $edges[] = array("head" => $row['SONID'], "tail" => $row['supplierABN'], "weight" => $row['count']);
}
 
$query = 'SELECT "SONID", title FROM standingoffers';
$query = $conn->prepare($query);
$query->execute();
foreach ($query->fetchAll() as $row) {
   // if (in_array($row['SONID'], $included_panel_ids)){
    add_node($row['SONID'], $row['title'], "", true);
   // }
}
 
$query = 'select "SONID", abn,name from standingoffer_suppliers';
$query = $conn->prepare($query);
$query->execute();
databaseError($conn->errorInfo());
foreach ($query->fetchAll() as $row) {
    add_node($row['abn'], $row['name']);
 
    $edges[] = array("head"=>$row['SONID'], "tail"=>$row['abn'], "weight"=> 0.1);
}
 
echo '</nodes>
        <edges>' . PHP_EOL;
foreach ($edges as $edge) {
    add_edge($edge['head'], $edge['tail']);
}
echo ' </edges>
    </graph>
</gexf>' . PHP_EOL;
 
?>