moneyclassda.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.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

import com.captainli.bean.AccountlistBean;
import com.captainli.bean.AccountlistShowBean;
import com.captainli.bean.MoneyAddBean;
import com.captainli.bean.MoneyBean;
import com.captainli.struts.form.MbigclassForm;
import com.captainli.struts.form.MsmallclassForm;
import com.captainli.util.GetConnection;

/**
 * 帐务大小类 数据库操作类
 * @author CaptainLi
 *
 */
public class MoneyClassDA {
	private Connection conn = GetConnection.getConn();
	private Statement stmt = null;
	private PreparedStatement pstmt = 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();
		}
	}
	/**
	 * 返回帐务大类集合
	 * @return
	 */
	public ArrayList showMBigClass(){
		ArrayList arry = new ArrayList();
		MbigclassForm form = null;
		String sql = "select * from mbigclass order by mbc_id";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				form = new MbigclassForm(rs.getInt("mbc_id"), rs.getString("mbc_name"), rs.getInt("mbc_type"));
				arry.add(form);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过帐务类型查询返回结果集合
	 * @return
	 */
	public ArrayList showMBigClassByType(int mbc_type){
		ArrayList arry = new ArrayList();
		MbigclassForm form = null;
		String sql = "select * from mbigclass where mbc_type = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, mbc_type);
			rs = pstmt.executeQuery();
			while(rs.next()){
				form = new MbigclassForm(rs.getInt("mbc_id"), rs.getString("mbc_name"), rs.getInt("mbc_type"));
				arry.add(form);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 帐务大类添加
	 * @param form
	 */
	public void addMBigClass(MbigclassForm form){
		String sql = "insert into mbigclass values (?, ?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getMbc_name());
			pstmt.setInt(2, form.getMbc_type());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 修改帐务大类
	 * @param form
	 * @param mbc_id
	 */
	public void updateMBigClass(MbigclassForm form, int mbc_id){
		String sql = "update mbigclass set mbc_name = ?, mbc_type = ? where mbc_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getMbc_name());
			pstmt.setInt(2, form.getMbc_type());
			pstmt.setInt(3, mbc_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 通过mbc_id返回帐务对象
	 * @param mbc_id
	 * @return
	 */
	public MbigclassForm showMbigclass(int mbc_id){
		MbigclassForm form = null;
		String sql = "select * from mbigclass where mbc_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, mbc_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				form = new MbigclassForm(rs.getInt("mbc_id"), rs.getString("mbc_name"), rs.getInt("mbc_type"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return form;
	}
	/**
	 * 通过帐务大类ID返回小类集合
	 * @param msc_mbc_id
	 * @return
	 */
	public ArrayList showMsmallclassByMbcid(int msc_mbc_id){
		ArrayList arry = new ArrayList();
		MsmallclassForm form = null;
		String sql = "select * from msmallclass where msc_mbc_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, msc_mbc_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				form = new MsmallclassForm(rs.getInt("msc_id"), rs.getString("msc_name"), rs.getInt("msc_mbc_id"));
				arry.add(form);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 帐务界面初始
	 * @return
	 */
	public ArrayList showAll(){
		ArrayList arry = new ArrayList();
		MoneyBean bean = null;
		String sql = "select mbigclass.*,msmallclass.* from mbigclass,msmallclass where msmallclass.msc_mbc_id=mbigclass.mbc_id";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				bean = new MoneyBean(rs.getInt("mbc_id"), rs.getString("mbc_name"), rs.getInt("mbc_type"), rs.getInt("msc_id"), rs.getString("msc_name"), rs.getInt("msc_mbc_id"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过帐务类型返回帐务集合
	 * @param mbc_type
	 * @return
	 */
	public ArrayList showAllByType(int mbc_type){
		ArrayList arry = new ArrayList();
		MoneyBean bean = null;
		String sql = "SELECT * FROM msmallclass INNER JOIN mbigclass ON msmallclass.msc_mbc_id = mbigclass.mbc_id WHERE mbc_type = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, mbc_type);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new MoneyBean(rs.getInt("mbc_id"), rs.getString("mbc_name"), rs.getInt("mbc_type"), rs.getInt("msc_id"), rs.getString("msc_name"), rs.getInt("msc_mbc_id"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过帐务大类返回帐务集合
	 * @param msc_mbc_id
	 * @return
	 */
	public ArrayList showAllByMscMbc(int msc_mbc_id){
		ArrayList arry = new ArrayList();
		MoneyBean bean = null;
		String sql = "SELECT * FROM msmallclass INNER JOIN mbigclass ON msmallclass.msc_mbc_id = mbigclass.mbc_id WHERE msc_mbc_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, msc_mbc_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new MoneyBean(rs.getInt("mbc_id"), rs.getString("mbc_name"), rs.getInt("mbc_type"), rs.getInt("msc_id"), rs.getString("msc_name"), rs.getInt("msc_mbc_id"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 添加帐务小类
	 * @param form
	 */
	public void addMSmallClass(MsmallclassForm form){
		String sql = "insert into msmallclass values (?, ?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getMsc_name());
			pstmt.setInt(2, form.getMsc_mbc_id());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 修改帐务小类
	 * @param form
	 * @param msc_id
	 */
	public void updateMSmallClass(MsmallclassForm form, int msc_id){
		String sql = "update msmallclass set msc_name = ?, msc_mbc_id = ? where msc_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getMsc_name());
			pstmt.setInt(2, form.getMsc_mbc_id());
			pstmt.setInt(3, msc_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 通过帐务小类ID返回帐务小类对象
	 * @param msc_id
	 * @return
	 */
	public MsmallclassForm showMSmallClassByMscid(int msc_id){
		MsmallclassForm form = null;
		String sql = "select * from msmallclass where msc_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, msc_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				form = new MsmallclassForm(rs.getInt("msc_id"), rs.getString("msc_name"), rs.getInt("msc_mbc_id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return form;
	}
	/**
	 * 添加帐务出纳
	 * @param bean
	 */
	public void addMoney(MoneyAddBean bean){
		String sql = "insert into money values (?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, bean.getM_mbc_type());
			pstmt.setInt(2, bean.getM_mbc_id());
			pstmt.setInt(3, bean.getM_msc_id());
			pstmt.setDouble(4, bean.getM_amount());
			pstmt.setInt(5, bean.getM_b_id());
			pstmt.setInt(6, bean.getM_l_id());
			pstmt.setString(7, bean.getM_note());
			pstmt.setString(8, bean.getM_time());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 通过bean返回帐务单ID
	 * @param bean
	 * @return
	 */
	public int showIdByBean(MoneyAddBean bean){
		int tmp = 0;
		String sql = "select m_id from money where m_mbc_type = ? and m_mbc_id = ? and m_msc_id = ? and m_amount = ? and m_b_id = ? and m_l_id = ? and m_note = ? and m_time = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, bean.getM_mbc_type());
			pstmt.setInt(2, bean.getM_mbc_id());
			pstmt.setInt(3, bean.getM_msc_id());
			pstmt.setDouble(4, bean.getM_amount());
			pstmt.setInt(5, bean.getM_b_id());
			pstmt.setInt(6, bean.getM_l_id());
			pstmt.setString(7, bean.getM_note());
			pstmt.setString(8, bean.getM_time());
			rs = pstmt.executeQuery();
			if(rs.next()){
				tmp = rs.getInt("m_id");