File "read.php"
Full path: /home2/santhos2/public_html/bharath-coe.firstmatrix.in.net/api/staff/read.php
File size: 7.44 KiB (7622 bytes)
MIME-type: text/x-php
Charset: utf-8
<?php
require_once '../../config/config.php';
header('Content-Type: application/json');
// Enable error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);
try {
$limit = isset($_GET['length']) ? (int)$_GET['length'] : 10;
$offset = isset($_GET['start']) ? (int)$_GET['start'] : 0;
$orderColumnIndex = isset($_GET['order'][0]['column']) ? (int)$_GET['order'][0]['column'] : 0;
$orderDir = isset($_GET['order'][0]['dir']) && in_array(strtolower($_GET['order'][0]['dir']), ['asc','desc']) ? $_GET['order'][0]['dir'] : 'asc';
$searchValue = isset($_GET['search']['value']) ? trim($_GET['search']['value']) : '';
$columns = ['staff_id','staff_code','first_name','last_name','college_name','department_name','email','phone','roles','status'];
$orderBy = isset($columns[$orderColumnIndex]) ? $columns[$orderColumnIndex] : 'staff_id';
// Check if department_id column exists (only once)
$check_dept_column = $pdo->query("SHOW COLUMNS FROM staff LIKE 'department_id'");
$has_department_column = $check_dept_column->rowCount() > 0;
$bindings = [];
$conditions = [];
// Global search from DataTables
if ($searchValue !== '') {
$conditions[] = '(st.staff_code LIKE :q OR st.first_name LIKE :q OR st.last_name LIKE :q OR st.email LIKE :q)';
$bindings[':q'] = "%$searchValue%";
}
// Custom filters
$filterCollege = isset($_GET['filter_college']) ? trim($_GET['filter_college']) : '';
$filterDepartment = isset($_GET['filter_department']) ? trim($_GET['filter_department']) : '';
$filterRole = isset($_GET['filter_role']) ? trim($_GET['filter_role']) : '';
$filterStatus = isset($_GET['filter_status']) ? trim($_GET['filter_status']) : '';
if ($filterCollege !== '') {
$conditions[] = 'st.college_id = :fco';
$bindings[':fco'] = $filterCollege;
}
if ($filterDepartment !== '' && $has_department_column) {
$conditions[] = 'st.department_id = :fdept';
$bindings[':fdept'] = $filterDepartment;
}
if ($filterRole !== '') {
$conditions[] = 'EXISTS (SELECT 1 FROM staff_roles sr2 JOIN staff_role_types srt2 ON sr2.role_id = srt2.role_id WHERE sr2.staff_id = st.staff_id AND srt2.role_name = :fr)';
$bindings[':fr'] = $filterRole;
}
if ($filterStatus !== '') {
$conditions[] = 'st.status = :fst';
$bindings[':fst'] = $filterStatus;
}
$where = count($conditions) ? ('WHERE ' . implode(' AND ', $conditions)) : '';
// Check external_college_name support
$check_ext_col = $pdo->query("SHOW COLUMNS FROM staff LIKE 'external_college_name'");
$has_external_college = $check_ext_col->rowCount() > 0;
$collegeSelect = $has_external_college
? "COALESCE(st.external_college_name, co.college_name)"
: "co.college_name";
if ($has_department_column) {
$sql = "SELECT st.staff_id, st.staff_code, st.first_name, st.last_name, st.email, st.phone, st.status,
$collegeSelect AS college_name, dept.department_name,
GROUP_CONCAT(srt.role_name SEPARATOR ', ') as roles
FROM staff st
LEFT JOIN colleges co ON co.college_id = st.college_id
LEFT JOIN departments dept ON dept.department_id = st.department_id
LEFT JOIN staff_roles sr ON st.staff_id = sr.staff_id
LEFT JOIN staff_role_types srt ON sr.role_id = srt.role_id
$where
GROUP BY st.staff_id
ORDER BY $orderBy $orderDir LIMIT :limit OFFSET :offset";
} else {
// Fallback query without department_id
$sql = "SELECT st.staff_id, st.staff_code, st.first_name, st.last_name, st.email, st.phone, st.status,
$collegeSelect AS college_name, NULL as department_name,
GROUP_CONCAT(srt.role_name SEPARATOR ', ') as roles
FROM staff st
LEFT JOIN colleges co ON co.college_id = st.college_id
LEFT JOIN staff_roles sr ON st.staff_id = sr.staff_id
LEFT JOIN staff_role_types srt ON sr.role_id = srt.role_id
$where
GROUP BY st.staff_id
ORDER BY $orderBy $orderDir LIMIT :limit OFFSET :offset";
}
$stmt=$pdo->prepare($sql);
foreach($bindings as $k=>$v){ $stmt->bindValue($k,$v,PDO::PARAM_STR);} $stmt->bindValue(':limit',$limit,PDO::PARAM_INT); $stmt->bindValue(':offset',$offset,PDO::PARAM_INT); $stmt->execute();
$data=$stmt->fetchAll(PDO::FETCH_ASSOC);
$total=(int)$pdo->query('SELECT COUNT(*) FROM staff')->fetchColumn();
if ($where){
if ($has_department_column) {
$stmtF=$pdo->prepare("SELECT COUNT(DISTINCT st.staff_id) FROM staff st
LEFT JOIN colleges co ON co.college_id = st.college_id
LEFT JOIN departments dept ON dept.department_id = st.department_id
LEFT JOIN staff_roles sr ON st.staff_id = sr.staff_id
LEFT JOIN staff_role_types srt ON sr.role_id = srt.role_id
$where");
} else {
$stmtF=$pdo->prepare("SELECT COUNT(DISTINCT st.staff_id) FROM staff st
LEFT JOIN colleges co ON co.college_id = st.college_id
LEFT JOIN staff_roles sr ON st.staff_id = sr.staff_id
LEFT JOIN staff_role_types srt ON sr.role_id = srt.role_id
$where");
}
foreach($bindings as $k=>$v){ $stmtF->bindValue($k,$v,PDO::PARAM_STR);}
$stmtF->execute();
$filtered=(int)$stmtF->fetchColumn();
} else {
$filtered=$total;
}
// Debug information (remove in production)
$debug_info = [
'filters' => [
'college' => $filterCollege,
'department' => $filterDepartment,
'role' => $filterRole,
'status' => $filterStatus
],
'has_department_column' => $has_department_column,
'conditions_count' => count($conditions),
'where_clause' => $where
];
echo json_encode([
'draw' => (int)($_GET['draw'] ?? 0),
'recordsTotal' => $total,
'recordsFiltered' => $filtered,
'data' => $data,
'debug' => $debug_info
]);
} catch (PDOException $e) {
// Handle database errors
http_response_code(500);
echo json_encode([
'error' => 'Database error: ' . $e->getMessage(),
'draw' => (int)($_GET['draw'] ?? 0),
'recordsTotal' => 0,
'recordsFiltered' => 0,
'data' => []
]);
error_log("Database error in staff read API: " . $e->getMessage());
} catch (Exception $e) {
// Handle general errors
http_response_code(500);
echo json_encode([
'error' => 'An unexpected error occurred: ' . $e->getMessage(),
'draw' => (int)($_GET['draw'] ?? 0),
'recordsTotal' => 0,
'recordsFiltered' => 0,
'data' => []
]);
error_log("Error in staff read API: " . $e->getMessage());
} catch (Throwable $e) {
// Handle fatal errors
http_response_code(500);
echo json_encode([
'error' => 'Fatal error: ' . $e->getMessage(),
'draw' => (int)($_GET['draw'] ?? 0),
'recordsTotal' => 0,
'recordsFiltered' => 0,
'data' => []
]);
error_log("Fatal error in staff read API: " . $e->getMessage());
}
?>