projects / contractdashboard.git / blobdiff
search:
summary | shortlog | log | commit | commitdiff | tree
unified | plain
Depreciate MySQL and GD image graphs
[contractdashboard.git] / lib / graphs.inc.php
blob:a/lib/graphs.inc.php -> blob:b/lib/graphs.inc.php
<?php <?php
  $includedFlot = false;
   
  function includeFlot() {
  if (!$includedFlot) {
  echo ' <!--[if lte IE 8]><script language="javascript" type="text/javascript" src="lib/flot/excanvas.min.js"></script><![endif]-->
   
  <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.selection.js"></script>
  <script language="javascript" type="text/javascript" src="lib/flot/jquery.flot.stack.js"></script>
   
  ';
  $includedFlot = true;
  }
  }
   
  function CNDistributionGraph() {
  global $conn;
  includeFlot();
  ?>
  <center><div id="cndist" style="width:900px;height:550px"></div></center>
  <script type="text/javascript">
  var placeholder = $("#cndist");
  $(function () {
   
  var d1 = [];
  var d2 = [];
  <?php
  $query = 'select cnid, count(*) from
  (select ("CNID"::integer - MOD("CNID"::integer,100)) as cnid
  from contractnotice where "CNID"::integer < 999999
  and "CNID" not like \'%-A%\'
  and "parentCN" is null) as a group by cnid order by cnid';
  $query = $conn->prepare($query);
  $query->execute();
  $errors = $conn->errorInfo();
  if ($errors[2] != "") {
  echo("Export terminated, db error" . print_r($errors, true));
  return Array();
  }
   
  foreach ($query->fetchAll() as $delta) {
   
  echo "d1.push([ " . intval($delta['cnid']) . ", " . intval($delta['count']) . "]); \n";
  };
  $query = 'select cnid, count(*) from (select ("CNID"::integer - MOD("CNID"::integer,100)) as cnid
  from contractnotice where "CNID" not like \'%-A%\' and "parentCN" is not null)
  as a group by cnid order by cnid';
  $query = $conn->prepare($query);
  $query->execute();
  $errors = $conn->errorInfo();
  if ($errors[2] != "") {
  echo("Export terminated, db error" . print_r($errors, true));
  return Array();
  }
   
  foreach ($query->fetchAll() as $delta) {
   
  echo "d2.push([ " . intval($delta['cnid']) . ", " . intval($delta['count']) . "]); \n";
  };
  ?>
   
  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();
  previousPoint = null;
  }
  });
   
  var plot = $.plot(placeholder, data,
  options);
  });
   
  function showTooltip(x, y, contents) {
  $('<div id="tooltip">' + contents + '</div>').css( {
  position: 'absolute',
  display: 'none',
  top: y + 5,
  left: x + 5,
  border: '1px solid #fdd',
  padding: '2px',
  'background-color': '#fee',
  opacity: 0.80
  }).appendTo("body").fadeIn(200);
  }
   
  </script>
  <?php
  }
   
function agenciesGraph() { function agenciesGraph() {
$topX = 15;  
$query = "SELECT SUM(value) as val, agencyName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND childCN = 0 global $conn;
GROUP BY agencyName ORDER BY val DESC limit $topX"; includeFlot();
$result = mysql_query($query); $query = 'SELECT SUM("value") as val, MAX(contractnotice."agencyName") as agencyname FROM contractnotice join agency on contractnotice."agencyName"=agency."agencyName" WHERE "childCN" is null
$agencies = Array(); GROUP BY abn ORDER BY SUM("value") DESC';
$values = Array(); $query = $conn->prepare($query);
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) { $query->execute();
$agencies[] = $row['agencyName']; $errors = $conn->errorInfo();
$values[] = doubleval($row["val"]); if ($errors[2] != "") {
} echo("Export terminated, db error" . print_r($errors, true));
mysql_free_result($result); return Array();
  }
$query = "SELECT sum(a.val) as value, count(1) as count from (SELECT SUM(value) as val, agencyName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND childCN = 0 ?>
GROUP BY agencyName ORDER BY val DESC LIMIT 18446744073709551610 OFFSET $topX) as a"; <script type="text/javascript">
$result = mysql_query($query); $(function () {
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) { // data
$agencies[] = $row['count'] . " other agencies"; var data = [
$values[] = doubleval($row[0]); <?php
} foreach ($query->fetchAll() as $row) {
mysql_free_result($result); echo '{ label: "' . $row['agencyname'] . '", data: ' . doubleval($row["val"]) . '},';
  }
}; ?>
  ];
  // GRAPH 7
  $.plot($("#graph7"), data,
  {
  series: {
  pie: {
  show: true,
  radius: 1,
  tilt: 0.75,
  label: {
  show: true,
  radius: 1,
  formatter: function(label, series){
  return '<div style="font-size:8pt;text-align:center;padding:2px;color:white;">'+label+': '+Math.round(series.percent)+'%</div>';
  },
  background: {
  opacity: 0.5,
  color: '#000'
  }
  },
  combine: {
  color: '#999',
  threshold: 0.012
  }
  }
  },
  legend: {
  show: false
  }
  });
  });
  </script>
  <div id="graph7" style="width:900px;height:550px"></div>
   
  <?php
  }
   
  ;
   
