materialcheckdao.java

JSP移动商品管理平台源代码.........

package imis_mate.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import imis_mate.bean.MaterialBean;
import imis_mate.bean.MaterialCheckBean;

public class MaterialCheckDAO {
	//SQL 语句
	private static final String INSERT_MATERIALCHECK_SQL = 
		"INSERT INTO " +
		"Material_Check_Table (checkID, materialNo, materialName, maModel, maBrand, maMadeIn, materialTypeNo, outCount, nowCount, checkCount, checkDate, checkBy, remark) " +
		"VALUES (CheckID.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?)";
	private static final String UPDATE_MATERIALCHECK_SQL = 
		"UPDATE Material_Check_Table " +
		"SET materialNo = ?, materialName = ?, maModel = ?, maBrand = ?, maMadeIn = ?, materialTypeNo = ?, outCount = ?, nowCount = ?, checkCount = ?, checkDate = ?, checkBy = ?, remark = ? " +
		"WHERE checkID = ?";
	private static final String DELETE_MATERIALCHECK_SQL = 
		"DELETE FROM Material_Check_Table " +
		"WHERE checkID = ?";
	private static final String SELECT_MATERIALCHECK_SQL = 
		"SELECT checkID, materialNo, materialName, maModel, maBrand, maMadeIn, materialTypeNo, outCount, nowCount, checkCount, checkDate, checkBy, remark " +
		"FROM Material_Check_Table " +
		"WHERE checkID = ?";
	private static final String SEARCH_MATERIALCHECK_SQL = 
		"SELECT checkID, materialNo, materialName, maModel, maBrand, maMadeIn, materialTypeNo, outCount, nowCount, checkCount, checkDate, checkBy, remark " +
		"FROM Material_Check_Table " +
		"WHERE ";
	
