<?php
// public/reports/export_personnel.php
declare(strict_types=1);

require_once __DIR__ . '/../../includes/auth.php';
require_once __DIR__ . '/../../includes/flash.php';
require_once __DIR__ . '/../../vendor/autoload.php';
require_once __DIR__ . '/../../config/db.php';
global $pdo;

use Dompdf\Dompdf;

// Collect filters from GET
$q  = $_GET['q'] ?? '';
$yr = $_GET['year'] ?? '';

// Build SQL
$sql = "SELECT 
            `id`,`employee_id`, `name`, `position`, `employee_type`, `status`,
            `address`, `cpnumber`, `type`, `level`, `created_at`, `updated_at`, `date_from`, `date_to`
        FROM `personnel`
        WHERE 1=1";

$params = [];

if ($q !== '') {
    $sql .= " AND (
        `employee_id`   LIKE :q OR 
        `name`          LIKE :q OR 
        `position`      LIKE :q OR 
        `employee_type` LIKE :q OR 
        `status`        LIKE :q OR 
        `address`       LIKE :q OR 
        `cpnumber`      LIKE :q OR 
        `type`          LIKE :q OR 
        `level`         LIKE :q
    )";
    $params[':q'] = "%$q%";
}

if ($yr !== '') {
    $sql .= " AND (
        ((`date_from` IS NOT NULL AND YEAR(`date_from`) = :yr)
          OR (`date_to` IS NOT NULL AND YEAR(`date_to`) = :yr))
        OR YEAR(`created_at`) = :yr
    )";
    $params[':yr'] = $yr;
}

$sql .= " ORDER BY `created_at` DESC";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

/* ---- PDF Export ---- */
$logoLeft  = realpath(__DIR__ . 'logo_left.png');
$logoRight = realpath(__DIR__ . 'logo_right.png');
 // update path if needed

$html = '<style>
    body { font-family: DejaVu Sans, Arial, sans-serif; font-size: 11px; }
    .header { width: 100%; border: 1px solid #000; border-collapse: collapse; margin-bottom: 10px; }
    .header td { border: 1px solid #000; text-align: center; vertical-align: middle; }
    .header img { height: 70px; }
    h2 { text-align:center; margin: 8px 0; }
    table { width:100%; border-collapse: collapse; margin-top: 10px; }
    th, td { border:1px solid #333; padding:5px 7px; font-size: 10px; }
    th { background:#f0f0f0; }
</style>';

$html .= '<table class="header">
    <tr>
        <td style="width: 15%;"><img src="http://localhost/ldp/public/assets/img/logo.png" alt="Logo" class="brand-image img-circle elevation-3" style="opacity:.9"></td>
        <td style="width: 70%;">
            Republic of the Philippines<br>
            <strong>DR. EMILIO B. ESPINOSA SR., MEMORIAL STATE COLLEGE OF AGRICULTURE AND TECHNOLOGY</strong><br>
            Cabitan, Mandaon, Masbate | <a href="http://www.debesmscat.edu.ph">www.debesmscat.edu.ph</a><br>
            <strong style="font-size:14px;">PERSONNEL REPORTS</strong>
        </td>
        
        <td style="width: 15%;">
            
            <img src="../r.png" alt="mylogo" style="width:15%x; margin-top:10px;">
        </td>
    </tr>
</table>';

//<th>Title</th><th>Title of L&D</th> <th>Date From</th><th>Date To</th><th>Type</th><th>Level</th>

$html .= '<table><thead><tr>
            <th>ID No.</th><th>Name</th><th>Position</th><th>Employee Type</th>
            <th>Status</th> <th>Category</th>
          </tr></thead><tbody>';

foreach ($rows as $r) {
	
	 $babykop=$r['id']?? null;
	 
	 
	 $con = mysqli_connect("localhost","root","","ldp_db");

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

  date_default_timezone_set("Asia/Manila"); 
			    	$query=mysqli_query($con,"select * from training where traineename='$babykop'")or die(mysqli_error($con));
		$row56=mysqli_fetch_array($query);
	
    $html .= '<tr>'
        . '<td>' . htmlspecialchars($r['employee_id'] ?? '') . '</td>'
        . '<td>' . htmlspecialchars($r['name'] ?? '') . '</td>'
        . '<td>' . htmlspecialchars($r['position'] ?? '') . '</td>'
        . '<td>' . htmlspecialchars($r['employee_type'] ?? '') . '</td>'
        . '<td>' . htmlspecialchars($r['status'] ?? '') . '</td>'
        //. '<td>' . htmlspecialchars($r['title'] ?? '') . '</td>'
        //. '<td>' . htmlspecialchars($r['title_ld'] ?? '') . '</td>'
        //. '<td>' . htmlspecialchars($r['date_from'] ?? '') . '</td>'
        //. '<td>' . htmlspecialchars($r['date_to'] ?? '') . '</td>'
        //. '<td>' . htmlspecialchars($r['type'] ?? '') . '</td>'
       // . '<td>' . htmlspecialchars($r['level'] ?? '') . '</td>'
        . '<td>' .  $row56['leveloftraining']?? null . '</td>'
        . '</tr>';
}
if (!$rows) {
    $html .= '<tr><td colspan="12" style="text-align:center;color:#777;">No records found.</td></tr>';
}
$html .= '</tbody></table>';

$dompdf = new Dompdf();
$dompdf->setPaper('A4', 'landscape');
$dompdf->loadHtml($html);
$dompdf->render();
$dompdf->stream('personnel_report.pdf', ['Attachment' => true]);
exit;
