purchaseda.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.PurAllShowBean;
import com.captainli.bean.PurchaseBean;
import com.captainli.util.GetConnection;

/**
 * purchase入库表数据库操作类
 * @author CaptainLi
 *
 */
public class PurchaseDA {
	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 time
	 * @return
	 */
	public String selectMaxNo(String time){
		String tmp = "";
		String sql = "select max(p_no) pno from purchase where p_no like ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+time+"%");
			rs = pstmt.executeQuery();
			if(rs.next()){
				tmp = rs.getString("pno");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
	/**
	 * 入库
	 * @param bean
	 */
	public void addPur(PurchaseBean bean){
		String sql = "insert into purchase (p_no, p_type, p_i_id, p_l_id, p_w_id, p_p_id, p_prodate, p_u_id, p_effdate, p_quantity, p_price, p_amount, p_actual, p_b_id, p_note, p_ver, p_time, p_mongeytype) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bean.getP_no());
			pstmt.setInt(2, bean.getP_type());
			pstmt.setInt(3, bean.getP_i_id());
			pstmt.setInt(4, bean.getP_l_id());
			pstmt.setInt(5, bean.getP_w_id());
			pstmt.setInt(6, bean.getP_p_id());
			pstmt.setString(7, bean.getP_prodate());
			pstmt.setInt(8, bean.getP_u_id());
			pstmt.setInt(9, bean.getP_effdate());
			pstmt.setInt(10, bean.getP_quantity());
			pstmt.setDouble(11, bean.getP_price());
			pstmt.setDouble(12, bean.getP_amount());
			pstmt.setDouble(13, bean.getP_actual());
			pstmt.setInt(14, bean.getP_b_id());
			pstmt.setString(15, bean.getP_note());
			pstmt.setInt(16, bean.getP_ver());
			pstmt.setString(17, bean.getP_time());
			pstmt.setBoolean(18, bean.isP_mongeytype());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}		
	}
	/**
	 * 入库记录查询所有集合
	 * @return
	 */
	public ArrayList showByW_id(){
		PurchaseBean bean = null;
		ArrayList arry = new ArrayList();
		String sql = "select * from purchase where p_type = 1 order by p_time desc";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				bean = new PurchaseBean();
				bean.setP_id(rs.getInt("p_id"));
				bean.setP_no(rs.getString("p_no"));
				bean.setP_time(rs.getString("p_time"));
				bean.setP_quantity(rs.getInt("p_quantity"));
				bean.setP_price(rs.getDouble("p_price"));
				bean.setP_amount(rs.getDouble("p_amount"));
				arry.add(bean);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 入库记录查询显示通过w_id返回集合
	 * @param w_id
	 * @return
	 */
	public ArrayList showByW_id(int w_id){
		PurchaseBean bean = null;
		ArrayList arry = new ArrayList();
		String sql = "select * from purchase where p_w_id = ? and p_type = 1 order by p_time desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, w_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new PurchaseBean();
				bean.setP_id(rs.getInt("p_id"));
				bean.setP_no(rs.getString("p_no"));
				bean.setP_time(rs.getString("p_time"));
				bean.setP_quantity(rs.getInt("p_quantity"));
				bean.setP_price(rs.getDouble("p_price"));
				bean.setP_amount(rs.getDouble("p_amount"));
				arry.add(bean);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过入库单据编号查询返回集合
	 * @param p_no
	 * @return
	 */
	public ArrayList showByP_no(String p_no){
		PurchaseBean bean = null;
		ArrayList arry = new ArrayList();
		String sql = "select * from purchase where p_no like ? and p_type = 1 order by p_time desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+p_no+"%");
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new PurchaseBean();
				bean.setP_id(rs.getInt("p_id"));
				bean.setP_no(rs.getString("p_no"));
				bean.setP_time(rs.getString("p_time"));
				bean.setP_quantity(rs.getInt("p_quantity"));
				bean.setP_price(rs.getDouble("p_price"));
				bean.setP_amount(rs.getDouble("p_amount"));
				arry.add(bean);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过时间段返回入库单集合
	 * @param starttime
	 * @param endtime
	 * @return
	 */
	public ArrayList showByP_time(String starttime, String endtime){
		PurchaseBean bean = null;
		ArrayList arry = new ArrayList();
		String sql = "select * from purchase where p_time between ? and ? and p_type = 1 order by p_time desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, starttime);
			pstmt.setString(2, endtime);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new PurchaseBean();
				bean.setP_id(rs.getInt("p_id"));
				bean.setP_no(rs.getString("p_no"));
				bean.setP_time(rs.getString("p_time"));
				bean.setP_quantity(rs.getInt("p_quantity"));
				bean.setP_price(rs.getDouble("p_price"));
				bean.setP_amount(rs.getDouble("p_amount"));
				arry.add(bean);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 返回需要审核的入库单
	 * @return
	 */
	public ArrayList showWite(int l_id){
		PurchaseBean bean = null;
		ArrayList arry = new ArrayList();
		String sql = "select * from purchase where p_type = 0 and p_ver = ? order by p_time desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, l_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new PurchaseBean();
				bean.setP_id(rs.getInt("p_id"));
				bean.setP_no(rs.getString("p_no"));
				bean.setP_time(rs.getString("p_time"));
				bean.setP_quantity(rs.getInt("p_quantity"));
				bean.setP_price(rs.getDouble("p_price"));
				bean.setP_amount(rs.getDouble("p_amount"));
				arry.add(bean);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过p_id找到入库单据
	 * @param p_id
	 * @return
	 */
	public PurchaseBean showByP_id(int p_id){
		PurchaseBean bean = null;
		String sql = "select * from purchase where p_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new PurchaseBean();
				bean.setP_id(rs.getInt("p_id"));
				bean.setP_no(rs.getString("p_no"));
				bean.setP_time(rs.getString("p_time"));
				bean.setP_quantity(rs.getInt("p_quantity"));
				bean.setP_price(rs.getDouble("p_price"));
				bean.setP_amount(rs.getDouble("p_amount"));
				bean.setP_b_id(rs.getInt("p_b_id"));
				bean.setP_prodate(rs.getString("p_prodate"));
				bean.setP_w_id(rs.getInt("p_w_id"));
				bean.setP_i_id(rs.getInt("p_i_id"));
				bean.setP_effdate(rs.getInt("p_effdate"));
				bean.setP_p_id(rs.getInt("p_p_id"));