<?php
$page_title = 'Total Volume Output';
require_once('includes/load.php');
page_require_level(3); // Pastikan level pengguna yang diperlukan

// Inisialisasi variabel
$current_month_start = date('Y-m-01');
$current_month_end = date('Y-m-t');
$last_month_start = date('Y-m-01', strtotime('-1 month'));
$last_month_end = date('Y-m-t', strtotime('-1 month'));

// Query untuk total volume output bulan lalu
$query_last_month_output = "SELECT p.part_number, p.name, p.volume, 
                            SUM(po.quantity_produced) * p.volume AS total_volume
                      FROM production_output po
                      LEFT JOIN products p ON po.part_number = p.part_number
                      WHERE po.production_date BETWEEN '{$last_month_start}' AND '{$last_month_end}'
                      GROUP BY p.part_number, p.name, p.volume";
$result_last_month_output = $db->query($query_last_month_output);

// Query untuk total volume output bulan ini
$query_current_month_output = "SELECT p.part_number, p.name, p.volume, 
                            SUM(po.quantity_produced) * p.volume AS total_volume
                      FROM production_output po
                      LEFT JOIN products p ON po.part_number = p.part_number
                      WHERE po.production_date BETWEEN '{$current_month_start}' AND '{$current_month_end}'
                      GROUP BY p.part_number, p.name, p.volume";
$result_current_month_output = $db->query($query_current_month_output);

// Query untuk total volume sales bulan lalu dari tabel sales dan sales_jakarta
$query_last_month_sales = "SELECT p.part_number, p.name, p.volume, 
                          SUM(s.qty) * p.volume AS total_volume
                    FROM (
                        SELECT part_number, qty, date FROM sales
                        UNION ALL
                        SELECT part_number, qty, date FROM sales_jakarta
                    ) s
                    LEFT JOIN products p ON s.part_number = p.part_number
                    WHERE s.date BETWEEN '{$last_month_start}' AND '{$last_month_end}'
                    GROUP BY p.part_number, p.name, p.volume";
$result_last_month_sales = $db->query($query_last_month_sales);

// Query untuk total volume sales bulan ini dari tabel sales dan sales_jakarta
$query_current_month_sales = "SELECT p.part_number, p.name, p.volume, 
                          SUM(s.qty) * p.volume AS total_volume
                    FROM (
                        SELECT part_number, qty, date FROM sales
                        UNION ALL
                        SELECT part_number, qty, date FROM sales_jakarta
                    ) s
                    LEFT JOIN products p ON s.part_number = p.part_number
                    WHERE s.date BETWEEN '{$current_month_start}' AND '{$current_month_end}'
                    GROUP BY p.part_number, p.name, p.volume";
$result_current_month_sales = $db->query($query_current_month_sales);

// Ambil data volume output bulan lalu
$volume_last_month_output = [];
while ($row = $db->fetch_assoc($result_last_month_output)) {
    $volume_last_month_output[$row['part_number']] = [
        'name' => $row['name'],
        'volume' => $row['volume'],
        'total_volume' => $row['total_volume']
    ];
}

// Ambil data volume output bulan ini
$volume_current_month_output = [];
while ($row = $db->fetch_assoc($result_current_month_output)) {
    $volume_current_month_output[$row['part_number']] = [
        'name' => $row['name'],
        'volume' => $row['volume'],
        'total_volume' => $row['total_volume']
    ];
}

// Ambil data volume sales bulan lalu
$volume_last_month_sales = [];
while ($row = $db->fetch_assoc($result_last_month_sales)) {
    $volume_last_month_sales[$row['part_number']] = [
        'name' => $row['name'],
        'volume' => $row['volume'],
        'total_volume' => $row['total_volume']
    ];
}

// Ambil data volume sales bulan ini
$volume_current_month_sales = [];
while ($row = $db->fetch_assoc($result_current_month_sales)) {
    $volume_current_month_sales[$row['part_number']] = [
        'name' => $row['name'],
        'volume' => $row['volume'],
        'total_volume' => $row['total_volume']
    ];
}

