<?php
// monthly_totals.php

include('includes/header.php'); // Include your header file

// Get the selected month and year from the query parameters (e.g., ?month=8&year=2023)
$selected_month = isset($_GET['month']) ? intval($_GET['month']) : date('m'); // Default to the current month
$selected_year = isset($_GET['year']) ? intval($_GET['year']) : date('Y');    // Default to the current year

// Construct a date range for the selected month and year
$start_date = "$selected_year-$selected_month-01";
$end_date = date('Y-m-t', strtotime($start_date));

// Add a condition to the SQL query to filter by the selected month and year
$filter_condition = "MONTH(t.tentryDate) = $selected_month AND YEAR(t.tentryDate) = $selected_year";

$query = "SELECT
            u.userID,
            u.fname,
            u.lname,
            u.budgetedRGLq1,
            u.budgetedRGLq2,
            u.budgetedRGLq3,
            u.budgetedRGLq4,
            u.employeeRate,
            t.clientID,
            SUM(CASE WHEN t.tentryDate BETWEEN '2026-04-01' AND '2026-06-30' AND t.clientID NOT IN (27, 72, 436) THEN t.hrs ELSE 0 END) AS total_hours_q1,
            SUM(CASE WHEN t.tentryDate BETWEEN '2026-07-01' AND '2026-09-30' AND t.clientID NOT IN (27, 72, 436) THEN t.hrs ELSE 0 END) AS total_hours_q2,
            SUM(CASE WHEN t.tentryDate BETWEEN '2026-10-01' AND '2026-12-31' AND t.clientID NOT IN (27, 72, 436) THEN t.hrs ELSE 0 END) AS total_hours_q3,
            SUM(CASE WHEN t.tentryDate BETWEEN '2027-01-01' AND '2027-03-31' AND t.clientID NOT IN (27, 72, 436) THEN t.hrs ELSE 0 END) AS total_hours_q4
          FROM timesheet t
          INNER JOIN users u ON t.userID = u.userID
          WHERE YEAR(t.tentryDate) IN (2024, 2025, 2026)
          GROUP BY u.userID, u.fname, u.lname
          ORDER BY u.fname ASC";

// Execute the query
$result = mysqli_query($conn, $query);

if (!$result) {
    // Handle database query error
    die("Query failed: " . mysqli_error($conn));
}
?>

