<?php

namespace App\Modelos;

use Core\Database;

class Ingresosegresos extends Database
{
    public function totalIgresosSegunMoneda()
    {
        return $this->consultar('SELECT SUM(acm.cmp_saldo) as total , d.div_locale, div_simbolo, d.div_nombre
        FROM app_cuentas_metodopago acm
        JOIN app_metodopago_divisa md ON md.md_id = acm.cmp_mdid 
        JOIN app_divisas d ON d.div_id = md.md_divid 
        GROUP BY d.div_id')->all();
    }

    public function totalIngresoSeunDivisaMetodo()
    {
        return $this->consultar('SELECT SUM(cm.cmp_saldo) as total, m.mp_nombre, d.div_nombre, d.div_locale, div_simbolo         
        FROM app_cuentas_metodopago cm 
        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 mpd ON mpd.md_id = cm.cmp_mdid     
        JOIN app_metodopago m ON m.mp_id = mpd.md_mpid
        JOIN app_divisas d ON d.div_id = mpd.md_divid        
        GROUP BY d.div_id, cm.cmp_mdid')->all();
    }

    public function totalIngresosSegunCuentas()
    {
        return $this->consultar("SELECT DISTINCT(bc_id), bc_saldo as total, bc_cuenta, bc_alias, bc_tipo, ban_descripcion, d.div_id, d.div_nombre, d.div_locale, div_simbolo 
        FROM app_cuentas_metodopago cm 
        JOIN app_bancos_cuentas bc ON bc.bc_id = cm.cmp_cueid 
        JOIN app_empleados_cuentas empc ON bc.bc_id = empc.ec_bcid  
        JOIN app_bancos b ON b.ban_id = bc.bc_banid       
        JOIN app_metodopago_divisa mpd ON mpd.md_id = cm.cmp_mdid         
        JOIN app_divisas d ON d.div_id = mpd.md_divid WHERE empc.ec_empid={$_SESSION['user_data']['emp_id']} 
        ORDER BY bc_alias")->all();
    }

    public function totalIngresosSegunCuentasDivisa($divisa)
    {
        return $this->consultar("SELECT DISTINCT(bc_id), bc_saldo as total, bc_cuenta, bc_alias, bc_tipo, ban_descripcion, d.div_id, d.div_nombre, d.div_locale, div_simbolo 
        FROM app_cuentas_metodopago cm 
        JOIN app_bancos_cuentas bc ON bc.bc_id = cm.cmp_cueid 
        JOIN app_empleados_cuentas empc ON bc.bc_id = empc.ec_bcid  
        JOIN app_bancos b ON b.ban_id = bc.bc_banid       
        JOIN app_metodopago_divisa mpd ON mpd.md_id = cm.cmp_mdid         
        JOIN app_divisas d ON d.div_id = mpd.md_divid WHERE d.div_id=$divisa AND empc.ec_empid={$_SESSION['user_data']['emp_id']} 
        ORDER BY bc_alias")->all();
    }

    public function totalIngresosSegunCuentasDivisaMonto($divisa)
    {
        return $this->consultar("SELECT DISTINCT(bc_id), SUM(bc_saldo) as total  
        FROM app_cuentas_metodopago cm 
        JOIN app_bancos_cuentas bc ON bc.bc_id = cm.cmp_cueid 
        JOIN app_empleados_cuentas empc ON bc.bc_id = empc.ec_bcid  
        JOIN app_bancos b ON b.ban_id = bc.bc_banid       
        JOIN app_metodopago_divisa mpd ON mpd.md_id = cm.cmp_mdid         
        JOIN app_divisas d ON d.div_id = mpd.md_divid WHERE d.div_id=$divisa AND empc.ec_empid={$_SESSION['user_data']['emp_id']} 
        ORDER BY bc_alias")->all();
    }

    //TAB BUSQUEDA

    public function obtenerMetodosSegunDivisa(int $id_divisa)
    {
        return $this->consultar("SELECT * 
        FROM app_metodopago_divisa md
        JOIN app_metodopago m ON m.mp_id = md.md_mpid
        WHERE md_divid=$id_divisa and m.mp_estatus = 1")->all();
    }

    public function obtenerCuentasSegunDivisa(int $id_divisa)
    {
        return $this->consultar("SELECT cmp_id id, bc_alias alias, bc_tipo tipo, ban_codigo codigo, mp_codigo
        FROM app_cuentas_metodopago cm
        JOIN app_bancos_cuentas bc ON bc.bc_id = cm.cmp_cueid
        JOIN app_metodopago_divisa md ON md.md_id = cm.cmp_mdid
        JOIN app_metodopago m ON md.md_mpid = m.mp_id
        JOIN app_divisas d ON d.div_id = md.md_divid 
        JOIN app_bancos b ON b.ban_id = bc.bc_banid 
        WHERE md_divid=$id_divisa AND ban_estatus = 1 GROUP BY bc.bc_id ORDER BY bc.bc_alias")->all();
    }

    public function obtenerCuentasSegunMetodo(int $id_metodo)
    {
        return $this->consultar("SELECT cmp_id id, bc_alias alias, bc_tipo tipo, ban_codigo codigo 
        FROM app_cuentas_metodopago cm
        JOIN app_bancos_cuentas bc ON bc.bc_id = cm.cmp_cueid
        JOIN app_bancos b ON b.ban_id = bc.bc_banid 
        WHERE cm.cmp_mdid=$id_metodo AND ban_estatus = 1 AND cmp_cueid IN (SELECT ec_bcid FROM app_empleados_cuentas WHERE ec_empid = {$_SESSION['user_data']['emp_id']})")->all();
    }

    public function obtenerCuentasIgualBS()
    {
        return $this->consultar("SELECT cmp_id id, bc_alias alias, bc_tipo tipo, ban_codigo codigo 
        FROM app_metodopago_divisa md
        JOIN app_cuentas_metodopago cmp ON cmp.cmp_mdid = md.md_id
        JOIN app_bancos_cuentas bc ON bc.bc_id = cmp.cmp_cueid
        JOIN app_bancos b ON b.ban_id = bc.bc_banid
        WHERE md.md_divid=1 AND ban_estatus = 1 AND cmp_cueid IN (SELECT ec_bcid FROM app_empleados_cuentas WHERE ec_empid = {$_SESSION['user_data']['emp_id']})")->all();
    }

    public function obtenerCuentasIgualUsd()
    {
        return $this->consultar("SELECT  cmp_id id, bc_alias alias, bc_tipo tipo, ban_codigo codigo 
        FROM app_metodopago_divisa md
        JOIN app_cuentas_metodopago cmp ON cmp.cmp_mdid = md.md_id
        JOIN app_bancos_cuentas bc ON bc.bc_id = cmp.cmp_cueid
        JOIN app_bancos b ON b.ban_id = bc.bc_banid
        WHERE md.md_divid=2 AND ban_estatus = 1 AND cmp_cueid IN (SELECT ec_bcid FROM app_empleados_cuentas WHERE ec_empid = {$_SESSION['user_data']['emp_id']})")->all();
    }

    public function obtenerCuentaMetodo(int $id_cuenta_metodo)
    {
        return $this->consultar("SELECT *
        FROM app_cuentas_metodopago cm
        WHERE cm.cmp_id=$id_cuenta_metodo")->row();
    }

    public function obtenerCuentasSegunMetodoDestino(int $id_cuenta, int $id_div_metodo)
    {
        return $this->consultar("SELECT cmp_id id, bc_alias alias, bc_tipo tipo, ban_codigo codigo 
        FROM app_cuentas_metodopago cm
        JOIN app_bancos_cuentas bc ON bc.bc_id = cm.cmp_cueid
        JOIN app_bancos b ON b.ban_id = bc.bc_banid 
        WHERE cm.cmp_cueid!=$id_cuenta AND ban_estatus = 1 AND cmp_mdid=$id_div_metodo")->all();
    }

    public function obtenerDivisaSegunCuenta(int $id_cuenta)
    {
        return $this->consultar("SELECT div_id
        FROM app_cuentas_metodopago cm
        JOIN app_metodopago_divisa md ON md.md_id = cm.cmp_mdid 
        JOIN app_divisas d ON d.div_id = md.md_divid 
        WHERE cm.cmp_id = $id_cuenta")->row();
    }

    public function obtenerCuentasSegunMetodoDestinoTrans(int $id_cuenta, int $id_divisa)
    {
        return $this->consultar("SELECT cmp_id id, bc_alias alias, bc_tipo tipo, ban_codigo codigo, mp_codigo
        FROM app_cuentas_metodopago cm
        JOIN app_bancos_cuentas bc ON bc.bc_id = cm.cmp_cueid
        JOIN app_metodopago_divisa md ON md.md_id = cm.cmp_mdid
        JOIN app_metodopago m ON md.md_mpid = m.mp_id
        JOIN app_divisas d ON d.div_id = md.md_divid 
        JOIN app_bancos b ON b.ban_id = bc.bc_banid 
        WHERE cm.cmp_cueid!=$id_cuenta AND md_divid=$id_divisa AND ban_estatus = 1")->all();
    }

    //TAB PRODUCCION VENTAS

    public function obtenerFacturas($fecha_desde, $fecha_hasta)
    {
        return $this->consultar("SELECT c.cli_id, c.cli_dni, c.cli_razon_social, fm.fm_id, fm.fm_correlativo, fm.fm_total, DATE_FORMAT(fm.fm_fecha, '%d/%m/%Y') as fecha, DATE_FORMAT(fm.fm_fecha, '%h:%i') as hora, fm.fm_estatus, d.*, s.ser_codigo, su.* 
        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 
        JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
        JOIN app_clientes c ON c.cli_id = fm.fm_cliid
        WHERE (fm.fm_estatus = 0 OR fm.fm_estatus = 2) AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta')")->all();
    }

    public function obtenerNotasdeentrega($fecha_desde, $fecha_hasta)
    {
        return $this->consultar("SELECT c.cli_id, c.cli_dni, c.cli_razon_social, ne.ne_id, ne.ne_correlativo, ne.ne_total, DATE_FORMAT(ne.ne_fecha, '%d/%m/%Y') as fecha, DATE_FORMAT(ne.ne_fecha, '%h:%i') as hora, ne.ne_estatus, d.*, s.ser_codigo,su.* 
        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
        JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
        JOIN app_clientes c ON c.cli_id = ne.ne_cliid
        WHERE (ne.ne_estatus = 0 OR ne.ne_estatus = 2) AND (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 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 obtenerArticulosFactura($id_factura)
    {
        return $this->consultar("SELECT fa_proid, fa_procantidad FROM app_factura_articulos WHERE fa_fmid = $id_factura")->all();
    }

    public function obtenerCantidadTotalDeArticulosFactura($id_factura)
    {
        return $this->consultar("SELECT SUM(fa_procantidad) AS cantidad FROM app_factura_articulos WHERE fa_fmid = $id_factura")->row();
    }

    public function obtenerArticulosNotasEntrega($id_factura)
    {
        return $this->consultar("SELECT neart_proid, neart_procantidad FROM app_notas_de_entrega_articulos WHERE neart_neid = $id_factura")->all();
    }

    public function obtenerCantidadTotalDeArticulosNotasEntrega($id_factura)
    {
        return $this->consultar("SELECT SUM(neart_procantidad) AS cantidad FROM app_notas_de_entrega_articulos WHERE neart_neid = $id_factura")->row();
    }

    public function obtenerProducto($id_producto)
    {
        return $this->consultar("SELECT * FROM app_productos WHERE pro_id = $id_producto")->row();
    }

    // TAB PRODUCTOS

    public function obtenerProductosConStock($id_almacen)
    {
        if (empty($id_almacen)) {
            return $this->consultar("SELECT pro.pro_id, pro.pro_descripcion, pro.pro_costo, pro.pro_precio, ps.ps_cantidad as cantidad, a.alm_nombre
            FROM app_productos pro         
            LEFT JOIN app_productos_stocks ps ON ps.ps_proid=pro_id
            LEFT JOIN app_almacenes a ON a.alm_id = ps.ps_almid
            WHERE pro_tipo <> 3 ORDER BY pro.pro_descripcion")->all();
        } else {
            return $this->consultar("SELECT pro.pro_id, pro.pro_descripcion, pro.pro_costo, pro.pro_precio, ps.ps_cantidad as cantidad, a.alm_nombre
            FROM app_productos pro         
            LEFT JOIN app_productos_stocks ps ON ps.ps_proid=pro_id
            LEFT JOIN app_almacenes a ON a.alm_id = ps.ps_almid
            WHERE pro_tipo <> 3 AND a.alm_id = $id_almacen ORDER BY pro.pro_descripcion")->all();
        }
    }

    public function obtenerProductosStockBajo($id_almacen)
    {
        return $this->consultar("SELECT * FROM app_productos pro 
        LEFT JOIN app_productos_stocks ps ON ps.ps_proid = pro.pro_id 
        LEFT JOIN app_almacenes a ON a.alm_id = ps.ps_almid 
        LEFT JOIN app_fabricantes fa ON fa.fa_id = pro.pro_fabid 
        WHERE (ps.ps_stockmin > ps.ps_disponible) OR ps.ps_disponible = 0 AND a.alm_id = $id_almacen")->all();
    }

    public function obtenerProductosStockAlmacen($id_almacen)
    {
        return $this->consultar("SELECT * 
            FROM app_productos pro   
            LEFT JOIN app_productos_stocks ps ON ps.ps_proid=pro.pro_id
            LEFT JOIN app_almacenes a ON a.alm_id = ps.ps_almid
            WHERE a.alm_garantia=0 AND ps.ps_disponible > 0 AND a.alm_id = $id_almacen")->all();
    }

    // TAB CLIENTES PENDIENTES POR PAGAR

    public function obtenerFacturasPendientes()
    {
        return $this->consultar("SELECT c.cli_id, c.cli_dni, c.cli_razon_social,c.cli_grupid,g.grup_id,g.grup_nombre, fm.fm_id, fm.fm_correlativo, fm.fm_total, DATE_FORMAT(fm.fm_fecha, '%d/%m/%Y') as fecha, fm.fm_estatus,fm.fm_empagente, d.div_id, d.div_nombre, d.div_locale, div_simbolo, s.ser_codigo, su.* 
        FROM app_factura_master fm 
        JOIN app_series s ON s.ser_id = fm.fm_serid
        JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
        JOIN app_divisas d ON d.div_id = fm.fm_divid
        JOIN app_clientes c ON c.cli_id = fm.fm_cliid 
        LEFT JOIN app_clientes_grupos g ON c.cli_grupid=g.grup_id 
        WHERE fm_estatus = 0 OR fm_estatus = 2")->all();
    }

    public function obtenerNotasdeentregaPendiente()
    {
        return $this->consultar("SELECT c.cli_id, c.cli_dni, c.cli_razon_social,c.cli_grupid,g.grup_id,g.grup_nombre, ne.ne_id, ne.ne_correlativo, ne.ne_total, DATE_FORMAT(ne.ne_fecha, '%d/%m/%Y') as fecha, ne.ne_estatus,ne.ne_empagente, d.div_id, d.div_nombre, d.div_locale, div_simbolo, s.ser_codigo, su.* 
        FROM app_notas_de_entrega ne 
        JOIN app_series s ON s.ser_id = ne.ne_serid 
        JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
        JOIN app_divisas d ON d.div_id = ne.ne_divid
        JOIN app_clientes c ON c.cli_id = ne.ne_cliid 
        LEFT JOIN app_clientes_grupos g ON c.cli_grupid=g.grup_id 
        WHERE (ne_estatus = 0 OR ne_estatus = 2) 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 obtenerOrdendeentregaPendiente()
    {
        return $this->consultar("SELECT c.cli_id, c.cli_dni, c.cli_razon_social,c.cli_grupid,g.grup_id,g.grup_nombre, n.n_id, n.n_correlativo, n.n_total, DATE_FORMAT(n.n_fecha, '%d/%m/%Y') as fecha, n.n_estatus,n.n_empagente, d.div_id, d.div_nombre, d.div_locale, div_simbolo, s.ser_codigo,su.* 
        FROM app_orden_de_entrega n
        JOIN app_series s ON s.ser_id = n.n_serid 
        JOIN app_sucursales su ON su.su_id=n.n_surcid 
        JOIN app_divisas d ON d.div_id = n.n_divid
        JOIN app_clientes c ON c.cli_id = n.n_cliid 
        LEFT JOIN app_clientes_grupos g ON c.cli_grupid=g.grup_id 
        WHERE (n_estatus = 0 OR n_estatus = 2) 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 obtenerAcumulado(int $id_factura, int $documento)
    {
        return $this->consultar("SELECT SUM(fab_conversion) as total FROM app_movimientos WHERE fab_idgenerico=$id_factura AND fab_doc=$documento")->row();
    }

    //TAB COMPRAS PENDIENTES

    public function obtenerNotasPendientesCMP()
    {
        return $this->consultar("SELECT p.prov_id, p.prov_razonsocial, cmp.cmpn_id, cmp.cmpn_correlativo, cmp.cmpn_total, DATE_FORMAT(cmp.cmpn_fecha, '%d/%m/%Y') as fecha, cmp.cmpn_estatus, d.div_id, d.div_locale, d.div_nombre, d.div_simbolo, s.ser_codigo
        FROM app_compras_notas cmp
        JOIN app_series s ON s.ser_id = cmp.cmpn_serid
        JOIN app_divisas d ON d.div_id = cmp.cmpn_divid
        JOIN app_proveedores p ON p.prov_id = cmp.cmpn_cliid
        WHERE (cmpn_estatus = 0 OR cmpn_estatus = 2)
        AND cmp.cmpn_id not in (SELECT ard.rd_facid_procedencia FROM app_ruta_documento ard WHERE rd_docid_procedencia=9 AND rd_docid_conversion =8)")->all();
    }

    public function obtenerFacturasPendientesCMP()
    {
        return $this->consultar("SELECT p.prov_id, p.prov_razonsocial, cmp.cmp_id, cmp.cmp_correlativo, cmp.cmp_total, DATE_FORMAT(cmp.cmp_fecha, '%d/%m/%Y') as fecha, cmp.cmp_estatus, d.div_id, d.div_locale, d.div_nombre, d.div_simbolo, s.ser_codigo
        FROM app_compras cmp
        JOIN app_series s ON s.ser_id = cmp.cmp_serid
        JOIN app_divisas d ON d.div_id = cmp.cmp_divid
        JOIN app_proveedores p ON p.prov_id = cmp.cmp_cliid
        WHERE (cmp_estatus = 0 OR cmp_estatus = 2)
        AND cmp.cmp_id not in (SELECT ard.rd_facid_procedencia FROM app_ruta_documento ard WHERE rd_docid_procedencia=9 AND rd_docid_conversion =8)")->all();
    }

    //TAB PRODUCTOS VENDIDOS

    public function obtenerProductos()
    {
        return $this->consultar("SELECT *
        FROM app_productos pro")->all();
    }

    public function obtenerProductossinServicios()
    {
        return $this->consultar("SELECT *
        FROM app_productos WHERE pro_tipo=1")->all();
    }

    public function obtenerCategoria($id)
    {
        return $this->consultar("SELECT *
        FROM app_productos_categorias cap
        LEFT JOIN app_categorias ct ON ct.cat_id=cap.pc_catid WHERE cap.pc_proid=$id")->all();
    }

    public function obtenerArticulosDeFACV($id_producto, $sucursal, $fecha_desde, $fecha_hasta, $divisa = null)
    {
        if (is_null($divisa)) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, SUM(art.fa_prototal) AS total, SUM(art.fa_procantidad) as cantidad,art.fa_procosto AS costo, s.su_nombre
                FROM app_factura_master fm
                LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid 
                LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid  
                WHERE fm_estatus < 3 
                AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                AND art.fa_proid = $id_producto 
                GROUP BY fm.fm_divid")->all();
            } else {
                return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, SUM(art.fa_prototal) AS total, SUM(art.fa_procantidad) as cantidad,art.fa_procosto AS costo, s.su_nombre
                FROM app_factura_master fm
                LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid 
                LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid  
                WHERE fm.fm_surcid=$sucursal AND fm_estatus < 3 
                AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                AND art.fa_proid = $id_producto 
                GROUP BY fm.fm_divid")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, SUM(art.fa_prototal) AS total, SUM(art.fa_procantidad) as cantidad,art.fa_procosto AS costo,  s.su_nombre
                FROM app_factura_master fm
                LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid AND fm.fm_divid = $divisa 
                LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                WHERE fm_estatus < 3 
                AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                AND art.fa_proid = $id_producto
                GROUP BY fm.fm_divid")->all();
            } else {
                return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, SUM(art.fa_prototal) AS total, SUM(art.fa_procantidad) as cantidad,art.fa_procosto AS costo, s.su_nombre
                FROM app_factura_master fm
                LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid AND fm.fm_divid = $divisa 
                LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                WHERE fm.fm_surcid=$sucursal AND fm_estatus < 3 
                AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                AND art.fa_proid = $id_producto
                GROUP BY fm.fm_divid")->all();
            }
        }
    }

    public function obtenerArticulosDeNEV($id_producto, $sucursal, $fecha_desde, $fecha_hasta, $divisa = null)
    {
        if (is_null($divisa)) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, (neart.neart_prototal) AS total, (neart.neart_procantidad) as cantidad,neart.neart_procosto AS costo, s.su_nombre 
                FROM app_notas_de_entrega ne
                LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid
                LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid  
                WHERE ne_estatus < 3 AND (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND neart.neart_proid = $id_producto 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();
            } else {
                return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, (neart.neart_prototal) AS total, (neart.neart_procantidad) as cantidad,neart.neart_procosto AS costo, s.su_nombre 
                FROM app_notas_de_entrega ne
                LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid
                LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid  
                WHERE ne.ne_surcid=$sucursal AND ne_estatus < 3 AND (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND neart.neart_proid = $id_producto 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();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, (neart.neart_prototal) AS total, (neart.neart_procantidad) as cantidad,neart.neart_procosto AS costo, s.su_nombre 
                FROM app_notas_de_entrega ne
                LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid AND ne.ne_divid = $divisa 
                LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid  
                WHERE ne_estatus < 3 AND (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND neart.neart_proid = $id_producto 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();
            } else {
                return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, (neart.neart_prototal) AS total, (neart.neart_procantidad) as cantidad,neart.neart_procosto AS costo, s.su_nombre 
                FROM app_notas_de_entrega ne
                LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid AND ne.ne_divid = $divisa 
                LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid  
                WHERE ne.ne_surcid=$sucursal AND ne_estatus < 3 AND (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND neart.neart_proid = $id_producto 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 obtenerArticulosDeOrd($id_producto, $sucursal, $fecha_desde, $fecha_hasta, $divisa = null)
    {
        if (is_null($divisa)) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, (nart.nart_prototal) AS total, (nart.nart_procantidad) as cantidad,nart.nart_procosto AS costo, s.su_nombre
                FROM app_orden_de_entrega n
                LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                WHERE n_estatus < 3 AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_divid")->all();
            } else {
                return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, (nart.nart_prototal) AS total, (nart.nart_procantidad) as cantidad,nart.nart_procosto AS costo, s.su_nombre
                FROM app_orden_de_entrega n
                LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                WHERE n.n_surcid=$sucursal AND n_estatus < 3 AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_divid")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, (nart.nart_prototal) AS total, (nart.nart_procantidad) as cantidad, nart.nart_procosto AS costo,s.su_nombre 
                FROM app_orden_de_entrega n
                LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                WHERE n.n_divid = $divisa AND n_estatus < 3 AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_divid")->all();
            } else {
                return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, (nart.nart_prototal) AS total, (nart.nart_procantidad) as cantidad,nart.nart_procosto AS costo, s.su_nombre 
                FROM app_orden_de_entrega n
                LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                WHERE n.n_surcid=$sucursal AND n.n_divid = $divisa AND n_estatus < 3 AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_divid")->all();
            }
        }
    }

    public function obtenerArticulosDeFACVL($id_producto, $sucursal, $fecha_desde, $fecha_hasta, $divisa = null, $estatus, $agente = 0)
    {
        if (is_null($divisa)) {
            if ($sucursal == 0) {
                if ($estatus != 0) {
                    if ($agente != 0) {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus, c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo, s.su_nombre,gp.*  
                        FROM app_factura_master fm
                        LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid 
                        LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                        LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE fm_estatus= $estatus AND fm.fm_empagente=$agente 
                        AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                        AND art.fa_proid = $id_producto 
                        GROUP BY fm.fm_fecha")->all();
                    } else {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus, c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo, s.su_nombre,gp.* 
                        FROM app_factura_master fm
                        LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid 
                        LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                        LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE fm_estatus= $estatus  
                        AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                        AND art.fa_proid = $id_producto 
                        GROUP BY fm.fm_fecha")->all();
                    }
                } else {
                    if ($agente != 0) {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus, c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo, s.su_nombre,gp.* 
                FROM app_factura_master fm
                LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid 
                LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                WHERE fm_estatus < 3 AND fm.fm_empagente=$agente 
                AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                AND art.fa_proid = $id_producto 
                GROUP BY fm.fm_fecha")->all();
                    } else {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus, c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo, s.su_nombre,gp.* 
                FROM app_factura_master fm
                LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid 
                LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                WHERE fm_estatus < 3 
                AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                AND art.fa_proid = $id_producto 
                GROUP BY fm.fm_fecha")->all();
                    }
                }
            } else {
                if ($estatus != 0) {
                    if ($agente != 0) {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus, c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo, s.su_nombre,gp.* 
                        FROM app_factura_master fm
                        LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid 
                        LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                        LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE fm.fm_surcid=$sucursal AND fm_estatus = $estatus AND fm.fm_empagente=$agente 
                        AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                        AND art.fa_proid = $id_producto 
                        GROUP BY fm.fm_fecha")->all();
                    } else {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus, c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo, s.su_nombre,gp.* 
                        FROM app_factura_master fm
                        LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid 
                        LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                        LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE fm.fm_surcid=$sucursal AND fm_estatus = $estatus  
                        AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                        AND art.fa_proid = $id_producto 
                        GROUP BY fm.fm_fecha")->all();
                    }
                } else {
                    if ($agente != 0) {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus, c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo, s.su_nombre,gp.* 
                FROM app_factura_master fm
                LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid 
                LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                WHERE fm.fm_surcid=$sucursal AND fm_estatus < 3 AND fm.fm_empagente=$agente 
                AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                AND art.fa_proid = $id_producto 
                GROUP BY fm.fm_fecha")->all();
                    } else {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus, c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo, s.su_nombre,gp.* 
                FROM app_factura_master fm
                LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid 
                LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                WHERE fm.fm_surcid=$sucursal AND fm_estatus < 3 
                AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                AND art.fa_proid = $id_producto 
                GROUP BY fm.fm_fecha")->all();
                    }
                }
            }
        } else {
            if ($sucursal == 0) {
                if ($estatus != 0) {
                    if ($agente != 0) {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo,  s.su_nombre,gp.* 
                        FROM app_factura_master fm
                        LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid AND fm.fm_divid = $divisa 
                        LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE fm_estatus = $estatus AND fm.fm_empagente=$agente 
                        AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                        AND art.fa_proid = $id_producto
                        GROUP BY fm.fm_fecha")->all();
                    } else {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo,  s.su_nombre,gp.* 
                        FROM app_factura_master fm
                        LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid AND fm.fm_divid = $divisa 
                        LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE fm_estatus = $estatus 
                        AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                        AND art.fa_proid = $id_producto
                        GROUP BY fm.fm_fecha")->all();
                    }
                } else {
                    if ($agente != 0) {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo,  s.su_nombre,gp.* 
                        FROM app_factura_master fm
                        LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid AND fm.fm_divid = $divisa 
                        LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE fm_estatus < 3 AND fm.fm_empagente=$agente 
                        AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                        AND art.fa_proid = $id_producto
                        GROUP BY fm.fm_fecha")->all();
                    } else {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo,  s.su_nombre,gp.* 
                        FROM app_factura_master fm
                        LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid AND fm.fm_divid = $divisa 
                        LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE fm_estatus < 3 
                        AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                        AND art.fa_proid = $id_producto
                        GROUP BY fm.fm_fecha")->all();
                    }
                }
            } else {
                if ($estatus != 0) {
                    if ($agente != 0) {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo, s.su_nombre,gp.* 
                    FROM app_factura_master fm
                    LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid AND fm.fm_divid = $divisa 
                    LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE fm.fm_surcid=$sucursal AND fm_estatus =$estatus AND fm.fm_empagente=$agente 
                    AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                    AND art.fa_proid = $id_producto
                    GROUP BY fm.fm_fecha")->all();
                    } else {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo, s.su_nombre,gp.* 
                    FROM app_factura_master fm
                    LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid AND fm.fm_divid = $divisa 
                    LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE fm.fm_surcid=$sucursal AND fm_estatus =$estatus  
                    AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                    AND art.fa_proid = $id_producto
                    GROUP BY fm.fm_fecha")->all();
                    }
                } else {
                    if ($agente != 0) {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo, s.su_nombre,gp.* 
                        FROM app_factura_master fm
                        LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid AND fm.fm_divid = $divisa 
                        LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE fm.fm_surcid=$sucursal AND fm_estatus < 3 AND fm.fm_empagente=$agente 
                        AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                        AND art.fa_proid = $id_producto
                        GROUP BY fm.fm_fecha")->all();
                    } else {
                        return $this->consultar("SELECT fm.fm_id as id,fm.fm_correlativo as correlativo,fm.fm_fecha as fecha, fm.fm_divid as divisa, fm.fm_empagente as agente,fm.fm_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, art.fa_proid, art.fa_prototal AS total, art.fa_procantidad as cantidad,art.fa_procosto as costo, s.su_nombre,gp.* 
                        FROM app_factura_master fm
                        LEFT JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid AND fm.fm_divid = $divisa 
                        LEFT JOIN app_clientes c ON c.cli_id = fm.fm_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=fm.fm_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = fm.fm_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE fm.fm_surcid=$sucursal AND fm_estatus < 3 
                        AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') 
                        AND art.fa_proid = $id_producto
                        GROUP BY fm.fm_fecha")->all();
                    }
                }
            }
        }
    }

    public function obtenerArticulosDeNEVL($id_producto, $sucursal, $fecha_desde, $fecha_hasta, $divisa = null, $estatus, $agente = 0)
    {
        if (is_null($divisa)) {
            if ($sucursal == 0) {
                if ($estatus != 0) {
                    if ($agente != 0) {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa, ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                        FROM app_notas_de_entrega ne
                        LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid 
                        LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_estatus =$estatus AND neart.neart_proid = $id_producto AND ne.ne_empagente=$agente 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa, ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                        FROM app_notas_de_entrega ne
                        LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid 
                        LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_estatus =$estatus AND neart.neart_proid = $id_producto 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_fecha")->all();
                    }
                } else {
                    if ($agente != 0) {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa, ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                        FROM app_notas_de_entrega ne
                        LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid 
                        LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_estatus < 3 AND neart.neart_proid = $id_producto AND ne.ne_empagente=$agente 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa, ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                        FROM app_notas_de_entrega ne
                        LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid 
                        LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_estatus < 3 AND neart.neart_proid = $id_producto 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_fecha")->all();
                    }
                }
            } else {
                if ($estatus != 0) {
                    if ($agente != 0) {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa, ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                        FROM app_notas_de_entrega ne
                        LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid 
                        LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_surcid=$sucursal AND ne.ne_estatus =$estatus AND ne.ne_empagente=$agente AND neart.neart_proid = $id_producto 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa, ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                        FROM app_notas_de_entrega ne
                        LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid 
                        LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_surcid=$sucursal AND ne.ne_estatus =$estatus AND neart.neart_proid = $id_producto 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_fecha")->all();
                    }
                } else {
                    if ($agente != 0) {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa, ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                FROM app_notas_de_entrega ne
                LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid 
                LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid 
                LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_surcid=$sucursal AND ne.ne_empagente=$agente AND ne.ne_estatus < 3 AND neart.neart_proid = $id_producto 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa, ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id, aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                FROM app_notas_de_entrega ne
                LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid 
                LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid 
                LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_surcid=$sucursal AND ne.ne_estatus < 3 AND neart.neart_proid = $id_producto 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_fecha")->all();
                    }
                }
            }
        } else {
            if ($sucursal == 0) {
                if ($estatus != 0) {
                    if ($agente != 0) {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa,ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                    FROM app_notas_de_entrega ne
                    LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid AND ne.ne_divid = $divisa 
                    LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid  
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid  
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_estatus =$estatus AND ne.ne_empagente=$agente AND neart.neart_proid = $id_producto 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa,ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                    FROM app_notas_de_entrega ne
                    LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid AND ne.ne_divid = $divisa 
                    LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid  
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid  
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_estatus =$estatus AND neart.neart_proid = $id_producto 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_fecha")->all();
                    }
                } else {
                    if ($agente != 0) {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa,ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                        FROM app_notas_de_entrega ne
                        LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid AND ne.ne_divid = $divisa 
                        LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid  
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid  
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_estatus < 3  AND ne.ne_empagente=$agente AND neart.neart_proid = $id_producto 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa,ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                        FROM app_notas_de_entrega ne
                        LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid AND ne.ne_divid = $divisa 
                        LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid  
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid  
                        WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_estatus < 3 AND neart.neart_proid = $id_producto 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_fecha")->all();
                    }
                }
            } else {
                if ($estatus != 0) {
                    if ($agente != 0) {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa,ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                    FROM app_notas_de_entrega ne
                    LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid AND ne.ne_divid = $divisa 
                    LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid  
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid  
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_surcid=$sucursal AND ne.ne_estatus =$estatus AND ne.ne_empagente=$agente AND neart.neart_proid = $id_producto 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa,ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                    FROM app_notas_de_entrega ne
                    LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid AND ne.ne_divid = $divisa 
                    LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid  
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid  
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_surcid=$sucursal AND ne.ne_estatus =$estatus AND neart.neart_proid = $id_producto 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_fecha")->all();
                    }
                } else {
                    if ($agente != 0) {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa,ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                        FROM app_notas_de_entrega ne
                        LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid AND ne.ne_divid = $divisa 
                        LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid  
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid  
                        WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_surcid=$sucursal AND ne.ne_estatus < 3 AND ne.ne_empagente=$agente AND neart.neart_proid = $id_producto 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT ne.ne_id as id,ne.ne_correlativo as correlativo,ne.ne_fecha as fecha, ne.ne_divid as divisa,ne.ne_empagente as agente,ne.ne_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, neart.neart_proid, neart.neart_prototal AS total, neart.neart_procantidad as cantidad,neart.neart_procosto as costo, s.su_nombre,gp.* 
                        FROM app_notas_de_entrega ne
                        LEFT JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid AND ne.ne_divid = $divisa 
                        LEFT JOIN app_clientes c ON c.cli_id = ne.ne_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=ne.ne_surcid  
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = ne.ne_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid  
                        WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_surcid=$sucursal AND ne.ne_estatus < 3 AND neart.neart_proid = $id_producto 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_fecha")->all();
                    }
                }
            }
        }
    }

    public function obtenerArticulosDeOrdL($id_producto, $sucursal, $fecha_desde, $fecha_hasta, $divisa = null, $estatus, $agente = 0)
    {
        if (is_null($divisa)) {
            if ($sucursal == 0) {
                if ($estatus != 0) {
                    if ($agente != 0) {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre,gp.* 
                    FROM app_orden_de_entrega n
                    LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                    LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE n_estatus =$estatus AND n.n_empagente=$agente AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre,gp.* 
                    FROM app_orden_de_entrega n
                    LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                    LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE n_estatus =$estatus AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    }
                } else {
                    if ($agente != 0) {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre
                        FROM app_orden_de_entrega n
                        LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                        LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE n_estatus < 3 AND n.n_empagente=$agente AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre,gp.* 
                        FROM app_orden_de_entrega n
                        LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                        LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE n_estatus < 3 AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    }
                }
            } else {
                if ($estatus != 0) {
                    if ($agente != 0) {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre,gp.* 
                    FROM app_orden_de_entrega n
                    LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                    LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE n.n_surcid=$sucursal AND n.n_empagente=$agente AND n_estatus =$estatus AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre,gp.* 
                    FROM app_orden_de_entrega n
                    LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                    LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE n.n_surcid=$sucursal AND n_estatus =$estatus AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    }
                } else {
                    if ($agente != 0) {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre,gp.* 
                FROM app_orden_de_entrega n
                LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                WHERE n.n_surcid=$sucursal AND n.n_empagente=$agente AND n_estatus < 3 AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre,gp.* 
                FROM app_orden_de_entrega n
                LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                WHERE n.n_surcid=$sucursal AND n_estatus < 3 AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    }
                }
            }
        } else {
            if ($sucursal == 0) {
                if ($estatus != 0) {
                    if ($agente != 0) {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre,gp.*  
                    FROM app_orden_de_entrega n
                    LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                    LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE n.n_divid = $divisa AND n.n_empagente=$agente AND n_estatus =$estatus AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre,gp.*  
                    FROM app_orden_de_entrega n
                    LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                    LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE n.n_divid = $divisa AND n_estatus =$estatus AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    }
                } else {
                    if ($agente != 0) {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre,gp.*  
                    FROM app_orden_de_entrega n
                    LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                    LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE n.n_divid = $divisa AND n.n_empagente=$agente AND n_estatus < 3 AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre,gp.*  
                    FROM app_orden_de_entrega n
                    LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                    LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE n.n_divid = $divisa AND n_estatus < 3 AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    }
                }
            } else {
                if ($estatus != 0) {
                    if ($agente != 0) {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre,gp.*  
                    FROM app_orden_de_entrega n
                    LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                    LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE n.n_surcid=$sucursal AND n.n_empagente=$agente AND n.n_divid = $divisa AND n_estatus =$estatus AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre,gp.*  
                    FROM app_orden_de_entrega n
                    LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                    LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                    LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                    LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                    LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                    WHERE n.n_surcid=$sucursal AND n.n_divid = $divisa AND n_estatus =$estatus AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    }
                } else {
                    if ($agente != 0) {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre,gp.*  
                        FROM app_orden_de_entrega n
                        LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                        LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid  
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid 
                        WHERE n.n_surcid=$sucursal AND n.n_empagente=$agente AND n.n_divid = $divisa AND n_estatus < 3 AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    } else {
                        return $this->consultar("SELECT n.n_id as id,n.n_correlativo as correlativo,n.n_fecha as fecha, n.n_divid as divisa,n.n_empagente as agente,n.n_estatus as estatus,c.cli_razon_social,c.cli_id,aei.einfo_nombres as nombre,aei.einfo_apellidos as apellido, nart.nart_proid, nart.nart_prototal AS total, nart.nart_procantidad as cantidad,nart.nart_procosto as costo, s.su_nombre,gp.*  
                        FROM app_orden_de_entrega n
                        LEFT JOIN app_orden_de_entrega_articulos nart ON n.n_id = nart.nart_nid 
                        LEFT JOIN app_clientes c ON c.cli_id = n.n_cliid  
                        LEFT JOIN app_sucursales s ON s.su_id=n.n_surcid 
                        LEFT JOIN app_empleados_info aei ON aei.einfo_id = n.n_empleadoid 
                        LEFT JOIN app_clientes_grupos gp ON gp.grup_id= c.cli_grupid  
                        WHERE n.n_surcid=$sucursal AND n.n_divid = $divisa AND n_estatus < 3 AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND nart.nart_proid = $id_producto 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_fecha")->all();
                    }
                }
            }
        }
    }

    //TAB BALANCE DE MOVIMENTOS

    public function obtenerBalanceDelMesCredito($fecha_desde, $fecha_hasta, $tipo)
    {
        if ($tipo == 0 || $tipo == 1) {
            return $this->consultar("SELECT *, DATE_FORMAT(fab_fecha, '%d/%m/%Y') as fecha 
            FROM app_movimientos m                                    
            WHERE fab_credito = 1 AND fab_retiro = $tipo AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC")->all();
        } else {
            return $this->consultar("SELECT *, DATE_FORMAT(fab_fecha, '%d/%m/%Y') as fecha 
            FROM app_movimientos m                                     
            WHERE fab_credito = 1 AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND fab_retiro !=4 ORDER BY m.fab_id ASC")->all();
        }
    }

    public function obtenerBalanceDelMesCreditoDivisa($fecha_desde, $fecha_hasta, $tipo, $id_divisa)
    {
        if ($tipo == 0 || $tipo == 1) {
            if (!empty($id_divisa)) {
                return $this->consultar("SELECT *, DATE_FORMAT(fab_fecha, '%d/%m/%Y') as fecha 
                FROM app_movimientos m 
                WHERE fab_divisa = $id_divisa AND fab_credito = 1 AND fab_retiro = $tipo AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC")->all();
            } else {
                return $this->consultar("SELECT *, DATE_FORMAT(fab_fecha, '%d/%m/%Y') as fecha 
                FROM app_movimientos m 
                WHERE fab_credito = 1 AND fab_retiro = $tipo AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC")->all();
            }
        } else {
            if (!empty($id_divisa)) {
                return $this->consultar("SELECT *, DATE_FORMAT(fab_fecha, '%d/%m/%Y') as fecha 
                FROM app_movimientos m 
                WHERE fab_divisa = $id_divisa AND fab_credito = 1 AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC")->all();
            } else {
                return $this->consultar("SELECT *, DATE_FORMAT(fab_fecha, '%d/%m/%Y') as fecha 
                FROM app_movimientos m 
                WHERE fab_credito = 1 AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC")->all();
            }
        }
    }

    public function obtenerBalanceDelMes($fecha_desde, $fecha_hasta, $tipo, $categoria = null)
    {
        if (!empty($categoria) || $categoria != null) {
            if ($tipo == 0 || $tipo == 1) {
                $where = "fab_credito = 0 AND fab_retiro = $tipo AND m.fab_categoria = $categoria AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            } else {
                $where = "fab_credito = 0 AND m.fab_categoria = $categoria AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND fab_retiro !=4 ORDER BY m.fab_id ASC";
            }
        } else {
            if ($tipo == 0 || $tipo == 1) {
                $where = "fab_credito = 0 AND fab_retiro = $tipo AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            } else {
                $where = "fab_credito = 0 AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND fab_retiro !=4 ORDER BY m.fab_id ASC";
            }
        }

        return $this->consultar("SELECT e.emp_username, m.fab_doc,m.fab_idgenerico,m.fab_factor,m.fab_divid_conversion,m.fab_divisa, m.fab_monto, m.fab_factor_master, m.fab_descripcion, m.fab_observacion, bc_alias, cie_descripcion, DATE_FORMAT(fab_fecha, '%d/%m/%Y') as fecha, s.* 
            FROM app_movimientos m
            JOIN app_empleados e ON e.emp_id = m.fab_empid
            JOIN app_cuentas_metodopago cm ON cm.cmp_id = m.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 
            LEFT JOIN app_sucursales s ON s.su_id=m.fab_sucursal 
            LEFT JOIN app_categorias_ingreso_egreso cie ON cie.cie_id = m.fab_categoria
            WHERE $where")->all();
    }

    public function obtenerBalanceDelMesCreditoGrafica($anio)
    {
        if ($anio > 0) {
            return $this->consultar("SELECT *, DATE_FORMAT(fab_fecha, '%m') as mes  
    FROM app_movimientos m 
    WHERE fab_credito = 1 AND YEAR(fab_fecha) ='$anio' AND fab_retiro!=3")->all();
        } else {
            return $this->consultar("SELECT *, DATE_FORMAT(fab_fecha, '%m') as mes  
    FROM app_movimientos m 
    WHERE fab_credito = 1 AND fab_retiro!=3")->all();
        }
    }

    public function obtenerBalanceDelMesGrafica($anio, $mes, $tipo, $sucursal, $divisa)
    {

        if ($divisa > 0) {
            $peticion = ($sucursal > 0) ? " AND m.fab_sucursal=$sucursal AND m.fab_divisa=$divisa" : "";
        } else {
            $peticion = ($sucursal > 0) ? " AND m.fab_sucursal=$sucursal" : "";
        }


        if ($mes > 0) {
            if ($tipo == 0 || $tipo == 1) {
                return $this->consultar("SELECT e.emp_username, m.fab_doc,m.fab_idgenerico,m.fab_factor,m.fab_retiro,m.fab_divid_conversion,m.fab_divisa, m.fab_monto, m.fab_factor_master, m.fab_descripcion, m.fab_observacion,m.fab_categoria, bc_alias, cie_descripcion, DATE_FORMAT(fab_fecha, '%m') as mes, s.* 
            FROM app_movimientos m
            JOIN app_empleados e ON e.emp_id = m.fab_empid
            JOIN app_cuentas_metodopago cm ON cm.cmp_id = m.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 
            LEFT JOIN app_sucursales s ON s.su_id=m.fab_sucursal 
            LEFT JOIN app_categorias_ingreso_egreso cie ON cie.cie_id = m.fab_categoria
            WHERE YEAR(m.fab_fecha) ='$anio' AND MONTH(m.fab_fecha) ='$mes' AND m.fab_retiro=$tipo $peticion")->all();
            } else {
                return $this->consultar("SELECT e.emp_username, m.fab_doc,m.fab_idgenerico,m.fab_factor,m.fab_retiro,m.fab_divid_conversion,m.fab_divisa, m.fab_monto, m.fab_factor_master, m.fab_descripcion, m.fab_observacion,m.fab_categoria, bc_alias, cie_descripcion, DATE_FORMAT(fab_fecha, '%m') as mes, s.* 
                FROM app_movimientos m
                JOIN app_empleados e ON e.emp_id = m.fab_empid
                JOIN app_cuentas_metodopago cm ON cm.cmp_id = m.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 
                LEFT JOIN app_sucursales s ON s.su_id=m.fab_sucursal 
                LEFT JOIN app_categorias_ingreso_egreso cie ON cie.cie_id = m.fab_categoria
                WHERE YEAR(m.fab_fecha) ='$anio' AND MONTH(m.fab_fecha) ='$mes' AND m.fab_retiro!=4 $peticion")->all();
            }
        } else {
            if ($tipo == 0 || $tipo == 1) {
                return $this->consultar("SELECT e.emp_username, m.fab_doc,m.fab_idgenerico,m.fab_factor,m.fab_retiro,m.fab_divid_conversion,m.fab_divisa, m.fab_monto, m.fab_factor_master, m.fab_descripcion, m.fab_observacion,m.fab_categoria, bc_alias, cie_descripcion, DATE_FORMAT(fab_fecha, '%m') as mes, s.* 
            FROM app_movimientos m
            JOIN app_empleados e ON e.emp_id = m.fab_empid
            JOIN app_cuentas_metodopago cm ON cm.cmp_id = m.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 
            LEFT JOIN app_sucursales s ON s.su_id=m.fab_sucursal 
            LEFT JOIN app_categorias_ingreso_egreso cie ON cie.cie_id = m.fab_categoria 
            WHERE YEAR(m.fab_fecha) ='$anio' AND m.fab_retiro=$tipo $peticion")->all();
            } else {
                return $this->consultar("SELECT e.emp_username, m.fab_doc,m.fab_idgenerico,m.fab_factor,m.fab_retiro,m.fab_divid_conversion,m.fab_divisa, m.fab_monto, m.fab_factor_master, m.fab_descripcion, m.fab_observacion,m.fab_categoria, bc_alias, cie_descripcion, DATE_FORMAT(fab_fecha, '%m') as mes, s.* 
                FROM app_movimientos m
                JOIN app_empleados e ON e.emp_id = m.fab_empid
                JOIN app_cuentas_metodopago cm ON cm.cmp_id = m.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 
                LEFT JOIN app_sucursales s ON s.su_id=m.fab_sucursal 
                LEFT JOIN app_categorias_ingreso_egreso cie ON cie.cie_id = m.fab_categoria 
                WHERE YEAR(m.fab_fecha) ='$anio' AND m.fab_retiro!=4 $peticion")->all();
            }
        }
    }

    public function obtenerBalancePorDivisa($fecha_desde, $fecha_hasta, $id_divisa, $tipo, $categoria = null)
    {
        if (!empty($categoria) || $categoria != null) {
            if ($tipo == 0 || $tipo == 1) {
                $where = "fab_credito = 0 AND fab_retiro = $tipo AND m.fab_categoria = $categoria AND m.fab_divisa = $id_divisa AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            } else {
                $where = "fab_credito = 0 AND m.fab_categoria = $categoria AND m.fab_divisa = $id_divisa AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            }
        } else {
            if ($tipo == 0 || $tipo == 1) {
                $where = "fab_credito = 0 AND fab_retiro = $tipo AND m.fab_divisa = $id_divisa AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            } else {
                $where = "fab_credito = 0 AND m.fab_divisa = $id_divisa AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            }
        }

        return $this->consultar("SELECT e.emp_username,  m.fab_doc,m.fab_idgenerico,m.fab_factor,m.fab_divid_conversion,m.fab_divisa, m.fab_monto, m.fab_factor_master, m.fab_descripcion, m.fab_observacion, bc_alias, cie_descripcion, DATE_FORMAT(fab_fecha, '%d/%m/%Y') as fecha 
            FROM app_movimientos m 
            JOIN app_empleados e ON e.emp_id = m.fab_empid
            JOIN app_cuentas_metodopago cm ON cm.cmp_id = m.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 
            LEFT JOIN app_categorias_ingreso_egreso cie ON cie.cie_id = m.fab_categoria
            WHERE $where")->all();
    }

    public function obtenerBalancePorDivisaYTipodeMetodo($fecha_desde, $fecha_hasta, $id_divisa, $id_metodo_divisa, $tipo, $categoria = null)
    {
        if (!empty($categoria) || $categoria != null) {
            if ($tipo == 0 || $tipo == 1) {
                $where = "fab_credito = 0 AND fab_retiro = $tipo AND m.fab_categoria = $categoria AND m.fab_divisa = $id_divisa AND m.fab_metodo = $id_metodo_divisa AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            } else {
                $where = "fab_credito = 0 AND m.fab_categoria = $categoria AND m.fab_divisa = $id_divisa AND m.fab_metodo = $id_metodo_divisa AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            }
        } else {
            if ($tipo == 0 || $tipo == 1) {
                $where = "fab_credito = 0 AND fab_retiro = $tipo AND m.fab_divisa = $id_divisa AND m.fab_metodo = $id_metodo_divisa AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            } else {
                $where = "fab_credito = 0 AND m.fab_divisa = $id_divisa AND m.fab_metodo = $id_metodo_divisa AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            }
        }

        return $this->consultar("SELECT e.emp_username, m.fab_doc,m.fab_idgenerico,m.fab_factor,m.fab_divid_conversion, m.fab_divisa, m.fab_monto, m.fab_factor_master, m.fab_descripcion, m.fab_observacion, bc_alias, cie_descripcion, DATE_FORMAT(fab_fecha, '%d/%m/%Y') as fecha 
            FROM app_movimientos m 
            JOIN app_empleados e ON e.emp_id = m.fab_empid
            JOIN app_cuentas_metodopago cm ON cm.cmp_id = m.fab_cmpid 
            JOIN app_metodopago_divisa mpd ON mpd.md_id = cm.cmp_mdid 
            JOIN app_metodopago me ON me.mp_id = mpd.md_mpid 
            JOIN app_bancos_cuentas bc ON bc.bc_id = cm.cmp_cueid 
            JOIN app_bancos b ON b.ban_id = bc.bc_banid 
            LEFT JOIN app_categorias_ingreso_egreso cie ON cie.cie_id = m.fab_categoria
            WHERE $where")->all();
    }

    public function obtenerBalancePorCuenta($fecha_desde, $fecha_hasta, $id_cuenta_metodo, $tipo, $categoria = null)
    {
        if (!empty($categoria) || $categoria != null) {
            if ($tipo == 0 || $tipo == 1) {
                $where = "fab_credito = 0 AND fab_retiro = $tipo AND m.fab_categoria = $categoria AND m.fab_cmpid = $id_cuenta_metodo AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            } else {
                $where = "fab_credito = 0 AND m.fab_categoria = $categoria AND m.fab_cmpid = $id_cuenta_metodo AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            }
        } else {
            if ($tipo == 0 || $tipo == 1) {
                $where = "fab_credito = 0 AND fab_retiro = $tipo AND m.fab_cmpid = $id_cuenta_metodo AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            } else {
                $where = "fab_credito = 0 AND m.fab_cmpid = $id_cuenta_metodo AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            }
        }

        return $this->consultar("SELECT e.emp_username, m.fab_doc,m.fab_idgenerico,m.fab_factor,m.fab_divid_conversion, m.fab_divisa, m.fab_monto, m.fab_factor_master, m.fab_descripcion, m.fab_observacion, bc_alias, cie_descripcion, DATE_FORMAT(fab_fecha, '%d/%m/%Y') as fecha 
            FROM app_movimientos m 
            JOIN app_empleados e ON e.emp_id = m.fab_empid
            JOIN app_cuentas_metodopago cm ON cm.cmp_id = m.fab_cmpid 
            JOIN app_metodopago_divisa mpd ON mpd.md_id = cm.cmp_mdid 
            JOIN app_metodopago me ON me.mp_id = mpd.md_mpid 
            JOIN app_bancos_cuentas bc ON bc.bc_id = cm.cmp_cueid 
            JOIN app_bancos b ON b.ban_id = bc.bc_banid 
            LEFT JOIN app_categorias_ingreso_egreso cie ON cie.cie_id = m.fab_categoria
            WHERE $where")->all();
    }

    public function obtenerBalanceSoloPorCuenta($fecha_desde, $fecha_hasta, $id_cuenta_metodo, $tipo, $categoria = null)
    {
        $cuenta_metodo = $this->consultar("SELECT * FROM app_cuentas_metodopago WHERE cmp_id = $id_cuenta_metodo")->row();
        $cuenta = $this->consultar("SELECT * FROM app_bancos_cuentas WHERE bc_id = {$cuenta_metodo['cmp_cueid']}")->row();

        if (!empty($categoria) || $categoria != null) {
            if ($tipo == 0 || $tipo == 1) {
                $where = "bc.bc_id = {$cuenta['bc_id']} AND fab_credito = 0 AND fab_retiro = $tipo AND m.fab_categoria = $categoria AND m.fab_cmpid = $id_cuenta_metodo AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            } else {
                $where = "bc.bc_id = {$cuenta['bc_id']} AND fab_credito = 0 AND m.fab_categoria = $categoria AND m.fab_cmpid = $id_cuenta_metodo AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            }
        } else {
            if ($tipo == 0 || $tipo == 1) {
                $where = "bc.bc_id = {$cuenta['bc_id']} AND fab_credito = 0 AND fab_retiro = $tipo AND m.fab_cmpid = $id_cuenta_metodo AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            } else {
                $where = "bc.bc_id = {$cuenta['bc_id']} AND fab_credito = 0 AND m.fab_cmpid = $id_cuenta_metodo AND (DATE(fab_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY m.fab_id ASC";
            }
        }

        return $this->consultar("SELECT e.emp_username, m.fab_doc,m.fab_idgenerico,m.fab_factor,m.fab_divid_conversion, m.fab_divisa, m.fab_monto, m.fab_factor_master, m.fab_descripcion, m.fab_observacion, bc_alias, cie_descripcion, DATE_FORMAT(fab_fecha, '%d/%m/%Y') as fecha 
            FROM app_movimientos m 
            JOIN app_empleados e ON e.emp_id = m.fab_empid
            JOIN app_cuentas_metodopago cm ON cm.cmp_id = m.fab_cmpid 
            JOIN app_metodopago_divisa mpd ON mpd.md_id = cm.cmp_mdid 
            JOIN app_metodopago me ON me.mp_id = mpd.md_mpid 
            JOIN app_bancos_cuentas bc ON bc.bc_id = cm.cmp_cueid 
            JOIN app_bancos b ON b.ban_id = bc.bc_banid 
            LEFT JOIN app_categorias_ingreso_egreso cie ON cie.cie_id = m.fab_categoria
            WHERE $where")->all();
    }
    //TAB HORA
    public function obtenerProduccionNEVDiaHora($dia, $divisa, $sucursal)
    {
        if ($divisa == 0) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT ne.ne_divid, DATE_FORMAT(ne.ne_fecha, '%r') AS hora, SUM(neart_procantidad) AS cantidad,  SUM(neart.neart_procantidad*neart.neart_procosto) as total_costo, SUM(neart_prototal) as total_venta, su.* 
                FROM app_notas_de_entrega_articulos neart 
                JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
                JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                JOIN app_productos p ON p.pro_id = neart.neart_proid 
                WHERE ne.ne_estatus < 3 AND DATE(ne.ne_fecha) = '$dia' 
                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 DAY(ne.ne_fecha), ne.ne_divid, p.pro_id")->all();
            } else {
                return $this->consultar("SELECT ne.ne_divid, DATE_FORMAT(ne.ne_fecha, '%r') AS hora, SUM(neart_procantidad) AS cantidad,  SUM(neart.neart_procantidad*neart.neart_procosto) as total_costo, SUM(neart_prototal) as total_venta, su.* 
                FROM app_notas_de_entrega_articulos neart 
                JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
                JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                JOIN app_productos p ON p.pro_id = neart.neart_proid 
                WHERE ne.ne_surcid=$sucursal AND ne.ne_estatus < 3 AND DATE(ne.ne_fecha) = '$dia' 
                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 DAY(ne.ne_fecha), ne.ne_divid, p.pro_id")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT ne.ne_divid, DATE_FORMAT(ne.ne_fecha, '%r') AS hora, SUM(neart_procantidad) AS cantidad, SUM(neart.neart_procantidad*neart.neart_procosto) as total_costo, SUM(neart_prototal) as total_venta, su.* 
                FROM app_notas_de_entrega_articulos neart 
                JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
                JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                JOIN app_productos p ON p.pro_id = neart.neart_proid 
                WHERE ne.ne_estatus < 3 AND DATE(ne.ne_fecha) = '$dia' AND ne.ne_divid = $divisa 
                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 DAY(ne.ne_fecha), ne.ne_divid, p.pro_id")->all();
            } else {
                return $this->consultar("SELECT ne.ne_divid, DATE_FORMAT(ne.ne_fecha, '%r') AS hora, SUM(neart_procantidad) AS cantidad,  SUM(neart.neart_procantidad*neart.neart_procosto) as total_costo, SUM(neart_prototal) as total_venta, su.* 
                FROM app_notas_de_entrega_articulos neart 
                JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
                JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                JOIN app_productos p ON p.pro_id = neart.neart_proid 
                WHERE ne.ne_surcid=$sucursal AND ne.ne_estatus < 3 AND DATE(ne.ne_fecha) = '$dia' AND ne.ne_divid = $divisa
                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 DAY(ne.ne_fecha), ne.ne_divid, p.pro_id")->all();
            }
        }
    }
    public function obtenerProduccionFACVDiaHora($fecha, $divisa, $sucursal)
    {
        //p.pro_costo * SUM(fa.fa_procantidad) as total_costo
        if ($divisa == 0) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%r') AS hora, SUM(fa.fa_procantidad) AS cantidad, SUM(fa.fa_procantidad*fa.fa_procosto) as total_costo, SUM(fa.fa_prototal) as total_venta,su.* 
                FROM app_factura_articulos fa
                JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_productos p ON p.pro_id = fa.fa_proid 
                WHERE fm.fm_estatus < 3 AND YEAR(fm.fm_fecha) = '$fecha' 
                GROUP BY DAY(fm.fm_fecha), fm.fm_divid, p.pro_id")->all();
            } else {
                return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%r') AS hora, SUM(fa.fa_procantidad) AS cantidad,  SUM(fa.fa_procantidad*fa.fa_procosto) as total_costo, SUM(fa.fa_prototal) as total_venta,su.* 
                FROM app_factura_articulos fa
                JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_productos p ON p.pro_id = fa.fa_proid 
                WHERE fm.fm_surcid=$sucursal AND fm.fm_estatus < 3 AND YEAR(fm.fm_fecha) = '$fecha' 
                GROUP BY DAY(fm.fm_fecha), fm.fm_divid, p.pro_id")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%r') AS hora, SUM(fa.fa_procantidad) AS cantidad, SUM(fa.fa_procantidad*fa.fa_procosto) as total_costo, SUM(fa.fa_prototal) as total_venta,su.* 
                FROM app_factura_articulos fa
                JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_productos p ON p.pro_id = fa.fa_proid 
                WHERE fm.fm_estatus < 3 AND YEAR(fm.fm_fecha) = '$fecha' AND fm.fm_divid = $divisa
                GROUP BY DAY(fm.fm_fecha), fm.fm_divid, p.pro_id")->all();
            } else {
                return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%r') AS hora, SUM(fa.fa_procantidad) AS cantidad, SUM(fa.fa_procantidad*fa.fa_procosto) as total_costo, SUM(fa.fa_prototal) as total_venta,su.* 
                FROM app_factura_articulos fa
                JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_productos p ON p.pro_id = fa.fa_proid 
                WHERE fm.fm_surcid=$sucursal AND fm.fm_estatus < 3 AND YEAR(fm.fm_fecha) = '$fecha' AND fm.fm_divid = $divisa
                GROUP BY DAY(fm.fm_fecha), fm.fm_divid, p.pro_id")->all();
            }
        }
    }
    public function obtenerProduccionORDENDiaHora($fecha, $divisa, $sucursal)
    {
        if ($divisa == 0) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT n.n_divid, DATE_FORMAT(n.n_fecha, '%r') AS hora, SUM(nart_procantidad) AS cantidad,  SUM(nart.nart_procantidad*nart.nart_procosto) as total_costo, SUM(nart_prototal) as total_venta,su.* 
                FROM app_orden_de_entrega_articulos nart 
                JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid 
                JOIN app_sucursales su ON su.su_id=n.n_surcid 
                JOIN app_productos p ON p.pro_id = nart.nart_proid 
                WHERE n.n_estatus < 3 AND YEAR(n.n_fecha) = '$fecha' 
                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 DAY(n.n_fecha), n.n_divid, p.pro_id")->all();
            } else {
                return $this->consultar("SELECT n.n_divid, DATE_FORMAT(n.n_fecha, '%r') AS hora, SUM(nart_procantidad) AS cantidad,  SUM(nart.nart_procantidad*nart.nart_procosto) as total_costo, SUM(nart_prototal) as total_venta,su.* 
                FROM app_orden_de_entrega_articulos nart 
                JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid 
                JOIN app_sucursales su ON su.su_id=n.n_surcid 
                JOIN app_productos p ON p.pro_id = nart.nart_proid 
                WHERE n.n_surcid=$sucursal AND  n.n_estatus < 3 AND YEAR(n.n_fecha) = '$fecha' 
                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 DAY(n.n_fecha), n.n_divid, p.pro_id")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT n.n_divid, DATE_FORMAT(n.n_fecha, '%r') AS hora, SUM(nart_procantidad) AS cantidad,  SUM(nart.nart_procantidad*nart.nart_procosto) as total_costo, SUM(nart_prototal) as total_venta,su.* 
                FROM app_orden_de_entrega_articulos nart 
                JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid 
                JOIN app_sucursales su ON su.su_id=n.n_surcid 
                JOIN app_productos p ON p.pro_id = nart.nart_proid 
                WHERE n.n_estatus < 3 AND YEAR(n.n_fecha) = '$fecha' AND n.n_divid = $divisa
                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 DAY(n.n_fecha), n.n_divid, p.pro_id")->all();
            } else {
                return $this->consultar("SELECT n.n_divid, DATE_FORMAT(n.n_fecha, '%r') AS hora, SUM(nart_procantidad) AS cantidad,  SUM(nart.nart_procantidad*nart.nart_procosto) as total_costo, SUM(nart_prototal) as total_venta,su.* 
                FROM app_orden_de_entrega_articulos nart 
                JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid 
                JOIN app_sucursales su ON su.su_id=n.n_surcid 
                JOIN app_productos p ON p.pro_id = nart.nart_proid 
                WHERE n.n_surcid=$sucursal AND  n.n_estatus < 3 AND YEAR(n.n_fecha) = '$fecha' AND n.n_divid = $divisa
                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 DAY(n.n_fecha), n.n_divid, p.pro_id")->all();
            }
        }
    }
    //TAB DIA

    public function obtenerProduccionNEVDia($mes, $anio, $divisa, $sucursal)
    {
        if ($divisa == 0) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT ne.ne_divid, DATE_FORMAT(ne.ne_fecha, '%d') AS dia, SUM(neart_procantidad) AS cantidad,  SUM(neart.neart_procantidad*neart.neart_procosto) as total_costo, SUM(neart_prototal) as total_venta, su.* 
                FROM app_notas_de_entrega_articulos neart 
                JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
                JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                JOIN app_productos p ON p.pro_id = neart.neart_proid 
                WHERE ne.ne_estatus < 3 AND YEAR(ne.ne_fecha) = '$anio' AND MONTH(ne.ne_fecha) = '$mes' 
                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 DAY(ne.ne_fecha), ne.ne_divid, p.pro_id")->all();
            } else {
                return $this->consultar("SELECT ne.ne_divid, DATE_FORMAT(ne.ne_fecha, '%d') AS dia, SUM(neart_procantidad) AS cantidad,  SUM(neart.neart_procantidad*neart.neart_procosto) as total_costo, SUM(neart_prototal) as total_venta, su.* 
                FROM app_notas_de_entrega_articulos neart 
                JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
                JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                JOIN app_productos p ON p.pro_id = neart.neart_proid 
                WHERE ne.ne_surcid=$sucursal AND ne.ne_estatus < 3 AND YEAR(ne.ne_fecha) = '$anio' AND MONTH(ne.ne_fecha) = '$mes' 
                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 DAY(ne.ne_fecha), ne.ne_divid, p.pro_id")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT ne.ne_divid, DATE_FORMAT(ne.ne_fecha, '%d') AS dia, SUM(neart_procantidad) AS cantidad, SUM(neart.neart_procantidad*neart.neart_procosto) as total_costo, SUM(neart_prototal) as total_venta, su.* 
                FROM app_notas_de_entrega_articulos neart 
                JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
                JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                JOIN app_productos p ON p.pro_id = neart.neart_proid 
                WHERE ne.ne_estatus < 3 AND YEAR(ne.ne_fecha) = '$anio' AND MONTH(ne.ne_fecha) = '$mes' AND ne.ne_divid = $divisa
                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 DAY(ne.ne_fecha), ne.ne_divid, p.pro_id")->all();
            } else {
                return $this->consultar("SELECT ne.ne_divid, DATE_FORMAT(ne.ne_fecha, '%d') AS dia, SUM(neart_procantidad) AS cantidad,  SUM(neart.neart_procantidad*neart.neart_procosto) as total_costo, SUM(neart_prototal) as total_venta, su.* 
                FROM app_notas_de_entrega_articulos neart 
                JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
                JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                JOIN app_productos p ON p.pro_id = neart.neart_proid 
                WHERE ne.ne_surcid=$sucursal AND ne.ne_estatus < 3 AND YEAR(ne.ne_fecha) = '$anio' AND MONTH(ne.ne_fecha) = '$mes' AND ne.ne_divid = $divisa
                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 DAY(ne.ne_fecha), ne.ne_divid, p.pro_id")->all();
            }
        }
    }

    public function obtenerProduccionFACVDia($mes, $anio, $divisa, $sucursal)
    {
        //p.pro_costo * SUM(fa.fa_procantidad) as total_costo
        if ($divisa == 0) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%d') AS dia, SUM(fa.fa_procantidad) AS cantidad, SUM(fa.fa_procantidad*fa.fa_procosto) as total_costo, SUM(fa.fa_prototal) as total_venta,su.* 
                FROM app_factura_articulos fa
                JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_productos p ON p.pro_id = fa.fa_proid 
                WHERE fm.fm_estatus < 3 AND YEAR(fm.fm_fecha) = '$anio' AND MONTH(fm.fm_fecha) = '$mes' 
                GROUP BY DAY(fm.fm_fecha), fm.fm_divid, p.pro_id")->all();
            } else {
                return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%d') AS dia, SUM(fa.fa_procantidad) AS cantidad,  SUM(fa.fa_procantidad*fa.fa_procosto) as total_costo, SUM(fa.fa_prototal) as total_venta,su.* 
                FROM app_factura_articulos fa
                JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_productos p ON p.pro_id = fa.fa_proid 
                WHERE fm.fm_surcid=$sucursal AND fm.fm_estatus < 3 AND YEAR(fm.fm_fecha) = '$anio' AND MONTH(fm.fm_fecha) = '$mes' 
                GROUP BY DAY(fm.fm_fecha), fm.fm_divid, p.pro_id")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%d') AS dia, SUM(fa.fa_procantidad) AS cantidad, SUM(fa.fa_procantidad*fa.fa_procosto) as total_costo, SUM(fa.fa_prototal) as total_venta,su.* 
                FROM app_factura_articulos fa
                JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_productos p ON p.pro_id = fa.fa_proid 
                WHERE fm.fm_estatus < 3 AND YEAR(fm.fm_fecha) = '$anio' AND MONTH(fm.fm_fecha) = '$mes' AND fm.fm_divid = $divisa
                GROUP BY DAY(fm.fm_fecha), fm.fm_divid, p.pro_id")->all();
            } else {
                return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%d') AS dia, SUM(fa.fa_procantidad) AS cantidad, SUM(fa.fa_procantidad*fa.fa_procosto) as total_costo, SUM(fa.fa_prototal) as total_venta,su.* 
                FROM app_factura_articulos fa
                JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_productos p ON p.pro_id = fa.fa_proid 
                WHERE fm.fm_surcid=$sucursal AND fm.fm_estatus < 3 AND YEAR(fm.fm_fecha) = '$anio' AND MONTH(fm.fm_fecha) = '$mes' AND fm.fm_divid = $divisa
                GROUP BY DAY(fm.fm_fecha), fm.fm_divid, p.pro_id")->all();
            }
        }
    }

    public function obtenerProduccionORDENDia($mes, $anio, $divisa, $sucursal)
    {
        if ($divisa == 0) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT n.n_divid, DATE_FORMAT(n.n_fecha, '%d') AS dia, SUM(nart_procantidad) AS cantidad,  SUM(nart.nart_procantidad*nart.nart_procosto) as total_costo, SUM(nart_prototal) as total_venta,su.* 
                FROM app_orden_de_entrega_articulos nart 
                JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid 
                JOIN app_sucursales su ON su.su_id=n.n_surcid 
                JOIN app_productos p ON p.pro_id = nart.nart_proid 
                WHERE n.n_estatus < 3 AND YEAR(n.n_fecha) = '$anio' AND MONTH(n.n_fecha) = '$mes' 
                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 DAY(n.n_fecha), n.n_divid, p.pro_id")->all();
            } else {
                return $this->consultar("SELECT n.n_divid, DATE_FORMAT(n.n_fecha, '%d') AS dia, SUM(nart_procantidad) AS cantidad,  SUM(nart.nart_procantidad*nart.nart_procosto) as total_costo, SUM(nart_prototal) as total_venta,su.* 
                FROM app_orden_de_entrega_articulos nart 
                JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid 
                JOIN app_sucursales su ON su.su_id=n.n_surcid 
                JOIN app_productos p ON p.pro_id = nart.nart_proid 
                WHERE n.n_surcid=$sucursal AND  n.n_estatus < 3 AND YEAR(n.n_fecha) = '$anio' AND MONTH(n.n_fecha) = '$mes' 
                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 DAY(n.n_fecha), n.n_divid, p.pro_id")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT n.n_divid, DATE_FORMAT(n.n_fecha, '%d') AS dia, SUM(nart_procantidad) AS cantidad,  SUM(nart.nart_procantidad*nart.nart_procosto) as total_costo, SUM(nart_prototal) as total_venta,su.* 
                FROM app_orden_de_entrega_articulos nart 
                JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid 
                JOIN app_sucursales su ON su.su_id=n.n_surcid 
                JOIN app_productos p ON p.pro_id = nart.nart_proid 
                WHERE n.n_estatus < 3 AND YEAR(n.n_fecha) = '$anio' AND MONTH(n.n_fecha) = '$mes' AND n.n_divid = $divisa
                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 DAY(n.n_fecha), n.n_divid, p.pro_id")->all();
            } else {
                return $this->consultar("SELECT n.n_divid, DATE_FORMAT(n.n_fecha, '%d') AS dia, SUM(nart_procantidad) AS cantidad,  SUM(nart.nart_procantidad*nart.nart_procosto) as total_costo, SUM(nart_prototal) as total_venta,su.* 
                FROM app_orden_de_entrega_articulos nart 
                JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid 
                JOIN app_sucursales su ON su.su_id=n.n_surcid 
                JOIN app_productos p ON p.pro_id = nart.nart_proid 
                WHERE n.n_surcid=$sucursal AND  n.n_estatus < 3 AND YEAR(n.n_fecha) = '$anio' AND MONTH(n.n_fecha) = '$mes' AND n.n_divid = $divisa
                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 DAY(n.n_fecha), n.n_divid, p.pro_id")->all();
            }
        }
    }

    //TAB MES

    public function obtenerProduccionNEVMes($anio, $divisa, $sucursal)
    {
        if ($anio > 0) {
            $peticion = " AND YEAR(ne.ne_fecha) = '$anio'";
        } else {
            $peticion = "";
        }

        if ($divisa == 0) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT ne.ne_divid, DATE_FORMAT(ne.ne_fecha, '%m') AS mes, DATE_FORMAT(ne.ne_fecha, '%Y') AS anio, neart.neart_procantidad AS cantidad, (neart.neart_procantidad*neart.neart_procosto) AS total_costo, neart.neart_prototal as total_venta,su.* 
                FROM app_notas_de_entrega_articulos neart 
                JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
                JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                JOIN app_productos p ON p.pro_id = neart.neart_proid 
                WHERE ne.ne_estatus < 3 $peticion ")->all();
            } else {
                return $this->consultar("SELECT ne.ne_divid, DATE_FORMAT(ne.ne_fecha, '%m') AS mes,DATE_FORMAT(ne.ne_fecha, '%Y') AS anio, neart.neart_procantidad AS cantidad,(neart.neart_procantidad*neart.neart_procosto) AS total_costo, neart.neart_prototal as total_venta,su.* 
                FROM app_notas_de_entrega_articulos neart 
                JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
                JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                JOIN app_productos p ON p.pro_id = neart.neart_proid 
                WHERE ne.ne_surcid=$sucursal AND ne.ne_estatus < 3 $peticion ")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT ne.ne_divid, DATE_FORMAT(ne.ne_fecha, '%m') AS mes,DATE_FORMAT(ne.ne_fecha, '%Y') AS anio, neart.neart_procantidad AS cantidad, (neart.neart_procantidad*neart.neart_procosto) AS total_costo, neart.neart_prototal as total_venta,su.* 
                FROM app_notas_de_entrega_articulos neart 
                JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
                JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                JOIN app_productos p ON p.pro_id = neart.neart_proid 
                WHERE ne.ne_estatus < 3 $peticion AND ne.ne_divid = $divisa ")->all();
            } else {
                return $this->consultar("SELECT ne.ne_divid, DATE_FORMAT(ne.ne_fecha, '%m') AS mes,DATE_FORMAT(ne.ne_fecha, '%Y') AS anio, neart.neart_procantidad AS cantidad, (neart.neart_procantidad*neart.neart_procosto) AS total_costo, neart.neart_prototal as total_venta,su.* 
                FROM app_notas_de_entrega_articulos neart 
                JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
                JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                JOIN app_productos p ON p.pro_id = neart.neart_proid 
                WHERE ne.ne_surcid=$sucursal AND ne.ne_estatus < 3 $peticion AND ne.ne_divid = $divisa")->all();
            }
        }
    }

    public function obtenerProduccionFACVMes($anio, $divisa, $sucursal)
    {
        if ($anio > 0) {
            $peticion = " AND YEAR(fm.fm_fecha) = '$anio'";
        } else {
            $peticion = "";
        }

        if ($divisa == 0) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%m') AS mes,DATE_FORMAT(fm.fm_fecha, '%Y') AS anio, fa.fa_procantidad AS cantidad, (fa.fa_procantidad*fa.fa_procosto) as total_costo, fa.fa_prototal as total_venta, su.*
                FROM app_factura_articulos fa
                JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_productos p ON p.pro_id = fa.fa_proid 
                WHERE fm.fm_estatus < 3 $peticion")->all();
            } else {
                return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%m') AS mes,DATE_FORMAT(fm.fm_fecha, '%Y') AS anio, fa.fa_procantidad AS cantidad, (fa.fa_procantidad*fa.fa_procosto) as total_costo, fa.fa_prototal as total_venta, su.*
                FROM app_factura_articulos fa
                JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_productos p ON p.pro_id = fa.fa_proid 
                WHERE fm.fm_surcid=$sucursal AND fm.fm_estatus < 3  $peticion ")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%m') AS mes,DATE_FORMAT(fm.fm_fecha, '%Y') AS anio, fa.fa_procantidad AS cantidad,  (fa.fa_procantidad*fa.fa_procosto) as total_costo, fa.fa_prototal as total_venta, su.*
                FROM app_factura_articulos fa
                JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_productos p ON p.pro_id = fa.fa_proid 
                WHERE fm.fm_estatus < 3 $peticion AND fm.fm_divid = $divisa ")->all();
            } else {
                return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%m') AS mes,DATE_FORMAT(fm.fm_fecha, '%Y') AS anio, fa.fa_procantidad AS cantidad,  fa.fa_procantidad*fa.fa_procosto as total_costo, fa.fa_prototal as total_venta, su.*
                FROM app_factura_articulos fa
                JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_productos p ON p.pro_id = fa.fa_proid 
                WHERE fm.fm_surcid=$sucursal AND fm.fm_estatus < 3 $peticion AND fm.fm_divid = $divisa ")->all();
            }
        }
    }

    public function obtenerProduccionORDENMes($anio, $divisa, $sucursal)
    {
        if ($anio > 0) {
            $peticion = " AND YEAR(n.n_fecha) = '$anio'";
        } else {
            $peticion = "";
        }

        if ($divisa == 0) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT n.n_divid, DATE_FORMAT(n.n_fecha, '%m') AS mes,DATE_FORMAT(n.n_fecha, '%Y') AS anio, nart_procantidad AS cantidad, (nart.nart_procantidad*nart.nart_procosto) as total_costo, nart_prototal as total_venta, su.*
                FROM app_orden_de_entrega_articulos nart 
                JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid 
                JOIN app_sucursales su ON su.su_id=n.n_surcid 
                JOIN app_productos p ON p.pro_id = nart.nart_proid 
                WHERE n.n_estatus < 3 $peticion ")->all();
            } else {
                return $this->consultar("SELECT n.n_divid, DATE_FORMAT(n.n_fecha, '%m') AS mes,DATE_FORMAT(n.n_fecha, '%Y') AS anio, nart_procantidad AS cantidad, (nart.nart_procantidad*nart.nart_procosto) as total_costo, nart_prototal as total_venta, su.*
                FROM app_orden_de_entrega_articulos nart 
                JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid 
                JOIN app_sucursales su ON su.su_id=n.n_surcid 
                JOIN app_productos p ON p.pro_id = nart.nart_proid 
                WHERE n.n_surcid=$sucursal AND n.n_estatus < 3 $peticion ")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT n.n_divid, DATE_FORMAT(n.n_fecha, '%m') AS mes,DATE_FORMAT(n.n_fecha, '%Y') AS anio, nart_procantidad AS cantidad, (nart.nart_procantidad*nart.nart_procosto) as total_costo, nart_prototal as total_venta, su.*
            FROM app_orden_de_entrega_articulos nart 
            JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid 
            JOIN app_sucursales su ON su.su_id=n.n_surcid 
            JOIN app_productos p ON p.pro_id = nart.nart_proid 
            WHERE n.n_estatus < 3 $peticion AND n.n_divid = $divisa ")->all();
            } else {
                return $this->consultar("SELECT n.n_divid, DATE_FORMAT(n.n_fecha, '%m') AS mes,DATE_FORMAT(n.n_fecha, '%Y') AS anio, nart_procantidad AS cantidad, (nart.nart_procantidad*nart.nart_procosto) as total_costo, nart_prototal as total_venta, su.*
            FROM app_orden_de_entrega_articulos nart 
            JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid 
            JOIN app_sucursales su ON su.su_id=n.n_surcid 
            JOIN app_productos p ON p.pro_id = nart.nart_proid 
            WHERE n.n_surcid=$sucursal AND n.n_estatus < 3 $peticion AND n.n_divid = $divisa ")->all();
            }
        }
    }

    //TAB ANIO

    public function obtenerProduccionNEVAnio($anio, $sucursal, $divisa)
    {

        if ($sucursal > 0) {
            $peticion = ($divisa > 0) ? " AND ne.ne_divid=$divisa AND ne.ne_surcid=$sucursal" : "AND ne.ne_surcid=$sucursal";
        } else {
            $peticion = ($divisa > 0) ? " AND ne.ne_divid=$divisa " : " ";
        }

        if ($anio == 0) {
            return $this->consultar("SELECT ne.ne_divid, DATE_FORMAT(ne.ne_fecha, '%m') AS mes, DATE_FORMAT(ne.ne_fecha, '%Y') AS anio, neart.neart_procantidad AS cantidad, (neart.neart_procantidad*neart.neart_procosto) AS total_costo, neart.neart_prototal as total_venta,su.* 
                  FROM app_notas_de_entrega_articulos neart 
                  JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
                  JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                  JOIN app_productos p ON p.pro_id = neart.neart_proid 
                  WHERE ne.ne_estatus < 3 $peticion
                  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();
        } else {
            return $this->consultar("SELECT ne.ne_divid, DATE_FORMAT(ne.ne_fecha, '%m') AS mes,DATE_FORMAT(ne.ne_fecha, '%Y') AS anio, neart.neart_procantidad AS cantidad,(neart.neart_procantidad*neart.neart_procosto) AS total_costo, neart.neart_prototal as total_venta,su.* 
                  FROM app_notas_de_entrega_articulos neart 
                  JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
                  JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                  JOIN app_productos p ON p.pro_id = neart.neart_proid 
                  WHERE  YEAR(ne.ne_fecha) = '$anio' AND ne.ne_estatus < 3  $peticion 
                  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 obtenerProduccionFACVAnio($anio, $sucursal, $divisa)
    {

        if ($sucursal > 0) {
            $peticion = ($divisa > 0) ? " AND fm.fm_divid=$divisa AND fm.fm_surcid=$sucursal" : "AND fm.fm_surcid=$sucursal";
        } else {
            $peticion = ($divisa > 0) ? " AND fm.fm_divid=$divisa " : " ";
        }

        if ($anio == 0) {
            return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%m') AS mes,DATE_FORMAT(fm.fm_fecha, '%Y') AS anio, fa.fa_procantidad AS cantidad, (fa.fa_procantidad*fa.fa_procosto) as total_costo, fa.fa_prototal as total_venta, su.*
                  FROM app_factura_articulos fa
                  JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                  JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                  JOIN app_productos p ON p.pro_id = fa.fa_proid 
                  WHERE fm.fm_estatus < 3 $peticion ")->all();
        } else {
            return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%m') AS mes,DATE_FORMAT(fm.fm_fecha, '%Y') AS anio, fa.fa_procantidad AS cantidad, (fa.fa_procantidad*fa.fa_procosto) as total_costo, fa.fa_prototal as total_venta, su.*
                  FROM app_factura_articulos fa
                  JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
                  JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                  JOIN app_productos p ON p.pro_id = fa.fa_proid 
                  WHERE  YEAR(fm.fm_fecha) = '$anio' AND fm.fm_estatus < 3 $peticion")->all();
        }
    }

    public function obtenerProduccionORDENAnio($anio, $sucursal, $divisa)
    {

        if ($sucursal > 0) {
            $peticion = ($divisa > 0) ? " AND n.n_divid=$divisa AND n.n_surcid=$sucursal" : "AND n.n_surcid=$sucursal";
        } else {
            $peticion = ($divisa > 0) ? " AND n.n_divid=$divisa " : " ";
        }

        if ($anio == 0) {
            return $this->consultar("SELECT n.n_divid, DATE_FORMAT(n.n_fecha, '%m') AS mes,DATE_FORMAT(n.n_fecha, '%Y') AS anio, nart_procantidad AS cantidad, (nart.nart_procantidad*nart.nart_procosto) as total_costo, nart_prototal as total_venta, su.*
                  FROM app_orden_de_entrega_articulos nart 
                  JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid 
                  JOIN app_sucursales su ON su.su_id=n.n_surcid 
                  JOIN app_productos p ON p.pro_id = nart.nart_proid 
                  WHERE n.n_estatus < 3  $peticion 
                  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();
        } else {
            return $this->consultar("SELECT n.n_divid, DATE_FORMAT(n.n_fecha, '%m') AS mes,DATE_FORMAT(n.n_fecha, '%Y') AS anio, nart_procantidad AS cantidad, (nart.nart_procantidad*nart.nart_procosto) as total_costo, nart_prototal as total_venta, su.*
                  FROM app_orden_de_entrega_articulos nart 
                  JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid 
                  JOIN app_sucursales su ON su.su_id=n.n_surcid 
                  JOIN app_productos p ON p.pro_id = nart.nart_proid 
                  WHERE YEAR(n.n_fecha) = '$anio' AND n.n_estatus < 3  $peticion 
                  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();
        }
    }


    //TAB MES COMPRAS

    public function obtenerProduccionNEVMesCompras($anio, $divisa, $sucursal, $proid)
    {
        $sucursal = ($sucursal > 0) ? " AND ne.ne_surcid=$sucursal" : "";

        return $this->consultar("SELECT ne.ne_divid, DATE_FORMAT(ne.ne_fecha, '%m') AS mes, SUM(neart_procantidad) AS cantidad, p.pro_costo * SUM(neart.neart_procantidad) as total_costo, SUM(neart_prototal) as total_venta,neart.neart_proid as producto,su.* 
         FROM app_notas_de_entrega_articulos neart 
         LEFT JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
         LEFT JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
         LEFT JOIN app_productos p ON p.pro_id = neart.neart_proid 
         WHERE neart.neart_proid=$proid AND $sucursal AND ne.ne_estatus < 3 AND YEAR(ne.ne_fecha) = '$anio' AND ne.ne_divid = $divisa
         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 MONTH(ne.ne_fecha)")->all();
    }

    public function obtenerProduccionFACVMesCompras($anio, $divisa, $sucursal, $proid)
    {
        $sucursal = ($sucursal > 0) ? " AND fm.fm_surcid=$sucursal" : "";

        return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%m') AS mes, SUM(fa.fa_procantidad) AS cantidad,  p.pro_costo * SUM(fa.fa_procantidad) as total_costo, SUM(fa.fa_prototal) as total_venta,fa.fa_proid as producto, su.*
         FROM app_factura_articulos fa
         LEFT JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
         LEFT JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
         LEFT JOIN app_productos p ON p.pro_id = fa.fa_proid 
         WHERE fa.fa_proid=$proid $sucursal AND fm.fm_estatus < 3 AND YEAR(fm.fm_fecha) = '$anio' AND fm.fm_divid = $divisa
         GROUP BY MONTH(fm.fm_fecha)")->all();
    }

    public function obtenerProduccionFACTURASVMesCompras($anio,$mes, $divisa, $sucursal, $proid)
    {
        $sucursal = ($sucursal > 0) ? " AND fm.fm_surcid=$sucursal" : "";

        return $this->consultar("SELECT fm.fm_divid, DATE_FORMAT(fm.fm_fecha, '%m-%Y') AS mes, SUM(fa.fa_procantidad) AS cantidad,  p.pro_costo * SUM(fa.fa_procantidad) as total_costo, SUM(fa.fa_prototal) as total_venta,fa.fa_proid as producto, su.*
         FROM app_factura_articulos fa
         LEFT JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
         LEFT JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
         LEFT JOIN app_productos p ON p.pro_id = fa.fa_proid 
         WHERE fa.fa_proid=$proid $sucursal AND fm.fm_estatus < 3 AND (YEAR(fm.fm_fecha) = '$anio') AND (fm.fm_divid = $divisa)
         GROUP BY MONTH(fm.fm_fecha)")->all();
    }

    public function obtenerProduccionORDENMesCompras($anio, $divisa, $sucursal, $proid)
    {
        $sucursal = ($sucursal > 0) ? " AND n.n_surcid=$sucursal" : "";

        return $this->consultar("SELECT n.n_divid, DATE_FORMAT(n.n_fecha, '%m') AS mes, SUM(nart_procantidad) AS cantidad, p.pro_costo * SUM(nart.nart_procantidad) as total_costo, SUM(nart_prototal) as total_venta,nart.nart_proid as producto, su.*
         FROM app_orden_de_entrega_articulos nart 
         LEFT JOIN app_orden_de_entrega n ON n.n_id = nart.nart_nid 
         LEFT JOIN app_sucursales su ON su.su_id=n.n_surcid 
         LEFT JOIN app_productos p ON p.pro_id = nart.nart_proid 
         WHERE nart.nart_proid=$proid $sucursal AND n.n_estatus < 3 AND YEAR(n.n_fecha) = '$anio' AND n.n_divid = $divisa
         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 MONTH(n.n_fecha)")->all();
    }

    public function obtenerFacturasCompras($prod, $anio)
    {
        return $this->consultar("SELECT cmpart.*,cmp.*,p.*,pr.*, DATE_FORMAT(cmp.cmp_fecha, '%m') AS mes,cmp.cmp_id as codigo 
        FROM app_compras_articulos cmpart 
        JOIN app_compras cmp ON cmp.cmp_id = cmpart.cmpart_cmpid
        LEFT JOIN app_series s ON s.ser_id = cmp.cmp_serid
        LEFT JOIN app_divisas d ON d.div_id = cmp.cmp_divid 
        LEFT JOIN app_proveedores p ON p.prov_id = cmp.cmp_cliid 
        LEFT JOIN app_productos pr ON pr.pro_id = cmpart.cmpart_proid 
        WHERE cmp.cmp_estatus = 1 AND cmpart.cmpart_proid=$prod AND YEAR(cmp.cmp_fecha) = '$anio' ORDER BY MONTH(cmp.cmp_fecha) ASC")->all();
    }
    public function obtenerFacturasComprasTodas($prod)
    {
        return $this->consultar("SELECT cmpart.*,cmp.*,p.*,pr.*, DATE_FORMAT(cmp.cmp_fecha, '%m') AS mes,cmp.cmp_id as codigo 
        FROM app_compras_articulos cmpart 
        JOIN app_compras cmp ON cmp.cmp_id = cmpart.cmpart_cmpid
        LEFT JOIN app_series s ON s.ser_id = cmp.cmp_serid
        LEFT JOIN app_divisas d ON d.div_id = cmp.cmp_divid 
        LEFT JOIN app_proveedores p ON p.prov_id = cmp.cmp_cliid 
        LEFT JOIN app_productos pr ON pr.pro_id = cmpart.cmpart_proid 
        WHERE cmp.cmp_estatus = 1 AND cmpart.cmpart_proid=$prod ORDER BY MONTH(cmp.cmp_fecha) ASC")->all();
    }
    //GENERAL

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

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

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

    public function obtenerCierreDelDia(int $retiro, string $fecha, int $sucursal)
    {
        if ($sucursal == 0) {
            return $this->consultar("SELECT fa.fab_empid, aei.einfo_nombres, aei.einfo_apellidos, SUM(fa.fab_monto) AS monto, fab_metodo, fa.fab_fecha, b.ban_codigo, b.ban_descripcion, bc.bc_cuenta, bc.bc_alias, bc.bc_tipo, m.mp_nombre, d.*, su.* 
            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_empleados ae ON ae.emp_id = fa.fab_empid
            JOIN app_empleados_info aei ON aei.einfo_id = ae.emp_edatosid 
            WHERE fa.fab_retiro = $retiro AND DATE(fa.fab_fecha) = '$fecha'
            GROUP BY bc.bc_id, mp_id, d.div_id ORDER BY bc_id")->all();
        } else {
            return $this->consultar("SELECT fa.fab_empid, aei.einfo_nombres, aei.einfo_apellidos, SUM(fa.fab_monto) AS monto, fab_metodo, fa.fab_fecha, b.ban_codigo, b.ban_descripcion, bc.bc_cuenta, bc.bc_alias, bc.bc_tipo, m.mp_nombre, d.*, su.* 
            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_empleados ae ON ae.emp_id = fa.fab_empid
            JOIN app_empleados_info aei ON aei.einfo_id = ae.emp_edatosid 
            WHERE fa.fab_sucursal = $sucursal AND fa.fab_retiro = $retiro AND DATE(fa.fab_fecha) = '$fecha'
            GROUP BY bc.bc_id, mp_id, d.div_id ORDER BY bc_id")->all();
        }
    }

    public function obtenerCierreDelDiaNega(int $retiro, string $fecha, int $sucursal)
    {
        if ($sucursal == 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_empleados ae ON ae.emp_id = fa.fab_empid
            JOIN app_empleados_info aei ON aei.einfo_id = ae.emp_edatosid 
            WHERE fa.fab_retiro = $retiro AND DATE(fa.fab_fecha) = '$fecha'
            GROUP BY bc.bc_id, mp_id, d.div_id ORDER BY bc_id")->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_empleados ae ON ae.emp_id = fa.fab_empid
            JOIN app_empleados_info aei ON aei.einfo_id = ae.emp_edatosid 
            WHERE fa.fab_sucursal = $sucursal AND fa.fab_retiro = $retiro AND DATE(fa.fab_fecha) = '$fecha'
            GROUP BY bc.bc_id, mp_id, d.div_id ORDER BY bc_id")->all();
        }
    }

    public function obtenerCierreMes($retiro, $anio, $categeoria)
    {
        if ($anio == 0) {
            return $this->consultar("SELECT m.fab_monto AS monto, DATE_FORMAT(m.fab_fecha, '%m') AS mes, m.fab_divisa as div_id,m.fab_factor_master    
            FROM app_movimientos m 
            WHERE m.fab_credito = 0 AND m.fab_retiro = $retiro AND m.fab_categoria= $categeoria")->all();
        } else {
            return $this->consultar("SELECT m.fab_monto AS monto, DATE_FORMAT(m.fab_fecha, '%m') AS mes, m.fab_divisa as div_id,m.fab_factor_master    
            FROM app_movimientos m 
            WHERE m.fab_credito = 0 AND m.fab_retiro = $retiro AND YEAR(m.fab_fecha) = '$anio' AND m.fab_categoria= $categeoria")->all();
        }
    }

    public function obtenerCierreMesSucursal($retiro, $anio, $categeoria, $sucursal, $divis)
    {
        $peticion = ($divis > 0) ? " AND m.fab_divisa=$divis" : "";

        if ($sucursal == 0) {
            if ($anio == 0) {
                return $this->consultar("SELECT m.fab_monto AS monto, DATE_FORMAT(m.fab_fecha, '%m') AS mes, m.fab_divisa as div_id,m.fab_factor_master    
                FROM app_movimientos m 
                WHERE m.fab_credito = 0 AND m.fab_retiro = $retiro AND m.fab_categoria= $categeoria $peticion")->all();
            } else {
                return $this->consultar("SELECT m.fab_monto AS monto, DATE_FORMAT(m.fab_fecha, '%m') AS mes, m.fab_divisa as div_id,m.fab_factor_master    
                FROM app_movimientos m 
                WHERE m.fab_credito = 0 AND m.fab_retiro = $retiro AND YEAR(m.fab_fecha) = '$anio' AND m.fab_categoria= $categeoria $peticion")->all();
            }
        } else {
            if ($anio == 0) {
                return $this->consultar("SELECT m.fab_monto AS monto, DATE_FORMAT(m.fab_fecha, '%m') AS mes, m.fab_divisa as div_id,m.fab_factor_master    
            FROM app_movimientos m 
            WHERE m.fab_credito = 0 AND m.fab_retiro = $retiro AND m.fab_categoria= $categeoria AND m.fab_sucursal=$sucursal $peticion")->all();
            } else {
                return $this->consultar("SELECT m.fab_monto AS monto, DATE_FORMAT(m.fab_fecha, '%m') AS mes, m.fab_divisa as div_id,m.fab_factor_master    
            FROM app_movimientos m 
            WHERE m.fab_credito = 0 AND m.fab_retiro = $retiro AND YEAR(m.fab_fecha) = '$anio' AND m.fab_categoria= $categeoria AND m.fab_sucursal=$sucursal $peticion")->all();
            }
        }
    }


    public function obtenerCierreMovimientocompletoNot(string $fecha, int $sucursal)
    {
        if ($sucursal > 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_empleados ae ON ae.emp_id = fa.fab_empid
            JOIN app_empleados_info aei ON aei.einfo_id = ae.emp_edatosid 
            JOIN app_notas_de_entrega n ON n.ne_id=fa.fab_idgenerico 
            WHERE fa.fab_doc=1 AND fa.fab_retiro=0 AND fa.fab_sucursal = $sucursal AND (DATE(fa.fab_fecha) BETWEEN '$fecha' AND '$fecha')")->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_empleados ae ON ae.emp_id = fa.fab_empid
            JOIN app_empleados_info aei ON aei.einfo_id = ae.emp_edatosid 
            JOIN app_notas_de_entrega n ON n.ne_id=fa.fab_idgenerico  
            WHERE fa.fab_doc=1 AND fa.fab_retiro=0 AND (DATE(fa.fab_fecha) BETWEEN '$fecha' AND '$fecha')")->all();
        }
    }

    public function obtenerCierreMovimientocompletoFact(string $fecha, int $sucursal)
    {
        if ($sucursal > 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_empleados ae ON ae.emp_id = fa.fab_empid
            JOIN app_empleados_info aei ON aei.einfo_id = ae.emp_edatosid 
            JOIN app_factura_master fm ON fm.fm_id=fa.fab_idgenerico 
            WHERE fa.fab_doc=2 AND fa.fab_retiro=0 AND fa.fab_sucursal = $sucursal AND (DATE(fa.fab_fecha) BETWEEN '$fecha' AND '$fecha')")->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_empleados ae ON ae.emp_id = fa.fab_empid
            JOIN app_empleados_info aei ON aei.einfo_id = ae.emp_edatosid   
            JOIN app_factura_master fm ON fm.fm_id=fa.fab_idgenerico 
            WHERE fa.fab_doc=2 AND fa.fab_retiro=0 AND (DATE(fa.fab_fecha) BETWEEN '$fecha' AND '$fecha')")->all();
        }
    }

    public function obtenerCierreMovimientocompletoOrd(string $fecha, int $sucursal)
    {
        if ($sucursal > 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_empleados ae ON ae.emp_id = fa.fab_empid
            JOIN app_empleados_info aei ON aei.einfo_id = ae.emp_edatosid 
            JOIN app_orden_de_entrega n ON n.n_id=fa.fab_idgenerico 
            WHERE  fa.fab_doc=12 AND fa.fab_retiro=0 AND fa.fab_sucursal = $sucursal AND (DATE(fa.fab_fecha) BETWEEN '$fecha' AND '$fecha')")->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_empleados ae ON ae.emp_id = fa.fab_empid
            JOIN app_empleados_info aei ON aei.einfo_id = ae.emp_edatosid 
            JOIN app_orden_de_entrega n ON n.n_id=fa.fab_idgenerico 
            WHERE  fa.fab_doc=12 AND fa.fab_retiro=0 AND (DATE(fa.fab_fecha) BETWEEN '$fecha' AND '$fecha')")->all();
        }
    }

    public function obtenerDocumento(int $id)
    {
        return $this->consultar("SELECT * FROM app_documentos WHERE doc_id=$id")->row();
    }

    public function obtenerFacturasVentasDelDia(int $id_divisa, string $fecha_desde, string $fecha_hasta, int $sucursal)
    {
        if ($id_divisa == 0) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT fm.fm_id id, fm.fm_correlativo correlativo, fm.fm_fecha fecha, fm_articulos_total art_total, fm.fm_total total, d.*, c.cli_razon_social cliente,su.* 
                FROM app_factura_master fm 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_divisas d ON d.div_id = fm.fm_divid
                JOIN app_clientes c ON c.cli_id = fm.fm_cliid 
                WHERE (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY fm_id DESC")->all();
            } else {
                return $this->consultar("SELECT fm.fm_id id, fm.fm_correlativo correlativo, fm.fm_fecha fecha, fm_articulos_total art_total, fm.fm_total total, d.*, c.cli_razon_social cliente,su.* 
                FROM app_factura_master fm 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_divisas d ON d.div_id = fm.fm_divid
                JOIN app_clientes c ON c.cli_id = fm.fm_cliid 
                WHERE fm.fm_surcid=$sucursal AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY fm_id DESC")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT fm.fm_id id, fm.fm_correlativo correlativo, fm.fm_fecha fecha, fm_articulos_total art_total, fm.fm_total total, d.*, c.cli_razon_social cliente,su.* 
                FROM app_factura_master fm 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_divisas d ON d.div_id = fm.fm_divid
                JOIN app_clientes c ON c.cli_id = fm.fm_cliid 
                WHERE (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND fm.fm_divid = $id_divisa ORDER BY fm_id DESC")->all();
            } else {
                return $this->consultar("SELECT fm.fm_id id, fm.fm_correlativo correlativo, fm.fm_fecha fecha, fm_articulos_total art_total, fm.fm_total total, d.*, c.cli_razon_social cliente,su.* 
                FROM app_factura_master fm 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_divisas d ON d.div_id = fm.fm_divid
                JOIN app_clientes c ON c.cli_id = fm.fm_cliid 
                WHERE fm.fm_surcid=$sucursal AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND fm.fm_divid = $id_divisa ORDER BY fm_id DESC")->all();
            }
        }
    }

    public function obtenerNotasVentasDelDia(int $id_divisa, string $fecha_desde, string $fecha_hasta, int $sucursal)
    {
        if ($id_divisa == 0) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT ne.ne_id id, ne.ne_correlativo correlativo, ne.ne_fecha fecha, ne_articulos_total art_total, ne.ne_total total, d.*, c.cli_razon_social cliente, su.* 
        FROM app_notas_de_entrega ne 
        JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
        JOIN app_divisas d ON d.div_id = ne.ne_divid
        JOIN app_clientes c ON c.cli_id = ne.ne_cliid 
        WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY ne_id DESC")->all();
            } else {
                return $this->consultar("SELECT ne.ne_id id, ne.ne_correlativo correlativo, ne.ne_fecha fecha, ne_articulos_total art_total, ne.ne_total total, d.*, c.cli_razon_social cliente, su.* 
                FROM app_notas_de_entrega ne 
                JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                JOIN app_divisas d ON d.div_id = ne.ne_divid
                JOIN app_clientes c ON c.cli_id = ne.ne_cliid 
                WHERE ne.ne_surcid=$sucursal AND (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY ne_id DESC")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT ne.ne_id id, ne.ne_correlativo correlativo, ne.ne_fecha fecha, ne_articulos_total art_total, ne.ne_total total, d.*, c.cli_razon_social cliente, su.* 
        FROM app_notas_de_entrega ne 
        JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
        JOIN app_divisas d ON d.div_id = ne.ne_divid
        JOIN app_clientes c ON c.cli_id = ne.ne_cliid 
        WHERE (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_divid = $id_divisa ORDER BY ne_id DESC")->all();
            } else {
                return $this->consultar("SELECT ne.ne_id id, ne.ne_correlativo correlativo, ne.ne_fecha fecha, ne_articulos_total art_total, ne.ne_total total, d.*, c.cli_razon_social cliente, su.* 
                FROM app_notas_de_entrega ne 
                JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
                JOIN app_divisas d ON d.div_id = ne.ne_divid
                JOIN app_clientes c ON c.cli_id = ne.ne_cliid 
                WHERE ne.ne_surcid=$sucursal AND (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND ne.ne_divid = $id_divisa ORDER BY ne_id DESC")->all();
            }
        }
    }

    public function obtenerOrdenesVentasDelDia(int $id_divisa, string $fecha_desde, string $fecha_hasta, int $sucursal)
    {
        if ($id_divisa == 0) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT n.n_id id, n.n_correlativo correlativo, n.n_fecha fecha, n_articulos_total art_total, n.n_total total, d.*, c.cli_razon_social cliente,su.* 
        FROM app_orden_de_entrega n
        JOIN app_sucursales su ON su.su_id=n.n_surcid 
        JOIN app_divisas d ON d.div_id = n.n_divid
        JOIN app_clientes c ON c.cli_id = n.n_cliid 
        WHERE (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY n_id DESC")->all();
            } else {
                return $this->consultar("SELECT n.n_id id, n.n_correlativo correlativo, n.n_fecha fecha, n_articulos_total art_total, n.n_total total, d.*, c.cli_razon_social cliente,su.* 
        FROM app_orden_de_entrega n
        JOIN app_sucursales su ON su.su_id=n.n_surcid 
        JOIN app_divisas d ON d.div_id = n.n_divid
        JOIN app_clientes c ON c.cli_id = n.n_cliid 
        WHERE n.n_surcid=$sucursal AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') ORDER BY n_id DESC")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT n.n_id id, n.n_correlativo correlativo, n.n_fecha fecha, n_articulos_total art_total, n.n_total total, d.*, c.cli_razon_social cliente,su.* 
        FROM app_orden_de_entrega n
        JOIN app_sucursales su ON su.su_id=n.n_surcid 
        JOIN app_divisas d ON d.div_id = n.n_divid
        JOIN app_clientes c ON c.cli_id = n.n_cliid 
        WHERE (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND n.n_divid = $id_divisa ORDER BY n_id DESC")->all();
            } else {
                return $this->consultar("SELECT n.n_id id, n.n_correlativo correlativo, n.n_fecha fecha, n_articulos_total art_total, n.n_total total, d.*, c.cli_razon_social cliente,su.* 
        FROM app_orden_de_entrega n
        JOIN app_sucursales su ON su.su_id=n.n_surcid 
        JOIN app_divisas d ON d.div_id = n.n_divid
        JOIN app_clientes c ON c.cli_id = n.n_cliid 
        WHERE n.n_surcid=$sucursal AND (DATE(n.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND n.n_divid = $id_divisa ORDER BY n_id DESC")->all();
            }
        }
    }

    public function obtenerNotFacttotalArt($id, $filtro)
    {
        if ($filtro == 1) {
            return $this->consultar("SELECT neart_neid,neart_proid 
            FROM app_notas_de_entrega_articulos WHERE neart_neid = $id")->all();
        }
        if ($filtro == 2) {
            return $this->consultar("SELECT fa_fmid,fa_proid FROM app_factura_articulos
        WHERE fa_fmid= $id")->all();
        }
        if ($filtro == 12) {
            return $this->consultar("SELECT nart_nid,nart_proid FROM app_orden_de_entrega_articulos
        WHERE nart_nid = $id")->all();
        }
    }

    public function obtenerDocumentosVentaDiaFactDivisa($desde, $hasta, $divisa, $filtro)
    {
        if ($filtro == 1) {
            return $this->consultar("SELECT  *, ne_divid divisa FROM app_notas_de_entrega ne  
        JOIN app_clientes c ON c.cli_id = ne.ne_cliid 
        JOIN app_divisas d ON d.div_id = ne.ne_divid 
        WHERE (DATE(ne.ne_fecha) BETWEEN '$desde' AND '$hasta') AND ne.ne_divid = $divisa
        ORDER BY ne.ne_fecha DESC")->all();
        }
        if ($filtro == 2) {
            return $this->consultar("SELECT *, fm_divid divisa FROM app_factura_master fm
        JOIN app_clientes c ON c.cli_id = fm.fm_cliid 
        JOIN app_divisas d ON d.div_id = fm.fm_divid 
        WHERE (DATE(fm.fm_fecha) BETWEEN '$desde' AND '$hasta') AND fm.fm_divid = $divisa ORDER BY fm.fm_fecha DESC")->all();
        }

        if ($filtro == 3) {
            return $this->consultar("SELECT *, pre_divid divisa FROM app_presupuesto pe
        JOIN app_clientes c ON c.cli_id = pe.pre_cliid 
        JOIN app_divisas d ON d.div_id = pe.pre_divid 
        WHERE (DATE(pe.pre_fecha) BETWEEN '$desde' AND '$hasta') AND pe.pre_divid = $divisa
        ORDER BY pe.pre_fecha DESC")->all();
        }

        if ($filtro == 4) {
            return $this->consultar("SELECT *, pe_divid divisa FROM app_pedidos pe
        JOIN app_clientes c ON c.cli_id = pe.pe_cliid 
        JOIN app_divisas d ON d.div_id = pe.pe_divid 
        WHERE (DATE(pe.pe_fecha) BETWEEN '$desde' AND '$hasta') AND pe.pe_divid = $divisa
        ORDER BY pe.pe_fecha DESC")->all();
        }

        if ($filtro == 12) {
            return $this->consultar("SELECT *, n_divid divisa FROM app_orden_de_entrega n
        JOIN app_clientes c ON c.cli_id = n.n_cliid 
        JOIN app_divisas d ON d.div_id = n.n_divid 
        WHERE (DATE(n.n_fecha) BETWEEN '$desde' AND '$hasta') AND n.n_divid = $divisa
        ORDER BY n.n_fecha DESC")->all();
        }
    }

    //Precio por producto
    public function obtenerArticulosDeFACVPREC($fecha_desde, $fecha_hasta, $divisa = null, $sucursal)
    {
        if ($divisa == 0) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT fm.fm_id AS id, fm.fm_divid AS divisa,fm.fm_neto neto, art.fa_proid as proid, art.fa_prototal AS total, art.fa_procantidad AS cantidad, art.fa_procosto AS costo, art.fa_proprecio AS precio, art.fa_prodescuento AS descuento, su.* 
                FROM app_factura_master fm 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid 
                WHERE fm_estatus < 3 
                AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta')")->all();
            } else {
                return $this->consultar("SELECT fm.fm_id AS id, fm.fm_divid AS divisa,fm.fm_neto neto, art.fa_proid as proid, art.fa_prototal AS total, art.fa_procantidad AS cantidad, art.fa_procosto AS costo, art.fa_proprecio AS precio, art.fa_prodescuento AS descuento, su.* 
                FROM app_factura_master fm 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid 
                WHERE fm.fm_surcid=$sucursal AND fm_estatus < 3 
                AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta')")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT fm.fm_id AS id, fm.fm_divid AS divisa,fm.fm_neto neto, art.fa_proid as proid, art.fa_prototal AS total, art.fa_procantidad AS cantidad, art.fa_procosto AS costo, art.fa_proprecio AS precio, art.fa_prodescuento AS descuento, su.* 
                FROM app_factura_master fm 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid AND fm.fm_divid = $divisa
                WHERE fm_estatus < 3 
                AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta')")->all();
            } else {
                return $this->consultar("SELECT fm.fm_id AS id, fm.fm_divid AS divisa,fm.fm_neto neto, art.fa_proid as proid, art.fa_prototal AS total, art.fa_procantidad AS cantidad, art.fa_procosto AS costo, art.fa_proprecio AS precio, art.fa_prodescuento AS descuento, su.* 
                FROM app_factura_master fm 
                JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
                JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid AND fm.fm_divid = $divisa
                WHERE fm.fm_surcid=$sucursal AND fm_estatus < 3 
                AND (DATE(fm.fm_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta')")->all();
            }
        }
    }

    public function obtenerArticulosDeNEVPREC($fecha_desde, $fecha_hasta, $divisa = null, $sucursal)
    {
        if ($divisa == 0) {
            if ($sucursal == 0) {
                return $this->consultar("SELECT ne.ne_id AS id, ne.ne_divid as divisa,ne.ne_neto neto, neart.neart_proid as proid, neart.neart_prototal AS total, neart.neart_procantidad AS cantidad, neart.neart_procosto AS costo, neart.neart_proprecio AS precio, neart.neart_prodescuento AS descuento, su.* 
             FROM app_notas_de_entrega ne 
             JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
             JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid 
             WHERE ne_estatus < 3 AND (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta')")->all();
            } else {
                return $this->consultar("SELECT ne.ne_id AS id, ne.ne_divid as divisa,ne.ne_neto neto, neart.neart_proid as proid, neart.neart_prototal AS total, neart.neart_procantidad AS cantidad, neart.neart_procosto AS costo, neart.neart_proprecio AS precio, neart.neart_prodescuento AS descuento, su.* 
             FROM app_notas_de_entrega ne 
             JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
             JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid 
             WHERE ne.ne_surcid=$sucursal AND ne_estatus < 3 AND (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta')")->all();
            }
        } else {
            if ($sucursal == 0) {
                return $this->consultar("SELECT ne.ne_id AS id, ne.ne_divid as divisa,ne.ne_neto neto, neart.neart_proid as proid, neart.neart_prototal AS total, neart.neart_procantidad AS cantidad, neart.neart_procosto AS costo, neart.neart_proprecio AS precio, neart.neart_prodescuento AS descuento, su.* 
             FROM app_notas_de_entrega ne 
             JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
             JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid AND ne.ne_divid = $divisa
             WHERE ne_estatus < 3 AND (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta')")->all();
            } else {
                return $this->consultar("SELECT ne.ne_id AS id, ne.ne_divid as divisa,ne_neto neto, neart.neart_proid as proid, neart.neart_prototal AS total, neart.neart_procantidad AS cantidad, neart.neart_procosto AS costo, neart.neart_proprecio AS precio, neart.neart_prodescuento AS descuento, su.* 
             FROM app_notas_de_entrega ne 
             JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
             JOIN app_notas_de_entrega_articulos neart ON ne.ne_id = neart.neart_neid AND ne.ne_divid = $divisa
             WHERE ne.ne_surcid=$sucursal AND ne_estatus < 3 AND (DATE(ne.ne_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta')")->all();
            }
        }
    }

    public function obtenerProductosid($id)
    {
        return $this->consultar("SELECT *
        FROM app_productos WHERE pro_id = $id")->row();
    }

    //comisiones pago
    public function obtenerComisionesFacturasPendientes($desde, $hasta, $divisa, $comision, $agente, $documento)
    {
        if ($comision == 0 && $agente == 0 && $documento == 0) {

            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_factura_master fm ON fn.comagentefn_fnid=fm.fm_id
            JOIN app_clientes cli ON fm.fm_cliid=cli.cli_id
            JOIN app_divisas dv ON fm.fm_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=2 AND fn.comagentefn_estatus=0 AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision == 0 && $agente > 0 && $documento == 0) {

            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_factura_master fm ON fn.comagentefn_fnid=fm.fm_id
            JOIN app_clientes cli ON fm.fm_cliid=cli.cli_id
            JOIN app_divisas dv ON fm.fm_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=2 AND fn.comagentefn_estatus=0 AND fn.comagentefn_divid=$divisa AND fn.comagentefn_fnempagente=$agente AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision > 0 && $agente == 0 && $documento == 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_factura_master fm ON fn.comagentefn_fnid=fm.fm_id
            JOIN app_clientes cli ON fm.fm_cliid=cli.cli_id
            JOIN app_divisas dv ON fm.fm_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=2 AND fn.comagentefn_estatus=0 AND fn.comagentefn_com=$comision AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision > 0 && $agente  > 0 && $documento == 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_factura_master fm ON fn.comagentefn_fnid=fm.fm_id
            JOIN app_clientes cli ON fm.fm_cliid=cli.cli_id
            JOIN app_divisas dv ON fm.fm_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=2 AND fn.comagentefn_estatus=0 AND fn.comagentefn_com=$comision AND fn.comagentefn_fnempagente=$agente AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision > 0 && $agente  > 0 && $documento > 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_factura_master fm ON fn.comagentefn_fnid=fm.fm_id
            JOIN app_clientes cli ON fm.fm_cliid=cli.cli_id
            JOIN app_divisas dv ON fm.fm_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=2 AND fm.fm_estatus=$documento AND fn.comagentefn_estatus=0 AND fn.comagentefn_com=$comision AND fn.comagentefn_fnempagente=$agente AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision == 0 && $agente == 0 && $documento > 0) {

            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_factura_master fm ON fn.comagentefn_fnid=fm.fm_id
            JOIN app_clientes cli ON fm.fm_cliid=cli.cli_id
            JOIN app_divisas dv ON fm.fm_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=2 AND fm.fm_estatus=$documento AND fn.comagentefn_estatus=0 AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision == 0 && $agente > 0 && $documento > 0) {

            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_factura_master fm ON fn.comagentefn_fnid=fm.fm_id
            JOIN app_clientes cli ON fm.fm_cliid=cli.cli_id
            JOIN app_divisas dv ON fm.fm_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=2 AND fm.fm_estatus=$documento AND fn.comagentefn_estatus=0 AND fn.comagentefn_divid=$divisa AND fn.comagentefn_fnempagente=$agente AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision > 0 && $agente == 0 && $documento > 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_factura_master fm ON fn.comagentefn_fnid=fm.fm_id
            JOIN app_clientes cli ON fm.fm_cliid=cli.cli_id
            JOIN app_divisas dv ON fm.fm_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=2 AND fm.fm_estatus=$documento AND fn.comagentefn_estatus=0 AND fn.comagentefn_com=$comision AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
    }
    public function obtenerComisionesNotasPendientes($desde, $hasta, $divisa, $comision, $agente, $documento)
    {
        if ($comision == 0 && $agente == 0 && $documento == 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_notas_de_entrega nt ON fn.comagentefn_fnid=nt.ne_id 
            JOIN app_clientes cli ON nt.ne_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.ne_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=1 AND fn.comagentefn_estatus=0 AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision == 0 && $agente > 0 && $documento == 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_notas_de_entrega nt ON fn.comagentefn_fnid=nt.ne_id 
            JOIN app_clientes cli ON nt.ne_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.ne_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=1 AND fn.comagentefn_estatus=0 AND fn.comagentefn_fnempagente=$agente AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision > 0 && $agente == 0 && $documento == 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_notas_de_entrega nt ON fn.comagentefn_fnid=nt.ne_id 
            JOIN app_clientes cli ON nt.ne_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.ne_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=1 AND fn.comagentefn_estatus=0 AND fn.comagentefn_com=$comision AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision > 0 && $agente > 0 && $documento == 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_notas_de_entrega nt ON fn.comagentefn_fnid=nt.ne_id 
            JOIN app_clientes cli ON nt.ne_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.ne_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=1 AND fn.comagentefn_estatus=0 AND fn.comagentefn_com=$comision AND fn.comagentefn_fnempagente=$agente AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision == 0 && $agente == 0 && $documento > 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_notas_de_entrega nt ON fn.comagentefn_fnid=nt.ne_id 
            JOIN app_clientes cli ON nt.ne_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.ne_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=1 AND nt.ne_estatus=$documento AND fn.comagentefn_estatus=0 AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision == 0 && $agente > 0 && $documento > 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_notas_de_entrega nt ON fn.comagentefn_fnid=nt.ne_id 
            JOIN app_clientes cli ON nt.ne_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.ne_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=1 AND nt.ne_estatus=$documento AND fn.comagentefn_estatus=0 AND fn.comagentefn_fnempagente=$agente AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision > 0 && $agente == 0 && $documento > 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_notas_de_entrega nt ON fn.comagentefn_fnid=nt.ne_id 
            JOIN app_clientes cli ON nt.ne_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.ne_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=1 AND nt.ne_estatus=$documento AND fn.comagentefn_estatus=0 AND fn.comagentefn_com=$comision AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision > 0 && $agente > 0 && $documento > 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_notas_de_entrega nt ON fn.comagentefn_fnid=nt.ne_id 
            JOIN app_clientes cli ON nt.ne_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.ne_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=1 AND nt.ne_estatus=$documento AND fn.comagentefn_estatus=0 AND fn.comagentefn_com=$comision AND fn.comagentefn_fnempagente=$agente AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
    }
    public function obtenerComisionesOrdenPendientes($desde, $hasta, $divisa, $comision, $agente, $documento)
    {
        if ($comision == 0 && $agente == 0 && $documento == 0) {

            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_orden_de_entrega nt ON fn.comagentefn_fnid=nt.n_id 
            JOIN app_clientes cli ON nt.n_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.n_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=12 AND fn.comagentefn_estatus=0 AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision == 0 && $agente > 0 && $documento == 0) {

            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_orden_de_entrega nt ON fn.comagentefn_fnid=nt.n_id 
            JOIN app_clientes cli ON nt.n_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.n_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=12 AND fn.comagentefn_estatus=0 AND fn.comagentefn_divid=$divisa AND fn.comagentefn_fnempagente=$agente AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision > 0 && $agente == 0 && $documento == 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_orden_de_entrega nt ON fn.comagentefn_fnid=nt.n_id 
            JOIN app_clientes cli ON nt.n_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.n_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=12 AND fn.comagentefn_estatus=0 AND fn.comagentefn_com=$comision AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision > 0 && $agente > 0 && $documento == 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_orden_de_entrega nt ON fn.comagentefn_fnid=nt.n_id 
            JOIN app_clientes cli ON nt.n_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.n_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=12 AND fn.comagentefn_estatus=0 AND fn.comagentefn_com=$comision AND fn.comagentefn_fnempagente=$agente AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision == 0 && $agente == 0 && $documento > 0) {

            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_orden_de_entrega nt ON fn.comagentefn_fnid=nt.n_id 
            JOIN app_clientes cli ON nt.n_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.n_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=12 AND nt.n_estatus=$documento AND fn.comagentefn_estatus=0 AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision == 0 && $agente > 0 && $documento > 0) {

            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_orden_de_entrega nt ON fn.comagentefn_fnid=nt.n_id 
            JOIN app_clientes cli ON nt.n_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.n_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=12 AND nt.n_estatus=$documento AND fn.comagentefn_estatus=0 AND fn.comagentefn_divid=$divisa AND fn.comagentefn_fnempagente=$agente AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision > 0 && $agente == 0 && $documento > 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_orden_de_entrega nt ON fn.comagentefn_fnid=nt.n_id 
            JOIN app_clientes cli ON nt.n_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.n_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=12 AND nt.n_estatus=$documento AND fn.comagentefn_estatus=0 AND fn.comagentefn_com=$comision AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
        if ($comision > 0 && $agente > 0 && $documento > 0) {
            return $this->consultar("SELECT * 
            FROM app_comisiones_agente_fn fn
            JOIN app_orden_de_entrega nt ON fn.comagentefn_fnid=nt.n_id 
            JOIN app_clientes cli ON nt.n_cliid=cli.cli_id
            JOIN app_divisas dv ON nt.n_divid=dv.div_id
            JOIN app_comision c ON fn.comagentefn_com=c.com_id
            JOIN app_empleados_info ep ON fn.comagentefn_fnempagente=ep.einfo_id
        WHERE fn.comagentefn_fntipo=12 AND nt.n_estatus=$documento AND fn.comagentefn_estatus=0 AND fn.comagentefn_com=$comision AND fn.comagentefn_fnempagente=$agente AND fn.comagentefn_divid=$divisa AND (DATE(fn.comagentefn_fecha) BETWEEN '$desde' AND '$hasta')")->all();
        }
    }

    //articulos vendidos cliente
    public function obtenerArticulosDeFACVCliente($peticion)
    {
        return $this->consultar("SELECT * 
            FROM app_factura_master fm
            JOIN app_factura_articulos art ON fm.fm_id = art.fa_fmid
            WHERE $peticion")->all();
    }

    public function obtenerArticulosDeNEVCliente($peticion)
    {
        return $this->consultar("SELECT * 
            FROM app_notas_de_entrega ne
            JOIN app_notas_de_entrega_articulos art ON ne.ne_id = art.neart_neid
            WHERE $peticion")->all();
    }

    public function obtenerArticulosDeOrdCliente($peticion)
    {
        return $this->consultar("SELECT * 
            FROM app_orden_de_entrega n
            JOIN app_orden_de_entrega_articulos art ON n.n_id = art.nart_nid
            WHERE $peticion")->all();
    }
}
