<?php

namespace App\Modelos;

use Core\Database;

class PlanillaServicio extends Database
{
    protected $table = 'app_planillas_servicios';
    protected $id = 'serv_id';

    public function obtenerServiciosSegunCategoria(int $id_categoria, int $id_motor)
    {
        return $this->consultar("SELECT serv_id id, pro_descripcion descripcion
        FROM app_planillas_servicios s
        JOIN app_productos p ON p.pro_id = s.serv_proid
        WHERE s.serv_catid = $id_categoria
        AND s.serv_id NOT IN (SELECT ms_servid FROM app_planillas_motor_servicios WHERE ms_motid = $id_motor)")->all();
    }

    public function obtenerServiciosSegunCategoriaYTipoMotor(int $id_categoria, int $id_tipo)
    {
        return $this->consultar("SELECT serv_id id, pro_descripcion descripcion
        FROM app_planillas_servicios s
        JOIN app_productos p ON p.pro_id = s.serv_proid
        WHERE s.serv_catid = $id_categoria
        AND s.serv_id NOT IN (SELECT tipserv_servid FROM app_planilla_tipomotor_servicios WHERE tipserv_tipid = $id_tipo)")->all();
    }

    public function obtenerCategoriasSegunMotor(int $id_motor)
    {
        return $this->consultar("SELECT DISTINCT(cat_id) cat_id, cat_categorias
        FROM app_planillas_servicios s
        JOIN app_planilla_categorias c ON c.cat_id = s.serv_catid
        JOIN app_planillas_motor_servicios ms ON ms.ms_servid = s.serv_id
        WHERE ms.ms_motid = $id_motor")->all();
    }

    public function obtenerServicioProductoSegunCategoriaYMotor(int $id_categoria, int $id_motor, int $id_preorden)
    {
        return $this->consultar("SELECT serv_id, pro_descripcion, serv_proid 
        FROM app_planillas_servicios s 
        JOIN app_planillas_motor_servicios ms ON ms.ms_servid = s.serv_id
        JOIN app_productos p ON p.pro_id = s.serv_proid
        WHERE ms.ms_motid = $id_motor AND s.serv_catid = $id_categoria

")->all();
    }

    public function obtenerServicioProductoSegunCategoriaYTipoMotor(int $id_categoria, int $id_tipomotor, int $id_preorden)
    {
        return $this->consultar("SELECT serv_id, pro_descripcion,serv_proid 
        FROM app_planillas_servicios s
        JOIN app_planilla_tipomotor_servicios tipserv ON tipserv.tipserv_servid = s.serv_id
        JOIN app_productos p ON p.pro_id = s.serv_proid
        WHERE tipserv_tipid = $id_tipomotor AND s.serv_catid = $id_categoria AND s.serv_id NOT IN (SELECT preserv_servid FROM app_preorden_master_servicios WHERE preserv_preid = $id_preorden)")->all();
    }

    public function obtenerServicios()
    {
        return $this->consultar("SELECT * 
        FROM app_planillas_servicios s 
        JOIN app_planilla_categorias c ON c.cat_id = s.serv_catid
        JOIN app_productos p ON p.pro_id = s.serv_proid")->all();
    }

    public function obtenerServicioProductoSegunCategoria(int $id_categoria, int $id_preorden)
    {
        return $this->consultar("SELECT serv_id, pro_descripcion, serv_proid
        FROM app_planillas_servicios s 
        JOIN app_productos p ON p.pro_id = s.serv_proid
        WHERE s.serv_catid = $id_categoria AND s.serv_id NOT IN (SELECT preserv_servid FROM app_preorden_master_servicios WHERE preserv_preid = $id_preorden)")->all();
    }

    public function obtenerProductosTipoServicioNoAsignadosACategorias()
    {
        /*return $this->consultar("SELECT pro_id, pro_codigo, pro_descripcion
        FROM app_productos  
        WHERE pro_tipo = 3 
        AND pro_id NOT IN (SELECT serv_proid FROM app_planillas_servicios)")->all();*/
return $this->consultar("SELECT pro_id, pro_codigo, pro_descripcion
        FROM app_productos  
        WHERE pro_tipo = 3")->all();
    }
}
