<?php
require_once("dbconn.php");
require_once("phpFunctions.php");

$headers = [
	"Batch Code", 
	"Batch Name", 
	"Bldg. No.", 
	"Check In Date", 
	"Breed Type", 
	"Hen Housed", 
	"Current", 
	"Previous", 
	"YTD",
	"JMB", 
	"XLR", 
	"LRG", 
	"MED", 
	"SML", 
	"PUL", 
	"PWE", 
	"SPW", 
	"B1", 
	"B2", 
	"B3", 
	"M.Dirty", 
	"B.Dirty",
	"JMB", 
	"XLR", 
	"LRG", 
	"MED", 
	"SML", 
	"PUL", 
	"PWE", 
	"SPW", 
	"B1", 
	"B2", 
	"B3", 
	"M.Dirty", 
	"B.Dirty",
	"JMB", 
	"XLR", 
	"LRG", 
	"MED", 
	"SML", 
	"PUL", 
	"PWE", 
	"SPW", 
	"B1", 
	"B2", 
	"B3", 
	"M.Dirty", 
	"B.Dirty", 
	"TOTAL", 
	"A.W.C", 
	"Age In Weeks", 
	"S.H.H.",
	"Status"
];

echo "<table id='MyTable_controller' class='ui display controller bd' style='display: none;'>";
echo "<thead><tr>";
echo "<th colspan='6'></th>
<th colspan='3' class='bg-dark text-center text-white text-uppercase border-bottom-0'>Egg Production</th>
<th colspan='13' class='bg-dark text-center text-white text-uppercase border-bottom-0' style='--bs-bg-opacity:0.9;'>Current</th>
<th colspan='13' class='bg-dark text-center text-white text-uppercase border-bottom-0' style='--bs-bg-opacity:0.8;'>Previous</th>
<th colspan='13' class='bg-dark text-center text-white text-uppercase border-bottom-0' style='--bs-bg-opacity:0.7;'>YTD</th>
<th colspan='5'></th>
</tr>";

echo "<tr>";


$eggCurrent = [9,10,11,12,13,14,15,16,17,18,19,20,21];
$eggPrevious = [22,23,24,25,26,27,28,29,30,31,32,33,34];
$eggYTD = [35,36,37,38,39,40,41,42,43,44,45,46,47];

foreach ($headers as $index => $headerText) 
{
	if ($index == 6 || $index == 7 || $index == 8) {
		echo "<th class='bg-dark text-center text-white text-uppercase'>{$headerText}</th>";
	} else if (in_array($index, $eggCurrent)) {
		echo "<th class='bg-dark text-center text-white text-uppercase' style='--bs-bg-opacity:0.9;'>{$headerText}</th>";
	} else if (in_array($index, $eggPrevious)) {
		echo "<th class='bg-dark text-center text-white text-uppercase' style='--bs-bg-opacity:0.8;'>{$headerText}</th>";
	} else if (in_array($index, $eggYTD)) {
		echo "<th class='bg-dark text-center text-white text-uppercase' style='--bs-bg-opacity:0.7;'>{$headerText}</th>";
	} else {
		echo "<th class='bg-light text-center text-uppercase'>{$headerText}</th>";
	}
}
echo "</tr></thead><tbody>";

$result = executeQuery($conn, "SELECT * FROM batch_details WHERE is_deleted = FALSE AND is_current = TRUE ORDER BY Status, CheckInDate");

if ($result === false) { 
	throw new Exception("Error executing query: " . $conn->error);
} 

