materialdao.java

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

	// 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());