<?php
// Page settings
$page_title = 'Sales Monthly Review';
require_once('includes/load.php');
page_require_level(8);

require_once('vendor/autoload.php'); // Sesuaikan jika library terpasang di folder vendor

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

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

// Get sales data for the selected month
$sales_totals = get_all_summary_totals($month);

// Function to fetch all summary totals from all warehouse sales tables (Surabaya, Jakarta, Makassar)
function get_all_summary_totals($month)
{
    global $db;

    // Query untuk menggabungkan data dari seluruh warehouse (Surabaya, Jakarta, Makassar, Bali) dan menyimpannya sebagai 1 data per SKU
    $sql = "
    SELECT 
        sales.part_number, 
        products.name, 
        categories.name AS category_name,
        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

    UNION ALL

    SELECT 
        sales_jakarta.part_number, 
        products.name, 
        categories.name AS category_name,
        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

    UNION ALL

    SELECT 
        sales_makassar.part_number, 
        products.name, 
        categories.name AS category_name,
        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
    ";

    return find_by_sql($sql);
}

// Get current day of the month for calculations
$current_day = date('j');

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">Sales Monthly Review</h3>
            </div>
            <div class="panel-body">
                <form method="post" action="review_outgoing.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>
                    <button type="button" class="btn btn-success" id="exportExcelBtn">Export to Excel</button>
                </form>

                <!-- Display Sales Data Table -->
                <?php display_sales_data($sales_totals, 'Sales Data Summary'); ?>
            </div>
        </div>
    </div>
</div>

<?php include_once('layouts/footer.php'); ?>
<script>
    document.getElementById('exportExcelBtn').addEventListener('click', function() {
        var wb = XLSX.utils.book_new(); // Membuat workbook baru

        // Fungsi untuk menambahkan sheet dari data penjualan
        function addSheetForSummary(sheetName, salesData) {
            var ws_data = [
                ['Part Number', 'Product Name', 'Category', 'Total Quantity']
            ]; // Header tabel

            // Menambahkan data ke sheet
            salesData.forEach(function(total) {
                ws_data.push([
                    total.part_number,
                    total.name,
                    total.category_name,
                    total.total_quantity
                ]);
            });

            var ws = XLSX.utils.aoa_to_sheet(ws_data); // Konversi data menjadi worksheet
            XLSX.utils.book_append_sheet(wb, ws, sheetName); // Tambahkan sheet ke workbook
        }

        // Data Summary untuk seluruh warehouse
        var summaryData = <?php echo json_encode($sales_totals); ?>;

        // Tambahkan sheet untuk Summary Data
        addSheetForSummary('Sales Summary', summaryData);

        // Simpan file Excel
        XLSX.writeFile(wb, "SalesSummaryReview.xlsx");
    });
</script>

<?php
// Helper function to display sales data in a table
function display_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>
                </tr>
            </thead>
            <tbody>
                <?php
                // Menjumlahkan kuantitas per SKU (part_number)
                $sku_totals = [];
                foreach ($sales_data as $total) {
                    if (!isset($sku_totals[$total['part_number']])) {
                        $sku_totals[$total['part_number']] = [
                            'part_number' => $total['part_number'],
                            'name' => $total['name'],
                            'category_name' => $total['category_name'],
                            'total_quantity' => 0
                        ];
                    }
                    $sku_totals[$total['part_number']]['total_quantity'] += $total['total_quantity'];
                }

                // Menampilkan data SKU yang sudah di-total
                foreach ($sku_totals as $total) {
                    echo "<tr>
                            <td>" . htmlspecialchars($total['part_number']) . "</td>
                            <td>" . htmlspecialchars($total['name']) . "</td>
                            <td>" . htmlspecialchars($total['category_name']) . "</td>
                            <td>" . htmlspecialchars($total['total_quantity']) . "</td>
                          </tr>";
                }
                ?>
            </tbody>
        </table>
    <?php else: ?>
        <p>No data available for the selected month.</p>
<?php endif;
}
?>