--- a/displayBubbletree.php +++ b/displayBubbletree.php @@ -1,4 +1,6 @@ - +<?php +include_once ("lib/common.inc.php"); +?> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head> @@ -17,9 +19,7 @@ <script type="text/javascript"> $(function() { - <?php -include_once ("lib/common.inc.php"); - +<?php include("lib/Color.php"); $color = new Lux_Color(); @@ -32,57 +32,57 @@ } $total = 0; -$catsresult = $conn->prepare('SELECT distinct substr( "categoryUNSPSC"::text, 0, 2 ) as cat +$catsresult = $conn->prepare('SELECT distinct substr( "categoryUNSPSC"::text, 0, 3 ) 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 +$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,1)][] = $tworow['cat']; + $cattwos[substr($tworow['cat'],0,2)][] = $tworow['cat']; } -$catthreesresult = $conn->prepare('SELECT substr( "categoryUNSPSC"::text, 0, 4 ) as cat , SUM( "value" ) as value +$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,2)][] = $threerow; + $catthrees[substr($threerow['cat'],0,4)][] = $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; + $catName = substr($unspsc[$catNum . "000000"],0,18); $subnodes = Array(); $catValue = 0; foreach ($cattwos[$catNum] as $twoCatNum) { - $subcatName = $unspsc[$twoCatNum. "000000"] .$twoCatNum; + $subcatName = $unspsc[$twoCatNum. "0000"]; $subsubnodes = Array(); - $subCatValue = 0; + $subCatValue = 1; $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']; + /*$subsubcatName = $unspsc[$threerow['cat'] . "00"]; $subsubnodes[] = Array( "label" => $subsubcatName, "amount" => $threerow['value'], - //"color" => "#".$subCatColor - ); + "color" => "#".$subCatColor + );*/ $subCatValue += $threerow['value']; } - $subnodes[] = Array( + /*$subnodes[] = Array( "label" => $subcatName, "amount" => $subCatValue, "color" => "#".$subCatColor, "children" => $subsubnodes - ); + );*/ $catValue += $subCatValue; } $nodes[] = Array( @@ -99,7 +99,7 @@ "color" => "#000000", "children" => $nodes ); -echo "var data =eval('('+'" . json_encode($data) . "'+')');"; +echo "var data = JSON.parse('" . str_replace(",",",",json_encode($data)) . "');"; ?> new BubbleTree({