More unspsc
[contractdashboard.git] / displayBubbletree.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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
 
<!DOCTYPE html> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head> 
        <meta charset="UTF-8"/> 
        <title>Minimal BubbleTree Demo</title> 
        <script type="text/javascript" src="lib/bubbletree/lib/jquery-1.5.2.min.js"></script> 
        <script type="text/javascript" src="lib/bubbletree/lib/jquery.history.js"></script> 
        <script type="text/javascript" src="lib/bubbletree/lib/raphael.js"></script> 
        <script type="text/javascript" src="lib/bubbletree/lib/vis4.js"></script> 
        <script type="text/javascript" src="lib/bubbletree/lib/Tween.js"></script> 
        <script type="text/javascript" src="lib/bubbletree/build/bubbletree.js"></script> 
        <link rel="stylesheet" type="text/css" href="lib/bubbletree/build/bubbletree.css" /> 
        <script type="text/javascript" src="lib/bubbletree/styles/cofog.js"></script> 
        
        
        <script type="text/javascript"> 
       
                $(function() {
                <?php
include_once ("lib/common.inc.php");
 
include("lib/Color.php");
$color = new Lux_Color();
 
 
$unspscresult= $conn->prepare('select * from "UNSPSCcategories";');
$unspscresult->execute();
foreach ($unspscresult->fetchAll() as $row) {
  $unspsc[$row['UNSPSC']] = $row['Title'];
 
}
$total = 0;
 
$catsresult = $conn->prepare('SELECT distinct substr( "categoryUNSPSC"::text, 0, 2 ) as cat 
FROM contractnotice WHERE "categoryUNSPSC" IS NOT NULL group by "categoryUNSPSC";;');
$catsresult->execute();
foreach ($catsresult->fetchAll() as $row) {
        $cats[] = $row['cat'];
}
$cattwosresult = $conn->prepare('SELECT distinct substr( "categoryUNSPSC"::text, 0, 3 ) as cat
FROM contractnotice
WHERE "categoryUNSPSC" IS NOT NULL
GROUP BY  "categoryUNSPSC"  order by cat ;');
$cattwosresult->execute();
                foreach ($cattwosresult->fetchAll() as $tworow) { 
                        $cattwos[substr($tworow['cat'],0,1)][] = $tworow['cat'];
                }
                                
        
 
$catthreesresult = $conn->prepare('SELECT substr( "categoryUNSPSC"::text, 0, 4 ) as cat , SUM( "value" ) as value
FROM contractnotice
WHERE "childCN" is null and "categoryUNSPSC" IS NOT NULL
GROUP BY cat order by cat ;');
$catthreesresult->execute();
                foreach ($catthreesresult->fetchAll() as $threerow) { 
                        $catthrees[substr($threerow['cat'],0,2)][] = $threerow;
                }
$nodes = Array();
foreach ($cats as $catNum) {
$catColor = $color->hsv2hex(Array($catNum/10, .7, abs(($catNum*(1/10))-.5) + .5));
        $catName = $unspsc[$catNum . "0000000"] . $catNum;
        $subnodes = Array();
        $catValue = 0;
        foreach ($cattwos[$catNum] as $twoCatNum) {
                $subcatName = $unspsc[$twoCatNum. "000000"] .$twoCatNum;
                $subsubnodes = Array();
        $subCatValue = 0;
                $subCatColor = $color->hsv2hex(Array($catNum/10, rand(1,10)/10, abs(($catNum*(1/10))-.5) + .5));
                foreach ($catthrees[$twoCatNum] as $threerow) {
                        $subsubcatName = $unspsc[$threerow['cat'] . "00000"] . $threerow['cat'];
                        $subsubnodes[] = Array(
                                "label" => $subsubcatName,
                                "amount" => $threerow['value'],
                                //"color" => "#".$subCatColor
                        );
                        $subCatValue += $threerow['value'];
                }
                $subnodes[] = Array(
                        "label" => $subcatName,
                        "amount" => $subCatValue,
                        "color" => "#".$subCatColor,
                        "children" => $subsubnodes
                );
                $catValue += $subCatValue;
        }
        $nodes[] = Array(
                "label" => $catName,
                "amount" => $catValue,
                "color" => "#".$catColor,
                "children" => $subnodes
        );
        $total += $catValue;
}
$data = Array(
        "label" => "Australian Federal Government Contract Spending",
        "amount" => $total,
        "color" => "#000000",
        "children" => $nodes
);
echo "var data =eval('('+'" . json_encode($data) . "'+')');";
?>
 
                        new BubbleTree({
                                data: data,
                                container: '.bubbletree'
                        });
                
                        
                });
     
        </script> 
</head> 
<body> 
        <div class="bubbletree-wrapper"> 
                <div class="bubbletree"></div> 
        </div> 
</body> 
</html>