ExaminationDAO
unknown
plain_text
a year ago
13 kB
6
Indexable
/*
* 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();
}
}