<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);

$page_title = 'Monthly SKU Outgoing Comparison';
require_once('includes/load.php');
page_require_level(3); // Hanya level 3 yang diizinkan

// Ambil bulan yang dipilih dari form
$selected_month = isset($_POST['month']) ? $_POST['month'] : date('Y-m');

// Ambil data dari tabel `incoming_goods` untuk categorie_id 7
$query_outgoing_sku = "
    SELECT 
        DATE_FORMAT(sales_data.date, '%Y-%m') AS month,
        part_number,
        SUM(sales_data.qty) AS total_quantity
    FROM (
        SELECT date, qty, part_number FROM sales WHERE categorie_id IN (7, 8) -- Surabaya
        UNION ALL
        SELECT date, qty, part_number FROM sales_jakarta WHERE categorie_id IN (7, 8) -- Jakarta
        UNION ALL
        SELECT date, qty, part_number FROM sales_makassar WHERE categorie_id IN (7, 8) -- Makassar
        UNION ALL
        SELECT date, qty, part_number FROM sales_bali WHERE categorie_id IN (7, 8) -- Bali
        UNION ALL
        SELECT date, qty, part_number FROM sales_semarang WHERE categorie_id IN (7, 8) -- Semarang
        UNION ALL
        SELECT date, qty, part_number FROM sales_bandung WHERE categorie_id IN (7, 8) -- Bandung
    ) AS sales_data
    WHERE DATE_FORMAT(sales_data.date, '%Y-%m') = '$selected_month'
    GROUP BY month, part_number
    ORDER BY total_quantity DESC
";

// Eksekusi query untuk outgoing SKU
$result_outgoing_sku = $db->query($query_outgoing_sku);

// Siapkan data untuk chart
$data = [];
$outgoing_sku_data = [];
$total_outgoing_quantity = 0;

while ($row = $result_outgoing_sku->fetch_assoc()) {
    $outgoing_sku_data[$row['part_number']] = (int)$row['total_quantity'];
    $total_outgoing_quantity += (int)$row['total_quantity'];
}

// Hitung persentase kontribusi setiap SKU
foreach ($outgoing_sku_data as $part_number => $quantity) {
    $data[] = [
        'part_number' => $part_number,
        'total_quantity' => $quantity,
        'percentage' => ($total_outgoing_quantity > 0) ? ($quantity / $total_outgoing_quantity) * 100 : 0
    ];
}

// Ambil data dari tabel `incoming_goods` untuk categorie_id 7 untuk 5 bulan terakhir
$query_last_5_months = "
    SELECT 
        DATE_FORMAT(sales_data.date, '%Y-%m') AS month,
        part_number,
        SUM(sales_data.qty) AS total_quantity
    FROM (
        SELECT date, qty, part_number FROM sales WHERE categorie_id IN (7, 8) -- Surabaya
        UNION ALL
        SELECT date, qty, part_number FROM sales_jakarta WHERE categorie_id IN (7, 8) -- Jakarta
        UNION ALL
        SELECT date, qty, part_number FROM sales_makassar WHERE categorie_id IN (7, 8) -- Makassar
        UNION ALL
        SELECT date, qty, part_number FROM sales_bali WHERE categorie_id IN (7, 8) -- Bali
        UNION ALL
        SELECT date, qty, part_number FROM sales_semarang WHERE categorie_id IN (7, 8) -- Semarang
        UNION ALL
        SELECT date, qty, part_number FROM sales_bandung WHERE categorie_id IN (7, 8) -- Bandung
    ) AS sales_data
    WHERE sales_data.date >= DATE_SUB(CURDATE(), INTERVAL 5 MONTH)
    GROUP BY month, part_number
    ORDER BY month, part_number
";

// Eksekusi query untuk penjualan 5 bulan terakhir
$result_last_5_months = $db->query($query_last_5_months);

// Siapkan data untuk tabel penjualan 5 bulan terakhir
$last_5_months_data = [];
$months = [];

while ($row = $result_last_5_months->fetch_assoc()) {
    $month = $row['month'];
    $part_number = $row['part_number'];
    $total_quantity = (int)$row['total_quantity'];

    if (!in_array($month, $months)) {
        $months[] = $month;
    }

    if (!isset($last_5_months_data[$part_number])) {
        $last_5_months_data[$part_number] = [];
    }

    $last_5_months_data[$part_number][$month] = $total_quantity;
}

// Urutkan bulan dari terlama ke terbaru
sort($months);

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

<div class="row">
    <div class="col-md-12">
        <form method="POST" action="">
            <label for="month">Select Month:</label>
            <input type="month" id="month" name="month" value="<?php echo htmlspecialchars($selected_month); ?>" required>
            <button type="submit">Filter</button>
        </form>
        <div class="panel panel-default">
            <div class="panel-heading">
                <h3 class="panel-title text-center">Monthly SKU Outgoing Comparison for <?php echo htmlspecialchars($selected_month); ?></h3>
            </div>
            <div class="panel-body">
                <table class="table table-bordered table-striped table-hover">
                    <thead class="thead-dark">
                        <tr>
                            <th>Part Number</th>
                            <th>Total Outgoing Quantity</th>
                            <th>Percentage Contribution</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($data as $row): ?>
                            <tr>
                                <td><a href="view_sku_chart.php?part_number=<?php echo htmlspecialchars($row['part_number']); ?>"><?php echo htmlspecialchars($row['part_number']); ?></a></td>
                                <td><?php echo htmlspecialchars(number_format($row['total_quantity'])); ?></td>
                                <td><?php echo htmlspecialchars(number_format($row['percentage'], 2)); ?>%</td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading">
                <h3 class="panel-title text-center">Sales Data for the Last 5 Months</h3>
            </div>
            <div class="panel-body">
                <table class="table table-bordered table-striped table-hover">
                    <thead class="thead-dark">
                        <tr>
                            <th>Part Number</th>
                            <?php foreach ($months as $month): ?>
                                <th><?php echo htmlspecialchars($month); ?></th>
                            <?php endforeach; ?>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($last_5_months_data as $part_number => $monthly_data): ?>
                            <tr>
                                <td><?php echo htmlspecialchars($part_number); ?></td>
                                <?php foreach ($months as $month): ?>
                                    <td><?php echo htmlspecialchars(number_format($monthly_data[$month] ?? 0)); ?></td>
                                <?php endforeach; ?>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

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