gitphp 0.2.9.1 :: contractdashboard.git/blobdiff

blob:a/displaySupplier.php -> blob:b/displaySupplier.php

<?php	<?php
include_once ("./lib/common.inc.php");
	include_once("./lib/common.inc.php");
if ($_REQUEST['supplier']) {	if ($_REQUEST['supplier']) {
	$supplierS = htmlentities(strip_tags($_REQUEST['supplier']));
include_header("Supplier");	$title = $supplierName;
$supplierS = htmlentities(strip_tags($_REQUEST['supplier']));	if (isset($supplierABN)) {
echo '<img src="graphs/displayMethodCountGraph.php?supplier=' . stripslashes($supplierS) . '">';	$query = 'SELECT text_mode("supplierName") AS "supplierName"
echo '<img src="graphs/displayCnCGraph.php?supplier=' . stripslashes($supplierS) . '">';	FROM contractnotice
echo '<img src="graphs/displayMethodValueGraph.php?supplier=' . stripslashes($supplierS) . '">';	WHERE "supplierABN" = :supplierABN
/*lobbyist ties	GROUP BY "supplierABN"';
	$query = $conn->prepare($query);
links to ABR/ASIC/Google News/ASX/Court records	$query->bindParam(":supplierABN", $supplierABN);
	$query->execute();
total value to various agencies (bar graph)	databaseError($conn->errorInfo());
	$title = reset($query->fetchAll())['supplierName'];
spread procurement methods + percent consultancies + percent confidential (bar graph)	}
	include_header(str_replace("%", "", $title));
spread of contract values	echo '<center><h1>' . str_replace("%", "", $title) . '</h1></center>';

spread of industries (textual?)*/	if (isset($supplierABN)) {
$query = "SELECT CNID, description, value, agencyName, category, contractStart, supplierName	echo "<b>ABN:</b> <a href=\"https://abr.business.gov.au/SearchByAbn.aspx?abn=$supplierABN\">$supplierABN</a>";
FROM `contractnotice` WHERE
$supplierQ $standardQ	$cleansedName = '%'.cleanseName($title).'%';
ORDER BY value DESC";	$query = 'SELECT DISTINCT ON ("supplierABN") "supplierName","supplierABN" FROM contractnotice WHERE "supplierName" ILIKE :cleansedName and "supplierABN" != :supplierABN';
echo $query;	$query = $conn->prepare($query);
$result = mysql_query($query);	$query->bindParam(":cleansedName", $cleansedName);
echo '<img src="graphs/displayMethodCountGraph.php?month=' . stripslashes($supplier) . '">';	$query->bindParam(":supplierABN", $supplierABN);
echo '<img src="graphs/displayCnCGraph.php?month=' . stripslashes($supplier) . '">';	$query->execute();
	databaseError($conn->errorInfo());
echo "<table> <thead>	echo "<br/><b>Similar to:</b> ";
	foreach ($query->fetchAll() as $row) {
	echo "<a href='displaySupplier.php?supplier={$row['supplierABN']}-".urlencode($row['supplierName'])."'> {$row['supplierName']} (ABN: {$row['supplierABN']})</a>, ";
	}

	$query = 'SELECT distinct on (lower("supplierName")) "supplierName" from contractnotice where "supplierABN" = :supplierABN';
	$query = $conn->prepare($query);
	$query->bindParam(":supplierABN", $supplierABN);
	$query->execute();
	databaseError($conn->errorInfo());
	$names = Array();
	foreach ($query->fetchAll() as $row) {
	$names[cleanseName($row[0])] = $row[0];
	}
	if (count($names) < 20) {
	echo "<br/><b>Also known as:</b> " . implode(', ', array_values($names))."<br/>";
	} else {
	echo "<details>
	<summary>Also known as ...</summary>
	<p>".implode(', ', array_values($names))."</p>
	</details>";
	}

	}


	$query = '
	SELECT
	sum((consultancy = \'Yes\')::int) AS consultancy,
	sum(("confidentialityContract" = \'Yes\')::int) AS "confidentialityContract",
	sum(("confidentialityOutputs" = \'Yes\')::int) AS "confidentialityOutputs",
	sum((("procurementMethod" = \'Open\' OR "procurementMethod" = \'Open tender\') AND "SONID" IS null)::int) AS open,
	sum((("procurementMethod" = \'Open\' OR "procurementMethod" = \'Open tender\') AND "SONID" IS NOT null)::int) AS "openSON",
	sum(("procurementMethod" = \'Prequalified tender\' OR "procurementMethod" = \'Select\')::INT) AS prequalified,
	sum(("procurementMethod" = \'Direct\' OR "procurementMethod" = \'Limited tender\')::int) AS limited,
	sum("value") as total_value,
	COUNT(*) AS total
	FROM contractnotice
	WHERE' . $supplierQ . " " . $yearQ . " " .$standardQ ;
	$query = $conn->prepare($query);
	if (isset($supplierABN)) {
	$query->bindParam(":supplierABN", $supplierABN);
	} else {
	$query->bindParam(":supplierName", $supplierName);
	}
	$query->execute();
	databaseError($conn->errorInfo());

	$stats = reset($query->fetchAll());
	show_stats($stats);

	$query = 'SELECT category, min("categoryUNSPSC") AS "categoryUNSPSC", count(*) AS count, sum(value) AS value FROM contractnotice
	WHERE ' . $supplierQ . " $yearQ $standardQ ". ' GROUP BY category ORDER BY count(*) DESC LIMIT 10';
	$query = $conn->prepare($query);
	if (isset($supplierABN)) {
	$query->bindParam(":supplierABN", $supplierABN);
	} else {
	$query->bindParam(":supplierName", $supplierName);
	}
	$query->execute();
	databaseError($conn->errorInfo());


	echo "<h3>Categories</h3><table> <thead>
	<tr>
	<th>Category</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);
	$category = urlencode($row['category']);
	echo("<tr>
	<td><B><a href='displayCategory.php?category=$category'>{$row['category']}</a></b></td>
	<td>{$row['count']}</td>
	<td>\$$value</td>
	</tr>");
	}
	echo "</table><br/>";


	/$query = 'SELECT SUM("value") AS "value", count(), text_mode(contractnotice."agencyName") AS agencyname FROM contractnotice JOIN agency_nametoabn ON contractnotice."agencyName"=agency_nametoabn."agencyName"
	WHERE ' . $yearQ . ' ' . $supplierQ . ' "childCN" IS NULL
	GROUP BY abn ORDER BY SUM("value") DESC';*/
	$query = 'SELECT SUM("value") AS "value", count(*), "agencyName" AS agencyname FROM contractnotice
	WHERE ' . $yearQ . ' ' . $supplierQ . ' "childCN" IS NULL GROUP BY "agencyName" ORDER BY SUM("value") DESC';

	$query = $conn->prepare($query);
	if (isset($supplierABN)) {
	$query->bindParam(":supplierABN", $supplierABN);
	} else {
	$query->bindParam(":supplierName", $supplierName);
	}
	$query->execute();
	databaseError($conn->errorInfo());

	echo "<h3>Agencies</h3><table> <thead>
	<tr>
	<th>Agency</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);
	$agency =urlencode($row['agencyname']);
	echo("<tr>
	<td><B><a href=\"displayAgency.php?agency=$agency\">{$row['agencyname']}</a></b></td>
	<td>{$row['count']}</td>
	<td>\$$value</td>
	</tr>");
	}
	echo "</table><br/>";

	$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 (isset($supplierABN)) {
	$query->bindParam(":supplierABN", $supplierABN);
	} else {
	$query->bindParam(":supplierName", $supplierName);
	}
	$query->execute();
	databaseError($conn->errorInfo());
	$sonrows = "";
	foreach ($query->fetchAll() as $row) {
	setlocale(LC_MONETARY, 'en_US');
	$value = number_format(doubleval($row['value']), 2);
	$sonrows .= "<tr>
	<td><B><a href=\"displaySON.php?SONID={$row['SONID']}\">{$row['title']}</a></b></td>
	<td>{$row['count']}</td>
	<td>\$$value</td>
	</tr>";
	}
	if (strlen($sonrows) > 1) {
	echo "<h3>Standing Offers/Panels</h3><table> <thead>
	<tr>
	<th>Standing Offer</th>
	<th>Contracts Count</th>
	<th>Total Contract Value</th>
	</tr>
	</thead>$sonrows</table><br/>";
	}
	echo "<table><h3>Contracts</h3> <thead>
<tr>	<tr>
<th>Contract Notice Number</th>	<th>Contract Notice Number</th>
<th>Contract Description</th>	<th>Contract Description</th>
<th>Total Contract Value</th>	<th>Total Contract Value</th>
<th>Agency</th>	<th>Agency</th>
<th>Contract Start Date</th>	<th>Contract Start Date</th>
<th>Supplier</th>	<th>Supplier</th>
</tr>	</tr>
</thead>";	</thead>";
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {	$query = 'SELECT "CNID", "description", "value", "agencyName", "category",
setlocale(LC_MONETARY, 'en_US');	"contractStart", "supplierName"
$value = number_format(doubleval($row['value']) , 2);	FROM contractnotice WHERE ' . $yearQ . ' ' .
echo ("<tr>	$supplierQ . ' ' . $standardQ
	. ' ORDER BY VALUE DESC';

	$query = $conn->prepare($query);


	if (isset($supplierABN)) {
	$query->bindParam(":supplierABN", $supplierABN);
	} else {
	$query->bindParam(":supplierName", $supplierName);
	}
	$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><a href=\"displayContract.php?CNID={$row['CNID']}\">{$row['CNID']}</a></td>
<td><b>{$row['description']}</b></a></td>	<td><b>{$row['description']}</b></a></td>
<td>\$$value</td><td>{$row['agencyName']}</td>	<td>\$$value</td><td>{$row['agencyName']}</td>
<td>{$row['contractStart']}</td>	<td>{$row['contractStart']}</td>
<td>{$row['supplierName']}</td>	<td>{$row['supplierName']}</td>
</tr>");	</tr>");
}	}
echo "</table>";	echo "</table>";
mysql_free_result($result);
} else {	} else {
/*	/*
histograph of supplier size/value	histograph of supplier size/value
*/	*/
include_header("Suppliers");	include_header("Suppliers");
$query = "SELECT SUM(value) as val, supplierName, supplierABN	suppliersGraph();
FROM `contractnotice`	$query = 'SELECT SUM("value") AS val, text_mode("supplierName") AS supplierName, "supplierABN",(
WHERE childCN = 0	CASE WHEN "supplierABN" != 0 THEN lower("supplierABN"::TEXT) ELSE lower("supplierName") END) AS supplierID
GROUP BY supplierName	FROM contractnotice
ORDER BY val DESC	WHERE ' . $yearQ . ' "childCN" IS NULL
LIMIT 100";	GROUP BY supplierID,"supplierABN"
$result = mysql_query($query);	ORDER BY val DESC';
echo "<table> <thead>	if ($yearQ == '') $query .=' LIMIT 1000';
<tr>	$query = $conn->prepare($query);
	$query->execute();
	databaseError($conn->errorInfo());
	if ($query->rowCount() > 999 && $yearQ == '') {
	// if 1000 records warn too many results
	print "<b> More than 1000 results found so only first 1000 shown. Please filter by year to see all results </b>";
	}
	echo "<table> <thead>
	<tr>
	<th>Position</th>
<th>Supplier</th>	<th>Supplier</th>
<th>Total Contract Value</th>	<th>Total Contract Value</th>
</tr>	</tr>
</thead>";	</thead>";
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {	$i = 1;
setlocale(LC_MONETARY, 'en_US');	foreach ($query->fetchAll() as $row) {
$value = number_format(doubleval($row['val']) , 2);	setlocale(LC_MONETARY, 'en_US');
$supplier = stripslashes($row['supplierABN'].'-'.$row['supplierName']);	$value = number_format(doubleval($row['val']), 2);
echo ("<tr><td><b><a href=\"displaySupplier.php?supplier={$supplier}\">".ucsmart($row['supplierName'])."</a></b></td><td>\$$value</td></tr>\n");	$supplier = stripslashes($row['supplierABN'] . '-' . $row['suppliername']);
}	echo("<tr><td>$i</td><td><b><a href=\"displaySupplier.php?supplier={$supplier}\">" . ucsmart($row['suppliername']) . "</a></b></td><td>\$$value</td></tr>\n");
echo "</table>";	$i++;
mysql_free_result($result);	}
	echo "</table>";
}	}
include_footer();	include_footer();
?>	?>