Файловый менеджер - Редактировать - /home/emeraadmin/public_html/4d695/Service.zip
Назад
PK �e�\9��� � ServiceService.phpnu �[��� <?php require_once __DIR__ . '/../Classes/Database.php'; require_once __DIR__ . '/../Classes/Service.php'; class ServiceService { private $db; public function __construct() { $this->db = new Database(); } public function addService($service) { $this->db->query('INSERT INTO services (name, region, address_line1, address_line2, suburb, city, state, postal_code, country, latitude, longitude, created_at, updated_at) VALUES (:name, :region, :address_line1, :address_line2, :suburb, :city, :state, :postal_code, :country, :latitude, :longitude, :created_at, :updated_at)'); $this->db->bind(':name', $service->name); $this->db->bind(':region', $service->region); $this->db->bind(':address_line1', $service->address_line1); $this->db->bind(':address_line2', $service->address_line2); $this->db->bind(':suburb', $service->suburb); $this->db->bind(':city', $service->city); $this->db->bind(':state', $service->state); $this->db->bind(':postal_code', $service->postal_code); $this->db->bind(':country', $service->country); $this->db->bind(':latitude', $service->latitude); $this->db->bind(':longitude', $service->longitude); $this->db->bind(':created_at', $service->created_at); $this->db->bind(':updated_at', $service->updated_at); if ($this->db->execute()) { return true; } else { return false; } } public function addServicereturnlastid($service) { $this->db->query('INSERT INTO services (name, region, address_line1, address_line2, suburb, city, state, postal_code, country, latitude, longitude, created_at, updated_at) VALUES (:name, :region, :address_line1, :address_line2, :suburb, :city, :state, :postal_code, :country, :latitude, :longitude, :created_at, :updated_at)'); $this->db->bind(':name', $service->name); $this->db->bind(':region', $service->region); $this->db->bind(':address_line1', $service->address_line1); $this->db->bind(':address_line2', $service->address_line2); $this->db->bind(':suburb', $service->suburb); $this->db->bind(':city', $service->city); $this->db->bind(':state', $service->state); $this->db->bind(':postal_code', $service->postal_code); $this->db->bind(':country', $service->country); $this->db->bind(':latitude', $service->latitude); $this->db->bind(':longitude', $service->longitude); $this->db->bind(':created_at', $service->created_at); $this->db->bind(':updated_at', $service->updated_at); if ($this->db->execute()) { return $this->db->lastInsertId(); // Retrieve the last inserted ID } else { return false; } } public function getAllServices() { $this->db->query('SELECT * FROM services'); return $this->db->resultSet(); } public function getAllServicesForDropdown() { $this->db->query('SELECT id, name,region FROM services'); return $this->db->resultSet(); } public function getServiceById(int $serviceId) { $this->db->query('SELECT * FROM services WHERE id = :id'); $this->db->bind(':id', $serviceId); return $this->db->single(); } public function updateService(array $serviceData) { $this->db->query('UPDATE services SET name = :name, region = :region, address_line1 = :address_line1, address_line2 = :address_line2, suburb = :suburb, city = :city, state = :state, postal_code = :postal_code, country = :country, phone = :phone, on_site_location = :on_site_location, latitude = :latitude, longitude = :longitude, updated_at = CURRENT_TIMESTAMP WHERE id = :id'); $this->db->bind(':id', $serviceData['id']); $this->db->bind(':name', $serviceData['name']); $this->db->bind(':region', $serviceData['region']); $this->db->bind(':address_line1', $serviceData['address_line1']); $this->db->bind(':address_line2', $serviceData['address_line2']); $this->db->bind(':suburb', $serviceData['suburb']); $this->db->bind(':city', $serviceData['city']); $this->db->bind(':state', $serviceData['state']); $this->db->bind(':postal_code', $serviceData['postal_code']); $this->db->bind(':country', $serviceData['country']); $this->db->bind(':phone', $serviceData['phone']); $this->db->bind(':on_site_location', $serviceData['on_site_location']); $this->db->bind(':latitude', $serviceData['latitude']); $this->db->bind(':longitude', $serviceData['longitude']); if ($this->db->execute()) { return true; // Update successful } else { return false; // Update failed } } public function getServiceByNameAndRegion(string $serviceName, string $region) { $this->db->query('SELECT * FROM services WHERE name = :name AND region = :region'); $this->db->bind(':name', $serviceName); $this->db->bind(':region', $region); return $this->db->single(); } }PK �e�\iJ�*H H TaskFormService.phpnu �[��� <?php require_once __DIR__ . '/../Classes/Database.php'; require_once __DIR__ . '/../Classes/TaskForm.php'; class TaskFormService { private $db; public function __construct() { $this->db = new Database(); } public function insertTaskForm($taskForm) { $this->db->query('INSERT INTO form_submissions (subtask_id, school_name, date, time, main_areas, notes, client_name, staff_name, client_signature, staff_signature, completion_time, latitude, longitude) VALUES (:subtask_id, :school_name, :date, :time, :main_areas, :notes, :client_name, :staff_name, :client_signature, :staff_signature, :completion_time, :latitude, :longitude)'); $this->db->bind(':subtask_id', $taskForm->subtask_id); $this->db->bind(':school_name', $taskForm->school_name); $this->db->bind(':date', $taskForm->date); $this->db->bind(':time', $taskForm->time); $this->db->bind(':main_areas', $taskForm->main_areas); $this->db->bind(':notes', $taskForm->notes); $this->db->bind(':client_name', $taskForm->client_name); $this->db->bind(':staff_name', $taskForm->staff_name); $this->db->bind(':client_signature', $taskForm->client_signature); $this->db->bind(':staff_signature', $taskForm->staff_signature); $this->db->bind(':completion_time', $taskForm->completion_time); $this->db->bind(':latitude', $taskForm->latitude); $this->db->bind(':longitude', $taskForm->longitude); if ($this->db->execute()) { return true; } else { return false; } } public function getTaskFormById($id) { $this->db->query('SELECT * FROM form_submissions WHERE id = :id'); $this->db->bind(':id', $id); return $this->db->single(); } }PK �e�\<�HA� � SubtaskService.phpnu �[��� <?php require_once __DIR__ . '/../Classes/Database.php'; require_once __DIR__ . '/../Classes/Subtask.php'; class SubtaskService { private $db; public function __construct() { $this->db = new Database(); } public function getSubtaskById($subtaskId) { $query = ' SELECT s.*, t.service_id, srv.name as service_name, srv.region as service_region FROM subtasks s LEFT JOIN tasks t ON s.task_id = t.id LEFT JOIN services srv ON t.service_id = srv.id WHERE s.id = :id '; $this->db->query($query); $this->db->bind(':id', $subtaskId); return $this->db->single(); } public function getSubtaskDetailsForWorkForm($id) { $query = ' SELECT s.*, t.service_id, srv.name as service_name, srv.region as service_region FROM subtasks s LEFT JOIN tasks t ON s.task_id = t.id LEFT JOIN services srv ON t.service_id = srv.id WHERE s.id = :id '; $this->db->query($query); $this->db->bind(':id', $id); return $this->db->single(); } public function updateSubtaskStatusAfterMessage(int $subtask_id, string $assigned_to_Mobile, string $assigned_Message_Rsponse) { try { $this->db->query('UPDATE subtasks SET assigned = true, assigned_to_Mobile = :assigned_to_Mobile, assigned_Message_Rsponse = :assigned_Message_Rsponse WHERE id = :id'); $this->db->bind(':id', $subtask_id); $this->db->bind(':assigned_to_Mobile', $assigned_to_Mobile); $this->db->bind(':assigned_Message_Rsponse', $assigned_Message_Rsponse); $result = $this->db->execute(); if (!$result) { throw new Exception('Database execution failed.'); } return $result; } catch (Exception $e) { error_log('Error in updateSubtaskStatusAfterMessage: ' . $e->getMessage()); throw $e; } } public function addSubtask(Subtask $subtask) { try { $this->db->query('INSERT INTO subtasks (task_id, service_id, subtask_date, subtask_time, created_at, updated_at) VALUES (:task_id, :service_id, :subtask_date, :subtask_time, :created_at, :updated_at)'); $this->db->bind(':task_id', $subtask->task_id); $this->db->bind(':service_id', $subtask->service_id); $this->db->bind(':subtask_date', $subtask->subtask_date); $this->db->bind(':subtask_time', $subtask->subtask_time); $this->db->bind(':created_at', $subtask->created_at); $this->db->bind(':updated_at', $subtask->updated_at); $result = $this->db->execute(); if (!$result) { throw new Exception('Database execution failed.'); } return $result; } catch (Exception $e) { error_log('Error in addSubtask: ' . $e->getMessage()); throw $e; } } public function deleteSubtaskById(int $subtaskId) { $this->db->query('DELETE FROM subtasks WHERE id = :id'); $this->db->bind(':id', $subtaskId); return $this->db->execute(); } public function getSubtaskLocation(string $subtaskId) { $this->db->query('SELECT latitude, longitude FROM form_submissions WHERE subtask_id = :subtask_id'); $this->db->bind(':subtask_id', $subtaskId); return $this->db->single(); } public function getSubtasksByDayForAssignedUser(int $assignedUserId) { //for now select all subtasks $this->db->query('SELECT * FROM subtasks'); $subtasks = $this->db->resultSet(); $subtasksByDay = []; foreach ($subtasks as $subtask) { $subtaskDate = $subtask->subtask_date; $subtaskDate = date('Y-m-d', strtotime($subtaskDate)); $subtasksByDay[$subtaskDate][] = $subtask; } return $subtasksByDay; } public function getAllSubtasksForTask($task_id) { try { // Adjusted SQL query to include service_name and region from services table $query = " SELECT subtasks.*, srv.name AS service_name, srv.region AS region FROM subtasks INNER JOIN tasks ON subtasks.task_id = tasks.id LEFT JOIN services srv ON tasks.service_id = srv.id WHERE subtasks.task_id = :task_id "; $this->db->query($query); $this->db->bind(':task_id', $task_id); $subtasks = $this->db->resultSet(); return $subtasks; } catch (PDOException $e) { // Handle database errors appropriately (log, throw further, etc.) error_log('Database error: ' . $e->getMessage()); return []; // Return empty array or handle error as needed } } public function markSubtaskAsComplete($subtaskId) { try { $this->db->query('UPDATE subtasks SET completed = true, completed_at = NOW(),submitted_by_admin = true WHERE id = :id'); $this->db->bind(':id', $subtaskId); $result = $this->db->execute(); if (!$result) { throw new Exception('Database execution failed.'); } return $result; } catch (Exception $e) { error_log('Error in markSubtaskAsComplete: ' . $e->getMessage()); throw $e; } } public function ignoreSubtask($subtaskId) { try { $this->db->query('UPDATE subtasks SET ignored = true WHERE id = :id'); $this->db->bind(':id', $subtaskId); $result = $this->db->execute(); if (!$result) { throw new Exception('Database execution failed.'); } return $result; } catch (Exception $e) { error_log('Error in ignoreSubtask: ' . $e->getMessage()); throw $e; } } public function ignoreSubtasks(array $subtaskIds) { try { $placeholders = implode(',', array_fill(0, count($subtaskIds), '?')); $query = "UPDATE subtasks SET ignored = true WHERE id IN ($placeholders)"; $this->db->query($query); foreach ($subtaskIds as $index => $id) { $this->db->bind(($index + 1), $id); } $result = $this->db->execute(); if (!$result) { throw new Exception('Database execution failed.'); } return $result; } catch (Exception $e) { error_log('Error in ignoreSubtasks: ' . $e->getMessage()); throw $e; } } } PK �e�\n��G� � UserService.phpnu �[��� <?php require_once __DIR__ . '/../Classes/Database.php'; require_once __DIR__ . '/../Classes/User.php'; class UserService { private $db; public function __construct() { $this->db = new Database(); } public function login($email, $password) { $this->db->query('SELECT id, email, password, role FROM users WHERE email = :email and status = :status'); $this->db->bind(':email', $email); $this->db->bind(':status', 'active'); $row = $this->db->single(); if ($row) { // Verify the password using password_verify() if (password_verify($password, $row->password)) { return $row; // Return the entire user row, which includes the role } } return false; } public function getAllUsers() { $this->db->query('SELECT id, first_name, last_name, email,phone,status, role ,last_login FROM users'); return $this->db->resultSet(); } public function authenticate($email, $password) { $this->db->query('SELECT * FROM users WHERE email = :email'); $this->db->bind(':email', $email); $row = $this->db->single(); if ($row && password_verify($password, $row->password)) { return true; } return false; } public function addUser($firstName, $lastName, $email, $phone, $role, $status, $password) { // Hash the password $passwordHash = password_hash($password, PASSWORD_DEFAULT); // Prepare and execute the SQL query $this->db->query('INSERT INTO users (first_name, last_name, email, phone, role, status, password) VALUES (:first_name, :last_name, :email, :phone, :role, :status, :password)'); $this->db->bind(':first_name', $firstName); $this->db->bind(':last_name', $lastName); $this->db->bind(':email', $email); $this->db->bind(':phone', $phone); $this->db->bind(':role', $role); $this->db->bind(':status', $status); $this->db->bind(':password', $passwordHash); $this->db->execute(); return $this->db->lastInsertId(); } public function updateUser($id, $firstName, $lastName, $email, $phone, $role) { $this->db->query('UPDATE users SET first_name = :first_name, last_name = :last_name, email = :email, phone = :phone, role = :role WHERE id = :id'); $this->db->bind(':id', $id); $this->db->bind(':first_name', $firstName); $this->db->bind(':last_name', $lastName); $this->db->bind(':email', $email); $this->db->bind(':phone', $phone); $this->db->bind(':role', $role); $this->db->execute(); } public function deleteUser($id) { // Soft delete if already active make it inactive or vice versa $this->db->query('SELECT status FROM users WHERE id = :id'); $this->db->bind(':id', $id); $row = $this->db->single(); $status = $row->status == 'active' ? 'inactive' : 'active'; $this->db->query('UPDATE users SET status = :status WHERE id = :id'); $this->db->bind(':id', $id); $this->db->bind(':status', $status); $this->db->execute(); } public function getUserById($id) { $this->db->query('SELECT id, first_name, last_name, email, phone, role, status FROM users WHERE id = :id'); $this->db->bind(':id', $id); return $this->db->single(); } public function isEmailExists($email) { $this->db->query('SELECT id FROM users WHERE email = :email'); $this->db->bind(':email', $email); $row = $this->db->single(); return $row ? true : false; } public function isEmailExistsForOthers($id, $email) { $this->db->query('SELECT id FROM users WHERE email = :email AND id != :id'); $this->db->bind(':email', $email); $this->db->bind(':id', $id); $row = $this->db->single(); return $row ? true : false; } public function resetPassword($id, $password) { $passwordHash = password_hash($password, PASSWORD_DEFAULT); $this->db->query('UPDATE users SET password = :password WHERE id = :id'); $this->db->bind(':id', $id); $this->db->bind(':password', $passwordHash); $this->db->execute(); } public function fetchUserName(int $userId) { $this->db->query('SELECT first_name, last_name FROM users WHERE id = :id'); $this->db->bind(':id', $userId); $row = $this->db->single(); return $row->first_name . ' ' . $row->last_name; } // Function to verify the user's password public function verifyUserPassword($email, $password) { // Call the getUserByEmail method from within the class $user = $this->getUserByEmail($email); if ($user) { // Verify the password using password_verify return password_verify($password, $user->password); } return false; // Return false if the user is not found } // Function to retrieve the user by email public function getUserByEmail($email) { // Get the database connection from the Database class $db = $this->db->getConn(); // Ensure the database object is properly initialized // Prepare and execute the query $stmt = $db->prepare("SELECT * FROM users WHERE email = :email"); $stmt->bindParam(':email', $email); $stmt->execute(); // Fetch the user as an object return $stmt->fetch(PDO::FETCH_OBJ); } } PK �e�\ n�@J @J ReportsAndDashboardService.phpnu �[��� <?php require_once __DIR__ . '/../Classes/Database.php'; class ReportsAndDashboardService { private $db; public function __construct() { $this->db = new Database(); } public function getServicesCount() { $this->db->query('SELECT COUNT(*) as count FROM services'); return $this->db->single(); } public function getUsersCount() { $this->db->query('SELECT COUNT(*) as count FROM users'); return $this->db->single(); } public function getTasksCount() { $this->db->query('SELECT COUNT(*) as count FROM tasks'); return $this->db->single(); } public function getSchedulesCount() { $this->db->query('SELECT COUNT(*) as count FROM schedules'); return $this->db->single(); } public function getSubtasksCount() { $this->db->query('SELECT COUNT(*) as count FROM subtasks'); return $this->db->single(); } //count unassign tasks public function getUnassignTasksCount() { $this->db->query('SELECT COUNT(*) as count FROM tasks WHERE is_assigned = 0'); return $this->db->single(); } //assigned tasks public function getAssignTasksCount() { $this->db->query('SELECT COUNT(*) as count FROM tasks WHERE is_assigned = 1'); return $this->db->single(); } //Total Contracts public function getContractorsCount() { $this->db->query('SELECT COUNT(*) as count FROM users where role = "subcontractor"'); return $this->db->single(); } // In your ScheduleService.php or a similar service file public function getSubtasks() { $thismonth = date('Y-m-01'); $this->db->query('SELECT DATE(completed_at) as completed_at, COUNT(*) as completed_subtasks FROM subtasks WHERE completed = 1 AND completed_at >= :thismonth GROUP BY DATE(completed_at)'); $this->db->bind(':thismonth', $thismonth); return $this->db->resultSet(); } public function getPendingTasks($companyId) { $this->db->query(' SELECT COUNT(*) as count FROM tasks WHERE assigned_to = :company_id and accepted = 1 AND finished = 0 '); $this->db->bind(':company_id', $companyId); $result = $this->db->single(); // Assuming single() returns an object // Check if $result is an object and has the 'count' property if (is_object($result) && isset($result->count)) { return $result->count; // Return the count property of the object } else { return 0; // Return 0 or handle the error condition appropriately } } public function getCompletedTasks($companyId) { $this->db->query(' SELECT COUNT(*) as count FROM tasks WHERE assigned_to = :company_id AND finished = 1 '); $this->db->bind(':company_id', $companyId); $result = $this->db->single(); // Assuming single() returns an object // Check if $result is an object and has the 'count' property if (is_object($result) && isset($result->count)) { return $result->count; // Return the count property of the object } else { return 0; // Return 0 or handle the error condition appropriately } } public function getOngoingSubtasks($companyId) { $this->db->query(' SELECT COUNT(*) as count FROM subtasks s JOIN tasks t ON s.task_id = t.id WHERE t.assigned_to = :company_id AND s.assigned = TRUE AND s.completed = FALSE '); $this->db->bind(':company_id', $companyId); $result = $this->db->single(); // Assuming single() returns an object // Check if $result is an object and has the 'count' property if (is_object($result) && isset($result->count)) { return $result->count; // Return the count property of the object } else { return 0; // Return 0 or handle the error condition appropriately } } public function getUnassignedSubtasks($companyId) { $this->db->query(' SELECT COUNT(*) as count FROM subtasks s JOIN tasks t ON s.task_id = t.id WHERE t.assigned_to = :company_id AND t.accepted = 1 AND s.assigned = FALSE '); $this->db->bind(':company_id', $companyId); $result = $this->db->single(); // Assuming single() returns an object // Check if $result is an object and has the 'count' property if (is_object($result) && isset($result->count)) { return $result->count; // Return the count property of the object } else { return 0; // Return 0 or handle the error condition appropriately } } public function getTasksAddedByMe($taskProviderId) { $this->db->query(' SELECT COUNT(*) as count FROM tasks WHERE added_by = :task_provider_id '); $this->db->bind(':task_provider_id', $taskProviderId); $result = $this->db->single(); // Assuming single() returns an object // Check if $result is an object and has the 'count' property if (is_object($result) && isset($result->count)) { return $result->count; // Return the count property of the object } else { return 0; // Return 0 or handle the error condition appropriately } } public function getOngoingSubTasksAdmin() { $this->db->query(' SELECT COUNT(*) as count FROM subtasks WHERE assigned = TRUE AND completed = FALSE '); $result = $this->db->single(); // Assuming single() returns an object // Check if $result is an object and has the 'count' property if (is_object($result) && isset($result->count)) { return $result->count; // Return the count property of the object } else { return 0; // Return 0 or handle the error condition appropriately } } public function getSubtasksSummaryAdmin() { // Combine queries to get counts for both completed and incomplete subtasks $this->db->query(' SELECT SUM(CASE WHEN completed = TRUE THEN 1 ELSE 0 END) as completed_count, SUM(CASE WHEN completed = FALSE THEN 1 ELSE 0 END) as incomplete_count FROM subtasks '); $result = $this->db->single(); // Assuming single() returns an object // Initialize counts to 0 $completed = 0; $incomplete = 0; // Check if $result is an object and extract the properties if they exist if (is_object($result)) { $completed = isset($result->completed_count) ? (int)$result->completed_count : 0; $incomplete = isset($result->incomplete_count) ? (int)$result->incomplete_count : 0; } // Return an associative array with the counts return [ 'completed' => $completed, 'incomplete' => $incomplete ]; } public function getSubtasksSummaryTaskProvider($taskProviderId) { // Query to get counts for completed and incomplete subtasks for a specific task provider $this->db->query(' SELECT SUM(CASE WHEN subtasks.completed = TRUE THEN 1 ELSE 0 END) as completed_count, SUM(CASE WHEN subtasks.completed = FALSE THEN 1 ELSE 0 END) as incomplete_count FROM subtasks JOIN tasks ON subtasks.task_id = tasks.id JOIN schedules ON tasks.schedule_id = schedules.id WHERE schedules.created_by = :taskProviderId '); // Bind the task provider ID parameter to the query $this->db->bind(':taskProviderId', $taskProviderId); // Execute the query and fetch the result $result = $this->db->single(); // Assuming single() returns an object // Initialize counts to 0 $completed = 0; $incomplete = 0; // Check if $result is an object and extract the properties if they exist if (is_object($result)) { $completed = isset($result->completed_count) ? (int)$result->completed_count : 0; $incomplete = isset($result->incomplete_count) ? (int)$result->incomplete_count : 0; } // Return an associative array with the counts return [ 'completed' => $completed, 'incomplete' => $incomplete ]; } //total subtask for today = subtask_date public function getSubtasksSummaryForTodayAdmin($today) { // Combine all queries into one to reduce the number of database calls $this->db->query(' SELECT COUNT(*) as total_count, SUM(CASE WHEN completed = TRUE THEN 1 ELSE 0 END) as completed_count, SUM(CASE WHEN completed = FALSE THEN 1 ELSE 0 END) as incomplete_count FROM subtasks WHERE subtask_date = :today '); $this->db->bind(':today', $today); $result = $this->db->single(); // Assuming single() returns an object // Initialize counts to 0 $total = 0; $completed = 0; $incomplete = 0; // Check if $result is an object and extract the properties if they exist if (is_object($result)) { $total = isset($result->total_count) ? (int)$result->total_count : 0; $completed = isset($result->completed_count) ? (int)$result->completed_count : 0; $incomplete = isset($result->incomplete_count) ? (int)$result->incomplete_count : 0; } // Return an associative array with the counts return [ 'total' => $total, 'completed' => $completed, 'incomplete' => $incomplete ]; } public function getSubtasksSummaryForTodayTaskProvider($taskProviderId, $today) { // Query to get counts for subtasks for a specific task provider on a specific date $this->db->query(' SELECT COUNT(*) as total_count, SUM(CASE WHEN subtasks.completed = TRUE THEN 1 ELSE 0 END) as completed_count, SUM(CASE WHEN subtasks.completed = FALSE THEN 1 ELSE 0 END) as incomplete_count FROM subtasks JOIN tasks ON subtasks.task_id = tasks.id JOIN schedules ON tasks.schedule_id = schedules.id WHERE schedules.created_by = :taskProviderId AND subtasks.subtask_date = :today '); // Bind parameters to the query $this->db->bind(':taskProviderId', $taskProviderId); $this->db->bind(':today', $today); // Execute the query and fetch the result $result = $this->db->single(); // Assuming single() returns an object // Initialize counts to 0 $total = 0; $completed = 0; $incomplete = 0; // Check if $result is an object and extract the properties if they exist if (is_object($result)) { $total = isset($result->total_count) ? (int)$result->total_count : 0; $completed = isset($result->completed_count) ? (int)$result->completed_count : 0; $incomplete = isset($result->incomplete_count) ? (int)$result->incomplete_count : 0; } // Return an associative array with the counts return [ 'total' => $total, 'completed' => $completed, 'incomplete' => $incomplete ]; } public function getTodayTotalSubTasks($companyId, bool $date) { } public function getSubtasksSummaryForTodaySubContractor($companyId, $date) { // Combined query to get total, completed, and incomplete subtasks in one call $this->db->query(' SELECT COUNT(*) as total, SUM(CASE WHEN s.completed = 1 THEN 1 ELSE 0 END) as completed, SUM(CASE WHEN s.completed = 0 THEN 1 ELSE 0 END) as incomplete FROM subtasks s JOIN tasks t ON s.task_id = t.id WHERE t.assigned_to = :company_id AND s.subtask_date = :date AND t.accepted = 1 '); // Bind parameters $this->db->bind(':company_id', $companyId); $this->db->bind(':date', $date); // Execute query and get the result $result = $this->db->single(); // Assuming single() returns an object // Initialize counts to 0 $total = 0; $completed = 0; $incomplete = 0; // Check if $result is an object and extract the properties if they exist if (is_object($result)) { $total = isset($result->total) ? (int)$result->total : 0; $completed = isset($result->completed) ? (int)$result->completed : 0; $incomplete = isset($result->incomplete) ? (int)$result->incomplete : 0; } // Return an associative array with the counts return [ 'total' => $total, 'completed' => $completed, 'incomplete' => $incomplete ]; } public function getSubtasksSummaryForSubContractor($companyId) { // Combined query to get total, completed, and incomplete subtasks in one call $this->db->query(' SELECT COUNT(*) as total, SUM(CASE WHEN s.completed = 1 THEN 1 ELSE 0 END) as completed, SUM(CASE WHEN s.completed = 0 THEN 1 ELSE 0 END) as incomplete FROM subtasks s JOIN tasks t ON s.task_id = t.id WHERE t.assigned_to = :company_id AND t.accepted = 1 '); // Bind the company ID parameter to the query $this->db->bind(':company_id', $companyId); // Execute the query and fetch the result $result = $this->db->single(); // Assuming single() returns an object // Initialize counts to 0 $total = 0; $completed = 0; $incomplete = 0; // Check if $result is an object and extract the properties if they exist if (is_object($result)) { $total = isset($result->total) ? (int)$result->total : 0; $completed = isset($result->completed) ? (int)$result->completed : 0; $incomplete = isset($result->incomplete) ? (int)$result->incomplete : 0; } // Return an associative array with the counts return [ 'total' => $total, 'completed' => $completed, 'incomplete' => $incomplete ]; } public function getTasksSummaryAdmin() { // Combined query to get total, completed, and incomplete tasks in one call $this->db->query(' SELECT COUNT(*) as total, SUM(CASE WHEN finished = 1 THEN 1 ELSE 0 END) as completed, SUM(CASE WHEN finished = 0 THEN 1 ELSE 0 END) as incomplete FROM tasks '); $result = $this->db->single(); // Assuming single() returns an object // Initialize counts to 0 $total = 0; $completed = 0; $incomplete = 0; // Check if $result is an object and extract the properties if they exist if (is_object($result)) { $total = isset($result->total) ? (int)$result->total : 0; $completed = isset($result->completed) ? (int)$result->completed : 0; $incomplete = isset($result->incomplete) ? (int)$result->incomplete : 0; } // Return an associative array with the counts return [ 'total' => $total, 'completed' => $completed, 'incomplete' => $incomplete ]; } public function getTasksSummaryForSubContractor($companyId) { // Combined query to get total, completed, and incomplete tasks in one call $this->db->query(' SELECT COUNT(*) as total, SUM(CASE WHEN finished = 1 THEN 1 ELSE 0 END) as completed, SUM(CASE WHEN finished = 0 THEN 1 ELSE 0 END) as incomplete FROM tasks WHERE assigned_to = :company_id '); // Bind the company ID parameter to the query $this->db->bind(':company_id', $companyId); // Execute the query and fetch the result $result = $this->db->single(); // Assuming single() returns an object // Initialize counts to 0 $total = 0; $completed = 0; $incomplete = 0; // Check if $result is an object and extract the properties if they exist if (is_object($result)) { $total = isset($result->total) ? (int)$result->total : 0; $completed = isset($result->completed) ? (int)$result->completed : 0; $incomplete = isset($result->incomplete) ? (int)$result->incomplete : 0; } // Return an associative array with the counts return [ 'total' => $total, 'completed' => $completed, 'incomplete' => $incomplete ]; } public function getTasksSummaryForTaskProvider($taskProviderId) { // Combined query to get total, completed, and incomplete tasks in one call $this->db->query(' SELECT COUNT(*) as total, SUM(CASE WHEN finished = 1 THEN 1 ELSE 0 END) as completed, SUM(CASE WHEN finished = 0 THEN 1 ELSE 0 END) as incomplete FROM tasks JOIN schedules ON tasks.schedule_id = schedules.id WHERE schedules.created_by = :taskProviderId '); // Bind the task provider ID parameter to the query $this->db->bind(':taskProviderId', $taskProviderId); // Execute the query and fetch the result $result = $this->db->single(); // Assuming single() returns an object // Initialize counts to 0 $total = 0; $completed = 0; $incomplete = 0; // Check if $result is an object and extract the properties if they exist if (is_object($result)) { $total = isset($result->total) ? (int)$result->total : 0; $completed = isset($result->completed) ? (int)$result->completed : 0; $incomplete = isset($result->incomplete) ? (int)$result->incomplete : 0; } // Return an associative array with the counts return [ 'total' => $total, 'completed' => $completed, 'incomplete' => $incomplete ]; } }PK �e�\]�#i�&