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

$ProductionDate = isset($_SESSION["ProductionDate"]) ? $_SESSION["ProductionDate"] : date("Y-m-d");
$dayOfWeek = date("l", strtotime($ProductionDate));

$headers = [
	"Batch Name", 
	"Age", 
	"S.W", 
	"A.W", 
	"S.W.C", 
	"A.W.C", 
	"S.EM",
	"A.EM",
	"JMB", 
	"XLR", 
	"LRG", 
	"MED", 
	"SML", 
	"PUL", 
	"PWE", 
	"SPW", 
	"B1", 
	"B2", 
	"B3", 
	"Crack",
	"Dirty",
	"DEV %", 
];

echo "<table id='MyTable_es' class='ui display es_report' style='display: none;'>";
echo "<thead><tr>
<th colspan='4'><img src='/img/original-logo-pg7.jpeg' alt='PG7' style='float: left; width: 100px; margin: 5px 0 0 5px;' /></th>
<th colspan='13'><h1>Daily Egg Size Report</h1></th>
<th colspan='5' class='print-btn-placeholder'></th>
</tr>";

echo "<tr>
<th colspan='6'>Calendar Year Target:</th>
<th colspan='6'>Production Percentage - 90%</th>
<th colspan='5'>Livability % at 80wks - 90%</th>
<th colspan='5'>Depletion Rate - 0.5%</th>
</tr>";

echo "<tr>
<th colspan='22'><h2>Daily Eggsize and Shipment Recon Report</h2></th>
</tr>";
echo "<tr>
<th colspan='8'>Key Performance Indicator</th>
<th colspan='10'>Egg Size</th>
<th colspan='2'>{$ProductionDate}</th>
<th colspan='2'>{$dayOfWeek}</th>
</tr>";

echo "<tr>";
foreach ($headers as $headerText) 
{
	echo "<th>{$headerText}</th>";
}
echo "</tr></thead><tbody>";

$query = "SELECT 
						ew.id, 
						ew.BatchName, 
						ew.Dev, 
						ew.AgeInWeeks, 
						ew.A_W, 
						ew.JMB, 
						ew.XLR, 
						ew.LRG, 
						ew.MED, 
						ew.SML, 
						ew.PUL, 
						ew.PWE, 
						ew.SPW, 
						bd.Breed,
						COALESCE(ROUND((ew.B1 / ew.Total) * 100, 1), 0) as B1, 
						COALESCE(ROUND((ew.B2 / ew.Total) * 100, 1), 0) as B2, 
						COALESCE(ROUND((ew.B3 / ew.Total) * 100, 1), 0) as B3, 
						ew.Total, 
						ew.M_Dirty,
						ew.B_Dirty,
						ew.Dirty,
						es.EggWeight_Weeks,
						es.EggWeight_Cum,
						es.EggMass_Weeks,
						bd.prev_JMB,
						bd.prev_XLR,
						bd.prev_LRG,
						bd.prev_MED,
						bd.prev_SML,
						bd.prev_PUL,
						bd.prev_PWE,
						bd.prev_SPW,
						COALESCE(SUM(ip.H_TOTAL), 0) as HenDayTotal, 
						COALESCE(SUM(ip.Hen_Day), 0) as Pop
					FROM egg_weight ew
						LEFT JOIN batch_details bd ON ew.BatchName = bd.BatchName AND bd.is_deleted = FALSE AND bd.is_current = TRUE
						LEFT JOIN egg_standards es ON es.BreedType = bd.Breed AND es.AgeInWeeks = ew.AgeInWeeks AND es.is_deleted = FALSE AND es.is_current = TRUE
						LEFT JOIN input_production ip ON ew.BatchName = ip.BatchName AND ew.ProductionDate = ip.ProductionDate AND ip.is_deleted = FALSE AND ip.is_current = TRUE
					WHERE ew.ProductionDate = ? AND ew.is_deleted = FALSE AND ew.is_current = TRUE
						GROUP BY ew.BatchName
						ORDER BY ew.AgeInWeeks DESC";

$result = executeQuery($conn, $query, "s", [$ProductionDate]);

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

