projects / contractdashboard.git / blobdiff
search:
summary | shortlog | log | commit | commitdiff | tree
unified | plain
Support partial data amendments with multi line descriptions
[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
$query = $conn->prepare($query); (select ("CNID"::integer - MOD("CNID"::integer,100)) as cnid
$query->execute(); from contractnotice where "CNID"::integer < 999999
if (!$query) { and "CNID" not like \'%-A%\'
databaseError($conn->errorInfo()); and "parentCN" is null) as a group by cnid order by cnid';
return Array(); $query = $conn->prepare($query);
} $query->execute();
  $errors = $conn->errorInfo();
foreach ($query->fetchAll() as $delta) { if ($errors[2] != "") {
  echo("Export terminated, db error" . print_r($errors, true));
echo "d1.push([ ".intval($delta['cnid']).", ".intval($delta['count'])."]); \n"; return Array();
}; }
$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); foreach ($query->fetchAll() as $delta) {
$query->execute();  
if (!$query) { echo "d1.push([ " . intval($delta['cnid']) . ", " . intval($delta['count']) . "]); \n";
databaseError($conn->errorInfo()); };
return Array(); $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';
foreach ($query->fetchAll() as $delta) { $query = $conn->prepare($query);
  $query->execute();
echo "d2.push([ ".intval($delta['cnid']).", ".intval($delta['count'])."]); \n"; $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";
  };
  d1d2Graph();
  }
  function d1d2Graph($time = false) {
  ?>
   
  var data = [
  {
  data: d1,
  series: {
  lines: { show: true },
  points: { show: true }
  }
  <?php if (!$time){
  echo ',bars: { show: true }';
  }?>
  },
  {
  data: d2,
  series: {
  lines: { show: true },
  points: { show: true }
  }
  <?php if (!$time){
  echo ',bars: { show: true }';
  } else {
  echo ',yaxis: 2';
  }
?> ?>
  }]
var data = [ ;
{ var options =
data: d1, {
series: {  
lines: { show: true }, grid: { hoverable: true, clickable: true, labelMargin: 17 },
points: { show: true } selection: { mode: "x" } <?php if ($time){
}, echo ', xaxis: {
bars: { show: true } mode: "time"
}, }';} else {
{ echo ', series: {
data: d2, stack: true
series: { },';
lines: { show: true }, } ?>
points: { show: true } };
},  
bars: { show: true } placeholder.bind("plotselected", function (event, ranges) {
}, plot = $.plot(placeholder, data,
]; $.extend(true, {}, options, {
var options = xaxis: { min: ranges.xaxis.from, max: ranges.xaxis.to }
{ }));
series: { });
stack: true, var previousPoint = null;
}, placeholder.bind("plothover", function (event, pos, item) {
grid: { hoverable: true, clickable: true, labelMargin: 17 }, $("#x").text(pos.x.toFixed(2));
selection: { mode: "x" } $("#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() {
   
  global $conn, $yearQ, $supplierParts,$supplierQ, $supplierABN, $supplierName;
  includeFlot();
  $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
  GROUP BY abn ORDER BY SUM("value") DESC';
  $query = $conn->prepare($query);
  if (count($supplierParts) > 0) {
  if ($supplierParts[0] > 0) {
  $query->bindParam(":supplierABN", $supplierABN);
  } else {
  $query->bindParam(":supplierName", $supplierName);
  }
  }
  $query->execute();
  $errors = $conn->errorInfo();
  if ($errors[2] != "") {
  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"]) . '},';
  }
  ?>
  ];
  // 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) {
   
  global $conn,$startYear, $yearQ;
  includeFlot();
   
  $topX = 20;
  $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;
  $query = $conn->prepare($query);
  $query->bindParam(":startYear", $startYear);
  $query->bindParam(":agency", $agency);
  $query->execute();
  databaseError($conn->errorInfo());
  $suppliers = Array();
  $values = Array();
  foreach ($query->fetchAll() as $row) {
  $suppliers[] = ucsmart($row['supplierName']);
  $values[] = doubleval($row["val"]);
  }
   
   
  $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';
  $query = $conn->prepare($query);
  $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]);
  }
  }
  ?>
  <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
  }
   
  function CnCGraph() {
   
  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"
  );