?? materialdao.java
字號:
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;
}
// 出庫后對庫存數(shù)量進行更新
public boolean updateNowCount(ArrayList list) throws SQLException {
// TODO Auto-generated method stub
boolean flag = false;
Connection conn = null;
PreparedStatement ps = null;
MateOutDetailBean modb;
Iterator it = list.iterator();
conn = DBConnection.getConnection();
conn.setAutoCommit(false);
while(it.hasNext()){
modb=(MateOutDetailBean)it.next();
try {
ps = conn.prepareStatement(UPDATE_NOWCOUNT_SQL);
ps.setInt(1, modb.getOutCount());
ps.setInt(2,modb.getMaterialNo());
int i = ps.executeUpdate();
flag = i > 0 ? true : false;
} catch(SQLException e) {
e.printStackTrace();
}
if(flag==false){
conn.rollback();
break;
}
}
conn.commit();
DBConnection.close(ps);
DBConnection.close(conn);
return flag;
}
public ArrayList checkSearchMaterial(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(CHECK_SEARCH_MATERIAL_SQL);
// if (mate.getMaterialTypeNo() != null && mate.getMaterialTypeNo().equals("") != true) {
// sql.append("Material_Table.MaterialTypeNo = '"
// + DBUtil.fixSqlFieldValue(mate.getMaterialTypeNo())+ "' AND ");
// }
if (mate.getMaterialTypeNo() != null && mate.getMaterialTypeNo().equals("") != true) {
sql.append("(MaterialTypeNo IN "+mateTypeDAO.getMaterialSQL(mate.getMaterialTypeNo())+") AND ");
}
if ((startDate != null && startDate.equals("") != true)&& (endDate != null && endDate.equals("") != true)) {
sql.append("Material_Table.MaterialNo not in (select MaterialNo from Material_Check_Table Where Material_Check_Table.CheckDate 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(" nowCount > 0 ");
sql.append(" ORDER BY Material_Table.MaterialTypeName,Material_Table.MaterialTypeNo DESC");
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 boolean updateMaterialForMateOutDetail(int materialNo, int outCount, int outCount2) throws Exception {
// 修改出庫單 更新庫存信息
boolean flag = false;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBConnection.getConnection();
ps = conn.prepareStatement(UPDATE_MATERIAL_OUT_SQL);
ps.setInt(1, outCount-outCount2);
ps.setInt(2, materialNo);
int i = ps.executeUpdate();
flag = i > 0 ? true : false;
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(ps);
DBConnection.close(conn);
}
return flag;
}
public boolean updateMaterialForMateCheck(int materialNo, int checkCount) throws Exception {
// 修改出庫單 更新庫存信息
boolean flag = false;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBConnection.getConnection();
ps = conn.prepareStatement(UPDATE_MATERIAL_OUT_SQL_FOR_CHECK);
ps.setInt(1, checkCount);
ps.setInt(2, materialNo);
int i = ps.executeUpdate();
flag = i > 0 ? true : false;
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(ps);
DBConnection.close(conn);
}
return flag;
}
public boolean rollBackMaterial(MaterialCheckBean mateCheckBean) throws Exception {
//刪除盤點信息 還原庫存信息 //根據(jù)盤點信息 刪除添加的庫存信息
boolean flag = false;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
// Build the search criterias
StringBuffer sql = new StringBuffer(512);
sql.append(CHECK_DELECT_MATERIAL_SQL);
//物品名稱
if (mateCheckBean.getMaterialName() != null && mateCheckBean.getMaterialName().equals("") != true) {
sql.append("MaterialTypeName = '"
+ DBUtil.fixSqlFieldValue(mateCheckBean.getMaterialName())+ "' AND ");
}//型號
if (mateCheckBean.getMaModel() != null && mateCheckBean.getMaModel().equals("") != true) {
sql.append("MaModel = '"
+ DBUtil.fixSqlFieldValue(mateCheckBean.getMaModel())+ "' AND ");
}//品牌
if (mateCheckBean.getMaBrand() != null && mateCheckBean.getMaBrand().equals("") != true) {
sql.append("MaBrand = '"
+ DBUtil.fixSqlFieldValue(mateCheckBean.getMaBrand())+ "' AND ");
}//產(chǎn)地
if (mateCheckBean.getMaMadeIn() != null && mateCheckBean.getMaMadeIn().equals("") != true) {
sql.append("MaMadeIn = '"
+ DBUtil.fixSqlFieldValue(mateCheckBean.getMaMadeIn())+ "' AND ");
}//物品類型
if (mateCheckBean.getMaterialTypeNo() != null && mateCheckBean.getMaterialTypeNo().equals("") != true) {
sql.append("MaterialTypeNo = '"
+ DBUtil.fixSqlFieldValue(mateCheckBean.getMaterialTypeNo())+ "' AND ");
}//入庫數(shù)量
if (mateCheckBean.getCheckCount() != 0) {
sql.append("InCount = "
+ DBUtil.fixSqlFieldValue(String.valueOf(mateCheckBean.getCheckCount()))+ " AND ");
}//入庫日期
if (mateCheckBean.getCheckDate() != null) {
sql.append("InDate = TO_DATE('"
+ DBUtil.fixSqlFieldValue(mateCheckBean.getCheckDate().toString())+ "','YYYY-MM-DD')" + " AND ");
}
// 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);
}
//入庫來源 盤點入庫 4
sql.append("AND Material_Table.InFrom = 4 ");
try {
conn = DBConnection.getConnection();
stmt = conn.createStatement();
//System.out.println(sql.toString()); //
int i = stmt.executeUpdate(sql.toString());
flag = i > 0 ? true : false;
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(rs);
DBConnection.close(stmt);
DBConnection.close(conn);
}
return flag;
}
public boolean rollBackMaterial(MateOutDetailBean mateOutBean, Date outDate) throws Exception {
//刪除變更信息 還原庫存信息 //根據(jù)出庫信息 刪除添加的庫存信息
boolean flag = false;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
// Build the search criterias
StringBuffer sql = new StringBuffer(512);
sql.append(CHECK_DELECT_MATERIAL_SQL);
//物品名稱
if (mateOutBean.getMaterialName() != null && mateOutBean.getMaterialName().equals("") != true) {
sql.append("MaterialTypeName = '"
+ DBUtil.fixSqlFieldValue(mateOutBean.getMaterialName())+ "' AND ");
}//型號
if (mateOutBean.getMaModel() != null && mateOutBean.getMaModel().equals("") != true) {
sql.append("MaModel = '"
+ DBUtil.fixSqlFieldValue(mateOutBean.getMaModel())+ "' AND ");
}//品牌
if (mateOutBean.getMaBrand() != null && mateOutBean.getMaBrand().equals("") != true) {
sql.append("MaBrand = '"
+ DBUtil.fixSqlFieldValue(mateOutBean.getMaBrand())+ "' AND ");
}//產(chǎn)地
if (mateOutBean.getMaMadeIn() != null && mateOutBean.getMaMadeIn().equals("") != true) {
sql.append("MaMadeIn = '"
+ DBUtil.fixSqlFieldValue(mateOutBean.getMaMadeIn())+ "' AND ");
}//物品類型
if (mateOutBean.getMaterialTypeNo() != null && mateOutBean.getMaterialTypeNo().equals("") != true) {
sql.append("MaterialTypeNo = '"
+ DBUtil.fixSqlFieldValue(mateOutBean.getMaterialTypeNo())+ "' AND ");
}//入庫數(shù)量
if (mateOutBean.getOutCount() != 0) {
sql.append("InCount = "
+ DBUtil.fixSqlFieldValue(String.valueOf(mateOutBean.getOutCount()))+ " AND ");
}//入庫日期
if (outDate != null) {
sql.append("InDate = TO_DATE('"
+ DBUtil.fixSqlFieldValue(outDate.toString())+ "','YYYY-MM-DD')" + " AND ");
}
// 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);
}
//入庫來源 變更入庫 3
sql.append("AND Material_Table.InFrom = 3 ");
try {
conn = DBConnection.getConnection();
stmt = conn.createStatement();
//System.out.println(sql.toString()); //
int i = stmt.executeUpdate(sql.toString());
flag = i > 0 ? true : false;
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(rs);
DBConnection.close(stmt);
DBConnection.close(conn);
}
return flag;
}
public boolean rollBackOutDetail(MateOutDetailBean modb) throws Exception {
// 還原庫存信息
boolean flag = false;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBConnection.getConnection();
ps = conn.prepareStatement(UPDATE_MATERIAL_OUT_SQL);
ps.setInt(1, modb.getOutCount());
ps.setInt(2, modb.getMaterialNo());
int i = ps.executeUpdate();
flag = i > 0 ? true : false;
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(ps);
DBConnection.close(conn);
}
return flag;
}
}
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -