<?php
$page_title = 'Plan vs Actual Production';
require_once('includes/load.php');
page_require_level(3); // Pastikan level pengguna yang diperlukan

// Variabel untuk menyimpan pesan dan data
$msg = '';

// Filter bulan
if (isset($_GET['month'])) {
    $selected_month = $_GET['month']; // Format: YYYY-MM
} else {
    $selected_month = date('Y-m'); // Default ke bulan ini
}

// Menghitung tanggal awal dan akhir bulan yang dipilih
$start_date = $selected_month . '-01';
$end_date = date("Y-m-t", strtotime($start_date)); // Ambil tanggal terakhir bulan tersebut

// Query untuk mengambil data jadwal produksi berdasarkan bulan
$schedule_query = "SELECT part_number, SUM(quantity_order) as total_scheduled FROM production_schedule WHERE delivery_date BETWEEN ? AND ? GROUP BY part_number";
$schedule_stmt = $db->prepare($schedule_query);
$schedule_stmt->bind_param("ss", $start_date, $end_date);
$schedule_stmt->execute();
$schedule_result = $schedule_stmt->get_result();
$schedules = $schedule_result ? $db->while_loop($schedule_result) : [];
$schedule_stmt->close();

// Query untuk mengambil data output produksi berdasarkan bulan
$output_query = "SELECT part_number, SUM(quantity_produced) as total_produced FROM production_output WHERE production_date BETWEEN ? AND ? GROUP BY part_number";
$output_stmt = $db->prepare($output_query);
$output_stmt->bind_param("ss", $start_date, $end_date);
$output_stmt->execute();
$output_result = $output_stmt->get_result();
$outputs = $output_result ? $db->while_loop($output_result) : [];
$output_stmt->close();

// Menggabungkan data jadwal dan output
$production_data = [];
foreach ($schedules as $schedule) {
    $part_number = $schedule['part_number'];
    $production_data[$part_number]['scheduled'] = $schedule['total_scheduled'];
    $production_data[$part_number]['produced'] = 0; // Default jika tidak ada output
}

foreach ($outputs as $output) {
    $part_number = $output['part_number'];
    if (!isset($production_data[$part_number])) {
        $production_data[$part_number]['scheduled'] = 0; // Jika tidak ada jadwal
    }
    $production_data[$part_number]['produced'] = $output['total_produced'];
}

// Menyiapkan total
$total_scheduled = 0;
$total_produced = 0;

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">
                <div class="pull-left">
                    <h3 class="panel-title">Plan vs Actual Production</h3>
                </div>
            </div>
            <div class="panel-body">
                <form method="GET" action="plan_actual.php">
                    <div class="form-group">
                        <label for="month">Pilih Bulan:</label>
                        <input type="month" name="month" id="month" class="form-control" value="<?php echo $selected_month; ?>">
                    </div>
                    <button type="submit" class="btn btn-primary">Filter</button>
                </form>

                <div class="table-responsive">
                    <table class="table table-bordered">
                        <thead>
                            <tr>
                                <th class="text-center">#</th>
                                <th>Part Number</th>
                                <th class="text-center">Total Scheduled</th>
                                <th class="text-center">Total Produced</th>
                                <th class="text-center">Balance</th>
                                <th class="text-center">Status</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($production_data as $part_number => $data) : ?>
                                <?php
                                    $scheduled = $data['scheduled'];
                                    $produced = $data['produced'];
                                    $balance = $scheduled - $produced;
                                    $status = '';

                                    if ($scheduled == 0 && $produced > 0) {
                                        $status = 'Tidak Ada Schedule';
                                    } elseif ($scheduled > 0 && $produced == 0) {
                                        $status = 'Ada Schedule';
                                    } else {
                                        $status = 'Normal';
                                    }

                                    // Update total
                                    $total_scheduled += $scheduled;
                                    $total_produced += $produced;
                                ?>
                                <tr>
                                    <td class="text-center"><?php echo count_id(); ?></td>
                                    <td><?php echo remove_junk($part_number); ?></td>
                                    <td class="text-center"><?php echo number_format($scheduled, 0); ?></td>
                                    <td class="text-center"><?php echo number_format($produced, 0); ?></td>
                                    <td class="text-center"><?php echo number_format($balance, 0); ?></td>
                                    <td class="text-center"><?php echo $status; ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                        <tfoot>
                            <tr>
                                <th class="text-center" colspan="2">Total</th>
                                <th class="text-center"><?php echo number_format($total_scheduled, 0); ?></th>
                                <th class="text-center"><?php echo number_format($total_produced, 0); ?></th>
                                <th class="text-center"><?php echo number_format($total_scheduled - $total_produced, 0); ?></th>
                                <th class="text-center"></th>
                            </tr>
                        </tfoot>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

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