<?php

namespace App\Modelos;

use Core\Database;

class Compras extends Database
{
    public function obtenerTiposdeDocumentos()
    {
        return $this->consultar("SELECT * FROM  app_tipo_documentos")->all();
    }

    public function buscarProveedores(string $buscar)
    {
        $argumento = strtolower($buscar);
        return $this->consultar("SELECT prov_id, prov_cifnif, prov_razonsocial FROM app_proveedores  WHERE prov_estatus = 1 AND (LOWER(prov_cifnif) LIKE  '%$argumento%' OR LOWER(prov_razonsocial) LIKE '%$argumento%')")->all();
    }

    public function obtenerDivisaMaster()
    {
        return $this->consultar("SELECT * FROM app_divisas WHERE div_predeterminado = 1")->row();
    }

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

    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 Series()
    {
        return $this->consultar("SELECT * FROM app_empleados_serie es 
        JOIN app_series s ON s.ser_id = es.empser_serid 
        JOIN app_sucursales su ON s.ser_sucursal = su.su_id
        WHERE es.empser_empid={$_SESSION['user_data']['emp_id']}")->all();
    }

    public function obtenerFacturasAnuales($peticion)
    {
        return $this->consultar("SELECT cmp.cmp_id, cmp.cmp_correlativo, cmp.cmp_fecha, cmp.cmp_total,cmp.cmp_numfactura, 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_estatus=1 AND $peticion ORDER BY cmp.cmp_id DESC")->all();
    }

    public function obtenerFacturasAnualesPendientes($peticion)
    {
        return $this->consultar("SELECT cmp.cmp_id, cmp.cmp_correlativo, cmp.cmp_fecha, cmp.cmp_total,cmp.cmp_numfactura, 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_estatus=0 OR cmp.cmp_estatus=2) AND $peticion ORDER BY cmp.cmp_id DESC")->all();
    }

    public function obtenerFacturasAnualesAnuladas($peticion)
    {
        return $this->consultar("SELECT cmp.cmp_id, cmp.cmp_correlativo, cmp.cmp_fecha, cmp.cmp_total,cmp.cmp_numfactura, 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_estatus=3 AND $peticion ORDER BY cmp.cmp_id DESC")->all();
    }

    public function obtenerNotasdeEntregaAnuales($peticion)
    {
        return $this->consultar("SELECT cmp.cmpn_id, cmp.cmpn_correlativo, cmp.cmpn_fecha, cmp.cmpn_total,cmp.cmpn_numfactura, 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_estatus=1 AND $peticion         
        ORDER BY cmp.cmpn_id DESC")->all();
    }

    public function obtenerNotasdeEntregaAnualesPendientes($peticion)
    {
        return $this->consultar("SELECT cmp.cmpn_id, cmp.cmpn_correlativo, cmp.cmpn_fecha, cmp.cmpn_total,cmp.cmpn_numfactura, 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_estatus=0 OR cmp.cmpn_estatus=2) AND $peticion          
        ORDER BY cmp.cmpn_id DESC")->all();
    }

    public function obtenerNotasdeEntregaAnualesAnuladas($peticion)
    {
        return $this->consultar("SELECT cmp.cmpn_id, cmp.cmpn_correlativo, cmp.cmpn_fecha, cmp.cmpn_total,cmp.cmpn_numfactura, 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_estatus=3 AND $peticion         
        ORDER BY cmp.cmpn_id DESC")->all();
    }

    public function obtenerPedidosAnuales($peticion)
    {
        return $this->consultar("SELECT *  
        FROM app_cmppedidos cmp
        JOIN app_divisas d ON d.div_id = cmp.cmppe_divid
        JOIN app_proveedores c ON c.prov_id = cmp.cmppe_cliid 
        WHERE cmp.cmppe_estatus=1 AND $peticion         
        ORDER BY cmp.cmppe_id DESC")->all();
    }

    public function obtenerPedidosAnualesPendientes($peticion)
    {
        return $this->consultar("SELECT *  
        FROM app_cmppedidos cmp
        JOIN app_divisas d ON d.div_id = cmp.cmppe_divid
        JOIN app_proveedores c ON c.prov_id = cmp.cmppe_cliid 
        WHERE (cmp.cmppe_estatus=0 OR cmp.cmppe_estatus=2) AND $peticion          
        ORDER BY cmp.cmppe_id DESC")->all();
    }

    public function obtenerPedidosAnualesAnuladas($peticion)
    {
        return $this->consultar("SELECT *  
        FROM app_cmppedidos cmp
        JOIN app_divisas d ON d.div_id = cmp.cmppe_divid
        JOIN app_proveedores c ON c.prov_id = cmp.cmppe_cliid 
        WHERE cmp.cmppe_estatus=3 AND $peticion         
        ORDER BY cmp.cmppe_id DESC")->all();
    }

    public function obtenerCreditosAnuales($peticion)
    {
        return $this->consultar("SELECT cp.crecmp_id, cp.crecmp_docid, cp.crecmp_modulo, DATE_FORMAT(cp.crecmp_fecha, '%d-%m-%Y %h:%i') as fecha, cp.crecmp_descripcion, p.prov_id, p.prov_cifnif, p.prov_razonsocial 
        FROM app_credito_compras cp
        JOIN app_proveedores p ON p.prov_id = cp.crecmp_provid 
        WHERE cp.crecmp_estatus = 0 AND $peticion ORDER BY cp.crecmp_fecha DESC")->all();
    }

    public function obtenerCreditosAnualesInactivos($peticion)
    {
        return $this->consultar("SELECT cp.crecmp_id, cp.crecmp_docid, cp.crecmp_modulo, DATE_FORMAT(cp.crecmp_fecha, '%d-%m-%Y %h:%i') as fecha, cp.crecmp_descripcion, p.prov_id, p.prov_cifnif, p.prov_razonsocial 
        FROM app_credito_compras cp
        JOIN app_proveedores p ON p.prov_id = cp.crecmp_provid 
        WHERE cp.crecmp_estatus = 1 AND $peticion ORDER BY cp.crecmp_fecha DESC")->all();
    }

    public function obtenerNotadeCreditoDetalle($id)
    {
        return $this->consultar("SELECT *, DATE_FORMAT(crecmpart_fecha, '%d-%m-%Y %h:%i') as fecha FROM app_credito_compras_pagos WHERE crecmpart_crecmpid = $id")->all();
    }

    public function buscarLineasNEV($buscar, $filtro, $fecha_desde, $fecha_hasta)
    {
        if ($filtro == 1) {
            return $this->consultar("SELECT cmpn.cmpn_divid, cmpn.cmpn_id, cmpn.cmpn_correlativo, c.prov_razonsocial, cmpnart.cmpnart_proid, cmpnart.cmpnart_procantidad, cmpnart.cmpnart_procodigo, cmpnart.cmpnart_propnombre, cmpnart.cmpnart_prototal, DATE_FORMAT(cmpn.cmpn_fecha, '%d-%m-%Y')  as fecha
            FROM app_compras_notas_articulos cmpnart
            JOIN app_compras_notas cmpn ON cmpn.cmpn_id = cmpnart.cmpnart_cmpnid 
            JOIN app_proveedores c ON c.prov_id = cmpn.cmpn_cliid 
            WHERE (DATE(cmpn.cmpn_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND (LOWER(cmpnart.cmpnart_procodigo) LIKE '%$buscar%' OR LOWER(cmpnart.cmpnart_propnombre) LIKE '%$buscar%') AND cmpn.cmpn_id NOT IN (SELECT ard.rd_facid_procedencia FROM app_ruta_documento ard WHERE rd_docid_procedencia=9 AND rd_docid_conversion =8)
            ORDER BY cmpn.cmpn_fecha DESC")->all();
        } elseif ($filtro == 2) {
            return $this->consultar("SELECT cmpn.cmpn_divid, cmpn.cmpn_id, cmpn.cmpn_correlativo, c.prov_razonsocial, cmpnart.cmpnart_proid, cmpnart.cmpnart_procantidad, cmpnart.cmpnart_procodigo, cmpnart.cmpnart_propnombre, cmpnart.cmpnart_prototal, DATE_FORMAT(cmpn.cmpn_fecha, '%d-%m-%Y')  as fecha
            FROM app_compras_notas_articulos cmpnart
            JOIN app_compras_notas cmpn ON cmpn.cmpn_id = cmpnart.cmpnart_cmpnid  
            JOIN app_proveedores c ON c.prov_id = cmpn.cmpn_cliid 
            WHERE (DATE(cmpn.cmpn_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND (LOWER(c.prov_cifnif) LIKE '%$buscar%' OR LOWER(c.prov_razonsocial) LIKE '%$buscar%') AND cmpn.cmpn_id NOT IN (SELECT ard.rd_facid_procedencia FROM app_ruta_documento ard WHERE rd_docid_procedencia=9 AND rd_docid_conversion =8)
            ORDER BY cmpn.cmpn_fecha DESC")->all();
        } else {
            return $this->consultar("SELECT cmpn.cmpn_divid, cmpn.cmpn_id, cmpn.cmpn_correlativo, c.prov_razonsocial, cmpnart.cmpnart_proid, cmpnart.cmpnart_procantidad, cmpnart.cmpnart_procodigo, cmpnart.cmpnart_propnombre, cmpnart.cmpnart_prototal, DATE_FORMAT(cmpn.cmpn_fecha, '%d-%m-%Y')  as fecha
            FROM app_compras_notas_articulos cmpnart
            JOIN app_compras_notas cmpn ON cmpn.cmpn_id = cmpnart.cmpnart_cmpnid 
            JOIN app_proveedores c ON c.prov_id = cmpn.cmpn_cliid 
            WHERE (DATE(cmpn.cmpn_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND (LOWER(cmpn.cmpn_observaciones) LIKE '%$buscar%') AND ne.ne_id NOT IN (SELECT ard.rd_facid_procedencia FROM app_ruta_documento ard WHERE rd_docid_procedencia=9 AND rd_docid_conversion =8)
            ORDER BY cmpn.cmpn_fecha DESC")->all();
        }
    }

    public function buscarLineasFACV($buscar, $filtro, $fecha_desde, $fecha_hasta)
    {
        if ($filtro == 1) {
            return $this->consultar("SELECT cmp.cmp_divid, cmp.cmp_id, cmp.cmp_correlativo, c.prov_razonsocial, cmpart.cmpart_proid, cmpart.cmpart_procantidad, cmpart.cmpart_procodigo, cmpart.cmpart_propnombre, cmpart.cmpart_prototal, DATE_FORMAT(cmp.cmp_fecha, '%d-%m-%Y')  as fecha
            FROM app_compras cmp
            JOIN app_compras_articulos cmpart ON cmp.cmp_id = cmpart.cmpart_cmpid
            JOIN app_proveedores c ON c.prov_id = cmp.cmp_cliid 
            WHERE (DATE(cmp.cmp_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND (LOWER(cmpart.cmpart_procodigo) LIKE  '%$buscar%' OR LOWER(cmpart.cmpart_propnombre) LIKE '%$buscar%') ORDER BY cmp.cmp_fecha DESC")->all();
        } elseif ($filtro == 2) {
            return $this->consultar("SELECT cmp.cmp_divid, cmp.cmp_id, cmp.cmp_correlativo, c.prov_razonsocial, cmpart.cmpart_proid, cmpart.cmpart_procantidad, cmpart.cmpart_procodigo, cmpart.cmpart_propnombre, cmpart.cmpart_prototal, DATE_FORMAT(cmp.cmp_fecha, '%d-%m-%Y')  as fecha
            FROM app_compras cmp
            JOIN app_compras_articulos cmpart ON cmp.cmp_id = cmpart.cmpart_cmpid
            JOIN app_proveedores c ON c.prov_id = cmp.cmp_cliid 
            WHERE (DATE(cmp.cmp_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND (LOWER(c.prov_cifnif) LIKE '%$buscar%' OR LOWER(c.prov_razonsocial) LIKE '%$buscar%') ORDER BY cmp.cmp_fecha DESC")->all();
        } else {
            return $this->consultar("SELECT cmp.cmp_divid, cmp.cmp_id, cmp.cmp_correlativo, c.prov_razonsocial, cmpart.cmpart_proid, cmpart.cmpart_procantidad, cmpart.cmpart_procodigo, cmpart.cmpart_propnombre, cmpart.cmpart_prototal, DATE_FORMAT(cmp.cmp_fecha, '%d-%m-%Y')  as fecha
            FROM app_compras cmp
            JOIN app_compras_articulos cmpart ON cmp.cmp_id = cmpart.cmpart_cmpid
            JOIN app_proveedores c ON c.prov_id = cmp.cmp_cliid 
            WHERE (DATE(cmp.cmp_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND (LOWER(cmp.cmp_observaciones) LIKE '%$buscar%') ORDER BY cmp.cmp_fecha DESC")->all();
        }
    }

    public function obtenerProveedoresNEV($sucursal, $fecha_desde, $fecha_hasta)
    {
        if ($sucursal == 0) {
            return $this->consultar("SELECT *  
                FROM app_notas_de_entrega_articulos neart 
                JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid
                JOIN app_sucursales s ON s.su_id=ne.ne_surcid 
                JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid 
                JOIN app_productos pr ON pr.pro_id=neart.neart_proid 
                WHERE ne.ne_estatus < 3 AND (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta')")->all();
        } else {
            return $this->consultar("SELECT *  
                 FROM app_notas_de_entrega_articulos neart 
                JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
                JOIN app_sucursales s ON s.su_id=ne.ne_surcid 
                JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid 
                JOIN app_productos pr ON pr.pro_id=neart.neart_proid 
                WHERE ne.ne_surcid=$sucursal AND ne_estatus < 3 AND (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta')")->all();
        }
    }

    public function obtenerProveedoresFact($sucursal, $fecha_desde, $fecha_hasta)
    {
        if ($sucursal == 0) {
            return $this->consultar("SELECT *  
                FROM app_factura_articulos fa 
                JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                JOIN app_productos pr ON pr.pro_id=fa.fa_proid 
                WHERE fm.fm_estatus < 3 AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta')")->all();
        } else {
            return $this->consultar("SELECT *  
                FROM app_factura_articulos fa 
                JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                JOIN app_productos pr ON pr.pro_id=fa.fa_proid 
                WHERE fm.fm_surcid=$sucursal AND fm_estatus < 3 AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta')")->all();
        }
    }

    public function obtenerProveedoresNEVAno($anio)
    {
        $mes=date('m');
        return $this->consultar("SELECT *  
                FROM app_notas_de_entrega_articulos neart 
                JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid
                JOIN app_sucursales s ON s.su_id=ne.ne_surcid 
                JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid 
                JOIN app_productos pr ON pr.pro_id=neart.neart_proid 
                WHERE ne.ne_estatus < 3 AND YEAR(ne.ne_fecha) = '$anio' AND MONTH(ne.ne_fecha)=$mes AND pr.pro_tipo != 3")->all();
    }

    public function obtenerProveedoresORDAno($anio)
    {
         $mes=date('m');
        return $this->consultar("SELECT *  
                FROM app_orden_de_entrega_articulos nart 
                JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid
                JOIN app_sucursales s ON s.su_id=n.n_surcid 
                JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid 
                JOIN app_productos pr ON pr.pro_id=nart.nart_proid 
                WHERE n.n_estatus < 3 AND YEAR(n.n_fecha) = '$anio' AND MONTH(n.n_fecha)=$mes AND pr.pro_tipo != 3")->all();
    }

    public function obtenerProveedoresFactAno($anio)
    {
         $mes=date('m');
        return $this->consultar("SELECT *  
                FROM app_factura_articulos fa 
                JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                JOIN app_productos pr ON pr.pro_id=fa.fa_proid 
                WHERE fm.fm_estatus < 3 AND YEAR(fm.fm_fecha) = '$anio' AND MONTH(fm.fm_fecha)=$mes AND  pr.pro_tipo != 3")->all();
    }

    public function obtenerProveedores($producto)
    {
        return $this->consultar("SELECT *  
        FROM app_productos_proveedores pv 
        JOIN app_proveedores p ON pv.pp_provid = p.prov_id 
        WHERE pv.pp_proid=$producto")->all();
    }

    public function obtenerProdProveedores($id)
    {
        return $this->consultar("SELECT *  
        FROM app_productos_proveedores pv 
        JOIN app_productos p ON pv.pp_proid = p.pro_id 
        WHERE pv.pp_provid=$id")->all();
    }

    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();
    }

    public function obtenerCierreMovimientocompletoNot($fecha_desde, $fecha_hasta, $proveedor, $divisa)
    {
        $peticion = ($divisa > 0) ? " AND fa.fab_divisa=$divisa" : "";

        if ($proveedor > 0) {
            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 (DATE(fa.fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND cmpn.cmpn_cliid = $proveedor $peticion")->all();
        } else {
            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 (DATE(fa.fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') $peticion")->all();
        }
    }

    public function obtenerCierreMovimientocompletoFact($fecha_desde, $fecha_hasta, $proveedor, $divisa)
    {
        $peticion = ($divisa > 0) ? " AND fa.fab_divisa=$divisa" : "";

        if ($proveedor > 0) {
            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 (DATE(fa.fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND cmpn.cmp_cliid = $proveedor $peticion")->all();
        } else {
            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 (DATE(fa.fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') $peticion")->all();
        }
    }

    public function obtenerFacturasAnualest($peticion)
    {
        return $this->consultar("SELECT cmp.cmp_id as documentos, YEAR(cmp.cmp_fecha) as anio, cmp.cmp_total as total, cmp.cmp_divid as divisa   
        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_estatus!=3 AND $peticion")->all();
    }

    public function obtenerNotasdeEntregaAnualest($peticion)
    {
        return $this->consultar("SELECT cmp.cmpn_id as documentos, YEAR(cmp.cmpn_fecha) as anio, cmp.cmpn_total as total, cmp.cmpn_divid as divisa   
        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_estatus!=3 AND $peticion ")->all();
    }

    public function obtenerFacturasAnualesTodos()
    {
        return $this->consultar("SELECT cmp.cmp_id as documentos, YEAR(cmp.cmp_fecha) as anio, cmp.cmp_total as total, cmp.cmp_divid as divisa   
        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_estatus!=3")->all();
    }

    public function obtenerNotasdeEntregaAnualesTodos()
    {
        return $this->consultar("SELECT cmp.cmpn_id as documentos, YEAR(cmp.cmpn_fecha) as anio, cmp.cmpn_total as total, cmp.cmpn_divid as divisa   
        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_estatus!=3 ")->all();
    }

    public function obtenerFacturasAnualesMes($peticion)
    {
        return $this->consultar("SELECT cmp.cmp_id as documentos,DATE_FORMAT(cmp.cmp_fecha, '%m') AS mes,  cmp.cmp_total as total, cmp.cmp_divid as divisa   
        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_estatus!=3 AND $peticion")->all();
    }

    public function obtenerNotasdeEntregaAnualesMes($peticion)
    {
        return $this->consultar("SELECT cmp.cmpn_id as documentos, DATE_FORMAT(cmp.cmpn_fecha, '%m') AS mes, cmp.cmpn_total as total, cmp.cmpn_divid as divisa   
        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_estatus!=3 AND $peticion ")->all();
    }

    public function obtenerFacturasAnualesMesIng($peticion)
    {
        if ($peticion != null) {
            return $this->consultar("SELECT cmp.cmp_id as documentos, DATE_FORMAT(cmp.cmp_fecha, '%m') AS mes,  cmp.cmp_total as total, cmp.cmp_divid as divisa   
            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_estatus!=3 $peticion")->all();
        } else {
            return $this->consultar("SELECT cmp.cmp_id as documentos,DATE_FORMAT(cmp.cmp_fecha, '%m') AS mes,  cmp.cmp_total as total, cmp.cmp_divid as divisa   
            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_estatus!=3")->all();
        }
    }

    public function obtenerNotasdeEntregaAnualesMesIng($peticion)
    {
        if ($peticion != null) {
            return $this->consultar("SELECT cmp.cmpn_id as documentos, DATE_FORMAT(cmp.cmpn_fecha, '%m') AS mes, cmp.cmpn_total as total, cmp.cmpn_divid as divisa   
            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_estatus!=3 $peticion ")->all();
        } else {
            return $this->consultar("SELECT cmp.cmpn_id as documentos,DATE_FORMAT(cmp.cmpn_fecha, '%m') AS mes, cmp.cmpn_total as total, cmp.cmpn_divid as divisa   
            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_estatus!=3 ")->all();
        }
    }

    public function obtenerProductosStockBajo()
    {
        return $this->consultar("SELECT DISTINCT pro.pro_id,pro.*,ps.* FROM app_productos pro 
        LEFT JOIN app_productos_stocks ps ON ps.ps_proid = pro.pro_id  
        WHERE ps.ps_stockmin > ps.ps_cantidad AND pro.pro_tipo=1")->all();
        //ps.ps_stockmin > ps.ps_cantidad
    }
    
     public function obtenerProductosStockBajoCantidad($id)
    {
        return $this->consultar("SELECT ps.ps_cantidad FROM app_productos pro 
        LEFT JOIN app_productos_stocks ps ON ps.ps_proid = pro.pro_id  
        WHERE ps.ps_stockmin > ps.ps_cantidad AND pro.pro_id=$id")->all();
        //ps.ps_stockmin > ps.ps_cantidad
    }

    public function obtenerProductosStockBajoId($searchValue)
    {
        $palabras = explode(" ", $searchValue);
        $searchQuery = "";

        if (count($palabras) > 1) {
            $searchQuery .= " (";
            $vacio="";
            foreach ($palabras as $i => $pal) {
                if (strlen($pal)>2 && $pal != "A" && $pal != "" && $pal != " " && $pal != "la" && $pal != "el" && $pal != "para" && $pal != "de" && $pal != "a" && $pal != "e" && $pal != "o" && $pal != "con" && $pal != "Lan" && $pal != "lan") {
                    if ($i == 0 || strlen($vacio)==0) {
                        $vacio .= "(pro.pprov_producto LIKE '%" . $pal . "%')";
                    } else {
                        $vacio .= " OR (pro.pprov_producto LIKE '%" . $pal . "%')";
                    }
                }
            }
            $searchQuery.=$vacio;
            $searchQuery .= ")";
        } else {
            $searchQuery .= "(pro.pprov_producto LIKE '%" . $searchValue . "%') ";
        }

        return $this->consultar("SELECT * FROM app_proveedor_lista_productos pro 
        LEFT JOIN app_proveedores p ON p.prov_id=pro.pprov_proveedor      
        WHERE $searchQuery")->all();
    }

    public function obtenerProveedoresBj($id)
    {
        return $this->consultar("SELECT * FROM app_productos_proveedores pro 
        LEFT JOIN app_proveedores p ON p.prov_id=pro.pp_provid  
        WHERE pro.pp_proid=$id")->all();
    }

    public function obtenerPrecioFacturasCompras($producto, $proveedor)
    {
        return $this->consultar("SELECT MAX(cmp.cmp_fecha) as fecha, cmpart.cmpart_proprecio as precio,cmp.cmp_divid as divisa    
        FROM app_compras_articulos cmpart 
        LEFT JOIN app_compras cmp ON cmpart.cmpart_cmpid = cmp.cmp_id 
        WHERE cmpart.cmpart_proid=$producto AND cmp.cmp_cliid=$proveedor")->row();
    }

    public function obtenerPrecioNotasCompras($producto, $proveedor)
    {
        return $this->consultar("SELECT MAX(cmp.cmpn_fecha) as fecha, cmpart.cmpnart_proprecio as precio   
        FROM app_compras_notas_articulos cmpart 
        LEFT JOIN app_compras_notas cmp ON cmpart.cmpnart_cmpnid = cmp.cmpn_id 
        WHERE cmpart.cmpnart_proid=$producto AND cmp.cmpn_cliid=$proveedor")->row();
    }

    public function obtenerHistoriaFacturasCompras($producto)
    {
        return $this->consultar("SELECT *     
        FROM app_compras_articulos cmpart 
        LEFT JOIN app_compras cmp ON cmpart.cmpart_cmpid = cmp.cmp_id 
        LEFT JOIN app_proveedores p ON cmp.cmp_cliid=p.prov_id 
        WHERE cmpart.cmpart_proid=$producto")->all();
    }

    public function obtenerHistoriaNotasCompras($producto)
    {
        return $this->consultar("SELECT *   
        FROM app_compras_notas_articulos cmpart 
        LEFT JOIN app_compras_notas cmp ON cmpart.cmpnart_cmpnid = cmp.cmpn_id 
        LEFT JOIN app_proveedores p ON cmp.cmpn_cliid=p.prov_id 
        WHERE cmpart.cmpnart_proid=$producto")->all();
    }
    
     public function obtenerPreciosFacturasComprasProv($producto)
    {
        return $this->consultar("SELECT DISTINCT p.prov_id,p.*, cmp.*      
        FROM app_compras_articulos cmpart 
        LEFT JOIN app_compras cmp ON cmpart.cmpart_cmpid = cmp.cmp_id 
        LEFT JOIN app_proveedores p ON cmp.cmp_cliid=p.prov_id 
        WHERE cmpart.cmpart_proid=$producto")->all();
    }

    public function obtenerPreciosNotasComprasProv($producto)
    {
        return $this->consultar("SELECT DISTINCT p.prov_id,p.*, cmp.*   
        FROM app_compras_notas_articulos cmpart 
        LEFT JOIN app_compras_notas cmp ON cmpart.cmpnart_cmpnid = cmp.cmpn_id 
        LEFT JOIN app_proveedores p ON cmp.cmpn_cliid=p.prov_id 
        WHERE cmpart.cmpnart_proid=$producto")->all();
    }

    public function obtenerProducto($id)
    {
        return $this->consultar("SELECT* FROM app_productos 
        WHERE pro_id=$id")->row();
        //ps.ps_stockmin > ps.ps_cantidad
    }
    
    public function obtenerPreciosFacturasComprasProvCont($producto)
    {
        return $this->consultar("SELECT SUM(cmpart.fa_procantidad) as cantidad       
         FROM app_factura_articulos cmpart 
        LEFT JOIN app_factura_master cmp ON cmpart.fa_fmid = cmp.fm_id 
        LEFT JOIN app_productos pr ON pr.pro_id=cmpart.fa_proid 
        LEFT JOIN app_productos_proveedores pp ON pr.pro_id=pp.pp_proid 
        LEFT JOIN app_proveedores p ON pp.pp_provid=p.prov_id 
        WHERE cmpart.fa_proid=$producto AND cmp.fm_estatus!=3")->all();
    }

    public function obtenerPreciosNotasComprasProvCont($producto)
    {
        return $this->consultar("SELECT SUM(cmpart.neart_procantidad) as cantidad     
        FROM app_notas_de_entrega_articulos cmpart 
        LEFT JOIN app_notas_de_entrega cmp ON cmpart.neart_neid = cmp.ne_id 
        LEFT JOIN app_productos pr ON pr.pro_id=cmpart.neart_proid 
        LEFT JOIN app_productos_proveedores pp ON pr.pro_id=pp.pp_proid 
        LEFT JOIN app_proveedores p ON pp.pp_provid=p.prov_id 
        WHERE cmpart.neart_proid=$producto AND cmp.ne_estatus!=3")->all();
    }
    
     
    public function obtenerPreciosNotasComprasProvTotal($producto)
    {
        return $this->consultar("SELECT *        
        FROM app_notas_de_entrega_articulos cmpart 
        LEFT JOIN app_notas_de_entrega cmp ON cmpart.neart_neid = cmp.ne_id 
        LEFT JOIN app_productos pr ON pr.pro_id=cmpart.neart_proid 
        LEFT JOIN app_productos_proveedores pp ON pr.pro_id=pp.pp_proid 
        LEFT JOIN app_proveedores p ON pp.pp_provid=p.prov_id 
        WHERE cmpart.neart_proid=$producto AND cmp.ne_estatus!=3 AND cmp.ne_divid=2")->all();
    }

    public function obtenerPreciosFacturasComprasProvTotal($producto)
    {
        return $this->consultar("SELECT *      
        FROM app_factura_articulos cmpart 
        LEFT JOIN app_factura_master cmp ON cmpart.fa_fmid = cmp.fm_id 
        LEFT JOIN app_productos pr ON pr.pro_id=cmpart.fa_proid 
        LEFT JOIN app_productos_proveedores pp ON pr.pro_id=pp.pp_proid 
        LEFT JOIN app_proveedores p ON pp.pp_provid=p.prov_id 
        WHERE cmpart.fa_proid=$producto AND cmp.fm_estatus!=3  AND cmp.fm_divid=2")->all();
    }
}
