raidda.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.RaidBean;
import com.captainli.util.GetConnection;

/**
 * 批次表raid数据库操作类
 * @author CaptainLi
 *
 */
public class RaidDA {
	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 addRaid(RaidBean bean){
		String sql = "insert into raid values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bean.getR_no());
			pstmt.setInt(2, bean.getR_p_id());
			pstmt.setInt(3, bean.getR_quantity());
			pstmt.setDouble(4, bean.getR_price());
			pstmt.setDouble(5, bean.getR_amount());
			pstmt.setString(6, bean.getR_time());
			pstmt.setString(7, bean.getR_prodate());
			pstmt.setString(8, bean.getR_due());
			pstmt.setInt(9, bean.getR_w_id());
			pstmt.setInt(10, bean.getR_i_id());
			pstmt.setInt(11, bean.getR_b_id());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 通过批号查到批次信息
	 * @param r_no
	 * @return
	 */
	public RaidBean showRaidByR_no(String r_no){
		String sql = "select * from raid where r_no = ?";
		RaidBean bean = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, r_no);
			rs = pstmt.executeQuery();
			if(rs.next()){
				bean = new RaidBean();
				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_time(rs.getString("r_time"));
				bean.setR_prodate(rs.getString("r_prodate"));
				bean.setR_due(rs.getString("r_due"));
				bean.setR_i_id(rs.getInt("r_i_id"));
				bean.setR_w_id(rs.getInt("r_w_id"));
				bean.setR_b_id(rs.getInt("r_b_id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return bean;
	}
	/**
	 * 通过批次id查到批次信息
	 * @param r_no
	 * @return
	 */
	public RaidBean showRaidByR_id(int r_id){
		String sql = "select * from raid where r_id = ?";
		RaidBean bean = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, r_id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				bean = new RaidBean();
				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_time(rs.getString("r_time"));
				bean.setR_prodate(rs.getString("r_prodate"));
				bean.setR_due(rs.getString("r_due"));
				bean.setR_i_id(rs.getInt("r_i_id"));
				bean.setR_w_id(rs.getInt("r_w_id"));
				bean.setR_b_id(rs.getInt("r_b_id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return bean;
	}
	/**
	 * 单据红冲后删除响应的库存
	 */
	public void delHongChongRaid(String p_no){
		String sql = "delete raid where r_no like ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+p_no.substring(4)+"%");
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 通过仓库id和药品id返回批次
	 * @param r_p_id
	 * @param r_w_id
	 * @return
	 */
	public ArrayList showRaidR_no(int r_p_id, int r_w_id){
		ArrayList arry = new ArrayList();
		RaidBean bean = null;
		String sql = "select r_no from raid where r_p_id = ? and r_w_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, r_p_id);
			pstmt.setInt(2, r_w_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new RaidBean();
				bean.setR_no(rs.getString("r_no"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过药品id返回批次集合
	 * @param r_p_id
	 * @return
	 */
	public ArrayList showRaidR_no(int r_p_id){
		ArrayList arry = new ArrayList();
		RaidBean bean = null;
		String sql = "select r_id, r_no from raid where r_p_id = ? order by r_id";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, r_p_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new RaidBean();
				bean.setR_id(rs.getInt("r_id"));
				bean.setR_no(rs.getString("r_no"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 修改批次库存数量
	 * @param r_id
	 */
	public void updateRaid(int r_quantity, int r_id){
		String sql = "update raid set r_quantity = r_quantity - ? where r_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, r_quantity);
			pstmt.setInt(2, r_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 修改批次数量减少后的总金额
	 * @param r_amount
	 * @param r_id
	 */
	public void updateRaid(double r_amount, int r_id){
		String sql = "update raid set r_amount = ? where r_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, r_amount);
			pstmt.setInt(2, r_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 检查有没有新的批次生成
	 * @param r_no
	 * @param r_p_id
	 * @param r_w_id
	 * @return
	 */
	public boolean checkR_no(String r_no, int r_p_id, int r_w_id){
		boolean flag = false;
		String sql = "select * from raid where r_no = ? and r_p_id = ? and r_w_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, r_no);
			pstmt.setInt(2, r_p_id);
			pstmt.setInt(3, r_w_id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return flag;
	}
	/**
	 * 返回有新批次生成的信息
	 * @param r_no
	 * @param r_p_id
	 * @param r_w_id
	 * @return
	 */
	public RaidBean showcheckR_no(String r_no, int r_p_id, int r_w_id){
		boolean flag = false;
		String sql = "select * from raid where r_no = ? and r_p_id = ? and r_w_id = ?";
		RaidBean bean = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, r_no);
			pstmt.setInt(2, r_p_id);
			pstmt.setInt(3, r_w_id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				bean = new RaidBean();
				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_time(rs.getString("r_time"));
				bean.setR_prodate(rs.getString("r_prodate"));
				bean.setR_due(rs.getString("r_due"));
				bean.setR_i_id(rs.getInt("r_i_id"));
				bean.setR_w_id(rs.getInt("r_w_id"));
				bean.setR_b_id(rs.getInt("r_b_id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return bean;
	}
	//如果有库存批次，修改批次信息
	public void updateRaid(int r_quantity, double r_amount, String r_no, int r_p_id, int r_w_id){
		String sql = "update raid set r_quantity = r_quantity + ?, r_amount = ? where r_no = ? and r_p_id = ? and r_w_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, r_quantity);
			pstmt.setDouble(2, r_amount);
			pstmt.setString(3, r_no);
			pstmt.setInt(4, r_p_id);
			pstmt.setInt(5, r_w_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
}