<?php   
$page_title = 'Daily Resi Summary';   
require_once('includes/load.php');   
page_require_level(9);   
   
// Include PhpSpreadsheet classes   
use PhpOffice\PhpSpreadsheet\Spreadsheet;   
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;   
   
include_once('layouts/header.php');   
   
// Set default scan in date to the first day of the current month   
$current_month = date('Y-m');   
   
// Handle form submission   
$scan_in_month = $current_month; // Default to current month   
if (isset($_POST['check'])) {   
    $scan_in_month = $_POST['scan_in_month'];   
}   
   
// Query to get daily summary data for the selected month, grouped by location based on scan_in   
$sql_daily_summary = "   
    SELECT    
        DATE(scan_in) as scan_date,   
        CASE    
            WHEN loc = 'jakarta' THEN 'Jakarta'    
            WHEN loc = 'makassar' THEN 'Makassar'   
            WHEN loc = 'bali' THEN 'Bali'   
            WHEN loc = 'semarang' THEN 'Semarang'   
            WHEN loc = 'bandung' THEN 'Bandung'  -- Add Bandung here   
            WHEN loc = 'sidoarjo' THEN 'Surabaya'   
            ELSE 'Other'    
        END as location,    
        COUNT(DISTINCT resi) as total   
    FROM awb   
    WHERE DATE_FORMAT(scan_in, '%Y-%m') = '{$scan_in_month}'   
    GROUP BY scan_date, location   
    ORDER BY scan_date ASC, location ASC";   
   
$result_daily_summary = $db->query($sql_daily_summary);   
$daily_summary = [];   
while ($row_daily_summary = $result_daily_summary->fetch_assoc()) {   
    $daily_summary[$row_daily_summary['scan_date']][$row_daily_summary['location']] = $row_daily_summary['total'];   
}   
   
// Prepare data for the chart   
$dates = array_keys($daily_summary);   
$cities = ['Jakarta', 'Surabaya', 'Makassar', 'Bali', 'Semarang', 'Bandung'];   // Add Bandung here   
$chart_data = [];   
$total_resi_per_day = [];   
$average_data = [];   

// Initialize total, lowest, highest, and their corresponding dates per city   
$total_per_city = array_fill_keys($cities, 0);   
$lowest_per_city = array_fill_keys($cities, PHP_INT_MAX);   
$highest_per_city = array_fill_keys($cities, 0);   
$lowest_date_per_city = array_fill_keys($cities, null);   
$highest_date_per_city = array_fill_keys($cities, null);   

// Calculate total, average, lowest, highest, and their dates for each city   
foreach ($cities as $city) {   
    $city_data = [];   
    $total_count = 0;   
    $day_count = 0;   

    foreach ($dates as $date) {   
        $count = isset($daily_summary[$date][$city]) ? $daily_summary[$date][$city] : 0;   
        $city_data[] = $count;   
        $total_count += $count;   
        $total_per_city[$city] += $count; // Add to total per city   
        if ($count > 0) {   
            $day_count++;   
        }   
        // Calculate lowest and highest   
        if ($count > 0) {   
            if ($count < $lowest_per_city[$city]) {   
                $lowest_per_city[$city] = $count;   
                $lowest_date_per_city[$city] = $date; // Store the date of the lowest resi   
            }   
            if ($count > $highest_per_city[$city]) {   
                $highest_per_city[$city] = $count;   
                $highest_date_per_city[$city] = $date; // Store the date of the highest resi   
            }   
        }   
    }   
    $chart_data[$city] = $city_data;   
    $average_data[$city] = $day_count > 0 ? $total_count / $day_count : 0; // Calculate average   
}   

// Calculate total resi per day   
foreach ($dates as $date) {   
    $total_resi_per_day[$date] = 0;   
    foreach ($cities as $city) {   
        $total_resi_per_day[$date] += isset($daily_summary[$date][$city]) ? $daily_summary[$date][$city] : 0;   
    }   
}   

