--- a/lib/graphs.inc.php
+++ b/lib/graphs.inc.php
@@ -1,34 +1,198 @@
 <?php
+$includedFlot = false;
+function includeFlot() {
+	if (!$includedFlot) {
+		echo '   <!--[if lte IE 8]><script language="javascript" type="text/javascript" src="lib/flot/excanvas.min.js"></script><![endif]--> 
+ 
+    <script language="javascript" type="text/javascript" src="lib/flot/jquery.flot.js"></script> 
+        <script language="javascript" type="text/javascript" src="lib/flot/jquery.flot.pie.js"></script> 
+              <script language="javascript" type="text/javascript" src="lib/flot/jquery.flot.selection.js"></script> 
+              <script language="javascript" type="text/javascript" src="lib/flot/jquery.flot.stack.js"></script> 
+
+';
+$includedFlot = true;
+	}
+}
+
+function CNDistributionGraph() {
+	global $conn;
+	includeFlot();
+	 ?>
+	   <center><div id="cndist" style="width:900px;height:550px"></div></center>
+<script type="text/javascript"> 
+       var placeholder = $("#cndist");
+	 $(function () {
+
+ var d1 = [];
+ var d2 = [];
+<?php
+$query = 'select cnid, count(*) from (select ("CNID" - MOD("CNID",100)) as cnid from contractnotice where "CNID" < 999999 and "parentCN" is null) as a group by cnid order by cnid';
+$query = $conn->prepare($query);
+$query->execute();
+if (!$query) {
+	databaseError($conn->errorInfo());
+	return Array();
+}
+
+foreach ($query->fetchAll() as $delta) {
+
+	echo "d1.push([ ".intval($delta['cnid']).", ".intval($delta['count'])."]); \n";
+};
+$query = 'select cnid, count(*) from (select ("CNID" - MOD("CNID",100)) as cnid from contractnotice where "CNID" < 999999 and "parentCN" is not null) as a group by cnid order by cnid';
+$query = $conn->prepare($query);
+$query->execute();
+if (!$query) {
+	databaseError($conn->errorInfo());
+	return Array();
+}
+
+foreach ($query->fetchAll() as $delta) {
+
+	echo "d2.push([ ".intval($delta['cnid']).", ".intval($delta['count'])."]); \n";
+};
+?>
+
+var data = [
+       {
+            data: d1,
+series: {
+                   lines: { show: true },
+                   points: { show: true }
+               },
+            bars: { show: true }
+        },
+       {
+            data: d2,
+series: {
+                   lines: { show: true },
+                   points: { show: true }
+               },
+            bars: { show: true }
+        },
+    ];
+    var options = 
+    {
+series: {
+                stack: true,
+            },
+            grid: { hoverable: true, clickable: true, labelMargin: 17  },
+            selection: { mode: "x" }
+    };
+
+placeholder.bind("plotselected", function (event, ranges) {
+            plot = $.plot(placeholder, data,
+                          $.extend(true, {}, options, {
+                              xaxis: { min: ranges.xaxis.from, max: ranges.xaxis.to }
+                          }));
+    });
+    var previousPoint = null;
+    placeholder.bind("plothover", function (event, pos, item) {
+        $("#x").text(pos.x.toFixed(2));
+        $("#y").text(pos.y.toFixed(2));
+ 
+            if (item) {
+                if (previousPoint != item.dataIndex) {
+                    previousPoint = item.dataIndex;
+                    
+                    $("#tooltip").remove();
+                    var x = item.datapoint[0].toFixed(2),
+                        y = item.datapoint[1].toFixed(2);
+                    
+                    showTooltip(item.pageX, item.pageY,
+                                item.series.label + " of " + x + " = " + y);
+                }
+            }
+            else {
+                $("#tooltip").remove();
+                previousPoint = null;            
+            }
+    });
+
+    var plot = $.plot(placeholder, data,
+        options);
+ });
+
+   function showTooltip(x, y, contents) {
+        $('<div id="tooltip">' + contents + '</div>').css( {
+            position: 'absolute',
+            display: 'none',
+            top: y + 5,
+            left: x + 5,
+            border: '1px solid #fdd',
+            padding: '2px',
+            'background-color': '#fee',
+            opacity: 0.80
+        }).appendTo("body").fadeIn(200);
+    }
+ 
+</script> 
+<?php
+}
 
 function agenciesGraph() {
-	$topX = 15;
-$query = "SELECT SUM(value) as val, agencyName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND childCN = 0
-GROUP BY agencyName ORDER BY val DESC limit $topX";
-$result = mysql_query($query);
-$agencies = Array();
-$values = Array();
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
-  $agencies[] = $row['agencyName'];
-  $values[] = doubleval($row["val"]);
-}
-mysql_free_result($result);
-
-$query = "SELECT sum(a.val) as value, count(1) as count from (SELECT SUM(value) as val, agencyName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND childCN = 0
-GROUP BY agencyName ORDER BY val DESC LIMIT 18446744073709551610 OFFSET $topX) as a";
-$result = mysql_query($query);
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
-  $agencies[] = $row['count'] . " other agencies";
-  $values[] = doubleval($row[0]);
-}
-mysql_free_result($result);
-
+	
+	global $conn;
+	includeFlot();
+$query = 'SELECT SUM("value") as val, MAX(contractnotice."agencyName") as agencyname FROM contractnotice join agency on contractnotice."agencyName"=agency."agencyName"  WHERE "childCN" is null
+GROUP BY abn ORDER BY SUM("value") DESC';
+$query = $conn->prepare($query);
+$query->execute();
+if (!$query) {
+	databaseError($conn->errorInfo());
+	return Array();
+}
+?>
+<script type="text/javascript">
+$(function () {
+	// data
+	var data = [
+  <?php
+foreach ($query->fetchAll() as $row) {
+		echo '{ label: "'.$row['agencyname'].'",  data: '.doubleval($row["val"]).'},';
+  }
+    ?>
+	];
+	// GRAPH 7
+	$.plot($("#graph7"), data, 
+	{
+		series: {
+			pie: { 
+				show: true,
+          radius: 1,
+          tilt: 0.75,
+                label: {
+                    show: true,
+                    radius: 1,
+                    formatter: function(label, series){
+                        return '<div style="font-size:8pt;text-align:center;padding:2px;color:white;">'+label+': '+Math.round(series.percent)+'%</div>';
+                    },
+                    background: { 
+                        opacity: 0.5,
+                        color: '#000'
+                    }
+                  },
+				combine: {
+					color: '#999',
+					threshold: 0.012
+				}
+			}
+		},
+		legend: {
+			show: false
+		}
+	});
+});
+</script>
+    <div id="graph7" style="width:900px;height:550px"></div>
+
+<?php
 };
 
 function agencySuppliersGraph($agency) {
 	$agency = "AusAid";
 $topX = 15;
-$query = "SELECT SUM(value) as val, supplierName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND childCN = 0 AND agencyName = '$agency'
-GROUP BY lower(supplierName) ORDER BY val DESC limit $topX";
+$query = 'SELECT SUM(value) as val, supplierName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND "childCN" is null AND agencyName = \'$agency\'
+GROUP BY lower(supplierName) ORDER BY val DESC limit $topX';
 $result = mysql_query($query);
 $suppliers = Array();
 $values = Array();
@@ -38,8 +202,8 @@
 }
 mysql_free_result($result);
 
