<?php

namespace App\Modelos;

use Core\Database;

class Proveedor extends Database
{
    protected $table    = 'app_proveedores';
    protected $id       = 'prov_id';

    public function proveedoresActivos()
    {
        return $this->consultar("SELECT * FROM app_proveedores WHERE prov_estatus = 1")->all();
    }

    public function proveedores()
    {
        return $this->consultar("SELECT * FROM app_proveedores")->all();
    }

    public function proveedorForaneo($id_proveedor)
    {
        return $this->consultar("SELECT * FROM app_proveedores p JOIN app_productos_proveedores pp ON p.prov_id=pp.pp_provid WHERE p.prov_id=$id_proveedor")->row();
    }

    public function modificarEstatus($id, $estatus)
    {
        return $this->consultar("UPDATE app_proveedores SET prov_estatus=$estatus WHERE prov_id=$id")->run();
    }

    public function condicinoal($condicion)
    {
        return $this->consultar("$condicion")->row();
    }

    public function chuequearCliente($condicion)
    {
        return $this->consultar("SELECT cli_id FROM app_clientes WHERE $condicion")->row();
    }

    public function obtenerFacturasAnuales(int $id_proveedor, int $anio)
    {
        return $this->consultar("SELECT cmp.cmp_id, cmp.cmp_correlativo, cmp.cmp_fecha, cmp.cmp_total, d.*, c.prov_id, c.prov_cifnif, c.prov_razonsocial 
        FROM app_compras cmp
        JOIN app_divisas d ON d.div_id = cmp.cmp_divid
        JOIN app_proveedores c ON c.prov_id = cmp.cmp_cliid 
        WHERE cmp.cmp_cliid = $id_proveedor AND cmp.cmp_estatus = 1 
        AND year(cmp.cmp_fecha) = '$anio' 
        ORDER BY cmp.cmp_id DESC")->all();
    }

    public function obtenerFacturasAnualesPendientes(int $id_proveedor, int $anio)
    {
        return $this->consultar("SELECT cmp.cmp_id, cmp.cmp_correlativo, cmp.cmp_fecha, cmp.cmp_total, d.*, c.prov_id, c.prov_cifnif, c.prov_razonsocial 
        FROM app_compras cmp
        JOIN app_divisas d ON d.div_id = cmp.cmp_divid
        JOIN app_proveedores c ON c.prov_id = cmp.cmp_cliid 
        WHERE cmp.cmp_cliid = $id_proveedor AND (cmp.cmp_estatus=0 OR cmp.cmp_estatus=2) 
        AND year(cmp.cmp_fecha) = '$anio' 
        ORDER BY cmp.cmp_id DESC")->all();
    }

    public function obtenerFacturasAnualesAnuladas(int $id_proveedor, int $anio)
    {
        return $this->consultar("SELECT cmp.cmp_id, cmp.cmp_correlativo, cmp.cmp_fecha, cmp.cmp_total, d.*, c.prov_id, c.prov_cifnif, c.prov_razonsocial 
        FROM app_compras cmp
        JOIN app_divisas d ON d.div_id = cmp.cmp_divid
        JOIN app_proveedores c ON c.prov_id = cmp.cmp_cliid 
        WHERE cmp.cmp_cliid = $id_proveedor AND cmp.cmp_estatus = 3 
        AND year(cmp.cmp_fecha) = '$anio' 
        ORDER BY cmp.cmp_id DESC")->all();
    }

    public function obtenerNotasdeEntregaAnuales(int $id_proveedor, int $anio)
    {
        return $this->consultar("SELECT cmp.cmpn_id, cmp.cmpn_correlativo, cmp.cmpn_fecha, cmp.cmpn_total, d.*, c.prov_id, c.prov_cifnif, c.prov_razonsocial 
        FROM app_compras_notas cmp
        JOIN app_divisas d ON d.div_id = cmp.cmpn_divid
        JOIN app_proveedores c ON c.prov_id = cmp.cmpn_cliid 
        WHERE cmp.cmpn_cliid = $id_proveedor AND cmp.cmpn_estatus = 1 
        AND year(cmp.cmpn_fecha) = '$anio'        
        ORDER BY cmp.cmpn_id DESC")->all();
    }

    public function obtenerNotasdeEntregaAnualesPendientes(int $id_proveedor, int $anio)
    {
        return $this->consultar("SELECT cmp.cmpn_id, cmp.cmpn_correlativo, cmp.cmpn_fecha, cmp.cmpn_total, d.*, c.prov_id, c.prov_cifnif, c.prov_razonsocial 
        FROM app_compras_notas cmp
        JOIN app_divisas d ON d.div_id = cmp.cmpn_divid
        JOIN app_proveedores c ON c.prov_id = cmp.cmpn_cliid 
        WHERE cmp.cmpn_cliid = $id_proveedor AND (cmp.cmpn_estatus=0 OR cmp.cmpn_estatus=2)  
        AND year(cmp.cmpn_fecha) = '$anio'        
        ORDER BY cmp.cmpn_id DESC")->all();
    }

    public function obtenerNotasdeEntregaAnualesAnuladas(int $id_proveedor, int $anio)
    {
        return $this->consultar("SELECT cmp.cmpn_id, cmp.cmpn_correlativo, cmp.cmpn_fecha, cmp.cmpn_total, d.*, c.prov_id, c.prov_cifnif, c.prov_razonsocial 
        FROM app_compras_notas cmp
        JOIN app_divisas d ON d.div_id = cmp.cmpn_divid
        JOIN app_proveedores c ON c.prov_id = cmp.cmpn_cliid 
        WHERE cmp.cmpn_cliid = $id_proveedor AND cmp.cmpn_estatus = 3 
        AND year(cmp.cmpn_fecha) = '$anio'        
        ORDER BY cmp.cmpn_id DESC")->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 obtenerDivisaMaster()
    {
        return $this->consultar("SELECT * FROM app_divisas WHERE div_predeterminado = 1")->row();
    }

    public function obtenerListaProveedores()
    {
        return $this->consultar("SELECT * FROM app_proveedor_lista_productos pro 
        LEFT JOIN app_proveedores prov ON pro.pprov_proveedor=prov.prov_id ")->all();
    }

    public function obtenerProveedor($producto)
    {
        return $this->consultar("SELECT * FROM app_proveedor_lista_productos WHERE pprov_producto='$producto'")->row();
    }

    public function eliminarProv($id)
    {
        return $this->consultar("DELETE FROM app_proveedor_lista_productos WHERE pprov_id = $id")->run();
    }

    public function eliminarProveedor($id)
    {
        return $this->consultar("DELETE FROM app_proveedor_lista_productos WHERE pprov_proveedor = $id")->run();
    }
    
    public function obtenerMovimientoNota($proveedor)
    {
            return $this->consultar("SELECT * 
            FROM app_movimientos fa 
            JOIN app_sucursales su ON su.su_id=fa.fab_sucursal  
            JOIN app_cuentas_metodopago cm ON cm.cmp_id = fa.fab_cmpid
            JOIN app_bancos_cuentas bc ON bc.bc_id = cm.cmp_cueid
            JOIN app_bancos b ON b.ban_id = bc.bc_banid 
            JOIN app_metodopago_divisa md ON  md.md_id = cm.cmp_mdid 
            JOIN app_metodopago m ON m.mp_id = md.md_mpid
            JOIN app_divisas d ON d.div_id = md.md_divid 
            JOIN app_compras_notas cmpn ON cmpn.cmpn_id = fa.fab_idgenerico  
            JOIN app_proveedores c ON c.prov_id = cmpn.cmpn_cliid 
            WHERE fa.fab_doc=9 AND fa.fab_retiro=1 AND fa.fab_categoria=2 AND cmpn.cmpn_cliid = $proveedor ORDER BY cmpn.cmpn_correlativo DESC")->all();
    
    }

    public function obtenerMovimientoFactura($proveedor)
    {
            return $this->consultar("SELECT * 
            FROM app_movimientos fa 
            JOIN app_sucursales su ON su.su_id=fa.fab_sucursal  
            JOIN app_cuentas_metodopago cm ON cm.cmp_id = fa.fab_cmpid 
            JOIN app_bancos_cuentas bc ON bc.bc_id = cm.cmp_cueid 
            JOIN app_bancos b ON b.ban_id = bc.bc_banid 
            JOIN app_metodopago_divisa md ON  md.md_id = cm.cmp_mdid 
            JOIN app_metodopago m ON m.mp_id = md.md_mpid 
            JOIN app_divisas d ON d.div_id = md.md_divid 
            JOIN app_compras cmpn ON cmpn.cmp_id = fa.fab_idgenerico  
            JOIN app_proveedores c ON c.prov_id = cmpn.cmp_cliid 
            WHERE fa.fab_doc=8 AND fa.fab_retiro=1 AND fa.fab_categoria=2 AND cmpn.cmp_cliid = $proveedor ORDER BY cmpn.cmp_correlativo DESC")->all();
    }
}