// Function to export data to Excel   
if (isset($_POST['export_excel'])) {   
    $spreadsheet = new Spreadsheet();   
    $sheet = $spreadsheet->getActiveSheet();   

    // Set the header for the Excel file   
    $sheet->setCellValue('A1', 'Date');   
    $sheet->setCellValue('B1', 'Total Resi');   
    $sheet->setCellValue('C1', 'Jakarta');   
    $sheet->setCellValue('D1', 'Surabaya');   
    $sheet->setCellValue('E1', 'Makassar');   
    $sheet->setCellValue('F1', 'Bali');   
    $sheet->setCellValue('G1', 'Semarang');   
    $sheet->setCellValue('H1', 'Bandung');   // Add Bandung here   

    // Populate the data   
    $row = 2; // Start from the second row   
    foreach ($dates as $date) {   
        $total_for_day = 0;   
        foreach ($cities as $city) {   
            $total_for_day += isset($daily_summary[$date][$city]) ? $daily_summary[$date][$city] : 0;   
        }   
        $sheet->setCellValue('A' . $row, $date); // Date can remain a string   
        $sheet->setCellValue('B' . $row, $total_for_day);   
        $sheet->setCellValue('C' . $row, isset($daily_summary[$date]['Jakarta']) ? $daily_summary[$date]['Jakarta'] : 0);   
        $sheet->setCellValue('D' . $row, isset($daily_summary[$date]['Surabaya']) ? $daily_summary[$date]['Surabaya'] : 0);   
        $sheet->setCellValue('E' . $row, isset($daily_summary[$date]['Makassar']) ? $daily_summary[$date]['Makassar'] : 0);   
        $sheet->setCellValue('F' . $row, isset($daily_summary[$date]['Bali']) ? $daily_summary[$date]['Bali'] : 0);   
        $sheet->setCellValue('G' . $row, isset($daily_summary[$date]['Semarang']) ? $daily_summary[$date]['Semarang'] : 0);   
        $sheet->setCellValue('H' . $row, isset($daily_summary[$date]['Bandung']) ? $daily_summary[$date]['Bandung'] : 0);   // Add Bandung here   
        $row++;   
    }   

    // Set the filename and save the file   
    $writer = new Xlsx($spreadsheet);   
    $filename = 'daily_resi_summary_' . $scan_in_month . '.xlsx'; // Dynamic filename for clarity   

    // Set headers for download   
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');   
    header('Content-Disposition: attachment; filename="' . $filename . '"');   
    header('Cache-Control: max-age=0');   

    // Save the file to output   
    $writer->save('php://output');   
    exit; // Stop further execution   
}   
?>   
   
