<?php

namespace App\Modelos;

use Core\Database;

class Cmpfactura extends Database
{
    protected $table = 'app_compras';
    protected $id = 'cmp_id';

    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 obtenerSaldoCuenta($id_cuenta)
    {
        return $this->consultar("SELECT cmp_saldo FROM app_cuentas_metodopago WHERE cmp_id=$id_cuenta")->row();
    }

    public function pagoPositivo($id_cuenta, $monto)
    {
        return $this->consultar("UPDATE app_bancos_cuentas SET bc_saldo = $monto WHERE bc_id = $id_cuenta")->run();
    }

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

    public function obtenerPaises()
    {
        return $this->consultar('SELECT * FROM app_paises')->all();
    }

    public function obternerTiposDeDocumentos()
    {
        return $this->consultar('SELECT * FROM app_tipo_documentos')->all();
    }

    public function obtenerImpuestos()
    {
        return $this->consultar('SELECT * FROM app_impuestos WHERE imp_retencion = 0')->all();
    }

    public function obtenerAlmacenes()
    {
        return $this->consultar('SELECT * FROM app_almacenes')->all();
    }

    public function obtenerAlmacen($id)
    {
        return $this->consultar("SELECT * FROM app_almacenes WHERE alm_id = $id")->row();
    }

    public function obtenerSeries()
    {
        return $this->consultar('SELECT * FROM app_series')->all();
    }

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

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

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

