materialdao.java

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

package imis_mate.DAO;

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

import database.DBPoolManager;

import imis_mate.bean.MateOutDetailBean;
import imis_mate.bean.MaterialBean;
import imis_mate.bean.MaterialBuyDetailBean;
import imis_mate.bean.MaterialCheckBean;
import imis_mate.bean.MaterialTypeBean;

public class MaterialDAO {
	// SQL 语句
	private static final String INSERT_MATERIAL_SQL = "INSERT INTO "
			+ "Material_Table(materialNo,materialTypeName,materialTypeNo,maModel,maBrand,maMadeIn,buyId,priceUnit,inCount,nowCount,inDate,userCode,inFrom,reMark)"
			+ "VALUES (MaterialNo.Nextval,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	private static final String INSERT_MATERIAL_CHECK_SQL = "INSERT INTO "
			+ "Material_Table(materialNo,materialTypeName,materialTypeNo,maModel,maBrand,maMadeIn,buyId,priceUnit,inCount,nowCount,inDate,userCode,inFrom,reMark)"
			+ "VALUES (MaterialNo.Nextval,?,?,?,?,?,?,?,?,?,to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'),?,?,?)";
	
	private static final String UPDATE_MATERIAL_SQL = "" +
			"UPDATE Material_Table "+ 
			"SET materialTypeName = ?,materialTypeNo = ?,maModel = ?,maBrand = ?,maMadeIn = ?,buyId = ?,priceUnit = ?,inCount = ?,nowCount = ?,inDate = ?,userCode = ?,inFrom = ?,reMark = ? "+
			"WHERE materialNo = ?";
	private static final String UPDATE_MATERIAL_OUT_SQL = "" +
			"UPDATE Material_Table "+ 
			"SET nowCount = nowCount + ? "+
			"WHERE materialNo = ?";
	private static final String UPDATE_MATERIAL_OUT_SQL_FOR_CHECK = "" +
			"UPDATE Material_Table "+ 
			"SET nowCount =  ? "+
			"WHERE materialNo = ?";
	private static final String DELETE_MATERIAL_SQL = "DELETE FROM Material_Table "
			+ "WHERE MaterialNo = ?";

	private static final String SELECT_MATERIAL_SQL = "SELECT materialNo,materialTypeName,materialTypeNo,maModel,maBrand,maMadeIn,buyId,priceUnit,inCount,nowCount,inDate,userCode,inFrom,reMark "
			+ "FROM Material_Table " + "WHERE MaterialNo = ?";

	private static final String SEARCH_MATERIAL_SQL = ""
			+ "SELECT materialNo,materialTypeName,materialTypeNo,maModel,maBrand,maMadeIn,buyId,priceUnit,inCount,nowCount,inDate,userCode,inFrom,reMark "
			+ "FROM Material_Table " + "WHERE ";

	private static final String RESEVE_SEARCH_MATERIAL_SQL = ""
			+ "SELECT materialNo,materialTypeName,materialTypeNo,maModel,maBrand,maMadeIn,priceUnit,nowCount,inFrom,inDate,buyId "
			+ "FROM Material_Table " + "WHERE ";

	private static final String BUY_SEARCH_MATERIAL_SQL = ""
			+ "SELECT BuyId,BuyNo,MaterialTypeName,MaModel,MaBrand,MaMadeIn,MaterialTypeNo,PriceUnit,InCount,flag,reflag "
			+ "FROM Material_Buy_Detail "
			+ "WHERE Flag IS NULL AND ReFlag IS NULL ";

	private static final String BUY_INSERT_MATERIAL_SQL = "INSERT INTO "
			+ "Material_Table(materialNo,materialTypeName,materialTypeNo,maModel,maBrand,maMadeIn,buyId,priceUnit,inCount,nowCount,inDate,userCode,inFrom,reMark)"
			+ "VALUES (MaterialNo.Nextval,?,?,?,?,?,?,?,?,?,to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'),?,?,?)";

	/*private static final String CHECK_SEARCH_MATERIAL_SQL = ""+ 
			"SELECT Material_Table.materialNo,Material_Table.materialTypeName,Material_Table.materialTypeNo,Material_Table.maModel,Material_Table.maBrand,Material_Table.maMadeIn,Material_Table.priceUnit,Material_Table.nowCount,Material_Table.inFrom,Material_Table.inDate "+ 
			"FROM Material_Table,Material_Check_Table " + 
			"WHERE Material_Table.MaterialNo = Material_Check_Table.MaterialNo " +
			"AND Material_Table.nowCount > 0 " +
			"AND ";*/
	