-$query = "SELECT sum(a.val) as value, count(1) as count from (SELECT SUM(value) as val, supplierName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND childCN = 0 and agencyName = '$agency'
-GROUP BY lower(supplierName) ORDER BY val DESC LIMIT 18446744073709551610 OFFSET $topX) as a";
+$query = 'SELECT sum(a.val) as value, count(1) as count from (SELECT SUM(value) as val, supplierName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND "childCN" is null and agencyName = \'$agency\'
+GROUP BY lower(supplierName) ORDER BY val DESC LIMIT 18446744073709551610 OFFSET $topX) as a';
 $result = mysql_query($query);
 while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
 if ($row['count'] > 0) {
@@ -90,11 +254,11 @@
   "Consultancies",
   "Confidentialities"
 );
-$query = "SELECT 'consultancy', count(1) FROM `contractnotice` WHERE $agencyQ $supplierQ consultancy='Yes' AND childCN = 0;";
+$query = 'SELECT \'consultancy\', count(1) FROM `contractnotice` WHERE $agencyQ $supplierQ consultancy=\'Yes\' AND "childCN" is null;';
 $result = mysql_query($query);
 $row = mysql_fetch_array($result, MYSQL_BOTH);
 $attributes[0] = $row[1];
-$query = "SELECT 'confidentiality', count(1) FROM `contractnotice` WHERE $agencyQ $supplierQ (confidentialityContract='Yes' OR confidentialityOutputs='Yes') AND childCN = 0;";
+$query = 'SELECT \'confidentiality\', count(1) FROM `contractnotice` WHERE $agencyQ $supplierQ (confidentialityContract=\'Yes\' OR confidentialityOutputs=\'Yes\') AND "childCN" is null;';
 $result = mysql_query($query);
 $row = mysql_fetch_array($result, MYSQL_BOTH);
 $attributes[1] = $row[1];
