--- a/lib/graphs.inc.php +++ b/lib/graphs.inc.php @@ -28,10 +28,10 @@ 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'; + (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(); @@ -45,8 +45,8 @@ 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'; + 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(); @@ -59,6 +59,9 @@ echo "d2.push([ " . intval($delta['cnid']) . ", " . intval($delta['count']) . "]); \n"; }; + d1d2Graph(); +} +function d1d2Graph($time = false) { ?> var data = [ @@ -67,25 +70,37 @@ series: { lines: { show: true }, points: { show: true } - }, - bars: { show: true } + } + <?php if (!$time){ + echo ',bars: { show: true }'; + }?> }, { data: d2, series: { lines: { show: true }, points: { show: true } - }, - bars: { show: true } + } + <?php if (!$time){ + echo ',bars: { show: true }'; + } else { + echo ',yaxis: 2'; + } +?> }] - ]; + ; var options = { - series: { - stack: true, - }, + grid: { hoverable: true, clickable: true, labelMargin: 17 }, - selection: { mode: "x" } + selection: { mode: "x" }<?php if ($time){ + echo ', xaxis: { + mode: "time" + }';} else { + echo 'series: { + stack: true + },'; + } ?> }; placeholder.bind("plotselected", function (event, ranges) { @@ -98,15 +113,15 @@ 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); } @@ -133,7 +148,7 @@ opacity: 0.80 }).appendTo("body").fadeIn(200); } - + </script> <?php } @@ -142,7 +157,7 @@ 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 + $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(); @@ -201,11 +216,15 @@ ; function agencySuppliersGraph($agency) { + + global $conn; $agency = "AusAid"; $topX = 15; - $query = 'SELECT SUM(value) as val, supplierName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND "childCN" is null AND agencyName = \'$agency\' + $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(); @@ -216,9 +235,11 @@ } - $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'; - $query = $conn->prepare($query); + $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) { @@ -230,8 +251,10 @@ } function CnCGraph() { - $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"; + + 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()); @@ -273,23 +296,25 @@ "Consultancies", "Confidentialities" ); - $query = 'SELECT \'consultancy\', count(1) FROM `contractnotice` WHERE $agencyQ $supplierQ consultancy=\'Yes\' AND "childCN" is null;'; + $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;'; + $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() { - $query = 'SELECT YEAR(publishDate), MONTH(publishDate), -SUM(value) as val, count(1) as count FROM `contractnotice` -WHERE (YEAR(publishDate) >= 2008) + + 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 MONTH(publishDate), YEAR(publishDate) -ORDER BY YEAR(publishDate), MONTH(publishDate)'; +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(); @@ -308,31 +333,56 @@ } 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)'; - - $query = $conn->prepare($query); - $query->execute(); - databaseError($conn->errorInfo()); - - $dates = Array(); + + 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); - $month_name = date('F', mktime(0, 0, 0, $row[1])); - $dates[] = $month_name . " {$row[0]}"; - $counts[] = doubleval($row["count"]); - $values[] = doubleval($row["val"]); - } + $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"; + }; + foreach ($values as $key => $value) { + + echo "d2.push([ " . $dates[$key] . ", " . $value . "]); \n"; + }; + d1d2Graph(true); } function MethodCountGraph() { - $query = 'select procurementMethod, count(1) as count, SUM(value) as value, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice` + + 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(); @@ -367,7 +417,9 @@ } function MethodValueGraph() { - $query = "select procurementMethod, SUM(value) as value, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice` + + 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"; $methods = Array("Direct", "Open", "Select"); $dates = Array();