<div class="container mt-4">
    <h1 class="mt-4 mb-4">RGL Variance Report</h1>
    <button id="download-excel" class="btn btn-primary float-end" style="margin-bottom: 10px;">Download Excel</button>
    <table class="table table-bordered table-striped table-hover text-center table-responsive">
        <thead class="align-middle text-center" style="background-color: #ccc;">
            <tr>
                <th rowspan="3">Employee Name</th>
                <th colspan="4">Q1</th>
                <th colspan="4">Q2</th>
                <th colspan="4">Q3</th>
                <th colspan="4">Q4</th>
            </tr>
            <tr>
                <th rowspan="2">Actual Days</th>
                <th rowspan="2">Budgeted Days</th> <!-- Added column for Budgeted Days in Q1 -->
                <th colspan="2">Variance</th>
                <th rowspan="2">Actual Days</th>
                <th rowspan="2">Budgeted Days</th> <!-- Added column for Budgeted Days in Q2 -->
                <th colspan="2">Variance</th>
                <th rowspan="2">Actual Days</th>
                <th rowspan="2">Budgeted Days</th> <!-- Added column for Budgeted Days in Q3 -->
                <th colspan="2">Variance</th>
                <th rowspan="2">Actual Days</th>
                <th rowspan="2">Budgeted Days</th> <!-- Added column for Budgeted Days in Q4 -->
                <th colspan="2">Variance</th>
            </tr>
            <tr>
                <th>Days</th>
                <th>Value (in Lakhs)</th>
                <th>Days</th>
                <th>Value (in Lakhs)</th>
                <th>Days</th>
                <th>Value (in Lakhs)</th>
                <th>Days</th>
                <th>Value (in Lakhs)</th>
            </tr>
        </thead>
        <tbody>
            <?php
            $total_actual_days_q1 = $total_budgeted_days_q1 = $total_variance_days_q1 = $total_variance_value_q1 = 0;
            $total_actual_days_q2 = $total_budgeted_days_q2 = $total_variance_days_q2 = $total_variance_value_q2 = 0;
            $total_actual_days_q3 = $total_budgeted_days_q3 = $total_variance_days_q3 = $total_variance_value_q3 = 0;
            $total_actual_days_q4 = $total_budgeted_days_q4 = $total_variance_days_q4 = $total_variance_value_q4 = 0;
            while ($row = mysqli_fetch_assoc($result)) {
                echo "<tr>";
                echo "<td>{$row['fname']} {$row['lname']}</td>";
                
                // Q1
                $total_hours_q1 = round($row['total_hours_q1']);
                $actual_days_q1 = round($total_hours_q1 / 8,1);
                $budgeted_days_q1 = round((176/8) * $row['budgetedRGLq1'] / 100)*3;
                echo "<td>{$actual_days_q1}</td>";
                echo "<td>{$budgeted_days_q1}</td>";
                echo "<td class='fw-bold'>" . ($actual_days_q1 - $budgeted_days_q1) . "</td>";
                echo "<td class='fw-bold'>" . number_format(($actual_days_q1 - $budgeted_days_q1) * ($row['employeeRate']) / 100000, 2) . "</td>";
                
                // Q2
                $total_hours_q2 = round($row['total_hours_q2']);
                $actual_days_q2 = round($total_hours_q2 / 8,1);
                $budgeted_days_q2 = round((176/8) * $row['budgetedRGLq2'] / 100)*3;
                echo "<td>{$actual_days_q2}</td>";
                echo "<td>{$budgeted_days_q2}</td>";
                echo "<td class='fw-bold'>" . ($actual_days_q2 - $budgeted_days_q2) . "</td>";
                echo "<td class='fw-bold'>" . number_format(($actual_days_q2 - $budgeted_days_q2) * ($row['employeeRate']) / 100000, 2) . "</td>";
                
                // Q3
                $total_hours_q3 = round($row['total_hours_q3']);
                $actual_days_q3 = round($total_hours_q3 / 8,1);
                $budgeted_days_q3 = round((176/8) * $row['budgetedRGLq3'] / 100)*3;
                echo "<td>{$actual_days_q3}</td>";
                echo "<td>{$budgeted_days_q3}</td>";
                echo "<td class='fw-bold'>" . ($actual_days_q3 - $budgeted_days_q3) . "</td>";
                echo "<td class='fw-bold'>" . number_format(($actual_days_q3 - $budgeted_days_q3) * ($row['employeeRate']) / 100000, 2) . "</td>";
                
                // Q4
                $total_hours_q4 = round($row['total_hours_q4']);
                $actual_days_q4 = round($total_hours_q4 / 8);
                $budgeted_days_q4 = round((176/8) * $row['budgetedRGLq4'] / 100)*3;
                echo "<td>{$actual_days_q4}</td>";
                echo "<td>{$budgeted_days_q4}</td>";
                echo "<td class='fw-bold'>" . ($actual_days_q4 - $budgeted_days_q4) . "</td>";
                echo "<td class='fw-bold'>" . number_format(($actual_days_q4 - $budgeted_days_q4) * ($row['employeeRate']) / 100000, 2) . "</td>";
                echo "</tr>";
                
                $total_actual_days_q1 += $actual_days_q1;
                $total_budgeted_days_q1 += $budgeted_days_q1;
                $total_variance_days_q1 += ($actual_days_q1 - $budgeted_days_q1);
                $total_variance_value_q1 += (($actual_days_q1 - $budgeted_days_q1) * ($row['employeeRate']) / 100000);
    
                $total_actual_days_q2 += $actual_days_q2;
                $total_budgeted_days_q2 += $budgeted_days_q2;
                $total_variance_days_q2 += ($actual_days_q2 - $budgeted_days_q2);
                $total_variance_value_q2 += (($actual_days_q2 - $budgeted_days_q2) * ($row['employeeRate']) / 100000);
    
                $total_actual_days_q3 += $actual_days_q3;
                $total_budgeted_days_q3 += $budgeted_days_q3;
                $total_variance_days_q3 += ($actual_days_q3 - $budgeted_days_q3);
                $total_variance_value_q3 += (($actual_days_q3 - $budgeted_days_q3) * ($row['employeeRate']) / 100000);
                
                $total_actual_days_q4 += $actual_days_q4;
                $total_budgeted_days_q4 += $budgeted_days_q4;
                $total_variance_days_q4 += ($actual_days_q4 - $budgeted_days_q4);
                $total_variance_value_q4 += (($actual_days_q4 - $budgeted_days_q4) * ($row['employeeRate']) / 100000);
            }
                // Display totals row
                echo "<tr class='fw-bold' style='background-color: #ccc;'>";
                echo "<td>Total</td>";
        
                // Display totals for each quarter
                echo "<td>{$total_actual_days_q1}</td>";
                echo "<td>{$total_budgeted_days_q1}</td>";
                echo "<td>{$total_variance_days_q1}</td>";
                echo "<td>" . number_format($total_variance_value_q1, 2) . "</td>";
        
                echo "<td>{$total_actual_days_q2}</td>";
                echo "<td>{$total_budgeted_days_q2}</td>";
                echo "<td>{$total_variance_days_q2}</td>";
                echo "<td>" . number_format($total_variance_value_q2, 2) . "</td>";
        
                echo "<td>{$total_actual_days_q3}</td>";
                echo "<td>{$total_budgeted_days_q3}</td>";
                echo "<td>{$total_variance_days_q3}</td>";
                echo "<td>" . number_format($total_variance_value_q3, 2) . "</td>";
                
                echo "<td>{$total_actual_days_q4}</td>";
                echo "<td>{$total_budgeted_days_q4}</td>";
                echo "<td>{$total_variance_days_q4}</td>";
                echo "<td>" . number_format($total_variance_value_q4, 2) . "</td>";
        
                echo "</tr>";
            ?>
        </tbody>
    </table>
</div>
<script>
    function downloadExcel() {
        const table = document.querySelector('.table'); // Select the table
        const wb = XLSX.utils.table_to_book(table, { sheet: "Variance Report" }); // Convert table to Excel workbook
        
        // Generate the Excel file and trigger the download
        XLSX.writeFile(wb, 'RGL Variance Report.xlsx');
    }
    
    // Add event listener to the "Download Excel" button
    document.getElementById('download-excel').addEventListener('click', downloadExcel);
</script>
<?php include('includes/footer.php'); ?>