How to check data range?

8

When requesting a vehicle reservation, the system enters the expected departure and return time code (we prefer to work with fixed codes instead of schedules to facilitate scheduling). What happens is that when the reservation is made at times ahead (for example, I'm reserving in the morning a schedule in the afternoon) the system allows another reservation to pass "over" the previous reservation.

For example, if I reserve from 9:00 a.m. to 10:00 p.m., my% cc% does not show this interval after confirmation, but if someone book from 8:00 a.m. to 11:00 p.m. on the same day he will let me record . With select below I do not display the reserved times, any idea how to do the validation?

SELECT H.* FROM HORARIOS H
WHERE NOT EXISTS (
    SELECT * FROM RESERVAS R
    WHERE H.COD BETWEEN R.COD_HORA_SAIDA AND
          R.COD_HORA_PREVISTA-1 AND
          R.COD_VEICULO = :codveiculo AND
          DATE_FORMAT(DATA_SAIDA, \'%d-%m-%Y\') = :codcalendario AND
          r.ativa = 1)
ORDER BY H.COD

Below is an example of the situation that occurs:

    
asked by anonymous 14.12.2015 / 13:09

2 answers

2

I have decided as follows:

SELECT count(*) as flag FROM HORARIOS H
                     WHERE EXISTS (
                           SELECT * FROM RESERVAS R
                                WHERE H.COD BETWEEN R.COD_HORA_SAIDA AND
                                      R.COD_HORA_PREVISTA-1 AND
                                      R.COD_VEICULO = 2 AND
                                      DATE_FORMAT(DATA_SAIDA, '%d-%m-%Y') = :data_saida and
                                      (h.cod between :codsaida and :codretorno) and
                                      r.ativa = 1)
                                ORDER BY H.COD;

That way, I did an if, if the result is more than 1 it has something in the range and gives error. Thank you all.

    
14.12.2015 / 18:20
2

Come on. I will try to use a pseudo code, as I have no basis in php.

Given the variances:

horaEntradaBanco = registroBanco.horaEntrada;
horaSaidaBanco = registroBanco.horaSaida;
horaEntradaMarkup = $('#campoHorarioEntrada').getValue();
horaSaidaMarkup = $('#campoHorarioSaida').getValue();

You could do the following validations:

if(horaEntradaMarkup<=horaEntradaBanco && horaSaidaMarkup>=horaSaidaBanco){
    //bloquear utilização de horário
    //exemplo de entrada no if: Horas banco: 14h~15h
                                Horas Markup: 13h~17h
} else if(horaEntradaMarkup<=horaEntradaBanco && horaSaidaMarkup<=horaSaidaBanco) {
    //bloquear utilização de horário
    //exemplo de entrada no if: Horas banco: 14h~16h
                                Horas Markup: 13h~15h

    //Aqui eu não sei se já está sendo validado ou não, já que vc só falou do exemplo acima.
}

Ve goes right.

    
14.12.2015 / 13:53