while ($row = mysqli_fetch_assoc($result)) 
{
    echo "<tr class='bd-clickable' data-id='". $row["id"] . "'>";
	echo "<td class='align-baseline'>" . $row["BatchCode"] . "</td>";
	echo "<td class='align-baseline'><span class='bolded-text'>" . $row["BatchName"] . "</span></td>";
	
	$bldgNo = $row["BldgNo"];
    
    if (!empty($bldgNo)) {
		$items = explode(",", $bldgNo);
		
		$formattedItems = [];

		foreach ($items as $item) {
			$item = trim($item);
			$parts = explode("[", $item);
			$label = $parts[0];
			$valueParts = explode("]", $parts[1]);
			$value = $valueParts[0];
			$switch = trim($valueParts[1], "{}");

			$status = ($switch === "1") ? "<span class='bg-success rounded text-light px-1'>ON</span>" : "<span class='bg-danger rounded text-light px-1'>OFF</span>";

			$formattedItems[] = "<span class='bg-warning rounded text-dark px-1'>$label</span><span class='bg-dark rounded text-white px-1'>$value</span>{$status}";
		}

		$output = implode("<br>", $formattedItems);
	} else {
		$output = "";
	}

    echo "<td class=''>$output</td>";
	
	echo "<td class='align-baseline'>" . $row["CheckInDate"] . "</td>";
	echo "<td class='align-baseline'>" . $row["Breed"] . "</td>";
	echo "<td class='align-baseline'>" . number_format($row["HenHoused"]) . "</td>";

	$rrquery = "SELECT COALESCE(SUM(H_TOTAL), 0) as Prod_Current FROM input_production WHERE is_deleted = FALSE and is_current = TRUE AND BatchName = ?";
	$rresult = executeQuery($conn, $rrquery, "s", [$row["BatchName"]]);
	
	if ($rresult === false) { 
		throw new Exception("Error executing query: " . $conn->error);
	}
	
	$rrow = mysqli_fetch_assoc($rresult);
	
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.9;'>" . number_format($rrow["Prod_Current"]) . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.9;'>" . number_format($row["Prod_Previous"]) . "</td>";

	$Prod_YTD = $rrow["Prod_Current"] + $row["Prod_Previous"];
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.9;'>" . number_format($Prod_YTD) . "</td>";
	
	// START EGGSIZE
	$rrquery = "SELECT 
		COALESCE(SUM(JMB), 0) as cur_JMB,
		COALESCE(SUM(XLR), 0) as cur_XLR,
		COALESCE(SUM(LRG), 0) as cur_LRG,
		COALESCE(SUM(MED), 0) as cur_MED,
		COALESCE(SUM(SML), 0) as cur_SML,
		COALESCE(SUM(PUL), 0) as cur_PUL,
		COALESCE(SUM(PWE), 0) as cur_PWE,
		COALESCE(SUM(SPW), 0) as cur_SPW,
		COALESCE(SUM(B1), 0) as cur_B1,
		COALESCE(SUM(B2), 0) as cur_B2,
		COALESCE(SUM(B3), 0) as cur_B3,
		COALESCE(SUM(M_Dirty), 0) as cur_M_Dirty,
		COALESCE(SUM(B_Dirty), 0) as cur_B_Dirty
	FROM egg_weight
	WHERE 
	is_deleted = FALSE AND
	is_current = TRUE AND
	BatchName = ?";
	
	$rresult = executeQuery($conn, $rrquery, "s", [$row["BatchName"]]);
	
	if ($rresult === false) { 
		throw new Exception("Error executing query: " . $conn->error);
	} 
	
	$rrow = mysqli_fetch_assoc($rresult);
	
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.8;'>" . $rrow["cur_JMB"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.8;'>" . $rrow["cur_XLR"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.8;'>" . $rrow["cur_LRG"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.8;'>" . $rrow["cur_MED"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.8;'>" . $rrow["cur_SML"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.8;'>" . $rrow["cur_PUL"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.8;'>" . $rrow["cur_PWE"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.8;'>" . $rrow["cur_SPW"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.8;'>" . $rrow["cur_B1"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.8;'>" . $rrow["cur_B2"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.8;'>" . $rrow["cur_B3"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.8;'>" . $rrow["cur_M_Dirty"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.8;'>" . $rrow["cur_B_Dirty"] . "</td>";
	
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.7;'>" . $row["prev_JMB"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.7;'>" . $row["prev_XLR"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.7;'>" . $row["prev_LRG"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.7;'>" . $row["prev_MED"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.7;'>" . $row["prev_SML"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.7;'>" . $row["prev_PUL"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.7;'>" . $row["prev_PWE"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.7;'>" . $row["prev_SPW"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.7;'>" . $row["prev_B1"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.7;'>" . $row["prev_B2"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.7;'>" . $row["prev_B3"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.7;'>" . $row["prev_M_Dirty"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.7;'>" . $row["prev_B_Dirty"] . "</td>";
	
	$ytdQuery = "SELECT 
		COALESCE(SUM({$rrow['cur_JMB']} + prev_JMB), 0) as ytd_JMB,
		COALESCE(SUM({$rrow['cur_XLR']} + prev_XLR), 0) as ytd_XLR,
		COALESCE(SUM({$rrow['cur_LRG']} + prev_LRG), 0) as ytd_LRG,
		COALESCE(SUM({$rrow['cur_MED']} + prev_MED), 0) as ytd_MED,
		COALESCE(SUM({$rrow['cur_SML']} + prev_SML), 0) as ytd_SML,
		COALESCE(SUM({$rrow['cur_PUL']} + prev_PUL), 0) as ytd_PUL,
		COALESCE(SUM({$rrow['cur_PWE']} + prev_PWE), 0) as ytd_PWE,
		COALESCE(SUM({$rrow['cur_SPW']} + prev_SPW), 0) as ytd_SPW,
		COALESCE(SUM({$rrow['cur_B1']} + prev_B1), 0) as ytd_B1,
		COALESCE(SUM({$rrow['cur_B2']} + prev_B2), 0) as ytd_B2,
		COALESCE(SUM({$rrow['cur_B3']} + prev_B3), 0) as ytd_B3,
		COALESCE(SUM({$rrow['cur_M_Dirty']} + prev_M_Dirty), 0) as ytd_M_Dirty,
		COALESCE(SUM({$rrow['cur_B_Dirty']} + prev_B_Dirty), 0) as ytd_B_Dirty
	FROM batch_details
	WHERE 
	is_deleted = FALSE AND 
	is_current = TRUE AND 
	BatchName = ?";
	
	$ytdResult = executeQuery($conn, $ytdQuery, "s", [$row["BatchName"]]);
	
	if ($ytdResult === false) { 
		throw new Exception("Error executing query: " . $conn->error);
	} 
	
	$ytdRow = mysqli_fetch_assoc($ytdResult);
	
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.6;'>" . $ytdRow["ytd_JMB"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.6;'>" . $ytdRow["ytd_XLR"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.6;'>" . $ytdRow["ytd_LRG"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.6;'>" . $ytdRow["ytd_MED"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.6;'>" . $ytdRow["ytd_SML"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.6;'>" . $ytdRow["ytd_PUL"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.6;'>" . $ytdRow["ytd_PWE"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.6;'>" . $ytdRow["ytd_SPW"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.6;'>" . $ytdRow["ytd_B1"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.6;'>" . $ytdRow["ytd_B2"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.6;'>" . $ytdRow["ytd_B3"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.6;'>" . $ytdRow["ytd_M_Dirty"] . "</td>";
	echo "<td class='bg-dark text-white align-baseline' style='--bs-bg-opacity:.6;'>" . $ytdRow["ytd_B_Dirty"] . "</td>";
	
	$TOTAL = 0;
	foreach ($ytdRow as $columnTotal) 
	{
		$TOTAL += $columnTotal;
	}
	echo "<td class='align-baseline'><span class='bg-warning rounded text-dark px-1'>" . $TOTAL . "</span></td>";
	
	$AWC = 0;
	if ($TOTAL != 0) 
	{
		$AWC = number_format((($ytdRow["ytd_JMB"] * 89.5) + ($ytdRow["ytd_XLR"] * 75.14) + ($ytdRow["ytd_LRG"] * 67.89) + ($ytdRow["ytd_MED"] * 63.45) + ($ytdRow["ytd_SML"] * 59.55 ) + ($ytdRow["ytd_PUL"] * 55.68) +( $ytdRow["ytd_PWE"] * 51.85) + ($ytdRow["ytd_SPW"] * 49.86)) / $TOTAL, 2);
	}
	
	echo "<td class='align-baseline'><span class='bg-info rounded text-dark px-1'>" . $AWC . "</span></td>";
	// END EGGSIZE
	
	$AgeInWeeks = floor((strtotime(date("Y-m-d")) - strtotime($row["CheckInDate"])) / (7 * 24 * 60 * 60));
	
	echo "<td class='align-baseline'>" . $AgeInWeeks . "</td>";
	
	$shhquery = "SELECT Stnd_H_H_per FROM egg_standards WHERE is_deleted = FALSE AND is_current = TRUE AND BreedType = ? AND AgeInWeeks = ?";
	$shhresult = executeQuery($conn, $shhquery, "ss", [$row["Breed"],$AgeInWeeks]);
	
	if ($shhresult === false) { 
		throw new Exception("Error executing query: " . $conn->error);
	}
	
	$shhrow = ($shhresult && mysqli_num_rows($shhresult) > 0) ? mysqli_fetch_assoc($shhresult) : null;
	$stdHH = $shhrow["Stnd_H_H_per"] ?? 0;
	
	echo "<td class='align-baseline'>" . $stdHH . "</td>";
	
	$Status = "<span class='bg-danger rounded text-white px-1'>Inactive</span>";
	if ( $row["Status"] != 0 ) 
	{
		$Status = "<span class='bg-success rounded text-white px-1'>Active</span>";
	}
	echo "<td class='align-baseline'>" . $Status . "</td>";
	
	echo "</tr>";
}
echo "</tbody></table>";
?>