<?php
$page_title = 'List Retur AWB';
require_once('includes/load.php');
// page_require_level(12);  

// Include PhpSpreadsheet  
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Set default start and end date to current month  
$start_date_default = date('Y-m-01');
$end_date_default = date('Y-m-t');

// Function to fetch all retur AWB entries, optionally filter by date  
function find_all_retur_awb($start_date = null, $end_date = null)
{
    global $db;
    $sql = "SELECT *, IFNULL(loc, 'surabaya') AS loc FROM awb WHERE scan_retur IS NOT NULL";
    if ($start_date && $end_date) {
        $sql .= " AND scan_retur BETWEEN '{$start_date}' AND '{$end_date}'"; // Use scan_retur here  
    }
    $sql .= " ORDER BY scan_retur DESC"; // Order from newest to oldest  
    return find_by_sql($sql);
}

// Function to search AWB by resi and optionally filter by date  
function search_retur_awb($search_term, $start_date = null, $end_date = null)
{
    global $db;
    $sql = "SELECT *, IFNULL(loc, 'surabaya') AS loc FROM awb WHERE (resi LIKE '%{$search_term}%') AND scan_retur IS NOT NULL";
    if ($start_date && $end_date) {
        $sql .= " AND scan_retur BETWEEN '{$start_date}' AND '{$end_date}'"; // Use scan_retur here  
    }
    $sql .= " ORDER BY scan_retur DESC"; // Order from newest to oldest  
    return find_by_sql($sql);
}

// Fetch filtered or searched retur AWB entries  
if (isset($_POST['search'])) {
    $search_term = $_POST['search_term'];
    $start_date = $_POST['start_date'] ? $_POST['start_date'] : $start_date_default;
    $end_date = $_POST['end_date'] ? $_POST['end_date'] : $end_date_default;

    if (!empty($search_term)) {
        $retur_awb_entries = search_retur_awb($search_term, $start_date, $end_date);
    } else {
        $retur_awb_entries = find_all_retur_awb($start_date, $end_date);
    }
} else {
    $retur_awb_entries = find_all_retur_awb($start_date_default, $end_date_default);
}

// Export to Excel  
if (isset($_POST['export_excel'])) {
    $search_term = $_POST['search_term'];
    $start_date = $_POST['start_date'] ? $_POST['start_date'] : $start_date_default;
    $end_date = $_POST['end_date'] ? $_POST['end_date'] : $end_date_default;

    if (!empty($search_term)) {
        $retur_awb_entries = search_retur_awb($search_term, $start_date, $end_date);
    } else {
        $retur_awb_entries = find_all_retur_awb($start_date, $end_date);
    }

    // Create a new Spreadsheet object  
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    // Set column headers  
    $sheet->setCellValue('A1', 'No');
    $sheet->setCellValue('B1', 'AWB Resi');
    $sheet->setCellValue('C1', 'Kurir');
    $sheet->setCellValue('D1', 'Part Number');
    $sheet->setCellValue('E1', 'Date Scan');
    $sheet->setCellValue('F1', 'Quantity');
    $sheet->setCellValue('G1', 'Scan Status');
    $sheet->setCellValue('H1', 'Scan Retur');
    $sheet->setCellValue('I1', 'Packer No');
    $sheet->setCellValue('J1', 'Loc');
    $sheet->setCellValue('K1', 'Catatan');

    // Populate data  
    $row = 2;
    foreach ($retur_awb_entries as $key => $entry) {
        $sheet->setCellValue('A' . $row, $key + 1);
        $sheet->setCellValue('B' . $row, $entry['resi']);
        $sheet->setCellValue('C' . $row, $entry['kurir']);
        $sheet->setCellValue('D' . $row, $entry['part_number']);
        $sheet->setCellValue('E' . $row, $entry['date_scan']);
        $sheet->setCellValue('F' . $row, $entry['quantity']);
        $sheet->setCellValue('G' . $row, $entry['scan_status']);
        $sheet->setCellValue('H' . $row, $entry['scan_retur']);
        $sheet->setCellValue('I' . $row, $entry['packer_no']);
        $sheet->setCellValue('J' . $row, $entry['loc']);
        $sheet->setCellValue('K' . $row, $entry['catatan']);
        $row++;
    }

    // Set headers for Excel file  
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="list_retur.xlsx"');
    header('Cache-Control: max-age=0');

    // Write the Excel file to the browser  
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
}

