I have a logic problem to filter the registration and editing of my consultation schedule

Question

I am Using the Schedule component of Primefaces(6.2) My project uses: Postgres, Hibernate, JSF.

With the code this way I can register a new schedule and I can’t add a new schedule for the same doctor in the same date Start and dateFim of a schedule. (That was the goal)

Problem 1: The current problem is that when I edit the same schedule I cannot save, because it is understood by the code that I am trying to save another schedule in the same dateInition and dateFim of an existing schedule!

Issue 2: When editing an already created schedule and trying to save it the record is duplicated, the record before editing is maintained and the new saved is changed. Event Class(Scheduling)

/**
 * @author Humberto
 *
 */

@Audited
@Entity
@Table(name = "evento")
@SequenceGenerator(name = "evento_seq", sequenceName = "evento_seq", initialValue = 1, allocationSize = 1)
public class Evento  implements Serializable {

    private static final long serialVersionUID = 1089332436469136104L;
        @Id
        @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "evento_seq")
        private Long id;
        private String titulo;

        private Date dataInicio;
        private Date dataFim;
        private boolean diaInteiro;
        private TipoEvento tipoEvento;
        private String descricao;

        @ManyToOne(cascade = CascadeType.ALL)
        @JoinColumn()
        private Paciente paciente;

        @ManyToOne(cascade = CascadeType.ALL)
        @JoinColumn()
        private Medico medico;


        public Evento() {
            this.tipoEvento = TipoEvento.CONSULTA;
            this.titulo = "";
            this.diaInteiro = false;
        }

        public Evento(Long id,
                String titulo, 
                Date dataInicio, 
                Date dataFim, 
                boolean diaInteiro, 
                TipoEvento tipoEvento,
                String descricao,
                Paciente paciente,
                Medico medico)
        {

            this.id = id;
            this.titulo = titulo;
            this.dataInicio = dataInicio;
            this.dataFim = dataFim;
            this.diaInteiro = diaInteiro;
            this.tipoEvento = tipoEvento;
            this.setDescricao(descricao);
            this.paciente = paciente;
            this.medico =  medico;
        }

        public Long getId() {
            return id;
        }

        public void setId(Long id) {
            this.id = id;
        }

        public String getTitulo() {
            return titulo;
        }

        public void setTitulo(String titulo) {
            this.titulo = titulo;
        }

        public Date getDataInicio() {
            return dataInicio;
        }

        public void setDataInicio(Date dataInicio)  {
            //Calendar calendar = java.util.Calendar.getInstance() ;
          //  calendar.set(Calendar.HOUR_OF_DAY, 8) ;
            //calendar.set(Calendar);

            this.dataInicio = dataInicio;
        }

        public Date getDataFim() {
            return dataFim;
        }

        public void setDataFim(Date dataFim) {
            this.dataFim = dataFim;
        }

        public boolean isDiaInteiro() {
            return diaInteiro;
        }

        public void setDiaInteiro(boolean diaInteiro) {
            this.diaInteiro = diaInteiro;
        }

        public TipoEvento getTipoEvento() {
            return tipoEvento;
        }

        public void setTipoEvento(TipoEvento tipoEvento) {
            this.tipoEvento = tipoEvento;
        }

        public Medico getMedico() {
            return medico;
        }

        public void setMedico(Medico medico) {
            this.medico = medico;
        }
        @Override
        public int hashCode() {
            int hash = 3;
            hash = 29 * hash + Objects.hashCode(this.id);
            return hash;
        }

        @Override
        public boolean equals(Object obj) {
            if (this == obj) {
                return true;
            }
            if (obj == null) {
                return false;
            }
            if (getClass() != obj.getClass()) {
                return false;
            }
            final Evento other = (Evento) obj;
            if (!Objects.equals(this.id, other.id)) {
                return false;
            }
            return true;
        }

        public Paciente getPaciente() {
            return paciente;
        }

        public void setPaciente(Paciente paciente) {
            this.paciente = paciente;
        }

        public String getDescricao() {
            return descricao;
        }

        public void setDescricao(String descricao) {
            this.descricao = descricao;
        }


    }