// Hitung total volume
$total_volume_last_month_output = array_sum(array_column($volume_last_month_output, 'total_volume'));
$total_volume_current_month_output = array_sum(array_column($volume_current_month_output, 'total_volume'));
$total_volume_last_month_sales = array_sum(array_column($volume_last_month_sales, 'total_volume'));
$total_volume_current_month_sales = array_sum(array_column($volume_current_month_sales, 'total_volume'));

// Konversi ke liter (1 liter = 1000 mililiter)
$total_volume_last_month_output_liters = $total_volume_last_month_output / 1000;
$total_volume_current_month_output_liters = $total_volume_current_month_output / 1000;
$total_volume_last_month_sales_liters = $total_volume_last_month_sales / 1000;
$total_volume_current_month_sales_liters = $total_volume_current_month_sales / 1000;

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

<div class="row">
    <div class="col-md-12">
        <?php echo display_msg($msg); ?>
    </div>
</div>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading clearfix">
                <h3 class="panel-title">Total Volume Output and Sales</h3>
                <!-- Button to export PDF -->
                <form action="export_pdf_volume_output.php" method="post" style="margin-bottom: 20px;">
                    <button type="submit" name="export_pdf" class="btn btn-primary">Export to PDF</button>
                </form>
            </div>
            <div class="panel-body">
                <div class="alert alert-info">
                    <strong>Total Volume Output Last Month:</strong> <?php echo number_format($total_volume_last_month_output_liters, 2); ?> liters<br>
                    <strong>Total Volume Output Current Month:</strong> <?php echo number_format($total_volume_current_month_output_liters, 2); ?> liters<br>
                    <strong>Total Volume Sales Last Month:</strong> <?php echo number_format($total_volume_last_month_sales_liters, 2); ?> liters<br>
                    <strong>Total Volume Sales Current Month:</strong> <?php echo number_format($total_volume_current_month_sales_liters, 2); ?> liters
                </div>
                <div class="table-responsive">
                    <table class="table table-bordered">
                        <thead>
                            <tr>
                                <th>Part Number</th>
                                <th>Name</th>
                                <th>Output Volume Last Month (Liters)</th>
                                <th>Output Volume Current Month (Liters)</th>
                                <th>Sales Volume Last Month (Liters)</th>
                                <th>Sales Volume Current Month (Liters)</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            // Menggabungkan part_number dari semua data
                            $all_part_numbers = array_unique(array_merge(
                                array_keys($volume_last_month_output),
                                array_keys($volume_current_month_output),
                                array_keys($volume_last_month_sales),
                                array_keys($volume_current_month_sales)
                            ));

                            foreach ($all_part_numbers as $part_number) {
                                $name = isset($volume_last_month_output[$part_number]) ? $volume_last_month_output[$part_number]['name'] : '';
                                $volume_output_last = isset($volume_last_month_output[$part_number]) ? number_format($volume_last_month_output[$part_number]['total_volume'] / 1000, 2) : '0.00';
                                $volume_output_current = isset($volume_current_month_output[$part_number]) ? number_format($volume_current_month_output[$part_number]['total_volume'] / 1000, 2) : '0.00';
                                $volume_sales_last = isset($volume_last_month_sales[$part_number]) ? number_format($volume_last_month_sales[$part_number]['total_volume'] / 1000, 2) : '0.00';
                                $volume_sales_current = isset($volume_current_month_sales[$part_number]) ? number_format($volume_current_month_sales[$part_number]['total_volume'] / 1000, 2) : '0.00';
                                ?>
                                <tr>
                                    <td><?php echo remove_junk($part_number); ?></td>
                                    <td><?php echo remove_junk($name); ?></td>
                                    <td><?php echo $volume_output_last; ?></td>
                                    <td><?php echo $volume_output_current; ?></td>
                                    <td><?php echo $volume_sales_last; ?></td>
                                    <td><?php echo $volume_sales_current; ?></td>
                                </tr>
                                <?php
                            }
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

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