<?php

class Comunicaciones_model extends Databases
{
    function __construct() {
        parent::__construct();
    }
    
    public function getData($estado)
    {
        if($estado=='pendientes') {
            $_sql = " ( CASE WHEN (B.CANTIDAD IS NULL OR B.CANTIDAD < ".RANGE_PENDIENTES_1.") THEN 1 "
                    . " WHEN (B.CANTIDAD < ".RANGE_PENDIENTES_2.") THEN 2"
                    . " ELSE 3 END ) AS NIVEL";
        } elseif($estado=='paquetes') {
            $_sql = " ( CASE WHEN (PAQUETE_FECHA > DATEADD(second, -".RANGE_PPC_DATA_1.", GETDATE())) THEN 1 "
                    . " WHEN (PAQUETE_FECHA > DATEADD(second, -".RANGE_PPC_DATA_2.", GETDATE())) THEN 2"
                    . " ELSE 3 END ) AS NIVEL";
        } else {
            $_sql = " ( CASE WHEN (IP_FECHA_INFORMADA > DATEADD(second, -".RANGE_PPC_1.", GETDATE())) THEN 1 "
                    . " WHEN (IP_FECHA_INFORMADA > DATEADD(second, -".RANGE_PPC_2.", GETDATE())) THEN 2"
                    . " ELSE 3 END ) AS NIVEL";
        }
        
        $sql = "SELECT A.*, B.LOCAL AS COD_LOCAL, ISNULL(B.CANTIDAD,0)AS PAQUETES_PENDIENTES,ISNULL(B.TABLA,'ST') AS TABLA, $_sql
FROM dbo.consulta_info_1 A
FULL OUTER join  VV_ESTADO_PAQUETES b ON B.LOCAL = A.NUMERO_LOCAL";
        
        $sql.=" ORDER BY NIVEL DESC, NUMERO_LOCAL ASC";
        
        $data = parent::query($sql);
        if($data && count($data)>0)
        {
            return $data;
        }
        else
            return FALSE;
    }
    
    public function getDataOfLocal($local)
    {
        $local = (int)$local;
        if(!$local) {
            return FALSE;
        }
        
        $_sql = " ( CASE WHEN (B.CANTIDAD IS NULL OR B.CANTIDAD < ".RANGE_PENDIENTES_1.") THEN 1 "
                . " WHEN (B.CANTIDAD < ".RANGE_PENDIENTES_2.") THEN 2"
                . " ELSE 3 END ) AS NIVEL_PEN,"
                . " ( CASE WHEN (PAQUETE_FECHA > DATEADD(second, -".RANGE_PPC_DATA_1.", GETDATE())) THEN 1 "
                . " WHEN (PAQUETE_FECHA > DATEADD(second, -".RANGE_PPC_DATA_2.", GETDATE())) THEN 2"
                . " ELSE 3 END ) AS NIVEL_PQ,"
                . " ( CASE WHEN (IP_FECHA_INFORMADA > DATEADD(second, -".RANGE_PPC_1.", GETDATE())) THEN 1 "
                . " WHEN (IP_FECHA_INFORMADA > DATEADD(second, -".RANGE_PPC_2.", GETDATE())) THEN 2"
                . " ELSE 3 END ) AS NIVEL_CX";
        
        $sql = "SELECT TOP 1 A.*,ISNULL(B.CANTIDAD,0)AS PAQUETES_PENDIENTES,ISNULL(B.TABLA,'ST') AS TABLA, $_sql
FROM dbo.consulta_info_1 A
FULL OUTER join  VV_ESTADO_PAQUETES b ON B.LOCAL = A.NUMERO_LOCAL WHERE NUMERO_LOCAL=$local";
        
        $data = parent::query($sql);
        if($data && count($data)>0)
        {
            return $data[0];
        }
        else
            return FALSE;
    }
    
    public function getTime()
    {
        $sql = "SELECT GETDATE() as time";
        $data = parent::query($sql);
        
        if($data && count($data)>0)
        {
            return $data[0]['time'];
        }
        else
            return FALSE;
    }
}