materialchangedao.java

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

		if(materialName != null && materialName.equals("") != true) {
			sql.append("materialName LIKE '%" +
					DBUtil.fixSqlFieldValue(materialName.trim()) + "%' AND ");
		}
		if((startDate != null && startDate.equals("") != true) && (endDate != null && endDate.equals("") != true)) {
			sql.append("OutDate 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();
			rs = stmt.executeQuery(sql.toString());
			MaterialChangeSearchBean mate2 = null;
			
			while(rs.next()) {
//				mate2 = new MaterialChangeBean();
//				mate2.setChangeID(rs.getInt("changeId"));
//				mate2.setChangeType(rs.getInt("changeType"));
//				mate2.setOutNoId(rs.getInt("outNoId"));
//				mate2.setOutCount(rs.getInt("outCount"));
//				mate2.setOutDate(rs.getDate("outDate"));
//				mate2.setUserCode(rs.getString("userCode"));
//				mate2.setOraDep(rs.getInt("oraDep"));
//				mate2.setUseDep(rs.getInt("useDep"));
//				mate2.setManagerBy(rs.getString("managerBy"));
//				mate2.setUseBy(rs.getString("useBy"));
//				mate2.setBuildingNu(rs.getString("buildingNu"));
//				mate2.setChangeCause(rs.getString("changeCause"));
//				mate2.setRoomNu(rs.getString("roomNu"));
//				mate2.setReMark(rs.getString("remark"));
//				array.add(mate2);
				
				mate2 = new MaterialChangeSearchBean();
				mate2.setMaterialName(rs.getString("materialName"));
				mate2.setMaterialTypeNo(rs.getString("materialTypeNo"));
				mate2.setMaBrand(rs.getString("maBrand"));
				mate2.setChangeType(rs.getInt("changeType"));
				mate2.setOutCount(rs.getInt("outCount"));
				mate2.setOraDep(rs.getInt("oraDep"));
				mate2.setUseDep(rs.getInt("useDep"));
				mate2.setOutDate(rs.getDate("outDate"));
				mate2.setChangeCause(rs.getString("changeCause"));
				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 search(MateOutBaseBean mate, MateOutDetailBean mateD, String startDate, String endDate) throws Exception {
		// 多条件查询 添加变更信息使用
		
		ArrayList<MaterialOutSearchBean> array = new ArrayList();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		MaterialTypeDAO mateTypeDAO = new MaterialTypeDAO();
		OrganDAO organDAO = new OrganDAO();
		
		StringBuffer sql = new StringBuffer(512);
		sql.append(	"SELECT Material_Out_Detail.OutNoId,Material_Out_Table.Outno,Material_Out_Table.Usedep,Material_Out_Table.Useby,Material_Out_Detail.Materialname,Material_Out_Detail.Mamodel,Material_Out_Detail.Mabrand,Material_Out_Detail.Priceunit,Material_Out_Detail.Outcount,Material_Out_Detail.Chaflag "+  
					"FROM Material_Out_Table,Material_Out_Detail " + 
					"WHERE Material_Out_Table.OutNo = Material_Out_Detail.OutNo AND ");
//		if(mate.getUseDep() != 0) {
//			sql.append("UseDep = " +
//					DBUtil.fixSqlFieldValue(String.valueOf(mate.getUseDep())) + " AND ");
//		}
		if(mate.getUseDep() != 0) {
			sql.append("(UseDep IN " +organDAO.getOrganSQL(mate.getUseDep(),"UseDep")+") AND ");
		}
//		if(mateD.getMaterialTypeNo() != null && mateD.getMaterialTypeNo().equals("") != true) {
//			sql.append("MaterialTypeNo LIKE '%" +
//					DBUtil.fixSqlFieldValue(mateD.getMaterialTypeNo()) + "%' AND ");
//		}
		if (mateD.getMaterialTypeNo() != null && mateD.getMaterialTypeNo().equals("") != true) {
			sql.append("(MaterialTypeNo IN "+mateTypeDAO.getMaterialSQL(mateD.getMaterialTypeNo())+") AND ");
		}
		if((startDate != null && startDate.equals("") != true) && (endDate != null && endDate.equals("") != true)) {
			sql.append("OutDate BETWEEN TO_DATE('" +
					DBUtil.fixSqlFieldValue(startDate.trim()) + "','YYYY-MM-DD') AND TO_DATE('"+endDate.trim()+"','YYYY-MM-DD') AND ");
		}
		sql.append("Chaflag IS NULL");
		
		
		//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);
		}
		
		//System.out.println(sql.toString());
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql.toString());
			MaterialOutSearchBean mate1 = null;

			while(rs.next()) {
				mate1 = new MaterialOutSearchBean();
				mate1.setOutNoId(rs.getInt("outNoId"));
				mate1.setOutNo(rs.getString("outNo"));
				mate1.setMaterialName(rs.getString("materialName"));
				mate1.setMaModel(rs.getString("maModel"));
				mate1.setMaBrand(rs.getString("maBrand"));
				mate1.setPriceUnit(rs.getDouble("priceUnit"));
				mate1.setOutCount(rs.getInt("outCount"));
				mate1.setUseDep(rs.getInt("useDep"));
				mate1.setUseBy(rs.getString("useBy"));
				mate1.setChaFlag(rs.getString("chaFlag"));
				array.add(mate1);	
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(stmt);
			DBConnection.close(conn);
		}
		return array;	
	}
	public MaterialOutSearchBean getOutBean(int outNo) throws Exception {
		// 返回出库信息
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		MaterialOutSearchBean mate = null;
		
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(SELECT_OUT_MATERIAL_SQL);
			ps.setInt(1, outNo);
			rs = ps.executeQuery();
			if(rs.next()) {
				mate = new MaterialOutSearchBean();
				mate.setOutNoId(rs.getInt("outNoId"));
				mate.setOutNo(rs.getString("outNo"));
				mate.setMaterialName(rs.getString("materialName"));
				mate.setMaModel(rs.getString("maModel"));
				mate.setMaBrand(rs.getString("maBrand"));
				mate.setPriceUnit(rs.getDouble("priceUnit"));
				mate.setOutCount(rs.getInt("outCount"));
				mate.setUseDep(rs.getInt("useDep"));
				mate.setUseBy(rs.getString("useBy"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DBConnection.close(rs);
			DBConnection.close(ps);
			DBConnection.close(conn);
		}
		return mate;
	}
	public MaterialChangeBean selectMaterialChangeForOutNoId(int outNoId) throws Exception {
		// 根据出库编号 查询 变更信息
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		MaterialChangeBean mate = null;
		
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(SELECT_OUT_CHANGE_MATERIAL_SQL);
			ps.setInt(1, outNoId);
			rs = ps.executeQuery();
			if(rs.next()) {
				mate = new MaterialChangeBean();
				mate.setChangeID(rs.getInt("changeId"));
				mate.setChangeType(rs.getInt("changeType"));
				mate.setOutNoId(rs.getInt("outNoId"));
				mate.setOutCount(rs.getInt("outCount"));
				mate.setOutDate(rs.getDate("outDate"));
				mate.setUserCode(rs.getString("userCode"));
				mate.setOraDep(rs.getInt("oraDep"));
				mate.setUseDep(rs.getInt("useDep"));
				mate.setManagerBy(rs.getString("managerBy"));
				mate.setUseBy(rs.getString("useBy"));
				mate.setBuildingNu(rs.getString("buildingNu"));
				mate.setChangeCause(rs.getString("changeCause"));
				mate.setRoomNu(rs.getString("roomNu"));
				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;
	}
}