<div class="row">   
    <div class="col-md-12">   
        <div class="panel panel-default">   
            <div class="panel-heading">   
                <h3 class="panel-title">Daily Resi Summary</h3>   
            </div>   
            <div class="panel-body">   
                <form method="post" action="resi_daily.php">   
                    <div class="form-group">   
                        <label for="scan_in_month">Select Month</label>   
                        <input type="month" class="form-control" name="scan_in_month" id="scan_in_month" value="<?php echo htmlspecialchars($scan_in_month); ?>" required>   
                    </div>   
                    <button type="submit" name="check" class="btn btn-primary">Check</button>   
                    <button type="submit" name="export_excel" class="btn btn-success">Export to Excel</button> <!-- Export Button -->   
                </form>   
            </div>   
        </div>   
   
        <!-- Combined Chart Display -->   
        <div class="panel panel-default">   
            <div class="panel-heading">   
                <h3 class="panel-title">Daily Resi for All Cities</h3>   
            </div>   
            <div class="panel-body">   
                <canvas id="resiChart" width="400" height="200"></canvas>   
            </div>   
        </div>   
   
        <!-- Data Table Display -->   
        <div class="panel panel-default">   
            <div class="panel-heading">   
                <h3 class="panel-title">Daily Resi Data Table</h3>   
            </div>   
            <div class="panel-body">   
                <div class="table-responsive">   
                    <table class="table table-bordered table-striped">   
                        <thead>   
                            <tr>   
                                <th>Date</th>   
                                <th>Total Resi</th> <!-- New Total Column -->   
                                <th>Jakarta</th>   
                                <th>Surabaya</th>   
                                <th>Makassar</th>   
                                <th>Bali</th>   
                                <th>Semarang</th>   
                                <th>Bandung</th>   <!-- Add Bandung here -->   
                            </tr>   
                        </thead>   
                        <tbody>   
                            <?php foreach ($dates as $date): ?>   
                                <?php    
                                    // Calculate total for the day   
                                    $total_for_day = 0;   
                                    foreach ($cities as $city) {   
                                        $total_for_day += isset($daily_summary[$date][$city]) ? $daily_summary[$date][$city] : 0;   
                                    }   
                                ?>   
                                <tr>   
                                    <td><?php echo htmlspecialchars($date); ?></td>   
                                    <td><?php echo $total_for_day; ?></td> <!-- Display Total -->   
                                    <td><?php echo isset($daily_summary[$date]['Jakarta']) ? htmlspecialchars($daily_summary[$date]['Jakarta']) : 0; ?></td>   
                                    <td><?php echo isset($daily_summary[$date]['Surabaya']) ? htmlspecialchars($daily_summary[$date]['Surabaya']) : 0; ?></td>   
                                    <td><?php echo isset($daily_summary[$date]['Makassar']) ? htmlspecialchars($daily_summary[$date]['Makassar']) : 0; ?></td>   
                                    <td><?php echo isset($daily_summary[$date]['Bali']) ? htmlspecialchars($daily_summary[$date]['Bali']) : 0; ?></td>   
                                    <td><?php echo isset($daily_summary[$date]['Semarang']) ? htmlspecialchars($daily_summary[$date]['Semarang']) : 0; ?></td>   
                                    <td><?php echo isset($daily_summary[$date]['Bandung']) ? htmlspecialchars($daily_summary[$date]['Bandung']) : 0; ?></td>   <!-- Add Bandung here -->   
                                </tr>   
                            <?php endforeach; ?>   
                        </tbody>   
                    </table>   
                </div>   
            </div>   
        </div>   
   
        <!-- Average Resi Display -->   
        <div class="panel panel-default">   
            <div class="panel-heading">   
                <h3 class="panel-title">Average Resi Data</h3>   
            </div>   
            <div class="panel-body">   
                <table class="table table-bordered table-striped">   
                    <thead>   
                        <tr>   
                            <th>City</th>   
                            <th>Average Resi</th>   
                        </tr>   
                    </thead>   
                    <tbody>   
                        <?php foreach ($cities as $city): ?>   
                            <tr>   
                                <td><?php echo htmlspecialchars($city); ?></td>   
                                <td><?php echo number_format($average_data[$city], 2); ?></td> <!-- Display Average -->   
                            </tr>   
                        <?php endforeach; ?>   
                    </tbody>   
                </table>   
            </div>   
        </div>   
   
        <!-- Total Resi Per City Display -->   
        <div class="panel panel-default">   
            <div class="panel-heading">   
                <h3 class="panel-title">Total Resi Per City</h3>   
            </div>   
            <div class="panel-body">   
                <table class="table table-bordered table-striped">   
                    <thead>   
                        <tr>   
                            <th>City</th>   
                            <th>Total Resi</th>   
                        </tr>   
                    </thead>   
                    <tbody>   
                        <?php foreach ($cities as $city): ?>   
                            <tr>   
                                <td><?php echo htmlspecialchars($city); ?></td>   
                                <td><?php echo isset($total_per_city[$city]) ? htmlspecialchars($total_per_city[$city]) : 0; ?></td>   
                            </tr>   
                        <?php endforeach; ?>   
                    </tbody>   
                </table>   
            </div>   
        </div>   
   
        <!-- Lowest and Highest Resi Per City Display -->   
        <div class="panel panel-default">   
            <div class="panel-heading">   
                <h3 class="panel-title">Lowest and Highest Resi Per City</h3>   
            </div>   
            <div class="panel-body">   
                <table class="table table-bordered table-striped">   
                    <thead>   
                        <tr>   
                            <th>City</th>   
                            <th>Lowest Resi</th>   
                            <th>Date of Lowest Resi</th>   
                            <th>Highest Resi</th>   
                            <th>Date of Highest Resi</th>   
                        </tr>   
                    </thead>   
                    <tbody>   
                        <?php foreach ($cities as $city): ?>   
                            <tr>   
                                <td><?php echo htmlspecialchars($city); ?></td>   
                                <td><?php echo $lowest_per_city[$city] === PHP_INT_MAX ? 0 : $lowest_per_city[$city]; ?></td> <!-- Display Lowest -->   
                                <td><?php echo $lowest_date_per_city[$city] !== null ? htmlspecialchars($lowest_date_per_city[$city]) : 'N/A'; ?></td> <!-- Display Date of Lowest -->   
                                <td><?php echo $highest_per_city[$city]; ?></td> <!-- Display Highest -->   
                                <td><?php echo $highest_date_per_city[$city] !== null ? htmlspecialchars($highest_date_per_city[$city]) : 'N/A'; ?></td> <!-- Display Date of Highest -->   
                            </tr>   
                        <?php endforeach; ?>   
                    </tbody>   
                </table>   
            </div>   
        </div>   
    </div>   
