<?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 In Weeks", 
	"S.H.D.%", 
	"S.H.H", 
	"Hen Day", 
	"Pop", 
	"A.H.D.%", 
	"", 
	"A.H.H.", 
	"GRM/Bird", 
	"FCR/EW", 
	"FCR/EM", 
	"Depletion", 
	"Dep %", 
	"Liv %"
];

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

echo "<tr>
<th colspan='3'>Calendar Year Target:</th>
<th colspan='4'>Farm Average Percentage - 90%</th>
<th colspan='4'>Farm FCR - 1.40</th>
<th colspan='4'>Livability Rate @80wks - 90%</th>
</tr>";

echo "<tr>
<th colspan='15'><h2>Daily Production Report</h2></th>
</tr>";
echo "<tr>
<th colspan='5'></th>
<th colspan='4'>Production Date</th>
<th colspan='2'></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 
						ip.id, 
						ip.BatchName, 
						ip.AgeInWeeks, 
						COALESCE(SUM(ip.Inv_Adj), 0) as Inventory_Adjustment,
						COALESCE(SUM(ip.H_TOTAL), 0) as HenDayTotal, 
						COALESCE(SUM(ip.Hen_Day), 0) as Pop,
						COALESCE(SUM(ip.Depletion), 0) as Depletion,
						bd.Breed,
						bd.Prod_Previous,
						(SELECT COALESCE(SUM(fd.Consumption_GRAMS), 0) 
							FROM feed_delivery fd 
							WHERE fd.BatchName = ip.BatchName 
							  AND fd.ProductionDate = ip.ProductionDate AND fd.is_deleted = FALSE AND fd.is_current = TRUE
							LIMIT 1) as RAW_GRM_Bird,
						(SELECT COALESCE(SUM(prev_ip.H_TOTAL), 0) 
							FROM input_production prev_ip 
							WHERE prev_ip.BatchName = ip.BatchName 
							  AND prev_ip.ProductionDate = DATE_SUB(ip.ProductionDate, INTERVAL 1 DAY) AND prev_ip.is_deleted = FALSE AND prev_ip.is_current = TRUE
							LIMIT 1) as PHD_Total,
						(SELECT COALESCE(SUM(prev_ip.Hen_Day), 0) 
							FROM input_production prev_ip 
							WHERE prev_ip.BatchName = ip.BatchName 
							  AND prev_ip.ProductionDate = DATE_SUB(ip.ProductionDate, INTERVAL 1 DAY) AND prev_ip.is_deleted = FALSE AND prev_ip.is_current = TRUE
							LIMIT 1) as PPOP_Total,
						(SELECT Stnd_H_D_per
							FROM egg_standards es 
							WHERE es.BreedType = bd.Breed 
							  AND es.AgeInWeeks = ip.AgeInWeeks AND es.is_deleted = FALSE AND es.is_current = TRUE
							LIMIT 1) as Stnd_H_D_per,
						(SELECT Stnd_H_H_per 
							FROM egg_standards es 
							WHERE es.BreedType = bd.Breed 
							  AND es.AgeInWeeks = ip.AgeInWeeks AND es.is_deleted = FALSE AND es.is_current = TRUE
							LIMIT 1) as Stnd_H_H_per,
						(SELECT HenHoused 
							FROM batch_details hh 
							WHERE hh.BatchName = ip.BatchName AND hh.is_deleted = FALSE AND hh.is_current = TRUE
							LIMIT 1) as HenHoused,
						(SELECT A_W 
							FROM egg_weight ew 
							WHERE ew.BatchName = ip.BatchName AND ew.is_deleted = FALSE AND ew.is_current = TRUE
							LIMIT 1) as A_W,
						(SELECT COALESCE(SUM(liv.HenHoused), 0) 
							FROM batch_details liv 
							WHERE liv.BatchName = ip.BatchName AND liv.is_deleted = FALSE AND liv.is_current = TRUE
							LIMIT 1) as TotalHenHoused,
						GROUP_CONCAT(DISTINCT ip.BldgNo ORDER BY ip.BldgNo ASC) as BldgNos
					FROM input_production ip
						LEFT JOIN batch_details bd ON ip.BatchName = bd.BatchName AND bd.is_deleted = FALSE AND bd.is_current = TRUE
					WHERE ip.ProductionDate = ? AND ip.is_deleted = FALSE AND ip.is_current = TRUE
					GROUP BY ip.BatchName
					ORDER BY ip.AgeInWeeks DESC, BldgNos";

