<?php  
// Page settings  
$page_title = 'Top 20 Outgoing Sales by City';  
require_once('includes/load.php');  
page_require_level(8);  
  
// Variable for search  
$month = isset($_POST['month']) ? $_POST['month'] : date('Y-m');  
  
// Get top 20 outgoing sales data for each city  
$top_sales_jakarta = get_top_sales_jakarta($month);  
$top_sales_makassar = get_top_sales_makassar($month);  
$top_sales_bali = get_top_sales_bali($month);  
$top_sales_surabaya = get_top_sales_surabaya($month);  
  
// Function to fetch top 20 sales from the sales_jakarta table  
function get_top_sales_jakarta($month) {  
    global $db;  
    $sql = "SELECT   
                sales_jakarta.part_number,   
                products.name,   
                categories.name AS category_name,  
                COUNT(sales_jakarta.part_number) AS resi_count,   
                SUM(sales_jakarta.qty) AS total_quantity  
            FROM   
                sales_jakarta  
            LEFT JOIN   
                products ON sales_jakarta.part_number = products.part_number  
            LEFT JOIN  
                categories ON sales_jakarta.categorie_id = categories.id  
            WHERE   
                DATE_FORMAT(sales_jakarta.date, '%Y-%m') = '{$month}'  
                AND sales_jakarta.qty IS NOT NULL  
            GROUP BY   
                sales_jakarta.part_number, products.name, categories.name  
            ORDER BY   
                total_quantity DESC  
            LIMIT 20"; // Limit to top 20  
    return find_by_sql($sql);  
}  
  
// Function to fetch top 20 sales from the sales_makassar table  
function get_top_sales_makassar($month) {  
    global $db;  
    $sql = "SELECT   
                sales_makassar.part_number,   
                products.name,   
                categories.name AS category_name,  
                COUNT(sales_makassar.part_number) AS resi_count,   
                SUM(sales_makassar.qty) AS total_quantity  
            FROM   
                sales_makassar  
            LEFT JOIN   
                products ON sales_makassar.part_number = products.part_number  
            LEFT JOIN  
                categories ON sales_makassar.categorie_id = categories.id  
            WHERE   
                DATE_FORMAT(sales_makassar.date, '%Y-%m') = '{$month}'  
                AND sales_makassar.qty IS NOT NULL  
            GROUP BY   
                sales_makassar.part_number, products.name, categories.name  
            ORDER BY   
                total_quantity DESC  
            LIMIT 20"; // Limit to top 20  
    return find_by_sql($sql);  
}  
  
// Function to fetch top 20 sales from the sales_bali table  
function get_top_sales_bali($month) {  
    global $db;  
    $sql = "SELECT   
                sales_bali.part_number,   
                products.name,   
                categories.name AS category_name,  
                COUNT(sales_bali.part_number) AS resi_count,   
                SUM(sales_bali.qty) AS total_quantity  
            FROM   
                sales_bali  
            LEFT JOIN   
                products ON sales_bali.part_number = products.part_number  
            LEFT JOIN  
                categories ON sales_bali.categorie_id = categories.id  
            WHERE   
                DATE_FORMAT(sales_bali.date, '%Y-%m') = '{$month}'  
                AND sales_bali.qty IS NOT NULL  
            GROUP BY   
                sales_bali.part_number, products.name, categories.name  
            ORDER BY   
                total_quantity DESC  
            LIMIT 20"; // Limit to top 20  
    return find_by_sql($sql);  
}  
  
// Function to fetch top 20 sales from the sales_surabaya table  
function get_top_sales_surabaya($month) {  
    global $db;  
    $sql = "SELECT   
                sales.part_number,   
                products.name,   
                categories.name AS category_name,  
                COUNT(sales.part_number) AS resi_count,   
                SUM(sales.qty) AS total_quantity  
            FROM   
                sales  
            LEFT JOIN   
                products ON sales.part_number = products.part_number  
            LEFT JOIN  
                categories ON sales.categorie_id = categories.id  
            WHERE   
                DATE_FORMAT(sales.date, '%Y-%m') = '{$month}'  
                AND sales.qty IS NOT NULL  
            GROUP BY   
                sales.part_number, products.name, categories.name  
            ORDER BY   
                total_quantity DESC  
            LIMIT 20"; // Limit to top 20  
    return find_by_sql($sql);  
}  
  
include_once('layouts/header.php');  
?>  
  
<!-- Display message -->  
<div class="row">  
    <div class="col-md-12">  
        <?php echo display_msg($msg); ?>  
    </div>  
</div>  
  
<!-- Filter form and table display -->  
<div class="row">  
    <div class="col-md-12">  
        <div class="panel panel-default">  
            <div class="panel-heading clearfix">  
                <h3 class="panel-title">Top 20 Outgoing Sales by City</h3>  
            </div>  
            <div class="panel-body">  
                <form method="post" action="review_outgoing_chart.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>  
  
                <!-- Display Top Sales Data Table -->  
                <?php display_top_sales_data($top_sales_jakarta, 'Top Sales Jakarta'); ?>  
                <?php display_top_sales_data($top_sales_makassar, 'Top Sales Makassar'); ?>  
                <?php display_top_sales_data($top_sales_bali, 'Top Sales Bali'); ?>  
                <?php display_top_sales_data($top_sales_surabaya, 'Top Sales Surabaya'); ?>  
            </div>  
        </div>  
    </div>  
</div>  
  
<?php include_once('layouts/footer.php'); ?>  
  
<!-- Helper function to display top sales data in a table -->  
function display_top_sales_data($sales_data, $title) {  
    if (!empty($sales_data)): ?>  
        <h4><?php echo $title; ?></h4>  
        <table class="table table-bordered">  
            <thead>  
                <tr>  
                    <th>Part Number</th>  
                    <th>Product Name</th>  
                    <th>Category</th>  
                    <th>Total Quantity</th>  
                    <th>Resi Count</th>  
                </tr>  
            </thead>  
            <tbody>  
                <?php foreach ($sales_data as $total): ?>  
                    <tr>  
                        <td><?php echo htmlspecialchars($total['part_number']); ?></td>  
                        <td><?php echo htmlspecialchars($total['name']); ?></td>  
                        <td><?php echo htmlspecialchars($total['category_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 in <?php echo $title; ?>.</p>  
    <?php endif;  
}  
?>  