function agencySuppliersGraph($agency) { 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 = 0 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 = 0 and agencyName = '$agency'  
GROUP BY lower(supplierName) ORDER BY val DESC LIMIT 18446744073709551610 OFFSET $topX) as a";  
$result = mysql_query($query); $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\'
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) { GROUP BY lower(supplierName) ORDER BY val DESC LIMIT 18446744073709551610 OFFSET $topX) as a';
if ($row['count'] > 0) { $query = $conn->prepare($query);
$suppliers[] = $row['count'] . " other suppliers"; $query->execute();
$values[] = doubleval($row[0]); databaseError($conn->errorInfo());
} foreach ($query->fetchAll() as $row) {
} if ($row['count'] > 0) {
mysql_free_result($result); $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` $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 = 0;"; $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 = 0;"; $query = 'SELECT \'consultancy\', count(1) FROM `contractnotice` WHERE $agencyQ $supplierQ consultancy=\'Yes\' AND "childCN" is null;';
$result = mysql_query($query); $result = $conn->query($query);
$row = mysql_fetch_array($result, MYSQL_BOTH); $row = $result->fetch(PDO::FETCH_ASSOC);
$attributes[1] = $row[1]; $attributes[0] = $row[1];
mysql_free_result($result); $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() { function ContractPublishedGraph() {
$query = "SELECT YEAR(publishDate), MONTH(publishDate), $query = 'SELECT YEAR(publishDate), MONTH(publishDate),
SUM(value) as val, count(1) as count FROM `contractnotice` SUM(value) as val, count(1) as count FROM `contractnotice`
WHERE (YEAR(publishDate) >= 2008) WHERE (YEAR(publishDate) >= 2008)
AND childCN = 0 AND "childCN" is null
GROUP BY MONTH(publishDate), YEAR(publishDate) GROUP BY MONTH(publishDate), YEAR(publishDate)
ORDER BY YEAR(publishDate), MONTH(publishDate)"; ORDER BY YEAR(publishDate), MONTH(publishDate)';
   
$result = mysql_query($query); $query = $conn->prepare($query);
$dates = Array(); $query->execute();
$values = Array(); databaseError($conn->errorInfo());
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {  
setlocale(LC_MONETARY, 'en_US'); $dates = Array();
$value = number_format(doubleval($row["val"]),2); $values = Array();
$month_name = date( 'F', mktime(0, 0, 0, $row[1]) ); foreach ($query->fetchAll() as $row) {
$dates[] = $month_name." {$row[0]}"; setlocale(LC_MONETARY, 'en_US');
$counts[] = doubleval($row["count"]); $value = number_format(doubleval($row["val"]), 2);
$values[] = doubleval($row["val"]); $month_name = date('F', mktime(0, 0, 0, $row[1]));
} $dates[] = $month_name . " {$row[0]}";
mysql_free_result($result); $counts[] = doubleval($row["count"]);
  $values[] = doubleval($row["val"]);
} }
  }
   
function ContractStartingGraph() { function ContractStartingGraph() {
$query = "SELECT YEAR(contractStart), MONTH(contractStart), $query = 'SELECT YEAR(contractStart), MONTH(contractStart),
SUM(value) as val, count(1) as count FROM `contractnotice` SUM(value) as val, count(1) as count FROM `contractnotice`
WHERE (YEAR(contractStart) >= 2008) WHERE (YEAR(contractStart) >= 2008)
AND childCN = 0 AND "childCN" is null
GROUP BY MONTH(contractStart), YEAR(contractStart) GROUP BY MONTH(contractStart), YEAR(contractStart)
ORDER BY YEAR(contractStart), MONTH(contractStart)"; ORDER BY YEAR(contractStart), MONTH(contractStart)';
   
$result = mysql_query($query); $query = $conn->prepare($query);
$dates = Array(); $query->execute();
$values = Array(); databaseError($conn->errorInfo());
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {  
setlocale(LC_MONETARY, 'en_US'); $dates = Array();
$value = number_format(doubleval($row["val"]),2); $values = Array();
$month_name = date( 'F', mktime(0, 0, 0, $row[1]) ); foreach ($query->fetchAll() as $row) {
$dates[] = $month_name." {$row[0]}"; setlocale(LC_MONETARY, 'en_US');
$counts[] = doubleval($row["count"]); $value = number_format(doubleval($row["val"]), 2);
$values[] = doubleval($row["val"]); $month_name = date('F', mktime(0, 0, 0, $row[1]));
} $dates[] = $month_name . " {$row[0]}";
mysql_free_result($result); $counts[] = doubleval($row["count"]);
} $values[] = doubleval($row["val"]);
  }
  }
   
function MethodCountGraph() { function MethodCountGraph() {
$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';