// Function to get video path by AWB Resi  
function get_video_by_awb_resi($awb_resi)
{
    global $db;
    $sql = "SELECT file_path FROM retur_video WHERE awb_resi = '{$awb_resi}' LIMIT 1";
    return find_by_sql($sql);
}

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

<div class="row">
    <div class="col-md-12">
        <!-- Filter and Search Form -->
        <form class="form-inline pull-right" method="post" action="list_retur.php">
            <input type="text" name="search_term" class="form-control" placeholder="Search by AWB"
                value="<?php echo isset($search_term) ? $search_term : ''; ?>">
            <input type="date" name="start_date" class="form-control"
                value="<?php echo isset($start_date) ? $start_date : $start_date_default; ?>">
            <input type="date" name="end_date" class="form-control"
                value="<?php echo isset($end_date) ? $end_date : $end_date_default; ?>">
            <button type="submit" name="search" class="btn btn-primary">Search</button>
            <button type="submit" name="export_excel" class="btn btn-success">Export to Excel</button>
        </form>

        <!-- List Retur AWB Section -->
        <div class="panel panel-default">
            <div class="panel-heading">
                <h3 class="panel-title">List Retur AWB</h3>
            </div>
            <div class="panel-body">
                <div class="table-responsive">
                    <table class="table table-striped table-bordered">
                        <thead>
                            <tr>
                                <th>No</th>
                                <th>AWB Resi</th>
                                <th>Kurir</th>
                                <th>Part Number</th>
                                <th>Date Scan</th>
                                <th>Quantity</th>
                                <th>Scan Status</th>
                                <th>Scan Retur</th>
                                <th>Packer No</th>
                                <th>Loc</th>
                                <th>Catatan</th> <!-- Added Catatan column -->
                                <th>Actions</th> <!-- Column for actions -->
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($retur_awb_entries as $key => $entry):
                                // Get video path based on awb_resi  
                                $video = get_video_by_awb_resi($entry['resi']);
                                $video_path = isset($video[0]['file_path']) ? $video[0]['file_path'] : '';
                            ?>
                                <tr>
                                    <td><?php echo $key + 1; ?></td>
                                    <td><?php echo htmlspecialchars($entry['resi']); ?></td>
                                    <td><?php echo htmlspecialchars($entry['kurir']); ?></td>
                                    <td><?php echo htmlspecialchars($entry['part_number']); ?></td>
                                    <td><?php echo htmlspecialchars($entry['date_scan']); ?></td>
                                    <td><?php echo htmlspecialchars($entry['quantity']); ?></td>
                                    <td><?php echo htmlspecialchars($entry['scan_status']); ?></td>
                                    <td><?php echo htmlspecialchars($entry['scan_retur']); ?></td>
                                    <td><?php echo htmlspecialchars($entry['packer_no']); ?></td>
                                    <td><?php echo htmlspecialchars($entry['loc']); ?></td>
                                    <td><?php echo htmlspecialchars($entry['catatan']); ?></td> <!-- Display Catatan -->
                                    <td>
                                        <?php if ($video_path): ?>
                                            <!-- Buttons to view and download video -->
                                            <a href="<?php echo htmlspecialchars($video_path); ?>" target="_blank"
                                                class="btn btn-info btn-sm">View Video</a>
                                            <a href="<?php echo htmlspecialchars($video_path); ?>" download
                                                class="btn btn-success btn-sm">Download Video</a>
                                        <?php else: ?>
                                            <span class="text-danger">No Video</span>
                                        <?php endif; ?>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

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