materialdao.java

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

			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 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 {
		//删除盘点信息 还原库存信息 //根据盘点信息 删除添加的库存信息
		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 ");
		}//产地
		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 ");
		}//入库数量
		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 {
		//删除变更信息 还原库存信息 //根据出库信息 删除添加的库存信息
		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 ");
		}//产地
		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 ");
		}//入库数量
		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;
	}
}