while ($row = mysqli_fetch_assoc($result)) 
{
	echo "<tr>";
    echo "<td>" . $row["BatchName"] . "</td>";
	echo "<td>" . $row["AgeInWeeks"] . "</td>";
	echo "<td>" . number_format($row["EggWeight_Weeks"], 2) . "</td>";
	echo "<td>" . $row["A_W"] . "</td>";
	echo "<td>" . number_format($row["EggWeight_Cum"], 2) . "</td>";
	
	$startDate = "2024-08-01";
	$endDate = $ProductionDate;
	
	$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 BatchName = ? AND ProductionDate >= ? AND ProductionDate <= ? AND is_deleted = FALSE AND is_current = TRUE";
	$rresult = executeQuery($conn, $rrquery, "sss", [$row["BatchName"], $startDate, $endDate]);
	
	if ($rresult === false) { 
		throw new Exception("Error executing query: " . $conn->error);
	} 
	
	$rrow = mysqli_fetch_assoc($rresult);
	
	$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);	
	
	$ytdColumns = [
		"ytd_JMB", "ytd_XLR", "ytd_LRG", "ytd_MED", "ytd_SML", "ytd_PUL", "ytd_PWE", "ytd_SPW"
	];

	$TOTAL = 0;
	foreach ($ytdRow as $columnTotal) 
	{
		$TOTAL += $columnTotal;
	}
	
	//89.5	 75.14 	 67.89 	 63.45 	 59.55 	 55.68 	 51.85 	 49.86 
	$AWC = 0;
	if ($TOTAL != 0) 
	{
		$AWC = (($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;
	}

	echo "<td>" . number_format($AWC, 2) . "</td>";
	
	echo "<td>" . $row["EggMass_Weeks"] . "</td>";
	
	//AW x A.H.D.% (Egg Production) Same ProductionDate
	// AHD Calculation
	$AHD = 0;
    if (!empty($row["HenDayTotal"]) && !empty($row["Pop"])) {
        $AHD = ($row["HenDayTotal"] / $row["Pop"]);
    }
	
	echo "<td>" . number_format($row["A_W"] * $AHD, 2) . "</td>";
	
	$JMB = ($row["JMB"] !== 0 && $row["Total"] !== 0) ? ($row["JMB"] / $row["Total"]) * 100 : 0;
	echo "<td>" . number_format($JMB, 1) . "%</td>";
	
	$XLR = ($row["XLR"] !== 0 && $row["Total"] !== 0) ? ($row["XLR"] / $row["Total"]) * 100 : 0;
	echo "<td>" . number_format($XLR, 1) . "%</td>";

	$LRG = ($row["LRG"] !== 0 && $row["Total"] !== 0) ? ($row["LRG"] / $row["Total"]) * 100 : 0;
	echo "<td>" . number_format($LRG, 1) . "%</td>";
	
	$MED = ($row["MED"] !== 0 && $row["Total"] !== 0) ? ($row["MED"] / $row["Total"]) * 100 : 0;
	echo "<td>" . number_format($MED, 1) . "%</td>";
	
	$SML = ($row["SML"] !== 0 && $row["Total"] !== 0) ? ($row["SML"] / $row["Total"]) * 100 : 0;
	echo "<td>" . number_format($SML, 1) . "%</td>";
	
	$PUL = ($row["PUL"] !== 0 && $row["Total"] !== 0) ? ($row["PUL"] / $row["Total"]) * 100 : 0;
	echo "<td>" . number_format($PUL, 1) . "%</td>";
	
	$PWE = ($row["PWE"] !== 0 && $row["Total"] !== 0) ? ($row["PWE"] / $row["Total"]) * 100 : 0;
	echo "<td>" . number_format($PWE, 1) . "%</td>";
	
	$SPW = ($row["SPW"] !== 0 && $row["Total"] !== 0) ? ($row["SPW"] / $row["Total"]) * 100 : 0;
	echo "<td>" . number_format($SPW, 1) . "%</td>";
	
	$B1 = ($row["B1"] !== 0) ? $row["B1"] : 0;
	echo "<td>". number_format($B1, 1) . "%</td>";
	
	$B2 = ($row["B2"] !== 0) ? $row["B2"] : 0;
	echo "<td>". number_format($B2, 1) . "%</td>";
	
	$B3 = ($row["B3"] !== 0) ? $row["B3"] : 0;
	echo "<td>". number_format($B3, 1) . "%</td>";
	
	$Crack = $row["B1"] + $row["B2"] + $row["B3"];
	echo "<td>". number_format($Crack, 1) . "%</td>";
	
	$Dirty = ($row["Dirty"] !== 0 && $row["Total"] !== 0) ? ($row["Dirty"] / $row["Total"]) * 100 : 0;
	echo "<td>" . number_format($Dirty, 1) . "%</td>";
	
	echo "<td>" . number_format($row["Dev"], 1) . "%</td>";
	
	echo "</tr>"; 
}
$query = "SELECT 
						COALESCE(SUM(JMB), 0) as JMB_tally, 
						COALESCE(SUM(XLR), 0) as XLR_tally, 
						COALESCE(SUM(LRG), 0) as LRG_tally,  
						COALESCE(SUM(MED), 0) as MED_tally, 
						COALESCE(SUM(SML), 0) as SML_tally, 
						COALESCE(SUM(PUL), 0) as PUL_tally, 
						COALESCE(SUM(PWE), 0) as PWE_tally, 
						COALESCE(SUM(SPW), 0) as SPW_tally, 
						COALESCE(SUM(B1), 0) as B1_tally, 
						COALESCE(SUM(B2), 0) as B2_tally, 
						COALESCE(SUM(B3), 0) as B3_tally, 
						COALESCE(SUM(Dirty), 0) as Dirty_tally, 
						COALESCE(SUM(Total), 0) as Total_tally
					FROM egg_weight 
					WHERE ProductionDate = ? AND is_deleted = FALSE AND is_current = TRUE";

$result = executeQuery($conn, $query, "s", [$ProductionDate]);

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

$JMB = $XLR = $LRG = $MED = $SML = $PUL = $PWE = $SPW = $B1 = $B2 = $B3 = $Crack = $Dirty = $Total = 0;
$subtotal1 = $subtotal2 = $subtotal3 = $subtotal4 = $subtotal5 = $subtotal6 = $subtotal7 = $subtotal8 = $subtotal9 = $subtotal10 = $subtotal11 = $subtotal12 = $subtotal13 = 0;

if ($result) {
	while ($row = mysqli_fetch_assoc($result)) 
	{
		$JMB = $row["JMB_tally"];
		$XLR = $row["XLR_tally"];
		$LRG = $row["LRG_tally"];
		$MED = $row["MED_tally"];
		$SML = $row["SML_tally"];
		$PUL = $row["PUL_tally"];
		$PWE = $row["PWE_tally"];
		$SPW = $row["SPW_tally"];
		$B1 = $row["B1_tally"];
		$B2 = $row["B2_tally"];
		$B3 = $row["B3_tally"];
		$Crack = $B1 + $B2 + $B3;
		$Dirty = $row["Dirty_tally"];
		$Total = $row["Total_tally"];
		
		$sumOfegg = $JMB + $XLR + $LRG + $MED + $SML + $PUL + $PWE + $SPW + $B1 + $B2 + $B3 + $Dirty;
		
		if ($Total > 0) {
			$subtotal1 = number_format(($JMB / $Total) * 100, 1);
			$subtotal2 = number_format(($XLR / $Total) * 100, 1);
			$subtotal3 = number_format(($LRG / $Total) * 100, 1);
			$subtotal4 = number_format(($MED / $Total) * 100, 1);
			$subtotal5 = number_format(($SML / $Total) * 100, 1);
			$subtotal6 = number_format(($PUL / $Total) * 100, 1);
			$subtotal7 = number_format(($PWE / $Total) * 100, 1);
			$subtotal8 = number_format(($SPW / $Total) * 100, 1);
			$subtotal9 = number_format(($B1 / $Total) * 100, 1);
			$subtotal10 = number_format(($B2 / $Total) * 100, 1);
			$subtotal11 = number_format(($B3 / $Total) * 100, 1);
			$subtotal12 = number_format($subtotal9 + $subtotal10 + $subtotal11, 1);
			$subtotal13 = number_format(($Dirty / $Total) * 100, 1);
		}
	}
}

echo "</tbody><tfoot class='print-footer'>";
echo "<tr><td colspan='2'>Subtotal</td>
<td colspan='6'></td>
<td>{$subtotal1}%</td>
<td>{$subtotal2}%</td>
<td>{$subtotal3}%</td>
<td>{$subtotal4}%</td>
<td>{$subtotal5}%</td>
<td>{$subtotal6}%</td>
<td>{$subtotal7}%</td>
<td>{$subtotal8}%</td>
<td>{$subtotal9}%</td>
<td>{$subtotal10}%</td>
<td>{$subtotal11}%</td>
<td>{$subtotal12}%</td>
<td>{$subtotal13}%</td>
<td></td></tr>";
echo "<tr><td colspan='2'>Total eggs classified</td>
<td colspan='6'></td>
<td>{$JMB}</td>
<td>{$XLR}</td>
<td>{$LRG}</td>
<td>{$MED}</td>
<td>{$SML}</td>
<td>{$PUL}</td>
<td>{$PWE}</td>
<td>{$SPW}</td>
<td>{$B1}</td>
<td>{$B2}</td>
<td>{$B3}</td>
<td>{$sumOfegg}</td>
<td>{$Dirty}</td>
<td></td></tr>";

$dr_customer_name = "PG 7 - CEBU";

$query = "SELECT * FROM transactions WHERE  dr_date_created = ? AND dr_customer_name = ? AND is_deleted = FALSE AND is_current = TRUE";
$result = executeQuery($conn, $query, "ss", [$dr_customer_name, $ProductionDate]);

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

$dr_JMB = $dr_XLR = $dr_LRG = $dr_MED = $dr_SML = $dr_PUL = $dr_PWE = $dr_SPW = $dr_B1 = $dr_B2 = $dr_B3 = $dr_Total = $dr_Dirty = 0;

if ($result) {
	while ($row = mysqli_fetch_assoc($result)) 
	{
		$dr_JMB = $row["dr_jmb_trays"] * 15;
		$dr_XLR = $row["dr_xlr_trays"] * 30;
		$dr_LRG = $row["dr_lrg_trays"] * 30;
		$dr_MED = $row["dr_med_trays"] * 30;
		$dr_SML = $row["dr_sml_trays"] * 30;
		$dr_PUL = $row["dr_pul_trays"] * 30;
		$dr_PWE = $row["dr_pwe_trays"] * 30;
		$dr_SPW = $row["dr_spw_trays"] * 30;
		$dr_B1 = $row["dr_b1_trays"] * 30;
		$dr_B2 = $row["dr_b2_trays"] * 30;
		$dr_B3 = $row["dr_b3_trays"] * 30;
		$dr_Dirty = $row["dr_dirty_trays"] * 30;
		$dr_Total = $dr_JMB + $dr_XLR + $dr_LRG + $dr_MED + $dr_SML + $dr_PUL + $dr_PWE + $dr_SPW + $dr_B1 + $dr_B2 + $dr_B3 + $dr_Dirty;
	}
}

echo "<tr><td colspan='2'>Total eggs shipped</td>
<td colspan='6'></td>
<td>" . round(numberFormatPrecision($dr_JMB, 2, ".")) . "</td>
<td>" . round(numberFormatPrecision($dr_XLR, 2, ".")) . "</td>
<td>" . round(numberFormatPrecision($dr_LRG, 2, ".")) . "</td>
<td>" . round(numberFormatPrecision($dr_MED, 2, ".")) . "</td>
<td>" . round(numberFormatPrecision($dr_SML, 2, ".")) . "</td>
<td>" . round(numberFormatPrecision($dr_PUL, 2, ".")) . "</td>
<td>" . round(numberFormatPrecision($dr_PWE, 2, ".")) . "</td>
<td>" . round(numberFormatPrecision($dr_SPW, 2, ".")) . "</td>
<td>" . round(numberFormatPrecision($dr_B1, 2, ".")) . "</td>
<td>" . round(numberFormatPrecision($dr_B2, 2, ".")) . "</td>
<td>" . round(numberFormatPrecision($dr_B3, 2, ".")) . "</td>
<td>" . round(numberFormatPrecision($dr_Total, 2, ".")) . "</td>
<td>" . round(numberFormatPrecision($dr_Dirty, 2, ".")) . "</td>
<td></td></tr>";

$diff_JMB = $JMB - round(numberFormatPrecision($dr_JMB, 2, "."));
$diff_XLR = $XLR - round(numberFormatPrecision($dr_XLR, 2, "."));
$diff_LRG = $LRG - round(numberFormatPrecision($dr_LRG, 2, "."));
$diff_MED = $MED - round(numberFormatPrecision($dr_MED, 2, "."));
$diff_SML = $SML - round(numberFormatPrecision($dr_SML, 2, "."));
$diff_PUL = $PUL - round(numberFormatPrecision($dr_PUL, 2, "."));
$diff_PWE = $PWE - round(numberFormatPrecision($dr_PWE, 2, "."));
$diff_SPW = $SPW - round(numberFormatPrecision($dr_SPW, 2, "."));
$diff_B1 = $B1 - round(numberFormatPrecision($dr_B1, 2, "."));
$diff_B2 = $B2 - round(numberFormatPrecision($dr_B2, 2, "."));
$diff_B3 = $B3 - round(numberFormatPrecision($dr_B3, 2, "."));
$diff_Total = $Total - round(numberFormatPrecision($dr_Total, 2, "."));
$diff_Dirty = $Dirty - round(numberFormatPrecision($dr_Dirty, 2, "."));

echo "<tr><td colspan='2'>Difference</td>
<td colspan='6'></td>
<td>{$diff_JMB}</td>
<td>{$diff_XLR}</td>
<td>{$diff_LRG}</td>
<td>{$diff_MED}</td>
<td>{$diff_SML}</td>
<td>{$diff_PUL}</td>
<td>{$diff_PWE}</td>
<td>{$diff_SPW}</td>
<td>{$diff_B1}</td>
<td>{$diff_B2}</td>
<td>{$diff_B3}</td>
<td>{$diff_Total}</td>
<td>{$diff_Dirty}</td>
<td></td></tr>";
echo "</tfoot></table>";
?>