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
   
  function agenciesGraph() {
  $topX = 15;
  $query = "SELECT SUM(value) as val, agencyName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND childCN = 0
  GROUP BY agencyName ORDER BY val DESC limit $topX";
  $result = mysql_query($query);
  $agencies = Array();
  $values = Array();
  while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
  $agencies[] = $row['agencyName'];
  $values[] = doubleval($row["val"]);
  }
  mysql_free_result($result);
   
  $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";
  $result = mysql_query($query);
  while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
  $agencies[] = $row['count'] . " other agencies";
  $values[] = doubleval($row[0]);
  }
  mysql_free_result($result);
   
  };
   
  function agencySuppliersGraph($agency) {
  $agency = "AusAid";
  $topX = 15;
  $query = "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 $topX";
  $result = mysql_query($query);
  $suppliers = Array();
  $values = Array();
  while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
  $suppliers[] = ucsmart($row['supplierName']);
  $values[] = doubleval($row["val"]);
  }
  mysql_free_result($result);
   
  $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);
  while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
  if ($row['count'] > 0) {
  $suppliers[] = $row['count'] . " other suppliers";
  $values[] = doubleval($row[0]);
  }
  }
  mysql_free_result($result);
  }
   
  function CnCGraph() {
  $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";
  $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['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);
  mysql_free_result($result);
   
   
  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 = 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() {
  $topX = 10;
  $query = "SELECT value, supplierName
  FROM `contractnotice` WHERE (YEAR(contractStart) >= 2009) AND childCN = 0
  GROUP BY supplierABN ORDER BY value DESC limit $topX";
  $result = mysql_query($query);
  $suppliers = Array();
  $values = Array();
  while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
  setlocale(LC_MONETARY, 'en_US');
  // $value = number_format(doubleval($row["value"]) , 2);
  $suppliers[] = ucsmart($row[1]);
  $values[] = doubleval($row["value"]);
  }
  mysql_free_result($result);
   
  $query = "SELECT sum(a.value) as val, supplierCountry from (SELECT value, supplierName, supplierCountry
  FROM `contractnotice` WHERE (YEAR(contractStart) >= 2009) AND childCN = 0
  GROUP BY supplierName ORDER BY value LIMIT 18446744073709551610 OFFSET $topX) as a group by supplierCountry order by val DESC limit 5 offset 1";
  $result = mysql_query($query);
  while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
  $suppliers[] = "Other suppliers in ".ucsmart($row["supplierCountry"]);
  $values[] = doubleval($row[0]);
  }
   
  mysql_free_result($result);
   
  $query = "SELECT sum(a.value) as val, TRUNCATE(supplierPostcode,-2) as postcode from (SELECT value, supplierName, supplierPostcode, supplierCountry
  FROM `contractnotice` WHERE (YEAR(contractStart) >= 2009) AND childCN = 0
  GROUP BY supplierName ORDER BY value LIMIT 18446744073709551610 OFFSET $topX) as a
  WHERE (supplierCountry LIKE 'Australia' OR supplierCountry LIKE 'AUSTRALIA') AND supplierPostcode < 10000
  group by TRUNCATE(supplierPostcode,-2)
  order by val DESC";
  $result = mysql_query($query);
  while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
  if ($row['postcode'][0] == 2 && $row['postcode'][1] == 6) $ACTvalue += $row[0];
  else if ($row['postcode'][0] == 2 || $row['postcode'][0] == 1) $NSWvalue += $row[0];
  else if ($row['postcode'][0] == 3 || $row['postcode'][0] == 8) $Vicvalue += $row[0];
  else if ($row['postcode'][0] == 4 || $row['postcode'][0] == 9) $QLDvalue += $row[0];
  else if ($row['postcode'][0] == 5) $SAvalue += $row[0];
  else if ($row['postcode'][0] == 6) $WAvalue += $row[0];
  else if ($row['postcode'][0] == 7) $Tasvalue += $row[0];
  else if ($row['postcode'][0] == 0) $NTvalue += $row[0];
  }
  mysql_free_result($result);
  $suppliers[] = "Other suppliers in Australia - ACT";
  $values[] = doubleval($ACTvalue);
  $suppliers[] = "Other suppliers in Australia - NSW";
  $values[] = doubleval($NSWvalue);
  $suppliers[] = "Other suppliers in Australia - Victoria";
  $values[] = doubleval($Vicvalue);
  $suppliers[] = "Other suppliers in Australia - Queensland";
  $values[] = doubleval($QLDvalue);
  $suppliers[] = "Other suppliers in Australia - NT";
  $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);
   
  }
   
  ?>
Unnamed repository; edit this file to name it for gitweb.
GitPHP by Chris Han