moneyclassda.java

医药供应链管理系统

	 * @param m_msc_id
	 * @return
	 */
	public ArrayList showMoneyByM_mbc_typeAndM_mbc_idAndM_msc_id(int m_mbc_type, int m_mbc_id, int m_msc_id){
		ArrayList arry = new ArrayList();
		AccountlistShowBean bean = null;
		String sql = "SELECT money.m_id, money.m_mbc_type, mbigclass.mbc_name, msmallclass.msc_name, login.l_name, money.m_time, bank.b_name, money.m_amount " +
				"FROM money INNER JOIN mbigclass ON money.m_mbc_id = mbigclass.mbc_id INNER JOIN msmallclass ON money.m_msc_id = msmallclass.msc_id AND " +
				"mbigclass.mbc_id = msmallclass.msc_mbc_id INNER JOIN bank ON money.m_b_id = bank.b_id INNER JOIN login ON money.m_l_id = login.l_id " +
				"WHERE m_mbc_type = ? AND m_mbc_id = ? AND m_msc_id = ? ORDER BY m_time desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, m_mbc_type);
			pstmt.setInt(2, m_mbc_id);
			pstmt.setInt(3, m_msc_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new AccountlistShowBean(rs.getInt("m_id"), rs.getInt("m_mbc_type"), rs.getString("mbc_name"), rs.getString("msc_name"), rs.getString("l_name"), rs.getString("m_time"), rs.getString("b_name"), rs.getDouble("m_amount"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过帐务类型m_mbc_type和银行m_b_id返回集合
	 * @param m_mbc_type
	 * @param m_b_id
	 * @return
	 */
	public ArrayList showMoneyByM_mbc_typeAndM_b_id(int m_mbc_type, int m_b_id){
		ArrayList arry = new ArrayList();
		AccountlistShowBean bean = null;
		String sql = "SELECT money.m_id, money.m_mbc_type, mbigclass.mbc_name, msmallclass.msc_name, login.l_name, money.m_time, bank.b_name, money.m_amount " +
				"FROM money INNER JOIN mbigclass ON money.m_mbc_id = mbigclass.mbc_id INNER JOIN msmallclass ON money.m_msc_id = msmallclass.msc_id AND " +
				"mbigclass.mbc_id = msmallclass.msc_mbc_id INNER JOIN bank ON money.m_b_id = bank.b_id INNER JOIN login ON money.m_l_id = login.l_id " +
				"WHERE m_mbc_type = ? AND m_b_id = ? ORDER BY m_time desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, m_mbc_type);
			pstmt.setInt(2, m_b_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new AccountlistShowBean(rs.getInt("m_id"), rs.getInt("m_mbc_type"), rs.getString("mbc_name"), rs.getString("msc_name"), rs.getString("l_name"), rs.getString("m_time"), rs.getString("b_name"), rs.getDouble("m_amount"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过帐务类型m_mbc_type和银行m_b_id和帐务小类m_msc_id返回集合
	 * @param m_mbc_type
	 * @param m_b_id
	 * @param m_msc_id
	 * @return
	 */
	public ArrayList showMoneyByM_mbc_typeAndM_b_idAndM_msc_id(int m_mbc_type, int m_b_id, int m_msc_id){
		ArrayList arry = new ArrayList();
		AccountlistShowBean bean = null;
		String sql = "SELECT money.m_id, money.m_mbc_type, mbigclass.mbc_name, msmallclass.msc_name, login.l_name, money.m_time, bank.b_name, money.m_amount " +
				"FROM money INNER JOIN mbigclass ON money.m_mbc_id = mbigclass.mbc_id INNER JOIN msmallclass ON money.m_msc_id = msmallclass.msc_id AND " +
				"mbigclass.mbc_id = msmallclass.msc_mbc_id INNER JOIN bank ON money.m_b_id = bank.b_id INNER JOIN login ON money.m_l_id = login.l_id " +
				"WHERE m_mbc_type = ? AND m_b_id = ? AND m_msc_id = ? ORDER BY m_time desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, m_mbc_type);
			pstmt.setInt(2, m_b_id);
			pstmt.setInt(3, m_msc_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new AccountlistShowBean(rs.getInt("m_id"), rs.getInt("m_mbc_type"), rs.getString("mbc_name"), rs.getString("msc_name"), rs.getString("l_name"), rs.getString("m_time"), rs.getString("b_name"), rs.getDouble("m_amount"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过帐务类型m_mbc_type和银行m_b_id和帐务大类m_mbc_id返回集合
	 * @param m_mbc_type
	 * @param m_b_id
	 * @param m_mbc_id
	 * @return
	 */
	public ArrayList showMoneyByM_mbc_typeAndM_b_idAndM_mbc_id(int m_mbc_type, int m_b_id, int m_mbc_id){
		ArrayList arry = new ArrayList();
		AccountlistShowBean bean = null;
		String sql = "SELECT money.m_id, money.m_mbc_type, mbigclass.mbc_name, msmallclass.msc_name, login.l_name, money.m_time, bank.b_name, money.m_amount " +
				"FROM money INNER JOIN mbigclass ON money.m_mbc_id = mbigclass.mbc_id INNER JOIN msmallclass ON money.m_msc_id = msmallclass.msc_id AND " +
				"mbigclass.mbc_id = msmallclass.msc_mbc_id INNER JOIN bank ON money.m_b_id = bank.b_id INNER JOIN login ON money.m_l_id = login.l_id " +
				"WHERE m_mbc_type = ? AND m_b_id = ? AND m_mbc_id = ? ORDER BY m_time desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, m_mbc_type);
			pstmt.setInt(2, m_b_id);
			pstmt.setInt(3, m_mbc_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new AccountlistShowBean(rs.getInt("m_id"), rs.getInt("m_mbc_type"), rs.getString("mbc_name"), rs.getString("msc_name"), rs.getString("l_name"), rs.getString("m_time"), rs.getString("b_name"), rs.getDouble("m_amount"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过帐务类型m_mbc_type和银行m_b_id和帐务大类m_mbc_id和帐务小类m_msc_id返回集合
	 * @param m_mbc_type
	 * @param m_b_id
	 * @param m_mbc_id
	 * @param m_msc_id
	 * @return
	 */
	public ArrayList showMoneyByM_mbc_typeAndM_b_idAndM_mbc_idAndM_msc_id(int m_mbc_type, int m_b_id, int m_mbc_id, int m_msc_id){
		ArrayList arry = new ArrayList();
		AccountlistShowBean bean = null;
		String sql = "SELECT money.m_id, money.m_mbc_type, mbigclass.mbc_name, msmallclass.msc_name, login.l_name, money.m_time, bank.b_name, money.m_amount " +
				"FROM money INNER JOIN mbigclass ON money.m_mbc_id = mbigclass.mbc_id INNER JOIN msmallclass ON money.m_msc_id = msmallclass.msc_id AND " +
				"mbigclass.mbc_id = msmallclass.msc_mbc_id INNER JOIN bank ON money.m_b_id = bank.b_id INNER JOIN login ON money.m_l_id = login.l_id " +
				"WHERE m_mbc_type = ? AND m_b_id = ? AND m_mbc_id = ? AND m_msc_id = ? ORDER BY m_time desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, m_mbc_type);
			pstmt.setInt(2, m_b_id);
			pstmt.setInt(3, m_mbc_id);
			pstmt.setInt(4, m_msc_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new AccountlistShowBean(rs.getInt("m_id"), rs.getInt("m_mbc_type"), rs.getString("mbc_name"), rs.getString("msc_name"), rs.getString("l_name"), rs.getString("m_time"), rs.getString("b_name"), rs.getDouble("m_amount"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过时间段返回集合
	 * @param starttime
	 * @param endtime
	 * @return
	 */
	public ArrayList showMoneyByM_time(String starttime, String endtime){
		ArrayList arry = new ArrayList();
		AccountlistShowBean bean = null;
		String sql = "SELECT money.m_id, money.m_mbc_type, mbigclass.mbc_name, msmallclass.msc_name, login.l_name, money.m_time, bank.b_name, money.m_amount " +
				"FROM money INNER JOIN mbigclass ON money.m_mbc_id = mbigclass.mbc_id INNER JOIN msmallclass ON money.m_msc_id = msmallclass.msc_id AND " +
				"mbigclass.mbc_id = msmallclass.msc_mbc_id INNER JOIN bank ON money.m_b_id = bank.b_id INNER JOIN login ON money.m_l_id = login.l_id " +
				"WHERE m_time BETWEEN ? AND ? ORDER BY m_time desc";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, starttime);
			pstmt.setString(2, endtime);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new AccountlistShowBean(rs.getInt("m_id"), rs.getInt("m_mbc_type"), rs.getString("mbc_name"), rs.getString("msc_name"), rs.getString("l_name"), rs.getString("m_time"), rs.getString("b_name"), rs.getDouble("m_amount"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 查询红冲单据
	 * @return
	 */
	public ArrayList showMoneyHongChong(){
		ArrayList arry = new ArrayList();
		AccountlistShowBean bean = null;
		String sql = "SELECT moneychong.m_id, moneychong.m_mbc_type, mbigclass.mbc_name, msmallclass.msc_name, login.l_name, moneychong.m_time, bank.b_name, moneychong.m_amount " +
				"FROM moneychong INNER JOIN mbigclass ON moneychong.m_mbc_id = mbigclass.mbc_id INNER JOIN msmallclass ON moneychong.m_msc_id = msmallclass.msc_id AND " +
				"mbigclass.mbc_id = msmallclass.msc_mbc_id INNER JOIN bank ON moneychong.m_b_id = bank.b_id INNER JOIN login ON moneychong.m_l_id = login.l_id " +
				"ORDER BY m_time desc";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				bean = new AccountlistShowBean(rs.getInt("m_id"), rs.getInt("m_mbc_type"), rs.getString("mbc_name"), rs.getString("msc_name"), rs.getString("l_name"), rs.getString("m_time"), rs.getString("b_name"), rs.getDouble("m_amount"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 返回收入合计
	 * @return
	 */
	public double showAmount_jia(){
		String sql = "select sum(m_amount) he from money where m_mbc_type = 1";
		double tmp = 0;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next()){
				tmp = rs.getDouble("he");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
	/**
	 * 返回支出合计
	 * @return
	 */
	public double showAmount_jian(){
		String sql = "select sum(m_amount) he from money where m_mbc_type = 2";
		double tmp = 0;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next()){
				tmp = rs.getDouble("he");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
	/**
	 * 返回money对象
	 * @param m_id
	 * @return
	 */
	public MoneyAddBean showMoneyByM_id(int m_id){
		//获得系统时间
		Calendar cal=Calendar.getInstance();
	    SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	    String m_time=sdf.format(cal.getTime());
		
	    MoneyAddBean bean = null;
		String sql = "select * from money where m_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, m_id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				bean = new MoneyAddBean();
				bean.setM_mbc_type(rs.getInt("m_mbc_type"));
				bean.setM_mbc_id(rs.getInt("m_mbc_id"));
				bean.setM_msc_id(rs.getInt("m_msc_id"));
				bean.setM_amount(rs.getDouble("m_amount"));
				bean.setM_b_id(rs.getInt("m_b_id"));
				bean.setM_l_id(rs.getInt("m_l_id"));
				bean.setM_note(rs.getString("m_note"));
				bean.setM_time(m_time);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return bean;
	}
	/**
	 * 单据红冲
	 * @param m_id
	 */
	public void moneyHongchong(int m_id){
		String sql = "delete from money where m_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, m_id);
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 添加到红冲表moneychong中
	 * @param bean
	 */
	public void addMoneyChong(MoneyAddBean bean){
		String sql = "insert into moneychong 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();
		}
	}
	/**
	 * 清空money数据库表
	 */
	public void moneyDel(){
		String sql = "delete from money";
		try {
			stmt = conn.createStatement();
			stmt.execute(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 红冲单据删除
	 * @param m_id
	 */
	public void moneyHongchongDel(int m_id){
		String sql = "delete from moneychong where m_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, m_id);
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
}