	private static final String CHECK_SEARCH_MATERIAL_SQL = ""+ 
					"SELECT distinct Material_Table.materialNo,Material_Table.materialTypeName,Material_Table.materialTypeNo,Material_Table.maModel,Material_Table.maBrand,Material_Table.maMadeIn,Material_Table.priceUnit,Material_Table.nowCount,Material_Table.inFrom,Material_Table.inDate "+ 
					"FROM Material_Table " + 
					"WHERE Material_Table.nowCount > 0 " +
					"AND ";

	private static final String UPDATE_NOWCOUNT_SQL = ""
			+ "UPDATE Material_Table " + "SET nowCount = nowCount - ? "
			+ "WHERE materialNo = ?";
	
	private static final String CHECK_DELECT_MATERIAL_SQL = "" +
			"DELETE FROM Material_Table " +
			"WHERE ROWNUM <= 1 " +
			"AND ";
	private static final String DELETE_OUT_UPDATE_NOWCOUNT_SQL = "" +
			"UPDATE Material_Table " +
			"SET nowCount = nowCount + ? " +
			"WHERE materialNo = ?";
	
	// 方法
	public boolean insertMaterial(MaterialBean mate) throws Exception {
		// 插入库存信息
		boolean flag = false;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(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.getNowCount());
			ps.setDate(10, mate.getInDate());
			ps.setString(11, mate.getUserCode());
			ps.setInt(12, mate.getInFrom());
			ps.setString(13, mate.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 insertMaterialNotDate(MaterialBean mate) throws Exception {
		// 插入库存信息 取系统日期
		boolean flag = false;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(INSERT_MATERIAL_CHECK_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.getNowCount());
			//ps.setDate(10, mate.getInDate());
			ps.setString(10, mate.getUserCode());
			ps.setInt(11, mate.getInFrom());
			ps.setString(12, mate.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 insertCheckMaterial(MaterialBean mate) throws Exception {
		// 
		boolean flag = false;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(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.getNowCount());
			//ps.setDate(10, mate.getInDate());
			ps.setString(10, mate.getUserCode());
			ps.setInt(11, mate.getInFrom());
			ps.setString(12, mate.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 updateMaterial(MaterialBean mate) throws Exception {
		// 更新库存信息
		boolean flag = false;
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(UPDATE_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.getNowCount());
			ps.setDate(10, mate.getInDate());
			ps.setString(11, mate.getUserCode());
			ps.setInt(12, mate.getInFrom());
			ps.setString(13, mate.getReMark());
			ps.setInt(14, mate.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;
	}

	public boolean deleteMaterial(String materialNo) throws Exception {
		// 删除库存信息
		boolean flag = false;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(DELETE_MATERIAL_SQL);
			ps.setString(1, 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 MaterialBean selectMaterial(int materialNo) throws Exception {
		// 查询库存信息
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		MaterialBean mate = null;
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(SELECT_MATERIAL_SQL);
			ps.setInt(1, materialNo);
			rs = ps.executeQuery();
			if (rs.next()) {
				mate = new MaterialBean();
				mate.setMaterialNo(rs.getString("materialNo"));
				mate.setMaterialTypeName(rs.getString("materialTypeName"));
				mate.setMaterialTypeNo(rs.getString("materialTypeNo"));
				mate.setMaModel(rs.getString("maModel"));
				mate.setMaBrand(rs.getString("maBrand"));
				mate.setMaMadeIn(rs.getString("maMadeIn"));
				mate.setBuyId(rs.getInt("buyId"));
				mate.setPriceUnit(rs.getDouble("priceUnit"));
				mate.setInCount(rs.getInt("inCount"));
				mate.setNowCount(rs.getInt("nowCount"));
				mate.setInDate(rs.getDate("inDate"));
				mate.setUserCode(rs.getString("userCode"));
				mate.setInFrom(rs.getInt("inFrom"));
				mate.setReMark(rs.getString("reMark"));
			
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(ps);
			DBConnection.close(conn);
		}
		return mate;
	}

	// public ArrayList searchMaterial(MaterialBean mate) 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(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.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()) + "%'");
	// }
	//		
	// //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();
	// 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();