View data is disappearing at a certain time

Question

Here’s the thing, from what I understand the whole project follows the MVC Standard. I just have a big problem. A view of requests there is a refresh 30 seconds simple, looking for new orders. So far so good! The problem happens every day at 21:00, if by chance there is a new request from customers to view does not pull this data from the database to print onscreen.

To improve understanding, customers use an app Android to fulfill their orders. All requests that arise at times of (00:00 until 20:59), the data is shown on the screen, but past that nothing is shown.

Who can help me I will be very grateful...

---

Model:

<?php

namespace App\Model;

class PedidosDao extends Dao {

static public function getPedidosPorPeriodo($data1, $data2) {
    try {
        $sql = "SELECT * FROM cpedido where ped_status != 'PEDIDO RECUSADO' and ped_dataHora between ? and ?";
        $statement_sql = Conexao::getConnection()->prepare($sql);
        $statement_sql->bindValue(1, $data1);
        $statement_sql->bindValue(2, $data2);
        $statement_sql->execute();
        return $statement_sql->fetchAll(\PDO::FETCH_ASSOC);
    } catch (\PDOException $exc) {
        echo $exc->getMessage();
    }
}

static public function getPedidosPorCliente($cli_codigo) {
    try {
        $sql = "SELECT * FROM cpedido where ped_status != 'PEDIDO RECUSADO' and cli_codigo = ?";
        $statement_sql = Conexao::getConnection()->prepare($sql);
        $statement_sql->bindValue(1, $cli_codigo);
        $statement_sql->execute();
        return $statement_sql->fetchAll(\PDO::FETCH_ASSOC);
    } catch (\PDOException $exc) {
        echo $exc->getMessage();
    }
}

static public function getStatusPedido($ped_chave) {
    try {
        $sql = "select ped_status from cpedido where ped_chave = ?";
        $statement_sql = Conexao::getConnection()->prepare($sql);
        $statement_sql->bindValue(1, $ped_chave);
        $statement_sql->execute();
        return $statement_sql->fetch(\PDO::FETCH_ASSOC);
    } catch (\PDOException $exc) {
        echo $exc->getMessage();
    }
}

static public function getCPedido($ped_chave) {
    try {
        $sql = "select * from cpedido where ped_chave = ?";
        $statement_sql = Conexao::getConnection()->prepare($sql);
        $statement_sql->bindValue(1, $ped_chave);
        $statement_sql->execute();
        return $statement_sql->fetch(\PDO::FETCH_ASSOC);
    } catch (\PDOException $exc) {
        echo $exc->getMessage();
    }
}

static public function getAllPedidos($status) {
    try {
        $sql = "select * from cpedido where ped_status = ? AND DATE_FORMAT(ped_dataHora, '%Y-%m-%d') = CURDATE() ";
        $statement_sql = Conexao::getConnection()->prepare($sql);
        $statement_sql->bindValue(1, $status);
        $statement_sql->execute();
        return $statement_sql->fetchAll(\PDO::FETCH_ASSOC);
    } catch (\PDOException $exc) {
        echo $exc->getMessage();
    }
}

static public function getItemPedido($pedchave, $prdcodigo) {
    try {
        $sql = "select * from dpedido where pedchave = ? and prdcodigo = ?";
        $statement_sql = Conexao::getConnection()->prepare($sql);
        $statement_sql->bindValue(1, $pedchave);
        $statement_sql->bindValue(2, $prdcodigo);
        $statement_sql->execute();
        return $statement_sql->fetch(\PDO::FETCH_ASSOC);
    } catch (\PDOException $exc) {
        echo $exc->getMessage();
    }
}

static public function getProdutoPedido($pedchave) {
    try {
        $sql = "select * from dpedido where pedchave = ? and tipo ='PRODUTO' order by prdcodigo asc";
        $statement_sql = Conexao::getConnection()->prepare($sql);
        $statement_sql->bindValue(1, $pedchave);
        $statement_sql->execute();
        return $statement_sql->fetchAll(\PDO::FETCH_ASSOC);
    } catch (\PDOException $exc) {
        echo $exc->getMessage();
    }
}

static public function getAdicionalPedido($pedchave) {
    try {
        $sql = "select * from dpedido where pedchave = ? and tipo ='ADICIONAL'";
        $statement_sql = Conexao::getConnection()->prepare($sql);
        $statement_sql->bindValue(1, $pedchave);
        $statement_sql->execute();
        return $statement_sql->fetchAll(\PDO::FETCH_ASSOC);
    } catch (\PDOException $exc) {
        echo $exc->getMessage();
    }
}

static public function getItemsPedido($pedchave) {
    try {
        $sql = "select * from dpedido where pedchave = ?";
        $statement_sql = Conexao::getConnection()->prepare($sql);
        $statement_sql->bindValue(1, $pedchave);
        $statement_sql->execute();
        return $statement_sql->fetchAll(\PDO::FETCH_ASSOC);
    } catch (\PDOException $exc) {
        echo $exc->getMessage();
    }
}

static public function getProdutosMaisVendidos($data1, $data2) {
    try {
        $sql = "select sum(i.quantidade) as qtd, 
                i.prdcodigo as cod, 
                pr.prd_descricao as descr
                from cpedido p 
                inner join dpedido i on p.ped_chave = i.pedchave
                inner join produtos pr on pr.prd_codigo = i.prdcodigo
                where p.ped_status !=  'PEDIDO RECUSADO' and p.ped_dataHora between ? and ? group by i.prdcodigo order by qtd desc";
        $statement_sql = Conexao::getConnection()->prepare($sql);
        $statement_sql->bindValue(1, $data1);
        $statement_sql->bindValue(2, $data2);
        $statement_sql->execute();
        return $statement_sql->fetchAll(\PDO::FETCH_ASSOC);
    } catch (\PDOException $exc) {
        echo $exc->getMessage();
    }
}

}

Answer 1

The Problem

What is happening is a problem with the time zone. As in Brazil we are in the time zone -03:00, we can make a simple calculation, since the server - usually - works with time zone 00:00.

This causes a difference of 03 hours between you and the server. If the access was made from Portugal, the difference would be 01:00 hour, ie requests from 23:00 hours, would not appear.

21:00 + 03:00 = 00:00 do dia seguinte
└─┬─┘  └──┬──┘  └─┬─┘
  │       │       └─────── Horário no servidor
  │       └─────────────── Diferença de horário entre você e o servidor
  └─────────────────────── Horário local

If it is not clear, you can create a file PHP on your server and test with the code below.

<?php

/* Exibe a data do servidor */
echo date("c");

/* Altera o fuso horário do servidor */
date_default_timezone_set("America/Sao_Paulo");

/* Quebra de linha e exibe a data com o fuso horário definido */
echo PHP_EOL;
echo date("c");

Solution

One of the ways to fix this problem is by setting the time zone in the database and on PHP.

In the database, just run the query down below:

SET GLOBAL time_zone = '-3:00';

Already in the PHP, just add the code below:

date_default_timezone_set("America/Sao_Paulo");

List of Supported Time Zones