<?php
// Include the TCPDF library
require_once('../tcpdf/tcpdf.php');
include './conn.php';

// Set title and heading
$title = 'Lorenzo Group';
$heading = 'Lorenzo Group Leaves';

$sql = "SELECT * FROM leave_requests ORDER BY leave_status";
$result = mysqli_query($conn, $sql);

$selectApproved = "SELECT COUNT(*) AS approved_count FROM leave_requests WHERE leave_status = 'Approved'";
$resultApproved = mysqli_query($conn, $selectApproved);
$approvedCount = mysqli_fetch_assoc($resultApproved)['approved_count'];

$selectPending = "SELECT COUNT(*) AS Pending_count FROM leave_requests WHERE leave_status = 'Pending'";
$resultPending = mysqli_query($conn, $selectPending);
$PendingCount = mysqli_fetch_assoc($resultPending)['Pending_count'];

$selectRejected = "SELECT COUNT(*) AS rejected_count FROM leave_requests WHERE leave_status = 'Rejected'";
$resultRejected = mysqli_query($conn, $selectRejected);
$rejectedCount = mysqli_fetch_assoc($resultRejected)['rejected_count'];

// Create a new TCPDF instance
$pdf = new TCPDF();

// Set document information
$pdf->SetCreator('Lorenzo');
$pdf->SetAuthor('Lorenzo');
$pdf->SetTitle($title); // Set title
$pdf->SetSubject($heading); // Set heading
$pdf->SetKeywords('Staff, Leave, Report');

// Add a page
$pdf->AddPage();

// Set font for the heading
$pdf->SetFont('helvetica', 'B', 16); // Bold, 16pt font

// Add heading to the PDF
$pdf->Cell(0, 10, $heading, 0, 1, 'C'); // Centered heading

// Set font for the table headers
$pdf->SetFont('helvetica', 'B', 12); // Bold, 11pt font

// Get current date
$currentDate = date('Y-m-d');

// Add date to the top right corner
$pdf->Cell(0, 10, 'Date: ' . $currentDate, 0, 1, 'R'); // Right-aligned

// Add approved and rejected counts to the PDF
$pdf->Cell(0, 10, 'Approved Leaves: ' . $approvedCount, 0, 1);
$pdf->Cell(0, 10, 'Pending Leaves: ' . $PendingCount, 0, 1);
$pdf->Cell(0, 10, 'Rejected Leaves: ' . $rejectedCount, 0, 1);

// Set font for the date
$pdf->SetFont('helvetica', '', 10); // Regular, 10pt font


// Set font for the table content
$pdf->SetFont('helvetica', '', 12); // Regular, 10pt font

// Add a table to the PDF
$html = "<table>
            <tr>
                <th width='200px'>Staff ID</th>
                <th width='250px'>Leave Type</th>
                <th width='180px'>Date Applied</th>
                <th width='180px'>Days Requested</th>
                <th width='180px'>Leave Status</th>
            </tr>";

// Loop through query results and add rows to the table
while ($row = mysqli_fetch_array($result)) {
    $html .= "<tr>
                <td width='200px'>" . $row['staff_id'] . "</td>
                <td width='250px'>" . $row['leave_type'] . "</td>
                <td width='180px'>" . $row['date_applied'] . "</td>
                <td width='180px'>" . $row['days_requested'] . "</td>
                <td width='180px'>" . $row['leave_status'] . "</td>
            </tr>";
}

$html .= "</table>";

// Output the HTML content
$pdf->writeHTML($html, true, false, true, false, '');

// Close and output PDF
$pdf->Output('lorenzo_leaves_report_' . date('Y-m-d') . '.pdf', 'D');

// Close the database connection
mysqli_close($conn);
?>
