<?php
require_once('includes/load.php');
require_once('vendor/autoload.php'); // Composer autoload

use TCPDF;

// Pastikan level pengguna yang diperlukan
page_require_level(3);

// 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, 
                            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";
$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, 
                            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";
$result_current_month_output = $db->query($query_current_month_output);

// Query untuk total volume sales bulan lalu
$query_last_month_sales = "SELECT p.part_number, p.name, 
                          SUM(s.qty) * p.volume AS total_volume
                    FROM sales 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";
$result_last_month_sales = $db->query($query_last_month_sales);

// Query untuk total volume sales bulan ini
$query_current_month_sales = "SELECT p.part_number, p.name, 
                          SUM(s.qty) * p.volume AS total_volume
                    FROM sales 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";
$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'],
        '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'],
        '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'],
        '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'],
        '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;

// Buat instance TCPDF
$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

// Set informasi dokumen
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Your Company');
$pdf->SetTitle('Volume Output Report');
$pdf->SetSubject('Volume Output Report');
$pdf->SetKeywords('TCPDF, PDF, report, volume output');

// Set header dan footer
$pdf->SetHeaderData('', 0, 'Volume Output Report', 'Generated by Your Company');
$pdf->setFooterData('', '', '');

$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

// Tambahkan halaman
$pdf->AddPage();

// Set font
$pdf->SetFont('helvetica', '', 9); // Mengatur ukuran font lebih kecil

// Judul
$pdf->Cell(0, 10, 'Total Volume Output and Sales', 0, 1, 'C');

// Total Volume Output dan Sales
$html = '
<table border="1" cellpadding="5">
    <tr>
        <th colspan="2">Total Volume Output</th>
        <th colspan="2">Total Volume Sales</th>
    </tr>
    <tr>
        <td><strong>Last Month</strong></td>
        <td>' . number_format($total_volume_last_month_output_liters, 2) . ' liters</td>
        <td><strong>Last Month</strong></td>
        <td>' . number_format($total_volume_last_month_sales_liters, 2) . ' liters</td>
    </tr>
    <tr>
        <td><strong>Current Month</strong></td>
        <td>' . number_format($total_volume_current_month_output_liters, 2) . ' liters</td>
        <td><strong>Current Month</strong></td>
        <td>' . number_format($total_volume_current_month_sales_liters, 2) . ' liters</td>
    </tr>
</table>
<br>
';

$pdf->writeHTML($html, true, false, true, false, '');

// Table Header
$html = '
<table border="1" cellpadding="5">
    <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>
';

// 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';
    
    $html .= '
    <tr>
        <td>' . htmlspecialchars($part_number) . '</td>
        <td>' . htmlspecialchars($name) . '</td>
        <td>' . $volume_output_last . '</td>
        <td>' . $volume_output_current . '</td>
        <td>' . $volume_sales_last . '</td>
        <td>' . $volume_sales_current . '</td>
    </tr>
    ';
}

$html .= '</tbody></table>';

// Tulis HTML ke PDF
$pdf->writeHTML($html, true, false, true, false, '');

// Output PDF
$pdf->Output('volume_output_report.pdf', 'I');
?>
