Change references to numeric child/parent IDs which can now be text
[contractdashboard.git] / lib / graphs.inc.php
blob:a/lib/graphs.inc.php -> blob:b/lib/graphs.inc.php
--- a/lib/graphs.inc.php
+++ b/lib/graphs.inc.php
@@ -1,8 +1,9 @@
 <?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]--> 
+    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> 
@@ -10,409 +11,488 @@
               <script language="javascript" type="text/javascript" src="lib/flot/jquery.flot.stack.js"></script> 
 
 ';
-$includedFlot = true;
-	}
+        $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";
-};
+    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"::integer - MOD("CNID"::integer,100)) as cnid 
+    from contractnotice where "CNID"::integer < 999999 
+    and "CNID" not like \'%-A%\' 
+    and "parentCN" is null) as a group by cnid order by cnid';
+    $query = $conn->prepare($query);
+    $query->execute();
+    $errors = $conn->errorInfo();
+    if ($errors[2] != "") {
+        echo("Export terminated, db error" . print_r($errors, true));
+        return Array();
+    }
+
+    foreach ($query->fetchAll() as $delta) {
+
+        echo "d1.push([ " . intval($delta['cnid']) . ", " . intval($delta['count']) . "]); \n";
+    };
+    $query = 'select cnid, count(*) from (select ("CNID"::integer - MOD("CNID"::integer,100)) as cnid
+    from contractnotice where "CNID" not like \'%-A%\' and "parentCN" is not null) 
+    as a group by cnid order by cnid';
+    $query = $conn->prepare($query);
+    $query->execute();
+    $errors = $conn->errorInfo();
+    if ($errors[2] != "") {
+        echo("Export terminated, db error" . print_r($errors, true));
+        return Array();
+    }
+
+    foreach ($query->fetchAll() as $delta) {
+
+        echo "d2.push([ " . intval($delta['cnid']) . ", " . intval($delta['count']) . "]); \n";
+    };
+    d1d2Graph();
+}
+function  d1d2Graph($time = false) {
+    ?>
+
+            var data = [
+                {
+                    data: d1,
+                    series: {
+                        lines: { show: true },
+                        points: { show: true }
+                    }
+                    <?php if (!$time){
+                    echo ',bars: { show: true }';
+                    }?>
+                },
+                {
+                    data: d2,
+                    series: {
+                        lines: { show: true },
+                        points: { show: true }
+                    }
+                      <?php if (!$time){
+                    echo ',bars: { show: true }';
+                    } else {
+                        echo ',yaxis: 2';
+                    }
 ?>
-
-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" }
+                }]
+            ;
+            var options = 
+                {
+               
+                grid: { hoverable: true, clickable: true, labelMargin: 17  },
+                selection: { mode: "x" }<?php if ($time){
+                    echo ', xaxis: {
+                mode: "time"
+            }';} else {
+                echo 'series: {
+                    stack: true
+                },';
+            } ?>
+            };
+
+            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() {
+
+    global $conn;
+    includeFlot();
+    $query = 'SELECT SUM("value") as val, MAX(contractnotice."agencyName") as agencyname FROM contractnotice join agency_nametoabn on contractnotice."agencyName"=agency_nametoabn."agencyName"  WHERE "childCN" is null
+GROUP BY abn ORDER BY SUM("value") DESC';
+    $query = $conn->prepare($query);
+    $query->execute();
+    $errors = $conn->errorInfo();
+    if ($errors[2] != "") {
+        echo("Export terminated, db error" . print_r($errors, true));
+        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) {
+
+    global $conn;
+    $agency = "AusAid";
+    $topX = 15;
+    $query = 'SELECT SUM(value) as val, "supplierName" FROM contractnotice WHERE extract ("YEAR" from "contractStart") >= :startYear AND "childCN" is null AND "agencyName" like :agency
+GROUP BY lower(supplierName) ORDER BY val DESC limit $topX';
+    $query = $conn->prepare($query);
+    $query->bindParam(":startYear", $startYear);
+    $query->bindParam(":agency", $agency);
+    $query->execute();
+    databaseError($conn->errorInfo());
+    $suppliers = Array();
+    $values = Array();
+    foreach ($query->fetchAll() as $row) {
+        $suppliers[] = ucsmart($row['supplierName']);
+        $values[] = doubleval($row["val"]);
+    }
+
+
+    $query = 'SELECT sum(a.val) as value, count(1) as count from (SELECT SUM(value) as val, "supplierName" FROM contractnotice WHERE (extract ("YEAR" from "contractStart") >= :startYear) AND "childCN" is null and "agencyName" like :agency
+GROUP BY lower("supplierName") ORDER BY val DESC LIMIT 18446744073709551610 OFFSET $topX) as a';
+    $query = $conn->prepare($query);
+    $query->bindParam(":startYear", $startYear);
+    $query->bindParam(":agency", $agency);
+    $query->execute();
+    databaseError($conn->errorInfo());
+    foreach ($query->fetchAll() as $row) {
+        if ($row['count'] > 0) {
+            $suppliers[] = $row['count'] . " other suppliers";
+            $values[] = doubleval($row[0]);
+        }
+    }
+}
+
+function CnCGraph() {
+
+    global $conn;
+    $query = 'select procurementMethod, count(1) as count, SUM(value) as val, 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 = $conn->prepare($query);
+    $query->execute();
+    databaseError($conn->errorInfo());
+
+    $methods = Array("Direct", "Open", "Select");
+    $dates = Array();
+    $methodCountsP = Array();
+    $methodCounts = Array();
+    $maxValue = 0;
+    foreach ($query->fetchAll() as $row) {
+        setlocale(LC_MONETARY, 'en_US');
+        if ($row['val'] > $maxValue)
+            $maxValue = $row['val'];
+        $date = date('F ', mktime(0, 0, 0, $row["month"])) . $row["year"];
+        if (array_search($date, $dates) === false) {
+            $dates[$row["year"] * 100 + $row["month"]] = $date;
+            ksort($dates);
+        }
+        $methodCountsP[$row["procurementMethod"]][$date] = $row["count"];
+    }
+    foreach ($methods as $method) {
+        foreach ($dates as $date) {
+            if ($methodCountsP[$method][$date] > 0)
+                $methodCounts[$method][] = $methodCountsP[$method][$date];
+            else
+                $methodCounts[$method][] = 0;
+        }
+    }
+    $dates = array_values($dates);
+    $totalRecords = array_sum_all($methodCounts);
+
+    function formatCallback($aVal) {
+        global $totalRecords;
+        return percent($aVal, $totalRecords) . "%";
+    }
+
+    $attributes = Array();
+    $attributeNames = Array(
+        "Consultancies",
+        "Confidentialities"
+    );
+    $query = 'SELECT \'consultancy\', count(1) FROM contractnotice WHERE $agencyQ $supplierQ consultancy=\'Yes\' AND "childCN" is null;';
+    $result = $conn->query($query);
+    $row = $result->fetch(PDO::FETCH_ASSOC);
+    $attributes[0] = $row[1];
+    $query = 'SELECT \'confidentiality\', count(1) FROM contractnotice WHERE $agencyQ $supplierQ (confidentialityContract=\'Yes\' OR confidentialityOutputs=\'Yes\') AND "childCN" is null;';
+    $result = $conn->query($query);
+    $row = $result->fetch(PDO::FETCH_ASSOC);
+    $attributes[1] = $row[1];
+}
+
+function ContractPublishedGraph() {
+
+    global $conn;
+    $query = 'SELECT extract(year from "contractStart"),extract(month from "contractStart"),
+SUM(value) as val, count(1) as count FROM contractnotice
+WHERE (extract(year from "contractStart") >= 2008)
+AND "childCN" is null
+GROUP BY extract(month from "contractStart"), extract(year from "contractStart") 
+ORDER BY extract(year from "contractStart"), extract(month from "contractStart")';
+
+    $query = $conn->prepare($query);
+    $query->execute();
+    databaseError($conn->errorInfo());
+
+    $dates = Array();
+    $values = Array();
+    foreach ($query->fetchAll() as $row) {
+        setlocale(LC_MONETARY, 'en_US');
+        $value = number_format(doubleval($row["val"]), 2);
+        $month_name = date('F', mktime(0, 0, 0, $row[1]));
+        $dates[] = $month_name . " {$row[0]}";
+        $counts[] = doubleval($row["count"]);
+        $values[] = doubleval($row["val"]);
+    }
+}
+
+function ContractStartingGraph() {
+
+    global $conn;
+    $query = 'SELECT extract (YEAR from "contractStart") as year, extract (MONTH from "contractStart") as month,
+SUM(value) as val, count(*) as count FROM contractnotice
+WHERE extract (YEAR from "contractStart") >= 2008
+AND "childCN" is null 
+GROUP BY extract (MONTH from "contractStart"), extract (YEAR from "contractStart") 
+ORDER BY extract (YEAR from "contractStart"), extract (MONTH from"contractStart")';
+
+    $query = $conn->prepare($query);
+    $query->execute();
+    databaseError($conn->errorInfo());
+$dates = Array();
+    $counts = Array();
+    $values = Array();
+    foreach ($query->fetchAll() as $row) {
+        setlocale(LC_MONETARY, 'en_US');
+        $value = number_format(doubleval($row["val"]), 2);
+        $date = mktime(0, 0, 0, $row["month"],1,$row["year"])*1000;
+        if ($row["count"] > 1) {
+        $dates[] = $date;
+        $counts[] = $row["count"];
+        $values[] = $row["val"];
+        }
+    }
+    includeFlot();
+    ?>
+    <center><div id="cstart" style="width:900px;height:550px"></div></center>
+    <script type="text/javascript"> 
+        var placeholder = $("#cstart");
+        $(function () {
+
+            var d1 = [];
+            var d2 = [];
+    <?php
+    foreach ($counts as $key => $count) {
+
+        echo "d1.push([ " . $dates[$key] . ", " . $count . "]); \n";
     };
-
-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() {
-	
-	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" is null AND agencyName = \'$agency\'
-GROUP BY lower(supplierName) ORDER BY val DESC limit $topX';
-$result = mysql_query($query);
-$suppliers = Array();
-$values = Array();
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
-  $suppliers[] = ucsmart($row['supplierName']);
-  $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, 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) {
-  $suppliers[] = $row['count'] . " other suppliers";
-  $values[] = doubleval($row[0]);
-}
-}
-mysql_free_result($result);
-}
-
-function CnCGraph() {
-$query = "select procurementMethod, count(1) as count, SUM(value) as val, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice`
+    foreach ($values as $key => $value) {
+
+        echo "d2.push([ " . $dates[$key] . ", " . $value . "]); \n";
+    };    
+    d1d2Graph(true);
+}
+
+function MethodCountGraph() {
+
+    global $conn;
+    $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';
+    $methods = Array("Direct", "Open", "Select");
+    $dates = Array();
+    $methodCountsP = Array();
+    $methodCounts = Array();
+    $maxValue = 0;
+    $query = $conn->prepare($query);
+    $query->execute();
+    databaseError($conn->errorInfo());
+
+    foreach ($query->fetchAll() as $row) {
+        setlocale(LC_MONETARY, 'en_US');
+        if ($row['value'] > $maxValue)
+            $maxValue = $row['value'];
+        $date = date('F ', mktime(0, 0, 0, $row["month"])) . $row["year"];
+        if (array_search($date, $dates) === false) {
+            $dates[$row["year"] * 100 + $row["month"]] = $date;
+            ksort($dates);
+        }
+        $methodCountsP[$row["procurementMethod"]][$date] = $row["count"];
+    }
+    foreach ($methods as $method) {
+        foreach ($dates as $date) {
+            if ($methodCountsP[$method][$date] > 0)
+                $methodCounts[$method][] = $methodCountsP[$method][$date];
+            else
+                $methodCounts[$method][] = 0;
+        }
+    }
+    $dates = array_values($dates);
+    $totalRecords = array_sum_all($methodCounts);
+}
+
+function MethodValueGraph() {
+
+    global $conn;
+    $query = "select procurementMethod, 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();
-$methodCountsP = Array();
-$methodCounts = Array();
-$maxValue = 0;
-while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
-  setlocale(LC_MONETARY, 'en_US');
-  if ($row['val'] > $maxValue) $maxValue = $row['val'];
-  $date = date( 'F ', mktime(0, 0, 0, $row["month"]) ). $row["year"];
-  if (array_search($date,$dates) === false ) {
-    $dates[$row["year"]*100 + $row["month"]] = $date;
-    ksort($dates);
-  }
-  $methodCountsP[$row["procurementMethod"]][$date] = $row["count"];
-
-}
-foreach ($methods as $method) {
-    foreach($dates as $date) {
-        if ($methodCountsP[$method][$date] > 0) $methodCounts[$method][] = $methodCountsP[$method][$date];
-        else $methodCounts[$method][] = 0;
-    }
-}
-$dates = array_values($dates);
-$totalRecords = array_sum_all($methodCounts);
-mysql_free_result($result);
-
-
-function formatCallback($aVal) {
-  global $totalRecords;
-  return percent($aVal, $totalRecords) . "%";
-}
-$attributes = Array();
-$attributeNames = Array(
-  "Consultancies",
-  "Confidentialities"
-);
-$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" is null;';
-$result = mysql_query($query);
-$row = mysql_fetch_array($result, MYSQL_BOTH);
-$attributes[1] = $row[1];
-mysql_free_result($result);
-	
-}
-function ContractPublishedGraph() {
-	$query = 'SELECT YEAR(publishDate), MONTH(publishDate),
-SUM(value) as val, count(1) as count FROM `contractnotice`
-WHERE (YEAR(publishDate) >= 2008)
-AND "childCN" is null
-GROUP BY MONTH(publishDate), YEAR(publishDate) 
-ORDER BY YEAR(publishDate), MONTH(publishDate)';
-
-$result = mysql_query($query);
-$dates = Array();
-$values = Array();
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
-setlocale(LC_MONETARY, 'en_US');
-$value = number_format(doubleval($row["val"]),2);
-$month_name = date( 'F', mktime(0, 0, 0, $row[1]) );
-	$dates[] = $month_name." {$row[0]}";
-	$counts[] = doubleval($row["count"]);
-	$values[] = doubleval($row["val"]);
-}
-mysql_free_result($result);
-
-}
-function ContractStartingGraph() {
-	$query = 'SELECT YEAR(contractStart), MONTH(contractStart),
-SUM(value) as val, count(1) as count FROM `contractnotice`
-WHERE (YEAR(contractStart) >= 2008)
-AND "childCN" is null
-GROUP BY MONTH(contractStart), YEAR(contractStart) 
-ORDER BY YEAR(contractStart), MONTH(contractStart)';
-
-$result = mysql_query($query);
-$dates = Array();
-$values = Array();
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
-setlocale(LC_MONETARY, 'en_US');
-$value = number_format(doubleval($row["val"]),2);
-$month_name = date( 'F', mktime(0, 0, 0, $row[1]) );
-	$dates[] = $month_name." {$row[0]}";
-	$counts[] = doubleval($row["count"]);
-	$values[] = doubleval($row["val"]);
-}
-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';
-$result = mysql_query($query);
-$methods = Array("Direct","Open","Select");
-$dates = Array();
-$methodCountsP = Array();
-$methodCounts = Array();
-$maxValue = 0;
-while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
-  setlocale(LC_MONETARY, 'en_US');
-  if ($row['value'] > $maxValue) $maxValue = $row['value'];
-  $date = date( 'F ', mktime(0, 0, 0, $row["month"]) ). $row["year"];
-  if (array_search($date,$dates) === false ) {
-    $dates[$row["year"]*100 + $row["month"]] = $date;
-    ksort($dates);
-  }
-  $methodCountsP[$row["procurementMethod"]][$date] = $row["count"];
-
-}
-foreach ($methods as $method) {
-    foreach($dates as $date) {
-        if ($methodCountsP[$method][$date] > 0) $methodCounts[$method][] = $methodCountsP[$method][$date];
-        else $methodCounts[$method][] = 0;
-    }
-}
-$dates = array_values($dates);
-$totalRecords = array_sum_all($methodCounts);
-mysql_free_result($result);
-}
-function MethodValueGraph() {
-	$query = "select procurementMethod, 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();
-$methodValuesP = Array();
-$methodValues = Array();
-$maxValue = 0;
-while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
-  setlocale(LC_MONETARY, 'en_US');
-  if ($row['value'] > $maxValue) $maxValue = $row['value'];
-  $date = date( 'F ', mktime(0, 0, 0, $row["month"]) ). $row["year"];
-  if (array_search($date,$dates) === false ) {
-    $dates[$row["year"]*100 + $row["month"]] = $date;
-    ksort($dates);
-  }
-  $methodValuesP[$row["procurementMethod"]][$date] = $row["value"];
-
-}
-foreach ($methods as $method) {
-    foreach($dates as $date) {
-        if ($methodValuesP[$method][$date] > 0) $methodValues[$method][] = $methodValuesP[$method][$date];
-        else $methodValues[$method][] = 0;
-    }
-}
-$dates = array_values($dates);
-$totalRecords = array_sum_all($methodValues);
-mysql_free_result($result);
-
-}
+    $methods = Array("Direct", "Open", "Select");
+    $dates = Array();
+    $methodValuesP = Array();
+    $methodValues = Array();
+    $maxValue = 0;
+    $query = $conn->prepare($query);
+    $query->execute();
+    databaseError($conn->errorInfo());
+
+    foreach ($query->fetchAll() as $row) {
+        setlocale(LC_MONETARY, 'en_US');
+        if ($row['value'] > $maxValue)
+            $maxValue = $row['value'];
+        $date = date('F ', mktime(0, 0, 0, $row["month"])) . $row["year"];
+        if (array_search($date, $dates) === false) {
+            $dates[$row["year"] * 100 + $row["month"]] = $date;
+            ksort($dates);
+        }
+        $methodValuesP[$row["procurementMethod"]][$date] = $row["value"];
+    }
+    foreach ($methods as $method) {
+        foreach ($dates as $date) {
+            if ($methodValuesP[$method][$date] > 0)
+                $methodValues[$method][] = $methodValuesP[$method][$date];
+            else
+                $methodValues[$method][] = 0;
+        }
+    }
+    $dates = array_values($dates);
+    $totalRecords = array_sum_all($methodValues);
+}
+
 function SuppliersGraph() {
 
-  global $conn;
-  includeFlot();
-$topX = 10;
-$suppliers = Array();
-$values = Array();
-
-
-$query = 'SELECT SUM("value") as value, MAX("supplierName") as supplierName, (
+    global $conn;
+    includeFlot();
+    $topX = 10;
+    $suppliers = Array();
+    $values = Array();
+
+
+    $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);