Tul xxx Tul

Viewing: ProjectAction.php

<?php

class ProjectAction {
    private $db;

    public function __construct() {
        require_once __DIR__ . '/../core/Database.php';
        $this->db = (new Database())->connect();
    }

    public function getByProjectId($projectId) {
        $stmt = $this->db->prepare('SELECT id, project_id,
            fase_num AS fase,
            fase_nombre AS nombre,
            accion_num,
            accion,
            horas,
            dia,
            status,
            inicio,
            fin,
            especialista,
            observaciones,
            precio
            FROM project_actions WHERE project_id = ? ORDER BY accion_num ASC, id ASC');
        $stmt->execute([$projectId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function create($data) {
        $sql = 'INSERT INTO project_actions (project_id, fase_num, fase_nombre, accion_num, accion, horas, dia, status, inicio, fin, especialista, observaciones, precio)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([
            $data['project_id'],
            $data['fase_num'] ?? null,
            $data['fase_nombre'] ?? null,
            $data['accion_num'] ?? null,
            $data['accion'] ?? null,
            $data['horas'] ?? null,
            $data['dia'] ?? null,
            $data['status'] ?? 'Pendiente',
            $data['inicio'] ?? null,
            $data['fin'] ?? null,
            $data['especialista'] ?? null,
            $data['observaciones'] ?? null,
            isset($data['precio']) ? $data['precio'] : null,
        ]);
    }

    public function update($id, $data) {
        // Obtener datos actuales para rellenar campos no proporcionados
        $current = $this->getById($id);
        if (!$current) {
            return false;
        }
        
        // Usar datos nuevos si existen, o mantener los actuales
        $sql = 'UPDATE project_actions SET project_id = ?, fase_num = ?, fase_nombre = ?, accion_num = ?, accion = ?, horas = ?, dia = ?, status = ?, inicio = ?, fin = ?, especialista = ?, observaciones = ?, precio = ? WHERE id = ?';
        $stmt = $this->db->prepare($sql);
        
        try {
            return $stmt->execute([
                (int)($data['project_id'] ?? $current['project_id']),
                isset($data['fase_num']) && $data['fase_num'] !== '' ? (int)$data['fase_num'] : $current['fase_num'],
                isset($data['fase_nombre']) && trim($data['fase_nombre']) !== '' ? trim($data['fase_nombre']) : $current['fase_nombre'],
                isset($data['accion_num']) && $data['accion_num'] !== '' ? (int)$data['accion_num'] : $current['accion_num'],
                isset($data['accion']) && trim($data['accion']) !== '' ? trim($data['accion']) : $current['accion'],
                isset($data['horas']) && $data['horas'] !== '' ? (int)$data['horas'] : $current['horas'],
                isset($data['dia']) && $data['dia'] !== '' ? (int)$data['dia'] : $current['dia'],
                isset($data['status']) && trim($data['status']) !== '' ? trim($data['status']) : ($current['status'] ?? 'Pendiente'),
                isset($data['inicio']) && trim($data['inicio']) !== '' ? trim($data['inicio']) : $current['inicio'],
                isset($data['fin']) && trim($data['fin']) !== '' ? trim($data['fin']) : $current['fin'],
                isset($data['especialista']) && trim($data['especialista']) !== '' ? trim($data['especialista']) : $current['especialista'],
                isset($data['observaciones']) && trim($data['observaciones']) !== '' ? trim($data['observaciones']) : $current['observaciones'],
                isset($data['precio']) && $data['precio'] !== '' ? (float)$data['precio'] : $current['precio'],
                $id
            ]);
        } catch (PDOException $e) {
            // Log del error para debugging
            error_log("Error updating project action: " . $e->getMessage());
            return false;
        }
    }
    
    public function updatePartial($id, $data) {
        // Obtener el registro actual para campos no especificados
        $current = $this->getById($id);
        if (!$current) {
            return false;
        }
        
        // Mezclar datos actuales con nuevos (los nuevos sobrescriben)
        $merged = array_merge($current, $data);
        
        // Asegurar que los valores numéricos sean correctos
        $merged['project_id'] = (int)($merged['project_id'] ?? 0);
        $merged['fase_num'] = isset($merged['fase_num']) && $merged['fase_num'] !== '' ? (int)$merged['fase_num'] : null;
        $merged['accion_num'] = isset($merged['accion_num']) && $merged['accion_num'] !== '' ? (int)$merged['accion_num'] : null;
        $merged['horas'] = isset($merged['horas']) && $merged['horas'] !== '' ? (int)$merged['horas'] : null;
        $merged['dia'] = isset($merged['dia']) && $merged['dia'] !== '' ? (int)$merged['dia'] : null;
        $merged['precio'] = isset($merged['precio']) && $merged['precio'] !== '' ? (float)$merged['precio'] : null;
        
        // Asegurar que los campos de texto no sean vacíos
        $merged['fase_nombre'] = isset($merged['fase_nombre']) && trim($merged['fase_nombre']) !== '' ? trim($merged['fase_nombre']) : null;
        $merged['accion'] = isset($merged['accion']) && trim($merged['accion']) !== '' ? trim($merged['accion']) : null;
        $merged['especialista'] = isset($merged['especialista']) && trim($merged['especialista']) !== '' ? trim($merged['especialista']) : null;
        $merged['observaciones'] = isset($merged['observaciones']) && trim($merged['observaciones']) !== '' ? trim($merged['observaciones']) : null;
        $merged['status'] = isset($merged['status']) && trim($merged['status']) !== '' ? trim($merged['status']) : 'Pendiente';
        
        // Manejar fechas
        $merged['inicio'] = isset($merged['inicio']) && trim($merged['inicio']) !== '' ? trim($merged['inicio']) : null;
        $merged['fin'] = isset($merged['fin']) && trim($merged['fin']) !== '' ? trim($merged['fin']) : null;
        
        $sql = 'UPDATE project_actions SET project_id = ?, fase_num = ?, fase_nombre = ?, accion_num = ?, accion = ?, horas = ?, dia = ?, status = ?, inicio = ?, fin = ?, especialista = ?, observaciones = ?, precio = ? WHERE id = ?';
        $stmt = $this->db->prepare($sql);
        
        try {
            return $stmt->execute([
                $merged['project_id'],
                $merged['fase_num'],
                $merged['fase_nombre'],
                $merged['accion_num'],
                $merged['accion'],
                $merged['horas'],
                $merged['dia'],
                $merged['status'],
                $merged['inicio'],
                $merged['fin'],
                $merged['especialista'],
                $merged['observaciones'],
                $merged['precio'],
                $id
            ]);
        } catch (PDOException $e) {
            // Log del error para debugging
            error_log("Error updating project action: " . $e->getMessage());
            return false;
        }
    }

    public function updateStatus($id, $status) {
        $stmt = $this->db->prepare('UPDATE project_actions SET status = ? WHERE id = ?');
        return $stmt->execute([$status, $id]);
    }

    public function delete($id) {
        $stmt = $this->db->prepare('DELETE FROM project_actions WHERE id = ?');
        return $stmt->execute([$id]);
    }

    public function getById($id) {
        $stmt = $this->db->prepare('SELECT * FROM project_actions WHERE id = ? LIMIT 1');
        $stmt->execute([$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getProjectIdByAction($actionId) {
        if (!$actionId) {
            return null;
        }
        $stmt = $this->db->prepare('SELECT project_id FROM project_actions WHERE id = ? LIMIT 1');
        $stmt->execute([$actionId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row && isset($row['project_id']) ? (int)$row['project_id'] : null;
    }

    public function syncProjectStatusWithActions($projectId) {
        if (!$projectId) {
            return null;
        }

        $stmt = $this->db->prepare("SELECT 
                COUNT(*) AS total,
                SUM(CASE WHEN LOWER(TRIM(COALESCE(status, ''))) IN ('lograda','logrado','completada','completado') THEN 1 ELSE 0 END) AS logradas,
                SUM(CASE WHEN LOWER(TRIM(COALESCE(status, ''))) = 'en progreso' THEN 1 ELSE 0 END) AS en_progreso
            FROM project_actions
            WHERE project_id = ?");
        $stmt->execute([$projectId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

        $total = (int)($row['total'] ?? 0);
        $logradas = (int)($row['logradas'] ?? 0);
        $enProgreso = (int)($row['en_progreso'] ?? 0);

        if ($total > 0 && $logradas === $total) {
            $newStatus = 'Logrado';
        } elseif ($enProgreso > 0) {
            $newStatus = 'En Progreso';
        } else {
            $newStatus = 'Pendiente';
        }

        $update = $this->db->prepare('UPDATE projects SET status = ? WHERE id = ?');
        $update->execute([$newStatus, $projectId]);

        return $newStatus;
    }

    public function updatePhaseAndOrder($id, $faseNum, $faseNombre, $accionNum) {
        $stmt = $this->db->prepare('UPDATE project_actions SET fase_num = ?, fase_nombre = ?, accion_num = ? WHERE id = ?');
        return $stmt->execute([$faseNum, $faseNombre, $accionNum, $id]);
    }

    public function reorderList($projectId, $faseNum, $faseNombre, array $orderedIds) {
        try {
            $this->db->beginTransaction();
            $index = 1;
            foreach ($orderedIds as $id) {
                $stmt = $this->db->prepare('UPDATE project_actions SET project_id = ?, fase_num = ?, fase_nombre = ?, accion_num = ? WHERE id = ?');
                $stmt->execute([$projectId, $faseNum, $faseNombre, $index, $id]);
                $index++;
            }
            $this->db->commit();
            return true;
        } catch (Throwable $e) {
            $this->db->rollBack();
            return false;
        }
    }

    /**
     * Obtiene totales agregados por proyecto:
     * - total_hours: suma de horas de todas las acciones
     * - phases_count: cantidad de fases distintas (fase_num, fase_nombre)
     * - actions_count: cantidad total de acciones del proyecto
     * - total_phase_days: suma del máximo "dia" por cada fase
     */
    public function getTotalsByProjectId($projectId) {
        // Total de horas y cantidad de fases
        $stmt = $this->db->prepare("SELECT 
                COALESCE(SUM(COALESCE(horas, 0)), 0) AS total_hours,
                COUNT(DISTINCT CONCAT(COALESCE(fase_num, ''), '|', COALESCE(fase_nombre, ''))) AS phases_count,
                COUNT(*) AS actions_count,
                SUM(CASE WHEN LOWER(TRIM(COALESCE(status, ''))) IN ('lograda','completada','completado') THEN 1 ELSE 0 END) AS actions_done,
                COALESCE(SUM(COALESCE(precio, 0)), 0) AS total_price
            FROM project_actions WHERE project_id = ?");
        $stmt->execute([$projectId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$row) { $row = ['total_hours' => 0, 'phases_count' => 0, 'actions_count' => 0, 'actions_done' => 0, 'total_price' => 0]; }

        // Suma de máximos de "dia" por fase
        $stmt2 = $this->db->prepare("SELECT SUM(max_dia) AS total_phase_days FROM (
                SELECT COALESCE(MAX(COALESCE(dia, 0)), 0) AS max_dia
                FROM project_actions
                WHERE project_id = ?
                GROUP BY COALESCE(fase_num, ''), COALESCE(fase_nombre, '')
            ) t");
        $stmt2->execute([$projectId]);
        $row2 = $stmt2->fetch(PDO::FETCH_ASSOC);

        $row['total_hours'] = (int)($row['total_hours'] ?? 0);
        $row['phases_count'] = (int)($row['phases_count'] ?? 0);
        $row['actions_count'] = (int)($row['actions_count'] ?? 0);
        $row['actions_done'] = (int)($row['actions_done'] ?? 0);
        $row['total_phase_days'] = (int)($row2['total_phase_days'] ?? 0);
        $row['total_price'] = (int)($row['total_price'] ?? 0);

        return $row;
    }

    public function getSumPrecioLogradaByUser($userId) {
        $sql = "SELECT SUM(COALESCE(pa.precio, 0)) AS total
                FROM project_actions pa
                INNER JOIN projects p ON p.id = pa.project_id
                WHERE p.user_id = ? AND UPPER(TRIM(COALESCE(pa.status, ''))) = 'LOGRADA'";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$userId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $val = $row && isset($row['total']) ? (float)$row['total'] : 0;
        return (int)round($val);
    }

    /**
     * Suma total del precio de acciones con status 'Pendiente' o 'En Progreso' por usuario
     */
    public function getSumPrecioPendienteEnProgresoByUser($userId) {
        $sql = "SELECT SUM(COALESCE(pa.precio, 0)) AS total
                FROM project_actions pa
                INNER JOIN projects p ON p.id = pa.project_id
                WHERE p.user_id = ? AND pa.status IN ('Pendiente','En Progreso')";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$userId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $val = $row && isset($row["total"]) ? (float)$row["total"] : 0;
        return (int)round($val);
    }
}
User / IP	: 216.73.216.159
Host / Server	: 45.84.207.204 / aircan.me
System	: Linux lt-bnk-web1726.main-hosting.eu 5.14.0-611.36.1.el9_7.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Mar 3 11:23:52 EST 2026 x86_64