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

// Fungsi untuk menghitung jumlah quantity_needed dari purchase_request_items dan total quantity dari incoming_goods berdasarkan part_number dan due_date
function get_summarized_request_items($year, $month) {
    global $db;
    
    // Query SQL untuk menghitung jumlah quantity_needed dari purchase_request_items
    $sql = "SELECT pri.part_number, 
                   SUM(pri.quantity_needed) as total_quantity_needed,
                   COALESCE(SUM(ig.quantity), 0) as total_quantity_incoming,
                   SUM(pri.quantity_needed) - COALESCE(SUM(ig.quantity), 0) as balance
            FROM purchase_request_items pri
            LEFT JOIN incoming_goods ig ON pri.part_number = ig.part_number 
                                           AND YEAR(ig.arrival_date) = '{$year}' 
                                           AND MONTH(ig.arrival_date) = '{$month}'
            WHERE YEAR(pri.due_date) = '{$year}' AND MONTH(pri.due_date) = '{$month}'
            GROUP BY pri.part_number";
    
    $result = $db->query($sql);
    $items = [];

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

    return $items;
}

// Ambil data dari filter atau gunakan bulan dan tahun saat ini sebagai default
$year = isset($_POST['year']) ? $_POST['year'] : date('Y');
$month = isset($_POST['month']) ? $_POST['month'] : date('m');

// Ambil data berdasarkan filter
$summarized_request_items = get_summarized_request_items($year, $month);

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 of Purchase Request Items by Part Number</h3>
            </div>
            <div class="panel-body">
                <!-- Form filter bulan dan tahun -->
                <form method="post" action="">
                    <div class="row">
                        <div class="col-md-3">
                            <div class="form-group">
                                <label for="year">Year:</label>
                                <select id="year" name="year" class="form-control">
                                    <?php for ($i = date('Y') - 10; $i <= date('Y') + 10; $i++): ?>
                                        <option value="<?php echo $i; ?>" <?php echo $i == $year ? 'selected' : ''; ?>>
                                            <?php echo $i; ?>
                                        </option>
                                    <?php endfor; ?>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="form-group">
                                <label for="month">Month:</label>
                                <select id="month" name="month" class="form-control">
                                    <?php foreach (range(1, 12) as $m): ?>
                                        <option value="<?php echo $m; ?>" <?php echo $m == $month ? 'selected' : ''; ?>>
                                            <?php echo date('F', mktime(0, 0, 0, $m, 1)); ?>
                                        </option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="form-group">
                                <button type="submit" class="btn btn-primary mt-4">Filter</button>
                            </div>
                        </div>
                    </div>
                </form>

                <table id="summarized-request-items-table" class="table table-bordered mt-3">
                    <thead>
                        <tr>
                            <th>Part Number</th>
                            <th>Total Quantity Needed</th>
                            <th>Total Quantity Incoming</th>
                            <th>Balance</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (!empty($summarized_request_items)) : ?>
                            <?php foreach ($summarized_request_items as $item) : ?>
                                <tr>
                                    <td><?php echo remove_junk($item['part_number']); ?></td>
                                    <td><?php echo remove_junk($item['total_quantity_needed']); ?></td>
                                    <td><?php echo remove_junk($item['total_quantity_incoming']); ?></td>
                                    <td><?php echo remove_junk($item['balance']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php else : ?>
                            <tr>
                                <td colspan="4">No data available</td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

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