<?php

namespace App\Modelos;

use Core\Database;

class Cliente extends Database
{
    protected $table    = 'app_clientes';
    protected $id       = 'cli_id';

    public function contarClientes()
    {
        return $this->consultar('SELECT * FROM app_clientes')->count();
    }

    public function obtenerClientes()
    {
        return $this->consultar('SELECT cli_dni, cli_id, cli_razon_social, cli_telefono, cli_email, cli_estatus 
        FROM app_clientes ac 
        LEFT JOIN app_tipo_documentos tp ON tp.tipodoc_id = ac.cli_tipodocid')->all();
    }

    public function obtenerClienteDNI($cli_dni)
    {
        return $this->consultar("SELECT * FROM app_clientes ac LEFT JOIN app_tipo_documentos tp ON tp.tipodoc_id = ac.cli_tipodocid WHERE cli_dni='$cli_dni'")->row();
    }

    public function obtenerClienteNombre($cli_nombre)
    {
        return $this->consultar("SELECT * FROM app_clientes ac LEFT JOIN app_tipo_documentos tp ON tp.tipodoc_id = ac.cli_tipodocid WHERE cli_razon_social='$cli_nombre'")->row();
    }

    public function obtenerClienteID($cli_id)
    {
        return $this->consultar("SELECT * FROM app_clientes ac LEFT JOIN app_tipo_documentos tp ON tp.tipodoc_id = ac.cli_tipodocid WHERE cli_id=$cli_id")->row();
    }

    public function estatus($id_cliente)
    {
        return $this->consultar("SELECT cli_estatus FROM app_clientes WHERE cli_id=$id_cliente")->row();
    }

    public function modificarEstatus($id_cliente, $estatus)
    {
        return $this->consultar("UPDATE app_clientes SET cli_estatus = $estatus WHERE cli_id = $id_cliente")->run();
    }

    public function totalClientesActivos()
    {
        return $this->consultar('SELECT * FROM app_clientes WHERE cli_estatus=1')->count();
    }

    public function totalClientesInactivos()
    {
        return $this->consultar('SELECT * FROM app_clientes WHERE cli_estatus=0')->count();
    }

    public function totalClientesGrupos()
    {
        return $this->consultar('SELECT cli_id FROM app_clientes c JOIN app_clientes_grupos cg WHERE cg.grup_id=c.cli_grupid AND cli_estatus=1')->count();
    }

    public function obtenerDescuento($id_cliente)
    {
        return $this->consultar("SELECT cli_descuento FROM app_clientes WHERE cli_id=$id_cliente")->row();
    }

    public function correoExiste($id_cliente, $correo)
    {
        return $this->consultar("SELECT * FROM app_clientes WHERE cli_id!=$id_cliente AND cli_email='$correo'")->row();
    }

    public function cedulaExiste($id_cliente, $dni)
    {
        return $this->consultar("SELECT * FROM app_clientes WHERE cli_id!=$id_cliente AND cli_dni='$dni'")->row();
    }

    public function obtenerFacturas($id_cliente)
    {
        return $this->consultar("SELECT fm.fm_id, fm.fm_correlativo, fm.fm_total, DATE_FORMAT(fm.fm_fecha, '%d-%m-%Y %h:%i') as fecha, fm.fm_estatus,fm.fm_divid, d.*, s.ser_codigo,su.* 
        FROM app_factura_master fm 
        JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
        JOIN app_series s ON s.ser_id = fm.fm_serid
        JOIN app_divisas d ON d.div_id = fm.fm_divid
        WHERE fm.fm_cliid=$id_cliente")->all();
    }

    public function obtenerFacturasPendientes($id_cliente)
    {
        return $this->consultar("SELECT fm.fm_id, fm.fm_correlativo, fm.fm_total, DATE_FORMAT(fm.fm_fecha, '%d-%m-%Y %h:%i') as fecha, fm.fm_estatus, d.*, s.ser_codigo
        FROM app_factura_master fm 
        JOIN app_series s ON s.ser_id = fm.fm_serid
        JOIN app_divisas d ON d.div_id = fm.fm_divid
        WHERE (fm.fm_estatus = 0 OR fm.fm_estatus = 2) AND fm.fm_cliid=$id_cliente")->all();
    }

    public function obtenerAcumulado($id_factura, $documento)
    {
        return $this->consultar("SELECT SUM(fab_conversion) as total FROM app_movimientos WHERE fab_idgenerico=$id_factura AND fab_doc=$documento")->row();
    }

    public function obtenerAcumuladoMovimFactura($id_factura)
    {
        return $this->consultar("SELECT * FROM app_movimientos m 
        JOIN app_factura_master fm ON fm.fm_id=m.fab_idgenerico 
        JOIN app_divisas d ON d.div_id = fm.fm_divid 
        JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
        WHERE fab_idgenerico=$id_factura AND fab_doc=2")->all();
    }

    public function obtenerAcumuladoMovimNota($id_factura)
    {
        return $this->consultar("SELECT * FROM app_movimientos m 
        JOIN app_notas_de_entrega ne ON ne.ne_id=m.fab_idgenerico 
        JOIN app_divisas d ON d.div_id = ne.ne_divid 
        JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
        WHERE m.fab_idgenerico=$id_factura AND m.fab_doc=1")->all();
    }

    public function obtenerNotasdeentrega($id_cliente)
    {
        return $this->consultar("SELECT ne.ne_id, ne.ne_correlativo, ne.ne_total, DATE_FORMAT(ne.ne_fecha, '%d-%m-%Y %h:%i') as fecha, ne.ne_estatus,ne.ne_divid, d.*, s.ser_codigo,su.* 
        FROM app_notas_de_entrega ne 
        JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
        JOIN app_series s ON s.ser_id = ne.ne_serid
        JOIN app_divisas d ON d.div_id = ne.ne_divid
        WHERE ne.ne_cliid = $id_cliente AND ne.ne_id not in (SELECT ard.rd_facid_procedencia FROM app_ruta_documento ard WHERE rd_docid_procedencia=1 AND rd_docid_conversion =2)")->all();
    }

    public function obtenerOrdendeEntrega($id_cliente)
    {
        return $this->consultar("SELECT n.n_id, n.n_correlativo, n.n_total, DATE_FORMAT(n.n_fecha, '%d-%m-%Y %h:%i') as fecha, n.n_estatus, d.*, s.ser_codigo,su.* 
        FROM app_orden_de_entrega n 
        JOIN app_sucursales su ON su.su_id=n.n_surcid
        JOIN app_series s ON s.ser_id = n.n_serid
        JOIN app_divisas d ON d.div_id = n.n_divid
        WHERE n.n_cliid = $id_cliente AND n.n_id not in (SELECT ard.rd_facid_procedencia FROM app_ruta_documento ard WHERE rd_docid_procedencia=12 AND rd_docid_conversion =2)")->all();
    }

    public function obtenerNotasdeentregaPendientes($id_cliente)
    {
        return $this->consultar("SELECT ne.ne_id, ne.ne_correlativo, ne.ne_total, DATE_FORMAT(ne.ne_fecha, '%d-%m-%Y %h:%i') as fecha, ne.ne_estatus, d.*, s.ser_codigo
        FROM app_notas_de_entrega ne 
        JOIN app_series s ON s.ser_id = ne.ne_serid
        JOIN app_divisas d ON d.div_id = ne.ne_divid
        WHERE (ne.ne_estatus = 0 OR ne.ne_estatus = 2) AND ne.ne_cliid = $id_cliente AND ne.ne_id not in (SELECT ard.rd_facid_procedencia FROM app_ruta_documento ard WHERE rd_docid_procedencia=1 AND rd_docid_conversion =2)")->all();
    }

    public function obtenerPedidos($id_cliente)
    {
        return $this->consultar("SELECT pe.pe_id, pe.pe_correlativo, pe.pe_total, DATE_FORMAT(pe.pe_fecha, '%d-%m-%Y %h:%i') as fecha, pe.pe_estatus, d.*, s.ser_codigo,su.* 
        FROM app_pedidos  
        JOIN app_sucursales su ON su.su_id=pe.pe_surcid 
        JOIN app_series s ON s.ser_id = pe.pe_serid 
        JOIN app_divisas d ON d.div_id = pe.pe_divid
        WHERE pe.pe_cliid=$id_cliente")->all();
    }

    public function obtenerPresupuestos($id_cliente)
    {
        return $this->consultar("SELECT pre.pre_id, pre.pre_correlativo, pre.pre_total, DATE_FORMAT(pre.pre_fecha, '%d-%m-%Y %h:%i') as fecha, pre.pre_estatus, d.*, s.ser_codigo,su.* 
        FROM app_presupuesto pre 
        JOIN app_sucursales su ON su.su_id=pre.pre_surcid 
        JOIN app_series s ON s.ser_id = pre.pre_serid
        JOIN app_divisas d ON d.div_id = pre.pre_divid
        WHERE pre.pre_cliid=$id_cliente")->all();
    }

    public function chuequearProveedor($condicion)
    {
        return $this->consultar("SELECT prov_id FROM app_proveedores WHERE $condicion")->row();
    }

    public function obtenerCreditos($id_cliente)
    {
        return $this->consultar("SELECT nc.cre_id, nc.cre_docid, nc.cre_modulo, DATE_FORMAT(nc.cre_fecha, '%d-%m-%Y %h:%i') as fecha, nc.cre_descripcion, nc.cre_estatus, su.* 
        FROM app_nota_de_credito nc    
        JOIN app_sucursales su ON su.su_id=nc.cre_surcid     
        WHERE nc.cre_cliid = $id_cliente AND year(nc.cre_fecha) = year(current_date) ORDER BY nc.cre_fecha DESC")->all();
    }

    public function obtenerClientesDeNEVart($id)
    {
            return $this->consultar("SELECT SUM(neart_procantidad) as cantidad 
            FROM app_notas_de_entrega_articulos 
            WHERE neart_neid=$id")->all();
    }

    public function obtenerClientesDeFACVart($id)
    {
            return $this->consultar("SELECT SUM(fa_procantidad) as cantidad 
            FROM app_factura_articulos 
            WHERE fa_fmid=$id")->all();
    }

    public function obtenerClientesDeOrdart($id)
    {
            return $this->consultar("SELECT SUM(nart_procantidad) as cantidad 
            FROM app_orden_de_entrega_articulos 
            WHERE nart_neid=$id")->all();
    }

    public function obtenerClientesDeNEV($id_cliente, $fecha_desde, $fecha_hasta,$divisa)
    {
            return $this->consultar("SELECT ne.ne_id,ne.ne_cliid, ne.ne_divid as divisa,ne.total_procosto as total_costo, ne.ne_total AS total
            FROM  app_notas_de_entrega ne 
            JOIN app_clientes c ON c.cli_id = ne.ne_cliid 
            WHERE ne.ne_estatus < 3 AND ne.ne_divid = $divisa AND (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_cliid = $id_cliente AND ne.ne_id not in (SELECT ard.rd_facid_procedencia FROM app_ruta_documento ard WHERE rd_docid_procedencia=1 AND rd_docid_conversion =2) GROUP BY ne.ne_id")->all();
    }

    public function obtenerClientesDeFACV($id_cliente, $fecha_desde, $fecha_hasta,$divisa)
    {
            return $this->consultar("SELECT fm.fm_id, fm.fm_cliid, fm.fm_divid as divisa, fm.total_procosto as total_costo, fm.fm_total AS total 
            FROM app_factura_master fm 
            JOIN app_clientes c ON c.cli_id = fm.fm_cliid 
            WHERE fm.fm_estatus < 3 AND fm.fm_divid = $divisa 
            AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
            AND fm.fm_cliid = $id_cliente GROUP BY fm.fm_id")->all();
    }

    public function obtenerClientesDeOrd($id_cliente, $fecha_desde, $fecha_hasta,$divisa)
    {
            return $this->consultar("SELECT n.n_id,n.n_cliid, n.n_divid as divisa,n.total_procosto as total_costo, n.n_total AS total 
            FROM app_orden_de_entrega n 
            JOIN app_clientes c ON c.cli_id = n.n_cliid 
            WHERE n.n_estatus < 3 AND n.n_divid = $divisa AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND n.n_cliid = $id_cliente AND n.n_id not in (SELECT ard.rd_facid_procedencia FROM app_ruta_documento ard WHERE rd_docid_procedencia=12 AND rd_docid_conversion =2)
             GROUP BY n.n_id")->all();
    }

    public function obtenerDivisa($id_divisa)
    {
        return $this->consultar("SELECT * FROM app_divisas WHERE div_id = $id_divisa")->row();
    }

    public function obtenerDivisas()
    {
        return $this->consultar("SELECT * FROM app_divisas WHERE div_status = 1")->all();
    }

    public function obtenerCliRedesSociales($id_cliente)
    {
        return $this->consultar("SELECT * FROM app_clientes_redes_sociales WHERE clird_cliid = $id_cliente")->all();
    }

    public function obtenerDivisaMaster()
    {
        return $this->consultar("SELECT * FROM app_divisas WHERE div_predeterminado = 1")->row();
    }
    public function divisaFactor($div_master, $div_deseada)
    {
        return $this->consultar("SELECT df_factor FROM app_divisas_factor WHERE df_divid_master=$div_master AND df_divid = $div_deseada")->row();
    }
}
