<?php
$page_title = 'Material Requirements Planning (MRP)';
require_once('includes/load.php');
page_require_level(3);

$selected_product_name = ''; // Initialize the selected product name

// Retrieve part numbers, no_order, total quantity orders, and delivery_date from production_schedule table
$partNumbersResult = $db->query("SELECT part_number, no_order, SUM(quantity_order) AS total_quantity_order, MAX(delivery_date) AS delivery_date FROM production_schedule GROUP BY part_number, no_order");

$mrpData = array();

while ($row = $db->fetch_assoc($partNumbersResult)) {
    $partNumber = $row['part_number'];
    $noOrder = $row['no_order'];
    $totalQuantityOrder = $row['total_quantity_order'];
    $deliveryDate = $row['delivery_date'];

    if (!isset($mrpData[$noOrder])) {
        $mrpData[$noOrder] = array();
    }

    $mrpData[$noOrder][$partNumber] = array(
        'total_quantity_order' => $totalQuantityOrder,
        'delivery_date' => $deliveryDate
    );
}

include_once('layouts/header.php');
?>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading">
                <h3 class="panel-title">Material Requirements Planning (MRP)</h3>
            </div>
            <div class="panel-body">
                <p>Calculation Date: <?php echo date('Y-m-d'); ?></p>
            </div>
        </div>
    </div>
</div>

<?php if (!empty($mrpData)) : ?>
    <?php foreach ($mrpData as $noOrder => $partNumbers) : ?>
        <!-- Display MRP data for each no_order -->
        <div class="row">
            <div class="col-md-12">
                <div class="panel panel-default">
                    <div class="panel-heading">
                        <h3 class="panel-title">MRP for No. Order: <?php echo htmlspecialchars($noOrder); ?></h3>
                    </div>
                    <div class="panel-body">
                        <table class="table table-bordered table-hover">
                            <thead>
                                <tr>
                                    <th>Part Number</th>
                                    <th>Selected Product Name</th>
                                    <th>Child BOM</th>
                                    <th>Child Name</th>
                                    <th>Level</th>
                                    <th>Quantity</th>
                                    <th>Quantity Order</th>
                                    <th>Total Requirement</th>
                                    <th>UoM</th>
                                   
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($partNumbers as $partNumber => $data) : ?>
                                    <?php
                                    $bom_structure = get_bom_structure($partNumber);
                                    $selected_product_name = get_product_name_by_part_number($partNumber);
                                    $calculationDate = date('Y-m-d', strtotime($data['delivery_date'] . '-3 days')); // Date calculation, 3 days earlier
                                    ?>
                                    <?php foreach ($bom_structure as $bom_item) : ?>
                                        <tr>
                                            <td><?php echo htmlspecialchars($partNumber); ?></td>
                                            <td><?php echo htmlspecialchars($selected_product_name); ?></td>
                                            <td><?php echo htmlspecialchars($bom_item['child_bom']); ?></td>
                                            <td><?php echo htmlspecialchars($bom_item['child_name']); ?></td>
                                            <td><?php echo htmlspecialchars($bom_item['level']); ?></td>
                                            <td><?php echo htmlspecialchars($bom_item['qty']); ?></td>
                                            <td><?php echo htmlspecialchars(number_format($data['total_quantity_order'], 0, ',', '.')); ?></td>
                                            <td><?php echo htmlspecialchars(number_format($bom_item['qty'] * $data['total_quantity_order'], 0, ',', '.')); ?></td>
                                            <td><?php echo htmlspecialchars($bom_item['uom']); ?></td>
                                        </tr>
                                    <?php endforeach; ?>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    <?php endforeach; ?>
<?php endif; ?>

<?php include_once('layouts/footer.php'); ?>
