<?php
include('includes/header.php'); // Include your database connection file
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Project-Wise Direct Labour - Month</title>
    <!-- Include the TableExport library -->
    <script src="https://cdn.jsdelivr.net/npm/tableexport@5.2.0/dist/js/tableexport.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/tableexport@5.2.0/dist/css/tableexport.min.css">
</head>

<body>
    <?php
    // Dynamic date calculations
    $currentDate = new DateTime();
    $currentYear = $currentDate->format('Y');
    $currentMonth = (int)$currentDate->format('m');
    
    // Calculate the reporting month (always the previous month)
    $reportingDate = clone $currentDate;
    $reportingDate->modify('-1 month');
    $reportingMonth = $reportingDate->format('m');
    $reportingYear = $reportingDate->format('Y');
    $reportingMonthName = date('F', $reportingDate->getTimestamp());
    
    // Create start and end dates for the reporting month
    $reportingMonthStart = clone $reportingDate;
    $reportingMonthStart->modify('first day of this month');
    $reportingMonthEnd = clone $reportingDate;
    $reportingMonthEnd->modify('last day of this month');
    
    // Format date strings for display and database queries
    $startMonth = $reportingMonthStart->format('Y-m-d');
    $endMonth = $reportingMonthEnd->format('Y-m-d');
    
    // Format the reporting month date for database insertion
    $reportingMonthDBDate = $reportingYear . '-' . $reportingMonth . '-01';
    ?>

    <h1>Project-Wise Direct Labour - Month</h1>
    <h3>Reporting Period: <?php echo $reportingMonthName . ' ' . $reportingYear; ?></h3>

    <?php
    // Your database connection code (already included)
    
    // Calculate Project Direct Labour (DL) for a particular milestone = hrs x employeeRate
    $queryDL = "SELECT clients.clientName, projects.projectName, milestones.milestoneName, SUM(timesheet.hrs) AS totalHrs, users.employeeRate, users.fname,
                (users.employeeRate * (SUM(timesheet.hrs) / 8)) AS DL
                FROM timesheet
                INNER JOIN milestones ON timesheet.milestoneID = milestones.milestoneID
                INNER JOIN projects ON milestones.projectID = projects.projectID
                INNER JOIN clients ON projects.clientID = clients.clientID
                INNER JOIN users ON timesheet.userID = users.userID
                WHERE timesheet.tentryDate BETWEEN '$startMonth' AND '$endMonth'
                AND projects.clientID NOT IN (27, 72, 436) -- Exclude specific client IDs
                GROUP BY clients.clientName, projects.projectName, milestones.milestoneName, users.employeeRate, users.fname
                ORDER BY clients.clientName ASC";

    $resultDL = mysqli_query($conn, $queryDL);

    if (!$resultDL) {
        die("Query failed: " . mysqli_error($conn));
    }

    // Calculate Project Direct Labour (DL) for a particular milestone = hrs x employeeRate
    $queryNDL = "SELECT clients.clientName, projects.projectName, milestones.milestoneName, SUM(timesheet.hrs) AS totalHrs, users.employeeRate, users.fname,
                (users.employeeRate * (SUM(timesheet.hrs) / 8)) AS DL
                FROM timesheet
                INNER JOIN milestones ON timesheet.milestoneID = milestones.milestoneID
                INNER JOIN projects ON milestones.projectID = projects.projectID
                INNER JOIN clients ON projects.clientID = clients.clientID
                INNER JOIN users ON timesheet.userID = users.userID
                WHERE timesheet.tentryDate BETWEEN '$startMonth' AND '$endMonth'
                AND projects.clientID IN (27, 72, 436) AND milestones.milestoneID NOT IN (1664) -- Exclude specific client IDs
                GROUP BY clients.clientName, projects.projectName, milestones.milestoneName, users.employeeRate, users.fname
                ORDER BY clients.clientName ASC";

    $resultNDL = mysqli_query($conn, $queryNDL);

    if (!$resultNDL) {
        die("Query failed: " . mysqli_error($conn));
    }

    // Calculate Cumulative Project Direct Labour for each project
    $queryCumulativeDL = "SELECT clients.clientName, projects.projectName, SUM(users.employeeRate * (timesheet.hrs / 8)) AS CumulativeDL, projects.directMaterialcost, projects.directMaterialcostPrev
                      FROM timesheet
                      INNER JOIN milestones ON timesheet.milestoneID = milestones.milestoneID
                      INNER JOIN projects ON milestones.projectID = projects.projectID
                      INNER JOIN clients ON projects.clientID = clients.clientID
                      INNER JOIN users ON timesheet.userID = users.userID
                      WHERE timesheet.tentryDate BETWEEN '$startMonth' AND '$endMonth'
                      AND milestones.milestoneID NOT IN (1664)
                      GROUP BY clients.clientName, projects.projectName
                      ORDER BY CumulativeDL DESC";

    $resultCumulativeDL = mysqli_query($conn, $queryCumulativeDL);

    if (!$resultCumulativeDL) {
        die("Query failed: " . mysqli_error($conn));
    }

    // Calculate Cumulative MIS DL (sum of Cumulative Project Direct Labour for each project excluding specific client IDs)
    $queryCumulativeMISDL = "SELECT SUM(CumulativeDL) AS CumulativeMISDL
                         FROM (
                           SELECT clients.clientName, projects.projectName, SUM(users.employeeRate * (timesheet.hrs / 8)) AS CumulativeDL
                           FROM timesheet
                           INNER JOIN milestones ON timesheet.milestoneID = milestones.milestoneID
                           INNER JOIN projects ON milestones.projectID = projects.projectID
                           INNER JOIN clients ON projects.clientID = clients.clientID
                           INNER JOIN users ON timesheet.userID = users.userID
                           WHERE timesheet.tentryDate BETWEEN '$startMonth' AND '$endMonth'
                           AND milestones.milestoneID NOT IN (1664)
                           GROUP BY clients.clientName, projects.projectName
                         ) AS ProjectDL";


    $resultCumulativeMISDL = mysqli_query($conn, $queryCumulativeMISDL);

    if (!$resultCumulativeMISDL) {
        die("Query failed: " . mysqli_error($conn));
    }

    // Calculate Cumulative MIS DL (sum of Cumulative Project Direct Labour for each project excluding specific client IDs)
    $queryCumulativeNMISDL = "SELECT SUM(CumulativeDL) AS CumulativeNMISDL
                         FROM (
                           SELECT clients.clientName, projects.projectName, SUM(users.employeeRate * (timesheet.hrs / 8)) AS CumulativeDL
                           FROM timesheet
                           INNER JOIN milestones ON timesheet.milestoneID = milestones.milestoneID
                           INNER JOIN projects ON milestones.projectID = projects.projectID
                           INNER JOIN clients ON projects.clientID = clients.clientID
                           INNER JOIN users ON timesheet.userID = users.userID
                           WHERE timesheet.tentryDate BETWEEN '$startMonth' AND '$endMonth'
                           AND projects.clientID NOT IN (27, 72, 436) AND milestones.milestoneID NOT IN (1664) -- Exclude specific client IDs
                           GROUP BY clients.clientName, projects.projectName
                         ) AS ProjectDL";


    $resultCumulativeNMISDL = mysqli_query($conn, $queryCumulativeNMISDL);

    if (!$resultCumulativeNMISDL) {
        die("Query failed: " . mysqli_error($conn));
    }
    ?>

    <!-- Display Project Direct Labour (DL) -->
    <h2 class="mt-6 mb-6">Project Revenue Generating Labour (RGL)</h2>
    <button onclick="exportTableToExcel('Project Wise DL(RGL) - <?php echo $reportingMonthName . '_' . $reportingYear; ?>.xlsx', 'tableRGL')" class='btn btn-primary float-end' style='margin-bottom: 10px;'>Download Excel</button>
    <table id='tableRGL' class='table table-bordered table-striped'>
        <thead class="align-middle text-center">
            <tr>
                <th>Employee Name</th>
                <th>Client</th>
                <th>Project</th>
                <th>Milestone</th>
                <th>Employee Rate</th>
                <th>Hours</th>
                <th>DL Charge</th>
            </tr>
        </thead>
        <tbody>
            <?php
            $currentProject = null;
            $totalHours = 0;
            $totalDL = 0;

            while ($row = mysqli_fetch_assoc($resultDL)) {
                if ($row['projectName'] !== $currentProject) {
                    // Display the total hours and total DL for the previous project row
                    if ($currentProject !== null) {
                        echo "<tr>";
                        echo "<td colspan='5'><strong>Total</strong></td>";
                        echo "<td><strong>" . number_format($totalHours, 2) . "</strong></td>";
                        echo "<td><strong>" . number_format($totalDL, 2) . "</strong></td>";
                        echo "</tr>";
                    }

                    // Reset totals for the new project
                    $currentProject = $row['projectName'];
                    $totalHours = 0;
                    $totalDL = 0;
                }

                // Update the totals for each row
                $totalHours += $row['totalHrs'];
                $totalDL += $row['DL'];

                $dlCharge = number_format($row['DL'], 2); // Format DL with 2 decimal places
                echo "<tr>";
                echo "<td>{$row['fname']}</td>";
                echo "<td>{$row['clientName']}</td>";
                echo "<td>{$row['projectName']}</td>";
                echo "<td>{$row['milestoneName']}</td>";
                echo "<td>{$row['employeeRate']}</td>";
                echo "<td>" . number_format($row['totalHrs'], 2) . "</td>";
                echo "<td>{$dlCharge}</td>"; // Display the formatted DL Charge
                echo "</tr>";

            }

            // Display the totals for the last project row
            if ($currentProject !== null) {
                // Calculate the total DL with 2 decimal places
                $totalDLFormatted = number_format($totalDL, 2);

                echo "<tr>";
                echo "<td colspan='5'><strong>Total</strong></td>";
                echo "<td><strong>" . number_format($totalHours, 2) . "</strong></td>";
                echo "<td><strong>$totalDLFormatted</strong></td>"; // Display the formatted total DL
                echo "</tr>";
            }
            ?>
        </tbody>
    </table>

    <!-- Display Project Direct Labour (DL) -->
    <h2>Project Non-Revenue Generating Labour (NRGL)</h2>
    <button onclick="exportTableToExcel('Project Wise DL(NRGL) - <?php echo $reportingMonthName . '_' . $reportingYear; ?>.xlsx', 'tableNRGL')" class='btn btn-primary float-end' style='margin-bottom: 10px;'>Download Excel</button>
    <table id='tableNRGL' class='table table-bordered table-striped'>
        <thead>
            <tr>
                <th>Employee Name</th>
                <th>Client</th>
                <th>Project</th>
                <th>Milestone</th>
                <th>Employee Rate</th>
                <th>Hours</th>
                <th>DL Charge</th>
            </tr>
        </thead>
        <tbody>
            <?php
            $currentProject = null;
            $totalHours = 0;
            $totalDL = 0;

            while ($row = mysqli_fetch_assoc($resultNDL)) {
                if ($row['projectName'] !== $currentProject) {
                    // Display the total hours and total DL for the previous project row
                    if ($currentProject !== null) {
                        echo "<tr>";
                        echo "<td colspan='5'><strong>Total</strong></td>";
                        echo "<td><strong>" . number_format($totalHours, 2) . "</strong></td>";
                        echo "<td><strong>" . number_format($totalDL, 2) . "</strong></td>";
                        echo "</tr>";
                    }

                    // Reset totals for the new project
                    $currentProject = $row['projectName'];
                    $totalHours = 0;
                    $totalDL = 0;
                }

                // Update the totals for each row
                $totalHours += $row['totalHrs'];
                $totalDL += $row['DL'];

                $dlCharge = number_format($row['DL'], 2); // Format DL with 2 decimal places
                echo "<tr>";
                echo "<td>{$row['fname']}</td>";
                echo "<td>{$row['clientName']}</td>";
                echo "<td>{$row['projectName']}</td>";
                echo "<td>{$row['milestoneName']}</td>";
                echo "<td>{$row['employeeRate']}</td>";
                echo "<td>" . number_format($row['totalHrs'], 2) . "</td>";
                echo "<td>{$dlCharge}</td>"; // Display the formatted DL Charge
                echo "</tr>";

            }

            // Display the totals for the last project row
            if ($currentProject !== null) {
                // Calculate the total DL with 2 decimal places
                $totalDLFormatted = number_format($totalDL, 2);

                echo "<tr>";
                echo "<td colspan='5'><strong>Total</strong></td>";
                echo "<td><strong>" . number_format($totalHours, 2) . "</strong></td>";
                echo "<td><strong>$totalDLFormatted</strong></td>"; // Display the formatted total DL
                echo "</tr>";
            }
            ?>
        </tbody>
    </table>

    <!-- Display Cumulative Project Direct Labour and Direct Material Cost -->
    <h2>Cumulative Project Direct Labour and Direct Material Cost</h2>
    <button onclick="exportTableToExcel('Monthly Project DL & DM - <?php echo $reportingMonthName . '_' . $reportingYear; ?>.xlsx', 'tableCumulativeDLDM')" class='btn btn-primary float-end' style='margin-bottom: 10px;'>Download Excel</button>
    <table id='tableCumulativeDLDM' class='table table-bordered table-striped'>
        <thead>
            <tr>
                <th>Client</th>
                <th>Project</th>
                <th>Monthly DL</th>
                <th>Monthly DM</th>
                <th>Total Monthly</th> <!-- New column for the sum -->
            </tr>
        </thead>
        <tbody>
            <?php
            $totalCumulativeDL = 0;
            $totalCumulativeDM = 0;

            while ($row = mysqli_fetch_assoc($resultCumulativeDL)) {
                $cumulativeDL = $row['CumulativeDL'];
                $cumulativeDM = $row['directMaterialcost'] - $row['directMaterialcostPrev'];
                $totalCumulativeDL += $cumulativeDL; // Calculate the total Cumulative DL
                $totalCumulativeDM += $cumulativeDM; // Calculate the total Cumulative DM
                $totalCumulative = $cumulativeDL + $cumulativeDM; // Calculate the sum
            
                echo "<tr>";
                echo "<td>{$row['clientName']}</td>";
                echo "<td>{$row['projectName']}</td>";
                echo "<td>" . number_format($cumulativeDL, 2) . "</td>";
                echo "<td>" . number_format($cumulativeDM, 2) . "</td>";
                echo "<td>" . number_format($totalCumulative, 2) . "</td>"; // Display the sum
                echo "</tr>";
            }
            ?>
        </tbody>
        <tfoot>
            <tr>
                <td colspan="2"><strong>Total</strong></td>
                <td><strong>
                        <?php echo number_format($totalCumulativeDL, 2); ?>
                    </strong></td>
                <td><strong>
                        <?php echo number_format($totalCumulativeDM, 2); ?>
                    </strong></td>
                <td><strong>
                        <?php echo number_format($totalCumulativeDL + $totalCumulativeDM, 2); ?>
                    </strong></td>
            </tr>
        </tfoot>
    </table>


    <!-- Display Cumulative MIS DL -->
    <h2>Monthly MIS Direct Labour With NRGL</h2>
    <h4>
        <?php
        $row = mysqli_fetch_assoc($resultCumulativeMISDL);
        $cumulativeMISDL = $row['CumulativeMISDL'];
        echo "Monthly MIS DL: " . number_format($cumulativeMISDL, 2);
        ?>
    </h4>

    <!-- Display Cumulative MIS DL -->
    <h2>Cumulative MIS Direct Labour Without NRGL</h2>
    <h4>
        <?php
        $row = mysqli_fetch_assoc($resultCumulativeNMISDL);
        $cumulativeNMISDL = $row['CumulativeNMISDL'];
        echo "Monthly MIS DL: " . number_format($cumulativeNMISDL, 2);
        
        // Store the calculated MIS income in the database with dynamic reporting month
        $insertQuery = "INSERT INTO mis_DL_monthly (month, monthly_DL_value) VALUES ('$reportingMonthDBDate', '$cumulativeNMISDL')
                        ON DUPLICATE KEY UPDATE monthly_DL_value = '$cumulativeNMISDL'";

        $result = mysqli_query($conn, $insertQuery);

        if (!$result) {
            die("Insert failed: " . mysqli_error($conn));
        }
        ?>
    </h4>

    <?php
    // Close the database connection
    mysqli_close($conn);
    ?>
    
    <script>
        // Function to export table to Excel format
        function exportTableToExcel(filename, tableId) {
            let table = document.getElementById(tableId); // Get the table by ID
            let wb = XLSX.utils.table_to_book(table, { sheet: "Sheet 1" }); // Convert table to Excel workbook
            XLSX.writeFile(wb, filename); // Generate and download the Excel file with the specified filename
        }
    </script>

</body>

</html>