--- a/lib/graphs.inc.php +++ b/lib/graphs.inc.php @@ -27,7 +27,11 @@ var d1 = []; var d2 = []; <?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"::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(); @@ -40,7 +44,9 @@ 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 = '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(); @@ -71,7 +77,7 @@ points: { show: true } }, bars: { show: true } - }, + }] ]; var options = { @@ -136,7 +142,7 @@ global $conn; 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_nametoabn on contractnotice."agencyName"=agency_nametoabn."agencyName" WHERE "childCN" is null GROUP BY abn ORDER BY SUM("value") DESC'; $query = $conn->prepare($query); $query->execute(); @@ -195,11 +201,15 @@ ; function agencySuppliersGraph($agency) { + + global $conn; $agency = "AusAid"; $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 (extract ("YEAR" from "contractStart") >= :startYear) AND "childCN" is null AND "agencyName" = :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(); @@ -210,9 +220,11 @@ } - $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'; - $query = $conn->prepare($query); + $query = 'SELECT sum(a.val) as value, count(1) as count from (SELECT SUM(value) as val, "supplierName" FROM contractnotice WHERE (extract ("YEAR" from "contractStart") >= :startYear) AND "childCN" is null and "agencyName" = :agency +GROUP BY lower("supplierName") ORDER BY val DESC LIMIT 18446744073709551610 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) { @@ -224,8 +236,10 @@ } 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"; + + 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()); @@ -267,19 +281,21 @@ "Consultancies", "Confidentialities" ); - $query = 'SELECT \'consultancy\', count(1) FROM `contractnotice` WHERE $agencyQ $supplierQ consultancy=\'Yes\' AND "childCN" is null;'; + $query = 'SELECT \'consultancy\', count(1) FROM contractnotice WHERE $agencyQ $supplierQ consultancy=\'Yes\' AND "childCN" is null;'; $result = $conn->query($query); $row = $result->fetch(PDO::FETCH_ASSOC); $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 \'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() { + + global $conn; $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) AND "childCN" is null GROUP BY MONTH(publishDate), YEAR(publishDate) @@ -302,8 +318,10 @@ } function ContractStartingGraph() { + + global $conn; $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) AND "childCN" is null GROUP BY MONTH(contractStart), YEAR(contractStart) @@ -326,7 +344,9 @@ } function MethodCountGraph() { - $query = 'select procurementMethod, count(1) as count, SUM(value) as value, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice` + + global $conn; + $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'; $methods = Array("Direct", "Open", "Select"); $dates = Array(); @@ -361,7 +381,9 @@ } function MethodValueGraph() { - $query = "select procurementMethod, SUM(value) as value, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice` + + global $conn; + $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"; $methods = Array("Direct", "Open", "Select"); $dates = Array();