ExaminationDAO
unknown
plain_text
24 days ago
13 kB
5
Indexable
Never
/* * Click nbfs://nbhost/SystemFileSystem/Templates/Licenses/license-default.txt to change this license * Click nbfs://nbhost/SystemFileSystem/Templates/Classes/Class.java to edit this template */ package DAO; import Model.Reservation; import Model.ReservationHistory; import Model.ServiceList; import com.oracle.wls.shaded.org.apache.bcel.generic.AALOAD; import java.sql.Connection; import java.sql.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.time.LocalDateTime; import java.util.ArrayList; import java.util.List; /** * * @author ADMIN */ public class ExaminationDAO extends DBContext { public void AddReservationToHistory(int ReId, int CusId, int SerId, int StaffId, String ReDate) { String Sql = "INSERT INTO ReservationHistory(ReservationID, CustomerID, ServiceID, StaffID, ReservationDate, Status) VALUES (?,?,?,?,?,0)"; try { PreparedStatement st = connection.prepareStatement(Sql); st.setInt(1, ReId); st.setInt(2, CusId); st.setInt(3, SerId); st.setInt(4, StaffId); st.setString(5, ReDate); st.executeUpdate(); } catch (SQLException e) { System.out.println(e); } } public List<ReservationHistory> getReservationInHistory() { List<ReservationHistory> list = new ArrayList<>(); String sql = "select ReservationHistory.ID, ReservationHistory.ReservationID, UserAccount.Name, Service.ServiceName, Service.Price, EmployeeAccount.Name, \n" + "ReservationHistory.ReservationDate, ReservationHistory.FinishedDate, ReservationHistory.Status, ReservationHistory.Note\n" + "from Reservation\n" + "inner join UserAccount on Reservation.CustomerID = UserAccount.ID\n" + "inner join Service on Reservation.ServiceID = Service.ServiceID\n" + "inner join EmployeeAccount on Reservation.StaffID = EmployeeAccount.ID\n" + "inner join ChildrenInformation on Reservation.ID = ChildrenInformation.ReservationID\n" + "inner join ReservationHistory on ReservationHistory.ReservationID = Reservation.ID"; try { PreparedStatement st = connection.prepareStatement(sql); ResultSet rs = st.executeQuery(); while (rs.next()) { ReservationHistory re = new ReservationHistory(); re.setId(rs.getInt(1)); re.setReid(rs.getInt(2)); re.setCusName(rs.getString(3)); re.setSerName(rs.getString(4)); re.setPrice(rs.getDouble(5)); re.setStaffName(rs.getString(6)); re.setReDate(rs.getDate(7)); re.setReFinDate(rs.getDate(8)); re.setStatus(rs.getBoolean(9)); re.setNote(rs.getString(10)); list.add(re); } } catch (Exception e) { System.out.println(e); } return list; } public List<Reservation> getReservation(int staffid, int cusid) { List<Reservation> list = new ArrayList<>(); String sql = "select Schedule.WorkDay, Schedule.Slot, Service.ServiceName, Reservation.MedicalPrescription, Reservation.ID \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 Schedule.DoctorId = ? and CustomerID = ? and Reservation.Status='Completed'"; try { PreparedStatement st = connection.prepareStatement(sql); st.setInt(1, staffid); st.setInt(2, cusid); ResultSet rs = st.executeQuery(); while (rs.next()) { Reservation re = new Reservation(); re.setWorkday(rs.getDate(1)); re.setSlot(rs.getInt(2)); re.setServiceName(rs.getString(3)); re.setMedicalPrep(rs.getString(4)); re.setReservationId(rs.getInt(5)); list.add(re); } } catch (Exception e) { System.out.println(e); } return list; } public Reservation getReservtionDetailById(int reId) { String sql = "select Note, MedicalPrescription, Reservation.ID, UserAccount.Name, UserAccount.Email, UserAccount.Mobile, Reservation.ReservationDate, Service.Price, EmployeeAccount.Name\n" + ",Schedule.WorkDay, Schedule.Slot, Reservation.Status, Service.ServiceName\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 Reservation.ID = ?"; try { PreparedStatement st = connection.prepareStatement(sql); st.setInt(1, reId); ResultSet rs = st.executeQuery(); while (rs.next()) { Reservation re = new Reservation(); re.setNote(rs.getString(1)); re.setMedicalPrep(rs.getString(2)); re.setReservationId(rs.getInt(3)); re.setCustomerName(rs.getString(4)); re.setCustomerEmail(rs.getString(5)); re.setCustomerMobile(rs.getString(6)); re.setReservedDate(rs.getDate(7)); re.setCosts(rs.getInt(8)); re.setDoctorName(rs.getString(9)); re.setWorkday(rs.getDate(10)); re.setSlot(rs.getInt(11)); re.setStatus(rs.getString(12)); re.setServiceName(rs.getString(13)); return re; } } catch (Exception e) { System.out.println(e); } return null; } public Reservation getInfCusDetailById(int reId) { String sql = "select UserAccount.Name, UserAccount.Gender, UserAccount.Email, UserAccount.Mobile, UserAccount.Address\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 Reservation.ID = ?"; try { PreparedStatement st = connection.prepareStatement(sql); st.setInt(1, reId); ResultSet rs = st.executeQuery(); while (rs.next()) { Reservation re = new Reservation(); re.setCustomerName(rs.getString(1)); re.setCustomerGender(rs.getBoolean(2)); re.setCustomerEmail(rs.getString(3)); re.setCustomerMobile(rs.getString(4)); re.setCustomerAddress(rs.getString(5)); return re; } } catch (Exception e) { System.out.println(e); } return null; } public Reservation getInfPatDetailById(int reId) { String sql = "select ChildrenInformation.Name, ChildrenInformation.Gender, ChildrenInformation.DateOfBirth, ChildrenInformation.Relationship\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 Reservation.ID = ?"; try { PreparedStatement st = connection.prepareStatement(sql); st.setInt(1, reId); ResultSet rs = st.executeQuery(); while (rs.next()) { Reservation re = new Reservation(); re.setPatientName(rs.getString(1)); re.setPatientGender(rs.getBoolean(2)); re.setPatientDoB(rs.getDate(3)); re.setPatientRelation(rs.getString(4)); return re; } } catch (Exception e) { System.out.println(e); } return null; } public void updateMedicPrescription(String medicprep, int id) { String sql = "update ReservationHistory\n" + "set MedicalPrescription = ?\n" + "where ID = ?"; try { PreparedStatement st = connection.prepareStatement(sql); st.setString(1, medicprep); st.setInt(2, id); st.executeUpdate(); } catch (SQLException e) { System.out.println(e); } } public void deleteReservation(int id) { String sql = "DELETE FROM Reservation\n" + "WHERE ID = ?"; try { PreparedStatement st = connection.prepareStatement(sql); st.setInt(1, id); st.executeUpdate(); } catch (SQLException e) { System.out.println(e); } } public void updateReservation(String conclude, String medicprep, int reId) { String sql = "Update Reservation\n" + "set Note = ?\n" + ", MedicalPrescription = ?\n" + ", Status = 'Completed'\n" + "where Reservation.ID = ?"; try { PreparedStatement st = connection.prepareStatement(sql); st.setString(1, conclude); st.setString(2, medicprep); st.setInt(3, reId); st.executeUpdate(); } catch (SQLException e) { System.out.println(e); } } public List<Reservation> filterbyDate(int staffid, String startdate, String enddate) { List<Reservation> list = new ArrayList<>(); String sql = "select Reservation.ID, UserAccount.Name, ChildrenInformation.Name, Service.ServiceName, 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 = ?"; 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.setPatientName(rs.getString(3)); re.setServiceName(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 static void main(String[] args) { ExaminationDAO dao = new ExaminationDAO(); System.out.println(); } }