示例#1
1
  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");
    }
  }
示例#10
0
 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(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;
  }
示例#12
0
 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();
     }
   }
 }
  /** Load properties that already exist from Jive properties. */
  private void loadProperties() {

    driver = JiveGlobals.getXMLProperty("database.defaultProvider.driver");
    serverURL = JiveGlobals.getXMLProperty("database.defaultProvider.serverURL");
    username = JiveGlobals.getXMLProperty("database.defaultProvider.username");
    password = JiveGlobals.getXMLProperty("database.defaultProvider.password");
    String minCons = JiveGlobals.getXMLProperty("database.defaultProvider.minConnections");
    String maxCons = JiveGlobals.getXMLProperty("database.defaultProvider.maxConnections");
    String conTimeout = JiveGlobals.getXMLProperty("database.defaultProvider.connectionTimeout");
    testSQL =
        JiveGlobals.getXMLProperty(
            "database.defaultProvider.testSQL", DbConnectionManager.getTestSQL(driver));
    testBeforeUse = JiveGlobals.getXMLProperty("database.defaultProvider.testBeforeUse", true);
    testAfterUse = JiveGlobals.getXMLProperty("database.defaultProvider.testAfterUse", true);

    // See if we should use Unicode under MySQL
    mysqlUseUnicode = Boolean.valueOf(JiveGlobals.getXMLProperty("database.mysql.useUnicode"));
    try {
      if (minCons != null) {
        minConnections = Integer.parseInt(minCons);
      }
      if (maxCons != null) {
        maxConnections = Integer.parseInt(maxCons);
      }
      if (conTimeout != null) {
        connectionTimeout = Double.parseDouble(conTimeout);
      }
    } catch (Exception e) {
      Log.error(
          "Error: could not parse default pool properties. "
              + "Make sure the values exist and are correct.",
          e);
    }
  }
  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;
  }
示例#21
0
 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;
  }
示例#23
0
  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);
    }
  }