Untitled
unknown
plain_text
10 days ago
65 kB
1
Indexable
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
if (basename(__FILE__) == basename($_SERVER['PHP_SELF'])) {
http_response_code(403);
die('Direct access not allowed.');
}
class User
{
private AppContainer $container;
private PDO $db;
private $user_id;
private Permission $permission;
private Tenant $tenant;
private EmployeeWageRate $employeeWageRate;
private Mail $mail;
public function __construct(AppContainer $container)
{
$this->container = $container;
$this->db = $container->getDb();
$this->permission = $container->getPermission();
$this->tenant = $container->getTenant();
$this->employeeWageRate = $container->getEmployeeWageRate();
$this->mail = $container->getMail();
}
public function login($email, $password, $rememberMe=true)
{
try {
$sth = $this->db->prepare("SELECT * FROM Users WHERE LOWER(email) = LOWER(:email) AND is_active = 1 AND deleted_at IS NULL");
$sth->bindValue(':email', $email, PDO::PARAM_STR);
$sth->execute();
$user = $sth->fetch(PDO::FETCH_OBJ);
if (!$user) {
$this->insertLoginAttempt(null, 'failure', 'User: '.$email.' not found');
header('Content-Type: application/json; charset=utf-8');
return json_encode(['status' => 'error', 'message' => 'Invalid credentials.']);
}
$userId = $user->id;
$tenantId = $user->tenant_id;
$userRole = $user->role;
if ($user->is_blocked) {
$this->insertLoginAttempt($userId, 'failure', 'User is blocked');
header('Content-Type: application/json; charset=utf-8');
return json_encode(['status' => 'error', 'message' => 'Your account is blocked.']);
}
if (!password_verify($password, $user->password)) {
$this->increaseFailedLoginAttempts($userId);
if ($user->failed_login_attempts + 1 >= 5) {
$this->blockUser($userId);
}
$this->insertLoginAttempt($userId, 'failure', 'Invalid password');
header('Content-Type: application/json; charset=utf-8');
return json_encode(['status' => 'error', 'message' => 'Invalid credentials.']);
}
//delete
$this->resetFailedLoginAttempts($userId);
$this->insertLoginAttempt($userId, 'success', null);
$_SESSION['user_id'] = $userId;
$_SESSION['tenant_id'] = $tenantId;
$_SESSION['role'] = $userRole;
if ($rememberMe) {
$this->createRememberMeToken($userId, $tenantId);
}
header('Content-Type: application/json; charset=utf-8');
return json_encode(['status' => 'success', 'message' => 'Login successful.']);
} catch (PDOException $e) {
header('Content-Type: application/json; charset=utf-8');
return json_encode(['status' => 'error', 'message' => 'Database error: ' . $e->getMessage()]);
}
}
public function checkLogin(): object
{
try {
if (!isset($_COOKIE['remember_me'])) {
return (object) ['status' => 404, 'message' => 'remember_me not set.'];
}
$cookieParts = explode(':', $_COOKIE['remember_me']);
if (count($cookieParts) !== 4) {
return (object) ['status' => 400, 'message' => 'Invalid token format.'];
}
list($userId, $tenantId, $token, $hash) = $cookieParts;
$expectedHash = hash_hmac('sha256', "$userId:$tenantId:$token", 'secret_key');
if (!hash_equals($expectedHash, $hash)) {
return (object) ['status' => 403, 'message' => 'Invalid token signature.'];
}
$sth = $this->db->prepare("SELECT * FROM UserTokens WHERE user_id = :user_id AND token = :token");
$sth->bindValue(':user_id', $userId, PDO::PARAM_INT);
$sth->bindValue(':token', $token, PDO::PARAM_STR);
$sth->execute();
$row = $sth->fetch();
if ($sth->rowCount() > 0) {
$currentTime = time();
if ($currentTime < strtotime($row['expiry'])) {
$userData = $this->getUser($tenantId, $userId);
$_SESSION['user_id'] = $userId;
$_SESSION['tenant_id'] = $tenantId;
$_SESSION['role'] = $userData->data->role;
return (object) ['status' => 200, 'token' => $token];
} else {
setcookie('remember_me', '', time() - 3600, "/", "", true, true);
return (object) ['status' => 401, 'message' => 'Token expired.'];
}
}
return (object) ['status' => 403, 'message' => 'Data not found.'];
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
private function insertLoginAttempt($userId, $status, $failureReason = null)
{
try {
$sth = $this->db->prepare("
INSERT INTO LoginLogs (user_id, status, failure_reason, ip_address, user_agent)
VALUES (:user_id, :status, :failure_reason, :ip_address, :user_agent)
");
$sth->bindValue(':user_id', $userId, $userId ? PDO::PARAM_INT : PDO::PARAM_NULL);
$sth->bindValue(':status', $status, PDO::PARAM_STR);
$sth->bindValue(':failure_reason', $failureReason, PDO::PARAM_STR);
$sth->bindValue(':ip_address', $_SERVER['REMOTE_ADDR'], PDO::PARAM_STR);
$sth->bindValue(':user_agent', $_SERVER['HTTP_USER_AGENT'], PDO::PARAM_STR);
$sth->execute();
if ($sth->rowCount() > 0) {
return json_encode(['status' => 'success', 'message' => 'Login log successfuly inserted.']);
} else {
return json_encode(['status' => 'failure', 'message' => 'Failed to insert login log.']);
}
} catch (PDOException $e) {
return json_encode(['status' => 'error', 'message' => 'Database error: ' . $e->getMessage()]);
}
}
private function increaseFailedLoginAttempts($userId)
{
try {
$sth = $this->db->prepare("UPDATE Users SET failed_login_attempts = failed_login_attempts + 1 WHERE id = :user_id");
$sth->bindValue(':user_id', $userId, PDO::PARAM_INT);
$sth->execute();
if ($sth->rowCount() > 0) {
return json_encode(['status' => 'success', 'message' => 'Failed login attempts successfully updated.']);
} else {
return json_encode(['status' => 'failure', 'message' => 'Failed to update failed login attempts.']);
}
} catch (PDOException $e) {
return json_encode(['status' => 'error', 'message' => 'Database error: ' . $e->getMessage()]);
}
}
private function resetFailedLoginAttempts($userId)
{
try {
$sth = $this->db->prepare("UPDATE Users SET failed_login_attempts = 0 WHERE id = :user_id");
$sth->bindValue(':user_id', $userId, PDO::PARAM_INT);
$sth->execute();
if ($sth->rowCount() > 0) {
return json_encode(['status' => 'success', 'message' => 'Failed login attempts successfully reset.']);
} else {
return json_encode(['status' => 'failure', 'message' => 'Failed to reset failed login attempts.']);
}
} catch (PDOException $e) {
return json_encode(['status' => 'error', 'message' => 'Database error: ' . $e->getMessage()]);
}
}
public function blockUser($userId)
{
try {
$sth = $this->db->prepare("UPDATE Users SET is_blocked = 1 WHERE id = :user_id");
$sth->bindValue(':user_id', $userId, PDO::PARAM_INT);
$sth->execute();
if ($sth->rowCount() > 0) {
return json_encode(['status' => 'success', 'message' => 'User successfully blocked.']);
} else {
return json_encode(['status' => 'failure', 'message' => 'Failed to block the user.']);
}
} catch (PDOException $e) {
return json_encode(['status' => 'error', 'message' => 'Database error: ' . $e->getMessage()]);
}
}
public function unblockUser($userId)
{
try {
$sth = $this->db->prepare("UPDATE Users SET is_blocked = 0, failed_login_attempts = 0 WHERE id = :user_id");
$sth->bindValue(':user_id', $userId, PDO::PARAM_INT);
$sth->execute();
if ($sth->rowCount() > 0) {
return json_encode(['status' => 'success', 'message' => 'User successfully unblocked.']);
} else {
return json_encode(['status' => 'failure', 'message' => 'Failed to unblock the user.']);
}
} catch (PDOException $e) {
return json_encode(['status' => 'error', 'message' => 'Database error: ' . $e->getMessage()]);
}
}
private function createRememberMeToken($userId, $tenantId)
{
try {
$token = bin2hex(random_bytes(32)); // 64-character token
$expiry = date('Y-m-d H:i:s', strtotime('+30 days'));
$sth = $this->db->prepare("SELECT COUNT(*) FROM UserTokens WHERE user_id = :user_id");
$sth->bindValue(':user_id', $userId, PDO::PARAM_INT);
$sth->execute();
$exists = $sth->fetchColumn() > 0;
if ($exists) {
$sth = $this->db->prepare("
UPDATE UserTokens
SET token = :token, expiry = :expiry
WHERE
tenant_id = :tenant_id AND
user_id = :user_id
");
} else {
$sth = $this->db->prepare("
INSERT INTO UserTokens (tenant_id, user_id, token, expiry)
VALUES (:tenant_id, :user_id, :token, :expiry)
");
}
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->bindValue(':user_id', $userId, PDO::PARAM_INT);
$sth->bindValue(':token', $token, PDO::PARAM_STR);
$sth->bindValue(':expiry', $expiry, PDO::PARAM_STR);
$sth->execute();
if ($sth->rowCount() > 0) {
$cookieValue = $userId . ':' . $tenantId . ':' . $token;
$hashedValue = hash_hmac('sha256', $cookieValue, 'secret_key');
$cookieValue = $cookieValue . ':' . $hashedValue;
setcookie('remember_me', $cookieValue, strtotime($expiry), '/', '', true, true);
return json_encode(['status' => 'success', 'message' => 'Remember me token successfully inserted.']);
} else {
return json_encode(['status' => 'failure', 'message' => 'Failed to insert remember me token.']);
}
} catch (Exception $e) {
return json_encode(['status' => 'error', 'message' => 'Failed to create remember me token: ' . $e->getMessage()]);
}
}
public function logout()
{
try {
if (isset($_COOKIE['remember_me'])) {
$cookieValue = urldecode($_COOKIE['remember_me']);
$cookieParts = explode(':', $cookieValue);
$cookieParts = explode(':', $_COOKIE['remember_me']);
if (count($cookieParts) === 4) {
list($userId, $tenantId, $token, $hash) = $cookieParts;
$sth = $this->db->prepare("
DELETE FROM UserTokens
WHERE
tenant_id = :tenant_id AND
user_id = :user_id AND
token = :token
");
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->bindValue(':user_id', $userId, PDO::PARAM_INT);
$sth->bindValue(':token', $token, PDO::PARAM_STR);
$sth->execute();
}
}
setcookie('remember_me', '', time() - 3600, '/', '', true, true);
unset($_SESSION['user_id']);
unset($_SESSION['tenant_id']);
session_destroy();
header('Content-Type: application/json; charset=utf-8');
return json_encode(['status' => 200, 'message' => 'Logout successful.']);
} catch (Exception $e) {
header('Content-Type: application/json; charset=utf-8');
return json_encode(['status' => 403, 'message' => 'Logout failed: ' . $e->getMessage()]);
}
}
public function setPassword($userId, $password, $newPassword1, $newPassword2)
{
try {
$password = password_hash($password, PASSWORD_DEFAULT);
$newPassword1 = password_hash($newPassword1, PASSWORD_DEFAULT);
$newPassword2 = password_hash($newPassword2, PASSWORD_DEFAULT);
if($newPassword1 != $newPassword2) {
return json_encode(['status' => 'failure', 'message' => 'Password 1 and 2 does not match.']);
}
$sth = $this->db->prepare('UPDATE Users SET password = :newPassword WHERE id = :user_id AND password = :password');
$sth->bindValue(':user_id', $userId, PDO::PARAM_INT);
$sth->bindValue(':oldPassword', $password, PDO::PARAM_STR);
$sth->bindValue(':newPassword', $newPassword1, PDO::PARAM_STR);
$sth->execute();
if ($sth->rowCount() > 0) {
return json_encode(['status' => 200, 'message' => 'Password successfuly updated.']);
} else {
return json_encode(['status' => 403, 'message' => 'Failed to update the password.']);
}
} catch (PDOException $e) {
return json_encode(['status' => 'error', 'message' => 'Failed to update a password: ' . $e->getMessage()]);
}
}
public function getUser(int $tenantId, int $userId, ?int $employeeId = null): object
{
try {
$db = $this->container->getDb();
$query = "SELECT role FROM Users WHERE tenant_id = :tenant_id AND id = :user_id";
$roleStmt = $this->db->prepare($query);
$roleStmt->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$roleStmt->bindValue(':user_id', $userId, PDO::PARAM_INT);
$roleStmt->execute();
$userRole = $roleStmt->fetchColumn();
$useEmployeeId = ($employeeId !== null && in_array($userRole, ['ADMIN', 'ACCOUNTANT']));
$sth = $this->db->prepare("
SELECT
u.tenant_id,
u.email,
u.role,
u.employee_id_number,
u.first_name,
u.last_name,
u.gender,
u.language_id,
u.permanent_address,
u.permanent_postal_code,
u.permanent_city,
u.permanent_country_id,
u.temporary_address,
u.temporary_postal_code,
u.temporary_city,
u.temporary_country_id,
u.job_department_id,
u.job_position_id,
u.supervisor_user_id,
u.birth_date,
u.birth_place,
u.birth_country_id,
u.tax_number,
u.personal_id_number,
u.citizenship_country_id,
u.education,
u.base_gross_salary,
u.salary_bonus_1,
u.salary_bonus_2,
u.salary_bonus_3,
u.hourly_wage,
u.personal_email,
u.personal_phone_number,
u.work_email,
u.work_phone_number,
u.note,
u.is_active,
u.is_blocked,
u.employment_type_id,
u.default_location,
u.default_work_description,
CONCAT(u.first_name, ' ', u.last_name) AS user_full_name,
DATE_FORMAT(u.birth_date, '%d.%m.%Y') AS birth_date_formatted,
DATE_FORMAT(u.employment_date, '%d.%m.%Y') AS employment_date_formatted,
lbd.value AS default_lunch_break_duration,
COALESCE(FORMAT(cd.distance * 2, 2), NULL) AS commute_distance,
et.work_hours_capacity AS work_hours_capacity,
u.hours_balance,
REPLACE(FORMAT(u.hours_balance, 2), '.', ',') AS hours_balance_formatted,
CONCAT(
CASE
WHEN u.hours_balance < 0 THEN CONCAT('-', FLOOR(ABS(u.hours_balance)), ' h ')
ELSE CONCAT(FLOOR(u.hours_balance), ' h ')
END,
CASE
WHEN u.hours_balance < 0 THEN CONCAT(ROUND((ABS(u.hours_balance) - FLOOR(ABS(u.hours_balance))) * 60), ' min')
ELSE CONCAT(ROUND((u.hours_balance - FLOOR(u.hours_balance)) * 60), ' min')
END
) AS hours_minutes_balance_formatted,
CASE
WHEN u.is_active = 1 THEN 'Active'
ELSE 'Inactive'
END AS is_active_text,
CASE
WHEN u.is_blocked = 0 THEN 'Active'
ELSE 'Blocked'
END AS is_blocked_text,
u.employment_date,
DATE_FORMAT(COALESCE(ts.default_work_start_time, NOW()), '%H:%i:%s') AS default_work_start_time,
u.auto_log_work_hours,
DATE_FORMAT(u.auto_log_work_time_start, '%H:%i') AS auto_log_work_time_start,
u.password_reset,
u.allow_instant_work_log,
l.code AS language_code,
cb.name AS birth_country,
cc.name AS citizenship_country,
cpa.name AS permanent_country,
cta.name AS temporary_country,
et.type AS employment_type,
jd.job_department,
jb.job_position,
CONCAT(s.first_name, ' ', s.last_name) AS supervisor_full_name,
u.iban
FROM Users u
LEFT JOIN TenantLunchBreakDurations tlbd ON tlbd.employment_type_id = u.employment_type_id AND tlbd.is_default = 1 AND tlbd.tenant_id = :tenant_id
LEFT JOIN LunchBreakDuration lbd ON lbd.id = tlbd.lunch_break_duration_id
LEFT JOIN (
SELECT user_id, distance
FROM CommuteDistances
WHERE is_default = 1
) cd ON cd.user_id = u.id
LEFT JOIN EmploymentTypes et ON et.id = u.employment_type_id
LEFT JOIN TenantSettings ts ON ts.id = u.tenant_id
LEFT JOIN Languages l ON l.id = u.language_id
LEFT JOIN Countries cb ON cb.id = u.birth_country_id
LEFT JOIN Countries cc ON cc.id = u.citizenship_country_id
LEFT JOIN Countries cpa ON cpa.id = u.permanent_country_id
LEFT JOIN Countries cta ON cta.id = u.temporary_country_id
LEFT JOIN JobDepartments jd ON jd.id = u.job_department_id
LEFT JOIN JobPositions jb ON jb.id = u.job_position_id
LEFT JOIN Users s ON s.id = u.supervisor_user_id
WHERE
u.tenant_id = :tenant_id
AND u.id = :selected_user_id
AND u.deleted_at IS NULL
GROUP BY u.id;
");
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->bindValue(':selected_user_id', $useEmployeeId ? $employeeId : $userId, PDO::PARAM_INT);
$sth->execute();
$result = $sth->fetch(PDO::FETCH_OBJ);
if ($sth->rowCount() > 0) {
return (object) ['status' => 200, 'data' => $result];
} else {
return (object) ['status' => 404, 'message' => 'User not found.'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
public function getUsers(int $tenantId, ?int $userId = null): object
{
try {
$sth = $this->db->prepare("
SELECT
u.*,
CONCAT(u.first_name, ' ', u.last_name) AS employee_full_name,
u.id,
u.tenant_id,
u.email,
role,
u.first_name,
u.last_name,
u.employment_type_id,
et.work_hours_capacity,
et.type,
u.employment_date,
u.auto_log_work_hours,
l.code AS language_code,
jd.job_department,
jp.job_position,
DATE_FORMAT(u.birth_date, '%d.%m.%Y') AS birth_date_formatted,
DATE_FORMAT(u.employment_date, '%d.%m.%Y') AS employment_date_formatted,
FORMAT(u.base_gross_salary, 2, 'de_DE') AS base_gross_salary_formatted
FROM Users u
LEFT JOIN EmploymentTypes et ON et.id = u.employment_type_id
LEFT JOIN Languages l ON l.id = u.language_id
LEFT JOIN JobDepartments jd ON jd.id = u.job_department_id
LEFT JOIN JobPositions jp ON jp.id = u.job_position_id
WHERE
u.tenant_id = :tenant_id AND
u.is_active = 1
ORDER BY et.work_hours_capacity DESC, employee_full_name ASC
");
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_OBJ);
if ($sth->rowCount() > 0) {
return (object) ['status' => 200, 'data' => $result];
} else {
return (object) ['status' => 404, 'message' => 'No active users found.'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
public function getSupervisors(int $tenantId, int $userId): object
{
try {
$sth = $this->db->prepare("
SELECT
u.id,
CONCAT(u.first_name, ' ', u.last_name) AS user_full_name
FROM Users u
WHERE
u.tenant_id = :tenant_id AND
u.is_active = 1 AND
u.is_supervisor = 1 AND
u.deleted_at IS NULL
GROUP BY u.id;
");
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_OBJ);
if ($sth->rowCount() > 0) {
return (object) ['status' => 200, 'data' => $result];
} else {
return (object) ['status' => 404, 'message' => 'User not found.'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
public function getEmploymentTypes(int $tenantId, int $userId): object
{
try {
$sth = $this->db->prepare("
SELECT
et.*
FROM EmploymentTypes et
WHERE
et.is_active = 1
ORDER BY et.id;
");
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_OBJ);
if ($sth->rowCount() > 0) {
return (object) ['status' => 200, 'data' => $result];
} else {
return (object) ['status' => 404, 'message' => 'User not found.'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
private function getuserIdByEmail(string $email): object
{
try {
$sth = $this->db->prepare("
SELECT id, tenant_id
FROM Users
WHERE
email = :email
");
$sth->bindValue(':email', $email, PDO::PARAM_STR);
$sth->execute();
$result = $sth->fetch(PDO::FETCH_OBJ);
if ($sth->rowCount() > 0) {
return (object) ['status' => 200, 'user_id' => $result->id, 'tenant_id' => $result->tenant_id];
} else {
return (object) ['status' => 404, 'message' => 'User not found.'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
public function changePassword(int $tenantId, int $userId, string $oldPassword, string $newPassword, string $confirmPassword): object
{
try {
$sth = $this->db->prepare("
SELECT password
FROM Users
WHERE id = :user_id AND tenant_id = :tenant_id
");
$sth->bindValue(':user_id', $userId, PDO::PARAM_INT);
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->execute();
$user = $sth->fetch(PDO::FETCH_OBJ);
if (!$user) {
return (object) ['status' => 404, 'message' => 'User not found.'];
}
if (!password_verify($oldPassword, $user->password)) {
return (object) ['status' => 400, 'message' => 'Incorrect old password.'];
}
if ($newPassword !== $confirmPassword) {
return (object) ['status' => 400, 'message' => 'New password and confirmation do not match.'];
}
$hashedPassword = password_hash($newPassword, PASSWORD_DEFAULT);
$sth = $this->db->prepare("
UPDATE Users
SET
password = :new_password,
password_reset = 1,
last_password_reset_date = NOW(),
modified_by = :modified_by,
modified_at = NOW()
WHERE id = :user_id AND tenant_id = :tenant_id
");
$sth->bindValue(':new_password', $hashedPassword, PDO::PARAM_STR);
$sth->bindValue(':modified_by', $userId, PDO::PARAM_INT);
$sth->bindValue(':user_id', $userId, PDO::PARAM_INT);
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->execute();
if ($sth->rowCount() > 0) {
return (object) ['status' => 200, 'message' => 'Password changed successfully.', 'translate' => 'password_changed_successfully'];
} else {
return (object) ['status' => 400, 'message' => 'Password change failed.', 'translate' => 'password_changed_failed'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
public function forgotPassword(string $email): object
{
try {
$mail = $this->container->getMail();
if ($this->checkUserEmailExists($email)) {
$userIdObject = $this->getuserIdByEmail($email);
$userId = $userIdObject->user_id;
$token = bin2hex(random_bytes(32));
$hashedToken = password_hash($token, PASSWORD_DEFAULT);
$expiresAt = date('Y-m-d H:i:s', strtotime('+1 hour'));
$sth = $this->db->prepare("DELETE FROM PasswordResets WHERE email = :email");
$sth->bindValue(':email', $email, PDO::PARAM_STR);
$sth->execute();
$sth = $this->db->prepare("
INSERT INTO PasswordResets (user_id, email, ip_address, user_agent, token, expires_at)
VALUES (:user_id, :email, :ip_address, :user_agent, :token, :expires_at)
");
$sth->bindValue(':user_id', $userId, PDO::PARAM_INT);
$sth->bindValue(':email', $email, PDO::PARAM_STR);
$sth->bindValue(':ip_address', $_SERVER['REMOTE_ADDR'], PDO::PARAM_STR);
$sth->bindValue(':user_agent', $_SERVER['HTTP_USER_AGENT'], PDO::PARAM_STR);
$sth->bindValue(':token', $hashedToken, PDO::PARAM_STR);
$sth->bindValue(':expires_at', $expiresAt, PDO::PARAM_STR);
$sth->execute();
if ($sth->rowCount() > 0) {
$mail->sendForgotPasswordToken($email, urlencode($token));
}
}
return (object) ['status' => 200, 'message' => 'If an account associated with the entered email exists, we will send further instructions to your email.', 'translate' => 'forgot_password_submited_text'];
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
} catch (Exception $e) {
return (object) ['status' => 500, 'message' => 'Error: ' . $e->getMessage()];
}
}
public function resetPassword(string $token, string $email, string $newPassword, string $confirmPassword): object
{
try {
$sth = $this->db->prepare("SELECT email, expires_at FROM PasswordResets WHERE token = :token");
$sth = $this->db->prepare("
SELECT token, email
FROM PasswordResets
WHERE
email = :email AND
expires_at >= NOW()");
$sth->bindValue(':email', $email, PDO::PARAM_STR);
$sth->execute();
$resetEntry = $sth->fetch(PDO::FETCH_ASSOC);
if (!$resetEntry) {
return (object) ['status' => 400, 'message' => 'Invalid or expired token. Please request a new password reset by using the "Forgot Password" feature again.', 'translate' => 'invalid_or_expired_token'];
}
if (!password_verify($token, $resetEntry['token'])) {
return (object) ['status' => 400, 'message' => 'Invalid token.', 'translate' => 'invalid_token'];
}
//$email = $resetEntry['email'];
$hashedPassword = password_hash($newPassword, PASSWORD_DEFAULT);
$currentDateTime = date('Y-m-d H:i:s');
$sth = $this->db->prepare("
UPDATE Users
SET
password = :password,
password_reset = 1,
is_blocked = 0,
failed_login_attempts = 0,
last_password_reset_date = :last_password_reset_date
WHERE
email = :email AND
is_active = 1 AND
deleted_at IS NULL
");
$sth->bindValue(':password', $hashedPassword, PDO::PARAM_STR);
$sth->bindValue(':last_password_reset_date', $currentDateTime, PDO::PARAM_STR);
$sth->bindValue(':email', $email, PDO::PARAM_STR);
$sth->execute();
if ($sth->rowCount() > 0) {
return (object) ['status' => 200, 'message' => 'Password successfully reset.', 'message' => 'password_reset_successfully'];
} else {
return (object) ['status' => 400, 'message' => 'Password reset failed.', 'translate' => 'password_reset_failed'];
}
}
catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
public function getUserEmploymentType($userId)
{
try {
$sth = $this->db->prepare("
SELECT
*,
CONCAT(Users.first_name, ' ', Users.last_name) AS user_full_name
FROM Users
WHERE id = :user_id
");
$sth->bindValue(':user_id', $userId, PDO::PARAM_INT);
$sth->execute();
$user = $sth->fetch(PDO::FETCH_OBJ);
if ($sth->rowCount() > 0) {
return json_encode(['status' => 'success', 'data' => $user]);
} else {
return json_encode(['status' => 'failure', 'message' => 'User not found.']);
}
} catch (PDOException $e) {
return json_encode(['status' => 'error', 'message' => 'Database error: ' . $e->getMessage()]);
}
}
public function getNotificationReceivers($tenantId): object
{
try {
$sth = $this->db->prepare("
SELECT
u.email,
u.daily_summary_notification,
u.work_session_update_notification,
u.work_session_delete_notification,
l.code AS language_code
FROM Users u
LEFT JOIN Languages l ON l.id = u.language_id
WHERE
u.tenant_id = :tenant_id AND
u.role = 'ADMIN' AND
u.is_active = 1
");
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_OBJ);
if ($sth->rowCount() > 0) {
return (object) ['status' => 200, 'data' => $result];
} else {
return (object) ['status' => 404, 'message' => 'User not found.'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
// Admin section
public function insertEmployee(int $tenantId, int $userId, array $data): object
{
$permissionCheck = $this->permission->checkPermission($tenantId, $userId, 'Can add new users');
if ($permissionCheck->status !== 200) {
return $permissionCheck;
}
$permissionCheck = $this->permission->checkPermission($tenantId, $userId, 'Can add new role has user');
if ($permissionCheck->status !== 200) {
return $permissionCheck;
}
$permissionCheck = $this->permission->checkPermission($tenantId, $userId, 'Can add new commute distances');
if ($permissionCheck->status !== 200) {
return $permissionCheck;
}
$permissionCheck = $this->permission->checkPermission($tenantId, $userId, 'Can add new employee annual leave');
if ($permissionCheck->status !== 200) {
return $permissionCheck;
}
$canAddNewUser = $this->tenant->canAddNewUser($tenantId);
if ($canAddNewUser->status !== 200) {
return $canAddNewUser;
}
if ($this->checkUserEmailExists($data['login_email_address'])) {
return (object) ['status' => 409, 'message' => 'User with this email already exists.', 'translate' => 'user_email_exists'];
}
try {
$sth = $this->db->prepare("
INSERT INTO Users (
tenant_id,
email,
password,
first_name,
last_name,
birth_date,
birth_place,
birth_country_id,
citizenship_country_id,
employee_id_number,
personal_id_number,
tax_number,
education,
permanent_address,
permanent_postal_code,
permanent_city,
permanent_country_id,
temporary_address,
temporary_postal_code,
temporary_city,
temporary_country_id,
personal_email,
personal_phone_number,
work_email,
work_phone_number,
employment_type_id,
employment_date,
job_department_id,
job_position_id,
supervisor_user_id,
default_work_description,
iban,
is_active,
is_blocked,
note,
created_by,
created_at
) VALUES (
:tenant_id,
:login_email_address,
:password,
:first_name,
:last_name,
:birth_date,
:birth_place,
:birth_country_id,
:citizenship_country_id,
:employee_id_number,
:personal_id_number,
:tax_number,
:education,
:permanent_address,
:permanent_postal_code,
:permanent_city,
:permanent_country_id,
:temporary_address,
:temporary_postal_code,
:temporary_city,
:temporary_country_id,
:personal_email,
:personal_phone_number,
:work_email,
:work_phone_number,
:employment_type_id,
:employment_date,
:job_department_id,
:job_position_id,
:supervisor_user_id,
:default_work_description,
:iban,
:is_active,
:is_blocked,
:note,
:created_by,
NOW()
)");
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->bindValue(':login_email_address', $data['login_email_address'], PDO::PARAM_STR);
$sth->bindValue(':password', password_hash($data['password'], PASSWORD_DEFAULT), PDO::PARAM_STR);
$sth->bindValue(':first_name', $data['first_name'], PDO::PARAM_STR);
$sth->bindValue(':last_name', $data['last_name'], PDO::PARAM_STR);
$sth->bindValue(':birth_date', $data['birth_date'], PDO::PARAM_STR);
$sth->bindValue(':birth_place', $data['birth_place'], PDO::PARAM_STR);
$sth->bindValue(':birth_country_id', $data['birth_country'], PDO::PARAM_STR);
$sth->bindValue(':citizenship_country_id', $data['citizenship_country'], PDO::PARAM_STR);
$sth->bindValue(':employee_id_number', $data['employee_id_number'], PDO::PARAM_STR);
$sth->bindValue(':personal_id_number', $data['personal_id_number'], PDO::PARAM_STR);
$sth->bindValue(':tax_number', $data['tax_number'], PDO::PARAM_STR);
$sth->bindValue(':education', $data['education'], PDO::PARAM_STR);
$sth->bindValue(':permanent_address', $data['permanent_address'], PDO::PARAM_STR);
$sth->bindValue(':permanent_postal_code', $data['permanent_postal_code'], PDO::PARAM_STR);
$sth->bindValue(':permanent_city', $data['permanent_city'], PDO::PARAM_STR);
$sth->bindValue(':permanent_country_id', $data['permanent_country'], PDO::PARAM_STR);
$sth->bindValue(':temporary_address', $data['temporary_address'], PDO::PARAM_STR);
$sth->bindValue(':temporary_postal_code', $data['temporary_postal_code'], PDO::PARAM_STR);
$sth->bindValue(':temporary_city', $data['temporary_city'], PDO::PARAM_STR);
$sth->bindValue(':temporary_country_id', $data['temporary_country'], PDO::PARAM_STR);
$sth->bindValue(':personal_email', $data['personal_email'], PDO::PARAM_STR);
$sth->bindValue(':personal_phone_number', $data['personal_phone_number'], PDO::PARAM_STR);
$sth->bindValue(':work_email', $data['work_email'], PDO::PARAM_STR);
$sth->bindValue(':work_phone_number', $data['work_phone_number'], PDO::PARAM_STR);
$sth->bindValue(':employment_type_id', $data['employment_type'], PDO::PARAM_STR);
$sth->bindValue(':employment_date', $data['employment_date'], PDO::PARAM_STR);
$sth->bindValue(':job_department_id', $data['job_department'], PDO::PARAM_STR);
$sth->bindValue(':job_position_id', $data['job_position'], PDO::PARAM_STR);
$sth->bindValue(':supervisor_user_id', $data['supervisor'], PDO::PARAM_STR);
$sth->bindValue(':default_work_description', $data['default_work_description'], PDO::PARAM_STR);
$sth->bindValue(':iban', $data['iban'], PDO::PARAM_STR);
$sth->bindValue(':is_active', $data['employee_status'], PDO::PARAM_STR);
$sth->bindValue(':is_blocked', $data['account_status'], PDO::PARAM_STR);
$sth->bindValue(':note', $data['note'], PDO::PARAM_STR);
$sth->bindValue(':created_by', $userId, PDO::PARAM_INT);
$sth->execute();
if ($sth->rowCount() > 0) {
$employeeId = $db->lastInsertId();
$employeeData = [
'user_id' => $employeeId,
'commute_location_id' => $data['commute_location_id'],
'distance' => $data['commute_distance'],
'is_default' => 1,
'annual_leave_year' => $data['annual_leave_year'],
'annual_leave_days' => $data['annual_leave_days'],
'effective_date' => $data['employment_date'],
'employee_id' => $employeeId,
'base_gross_salary' => $data['base_gross_salary'],
'salary_bonus_1' => $data['salary_bonus_1'],
'salary_bonus_1' => $data['salary_bonus_1'],
'salary_bonus_2' => $data['salary_bonus_2'],
'salary_bonus_3' => $data['salary_bonus_3'],
'hourly_wage' => $data['hourly_wage'],
'regular_overtime_hourly_rate' => $data['regular_overtime_hourly_rate'],
'special_overtime_hourly_rate' => $data['special_overtime_hourly_rate'],
];
$insertRoleHasUserStatus = $this->insertRoleHasUser($tenantId, $userId, $data['user_role'], $employeeId);
if($data['employment_type'] < 3) {
$insertCommuteDistanceStatus = $this->insertCommuteDistance($tenantId, $userId, $employeeData);
$insertEmployeeAnnualLeaveStatus = $this->insertEmployeeAnnualLeave($tenantId, $userId, $employeeData);
$employeeWageRateStatus = $this->employeeWageRate->insertEmployeeWageRate($tenantId, $userId, $employeeData);
if($insertCommuteDistanceStatus->status == 201 && $insertRoleHasUserStatus->status == 201 && $insertEmployeeAnnualLeaveStatus->status == 201 && $employeeWageRateStatus->status == 201) {
return (object) ['status' => 201, 'message' => 'Employee was successfully inserted.', 'translate' => 'employee_was_successfully_inserted'];
}
else {
return (object) ['status' => 404, 'message' => 'Error. Check for details.', 'insertCommuteDistanceStatus' => $insertCommuteDistanceStatus, 'insertRoleHasUserStatus' => $insertRoleHasUserStatus, 'insertEmployeeAnnualLeaveStatus' => $insertEmployeeAnnualLeaveStatus, 'employeeWageRateStatus' => $employeeWageRateStatus];
}
}
else {
return (object) ['status' => 201, 'message' => 'Employee was successfully inserted.', 'translate' => 'employee_was_successfully_inserted'];
}
} else {
return (object) ['status' => 404, 'message' => 'Error: employee not inserted successfully.', 'translate' => 'employee_not_inserted_successfully'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
public function checkUserEmailExists(string $email): bool
{
try {
$query = "
SELECT COUNT(*)
FROM Users
WHERE
email = :email AND
deleted_at IS NULL AND
is_active = 1
";
$sth = $this->db->prepare($query);
$sth->bindValue(':email', $email, PDO::PARAM_STR);
$sth->execute();
$count = $sth->fetchColumn();
return $count > 0;
} catch (PDOException $e) {
return false;
}
}
public function updateEmployee(int $tenantId, int $userId, array $data): object
{
$permissionCheck = $this->permission->checkPermission($tenantId, $userId, 'Can update users');
if ($permissionCheck->status !== 200) {
return $permissionCheck;
}
try {
$sth = $this->db->prepare("
UPDATE Users
SET
first_name = :first_name,
last_name = :last_name,
birth_date = :birth_date,
birth_place = :birth_place,
birth_country_id = :birth_country_id,
citizenship_country_id = :citizenship_country_id,
employee_id_number = :employee_id_number,
personal_id_number = :personal_id_number,
tax_number = :tax_number,
education = :education,
permanent_address = :permanent_address,
permanent_postal_code = :permanent_postal_code,
permanent_city = :permanent_city,
permanent_country_id = :permanent_country_id,
temporary_address = :temporary_address,
temporary_postal_code = :temporary_postal_code,
temporary_city = :temporary_city,
temporary_country_id = :temporary_country_id,
personal_email = :personal_email,
personal_phone_number = :personal_phone_number,
work_email = :work_email,
work_phone_number = :work_phone_number,
employment_type_id = :employment_type_id,
employment_date = :employment_date,
job_department_id = :job_department_id,
job_position_id = :job_position_id,
supervisor_user_id = :supervisor_user_id,
base_gross_salary = :base_gross_salary,
salary_bonus_1 = :salary_bonus_1,
salary_bonus_2 = :salary_bonus_2,
salary_bonus_3 = :salary_bonus_3,
hourly_wage = :hourly_wage,
iban = :iban,
is_active = :is_active,
is_blocked = :is_blocked,
note = :note,
modified_by = :modified_by,
modified_at = NOW()
WHERE
tenant_id = :tenant_id AND
id = :employee_id
");
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->bindValue(':employee_id', $data['employee_id'], PDO::PARAM_STR);
$sth->bindValue(':modified_by', $userId, PDO::PARAM_INT);
$sth->bindValue(':first_name', $data['first_name'], PDO::PARAM_STR);
$sth->bindValue(':last_name', $data['last_name'], PDO::PARAM_STR);
$sth->bindValue(':birth_date', $data['birth_date'], PDO::PARAM_STR);
$sth->bindValue(':birth_place', $data['birth_place'], PDO::PARAM_STR);
$sth->bindValue(':birth_country_id', $data['birth_country'], PDO::PARAM_STR);
$sth->bindValue(':citizenship_country_id', $data['citizenship_country'], PDO::PARAM_STR);
$sth->bindValue(':employee_id_number', $data['employee_id_number'], PDO::PARAM_STR);
$sth->bindValue(':personal_id_number', $data['personal_id_number'], PDO::PARAM_STR);
$sth->bindValue(':tax_number', $data['tax_number'], PDO::PARAM_STR);
$sth->bindValue(':education', $data['education'], PDO::PARAM_STR);
$sth->bindValue(':permanent_address', $data['permanent_address'], PDO::PARAM_STR);
$sth->bindValue(':permanent_postal_code', $data['permanent_postal_code'], PDO::PARAM_STR);
$sth->bindValue(':permanent_city', $data['permanent_city'], PDO::PARAM_STR);
$sth->bindValue(':permanent_country_id', $data['permanent_country'], PDO::PARAM_STR);
$sth->bindValue(':temporary_address', $data['temporary_address'], PDO::PARAM_STR);
$sth->bindValue(':temporary_postal_code', $data['temporary_postal_code'], PDO::PARAM_STR);
$sth->bindValue(':temporary_city', $data['temporary_city'], PDO::PARAM_STR);
$sth->bindValue(':temporary_country_id', $data['temporary_country'], PDO::PARAM_STR);
$sth->bindValue(':personal_email', $data['personal_email'], PDO::PARAM_STR);
$sth->bindValue(':personal_phone_number', $data['personal_phone_number'], PDO::PARAM_STR);
$sth->bindValue(':work_email', $data['work_email'], PDO::PARAM_STR);
$sth->bindValue(':work_phone_number', $data['work_phone_number'], PDO::PARAM_STR);
$sth->bindValue(':employment_type_id', $data['employment_type'], PDO::PARAM_STR);
$sth->bindValue(':employment_date', $data['employment_date'], PDO::PARAM_STR);
$sth->bindValue(':job_department_id', $data['job_department'], PDO::PARAM_STR);
$sth->bindValue(':job_position_id', $data['job_position'], PDO::PARAM_STR);
$sth->bindValue(':supervisor_user_id', $data['supervisor'], PDO::PARAM_STR);
$sth->bindValue(':base_gross_salary', $data['base_gross_salary'], PDO::PARAM_STR);
$sth->bindValue(':salary_bonus_1', $data['salary_bonus_1'], PDO::PARAM_STR);
$sth->bindValue(':salary_bonus_2', $data['salary_bonus_2'], PDO::PARAM_STR);
$sth->bindValue(':salary_bonus_3', $data['salary_bonus_3'], PDO::PARAM_STR);
$sth->bindValue(':hourly_wage', $data['hourly_wage'], PDO::PARAM_STR);
$sth->bindValue(':iban', $data['iban'], PDO::PARAM_STR);
$sth->bindValue(':is_active', $data['employee_status'], PDO::PARAM_STR);
$sth->bindValue(':is_blocked', $data['account_status'], PDO::PARAM_STR);
$sth->bindValue(':note', $data['note'], PDO::PARAM_STR);
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_OBJ);
if ($sth->rowCount() > 0) {
return (object) ['status' => 200, 'message' => 'Employee was successfully updated.', 'translate' => 'employee_was_successfully_updated'];
} else {
return (object) ['status' => 404, 'message' => 'User not found.', 'translate' => 'user_not_found'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
public function deleteEmployee(int $tenantId, int $userId, int $employeeId): object
{
$permissionCheck = $this->permission->checkPermission($tenantId, $userId, 'Can delete users');
if ($permissionCheck->status !== 200) {
return $permissionCheck;
}
try {
$sth = $this->db->prepare("
UPDATE Users
SET
deleted_by = :deleted_by,
is_active = 0,
deleted_at = NOW()
WHERE
tenant_id = :tenant_id AND
id = :employee_id
");
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->bindValue(':employee_id', $employeeId, PDO::PARAM_INT);
$sth->bindValue(':deleted_by', $userId, PDO::PARAM_INT);
$sth->execute();
if ($sth->rowCount() > 0) {
return (object) ['status' => 200, 'message' => 'Employee was successfully deleted.', 'translate' => 'employee_was_successfully_deleted'];
} else {
return (object) ['status' => 404, 'message' => 'User not found.', 'translate' => 'user_not_found'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
public function insertCommuteDistance(int $tenantId, int $userId, array $data): object
{
$permissionCheck = $this->permission->checkPermission($tenantId, $userId, 'Can add new commute distances');
if ($permissionCheck->status !== 200) {
return $permissionCheck;
}
try {
$sth = $this->db->prepare("
INSERT INTO CommuteDistances (
tenant_id,
commute_location_id,
user_id,
distance,
is_default,
created_by,
created_at
) VALUES (
:tenant_id,
:commute_location_id,
:user_id,
:distance,
:is_default,
:created_by,
NOW()
)");
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->bindValue(':commute_location_id', $data['commute_location_id'], PDO::PARAM_INT);
$sth->bindValue(':user_id', $data['user_id'], PDO::PARAM_INT);
$sth->bindValue(':distance', $data['distance'], PDO::PARAM_STR);
$sth->bindValue(':is_default', $data['is_default'], PDO::PARAM_INT);
$sth->bindValue(':created_by', $userId, PDO::PARAM_INT);
$sth->execute();
if ($sth->rowCount() > 0) {
return (object) ['status' => 201, 'message' => 'Commute distance inserted successfully.', 'translate' => 'commute_distance_inserted_successfully'];
} else {
return (object) ['status' => 404, 'message' => 'Error: commute distance not inserted successfully.', 'translate' => 'commute_distance_not_inserted_successfully'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
public function insertRoleHasUser(int $tenantId, int $userId, int $roleId, int $employeeId): object
{
$permissionCheck = $this->permission->checkPermission($tenantId, $userId, 'Can add new role has user');
if ($permissionCheck->status !== 200) {
return $permissionCheck;
}
try {
$sth = $this->db->prepare("
INSERT INTO RoleHasUser (
role_id,
tenant_id,
user_id
) VALUES (
:role_id,
:tenant_id,
:user_id
)");
$sth->bindValue(':role_id', $roleId, PDO::PARAM_INT);
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->bindValue(':user_id', $employeeId, PDO::PARAM_INT);
$sth->execute();
if ($sth->rowCount() > 0) {
return (object) ['status' => 201, 'message' => 'Role has been successfully allocated to user.', 'translate' => 'role_has_been_successfully_allocated_to_user'];
} else {
return (object) ['status' => 404, 'message' => 'Error: role was not successfully allocated to user.', 'translate' => 'role_was_not_successfully_allocated_to_user'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
public function getUserRoles(int $tenantId, int $userId): object
{
$permissionCheck = $this->permission->checkPermission($tenantId, $userId, 'Can view roles');
if ($permissionCheck->status !== 200) {
return $permissionCheck;
}
try {
$sth = $this->db->prepare("
SELECT
r.*
FROM Roles r
WHERE
r.is_active = 1
");
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_OBJ);
if ($sth->rowCount() > 0) {
return (object) ['status' => 200, 'data' => $result];
} else {
return (object) ['status' => 404, 'message' => 'Roles not found.'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
public function insertEmployeeAnnualLeave(int $tenantId, int $userId, array $data): object
{
$permissionCheck = $this->permission->checkPermission($tenantId, $userId, 'Can add new role has user');
if ($permissionCheck->status !== 200) {
return $permissionCheck;
}
try {
$sth = $this->db->prepare("
INSERT INTO EmployeeAnnualLeave (
tenant_id,
user_id,
year,
annual_leave_days,
used_days,
is_expired,
created_by,
created_at
) VALUES (
:tenant_id,
:user_id,
:year,
:annual_leave_days,
:used_days,
:is_expired,
:created_by,
NOW()
)");
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->bindValue(':user_id', $data['user_id'], PDO::PARAM_INT);
$sth->bindValue(':year', $data['annual_leave_year'], PDO::PARAM_STR);
$sth->bindValue(':annual_leave_days', $data['annual_leave_days'], PDO::PARAM_STR);
$sth->bindValue(':used_days', 0, PDO::PARAM_STR);
$sth->bindValue(':is_expired', 0, PDO::PARAM_INT);
$sth->bindValue(':created_by', $userId, PDO::PARAM_INT);
$sth->execute();
if ($sth->rowCount() > 0) {
return (object) ['status' => 201, 'message' => 'Annual leave been successfully allocated to user.', 'translate' => 'annual_leave_has_been_successfully_allocated_to_user'];
} else {
return (object) ['status' => 404, 'message' => 'Error: annual leave was not successfully allocated to user.', 'translate' => 'annual_leave_was_not_successfully_allocated_to_user'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
public function getEmployeesHoursBalance(int $tenantId, string $userId): object
{
try {
$query = "
SELECT
CONCAT(u.first_name, ' ', u.last_name) AS employee_full_name,
u.hours_balance AS posted_hours_balance,
FORMAT(u.hours_balance, 2, 'de_DE') AS posted_hours_balance_formatted,
IFNULL(SUM(ds.non_working_hours), 0) AS total_non_working_hours,
IFNULL(SUM(ds.surplus_hours), 0) AS total_surplus_hours,
IFNULL(SUM(ds.surplus_hours), 0) - IFNULL(SUM(ds.non_working_hours), 0) AS current_balance,
(u.hours_balance + (IFNULL(SUM(ds.surplus_hours), 0) - IFNULL(SUM(ds.non_working_hours), 0))) AS actual_hours_balance,
FORMAT((u.hours_balance + (IFNULL(SUM(ds.surplus_hours), 0) - IFNULL(SUM(ds.non_working_hours), 0))), 2, 'de_DE') AS actual_balance_formatted
FROM Users u
LEFT JOIN DailySummaries ds ON u.id = ds.user_id AND ds.date BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND LAST_DAY(CURDATE())
WHERE
u.tenant_id = :tenant_id AND
u.employment_type_id < 3 AND
u.is_active = 1 AND
u.deleted_at IS NULL AND
ds.deleted_at IS NULL
GROUP BY u.id
ORDER BY employee_full_name ASC;
";
$sth = $this->db->prepare($query);
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_OBJ);
if ($sth->rowCount() > 0) {
return (object) ['status' => 200, 'data' => $result];
} else {
return (object) ['status' => 404, 'message' => 'Employees not found.'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
public function updateUserHoursBalance(int $tenantId, int $userId, int $employeeId, float $hoursBalance): Object
{
try {
$query = "
UPDATE Users u
SET
u.hours_balance = :hours_balance,
u.modified_by = :modified_by,
u.modified_at = NOW()
WHERE
u.tenant_id = :tenant_id AND
u.id = :user_id AND
u.hours_balance >= 0
";
$sth = $this->db->prepare($query);
$sth->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
$sth->bindValue(':modified_by', $userId, PDO::PARAM_INT);
$sth->bindValue(':user_id', $employeeId, PDO::PARAM_INT);
$sth->bindValue(':hours_balance', $hoursBalance, PDO::PARAM_STR);
$sth->execute();
if ($sth->rowCount() > 0) {
return (object) ['status' => 200, 'message' => 'Balance hours successfully updated.'];
} else {
return (object) ['status' => 404, 'message' => 'Employee not found.'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
public function checkUserIpAddress(int $tenantId, int $userId): Object
{
try {
$tenantSettings = $this->tenant->getTenantSettings($tenantId);
$companyIpAddress = $tenantSettings->data->company_ip;
$userIpAddress = $_SERVER['REMOTE_ADDR'];
if ($companyIpAddress == $userIpAddress ) {
return (object) ['status' => 200, 'userOnLocation' => true, 'message' => "You are at the company location.", 'translate' => 'you_are_at_the_company_location'];
} else {
return (object) ['status' => 200, 'userOnLocation' => false, 'message' => "You are not at the company location.", 'translate' => 'you_are_not_at_the_company_location'];
}
} catch (PDOException $e) {
return (object) ['status' => 500, 'message' => 'Database error: ' . $e->getMessage()];
}
}
}
?>Editor is loading...
Leave a Comment