private void saveToDb() {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(UPDATE_WARE);
System.out.println(UPDATE_WARE);
pstmt.setString(1, StringUtils.toChinese(this.Pname));
pstmt.setString(2, StringUtils.toChinese(this.Pmodel));
pstmt.setString(3, StringUtils.toChinese(this.Pcost));
pstmt.setString(4, StringUtils.toChinese(this.Pheft));
pstmt.setString(5, StringUtils.toChinese(this.Pfacturer));
pstmt.setString(6, StringUtils.toChinese(this.Pnote));
pstmt.setInt(7, this.Status);
pstmt.setInt(8, this.Id);
pstmt.executeUpdate();
} catch (SQLException sqle) {
System.err.println("错误位置: DbWare.java:saveToDb(): " + sqle);
sqle.printStackTrace();
} finally {
try {
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public int channelcountbydate(String date, int did, String time) {
int channelcount = 0;
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT * FROM `channelling` where Date = ? AND Doctor_Id =? AND Time =?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, date);
pst.setInt(2, did);
pst.setString(3, time);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
while (rs.next()) {
channelcount++;
}
} catch (SQLException ex) {
Logger.getLogger(PatientDataAccessManager.class.getName()).log(Level.SEVERE, null, ex);
}
return channelcount;
}
public String getPatient_RealName(int pId) {
String fName;
String lName;
String Name = "null";
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT * FROM `patient` WHERE Patient_Id =?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, pId);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
while (rs.next()) {
fName = rs.getString("First_name");
lName = rs.getString("Last_name");
Name = fName + " " + lName;
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return Name;
}
public ArrayList<String> getChannelIDList(String date, int did, String time) {
ArrayList<String> channel = new ArrayList<>();
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT * FROM `channelling` WHERE Date =? AND Time =? AND Doctor_Id= ?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, date);
pst.setString(2, time);
pst.setInt(3, did);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
int i = 0;
while (rs.next()) {
String patientId = rs.getString("Patient_Id");
channel.add(patientId);
// System.out.println("Channel Number "+channelNum+" Patient Id "+patientId);
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return channel;
}
public boolean checktheChannel(int pId, String date) {
boolean status = true;
int count = 0;
try {
st = dbCon.getInstance().createStatement();
String query = "Select * FROM `channelling` WHERE Date = ? AND Patient_Id =? ";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, date);
pst.setInt(2, pId);
try {
pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
count = 0;
while (rs.next()) {
count++;
}
} catch (SQLException ex) {
Logger.getLogger(PatientDataAccessManager.class.getName()).log(Level.SEVERE, null, ex);
}
if (count == 0) {
status = false;
}
return status;
}
public ArrayList<String> getNaturalJoinList(String date) {
ArrayList<String> channel = new ArrayList<>();
try {
st = dbCon.getInstance().createStatement();
String query =
"SELECT * FROM `channelling` NATURAL JOIN `patient` WHERE Date =? ORDER by Time";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, date);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
int i = 0;
while (rs.next()) {
String patientRealname = rs.getString("First_name"); // 0
channel.add(patientRealname);
String patientDOB = rs.getString("Date_of_birth"); // 1
channel.add(patientDOB);
String patientAddress = rs.getString("Address"); // 2
channel.add(patientAddress);
String patientId = rs.getString("Patient_Id"); // 3
channel.add(patientId);
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return channel;
}
public int getChannelNumber(int pId, String date, int did, String time) {
int channelNum = -1;
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT * FROM `channelling` WHERE Date =? AND Doctor_Id =? ORDER by Time";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, date);
pst.setInt(2, did);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
count = 1;
while (rs.next()) {
if (Integer.parseInt(rs.getString("Patient_Id")) != pId) {
count++;
}
if (Integer.parseInt(rs.getString("Patient_Id")) == pId) {
channelNum = count;
}
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return channelNum;
}
public ArrayList<String> getupdatedPrescription(String date, int did) {
ArrayList<String> channel = new ArrayList<>();
try {
st = dbCon.getInstance().createStatement();
String query =
"SELECT * FROM `prescription` WHERE Date =? AND Doctor_Id =? ORDER by Time DESC";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, date);
pst.setInt(2, did);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
int i = 0;
while (rs.next()) {
String patientId = rs.getString("Patient_Id"); // 1,3,5,7,9
channel.add(patientId);
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return channel;
}
public void saveData(
String first,
String last,
String gender,
String mobile,
String BDate,
String address,
String user,
String pass,
String efirst,
String elast,
String emobile) {
try {
st = dbCon.getInstance().createStatement();
String query =
"INSERT INTO `patient` (`Username`, `Password`,`First_name`,`Last_name`,`Gender`,`Mobile_number`,`Date_of_birth`,`Address`, `Emerg_First_name`, `Emerg_Last_name`, `Emerg_contact_number`) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, user);
pst.setString(2, pass);
pst.setString(3, first);
pst.setString(4, last);
pst.setString(5, gender);
pst.setString(6, mobile);
pst.setString(7, BDate);
pst.setString(8, address);
pst.setString(9, efirst);
pst.setString(10, elast);
pst.setString(11, emobile);
try {
pst.executeUpdate();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// st.executeUpdate(query);
System.out.println("Updated");
String query2 = "SELECT * FROM `patient`";
rs = st.executeQuery(query2);
patientcount = 0;
while (rs.next()) {
patientcount++;
}
JOptionPane.showMessageDialog(new JDialog(), "Patient Account Created! Id =" + patientcount);
} catch (SQLException ex) {
Logger.getLogger(PatientDataAccessManager.class.getName()).log(Level.SEVERE, null, ex);
System.out.println("vv");
}
}
public ArrayList<String> getAllData(String name) {
String tname = '%' + name + '%';
System.out.println(tname);
ArrayList<String> details = new ArrayList<>();
try {
String query = "SELECT * FROM `patient` WHERE First_name LIKE ? ORDER BY First_name";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, tname);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
System.out.println("Records from the database");
while (rs.next()) {
details.add(rs.getString("First_name")); // 0
details.add(rs.getString("Date_of_birth")); // 1
details.add(rs.getString("Address")); // 2
details.add(rs.getString("Patient_Id")); // 2
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return details;
}
private void insertIntoDb() {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(INSERT_WARE);
pstmt.setInt(1, this.Id);
pstmt.setString(2, StringUtils.toChinese(this.Pname));
pstmt.setString(3, StringUtils.toChinese(this.Pmodel));
pstmt.setString(4, StringUtils.toChinese(this.Pcost));
pstmt.setString(5, StringUtils.toChinese(this.Pheft));
pstmt.setString(6, StringUtils.toChinese(this.Pfacturer));
pstmt.setString(7, StringUtils.toChinese(this.Pnote));
pstmt.setString(8, StringUtils.toChinese(this.Createdate));
pstmt.setInt(9, this.Status);
pstmt.executeUpdate();
} catch (SQLException sqle) {
System.err.println("错误位置: Dbware:insertIntoDb()-" + sqle);
sqle.printStackTrace();
} finally {
try {
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
private void DELToDb() {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(Del_ware);
System.out.println(Del_ware);
pstmt.setInt(1, this.Status);
pstmt.setInt(2, this.Id);
pstmt.executeUpdate();
} catch (SQLException sqle) {
System.err.println("错误位置: DbShop.java:DELToDb(): " + sqle);
sqle.printStackTrace();
} finally {
try {
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public ArrayList<String> getAllData(int pId) {
ArrayList<String> details = new ArrayList<>();
String sql = "SELECT * FROM `patient` WHERE Patient_Id =?";
try {
pst = dbCon.getInstance().prepareStatement(sql);
pst.setInt(1, pId);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("yako Syntax awlak");
}
while (rs.next()) {
details.add(rs.getString("First_name")); // 0
details.add(rs.getString("Last_name")); // 1
details.add(rs.getString("Mobile_number")); // 2
details.add(rs.getString("Date_of_birth")); // 3
details.add(rs.getString("Address")); // 4
details.add(rs.getString("Emerg_First_name")); // 5
details.add(rs.getString("Emerg_Last_name")); // 6
details.add(rs.getString("Emerg_contact_number")); // 7
details.add(rs.getString("Gender")); // 8
}
} catch (SQLException | NumberFormatException e) {
JOptionPane.showMessageDialog(null, e);
}
return details;
}
public void cancellastappointment(int pId, int did) {
try {
st = dbCon.getInstance().createStatement();
String query =
"DELETE FROM `channelling` WHERE Patient_Id =? AND Doctor_Id =? Order by Date DESC, Time DESC Limit 1";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, pId);
pst.setInt(2, did);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
st.executeUpdate(query);
System.out.println("Records from the database");
} catch (Exception ex) {
System.out.println("Error " + ex);
}
}
public void deleteChannel(int pId, String date) {
try {
st = dbCon.getInstance().createStatement();
String query = "DELETE FROM `channelling` WHERE Patient_Id =? AND Date='?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, pId);
pst.setString(2, date);
try {
pst.executeUpdate();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// st.executeUpdate(query);
System.out.println("Deleted");
} catch (SQLException ex) {
Logger.getLogger(PatientDataAccessManager.class.getName()).log(Level.SEVERE, null, ex);
}
}
public void saveData(int id, String name, String paswrd) {
try {
st = dbCon.getInstance().createStatement();
String query =
"INSERT INTO `patient` (`Patient_Id`, `Pasword`, `UserName`, `Real_Name`, `First_Name`, `Last_Name`, `Gender`, `Mobile_No`, `DOB`, `Address`, `Emerg_First_Name`, `Emerg_Last_Name`, `Emerg_Gender`, `Emerg_Mobile_No`, `Emerg_Address`) VALUES (?,?,?, '', '', '', '', NULL, NULL, '', '', '', '', NULL, '')";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, id);
pst.setString(2, paswrd);
pst.setString(3, name);
try {
pst.executeUpdate();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// st.executeUpdate(query);
System.out.println("Updated");
} catch (SQLException ex) {
Logger.getLogger(PatientDataAccessManager.class.getName()).log(Level.SEVERE, null, ex);
}
}
public String getPatientGender(int pId) {
String pGender = null;
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT Gender FROM `patient` WHERE Patient_Id =?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, pId);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
while (rs.next()) {
pGender = rs.getString("Gender");
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return pGender;
}
public int getPatientMobile(int pId) {
int pMob = -1;
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT Mobile_number FROM `patient` WHERE Patient_Id =?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, pId);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
while (rs.next()) {
pMob = Integer.parseInt(rs.getString("Mobile_number"));
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return pMob;
}
public int getPatientCount() {
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT * FROM `patient`";
rs = st.executeQuery(query);
patientcount = 0;
while (rs.next()) {
patientcount++;
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return patientcount;
}
protected static void clear() {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(CLEAR_WARE);
pstmt.executeUpdate();
} catch (SQLException sqle) {
System.err.println("SQLException in DbTChatRooms.java:clearTChatRooms(): " + sqle);
sqle.printStackTrace();
} finally {
try {
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public ArrayList<String> patientList(String name, int did) {
String tname = '%' + name + '%';
System.out.println(tname);
ArrayList<String> details = new ArrayList<>();
try {
st = dbCon.getInstance().createStatement();
String query =
"SELECT * FROM `patient` WHERE Patient_Id in (Select Patient_Id from channelling where First_name LIKE '"
+ tname
+ "' AND Doctor_id ="
+ did
+ ")";
// pst = dbCon.getInstance().prepareStatement(query);
// pst.setString(1, tname);
// pst.setInt(2, did);
// try {
// pst.executeQuery();
// } catch (MySQLSyntaxErrorException e) {
// System.out.println("Yako Syntax awlak");
// }
rs = st.executeQuery(query);
System.out.println("Records from the database nn");
while (rs.next()) {
details.add(rs.getString("Patient_Id")); // 0
details.add(rs.getString("First_name")); // 1
details.add(rs.getString("Last_name")); // 2
details.add(rs.getString("Gender")); // 3
details.add(rs.getString("Address")); // 4
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return details;
}
private void loadFromDb() throws WareNotFoundException {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(LOAD_WARE_BY_ID);
pstmt.setInt(1, Id);
ResultSet rs = pstmt.executeQuery();
if (!rs.next()) {
throw new WareNotFoundException("从数据表[ware]中读取用户数据失败,欲读取的用户ID:[ " + Id + "]!");
}
this.Id = rs.getInt("Id");
this.Pname = rs.getString("Pname");
this.Pmodel = rs.getString("Pmodel");
this.Pcost = rs.getString("Pcost");
this.Pheft = rs.getString("Pheft");
this.Pfacturer = rs.getString("Pfacturer");
this.Pnote = rs.getString("Pnote");
this.Createdate = rs.getString("Createdate");
this.Status = rs.getInt("Status");
} catch (SQLException sqle) {
throw new WareNotFoundException("从数据表[WARE]中读取用户数据失败,欲读取的用户ID:[ " + Id + "]!");
} finally {
try {
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public int channelDoc(int pId, String date, String time, int did) {
int returnval = 0;
int pcount = -1;
int enetval = -1;
try {
st = dbCon.getInstance().createStatement();
String query1 =
"Select * from `channelling` where Patient_Id =? AND Date = ? AND Doctor_Id =?";
String query2 =
"Select max(channel_Num) from `channelling` where Date = "
+ "'"
+ date
+ "' AND Time = "
+ "'"
+ time
+ "'AND Doctor_Id ="
+ did
+ "";
pst = dbCon.getInstance().prepareStatement(query1);
pst.setInt(1, pId);
pst.setString(2, date);
pst.setInt(3, pId);
try {
pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query1);
if (rs.next()) {
JOptionPane.showMessageDialog(
new JDialog(),
"This patient Already have an appointment on this day to the selected Doctor");
returnval = -1;
} else {
rs = st.executeQuery(query2);
while (rs.next()) {
pcount = rs.getInt("max(channel_Num)");
System.out.println("danata" + pcount);
}
enetval = pcount + 1;
String query =
"INSERT INTO `channelling` (`Patient_Id`,`channel_Num`, `Date`,`Time`,`Doctor_Id`) VALUES ('"
+ pId
+ "','"
+ enetval
+ "', '"
+ date
+ "','"
+ time
+ "','"
+ did
+ "')";
st.executeUpdate(query);
System.out.println("Inserted");
returnval = pcount + 1;
}
} catch (SQLException ex) {
Logger.getLogger(PatientDataAccessManager.class.getName()).log(Level.SEVERE, null, ex);
}
return returnval;
}
/** @author Pravinda Perera */
public class PatientDataAccessManager {
private static DbConnectionManager dbCon = DbConnectionManager.getInstance();
private Statement st;
private ResultSet rs;
private PreparedStatement pst = null;
private static PatientDataAccessManager singleton = null;
private int patientcount;
private int count;
private String firstname;
private String lastname;
private String MobileNo;
private String DOB;
private String year;
private String month;
private String date;
private String Address;
private String Efirst;
private String ELast;
private String EMobilNo;
private String username;
private String password;
public static synchronized PatientDataAccessManager getConnection(DbConnectionManager dbCon) {
if (singleton == null) {
singleton = new PatientDataAccessManager(dbCon);
return singleton;
} else {
return singleton;
}
}
public static synchronized PatientDataAccessManager getConnection() {
if (singleton == null) {
singleton = new PatientDataAccessManager(dbCon);
return singleton;
} else {
return singleton;
}
}
private PatientDataAccessManager(DbConnectionManager dbCon) {
this.dbCon = dbCon;
}
public ArrayList<String> getAllData(int pId) {
ArrayList<String> details = new ArrayList<>();
String sql = "SELECT * FROM `patient` WHERE Patient_Id =?";
try {
pst = dbCon.getInstance().prepareStatement(sql);
pst.setInt(1, pId);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("yako Syntax awlak");
}
while (rs.next()) {
details.add(rs.getString("First_name")); // 0
details.add(rs.getString("Last_name")); // 1
details.add(rs.getString("Mobile_number")); // 2
details.add(rs.getString("Date_of_birth")); // 3
details.add(rs.getString("Address")); // 4
details.add(rs.getString("Emerg_First_name")); // 5
details.add(rs.getString("Emerg_Last_name")); // 6
details.add(rs.getString("Emerg_contact_number")); // 7
details.add(rs.getString("Gender")); // 8
}
} catch (SQLException | NumberFormatException e) {
JOptionPane.showMessageDialog(null, e);
}
return details;
}
public ArrayList<String> getAllData(String name) {
String tname = '%' + name + '%';
System.out.println(tname);
ArrayList<String> details = new ArrayList<>();
try {
String query = "SELECT * FROM `patient` WHERE First_name LIKE ? ORDER BY First_name";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, tname);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
System.out.println("Records from the database");
while (rs.next()) {
details.add(rs.getString("First_name")); // 0
details.add(rs.getString("Date_of_birth")); // 1
details.add(rs.getString("Address")); // 2
details.add(rs.getString("Patient_Id")); // 2
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return details;
}
public int getPatientCount() {
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT * FROM `patient`";
rs = st.executeQuery(query);
patientcount = 0;
while (rs.next()) {
patientcount++;
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return patientcount;
}
public void saveData(int id, String name, String paswrd) {
try {
st = dbCon.getInstance().createStatement();
String query =
"INSERT INTO `patient` (`Patient_Id`, `Pasword`, `UserName`, `Real_Name`, `First_Name`, `Last_Name`, `Gender`, `Mobile_No`, `DOB`, `Address`, `Emerg_First_Name`, `Emerg_Last_Name`, `Emerg_Gender`, `Emerg_Mobile_No`, `Emerg_Address`) VALUES (?,?,?, '', '', '', '', NULL, NULL, '', '', '', '', NULL, '')";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, id);
pst.setString(2, paswrd);
pst.setString(3, name);
try {
pst.executeUpdate();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// st.executeUpdate(query);
System.out.println("Updated");
} catch (SQLException ex) {
Logger.getLogger(PatientDataAccessManager.class.getName()).log(Level.SEVERE, null, ex);
}
}
public void saveData(
String first,
String last,
String gender,
String mobile,
String BDate,
String address,
String user,
String pass,
String efirst,
String elast,
String emobile) {
try {
st = dbCon.getInstance().createStatement();
String query =
"INSERT INTO `patient` (`Username`, `Password`,`First_name`,`Last_name`,`Gender`,`Mobile_number`,`Date_of_birth`,`Address`, `Emerg_First_name`, `Emerg_Last_name`, `Emerg_contact_number`) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, user);
pst.setString(2, pass);
pst.setString(3, first);
pst.setString(4, last);
pst.setString(5, gender);
pst.setString(6, mobile);
pst.setString(7, BDate);
pst.setString(8, address);
pst.setString(9, efirst);
pst.setString(10, elast);
pst.setString(11, emobile);
try {
pst.executeUpdate();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// st.executeUpdate(query);
System.out.println("Updated");
String query2 = "SELECT * FROM `patient`";
rs = st.executeQuery(query2);
patientcount = 0;
while (rs.next()) {
patientcount++;
}
JOptionPane.showMessageDialog(new JDialog(), "Patient Account Created! Id =" + patientcount);
} catch (SQLException ex) {
Logger.getLogger(PatientDataAccessManager.class.getName()).log(Level.SEVERE, null, ex);
System.out.println("vv");
}
}
public void updateDataa(
int PID,
String first,
String last,
String gender,
String mobile,
String BDate,
String address) {
try {
st = dbCon.getInstance().createStatement();
// String query = " `patients` (`Patient_Id`,`Pasword`,
// `UserName`,`Real_Name`,`First_Name`,`Last_Name`,`Gender`,`Mobile_No`,`DOB`,`Address`)
// VALUES ('" + PID + "','" + pass + "', '" + user + "', '" + fullname + "', '" + first + "',
// '" + last + "', '" + gender + "', '" + mobile + "', '" + BDate + "', '" + address + "')";
// String query = "UPDATE `patient` SET
// `First_name`=?,`Last_name`=?,`Gender`=?,`Mobile_number`=?,`Date_of_birth`=?,`Address`=?
// WHERE Patient_Id =?";
String query =
"UPDATE `patient` SET `First_name`='"
+ first
+ "',`Last_name`='"
+ last
+ "',`Gender`='"
+ gender
+ "',`Mobile_number`='"
+ mobile
+ "',`Date_of_birth`='"
+ BDate
+ "',`Address`='"
+ address
+ "' WHERE Patient_Id ='"
+ PID
+ "'";
// pst = dbCon.getInstance().prepareStatement(query);
// pst.setString(1, first);
// pst.setString(2, last);
// pst.setString(3, gender);
// pst.setString(4, mobile);
// pst.setString(5, BDate);
// pst.setString(6, address);
// pst.setInt(7, PID);
// try {
// pst.executeUpdate();
// } catch (MySQLSyntaxErrorException e) {
// System.out.println("Yako Syntax awlak");
// }
st.executeUpdate(query);
System.out.println("Updated");
String query2 = "SELECT * FROM `patient`";
rs = st.executeQuery(query2);
patientcount = 0;
while (rs.next()) {
patientcount++;
}
JOptionPane.showMessageDialog(new JDialog(), "Patient Account Updated! Id =" + PID);
} catch (SQLException ex) {
Logger.getLogger(PatientDataAccessManager.class.getName()).log(Level.SEVERE, null, ex);
}
}
public String getPatient_UserName(int pId) {
String pName = new String();
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT UserName FROM `patients` WHERE Patient_Id =?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, pId);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
while (rs.next()) {
String name = rs.getString("UserName");
pName = name;
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return pName;
}
public String getDOB(int pId) {
String pDOB = new String();
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT Date_of_birth FROM `patient` WHERE Patient_Id =?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, pId);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
while (rs.next()) {
pDOB = rs.getString("Date_of_birth");
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return pDOB;
}
public int channelcountbydate(String date, int did, String time) {
int channelcount = 0;
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT * FROM `channelling` where Date = ? AND Doctor_Id =? AND Time =?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, date);
pst.setInt(2, did);
pst.setString(3, time);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
while (rs.next()) {
channelcount++;
}
} catch (SQLException ex) {
Logger.getLogger(PatientDataAccessManager.class.getName()).log(Level.SEVERE, null, ex);
}
return channelcount;
}
public String getPatient_RealName(int pId) {
String fName;
String lName;
String Name = "null";
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT * FROM `patient` WHERE Patient_Id =?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, pId);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
while (rs.next()) {
fName = rs.getString("First_name");
lName = rs.getString("Last_name");
Name = fName + " " + lName;
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return Name;
}
public String getPatient_lastName(int pId) {
String LName = new String();
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT * FROM `patients` WHERE Patient_Id =?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, pId);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
while (rs.next()) {
String name = rs.getString("Last_Name");
LName = name;
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return LName;
}
public String getPatient_Address(int pId) {
String pAddress = null;
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT Address FROM `patient` WHERE Patient_Id =?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, pId);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
while (rs.next()) {
pAddress = rs.getString("Address");
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return pAddress;
}
public int getPatientMobile(int pId) {
int pMob = -1;
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT Mobile_number FROM `patient` WHERE Patient_Id =?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, pId);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
while (rs.next()) {
pMob = Integer.parseInt(rs.getString("Mobile_number"));
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return pMob;
}
public String getPatientGender(int pId) {
String pGender = null;
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT Gender FROM `patient` WHERE Patient_Id =?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, pId);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
while (rs.next()) {
pGender = rs.getString("Gender");
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return pGender;
}
public ArrayList<String> getChannelIDList(String date, int did, String time) {
ArrayList<String> channel = new ArrayList<>();
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT * FROM `channelling` WHERE Date =? AND Time =? AND Doctor_Id= ?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, date);
pst.setString(2, time);
pst.setInt(3, did);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
int i = 0;
while (rs.next()) {
String patientId = rs.getString("Patient_Id");
channel.add(patientId);
// System.out.println("Channel Number "+channelNum+" Patient Id "+patientId);
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return channel;
}
public ArrayList<String> getNaturalJoinList(String date) {
ArrayList<String> channel = new ArrayList<>();
try {
st = dbCon.getInstance().createStatement();
String query =
"SELECT * FROM `channelling` NATURAL JOIN `patient` WHERE Date =? ORDER by Time";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, date);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
int i = 0;
while (rs.next()) {
String patientRealname = rs.getString("First_name"); // 0
channel.add(patientRealname);
String patientDOB = rs.getString("Date_of_birth"); // 1
channel.add(patientDOB);
String patientAddress = rs.getString("Address"); // 2
channel.add(patientAddress);
String patientId = rs.getString("Patient_Id"); // 3
channel.add(patientId);
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return channel;
}
public ArrayList<String> getupdatedPrescription(String date, int did) {
ArrayList<String> channel = new ArrayList<>();
try {
st = dbCon.getInstance().createStatement();
String query =
"SELECT * FROM `prescription` WHERE Date =? AND Doctor_Id =? ORDER by Time DESC";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, date);
pst.setInt(2, did);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
int i = 0;
while (rs.next()) {
String patientId = rs.getString("Patient_Id"); // 1,3,5,7,9
channel.add(patientId);
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return channel;
}
public int getChannelNumber(int pId, String date, int did, String time) {
int channelNum = -1;
try {
st = dbCon.getInstance().createStatement();
String query = "SELECT * FROM `channelling` WHERE Date =? AND Doctor_Id =? ORDER by Time";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, date);
pst.setInt(2, did);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
count = 1;
while (rs.next()) {
if (Integer.parseInt(rs.getString("Patient_Id")) != pId) {
count++;
}
if (Integer.parseInt(rs.getString("Patient_Id")) == pId) {
channelNum = count;
}
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return channelNum;
}
public void cancellastappointment(int pId, int did) {
try {
st = dbCon.getInstance().createStatement();
String query =
"DELETE FROM `channelling` WHERE Patient_Id =? AND Doctor_Id =? Order by Date DESC, Time DESC Limit 1";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, pId);
pst.setInt(2, did);
try {
rs = pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
st.executeUpdate(query);
System.out.println("Records from the database");
} catch (Exception ex) {
System.out.println("Error " + ex);
}
}
public int channelDoc(int pId, String date, String time, int did) {
int returnval = 0;
int pcount = -1;
int enetval = -1;
try {
st = dbCon.getInstance().createStatement();
String query1 =
"Select * from `channelling` where Patient_Id =? AND Date = ? AND Doctor_Id =?";
String query2 =
"Select max(channel_Num) from `channelling` where Date = "
+ "'"
+ date
+ "' AND Time = "
+ "'"
+ time
+ "'AND Doctor_Id ="
+ did
+ "";
pst = dbCon.getInstance().prepareStatement(query1);
pst.setInt(1, pId);
pst.setString(2, date);
pst.setInt(3, pId);
try {
pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query1);
if (rs.next()) {
JOptionPane.showMessageDialog(
new JDialog(),
"This patient Already have an appointment on this day to the selected Doctor");
returnval = -1;
} else {
rs = st.executeQuery(query2);
while (rs.next()) {
pcount = rs.getInt("max(channel_Num)");
System.out.println("danata" + pcount);
}
enetval = pcount + 1;
String query =
"INSERT INTO `channelling` (`Patient_Id`,`channel_Num`, `Date`,`Time`,`Doctor_Id`) VALUES ('"
+ pId
+ "','"
+ enetval
+ "', '"
+ date
+ "','"
+ time
+ "','"
+ did
+ "')";
st.executeUpdate(query);
System.out.println("Inserted");
returnval = pcount + 1;
}
} catch (SQLException ex) {
Logger.getLogger(PatientDataAccessManager.class.getName()).log(Level.SEVERE, null, ex);
}
return returnval;
}
public void deleteChannel(int pId, String date) {
try {
st = dbCon.getInstance().createStatement();
String query = "DELETE FROM `channelling` WHERE Patient_Id =? AND Date='?";
pst = dbCon.getInstance().prepareStatement(query);
pst.setInt(1, pId);
pst.setString(2, date);
try {
pst.executeUpdate();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// st.executeUpdate(query);
System.out.println("Deleted");
} catch (SQLException ex) {
Logger.getLogger(PatientDataAccessManager.class.getName()).log(Level.SEVERE, null, ex);
}
}
public boolean checktheChannel(int pId, String date) {
boolean status = true;
int count = 0;
try {
st = dbCon.getInstance().createStatement();
String query = "Select * FROM `channelling` WHERE Date = ? AND Patient_Id =? ";
pst = dbCon.getInstance().prepareStatement(query);
pst.setString(1, date);
pst.setInt(2, pId);
try {
pst.executeQuery();
} catch (MySQLSyntaxErrorException e) {
System.out.println("Yako Syntax awlak");
}
// rs = st.executeQuery(query);
System.out.println("Records from the database");
count = 0;
while (rs.next()) {
count++;
}
} catch (SQLException ex) {
Logger.getLogger(PatientDataAccessManager.class.getName()).log(Level.SEVERE, null, ex);
}
if (count == 0) {
status = false;
}
return status;
}
public ArrayList<String> patientList(String name, int did) {
String tname = '%' + name + '%';
System.out.println(tname);
ArrayList<String> details = new ArrayList<>();
try {
st = dbCon.getInstance().createStatement();
String query =
"SELECT * FROM `patient` WHERE Patient_Id in (Select Patient_Id from channelling where First_name LIKE '"
+ tname
+ "' AND Doctor_id ="
+ did
+ ")";
// pst = dbCon.getInstance().prepareStatement(query);
// pst.setString(1, tname);
// pst.setInt(2, did);
// try {
// pst.executeQuery();
// } catch (MySQLSyntaxErrorException e) {
// System.out.println("Yako Syntax awlak");
// }
rs = st.executeQuery(query);
System.out.println("Records from the database nn");
while (rs.next()) {
details.add(rs.getString("Patient_Id")); // 0
details.add(rs.getString("First_name")); // 1
details.add(rs.getString("Last_name")); // 2
details.add(rs.getString("Gender")); // 3
details.add(rs.getString("Address")); // 4
}
} catch (Exception ex) {
System.out.println("Error " + ex);
}
return details;
}
}
public void updateDataa(
int PID,
String first,
String last,
String gender,
String mobile,
String BDate,
String address) {
try {
st = dbCon.getInstance().createStatement();
// String query = " `patients` (`Patient_Id`,`Pasword`,
// `UserName`,`Real_Name`,`First_Name`,`Last_Name`,`Gender`,`Mobile_No`,`DOB`,`Address`)
// VALUES ('" + PID + "','" + pass + "', '" + user + "', '" + fullname + "', '" + first + "',
// '" + last + "', '" + gender + "', '" + mobile + "', '" + BDate + "', '" + address + "')";
// String query = "UPDATE `patient` SET
// `First_name`=?,`Last_name`=?,`Gender`=?,`Mobile_number`=?,`Date_of_birth`=?,`Address`=?
// WHERE Patient_Id =?";
String query =
"UPDATE `patient` SET `First_name`='"
+ first
+ "',`Last_name`='"
+ last
+ "',`Gender`='"
+ gender
+ "',`Mobile_number`='"
+ mobile
+ "',`Date_of_birth`='"
+ BDate
+ "',`Address`='"
+ address
+ "' WHERE Patient_Id ='"
+ PID
+ "'";
// pst = dbCon.getInstance().prepareStatement(query);
// pst.setString(1, first);
// pst.setString(2, last);
// pst.setString(3, gender);
// pst.setString(4, mobile);
// pst.setString(5, BDate);
// pst.setString(6, address);
// pst.setInt(7, PID);
// try {
// pst.executeUpdate();
// } catch (MySQLSyntaxErrorException e) {
// System.out.println("Yako Syntax awlak");
// }
st.executeUpdate(query);
System.out.println("Updated");
String query2 = "SELECT * FROM `patient`";
rs = st.executeQuery(query2);
patientcount = 0;
while (rs.next()) {
patientcount++;
}
JOptionPane.showMessageDialog(new JDialog(), "Patient Account Updated! Id =" + PID);
} catch (SQLException ex) {
Logger.getLogger(PatientDataAccessManager.class.getName()).log(Level.SEVERE, null, ex);
}
}