<?php
require_once('includes/load.php');
page_require_level(3);

// Fungsi untuk mengambil data dari tabel purchase_request_items dan products
function get_purchase_request_items($month = null, $year = null, $status = null, $search = null) {
    global $db;

    $sql = "SELECT pri.id, pri.request_number, pri.part_number, p.name as product_name, pri.quantity_needed, 
            pri.due_date, p.uom, pri.incoming, pri.status, pri.remark 
            FROM purchase_request_items pri
            LEFT JOIN products p ON pri.part_number = p.part_number";

    $conditions = [];
    $params = [];
    $types = '';

    if ($month && $year) {
        $conditions[] = "MONTH(pri.due_date) = ?";
        $conditions[] = "YEAR(pri.due_date) = ?";
        $types .= 'ii';
        $params[] = $month;
        $params[] = $year;
    }

    if ($status && $status !== 'all') {
        $conditions[] = "pri.status = ?";
        $types .= 's';
        $params[] = $status;
    }

    if ($search) {
        $conditions[] = "(pri.request_number LIKE ? OR pri.part_number LIKE ? OR p.name LIKE ?)";
        $types .= 'sss';
        $search_param = "%{$search}%";
        $params[] = $search_param;
        $params[] = $search_param;
        $params[] = $search_param;
    }

    if (!empty($conditions)) {
        $sql .= " WHERE " . implode(' AND ', $conditions);
    }

    $stmt = $db->prepare($sql);

    if (!empty($params)) {
        $stmt->bind_param($types, ...$params);
    }

    if (!$stmt->execute()) {
        die('Error executing query: ' . $stmt->error);
    }

    $result = $stmt->get_result();
    $items = [];

    while ($row = $result->fetch_assoc()) {
        $items[] = $row;
    }

    $stmt->close();
    return $items;
}

// Ambil data dari tabel purchase_request_items dengan filter
$default_month = date('n'); // Bulan saat ini
$default_year = date('Y');  // Tahun saat ini

$month = isset($_POST['month']) ? (int)$_POST['month'] : $default_month;
$year = isset($_POST['year']) ? (int)$_POST['year'] : $default_year;
$status = isset($_POST['status']) ? $_POST['status'] : 'all';
$search = isset($_POST['search']) ? $_POST['search'] : '';

$purchase_request_items = get_purchase_request_items($month, $year, $status, $search);

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

<div class="row">
    <div class="col-md-12">
        <?php echo display_msg($msg); ?>
    </div>
</div>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading">
                <h3 class="panel-title">Purchase Request Items</h3>
            </div>
            <div class="panel-body">
                <form action="list_purchase_item.php" method="post" class="form-inline mb-3">
                    <div class="form-group">
                        <label for="month" class="sr-only">Month</label>
                        <select name="month" id="month" class="form-control">
                            <?php
                            for ($m = 1; $m <= 12; $m++) {
                                $selected = ($month == $m) ? 'selected' : '';
                                echo "<option value=\"$m\" $selected>" . date('F', mktime(0, 0, 0, $m, 1)) . "</option>";
                            }
                            ?>
                        </select>
                    </div>
                    <div class="form-group mx-sm-3">
                        <label for="year" class="sr-only">Year</label>
                        <input type="number" name="year" id="year" class="form-control" placeholder="Year" value="<?php echo $year; ?>">
                    </div>
                    <div class="form-group">
                        <label for="status" class="sr-only">Status</label>
                        <select name="status" id="status" class="form-control">
                            <option value="all" <?php echo ($status == 'all') ? 'selected' : ''; ?>>All</option>
                            <option value="pending" <?php echo ($status == 'pending') ? 'selected' : ''; ?>>Pending</option>
                            <option value="received" <?php echo ($status == 'received') ? 'selected' : ''; ?>>Received</option>
                        </select>
                    </div>
                    <div class="form-group mx-sm-3">
                        <label for="search" class="sr-only">Search</label>
                        <input type="text" name="search" id="search" class="form-control" placeholder="Search" value="<?php echo htmlspecialchars($search, ENT_QUOTES, 'UTF-8'); ?>">
                    </div>
                    <button type="submit" class="btn btn-primary">Filter</button>
                </form>

                <button id="export-purchase-request-items" class="btn btn-success mb-3">Export Purchase Request Items to Excel</button>
                <button id="summary-item-list" class="btn btn-info mb-3" onclick="window.location.href='summary_item_list.php'">Summary Item List</button>

                <table id="purchase-request-items-table" class="table table-bordered mt-3">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Request Number</th>
                            <th>Part Number</th>
                            <th>Product Name</th>
                            <th>Quantity Needed</th>
                            <th>Due Date</th>
                            <th>UoM</th>
                            <th>Incoming</th>
                            <th>Status</th>
                            <th>Remark</th> <!-- Kolom remark ditambahkan -->
                            <th>Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (!empty($purchase_request_items)) : ?>
                            <?php foreach ($purchase_request_items as $item) : ?>
                                <tr>
                                    <td><?php echo remove_junk($item['id']); ?></td>
                                    <td><?php echo remove_junk($item['request_number']); ?></td>
                                    <td><?php echo remove_junk($item['part_number']); ?></td>
                                    <td><?php echo remove_junk($item['product_name']); ?></td>
                                    <td><?php echo remove_junk($item['quantity_needed']); ?></td>
                                    <td><?php echo remove_junk($item['due_date']); ?></td>
                                    <td><?php echo remove_junk($item['uom']); ?></td>
                                    <td><?php echo remove_junk($item['incoming']); ?></td>
                                    <td><?php echo remove_junk($item['status']); ?></td>
                                    <td><?php echo remove_junk($item['remark']); ?></td> <!-- Menampilkan remark -->
                                    <td>
                                        <a href="edit_purchase_item.php?id=<?php echo $item['id']; ?>" class="btn btn-warning btn-sm">
                                            <i class="fas fa-edit"></i>
                                        </a>
                                        <a href="delete_purchase_item.php?id=<?php echo $item['id']; ?>" class="btn btn-danger btn-sm" onclick="return confirm('Are you sure you want to delete this item?');">
                                            <i class="fas fa-trash-alt"></i>
                                        </a>
                                        <a href="add_incoming.php?id=<?php echo $item['id']; ?>" class="btn btn-success btn-sm">
                                            <i class="fas fa-truck"></i>
                                        </a>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        <?php else : ?>
                            <tr>
                                <td colspan="11">No data available</td> <!-- Update jumlah kolom -->
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<!-- Script untuk ekspor data -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
<script>
document.getElementById('export-purchase-request-items').addEventListener('click', function () {
    let wb = XLSX.utils.table_to_book(document.getElementById('purchase-request-items-table'), { sheet: "Purchase Request Items" });
    XLSX.writeFile(wb, 'Purchase_Request_Items.xlsx');
});
</script>

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