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

// Set default start and end dates to the beginning of the current month and today's date
$default_start_date = date('Y-m-01');
$default_end_date = date('Y-m-d');

// Initialize filter values with default dates and empty part number
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : $default_start_date;
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : $default_end_date;
$part_number = isset($_GET['part_number']) ? $_GET['part_number'] : '';
$remark = isset($_GET['remark']) ? $_GET['remark'] : '';  // Add remark filter
$categorie_id = isset($_GET['categorie_id']) ? $_GET['categorie_id'] : '';

// Construct SQL query with filters
$sql = "SELECT ig.part_number, p.name AS product_name, p.categorie_id, c.name AS categorie_name, ig.quantity, ig.arrival_date, ig.remark, ig.loc
        FROM incoming_goods ig
        LEFT JOIN products p ON p.id = ig.product_id
        LEFT JOIN categories c ON c.id = p.categorie_id
        WHERE ig.arrival_date BETWEEN '$start_date' AND '$end_date'";

if (!empty($part_number)) {
    $sql .= " AND ig.part_number LIKE '%$part_number%'";
}
if (!empty($remark)) {
    $sql .= " AND ig.remark LIKE '%$remark%'";  // Add remark filter to SQL query
}
if (!empty($categorie_id)) {
    $sql .= " AND p.categorie_id = '$categorie_id'";
}

$sql .= " ORDER BY ig.arrival_date DESC"; // Example ordering by arrival date
$incoming_goods = find_by_sql($sql);

// Calculate total quantity
$total_quantity = 0;
foreach ($incoming_goods as $item) {
    $total_quantity += $item['quantity'];
}

// Query to fetch categories for dropdown
$category_query = "SELECT id, name FROM categories";
$categories = find_by_sql($category_query);

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

<!-- HTML for Filter Form -->
<form method="get" action="incoming_goods.php">
    <label for="start_date">Start Date:</label>
    <input type="date" name="start_date" value="<?php echo $start_date; ?>">
    <label for="end_date">End Date:</label>
    <input type="date" name="end_date" value="<?php echo $end_date; ?>">
    <label for="part_number">Part Number:</label>
    <input type="text" name="part_number" value="<?php echo $part_number; ?>">
    <label for="remark">Remark:</label>
    <input type="text" name="remark" value="<?php echo $remark; ?>"> <!-- Remark filter input -->
    <label for="categorie_id">Category:</label>
    <select name="categorie_id">
        <option value="">All Categories</option>
        <?php foreach ($categories as $category): ?>
            <option value="<?php echo $category['id']; ?>" <?php echo ($categorie_id == $category['id']) ? 'selected' : ''; ?>>
                <?php echo $category['name']; ?>
            </option>
        <?php endforeach; ?>
    </select>
    <button type="submit">Filter</button>
    <button type="button" class="btn btn-success" id="exportExcelBtn">Export to Excel</button>
</form>

<!-- Form for CSV Upload -->
<form method="post" action="incoming_goods.php" enctype="multipart/form-data">
    <label for="csv_file">Upload CSV File:</label>
    <input type="file" name="csv_file" id="csv_file">
    <button type="submit" name="import_csv" class="btn btn-primary">Import CSV</button>
</form>

<!-- Display Total Quantity -->
<div class="alert alert-info">
    <strong>Total Quantity:</strong> <?php echo number_format($total_quantity); ?>
</div>

<!-- Table to Display Incoming Goods -->
<div class="table-responsive">
    <table class="table table-bordered">
        <thead>
            <tr>
                <th>Part Number</th>
                <th>Product Name</th>
                <th>Category Name</th>
                <th>Quantity</th>
                <th>Arrival Date</th>
                <th>Remark</th>
                <th>Location</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($incoming_goods as $item): ?>
                <tr>
                    <td><?php echo $item['part_number']; ?></td>
                    <td><?php echo $item['product_name']; ?></td>
                    <td><?php echo $item['categorie_name']; ?></td>
                    <td><?php echo $item['quantity']; ?></td>
                    <td><?php echo $item['arrival_date']; ?></td>
                    <td><?php echo $item['remark']; ?></td>
                    <td><?php echo $item['loc']; ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>

<?php include_once('layouts/footer.php'); ?>
<script>
    document.getElementById('exportExcelBtn').addEventListener('click', function() {
        var wb = XLSX.utils.book_new();
        var ws_data = [
            ['Part Number', 'Product Name', 'Category Name', 'Quantity', 'Arrival Date', 'Remark', 'Location']
        ]; // Header row

        <?php foreach ($incoming_goods as $item): ?>
            ws_data.push([
                '<?php echo $item["part_number"]; ?>',
                '<?php echo $item["product_name"]; ?>',
                '<?php echo $item["categorie_name"]; ?>',
                '<?php echo $item["quantity"]; ?>',
                '<?php echo $item["arrival_date"]; ?>',
                '<?php echo $item["remark"]; ?>',
                '<?php echo $item["loc"]; ?>'
            ]);
        <?php endforeach; ?>

        var ws = XLSX.utils.aoa_to_sheet(ws_data);
        XLSX.utils.book_append_sheet(wb, ws, "Incoming Goods Report");

        var wbout = XLSX.write(wb, {
            bookType: 'xlsx',
            type: 'binary'
        });

        function s2ab(s) {
            var buf = new ArrayBuffer(s.length);
            var view = new Uint8Array(buf);
            for (var i = 0; i < s.length; i++) view[i] = s.charCodeAt(i) & 0xFF;
            return buf;
        }

        saveAs(new Blob([s2ab(wbout)], {
            type: 'application/octet-stream'
        }), 'incoming_goods_report.xlsx');
    });
</script>