<?php
$page_title = 'Create Purchase Order';
require_once('includes/load.php');
page_require_level(3);

$po_message = "";

// Query SQL untuk mendapatkan item yang perlu dibeli
$itemsToPurchaseQuery = "SELECT
                            mb.part_number,
                            p.name AS product_name,
                            p.quantity AS stock,
                            SUM(mb.calculate) AS calculate,
                            (SUM(mb.calculate) - COALESCE(p.quantity, 0)) AS shortage -- Menghitung kekurangan stok
                        FROM mrp_baru mb
                        LEFT JOIN products p ON mb.part_number = p.part_number
                        GROUP BY mb.part_number, product_name, stock
                        HAVING shortage > 0 -- Hanya menampilkan yang memiliki kekurangan stok
                        ORDER BY mb.part_number";

$itemsToPurchaseResult = $db->query($itemsToPurchaseQuery);

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Pengguna mengirimkan formulir, simpan data PO yang diinput

    // Iterasi melalui data PO yang diinput
    foreach ($_POST['quantity_po'] as $part_number => $quantity_po) {
        // Dapatkan data produk berdasarkan part_number
        $productQuery = "SELECT * FROM products WHERE part_number = '{$part_number}' LIMIT 1";
        $productResult = $db->query($productQuery);
        $product = $db->fetch_assoc($productResult);

        // Pastikan produk ditemukan
        if ($product) {
            $quantity_po = (float) $quantity_po; // Konversi ke tipe data float

            // Simpan data pembuatan pesanan pembelian ke dalam tabel purchase_requests
            $request_number = generate_request_number();
            $product_id = $product['id'];
            $request_date = date('Y-m-d H:i:s');
            $status = 'Pending';
            $notes = '';
            $created_at = date('Y-m-d H:i:s');
            $updated_at = date('Y-m-d H:i:s');
            $approved_by = null;

            $insertQuery = "INSERT INTO purchase_requests (request_number, product_id, quantity, request_date, status, notes, created_at, updated_at, approved_by)
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

            // Buat pernyataan persiapan
            $stmt = $db->prepare($insertQuery);

            if ($stmt) {
                // Ikat parameter ke pernyataan
                $stmt->bind_param("siisssssi", $request_number, $product_id, $quantity_po, $request_date, $status, $notes, $created_at, $updated_at, $approved_by);

                // Eksekusi pernyataan
                $stmt->execute();

                // Tutup pernyataan
                $stmt->close();
            } else {
                // Jika gagal membuat pernyataan persiapan
                echo "Error in prepared statement: " . $db->error;
            }
        }
    }

    // Setelah penyimpanan PO berhasil, Anda dapat menampilkan pesan sukses atau tautan kembali ke MRP
    $po_message = "Purchase Order telah berhasil dibuat.";
}

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">Create Purchase Order</h3>
            </div>
            <div class="panel-body">
                <p>Calculation Date: <?php echo date('Y-m-d'); ?></p>
            </div>
        </div>
    </div>
</div>

<?php if ($db->num_rows($itemsToPurchaseResult) > 0) : ?>
    <div class="row">
        <div class="col-md-12">
            <div class="panel panel-default">
                <div class="panel-heading">
                    <h3 class="panel-title">Items to Purchase (Shortage)</h3>
                </div>
                <div class="panel-body">
                    <form method="post" action="create_po.php">
                        <table class="table table-bordered table-hover">
                            <thead>
                                <tr>
                                    <th>Part Number</th>
                                    <th>Product Name</th>
                                    <th>Stock</th>
                                    <th>Calculate</th>
                                    <th>Shortage</th>
                                    <th>Quantity PO</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php while ($row = $db->fetch_assoc($itemsToPurchaseResult)) : ?>
                                    <tr>
                                        <td><?php echo htmlspecialchars($row['part_number']); ?></td>
                                        <td><?php echo htmlspecialchars($row['product_name']); ?></td>
                                        <td><?php echo number_format($row['stock'], 2, '.', ''); ?></td>
                                        <td><?php echo number_format($row['calculate'], 2, '.', ''); ?></td>
                                        <td><?php echo number_format($row['shortage'], 2, '.', ''); ?></td>
                                        <td>
                                            <input type="number" name="quantity_po[<?php echo $row['part_number']; ?>]" min="0" step="0.01" required>
                                        </td>
                                    </tr>
                                <?php endwhile; ?>
                            </tbody>
                        </table>
                        <button type="submit" name="create_po" class="btn btn-primary">Create PO</button>
                    </form>
                </div>
            </div>
        </div>
    </div>
<?php else : ?>
    <div class="alert alert-warning">
        Tidak ada barang yang perlu dibeli.
    </div>
<?php endif; ?>

<?php if (!empty($po_message)) : ?>
    <div class="alert alert-success">
        <?php echo $po_message; ?>
    </div>
<?php endif; ?>

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