.-.-.-.-.-

<?php

require_once __DIR__ . '/../Classes/Database.php';

class ReportsAndDashboardService
{

private $db;

public function __construct()
    {
        $this->db = new Database();
    }

public function getServicesCount()
    {
        $this->db->query('SELECT COUNT(*) as count FROM services');
        return $this->db->single();
    }

public function getUsersCount()
    {
        $this->db->query('SELECT COUNT(*) as count FROM users');
        return $this->db->single();
    }

public function getTasksCount()
    {
        $this->db->query('SELECT COUNT(*) as count FROM tasks');
        return $this->db->single();
    }

public function getSchedulesCount()
    {
        $this->db->query('SELECT COUNT(*) as count FROM schedules');
        return $this->db->single();
    }

public function getSubtasksCount()
    {
        $this->db->query('SELECT COUNT(*) as count FROM subtasks');
        return $this->db->single();
    }

//count unassign tasks
    public function getUnassignTasksCount()
    {
        $this->db->query('SELECT COUNT(*) as count FROM tasks WHERE is_assigned = 0');
        return $this->db->single();
    }

//assigned tasks
    public function getAssignTasksCount()
    {
        $this->db->query('SELECT COUNT(*) as count FROM tasks WHERE is_assigned = 1');
        return $this->db->single();
    }

//Total Contracts
    public function getContractorsCount()
    {
        $this->db->query('SELECT COUNT(*) as count FROM users where role = "subcontractor"');
        return $this->db->single();
    }

// In your ScheduleService.php or a similar service file
    public function getSubtasks()
    {

$thismonth = date('Y-m-01');
        $this->db->query('SELECT DATE(completed_at) as completed_at, COUNT(*) as completed_subtasks FROM subtasks WHERE completed = 1 AND completed_at >= :thismonth GROUP BY DATE(completed_at)');

$this->db->bind(':thismonth', $thismonth);
        return $this->db->resultSet();
    }

public function getPendingTasks($companyId)
    {
        $this->db->query('
        SELECT COUNT(*) as count
        FROM tasks
        WHERE assigned_to = :company_id and accepted = 1
          AND finished = 0
    ');
        $this->db->bind(':company_id', $companyId);
        $result = $this->db->single(); // Assuming single() returns an object

// Check if $result is an object and has the 'count' property
        if (is_object($result) && isset($result->count)) {
            return $result->count; // Return the count property of the object
        } else {
            return 0; // Return 0 or handle the error condition appropriately
        }
    }

public function getCompletedTasks($companyId)
    {
        $this->db->query('
        SELECT COUNT(*) as count
        FROM tasks
        WHERE assigned_to = :company_id
          AND finished = 1
    ');
        $this->db->bind(':company_id', $companyId);
        $result = $this->db->single(); // Assuming single() returns an object

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

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

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

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

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;

// 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;

// 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;

// 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;

// 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;

// Return an associative array with the counts
        return [
            'total' => $total,
            'completed' => $completed,
            'incomplete' => $incomplete
        ];
    }

}

/home/emeraadmin/.htpasswds/.././public_html/node_modules/../Service/ReportsAndDashboardService.php