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