materialbuydetaildao.java

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

		return list;
	}

	public MaterialBuyDetailBean getMaterialBuyDetailOfBuyId(int buyId) {
		MaterialBuyDetailBean mbdb = new MaterialBuyDetailBean();
		dbpool.getConnection();

		String sql = "select BuyId,BuyNo,MaterialTypeName,MaModel,MaBrand,MaMadeIn,MaterialTypeNo,PriceUnit,InCount,Flag,ReFlag from Material_Buy_Detail where BuyId="
				+ buyId;
		try {
			Statement stmt = dbpool.conn.createStatement();
			ResultSet rset = stmt.executeQuery(sql);
			while (rset.next()) {
				mbdb.setBuyId(rset.getInt("BuyId"));
				if (rset.getString("BuyNo") == null) {
					mbdb.setBuyNo("");
				} else {
					mbdb.setBuyNo(rset.getString("BuyNo").trim());
				}
				if (rset.getString("MaterialTypeName") == null) {
					mbdb.setMaterialTypeName("");
				} else {
					mbdb.setMaterialTypeName(rset.getString("MaterialTypeName")
							.trim());
				}
				if (rset.getString("MaModel") == null) {
					mbdb.setMaModel("");
				} else {
					mbdb.setMaModel(rset.getString("MaModel").trim());
				}
				if (rset.getString("Mabrand") == null) {
					mbdb.setMaBrand("");
				} else {
					mbdb.setMaBrand(rset.getString("Mabrand"));
				}
				if (rset.getString("MaMadeIn") == null) {
					mbdb.setMaMadeIn("");
				} else {
					mbdb.setMaMadeIn(rset.getString("MaMadeIn").trim());
				}
				if (rset.getString("MaterialTypeNo") == null) {
					mbdb.setMaterialTypeNo("");
				} else {
					mbdb.setMaterialTypeNo(rset.getString("MaterialTypeNo")
							.trim());
				}
				mbdb.setPriceUnit(rset.getDouble("PriceUnit"));
				mbdb.setInCount(rset.getInt("InCount"));
				if (rset.getString("Flag") == null) {
					mbdb.setFlag("");
				} else {
					mbdb.setFlag(rset.getString("Flag").trim());
				}
				if (rset.getString("ReFlag") == null) {
					mbdb.setReFlag("");
				} else {
					mbdb.setReFlag(rset.getString("ReFlag").trim());
				}
			}
			stmt.close();
			rset.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			dbpool.freeConnection();
		}
		return mbdb;
	}

	public ArrayList getMaterialBuyDetailOfBuyNo(String buyNo) {
		ArrayList list = new ArrayList();
		MaterialBuyDetailBean mbdb = new MaterialBuyDetailBean();
		dbpool.getConnection();

		String sql = "select BuyId,BuyNo,MaterialTypeName,MaModel,MaBrand,MaMadeIn,MaterialTypeNo,PriceUnit,InCount," +
				"Flag,ReFlag from Material_Buy_Detail where BuyNo='" + buyNo +"'";
		try {
			Statement stmt = dbpool.conn.createStatement();
			
			ResultSet rset = stmt.executeQuery(sql);
			
			while (rset.next()) {
				
				mbdb.setBuyId(rset.getInt("BuyId"));
				if (rset.getString("BuyNo") == null) {
					mbdb.setBuyNo("");
				} else {
					mbdb.setBuyNo(rset.getString("BuyNo").trim());
				}
				if (rset.getString("MaterialTypeName") == null) {
					mbdb.setMaterialTypeName("");
				} else {
					mbdb.setMaterialTypeName(rset.getString("MaterialTypeName")
							.trim());
				}
				if (rset.getString("MaModel") == null) {
					mbdb.setMaModel("");
				} else {
					mbdb.setMaModel(rset.getString("MaModel").trim());
				}
				if (rset.getString("Mabrand") == null) {
					mbdb.setMaBrand("");
				} else {
					mbdb.setMaBrand(rset.getString("Mabrand"));
				}
				if (rset.getString("MaMadeIn") == null) {
					mbdb.setMaMadeIn("");
				} else {
					mbdb.setMaMadeIn(rset.getString("MaMadeIn").trim());
				}
				if (rset.getString("MaterialTypeNo") == null) {
					mbdb.setMaterialTypeNo("");
				} else {
					mbdb.setMaterialTypeNo(rset.getString("MaterialTypeNo")
							.trim());
				}
				mbdb.setPriceUnit(rset.getDouble("PriceUnit"));
				mbdb.setInCount(rset.getInt("InCount"));
				if (rset.getString("Flag") == null) {
					mbdb.setFlag("");
				} else {
					mbdb.setFlag(rset.getString("Flag").trim());
				}
				if (rset.getString("ReFlag") == null) {
					mbdb.setReFlag("");
				} else {
					mbdb.setReFlag(rset.getString("ReFlag").trim());
				}
				list.add(mbdb);
			}
			
			stmt.close();
			rset.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		 
		}finally{
			dbpool.freeConnection();
		}
		return list;
	}

	public ArrayList[] SearchMaterialBuy(String startDate, String endDate,
			String MaterialTypeNo, int ApplyDep) {
		ArrayList[] list = new ArrayList[2];
		MaterialBuyDetailBean mbdb = new MaterialBuyDetailBean();
		MaterialBuyBean mb = new MaterialBuyBean();
		dbpool.getConnection();

		String sql = "select mbt.PayType,mbt.RePay,mbt.Acpay,mbt.InDate,"
				+ "mbt.BuyDep,mbt.BuyBy,mbt.ApplyDep,mbt.ApplyBy,mbt.UseDep,"
				+ "mbt.UseBy,mbt.BuyFor,mbt.SupplyNo,mbt.PerBy,mbt.ReMark,mbd.BuyId,"
				+ "mbd.BuyNo,mbd.MaterialTypeName,mbd.MaModel,mbd.MaBrand,"
				+ "mbd.MaMadeIn,mbd.MaterialTypeNo,mbd.PriceUnit,mbd.InCount,"
				+ "mbd.Flag,mbd.ReFlag from Material_Buy_Table mbt,"
				+ "Material_Buy_Detail mbd where mbt.BuyNo=mbd.BuyNo and InDate "
				+ startDate + "between" + endDate
				+ " and mbd.MaterialTypeNo like '%" + MaterialTypeNo
				+ "%' and mbt.ApplyDep =" + ApplyDep;
		try {
			Statement stmt = dbpool.conn.createStatement();
			ResultSet rset = stmt.executeQuery(sql);
			while (rset.next()) {
				mb.setBuyNo(rset.getString("mbd.BuyNo").trim());
				mb.setPayType(rset.getString("mbt.PayType").trim());
				mb.setRePay(rset.getDouble("mbt.Repay"));
				mb.setAcPay(rset.getDouble("mbt.AcPay"));
				mb.setInDate(rset.getString("mbt.InDate").trim());
				mb.setBuyDep(rset.getInt("mbt.BuyDep"));
				if (rset.getString("mbt.BuyBy") == null) {
					mb.setBuyBy("");
				} else {
					mb.setBuyBy(rset.getString("mbt.BuyBy"));
				}
				mb.setApplyDep(rset.getInt("mbt.ApplyDep"));
				if (rset.getString("mbt.ApplyBy") == null) {
					mb.setApplyBy("");
				} else {
					mb.setApplyBy(rset.getString("mbt.ApplyBy").trim());
				}
				mb.setUseDep(rset.getInt("mbt.UseDep"));
				if (rset.getString("mbt.UseBy") == null) {
					mb.setUseBy("");
				} else {
					mb.setUseBy(rset.getString("mbt.UseBy").trim());
				}
				mb.setSupplyNo(rset.getInt("mbt.SupplyNo"));
				if (rset.getString("mbt.PerBy") == null) {
					mb.setPerBy("");
				} else {
					mb.setPerBy(rset.getString("mbt.PerBy").trim());
				}
				if (rset.getString("mbt.ReMark") == null) {
					mb.setReMark("");
				} else {
					mb.setReMark(rset.getString("mbt.ReMark").trim());
				}
				mbdb.setBuyId(rset.getInt("mbd.BuyId"));
				if (rset.getString("mbd.BuyNo") == null) {
					mbdb.setBuyNo("");
				} else {
					mbdb.setBuyNo(rset.getString("mbd.BuyNo").trim());
				}
				if (rset.getString("mbd.MaterialTypeName") == null) {
					mbdb.setMaterialTypeName("");
				} else {
					mbdb.setMaterialTypeName(rset.getString(
							"mbd.MaterialTypeName").trim());
				}
				if (rset.getString("mbd.MaModel") == null) {
					mbdb.setMaModel("");
				} else {
					mbdb.setMaModel(rset.getString("mbd.MaModel").trim());
				}
				if (rset.getString("mbd.Mabrand") == null) {
					mbdb.setMaBrand("");
				} else {
					mbdb.setMaBrand(rset.getString("mbd.Mabrand"));
				}
				if (rset.getString("mbd.MaMadeIn") == null) {
					mbdb.setMaMadeIn("");
				} else {
					mbdb.setMaMadeIn(rset.getString("mbd.MaMadeIn").trim());
				}
				if (rset.getString("mbd.MaterialTypeNo") == null) {
					mbdb.setMaterialTypeNo("");
				} else {
					mbdb.setMaterialTypeNo(rset.getString("mbd.MaterialTypeNo")
							.trim());
				}
				mbdb.setPriceUnit(rset.getDouble("mbd.PriceUnit"));
				mbdb.setInCount(rset.getInt("mbd.InCount"));
				if (rset.getString("mbd.Flag") == null) {
					mbdb.setFlag("");
				} else {
					mbdb.setFlag(rset.getString("mbd.Flag").trim());
				}
				if (rset.getString("mbd.ReFlag") == null) {
					mbdb.setReFlag("");
				} else {
					mbdb.setReFlag(rset.getString("mbd.ReFlag").trim());
				}
				list[0].add(mb);
				list[1].add(mbdb);
			}
			stmt.close();
			rset.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			dbpool.freeConnection();
		}
		return list;
	}

	
	
	
