<?php namespace App\Models;

use CodeIgniter\Model;

class ReincidenteModelo extends Model {
    protected $table = 'reincidente';
    protected $primaryKey = 'id_reincidente';
    protected $allowedFields = ['id_reincidente','cliente_id','categoria','fecha_reincidente','incidentes','con_mantenimiento','fecha_mantenimiento','motivo', 'fecha_registro', 'eliminacion_logica'];

    public function listaGeneralReincidentesBk() {
        $consulta = 'SELECT 
            c.numero_cliente,
            c.id_cliente,
            r.categoria,
            r.fecha_reincidente,
            r.incidentes,
            r.con_mantenimiento,
            r.fecha_mantenimiento,
            r.motivo,
            g.reincidente_id,
            g.gestionable,
            g.accion_id,
            g.subaccion_id,
            a.accion,
            s.subaccion,
            g.status,
            g.fecha_gestion
        FROM cliente c
        INNER JOIN reincidente r ON c.reincidente_id = r.id_reincidente
        INNER JOIN gestion g ON r.id_reincidente = g.reincidente_id
        INNER JOIN accion a ON g.accion_id = a.id_accion
        INNER JOIN subaccion s ON g.subaccion_id = s.id_subaccion
        WHERE g.status NOT IN ("Completado")';

        $obtieneConsulta = $this->db->query($consulta);
        //$obtieneConsulta->getResult();
        if ($obtieneConsulta->getNumRows() > 0) {
            return $obtieneConsulta->getResultArray();
        } else {
            return FALSE;
        }
    }

    public function listaGeneralReincidentes($orderBy,$where,$length,$start) {
        if(strlen($where) > 0){
            $sSelect = "SELECT 
                c.numero_cliente,
                c.id_cliente,
                r.categoria,
                r.fecha_reincidente,
                r.incidentes,
                r.con_mantenimiento,
                r.fecha_mantenimiento,
                r.motivo,
                g.reincidente_id,
                g.gestionable,
                g.accion_id,
                g.subaccion_id,
                a.accion,
                s.subaccion,
                g.status,
                g.fecha_gestion
            FROM cliente c
            INNER JOIN reincidente r ON c.reincidente_id = r.id_reincidente
            INNER JOIN gestion g ON r.id_reincidente = g.reincidente_id
            INNER JOIN accion a ON g.accion_id = a.id_accion
            INNER JOIN subaccion s ON g.subaccion_id = s.id_subaccion
            WHERE g.status NOT IN ('Completado')" .  " AND (" . $where . ")";
        } else {
            $sSelect = "SELECT 
                c.numero_cliente,
                c.id_cliente,
                r.categoria,
                r.fecha_reincidente,
                r.incidentes,
                r.con_mantenimiento,
                r.fecha_mantenimiento,
                r.motivo,
                g.reincidente_id,
                g.gestionable,
                g.accion_id,
                g.subaccion_id,
                a.accion,
                s.subaccion,
                g.status,
                g.fecha_gestion
            FROM cliente c
            INNER JOIN reincidente r ON c.reincidente_id = r.id_reincidente
            INNER JOIN gestion g ON r.id_reincidente = g.reincidente_id
            INNER JOIN accion a ON g.accion_id = a.id_accion
            INNER JOIN subaccion s ON g.subaccion_id = s.id_subaccion
            WHERE g.status NOT IN ('Completado')";
        }
    
        $consulta = $sSelect . "  " . $orderBy . " LIMIT ". $length ." OFFSET " .$start;

        $obtieneConsulta = $this->db->query($consulta);

        $totalRegistros = $this->db->query($sSelect);

        if ($obtieneConsulta->getNumRows() > 0) {
            $datos = [
                // 'draw' => "4",
                'data' => $obtieneConsulta->getResultArray(),
                'recordsTotal' => $totalRegistros->getNumRows(),
                'recordsFiltered' => (int)(($totalRegistros->getNumRows()*$length)/$length)
            ];
            return $datos;
        } else {
            return FALSE;
        }
    }
}
