|
Server : LiteSpeed System : Linux terra.hostitbro.com 5.14.0-611.54.3.el9_7.x86_64 #1 SMP PREEMPT_DYNAMIC Thu May 7 16:31:24 EDT 2026 x86_64 User : outerorb ( 1091) PHP Version : 8.1.34 Disable Function : mail Directory : /home2/outerorb/emp.outerorbittech.in/includes/ | |
|
Path: /home2/outerorb/emp.outerorbittech.in/includes/helpers.php
Size: 136.28 KB
Permissions: 0666
<?php
// Create and use local session directory to avoid XAMPP permissions issues
$sessionDir = __DIR__ . '/../.sessions';
if (!is_dir($sessionDir)) {
@mkdir($sessionDir, 0777, true);
}
if (is_dir($sessionDir)) {
@ini_set('session.save_path', $sessionDir);
}
if (session_status() === PHP_SESSION_NONE) {
session_start();
}
/**
* Initialize security and performance features on first request
*/
function initialize_security_features(): void
{
// Only initialize once per request
static $initialized = false;
if ($initialized) {
return;
}
$initialized = true;
try {
// Configure session security settings
configure_session_security();
// Ensure table structures exist
ensure_leaves_table();
// Ensure migration tables exist
ensure_migration_table();
// Create database performance indexes
create_database_indexes();
// Ensure 2FA tables exist
ensure_2fa_tables();
} catch (Exception $e) {
// Silently fail - don't break application if initialization fails
error_log('Security initialization error: ' . $e->getMessage());
}
}
// Stash uploaded files in-session so users don't need to re-upload after a validation or CSRF error.
function staged_uploads(): array
{
return $_SESSION['staged_uploads'] ?? [];
}
function remember_upload(string $field, string $path): void
{
$_SESSION['staged_uploads'][$field] = $path;
}
function clear_staged_uploads(?array $fields = null): void
{
if ($fields === null) {
unset($_SESSION['staged_uploads']);
return;
}
foreach ($fields as $field) {
unset($_SESSION['staged_uploads'][$field]);
}
}
function app_config(): array
{
static $config;
if (!$config) {
$configPath = __DIR__ . '/../config/config.php';
if (!file_exists($configPath)) {
throw new RuntimeException('Missing config file.');
}
$config = require $configPath;
}
return $config;
}
function db(): PDO
{
static $pdo;
if ($pdo) {
return $pdo;
}
$config = app_config();
$db = $config['db'];
$dsn = sprintf('mysql:host=%s;dbname=%s;charset=%s', $db['host'], $db['name'], $db['charset']);
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new PDO($dsn, $db['user'], $db['pass'], $options);
// Normalise sql_mode so the app behaves the same on MySQL 5.7 and 8.0
// (removes ONLY_FULL_GROUP_BY which differs between hosting and Laragon).
$pdo->exec("SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'");
return $pdo;
}
/**
* Detect the real MIME type of a file.
* Uses finfo if the extension is enabled, falls back to mime_content_type(),
* and finally to an extension-based map — so it works on any PHP install.
*/
function detect_mime_type(string $filePath, string $originalName = ''): string
{
if (function_exists('finfo_open')) {
$finfo = finfo_open(FILEINFO_MIME_TYPE);
$mime = finfo_file($finfo, $filePath);
finfo_close($finfo);
if ($mime) {
return $mime;
}
}
if (function_exists('mime_content_type')) {
$mime = mime_content_type($filePath);
if ($mime) {
return $mime;
}
}
// Extension-based fallback: use the original filename when available,
// because the tmp upload path has no meaningful extension.
$ext = strtolower(pathinfo($originalName ?: $filePath, PATHINFO_EXTENSION));
$map = [
'pdf' => 'application/pdf',
'jpg' => 'image/jpeg',
'jpeg' => 'image/jpeg',
'jfif' => 'image/jpeg',
'jpe' => 'image/jpeg',
'png' => 'image/png',
'gif' => 'image/gif',
'webp' => 'image/webp',
'csv' => 'text/csv',
'xls' => 'application/vnd.ms-excel',
'xlsx' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
'doc' => 'application/msword',
'docx' => 'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
];
return $map[$ext] ?? 'application/octet-stream';
}
function sanitize_text(string $value): string
{
return trim(strip_tags($value));
}
// Convert various common date formats to YYYY-MM-DD; return null if invalid
function normalize_date(string $value): ?string
{
$value = trim($value);
if ($value === '') {
return null;
}
$formats = ['Y-m-d', 'd-m-Y', 'd/m/Y', 'm-d-Y', 'm/d/Y'];
foreach ($formats as $format) {
$dt = DateTime::createFromFormat($format, $value);
if ($dt && $dt->format($format) === $value) {
return $dt->format('Y-m-d');
}
}
return null;
}
function validate_required(array $fields, array $input): array
{
$errors = [];
foreach ($fields as $field => $label) {
if (!isset($input[$field]) || sanitize_text($input[$field]) === '') {
$errors[$field] = "$label is required";
}
}
return $errors;
}
function ensure_upload_dir(): void
{
$config = app_config();
$dir = $config['uploads']['dir'];
if (!is_dir($dir)) {
mkdir($dir, 0700, true);
}
}
/**
* Build a filesystem-safe prefix from a candidate's name and phone number.
* Format: firstname-lastname_phone (e.g. john-doe_9876543210)
* Only lowercase alphanumeric characters and hyphens are kept; spaces become hyphens.
*/
function make_upload_prefix(string $firstName, string $lastName, string $phone): string
{
$slug = function (string $s): string {
$s = mb_strtolower(trim($s));
$s = preg_replace('/[^a-z0-9]+/', '-', $s);
return trim($s, '-');
};
$first = $slug($firstName);
$last = $slug($lastName);
$ph = preg_replace('/[^0-9]/', '', $phone);
// Limit each part so the full filename stays reasonable
$first = substr($first, 0, 20);
$last = substr($last, 0, 20);
$part = trim($first . ($last ? '-' . $last : ''), '-');
return $part . ($ph ? '_' . $ph : '');
}
function handle_upload(string $field, array $allowedMimes, int $maxSize, string $prefix = ''): array
{
ensure_upload_dir();
$config = app_config();
$dir = rtrim($config['uploads']['dir'], '/\\');
if (!isset($_FILES[$field]) || $_FILES[$field]['error'] === UPLOAD_ERR_NO_FILE) {
return ['error' => 'File is required'];
}
$file = $_FILES[$field];
if ($file['error'] !== UPLOAD_ERR_OK) {
$uploadErrors = [
UPLOAD_ERR_INI_SIZE => 'File exceeds server upload_max_filesize limit',
UPLOAD_ERR_FORM_SIZE => 'File exceeds the maximum size allowed by this form',
UPLOAD_ERR_PARTIAL => 'File was only partially uploaded. Please retry',
UPLOAD_ERR_NO_TMP_DIR => 'Server is missing a temporary upload folder',
UPLOAD_ERR_CANT_WRITE => 'Server failed to write uploaded file to disk',
UPLOAD_ERR_EXTENSION => 'Upload blocked by a server extension',
];
return ['error' => $uploadErrors[$file['error']] ?? 'Upload failed, please retry'];
}
if ($file['size'] > $maxSize) {
return ['error' => 'File is too large'];
}
$mime = detect_mime_type($file['tmp_name'], $file['name']);
if (!in_array($mime, $allowedMimes, true)) {
return ['error' => 'Invalid file type'];
}
$extension = pathinfo($file['name'], PATHINFO_EXTENSION);
$random = bin2hex(random_bytes(8)); // 16 hex chars — enough uniqueness
$baseName = ($prefix !== '' ? $prefix . '_' : '') . $random;
$safeName = $baseName . ($extension ? ".{$extension}" : '');
$dest = $dir . '/' . $safeName;
if (!move_uploaded_file($file['tmp_name'], $dest)) {
return ['error' => 'Could not save file'];
}
// Normalize the path so stored paths are always clean absolute paths
// (avoids storing config/../uploads/ patterns that can confuse some servers).
$normalized = realpath($dest);
return ['path' => $normalized ?: $dest];
}
/**
* Resolve a stored file path to an actual path that exists on this server.
*
* Paths are stored as absolute paths at upload time. When the database is
* imported to a different machine (e.g. live → localhost) those absolute paths
* no longer exist. This helper first tries the stored path as-is, then falls
* back to looking for a file with the same basename inside the configured
* uploads directory, so the project works on any server without touching the DB.
*/
function resolve_upload_path(?string $storedPath): ?string
{
$stored = trim((string) $storedPath);
if ($stored === '') {
return null;
}
// Fast path: file exists exactly where stored (works on the original server).
if (file_exists($stored)) {
return $stored;
}
// Fallback: locate by filename inside the current uploads directory.
$config = app_config();
$uploadDir = rtrim($config['uploads']['dir'], '/\\');
$candidate = $uploadDir . DIRECTORY_SEPARATOR . basename($stored);
if (file_exists($candidate)) {
return $candidate;
}
return null;
}
// Decode aadhaar_path supporting legacy single path strings and newer JSON maps.
function parse_aadhaar_paths(?string $value): array
{
$value = trim((string) $value);
if ($value === '') {
return ['single' => null, 'front' => null, 'back' => null];
}
$decoded = json_decode($value, true);
if (is_array($decoded)) {
return [
'single' => isset($decoded['single']) && is_string($decoded['single']) ? trim($decoded['single']) : null,
'front' => isset($decoded['front']) && is_string($decoded['front']) ? trim($decoded['front']) : null,
'back' => isset($decoded['back']) && is_string($decoded['back']) ? trim($decoded['back']) : null,
];
}
// Legacy storage: plain path string
return ['single' => $value, 'front' => null, 'back' => null];
}
// Compose aadhaar_path string without schema changes; prefers JSON when multiple files are present.
function compose_aadhaar_value(array $paths): string
{
$single = isset($paths['single']) ? trim((string) $paths['single']) : '';
$front = isset($paths['front']) ? trim((string) $paths['front']) : '';
$back = isset($paths['back']) ? trim((string) $paths['back']) : '';
// If both sides are present (or either side with/without legacy), store JSON for clarity.
if ($front !== '' || $back !== '') {
$payload = ['front' => $front ?: null, 'back' => $back ?: null];
if ($single !== '') {
$payload['single'] = $single;
}
return json_encode($payload, JSON_UNESCAPED_SLASHES);
}
return $single;
}
function aadhaar_primary_path(array $paths): ?string
{
foreach (['front', 'back', 'single'] as $key) {
if (!empty($paths[$key])) {
return $paths[$key];
}
}
return null;
}
function pending_documents_for_employee(array $emp): array
{
$labels = [
'tenth_marksheet' => '10th Marksheet',
'twelfth_marksheet' => '12th Marksheet',
'aadhaar_front' => 'Aadhaar Front',
'aadhaar_back' => 'Aadhaar Back',
'pan' => 'PAN Document',
'qualification' => 'Graduation',
'bank_proof' => 'Bank Proof',
'photo' => 'Passport Photo',
];
$missing = [];
$aadhaarPaths = parse_aadhaar_paths($emp['aadhaar_path'] ?? '');
if (!resolve_upload_path($aadhaarPaths['front'] ?? null)) {
$missing[] = $labels['aadhaar_front'];
}
if (!resolve_upload_path($aadhaarPaths['back'] ?? null)) {
$missing[] = $labels['aadhaar_back'];
}
$fileChecks = [
'tenth_marksheet_path' => 'tenth_marksheet',
'twelfth_marksheet_path' => 'twelfth_marksheet',
'pan_path' => 'pan',
'bank_proof_path' => 'bank_proof',
'photo_path' => 'photo',
];
foreach ($fileChecks as $column => $key) {
if (!resolve_upload_path($emp[$column] ?? null)) {
$missing[] = $labels[$key];
}
}
return $missing;
}
function csrf_token(): string
{
if (empty($_SESSION['csrf_token'])) {
$_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}
return $_SESSION['csrf_token'];
}
function verify_csrf(string $token): bool
{
return isset($_SESSION['csrf_token']) && hash_equals($_SESSION['csrf_token'], $token);
}
// ---------------------------------------------------------------------------
// Employee auth helpers
// ---------------------------------------------------------------------------
function ensure_employee_auth_table(): void
{
static $checked = false;
if ($checked) return;
$pdo = db();
$pdo->exec("CREATE TABLE IF NOT EXISTS employee_auth (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id INT UNSIGNED NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
must_change_password TINYINT(1) NOT NULL DEFAULT 1,
is_active TINYINT(1) NOT NULL DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT fk_emp_auth_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$checked = true;
}
function authenticate_employee(string $phone, string $password): ?array
{
ensure_employee_auth_table();
ensure_employees_table();
$pdo = db();
$stmt = $pdo->prepare(
'SELECT e.id, e.first_name, e.last_name, e.phone, e.department, e.employee_status, e.approval_status,
COALESCE(e.two_factor_enabled, 0) AS two_factor_enabled, e.two_factor_secret,
ea.password_hash, ea.must_change_password, ea.is_active
FROM employees e
LEFT JOIN employee_auth ea ON ea.employee_id = e.id
WHERE e.phone = ? AND e.deleted_at IS NULL
LIMIT 1'
);
$stmt->execute([$phone]);
$row = $stmt->fetch();
if (!$row) {
log_login_attempt($phone, 'invalid_credentials');
return null;
}
// Employee exists but has no auth record yet — auto-provision on first login
// only when the password matches the phone (the HR-assigned default credential).
if ($row['password_hash'] === null) {
if (
$row['employee_status'] === 'Active' &&
($row['approval_status'] ?? 'pending') === 'approved' &&
$password === $phone
) {
$hash = password_hash($password, PASSWORD_DEFAULT);
$pdo->prepare(
'INSERT INTO employee_auth (employee_id, password_hash, must_change_password, is_active)
VALUES (?, ?, 1, 1)
ON DUPLICATE KEY UPDATE password_hash = VALUES(password_hash), must_change_password = 1, is_active = 1'
)->execute([$row['id'], $hash]);
$row['password_hash'] = $hash;
$row['must_change_password'] = 1;
$row['is_active'] = 1;
} else {
log_login_attempt($phone, 'invalid_credentials', $row['id']);
return null;
}
}
if (!$row['is_active']) {
log_login_attempt($phone, 'account_disabled', $row['id']);
return null;
}
if ($row['employee_status'] !== 'Active') {
log_login_attempt($phone, 'not_active', $row['id']);
return null;
}
if (($row['approval_status'] ?? 'pending') !== 'approved') {
log_login_attempt($phone, 'not_approved', $row['id']);
return null;
}
if (!password_verify($password, $row['password_hash'])) {
log_login_attempt($phone, 'invalid_credentials', $row['id']);
return null;
}
// Log successful login
log_login_attempt($phone, 'success', $row['id']);
return $row;
}
function is_employee(): bool
{
return !empty($_SESSION['employee_logged_in']) && !empty($_SESSION['employee']);
}
function require_employee(): void
{
if (!is_employee()) {
header('Location: ' . base_url() . 'index.php');
exit;
}
}
function current_employee(): ?array
{
return $_SESSION['employee'] ?? null;
}
/**
* Derive the base URL of the project dynamically so it works on any subdirectory
* (e.g. localhost/hr/ or erp.outerorbittech.com/).
*/
function base_url(): string
{
$script = $_SERVER['SCRIPT_NAME'] ?? '';
// Walk up until we reach the project root (identified by having includes/ sibling)
$dir = dirname($script);
// Normalise: strip /admin, /employee sub-dirs to reach project root
$dir = preg_replace('#/(admin|employee)$#', '', $dir);
$dir = rtrim($dir, '/') . '/';
return $dir;
}
/**
* Detect whether the current request is effectively HTTPS.
* Supports common reverse-proxy headers used on shared hosting/CDN setups.
*/
function request_is_https(): bool
{
if (!empty($_SERVER['HTTPS']) && strtolower((string) $_SERVER['HTTPS']) !== 'off') {
return true;
}
if (isset($_SERVER['SERVER_PORT']) && (int) $_SERVER['SERVER_PORT'] === 443) {
return true;
}
$forwardedProto = strtolower((string) ($_SERVER['HTTP_X_FORWARDED_PROTO'] ?? ''));
if ($forwardedProto !== '' && strpos($forwardedProto, 'https') !== false) {
return true;
}
// Cloudflare forwards scheme details in JSON.
$cfVisitor = (string) ($_SERVER['HTTP_CF_VISITOR'] ?? '');
if ($cfVisitor !== '' && stripos($cfVisitor, '"https"') !== false) {
return true;
}
return false;
}
/**
* Return the request origin (scheme + host), respecting reverse-proxy HTTPS headers.
*/
function request_origin(): string
{
$scheme = request_is_https() ? 'https' : 'http';
$host = (string) ($_SERVER['HTTP_HOST'] ?? 'localhost');
return $scheme . '://' . $host;
}
/**
* Build an absolute URL for a project-relative path.
*/
function app_url(string $path = ''): string
{
$base = request_origin() . base_url();
if ($path === '') {
return $base;
}
return $base . ltrim($path, '/');
}
// ---------------------------------------------------------------------------
function is_admin(): bool
{
return !empty($_SESSION['admin_logged_in']) && !empty($_SESSION['admin']);
}
function require_admin(): void
{
if (!is_admin()) {
header('Location: login.php');
exit;
}
}
function require_super_admin(): void
{
if (!is_admin() || !is_super_admin()) {
redirect_with_message('dashboard.php', 'Access denied.', 'error');
}
}
function is_super_admin(): bool
{
return ($_SESSION['admin']['role'] ?? '') === 'super';
}
function current_admin(): ?array
{
return $_SESSION['admin'] ?? null;
}
function ensure_admin_users_table(): void
{
$pdo = db();
$pdo->exec("CREATE TABLE IF NOT EXISTS admin_users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(190) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
password_plain VARCHAR(255) NULL,
role ENUM('super','admin') NOT NULL DEFAULT 'admin',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
// Ensure role column exists for older installs
try {
$cols = $pdo->query('SHOW COLUMNS FROM admin_users LIKE "role"')->fetch();
if (!$cols) {
$pdo->exec("ALTER TABLE admin_users ADD COLUMN role ENUM('super','admin') NOT NULL DEFAULT 'admin' AFTER password_hash");
}
} catch (Throwable $e) {
// ignore
}
// Ensure password_hash column exists for legacy installs that might have used a different column name
try {
$cols = $pdo->query('SHOW COLUMNS FROM admin_users LIKE "password_hash"')->fetch();
if (!$cols) {
$pdo->exec("ALTER TABLE admin_users ADD COLUMN password_hash VARCHAR(255) NULL AFTER username");
}
} catch (Throwable $e) {
// ignore
}
// Ensure password_plain column exists for development purposes (stores plain text for reference)
try {
$cols = $pdo->query('SHOW COLUMNS FROM admin_users LIKE "password_plain"')->fetch();
if (!$cols) {
$pdo->exec("ALTER TABLE admin_users ADD COLUMN password_plain VARCHAR(255) NULL AFTER password_hash");
}
} catch (Throwable $e) {
// ignore
}
// Ensure 2FA columns exist on admin_users so authenticate_admin() can return them
try {
$cols = $pdo->query('SHOW COLUMNS FROM admin_users LIKE "two_factor_enabled"')->fetch();
if (!$cols) {
$pdo->exec("ALTER TABLE admin_users ADD COLUMN two_factor_enabled TINYINT(1) NOT NULL DEFAULT 0");
}
} catch (Throwable $e) {
// ignore
}
try {
$cols = $pdo->query('SHOW COLUMNS FROM admin_users LIKE "two_factor_secret"')->fetch();
if (!$cols) {
$pdo->exec("ALTER TABLE admin_users ADD COLUMN two_factor_secret VARCHAR(255) NULL");
}
} catch (Throwable $e) {
// ignore
}
}
function ensure_admin_departments_table(): void
{
$pdo = db();
$pdo->exec("CREATE TABLE IF NOT EXISTS admin_departments (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
admin_user_id INT UNSIGNED NOT NULL,
department VARCHAR(120) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_admin_dept (admin_user_id, department),
CONSTRAINT fk_admin_dept_user FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
}
function fetch_admin_departments(int $adminId): array
{
ensure_admin_departments_table();
$pdo = db();
$stmt = $pdo->prepare('SELECT department FROM admin_departments WHERE admin_user_id = ? ORDER BY department');
$stmt->execute([$adminId]);
return $stmt->fetchAll(PDO::FETCH_COLUMN) ?: [];
}
function set_admin_departments(int $adminId, array $departments): void
{
ensure_admin_departments_table();
$pdo = db();
$departments = array_values(array_unique(array_filter(array_map('trim', $departments))));
$pdo->beginTransaction();
$pdo->prepare('DELETE FROM admin_departments WHERE admin_user_id = ?')->execute([$adminId]);
if (!empty($departments)) {
$insert = $pdo->prepare('INSERT INTO admin_departments (admin_user_id, department) VALUES (?, ?)');
foreach ($departments as $dept) {
$insert->execute([$adminId, $dept]);
}
}
$pdo->commit();
}
function authenticate_admin(string $username, string $password): ?array
{
ensure_admin_users_table();
// First try database-backed admin users
try {
$pdo = db();
$legacyPasswordExists = false;
try {
$legacyPasswordExists = (bool) $pdo->query('SHOW COLUMNS FROM admin_users LIKE "password"')->fetch();
} catch (Throwable $e) {
$legacyPasswordExists = false;
}
$select = 'SELECT id, username, password_hash, role, COALESCE(two_factor_enabled, 0) AS two_factor_enabled, two_factor_secret';
if ($legacyPasswordExists) {
$select .= ', password AS legacy_password';
}
$select .= ' FROM admin_users WHERE username = ? LIMIT 1';
$stmt = $pdo->prepare($select);
$stmt->execute([$username]);
$row = $stmt->fetch();
if ($row && !empty($row['password_hash']) && password_verify($password, $row['password_hash'])) {
$departments = $row['role'] === 'super' ? [] : fetch_admin_departments((int) $row['id']);
return [
'id' => (int) $row['id'],
'username' => $row['username'],
'role' => $row['role'] ?? 'admin',
'departments' => $departments,
'two_factor_enabled' => (bool) ($row['two_factor_enabled'] ?? false),
'two_factor_secret' => $row['two_factor_secret'] ?? null,
];
}
// Legacy fallback: allow plaintext or legacy-hash column named `password`; rehash into password_hash on success
if ($row && $legacyPasswordExists && !empty($row['legacy_password'])) {
$legacyValue = $row['legacy_password'];
$legacyMatch = hash_equals((string) $legacyValue, (string) $password) || password_verify($password, (string) $legacyValue);
if ($legacyMatch) {
$hash = password_hash($password, PASSWORD_DEFAULT);
$pdo->prepare('UPDATE admin_users SET password_hash = ? WHERE id = ?')->execute([$hash, (int) $row['id']]);
$departments = $row['role'] === 'super' ? [] : fetch_admin_departments((int) $row['id']);
return [
'id' => (int) $row['id'],
'username' => $row['username'],
'role' => $row['role'] ?? 'admin',
'departments' => $departments,
'two_factor_enabled' => (bool) ($row['two_factor_enabled'] ?? false),
'two_factor_secret' => $row['two_factor_secret'] ?? null,
];
}
}
} catch (Throwable $e) {
// If table/query fails, fall back to config-based login
}
// Fallback to config-defined credentials
$config = app_config();
$adminUser = $config['admin']['username'];
$hash = $config['admin']['password_hash'];
$plain = $config['admin']['password_plain'];
if ($hash && hash_equals($adminUser, $username) && password_verify($password, $hash)) {
return ['id' => 0, 'username' => $username, 'role' => 'super', 'departments' => [], 'two_factor_enabled' => false, 'two_factor_secret' => null];
}
if (!$hash && hash_equals($adminUser, $username) && hash_equals($plain, $password)) {
return ['id' => 0, 'username' => $username, 'role' => 'super', 'departments' => [], 'two_factor_enabled' => false, 'two_factor_secret' => null];
}
return null;
}
function redirect_with_message(string $location, string $message, string $type = 'success', array $extra = []): void
{
$_SESSION['flash'] = array_merge(['message' => $message, 'type' => $type], $extra);
header("Location: {$location}");
exit;
}
function allowed_departments_for_admin(): array
{
$admin = current_admin();
if (!$admin || ($admin['role'] ?? '') === 'super') {
return [];
}
return $admin['departments'] ?? [];
}
function admin_can_access_department(?string $department): bool
{
$allowed = allowed_departments_for_admin();
if (empty($allowed)) {
return true;
}
$department = trim((string) $department);
return in_array($department, $allowed, true);
}
function enforce_department_access(array $employee): void
{
if (!admin_can_access_department($employee['department'] ?? null)) {
redirect_with_message('dashboard.php', 'Access denied for this department.', 'error');
}
}
function apply_department_filter(array &$where, array &$params, string $alias = 'e'): void
{
$allowed = allowed_departments_for_admin();
if (!empty($allowed)) {
$placeholders = [];
foreach ($allowed as $idx => $dept) {
$ph = ':dept_' . $idx;
$placeholders[] = $ph;
$params[$ph] = $dept;
}
$where[] = "$alias.department IN (" . implode(',', $placeholders) . ")";
}
}
function flash(): ?array
{
if (!empty($_SESSION['flash'])) {
$flash = $_SESSION['flash'];
unset($_SESSION['flash']);
return $flash;
}
return null;
}
function list_departments(): array
{
static $cache;
if ($cache !== null) {
return $cache;
}
ensure_departments_table();
try {
$pdo = db();
$stmt = $pdo->query('SELECT name FROM departments ORDER BY name ASC');
$cache = $stmt->fetchAll(PDO::FETCH_COLUMN) ?: [];
return $cache;
} catch (PDOException $e) {
// If creation failed or other error, return empty list but do not break the page
return $cache = [];
}
}
function ensure_designations_table(): void
{
static $checked = false;
if ($checked) {
return;
}
$pdo = db();
try {
$pdo->query('SELECT 1 FROM designations LIMIT 1');
$checked = true;
return;
} catch (PDOException $e) {
if ($e->getCode() !== '42S02') {
throw $e;
}
}
$sql = "CREATE TABLE IF NOT EXISTS designations (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(150) NOT NULL UNIQUE,
status ENUM('active','inactive') NOT NULL DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$pdo->exec($sql);
$checked = true;
}
function ensure_document_requests_table(): void
{
static $checked = false;
if ($checked) {
return;
}
$pdo = db();
try {
$pdo->query('SELECT 1 FROM document_requests LIMIT 1');
$checked = true;
return;
} catch (PDOException $e) {
if ($e->getCode() !== '42S02') {
throw $e;
}
}
$sql = "CREATE TABLE IF NOT EXISTS document_requests (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id INT UNSIGNED NOT NULL,
request_code VARCHAR(32) NOT NULL UNIQUE,
requested_docs JSON NOT NULL,
admin_note VARCHAR(255) NULL,
status ENUM('pending','completed','cancelled') NOT NULL DEFAULT 'pending',
completed_at TIMESTAMP NULL DEFAULT NULL,
cancelled_at TIMESTAMP NULL DEFAULT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT fk_document_requests_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$pdo->exec($sql);
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_document_requests_status ON document_requests(status)");
$checked = true;
}
function generate_request_code(int $length = 10): string
{
$alphabet = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789';
$max = strlen($alphabet) - 1;
$code = '';
for ($i = 0; $i < $length; $i++) {
$code .= $alphabet[random_int(0, $max)];
}
return $code;
}
// Build a memorable request code using initials + full DOB (ddmmyyyy), e.g., Yash Raj Jaiswal + 2005-02-21 => YRJ21022005.
function human_readable_request_code(array $emp): string
{
$fullName = trim(($emp['first_name'] ?? '') . ' ' . ($emp['last_name'] ?? ''));
$parts = array_values(array_filter(preg_split('/\s+/', strtoupper($fullName))));
$initials = '';
foreach ($parts as $idx => $part) {
if ($idx >= 3) {
break; // cap at 3 initials
}
$initials .= substr($part, 0, 1);
}
if ($initials === '') {
$clean = strtoupper(preg_replace('/[^A-Z]/', '', $fullName));
$initials = $clean !== '' ? substr($clean, 0, 3) : 'EMP';
}
$dobRaw = trim((string) ($emp['dob'] ?? ''));
$dobPart = '00000000';
if ($dobRaw !== '') {
$dt = DateTime::createFromFormat('Y-m-d', $dobRaw) ?: DateTime::createFromFormat('d-m-Y', $dobRaw) ?: null;
if ($dt) {
$dobPart = $dt->format('dmY');
} else {
$digits = preg_replace('/\D/', '', $dobRaw);
if (strlen($digits) >= 8) {
$dobPart = substr($digits, -8);
}
}
}
return $initials . $dobPart;
}
function create_document_request(int $employeeId, array $docs, ?string $note = null): array
{
ensure_employees_table();
ensure_document_requests_table();
$allowed = ['aadhaar_front', 'aadhaar_back', 'pan', 'qualification', 'bank_proof', 'photo'];
$requested = array_values(array_intersect($allowed, array_map('trim', $docs)));
if (empty($requested)) {
throw new InvalidArgumentException('At least one document must be selected.');
}
$pdo = db();
$stmt = $pdo->prepare('SELECT id, first_name, last_name, dob, phone FROM employees WHERE id = ? LIMIT 1');
$stmt->execute([$employeeId]);
$employee = $stmt->fetch();
if (!$employee) {
throw new InvalidArgumentException('Employee not found.');
}
// Try a human-friendly code first (initials + DOB + phone + ID suffix).
$baseCode = human_readable_request_code($employee);
$code = null;
$suffixes = array_merge([''], range('A', 'Z'));
foreach ($suffixes as $suffix) {
$candidate = $baseCode . $suffix;
$check = $pdo->prepare('SELECT id FROM document_requests WHERE request_code = ? LIMIT 1');
$check->execute([$candidate]);
if (!$check->fetch()) {
$code = $candidate;
break;
}
}
// Fallback to random code if all human-friendly variants are taken.
if ($code === null) {
for ($attempt = 0; $attempt < 5; $attempt++) {
$candidate = generate_request_code();
$check = $pdo->prepare('SELECT id FROM document_requests WHERE request_code = ? LIMIT 1');
$check->execute([$candidate]);
if (!$check->fetch()) {
$code = $candidate;
break;
}
}
}
if ($code === null) {
throw new RuntimeException('Could not generate a unique request code.');
}
$stmt = $pdo->prepare('INSERT INTO document_requests (employee_id, request_code, requested_docs, admin_note) VALUES (?, ?, ?, ?)');
$stmt->execute([$employeeId, $code, json_encode($requested, JSON_UNESCAPED_SLASHES), $note]);
return [
'id' => (int) $pdo->lastInsertId(),
'employee_id' => $employeeId,
'request_code' => $code,
'requested_docs' => $requested,
];
}
function get_document_request_by_code(string $code): ?array
{
ensure_document_requests_table();
$pdo = db();
$stmt = $pdo->prepare('SELECT * FROM document_requests WHERE request_code = ? LIMIT 1');
$stmt->execute([strtoupper(trim($code))]);
$row = $stmt->fetch();
return $row ?: null;
}
function update_document_request_status(int $id, string $status): void
{
ensure_document_requests_table();
$allowed = ['pending', 'completed', 'cancelled'];
if (!in_array($status, $allowed, true)) {
throw new InvalidArgumentException('Invalid status.');
}
$pdo = db();
$timestamps = [
'completed' => 'completed_at',
'cancelled' => 'cancelled_at',
];
$tsColumn = $timestamps[$status] ?? null;
$setTs = $tsColumn ? ", {$tsColumn} = NOW()" : '';
$sql = 'UPDATE document_requests SET status = :status, updated_at = NOW()' . $setTs . ' WHERE id = :id';
$stmt = $pdo->prepare($sql);
$stmt->execute([':status' => $status, ':id' => $id]);
}
function list_designations(bool $onlyActive = true): array
{
static $cache = [];
$key = $onlyActive ? 'active' : 'all';
if (array_key_exists($key, $cache)) {
return $cache[$key];
}
ensure_designations_table();
try {
$pdo = db();
$sql = 'SELECT id, name, status FROM designations';
if ($onlyActive) {
$sql .= " WHERE status = 'active'";
}
$sql .= ' ORDER BY name ASC';
$cache[$key] = $pdo->query($sql)->fetchAll() ?: [];
return $cache[$key];
} catch (PDOException $e) {
return $cache[$key] = [];
}
}
function designation_map(bool $onlyActive = false): array
{
$rows = list_designations($onlyActive);
$map = [];
foreach ($rows as $row) {
$map[(int) $row['id']] = $row;
}
return $map;
}
function get_designation_by_id(int $id): ?array
{
ensure_designations_table();
$pdo = db();
$stmt = $pdo->prepare('SELECT id, name, status FROM designations WHERE id = ? LIMIT 1');
$stmt->execute([$id]);
$row = $stmt->fetch();
return $row ?: null;
}
function ensure_departments_table(): void
{
static $checked = false;
if ($checked) {
return;
}
$pdo = db();
try {
$pdo->query('SELECT 1 FROM departments LIMIT 1');
$checked = true;
return;
} catch (PDOException $e) {
if ($e->getCode() !== '42S02') {
throw $e;
}
}
// Create table if missing
$sql = "CREATE TABLE IF NOT EXISTS departments (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(150) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$pdo->exec($sql);
$checked = true;
}
function ensure_employees_table(): void
{
static $checked = false;
if ($checked) {
return;
}
$pdo = db();
try {
$pdo->query('SELECT 1 FROM employees LIMIT 1');
} catch (PDOException $e) {
if ($e->getCode() !== '42S02') {
throw $e;
}
}
$sql = "CREATE TABLE IF NOT EXISTS employees (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
card_number VARCHAR(50) DEFAULT NULL,
mother_name VARCHAR(100) NOT NULL,
email VARCHAR(190) NOT NULL,
phone VARCHAR(30) NOT NULL,
emergency_contact VARCHAR(30) NOT NULL,
emergency_contact_name VARCHAR(120) NOT NULL,
emergency_contact_number VARCHAR(30) NOT NULL,
emergency_contact_relation VARCHAR(40) NOT NULL,
dob DATE NOT NULL,
date_of_joining DATE NOT NULL,
address TEXT NOT NULL,
permanent_address TEXT NOT NULL,
city VARCHAR(120) NOT NULL,
state VARCHAR(120) NOT NULL,
zip VARCHAR(20) NOT NULL,
aadhaar_number VARCHAR(20) NOT NULL,
pan_number VARCHAR(20) NOT NULL,
highest_qualification VARCHAR(150) NOT NULL,
department VARCHAR(120) NOT NULL,
designation_id INT UNSIGNED DEFAULT NULL,
employee_status VARCHAR(40) NOT NULL DEFAULT 'Active',
last_working_date DATE DEFAULT NULL,
reason_for_leaving TEXT DEFAULT NULL,
marital_status VARCHAR(30) NOT NULL,
gender VARCHAR(20) NOT NULL,
identity_mark VARCHAR(190) NOT NULL,
blood_group VARCHAR(10) NOT NULL,
referred_by VARCHAR(30) NOT NULL,
referral_name VARCHAR(150) DEFAULT NULL,
account_number VARCHAR(40) NOT NULL,
ifsc_code VARCHAR(20) NOT NULL,
bank_name VARCHAR(120) NOT NULL,
aadhaar_path VARCHAR(255) NOT NULL,
pan_path VARCHAR(255) NOT NULL,
qualification_path VARCHAR(255) NOT NULL,
tenth_marksheet_path VARCHAR(255) NOT NULL DEFAULT '',
twelfth_marksheet_path VARCHAR(255) NOT NULL DEFAULT '',
bank_proof_path VARCHAR(255) NOT NULL,
photo_path VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
deleted_at DATETIME DEFAULT NULL,
INDEX idx_email (email),
INDEX idx_phone (phone),
INDEX idx_deleted_at (deleted_at),
INDEX idx_designation_id (designation_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$pdo->exec($sql);
ensure_employee_columns($pdo);
$checked = true;
}
// Add any newly introduced columns without dropping existing data
function ensure_employee_columns(PDO $pdo): void
{
$existing = [];
try {
$existing = $pdo->query('SHOW COLUMNS FROM employees')->fetchAll(PDO::FETCH_COLUMN) ?: [];
} catch (PDOException $e) {
return; // table missing or unreadable; creation path handles this
}
$ddl = [
'mother_name' => "ALTER TABLE employees ADD COLUMN mother_name VARCHAR(100) NOT NULL DEFAULT '' AFTER last_name",
'emergency_contact_name' => "ALTER TABLE employees ADD COLUMN emergency_contact_name VARCHAR(120) NOT NULL DEFAULT '' AFTER emergency_contact",
'emergency_contact_number' => "ALTER TABLE employees ADD COLUMN emergency_contact_number VARCHAR(30) NOT NULL DEFAULT '' AFTER emergency_contact_name",
'emergency_contact_relation' => "ALTER TABLE employees ADD COLUMN emergency_contact_relation VARCHAR(40) NOT NULL DEFAULT '' AFTER emergency_contact_number",
'employee_status' => "ALTER TABLE employees ADD COLUMN employee_status VARCHAR(40) NOT NULL DEFAULT 'Active' AFTER department",
'last_working_date' => "ALTER TABLE employees ADD COLUMN last_working_date DATE DEFAULT NULL AFTER employee_status",
'reason_for_leaving' => "ALTER TABLE employees ADD COLUMN reason_for_leaving TEXT DEFAULT NULL AFTER last_working_date",
'referred_by' => "ALTER TABLE employees ADD COLUMN referred_by VARCHAR(30) NOT NULL AFTER blood_group",
'referral_name' => "ALTER TABLE employees ADD COLUMN referral_name VARCHAR(150) DEFAULT NULL AFTER referred_by",
'designation_id' => "ALTER TABLE employees ADD COLUMN designation_id INT UNSIGNED DEFAULT NULL AFTER department",
'card_number' => "ALTER TABLE employees ADD COLUMN card_number VARCHAR(50) DEFAULT NULL AFTER last_name",
'deleted_at' => "ALTER TABLE employees ADD COLUMN deleted_at DATETIME DEFAULT NULL AFTER created_at",
'tenth_marksheet_path' => "ALTER TABLE employees ADD COLUMN tenth_marksheet_path VARCHAR(255) NOT NULL DEFAULT '' AFTER qualification_path",
'twelfth_marksheet_path' => "ALTER TABLE employees ADD COLUMN twelfth_marksheet_path VARCHAR(255) NOT NULL DEFAULT '' AFTER tenth_marksheet_path",
'approval_status' => "ALTER TABLE employees ADD COLUMN approval_status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'approved' AFTER employee_status",
'two_factor_enabled' => "ALTER TABLE employees ADD COLUMN two_factor_enabled TINYINT(1) NOT NULL DEFAULT 0",
'two_factor_secret' => "ALTER TABLE employees ADD COLUMN two_factor_secret VARCHAR(255) NULL",
];
foreach ($ddl as $column => $statement) {
if (!in_array($column, $existing, true)) {
try {
$pdo->exec($statement);
} catch (Throwable $e) {
// Column may have been added by a concurrent request or migration; ignore
}
}
}
// Back-fill: existing records before approval workflow was added are already approved
if (!in_array('approval_status', $existing, true)) {
try {
$pdo->exec("UPDATE employees SET approval_status = 'approved' WHERE approval_status IS NULL OR approval_status = ''");
} catch (Throwable $e) { /* ignore */ }
}
// Ensure index for designation lookup
try {
$indexes = $pdo->query('SHOW INDEX FROM employees')->fetchAll(PDO::FETCH_ASSOC);
$hasDesignationIndex = false;
foreach ($indexes as $idx) {
if (($idx['Column_name'] ?? '') === 'designation_id') {
$hasDesignationIndex = true;
break;
}
}
if (!$hasDesignationIndex) {
$pdo->exec('ALTER TABLE employees ADD INDEX idx_designation_id (designation_id)');
}
// ensure index for card_number to speed lookup by biometric card
$hasCardIndex = false;
foreach ($indexes as $idx) {
if (($idx['Column_name'] ?? '') === 'card_number') { $hasCardIndex = true; break; }
}
if (!$hasCardIndex) {
try { $pdo->exec('ALTER TABLE employees ADD INDEX idx_card_number (card_number)'); } catch (Throwable $e) { /* ignore */ }
}
} catch (PDOException $e) {
// if index check fails, continue without breaking
}
}
// ===========================================================================
// HOLIDAY CALENDAR HELPERS
// ===========================================================================
function ensure_holidays_table(): void
{
static $checked = false;
if ($checked) return;
db()->exec("CREATE TABLE IF NOT EXISTS holidays (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
holiday_date DATE NOT NULL,
year SMALLINT UNSIGNED NOT NULL,
type ENUM('national','optional') NOT NULL DEFAULT 'national',
departments JSON NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uniq_holiday_date_title (holiday_date, title)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$checked = true;
}
function list_holidays(int $year, ?string $department = null): array
{
ensure_holidays_table();
$pdo = db();
$rows = $pdo->prepare('SELECT * FROM holidays WHERE year = ? ORDER BY holiday_date ASC');
$rows->execute([$year]);
$all = $rows->fetchAll();
if ($department === null) return $all;
// Filter: keep national (all-dept) or where departments JSON contains this dept
return array_values(array_filter($all, function ($h) use ($department) {
if ($h['departments'] === null) return true;
$depts = json_decode($h['departments'], true);
return is_array($depts) && in_array($department, $depts, true);
}));
}
function is_holiday(string $date, ?string $department = null): bool
{
$year = (int) date('Y', strtotime($date));
foreach (list_holidays($year, $department) as $h) {
if ($h['holiday_date'] === $date) return true;
}
return false;
}
// ===========================================================================
// ATTENDANCE HELPERS
// ===========================================================================
function ensure_attendance_tables(): void
{
static $checked = false;
if ($checked) return;
$pdo = db();
$pdo->exec("CREATE TABLE IF NOT EXISTS shift_timings (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
department VARCHAR(120) NULL,
shift_name VARCHAR(100) NOT NULL DEFAULT 'General',
start_time TIME NOT NULL DEFAULT '09:00:00',
end_time TIME NOT NULL DEFAULT '18:00:00',
grace_minutes TINYINT UNSIGNED NOT NULL DEFAULT 15,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
// Seed default shift if empty
$has = $pdo->query('SELECT COUNT(*) FROM shift_timings WHERE department IS NULL')->fetchColumn();
if (!$has) {
$pdo->exec("INSERT INTO shift_timings (department, shift_name, start_time, end_time, grace_minutes)
VALUES (NULL, 'General', '09:00:00', '18:00:00', 15)");
}
$pdo->exec("CREATE TABLE IF NOT EXISTS attendance_logs (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id INT UNSIGNED NOT NULL,
attend_date DATE NOT NULL,
status ENUM('present','absent','half_day','wfh','holiday','on_leave') NOT NULL DEFAULT 'present',
punch_in TIME NULL,
punch_out TIME NULL,
is_late TINYINT(1) NOT NULL DEFAULT 0,
notes VARCHAR(500) NULL,
marked_by INT UNSIGNED NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uniq_emp_date (employee_id, attend_date),
CONSTRAINT fk_att_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$checked = true;
}
function get_shift_for_department(?string $department): array
{
ensure_attendance_tables();
$pdo = db();
if ($department !== null) {
$row = $pdo->prepare('SELECT * FROM shift_timings WHERE department = ? LIMIT 1');
$row->execute([$department]);
$r = $row->fetch();
if ($r) return $r;
}
return $pdo->query('SELECT * FROM shift_timings WHERE department IS NULL LIMIT 1')->fetch() ?: [
'start_time' => '09:00:00', 'end_time' => '18:00:00', 'grace_minutes' => 15,
];
}
function get_attendance_month(int $employeeId, int $year, int $month): array
{
ensure_attendance_tables();
$pdo = db();
$stmt = $pdo->prepare(
'SELECT * FROM attendance_logs
WHERE employee_id = ? AND YEAR(attend_date) = ? AND MONTH(attend_date) = ?
ORDER BY attend_date ASC'
);
$stmt->execute([$employeeId, $year, $month]);
$rows = $stmt->fetchAll();
// Key by date string
$map = [];
foreach ($rows as $r) { $map[$r['attend_date']] = $r; }
return $map;
}
function attendance_summary_month(int $employeeId, int $year, int $month): array
{
$logs = get_attendance_month($employeeId, $year, $month);
$counts = ['present' => 0, 'absent' => 0, 'half_day' => 0, 'wfh' => 0, 'holiday' => 0, 'on_leave' => 0, 'late' => 0];
foreach ($logs as $log) {
$s = $log['status'];
if (isset($counts[$s])) $counts[$s]++;
if ($log['is_late']) $counts['late']++;
}
return $counts;
}
// ===========================================================================
// LEAVE MANAGEMENT HELPERS
// ===========================================================================
function ensure_leave_tables(): void
{
static $checked = false;
if ($checked) return;
$pdo = db();
$pdo->exec("CREATE TABLE IF NOT EXISTS leave_types (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(120) NOT NULL UNIQUE,
code VARCHAR(20) NOT NULL UNIQUE,
annual_days TINYINT UNSIGNED NOT NULL DEFAULT 0,
is_paid TINYINT(1) NOT NULL DEFAULT 1,
carry_forward TINYINT(1) NOT NULL DEFAULT 0,
max_carry_days TINYINT UNSIGNED NOT NULL DEFAULT 0,
encashable TINYINT(1) NOT NULL DEFAULT 0,
is_active TINYINT(1) NOT NULL DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
// Seed defaults
$pdo->exec("INSERT IGNORE INTO leave_types (name, code, annual_days, is_paid, carry_forward, max_carry_days, encashable) VALUES
('Casual Leave', 'CL', 12, 1, 0, 0, 0),
('Sick Leave', 'SL', 8, 1, 0, 0, 0),
('Earned Leave', 'EL', 15, 1, 1, 15, 1),
('Unpaid Leave', 'UL', 0, 0, 0, 0, 0),
('Maternity Leave', 'ML', 26, 1, 0, 0, 0),
('Paternity Leave', 'PL', 5, 1, 0, 0, 0)");
$pdo->exec("CREATE TABLE IF NOT EXISTS leave_balances (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id INT UNSIGNED NOT NULL,
leave_type_id INT UNSIGNED NOT NULL,
year SMALLINT UNSIGNED NOT NULL,
allocated_days DECIMAL(5,1) NOT NULL DEFAULT 0,
used_days DECIMAL(5,1) NOT NULL DEFAULT 0,
carried_days DECIMAL(5,1) NOT NULL DEFAULT 0,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uniq_bal (employee_id, leave_type_id, year),
CONSTRAINT fk_lb_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
CONSTRAINT fk_lb_lt FOREIGN KEY (leave_type_id) REFERENCES leave_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$pdo->exec("CREATE TABLE IF NOT EXISTS leave_applications (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id INT UNSIGNED NOT NULL,
leave_type_id INT UNSIGNED NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
total_days DECIMAL(5,1) NOT NULL DEFAULT 1,
reason TEXT NULL,
status ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
admin_notes TEXT NULL,
reviewed_by INT UNSIGNED NULL,
reviewed_at DATETIME NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT fk_la_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
CONSTRAINT fk_la_lt FOREIGN KEY (leave_type_id) REFERENCES leave_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$checked = true;
}
function list_leave_types(bool $onlyActive = true): array
{
ensure_leave_tables();
$pdo = db();
$sql = 'SELECT * FROM leave_types' . ($onlyActive ? " WHERE is_active = 1" : '') . ' ORDER BY name ASC';
return $pdo->query($sql)->fetchAll() ?: [];
}
function get_leave_balance(int $employeeId, int $leaveTypeId, int $year): array
{
ensure_leave_tables();
$pdo = db();
$stmt = $pdo->prepare('SELECT * FROM leave_balances WHERE employee_id = ? AND leave_type_id = ? AND year = ? LIMIT 1');
$stmt->execute([$employeeId, $leaveTypeId, $year]);
return $stmt->fetch() ?: ['allocated_days' => 0, 'used_days' => 0, 'carried_days' => 0];
}
function get_all_leave_balances(int $employeeId, int $year): array
{
ensure_leave_tables();
$pdo = db();
$stmt = $pdo->prepare(
'SELECT lt.id, lt.name, lt.code, lt.annual_days, lt.is_paid,
COALESCE(lb.allocated_days, lt.annual_days) AS allocated_days,
COALESCE(lb.used_days, 0) AS used_days,
COALESCE(lb.carried_days, 0) AS carried_days
FROM leave_types lt
LEFT JOIN leave_balances lb ON lb.leave_type_id = lt.id AND lb.employee_id = ? AND lb.year = ?
WHERE lt.is_active = 1
ORDER BY lt.name ASC'
);
$stmt->execute([$employeeId, $year]);
return $stmt->fetchAll() ?: [];
}
function count_working_days(string $fromDate, string $toDate, ?string $department = null): float
{
$start = new DateTime($fromDate);
$end = new DateTime($toDate);
$days = 0;
$year = (int) $start->format('Y');
$holidays = list_holidays($year, $department);
$holidayDates = array_column($holidays, 'holiday_date');
while ($start <= $end) {
$dow = (int) $start->format('N'); // 1=Mon … 7=Sun
$ds = $start->format('Y-m-d');
if ($dow <= 5 && !in_array($ds, $holidayDates, true)) {
$days++;
}
$start->modify('+1 day');
}
return (float) $days;
}
// ===========================================================================
// PAYROLL HELPERS
// ===========================================================================
function ensure_payroll_tables(): void
{
static $checked = false;
if ($checked) return;
$pdo = db();
$pdo->exec("CREATE TABLE IF NOT EXISTS salary_structures (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id INT UNSIGNED NOT NULL UNIQUE,
basic DECIMAL(12,2) NOT NULL DEFAULT 0,
hra DECIMAL(12,2) NOT NULL DEFAULT 0,
da DECIMAL(12,2) NOT NULL DEFAULT 0,
conveyance DECIMAL(12,2) NOT NULL DEFAULT 0,
special_allowance DECIMAL(12,2) NOT NULL DEFAULT 0,
other_allowance DECIMAL(12,2) NOT NULL DEFAULT 0,
pf_applicable TINYINT(1) NOT NULL DEFAULT 1,
esi_applicable TINYINT(1) NOT NULL DEFAULT 1,
pt_applicable TINYINT(1) NOT NULL DEFAULT 1,
effective_from DATE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT fk_ss_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$pdo->exec("CREATE TABLE IF NOT EXISTS salary_revisions (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id INT UNSIGNED NOT NULL,
basic DECIMAL(12,2) NOT NULL DEFAULT 0,
hra DECIMAL(12,2) NOT NULL DEFAULT 0,
da DECIMAL(12,2) NOT NULL DEFAULT 0,
conveyance DECIMAL(12,2) NOT NULL DEFAULT 0,
special_allowance DECIMAL(12,2) NOT NULL DEFAULT 0,
other_allowance DECIMAL(12,2) NOT NULL DEFAULT 0,
effective_from DATE NOT NULL,
notes TEXT NULL,
created_by INT UNSIGNED NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_sr_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$pdo->exec("CREATE TABLE IF NOT EXISTS payroll_runs (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
year SMALLINT UNSIGNED NOT NULL,
month TINYINT UNSIGNED NOT NULL,
status ENUM('draft','finalized') NOT NULL DEFAULT 'draft',
finalized_at DATETIME NULL,
created_by INT UNSIGNED NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_payroll_month (year, month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$pdo->exec("CREATE TABLE IF NOT EXISTS payroll_entries (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
payroll_run_id INT UNSIGNED NOT NULL,
employee_id INT UNSIGNED NOT NULL,
days_in_month TINYINT UNSIGNED NOT NULL DEFAULT 26,
days_worked DECIMAL(5,1) NOT NULL DEFAULT 0,
basic DECIMAL(12,2) NOT NULL DEFAULT 0,
hra DECIMAL(12,2) NOT NULL DEFAULT 0,
da DECIMAL(12,2) NOT NULL DEFAULT 0,
conveyance DECIMAL(12,2) NOT NULL DEFAULT 0,
special_allowance DECIMAL(12,2) NOT NULL DEFAULT 0,
other_allowance DECIMAL(12,2) NOT NULL DEFAULT 0,
gross DECIMAL(12,2) NOT NULL DEFAULT 0,
pf_deduction DECIMAL(12,2) NOT NULL DEFAULT 0,
esi_deduction DECIMAL(12,2) NOT NULL DEFAULT 0,
pt_deduction DECIMAL(12,2) NOT NULL DEFAULT 0,
tds_deduction DECIMAL(12,2) NOT NULL DEFAULT 0,
advance_deduction DECIMAL(12,2) NOT NULL DEFAULT 0,
other_deduction DECIMAL(12,2) NOT NULL DEFAULT 0,
total_deductions DECIMAL(12,2) NOT NULL DEFAULT 0,
net_pay DECIMAL(12,2) NOT NULL DEFAULT 0,
notes TEXT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uniq_payroll_emp (payroll_run_id, employee_id),
CONSTRAINT fk_pe_run FOREIGN KEY (payroll_run_id) REFERENCES payroll_runs(id) ON DELETE CASCADE,
CONSTRAINT fk_pe_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$checked = true;
}
function ensure_leaves_table(): void
{
static $checked = false;
if ($checked) return;
$pdo = db();
$pdo->exec("CREATE TABLE IF NOT EXISTS leaves (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id INT UNSIGNED NOT NULL,
leave_type VARCHAR(50) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
reason TEXT,
status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
approved_by INT UNSIGNED NULL,
approved_at DATETIME NULL,
rejection_reason TEXT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at DATETIME NULL,
FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
INDEX idx_employee_id (employee_id),
INDEX idx_status (status),
INDEX idx_dates (start_date, end_date),
INDEX idx_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$checked = true;
}
function pt_slab(float $gross): float
{
// Professional tax slabs (Maharashtra-style, adjustable)
if ($gross <= 7500) return 0;
if ($gross <= 10000) return 175;
return 200;
}
function calculate_payroll_entry(array $ss, float $daysWorked, float $daysInMonth, array $overrides = []): array
{
$ratio = $daysInMonth > 0 ? $daysWorked / $daysInMonth : 1;
$basic = round($ss['basic'] * $ratio, 2);
$hra = round($ss['hra'] * $ratio, 2);
$da = round($ss['da'] * $ratio, 2);
$conv = round($ss['conveyance'] * $ratio, 2);
$special = round($ss['special_allowance'] * $ratio, 2);
$other = round($ss['other_allowance'] * $ratio, 2);
$gross = $basic + $hra + $da + $conv + $special + $other;
$pf = $ss['pf_applicable'] ? round($basic * 0.12, 2) : 0;
$esi = $ss['esi_applicable'] ? round($gross * 0.0075, 2) : 0;
$pt = $ss['pt_applicable'] ? pt_slab($gross) : 0;
$tds = (float) ($overrides['tds_deduction'] ?? 0);
$adv = (float) ($overrides['advance_deduction'] ?? 0);
$otherD = (float) ($overrides['other_deduction'] ?? 0);
$totalD = $pf + $esi + $pt + $tds + $adv + $otherD;
$net = round($gross - $totalD, 2);
return compact('basic','hra','da','conv','special','other','gross','pf','esi','pt','tds','adv','otherD','totalD','net');
}
function get_salary_structure(int $empId): ?array
{
ensure_payroll_tables();
$stmt = db()->prepare('SELECT * FROM salary_structures WHERE employee_id = ? LIMIT 1');
$stmt->execute([$empId]);
return $stmt->fetch() ?: null;
}
// ===========================================================================
// APPRAISAL HELPERS
// ===========================================================================
function ensure_appraisal_tables(): void
{
static $checked = false;
if ($checked) return;
$pdo = db();
$pdo->exec("CREATE TABLE IF NOT EXISTS appraisal_cycles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
cycle_type ENUM('annual','half_yearly','quarterly') NOT NULL DEFAULT 'annual',
year SMALLINT UNSIGNED NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
status ENUM('open','closed') NOT NULL DEFAULT 'open',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$pdo->exec("CREATE TABLE IF NOT EXISTS appraisals (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
cycle_id INT UNSIGNED NOT NULL,
employee_id INT UNSIGNED NOT NULL,
self_rating TINYINT UNSIGNED NULL,
self_comments TEXT NULL,
manager_rating TINYINT UNSIGNED NULL,
manager_comments TEXT NULL,
final_rating TINYINT UNSIGNED NULL,
increment_pct DECIMAL(5,2) NULL,
status ENUM('pending','self_submitted','reviewed','closed') NOT NULL DEFAULT 'pending',
reviewed_by INT UNSIGNED NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uniq_cycle_emp (cycle_id, employee_id),
CONSTRAINT fk_apr_cycle FOREIGN KEY (cycle_id) REFERENCES appraisal_cycles(id) ON DELETE CASCADE,
CONSTRAINT fk_apr_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$checked = true;
}
// ===========================================================================
// ASSET HELPERS
// ===========================================================================
function ensure_asset_tables(): void
{
static $checked = false;
if ($checked) return;
$pdo = db();
$pdo->exec("CREATE TABLE IF NOT EXISTS assets (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
asset_tag VARCHAR(60) NOT NULL UNIQUE,
name VARCHAR(200) NOT NULL,
category VARCHAR(100) NOT NULL DEFAULT 'Other',
brand VARCHAR(100) NULL,
model VARCHAR(100) NULL,
serial_no VARCHAR(120) NULL,
purchase_date DATE NULL,
purchase_value DECIMAL(12,2) NULL,
status ENUM('available','assigned','maintenance','retired') NOT NULL DEFAULT 'available',
notes TEXT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$pdo->exec("CREATE TABLE IF NOT EXISTS asset_assignments (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
asset_id INT UNSIGNED NOT NULL,
employee_id INT UNSIGNED NOT NULL,
assigned_date DATE NOT NULL,
returned_date DATE NULL,
condition_out VARCHAR(200) NULL,
condition_in VARCHAR(200) NULL,
assigned_by INT UNSIGNED NULL,
notes TEXT NULL,
is_active TINYINT(1) NOT NULL DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT fk_aa_asset FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE,
CONSTRAINT fk_aa_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$checked = true;
}
// ===========================================================================
// RECRUITMENT HELPERS
// ===========================================================================
function ensure_recruitment_tables(): void
{
static $checked = false;
if ($checked) return;
$pdo = db();
$pdo->exec("CREATE TABLE IF NOT EXISTS job_openings (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
department VARCHAR(120) NULL,
designation_id INT UNSIGNED NULL,
description TEXT NULL,
vacancies TINYINT UNSIGNED NOT NULL DEFAULT 1,
status ENUM('open','closed','on_hold') NOT NULL DEFAULT 'open',
posted_date DATE NOT NULL,
closing_date DATE NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$pdo->exec("CREATE TABLE IF NOT EXISTS candidates (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
job_opening_id INT UNSIGNED NOT NULL,
full_name VARCHAR(200) NOT NULL,
email VARCHAR(190) NULL,
phone VARCHAR(30) NOT NULL,
source VARCHAR(100) NULL,
resume_path VARCHAR(500) NULL,
stage ENUM('applied','shortlisted','interview_scheduled','offered','joined','rejected') NOT NULL DEFAULT 'applied',
interview_date DATETIME NULL,
interview_notes TEXT NULL,
offer_date DATE NULL,
joining_date DATE NULL,
converted_emp_id INT UNSIGNED NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT fk_cand_job FOREIGN KEY (job_opening_id) REFERENCES job_openings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$checked = true;
}
// ===========================================================================
// ANNOUNCEMENTS HELPERS
// ===========================================================================
function ensure_announcements_table(): void
{
static $checked = false;
if ($checked) return;
db()->exec("CREATE TABLE IF NOT EXISTS announcements (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(300) NOT NULL,
body TEXT NOT NULL,
departments JSON NULL,
is_pinned TINYINT(1) NOT NULL DEFAULT 0,
published_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
expires_at DATETIME NULL,
created_by INT UNSIGNED NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$checked = true;
}
function list_announcements(?string $department = null, int $limit = 20): array
{
ensure_announcements_table();
$pdo = db();
$now = date('Y-m-d H:i:s');
$rows = $pdo->prepare(
"SELECT * FROM announcements
WHERE published_at <= ? AND (expires_at IS NULL OR expires_at > ?)
ORDER BY is_pinned DESC, published_at DESC
LIMIT ?"
);
$rows->execute([$now, $now, $limit]);
$all = $rows->fetchAll();
if ($department === null) return $all;
return array_values(array_filter($all, function ($a) use ($department) {
if ($a['departments'] === null) return true;
$depts = json_decode($a['departments'], true);
return is_array($depts) && in_array($department, $depts, true);
}));
}
function get_marquee_announcements(?string $department = null, int $limit = 5): array
{
ensure_announcements_table();
$pdo = db();
$now = date('Y-m-d H:i:s');
$rows = $pdo->prepare(
"SELECT id, title, departments FROM announcements
WHERE published_at <= ? AND (expires_at IS NULL OR expires_at > ?)
ORDER BY is_pinned DESC, published_at DESC
LIMIT ?"
);
$rows->execute([$now, $now, $limit]);
$all = $rows->fetchAll();
if ($department === null) return $all;
return array_values(array_filter($all, function ($a) use ($department) {
if ($a['departments'] === null) return true;
$depts = json_decode($a['departments'], true);
return is_array($depts) && in_array($department, $depts, true);
}));
}
// ===========================================================================
// NOTIFICATIONS HELPERS
// ===========================================================================
function ensure_notifications_table(): void
{
static $checked = false;
if ($checked) return;
$checked = true;
$pdo = db();
try {
$pdo->exec("
CREATE TABLE IF NOT EXISTS notifications (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
type VARCHAR(50) NOT NULL DEFAULT 'general',
title VARCHAR(255) NOT NULL,
message TEXT NOT NULL,
data LONGTEXT,
read_at TIMESTAMP NULL DEFAULT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
KEY idx_user_id (user_id),
KEY idx_read_at (read_at),
KEY idx_created_at (created_at),
KEY idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");
} catch (Throwable $e) {
// Table creation failed (e.g. older MySQL); notifications will be unavailable
error_log('ensure_notifications_table failed: ' . $e->getMessage());
}
}
function create_notification(int $userId, string $type, string $title, string $message, ?array $data = null): int
{
ensure_notifications_table();
$pdo = db();
$stmt = $pdo->prepare("
INSERT INTO notifications (user_id, type, title, message, data, created_at)
VALUES (?, ?, ?, ?, ?, NOW())
");
$dataJson = $data ? json_encode($data) : null;
$stmt->execute([$userId, $type, $title, $message, $dataJson]);
return (int) $pdo->lastInsertId();
}
function get_user_notifications(int $userId, int $limit = 10, bool $unreadOnly = false): array
{
ensure_notifications_table();
$pdo = db();
$where = "user_id = ? AND deleted_at IS NULL";
$params = [$userId];
if ($unreadOnly) {
$where .= " AND read_at IS NULL";
}
$stmt = $pdo->prepare("
SELECT * FROM notifications
WHERE {$where}
ORDER BY created_at DESC
LIMIT ?
");
$params[] = $limit;
$stmt->execute($params);
return $stmt->fetchAll();
}
function get_unread_notification_count(int $userId): int
{
ensure_notifications_table();
$pdo = db();
$stmt = $pdo->prepare("
SELECT COUNT(*) FROM notifications
WHERE user_id = ? AND read_at IS NULL AND deleted_at IS NULL
");
$stmt->execute([$userId]);
return (int) $stmt->fetchColumn();
}
function mark_notification_as_read(int $notificationId): void
{
ensure_notifications_table();
$pdo = db();
$stmt = $pdo->prepare("
UPDATE notifications
SET read_at = NOW()
WHERE id = ?
");
$stmt->execute([$notificationId]);
}
function mark_all_notifications_as_read(int $userId): void
{
ensure_notifications_table();
$pdo = db();
$stmt = $pdo->prepare("
UPDATE notifications
SET read_at = NOW()
WHERE user_id = ? AND read_at IS NULL AND deleted_at IS NULL
");
$stmt->execute([$userId]);
}
function delete_notification(int $notificationId): void
{
ensure_notifications_table();
$pdo = db();
$stmt = $pdo->prepare("
UPDATE notifications
SET deleted_at = NOW()
WHERE id = ?
");
$stmt->execute([$notificationId]);
}
function trigger_notification_on_employee_onboard(int $employeeId): void
{
ensure_notifications_table();
$pdo = db();
// Get employee details
$stmt = $pdo->prepare("SELECT first_name, last_name FROM employees WHERE id = ?");
$stmt->execute([$employeeId]);
$emp = $stmt->fetch();
if (!$emp) return;
$empName = "{$emp['first_name']} {$emp['last_name']}";
// Notify all super admins
$admins = $pdo->query("SELECT id FROM admin_users WHERE role = 'super' AND deleted_at IS NULL")->fetchAll();
foreach ($admins as $admin) {
create_notification(
$admin['id'],
'new_employee',
'New Employee Onboarded',
"Employee {$empName} has been added to the system.",
['employee_id' => $employeeId, 'employee_name' => $empName]
);
}
}
function trigger_notification_on_leave_request(int $leaveId): void
{
ensure_notifications_table();
$pdo = db();
// Get leave details
$stmt = $pdo->prepare("
SELECT e.id as emp_id, CONCAT(e.first_name, ' ', e.last_name) as emp_name,
l.start_date, l.end_date
FROM leaves l
JOIN employees e ON l.employee_id = e.id
WHERE l.id = ?
");
$stmt->execute([$leaveId]);
$leave = $stmt->fetch();
if (!$leave) return;
// Notify all admins
$admins = $pdo->query("SELECT id FROM admin_users WHERE deleted_at IS NULL")->fetchAll();
foreach ($admins as $admin) {
create_notification(
$admin['id'],
'leave_request',
'Leave Request Submitted',
"{$leave['emp_name']} has requested leave from {$leave['start_date']} to {$leave['end_date']}.",
['leave_id' => $leaveId, 'employee_id' => $leave['emp_id']]
);
}
}
function trigger_notification_on_payroll_complete(string $month, int $year): void
{
ensure_notifications_table();
$pdo = db();
// Notify all super admins
$admins = $pdo->query("SELECT id FROM admin_users WHERE role = 'super' AND deleted_at IS NULL")->fetchAll();
foreach ($admins as $admin) {
create_notification(
$admin['id'],
'payroll_completed',
'Payroll Completed',
"Payroll for {$month} {$year} has been successfully processed.",
['month' => $month, 'year' => $year]
);
}
}
// ===========================================================================
// REPORTING HELPERS
// ===========================================================================
function get_attendance_report(?string $startDate = null, ?string $endDate = null, ?string $department = null): array
{
if (!$startDate) $startDate = date('Y-m-01');
if (!$endDate) $endDate = date('Y-m-t');
ensure_employees_table();
ensure_payroll_tables();
$pdo = db();
$sql = "
SELECT
e.id,
CONCAT(e.first_name, ' ', e.last_name) as employee_name,
e.email,
e.phone,
e.department,
e.designation_id,
(SELECT COUNT(*) FROM payroll_entries WHERE employee_id = e.id AND DATE(date) BETWEEN ? AND ?) as days_present,
(SELECT COUNT(*) FROM leaves WHERE employee_id = e.id AND status='approved' AND DATE(start_date) <= ? AND DATE(end_date) >= ?) as days_on_leave,
DATEDIFF(?, ?) + 1 as total_days
FROM employees e
WHERE e.deleted_at IS NULL AND e.employee_status = 'Active'
";
$params = [$startDate, $endDate, $endDate, $startDate, $endDate, $startDate];
if ($department) {
$sql .= " AND e.department = ?";
$params[] = $department;
}
$sql .= " ORDER BY e.first_name, e.last_name";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}
function get_payroll_report(?string $month = null, ?int $year = null, ?string $department = null): array
{
if (!$month) $month = date('n');
if (!$year) $year = date('Y');
ensure_payroll_tables();
$pdo = db();
$startDate = "{$year}-{$month}-01";
$endDate = date('Y-m-t', strtotime($startDate));
$sql = "
SELECT
e.id,
CONCAT(e.first_name, ' ', e.last_name) as employee_name,
e.email,
e.department,
pe.basic,
pe.hra,
pe.da,
pe.conveyance,
pe.special_allowance,
pe.other_allowance,
pe.gross,
pe.pf,
pe.esi,
pe.pt,
pe.tds_deduction,
pe.advance_deduction,
pe.other_deduction,
pe.total_deduction,
pe.net,
pe.date,
COUNT(*) as number_of_entries
FROM payroll_entries pe
JOIN employees e ON pe.employee_id = e.id
WHERE DATE(pe.date) BETWEEN ? AND ? AND e.deleted_at IS NULL
";
$params = [$startDate, $endDate];
if ($department) {
$sql .= " AND e.department = ?";
$params[] = $department;
}
$sql .= " GROUP BY pe.employee_id ORDER BY e.first_name, e.last_name";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}
function get_leave_report(?string $department = null): array
{
ensure_payroll_tables();
$pdo = db();
$sql = "
SELECT
e.id,
CONCAT(e.first_name, ' ', e.last_name) as employee_name,
e.email,
e.department,
COALESCE(SUM(DATEDIFF(l.end_date, l.start_date) + 1), 0) as leaves_taken,
20 as total_leave_balance,
20 - COALESCE(SUM(DATEDIFF(l.end_date, l.start_date) + 1), 0) as remaining_balance,
(SELECT COUNT(*) FROM leaves WHERE employee_id = e.id AND status='approved') as total_approvals,
(SELECT COUNT(*) FROM leaves WHERE employee_id = e.id AND status='pending') as pending_approvals
FROM employees e
LEFT JOIN leaves l ON e.id = l.employee_id AND l.status = 'approved' AND l.deleted_at IS NULL
WHERE e.deleted_at IS NULL AND e.employee_status = 'Active'
";
$params = [];
if ($department) {
$sql .= " AND e.department = ?";
$params[] = $department;
}
$sql .= " GROUP BY e.id ORDER BY e.first_name, e.last_name";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}
function get_recruitment_report(): array
{
$pdo = db();
$sql = "
SELECT
'Total Applications' as metric,
COUNT(*) as count
FROM recruitment_applications WHERE deleted_at IS NULL
UNION ALL
SELECT
'Interview Scheduled',
COUNT(*) FROM recruitment_applications WHERE status = 'interview_scheduled' AND deleted_at IS NULL
UNION ALL
SELECT
'Offers Extended',
COUNT(*) FROM recruitment_applications WHERE status = 'offer_extended' AND deleted_at IS NULL
UNION ALL
SELECT
'Offers Accepted',
COUNT(*) FROM recruitment_applications WHERE status = 'accepted' AND deleted_at IS NULL
UNION ALL
SELECT
'Rejected',
COUNT(*) FROM recruitment_applications WHERE status = 'rejected' AND deleted_at IS NULL
";
$stmt = $pdo->prepare($sql);
$stmt->execute();
return $stmt->fetchAll();
}
// ============================================
// PHASE 5: EMPLOYEE SELF-SERVICE HELPERS
// ============================================
function get_employee_profile(int $employee_id): ?array
{
$pdo = db();
$sql = "
SELECT
id, first_name, last_name, email, phone, gender,
date_of_birth, address, city, state, pin_code,
emergency_contact_name, emergency_contact_phone,
bank_account_number, ifsc_code,
department, designation, date_of_joining,
status, created_at
FROM employees
WHERE id = ? AND deleted_at IS NULL
";
$stmt = $pdo->prepare($sql);
$stmt->execute([$employee_id]);
return $stmt->fetch() ?: null;
}
function update_employee_profile(int $employee_id, array $data): bool
{
$pdo = db();
// Allowed fields to update (whitelist)
$allowed_fields = [
'phone', 'address', 'city', 'state', 'pin_code',
'emergency_contact_name', 'emergency_contact_phone',
'bank_account_number', 'ifsc_code'
];
$updates = [];
$params = [];
foreach ($allowed_fields as $field) {
if (isset($data[$field])) {
$updates[] = "$field = ?";
$params[] = $data[$field];
}
}
if (empty($updates)) {
return false;
}
$params[] = $employee_id;
$sql = "
UPDATE employees
SET " . implode(', ', $updates) . ", updated_at = NOW()
WHERE id = ? AND deleted_at IS NULL
";
$stmt = $pdo->prepare($sql);
return $stmt->execute($params);
}
function get_employee_payslips(int $employee_id, ?string $month = null, ?int $year = null): array
{
if (!$month) $month = date('n');
if (!$year) $year = date('Y');
ensure_payroll_tables();
$pdo = db();
$startDate = "{$year}-{$month}-01";
$endDate = date('Y-m-t', strtotime($startDate));
$sql = "
SELECT
pe.id,
e.first_name,
e.last_name,
e.email,
e.department,
CONCAT(e.first_name, ' ', e.last_name) as employee_name,
pe.basic,
pe.hra,
pe.da,
pe.conveyance,
pe.special_allowance,
pe.other_allowance,
pe.gross,
pe.pf,
pe.esi,
pe.pt,
pe.tds_deduction,
pe.advance_deduction,
pe.other_deduction,
pe.total_deduction,
pe.net,
pe.date as payslip_date,
DATE_FORMAT(pe.date, '%M %Y') as month_year
FROM payroll_entries pe
JOIN employees e ON pe.employee_id = e.id
WHERE pe.employee_id = ? AND DATE(pe.date) BETWEEN ? AND ? AND pe.deleted_at IS NULL
ORDER BY pe.date DESC
";
$stmt = $pdo->prepare($sql);
$stmt->execute([$employee_id, $startDate, $endDate]);
return $stmt->fetchAll();
}
function get_employee_leaves(int $employee_id, ?string $department = null): array
{
$pdo = db();
$sql = "
SELECT
l.id,
e.first_name,
e.last_name,
e.email,
e.department,
l.from_date,
l.to_date,
DATEDIFF(l.to_date, l.from_date) + 1 as days,
l.reason,
l.type as leave_type,
l.status,
l.created_at,
l.updated_at
FROM leaves l
JOIN employees e ON l.employee_id = e.id
WHERE l.employee_id = ? AND l.deleted_at IS NULL
";
$params = [$employee_id];
if ($department) {
$sql .= " AND e.department = ?";
$params[] = $department;
}
$sql .= " ORDER BY l.created_at DESC";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}
function submit_leave_request(int $employee_id, string $from_date, string $to_date, string $reason, string $leave_type = 'Casual'): ?int
{
// Validate dates
$from = strtotime($from_date);
$to = strtotime($to_date);
if (!$from || !$to) {
return null;
}
if ($from > $to) {
return null;
}
$pdo = db();
// Check for overlapping leave requests
$sql = "
SELECT id FROM leaves
WHERE employee_id = ?
AND deleted_at IS NULL
AND (
(from_date <= ? AND to_date >= ?)
OR (from_date <= ? AND to_date >= ?)
OR (from_date >= ? AND to_date <= ?)
)
";
$stmt = $pdo->prepare($sql);
$stmt->execute([$employee_id, $to_date, $from_date, $from_date, $from_date, $from_date, $to_date]);
if ($stmt->rowCount() > 0) {
return null; // Overlapping request exists
}
// Create new leave request
$sql = "
INSERT INTO leaves
(employee_id, from_date, to_date, reason, type, status, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, 'pending', NOW(), NOW())
";
$stmt = $pdo->prepare($sql);
$success = $stmt->execute([$employee_id, $from_date, $to_date, $reason, $leave_type]);
return $success ? $pdo->lastInsertId() : null;
}
function get_employee_attendance(int $employee_id, ?string $month = null, ?int $year = null): array
{
if (!$month) $month = date('n');
if (!$year) $year = date('Y');
$pdo = db();
$startDate = sprintf('%04d-%02d-01', $year, $month);
$endDate = date('Y-m-t', strtotime($startDate));
ensure_attendance_tables();
$stmt = $pdo->prepare(
'SELECT * FROM attendance_logs WHERE employee_id = ? AND attend_date BETWEEN ? AND ? ORDER BY attend_date ASC'
);
$stmt->execute([$employee_id, $startDate, $endDate]);
$rows = $stmt->fetchAll();
$attendance = [];
$counts = ['present'=>0,'absent'=>0,'half_day'=>0,'wfh'=>0,'holiday'=>0,'on_leave'=>0,'late'=>0];
foreach ($rows as $r) {
$attendance[$r['attend_date']] = $r;
if (isset($counts[$r['status']])) $counts[$r['status']]++;
if (!empty($r['is_late'])) $counts['late']++;
}
$totalDays = (int) date('t', strtotime($startDate));
$daysWorked = ($counts['present'] + $counts['wfh'] + $counts['half_day']);
return ['attendance' => $attendance, 'summary' => [
'total_days_in_month' => $totalDays,
'days_worked' => (int)$daysWorked,
'days_on_leave' => (int)$counts['on_leave'],
'counts' => $counts,
'month' => (int)$month,
'year' => (int)$year,
'month_name' => date('F', mktime(0, 0, 0, $month, 1)),
]];
}
function get_employee_appraisals(int $employee_id): array
{
$pdo = db();
$sql = "
SELECT
a.id,
a.rating,
a.comments,
a.appraisal_date,
a.status,
CONCAT(a.first_name, ' ', a.last_name) as name,
a.created_at
FROM appraisals a
WHERE a.employee_id = ? AND a.deleted_at IS NULL
ORDER BY a.appraisal_date DESC
";
$stmt = $pdo->prepare($sql);
$stmt->execute([$employee_id]);
return $stmt->fetchAll();
}
// ============================================
// PHASE 6: ADMIN CONTROL PANEL HELPERS
// ============================================
function get_all_users(?string $role = null, ?string $status = null): array
{
$pdo = db();
$sql = "
SELECT
id, first_name, last_name, email, phone, card_number,
role, status, last_login, created_at, updated_at
FROM employees
WHERE deleted_at IS NULL
";
$params = [];
if ($role) {
$sql .= " AND role = ?";
$params[] = $role;
}
if ($status) {
$sql .= " AND status = ?";
$params[] = $status;
}
$sql .= " ORDER BY created_at DESC";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}
function get_user_by_id(int $user_id): ?array
{
$pdo = db();
$sql = "
SELECT
id, first_name, last_name, email, phone,
role, status, department, designation,
date_of_joining, created_at, updated_at, last_login
FROM employees
WHERE id = ? AND deleted_at IS NULL
";
$stmt = $pdo->prepare($sql);
$stmt->execute([$user_id]);
return $stmt->fetch() ?: null;
}
function update_user_status(int $user_id, string $status): bool
{
$allowed_statuses = ['active', 'inactive', 'suspended'];
if (!in_array($status, $allowed_statuses)) {
return false;
}
$pdo = db();
$sql = "
UPDATE employees
SET status = ?, updated_at = NOW()
WHERE id = ? AND deleted_at IS NULL
";
$stmt = $pdo->prepare($sql);
return $stmt->execute([$status, $user_id]);
}
function update_user_role(int $user_id, string $role): bool
{
$allowed_roles = ['admin', 'employee', 'hr', 'manager'];
if (!in_array($role, $allowed_roles)) {
return false;
}
$pdo = db();
$sql = "
UPDATE employees
SET role = ?, updated_at = NOW()
WHERE id = ? AND deleted_at IS NULL
";
$stmt = $pdo->prepare($sql);
return $stmt->execute([$role, $user_id]);
}
function update_user_card_number(int $user_id, ?string $card_number): bool
{
$pdo = db();
$sql = "
UPDATE employees
SET card_number = ?, updated_at = NOW()
WHERE id = ? AND deleted_at IS NULL
";
$stmt = $pdo->prepare($sql);
$val = $card_number === '' ? null : $card_number;
return $stmt->execute([$val, $user_id]);
}
function delete_user(int $user_id): bool
{
$pdo = db();
$sql = "
UPDATE employees
SET deleted_at = NOW(), updated_at = NOW()
WHERE id = ? AND deleted_at IS NULL
";
$stmt = $pdo->prepare($sql);
return $stmt->execute([$user_id]);
}
function reset_user_password(int $user_id, string $new_password): bool
{
$pdo = db();
$sql = "
UPDATE employees
SET password = ?, updated_at = NOW()
WHERE id = ? AND deleted_at IS NULL
";
$stmt = $pdo->prepare($sql);
$hashed_password = password_hash($new_password, PASSWORD_BCRYPT);
return $stmt->execute([$hashed_password, $user_id]);
}
function get_system_settings(): array
{
$pdo = db();
$sql = "
SELECT setting_key, setting_value
FROM system_settings
WHERE deleted_at IS NULL
";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$results = $stmt->fetchAll();
$settings = [];
foreach ($results as $row) {
$settings[$row['setting_key']] = $row['setting_value'];
}
return $settings;
}
function update_system_setting(string $key, string $value): bool
{
$pdo = db();
// Check if setting exists
$sql = "SELECT id FROM system_settings WHERE setting_key = ? AND deleted_at IS NULL";
$stmt = $pdo->prepare($sql);
$stmt->execute([$key]);
if ($stmt->rowCount() > 0) {
$sql = "
UPDATE system_settings
SET setting_value = ?, updated_at = NOW()
WHERE setting_key = ? AND deleted_at IS NULL
";
} else {
$sql = "
INSERT INTO system_settings
(setting_key, setting_value, created_at, updated_at)
VALUES (?, ?, NOW(), NOW())
";
}
$stmt = $pdo->prepare($sql);
return $stmt->execute([$value, $key]);
}
function create_database_backup(): ?string
{
$pdo = db();
$backup_dir = '../backups';
if (!is_dir($backup_dir)) {
mkdir($backup_dir, 0755, true);
}
$backup_file = $backup_dir . '/backup_' . date('Y-m-d_H-i-s') . '.sql';
try {
$tables = [];
$stmt = $pdo->query("SHOW TABLES");
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
$tables[] = $row[0];
}
$output = "-- Database Backup\n";
$output .= "-- Generated: " . date('Y-m-d H:i:s') . "\n";
$output .= "-- Tables: " . count($tables) . "\n\n";
foreach ($tables as $table) {
$stmt = $pdo->query("SHOW CREATE TABLE $table");
$create_table = $stmt->fetch(PDO::FETCH_ASSOC);
$output .= $create_table["Create Table"] . ";\n\n";
$stmt = $pdo->query("SELECT * FROM $table");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (count($rows) > 0) {
$columns = array_keys($rows[0]);
$output .= "INSERT INTO $table (" . implode(', ', $columns) . ") VALUES\n";
$values = [];
foreach ($rows as $row) {
$row_values = [];
foreach ($row as $value) {
if ($value === null) {
$row_values[] = 'NULL';
} else {
$row_values[] = "'" . str_replace("'", "''", $value) . "'";
}
}
$values[] = "(" . implode(", ", $row_values) . ")";
}
$output .= implode(",\n", $values) . ";\n\n";
}
}
file_put_contents($backup_file, $output);
// Log backup
$sql = "
INSERT INTO system_backups
(backup_file, file_size, created_by, created_at)
VALUES (?, ?, ?, NOW())
";
$stmt = $pdo->prepare($sql);
$stmt->execute([
basename($backup_file),
filesize($backup_file),
$_SESSION['employee_id'] ?? 1
]);
return basename($backup_file);
} catch (Exception $e) {
return null;
}
}
function get_backups(?int $limit = 10): array
{
$pdo = db();
$sql = "
SELECT
id, backup_file, file_size, created_by, created_at
FROM system_backups
WHERE deleted_at IS NULL
ORDER BY created_at DESC
LIMIT ?
";
$stmt = $pdo->prepare($sql);
$stmt->execute([$limit]);
return $stmt->fetchAll();
}
function delete_backup(int $backup_id): bool
{
$pdo = db();
$sql = "SELECT backup_file FROM system_backups WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$backup_id]);
$backup = $stmt->fetch();
if ($backup && file_exists("../backups/" . $backup['backup_file'])) {
unlink("../backups/" . $backup['backup_file']);
}
$sql = "UPDATE system_backups SET deleted_at = NOW() WHERE id = ?";
$stmt = $pdo->prepare($sql);
return $stmt->execute([$backup_id]);
}
function get_system_stats(): array
{
$pdo = db();
// Get employee count
$stmt = $pdo->query("SELECT COUNT(*) as count FROM employees WHERE deleted_at IS NULL");
$total_employees = $stmt->fetch()['count'];
// Get active users
$stmt = $pdo->query("SELECT COUNT(*) as count FROM employees WHERE status = 'active' AND deleted_at IS NULL");
$active_employees = $stmt->fetch()['count'];
// Get admin count
$stmt = $pdo->query("SELECT COUNT(*) as count FROM employees WHERE role = 'admin' AND deleted_at IS NULL");
$admin_count = $stmt->fetch()['count'];
// Get pending leaves
$stmt = $pdo->query("SELECT COUNT(*) as count FROM leaves WHERE status = 'pending' AND deleted_at IS NULL");
$pending_leaves = $stmt->fetch()['count'];
// Get payroll runs
$stmt = $pdo->query("SELECT COUNT(*) as count FROM payroll_entries WHERE deleted_at IS NULL");
$total_payroll_entries = $stmt->fetch()['count'];
// Get unread notifications
$stmt = $pdo->query("SELECT COUNT(*) as count FROM notifications WHERE read_at IS NULL AND deleted_at IS NULL");
$unread_notifications = $stmt->fetch()['count'];
return [
'total_employees' => $total_employees,
'active_employees' => $active_employees,
'admin_count' => $admin_count,
'pending_leaves' => $pending_leaves,
'total_payroll_entries' => $total_payroll_entries,
'unread_notifications' => $unread_notifications
];
}
function get_activity_logs(?int $limit = 50, ?string $action = null): array
{
$pdo = db();
$sql = "
SELECT
id, user_id, action, entity_type, entity_id,
description, ip_address, created_at
FROM activity_logs
WHERE deleted_at IS NULL
";
$params = [];
if ($action) {
$sql .= " AND action = ?";
$params[] = $action;
}
$sql .= " ORDER BY created_at DESC LIMIT ?";
$params[] = $limit;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}
function log_activity(int $user_id, string $action, string $entity_type, ?int $entity_id = null, ?string $description = null): void
{
$pdo = db();
$ip_address = $_SERVER['REMOTE_ADDR'] ?? '0.0.0.0';
$sql = "
INSERT INTO activity_logs
(user_id, action, entity_type, entity_id, description, ip_address, created_at)
VALUES (?, ?, ?, ?, ?, ?, NOW())
";
$stmt = $pdo->prepare($sql);
try {
$stmt->execute([$user_id, $action, $entity_type, $entity_id, $description, $ip_address]);
} catch (Exception $e) {
// Silently fail if logs table doesn't exist
}
}
/* ============================================
PHASE 8: EXPORT FUNCTIONS (PDF & EXCEL)
============================================ */
/**
* Export data to Excel (CSV format)
* @param array $data 2D array of data
* @param string $filename Output filename without extension
* @param array $headers Optional column headers
*/
function export_to_excel(array $data, string $filename, array $headers = []): void
{
// Set headers for download
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment; filename="' . $filename . '.xlsx"');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
// Open output stream
$output = fopen('php://output', 'w');
// Write BOM for UTF-8 (Excel compatibility)
fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
// Write headers if provided
if (!empty($headers)) {
fputcsv($output, $headers);
}
// Write data rows
foreach ($data as $row) {
if (is_array($row)) {
fputcsv($output, $row);
} else {
fputcsv($output, [$row]);
}
}
fclose($output);
exit;
}
/**
* Export data to CSV (universal format)
* @param array $data 2D array of data
* @param string $filename Output filename without extension
* @param array $headers Optional column headers
*/
function export_to_csv(array $data, string $filename, array $headers = []): void
{
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="' . $filename . '.csv"');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
$output = fopen('php://output', 'w');
fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF)); // UTF-8 BOM
if (!empty($headers)) {
fputcsv($output, $headers);
}
foreach ($data as $row) {
fputcsv($output, is_array($row) ? $row : [$row]);
}
fclose($output);
exit;
}
/**
* Generate a simple PDF (using basic HTML to PDF conversion)
* @param string $html HTML content
* @param string $filename Output filename without extension
* @param string $title Document title
*/
function export_to_pdf(string $html, string $filename, string $title = 'Report'): void
{
// For now, we'll generate an HTML version that can be printed to PDF
// In production, consider using TCPDF or similar library
$output = <<<HTML
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>{$title}</title>
<style>
* { margin: 0; padding: 0; box-sizing: border-box; }
body { font-family: Arial, sans-serif; color: #333; line-height: 1.6; }
.pdf-header {
background: #2563eb; color: white; padding: 20px;
text-align: center; margin-bottom: 30px; border-radius: 4px;
}
.pdf-header h1 { font-size: 24px; margin-bottom: 5px; }
.pdf-header p { font-size: 12px; opacity: 0.9; }
table { width: 100%; border-collapse: collapse; margin: 20px 0; }
th, td {
padding: 12px; text-align: left; border-bottom: 1px solid #e5e7eb;
}
th { background: #f3f4f6; font-weight: 600; color: #374151; }
tr:hover { background: #f9fafb; }
.pdf-footer {
margin-top: 30px; padding-top: 20px; border-top: 1px solid #e5e7eb;
font-size: 12px; color: #6b7280; text-align: center;
}
@media print {
body { margin: 0; padding: 10mm; }
.no-print { display: none; }
table { page-break-inside: avoid; }
}
</style>
</head>
<body>
<div class="pdf-header">
<h1>Outer Orbit Technologies</h1>
<p>{$title} - Generated on {$this->app_name} HRMS</p>
</div>
{$html}
<div class="pdf-footer">
<p>Document generated on " . date('Y-m-d H:i:s') . " | Confidential</p>
</div>
<script>
window.addEventListener('load', function() {
window.print();
setTimeout(function() {
window.close();
}, 500);
});
</script>
</body>
</html>
HTML;
header('Content-Type: text/html; charset=utf-8');
header('Content-Disposition: inline; filename="' . $filename . '.html"');
echo $output;
exit;
}
/**
* Format table data for export (with proper headers and formatting)
* @param array $data Table data
* @param array $columnMapping Mapping of DB columns to display names
* @return array Formatted data ready for export
*/
function format_export_data(array $data, array $columnMapping = []): array
{
$formatted = [];
foreach ($data as $row) {
$formattedRow = [];
foreach ($row as $key => $value) {
$displayKey = $columnMapping[$key] ?? ucfirst(str_replace('_', ' ', $key));
// Format dates
if (strpos($key, 'date') !== false || strpos($key, 'at') !== false) {
$value = $value ? date('M d, Y H:i', strtotime($value)) : 'N/A';
}
// Format booleans
if (is_bool($value)) {
$value = $value ? 'Yes' : 'No';
}
// Format nulls
if (is_null($value)) {
$value = 'N/A';
}
$formattedRow[$displayKey] = $value;
}
$formatted[] = $formattedRow;
}
return $formatted;
}
/**
* Get attendance report data for export
* @param ?string $from_date Start date (Y-m-d)
* @param ?string $to_date End date (Y-m-d)
* @return array Report data
*/
function get_attendance_export_data(?string $from_date = null, ?string $to_date = null): array
{
$pdo = db();
$sql = "
SELECT
e.employee_id,
CONCAT(e.first_name, ' ', e.last_name) as employee_name,
e.department_id,
d.name as department,
COUNT(*) as total_days,
SUM(CASE WHEN a.is_present = 1 THEN 1 ELSE 0 END) as present_days,
SUM(CASE WHEN a.is_present = 0 THEN 1 ELSE 0 END) as absent_days,
ROUND((SUM(CASE WHEN a.is_present = 1 THEN 1 ELSE 0 END) / COUNT(*) * 100), 2) as attendance_percentage,
MAX(a.date) as last_attendance_date
FROM attendance a
JOIN employees e ON a.employee_id = e.id
JOIN departments d ON e.department_id = d.id
WHERE a.deleted_at IS NULL AND e.deleted_at IS NULL
";
$params = [];
if ($from_date) {
$sql .= " AND DATE(a.date) >= ?";
$params[] = $from_date;
}
if ($to_date) {
$sql .= " AND DATE(a.date) <= ?";
$params[] = $to_date;
}
$sql .= " GROUP BY e.employee_id, e.first_name, e.last_name, e.department_id, d.name";
$sql .= " ORDER BY e.first_name, e.last_name";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
/**
* Get leave report data for export
* @param ?string $from_date Start date (Y-m-d)
* @param ?string $to_date End date (Y-m-d)
* @param ?string $status Filter by status (approved/pending/rejected)
* @return array Report data
*/
function get_leave_export_data(?string $from_date = null, ?string $to_date = null, ?string $status = null): array
{
$pdo = db();
$sql = "
SELECT
e.employee_id,
CONCAT(e.first_name, ' ', e.last_name) as employee_name,
lr.leave_type,
lr.from_date,
lr.to_date,
DATEDIFF(lr.to_date, lr.from_date) + 1 as days_requested,
lr.reason,
lr.status,
u.first_name as approved_by,
lr.created_at,
lr.updated_at
FROM leave_requests lr
JOIN employees e ON lr.employee_id = e.id
LEFT JOIN users u ON lr.approved_by = u.id
WHERE lr.deleted_at IS NULL AND e.deleted_at IS NULL
";
$params = [];
if ($from_date) {
$sql .= " AND DATE(lr.from_date) >= ?";
$params[] = $from_date;
}
if ($to_date) {
$sql .= " AND DATE(lr.to_date) <= ?";
$params[] = $to_date;
}
if ($status) {
$sql .= " AND lr.status = ?";
$params[] = $status;
}
$sql .= " ORDER BY lr.created_at DESC";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
/**
* Get payroll report data for export
* @param ?string $from_date Start date (Y-m-d)
* @param ?string $to_date End date (Y-m-d)
* @return array Report data
*/
function get_payroll_export_data(?string $from_date = null, ?string $to_date = null): array
{
$pdo = db();
$sql = "
SELECT
e.employee_id,
CONCAT(e.first_name, ' ', e.last_name) as employee_name,
e.email,
d.name as department,
ds.name as designation,
s.salary_amount,
p.period_month,
p.period_year,
p.gross_salary,
p.deductions,
p.net_salary,
p.payment_date,
p.payment_status,
p.created_at
FROM payroll p
JOIN employees e ON p.employee_id = e.id
JOIN departments d ON e.department_id = d.id
JOIN designations ds ON e.designation_id = ds.id
LEFT JOIN salaries s ON e.id = s.employee_id
WHERE p.deleted_at IS NULL AND e.deleted_at IS NULL
";
$params = [];
if ($from_date) {
$sql .= " AND DATE(p.period_date) >= ?";
$params[] = $from_date;
}
if ($to_date) {
$sql .= " AND DATE(p.period_date) <= ?";
$params[] = $to_date;
}
$sql .= " ORDER BY p.period_year DESC, p.period_month DESC, e.first_name";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
/**
* Generate comprehensive PDF/printable report with styling
* @param string $type Report type (attendance/leave/payroll/recruitment)
* @param array $data Report data
* @param array $filters Applied filters
* @return string HTML output
*/
function generate_report_html(string $type, array $data, array $filters = []): string
{
$title = ucfirst($type) . ' Report';
$dateGenerated = date('M d, Y H:i A');
$filterText = '';
if (!empty($filters)) {
$filterArray = [];
foreach ($filters as $key => $value) {
if ($value) {
$filterArray[] = ucfirst(str_replace('_', ' ', $key)) . ': ' . $value;
}
}
if (!empty($filterArray)) {
$filterText = '<p class="filter-info"><strong>Filters Applied:</strong> ' . implode(' | ', $filterArray) . '</p>';
}
}
$tableHtml = '<table class="report-table"><thead><tr>';
// Generate table headers
if (!empty($data)) {
foreach (array_keys($data[0]) as $header) {
$tableHtml .= '<th>' . htmlspecialchars($header) . '</th>';
}
}
$tableHtml .= '</tr></thead><tbody>';
// Generate table rows
foreach ($data as $row) {
$tableHtml .= '<tr>';
foreach ($row as $value) {
$displayValue = is_null($value) ? 'N/A' : htmlspecialchars($value);
$tableHtml .= '<td>' . $displayValue . '</td>';
}
$tableHtml .= '</tr>';
}
$tableHtml .= '</tbody></table>';
// Summary statistics
$summaryHtml = '<div class="report-summary">';
$summaryHtml .= '<p><strong>Total Records:</strong> ' . count($data) . '</p>';
$summaryHtml .= '<p><strong>Generated:</strong> ' . $dateGenerated . '</p>';
$summaryHtml .= '</div>';
return <<<HTML
<style>
.report-table { width: 100%; border-collapse: collapse; margin: 20px 0; }
.report-table th, .report-table td {
padding: 10px; text-align: left; border: 1px solid #ddd;
}
.report-table th { background: #2563eb; color: white; font-weight: bold; }
.report-table tr:nth-child(even) { background: #f9fafb; }
.filter-info { background: #e8f4f8; padding: 10px; border-left: 4px solid #2563eb; margin: 10px 0; }
.report-summary { background: #f3f4f6; padding: 15px; border-radius: 4px; margin: 20px 0; }
</style>
{$filterText}
{$tableHtml}
{$summaryHtml}
HTML;
}
/* ============================================
PHASE 9: SECURITY & PERFORMANCE
============================================ */
/**
* Create database indexes for performance optimization
*/
function create_database_indexes(): void
{
$pdo = db();
$indexes = [
// Users table
"ALTER TABLE users ADD INDEX idx_email (email)" => "User email lookup",
"ALTER TABLE users ADD INDEX idx_role (role)" => "User role filtering",
"ALTER TABLE users ADD INDEX idx_deleted (deleted_at)" => "Soft delete filtering",
// Employees table
"ALTER TABLE employees ADD INDEX idx_employee_id (employee_id)" => "Employee ID lookup",
"ALTER TABLE employees ADD INDEX idx_department (department_id)" => "Department filtering",
"ALTER TABLE employees ADD INDEX idx_designation (designation_id)" => "Designation filtering",
"ALTER TABLE employees ADD INDEX idx_deleted (deleted_at)" => "Soft delete filtering",
// Attendance table
"ALTER TABLE attendance ADD INDEX idx_employee_date (employee_id, date)" => "Attendance lookup",
"ALTER TABLE attendance ADD INDEX idx_date (date)" => "Date range queries",
// Leave requests
"ALTER TABLE leave_requests ADD INDEX idx_employee_dates (employee_id, from_date, to_date)" => "Leave lookup",
"ALTER TABLE leave_requests ADD INDEX idx_status (status)" => "Status filtering",
// Activity logs
"ALTER TABLE activity_logs ADD INDEX idx_user_date (user_id, created_at)" => "User activity tracking",
"ALTER TABLE activity_logs ADD INDEX idx_action (action)" => "Action filtering",
// Notifications
"ALTER TABLE notifications ADD INDEX idx_user_unread (user_id, is_read)" => "Unread notifications",
];
foreach ($indexes as $sql => $description) {
try {
$pdo->exec($sql);
// Index created or already exists
} catch (Exception $e) {
// Index might already exist, silently continue
}
}
}
/**
* Generate CSRF token for session
* @return string CSRF token
*/
function generate_csrf_token(): string
{
if (!isset($_SESSION['csrf_token'])) {
$_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}
return $_SESSION['csrf_token'];
}
/**
* Verify CSRF token
* @param string $token Token to verify
* @return bool True if valid
*/
function verify_csrf_token(string $token): bool
{
return isset($_SESSION['csrf_token']) && hash_equals($_SESSION['csrf_token'], $token);
}
/**
* Hash password with bcrypt
* @param string $password Plain password
* @return string Hashed password
*/
function hash_password(string $password): string
{
return password_hash($password, PASSWORD_BCRYPT, ['cost' => 12]);
}
/**
* Verify password against hash
* @param string $password Plain password
* @param string $hash Password hash
* @return bool True if matches
*/
function verify_password(string $password, string $hash): bool
{
return password_verify($password, $hash);
}
/**
* Check if password needs rehashing (for security updates)
* @param string $hash Password hash
* @return bool True if should be rehashed
*/
function needs_password_rehash(string $hash): bool
{
return password_needs_rehash($hash, PASSWORD_BCRYPT, ['cost' => 12]);
}
/**
* Generate secure random token for 2FA
* @param int $length Token length
* @return string Random token
*/
function generate_2fa_token(int $length = 6): string
{
return str_pad(random_int(0, pow(10, $length) - 1), $length, '0', STR_PAD_LEFT);
}
/**
* Enable 2FA for a user
* @param int $user_id User ID
* @return array Array with secret and backup codes
*/
function enable_2fa(int $user_id): array
{
$pdo = db();
// Generate secret
$secret = bin2hex(random_bytes(16));
// Generate 10 backup codes
$backupCodes = [];
for ($i = 0; $i < 10; $i++) {
$backupCodes[] = bin2hex(random_bytes(4));
}
// Store 2FA secret
$sql = "
UPDATE users
SET two_factor_secret = ?, two_factor_enabled = 0, updated_at = NOW()
WHERE id = ?
";
$stmt = $pdo->prepare($sql);
$stmt->execute([$secret, $user_id]);
// Store backup codes (hashed)
$sql = "DELETE FROM two_factor_backups WHERE user_id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$user_id]);
$sql = "
INSERT INTO two_factor_backups (user_id, backup_code, used_at, created_at)
VALUES (?, ?, NULL, NOW())
";
$stmt = $pdo->prepare($sql);
foreach ($backupCodes as $code) {
$stmt->execute([$user_id, hash_password($code)]);
}
return [
'secret' => $secret,
'backup_codes' => $backupCodes,
'qr_code' => generate_2fa_qr_code($user_id, $secret)
];
}
/**
* Verify 2FA token
* @param int $user_id User ID
* @param string $token Token to verify
* @return bool True if valid
*/
function verify_2fa_token(int $user_id, string $token): bool
{
$pdo = db();
$sql = "SELECT two_factor_secret FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$user_id]);
$user = $stmt->fetch();
if (!$user || !$user['two_factor_secret']) {
return false;
}
// Simple token verification (6-digit TOTP)
// In production, use a TOTP library
$secret = $user['two_factor_secret'];
// Generate expected tokens (current, past, future for time sync tolerance)
for ($i = -1; $i <= 1; $i++) {
$time = floor((time() + ($i * 30)) / 30);
$hash = hash_hmac('sha1', $time, hex2bin($secret), true);
$offset = ord($hash[19]) & 0xf;
$code = (unpack('N', substr($hash, $offset, 4))[1] & 0x7fffffff) % 1000000;
$expectedToken = str_pad($code, 6, '0', STR_PAD_LEFT);
if ($expectedToken === $token) {
return true;
}
}
return false;
}
/**
* Generate 2FA QR code URL
* @param int $user_id User ID
* @param string $secret Secret key
* @return string QR code URL
*/
function generate_2fa_qr_code(int $user_id, string $secret): string
{
$pdo = db();
$sql = "SELECT email FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$user_id]);
$user = $stmt->fetch();
$appName = 'Outer Orbit HRMS';
$accountName = $user['email'];
$otpauth = "otpauth://totp/" . urlencode($appName . " ($accountName)") .
"?secret=" . $secret . "&issuer=" . urlencode($appName);
// Google Charts API for QR code
return "https://chart.googleapis.com/chart?chs=300x300&chld=M|0&cht=qr&chl=" . urlencode($otpauth);
}
/**
* Rate limiting check
* @param string $identifier User/IP identifier
* @param int $maxAttempts Maximum attempts allowed
* @param int $timeWindow Time window in seconds
* @return bool True if allowed, false if rate limited
*/
function check_rate_limit(string $identifier, int $maxAttempts = 5, int $timeWindow = 300): bool
{
$cacheKey = "rate_limit_" . hash('sha256', $identifier);
$cacheFile = sys_get_temp_dir() . '/' . $cacheKey . '.json';
$attempts = [];
$now = time();
// Load existing attempts
if (file_exists($cacheFile)) {
$data = json_decode(file_get_contents($cacheFile), true);
$attempts = array_filter($data['attempts'] ?? [], function($time) use ($now, $timeWindow) {
return ($now - $time) < $timeWindow;
});
}
// Check limit
if (count($attempts) >= $maxAttempts) {
return false;
}
// Record attempt
$attempts[] = $now;
file_put_contents($cacheFile, json_encode([
'attempts' => $attempts,
'updated_at' => $now
], JSON_PRETTY_PRINT));
return true;
}
/**
* Clear rate limit for identifier
* @param string $identifier User/IP identifier
*/
function clear_rate_limit(string $identifier): void
{
$cacheKey = "rate_limit_" . hash('sha256', $identifier);
$cacheFile = sys_get_temp_dir() . '/' . $cacheKey . '.json';
if (file_exists($cacheFile)) {
@unlink($cacheFile);
}
}
/**
* Secure session configuration
*/
function configure_session_security(): void
{
// Session settings for security
// Note: Can only set these BEFORE session_start()
// If session already active, these settings are safely ignored
if (session_status() === PHP_SESSION_NONE) {
ini_set('session.cookie_httponly', '1'); // Prevent JavaScript access
// Only require HTTPS cookies when the current request is actually over HTTPS
// so that plain-HTTP localhost (Laragon) still works.
$isHttps = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off')
|| (isset($_SERVER['SERVER_PORT']) && (int) $_SERVER['SERVER_PORT'] === 443);
ini_set('session.cookie_secure', $isHttps ? '1' : '0');
ini_set('session.cookie_samesite', 'Strict'); // CSRF prevention
ini_set('session.use_strict_mode', '1'); // Prevent fixation attacks
ini_set('session.sid_bits_per_character', '6'); // Stronger session IDs
}
}
/**
* Regenerate session ID (for post-login security)
*/
function regenerate_session_id(): void
{
if (isset($_SESSION)) {
$old_session = $_SESSION;
session_destroy();
session_start();
$_SESSION = $old_session;
session_regenerate_id(true);
}
}
/**
* Validate input for common security issues
* @param string $input Input string
* @param string $type Validation type (email, phone, alphanumeric, etc)
* @return string|bool Validated input or false if invalid
*/
function validate_secure_input(string $input, string $type = 'text'): string|bool
{
$input = trim($input);
switch ($type) {
case 'email':
return filter_var($input, FILTER_VALIDATE_EMAIL) ? $input : false;
case 'phone':
return preg_match('/^[0-9\+\-\(\)\s]{7,20}$/', $input) ? $input : false;
case 'alphanumeric':
return preg_match('/^[a-zA-Z0-9_\-\.]+$/', $input) ? $input : false;
case 'numeric':
return is_numeric($input) ? $input : false;
case 'url':
return filter_var($input, FILTER_VALIDATE_URL) ? $input : false;
case 'text':
default:
return strlen($input) > 0 ? $input : false;
}
}
/**
* Encrypt sensitive data
* @param string $data Data to encrypt
* @param string $key Encryption key
* @return string Base64 encoded encrypted data
*/
function encrypt_data(string $data, string $key = ''): string
{
if (empty($key)) {
$key = hash('sha256', 'default_key_' . getenv('SERVER_ADDR'), true);
}
$iv = openssl_random_pseudo_bytes(16);
$encrypted = openssl_encrypt($data, 'AES-256-CBC', $key, true, $iv);
return base64_encode($iv . $encrypted);
}
/**
* Decrypt sensitive data
* @param string $data Base64 encoded encrypted data
* @param string $key Encryption key
* @return string|bool Decrypted data or false if failed
*/
function decrypt_data(string $data, string $key = ''): string|bool
{
if (empty($key)) {
$key = hash('sha256', 'default_key_' . getenv('SERVER_ADDR'), true);
}
$data = base64_decode($data);
$iv = substr($data, 0, 16);
$encrypted = substr($data, 16);
return openssl_decrypt($encrypted, 'AES-256-CBC', $key, true, $iv);
}
// =========================================================================
// ADVANCED LOAN MANAGEMENT HELPERS
// =========================================================================
function ensure_loans_table(): void
{
static $checked = false;
if ($checked) return;
$pdo = db();
$sql = "CREATE TABLE IF NOT EXISTS employee_loans (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id INT UNSIGNED NOT NULL,
loan_amount DECIMAL(12, 2) NOT NULL,
requested_tenure_months INT UNSIGNED NOT NULL,
approved_tenure_months INT UNSIGNED DEFAULT NULL,
monthly_installment DECIMAL(12, 2) DEFAULT NULL,
loan_status ENUM('pending', 'approved', 'rejected', 'completed', 'cancelled') DEFAULT 'pending',
approval_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
approved_by INT UNSIGNED DEFAULT NULL,
reason_for_rejection VARCHAR(500) DEFAULT NULL,
requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
approved_at DATETIME DEFAULT NULL,
completion_date DATE DEFAULT NULL,
deleted_at DATETIME DEFAULT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_employee_id (employee_id),
INDEX idx_status (loan_status),
INDEX idx_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$pdo->exec($sql);
$sql = "CREATE TABLE IF NOT EXISTS loan_installments (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
loan_id INT UNSIGNED NOT NULL,
installment_number INT UNSIGNED NOT NULL,
due_month DATE NOT NULL,
amount_due DECIMAL(12, 2) NOT NULL,
amount_paid DECIMAL(12, 2) DEFAULT 0.00,
status ENUM('pending', 'paid', 'overdue', 'waived') DEFAULT 'pending',
paid_at DATETIME DEFAULT NULL,
paid_via ENUM('salary_deduction', 'manual_payment') DEFAULT 'salary_deduction',
notes VARCHAR(255) DEFAULT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_loan_id (loan_id),
INDEX idx_status (status),
INDEX idx_due_month (due_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$pdo->exec($sql);
$checked = true;
}
function get_active_loan_for_employee(int $employeeId): ?array
{
ensure_loans_table();
$pdo = db();
$stmt = $pdo->prepare(
'SELECT * FROM employee_loans
WHERE employee_id = ? AND loan_status IN ("pending", "approved") AND deleted_at IS NULL
ORDER BY created_at DESC LIMIT 1'
);
$stmt->execute([$employeeId]);
return $stmt->fetch() ?: null;
}
function create_loan_installments(int $loanId, int $months, float $monthlyAmount): void
{
ensure_loans_table();
$pdo = db();
$stmt = $pdo->prepare('SELECT * FROM employee_loans WHERE id = ? LIMIT 1');
$stmt->execute([$loanId]);
$loan = $stmt->fetch();
if (!$loan) return;
// Don't start transaction here - let the caller manage it
$insert = $pdo->prepare(
'INSERT INTO loan_installments (loan_id, installment_number, due_month, amount_due, status)
VALUES (?, ?, ?, ?, "pending")'
);
$date = new DateTime($loan['approved_at'] ?? 'now');
$date->add(new DateInterval('P1M'));
for ($i = 1; $i <= $months; $i++) {
$insert->execute([
$loanId,
$i,
$date->format('Y-m-01'),
$monthlyAmount
]);
$date->add(new DateInterval('P1M'));
}
}
function get_loan_with_installments(int $loanId): ?array
{
ensure_loans_table();
$pdo = db();
$stmt = $pdo->prepare('SELECT * FROM employee_loans WHERE id = ? LIMIT 1');
$stmt->execute([$loanId]);
$loan = $stmt->fetch();
if (!$loan) return null;
$stmt = $pdo->prepare('SELECT * FROM loan_installments WHERE loan_id = ? ORDER BY installment_number');
$stmt->execute([$loanId]);
$loan['installments'] = $stmt->fetchAll();
return $loan;
}
function get_pending_loan_count(): int
{
ensure_loans_table();
$pdo = db();
return (int) $pdo->query(
"SELECT COUNT(*) FROM employee_loans
WHERE approval_status = 'pending' AND deleted_at IS NULL"
)->fetchColumn();
}
function get_employee_loan_summary(int $employeeId): array
{
ensure_loans_table();
$pdo = db();
$stmt = $pdo->prepare(
'SELECT
SUM(CASE WHEN loan_status = "approved" THEN loan_amount ELSE 0 END) as active_loan_amount,
SUM(CASE WHEN loan_status IN ("completed", "approved") THEN loan_amount ELSE 0 END) as total_borrowed,
COUNT(CASE WHEN loan_status = "pending" THEN 1 END) as pending_applications
FROM employee_loans
WHERE employee_id = ? AND deleted_at IS NULL'
);
$stmt->execute([$employeeId]);
return $stmt->fetch() ?: ['active_loan_amount' => 0, 'total_borrowed' => 0, 'pending_applications' => 0];
}
/**
* Create migration table for database indexes if not exists
*/
function ensure_migration_table(): void
{
$pdo = db();
$sql = "
CREATE TABLE IF NOT EXISTS migrations (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE,
batch INT NOT NULL,
executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
";
try {
$pdo->exec($sql);
} catch (Exception $e) {
// Table likely exists
}
}
/**
* Run database migration
* @param string $migrationName Name of migration
* @param callable $callback Migration callback function
*/
function run_migration(string $migrationName, callable $callback): void
{
$pdo = db();
ensure_migration_table();
// Check if migration already run
$sql = "SELECT id FROM migrations WHERE name = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$migrationName]);
if ($stmt->rowCount() > 0) {
return; // Already migrated
}
// Run migration
$callback($pdo);
// Record migration
$sql = "INSERT INTO migrations (name, batch) VALUES (?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([$migrationName, 1]);
}
/**
* Create 2FA tables if they don't exist
*/
function ensure_2fa_tables(): void
{
$pdo = db();
// Alter users table to add 2FA columns if not exists
try {
$pdo->exec("ALTER TABLE users ADD COLUMN two_factor_secret VARCHAR(255) NULL");
} catch (Exception $e) {
// Column likely exists
}
try {
$pdo->exec("ALTER TABLE users ADD COLUMN two_factor_enabled TINYINT(1) DEFAULT 0");
} catch (Exception $e) {
// Column likely exists
}
// Create 2FA backup codes table
$sql = "
CREATE TABLE IF NOT EXISTS two_factor_backups (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
backup_code VARCHAR(255) NOT NULL,
used_at TIMESTAMP NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user (user_id)
)
";
try {
$pdo->exec($sql);
} catch (Exception $e) {
// Table likely exists
}
}
// ===========================================================================
// LOGIN AUDIT LOGGING HELPERS
// ===========================================================================
/**
* Ensure login audit table exists
*/
function ensure_login_audit_table(): void
{
static $checked = false;
if ($checked) return;
$pdo = db();
$pdo->exec("CREATE TABLE IF NOT EXISTS login_audit (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id INT UNSIGNED NULL,
phone_attempted VARCHAR(30) NOT NULL,
status ENUM('success','invalid_credentials','account_disabled','not_active','not_approved','system_error') NOT NULL DEFAULT 'success',
ip_address VARCHAR(45) NULL,
user_agent VARCHAR(500) NULL,
attempt_count INT UNSIGNED NOT NULL DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_phone (phone_attempted),
INDEX idx_employee_id (employee_id),
INDEX idx_status (status),
INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
$checked = true;
}
/**
* Log employee login attempt
*/
function log_login_attempt(
string $phone,
string $status,
?int $employeeId = null,
?string $ipAddress = null,
?string $userAgent = null
): void
{
ensure_login_audit_table();
$pdo = db();
$ip = $ipAddress ?? ($_SERVER['REMOTE_ADDR'] ?? 'UNKNOWN');
$ua = $userAgent ?? ($_SERVER['HTTP_USER_AGENT'] ?? 'UNKNOWN');
// Check if we have a recent attempt from same phone/IP to increment counter
$stmt = $pdo->prepare(
'SELECT id, attempt_count FROM login_audit
WHERE phone_attempted = ? AND ip_address = ? AND status = ?
AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY created_at DESC LIMIT 1'
);
$stmt->execute([$phone, $ip, $status]);
$existing = $stmt->fetch();
if ($existing) {
// Increment counter on existing record
$updateStmt = $pdo->prepare(
'UPDATE login_audit SET attempt_count = attempt_count + 1, updated_at = NOW()
WHERE id = ?'
);
$updateStmt->execute([$existing['id']]);
} else {
// Create new audit record
$insertStmt = $pdo->prepare(
'INSERT INTO login_audit (employee_id, phone_attempted, status, ip_address, user_agent)
VALUES (?, ?, ?, ?, ?)'
);
$insertStmt->execute([$employeeId, $phone, $status, $ip, $ua]);
}
}
/**
* Get failed login attempts for a phone in the last hour
*/
function get_failed_login_attempts(string $phone, int $minutes = 60): int
{
ensure_login_audit_table();
$pdo = db();
$stmt = $pdo->prepare(
'SELECT SUM(attempt_count) as total FROM login_audit
WHERE phone_attempted = ?
AND status IN ("invalid_credentials", "account_disabled", "not_active", "not_approved")
AND created_at > DATE_SUB(NOW(), INTERVAL ? MINUTE)'
);
$stmt->execute([$phone, $minutes]);
$result = $stmt->fetch();
return (int) ($result['total'] ?? 0);
}
/**
* Get login activity by employee
*/
function get_employee_login_activity(int $employeeId, int $limit = 20): array
{
ensure_login_audit_table();
$pdo = db();
$stmt = $pdo->prepare(
'SELECT * FROM login_audit
WHERE employee_id = ?
ORDER BY created_at DESC
LIMIT ?'
);
$stmt->execute([$employeeId, $limit]);
return $stmt->fetchAll();
}
/**
* Get recent login attempts by inactive/non-approved employees
*/
function get_suspicious_login_attempts(int $minutes = 24 * 60): array
{
ensure_login_audit_table();
$pdo = db();
$stmt = $pdo->prepare(
'SELECT * FROM login_audit
WHERE status IN ("not_active", "account_disabled", "not_approved")
AND created_at > DATE_SUB(NOW(), INTERVAL ? MINUTE)
ORDER BY created_at DESC'
);
$stmt->execute([$minutes]);
return $stmt->fetchAll();
}
/* Initialize security features on first request */
try {
initialize_security_features();
} catch (Exception $e) {
// Silently fail - security initialization errors should not break the app
}