Tul xxx Tul

Viewing: Payment.php

<?php
class Payment {
    private $db;
    
    public function __construct() {
        $this->db = (new Database())->connect();
        // Garantizar columnas adicionales necesarias para Nota de Cobro
        $this->ensureSchema();
    }
    
    public function getByUser($user_id) {
        $stmt = $this->db->prepare('SELECT * FROM payments WHERE user_id = ? ORDER BY created_at DESC');
        $stmt->execute([$user_id]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getAll() {
        $stmt = $this->db->query('SELECT p.*, u.name as user_name FROM payments p LEFT JOIN users u ON p.user_id = u.id ORDER BY p.payment_date DESC, p.is_nota_cobro ASC, p.created_at DESC');
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getById($id) {
        $stmt = $this->db->prepare('SELECT p.*, u.name as user_name FROM payments p LEFT JOIN users u ON p.user_id = u.id WHERE p.id = ?');
        $stmt->execute([$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    public function create($data) {
        try {
            $sql = 'INSERT INTO payments (
                user_id, amount, payment_method, reference, description, status, payment_date, comprobante,
                is_nota_cobro, nota_cobro_doc, subtotal, total, emprendimiento_id,
                consecutive, issue_city, issue_date, due_date,
                service_start_date, service_end_date, service_city,
                iva_percent, iva_amount, retefuente_percent, retefuente_amount, ica_percent, ica_amount
            ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
            $stmt = $this->db->prepare($sql);
            $ok = $stmt->execute([
                $data['user_id'],
                $data['amount'],
                $data['payment_method'],
                $data['reference'],
                $data['description'],
                $data['status'],
                $data['payment_date'],
                $data['comprobante'] ?? null,
                isset($data['is_nota_cobro']) ? (int)$data['is_nota_cobro'] : 0,
                $data['nota_cobro_doc'] ?? null,
                $data['subtotal'] ?? null,
                $data['total'] ?? null,
                $data['emprendimiento_id'] ?? null,
                $data['consecutive'] ?? null,
                $data['issue_city'] ?? null,
                $data['issue_date'] ?? null,
                $data['due_date'] ?? null,
                $data['service_start_date'] ?? null,
                $data['service_end_date'] ?? null,
                $data['service_city'] ?? null,
                $data['iva_percent'] ?? null,
                $data['iva_amount'] ?? null,
                $data['retefuente_percent'] ?? null,
                $data['retefuente_amount'] ?? null,
                $data['ica_percent'] ?? null,
                $data['ica_amount'] ?? null,
            ]);
            if ($ok) {
                return (int)$this->db->lastInsertId();
            }
            // Log del error SQL si falla
            error_log('Payment create failed - SQL Error: ' . print_r($stmt->errorInfo(), true));
            return false;
        } catch (Exception $e) {
            // Log del error de excepción
            error_log('Payment create exception: ' . $e->getMessage());
            error_log('Payment data: ' . print_r($data, true));
            return false;
        }
    }
    
    public function update($id, $data) {
        // Obtener datos actuales para preservar campos no proporcionados
        $current = $this->getById($id);
        if (!$current) {
            return false;
        }
        
        $sql = 'UPDATE payments SET 
            user_id = ?, amount = ?, payment_method = ?, reference = ?, description = ?, status = ?, payment_date = ?, comprobante = ?,
            is_nota_cobro = ?, nota_cobro_doc = ?, subtotal = ?, total = ?, emprendimiento_id = ?,
            consecutive = ?, issue_city = ?, issue_date = ?, due_date = ?,
            service_start_date = ?, service_end_date = ?, service_city = ?,
            iva_percent = ?, iva_amount = ?, retefuente_percent = ?, retefuente_amount = ?, ica_percent = ?, ica_amount = ?
            WHERE id = ?';
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([
            $data['user_id'] ?? $current['user_id'],
            isset($data['amount']) ? $data['amount'] : $current['amount'],
            $data['payment_method'] ?? $current['payment_method'],
            isset($data['reference']) ? $data['reference'] : $current['reference'],
            isset($data['description']) ? $data['description'] : $current['description'],
            $data['status'] ?? $current['status'],
            isset($data['payment_date']) ? $data['payment_date'] : $current['payment_date'],
            isset($data['comprobante']) ? $data['comprobante'] : $current['comprobante'],
            isset($data['is_nota_cobro']) ? (int)$data['is_nota_cobro'] : ($current['is_nota_cobro'] ?? 0),
            isset($data['nota_cobro_doc']) ? $data['nota_cobro_doc'] : $current['nota_cobro_doc'],
            isset($data['subtotal']) ? $data['subtotal'] : $current['subtotal'],
            isset($data['total']) ? $data['total'] : $current['total'],
            isset($data['emprendimiento_id']) ? $data['emprendimiento_id'] : $current['emprendimiento_id'],
            isset($data['consecutive']) ? $data['consecutive'] : $current['consecutive'],
            isset($data['issue_city']) ? $data['issue_city'] : $current['issue_city'],
            isset($data['issue_date']) ? $data['issue_date'] : $current['issue_date'],
            isset($data['due_date']) ? $data['due_date'] : $current['due_date'],
            isset($data['service_start_date']) ? $data['service_start_date'] : $current['service_start_date'],
            isset($data['service_end_date']) ? $data['service_end_date'] : $current['service_end_date'],
            isset($data['service_city']) ? $data['service_city'] : $current['service_city'],
            isset($data['iva_percent']) ? $data['iva_percent'] : $current['iva_percent'],
            isset($data['iva_amount']) ? $data['iva_amount'] : $current['iva_amount'],
            isset($data['retefuente_percent']) ? $data['retefuente_percent'] : $current['retefuente_percent'],
            isset($data['retefuente_amount']) ? $data['retefuente_amount'] : $current['retefuente_amount'],
            isset($data['ica_percent']) ? $data['ica_percent'] : $current['ica_percent'],
            isset($data['ica_amount']) ? $data['ica_amount'] : $current['ica_amount'],
            $id
        ]);
    }
    
    public function delete($id) {
        $stmt = $this->db->prepare('DELETE FROM payments WHERE id = ?');
        return $stmt->execute([$id]);
    }
    
    // Obtener total de pagos recibidos por usuario
    public function getTotalByUser($user_id) {
        $stmt = $this->db->prepare('SELECT SUM(amount) as total FROM payments WHERE user_id = ? AND status = "Recibido"');
        $stmt->execute([$user_id]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['total'] ?? 0;
    }
    
    // Obtener total de pagos pendientes por usuario
    public function getTotalPendingByUser($user_id) {
        $stmt = $this->db->prepare('SELECT SUM(amount) as total FROM payments WHERE user_id = ? AND status = "Pendiente"');
        $stmt->execute([$user_id]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['total'] ?? 0;
    }
    
    // Obtener total abonado a proyectos por usuario
    public function getTotalPaidToProjects($user_id) {
        $stmt = $this->db->prepare('SELECT SUM(paid_amount) as total FROM projects WHERE user_id = ?');
        $stmt->execute([$user_id]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['total'] ?? 0;
    }
    
    // Calcular saldo disponible: Pagos aprobados - Abonos a proyectos
    public function getAvailableBalance($user_id) {
        $totalPayments = $this->getTotalByUser($user_id);
        $totalPaidToProjects = $this->getTotalPaidToProjects($user_id);
        return $totalPayments - $totalPaidToProjects;
    }
    
    // Obtener estadísticas detalladas del usuario
    public function getUserStats($user_id) {
        $stats = [
            'total_payments' => $this->getTotalByUser($user_id),
            'pending_payments' => $this->getTotalPendingByUser($user_id),
            'total_paid_to_projects' => $this->getTotalPaidToProjects($user_id),
            'available_balance' => $this->getAvailableBalance($user_id)
        ];
        
        // Calcular porcentaje de uso
        if ($stats['total_payments'] > 0) {
            $stats['usage_percentage'] = round(($stats['total_paid_to_projects'] / $stats['total_payments']) * 100, 2);
        } else {
            $stats['usage_percentage'] = 0;
        }
        
        return $stats;
    }
    
    // Obtener pagos por estado
    public function getPaymentsByStatus($user_id, $status) {
        $stmt = $this->db->prepare('SELECT * FROM payments WHERE user_id = ? AND status = ? ORDER BY created_at DESC');
        $stmt->execute([$user_id, $status]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    // Obtener pagos por método
    public function getPaymentsByMethod($user_id, $method) {
        $stmt = $this->db->prepare('SELECT * FROM payments WHERE user_id = ? AND payment_method = ? ORDER BY created_at DESC');
        $stmt->execute([$user_id, $method]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    // Obtener siguiente consecutivo tipo NC-YYYY-####
    public function getNextConsecutive($prefix = 'NC') {
        $year = date('Y');
        $like = $prefix . '-' . $year . '-%';
        $stmt = $this->db->prepare('SELECT consecutive FROM payments WHERE consecutive LIKE ? ORDER BY id DESC LIMIT 1');
        try {
            $stmt->execute([$like]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            $last = $row['consecutive'] ?? '';
            $n = 0;
            if ($last) {
                $parts = explode('-', $last);
                $numPart = end($parts);
                if (ctype_digit($numPart)) {
                    $n = (int)$numPart;
                }
            }
            $next = $n + 1;
            return $prefix . '-' . $year . '-' . str_pad((string)$next, 4, '0', STR_PAD_LEFT);
        } catch (Throwable $e) {
            // fallback en caso de error
            return $prefix . '-' . $year . '-0001';
        }
    }
    
    public function getStatusOptions() {
        return [
            'Pendiente' => 'Pendiente',
            'Recibido' => 'Recibido',
            'Rechazado' => 'Rechazado',
            'Cancelado' => 'Cancelado'
        ];
    }
    
    public function getPaymentMethodOptions() {
        return [
            'Transferencia Bancaria' => 'Transferencia Bancaria',
            'PSE' => 'PSE',
            'Efectivo' => 'Efectivo',
            'Tarjeta de Crédito' => 'Tarjeta de Crédito',
            'Tarjeta de Débito' => 'Tarjeta de Débito',
            'Nequi' => 'Nequi',
            'Daviplata' => 'Daviplata',
            'PayPal' => 'PayPal',
            'Otro' => 'Otro'
        ];
    }
    
    // ==========================
    //  Schema helper (migración)
    // ==========================
    private function ensureSchema() {
        try {
            if (!$this->columnExists('payments', 'is_nota_cobro')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN is_nota_cobro TINYINT(1) DEFAULT 0 AFTER comprobante');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'nota_cobro_doc')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN nota_cobro_doc VARCHAR(255) DEFAULT NULL AFTER is_nota_cobro');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'subtotal')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN subtotal DECIMAL(10,2) DEFAULT NULL AFTER nota_cobro_doc');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'total')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN total DECIMAL(10,2) DEFAULT NULL AFTER subtotal');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'emprendimiento_id')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN emprendimiento_id INT NULL AFTER total');
            }
        } catch (Throwable $e) { /* noop */ }
        // Nuevos campos para notas: consecutivo, fechas, ciudades y tributos
        try {
            if (!$this->columnExists('payments', 'consecutive')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN consecutive VARCHAR(50) NULL AFTER emprendimiento_id');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'issue_city')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN issue_city VARCHAR(100) NULL AFTER consecutive');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'issue_date')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN issue_date DATE NULL AFTER issue_city');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'due_date')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN due_date DATE NULL AFTER issue_date');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'service_start_date')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN service_start_date DATE NULL AFTER due_date');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'service_end_date')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN service_end_date DATE NULL AFTER service_start_date');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'service_city')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN service_city VARCHAR(100) NULL AFTER service_end_date');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'iva_percent')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN iva_percent DECIMAL(5,2) NULL AFTER service_city');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'iva_amount')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN iva_amount DECIMAL(10,2) NULL AFTER iva_percent');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'retefuente_percent')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN retefuente_percent DECIMAL(5,2) NULL AFTER iva_amount');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'retefuente_amount')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN retefuente_amount DECIMAL(10,2) NULL AFTER retefuente_percent');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'ica_percent')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN ica_percent DECIMAL(5,2) NULL AFTER retefuente_amount');
            }
        } catch (Throwable $e) { /* noop */ }
        try {
            if (!$this->columnExists('payments', 'ica_amount')) {
                $this->db->exec('ALTER TABLE payments ADD COLUMN ica_amount DECIMAL(10,2) NULL AFTER ica_percent');
            }
        } catch (Throwable $e) { /* noop */ }
    }
    
    private function columnExists($table, $column) {
        $sql = 'SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?';
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$table, $column]);
        return (int)$stmt->fetchColumn() > 0;
    }
}
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