Schedulebean

 @ManagedBean(name = "scheduleBean")
    @Controller
    @Scope(value = "session")
    public class ScheduleBean extends BeanManagedViewAbstract {

        private static final long serialVersionUID = 1L;

        private ScheduleModel model;
        private Evento evento;
        private ScheduleEvent event;
        private List<ScheduleEvent> scheduleEvents;
        private Date dataInicioSelecionada;
        private Date dataFimSelecionada;

        Date dataAtual = new Date();

        private CarregamentoLazyListForObjeto<Evento> list = new CarregamentoLazyListForObjeto<Evento>();

        @Autowired
        private EventoController eventoController;

        @Autowired
        private PacienteController pacienteController;

        @Autowired
        private MedicoController medicoController;

        public ScheduleBean() {
            event = new CustomScheduleEvent();
            model = new DefaultScheduleModel();

            evento = new Evento();
        }

        @Override
        public StreamedContent getArquivoReport() throws Exception {
            super.setNomeRelatorioJasper("report_evento");
            super.setNomeRelatorioSaida("report_evento");
            super.setListDataBeanCollectionReport(eventoController.findList(getClassImp()));
            return super.getArquivoReport();
        }

        // LISTA DE PACIENTES
        public List<SelectItem> getPacientes() throws Exception {
            return pacienteController.getListPacientes();
        }

        // LISTA DE MEDICOS
        public List<SelectItem> getMedicos() throws Exception {
            return medicoController.getListMedicos();
        }

        @PostConstruct
        public void init() throws Exception {

            if (this.model != null) {
                List<Evento> eventos = this.eventoController.listarEventos();
                // List<Evento> eventos = this.eventoDAO.listarTodos();
                if (this.scheduleEvents == null) {
                    this.scheduleEvents = new ArrayList<ScheduleEvent>();
                }
                for (Evento eventoAtual : eventos) { // lista que popula os eventos e inseri
                    ScheduleEvent newEvent = new CustomScheduleEvent(eventoAtual.getTitulo(), eventoAtual.getDataInicio(),
                            eventoAtual.getDataFim(), eventoAtual.getTipoEvento().getCss(), eventoAtual.isDiaInteiro(),
                            eventoAtual.getDescricao(), eventoAtual.getMedico(), eventoAtual.getPaciente(), eventoAtual);
                    if (!this.scheduleEvents.contains(newEvent)) {
                        newEvent.setId(eventoAtual.getId().toString());
                        this.scheduleEvents.add(newEvent);
                        this.model.addEvent(newEvent);
                    }
                }
            }
        }

        public boolean validarMedico() throws Exception {

    String[] param = new String[] {"idMedico", "dataInicio", "dataFim","eventoId"};
    String hql ="FROM Evento e WHERE e.medico.idMedico = "
            + ":idMedico AND (e.dataInicio BETWEEN :dataInicio AND :dataFim "
            + "OR e.dataFim BETWEEN :dataInicio AND :dataFim) AND (e.id = :eventoId AND :eventoId IS NOT NULL)";

    List<Evento> lista = eventoController.findListByQueryDinamica(
            hql, Arrays.asList(param) , evento.getMedico().getIdMedico(), evento.getDataInicio(), evento.getDataFim(),evento.getId());
    //novo
    if (evento.getId() == null && !lista.isEmpty()) {
        return false;
        //se for igual as datas e lista vier completa
        // vindo completa quer dizer que há um agendamento gravado
    }else if((this.dataInicioSelecionada.compareTo(evento.getDataInicio()) != 0 ||
              this.dataFimSelecionada.compareTo(evento.getDataFim()) !=0) 
            &&  !lista.isEmpty()){
        return false;
    }else {
        return true;
    }
}

    public void salvar() throws Exception {
        // salva o construtor que implementa a interface do Schedule com os atributos.
        ScheduleEvent newEvent = new CustomScheduleEvent(this.evento.getTitulo(), this.evento.getDataInicio(),
                this.evento.getDataFim(), this.evento.getTipoEvento().getCss(), this.evento.isDiaInteiro(),
                this.evento.getDescricao(), this.evento.getMedico(), this.evento.getPaciente(), this.evento);

        if (evento.getDataInicio().before(evento.getDataFim()) && validarMedico()) {

            if (evento.getId() == null) {
                model.addEvent(newEvent);
            } else {
                newEvent.setId(event.getId());
                model.updateEvent(newEvent);
            }
            eventoController.merge(evento);
            FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_INFO, "Agendamento Salvo",
                    "Agendamento para:  " + evento.getTitulo());
            addMessage(message);
        }else {
            FacesMessage message = new FacesMessage(
                    FacesMessage.SEVERITY_INFO, "Já existe um médico cadastrado CAIU NO ELSE",
                    "" );
            addMessage(message);
        }
    }

    public void remover() throws Exception {
    try{
        eventoController.delete(evento);
        model.deleteEvent(event);
        FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_INFO, "Agendamento Removido",
                "Agendamento Removido :" + evento.getTitulo());
        addMessage(message);
    }catch (Exception e) {
            FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_INFO, "Impossivel remover",
                "Há dependencias:" + evento.getTitulo());
        addMessage(message);
    }


}

    // AO SALVAR SELEÇÃO DE UMA AGENDAMENTO
    public void onDateSelect(SelectEvent selectEvent) {
        this.evento = new Evento();
        Date dataSelecionada = (Date) selectEvent.getObject();
        @SuppressWarnings("unused")
        DateTime dataSelecionadaJoda = new DateTime(dataSelecionada.getTime());
        this.evento.setDataInicio(dataSelecionada);
        // Adiciona 30min por consulta
        //this.evento.setDataFim(dataSelecionadaJoda.plusMinutes(30).toDate());
    }

    // EVENTO DE SELEÇÃO DOS HORARIOS AGENDADOS
    public void onEventSelect(SelectEvent selectEvent) {
        event = (CustomScheduleEvent) selectEvent.getObject();
        this.evento = (Evento) event.getData();
        this.dataInicioSelecionada = this.evento.getDataInicio();
        this.dataFimSelecionada = this.evento.getDataFim();
    }

    // EVENTO QUE PERMITE REDIMENSIONAR HORARIOS
    public void onEventResize(ScheduleEntryResizeEvent event) {
        FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_INFO, "Evento Redimensionado",
                "Dia:" + event.getDayDelta() + ", Horário:" + event.getMinuteDelta());
        addMessage(message);
    }

    // EVENTO QUE PERMITE MOVER HORARIOS SELECIONADOS
    public void onEventMove(ScheduleEntryMoveEvent event) {
        /*
         * if(evento.getDataInicio().getTime() <= evento.getDataFim().getTime()){
         * 
         * try{ eventoController.merge(evento); }catch(Exception ex){
         * FacesContext.getCurrentInstance().addMessage(null, new
         * FacesMessage("Erro ao salvar trabalho", "Erro:" + ex.getMessage())); } evento
         * = new Evento(); }else{ FacesContext.getCurrentInstance().addMessage(null, new
         * FacesMessage("Data do começo do evento não pode ser maior que a do final",
         * "")); }
         */

        // FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_INFO, "Event
        // moved", "Day delta:" + event.getDayDelta() + ", Minute delta:" +
        // event.getMinuteDelta());

        // addMessage(message);
    }

    private void addMessage(FacesMessage message) {
        FacesContext.getCurrentInstance().addMessage(null, message);
    }

    public TipoEvento[] getTiposEventos() {
        return TipoEvento.values();
    }

    // GETTERS E SETTERS

    public List<ScheduleEvent> getScheduleEvents() {
        return scheduleEvents;
    }

    public void setScheduleEvents(List<ScheduleEvent> scheduleEvents) {
        this.scheduleEvents = scheduleEvents;
    }

    public Date getDataAtual() {
        return dataAtual;
    }

    public void setDataAtual(Date dataAtual) {
        // Pega somente a data para passar para data minima do calendario
        LocalDate localDate = new LocalDate();
        dataAtual = localDate.toDate();

        this.dataAtual = dataAtual;
    }

    @Override
    protected Class<Evento> getClassImp() {
        return Evento.class;
    }

    @Override
    protected InterfaceCrud<Evento> getController() {
        return eventoController;
    }

    @Override
    public void consultarEntidade() throws Exception {
        evento = new Evento();
        list.clean();
        list.setTotalRegistroConsulta(super.totalRegistroConsulta(), super.getSqlLazyQuery());

    }

    @Override
    public String condicaoAndParaPesquisa() throws Exception {
        return null;
    }

    public ScheduleModel getModel() {
        return model;
    }

    public void setModel(ScheduleModel model) {
        this.model = model;
    }

    public Evento getEvento() {
        return evento;
    }

    public void setEvento(Evento evento) {
        this.evento = evento;
    }

    public CarregamentoLazyListForObjeto<Evento> getList() {
        return list;
    }

    public void setList(CarregamentoLazyListForObjeto<Evento> list) {
        this.list = list;
    }

}

