Beispiel #1
0
 public int addDrug(Drug drug) {
   int flag = 0;
   String sql = "insert into drugvariety values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
   DBConnection db = new DBConnection();
   conn = db.getConn();
   try {
     ps = conn.prepareStatement(sql);
     ps.setString(1, drug.getDID());
     ps.setString(2, drug.getDrugURL());
     ps.setDouble(3, drug.getPurchasing_price());
     ps.setDouble(4, drug.getSelling_price());
     ps.setString(5, drug.getDrugName());
     ps.setInt(6, drug.getDrugType());
     ps.setString(7, drug.getDescription());
     ps.setString(8, drug.getProduction_date());
     ps.setString(9, drug.getExpiration_date());
     ps.setInt(10, drug.getShelf_life());
     ps.setString(11, drug.getDetail());
     ps.setString(12, drug.getManufacturer());
     ps.setString(13, drug.getDirections());
     ps.setInt(14, drug.getCountpurchases());
     ps.setInt(15, drug.getInventory());
     ps.setInt(16, drug.getDrugflag());
     ps.setString(17, drug.getDrugcomment());
     flag = ps.executeUpdate();
     ps.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return flag;
 }
Beispiel #2
0
 public int updateDrug(Drug drug) {
   int flag = 0;
   String sql =
       "update drugvariety set drugURL=?,purchasing_price=?,selling_price=?,drugName=?,drugType=?,description=?,production_date=?,expiration_date=?,shelf_life=?,detail=?,manufacturer=?,directions=?,countpurchases=?,inventory=?,drugflag=?,drugcomment=? where DID=?";
   DBConnection db = new DBConnection();
   conn = db.getConn();
   try {
     ps = conn.prepareStatement(sql);
     ps.setString(1, drug.getDrugURL());
     ps.setDouble(2, drug.getPurchasing_price());
     ps.setDouble(3, drug.getSelling_price());
     ps.setString(4, drug.getDrugName());
     ps.setInt(5, drug.getDrugType());
     ps.setString(6, drug.getDescription());
     ps.setString(7, drug.getProduction_date());
     ps.setString(8, drug.getExpiration_date());
     ps.setInt(9, drug.getShelf_life());
     ps.setString(10, drug.getDetail());
     ps.setString(11, drug.getManufacturer());
     ps.setString(12, drug.getDirections());
     ps.setInt(13, drug.getCountpurchases());
     ps.setInt(14, drug.getInventory());
     ps.setInt(15, drug.getDrugflag());
     ps.setString(16, drug.getDrugcomment());
     ps.setString(17, drug.getDID());
     flag = ps.executeUpdate();
     ps.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return flag;
 }
Beispiel #3
0
 public User findUserById(int id) {
   String sql = "select * from user where userid=" + id;
   DBConnection db = new DBConnection();
   conn = db.getConn();
   User user = new User();
   try {
     ps = conn.prepareStatement(sql);
     rs = ps.executeQuery();
     while (rs.next()) {
       user.setUserid(rs.getInt("userid"));
       user.setUsername(rs.getString("username"));
       user.setPassword(rs.getString("password"));
       user.setRealname(rs.getString("realname"));
       user.setEmail(rs.getString("email"));
       user.setStatus(rs.getInt("status"));
       user.setRoleid(rs.getInt("roleid"));
     }
     rs.close();
     ps.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return user;
 }
Beispiel #4
0
 public int delDrug(String DID) {
   int flag = 0;
   String sql = "delete from drugvariety where DID=?";
   DBConnection db = new DBConnection();
   conn = db.getConn();
   try {
     ps = conn.prepareStatement(sql);
     ps.setString(1, DID);
     flag = ps.executeUpdate();
     ps.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return flag;
 }
Beispiel #5
0
 public int deleteUser(int id) {
   int flag = 0;
   String sql = "delete from user where userid=?";
   DBConnection db = new DBConnection();
   conn = db.getConn();
   try {
     ps = conn.prepareStatement(sql);
     ps.setInt(1, id);
     flag = ps.executeUpdate();
     ps.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return flag;
 }
Beispiel #6
0
 @Override
 public int updateDrugInventory(String DID, int inventory) {
   int flag = 0;
   String sql = "update drugvariety set inventory=? where DID=?";
   DBConnection db = new DBConnection();
   conn = db.getConn();
   try {
     ps = conn.prepareStatement(sql);
     ps.setInt(1, inventory);
     ps.setString(2, DID);
     flag = ps.executeUpdate();
     ps.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return flag;
 }
Beispiel #7
0
 public int addUser(User user) {
   int flag = 0;
   String sql = "insert into user values(userid,?,?,?,?,?,?)";
   DBConnection db = new DBConnection();
   conn = db.getConn();
   try {
     ps = conn.prepareStatement(sql);
     ps.setString(1, user.getUsername());
     ps.setString(2, user.getPassword());
     ps.setString(3, user.getRealname());
     ps.setString(4, user.getEmail());
     ps.setInt(5, user.getStatus());
     ps.setInt(6, user.getRoleid());
     flag = ps.executeUpdate();
     ps.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return flag;
 }
Beispiel #8
0
 public Page findPageByUserName(String username, int pageNo, int pageSize, int totalCount) {
   Page page = new Page();
   String sql;
   if (username != null && username != "") {
     sql =
         "select * from user where username like '%"
             + username
             + "%' limit "
             + page.getOffset(pageNo)
             + ","
             + pageSize;
   } else {
     sql = "select * from user limit " + page.getOffset(pageNo) + "," + pageSize;
   }
   DBConnection db = new DBConnection();
   conn = db.getConn();
   List<User> userList = new ArrayList<User>();
   try {
     ps = conn.prepareStatement(sql);
     rs = ps.executeQuery();
     while (rs.next()) {
       User user = new User();
       user.setUserid(rs.getInt("userid"));
       user.setUsername(rs.getString("username"));
       user.setPassword(rs.getString("password"));
       user.setRealname(rs.getString("realname"));
       user.setEmail(rs.getString("email"));
       user.setStatus(rs.getInt("status"));
       user.setRoleid(rs.getInt("roleid"));
       userList.add(user);
     }
     rs.close();
     ps.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   page.setPageItem(userList);
   return page;
 }
Beispiel #9
0
 @Override
 public List<Drug> findAllDrug() {
   List<Drug> drugList = new ArrayList<Drug>();
   String sql = "select * from drugvariety";
   DBConnection db = new DBConnection();
   conn = db.getConn();
   try {
     ps = conn.prepareStatement(sql);
     rs = ps.executeQuery();
     while (rs.next()) {
       Drug drug = new Drug();
       drug.setDID(rs.getString("DID"));
       drug.setDrugURL(rs.getString("drugURL"));
       drug.setPurchasing_price(rs.getDouble("purchasing_price"));
       drug.setSelling_price(rs.getDouble("selling_price"));
       drug.setDrugName(rs.getString("drugName"));
       drug.setDrugType(rs.getInt("drugType"));
       drug.setDescription(rs.getString("description"));
       drug.setProduction_date(rs.getString("production_date"));
       drug.setExpiration_date(rs.getString("expiration_date"));
       drug.setShelf_life(rs.getInt("shelf_life"));
       drug.setDetail(rs.getString("detail"));
       drug.setManufacturer(rs.getString("manufacturer"));
       drug.setDirections(rs.getString("directions"));
       drug.setCountpurchases(rs.getInt("countpurchases"));
       drug.setInventory(rs.getInt("inventory"));
       drug.setDrugflag(rs.getInt("drugflag"));
       drug.setDrugcomment(rs.getString("drugcomment"));
       drugList.add(drug);
     }
     rs.close();
     ps.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return drugList;
 }
Beispiel #10
0
 public int updateUser(User user) {
   int flag = 0;
   String sql =
       "update user set username=?,password=?,realname=?,email=?,status=?,roleid=? where userid=?";
   DBConnection db = new DBConnection();
   conn = db.getConn();
   try {
     ps = conn.prepareStatement(sql);
     ps.setString(1, user.getUsername());
     ps.setString(2, user.getPassword());
     ps.setString(3, user.getRealname());
     ps.setString(4, user.getEmail());
     ps.setInt(5, user.getStatus());
     ps.setInt(6, user.getRoleid());
     ps.setInt(7, user.getUserid());
     flag = ps.executeUpdate();
     ps.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return flag;
 }
Beispiel #11
0
 public int findDrugCount(String drugName, int drugType) {
   int count = 0;
   String sql;
   if (drugName != null) {
     if (drugType == 0) {
       sql = "select count(*) as num from drugvariety where drugName like '%" + drugName + "%'";
     } else if (drugType == 4) {
       sql =
           "select count(*) as num from drugvariety where drugName like '%"
               + drugName
               + "%' and (drugType=1 or drugType=2)";
     } else {
       sql =
           "select count(*) as num from drugvariety where drugName like '%"
               + drugName
               + "%' and drugType="
               + drugType;
     }
   } else {
     sql = "select count(*) as num from drugvariety";
   }
   DBConnection db = new DBConnection();
   conn = db.getConn();
   try {
     ps = conn.prepareStatement(sql);
     rs = ps.executeQuery();
     while (rs.next()) {
       count = rs.getInt("num");
     }
     rs.close();
     ps.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return count;
 }
Beispiel #12
0
 public int findUserNameCount(String username) {
   int count = 0;
   String sql;
   if (username == null) {
     sql = "select count(*) as num from user";
   } else {
     sql = "select count(*) as num from user where username like '%" + username + "%'";
   }
   DBConnection db = new DBConnection();
   conn = db.getConn();
   try {
     ps = conn.prepareStatement(sql);
     rs = ps.executeQuery();
     while (rs.next()) {
       count = rs.getInt("num");
     }
     rs.close();
     ps.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return count;
 }
Beispiel #13
0
 public Page findDrugPage(
     String drugName, int drugType, int pageNo, int pageSize, int totalCount) {
   Page page = new Page();
   String sql;
   if (drugName != null) {
     if (drugType == 0) {
       sql =
           "select * from drugvariety where drugName like '%"
               + drugName
               + "%' limit "
               + page.getOffset(pageNo)
               + ","
               + pageSize;
     } else if (drugType == 4) {
       sql =
           "select * from drugvariety where drugName like '%"
               + drugName
               + "%' and (drugType=1 or drugType=2) limit "
               + page.getOffset(pageNo)
               + ","
               + pageSize;
     } else {
       sql =
           "select * from drugvariety where drugName like '%"
               + drugName
               + "%' and drugType="
               + drugType
               + " limit "
               + page.getOffset(pageNo)
               + ","
               + pageSize;
     }
   } else {
     sql = "select * from drugvariety limit " + page.getOffset(pageNo) + "," + pageSize;
   }
   DBConnection db = new DBConnection();
   conn = db.getConn();
   List<Drug> drugList = new ArrayList<Drug>();
   try {
     ps = conn.prepareStatement(sql);
     rs = ps.executeQuery();
     while (rs.next()) {
       Drug drug = new Drug();
       drug.setDID(rs.getString("DID"));
       drug.setDrugURL(rs.getString("drugURL"));
       drug.setPurchasing_price(rs.getDouble("purchasing_price"));
       drug.setSelling_price(rs.getDouble("selling_price"));
       drug.setDrugName(rs.getString("drugName"));
       drug.setDrugType(rs.getInt("drugType"));
       drug.setDescription(rs.getString("description"));
       drug.setProduction_date(rs.getString("production_date"));
       drug.setExpiration_date(rs.getString("expiration_date"));
       drug.setShelf_life(rs.getInt("shelf_life"));
       drug.setDetail(rs.getString("detail"));
       drug.setManufacturer(rs.getString("manufacturer"));
       drug.setDirections(rs.getString("directions"));
       drug.setCountpurchases(rs.getInt("countpurchases"));
       drug.setInventory(rs.getInt("inventory"));
       drug.setDrugflag(rs.getInt("drugflag"));
       drug.setDrugcomment(rs.getString("drugcomment"));
       drugList.add(drug);
     }
     rs.close();
     ps.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
   page.setPageItem(drugList);
   return page;
 }