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

/**
 * 出库单sells表数据库操作类
 * @author CaptainLi
 *
 */
public class SellsDA {
	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(s_no) sno from sells where s_no like ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+time+"%");
			rs = pstmt.executeQuery();
			if(rs.next()){
				tmp = rs.getString("sno");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
	/**
	 * 出库单生成
	 * @param bean
	 */
	public void addSells(SellsBean bean){
		String sql = "insert into sells (s_no, s_type, s_i_id, s_l_id, s_w_id, s_p_id, s_u_id, s_raidtime, s_prodate, s_durdate, s_r_no, s_purprice, s_i_name, s_quantity, s_price, s_amount, s_actual, s_b_id, s_note, s_ver, s_time, s_mongeytype) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bean.getS_no());
			pstmt.setInt(2, bean.getS_type());
			pstmt.setInt(3, bean.getS_i_id());
			pstmt.setInt(4, bean.getS_l_id());
			pstmt.setInt(5, bean.getS_w_id());
			pstmt.setInt(6, bean.getS_p_id());
			pstmt.setInt(7, bean.getS_u_id());
			pstmt.setString(8, bean.getS_raidtime());
			pstmt.setString(9, bean.getS_prodate());
			pstmt.setString(10, bean.getS_durdate());
			pstmt.setString(11, bean.getS_r_no());
			pstmt.setDouble(12, bean.getS_purprice());
			pstmt.setString(13, bean.getS_i_name());
			pstmt.setInt(14, bean.getS_quantity());
			pstmt.setDouble(15, bean.getS_price());
			pstmt.setDouble(16, bean.getS_amount());
			pstmt.setDouble(17, bean.getS_actual());
			pstmt.setInt(18, bean.getS_b_id());
			pstmt.setString(19, bean.getS_note());
			pstmt.setInt(20, bean.getS_ver());
			pstmt.setString(21, bean.getS_time());
			pstmt.setBoolean(22, bean.isS_mongeytype());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 返回所有审核通过的出库单集合
	 * @return
	 */
	public ArrayList showSells(){
		String sql = "SELECT sells.*, intercourse.i_name, warehouse.w_name, produit.p_name, bank.b_name, unit.u_name FROM sells INNER JOIN intercourse ON sells.s_i_id = intercourse.i_id INNER JOIN warehouse ON sells.s_w_id = warehouse.w_id INNER JOIN produit ON sells.s_p_id = produit.p_id INNER JOIN unit ON sells.s_u_id = unit.u_id INNER JOIN bank ON sells.s_b_id = bank.b_id where s_type = 1 order by s_time desc";
		ArrayList arry = new ArrayList();
		SellsBean bean = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				bean = new SellsBean();
				bean.setS_id(rs.getInt("s_id"));
				bean.setS_i_id(rs.getInt("s_i_id"));
				bean.setS_no(rs.getString("s_no"));
				bean.setI_name(rs.getString("i_name"));
				bean.setB_name(rs.getString("b_name"));
				bean.setP_name(rs.getString("p_name"));
				bean.setS_l_id(rs.getInt("s_l_id"));
				bean.setU_name(rs.getString("u_name"));
				bean.setS_raidtime(rs.getString("s_raidtime"));
				bean.setS_prodate(rs.getString("s_prodate"));
				bean.setS_durdate(rs.getString("s_durdate"));
				bean.setS_r_no(rs.getString("s_r_no"));
				bean.setW_name(rs.getString("w_name"));
				bean.setS_purprice(rs.getDouble("s_purprice"));
				bean.setS_i_name(rs.getString("s_i_name"));
				bean.setS_quantity(rs.getInt("s_quantity"));
				bean.setS_price(rs.getDouble("s_price"));
				bean.setS_amount(rs.getDouble("s_amount"));
				bean.setS_actual(rs.getDouble("s_actual"));
				bean.setS_note(rs.getString("s_note"));
				bean.setS_ver(rs.getInt("s_ver"));
				bean.setS_time(rs.getString("s_time"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过出库仓库返回出库单集合
	 * @param s_w_id
	 * @return
	 */
	public ArrayList showSellsByW_id(int s_w_id){
		ArrayList arry = new ArrayList();
		SellsBean bean = null;
		String sql = "SELECT sells.*, intercourse.i_name, warehouse.w_name, produit.p_name, bank.b_name, unit.u_name FROM sells INNER JOIN intercourse ON sells.s_i_id = intercourse.i_id INNER JOIN warehouse ON sells.s_w_id = warehouse.w_id INNER JOIN produit ON sells.s_p_id = produit.p_id INNER JOIN unit ON sells.s_u_id = unit.u_id INNER JOIN bank ON sells.s_b_id = bank.b_id where s_w_id = ? and  s_type = 1 order by s_time desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, s_w_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new SellsBean();
				bean.setS_id(rs.getInt("s_id"));
				bean.setS_i_id(rs.getInt("s_i_id"));
				bean.setS_no(rs.getString("s_no"));
				bean.setI_name(rs.getString("i_name"));
				bean.setB_name(rs.getString("b_name"));
				bean.setP_name(rs.getString("p_name"));
				bean.setS_l_id(rs.getInt("s_l_id"));
				bean.setU_name(rs.getString("u_name"));
				bean.setS_raidtime(rs.getString("s_raidtime"));
				bean.setS_prodate(rs.getString("s_prodate"));
				bean.setS_durdate(rs.getString("s_durdate"));
				bean.setS_r_no(rs.getString("s_r_no"));
				bean.setW_name(rs.getString("w_name"));
				bean.setS_purprice(rs.getDouble("s_purprice"));
				bean.setS_i_name(rs.getString("s_i_name"));
				bean.setS_quantity(rs.getInt("s_quantity"));
				bean.setS_price(rs.getDouble("s_price"));
				bean.setS_amount(rs.getDouble("s_amount"));
				bean.setS_actual(rs.getDouble("s_actual"));
				bean.setS_note(rs.getString("s_note"));
				bean.setS_ver(rs.getInt("s_ver"));
				bean.setS_time(rs.getString("s_time"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过单据编号查询返回所有出库单集合
	 * @param s_no
	 * @return
	 */
	public ArrayList showSellsByP_no(String s_no){
		String sql = "SELECT sells.*, intercourse.i_name, warehouse.w_name, produit.p_name, bank.b_name, unit.u_name FROM sells INNER JOIN intercourse ON sells.s_i_id = intercourse.i_id INNER JOIN warehouse ON sells.s_w_id = warehouse.w_id INNER JOIN produit ON sells.s_p_id = produit.p_id INNER JOIN unit ON sells.s_u_id = unit.u_id INNER JOIN bank ON sells.s_b_id = bank.b_id where s_type = 1 and s_no like ? order by s_time desc";
		ArrayList arry = new ArrayList();
		SellsBean bean = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+s_no+"%");
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new SellsBean();
				bean.setS_id(rs.getInt("s_id"));
				bean.setS_i_id(rs.getInt("s_i_id"));
				bean.setS_no(rs.getString("s_no"));
				bean.setI_name(rs.getString("i_name"));
				bean.setB_name(rs.getString("b_name"));
				bean.setP_name(rs.getString("p_name"));
				bean.setS_l_id(rs.getInt("s_l_id"));
				bean.setU_name(rs.getString("u_name"));
				bean.setS_raidtime(rs.getString("s_raidtime"));
				bean.setS_prodate(rs.getString("s_prodate"));
				bean.setS_durdate(rs.getString("s_durdate"));
				bean.setS_r_no(rs.getString("s_r_no"));
				bean.setW_name(rs.getString("w_name"));
				bean.setS_purprice(rs.getDouble("s_purprice"));
				bean.setS_i_name(rs.getString("s_i_name"));
				bean.setS_quantity(rs.getInt("s_quantity"));
				bean.setS_price(rs.getDouble("s_price"));
				bean.setS_amount(rs.getDouble("s_amount"));
				bean.setS_actual(rs.getDouble("s_actual"));
				bean.setS_note(rs.getString("s_note"));
				bean.setS_ver(rs.getInt("s_ver"));
				bean.setS_time(rs.getString("s_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 showSellsByS_no(String starttime, String endtime){
		String sql = "SELECT sells.*, intercourse.i_name, warehouse.w_name, produit.p_name, bank.b_name, unit.u_name FROM sells INNER JOIN intercourse ON sells.s_i_id = intercourse.i_id INNER JOIN warehouse ON sells.s_w_id = warehouse.w_id INNER JOIN produit ON sells.s_p_id = produit.p_id INNER JOIN unit ON sells.s_u_id = unit.u_id INNER JOIN bank ON sells.s_b_id = bank.b_id where s_type = 1 and s_time between ? and ? order by s_time desc";
		ArrayList arry = new ArrayList();
		SellsBean bean = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, starttime);
			pstmt.setString(2, endtime);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new SellsBean();
				bean.setS_id(rs.getInt("s_id"));
				bean.setS_i_id(rs.getInt("s_i_id"));
				bean.setS_no(rs.getString("s_no"));
				bean.setI_name(rs.getString("i_name"));
				bean.setB_name(rs.getString("b_name"));
				bean.setP_name(rs.getString("p_name"));
				bean.setS_l_id(rs.getInt("s_l_id"));
				bean.setU_name(rs.getString("u_name"));
				bean.setS_raidtime(rs.getString("s_raidtime"));
				bean.setS_prodate(rs.getString("s_prodate"));
				bean.setS_durdate(rs.getString("s_durdate"));
				bean.setS_r_no(rs.getString("s_r_no"));
				bean.setW_name(rs.getString("w_name"));
				bean.setS_purprice(rs.getDouble("s_purprice"));
				bean.setS_i_name(rs.getString("s_i_name"));
				bean.setS_quantity(rs.getInt("s_quantity"));
				bean.setS_price(rs.getDouble("s_price"));
				bean.setS_amount(rs.getDouble("s_amount"));
				bean.setS_actual(rs.getDouble("s_actual"));
				bean.setS_note(rs.getString("s_note"));
				bean.setS_ver(rs.getInt("s_ver"));
				bean.setS_time(rs.getString("s_time"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 返回未通过审核的出库单集合
	 * @return
	 */
	public ArrayList showWite(int l_id){
		String sql = "SELECT sells.*, intercourse.i_name, warehouse.w_name, produit.p_name, bank.b_name, unit.u_name FROM sells INNER JOIN intercourse ON sells.s_i_id = intercourse.i_id INNER JOIN warehouse ON sells.s_w_id = warehouse.w_id INNER JOIN produit ON sells.s_p_id = produit.p_id INNER JOIN unit ON sells.s_u_id = unit.u_id INNER JOIN bank ON sells.s_b_id = bank.b_id where s_type = 0 and s_ver = ? order by s_time desc";
		ArrayList arry = new ArrayList();