<?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 '2023-04-01' AND '2023-06-30' THEN t.hrs ELSE 0 END) AS total_hours_q1,
            SUM(CASE WHEN t.tentryDate BETWEEN '2023-07-01' AND '2023-09-30' THEN t.hrs ELSE 0 END) AS total_hours_q2,
            SUM(CASE WHEN t.tentryDate BETWEEN '2023-10-01' AND '2023-12-31' THEN t.hrs ELSE 0 END) AS total_hours_q3,
            SUM(CASE WHEN t.tentryDate BETWEEN '2024-01-01' AND '2024-03-31' 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) = 2023 OR 2024
          AND clientID NOT IN (27, 72, 436) -- Exclude specific client IDs
          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-csv' class='btn btn-primary float-end' style='margin-bottom: 10px;'>Download CSV</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);
                $budgeted_days_q1 = round((200/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);
                $budgeted_days_q2 = round((200/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);
                $budgeted_days_q3 = round((170/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((170/8) * ($row['budgetedRGLq4'] / 100));
                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 to trigger CSV download
        function downloadCSV() {
            const table = document.querySelector('.table');
            const rows = table.querySelectorAll('tr');
            const csv = [];

            // Generate CSV content
            rows.forEach((row, rowIndex) => {
                const rowData = [];
                row.querySelectorAll('td, th').forEach((cell, cellIndex) => {
                    // Wrap cell data in double quotes and escape existing double quotes
                    const cellData = cell.textContent.replace(/"/g, '""');
                    rowData.push(`"${cellData}"`);
                });

                // Include the header row if it's the first row
                if (rowIndex === 0) {
                    csv.push(rowData.join(','));
                } else {
                    csv.push(rowData.join(','));
                }
            });

            // Create a blob containing the CSV data
            const blob = new Blob([csv.join('\n')], { type: 'text/csv' });

            // Create a download link and trigger the download
            const link = document.createElement('a');
            link.href = URL.createObjectURL(blob);
            link.download = 'RGL_Variance.csv';
            link.style.display = 'none';
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
        }

        // Add click event listener to the download button
        document.getElementById('download-csv').addEventListener('click', downloadCSV);
</script>
<?php include('includes/footer.php'); ?>