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;
}
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;
}
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;
}
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;
}
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;
}
@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;
}
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;
}
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;
}
@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;
}
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;
}
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;
}
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;
}
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;
}