paymentda.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.PaymentBean;
import com.captainli.util.GetConnection;

/**
 * payment付款单表数据库操作
 * @author CaptainLi
 *
 */
public class PaymentDA {
	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 payment 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 addPayment(PaymentBean bean){
		String sql = "insert into payment values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bean.getP_no());
			pstmt.setInt(2, bean.getP_i_id());
			pstmt.setInt(3, bean.getP_l_id());
			pstmt.setInt(4, bean.getP_b_id());
			pstmt.setString(5, "");
			pstmt.setString(6, null);
			pstmt.setDouble(7, 0);
			pstmt.setDouble(8, 0);
			pstmt.setDouble(9, bean.getP_sett());
			pstmt.setString(10, bean.getP_time());
			pstmt.setString(11, bean.getP_note());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 按单结算付款单生成
	 * @param bean
	 */
	public void addPaymentByPid(PaymentBean bean){
		String sql = "insert into payment values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bean.getP_no());
			pstmt.setInt(2, bean.getP_i_id());
			pstmt.setInt(3, bean.getP_l_id());
			pstmt.setInt(4, bean.getP_b_id());
			pstmt.setString(5, bean.getP_p_no());
			pstmt.setString(6, bean.getP_time());
			pstmt.setDouble(7, bean.getP_p_amount());
			pstmt.setDouble(8, bean.getP_rem());
			pstmt.setDouble(9, bean.getP_sett());
			pstmt.setString(10, bean.getP_time());
			pstmt.setString(11, bean.getP_note());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 返回所有付款单信息
	 * @return
	 */
	public ArrayList showAllPayment(){
		ArrayList arry = new ArrayList();
		PaymentBean bean = null;
		String sql = "SELECT payment.*, bank.b_name, intercourse.i_name, login.l_name FROM payment INNER JOIN bank ON payment.p_b_id = bank.b_id INNER JOIN intercourse ON payment.p_i_id = intercourse.i_id INNER JOIN login ON payment.p_l_id = login.l_id";
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new PaymentBean();
				bean.setP_id(rs.getInt("p_id"));
				bean.setP_no(rs.getString("p_no"));
				bean.setB_name(rs.getString("b_name"));
				bean.setI_name(rs.getString("i_name"));
				bean.setL_name(rs.getString("l_name"));
				bean.setP_p_no(rs.getString("p_p_no"));
				bean.setP_p_time(rs.getString("p_p_time"));
				bean.setP_p_amount(rs.getDouble("p_p_amount"));
				bean.setP_rem(rs.getDouble("p_rem"));
				bean.setP_sett(rs.getDouble("p_sett"));
				bean.setP_time(rs.getString("p_time"));
				bean.setP_note(rs.getString("p_note"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过付款单编号模糊查询返回集合
	 * @param p_no
	 * @return
	 */
	public ArrayList showPaymentByP_no(String p_no){
		ArrayList arry = new ArrayList();
		PaymentBean bean = null;
		String sql = "SELECT payment.*, bank.b_name, intercourse.i_name, login.l_name FROM payment INNER JOIN bank ON payment.p_b_id = bank.b_id INNER JOIN intercourse ON payment.p_i_id = intercourse.i_id INNER JOIN login ON payment.p_l_id = login.l_id WHERE p_no like ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+p_no+"%");
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new PaymentBean();
				bean.setP_id(rs.getInt("p_id"));
				bean.setP_no(rs.getString("p_no"));
				bean.setB_name(rs.getString("b_name"));
				bean.setI_name(rs.getString("i_name"));
				bean.setL_name(rs.getString("l_name"));
				bean.setP_p_no(rs.getString("p_p_no"));
				bean.setP_p_time(rs.getString("p_p_time"));
				bean.setP_p_amount(rs.getDouble("p_p_amount"));
				bean.setP_rem(rs.getDouble("p_rem"));
				bean.setP_sett(rs.getDouble("p_sett"));
				bean.setP_time(rs.getString("p_time"));
				bean.setP_note(rs.getString("p_note"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过时间段返回付款单集合
	 * @param starttime
	 * @param endtime
	 * @return
	 */
	public ArrayList showPaymentByP_time(String starttime, String endtime){
		ArrayList arry = new ArrayList();
		PaymentBean bean = null;
		String sql = "SELECT payment.*, bank.b_name, intercourse.i_name, login.l_name FROM payment INNER JOIN bank ON payment.p_b_id = bank.b_id INNER JOIN intercourse ON payment.p_i_id = intercourse.i_id INNER JOIN login ON payment.p_l_id = login.l_id WHERE p_time between ? and ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, starttime);
			pstmt.setString(2, endtime);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new PaymentBean();
				bean.setP_id(rs.getInt("p_id"));
				bean.setP_no(rs.getString("p_no"));
				bean.setB_name(rs.getString("b_name"));
				bean.setI_name(rs.getString("i_name"));
				bean.setL_name(rs.getString("l_name"));
				bean.setP_p_no(rs.getString("p_p_no"));
				bean.setP_p_time(rs.getString("p_p_time"));
				bean.setP_p_amount(rs.getDouble("p_p_amount"));
				bean.setP_rem(rs.getDouble("p_rem"));
				bean.setP_sett(rs.getDouble("p_sett"));
				bean.setP_time(rs.getString("p_time"));
				bean.setP_note(rs.getString("p_note"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
}