| <!DOCTYPE html> | <!DOCTYPE html> |
| <html xmlns="http://www.w3.org/1999/xhtml"> | <html xmlns="http://www.w3.org/1999/xhtml"> |
| <head> | <head> |
| <meta charset="UTF-8"/> | <meta charset="UTF-8"/> |
| <title>Minimal BubbleTree Demo</title> | <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-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/jquery.history.js"></script> |
| <script type="text/javascript" src="lib/bubbletree/lib/raphael.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/vis4.js"></script> |
| <script type="text/javascript" src="lib/bubbletree/lib/Tween.js"></script> | <script type="text/javascript" src="lib/bubbletree/lib/Tween.js"></script> |
| <script type="text/javascript" src="lib/bubbletree/build/bubbletree.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" /> | <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" src="lib/bubbletree/styles/cofog.js"></script> |
| <script type="text/javascript"> | <script type="text/javascript"> |
| $(function() { | $(function() { |
| <?php | <?php |
| include_once ("lib/common.inc.php"); | include_once ("lib/common.inc.php"); |
| include("lib/Color.php"); | include("lib/Color.php"); |
| $color = new Lux_Color(); | $color = new Lux_Color(); |
| $unspscresult = mysql_query("select * from UNSPSCcategories;"); | $unspscresult= $conn->prepare('select * from "UNSPSCcategories";'); |
| while ($row = mysql_fetch_assoc($unspscresult)) { | $unspscresult->execute(); |
| $unspsc[$row['UNSPSC']] = $row['Title']; | foreach ($unspscresult->fetchAll() as $row) { |
| $unspsc[$row['UNSPSC']] = $row['Title']; | |
| } | } |
| $total = 0; | $total = 0; |
| $cats = 0; | |
| $catsresult = mysql_query("SELECT LEFT( categoryUNSPSC, 1 ) as cat , | $catsresult = $conn->prepare('SELECT distinct substr( "categoryUNSPSC"::text, 0, 3 ) as cat |
| SUM( value ) as value | FROM contractnotice WHERE "categoryUNSPSC" IS NOT NULL group by "categoryUNSPSC";;'); |
| FROM `contractnotice` | $catsresult->execute(); |
| WHERE childCN = 0 | foreach ($catsresult->fetchAll() as $row) { |
| GROUP BY cat ;"); | $cats[] = $row['cat']; |
| } | |
| $cattwosresult = $conn->prepare('SELECT distinct substr( "categoryUNSPSC"::text, 0, 5 ) 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,2)][] = $tworow['cat']; | |
| } | |
| $catthreesresult = $conn->prepare('SELECT substr( "categoryUNSPSC"::text, 0, 7 ) 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,4)][] = $threerow; | |
| } | |
| $nodes = Array(); | $nodes = Array(); |
| while ($row = mysql_fetch_assoc($catsresult)) { | foreach ($cats as $catNum) { |
| $cats++; | $catColor = $color->hsv2hex(Array($catNum/10, .7, abs(($catNum*(1/10))-.5) + .5)); |
| $catColor = $color->hsl2hex(Array($cats/10, .7, .5)); | $catName = substr($unspsc[$catNum . "000000"],0,18) . $catNum; |
| $catName = $unspsc[$row['cat'] . "0000000"] . $row['cat']; | |
| if ($row['cat'] == "") $catName = "null"; | |
| $subnodes = Array(); | $subnodes = Array(); |
| $cattwosresult = mysql_query("SELECT LEFT( categoryUNSPSC, 2 ) as cat , | $catValue = 0; |
| SUM( value ) as value | foreach ($cattwos[$catNum] as $twoCatNum) { |
| FROM `contractnotice` | $subcatName = $unspsc[$twoCatNum. "0000"] .$twoCatNum; |
| WHERE childCN = 0 and LEFT( categoryUNSPSC, 1 ) = '{$row['cat']}' | |
| GROUP BY cat ;"); | |
| while ($tworow = mysql_fetch_assoc($cattwosresult)) { | |
| $subcatName = $unspsc[$tworow['cat'] . "000000"] . $tworow['cat']; | |
| if ($tworow['cat'] == "") $subcatName = "null"; | |
| $subsubnodes = Array(); | $subsubnodes = Array(); |
| $catthreesresult = mysql_query("SELECT LEFT( categoryUNSPSC, 3 ) as cat , | $subCatValue = 1; |
| SUM( value ) as value | $subCatColor = $color->hsv2hex(Array($catNum/10, rand(1,10)/10, abs(($catNum*(1/10))-.5) + .5)); |
| FROM `contractnotice` | foreach ($catthrees[$twoCatNum] as $threerow) { |
| WHERE childCN = 0 and LEFT( categoryUNSPSC, 2 ) = '{$tworow['cat']}' | $subsubcatName = $unspsc[$threerow['cat'] . "00"] . $threerow['cat']; |
| GROUP BY cat ;"); | |
| $subCatColor = $color->hsl2hex(Array($cats/10, rand(1,10)/10, .5)); | |
| while ($threerow = mysql_fetch_assoc($catthreesresult)) { | |
| $subsubcatName = $unspsc[$threerow['cat'] . "00000"] . $threerow['cat']; | |
| if ($threerow['cat'] == "") $subsubcatName = "null"; | |
| $subsubnodes[] = Array( | $subsubnodes[] = Array( |
| "label" => $subsubcatName, | "label" => $subsubcatName, |
| "amount" => $threerow['value'], | "amount" => $threerow['value'], |
| "color" => "#".$subCatColor | "color" => "#".$subCatColor |
| ); | ); |
| $subCatValue += $threerow['value']; | |
| } | } |
| $subnodes[] = Array( | $subnodes[] = Array( |
| "label" => $subcatName, | "label" => $subcatName, |
| "amount" => $tworow['value'], | "amount" => $subCatValue, |
| "color" => "#".$subCatColor, | "color" => "#".$subCatColor, |
| "children" => $subsubnodes | "children" => $subsubnodes |
| ); | ); |
| $catValue += $subCatValue; | |
| } | } |
| $nodes[] = Array( | $nodes[] = Array( |
| "label" => $catName, | "label" => $catName, |
| "amount" => $row['value'], | "amount" => $catValue, |
| "color" => "#".$catColor, | "color" => "#".$catColor, |
| "children" => $subnodes | "children" => $subnodes |
| ); | ); |
| $total+= $row['value']; | $total += $catValue; |
| } | } |
| $data = Array( | $data = Array( |
| "label" => "Australian Federal Government Contract Spending", | "label" => "Australian Federal Government Contract Spending", |
| "amount" => $total, | "amount" => $total, |
| "color" => "#000000", | "color" => "#000000", |
| "children" => $nodes | "children" => $nodes |
| ); | ); |
| echo "var data =eval('('+'" . json_encode($data) . "'+')');"; | echo "var data =eval('('+'" . json_encode($data) . "'+')');"; |
| ?> | ?> |
| new BubbleTree({ | new BubbleTree({ |
| data: data, | data: data, |
| container: '.bubbletree' | container: '.bubbletree' |
| }); | }); |
| }); | }); |
| </script> | </script> |
| </head> | </head> |
| <body> | <body> |
| <div class="bubbletree-wrapper"> | <div class="bubbletree-wrapper"> |
| <div class="bubbletree"></div> | <div class="bubbletree"></div> |
| </div> | </div> |
| </body> | </body> |
| </html> | </html> |