<?php

namespace App\Modelos;

use Core\Database;

class PlanillaPreOrden extends Database
{
    protected $table = 'app_preorden_master';
    protected $id = 'pre_id';

    public function obtenerPreOrdenes()
    {
        return $this->consultar("SELECT * FROM app_preorden_master p JOIN app_clientes c ON c.cli_id = p.pre_cliid")->all();
    }

    public function obtenerPreOrdenesPendientes($peticion)
    {
        return $this->consultar("SELECT * 
        FROM app_preorden_master p 
        JOIN app_clientes c ON c.cli_id = p.pre_cliid 
        LEFT JOIN app_motores mot ON mot.mot_id = p.pre_motid
        LEFT JOIN app_marcas m ON m.m_id = mot.mot_marca
        WHERE pre_estatus = 1 AND $peticion ORDER BY pre_id DESC")->all();
    }

    public function obtenerPreOrdenesAnuladas($peticion)
    {
        return $this->consultar("SELECT * 
        FROM app_preorden_master p 
        JOIN app_clientes c ON c.cli_id = p.pre_cliid 
        LEFT JOIN app_motores mot ON mot.mot_id = p.pre_motid
        LEFT JOIN app_marcas m ON m.m_id = mot.mot_marca
        WHERE pre_estatus = 3 AND $peticion ORDER BY pre_id DESC")->all();
    }

    public function obtenerPreOrdenesCompletadas($peticion)
    {
        return $this->consultar("SELECT * 
        FROM app_preorden_master p 
        JOIN app_clientes c ON c.cli_id = p.pre_cliid 
        LEFT JOIN app_motores mot ON mot.mot_id = p.pre_motid
        LEFT JOIN app_marcas m ON m.m_id = mot.mot_marca
        WHERE pre_estatus = 2 AND $peticion ORDER BY pre_id DESC")->all();
    }

    public function obtenerPreOrdenesCompletadasEntregada($peticion)
    {
        return $this->consultar("SELECT * 
        FROM app_preorden_master p 
        JOIN app_clientes c ON c.cli_id = p.pre_cliid 
        LEFT JOIN app_motores mot ON mot.mot_id = p.pre_motid
        LEFT JOIN app_marcas m ON m.m_id = mot.mot_marca
        WHERE pre_estatus = 2 AND pre_entrega = 1 AND $peticion ORDER BY pre_id DESC")->all();
    }

    public function obtenerPreOrdenesTodas($peticion)
    {
        return $this->consultar("SELECT * 
        FROM app_preorden_master p 
        JOIN app_clientes c ON c.cli_id = p.pre_cliid 
        LEFT JOIN app_motores mot ON mot.mot_id = p.pre_motid
        LEFT JOIN app_marcas m ON m.m_id = mot.mot_marca
        WHERE $peticion ORDER BY pre_id DESC")->all();
    }

    public function obtenerPreOrdenesPorEntregar($peticion)
    {
        return $this->consultar("SELECT * 
        FROM app_preorden_master p 
        JOIN app_clientes c ON c.cli_id = p.pre_cliid 
        LEFT JOIN app_motores mot ON mot.mot_id = p.pre_motid
        LEFT JOIN app_marcas m ON m.m_id = mot.mot_marca
        WHERE pre_estatus = 0 AND $peticion ORDER BY pre_id DESC")->all();
    }

    public function obtenerPreOrdenesDetenidos($peticion)
    {
        return $this->consultar("SELECT * 
        FROM app_preorden_master p 
        JOIN app_clientes c ON c.cli_id = p.pre_cliid 
        LEFT JOIN app_motores mot ON mot.mot_id = p.pre_motid
        LEFT JOIN app_marcas m ON m.m_id = mot.mot_marca
        WHERE pre_estatus = 4 AND $peticion ORDER BY pre_id DESC")->all();
    }

    public function obtenerPreOrdenesCreditos($peticion)
    {
        return $this->consultar("SELECT * 
        FROM app_preorden_master p 
        JOIN app_clientes c ON c.cli_id = p.pre_cliid 
        LEFT JOIN app_motores mot ON mot.mot_id = p.pre_motid
        LEFT JOIN app_marcas m ON m.m_id = mot.mot_marca
        WHERE pre_estatus = 5 AND $peticion ORDER BY pre_id DESC")->all();
    }

    public function obtenerPreOrden(int $id_orden)
    {
        return $this->consultar("SELECT * FROM app_preorden_master p JOIN app_clientes c ON c.cli_id = p.pre_cliid WHERE pre_id = $id_orden")->row();
    }

    public function chuequearCorrelativo(int $id_serie, int $id_documento)
    {
        return $this->consultar("SELECT * FROM app_correlativos WHERE cor_serid = $id_serie AND cor_docid = $id_documento")->row();
    }

    public function obtenerSerie($id_serie)
    {
        return $this->consultar("SELECT * FROM app_series WHERE ser_id=$id_serie")->row();
    }

    public function obtenerCliente(int $id_cliente)
    {
        return $this->consultar("SELECT *
        FROM app_clientes c
        JOIN app_tipo_documentos ON cli_tipodocid = tipodoc_id
        LEFT JOIN app_paises ON pa_id = cli_paisid 
        LEFT JOIN app_clientes_grupos gr ON gr.grup_id = c.cli_grupid
        WHERE c.cli_id=$id_cliente")->row();
    }

    public function obtenerDivisaAlCambio(int $id_divisa_master, int $id_divisa_buscada)
    {
        return $this->consultar("SELECT * FROM app_divisas_factor WHERE df_divid_master=$id_divisa_master and df_divid=$id_divisa_buscada")->row();
    }

    public function obtenerSeriesEmpleado()
    {
        return $this->consultar("SELECT * FROM app_empleados_serie 
        JOIN app_series s ON s.ser_id = empser_serid
        JOIN app_sucursales su ON s.ser_sucursal = su.su_id
        WHERE empser_empid={$_SESSION['user_data']['emp_id']}")->all();
    }

    public function obtenerDivisaEmpleado()
    {
        return $this->consultar("SELECT *
        FROM app_empleados_divisas ed
        JOIN app_divisas d ON d.div_id = ed.ed_divid
        WHERE ed_empid={$_SESSION['user_data']['emp_id']}")->all();
    }

    public function obtenerAlmacenesEmpleado()
    {
        return $this->consultar("SELECT * 
        FROM app_empleados_almacen 
        JOIN app_almacenes a ON a.alm_id = empalm_almid
        WHERE empalm_empid={$_SESSION['user_data']['emp_id']}")->all();
    }

    public function obtenerEmpleadosId($id)
    {
        return $this->consultar("SELECT einfo_id id, CONCAT(einfo_nombres, ' ', einfo_apellidos) empleado FROM app_empleados_info WHERE einfo_id = $id")->row();
    }
}