$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 class='rowParent'>";
    echo "<td><span class='rp'>" . $row["BatchName"] . "</span></td>";
    echo "<td>" . $row["AgeInWeeks"] . "</td>";
    
	$SHD_PER = !empty($row["Stnd_H_D_per"]) ? $row["Stnd_H_D_per"] : 0;
    echo "<td>" . number_format($SHD_PER, 2) . "%</td>";
    
	$SHH = !empty($row["Stnd_H_H_per"]) ? $row["Stnd_H_H_per"] : 0;
    echo "<td>" . number_format($SHH, 2) . "</td>";
    
    // Hen Day Total and Population
    echo "<td>" . number_format($row["HenDayTotal"]) . "</td>";
	
	//echo "Inventory Adjustment" . $row["Inventory_Adjustment"];
    echo "<td>" . number_format($row["Pop"] + $row["Inventory_Adjustment"]) . "</td>";
    
    // AHD Calculation
	$AHD = 0;
    if (!empty($row["HenDayTotal"]) && !empty($row["Pop"])) {
        $AHD = ($row["HenDayTotal"] / $row["Pop"]) * 100;
    } 
    echo "<td>" . number_format($AHD, 2) . "%</td>";
    
    // Previous Hen Day Total and Population
    $PHD_Total = !empty($row["PHD_Total"]) ? $row["PHD_Total"] : 0;
    $PPOP_Total = !empty($row["PPOP_Total"]) ? $row["PPOP_Total"] : 0;
    
    // Previous AHD Calculation
	$prevAHD = 0;
    if (!empty($row["PHD_Total"]) && !empty($row["PPOP_Total"])) {
        $prevAHD = ($row["PHD_Total"] / $row["PPOP_Total"]) * 100;
    }
    
    // PHD_PER Calculation
	$PHD_PER = 0;
    if (!empty($row["HenDayTotal"]) && !empty($row["Pop"]) && !empty($row["PHD_Total"]) && !empty($row["PPOP_Total"])) {
        $PHD_PER = (($row["HenDayTotal"] / $row["Pop"]) * 100) - (($row["PHD_Total"] / $row["PPOP_Total"]) * 100);
    }
	
    // UpDown Indicator
    if ($AHD == $prevAHD) {
		$UpDown = "<td><img src='img/png_fokzh.png' class='trend-arrow' /></td>";
	} else if ($PHD_PER > 0) {
		$UpDown = "<td><img src='img/png_qf2osj.png' class='trend-arrow' /></td>";
	} else {
		$UpDown = "<td><img src='img/png_yp931.png' class='trend-arrow' /></td>";
    }
    echo $UpDown;
	
	$startDate = "2024-08-01";
	$endDate = $ProductionDate; // Example end date
	
	$rrquery = "SELECT 
								COALESCE(SUM(H_TOTAL), 0) as Prod_Current 
							FROM input_production 
							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);

	$Prod_YTD = $rrow["Prod_Current"] + $row["Prod_Previous"];
	
	// A.H.H. Block
    $AHH_TOTAL = 0;
    if (!empty($row["HenHoused"]) && !empty($Prod_YTD)) {
        $AHH_TOTAL = $Prod_YTD / $row["HenHoused"];
    }
    echo "<td>" . ($AHH_TOTAL == 0 ? "" : number_format($AHH_TOTAL, 2)) . "</td>";
	
    // GRM_BIRD Calculation
	$GRM_BIRD = 0;
    if (!empty($row["RAW_GRM_Bird"]) && !empty($row["Pop"])) {
        $GRM_BIRD = $row["RAW_GRM_Bird"] / $row["Pop"];
    } 
    echo "<td>" . ($GRM_BIRD == 0 ? "NA" : number_format($GRM_BIRD, 0)) . "</td>";
    
    // FCR_EW Calculation
	$FCR_EW = 0;
    if (!empty($row["Pop"]) && !empty($row["HenDayTotal"])) {
        $FCR_EW = ((($row["Pop"] * $GRM_BIRD) / 1000) * 12) / $row["HenDayTotal"];
    }
    echo "<td>" . ($FCR_EW == 0 ? "NA" : number_format($FCR_EW, 1)) . "</td>";
    
    // FCR/EM Block
	$FCR_EM = 0;
    if (!empty($row["A_W"]) && !empty($GRM_BIRD) && !empty($row["Pop"]) && !empty($row["HenDayTotal"])) {
        $FCR_EM = (($GRM_BIRD * $row["Pop"]) / 1000) / (($row["A_W"] * $row["HenDayTotal"]) / 1000);
    }
    echo "<td>" . ($FCR_EM == 0 ? "NA" : number_format($FCR_EM, 1)) . "</td>";
    
    // Depletion
    echo "<td>" . ($row["Depletion"] == 0 ? 0 : $row["Depletion"]) . "</td>";
    
    // DEP_PER Calculation
	$DEP_PER = 0;
    if (!empty($row["Depletion"]) && !empty($row["Pop"])) {
        $DEP_PER = ($row["Depletion"] / $row["Pop"]) * 100;
    }
    echo "<td>" . number_format($DEP_PER, 2) . "%</td>";
    
    // Liv % Block
	$LIV_PER = 0;
    if (!empty($row["TotalHenHoused"])) {
        $LIV_PER = ($row["Pop"] / $row["TotalHenHoused"]) * 100;
    }
    echo "<td>" . number_format($LIV_PER, 2) . "%</td>";
    echo "</tr>";
	
	// Split BldgNos into an array
    $bldgNos = explode(",", $row["BldgNos"]);

    foreach ($bldgNos as $bldgNo) {
        // Fetch and display original data for each BldgNo
        $originalDataQuery = "SELECT 
														ip.id, 
														ip.BldgNo, 
														ip.Inv_Adj,
														COALESCE(ip.H_TOTAL, 0) as H_TOTAL, 
														COALESCE(ip.Hen_Day, 0) as Pop, 
														COALESCE(ip.Depletion, 0) as Depletion,
														(SELECT COALESCE(SUM(fd.Consumption_GRAMS), 0) 
															FROM feed_delivery fd 
															WHERE fd.BldgNo = ip.BldgNo AND fd.ProductionDate = ip.ProductionDate AND fd.is_deleted = FALSE AND fd.is_current = TRUE LIMIT 1) as RAW_GRM_Bird,
														(SELECT COALESCE(SUM(prev_ip.H_TOTAL), 0) 
															FROM input_production prev_ip 
															WHERE prev_ip.BldgNo = ip.BldgNo AND prev_ip.ProductionDate = DATE_SUB(ip.ProductionDate, INTERVAL 1 DAY)  AND prev_ip.is_deleted = FALSE AND prev_ip.is_current = TRUE LIMIT 1) as PHD_Total,
														(SELECT COALESCE(SUM(prev_ip.Hen_Day), 0) 
															FROM input_production prev_ip 
															WHERE prev_ip.BldgNo = ip.BldgNo AND prev_ip.ProductionDate = DATE_SUB(ip.ProductionDate, INTERVAL 1 DAY) AND prev_ip.is_deleted = FALSE AND prev_ip.is_current = TRUE LIMIT 1) as PPOP_Total,
														(SELECT COALESCE(SUM(prev_ip.H_TOTAL), 0) 
															FROM input_production prev_ip 
															WHERE prev_ip.BldgNo = ip.BldgNo AND prev_ip.ProductionDate = DATE_SUB(ip.ProductionDate, INTERVAL 1 DAY) AND prev_ip.is_deleted = FALSE AND prev_ip.is_current = TRUE LIMIT 1) as HenDayTotal,
														(SELECT COALESCE(SUM(prev_ip.Hen_Day), 0) 
															FROM input_production prev_ip 
															WHERE prev_ip.BldgNo = ip.BldgNo AND prev_ip.ProductionDate = DATE_SUB(ip.ProductionDate, INTERVAL 1 DAY) AND prev_ip.is_deleted = FALSE AND prev_ip.is_current = TRUE LIMIT 1) as PrevPop
													FROM input_production ip
													WHERE ip.ProductionDate = ? AND ip.BatchName = ? AND ip.BldgNo = ? AND ip.is_deleted = FALSE AND ip.is_current = TRUE";

        $originalDataResult = executeQuery($conn, $originalDataQuery, "sss", [$ProductionDate, $row["BatchName"], $bldgNo]);
		
		if ($originalDataResult === false) { 
			throw new Exception("Error executing query: " . $conn->error);
		} 

        while ($originalDataRow = mysqli_fetch_assoc($originalDataResult)) {
            // Display individual rows with BldgNo as BatchName
            echo "<tr class='rowChild'>";
            echo "<td><span class='rc'>" . $originalDataRow["BldgNo"] . "</span></td>"; // Use BldgNo as BatchName
            echo "<td></td>"; // SET BLANK for Age
            echo "<td></td>"; // SET BLANK for S.H.D.%
            echo "<td></td>"; // SET BLANK for S.H.H

            echo "<td>".number_format($originalDataRow["H_TOTAL"])."</td>";
            echo "<td>".number_format($originalDataRow["Pop"] + $originalDataRow["Inv_Adj"])."</td>";
			
			// AHD Calculation
			$AHD = 0;
			if (!empty($originalDataRow["H_TOTAL"]) && !empty($originalDataRow["Pop"])) {
				$AHD = ($originalDataRow["H_TOTAL"] / $originalDataRow["Pop"]) * 100;
			} 
			echo "<td>" . number_format($AHD, 2) . "%</td>";
			
			// Previous Hen Day Total and Population
			$PHD_Total = !empty($originalDataRow["PHD_Total"]) ? $originalDataRow["PHD_Total"] : 0;
			$PPOP_Total = !empty($originalDataRow["PPOP_Total"]) ? $originalDataRow["PPOP_Total"] : 0;
			
			// Previous AHD Calculation
			$prevAHD = 0;
			if (!empty($originalDataRow["PHD_Total"]) && !empty($originalDataRow["PPOP_Total"])) {
				$prevAHD = ($originalDataRow["PHD_Total"] / $originalDataRow["PPOP_Total"]) * 100;
			}
			
            // PHD_PER Calculation
            $PHD_PER = !empty($originalDataRow["H_TOTAL"]) && !empty($originalDataRow["Pop"]) && !empty($originalDataRow["PHD_Total"]) && !empty($originalDataRow["PPOP_Total"]) 
                       ? (($originalDataRow["H_TOTAL"] / $originalDataRow["Pop"]) * 100) - (($originalDataRow["PHD_Total"] / $originalDataRow["PPOP_Total"]) * 100)
                       : 0;

            // UpDown Indicator
            if ($AHD == $prevAHD) {
				$UpDown = "<td><img src='img/png_fokzh.png' class='trend-arrow' /></td>";
			} else if ($PHD_PER > 0) {
				$UpDown = "<td><img src='img/png_qf2osj.png' class='trend-arrow' /></td>";
			} else {
				$UpDown = "<td><img src='img/png_yp931.png' class='trend-arrow' /></td>";
            }
			
            echo $UpDown;
            echo "<td></td>"; // SET BLANK for A.H.H.
            
			// GRM_BIRD Calculation
			$GRM_BIRD = !empty($originalDataRow["RAW_GRM_Bird"]) && !empty($originalDataRow["Pop"]) 
						? $originalDataRow["RAW_GRM_Bird"] / $originalDataRow["Pop"]
						: 0;
			echo "<td>" . ($GRM_BIRD == 0 ? "NA" : number_format($GRM_BIRD, 0)) . "</td>";

            echo "<td></td>"; // SET BLANK for FCR/EW
            echo "<td></td>"; // SET BLANK for FCR/EM
            echo "<td>" . ($originalDataRow["Depletion"] == 0 ? 0 : $originalDataRow["Depletion"]) . "</td>";

            // DEP_PER Calculation
            $DEP_PER = !empty($originalDataRow["Depletion"]) && !empty($originalDataRow["Pop"]) 
                       ? ($originalDataRow["Depletion"] / $originalDataRow["Pop"]) * 100
                       : 0;
            echo "<td>" . number_format($DEP_PER, 2) . "%</td>";
            echo "<td></td>"; // SET BLANK for Liv %
            echo "</tr>";
        }
    }
}
$sb_query = "SELECT 
								ip.id, 
								ip.BatchName, 
								ip.AgeInWeeks, 
								GROUP_CONCAT(DISTINCT ip.BldgNo ORDER BY ip.BldgNo ASC) as BldgNos, 
								COALESCE(SUM(ip.H_TOTAL), 0) as HenDayTotal,
								COALESCE(SUM(ip.Hen_Day), 0) as Pop,
								(SELECT COALESCE(SUM(fd.Consumption_GRAMS), 0) 
									FROM feed_delivery fd 
									WHERE fd.BatchName = ip.BatchName AND fd.ProductionDate = ip.ProductionDate AND  fd.is_deleted = FALSE AND fd.is_current = TRUE LIMIT 1) as RAW_GRM_Bird
							FROM input_production ip
							WHERE ip.ProductionDate = ? AND ip.is_deleted = FALSE AND ip.is_current = TRUE
							GROUP BY ip.BatchName
							ORDER BY ip.AgeInWeeks DESC, BldgNos";
$sb_result = executeQuery($conn, $sb_query, "s", [$ProductionDate]);

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

$GRM_Bird = $Pop = $Hen_Day = $subtotal10 = $subtotal11 = 0;

if ($sb_result) {
	while ($sb_row = mysqli_fetch_assoc($sb_result)) 
	{
		$GRM_Bird += $sb_row["RAW_GRM_Bird"];
		$Pop += $sb_row["Pop"];
		$Hen_Day += $sb_row["HenDayTotal"];
	}
}

if ($GRM_Bird != 0 && $Pop != 0) {
	$subtotal10 = $GRM_Bird / $Pop;
}

if ($GRM_Bird != 0 && $Hen_Day != 0) {
	$subtotal11 = (($GRM_Bird / 1000) * 12) / $Hen_Day;
}

echo "</tbody><tfoot class='print-footer'><tr>";
echo "<td colspan='2'>Subtotal</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>" . number_format($subtotal10, 0) . "</td>
<td>" . number_format($subtotal11, 1) . "</td>
<td></td>
<td></td>
<td></td>
<td></td>";
echo "</tr></tfoot></table>";
?>