	//方法
	public boolean insertMaterialCheck(MaterialCheckBean mateCheck) throws Exception {
		//插入盘点信息
		boolean flag = false;
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(INSERT_MATERIALCHECK_SQL);
			
			ps.setInt(1, mateCheck.getMaterialNo());
			ps.setString(2, mateCheck.getMaterialName());
			ps.setString(3, mateCheck.getMaModel());
			ps.setString(4, mateCheck.getMaBrand());
			ps.setString(5, mateCheck.getMaMadeIn());
			ps.setString(6, mateCheck.getMaterialTypeNo());
			ps.setInt(7, mateCheck.getOutCount());
			ps.setInt(8, mateCheck.getNowCount());
			ps.setInt(9, mateCheck.getCheckCount());
			ps.setDate(10, mateCheck.getCheckDate());
			ps.setString(11, mateCheck.getCheckBy());
			ps.setString(12, mateCheck.getRemark());
			
			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 boolean updateMaterialCheck(MaterialCheckBean mateCheck) throws Exception {
		//更新物品类型
		boolean flag = false;
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			conn = DBConnection.getConnection();
			
			ps = conn.prepareStatement(UPDATE_MATERIALCHECK_SQL);
			
			ps.setInt(1, mateCheck.getMaterialNo());
			ps.setString(2, mateCheck.getMaterialName());
			ps.setString(3, mateCheck.getMaModel());
			ps.setString(4, mateCheck.getMaBrand());
			ps.setString(5, mateCheck.getMaMadeIn());
			ps.setString(6, mateCheck.getMaterialTypeNo());
			ps.setInt(7, mateCheck.getOutCount());
			ps.setInt(8, mateCheck.getNowCount());
			ps.setInt(9, mateCheck.getCheckCount());
			ps.setDate(10, mateCheck.getCheckDate());
			ps.setString(11, mateCheck.getCheckBy());
			ps.setString(12, mateCheck.getRemark());
			ps.setInt(13, mateCheck.getCheckID());
			
			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 deleteMaterialCheck(String checkID) throws Exception {
		//删除盘点信息
		
		boolean flag = false;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(DELETE_MATERIALCHECK_SQL);
			ps.setString(1, checkID);
			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 MaterialCheckBean selectMaterialCheck(int checkID) throws Exception {
		//查询盘点信息
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		MaterialCheckBean mateCheck = null;
		
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(SELECT_MATERIALCHECK_SQL);
			ps.setInt(1, checkID);
			rs = ps.executeQuery();
			if(rs.next()) {
				mateCheck = new MaterialCheckBean();
				mateCheck.setCheckID(checkID);
				mateCheck.setMaterialNo(rs.getString("materialNo"));
				mateCheck.setMaterialName(rs.getString("materialName"));
				mateCheck.setMaModel(rs.getString("maModel"));
				mateCheck.setMaBrand(rs.getString("maBrand"));
				mateCheck.setMaMadeIn(rs.getString("maMadeIn"));
				mateCheck.setMaterialTypeNo(rs.getString("materialTypeNo"));
				mateCheck.setOutCount(rs.getString("outCount"));
				mateCheck.setNowCount(rs.getString("nowCount"));
				mateCheck.setCheckCount(rs.getString("checkCount"));
				//mateCheck.setCheckDate(rs.getString("checkDate"));
				mateCheck.setCheckDate(rs.getDate("checkDate"));
				mateCheck.setCheckBy(rs.getString("checkBy"));
				mateCheck.setRemark(rs.getString("remark"));
			} 
			} catch (SQLException e) {
				e.printStackTrace();
				throw new Exception();
			} finally {
				DBConnection.close(rs);
				DBConnection.close(ps);
				DBConnection.close(conn);
			}
			return mateCheck;
	}


	public ArrayList searchMaterialCheck(MaterialCheckBean mateCheck, 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(SEARCH_MATERIALCHECK_SQL);
		if(mateCheck.getCheckID() != 0) {
			sql.append("CheckID LIKE '%" +
					DBUtil.fixSqlFieldValue(String.valueOf(mateCheck.getCheckID())) + "%' AND ");
		}
		if(mateCheck.getMaterialNo() != 0) {
			sql.append("MaterialNo LIKE '%" +
					DBUtil.fixSqlFieldValue(String.valueOf(mateCheck.getMaterialNo())) + "%' AND ");
		}
		if(mateCheck.getMaterialName() != null && (mateCheck.getMaterialName().equals("")!=true)) {
			sql.append("MaterialName LIKE '%" +
					DBUtil.fixSqlFieldValue(mateCheck.getMaterialName()) + "%' AND ");
		}
//		if(mateCheck.getMaterialTypeNo() != null && (mateCheck.getMaterialTypeNo().equals("")!=true)) {
//			sql.append("MaterialTypeNo LIKE '%" +
//					DBUtil.fixSqlFieldValue(mateCheck.getMaterialTypeNo()) + "%' AND ");
//		}
		if (mateCheck.getMaterialTypeNo() != null && mateCheck.getMaterialTypeNo().equals("") != true) {
			sql.append("(MaterialTypeNo IN "+mateTypeDAO.getMaterialSQL(mateCheck.getMaterialTypeNo())+") AND ");
		}
//		if(mateCheck.getCheckDateToString() != null) {
//			sql.append("CheckDate BETWEEN TO_DATE('" +
//					DBUtil.fixSqlFieldValue(mateCheck.getCheckDateToString()) + "','YYYY-MM-DD') AND SYSDATE");
//		}
		if((startDate != null && startDate.equals("") != true) && (endDate != null && endDate.equals("") != true)) {
			sql.append("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);
		}
		
		
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			//System.out.println("  "+sql.toString());
			rs = stmt.executeQuery(sql.toString());
			
			while(rs.next()) {
				MaterialCheckBean mate = new MaterialCheckBean();
				mate.setCheckID(rs.getInt("checkID"));
				mate.setMaterialNo(rs.getInt("materialNo"));
				mate.setMaterialName(rs.getString("materialName"));
				mate.setMaModel(rs.getString("maModel"));
				mate.setMaBrand(rs.getString("maBrand"));
				mate.setMaMadeIn(rs.getString("maMadeIn"));
				mate.setMaterialTypeNo(rs.getString("materialTypeNo"));
				mate.setOutCount(rs.getInt("outCount"));
				mate.setNowCount(rs.getInt("nowCount"));
				mate.setCheckCount(rs.getInt("checkCount"));
				mate.setCheckDate(rs.getDate("checkDate"));
				mate.setCheckBy(rs.getString("checkBy"));
				mate.setRemark(rs.getString("remark"));
				array.add(mate);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(stmt);
			DBConnection.close(conn);
		}
		return array;
	}
}