stockda.java

医药供应链管理系统

package com.captainli.dboperation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.captainli.bean.StockPagesBean;
import com.captainli.util.GetConnection;

/**
 * 库存 数据库操作类
 * @author CaptainLi
 *
 */
public class StockDA {
	private Connection conn = GetConnection.getConn();
	private PreparedStatement pstmt = null;
	private Statement stmt = null;
	private ResultSet rs = null;
	/**
	 * 关闭数据库对象
	 *
	 */
	public void closeDB(){
		try {
			if(rs != null){
				rs.close();
			}
			if(stmt != null){
				stmt.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
			if(conn != null){
				conn.close();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
	}
	/**
	 * 返回库存总金额
	 * @return
	 */
	public double showSumAmount(){
		double tmp = 0;
		String sql = "select sum(r_amount) amount_sum from raid";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next()){
				tmp = rs.getDouble("amount_sum");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
	/**
	 * 返回所有库存
	 * @return
	 */
	public ArrayList showAllStock(){
		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 " +
				"ORDER BY r_no desc";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			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_sc_id
	 * @return
	 */
	public ArrayList showAllByP_sc_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 smallclass.sc_id = ? ORDER BY r_no desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, 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_bc_id
	 * @return
	 */
	public ArrayList showAllByP_bc_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 bigclass.bc_id = ? ORDER BY r_no desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, 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_w_id
	 * @return
	 */
	public ArrayList showAllByP_w_id(int p_w_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 = ? ORDER BY r_no desc";
		try {