stockda.java

医药供应链管理系统

			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_w_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new StockPagesBean();
				bean.setR_id(rs.getInt("r_id"));
				bean.setR_no(rs.getString("r_no"));
				bean.setR_p_id(rs.getInt("r_p_id"));
				bean.setR_quantity(rs.getInt("r_quantity"));
				bean.setR_price(rs.getDouble("r_price"));
				bean.setR_amount(rs.getDouble("r_amount"));
				bean.setR_time(rs.getString("r_time"));
				bean.setR_prodate(rs.getString("r_prodate"));
				bean.setR_due(rs.getString("r_due"));
				bean.setW_name(rs.getString("w_name"));
				bean.setI_name(rs.getString("i_name"));
				bean.setP_name(rs.getString("p_name"));
				bean.setBc_name(rs.getString("bc_name"));
				bean.setSc_name(rs.getString("sc_name"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过仓库和小类查询返回库存集合
	 * @param p_w_id
	 * @param p_sc_id
	 * @return
	 */
	public ArrayList showAllByP_w_idAndP_sc_id(int p_w_id, int p_sc_id){
		ArrayList arry = new ArrayList();
		StockPagesBean bean = null;
		String sql = "SELECT raid.r_id, raid.r_no, raid.r_p_id, raid.r_quantity, raid.r_price, raid.r_amount, raid.r_time, raid.r_prodate, raid.r_due, warehouse.w_name, intercourse.i_name, produit.p_name, bigclass.bc_name, smallclass.sc_name " +
				"FROM intercourse INNER JOIN raid ON intercourse.i_id = raid.r_i_id INNER JOIN produit ON raid.r_p_id = produit.p_id INNER JOIN warehouse ON raid.r_w_id = warehouse.w_id INNER JOIN bigclass ON produit.p_bc_id = bigclass.bc_id INNER JOIN smallclass ON produit.p_sc_id = smallclass.sc_id AND bigclass.bc_id = smallclass.sc_bc_id " +
				"WHERE warehouse.w_id = ? AND smallclass.sc_id = ? ORDER BY r_no desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_w_id);
			pstmt.setInt(2, p_sc_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new StockPagesBean();
				bean.setR_id(rs.getInt("r_id"));
				bean.setR_no(rs.getString("r_no"));
				bean.setR_p_id(rs.getInt("r_p_id"));
				bean.setR_quantity(rs.getInt("r_quantity"));
				bean.setR_price(rs.getDouble("r_price"));
				bean.setR_amount(rs.getDouble("r_amount"));
				bean.setR_time(rs.getString("r_time"));
				bean.setR_prodate(rs.getString("r_prodate"));
				bean.setR_due(rs.getString("r_due"));
				bean.setW_name(rs.getString("w_name"));
				bean.setI_name(rs.getString("i_name"));
				bean.setP_name(rs.getString("p_name"));
				bean.setBc_name(rs.getString("bc_name"));
				bean.setSc_name(rs.getString("sc_name"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过仓库和大类查询返回库存集合
	 * @param p_w_id
	 * @param p_bc_id
	 * @return
	 */
	public ArrayList showAllByP_w_idAndP_bc_id(int p_w_id, int p_bc_id){
		ArrayList arry = new ArrayList();
		StockPagesBean bean = null;
		String sql = "SELECT raid.r_id, raid.r_no, raid.r_p_id, raid.r_quantity, raid.r_price, raid.r_amount, raid.r_time, raid.r_prodate, raid.r_due, warehouse.w_name, intercourse.i_name, produit.p_name, bigclass.bc_name, smallclass.sc_name " +
				"FROM intercourse INNER JOIN raid ON intercourse.i_id = raid.r_i_id INNER JOIN produit ON raid.r_p_id = produit.p_id INNER JOIN warehouse ON raid.r_w_id = warehouse.w_id INNER JOIN bigclass ON produit.p_bc_id = bigclass.bc_id INNER JOIN smallclass ON produit.p_sc_id = smallclass.sc_id AND bigclass.bc_id = smallclass.sc_bc_id " +
				"WHERE warehouse.w_id = ? AND bigclass.bc_id = ? ORDER BY r_no desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_w_id);
			pstmt.setInt(2, p_bc_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new StockPagesBean();
				bean.setR_id(rs.getInt("r_id"));
				bean.setR_no(rs.getString("r_no"));
				bean.setR_p_id(rs.getInt("r_p_id"));
				bean.setR_quantity(rs.getInt("r_quantity"));
				bean.setR_price(rs.getDouble("r_price"));
				bean.setR_amount(rs.getDouble("r_amount"));
				bean.setR_time(rs.getString("r_time"));
				bean.setR_prodate(rs.getString("r_prodate"));
				bean.setR_due(rs.getString("r_due"));
				bean.setW_name(rs.getString("w_name"));
				bean.setI_name(rs.getString("i_name"));
				bean.setP_name(rs.getString("p_name"));
				bean.setBc_name(rs.getString("bc_name"));
				bean.setSc_name(rs.getString("sc_name"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过药品名称或者拼音玛模糊查询返回库存集合
	 * @param p_name
	 * @return
	 */
	public ArrayList showAllByP_name(String p_name){
		ArrayList arry = new ArrayList();
		StockPagesBean bean = null;
		String sql = "SELECT raid.r_id, raid.r_no, raid.r_p_id, raid.r_quantity, raid.r_price, raid.r_amount, raid.r_time, raid.r_prodate, raid.r_due, warehouse.w_name, intercourse.i_name, produit.p_name, bigclass.bc_name, smallclass.sc_name " +
				"FROM intercourse INNER JOIN raid ON intercourse.i_id = raid.r_i_id INNER JOIN produit ON raid.r_p_id = produit.p_id INNER JOIN warehouse ON raid.r_w_id = warehouse.w_id INNER JOIN bigclass ON produit.p_bc_id = bigclass.bc_id INNER JOIN smallclass ON produit.p_sc_id = smallclass.sc_id AND bigclass.bc_id = smallclass.sc_bc_id " +
				"WHERE produit.p_name like ? or produit.p_pinyin like ? ORDER BY r_no desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+p_name+"%");
			pstmt.setString(2, "%"+p_name+"%");
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new StockPagesBean();
				bean.setR_id(rs.getInt("r_id"));
				bean.setR_no(rs.getString("r_no"));
				bean.setR_p_id(rs.getInt("r_p_id"));
				bean.setR_quantity(rs.getInt("r_quantity"));
				bean.setR_price(rs.getDouble("r_price"));
				bean.setR_amount(rs.getDouble("r_amount"));
				bean.setR_time(rs.getString("r_time"));
				bean.setR_prodate(rs.getString("r_prodate"));
				bean.setR_due(rs.getString("r_due"));
				bean.setW_name(rs.getString("w_name"));
				bean.setI_name(rs.getString("i_name"));
				bean.setP_name(rs.getString("p_name"));
				bean.setBc_name(rs.getString("bc_name"));
				bean.setSc_name(rs.getString("sc_name"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过ID返回批次库存明细
	 * @param r_id
	 * @return
	 */
	public StockPagesBean showStockByR_id(int r_id){
		StockPagesBean bean = null;
		String sql = "SELECT raid.r_id, raid.r_no, raid.r_p_id, raid.r_quantity, raid.r_price, raid.r_amount, raid.r_time, raid.r_prodate, raid.r_due, warehouse.w_name, intercourse.i_name, produit.p_name, bigclass.bc_name, smallclass.sc_name " +
		"FROM intercourse INNER JOIN raid ON intercourse.i_id = raid.r_i_id INNER JOIN produit ON raid.r_p_id = produit.p_id INNER JOIN warehouse ON raid.r_w_id = warehouse.w_id INNER JOIN bigclass ON produit.p_bc_id = bigclass.bc_id INNER JOIN smallclass ON produit.p_sc_id = smallclass.sc_id AND bigclass.bc_id = smallclass.sc_bc_id " +
		"WHERE raid.r_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, r_id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				bean = new StockPagesBean();
				bean.setR_id(rs.getInt("r_id"));
				bean.setR_no(rs.getString("r_no"));
				bean.setR_p_id(rs.getInt("r_p_id"));
				bean.setR_quantity(rs.getInt("r_quantity"));
				bean.setR_price(rs.getDouble("r_price"));
				bean.setR_amount(rs.getDouble("r_amount"));
				bean.setR_time(rs.getString("r_time"));
				bean.setR_prodate(rs.getString("r_prodate"));
				bean.setR_due(rs.getString("r_due"));
				bean.setW_name(rs.getString("w_name"));
				bean.setI_name(rs.getString("i_name"));
				bean.setP_name(rs.getString("p_name"));
				bean.setBc_name(rs.getString("bc_name"));
				bean.setSc_name(rs.getString("sc_name"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return bean;
	}
}