@@ -102,12 +266,12 @@
 	
 }
 function ContractPublishedGraph() {
-	$query = "SELECT YEAR(publishDate), MONTH(publishDate),
+	$query = 'SELECT YEAR(publishDate), MONTH(publishDate),
 SUM(value) as val, count(1) as count FROM `contractnotice`
 WHERE (YEAR(publishDate) >= 2008)
-AND childCN = 0
+AND "childCN" is null
 GROUP BY MONTH(publishDate), YEAR(publishDate) 
-ORDER BY YEAR(publishDate), MONTH(publishDate)";
+ORDER BY YEAR(publishDate), MONTH(publishDate)';
 
 $result = mysql_query($query);
 $dates = Array();
@@ -124,12 +288,12 @@
 
 }
 function ContractStartingGraph() {
-	$query = "SELECT YEAR(contractStart), MONTH(contractStart),
+	$query = 'SELECT YEAR(contractStart), MONTH(contractStart),
 SUM(value) as val, count(1) as count FROM `contractnotice`
 WHERE (YEAR(contractStart) >= 2008)
-AND childCN = 0
+AND "childCN" is null
 GROUP BY MONTH(contractStart), YEAR(contractStart) 
-ORDER BY YEAR(contractStart), MONTH(contractStart)";
+ORDER BY YEAR(contractStart), MONTH(contractStart)';
 
 $result = mysql_query($query);
 $dates = Array();
@@ -145,8 +309,8 @@
 mysql_free_result($result);
 }
 function MethodCountGraph() {
-	$query = "select procurementMethod, count(1) as count, SUM(value) as value, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice`
-where $agencyQ $supplierQ $standardQ group by procurementMethod,year,month order by procurementMethod,year,month";
+	$query = 'select procurementMethod, count(1) as count, SUM(value) as value, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice`
+where $agencyQ $supplierQ $standardQ group by procurementMethod,year,month order by procurementMethod,year,month';
 $result = mysql_query($query);
 $methods = Array("Direct","Open","Select");
 $dates = Array();
@@ -206,40 +370,63 @@
 
 }
 function SuppliersGraph() {
+
+  global $conn;
+  includeFlot();
 $topX = 10;
-$query = "SELECT value, supplierName
-FROM `contractnotice` WHERE (YEAR(contractStart) >= 2009) AND childCN = 0
-GROUP BY supplierABN ORDER BY value DESC limit $topX";
-$result = mysql_query($query);
 $suppliers = Array();
 $values = Array();
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
+
+
+$query = 'SELECT SUM("value") as value, MAX("supplierName") as supplierName, (
+ case when "supplierABN" != 0 THEN "supplierABN"::text ELSE "supplierName" END) as supplierID 
+FROM contractnotice
+WHERE "childCN" is null
+GROUP BY supplierID
+ORDER BY value DESC
+LIMIT '.$topX;
+$query = $conn->prepare($query);
+  $query->execute();
+  if (!$query) {
+    databaseError($conn->errorInfo());
+  }
+  foreach ($query->fetchAll() as $row) {
   setlocale(LC_MONETARY, 'en_US');
  // $value = number_format(doubleval($row["value"]) , 2);
   $suppliers[] = ucsmart($row[1]);
   $values[] = doubleval($row["value"]);
 }
