More MySQL purging
[contractdashboard.git] / heuristics / historyHeuristics.php
blob:a/heuristics/historyHeuristics.php -> blob:b/heuristics/historyHeuristics.php
--- a/heuristics/historyHeuristics.php
+++ b/heuristics/historyHeuristics.php
@@ -20,9 +20,9 @@
 $agencyTransactions = Array();
 function getAgencyTransactions($agencyName)
 {
-	global $agencyTransactions;
+	global $agencyTransactions,$conn;
 	if (!$agencyTransactions[$agencyName]) {
-		$query = 'select count(*) from contractnotice where agencyName = "' . $agencyName . '"';
+		$query = 'select count(*) from contractnotice where "agencyName" = \'' . $agencyName . '"\'';
     $result = $conn->query($query);
     $r = $result->fetch(PDO::FETCH_BOTH);
 		$agencyTransactions[$agencyName] = $r[0];
@@ -49,9 +49,9 @@
 }
 function getStatsAgencyTransactions()
 {
-	global $averageAgencyTransactions, $stddevAgencyTransactions;
-	$query = "select avg(count), STDDEV(count) from (select count(*) as count
-                from contractnotice group by agencyName) as a;";
+	global $averageAgencyTransactions, $stddevAgencyTransactions,$conn;
+	$query = 'select avg(count), STDDEV(count) from (select count(*) as count
+                from contractnotice group by "agencyName") as a;';
     $result = $conn->query($query);
     $r = $result->fetch(PDO::FETCH_BOTH);
 	$averageAgencyTransactions = $r[0];
@@ -78,10 +78,10 @@
 $supplierTransactions = Array();
 function getSupplierTransactions($supplierName, $supplierABN)
 {
-	global $supplierTransactions;
+	global $supplierTransactions,$conn;
 	if ($supplierABN != 0 && $supplierABN != "") {
 		if (!$supplierTransactions[$supplierABN]) {
-			$query = 'select count(*) from contractnotice where supplierABN = "' . $supplierABN . '"';
+			$query = "select count(*) from contractnotice where \"supplierABN\" = '" . $supplierABN . "'";
 			    $result = $conn->query($query);
     $r = $result->fetch(PDO::FETCH_BOTH);
 			$supplierTransactions[$supplierABN] = $r[0];
@@ -89,7 +89,8 @@
 		return $supplierTransactions[$supplierABN];
 	}
 	if (!$supplierTransactions[$supplierName]) {
-		$query = 'select count(*) from contractnotice where supplierName = "' . $supplierName . '"';
+		$query = "select count(*) from contractnotice where \"supplierName\" = '" . $supplierName . "'";
+                
     $result = $conn->query($query);
     $r = $result->fetch(PDO::FETCH_BOTH);
 		$supplierTransactions[$supplierName] = $r[0];
@@ -116,8 +117,10 @@
 }
 function getStatsSupplierTransactions()
 {
-	global $averageSupplierTransactions, $stddevSupplierTransactions;
-	$query = 'select avg(count), stddev(count) from (select IF(supplierABN != "",supplierABN,supplierName) as supplierID, count(*) as count from contractnotice group by supplierID) as a;';
+	global $averageSupplierTransactions, $stddevSupplierTransactions,$conn;
+	$query = 'select avg(count), stddev(count) from (
+            select (case when "supplierABN" != 0 THEN "supplierABN"::text ELSE "supplierName" END) as "supplierID",
+            count(*) as count from contractnotice group by "supplierID") as a;';
     $result = $conn->query($query);
     $r = $result->fetch(PDO::FETCH_BOTH);
 	$averageSupplierTransactions = $r[0];