| <?php | |
| include_once ("./lib/common.inc.php"); | |
| if ($_REQUEST['SONID']) { | |
| $query = 'SELECT title from standingoffers where "SONID" = :SONID'; | |
| $query = $conn->prepare($query); | |
| $query->bindParam(":SONID", $_REQUEST['SONID']); | |
| $query->execute(); | |
| databaseError($conn->errorInfo()); | |
| $title = reset($query->fetchAll())['title']; | |
| include_header($title); | |
| echo "<center><h1>".$_REQUEST['SONID']." - ".$title."</h1></center>"; | |
| $query = 'select "supplierABN", min("supplierName") as "supplierName", count(*), sum(value) as value from contractnotice WHERE '.$yearQ.' "childCN" is null AND "SONID" = :SONID group by "supplierABN"'; | |
| $query = $conn->prepare($query); | |
| $query->bindParam(":SONID", $_REQUEST['SONID']); | |
| $query->execute(); | |
| databaseError($conn->errorInfo()); | |
| echo "<table> <thead> | |
| <tr> | |
| <th>Supplier</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); | |
| $supplier = stripslashes($row['supplierABN'] . '-' . $row['supplierName']); | |
| echo ("<tr> | |
| <td><b><a href=\"displaySupplier.php?supplier={$supplier}\">{$row['supplierName']}</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", title, description, value, "agencyName", contractnotice."SONID", "contractStart", "supplierName" | |
| FROM contractnotice inner join standingoffers on contractnotice."SONID" = standingoffers."SONID" | |
| WHERE '.$yearQ.' "childCN" is null | |
| AND contractnotice."SONID" = :SONID | |
| ORDER BY value DESC'; | |
| $query = $conn->prepare($query); | |
| $query->bindParam(":SONID", $_REQUEST['SONID']); | |
| $query->execute(); | |
| databaseError($conn->errorInfo()); | |
| foreach ($query->fetchAll() as $row) { | |
| setlocale(LC_MONETARY, 'en_US'); | |
| $value = number_format(doubleval($row['value']) , 2); | |
| echo ("<tr> | |
| <td><a href=\"displayContract.php?CNID={$row['CNID']}\">{$row['CNID']}</a></td> | |
| <td><b>{$row['description']}</b></a></td> | |
| <td>\$$value</td><td>{$row['agencyName']}</td> | |
| <td>{$row['contractStart']}</td> | |
| <td>{$row['supplierName']}</td> | |
| </tr>"); | |
| } | |
| echo "</table>"; | |
| } else { | |
| /* | |
| split by main categories | |
| */ | |
| include_header("Standing Offers"); | |
| $query = 'SELECT sum(value), count(*), contractnotice."SONID", min(title) as title | |
| FROM contractnotice inner join standingoffers on contractnotice."SONID" = standingoffers."SONID" | |
| WHERE '.$yearQ.' "childCN" is null and contractnotice."SONID" != \'\' | |
| GROUP BY contractnotice."SONID" ORDER BY sum(value) DESC '; | |
| $query = $conn->prepare($query); | |
| $query->execute(); | |
| databaseError($conn->errorInfo()); | |
| echo "<table> <thead> | |
| <tr> | |
| <th>SONID</th> | |
| <th>Total Contracts Value</th> | |
| <th>Number of Contracts</th> | |
| </tr> | |
| </thead>"; | |
| foreach ($query->fetchAll() as $row) { | |
| setlocale(LC_MONETARY, 'en_US'); | |
| $value = number_format(doubleval($row['sum']) , 2); | |
| echo ("<tr><td><A href=\"displaySON.php?SONID={$row['SONID']}\"><b>{$row['title']}</b></a></td><td>\$$value</td><td>{$row['count']}</td></tr>"); | |
| } | |
| echo "</table>"; | |
| } | |
| include_footer(); | |
| ?> | |