<?php
$page_title = 'AWB Monthly Review';
require_once('includes/load.php');
page_require_level(3);

// Variable for search
$month = isset($_POST['month']) ? $_POST['month'] : date('Y-m');

function get_part_number_totals($month) {
    global $db;
    $sql = "SELECT 
                awb.part_number, 
                products.name, 
                COUNT(DISTINCT awb.resi) AS resi_count, 
                SUM(awb.quantity) AS total_quantity
            FROM 
                awb
            LEFT JOIN 
                products ON awb.part_number = products.part_number
            WHERE 
                DATE_FORMAT(awb.date_scan, '%Y-%m') = '{$month}'
                AND awb.scan_status IS NOT NULL
            GROUP BY 
                awb.part_number, products.name
            ORDER BY 
                total_quantity DESC";
    return find_by_sql($sql);
}


$part_number_totals = get_part_number_totals($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 clearfix">
                <h3 class="panel-title">AWB Monthly Review</h3>
            </div>
            <div class="panel-body">
                <form method="post" action="review_awb.php" class="form-inline">
                    <div class="form-group">
                        <label for="month">Filter by Month:</label>
                        <input type="month" class="form-control" name="month" value="<?php echo htmlspecialchars($month); ?>">
                    </div>
                    <button type="submit" class="btn btn-primary">Filter</button>
                </form>
                
                <?php if (!empty($part_number_totals)): ?>
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th>Part Number</th>
                            <th>Product Name</th>
                            <th>Total Quantity</th>
                            <th>Jumlah Resi</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($part_number_totals as $total): ?>
                            <tr>
                                <td><?php echo htmlspecialchars($total['part_number']); ?></td>
                                <td><?php echo htmlspecialchars($total['name']); ?></td>
                                <td><?php echo htmlspecialchars($total['total_quantity']); ?></td>
                                <td><?php echo htmlspecialchars($total['resi_count']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
                <?php else: ?>
                <p>No data available for the selected month.</p>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

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