<?php    
$page_title = 'Summary Scan In Month Table';    
require_once('includes/load.php');    
page_require_level(3);    

// Set default timezone    
date_default_timezone_set('Asia/Jakarta');    

// Generate the last 6 months    
$months = [];    
for ($i = 0; $i < 6; $i++) {    
    $months[] = date('Y-m', strtotime("-$i months"));    
}    

// Initialize data array for each location    
$locations = ['Jakarta', 'Makassar', 'Bali', 'Semarang', 'Bandung', 'Surabaya'];    
$month_data = array_fill_keys($locations, array_fill_keys($months, 0));    

// Query to get total quantities per city for the last 6 months    
$sql = "SELECT awb_data.loc, DATE_FORMAT(awb_data.scan_in, '%Y-%m') AS month, SUM(awb_data.quantity) AS total_quantity ";
$sql .= "FROM awb_data ";
$sql .= "WHERE DATE_FORMAT(awb_data.scan_in, '%Y-%m') IN ('" . implode("', '", $months) . "') ";
$sql .= "GROUP BY awb_data.loc, month ";
$sql .= "ORDER BY awb_data.loc, month";
$result = $db->query($sql);    

// Populate the data array with query results    
while ($row = $result->fetch_assoc()) {    
    if (in_array($row['loc'], $locations)) {    
        $month_data[$row['loc']][$row['month']] = $row['total_quantity'];    
    }    
}    

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

<div class="row">    
    <div class="col-md-12">    
        <div class="panel panel-default">    
            <div class="panel-heading">    
                <h3 class="panel-title">Total Quantity per City for the Last 6 Months</h3>    
            </div>    
            <div class="panel-body">    
                <table class="table table-bordered table-striped" style="margin-top: 20px; background-color: #f9f9f9;">
                    <thead style="background-color: #007bff; color: white;">
                        <tr>
                            <th>Kota</th>
                            <?php foreach (array_reverse($months) as $month): ?>
                                <th><?php echo $month; ?></th>
                            <?php endforeach; ?>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($locations as $location): ?>
                        <tr>
                            <td><?php echo $location; ?></td>
                            <?php foreach (array_reverse($months) as $month): ?>
                                <td><?php echo $month_data[$location][$month]; ?></td>
                            <?php endforeach; ?>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>    
        </div>    
    </div>    
</div>    

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