collectionda.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 com.captainli.bean.CollectionBean;
import com.captainli.util.GetConnection;

/**
 * 收款单collection数据库操作类
 * @author CaptainLi
 *
 */
public class CollectionDA {
	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(c_no) cno from collection where c_no like ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+time+"%");
			rs = pstmt.executeQuery();
			if(rs.next()){
				tmp = rs.getString("cno");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
	/**
	 * 普通收款单生成
	 * @param bean
	 */
	public void addCollection(CollectionBean bean){
		String sql = "insert into collection values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bean.getC_no());
			pstmt.setInt(2, bean.getC_i_id());
			pstmt.setInt(3, bean.getC_l_id());
			pstmt.setInt(4, bean.getC_b_id());
			pstmt.setString(5, "");
			pstmt.setString(6, null);
			pstmt.setDouble(7, 0);
			pstmt.setDouble(8, 0);
			pstmt.setDouble(9, bean.getC_sett());
			pstmt.setString(10, bean.getC_time());
			pstmt.setString(11, bean.getC_note());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 按单结算生成收款单
	 * @param bean
	 */
	public void addCollectionBySid(CollectionBean bean){
		String sql = "insert into collection values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bean.getC_no());
			pstmt.setInt(2, bean.getC_i_id());
			pstmt.setInt(3, bean.getC_l_id());
			pstmt.setInt(4, bean.getC_b_id());
			pstmt.setString(5, bean.getC_p_no());
			pstmt.setString(6, bean.getC_p_time());
			pstmt.setDouble(7, bean.getC_p_amount());
			pstmt.setDouble(8, bean.getC_rem());
			pstmt.setDouble(9, bean.getC_sett());
			pstmt.setString(10, bean.getC_time());
			pstmt.setString(11, bean.getC_note());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
}