appropriationda.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.AppropriationBean;
import com.captainli.util.GetConnection;

/**
 * appropriation库存调拨表数据库操作类
 * @author CaptainLi
 *
 */
public class AppropriationDA {
	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();
		}
	}
	/**
	 * 库存记录生成
	 * @param bean
	 */
	public void addAppro(AppropriationBean bean){
		String sql = "insert into appropriation values (?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bean.getA_r_no());
			pstmt.setInt(2, bean.getA_gow());
			pstmt.setInt(3, bean.getA_tow());
			pstmt.setInt(4, bean.getA_p_id());
			pstmt.setInt(5, bean.getA_quantity());
			pstmt.setDouble(6, bean.getA_price());
			pstmt.setDouble(7, bean.getA_amount());
			pstmt.setString(8, bean.getA_time());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 返回所有调拨信息集合
	 * @return
	 */
	public ArrayList showApp(){
		ArrayList arry = new ArrayList();
		AppropriationBean bean = null;
		String sql = "SELECT appropriation.*, produit.p_name FROM appropriation INNER JOIN produit ON appropriation.a_p_id = produit.p_id order by a_time desc";
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new AppropriationBean();
				bean.setA_id(rs.getInt("a_id"));
				bean.setA_r_no(rs.getString("a_r_no"));
				bean.setA_gow(rs.getInt("a_gow"));
				bean.setA_tow(rs.getInt("a_tow"));
				bean.setP_name(rs.getString("p_name"));
				bean.setA_quantity(rs.getInt("a_quantity"));
				bean.setA_price(rs.getDouble("a_price"));
				bean.setA_amount(rs.getDouble("a_amount"));
				bean.setA_time(rs.getString("a_time"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过批次返回调拨集合
	 * @return
	 */
	public ArrayList showAppByA_no(String a_r_no){
		ArrayList arry = new ArrayList();
		AppropriationBean bean = null;
		String sql = "SELECT appropriation.*, produit.p_name FROM appropriation INNER JOIN produit ON appropriation.a_p_id = produit.p_id WHERE a_r_no like ? order by a_time desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+a_r_no+"%");
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new AppropriationBean();
				bean.setA_id(rs.getInt("a_id"));
				bean.setA_r_no(rs.getString("a_r_no"));
				bean.setA_gow(rs.getInt("a_gow"));
				bean.setA_tow(rs.getInt("a_tow"));
				bean.setP_name(rs.getString("p_name"));
				bean.setA_quantity(rs.getInt("a_quantity"));
				bean.setA_price(rs.getDouble("a_price"));
				bean.setA_amount(rs.getDouble("a_amount"));
				bean.setA_time(rs.getString("a_time"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过药品id返回调拨信息集合
	 * @param a_p_id
	 * @return
	 */
	public ArrayList showAppByA_p_id(int a_p_id){
		ArrayList arry = new ArrayList();
		AppropriationBean bean = null;
		String sql = "SELECT appropriation.*, produit.p_name FROM appropriation INNER JOIN produit ON appropriation.a_p_id = produit.p_id where a_p_id = ? order by a_time desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, a_p_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new AppropriationBean();
				bean.setA_id(rs.getInt("a_id"));
				bean.setA_r_no(rs.getString("a_r_no"));
				bean.setA_gow(rs.getInt("a_gow"));
				bean.setA_tow(rs.getInt("a_tow"));
				bean.setP_name(rs.getString("p_name"));
				bean.setA_quantity(rs.getInt("a_quantity"));
				bean.setA_price(rs.getDouble("a_price"));
				bean.setA_amount(rs.getDouble("a_amount"));
				bean.setA_time(rs.getString("a_time"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过时间段查找调拨信息
	 * @param starttime
	 * @param endtime
	 * @return
	 */
	public ArrayList showAppByA_time(String starttime, String endtime){
		ArrayList arry = new ArrayList();
		AppropriationBean bean = null;
		String sql = "SELECT appropriation.*, produit.p_name FROM appropriation INNER JOIN produit ON appropriation.a_p_id = produit.p_id where a_time between ? and ? order by a_time desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, starttime);
			pstmt.setString(2, endtime);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new AppropriationBean();
				bean.setA_id(rs.getInt("a_id"));
				bean.setA_r_no(rs.getString("a_r_no"));
				bean.setA_gow(rs.getInt("a_gow"));
				bean.setA_tow(rs.getInt("a_tow"));
				bean.setP_name(rs.getString("p_name"));
				bean.setA_quantity(rs.getInt("a_quantity"));
				bean.setA_price(rs.getDouble("a_price"));
				bean.setA_amount(rs.getDouble("a_amount"));
				bean.setA_time(rs.getString("a_time"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
}