    public function obtenerProveedor($id_proveedor)
    {
        return $this->consultar("SELECT * FROM app_proveedores 
        JOIN app_tipo_documentos ON prov_tipoidfiscal = tipodoc_id
        LEFT JOIN app_paises ON pa_id = prov_paisid 
        WHERE prov_id=$id_proveedor AND prov_estatus = 1")->row();
    }

    public function buscarProductos($buscar)
    {
        $buscar = strtolower($buscar);
        return $this->consultar("SELECT DISTINCT pro_id, fa_nombre, pro_descripcion, pro_costo, pimg.pi_imagen
        FROM app_productos p        
        LEFT JOIN app_productos_imagen pimg ON pimg.pi_proid = p.pro_id
        LEFT JOIN app_fabricantes fa ON fa.fa_id=p.pro_fabid
        WHERE pro_secompra = 1 AND pro_bloqueado = 0 and (LOWER(pro_codigo) LIKE '%$buscar%' OR LOWER(pro_descripcion) LIKE '%$buscar%')
        GROUP BY pro_id
        LIMIT 10")->all();
    }

    public function obtenerProducto($id_producto)
    {
        return $this->consultar("SELECT p.pro_id, p.pro_codigo, p.pro_descripcion, p.pro_costo, p.pro_precio, p.pro_impid, i.imp_descripcion, i.imp_valor FROM app_productos p LEFT JOIN app_impuestos i ON p.pro_impid = i.imp_id WHERE p.pro_id=$id_producto")->row();
    }

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

    public function verificarProductoEnStock($id_producto, $id_almacen)
    {
        return $this->consultar("SELECT ps_cantidad, ps_stockmin FROM app_productos_stocks ps WHERE ps.ps_proid=$id_producto and ps_almid=$id_almacen")->row();
    }

    public function obtenerDivisaAlCambio($id_divisa_master, $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 obtenerDescuento($id_cliente)
    {
        return $this->consultar("SELECT cli_descuento FROM app_clientes WHERE cli_id=$id_cliente")->row();
    }

    public function obtenerImpuesto($imp_id)
    {
        return $this->consultar("SELECT * FROM app_impuestos WHERE imp_id=$imp_id")->row();
    }

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

    public function obtenerFactura($id_factura)
    {
        return $this->consultar("SELECT * FROM app_compras WHERE cmp_id=$id_factura")->row();
    }

    public function obtenerEmpleado($id_empleado)
    {
        return $this->consultar("SELECT * FROM app_empleados_info WHERE einfo_id=$id_empleado")->row();
    }

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

    public function obtenerCuentasSegunMetodo($id_metodo_divisa)
    {
        return $this->consultar("SELECT cmp_id, bc_cuenta, bc_tipo, bc_alias, b.ban_codigo, b.ban_descripcion
        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 cmp.cmp_mdid=$id_metodo_divisa AND cmp_cueid IN (SELECT ec_bcid FROM app_empleados_cuentas WHERE ec_empid = {$_SESSION['user_data']['emp_id']})")->all();
    }

    public function obtenerCuenta($id_cuenta)
    {
        return $this->consultar("SELECT cm.cmp_id, md.md_divid, md.md_mpid
        FROM app_cuentas_metodopago cm
        JOIN app_metodopago_divisa md ON md.md_id = cmp_mdid
        WHERE cmp_id=$id_cuenta")->row();
    }

    public function obtenerDivisaSegunCuenta($id_cuenta)
    {
        return $this->consultar("SELECT *
        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 abonosSinDetalles($id_factura)
    {
        return $this->consultar("SELECT SUM(fa.fab_monto) as total, fa.fab_idgenerico
        FROM app_movimientos fa
        JOIN app_compras cmp ON cmp.cmp_id = fa.fab_idgenerico        
        WHERE fab_idgenerico=$id_factura AND fa.fab_doc = 8")->row();
    }

    public function abonos($id_factura)
    {
        return $this->consultar("SELECT *, DATE_FORMAT(fab_fecha, '%d-%m-%Y') as fecha 
        FROM app_movimientos fa
        JOIN app_compras cmp ON cmp.cmp_id = fa.fab_idgenerico
        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_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
        WHERE fab_idgenerico=$id_factura AND fa.fab_doc = 8 ORDER BY fab_fecha DESC")->all();
    }

    public function culminarFactura($id_factura)
    {
        return $this->consultar("UPDATE app_compras SET cmp_estatus = 1 WHERE cmp_id = $id_factura")->run();
    }

    public function correlativo(int $id_serie, int $id_documento)
    {
        return $this->consultar("SELECT c.cor_id, c.cor_serid, c.cor_docid, s.ser_descripcion, s.ser_impuesto, d.doc_prefijo, c.cor_correlativo
        FROM app_correlativos c
        JOIN app_series s ON s.ser_id = c.cor_serid
        JOIN app_documentos d ON d.doc_id = c.cor_docid
        WHERE c.cor_serid = $id_serie AND c.cor_docid = $id_documento")->row();
    }

    public function obtenerFacturaCompleta($id_factura)
    {
        return $this->consultar("SELECT * 
        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
        WHERE cmp.cmp_id=$id_factura")->row();
    }

    //-----------------------------------------------------------------------------

    public function actualizarCorrelativo(int $id_serie, $id_documento, int $correlartivo)
    {
        return $this->consultar("UPDATE app_correlativos SET cor_correlativo = $correlartivo WHERE cor_serid = $id_serie AND cor_docid=$id_documento")->run();
    }

    public function obtenerDatosEmpresa()
    {
        return $this->consultar('SELECT * FROM app_empresas WHERE emp_id=1')->row();
    }

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

    public function desontardeStock($id_producto, $id_alamcen, $cantidad)
    {
        return $this->consultar("UPDATE app_productos_stocks SET ps_cantidad = ps_cantidad - $cantidad WHERE ps_proid=$id_producto AND ps_almid=$id_alamcen")->run();
    }

    public function restablecerStock($id_producto, $id_alamcen, $cantidad)
    {
        return $this->consultar("UPDATE app_productos_stocks SET ps_cantidad = ps_cantidad + $cantidad WHERE ps_proid=$id_producto AND ps_almid=$id_alamcen")->run();
    }

    public function chuequearStock($id_producto, $id_almacen)
    {
        return $this->consultar("SELECT * FROM app_productos_stocks WHERE ps_proid = $id_producto AND ps_almid = $id_almacen")->row();
    }

    public function rutaDocumento($id_factura)
    {
        return $this->consultar("SELECT * 
        FROM app_ruta_documento rd 
        JOIN app_documentos d ON d.doc_id = rd.rd_docid_procedencia
        WHERE rd.rd_facid_conversion = $id_factura AND rd_docid_conversion = 8")->row();
    }
}
