<?php

namespace App\Modelos;

use Core\Database;

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

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

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

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

    public function obtenerFacturasAnuales($peticion)
    {
        return $this->consultar("SELECT fm.fm_id, fm.fm_correlativo, fm.fm_fecha, fm.fm_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_factura_master fm 
        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_estatus=1 AND $peticion ORDER BY fm_fecha DESC")->all();
    }

    public function obtenerSucursales($id_empleado)
    {
        return $this->consultar("SELECT * FROM app_empleados_sucursales s 
        JOIN app_sucursales su ON s.sucursal_id = su.su_id
        WHERE empleado_id=$id_empleado")->all();
    }

    public function obtenerFacturasxempleadoshoy($anio)
    {
        return $this->consultar("SELECT fm.fm_id,fm.fm_empleadoid, fm.fm_correlativo, fm.fm_fecha, fm.fm_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_factura_master fm 
        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_estatus=1 AND  DATE(fm.fm_fecha)=CURRENT_DATE()  and fm.fm_empleadoid='{$_SESSION['user_data']['einfo_id']}'  ORDER BY fm.fm_fecha DESC")->all();
    }

    public function obtenerFacturasAnualesPendientes($peticion)
    {
        return $this->consultar("SELECT fm.fm_id, fm.fm_correlativo, fm.fm_fecha, fm.fm_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM  app_factura_master fm 
        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_estatus=0 OR fm.fm_estatus=2) AND $peticion ORDER BY fm_fecha DESC")->all();
    }

    public function obtenerFacturasPendientesxempleadoshoy($anio)
    {
        return $this->consultar("SELECT fm.fm_id, fm.fm_empleadoid, fm.fm_correlativo, fm.fm_fecha, fm.fm_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM  app_factura_master fm 
        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_estatus=0 OR fm.fm_estatus=2) AND DATE(fm.fm_fecha)=CURRENT_DATE()  and fm.fm_empleadoid='{$_SESSION['user_data']['einfo_id']}'  ORDER BY fm.fm_fecha DESC")->all();
    }

    public function obtenerFacturasAnualesAnuladas($peticion)
    {
        return $this->consultar("SELECT fm.fm_id, fm.fm_correlativo, fm.fm_fecha, fm.fm_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM  app_factura_master fm 
        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_estatus=3 AND $peticion ORDER BY fm_fecha DESC")->all();
    }

    public function obtenerFacturasAnuladasxempleadoshoy($anio)
    {
        return $this->consultar("SELECT fm.fm_id,fm.fm_empleadoid, fm.fm_correlativo, fm.fm_fecha, fm.fm_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM  app_factura_master fm 
        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_estatus=3 AND  DATE(fm.fm_fecha)=CURRENT_DATE()  and fm.fm_empleadoid='{$_SESSION['user_data']['einfo_id']}' ORDER BY fm.fm_fecha DESC")->all();
    }

    public function obtenerNotasdeEntregaAnuales($peticion)
    {
        return $this->consultar("SELECT ne.ne_id, ne.ne_correlativo, ne.ne_fecha, ne.ne_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_notas_de_entrega ne 
        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_estatus=1 AND $peticion ORDER BY ne_fecha DESC")->all();
    }

    public function obtenerNotasdeEntregaxempleadoHoy($anio)
    {
        return $this->consultar("SELECT ne.ne_id, ne.ne_correlativo, ne.ne_fecha, ne.ne_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_notas_de_entrega ne 
        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_estatus=1 and DATE(ne.ne_fecha)=CURRENT_DATE()  and ne_empleadoid='{$_SESSION['user_data']['einfo_id']}' ORDER BY ne.ne_fecha DESC")->all();
    }

    public function obtenerNotasdeEntregaAnualesPendientesTotal()
    {
        $date = date('Y');
        return $this->consultar("SELECT * FROM app_notas_de_entrega WHERE ne_estatus=0 OR ne_estatus=2 AND YEAR(ne_fecha)=$date   ORDER BY ne_fecha DESC")->count();
    }

    public function obtenerFacturasPendientesTotal($sucursal)
    {
        $date = date('Y');
        if ($sucursal == 0) {
            return $this->consultar("SELECT * FROM app_factura_master WHERE fm_estatus=0 OR fm_estatus=2 AND YEAR(fm_fecha)=$date")->count();
        } else {
            return $this->consultar("SELECT * FROM app_factura_master WHERE fm_estatus=0 OR fm_estatus=2 AND fm_surcid=$sucursal AND YEAR(fm_fecha)=$date")->count();
        }
    }

    public function obtenerNotasdeEntregaAnualesPendientes($peticion)
    {
        return $this->consultar("SELECT ne.ne_id, ne.ne_correlativo, ne.ne_fecha, ne.ne_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_notas_de_entrega ne 
        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_estatus=0 OR ne.ne_estatus=2) AND $peticion ORDER BY ne.ne_fecha DESC")->all();
    }

    public function obtenerNotasdeEntregaPendientesxempleadoHoy($anio)
    {
        return $this->consultar("SELECT ne.ne_id, ne.ne_correlativo, ne.ne_fecha, ne.ne_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_notas_de_entrega ne 
        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_estatus=0 OR ne.ne_estatus=2)  and DATE(ne.ne_fecha)=CURRENT_DATE()  and ne_empleadoid='{$_SESSION['user_data']['einfo_id']}' ORDER BY ne.ne_fecha DESC")->all();
    }

    public function obtenerNotasdeEntregaAnualesAnuladas($peticion)
    {
        return $this->consultar("SELECT ne.ne_id, ne.ne_correlativo, ne.ne_fecha, ne.ne_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_notas_de_entrega ne 
        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_estatus=3 AND $peticion ORDER BY ne_id DESC")->all();
    }

    public function obtenerNotasdeEntregaAnuladasxempleadoHoy($anio)
    {
        return $this->consultar("SELECT ne.ne_id, ne.ne_correlativo, ne.ne_fecha, ne.ne_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_notas_de_entrega ne 
        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_estatus=3 AND  DATE(ne.ne_fecha)=CURRENT_DATE()  and ne_empleadoid='{$_SESSION['user_data']['einfo_id']}' ORDER BY ne.ne_id DESC")->all();
    }

    public function obtenerOrdenesdeEntregaAnuales($peticion)
    {
        return $this->consultar("SELECT n.n_id, n.n_correlativo, n.n_fecha, n.n_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_orden_de_entrega n
        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_estatus=1 AND $peticion ORDER BY n_id DESC")->all();
    }

    public function obtenerOrdenesdeEntregaAnualesPendientes($peticion)
    {
        return $this->consultar("SELECT n.n_id, n.n_correlativo, n.n_fecha, n.n_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_orden_de_entrega n
        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_estatus=0 OR n.n_estatus=2) AND $peticion ORDER BY n_id DESC")->all();
    }

    public function obtenerOrdenesdeEntregaAnualesCreditos($peticion)
    {
        return $this->consultar("SELECT n.n_id, n.n_correlativo, n.n_fecha, n.n_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_orden_de_entrega n
        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_estatus=4) AND $peticion ORDER BY n_id DESC")->all();
    }

    public function obtenerOrdenesdeEntregaAnualesAnuladas($peticion)
    {
        return $this->consultar("SELECT n.n_id, n.n_correlativo, n.n_fecha, n.n_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_orden_de_entrega n
        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_estatus=3 AND $peticion ORDER BY n_id DESC")->all();
    }

    public function obtenerPedidosAnuales($peticion)
    {
        return $this->consultar("SELECT pe.pe_id, pe.pe_correlativo, pe.pe_fecha, pe.pe_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_pedidos pe 
        JOIN app_divisas d ON d.div_id = pe.pe_divid
        JOIN app_clientes c ON c.cli_id = pe.pe_cliid 
        WHERE pe.pe_estatus=1 AND $peticion ORDER BY pe_id DESC")->all();
    }

    public function obtenerPedidosAnualesPendientesTotal($sucursal)
    {
        $date = date('Y');
        if ($sucursal == 0) {
            return $this->consultar("SELECT * FROM app_pedidos WHERE pe_estatus=0 OR pe_estatus=2  AND YEAR(pe_fecha)=$date")->count();
        } else {
            return $this->consultar("SELECT * FROM app_pedidos WHERE pe_estatus=0 OR pe_estatus=2 AND pe_surcid=$sucursal  AND YEAR(pe_fecha)=$date")->count();
        }
    }

    public function obtenerPedidosAnualesPendientes($peticion)
    {
        return $this->consultar("SELECT pe.pe_id, pe.pe_correlativo, pe.pe_fecha, pe.pe_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_pedidos pe 
        JOIN app_divisas d ON d.div_id = pe.pe_divid
        JOIN app_clientes c ON c.cli_id = pe.pe_cliid 
        WHERE (pe.pe_estatus=0 OR pe.pe_estatus=2) AND $peticion ORDER BY pe_id DESC")->all();
    }

    public function obtenerPresupuestosAnuales($peticion)
    {
        return $this->consultar("SELECT pre.pre_id, pre.pre_correlativo, pre.pre_fecha, pre.pre_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_presupuesto pre 
        JOIN app_divisas d ON d.div_id = pre.pre_divid
        JOIN app_clientes c ON c.cli_id = pre.pre_cliid 
        WHERE pre.pre_estatus=1 AND $peticion ORDER BY pre_id DESC")->all();
    }

    public function obtenerPresupuestosAnualesPendientesTotal($sucursal)
    {
        $date = date('Y');
        if ($sucursal == 0) {
            return $this->consultar("SELECT * FROM app_presupuesto WHERE pre_estatus=0 OR pre_estatus=2  AND YEAR(pre_fecha)=$date")->count();
        } else {
            return $this->consultar("SELECT * FROM app_presupuesto WHERE pre_estatus=0 OR pre_estatus=2 AND pre_surcid=$sucursal  AND YEAR(pre_fecha)=$date")->count();
        }
    }

    public function obtenerPresupuestosAnualesPendientes($peticion)
    {
        return $this->consultar("SELECT pre.pre_id, pre.pre_correlativo, pre.pre_fecha, pre.pre_total, d.*, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_presupuesto pre 
        JOIN app_divisas d ON d.div_id = pre.pre_divid
        JOIN app_clientes c ON c.cli_id = pre.pre_cliid 
        WHERE (pre.pre_estatus=0 OR pre.pre_estatus=2) AND $peticion ORDER BY pre_id DESC")->all();
    }

    public function obtenerCreditosAnuales($peticion)
    {
        return $this->consultar("SELECT nc.cre_id, nc.cre_docid, cre_facid, nc.cre_modulo, nc.cre_monto, nc.cre_fecha, nc.cre_descripcion, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_nota_de_credito nc
        JOIN app_clientes c ON c.cli_id = nc.cre_cliid 
        WHERE nc.cre_estatus = 0 AND $peticion ORDER BY cre_id DESC")->all();
    }

    public function obtenerCreditosAnualesInactivos($peticion)
    {
        return $this->consultar("SELECT nc.cre_id, nc.cre_docid, cre_facid, nc.cre_modulo, nc.cre_monto, nc.cre_fecha, nc.cre_descripcion, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_nota_de_credito nc
        JOIN app_clientes c ON c.cli_id = nc.cre_cliid 
        WHERE nc.cre_estatus = 1 AND $peticion ORDER BY cre_id DESC")->all();
    }

    public function obtenerNotadeCreditoDetalle(int $id_credito)
    {
        return $this->consultar("SELECT * FROM app_nota_de_credito_detalles WHERE credet_creid = $id_credito")->all();
    }

    public function obtenerNotadeCreditoDetalleTotal(int $id_credito)
    {
        return $this->consultar("SELECT SUM(credet_monto) total FROM app_nota_de_credito_detalles WHERE credet_creid = $id_credito")->row();
    }

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

    public function buscarLineasNEV($buscar, $filtro, $fecha_desde, $fecha_hasta, $sucursal)
    {
        $sqlproducto = '';

        $palabras = explode(' ', $buscar);
        if (count($palabras) > 1) {

            if ($filtro == 1) {
                if ($sucursal > 0) {
                    $sqlproducto .= " ne.ne_surcid=$sucursal AND";
                }
                foreach ($palabras as $i => $pal) {
                    if ($i == 0) {
                        $sqlproducto .= " (LOWER(andea.neart_procodigo) LIKE '%$pal%' OR LOWER(andea.neart_propnombre) LIKE '%$pal%')";
                    } else {
                        $sqlproducto .= " AND (LOWER(andea.neart_procodigo) LIKE '%$pal%' OR LOWER(andea.neart_propnombre) LIKE '%$pal%')";
                    }
                }
            } elseif ($filtro == 2) {
                if ($sucursal > 0) {
                    $sqlproducto .= " ne.ne_surcid=$sucursal AND";
                }
                foreach ($palabras as $i => $pal) {
                    if ($i == 0) {
                        $sqlproducto .= " (LOWER(c.cli_dni) LIKE '%$pal%' OR LOWER(c.cli_razon_social) LIKE '%$pal%')";
                    } else {
                        $sqlproducto .= " AND (LOWER(c.cli_dni) LIKE '%$pal%' OR LOWER(c.cli_razon_social) LIKE '%$pal%')";
                    }
                }
            } else {
                if ($sucursal > 0) {
                    $sqlproducto .= " ne.ne_surcid=$sucursal AND";
                }
                foreach ($palabras as $i => $pal) {
                    if ($i == 0) {
                        $sqlproducto .= " (LOWER(ne.ne_observaciones) LIKE '%$pal%')";
                    } else {
                        $sqlproducto .= " AND (LOWER(ne.ne_observaciones) LIKE '%$pal%')";
                    }
                }
            }
        } else {
            if ($filtro == 1) {
                if ($sucursal > 0) {
                    $sqlproducto .= " ne.ne_surcid=$sucursal AND";
                }
                $sqlproducto .= " (LOWER(andea.neart_procodigo) LIKE '%$buscar%' OR LOWER(andea.neart_propnombre) LIKE '%$buscar%')";
            } elseif ($filtro == 2) {
                if ($sucursal > 0) {
                    $sqlproducto .= " ne.ne_surcid=$sucursal AND";
                }
                $sqlproducto .= " (LOWER(c.cli_dni) LIKE '%$buscar%' OR LOWER(c.cli_razon_social) LIKE '%$buscar%')";
            } else {
                if ($sucursal > 0) {
                    $sqlproducto .= " ne.ne_surcid=$sucursal AND";
                }

                $sqlproducto .= " (LOWER(ne.ne_observaciones) LIKE '%$buscar%')";
            }
        }

        $sql = "SELECT ne.ne_divid, ne.ne_id, ne.ne_correlativo, c.cli_razon_social, andea.neart_proid, andea.neart_procantidad, andea.neart_procodigo, andea.neart_propnombre, andea.neart_prototal, DATE_FORMAT(ne.ne_fecha, '%d/%m/%Y')  as fecha
        FROM app_notas_de_entrega_articulos andea
        JOIN app_notas_de_entrega ne ON ne.ne_id = andea.neart_neid 
        JOIN app_sucursales su ON su.su_id=ne.ne_surcid 
        JOIN app_clientes c ON c.cli_id = ne.ne_cliid 
        WHERE $sqlproducto 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";

        return $this->consultar("$sql")->all();
    }

    public function buscarLineasORDENES($buscar, $filtro, $fecha_desde, $fecha_hasta, $sucursal)
    {
        $sqlproducto = '';

        $palabras = explode(' ', $buscar);
        if (count($palabras) > 1) {

            if ($filtro == 1) {
                if ($sucursal > 0) {
                    $sqlproducto .= " n.n_surcid=$sucursal AND";
                }
                foreach ($palabras as $i => $pal) {
                    if ($i == 0) {
                        $sqlproducto .= " (LOWER(andea.nart_procodigo) LIKE '%$pal%' OR LOWER(andea.nart_propnombre) LIKE '%$pal%')";
                    } else {
                        $sqlproducto .= " AND (LOWER(andea.nart_procodigo) LIKE '%$pal%' OR LOWER(andea.nart_propnombre) LIKE '%$pal%')";
                    }
                }
            } elseif ($filtro == 2) {
                if ($sucursal > 0) {
                    $sqlproducto .= " n.n_surcid=$sucursal AND";
                }
                foreach ($palabras as $i => $pal) {
                    if ($i == 0) {
                        $sqlproducto .= " (LOWER(c.cli_dni) LIKE '%$pal%' OR LOWER(c.cli_razon_social) LIKE '%$pal%')";
                    } else {
                        $sqlproducto .= " AND (LOWER(c.cli_dni) LIKE '%$pal%' OR LOWER(c.cli_razon_social) LIKE '%$pal%')";
                    }
                }
            } else {
                if ($sucursal > 0) {
                    $sqlproducto .= " n.n_surcid=$sucursal AND";
                }
                foreach ($palabras as $i => $pal) {
                    if ($i == 0) {
                        $sqlproducto .= " (LOWER(n.n_observaciones) LIKE '%$pal%')";
                    } else {
                        $sqlproducto .= " AND (LOWER(n.n_observaciones) LIKE '%$pal%')";
                    }
                }
            }
        } else {
            if ($filtro == 1) {
                if ($sucursal > 0) {
                    $sqlproducto .= " n.n_surcid=$sucursal AND";
                }
                $sqlproducto .= " (LOWER(andea.nart_procodigo) LIKE '%$buscar%' OR LOWER(andea.nart_propnombre) LIKE '%$buscar%')";
            } elseif ($filtro == 2) {
                if ($sucursal > 0) {
                    $sqlproducto .= " n.n_surcid=$sucursal AND";
                }
                $sqlproducto .= " (LOWER(c.cli_dni) LIKE '%$buscar%' OR LOWER(c.cli_razon_social) LIKE '%$buscar%')";
            } else {
                if ($sucursal > 0) {
                    $sqlproducto .= " n.n_surcid=$sucursal AND";
                }
                $sqlproducto .= " (LOWER(n.n_observaciones) LIKE '%$buscar%')";
            }
        }

        $sql = "SELECT n.n_divid, n.n_id, n.n_correlativo, c.cli_razon_social, andea.nart_proid, andea.nart_procantidad, andea.nart_procodigo, andea.nart_propnombre, andea.nart_prototal, DATE_FORMAT(n.n_fecha, '%d/%m/%Y') as fecha
        FROM app_orden_de_entrega_articulos andea
        JOIN app_orden_de_entrega n ON n.n_id = andea.nart_nid 
        JOIN app_sucursales su ON su.su_id=n.n_surcid 
        JOIN app_clientes c ON c.cli_id = n.n_cliid 
        WHERE $sqlproducto 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";

        return $this->consultar("$sql")->all();
    }

    public function buscarLineasFACV($buscar, $filtro, $fecha_desde, $fecha_hasta, $sucursal)
    {
        $sqlproducto = '';

        $palabras = explode(' ', $buscar);
        if (count($palabras) > 1) {

            if ($filtro == 1) {
                if ($sucursal > 0) {
                    $sqlproducto .= " fm.fm_surcid=$sucursal AND";
                }
                foreach ($palabras as $i => $pal) {
                    if ($i == 0) {
                        $sqlproducto .= " (LOWER(fa.fa_procodigo) LIKE  '%$pal%' OR LOWER(fa.fa_propnombre) LIKE '%$pal%')";
                    } else {
                        $sqlproducto .= " AND (LOWER(fa.fa_procodigo) LIKE  '%$pal%' OR LOWER(fa.fa_propnombre) LIKE '%$pal%')";
                    }
                }
            } elseif ($filtro == 2) {
                if ($sucursal > 0) {
                    $sqlproducto .= " fm.fm_surcid=$sucursal AND";
                }
                foreach ($palabras as $i => $pal) {
                    if ($i == 0) {
                        $sqlproducto .= " (LOWER(c.cli_dni) LIKE '%$pal%' OR LOWER(c.cli_razon_social) LIKE '%$pal%')";
                    } else {
                        $sqlproducto .= " AND (LOWER(c.cli_dni) LIKE '%$pal%' OR LOWER(c.cli_razon_social) LIKE '%$pal%')";
                    }
                }
            } else {
                if ($sucursal > 0) {
                    $sqlproducto .= " fm.fm_surcid=$sucursal AND";
                }
                foreach ($palabras as $i => $pal) {
                    if ($i == 0) {
                        $sqlproducto .= " (LOWER(fm.fm_observaciones) LIKE '%$pal%')";
                    } else {
                        $sqlproducto .= " AND (LOWER(fm.fm_observaciones) LIKE '%$pal%')";
                    }
                }
            }
        } else {
            if ($filtro == 1) {
                if ($sucursal > 0) {
                    $sqlproducto .= " fm.fm_surcid=$sucursal AND";
                }
                $sqlproducto .= " (LOWER(fa.fa_procodigo) LIKE  '%$buscar%' OR LOWER(fa.fa_propnombre) LIKE '%$buscar%')";
            } elseif ($filtro == 2) {
                if ($sucursal > 0) {
                    $sqlproducto .= " fm.fm_surcid=$sucursal AND";
                }
                $sqlproducto .= " (LOWER(c.cli_dni) LIKE '%$buscar%' OR LOWER(c.cli_razon_social) LIKE '%$buscar%')";
            } else {
                if ($sucursal > 0) {
                    $sqlproducto .= " fm.fm_surcid=$sucursal AND";
                }
                $sqlproducto .= " (LOWER(fm.fm_observaciones) LIKE '%$buscar%')";
            }
        }

        $sql = "SELECT fm.fm_divid, fm.fm_id, fm.fm_correlativo, c.cli_razon_social, fa.fa_proid, fa.fa_procantidad, fa.fa_procodigo, fa.fa_propnombre, fa.fa_prototal, DATE_FORMAT(fm.fm_fecha, '%d/%m/%Y')  as fecha
        FROM app_factura_master fm 
        JOIN app_sucursales su ON su.su_id=fm.fm_surcid 
        JOIN app_factura_articulos fa ON fm.fm_id = fa.fa_fmid
        JOIN app_clientes c ON c.cli_id = fm.fm_cliid 
        WHERE $sqlproducto GROUP BY fm.fm_fecha";

        return $this->consultar("$sql")->all();
    }

    public function buscarLineasPEDV($buscar, $filtro, $fecha_desde, $fecha_hasta, $sucursal)
    {

        $sqlproducto = '';

        $palabras = explode(' ', $buscar);
        if (count($palabras) > 1) {

            if ($filtro == 1) {
                if ($sucursal > 0) {
                    $sqlproducto .= " pe.pe_surcid=$sucursal AND";
                }
                foreach ($palabras as $i => $pal) {
                    if ($i == 0) {
                        $sqlproducto .= " (LOWER(pa.peart_procodigo) LIKE  '%$pal%' OR LOWER(pa.peart_propnombre) LIKE '%$pal%')";
                    } else {
                        $sqlproducto .= " AND (LOWER(pa.peart_procodigo) LIKE  '%$pal%' OR LOWER(pa.peart_propnombre) LIKE '%$pal%')";
                    }
                }
            } elseif ($filtro == 2) {
                if ($sucursal > 0) {
                    $sqlproducto .= " pe.pe_surcid=$sucursal AND";
                }
                foreach ($palabras as $i => $pal) {
                    if ($i == 0) {
                        $sqlproducto .= " (LOWER(c.cli_dni) LIKE  '%$pal%' OR LOWER(c.cli_razon_social) LIKE '%$pal%')";
                    } else {
                        $sqlproducto .= " AND (LOWER(c.cli_dni) LIKE  '%$pal%' OR LOWER(c.cli_razon_social) LIKE '%$pal%')";
                    }
                }
            } else {
                if ($sucursal > 0) {
                    $sqlproducto .= " pe.pe_surcid=$sucursal AND";
                }
                foreach ($palabras as $i => $pal) {
                    if ($i == 0) {
                        $sqlproducto .= " (LOWER(pe.pe_observaciones) LIKE  '%$pal%')";
                    } else {
                        $sqlproducto .= " AND (LOWER(pe.pe_observaciones) LIKE  '%$pal%')";
                    }
                }
            }
        } else {
            if ($filtro == 1) {
                if ($sucursal > 0) {
                    $sqlproducto .= " pe.pe_surcid=$sucursal AND";
                }
                $sqlproducto .= " (LOWER(pa.peart_procodigo) LIKE  '%$buscar%' OR LOWER(pa.peart_propnombre) LIKE '%$buscar%')";
            } elseif ($filtro == 2) {
                if ($sucursal > 0) {
                    $sqlproducto .= " pe.pe_surcid=$sucursal AND";
                }
                $sqlproducto .= " (LOWER(c.cli_dni) LIKE  '%$buscar%' OR LOWER(c.cli_razon_social) LIKE '%$buscar%')";
            } else {
                if ($sucursal > 0) {
                    $sqlproducto .= " pe.pe_surcid=$sucursal AND";
                }
                $sqlproducto .= "  (LOWER(pe.pe_observaciones) LIKE  '%$buscar%')";
            }
        }

        $sql = "SELECT pe.pe_divid, pe.pe_id, pe.pe_correlativo, c.cli_razon_social, pa.peart_proid, pa.peart_procantidad, pa.peart_procodigo, pa.peart_propnombre, pa.peart_prototal, DATE_FORMAT(pe.pe_fecha, '%d/%m/%Y')  as fecha
        FROM app_pedidos pe 
        JOIN app_sucursales su ON su.su_id=pe.pe_surcid 
        JOIN app_pedidos_articulos pa ON pe.pe_id = pa.peart_peid
        JOIN app_clientes c ON c.cli_id = pe.pe_cliid 
        WHERE $sqlproducto AND pe.pe_id NOT IN (SELECT ard.rd_facid_procedencia FROM app_ruta_documento ard WHERE rd_docid_procedencia=4 AND (rd_docid_conversion = 3 OR rd_docid_conversion = 2 OR rd_docid_conversion = 1)) 
        GROUP BY pe.pe_fecha ";

        return $this->consultar("$sql")->all();
    }

    public function buscarLineasPRESV($buscar, $filtro, $fecha_desde, $fecha_hasta, $sucursal)
    {
        $sqlproducto = '';

        $palabras = explode(' ', $buscar);
        if (count($palabras) > 1) {

            if ($filtro == 1) {
                if ($sucursal > 0) {
                    $sqlproducto .= " pe.pre_surcid=$sucursal AND";
                }
                foreach ($palabras as $i => $pal) {
                    if ($i == 0) {
                        $sqlproducto .= " (LOWER(pa.preart_procodigo) LIKE  '%$pal%' OR LOWER(pa.preart_propnombre) LIKE '%$pal%')";
                    } else {
                        $sqlproducto .= " AND (LOWER(pa.preart_procodigo) LIKE  '%$pal%' OR LOWER(pa.preart_propnombre) LIKE '%$pal%')";
                    }
                }
            } elseif ($filtro == 2) {
                if ($sucursal > 0) {
                    $sqlproducto .= " pe.pre_surcid=$sucursal AND";
                }
                foreach ($palabras as $i => $pal) {
                    if ($i == 0) {
                        $sqlproducto .= " (LOWER(c.cli_dni) LIKE  '%$pal%' OR LOWER(c.cli_razon_social) LIKE '%$pal%')";
                    } else {
                        $sqlproducto .= " AND (LOWER(c.cli_dni) LIKE  '%$pal%' OR LOWER(c.cli_razon_social) LIKE '%$pal%')";
                    }
                }
            } else {
                if ($sucursal > 0) {
                    $sqlproducto .= " pe.pre_surcid=$sucursal AND";
                }
                foreach ($palabras as $i => $pal) {
                    if ($i == 0) {
                        $sqlproducto .= " (LOWER(pe.pre_observaciones) LIKE  '%$pal%')";
                    } else {
                        $sqlproducto .= " AND (LOWER(pe.pre_observaciones) LIKE  '%$pal%')";
                    }
                }
            }
        } else {
            if ($filtro == 1) {
                if ($sucursal > 0) {
                    $sqlproducto .= " pe.pre_surcid=$sucursal AND";
                }
                $sqlproducto .= " (LOWER(pa.preart_procodigo) LIKE  '%$buscar%' OR LOWER(pa.preart_propnombre) LIKE '%$buscar%')";
            } elseif ($filtro == 2) {
                if ($sucursal > 0) {
                    $sqlproducto .= " pe.pre_surcid=$sucursal AND";
                }
                $sqlproducto .= " (LOWER(c.cli_dni) LIKE  '%$buscar%' OR LOWER(c.cli_razon_social) LIKE '%$buscar%')";
            } else {
                if ($sucursal > 0) {
                    $sqlproducto .= " pe.pre_surcid=$sucursal AND";
                }
                $sqlproducto .= " (LOWER(pe.pre_observaciones) LIKE  '%$buscar%')";
            }
        }

        $sql = "SELECT pe.pre_divid, pe.pre_id, pe.pre_correlativo, c.cli_razon_social, pa.preart_proid, pa.preart_procantidad, pa.preart_procodigo, pa.preart_propnombre, pa.preart_prototal, DATE_FORMAT(pe.pre_fecha, '%d-%m-%Y')  as fecha
        FROM app_presupuesto pe 
        JOIN app_sucursales su ON su.su_id=pe.pre_surcid 
        JOIN app_presupuesto_articulos pa ON pe.pre_id = pa.preart_preid
        JOIN app_clientes c ON c.cli_id = pe.pre_cliid  
        WHERE $sqlproducto AND 
        pe.pre_id NOT IN (SELECT ard.rd_facid_procedencia FROM app_ruta_documento ard WHERE rd_docid_procedencia = 3 AND (rd_docid_conversion = 2 OR rd_docid_conversion = 1))
        ORDER BY pe.pre_fecha DESC";

        return $this->consultar("$sql")->all();
    }

    public function obtenerCierreDelDia(int $retiro = 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.*
        FROM app_movimientos fa
        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_empleados_cuentas aec on aec.ec_bcid = bc.bc_id
        WHERE aec.ec_empid = {$_SESSION['user_data']['emp_id']} AND fa.fab_retiro = $retiro AND DATE(fa.fab_fecha) = CURRENT_DATE()
        GROUP BY bc.bc_id, mp_id, d.div_id ORDER BY bc_id")->all();
    }

    public function obtenerCredito($id_credito)
    {
        return $this->consultar("SELECT nc.cre_id, nc.cre_docid, nc.cre_modulo, nc.cre_monto, nc.cre_fecha, nc.cre_descripcion, c.cli_id, c.cli_dni, c.cli_razon_social,
        cli_direccion, cli_telefono, einfo_nombres, einfo_apellidos
        FROM app_nota_de_credito nc
        JOIN app_clientes c ON c.cli_id = nc.cre_cliid 
        JOIN app_empleados e ON e.emp_id = nc.cre_empleado
        JOIN app_empleados_info i ON i.einfo_id = e.emp_edatosid
        WHERE cre_id = $id_credito")->row();
    }

    public function impresora(int $id_impresora)
    {
        return $this->consultar("SELECT imp_tipo, CONCAT(imp_ip, ':', imp_puerto) ip_puerto 
        FROM app_impresoras WHERE imp_id = $id_impresora")->row();
    }

    public function impresoras()
    {
        return $this->consultar("SELECT imp_id id, imp_descripcion impresora, imp_tipo tipo FROM app_impresoras WHERE imp_tipo = 2 AND imp_estatus = 1")->all();
    }

    public function obtenerProduccionNEVSemana($divisa)
    {
        return $this->consultar("SELECT ne.ne_divid, WEEKDAY(ne.ne_fecha) as n_dia_semana, DAYNAME(ne.ne_fecha) as dia_semana, SUM(neart_procantidad) AS cantidad,  p.pro_costo * SUM(neart.neart_procantidad) as total_costo, SUM(neart_prototal) as total_venta
        FROM app_notas_de_entrega_articulos neart 
        JOIN app_notas_de_entrega ne ON ne.ne_id = neart.neart_neid 
        JOIN app_productos p ON p.pro_id = neart.neart_proid 
        WHERE ne.ne_estatus < 3 AND ne.ne_fecha>=(CURDATE()-INTERVAL WEEKDAY(CURDATE()) DAY) AND ne.ne_fecha<(CURDATE()-INTERVAL WEEKDAY(CURDATE())-5 DAY) 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 obtenerProduccionFACVSemana($divisa)
    {
        return $this->consultar("SELECT fm.fm_divid, WEEKDAY(fm.fm_fecha) as n_dia_semana, DAYNAME(fm.fm_fecha) as dia_semana, SUM(fa.fa_procantidad) AS cantidad, p.pro_costo * SUM(fa.fa_procantidad) as total_costo, SUM(fa.fa_prototal) as total_venta
        FROM app_factura_articulos fa
        JOIN app_factura_master fm ON fm.fm_id = fa.fa_fmid 
        JOIN app_productos p ON p.pro_id = fa.fa_proid 
        WHERE fm.fm_estatus < 3 AND fm.fm_fecha>=(CURDATE()-INTERVAL WEEKDAY(CURDATE()) DAY) AND fm.fm_fecha<(CURDATE()-INTERVAL WEEKDAY(CURDATE())-5 DAY) AND fm.fm_divid = $divisa
        GROUP BY DAY(fm.fm_fecha), fm.fm_divid, p.pro_id")->all();
    }

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

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

    public function obtenerProveedoresOrden($sucursal, $fecha_desde, $fecha_hasta)
    {
        if ($sucursal == 0) {
            return $this->consultar("SELECT *  
                FROM app_orden_de_entrega_articulos nart 
                JOIN app_orden_de_entrega ne ON ne.n_id = nart.nart_nid
                JOIN app_sucursales s ON s.su_id=ne.n_surcid 
                JOIN app_empleados_info aei ON aei.einfo_id = ne.n_empleadoid 
                JOIN app_productos pr ON pr.pro_id=nart.nart_proid 
                WHERE ne.n_estatus < 3 AND (DATE(ne.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND pr.pro_tipo != 3 ")->all();
        } else {
            return $this->consultar("SELECT *  
                 FROM app_orden_de_entrega_articulos nart 
                JOIN app_orden_de_entrega ne ON ne.n_id = nart.nart_nid 
                JOIN app_sucursales s ON s.su_id=ne.n_surcid 
                JOIN app_empleados_info aei ON aei.einfo_id = ne.n_empleadoid 
                JOIN app_productos pr ON pr.pro_id=nart.nart_proid 
                WHERE ne.n_surcid=$sucursal AND ne.n_estatus < 3 AND (DATE(ne.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND pr.pro_tipo != 3")->all();
        }
    }

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

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

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

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

    public function obtenerProduccionNEVDia($sucursal,$mes,$divisa)
    {
        if($sucursal>0){
            return $this->consultar("SELECT (1) as documentos,ne.ne_divid, WEEKDAY(ne.ne_fecha) as n_dia_semana, DAYNAME(ne.ne_fecha) as dia_semana,ne.ne_total as total  
            FROM app_notas_de_entrega ne  
            WHERE ne.ne_estatus < 3 AND YEAR(ne.ne_fecha)=YEAR(NOW()) AND MONTH(ne.ne_fecha) = $mes  AND ne.ne_divid = $divisa AND ne.ne_surcid=$sucursal ")->all();
        }else{
            return $this->consultar("SELECT (1) as documentos,ne.ne_divid, WEEKDAY(ne.ne_fecha) as n_dia_semana, DAYNAME(ne.ne_fecha) as dia_semana,ne.ne_total as total  
        FROM app_notas_de_entrega ne  
        WHERE ne.ne_estatus < 3 AND YEAR(ne.ne_fecha)=YEAR(NOW()) AND MONTH(ne.ne_fecha) = $mes AND ne.ne_divid = $divisa")->all();
        }
        
    }

    public function obtenerProduccionORDVDia($sucursal,$mes,$divisa)
    {
        if($sucursal>0){
            return $this->consultar("SELECT (1) as documentos,n.n_divid, WEEKDAY(n.n_fecha) as n_dia_semana, DAYNAME(n.n_fecha) as dia_semana,n.n_total as total  
            FROM app_orden_de_entrega n  
            WHERE n.n_estatus < 3 AND YEAR(n.n_fecha)=YEAR(NOW()) AND MONTH(n.n_fecha) = $mes  AND n.n_divid = $divisa AND n.n_surcid=$sucursal")->all();
        }else{
            return $this->consultar("SELECT (1) as documentos,n.n_divid, WEEKDAY(n.n_fecha) as n_dia_semana, DAYNAME(n.n_fecha) as dia_semana,n.n_total as total  
        FROM app_orden_de_entrega n 
        WHERE n.n_estatus < 3 AND YEAR(n.n_fecha)=YEAR(NOW()) AND MONTH(n.n_fecha) = $mes AND n.n_divid = $divisa ")->all();
        }
        
    }

    public function obtenerProduccionFACVDia($sucursal,$mes,$divisa)
    {
        if($sucursal>0){
            return $this->consultar("SELECT (1) as documentos,fm.fm_divid, WEEKDAY(fm.fm_fecha) as n_dia_semana, DAYNAME(fm.fm_fecha) as dia_semana, fm.fm_total as total 
            FROM app_factura_master fm 
            WHERE fm.fm_estatus < 3 AND YEAR(fm.fm_fecha)=YEAR(NOW()) AND MONTH(fm.fm_fecha) = $mes AND fm.fm_divid = $divisa AND fm.fm_surcid=$sucursal ")->all();
        }else{
            return $this->consultar("SELECT (1) as documentos,fm.fm_divid, WEEKDAY(fm.fm_fecha) as n_dia_semana, DAYNAME(fm.fm_fecha) as dia_semana, fm.fm_total as total 
            FROM app_factura_master fm 
            WHERE fm.fm_estatus < 3 AND YEAR(fm.fm_fecha)=YEAR(NOW()) AND MONTH(fm.fm_fecha) = $mes AND fm.fm_divid = $divisa  ")->all();
        }
        
    }

    public function obtenerProduccionNEVDiaTodas($sucursal,$mes)
    {
        if($sucursal>0){
            return $this->consultar("SELECT (1) as documentos,ne.ne_divid, WEEKDAY(ne.ne_fecha) as n_dia_semana, DAYNAME(ne.ne_fecha) as dia_semana,ne.ne_total as total  
            FROM app_notas_de_entrega ne  
            WHERE ne.ne_estatus < 3 AND YEAR(ne.ne_fecha)=YEAR(NOW()) AND MONTH(ne.ne_fecha) = $mes  AND ne.ne_surcid=$sucursal ")->all();
        }else{
            return $this->consultar("SELECT (1) as documentos,ne.ne_divid, WEEKDAY(ne.ne_fecha) as n_dia_semana, DAYNAME(ne.ne_fecha) as dia_semana,ne.ne_total as total  
        FROM app_notas_de_entrega ne  
        WHERE ne.ne_estatus < 3 AND YEAR(ne.ne_fecha)=YEAR(NOW()) AND MONTH(ne.ne_fecha) = $mes")->all();
        }
        
    }

    public function obtenerProduccionORDVDiaTodas($sucursal,$mes)
    {
        if($sucursal>0){
            return $this->consultar("SELECT (1) as documentos,n.n_divid, WEEKDAY(n.n_fecha) as n_dia_semana, DAYNAME(n.n_fecha) as dia_semana,n.n_total as total  
            FROM app_orden_de_entrega n  
            WHERE n.n_estatus < 3 AND YEAR(n.n_fecha)=YEAR(NOW()) AND MONTH(n.n_fecha) = $mes AND n.n_surcid=$sucursal")->all();
        }else{
            return $this->consultar("SELECT (1) as documentos,n.n_divid, WEEKDAY(n.n_fecha) as n_dia_semana, DAYNAME(n.n_fecha) as dia_semana,n.n_total as total  
        FROM app_orden_de_entrega n 
        WHERE n.n_estatus < 3 AND YEAR(n.n_fecha)=YEAR(NOW()) AND MONTH(n.n_fecha) = $mes")->all();
        }
        
    }

    public function obtenerProduccionFACVDiaTodas($sucursal,$mes)
    {
        if($sucursal>0){
            return $this->consultar("SELECT (1) as documentos,fm.fm_divid, WEEKDAY(fm.fm_fecha) as n_dia_semana, DAYNAME(fm.fm_fecha) as dia_semana, fm.fm_total as total 
            FROM app_factura_master fm 
            WHERE fm.fm_estatus < 3 AND YEAR(fm.fm_fecha)=YEAR(NOW()) AND MONTH(fm.fm_fecha) = $mes AND fm.fm_surcid=$sucursal ")->all();
        }else{
            return $this->consultar("SELECT (1) as documentos,fm.fm_divid, WEEKDAY(fm.fm_fecha) as n_dia_semana, DAYNAME(fm.fm_fecha) as dia_semana, fm.fm_total as total 
            FROM app_factura_master fm 
            WHERE fm.fm_estatus < 3 AND YEAR(fm.fm_fecha)=YEAR(NOW()) AND MONTH(fm.fm_fecha) = $mes")->all();
        }
        
    }

    public function obtenerProduccionNEVDiaHora($sucursal,$mes,$divisa)
    {
        $formt="%h %p";

        if($sucursal>0){
            return $this->consultar("SELECT (1) as documentos,ne.ne_divid,DATE_FORMAT(ne.ne_fecha,".'"%h %p"'.") as hora, ne.ne_total as total  
            FROM app_notas_de_entrega ne  
            WHERE ne.ne_estatus < 3 AND (YEAR(ne.ne_fecha)=YEAR(NOW()) AND MONTH(ne.ne_fecha) = $mes)  AND ne.ne_divid = $divisa AND ne.ne_surcid=$sucursal")->all();
        }else{
            return $this->consultar("SELECT (1) as documentos,ne.ne_divid,"."DATE_FORMAT(ne.ne_fecha,".'"%h %p"'.") as hora,ne.ne_total as total FROM app_notas_de_entrega ne  
        WHERE ne.ne_estatus < 3 AND (YEAR(ne.ne_fecha)=YEAR(NOW()) AND MONTH(ne.ne_fecha) = $mes) AND ne.ne_divid = $divisa")->all();
        }
        
    }

    public function obtenerProduccionFACVDiaHora($sucursal,$mes,$divisa)
    {
        $formt="%h %p";

        if($sucursal>0){
            return $this->consultar("SELECT (1) as documentos,fm.fm_divid,DATE_FORMAT(fm.fm_fecha,".'"%h %p"'.") as hora, fm.fm_total as total 
            FROM app_factura_master fm 
            WHERE fm.fm_estatus < 3 AND (YEAR(fm.fm_fecha)=YEAR(NOW()) AND MONTH(fm.fm_fecha) = $mes) AND fm.fm_divid = $divisa AND fm.fm_surcid=$sucursal")->all();
        }else{
            return $this->consultar("SELECT (1) as documentos,fm.fm_divid,"."DATE_FORMAT(fm.fm_fecha,".'"%h %p"'.") as hora, fm.fm_total as total 
            FROM app_factura_master fm 
            WHERE fm.fm_estatus < 3 AND (YEAR(fm.fm_fecha)=YEAR(NOW()) AND MONTH(fm.fm_fecha) = $mes) AND fm.fm_divid = $divisa ")->all();
        }
        
    }

    public function obtenerProduccionORDVDiaHora($sucursal,$mes,$divisa)
    {
        $formt="%h %p";

        if($sucursal>0){
            return $this->consultar("SELECT 1 as documentos,n.n_divid,DATE_FORMAT(n.n_fecha,".'"%h %p"'.") as hora, n.n_total as total  
            FROM app_orden_de_entrega n  
            WHERE n.n_estatus < 3 AND (YEAR(n.n_fecha)=YEAR(NOW()) AND MONTH(n.n_fecha) = $mes)  AND n.n_divid = $divisa AND n.n_surcid=$sucursal")->all();
        }else{
            return $this->consultar("SELECT 1 as documentos,n.n_divid,"."DATE_FORMAT(n.n_fecha,".'"%h %p"'.") as hora, n.n_total as total  
        FROM app_orden_de_entrega n  WHERE n.n_estatus < 3 AND YEAR(n.n_fecha)=YEAR(NOW()) AND MONTH(n.n_fecha) = $mes  AND n.n_divid = $divisa ")->all();
        }
        
    }

    public function obtenerProduccionNEVDiaHoraTodas($sucursal,$mes)
    {
        $formt="%h %p";

        if($sucursal>0){
            return $this->consultar("SELECT (1) as documentos,ne.ne_divid,DATE_FORMAT(ne.ne_fecha,".'"%h %p"'.") as hora, ne.ne_total as total  
            FROM app_notas_de_entrega ne  
            WHERE ne.ne_estatus < 3 AND (YEAR(ne.ne_fecha)=YEAR(NOW()) AND MONTH(ne.ne_fecha) = $mes) AND ne.ne_surcid=$sucursal")->all();
        }else{
            return $this->consultar("SELECT (1) as documentos,ne.ne_divid,"."DATE_FORMAT(ne.ne_fecha,".'"%h %p"'.") as hora,ne.ne_total as total FROM app_notas_de_entrega ne  
        WHERE ne.ne_estatus < 3 AND (YEAR(ne.ne_fecha)=YEAR(NOW()) AND MONTH(ne.ne_fecha) = $mes)")->all();
        }
        
    }

    public function obtenerProduccionFACVDiaHoraTodas($sucursal,$mes)
    {
        $formt="%h %p";

        if($sucursal>0){
            return $this->consultar("SELECT (1) as documentos,fm.fm_divid,DATE_FORMAT(fm.fm_fecha,".'"%h %p"'.") as hora, fm.fm_total as total 
            FROM app_factura_master fm 
            WHERE fm.fm_estatus < 3 AND (YEAR(fm.fm_fecha)=YEAR(NOW()) AND MONTH(fm.fm_fecha) = $mes) AND fm.fm_surcid=$sucursal")->all();
        }else{
            return $this->consultar("SELECT (1) as documentos,fm.fm_divid,"."DATE_FORMAT(fm.fm_fecha,".'"%h %p"'.") as hora, fm.fm_total as total 
            FROM app_factura_master fm 
            WHERE fm.fm_estatus < 3 AND (YEAR(fm.fm_fecha)=YEAR(NOW()) AND MONTH(fm.fm_fecha) = $mes) ")->all();
        }
        
    }

    public function obtenerProduccionORDVDiaHoraTodas($sucursal,$mes)
    {
        $formt="%h %p";

        if($sucursal>0){
            return $this->consultar("SELECT 1 as documentos,n.n_divid,DATE_FORMAT(n.n_fecha,".'"%h %p"'.") as hora, n.n_total as total  
            FROM app_orden_de_entrega n  
            WHERE n.n_estatus < 3 AND (YEAR(n.n_fecha)=YEAR(NOW()) AND MONTH(n.n_fecha) = $mes) AND n.n_surcid=$sucursal")->all();
        }else{
            return $this->consultar("SELECT 1 as documentos,n.n_divid,"."DATE_FORMAT(n.n_fecha,".'"%h %p"'.") as hora, n.n_total as total  
        FROM app_orden_de_entrega n  WHERE n.n_estatus < 3 AND YEAR(n.n_fecha)=YEAR(NOW()) AND MONTH(n.n_fecha) = $mes ")->all();
        }
        
    }

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

    public function obtenerproveedorProductos($Prod,$prov){
        return $this->consultar("SELECT *  
                FROM  app_productos_proveedores pv 
                LEFT JOIN app_proveedores p ON pv.pp_provid = p.prov_id 
                WHERE pv.pp_proid=$Prod AND p.prov_id=$prov")->row(); 
    }

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

    public function obtenerProveedoresOrdenpr($sucursal, $fecha_desde, $fecha_hasta)
    {
        if ($sucursal == 0) {
            return $this->consultar("SELECT *  
                FROM app_orden_de_entrega_articulos nart 
                JOIN app_orden_de_entrega ne ON ne.n_id = nart.nart_nid
                JOIN app_sucursales s ON s.su_id=ne.n_surcid 
                JOIN app_empleados_info aei ON aei.einfo_id = ne.n_empleadoid 
                JOIN app_productos pr ON pr.pro_id=neart.nart_proid 
                WHERE ne.n_estatus < 3 AND (DATE(ne.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND pr.pro_tipo != 3 ")->all();
        } else {
            return $this->consultar("SELECT *  
                 FROM app_orden_de_entrega_articulos neart 
                JOIN app_orden_de_entrega ne ON ne.n_id = neart.nart_nid 
                JOIN app_sucursales s ON s.su_id=ne.n_surcid 
                JOIN app_empleados_info aei ON aei.einfo_id = ne.n_empleadoid 
                JOIN app_productos pr ON pr.pro_id=neart.nart_proid 
                WHERE ne.n_surcid=$sucursal  AND ne.n_estatus < 3 AND (DATE(ne.n_fecha) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND pr.pro_tipo != 3")->all();
        }
    }

}
