Western Beach Historical

[insert_php]
$db = new mysqli(‘localhost’, ‘chadwick_waterne’, ‘mYc!ean_?Water16’, ‘chadwick_waterdata’);
$beachID = isset($_GET[‘beachid’]) ? $_GET[‘beachid’] : 6;
$beachData = array();
$beachMeta = array();
$dropDownData = array();
$failGuide = array(
‘Coliform’ => 0,
‘Ecoli’ => 0,
‘Faecal’ => 0
);
$failMandatory = array(
‘Coliform’ => 0,
‘Ecoli’ => 0,
‘Faecal’ => 0
);
if($db->connect_errno > 0){
die(‘Unable to connect to database [‘ . $db->connect_error . ‘]’);
}

// Retrieve Beach Name
$beachQuery = “SELECT * FROM `tblBeachNew` WHERE `BeachID`='{$beachID}’ LIMIT 1;”;
if(!$beachTable = $db->query($beachQuery)) {
die(“error getting [{$db->error}]”);
}
$beachMeta = $beachTable->fetch_assoc();

// Retreive data for this beach
$chosenYear = isset($_POST[‘resultYear’]) ? $_POST[‘resultYear’] : ‘2014-04-20′;

$sql = “SELECT `Date`, `Ecoli`, `Faecal`, `Coliform` FROM `tblBeachResultsNew` WHERE `BeachID`='{$beachID}’ “;
if($chosenYear !== false) {
// sanitize chosenYear by converting it to a date
$chosenYear = date(‘Y-m-d’, strtotime($chosenYear));
$sql .= “AND YEAR(`Date`)='{$chosenYear}’ “;
}
$sql .= “ORDER BY `Date` DESC;”;

if(!$beachDataTable = $db->query($sql)) {
die(“error getting [{$db->error}]”);
}

/* *
* Fail code index
* 0 : Coliform Guide
* 1 : Coliform Mandatory
* 2 : Ecoli Guide
* 3 : Ecoli Mandatory
* 4 : Faecal Guide
* 5 : Faecal Mandatory
* */

// Iterate over data, counting and marking failures
while($row = $beachDataTable->fetch_assoc()) {
// fishtacos – we can dry this up if we have time
// Check if we fail guide value
if($row[‘Coliform’] > $beachMeta[‘GuideColif_Value’]) {
// Mark for this index
$row[‘failures’][] = 0;
// Increment count of failures
$failGuide[‘Coliform’]++;
// We failed guide, so see if we fail Mandatory
if($row[‘Coliform’] > $beachMeta[‘ManColif_Value’]) {
// Mark fail index
$row[‘failures’][] = 1;
// Increment
$failMandatory[‘Coliform’]++;
}
}
if($row[‘Ecoli’] >= $beachMeta[‘GuideEcoli_Value’]) {
$row[‘failures’][] = 2;
$failGuide[‘Ecoli’]++;
if($row[‘Ecoli’] > $beachMeta[‘ManEcoli_Value’]) {
$row[‘failures’][] = 3;
$failMandatory[‘Ecoli’]++;
}
}
if($row[‘Faecal’] > $beachMeta[‘GuideFaecal_Value’]) {
$row[‘failures’][] = 4;
$failGuide[‘Faecal’]++;
if ($beachMeta[‘ManFaecal_Value’] != null){
if($row[‘Faecal’] > $beachMeta[‘ManFaecal_Value’]) {
$row[‘failures’][] = 5;
$failMandatory[‘Faecal’]++;
}
}
}
// Format date we’ll output later
$row[‘Date’] = date(‘j M Y’, strtotime($row[‘Date’]));
$beachData[] = $row;
}

// Retreive list of years for beachID
$sql = “SELECT DISTINCT YEAR(`Date`) as year FROM `tblBeachResultsNew` WHERE `BeachID`='{$beachID}’ ORDER BY year DESC;”;
if(!$beachYearsData = $db->query($sql)) {
die(“error getting [{$db->error}]”);
}
while($year = $beachYearsData->fetch_assoc()) {

if($year[‘year’]!=2015 and $year[‘year’]!=2016)
{
$dropDownData[$year[‘year’]] = $year[‘year’] . ‘-01-01’;
}
}

// Checks if a given row fails for mandatory, and if not, check for guide
// Return empty, red or green color.
function ifFailed($day, $guideIndex) {
if(isset($day[‘failures’])) {
if(in_array($guideIndex + 1, $day[‘failures’])) {
return ” style=\”background-color:#FF3B57;color:white;\””;
}
if(in_array($guideIndex, $day[‘failures’])) {
return ” style=\”background-color:#73C11A;color:white;\””;
}
}
return “”;
}
[/insert_php]

[insert_php] echo $beachMeta[‘Beach’]; [/insert_php]

Results For:

[insert_php]
foreach($dropDownData as $year => $value) {
$selected = “”;

if(date(‘Y’, strtotime($chosenYear)) == $year) {
$selected = “selected “;
}

echo ‘

‘ . $year . ‘‘;
}
[/insert_php]


(To view previous results select the desired year)

NB – Where the laboratory has been unable to count the type of bacterial colonies the cell’s data is shown as blank

[insert_php]
// Crazy formatting due to the way the insert_php plugin handles eval
foreach($beachData as $day) {
echo ‘

‘ . $day[‘Coliform’] . ‘

‘;

echo ‘

‘;

echo ‘

‘;

echo ‘

‘;
}
[/insert_php]

Date	TOTAL COLIFORMS/100ml	E.COLI /100ml	FAECAL STREPTOCCUS /100ml
MANDATORY STANDARD	[insert_php] echo $beachMeta[‘ManColif’]; [/insert_php]	[insert_php] echo $beachMeta[‘ManEcoli’]; [/insert_php]	[insert_php] echo $beachMeta[‘ManFaecal’]; [/insert_php]
GUIDE VALUES	[insert_php] echo $beachMeta[‘GuideColif’]; [/insert_php]	[insert_php] echo $beachMeta[‘GuideEcoli’]; [/insert_php]	[insert_php] echo $beachMeta[‘GuideFaecal’]; [/insert_php]
Failed Mandatory	[insert_php] echo $failMandatory[‘Coliform’]; [/insert_php]	[insert_php] echo $failMandatory[‘Ecoli’]; [/insert_php]	[insert_php] echo $failMandatory[‘Faecal’]; [/insert_php]
Failed Guide	[insert_php] echo $failGuide[‘Coliform’]; [/insert_php]	[insert_php] echo $failGuide[‘Ecoli’]; [/insert_php]	[insert_php] echo $failGuide[‘Faecal’]; [/insert_php]
‘ . $day[‘Date’] . ‘	‘ . $day[‘Ecoli’] . ‘	‘ . $day[‘Faecal’] . ‘