//修改采购明细所有信息
public boolean update(String oldbuyNo,String buyNo) {
		flag = false;
		dbpool.getConnection();
		//String sql = "update Material_Buy_Detail set BuyNo=?,MaterialTypeName=?,MaModel=?,MaBrand=?,MaMadeIn=?,MaterialTypeNo=?,PriceUnit=?,InCount=? where BuyId=?";
		String sql = "update Material_Buy_Detail set BuyNo='";
			sql=sql+buyNo+"'" + " where BuyNo='";
			sql = sql + oldbuyNo +"'";
			//System.out.println("updateDetail:"+sql);//
		try {
			st = dbpool.conn.createStatement();
			flag = st.executeUpdate(sql)>0? true : false;
				
		
			  
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		} finally {
			dbpool.freeConnection();
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				st = null;
			}
		}
	
		return flag;
	}

//根据List明细添加采购明细

public boolean save(ArrayList list){
	flag = false;
	MaterialBuyDetailBean mbdb;
	Iterator it = list.iterator();
	dbpool.getConnection();
	dbpool.setAutoCommit(false);

	while(it.hasNext()){
	mbdb=(MaterialBuyDetailBean)it.next();
	// String sql = "insert into
	// Material_Buy_Detail(BuyId,BuyNo,MaterialTypeName,MaModel,MaBrand,MaMadeIn,MaterialTypeNo,PriceUnit,InCount)
	// values(BuyId.nextval,?,?,?,?,?,?,?,?)";
	String sql = "insert into Material_Buy_Detail(BuyId,BuyNo,MaterialTypeName,MaModel,MaBrand,MaMadeIn,MaterialTypeNo,PriceUnit,InCount) values(BuyId.nextval,'";
	sql = sql + mbdb.getBuyNo() + "','";
	sql = sql + mbdb.getMaterialTypeName() + "','";
	sql = sql + mbdb.getMaModel() + "','";
	sql = sql + mbdb.getMaBrand() + "','";
	sql = sql + mbdb.getMaMadeIn() + "','";
	sql = sql + mbdb.getMaterialTypeNo() + "',";
	sql = sql + mbdb.getPriceUnit() + ",";
	sql = sql + mbdb.getInCount() + ")";


	try {

		st = dbpool.conn.createStatement();
		flag = st.executeUpdate(sql) > 0 ? true : false;
		
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		flag = false;
	} 
	if(flag==false){
	 		dbpool.rollback();
			break;
		}
	}
	dbpool.commit();
		dbpool.freeConnection();
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			pstmt = null;
		}
		
	
	return flag;
}
}