Untitled

 avatar
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