</div>   
   
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>   
<script>   
    const dates = <?php echo json_encode($dates); ?>;   
    const chartData = <?php echo json_encode($chart_data); ?>;   
    const totalResiPerDay = <?php echo json_encode(array_values($total_resi_per_day)); ?>;   

    const ctx = document.getElementById('resiChart').getContext('2d');   
    const resiChart = new Chart(ctx, {   
        type: 'bar', // Grouped bar chart   
        data: {   
            labels: dates,   
            datasets: [   
                {   
                    label: 'Jakarta',   
                    data: chartData['Jakarta'],   
                    backgroundColor: 'rgba(255, 99, 71, 0.8)', // Tomato   
                },   
                {   
                    label: 'Surabaya',   
                    data: chartData['Surabaya'],   
                    backgroundColor: 'rgba(255, 165, 0, 0.8)', // Orange   
                },   
                {   
                    label: 'Makassar',   
                    data: chartData['Makassar'],   
                    backgroundColor: 'rgba(30, 144, 255, 0.8)', // Dodger Blue   
                },   
                {   
                    label: 'Bali',   
                    data: chartData['Bali'],   
                    backgroundColor: 'rgba(34, 139, 34, 0.8)', // Forest Green   
                },   
                {   
                    label: 'Semarang',   
                    data: chartData['Semarang'],   
                    backgroundColor: 'rgba(128, 0, 128, 0.8)', // Purple   
                },   
                {   
                    label: 'Bandung',   // Add Bandung here   
                    data: chartData['Bandung'],   // Add Bandung here   
                    backgroundColor: 'rgba(255, 215, 0, 0.8)', // Gold   
                },   
                {   
                    label: 'Total Resi',   
                    data: totalResiPerDay,   
                    type: 'line',   
                    borderColor: 'rgba(211, 211, 211, 0.8)', // Black   
                    borderWidth: 2,   
                    fill: false,   
                }   
            ]   
        },   
        options: {   
            responsive: true,   
            scales: {   
                y: {   
                    beginAtZero: true,   
                    title: {   
                        display: true,   
                        text: 'Total Resi'   
                    }   
                },   
                x: {   
                    title: {   
                        display: true,   
                        text: 'Date'   
                    }   
                }   
            },   
            plugins: {   
                legend: {   
                    position: 'top',   
                },   
                title: {   
                    display: true,   
                    text: 'Daily Resi Summary by City'   
                }   
            }   
        }   
    });   
</script>   
   
<?php include_once('layouts/footer.php'); ?>   