<?php
require_once('includes/load.php');
require 'vendor/autoload.php'; // Load PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

page_require_level(3);

// Fungsi untuk mengambil summary item dengan filter
function get_summary_items($month = null, $year = null, $status = null, $search = null) {
    global $db;

    $sql = "SELECT pri.part_number, p.name AS product_name, p.UoM, 
            SUM(pri.quantity_needed) AS total_quantity_needed, 
            SUM(pri.incoming) AS total_incoming 
            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);
    }

    $sql .= " GROUP BY pri.part_number";

    $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;
}

// Set default bulan dan tahun
$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'] : '';

$summary_items = get_summary_items($month, $year, $status, $search);

// Fungsi untuk export ke Excel
if (isset($_POST['export_excel'])) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Summary Items');

    // Header
    $sheet->setCellValue('A1', 'Part Number');
    $sheet->setCellValue('B1', 'Product Name');
    $sheet->setCellValue('C1', 'UoM');
    $sheet->setCellValue('D1', 'Total Quantity Needed');
    $sheet->setCellValue('E1', 'Total Incoming');

    // Data
    $row = 2;
    foreach ($summary_items as $item) {
        $sheet->setCellValue('A' . $row, $item['part_number']);
        $sheet->setCellValue('B' . $row, $item['product_name']);
        $sheet->setCellValue('C' . $row, $item['UoM']); // Menambahkan UoM
        $sheet->setCellValue('D' . $row, $item['total_quantity_needed']);
        $sheet->setCellValue('E' . $row, $item['total_incoming']);
        $row++;
    }

    // Set auto-width column
    foreach (range('A', 'E') as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }

    // Export file
    $writer = new Xlsx($spreadsheet);
    $filename = 'Summary_Items_' . date('Y-m-d') . '.xlsx';

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header("Content-Disposition: attachment;filename=\"$filename\"");
    header('Cache-Control: max-age=0');

    $writer->save('php://output');
    exit;
}

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">Summary Item List</h3>
            </div>
            <div class="panel-body">
                <!-- Form untuk Filter -->
                <form action="summary_item_list.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>
                    <button type="submit" name="export_excel" class="btn btn-success">Export to Excel</button>
                </form>

                <!-- Tabel Summary -->
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th>Part Number</th>
                            <th>Product Name</th>
                            <th>UoM</th> <!-- Menambahkan UoM di header -->
                            <th>Total Quantity Needed</th>
                            <th>Total Incoming</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (!empty($summary_items)) : ?>
                            <?php foreach ($summary_items as $item) : ?>
                                <tr>
                                    <td><?php echo remove_junk($item['part_number']); ?></td>
                                    <td><?php echo remove_junk($item['product_name']); ?></td>
                                    <td><?php echo remove_junk($item['UoM']); ?></td> <!-- Menambahkan UoM -->
                                    <td><?php echo remove_junk($item['total_quantity_needed']); ?></td>
                                    <td><?php echo remove_junk($item['total_incoming']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php else : ?>
                            <tr>
                                <td colspan="5">No data available</td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

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