<?php

namespace App\Modelos;

use Core\Database;

class IndexModel extends Database
{
    public function obtenerFacturas($sucursal)
    {
        if($sucursal==0){
            return $this->consultar("SELECT 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, fm.fm_estatus, d.*, s.ser_codigo
            FROM app_factura_master fm 
            JOIN app_series s ON s.ser_id = fm.fm_serid
            JOIN app_divisas d ON d.div_id = fm.fm_divid
            JOIN app_clientes c ON c.cli_id = fm.fm_cliid
            WHERE DATE(fm.fm_fecha) = CURRENT_DATE()")->all();
        }else{
            return $this->consultar("SELECT 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, fm.fm_estatus, d.*, s.ser_codigo
            FROM app_factura_master fm 
            JOIN app_series s ON s.ser_id = fm.fm_serid
            JOIN app_divisas d ON d.div_id = fm.fm_divid
            JOIN app_clientes c ON c.cli_id = fm.fm_cliid
            WHERE fm.fm_surcid=$sucursal AND DATE(fm.fm_fecha) = CURRENT_DATE()")->all();
        }
        
    }

    public function obtenerNotasdeentrega($sucursal)
    {
        if($sucursal==0){
            return $this->consultar("SELECT 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, ne.ne_estatus, d.*, s.ser_codigo
            FROM app_notas_de_entrega ne 
            JOIN app_series s ON s.ser_id = ne.ne_serid
            JOIN app_divisas d ON d.div_id = ne.ne_divid
            JOIN app_clientes c ON c.cli_id = ne.ne_cliid
            WHERE DATE(ne.ne_fecha) = CURRENT_DATE() 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 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, ne.ne_estatus, d.*, s.ser_codigo
            FROM app_notas_de_entrega ne 
            JOIN app_series s ON s.ser_id = ne.ne_serid
            JOIN app_divisas d ON d.div_id = ne.ne_divid
            JOIN app_clientes c ON c.cli_id = ne.ne_cliid
            WHERE ne.ne_surcid=$sucursal AND DATE(ne.ne_fecha) = CURRENT_DATE() 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 obteneroOrdendeEnterga($sucursal)
    {
        if($sucursal==0){
            return $this->consultar("SELECT c.cli_dni, c.cli_razon_social, n.n_id, n.n_correlativo, n.n_total, DATE_FORMAT(n.n_fecha, '%d-%m-%Y') as fecha, n.n_estatus, d.*, s.ser_codigo
            FROM app_orden_de_entrega n
            JOIN app_series s ON s.ser_id = n.n_serid
            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) = CURRENT_DATE() 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 c.cli_dni, c.cli_razon_social, n.n_id, n.n_correlativo, n.n_total, DATE_FORMAT(n.n_fecha, '%d-%m-%Y') as fecha, n.n_estatus, d.*, s.ser_codigo
            FROM app_orden_de_entrega n
            JOIN app_series s ON s.ser_id = n.n_serid
            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) = CURRENT_DATE() 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($id_factura, $documento)
    {
        return $this->consultar("SELECT SUM(fab_monto) as total FROM app_movimientos WHERE fab_idgenerico=$id_factura AND fab_doc=$documento")->row();
    }

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

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

    public function empleados()
    {
        return $this->consultar("SELECT * FROM app_empleados_info WHERE einfo_eliminado = 0")->count();
    }

    public function empleadosUsuarios()
    {
        return $this->consultar("SELECT * FROM app_empleados_info ei
        JOIN app_empleados e ON e.emp_edatosid = ei.einfo_id
        WHERE e.emp_estatus = 1")->count();
    }

    public function obtenerProduccionNEVMes($divisa)
    {
        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
        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 YEAR(ne.ne_fecha) = YEAR(CURRENT_DATE()) 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), ne.ne_divid, p.pro_id")->all();
    }

    public function obtenerProduccionFACVMes($divisa)
    {
        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
        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 YEAR(fm.fm_fecha) = YEAR(CURRENT_DATE()) AND fm.fm_divid = $divisa
        GROUP BY MONTH(fm.fm_fecha), fm.fm_divid, p.pro_id")->all();
    }

    public function obtenerProduccionNEVDia($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 MONTH(ne.ne_fecha) = MONTH(CURRENT_DATE) AND WEEK(ne.ne_fecha, 1) = WEEK(CURRENT_DATE, 1) 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($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 MONTH(fm.fm_fecha) = MONTH(CURRENT_DATE) AND WEEK(fm.fm_fecha, 1) = WEEK(CURRENT_DATE, 1) AND fm.fm_divid = $divisa
        GROUP BY DAY(fm.fm_fecha), fm.fm_divid, p.pro_id")->all();
    }

    public function obtenerArtProduccionNEVDia()
    {
        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
        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 p.pro_tipo != 3 AND ne.ne_estatus < 3 AND MONTH(ne.ne_fecha) = MONTH(CURRENT_DATE) AND WEEK(ne.ne_fecha, 1) = WEEK(CURRENT_DATE, 1)
        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 obtenerArtProduccionFACVDia()
    {
        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
        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 p.pro_tipo != 3 AND fm.fm_estatus < 3 AND MONTH(fm.fm_fecha) = MONTH(CURRENT_DATE) AND WEEK(fm.fm_fecha, 1) = WEEK(CURRENT_DATE, 1)
        GROUP BY DAY(fm.fm_fecha), fm.fm_divid, p.pro_id")->all();
    }

    public function obtenerTopArtProduccionNEVDia()
    {
        return $this->consultar("SELECT p.pro_id, p.pro_descripcion, SUM(neart.neart_procantidad) AS cantidad
        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 p.pro_tipo != 3 AND MONTH(ne.ne_fecha) = MONTH(CURRENT_DATE)
        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 p.pro_id ORDER BY cantidad DESC LIMIT 10")->all();
    }

    public function obtenerTopArtProduccionFACVDia()
    {
        return $this->consultar("SELECT p.pro_id, p.pro_descripcion, SUM(fa.fa_procantidad) AS cantidad
        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 p.pro_tipo != 3 AND MONTH(fm.fm_fecha) = MONTH(CURRENT_DATE)
        GROUP BY p.pro_id ORDER BY cantidad DESC LIMIT 10")->all();
    }

    public function obtenerTopClientesNev()
    {
        return $this->consultar("SELECT sum(ne.ne_articulos_total) as cantidad, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_notas_de_entrega ne 
        JOIN app_clientes c ON c.cli_id = ne.ne_cliid 
        WHERE YEAR(ne.ne_fecha) = YEAR(CURRENT_DATE) 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_cliid ORDER BY cantidad DESC")->all();
    }

    public function obtenerTopClientesFac()
    {
        return $this->consultar("SELECT sum(fm.fm_articulos_total) as cantidad, c.cli_id, c.cli_dni, c.cli_razon_social 
        FROM app_factura_master fm
        JOIN app_clientes c ON c.cli_id = fm.fm_cliid
        WHERE YEAR(fm.fm_fecha) = YEAR(CURRENT_DATE)
        GROUP BY fm.fm_cliid ORDER BY cantidad DESC")->all();
    }


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

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

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

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

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