projects / contractdashboard.git / blobdiff
search:
summary | shortlog | log | commit | commitdiff | tree
unified | plain
More MySQL purging
[contractdashboard.git] / lib / graphs.inc.php
blob:a/lib/graphs.inc.php -> blob:b/lib/graphs.inc.php
<?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 (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 = '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 = $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();
  }
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" - MOD("CNID",100)) as cnid from contractnotice where "CNID" < 999999 and "parentCN" is not null) as a group by cnid order by cnid';  
$query = $conn->prepare($query);  
$query->execute();  
if (!$query) {  
databaseError($conn->errorInfo());  
return Array();  
}  
   
foreach ($query->fetchAll() as $delta) {  
   
echo "d2.push([ ".intval($delta['cnid']).", ".intval($delta['count'])."]); \n";  
};  
?>  
   
var data = [  
{  
data: d1,  
series: {  
lines: { show: true },  
points: { show: true }  
},  
bars: { show: true }  
},  
{  
data: d2,  
series: {  
lines: { show: true },  
points: { show: true }  
},  
bars: { show: true }  
},  
];  
var options =  
{  
series: {  
stack: true,  
},  
grid: { hoverable: true, clickable: true, labelMargin: 17 },  
selection: { mode: "x" }  
}; };
  $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';
placeholder.bind("plotselected", function (event, ranges) { $query = $conn->prepare($query);
plot = $.plot(placeholder, data, $query->execute();
$.extend(true, {}, options, { $errors = $conn->errorInfo();
xaxis: { min: ranges.xaxis.from, max: ranges.xaxis.to } if ($errors[2] != "") {
})); echo("Export terminated, db error" . print_r($errors, true));
}); return Array();
var previousPoint = null; }
placeholder.bind("plothover", function (event, pos, item) {  
$("#x").text(pos.x.toFixed(2)); foreach ($query->fetchAll() as $delta) {
$("#y").text(pos.y.toFixed(2));  
  echo "d2.push([ " . intval($delta['cnid']) . ", " . intval($delta['count']) . "]); \n";
if (item) { };
if (previousPoint != item.dataIndex) { ?>
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" is null $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'; 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"; $agency = "AusAid";
$topX = 15; $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 (YEAR(contractStart) >= $startYear) AND "childCN" is null AND agencyName = \'$agency\'
GROUP BY lower(supplierName) ORDER BY val DESC limit $topX'; GROUP BY lower(supplierName) ORDER BY val DESC limit $topX';
$result = mysql_query($query); $query = $conn->prepare($query);
$suppliers = Array(); $query->execute();
$values = Array(); databaseError($conn->errorInfo());
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) { $suppliers = Array();
$suppliers[] = ucsmart($row['supplierName']); $values = Array();
$values[] = doubleval($row["val"]); foreach ($query->fetchAll() as $row) {
} $suppliers[] = ucsmart($row['supplierName']);
mysql_free_result($result); $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 (YEAR(contractStart) >= $startYear) AND "childCN" is null and agencyName = \'$agency\'  
   
  $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'; GROUP BY lower(supplierName) ORDER BY val DESC LIMIT 18446744073709551610 OFFSET $topX) as a';
$result = mysql_query($query); $query = $conn->prepare($query);
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) { $query->execute();
if ($row['count'] > 0) { databaseError($conn->errorInfo());
$suppliers[] = $row['count'] . " other suppliers"; foreach ($query->fetchAll() as $row) {
$values[] = doubleval($row[0]); if ($row['count'] > 0) {
} $suppliers[] = $row['count'] . " other suppliers";
} $values[] = doubleval($row[0]);
mysql_free_result($result); }
  }
} }
   
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` $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";
$result = mysql_query($query); $query = $conn->prepare($query);
$methods = Array("Direct","Open","Select"); $query->execute();
$dates = Array(); databaseError($conn->errorInfo());
$methodCountsP = Array();  
$methodCounts = Array(); $methods = Array("Direct", "Open", "Select");
$maxValue = 0; $dates = Array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $methodCountsP = Array();
setlocale(LC_MONETARY, 'en_US'); $methodCounts = Array();
if ($row['val'] > $maxValue) $maxValue = $row['val']; $maxValue = 0;
$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['val'] > $maxValue)
ksort($dates); $maxValue = $row['val'];
} $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) { $methodCountsP[$row["procurementMethod"]][$date] = $row["count"];
if ($methodCountsP[$method][$date] > 0) $methodCounts[$method][] = $methodCountsP[$method][$date]; }
else $methodCounts[$method][] = 0; foreach ($methods as $method) {
} foreach ($dates as $date) {
} if ($methodCountsP[$method][$date] > 0)
$dates = array_values($dates); $methodCounts[$method][] = $methodCountsP[$method][$date];
$totalRecords = array_sum_all($methodCounts); else
mysql_free_result($result); $methodCounts[$method][] = 0;
  }
  }
function formatCallback($aVal) { $dates = array_values($dates);
global $totalRecords; $totalRecords = array_sum_all($methodCounts);
return percent($aVal, $totalRecords) . "%";  
} function formatCallback($aVal) {
$attributes = Array(); global $totalRecords;
$attributeNames = Array( return percent($aVal, $totalRecords) . "%";
"Consultancies", }
"Confidentialities"  
); $attributes = Array();
$query = 'SELECT \'consultancy\', count(1) FROM `contractnotice` WHERE $agencyQ $supplierQ consultancy=\'Yes\' AND "childCN" is null;'; $attributeNames = Array(
$result = mysql_query($query); "Consultancies",
$row = mysql_fetch_array($result, MYSQL_BOTH); "Confidentialities"
$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 \'consultancy\', cou