<?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_sales_totals($month);
$sales_jakarta_totals = get_sales_jakarta_totals($month);
$sales_makassar_totals = get_sales_makassar_totals($month);
$sales_bali_totals = get_sales_bali_totals($month); // Tambahkan Bali      
$sales_semarang_totals = get_sales_semarang_totals($month);
$sales_bandung_totals = get_sales_bandung_totals($month); // Tambahkan Bandung

// Add stock data to sales data arrays  
function add_stock_data(&$sales_data, $location)
{
    foreach ($sales_data as &$total) {
        $total['stock'] = get_stock($total['part_number'], $location);
    }
}

// Fetch stock data for each location  
add_stock_data($sales_totals, 'surabaya');
add_stock_data($sales_jakarta_totals, 'jakarta');
add_stock_data($sales_makassar_totals, 'makassar');
add_stock_data($sales_bali_totals, 'bali');
add_stock_data($sales_semarang_totals, 'semarang');
add_stock_data($sales_bandung_totals, 'bandung'); // Tambahkan Bandung

// Function to fetch sales totals from the sales table      
function get_sales_totals($month)
{
    $sql = "SELECT       
                COALESCE(gpn.part_number, sales.part_number) AS 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  
            LEFT JOIN 
                group_part_number gpn ON sales.part_number = gpn.part_number_child       
            WHERE       
                DATE_FORMAT(sales.date, '%Y-%m') = '{$month}'      
                AND sales.qty IS NOT NULL AND sales.qty > 0   
                AND sales.remark LIKE '%scan%'  -- New clause to filter 'scan' in remark
            GROUP BY       
                COALESCE(gpn.part_number, 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($month)
{
    $sql = "SELECT       
                COALESCE(gpn.part_number, sales_makassar.part_number) AS 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  
            LEFT JOIN 
                group_part_number gpn ON sales_makassar.part_number = gpn.part_number_child    
            WHERE       
                DATE_FORMAT(sales_makassar.date, '%Y-%m') = '{$month}'      
                AND sales_makassar.qty IS NOT NULL AND sales_makassar.qty > 0     
                AND sales_makassar.remark LIKE '%scan%'  -- New clause to filter 'scan' in remark
            GROUP BY       
                COALESCE(gpn.part_number, 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($month)
{
    global $db;
    $sql = "SELECT       
                COALESCE(gpn.part_number, sales_jakarta.part_number) AS 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  
            LEFT JOIN 
                group_part_number gpn ON sales_jakarta.part_number = gpn.part_number_child        
            WHERE       
                DATE_FORMAT(sales_jakarta.date, '%Y-%m') = '{$month}'      
                AND sales_jakarta.qty IS NOT NULL AND sales_jakarta.qty > 0         
                AND sales_jakarta.remark LIKE '%scan%'  -- New clause to filter 'scan' in remark
            GROUP BY       
                COALESCE(gpn.part_number, 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($month)
{
    $sql = "SELECT       
                COALESCE(gpn.part_number, sales_bali.part_number) AS 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  
            LEFT JOIN 
                group_part_number gpn ON sales_bali.part_number = gpn.part_number_child      
            WHERE       
                DATE_FORMAT(sales_bali.date, '%Y-%m') = '{$month}'      
                AND sales_bali.qty IS NOT NULL AND sales_bali.qty > 0        
                AND sales_bali.remark LIKE '%scan%'  -- New clause to filter 'scan' in remark
            GROUP BY       
                COALESCE(gpn.part_number, sales_bali.part_number), products.name, categories.name      
            ORDER BY       
                total_quantity DESC";

    return find_by_sql($sql);
}

// Function to fetch sales totals from the sales_semarang table      
function get_sales_semarang_totals($month)
{
    $sql = "SELECT       
                COALESCE(gpn.part_number, sales_semarang.part_number) AS part_number,        
                products.name,       
                categories.name AS category_name,      
                COUNT(sales_semarang.part_number) AS resi_count,       
                SUM(sales_semarang.qty) AS total_quantity      
            FROM       
                sales_semarang      
            LEFT JOIN       
                products ON sales_semarang.part_number = products.part_number      
            LEFT JOIN      
                categories ON sales_semarang.categorie_id = categories.id  
            LEFT JOIN 
                group_part_number gpn ON sales_semarang.part_number = gpn.part_number_child     
            WHERE       
                DATE_FORMAT(sales_semarang.date, '%Y-%m') = '{$month}'      
                AND sales_semarang.qty IS NOT NULL AND sales_semarang.qty > 0     
                AND sales_semarang.remark LIKE '%scan%'  -- New clause to filter 'scan' in remark
            GROUP BY       
                COALESCE(gpn.part_number, sales_semarang.part_number), products.name, categories.name      
            ORDER BY       
                total_quantity DESC";
    return find_by_sql($sql);
}

// Function to fetch sales totals from the sales_bandung table      
function get_sales_bandung_totals($month)
{
    $sql = "SELECT       
                COALESCE(gpn.part_number, sales_bandung.part_number) AS part_number,        
                products.name,       
                categories.name AS category_name,      
                COUNT(sales_bandung.part_number) AS resi_count,       
                SUM(sales_bandung.qty) AS total_quantity      
            FROM       
                sales_bandung      
            LEFT JOIN       
                products ON sales_bandung.part_number = products.part_number      
            LEFT JOIN      
                categories ON sales_bandung.categorie_id = categories.id  
            LEFT JOIN 
                group_part_number gpn ON sales_bandung.part_number = gpn.part_number_child     
            WHERE       
                DATE_FORMAT(sales_bandung.date, '%Y-%m') = '{$month}'      
                AND sales_bandung.qty IS NOT NULL AND sales_bandung.qty > 0     
                AND sales_bandung.remark LIKE '%scan%'  -- New clause to filter 'scan' in remark
            GROUP BY       
                COALESCE(gpn.part_number, sales_bandung.part_number), products.name, categories.name      
            ORDER BY       
                total_quantity DESC";
    return find_by_sql($sql);
}

// Function to fetch stock from the appropriate table      
function get_stock($part_number, $location)
{
    global $db;
    $table_name = '';
    switch ($location) {
        case 'surabaya':
            $table_name = 'products'; // Sesuaikan dengan nama tabel stock Surabaya      
            break;
        case 'jakarta':
            $table_name = 'wh_jakarta'; // Sesuaikan dengan nama tabel stock Jakarta      
            break;
        case 'makassar':
            $table_name = 'wh_makassar'; // Sesuaikan dengan nama tabel stock Makassar      
            break;
        case 'bali':
            $table_name = 'wh_bali'; // Sesuaikan dengan nama tabel stock Bali      
            break;
        case 'semarang':
            $table_name = 'wh_semarang'; // Sesuaikan dengan nama tabel stock Semarang      
            break;
        case 'bandung':
            $table_name = 'wh_bandung'; // Tambahkan tabel stock Bandung      
            break;
        default:
            return 0;
    }

    $sql = "SELECT quantity AS stock FROM $table_name WHERE part_number = '{$part_number}'";
    $result = find_by_sql($sql);
    return !empty($result) ? $result[0]['stock'] : 0;
}

// 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 Surabaya', 'surabaya'); ?>
                <?php display_sales_data($sales_jakarta_totals, 'Sales Jakarta Data', 'jakarta'); ?>
                <?php display_sales_data($sales_makassar_totals, 'Sales Makassar Data', 'makassar'); ?>
                <?php display_sales_data($sales_bali_totals, 'Sales Bali Data', 'bali'); ?>
                <?php display_sales_data($sales_semarang_totals, 'Sales Semarang Data', 'semarang'); ?>
                <?php display_sales_data($sales_bandung_totals, 'Sales Bandung Data', 'bandung'); ?>
                <!-- Tambahkan Bandung -->

            </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, location) {
            var ws_data = [
                ['Part Number', 'Product Name', 'Category', 'Total Quantity', 'Resi Count', 'Average/Day',
                    'Estimated 1 Month', 'Stock'
                ]
            ]; // 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      
                    ((total.total_quantity / <?php echo $current_day; ?>) * 30).toFixed(2), // Estimated 1 Month      
                    total.stock // Stock      
                ]);
            });

            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); ?>;
        var semarangData = <?php echo json_encode($sales_semarang_totals); ?>;
        var bandungData = <?php echo json_encode($sales_bandung_totals); ?>; // Tambahkan Bandung

        // Tambahkan sheet untuk masing-masing lokasi      
        addSheetForLocation('Surabaya', surabayaData, 'surabaya');
        addSheetForLocation('Jakarta', jakartaData, 'jakarta');
        addSheetForLocation('Makassar', makassarData, 'makassar');
        addSheetForLocation('Bali', baliData, 'bali');
        addSheetForLocation('Semarang', semarangData, 'semarang');
        addSheetForLocation('Bandung', bandungData, 'bandung'); // Tambahkan Bandung

        // 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, $location)
{
    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>
                    <th>Est 1 Month</th>
                    <th>Stock</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>
                        <td><?php echo calculate_estimate($total['total_quantity'], $current_day); ?></td>
                        <td><?php echo htmlspecialchars($total['stock']); ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    <?php else: ?>
        <p>No data available for the selected month 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';
}

function calculate_estimate($total_quantity, $current_day)
{
    return $current_day > 0 ? number_format(($total_quantity / $current_day) * 30, 2) : '0.00';
}
?>
