<?php
$page_title = 'Pesanan';
require_once('includes/load.php');
require 'vendor/autoload.php'; // Autoload PHPSpreadsheet

use PhpOffice\PhpSpreadsheet\IOFactory;

page_require_level(3);

// Initialize error message
$errors = [];
$search_query = "";

// Check if search form is submitted
if (isset($_POST['search'])) {
    $search_query = $_POST['search_query'];
    $pesanans = search_pesanan($search_query); // Implement search_pesanan function to handle search logic
} else {
    $pesanans = []; // Initialize as empty array if no search is performed
}

// Function to search pesanan based on search query
function search_pesanan($query) {
    global $db;
    $query = $db->escape($query);
    $sql = "SELECT * FROM awb 
            WHERE resi LIKE '%$query%' 
            OR invoice LIKE '%$query%'";
    return find_by_sql($sql);
}

function import_excel($file) {
    global $db, $errors;
    
    try {
        // Load the Excel file
        $spreadsheet = IOFactory::load($file);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray(null, true, true, true);

        // Skip the header row
        array_shift($rows);

        foreach ($rows as $row) {
            // Escape and assign Excel values to variables
            $tanggal_pesenan_dibuat = !empty($row['A']) ? date('Y-m-d H:i:s', strtotime($row['A'])) : null;
            $invoice = $db->escape($row['B']);
            $nama_toko = $db->escape($row['C']);
            $part_number = $db->escape($row['D']);
            $quantity = isset($row['E']) ? (int)$db->escape($row['E']) : 0;
            $unit_price = isset($row['F']) ? (int)$db->escape($row['F']) : 0;
            $total_price = isset($row['G']) ? (int)$db->escape($row['G']) : 0;
            $kurir = $db->escape($row['H']);
            $resi = $db->escape($row['I']);
            $batas_pengiriman = !empty($row['J']) ? date('Y-m-d H:i:s', strtotime($row['J'])) : null;

            // Automatically set date_scan to current date if empty
            $date_scan = date('Y-m-d');

            $uniq_code = $db->escape($resi . '-' . $part_number . '-' . $quantity);

            // Check if the uniq_code already exists in the database
            $check_sql = "SELECT id FROM awb WHERE uniq_code = '$uniq_code' LIMIT 1";
            $result = $db->query($check_sql);

            if ($db->num_rows($result) == 0) {
                // If uniq_code does not exist, insert the new record
                $sql = "INSERT INTO awb (tanggal_pesenan_dibuat, invoice, nama_toko, part_number, product_name, quantity, unit_price, total_price, kurir, resi, date_scan, batas_pengiriman, uniq_code) 
                        VALUES (
                            " . ($tanggal_pesenan_dibuat ? "'$tanggal_pesenan_dibuat'" : "NULL") . ", 
                            '$invoice', 
                            '$nama_toko', 
                            '$part_number', 
                            NULL, 
                            '$quantity', 
                            '$unit_price', 
                            '$total_price', 
                            '$kurir', 
                            '$resi', 
                            '$date_scan', 
                            " . ($batas_pengiriman ? "'$batas_pengiriman'" : "NULL") . ", 
                            '$uniq_code'
                        )";
                if (!$db->query($sql)) {
                    $errors[] = "Failed to insert record: " . $db->get_last_error();
                }
            } else {
                // If uniq_code exists, skip this record
                continue;
            }
        }
    } catch (Exception $e) {
        $errors[] = "Error importing Excel file: " . $e->getMessage();
    }
}

// Handle Excel upload
if (isset($_POST['import'])) {
    if (isset($_FILES['excel_file']) && $_FILES['excel_file']['error'] == 0) {
        $excel_file = $_FILES['excel_file']['tmp_name'];
        import_excel($excel_file);
        if (empty($errors)) {
            $session->msg('s', "Excel data imported successfully.");
            redirect('pesanan.php', false);
        }
    } else {
        $errors[] = "Failed to upload Excel file.";
    }
}

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

<div class="row">
    <div class="col-md-12">
        <?php echo display_msg($msg); ?>
        <?php if (!empty($errors)): ?>
            <div class="alert alert-danger">
                <ul>
                    <?php foreach ($errors as $error): ?>
                        <li><?php echo htmlspecialchars($error); ?></li>
                    <?php endforeach; ?>
                </ul>
            </div>
        <?php endif; ?>
    </div>
</div>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading clearfix">
                <h3 class="panel-title">Pesanan Management</h3>
            </div>
            <div class="panel-body">
                <div class="row">
                    <div class="col-md-6">
                        <div class="form-group">
                            <h4>Search Pesanan</h4>
                            <form method="post" action="pesanan.php" class="form-inline">
                                <div class="input-group">
                                    <input type="text" class="form-control" name="search_query" placeholder="Search by Invoice or Resi" value="<?php echo htmlspecialchars($search_query); ?>">
                                    <span class="input-group-btn">
                                        <button type="submit" name="search" class="btn btn-primary">
                                            <i class="fa fa-search"></i> Search
                                        </button>
                                    </span>
                                </div>
                            </form>
                        </div>
                    </div>
                    <div class="col-md-6">
                        <div class="form-group">
                            <h4>Import Excel</h4>
                            <form method="post" action="pesanan.php" enctype="multipart/form-data" class="form-inline">
                                <div class="input-group">
                                    <input type="file" name="excel_file" class="form-control" accept=".xlsx, .xls">
                                    <span class="input-group-btn">
                                        <button type="submit" name="import" class="btn btn-primary">Import Excel</button>
                                    </span>
                                </div>
                            </form>
                        </div>
                    </div>
                </div>
                
                <?php if (!empty($pesanans)): ?>
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th class="text-center">#</th>
                            <th>Tanggal Pesanan Dibuat</th>
                            <th>Invoice</th>
                            <th>Nama Toko</th>
                            <th>Part Number</th>
                            <th>Qty</th>
                            <th>Unit Price</th>
                            <th>Total Price</th>
                            <th>Kurir</th>
                            <th>Resi</th>
                            <th>Date Scan</th>
                            <th>Batas Pengiriman</th>
                            <th>Scan In</th>
                            <th>Scan Status</th>
                            <th>Packer No</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($pesanans as $pesanan): ?>
                            <tr>
                                <td class="text-center"><?php echo count_id(); ?></td>
                                <td><?php echo htmlspecialchars($pesanan['tanggal_pesenan_dibuat']); ?></td>
                                <td><?php echo htmlspecialchars($pesanan['invoice']); ?></td>
                                <td><?php echo htmlspecialchars($pesanan['nama_toko']); ?></td>
                                <td><?php echo htmlspecialchars($pesanan['part_number']); ?></td>
                                <td><?php echo htmlspecialchars($pesanan['quantity']); ?></td>
                                <td><?php echo htmlspecialchars($pesanan['unit_price']); ?></td>
                                <td><?php echo htmlspecialchars($pesanan['total_price']); ?></td>
                                <td><?php echo htmlspecialchars($pesanan['kurir']); ?></td>
                                <td><?php echo htmlspecialchars($pesanan['resi']); ?></td>
                                <td><?php echo htmlspecialchars($pesanan['date_scan']); ?></td>
                                <td><?php echo htmlspecialchars($pesanan['batas_pengiriman']); ?></td>
                                <td><?php echo htmlspecialchars($pesanan['scan_in']); ?></td>
                                <td><?php echo htmlspecialchars($pesanan['scan_status']); ?></td>
                                <td><?php echo htmlspecialchars($pesanan['packer_no']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
                <?php else: ?>
                    <div class="alert alert-info">No records found.</div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

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