?? materialdao.java
字號:
// throw new Exception();
// } finally {
// DBConnection.close(rs);
// DBConnection.close(stmt);
// }
// return array;
// }
public ArrayList getMaterial() {
// 返回物品類型list
ArrayList mates = new ArrayList();
DBPoolManager dbpool = new DBPoolManager();
dbpool.getConnection();
try {
String sql = "select MaterialTypeNo, MaterialTypeName from Material_Type WHERE MATERIALPATYPENO != 0 order by MaterialTypeNo";
Statement stmt = dbpool.conn.createStatement();
ResultSet rest = stmt.executeQuery(sql);
while (rest.next()) {
Hashtable ht = new Hashtable();
ht.put("MaterialTypeNo", rest.getString(1));
ht.put("MaterialTypeName", rest.getString(2));
mates.add(ht);
}
rest.close();
stmt.close();
} catch (SQLException ex) {
System.err
.println("Get Departments SQLException: " + ex.toString());
} finally {
dbpool.freeConnection();
}
return mates;
}
public ArrayList getPATYPENO() {
// 返回物品類型list
ArrayList mates = new ArrayList();
DBPoolManager dbpool = new DBPoolManager();
dbpool.getConnection();
try {
String sql = "select MATERIALSQ, MaterialTypeName from Material_Type order by MATERIALSQ";
Statement stmt = dbpool.conn.createStatement();
ResultSet rest = stmt.executeQuery(sql);
while (rest.next()) {
Hashtable ht = new Hashtable();
ht.put("MaterialTypeNo", Integer.valueOf(rest.getInt(1))
.toString());
ht.put("MaterialTypeName", rest.getString(2));
mates.add(ht);
}
rest.close();
stmt.close();
} catch (SQLException ex) {
System.err
.println("Get Departments SQLException: " + ex.toString());
} finally {
dbpool.freeConnection();
}
return mates;
}
public ArrayList searchMaterial(MaterialBean mate, String startDate,
String endDate) throws Exception {
// 按條件查詢入庫信息2
ArrayList array = new ArrayList();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
MaterialTypeDAO mateTypeDAO = new MaterialTypeDAO();
// Build the search criterias
StringBuffer sql = new StringBuffer(512);
sql.append(SEARCH_MATERIAL_SQL);
if (mate.getMaterialTypeName() != null
&& mate.getMaterialTypeName().equals("") != true) {
sql.append("MaterialTypeName LIKE '%"
+ DBUtil.fixSqlFieldValue(mate.getMaterialTypeName())
+ "%' AND ");
}
// if (mate.getMaterialTypeNo() != null
// && mate.getMaterialTypeNo().equals("") != true) {
// sql.append("MaterialTypeNo LIKE '%"
// + DBUtil.fixSqlFieldValue(mate.getMaterialTypeNo())
// + "%' AND ");
// }
if (mate.getMaterialTypeNo() != null && mate.getMaterialTypeNo().equals("") != true) {
sql.append("(MaterialTypeNo IN "+mateTypeDAO.getMaterialSQL(mate.getMaterialTypeNo())+") AND ");
}
if (mate.getMaModel() != null && mate.getMaModel().equals("") != true) {
sql.append("MaModel LIKE '%"
+ DBUtil.fixSqlFieldValue(mate.getMaModel()) + "%' AND ");
}
if (mate.getMaBrand() != null && mate.getMaBrand().equals("") != true) {
sql.append("MaBrand LIKE '%"
+ DBUtil.fixSqlFieldValue(mate.getMaBrand()) + "%' AND ");
}
if (mate.getMaMadeIn() != null && mate.getMaMadeIn().equals("") != true) {
sql.append("MaMadeIn LIKE '%"
+ DBUtil.fixSqlFieldValue(mate.getMaMadeIn()) + "%' AND ");
}
if (mate.getInFrom() != 0) {
sql.append("InFrom = "
+ DBUtil.fixSqlFieldValue(String.valueOf(mate.getInFrom()))
+ " AND ");
}
if ((startDate != null && startDate.equals("") != true)
&& (endDate != null && endDate.equals("") != true)) {
sql.append("InDate BETWEEN TO_DATE('"
+ DBUtil.fixSqlFieldValue(startDate.trim())
+ "','YYYY-MM-DD') AND TO_DATE('" + endDate.trim()
+ "','YYYY-MM-DD')");
}
// Remove unused 'AND' & 'WHERE'
if (sql.substring(sql.length() - 5).equals(" AND ")) {
sql.delete(sql.length() - 5, sql.length() - 1);
}
if (sql.substring(sql.length() - 7).equals(" WHERE ")) {
sql.delete(sql.length() - 7, sql.length() - 1);
}
// 末尾添加排序
sql.append(" ORDER BY inDate");
try {
conn = DBConnection.getConnection();
stmt = conn.createStatement();
//System.out.println(sql.toString());
rs = stmt.executeQuery(sql.toString());
while (rs.next()) {
MaterialBean mate2 = new MaterialBean();
mate2.setMaterialNo(rs.getString("materialNo"));
mate2.setMaterialTypeName(rs.getString("materialTypeName"));
mate2.setMaterialTypeNo(rs.getString("materialTypeNo"));
mate2.setMaModel(rs.getString("maModel"));
mate2.setMaBrand(rs.getString("maBrand"));
mate2.setMaMadeIn(rs.getString("maMadeIn"));
mate2.setBuyId(rs.getInt("buyId"));
mate2.setPriceUnit(rs.getDouble("priceUnit"));
mate2.setInCount(rs.getInt("inCount"));
mate2.setNowCount(rs.getInt("nowCount"));
mate2.setInDate(rs.getDate("inDate"));
mate2.setUserCode(rs.getString("userCode"));
mate2.setInFrom(rs.getInt("inFrom"));
mate2.setReMark(rs.getString("reMark"));
array.add(mate2);
}
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(rs);
DBConnection.close(stmt);
DBConnection.close(conn);
}
return array;
}
public ArrayList reseveSearchMaterial(MaterialBean mate, String startDate,
String endDate) throws Exception {
// 按條件查詢庫存信息
ArrayList array = new ArrayList();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
MaterialTypeDAO mateTypeDAO = new MaterialTypeDAO();
// Build the search criterias
StringBuffer sql = new StringBuffer(512);
sql.append(RESEVE_SEARCH_MATERIAL_SQL);
if (mate.getBuyId() != 0) {
sql.append("BuyId = "
+ DBUtil.fixSqlFieldValue(String.valueOf(mate.getBuyId()))
+ " AND ");
}
if (mate.getMaterialTypeName() != null
&& mate.getMaterialTypeName().equals("") != true) {
sql.append("MaterialTypeName LIKE '%"
+ DBUtil.fixSqlFieldValue(mate.getMaterialTypeName())
+ "%' AND ");
}
if (mate.getMaterialTypeNo() != null && mate.getMaterialTypeNo().equals("") != true) {
// sql.append("MaterialTypeNo IN '%"
// + DBUtil.fixSqlFieldValue(mate.getMaterialTypeNo())
// + "%' AND ");
sql.append("(MaterialTypeNo IN "+mateTypeDAO.getMaterialSQL(mate.getMaterialTypeNo())+") AND ");
}
if (mate.getMaModel() != null && mate.getMaModel().equals("") != true) {
sql.append("MaModel LIKE '%"
+ DBUtil.fixSqlFieldValue(mate.getMaModel()) + "%' AND ");
}
if (mate.getMaBrand() != null && mate.getMaBrand().equals("") != true) {
sql.append("MaBrand LIKE '%"
+ DBUtil.fixSqlFieldValue(mate.getMaBrand()) + "%' AND ");
}
if (mate.getMaMadeIn() != null && mate.getMaMadeIn().equals("") != true) {
sql.append("MaMadeIn LIKE '%"
+ DBUtil.fixSqlFieldValue(mate.getMaMadeIn()) + "%' AND ");
}
if (mate.getInFrom() != 0) {
sql.append("InFrom = "
+ DBUtil.fixSqlFieldValue(String.valueOf(mate.getInFrom()))
+ " AND ");
}
if (mate.getNowCount() == -1) {
sql.append("NowCount > "
+ DBUtil.fixSqlFieldValue(String.valueOf(0))
+ " AND ");
}
if ((startDate != null && startDate.equals("") != true)
&& (endDate != null && endDate.equals("") != true)) {
sql.append("InDate BETWEEN TO_DATE('"
+ DBUtil.fixSqlFieldValue(startDate.trim())
+ "','YYYY-MM-DD') AND TO_DATE('" + endDate.trim()
+ "','YYYY-MM-DD')");
}
// Remove unused 'AND' & 'WHERE'
if (sql.substring(sql.length() - 5).equals(" AND ")) {
sql.delete(sql.length() - 5, sql.length() - 1);
}
if (sql.substring(sql.length() - 7).equals(" WHERE ")) {
sql.delete(sql.length() - 7, sql.length() - 1);
}
// 末尾添加排序
sql.append(" ORDER BY MaterialTypeName,MaterialTypeNo,MaModel,MaBrand,MaMadeIn DESC");
try {
conn = DBConnection.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql.toString());
while (rs.next()) {
MaterialBean mate2 = new MaterialBean();
mate2.setMaterialNo(rs.getString("materialNo"));
mate2.setMaterialTypeName(rs.getString("materialTypeName"));
mate2.setMaterialTypeNo(rs.getString("materialTypeNo"));
mate2.setMaModel(rs.getString("maModel"));
mate2.setMaBrand(rs.getString("maBrand"));
mate2.setMaMadeIn(rs.getString("maMadeIn"));
mate2.setBuyId(rs.getInt("buyId"));
mate2.setPriceUnit(rs.getDouble("priceUnit"));
// mate2.setInCount(rs.getInt("inCount"));
mate2.setNowCount(rs.getInt("nowCount"));
mate2.setInDate(rs.getDate("inDate"));
// mate2.setUserCode(rs.getString("userCode"));
mate2.setInFrom(rs.getInt("inFrom"));
// mate2.setReMark(rs.getString("reMark"));
array.add(mate2);
}
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(rs);
DBConnection.close(stmt);
DBConnection.close(conn);
}
return array;
}
public ArrayList buyTableSearch() throws Exception {
// 采購信息查詢 查詢 buy table 返回list
ArrayList<MaterialBuyDetailBean> array = new ArrayList();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
// Build the search criterias
StringBuffer sql = new StringBuffer(512);
sql.append(BUY_SEARCH_MATERIAL_SQL);
// 末尾添加排序
sql.append(" ORDER BY BuyNo");
try {
conn = DBConnection.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql.toString());
while (rs.next()) {
MaterialBuyDetailBean mate2 = new MaterialBuyDetailBean();
mate2.setBuyId(rs.getInt("buyId"));
mate2.setBuyNo(rs.getString("buyNo"));
mate2.setMaterialTypeName(rs.getString("materialTypeName"));
mate2.setMaModel(rs.getString("maModel"));
mate2.setMaBrand(rs.getString("maBrand"));
mate2.setMaMadeIn(rs.getString("maMadeIn"));
mate2.setMaterialTypeNo(rs.getString("materialTypeNo"));
mate2.setPriceUnit(rs.getDouble("priceUnit"));
mate2.setInCount(rs.getInt("inCount"));
// mate2.setFlag(rs.getString("flag"));
// mate2.setReFlag(rs.getString("reFlag"));
array.add(mate2);
}
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(rs);
DBConnection.close(stmt);
DBConnection.close(conn);
}
return array;
}
public boolean setMateBuyBean(MaterialBuyDetailBean mate, String userCode) throws Exception {
// 傳入采購明細對象 存入數據庫
boolean flag = false;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBConnection.getConnection();
ps = conn.prepareStatement(BUY_INSERT_MATERIAL_SQL);
ps.setString(1, mate.getMaterialTypeName());
ps.setString(2, mate.getMaterialTypeNo());
ps.setString(3, mate.getMaModel());
ps.setString(4, mate.getMaBrand());
ps.setString(5, mate.getMaMadeIn());
ps.setInt(6, mate.getBuyId());
ps.setDouble(7, mate.getPriceUnit());
ps.setInt(8, mate.getInCount());
ps.setInt(9, mate.getInCount());
// ps.setDate(10, mate.getInDate());
ps.setString(10, userCode);
ps.setInt(11, 1);
ps.setString(12, "");
int i = ps.executeUpdate();
flag = i > 0 ? true : false;
} catch (Exception e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(ps);
DBConnection.close(conn);
}
return flag;
}
public ArrayList checkSearchMaterial() throws Exception {
// 按條件查詢庫存信息 盤點管理用
ArrayList array = new ArrayList();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
// Build the search criterias
StringBuffer sql = new StringBuffer(512);
sql.append(RESEVE_SEARCH_MATERIAL_SQL);
// 末尾添加排序
sql.append(" nowCount > 0 ");
sql.append(" ORDER BY MaterialTypeName,MaterialTypeNo DESC");
try {
conn = DBConnection.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql.toString());
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -