| <?php | <?php |
| $includedFlot = false; | $includedFlot = false; |
| function includeFlot() { | function includeFlot() { |
| if (!$includedFlot) { | if (!$includedFlot) { |
| echo ' <!--[if lte IE 8]><script language="javascript" type="text/javascript" src="lib/flot/excanvas.min.js"></script><![endif]--> | 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.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.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.selection.js"></script> |
| <script language="javascript" type="text/javascript" src="lib/flot/jquery.flot.stack.js"></script> | <script language="javascript" type="text/javascript" src="lib/flot/jquery.flot.stack.js"></script> |
| '; | '; |
| $includedFlot = true; | $includedFlot = true; |
| } | } |
| } | } |
| function CNDistributionGraph() { | function CNDistributionGraph() { |
| global $conn; | global $conn; |
| includeFlot(); | includeFlot(); |
| ?> | ?> |
| <center><div id="cndist" style="width:900px;height:550px"></div></center> | <center><div id="cndist" style="width:900px;height:550px"></div></center> |
| <script type="text/javascript"> | <script type="text/javascript"> |
| var placeholder = $("#cndist"); | var placeholder = $("#cndist"); |
| $(function () { | $(function () { |
| var d1 = []; | var d1 = []; |
| var d2 = []; | var d2 = []; |
| <?php | <?php |
| $query = 'select cnid, count(*) from | $query = 'select cnid, count(*) from |
| (select ("CNID"::integer - MOD("CNID"::integer,100)) as cnid | (select ("CNID"::integer - MOD("CNID"::integer,100)) as cnid |
| from contractnotice where "CNID"::integer < 999999 | from contractnotice where "CNID"::integer < 999999 |
| and "CNID" not like \'%-A%\' | and "CNID" not like \'%-A%\' |
| and "parentCN" is null) as a group by cnid order by cnid'; | and "parentCN" is null) as a group by cnid order by cnid'; |
| $query = $conn->prepare($query); | $query = $conn->prepare($query); |
| $query->execute(); | $query->execute(); |
| $errors = $conn->errorInfo(); | $errors = $conn->errorInfo(); |
| if ($errors[2] != "") { | if ($errors[2] != "") { |
| echo("Export terminated, db error" . print_r($errors, true)); | echo("Export terminated, db error" . print_r($errors, true)); |
| return Array(); | return Array(); |
| } | } |
| foreach ($query->fetchAll() as $delta) { | foreach ($query->fetchAll() as $delta) { |
| echo "d1.push([ " . intval($delta['cnid']) . ", " . intval($delta['count']) . "]); \n"; | echo "d1.push([ " . intval($delta['cnid']) . ", " . intval($delta['count']) . "]); \n"; |
| }; | }; |
| $query = 'select cnid, count(*) from (select ("CNID"::integer - MOD("CNID"::integer,100)) as cnid | $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) | from contractnotice where "CNID" not like \'%-A%\' and "parentCN" is not null) |
| as a group by cnid order by cnid'; | as a group by cnid order by cnid'; |
| $query = $conn->prepare($query); | $query = $conn->prepare($query); |
| $query->execute(); | $query->execute(); |
| $errors = $conn->errorInfo(); | $errors = $conn->errorInfo(); |
| if ($errors[2] != "") { | if ($errors[2] != "") { |
| echo("Export terminated, db error" . print_r($errors, true)); | echo("Export terminated, db error" . print_r($errors, true)); |
| return Array(); | return Array(); |
| } | } |
| foreach ($query->fetchAll() as $delta) { | foreach ($query->fetchAll() as $delta) { |
| echo "d2.push([ " . intval($delta['cnid']) . ", " . intval($delta['count']) . "]); \n"; | echo "d2.push([ " . intval($delta['cnid']) . ", " . intval($delta['count']) . "]); \n"; |
| }; | }; |
| d1d2Graph(); | d1d2Graph(); |
| } | } |
| function d1d2Graph($time = false) { | function d1d2Graph($time = false) { |
| ?> | ?> |
| var data = [ | var data = [ |
| { | { |
| data: d1, | data: d1, |
| series: { | series: { |
| lines: { show: true }, | lines: { show: true }, |
| points: { show: true } | points: { show: true } |
| } | } |
| <?php if (!$time){ | <?php if (!$time){ |
| echo ',bars: { show: true }'; | echo ',bars: { show: true }'; |
| }?> | }?> |
| }, | }, |
| { | { |
| data: d2, | data: d2, |
| series: { | series: { |
| lines: { show: true }, | lines: { show: true }, |
| points: { show: true } | points: { show: true } |
| } | } |
| <?php if (!$time){ | <?php if (!$time){ |
| echo ',bars: { show: true }'; | echo ',bars: { show: true }'; |
| } else { | } else { |
| echo ',yaxis: 2'; | echo ',yaxis: 2'; |
| } | } |
| ?> | ?> |
| }] | }] |
| ; | ; |
| var options = | var options = |
| { | { |
| grid: { hoverable: true, clickable: true, labelMargin: 17 }, | grid: { hoverable: true, clickable: true, labelMargin: 17 }, |
| selection: { mode: "x" } <?php if ($time){ | selection: { mode: "x" } <?php if ($time){ |
| echo ', xaxis: { | echo ', xaxis: { |
| mode: "time" | mode: "time" |
| }';} else { | }';} else { |
| echo ', series: { | echo ', series: { |
| stack: true | stack: true |
| },'; | },'; |
| } ?> | } ?> |
| }; | }; |
| placeholder.bind("plotselected", function (event, ranges) { | placeholder.bind("plotselected", function (event, ranges) { |
| plot = $.plot(placeholder, data, | plot = $.plot(placeholder, data, |
| $.extend(true, {}, options, { | $.extend(true, {}, options, { |
| xaxis: { min: ranges.xaxis.from, max: ranges.xaxis.to } | xaxis: { min: ranges.xaxis.from, max: ranges.xaxis.to } |
| })); | })); |
| }); | }); |
| var previousPoint = null; | var previousPoint = null; |
| placeholder.bind("plothover", function (event, pos, item) { | placeholder.bind("plothover", function (event, pos, item) { |
| $("#x").text(pos.x.toFixed(2)); | $("#x").text(pos.x.toFixed(2)); |
| $("#y").text(pos.y.toFixed(2)); | $("#y").text(pos.y.toFixed(2)); |
| if (item) { | if (item) { |
| if (previousPoint != item.dataIndex) { | if (previousPoint != item.dataIndex) { |
| previousPoint = item.dataIndex; | previousPoint = item.dataIndex; |
| $("#tooltip").remove(); | $("#tooltip").remove(); |
| var x = item.datapoint[0].toFixed(2), | var x = item.datapoint[0].toFixed(2), |
| y = item.datapoint[1].toFixed(2); | y = item.datapoint[1].toFixed(2); |
| showTooltip(item.pageX, item.pageY, | showTooltip(item.pageX, item.pageY, |
| item.series.label + " of " + x + " = " + y); | item.series.label + " of " + x + " = " + y); |
| } | } |
| } | } |
| else { | else { |
| $("#tooltip").remove(); | $("#tooltip").remove(); |
| previousPoint = null; | previousPoint = null; |
| } | } |
| }); | }); |
| var plot = $.plot(placeholder, data, | var plot = $.plot(placeholder, data, |
| options); | options); |
| }); | }); |
| function showTooltip(x, y, contents) { | function showTooltip(x, y, contents) { |
| $('<div id="tooltip">' + contents + '</div>').css( { | $('<div id="tooltip">' + contents + '</div>').css( { |
| position: 'absolute', | position: 'absolute', |
| display: 'none', | display: 'none', |
| top: y + 5, | top: y + 5, |
| left: x + 5, | left: x + 5, |
| border: '1px solid #fdd', | border: '1px solid #fdd', |
| padding: '2px', | padding: '2px', |
| 'background-color': '#fee', | 'background-color': '#fee', |
| opacity: 0.80 | opacity: 0.80 |
| }).appendTo("body").fadeIn(200); | }).appendTo("body").fadeIn(200); |
| } | } |
| </script> | </script> |
| <?php | <?php |
| } | } |
| function agenciesGraph() { | function agenciesGraph() { |
| global $conn, $yearQ, $supplierParts,$supplierQ, $supplierABN, $supplierName; | global $conn, $yearQ, $supplierParts,$supplierQ, $supplierABN, $supplierName; |
| includeFlot(); | includeFlot(); |
| $query = 'SELECT SUM("value") as val, MAX(contractnotice."agencyName") as agencyname FROM contractnotice join agency_nametoabn on contractnotice."agencyName"=agency_nametoabn."agencyName" | $query = 'SELECT SUM("value") as val, MAX(contractnotice."agencyName") as agencyname FROM contractnotice join agency_nametoabn on contractnotice."agencyName"=agency_nametoabn."agencyName" |
| WHERE ' .$yearQ . ' '.$supplierQ.' "childCN" is null | WHERE ' .$yearQ . ' '.$supplierQ.' "childCN" is null |
| GROUP BY abn ORDER BY SUM("value") DESC'; | GROUP BY abn ORDER BY SUM("value") DESC'; |
| $query = $conn->prepare($query); | $query = $conn->prepare($query); |
| if (count($supplierParts) > 0) { | if (count($supplierParts) > 0) { |
| if ($supplierParts[0] > 0) { | if ($supplierParts[0] > 0) { |
| $query->bindParam(":supplierABN", $supplierABN); | $query->bindParam(":supplierABN", $supplierABN); |
| } else { | } else { |
| $query->bindParam(":supplierName", $supplierName); | $query->bindParam(":supplierName", $supplierName); |
| } | } |
| } | } |
| $query->execute(); | $query->execute(); |
| $errors = $conn->errorInfo(); | $errors = $conn->errorInfo(); |
| if ($errors[2] != "") { | if ($errors[2] != "") { |
| echo("Export terminated, db error" . print_r($errors, true)); | echo("Export terminated, db error" . print_r($errors, true)); |
| return Array(); | return Array(); |
| } | } |
| ?> | ?> |
| <script type="text/javascript"> | <script type="text/javascript"> |
| $(function () { | $(function () { |
| // data | // data |
| var data = [ | var data = [ |
| <?php | <?php |
| foreach ($query->fetchAll() as $row) { | foreach ($query->fetchAll() as $row) { |
| echo '{ label: "' . $row['agencyname'] . '", data: ' . doubleval($row["val"]) . '},'; | echo '{ label: "' . $row['agencyname'] . '", data: ' . doubleval($row["val"]) . '},'; |
| } | } |
| ?> | ?> |
| ]; | ]; |
| // GRAPH 7 | // GRAPH 7 |
| $.plot($("#graph7"), data, | $.plot($("#graph7"), data, |
| { | { |
| series: { | series: { |
| pie: { | pie: { |
| show: true, | show: true, |
| radius: 1, | radius: 1, |
| tilt: 0.75, | tilt: 0.75, |
| label: { | label: { |
| show: true, | show: true, |
| radius: 1, | radius: 1, |
| formatter: function(label, series){ | formatter: function(label, series){ |
| return '<div style="font-size:8pt;text-align:center;padding:2px;color:white;">'+label+': '+Math.round(series.percent)+'%</div>'; | return '<div style="font-size:8pt;text-align:center;padding:2px;color:white;">'+label+': '+Math.round(series.percent)+'%</div>'; |
| }, | }, |
| background: { | background: { |
| opacity: 0.5, | opacity: 0.5, |
| color: '#000' | color: '#000' |
| } | } |
| }, | }, |
| combine: { | combine: { |
| color: '#999', | color: '#999', |
| threshold: 0.012 | threshold: 0.012 |
| } | } |
| } | } |
| }, | }, |
| legend: { | legend: { |
| show: false | show: false |
| } | } |
| }); | }); |
| }); | }); |
| </script> | </script> |
| <div id="graph7" style="width:900px;height:550px"></div> | <div id="graph7" style="width:900px;height:550px"></div> |
| <?php | <?php |
| } | } |
| ; | ; |
| function agencySuppliersGraph($agency) { | function agencySuppliersGraph($agency) { |
| global $conn,$startYear, $yearQ; | global $conn,$startYear, $yearQ; |
| includeFlot(); | includeFlot(); |
| $topX = 20; | $topX = 20; |
| $query = 'SELECT SUM(value) as val, mode("supplierName") "supplierName" FROM contractnotice WHERE ' .$yearQ . ' extract ("YEAR" from "contractStart") >= :startYear AND "childCN" is null AND "agencyName" like :agency | $query = 'SELECT SUM(value) as val, text_mode("supplierName") "supplierName" FROM contractnotice WHERE ' .$yearQ . ' extract ("YEAR" from "contractStart") >= :startYear AND "childCN" is null AND "agencyName" like :agency |
| GROUP BY lower("supplierName") ORDER BY val DESC limit '.$topX; | GROUP BY lower("supplierName") ORDER BY val DESC limit '.$topX; |
| $query = $conn->prepare($query); | $query = $conn->prepare($query); |
| $query->bindParam(":startYear", $startYear); | $query->bindParam(":startYear", $startYear); |
| $query->bindParam(":agency", $agency); | $query->bindParam(":agency", $agency); |
| $query->execute(); | $query->execute(); |
| databaseError($conn->errorInfo()); | databaseError($conn->errorInfo()); |
| $suppliers = Array(); | $suppliers = Array(); |
| $values = Array(); | $values = Array(); |
| foreach ($query->fetchAll() as $row) { | foreach ($query->fetchAll() as $row) { |
| $suppliers[] = ucsmart($row['supplierName']); | $suppliers[] = ucsmart($row['supplierName']); |
| $values[] = doubleval($row["val"]); | $values[] = doubleval($row["val"]); |
| } | } |
| $query = 'SELECT sum(a.val) as value, count(1) as count from (SELECT SUM(value) as val, mode("supplierName") FROM contractnotice WHERE ' .$yearQ . '(extract ("YEAR" from "contractStart") >= :startYear) AND "childCN" is null and "agencyName" like :agency | $query = 'SELECT sum(a.val) as value, count(1) as count from (SELECT SUM(value) as val, text_mode("supplierName") FROM contractnotice WHERE ' .$yearQ . '(extract ("YEAR" from "contractStart") >= :startYear) AND "childCN" is null and "agencyName" like :agency |
| GROUP BY lower("supplierName") ORDER BY val DESC LIMIT 184467440 OFFSET '.$topX.') as a'; | GROUP BY lower("supplierName") ORDER BY val DESC LIMIT 184467440 OFFSET '.$topX.') as a'; |
| $query = $conn->prepare($query); | $query = $conn->prepare($query); |
| $query->bindParam(":startYear", $startYear); | $query->bindParam(":startYear", $startYear); |
| $query->bindParam(":agency", $agency); | $query->bindParam(":agency", $agency); |
| $query->execute(); | $query->execute(); |
| databaseError($conn->errorInfo()); | databaseError($conn->errorInfo()); |
| foreach ($query->fetchAll() as $row) { | foreach ($query->fetchAll() as $row) { |
| if ($row['count'] > 0) { | if ($row['count'] > 0) { |
| $suppliers[] = $row['count'] . " other suppliers"; | $suppliers[] = $row['count'] . " other suppliers"; |
| $values[] = doubleval($row[0]); | $values[] = doubleval($row[0]); |
| } | } |
| } | } |
| ?> | ?> |
| <script type="text/javascript"> | <script type="text/javascript"> |
| $(function () { | $(function () { |
| // data | // data |
| var data = [ | var data = [ |
| <?php | <?php |
| foreach ($suppliers as $key => $supplier) { | foreach ($suppliers as $key => $supplier) { |
| echo '{ label: "' . $supplier . '", data: ' . doubleval($values[$key]) . '},'; | echo '{ label: "' . $supplier . '", data: ' . doubleval($values[$key]) . '},'; |
| } | } |
| ?> | ?> |
| ]; | ]; |
| // GRAPH 7 | // GRAPH 7 |
| $.plot($("#graph7"), data, | $.plot($("#graph7"), data, |
| { | { |
| series: { | series: { |
| pie: { | pie: { |
| show: true, | show: true, |
| radius: 1, | radius: 1, |
| tilt: 0.75, | tilt: 0.75, |
| label: { | label: { |
| show: true, | show: true, |
| radius: 1, | radius: 1, |
| formatter: function(label, series){ | formatter: function(label, series){ |
| return '<div style="font-size:8pt;text-align:center;padding:2px;color:white;">'+label+': '+Math.round(series.percent)+'%</div>'; | return '<div style="font-size:8pt;text-align:center;padding:2px;color:white;">'+label+': '+Math.round(series.percent)+'%</div>'; |
| }, | }, |
| background: { | background: { |
| opacity: 0.5, | opacity: 0.5, |
| color: '#000' | color: '#000' |
| } | } |
| }, | }, |
| combine: { | combine: { |
| color: '#999', | color: '#999', |
| threshold: 0.012 | threshold: 0.012 |
| } | } |
| } | } |
| }, | }, |
| legend: { | legend: { |
| show: false | show: false |
| } | } |
| }); | }); |
| }); | }); |
| </script> | </script> |
| <div id="graph7" style="width:900px;height:550px"></div> | <div id="graph7" style="width:900px;height:550px"></div> |
| <?php | <?php |
| } | } |
| function CnCGraph() { | function CnCGraph() { |
| global $conn; | global $conn; |
| $query = 'select procurementMethod, count(1) as count, SUM(value) as val, MONTH(contractStart) as month, YEAR(contractStart) as year from contractnotice | $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'; | where $agencyQ $supplierQ $standardQ group by procurementMethod,year,month order by procurementMethod,year,month'; |
| $query = $conn->prepare($query); | $query = $conn->prepare($query); |
| $query->execute(); | $query->execute(); |
| databaseError($conn->errorInfo()); | databaseError($conn->errorInfo()); |
| $methods = Array("Direct", "Open", "Select"); | $methods = Array("Direct", "Open", "Select"); |
| $dates = Array(); | $dates = Array(); |
| $methodCountsP = Array(); | $methodCountsP = Array(); |
| $methodCounts = Array(); | $methodCounts = Array(); |
| $maxValue = 0; | $maxValue = 0; |
| foreach ($query->fetchAll() as $row) { | foreach ($query->fetchAll() as $row) { |
| setlocale(LC_MONETARY, 'en_US'); | setlocale(LC_MONETARY, 'en_US'); |
| if ($row['val'] > $maxValue) | if ($row['val'] > $maxValue) |
| $maxValue = $row['val']; | $maxValue = $row['val']; |
| $date = date('F ', mktime(0, 0, 0, $row["month"])) . $row["year"]; | $date = date('F ', mktime(0, 0, 0, $row["month"])) . $row["year"]; |
| if (array_search($date, $dates) === false) { | if (array_search($date, $dates) === false) { |
| $dates[$row["year"] * 100 + $row["month"]] = $date; | $dates[$row["year"] * 100 + $row["month"]] = $date; |
| ksort($dates); | ksort($dates); |
| } | } |
| $methodCountsP[$row["procurementMethod"]][$date] = $row["count"]; | $methodCountsP[$row["procurementMethod"]][$date] = $row["count"]; |
| } | } |
| foreach ($methods as $method) { | foreach ($methods as $method) { |
| foreach ($dates as $date) { | foreach ($dates as $date) { |
| if ($methodCountsP[$method][$date] > 0) | if ($methodCountsP[$method][$date] > 0) |
| $methodCounts[$method][] = $methodCountsP[$method][$date]; | $methodCounts[$method][] = $methodCountsP[$method][$date]; |
| else | else |
| $methodCounts[$method][] = 0; | $methodCounts[$method][] = 0; |
| } | } |
| } | } |
| $dates = array_values($dates); | $dates = array_values($dates); |
| $totalRecords = array_sum_all($methodCounts); | $totalRecords = array_sum_all($methodCounts); |
| function formatCallback($aVal) { | function formatCallback($aVal) { |
| global $totalRecords; | global $totalRecords; |
| return percent($aVal, $totalRecords) . "%"; | return percent($aVal, $totalRecords) . "%"; |
| } | } |
| $attributes = Array(); | $attributes = Array(); |
| $attributeNames = Array( | $attributeNames = Array( |
| "Consultancies", | "Consultancies", |
| "Confidentialities" | "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); | $result = $conn->query($query); |
| $row = $result->fetch(PDO::FETCH_ASSOC); | $row = $result->fetch(PDO::FETCH_ASSOC); |
| $attributes[0] = $row[1]; | $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); | $result = $conn->query($query); |
| $row = $result->fetch(PDO::FETCH_ASSOC); | $row = $result->fetch(PDO::FETCH_ASSOC); |
| $attributes[1] = $row[1]; | $attributes[1] = $row[1]; |
| } | } |
| function ContractPublishedGraph() { | function ContractPublishedGraph() { |
| global $conn, $yearQ; | global $conn, $yearQ; |
| $query = 'SELECT extract(year from "contractStart"),extract(month from "contractStart"), | $query = 'SELECT extract(year from "contractStart"),extract(month from "contractStart"), |
| SUM(value) as val, count(1) as count FROM contractnotice | SUM(value) as val, count(1) as count FROM contractnotice |
| WHERE '.$yearQ.' (extract(year from "contractStart") >= 2008) | WHERE '.$yearQ.' (extract(year from "contractStart") >= 2008) |
| AND "childCN" is null | AND "childCN" is null |
| GROUP BY extract(month from "contractStart"), extract(year from "contractStart") | GROUP BY extract(month from "contractStart"), extract(year from "contractStart") |
| ORDER BY extract(year from "contractStart"), extract(month from "contractStart")'; | ORDER BY extract(year from "contractStart"), extract(month from "contractStart")'; |
| $query = $conn->prepare($query); | $query = $conn->prepare($query); |
| $query->execute(); | $query->execute(); |
| databaseError($conn->errorInfo()); | databaseError($conn->errorInfo()); |
| $dates = Array(); | $dates = Array(); |
| $values = Array(); | $values = Array(); |
| foreach ($query->fetchAll() as $row) { | foreach ($query->fetchAll() as $row) { |
| setlocale(LC_MONETARY, 'en_US'); | setlocale(LC_MONETARY, 'en_US'); |
| $value = number_format(doubleval($row["val"]), 2); | $value = number_format(doubleval($row["val"]), 2); |
| $month_name = date('F', mktime(0, 0, 0, $row[1])); | $month_name = date('F', mktime(0, 0, 0, $row[1])); |
| $dates[] = $month_name . " {$row[0]}"; | $dates[] = $month_name . " {$row[0]}"; |
| $counts[] = doubleval($row["count"]); | $counts[] = doubleval($row["count"]); |
| $values[] = doubleval($row["val"]); | $values[] = doubleval($row["val"]); |
| } | } |
| } | } |
| function ContractStartingGraph() { | function ContractStartingGraph() { |
| global $conn, $yearQ; | global $conn, $yearQ; |
| $query = 'SELECT extract (YEAR from "contractStart") as year, extract (MONTH from "contractStart") as month, | $query = 'SELECT extract (YEAR from "contractStart") as year, extract (MONTH from "contractStart") as month, |
| SUM(value) as val, count(*) as count FROM contractnotice | SUM(value) as val, count(*) as count FROM contractnotice |
| WHERE '.$yearQ.' extract (YEAR from "contractStart") >= 2008 | WHERE '.$yearQ.' extract (YEAR from "contractStart") >= 2008 |
| AND "childCN" is null | AND "childCN" is null |
| GROUP BY extract (MONTH from "contractStart"), extract (YEAR from "contractStart") | GROUP BY extract (MONTH from "contractStart"), extract (YEAR from "contractStart") |
| ORDER BY extract (YEAR from "contractStart"), extract (MONTH from"contractStart")'; | ORDER BY extract (YEAR from "contractStart"), extract (MONTH from"contractStart")'; |
| $query = $conn->prepare($query); | $query = $conn->prepare($query); |
| $query->execute(); | $query->execute(); |
| databaseError($conn->errorInfo()); | databaseError($conn->errorInfo()); |
| $dates = Array(); | $dates = Array(); |
| $counts = Array(); | $counts = Array(); |
| $values = Array(); | $values = Array(); |
| foreach ($query->fetchAll() as $row) { | foreach ($query->fetchAll() as $row) { |
| setlocale(LC_MONETARY, 'en_US'); | setlocale(LC_MONETARY, 'en_US'); |
| $value = number_format(doubleval($row["val"]), 2); | $value = number_format(doubleval($row["val"]), 2); |
| $date = mktime(0, 0, 0, $row["month"],1,$row["year"])*1000; | $date = mktime(0, 0, 0, $row["month"],1,$row["year"])*1000; |
| if ($row["count"] > 1) { | if ($row["count"] > 1) { |
| $dates[] = $date; | $dates[] = $date; |
| $counts[] = $row["count"]; | $counts[] = $row["count"]; |
| $values[] = $row["val"]; | $values[] = $row["val"]; |
| } | } |
| } | } |
| includeFlot(); | includeFlot(); |
| ?> | ?> |
| <center><div id="cstart" style="width:900px;height:550px"></div></center> | <center><div id="cstart" style="width:900px;height:550px"></div></center> |
| <script type="text/javascript"> | <script type="text/javascript"> |
| var placeholder = $("#cstart"); | var placeholder = $("#cstart"); |
| $(function () { | $(function () { |
| var d1 = []; | var d1 = []; |
| var d2 = []; | var d2 = []; |
| <?php | <?php |
| foreach ($counts as $key => $count) { | foreach ($counts as $key => $count) { |
| echo "d1.push([ " . $dates[$key] . ", " . $count . "]); \n"; | echo "d1.push([ " . $dates[$key] . ", " . $count . "]); \n"; |
| }; | }; |
| foreach ($values as $key => $value) { | foreach ($values as $key => $value) { |
| echo "d2.push([ " . $dates[$key] . ", " . $value . "]); \n"; | echo "d2.push([ " . $dates[$key] . ", " . $value . "]); \n"; |
| }; | }; |
| d1d2Graph(true); | d1d2Graph(true); |
| } | } |
| function MethodCountGraph() { | function MethodCountGraph() { |
| global $conn; | global $conn; |
| $query = 'select procurementMethod, count(1) as count, SUM(value) as value, MONTH(contractStart) as month, YEAR(contractStart) as year from contractnotice | $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'; | where $agencyQ $supplierQ $standardQ group by procurementMethod,year,month order by procurementMethod,year,month'; |
| $methods = Array("Direct", "Open", "Select"); | $methods = Array("Direct", "Open", "Select"); |
| $dates = Array(); | $dates = Array(); |
| $methodCountsP = Array(); | $methodCountsP = Array(); |
| $methodCounts = Array(); | $methodCounts = Array(); |
| $maxValue = 0; | $maxValue = 0; |
| $query = $conn->prepare($query); | $query = $conn->prepare($query); |
| $query->execute(); | $query->execute(); |
| databaseError($conn->errorInfo()); | databaseError($conn->errorInfo()); |
| foreach ($query->fetchAll() as $row) { | foreach ($query->fetchAll() as $row) { |
| setlocale(LC_MONETARY, 'en_US'); | setlocale(LC_MONETARY, 'en_US'); |
| if ($row['value'] > $maxValue) | if ($row['value'] > $maxValue) |
| $maxValue = $row['value']; | $maxValue = $row['value']; |
| $date = date('F ', mktime(0, 0, 0, $row["month"])) . $row["year"]; | $date = date('F ', mktime(0, 0, 0, $row["month"])) . $row["year"]; |
| if (array_search($date, $dates) === false) { | if (array_search($date, $dates) === false) { |
| $dates[$row["year"] * 100 + $row["month"]] = $date; | $dates[$row["year"] * 100 + $row["month"]] = $date; |
| ksort($dates); | ksort($dates); |
| } | } |
| $methodCountsP[$row["procurementMethod"]][$date] = $row["count"]; | $methodCountsP[$row["procurementMethod"]][$date] = $row["count"]; |
| } | } |
| foreach ($methods as $method) { | foreach ($methods as $method) { |
| foreach ($dates as $date) { | foreach ($dates as $date) { |
| if ($methodCountsP[$method][$date] > 0) | if ($methodCountsP[$method][$date] > 0) |
| $methodCounts[$method][] = $methodCountsP[$method][$date]; | $methodCounts[$method][] = $methodCountsP[$method][$date]; |
| else | else |
| $methodCounts[$method][] = 0; | $methodCounts[$method][] = 0; |
| } | } |
| } | } |
| $dates = array_values($dates); | $dates = array_values($dates); |
| $totalRecords = array_sum_all($methodCounts); | $totalRecords = array_sum_all($methodCounts); |
| } | } |
| function MethodValueGraph() { | function MethodValueGraph() { |
| global $conn; | global $conn; |
| $query = "select procurementMethod, SUM(value) as value, MONTH(contractStart) as month, YEAR(contractStart) as year from contractnotice | $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"; | where $agencyQ $supplierQ $standardQ group by procurementMethod,year,month order by procurementMethod,year,month"; |
| $methods = Array("Direct", "Open", "Select"); | $methods = Array("Direct", "Open", "Select"); |
| $dates = Array(); | $dates = Array(); |
| $methodValuesP = Array(); | $methodValuesP = Array(); |
| $methodValues = Array(); | $methodValues = Array(); |
| $maxValue = 0; | $maxValue = 0; |
| $query = $conn->prepare($query); | $query = $conn->prepare($query); |
| $query->execute(); | $query->execute(); |
| databaseError($conn->errorInfo()); | databaseError($conn->errorInfo()); |
| foreach ($query->fetchAll() as $row) { | foreach ($query->fetchAll() as $row) { |
| setlocale(LC_MONETARY, 'en_US'); | setlocale(LC_MONETARY, 'en_US'); |
| if ($row['value'] > $maxValue) | if ($row['value'] > $maxValue) |
| $maxValue = $row['value']; | $maxValue = $row['value']; |
| $date = date('F ', mktime(0, 0, 0, $row["month"])) . $row["year"]; | $date = date('F ', mktime(0, 0, 0, $row["month"])) . $row["year"]; |
| if (array_search($date, $dates) === false) { | if (array_search($date, $dates) === false) { |
| $dates[$row["year"] * 100 + $row["month"]] = $date; | $dates[$row["year"] * 100 + $row["month"]] = $date; |
| ksort($dates); | ksort($dates); |
| } | } |
| $methodValuesP[$row["procurementMethod"]][$date] = $row["value"]; | $methodValuesP[$row["procurementMethod"]][$date] = $row["value"]; |
| } | } |
| foreach ($methods as $method) { | foreach ($methods as $method) { |
| foreach ($dates as $date) { | foreach ($dates as $date) { |
| if ($methodValuesP[$method][$date] > 0) | if ($methodValuesP[$method][$date] > 0) |
| $methodValues[$method][] = $methodValuesP[$method][$date]; | $methodValues[$method][] = $methodValuesP[$method][$date]; |
| else | else |
| $methodValues[$method][] = 0; | $methodValues[$method][] = 0; |
| } | } |
| } | } |
| $dates = array_values($dates); | $dates = array_values($dates); |
| $totalRecords = array_sum_all($methodValues); | $totalRecords = array_sum_all($methodValues); |
| } | } |
| function SuppliersGraph() { | function SuppliersGraph() { |
| global $conn, $yearQ; | global $conn, $yearQ; |
| includeFlot(); | includeFlot(); |
| $topX = 10; | $topX = 10; |
| $suppliers = Array(); | $suppliers = Array(); |
| $values = Array(); | $values = Array(); |
| $query = 'SELECT SUM("value") as value, mode("supplierName") as supplierName, ( | $query = 'SELECT SUM("value") as value, text_mode("supplierName") as supplierName, ( |
| case when "supplierABN" != 0 THEN "supplierABN"::text ELSE "supplierName" END) as supplierID | case when "supplierABN" != 0 THEN "supplierABN"::text ELSE "supplierName" END) as supplierID |
| FROM contractnotice | FROM contractnotice |
| WHERE ' .$yearQ . ' "childCN" is null | WHERE ' .$yearQ . ' "childCN" is null |
| GROUP BY supplierID | GROUP BY supplierID |
| ORDER BY value DESC | ORDER BY value DESC |
| LIMIT ' . $topX; | LIMIT ' . $topX; |
| $query = $conn->prepare($query); | $query = $conn->prepare($query); |
| $query->execute(); | $query->execute(); |
| databaseError($conn->errorInfo()); | databaseError($conn->errorInfo()); |
| foreach ($query->fetchAll() as $row) { | foreach ($query->fetchAll() as $row) { |
| setlocale(LC_MONETARY, 'en_US'); | setlocale(LC_MONETARY, 'en_US'); |
| // $value = number_format(doubleval($row["value"]) , 2); | // $value = number_format(doubleval($row["value"]) , 2); |
| $suppliers[] = ucsmart($row[1]); | $suppliers[] = ucsmart($row[1]); |
| $values[] = doubleval($row["value"]); | $values[] = doubleval($row["value"]); |
| } | } |
| $query = 'SELECT sum(a.svalue) as val, suppliercountry from (SELECT sum("value") as svalue, max("supplierCountry") as 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\' | 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 '; | 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 = $conn->prepare($query); |
| $query->execute(); | $query->execute(); |
| databaseError($conn->errorInfo()); | databaseError($conn->errorInfo()); |
| foreach ($query->fetchAll() as $row) { | foreach ($query->fetchAll() as $row) { |
| $suppliers[] = "Other suppliers in " . ucsmart($row["suppliercountry"]); | $suppliers[] = "Other suppliers in " . ucsmart($row["suppliercountry"]); |
| $values[] = doubleval($row[0]); | $values[] = doubleval($row[0]); |
| } | } |
| $query = 'SELECT sum(a.value) as val, substring( | $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 | 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 | FROM contractnotice WHERE "childCN" is null |
| GROUP BY "supplierABN" ORDER BY sum(value) LIMIT 1844674 OFFSET 10) as a | GROUP BY "supplierABN" ORDER BY sum(value) LIMIT 1844674 OFFSET 10) as a |
| WHERE (suppliercountry ILIKE \'Australia\') | WHERE (suppliercountry ILIKE \'Australia\') |
| group by substring( | group by substring( |
| supplierpostcode from 0 for 2) | supplierpostcode from 0 for 2) |
| order by val DESC;'; | order by val DESC;'; |
| $query = $conn->prepare($query); | $query = $conn->prepare($query); |
| $query->execute(); | $query->execute(); |
| databaseError($conn->errorInfo()); | databaseError($conn->errorInfo()); |
| foreach ($query->fetchAll() as $row) { | foreach ($query->fetchAll() as $row) { |
| if ($row['postcode'][0] == 2 && $row['postcode'][1] == 6) | if ($row['postcode'][0] == 2 && $row['postcode'][1] == 6) |
| $ACTvalue += $row[0]; | $ACTvalue += $row[0]; |
| else if ($row['postcode'][0] == 2 || $row['postcode'][0] == 1) | else if ($row['postcode'][0] == 2 || $row['postcode'][0] == 1) |
| $NSWvalue += $row[0]; | $NSWvalue += $row[0]; |
| else if ($row['postcode'][0] == 3 || $row['postcode'][0] == 8) | else if ($row['postcode'][0] == 3 || $row['postcode'][0] == 8) |
| $Vicvalue += $row[0]; | $Vicvalue += $row[0]; |
| else if ($row['postcode'][0] == 4 || $row['postcode'][0] == 9) | else if ($row['postcode'][0] == 4 || $row['postcode'][0] == 9) |
| $QLDvalue += $row[0]; | $QLDvalue += $row[0]; |
| else if ($row['postcode'][0] == 5) | else if ($row['postcode'][0] == 5) |
| $SAvalue += $row[0]; | $SAvalue += $row[0]; |
| else if ($row['postcode'][0] == 6) | else if ($row['postcode'][0] == 6) |
| $WAvalue += $row[0]; | $WAvalue += $row[0]; |
| else if ($row['postcode'][0] == 7) | else if ($row['postcode'][0] == 7) |
| $Tasvalue += $row[0]; | $Tasvalue += $row[0]; |
| else if ($row['postcode'][0] == 0) | else if ($row['postcode'][0] == 0) |
| $NTvalue += $row[0]; | $NTvalue += $row[0]; |
| } | } |
| $suppliers[] = "Other suppliers in Australia - ACT"; | $suppliers[] = "Other suppliers in Australia - ACT"; |
| $values[] = doubleval($ACTvalue); | $values[] = doubleval($ACTvalue); |
| $suppliers[] = "Other suppliers in Australia - NSW"; | $suppliers[] = "Other suppliers in Australia - NSW"; |
| $values[] = doubleval($NSWvalue); | $values[] = doubleval($NSWvalue); |
| $suppliers[] = "Other suppliers in Australia - Victoria"; | $suppliers[] = "Other suppliers in Australia - Victoria"; |
| $values[] = doubleval($Vicvalue); | $values[] = doubleval($Vicvalue); |
| $suppliers[] = "Other suppliers in Australia - Queensland"; | $suppliers[] = "Other suppliers in Australia - Queensland"; |
| $values[] = doubleval($QLDvalue); | $values[] = doubleval($QLDvalue); |
| $suppliers[] = "Other suppliers in Australia - NT"; | $suppliers[] = "Other suppliers in Australia - NT"; |
| $values[] = doubleval($NTvalue); | $values[] = doubleval($NTvalue); |
| $suppliers[] = "Other suppliers in Australia - West Australia"; | $suppliers[] = "Other suppliers in Australia - West Australia"; |
| $values[] = doubleval($WAvalue); | $values[] = doubleval($WAvalue); |
| $suppliers[] = "Other suppliers in Australia - South Australia"; | $suppliers[] = "Other suppliers in Australia - South Australia"; |
| $values[] = doubleval($SAvalue); | $values[] = doubleval($SAvalue); |
| $suppliers[] = "Other suppliers in Australia - Tasmania"; | $suppliers[] = "Other suppliers in Australia - Tasmania"; |
| $values[] = doubleval($Tasvalue); | $values[] = doubleval($Tasvalue); |
| ?> | ?> |
| <script type="text/javascript"> | <script type="text/javascript"> |
| $(function () { | $(function () { |
| // data | // data |
| var data = [ | var data = [ |
| <?php | <?php |
| foreach ($suppliers as $key => $supplier) { | foreach ($suppliers as $key => $supplier) { |
| echo '{ label: "' . $supplier . '", data: ' . doubleval($values[$key]) . '},'; | echo '{ label: "' . $supplier . '", data: ' . doubleval($values[$key]) . '},'; |
| } | } |
| ?> | ?> |
| ]; | ]; |
| // GRAPH 7 | // GRAPH 7 |
| $.plot($("#graph7"), data, | $.plot($("#graph7"), data, |
| { | { |
| series: { | series: { |
| pie: { | pie: { |
| show: true, | show: true, |
| radius: 1, | radius: 1, |
| tilt: 0.75, | tilt: 0.75, |
| label: { | label: { |
| show: true, | show: true, |
| radius: 1, | radius: 1, |
| formatter: function(label, series){ | formatter: function(label, series){ |
| return '<div style="font-size:8pt;text-align:center;padding:2px;color:white;">'+label+': '+Math.round(series.percent)+'%</div>'; | return '<div style="font-size:8pt;text-align:center;padding:2px;color:white;">'+label+': '+Math.round(series.percent)+'%</div>'; |
| }, | }, |
| background: { | background: { |
| opacity: 0.5, | opacity: 0.5, |
| color: '#000' | color: '#000' |
| } | } |
| }, | }, |
| combine: { | combine: { |
| color: '#999', | color: '#999', |
| threshold: 0.012 | threshold: 0.012 |
| } | } |
| } | } |
| }, | }, |
| legend: { | legend: { |
| show: false | show: false |
| } | } |
| }); | }); |
| }); | }); |
| </script> | </script> |
| <div id="graph7" style="width:900px;height:550px"></div> | <div id="graph7" style="width:900px;height:550px"></div> |
| <?php | <?php |
| } | } |
| ?> | ?> |