<?php

namespace App\Modelos;

use Core\Database;

class StockTransferencia extends Database
{
    protected $table    = 'app_productos_transferencia';
    protected $id       = 'ptr_id';

    public function obtenerListaTransferencias()
    {
        return $this->consultar('SELECT ptr.ptr_id,alm2.alm_nombre as origen, alm.alm_nombre as destino, ptr_estatus
        FROM app_productos_transferencia ptr
        INNER JOIN app_almacenes alm ON alm.alm_id=ptr.ptr_almdestid
        INNER JOIN app_almacenes alm2 ON alm2.alm_id=ptr.ptr_almorigid
        WHERE ptr_estatus=1 or ptr_estatus=2 or ptr_estatus=3 ORDER BY ptr_id DESC')->all();
    }

    public function obtenerListaTransferenciast($peticion)
    {
        return $this->consultar("SELECT ptr.ptr_id,alm2.alm_nombre as origen, alm.alm_nombre as destino, ptr_estatus
        FROM app_productos_transferencia ptr
        INNER JOIN app_almacenes alm ON alm.alm_id=ptr.ptr_almdestid
        INNER JOIN app_almacenes alm2 ON alm2.alm_id=ptr.ptr_almorigid
        WHERE $peticion ORDER BY ptr_id DESC")->all();
    }

    public function obtenerDetalleTransferencia($ptr_id)
    {
        return $this->consultar("SELECT ptr.*,alm2.alm_id as origen_id,alm2.alm_nombre as origen, alm.alm_nombre as destino,alm.alm_id as destino_id,ei.*
        FROM app_productos_transferencia ptr
        INNER JOIN app_empleados_info ei ON ei.einfo_id=ptr.ptr_einfoid
        INNER JOIN app_almacenes alm ON alm.alm_id=ptr.ptr_almdestid
        INNER JOIN app_almacenes alm2 ON alm2.alm_id=ptr.ptr_almorigid
        WHERE ptr.ptr_id=$ptr_id")->row();
    }

    public function obtenerProductoTransferencia($ptr_id)
    {
        return $this->consultar("SELECT * FROM app_productos_transferencia WHERE ptr_id=$ptr_id")->row();
    }

    public function buscarLineas($buscar, $filtro, $fecha_desde, $fecha_hasta)
    {
        if ($filtro == 1) {
            return $this->consultar("SELECT pt.ptr_id, a2.alm_id as id_alm_org, a2.alm_nombre as origen, a.alm_id as id_alm_des, a.alm_nombre as destino, p.pro_id, p.pro_codigo, p.pro_descripcion, ptd.ptd_cantidad, DATE_FORMAT(pt.ptr_creado, '%d-%m-%Y')  as fecha, pt.ptr_estatus
            FROM app_productos_transferencia pt 
            JOIN app_almacenes a ON a.alm_id = pt.ptr_almdestid 
            JOIN app_almacenes a2 ON a2.alm_id = pt.ptr_almorigid 
            JOIN app_productos_transferencia_detalle ptd ON pt.ptr_id = ptd.ptd_ptrid 
            JOIN app_productos p ON p.pro_id = ptd.ptd_proid 
            WHERE (DATE(pt.ptr_creado) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND (LOWER(p.pro_codigo) LIKE '%$buscar%' OR LOWER(p.pro_descripcion) LIKE '%$buscar%')")->all();
        } else {
            return $this->consultar("SELECT pt.ptr_id, a2.alm_id as id_alm_org, a2.alm_nombre as origen, a.alm_id as id_alm_des, a.alm_nombre as destino, p.pro_id, p.pro_codigo, p.pro_descripcion, ptd.ptd_cantidad, DATE_FORMAT(pt.ptr_creado, '%d-%m-%Y')  as fecha, pt.ptr_estatus
            FROM app_productos_transferencia pt 
            JOIN app_almacenes a ON a.alm_id = pt.ptr_almdestid 
            JOIN app_almacenes a2 ON a2.alm_id = pt.ptr_almorigid 
            JOIN app_productos_transferencia_detalle ptd ON pt.ptr_id = ptd.ptd_ptrid 
            JOIN app_productos p ON p.pro_id = ptd.ptd_proid 
            WHERE (DATE(pt.ptr_creado) BETWEEN '$fecha_desde' AND '$fecha_hasta') AND (LOWER(a.alm_nombre) LIKE '%$buscar%')")->all();
        }
    }
}
