ReservationDao.java

public List<Reservation> filterbyDate(int staffid, String startdate, String enddate) {
        List<Reservation> list = new ArrayList<>();
        String sql = "select Reservation.ID, UserAccount.Name, Service.ServiceName, ChildrenInformation.Name, Service.Price, ReservationDate, Schedule.WorkDay, Schedule.Slot, Reservation.Status\n"
                + "from Reservation\n"
                + "INNER JOIN Schedule ON Schedule.Id = Reservation.ScheduleId\n"
                + "INNER JOIN EmployeeAccount ON Schedule.DoctorId = EmployeeAccount.ID\n"
                + "INNER JOIN UserAccount ON Reservation.CustomerID = UserAccount.ID\n"
                + "INNER JOIN Service ON Schedule.ServiceId = Service.ServiceID\n"
                + "INNER JOIN ChildrenInformation ON Reservation.ID = ChildrenInformation.ReservationID\n"
                + "where ReservationDate between ? and ?\n"
                + "and Schedule.DoctorId = ? and Reservation.Status = 'Waitting'";
        try {
            PreparedStatement st = connection.prepareStatement(sql);
            st.setString(1, startdate);
            st.setString(2, enddate);
            st.setInt(3, staffid);
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                Reservation re = new Reservation();
                re.setReservationId(rs.getInt(1));
                re.setCustomerName(rs.getString(2));
                re.setServiceName(rs.getString(3));
                re.setPatientName(rs.getString(4));                
                re.setCosts(rs.getInt(5));
                re.setReservedDate(rs.getDate(6));
                re.setWorkday(rs.getDate(7));
                re.setSlot(rs.getInt(8));
                re.setStatus(rs.getString(9));
                list.add(re);
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        return list;
    }
    
    public List<Reservation> filterbyDateHis(int staffid, String startdate, String enddate) {
        List<Reservation> list = new ArrayList<>();
        String sql = "select Reservation.ID, UserAccount.Name, Service.ServiceName, ChildrenInformation.Name, Service.Price, ReservationDate, Schedule.WorkDay, Schedule.Slot, Reservation.Status\n"
                + "from Reservation\n"
                + "INNER JOIN Schedule ON Schedule.Id = Reservation.ScheduleId\n"
                + "INNER JOIN EmployeeAccount ON Schedule.DoctorId = EmployeeAccount.ID\n"
                + "INNER JOIN UserAccount ON Reservation.CustomerID = UserAccount.ID\n"
                + "INNER JOIN Service ON Schedule.ServiceId = Service.ServiceID\n"
                + "INNER JOIN ChildrenInformation ON Reservation.ID = ChildrenInformation.ReservationID\n"
                + "where ReservationDate between ? and ?\n"
                + "and Schedule.DoctorId = ? and Reservation.Status = 'Completed'";
        try {
            PreparedStatement st = connection.prepareStatement(sql);
            st.setString(1, startdate);
            st.setString(2, enddate);
            st.setInt(3, staffid);
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                Reservation re = new Reservation();
                re.setReservationId(rs.getInt(1));
                re.setCustomerName(rs.getString(2));
                re.setServiceName(rs.getString(3));
                re.setPatientName(rs.getString(4));                
                re.setCosts(rs.getInt(5));
                re.setReservedDate(rs.getDate(6));
                re.setWorkday(rs.getDate(7));
                re.setSlot(rs.getInt(8));
                re.setStatus(rs.getString(9));
                list.add(re);
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        return list;
    }