Answer 1

User the H2 to test what you said works the Intel.

drop table scheduling medical; create table agendamento_medico (id int Primary key , id_medico int, date timestamp); create index idx_medico_agendament_medico (id_medico,data); Insert into agenda_medico values(1,1,'2019-05-05 00:00:00'); Insert into agenda_medico values(2,1,'2019-05-05 01:00:00'); Insert into agenda_medico values(4,1,'2019-05-05 02:00:00'); Insert into agenda_medico values(5,1,'2019-05-05 03:00:00'); Insert into agenda_medico values(6,1,'2019-05 04:00:00');

explain select * from agenda_medico Where id_medico = 1 and data between '2019-05-05 02:00:00' and '2019-05-05 03:30:00'

This way you can do an indice scan. noting that you need the doctor id in this scheduling table. is a collection of dates, does not need start date or end date, since there can be no collision.

the scheme is when Voce will insert into this table checks if collision will occur. which is what I assume Voce wants not to happen. then will record generates two lines, the Dice will work. recommend the H2 to do these things that is very quiet.

I am leaving the first faces - java , because this model data_inicio data_fim does not see it as the correct way to express the dates associated with the filling, because if we are talking about hql the problem is in the entidadae model and not in the first faces.

I think data_start and data_end is the evendo scale.. or better scheduling scale.

Answer 2

the model is kind of weird... because in theory you could use a separate date and time... would Where data = ? and hour = ? and you wouldn’t have the between payload which can be costly depending on the size of the table... and date can be a format without timestamp... but would have to give a better thought I wouldn’t use timestamp and between break date and time ... the Dice would get good... would be doctor’s code and date.