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