--- a/displaySupplier.php +++ b/displaySupplier.php @@ -6,7 +6,7 @@ $supplierS = htmlentities(strip_tags($_REQUEST['supplier'])); include_header(str_replace("%","",$supplierName)); echo '<center><h1>'.str_replace("%","",$supplierName).'</h1></center>'; - + agenciesGraph(); // MethodCountGraph($supplierS); // CnCGraph($supplierS); // MethodValueGraph($supplierS); @@ -21,12 +21,53 @@ spread of contract values spread of industries (textual?) */ + // echo '<img src="graphs/displayMethodCountGraph.php?month=' . stripslashes($supplier) . '">'; + // echo '<img src="graphs/displayCnCGraph.php?month=' . stripslashes($supplier) . '">'; +$query = 'select contractnotice."SONID", min(title) as title, count(*), sum(value) as value from contractnotice inner join standingoffers on contractnotice."SONID" = standingoffers."SONID" WHERE ' .$yearQ . ' ' . + $supplierQ . ' ' . $standardQ + . ' and contractnotice."SONID" != \'\' group by contractnotice."SONID"'; +$query = $conn->prepare($query); + + + if ($supplierParts[0] > 0) { + $query->bindParam(":supplierABN", $supplierABN); + } else { + $query->bindParam(":supplierName", $supplierName); + } + $query->execute(); + databaseError($conn->errorInfo()); +echo "<table> <thead> + <tr> + <th>Standing Offer</th> + <th>Contracts Count</th> + <th>Total Contract Value</th> + </tr> + </thead>"; + foreach ($query->fetchAll() as $row) { + setlocale(LC_MONETARY, 'en_US'); + $value = number_format(doubleval($row['value']), 2); + echo ("<tr> + <td><B><a href=\"displaySON.php?SONID={$row['SONID']}\">{$row['title']}</a></b></td> + <td>{$row['count']}</td> + <td>\$$value</td> + </tr>"); + } + echo "</table><table> <thead> + <tr> + <th>Contract Notice Number</th> + <th>Contract Description</th> + <th>Total Contract Value</th> + <th>Agency</th> + <th>Contract Start Date</th> + <th>Supplier</th> + </tr> + </thead>"; $query = 'SELECT "CNID", "description", "value", "agencyName", "category", "contractStart", "supplierName" - FROM contractnotice WHERE ' . + FROM contractnotice WHERE ' .$yearQ . ' ' . $supplierQ . ' ' . $standardQ . ' ORDER BY value DESC'; - echo $query; + $query = $conn->prepare($query); @@ -37,19 +78,6 @@ } $query->execute(); databaseError($conn->errorInfo()); - // echo '<img src="graphs/displayMethodCountGraph.php?month=' . stripslashes($supplier) . '">'; - // echo '<img src="graphs/displayCnCGraph.php?month=' . stripslashes($supplier) . '">'; - - echo "<table> <thead> - <tr> - <th>Contract Notice Number</th> - <th>Contract Description</th> - <th>Total Contract Value</th> - <th>Agency</th> - <th>Contract Start Date</th> - <th>Supplier</th> - </tr> - </thead>"; foreach ($query->fetchAll() as $row) { setlocale(LC_MONETARY, 'en_US'); $value = number_format(doubleval($row['value']), 2); @@ -68,10 +96,10 @@ */ include_header("Suppliers"); suppliersGraph(); - $query = 'SELECT SUM("value") as val, MAX("supplierName") as supplierName, "supplierABN",( - case when "supplierABN" != 0 THEN "supplierABN"::text ELSE "supplierName" END) as supplierID + $query = 'SELECT SUM("value") as val, mode("supplierName") as supplierName, "supplierABN",( + case when "supplierABN" != 0 THEN lower("supplierABN"::text) ELSE lower("supplierName") END) as supplierID FROM contractnotice -WHERE "childCN" is null +WHERE ' .$yearQ . ' "childCN" is null GROUP BY supplierID,"supplierABN" ORDER BY val DESC LIMIT 100';