<?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
$start_month = isset($_POST['start_month']) ? $_POST['start_month'] : date('Y-m-01');
$end_month = isset($_POST['end_month']) ? $_POST['end_month'] : date('Y-m-t');

// Convert to full date format
$start_month = date('Y-m-01', strtotime($start_month));
$end_month = date('Y-m-t', strtotime($end_month));

// Initialize error message
$error_message = '';

// Get sales data for the selected months
try {
    $sales_totals = get_sales_totals($start_month, $end_month);
    $sales_jakarta_totals = get_sales_jakarta_totals($start_month, $end_month);
    $sales_makassar_totals = get_sales_makassar_totals($start_month, $end_month);
    $sales_bali_totals = get_sales_bali_totals($start_month, $end_month);
} catch (Exception $e) {
    $error_message = 'Error fetching sales data: ' . $e->getMessage();
}

// Function to fetch sales totals from the sales table
function get_sales_totals($start_month, $end_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 
                sales.date BETWEEN '{$start_month}' AND '{$end_month}'
                AND sales.qty IS NOT NULL
            GROUP BY 
                sales.part_number, products.name, categories.name
            ORDER BY 
                total_quantity DESC";
    return find_by_sql($sql);
}

// Function to fetch sales totals from the sales_makassar table
function get_sales_makassar_totals($start_month, $end_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 
                sales_makassar.date BETWEEN '{$start_month}' AND '{$end_month}'
                AND sales_makassar.qty IS NOT NULL
            GROUP BY 
                sales_makassar.part_number, products.name, categories.name
            ORDER BY 
                total_quantity DESC";
    return find_by_sql($sql);
}

// Function to fetch sales totals from the sales_jakarta table
function get_sales_jakarta_totals($start_month, $end_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 
                sales_jakarta.date BETWEEN '{$start_month}' AND '{$end_month}'
                AND sales_jakarta.qty IS NOT NULL
            GROUP BY 
                sales_jakarta.part_number, products.name, categories.name
            ORDER BY 
                total_quantity DESC";
    return find_by_sql($sql);
}

// Function to fetch sales totals from the sales_bali table
function get_sales_bali_totals($start_month, $end_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 
                sales_bali.date BETWEEN '{$start_month}' AND '{$end_month}'
                AND sales_bali.qty IS NOT NULL
            GROUP BY 
                sales_bali.part_number, products.name, categories.name
            ORDER BY 
                total_quantity DESC";
    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); ?>
        <?php if ($error_message): ?>
            <div class="alert alert-danger"><?php echo $error_message; ?></div>
        <?php endif; ?>
    </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_2month_outgoing.php" class="form-inline">
                    <div class="form-group">
                        <label for="start_month">Start Month:</label>
                        <input type="month" class="form-control" name="start_month"
                            value="<?php echo htmlspecialchars(date('Y-m', strtotime($start_month))); ?>">
                    </div>
                    <div class="form-group">
                        <label for="end_month">End Month:</label>
                        <input type="month" class="form-control" name="end_month"
                            value="<?php echo htmlspecialchars(date('Y-m', strtotime($end_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 Surabaya'); ?>
                <?php display_sales_data($sales_jakarta_totals, 'Sales Jakarta Data'); ?>
                <?php display_sales_data($sales_makassar_totals, 'Sales Makassar Data'); ?>
                <?php display_sales_data($sales_bali_totals, 'Sales Bali Data'); ?>
            </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 addSheetForLocation(sheetName, salesData) {
            var ws_data = [
                ['Part Number', 'Product Name', 'Category', 'Total Quantity', 'Resi Count', 'Average/Day']
            ]; // Header tabel

            // Menambahkan data ke sheet
            salesData.forEach(function(total) {
                ws_data.push([
                    total.part_number,
                    total.name,
                    total.category_name,
                    total.total_quantity,
                    total.resi_count,
                    (total.total_quantity / <?php echo $current_day; ?>).toFixed(2) // Average/day
                ]);
            });

            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 lokasi penjualan
        var surabayaData = <?php echo json_encode($sales_totals); ?>;
        var jakartaData = <?php echo json_encode($sales_jakarta_totals); ?>;
        var makassarData = <?php echo json_encode($sales_makassar_totals); ?>;
        var baliData = <?php echo json_encode($sales_bali_totals); ?>;

        // Tambahkan sheet untuk masing-masing lokasi
        addSheetForLocation('Surabaya', surabayaData);
        addSheetForLocation('Jakarta', jakartaData);
        addSheetForLocation('Makassar', makassarData);
        addSheetForLocation('Bali', baliData);

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

<?php
// Helper function to display sales data in a table
function display_sales_data($sales_data, $title)
{
    global $current_day;
    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>
                    <th>Average/day</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>
                        <td><?php echo calculate_average($total['total_quantity'], $current_day); ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    <?php else: ?>
        <p>No data available for the selected months in <?php echo $title; ?>.</p>
<?php endif;
}

// Helper functions for calculations
function calculate_average($total_quantity, $current_day)
{
    return $current_day > 0 ? number_format($total_quantity / $current_day, 2) : '0.00';
}
?>