-mysql_free_result($result);
-
-$query = "SELECT sum(a.value) as val, supplierCountry from (SELECT value, supplierName, supplierCountry
-FROM `contractnotice` WHERE (YEAR(contractStart) >= 2009) AND childCN = 0
-GROUP BY supplierName ORDER BY value LIMIT 18446744073709551610 OFFSET $topX) as a group by supplierCountry order by val DESC limit 5 offset 1";
-$result = mysql_query($query);
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
-  $suppliers[] = "Other suppliers in ".ucsmart($row["supplierCountry"]);
+
+$query = 'SELECT sum(a.svalue) as val, suppliercountry from (SELECT sum("value") as svalue,  max("supplierCountry") as suppliercountry
+FROM contractnotice WHERE "childCN" is null and "supplierCountry" NOT ILIKE \'Australia\'
+GROUP BY "supplierName" ORDER BY svalue LIMIT 18446744073 OFFSET 10) as a group by suppliercountry order by val DESC limit 10 ';
+$query = $conn->prepare($query);
+  $query->execute();
+  if (!$query) {
+    databaseError($conn->errorInfo());
+  }
+
+  foreach ($query->fetchAll() as $row) {
+  $suppliers[] = "Other suppliers in ".ucsmart($row["suppliercountry"]);
   $values[] = doubleval($row[0]);
 }
 
-mysql_free_result($result);
-
-$query = "SELECT sum(a.value) as val, TRUNCATE(supplierPostcode,-2) as postcode from (SELECT value, supplierName, supplierPostcode, supplierCountry
-FROM `contractnotice` WHERE (YEAR(contractStart) >= 2009) AND childCN = 0 
-GROUP BY supplierName ORDER BY value LIMIT 18446744073709551610 OFFSET $topX) as a
-WHERE (supplierCountry LIKE 'Australia' OR supplierCountry LIKE 'AUSTRALIA') AND supplierPostcode < 10000
-group by TRUNCATE(supplierPostcode,-2)
-order by val DESC";
-$result = mysql_query($query);
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
+
+$query = 'SELECT sum(a.value) as val, substring(
+supplierpostcode from 0 for 2) as postcode from (SELECT sum(value) as value, max("supplierPostcode") as supplierpostcode, max("supplierCountry") as suppliercountry
+FROM contractnotice WHERE "childCN" is null 
+GROUP BY "supplierABN" ORDER BY sum(value) LIMIT 1844674 OFFSET 10) as a
+WHERE (suppliercountry ILIKE \'Australia\')
+group by substring(
+supplierpostcode from 0 for 2)
+order by val DESC;';
+$query = $conn->prepare($query);
+  $query->execute();
+  if (!$query) {
+    databaseError($conn->errorInfo());
+  }
+
+  foreach ($query->fetchAll() as $row) {
     if ($row['postcode'][0] == 2 && $row['postcode'][1] == 6) $ACTvalue += $row[0];
     else if ($row['postcode'][0] == 2 || $row['postcode'][0] == 1) $NSWvalue += $row[0];
     else if ($row['postcode'][0] == 3 || $row['postcode'][0] == 8) $Vicvalue += $row[0];
@@ -249,7 +436,6 @@
     else if ($row['postcode'][0] == 7) $Tasvalue += $row[0];
     else if ($row['postcode'][0] == 0) $NTvalue += $row[0];
 }
-mysql_free_result($result);
 $suppliers[] = "Other suppliers in Australia - ACT";
 $values[] = doubleval($ACTvalue);
 $suppliers[] = "Other suppliers in Australia - NSW";
@@ -266,7 +452,54 @@
 $values[] = doubleval($SAvalue);
 $suppliers[] = "Other suppliers in Australia - Tasmania";
 $values[] = doubleval($Tasvalue);
-	
-}
+
 
 ?>
+<script type="text/javascript">
+$(function () {
+  // data
+  var data = [
+  <?php
+foreach ($suppliers as $key => $supplier) {
+    echo '{ label: "'.$supplier.'",  data: '.doubleval($values[$key]).'},';
+  }
+    ?>
+  ];
+  // GRAPH 7
+  $.plot($("#graph7"), data, 
+  {
+    series: {
+      pie: { 
+        show: true,
+          radius: 1,
+          tilt: 0.75,
+                label: {
+                    show: true,
+                    radius: 1,
+                    formatter: function(label, series){
+                        return '<div style="font-size:8pt;text-align:center;padding:2px;color:white;">'+label+': '+Math.round(series.percent)+'%</div>';
+                    },
+                    background: { 
+                        opacity: 0.5,
+                        color: '#000'
+                    }
+                  },
+        combine: {
+          color: '#999',
+          threshold: 0.012
+        }
+      }
+    },
+    legend: {
+      show: false
+    }
+  });
+});
+</script>
+    <div id="graph7" style="width:900px;height:550px"></div>
+
+<?php
+}
+
+?>
+