disppay.java

通过jsp、mysql实现了进销存管理

/**
 *  Title  财务管理系统
 *  @author: trowa
 *  Company: http://www.upol.cn
 *  Copyright: Copyright (c) 2004
 *  @version 1.0
 *  费用类型表中的一些查询操作
 */
package caiwu;

import java.sql.*;
import java.util.*;

public class DispPay extends Pay {

	public ResultSet rs;

	// 得到所有的费用
	public Vector allPay() {
		DBConnect dbc = null;
		Vector allPayVector = new Vector();
		try {
			dbc = new DBConnect();
			String sql = "SELECT * FROM pay order by id desc";
			dbc.prepareStatement(sql);
			rs = dbc.executeQuery();
			while (rs.next()) {
				Pay pay = new Pay();
				pay.setId(rs.getInt("id"));
				pay.setClassid(rs.getInt("classid"));
				pay.setPayerid(rs.getInt("payerid"));
				pay.setPaytypeid(rs.getInt("paytypeid"));
				pay.setMoney(rs.getInt("money"));
				pay.setIo(rs.getInt("io"));
				pay.setProject(rs.getString("project"));
				pay.setMessage(rs.getString("message"));
				pay.setAddtime(rs.getString("addtime"));
				allPayVector.add(pay);
			}
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return allPayVector;
	}

	// 得到所有的年份
	public Vector allAddtime_year() {
		DBConnect dbc = null;
		Vector allPayVector = new Vector();
		try {
			dbc = new DBConnect();
			dbc.prepareStatement("select distinct year(addtime) From pay");
			rs = dbc.executeQuery();
			while (rs.next()) {
				Pay pay = new Pay();
				pay.setAddtime_year(rs.getInt("year(addtime)"));
				allPayVector.add(pay);
			}
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return allPayVector;
	}

	// 得到部门所有的费用记录
	public Vector classidtoallPay() {
		DBConnect dbc = null;
		Vector allPayVector = new Vector();
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("SELECT * FROM pay where classid=? order by id desc");
			dbc.setInt(1, Classid);
			rs = dbc.executeQuery();
			while (rs.next()) {
				Pay pay = new Pay();
				pay.setId(rs.getInt("id"));
				pay.setClassid(rs.getInt("classid"));
				pay.setPayerid(rs.getInt("payerid"));
				pay.setPaytypeid(rs.getInt("paytypeid"));
				pay.setMoney(rs.getInt("money"));
				pay.setIo(rs.getInt("io"));
				pay.setProject(rs.getString("project"));
				pay.setMessage(rs.getString("message"));
				pay.setAddtime(rs.getString("addtime"));
				allPayVector.add(pay);
			}
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return allPayVector;
	}

	// 根据id得到费用记录
	public Pay idToPay() {
		DBConnect dbc = null;
		Pay pay = new Pay();
		try {
			dbc = new DBConnect();
			dbc.prepareStatement("SELECT * FROM pay where id=?");
			dbc.setInt(1, Id);
			rs = dbc.executeQuery();
			if (rs.next()) {
				// pay.setId(rs.getInt("id"));
				pay.setClassid(rs.getInt("classid"));
				pay.setPayerid(rs.getInt("payerid"));
				pay.setPaytypeid(rs.getInt("paytypeid"));
				pay.setMoney(rs.getInt("money"));
				pay.setIo(rs.getInt("io"));
				pay.setProject(rs.getString("project"));
				pay.setMessage(rs.getString("message"));
				pay.setAddtime(rs.getString("addtime"));
			}
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return pay;
	}

	// 搜索费用记录
	public Vector searchallPay() {
		DBConnect dbc = null;
		Vector allPayVector = new Vector();
		try {
			dbc = new DBConnect();
			dbc.prepareStatement("SELECT * FROM pay where " + Sqlstr
					+ " order by id desc");
			// dbc.setBytes(1,Sqlstr.getBytes("GB2312"));
			rs = dbc.executeQuery();
			while (rs.next()) {
				Pay pay = new Pay();
				pay.setId(rs.getInt("id"));
				pay.setClassid(rs.getInt("classid"));
				pay.setPayerid(rs.getInt("payerid"));
				pay.setPaytypeid(rs.getInt("paytypeid"));
				pay.setMoney(rs.getInt("money"));
				pay.setIo(rs.getInt("io"));
				pay.setProject(rs.getString("project"));
				pay.setMessage(rs.getString("message"));
				pay.setAddtime(rs.getString("addtime"));
				allPayVector.add(pay);
			}
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return allPayVector;
	}

	// 获得搜索记录总条数
	public int searchpaynum() {
		DBConnect dbc = null;
		int paycount = 0;
		try {
			dbc = new DBConnect();
			dbc.prepareStatement("SELECT count(*) FROM pay where " + Sqlstr);
			rs = dbc.executeQuery();
			if (rs.next())
				paycount = rs.getInt(1);
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return paycount;
	}

	// 获得记录总条数
	public int paynum() {
		DBConnect dbc = null;
		int paycount = 0;
		try {
			dbc = new DBConnect();
			dbc.prepareStatement("SELECT count(*) FROM pay");
			rs = dbc.executeQuery();
			if (rs.next())
				paycount = rs.getInt(1);
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return paycount;
	}

	// 根据classid获得记录总条数
	public int classidtopaynum() {
		DBConnect dbc = null;
		int paycount = 0;
		try {
			dbc = new DBConnect();
			dbc.prepareStatement("SELECT count(*) FROM pay where classid=?");
			dbc.setInt(1, Classid);
			rs = dbc.executeQuery();
			if (rs.next())
				paycount = rs.getInt(1);
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return paycount;
	}

	// 根据用户类型和年份和月份获得每月的总钱数
	public int totlemoney_month() {
		DBConnect dbc = null;
		int totlemoney = 0;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("select sum(money) from pay where classid=? and paytypeid=? and year(addtime)=? and month(addtime)=?");
			dbc.setInt(1, Classid);
			dbc.setInt(2, Paytypeid);
			dbc.setInt(3, Addtime_year);
			dbc.setInt(4, Addtime_month);
			rs = dbc.executeQuery();
			if (rs.next())
				totlemoney = rs.getInt(1);
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return totlemoney;
	}

	// 根据用户类型和年份获得每年的总钱数
	public int totlemoney_year() {
		DBConnect dbc = null;
		int totlemoney = 0;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("select sum(money) from pay where classid=? and paytypeid=? and year(addtime)=?");
			dbc.setInt(1, Classid);
			dbc.setInt(2, Paytypeid);
			dbc.setInt(3, Addtime_year);
			rs = dbc.executeQuery();
			if (rs.next())
				totlemoney = rs.getInt(1);
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return totlemoney;
	}

	// 根据所属类型和年份和月份获得每月的总钱数
	public int totleio_month() {
		DBConnect dbc = null;
		int totlemoney = 0;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("select sum(money) from pay where classid=? and io=? and year(addtime)=? and month(addtime)=?");
			dbc.setInt(1, Classid);
			dbc.setInt(2, Io);
			dbc.setInt(3, Addtime_year);
			dbc.setInt(4, Addtime_month);
			rs = dbc.executeQuery();
			if (rs.next())
				totlemoney = rs.getInt(1);
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return totlemoney;
	}

	// 根据用户类型和年份获得每年的总钱数
	public int totleio_year() {
		DBConnect dbc = null;
		int totlemoney = 0;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("select sum(money) from pay where classid=? and io=? and year(addtime)=?");
			dbc.setInt(1, Classid);
			dbc.setInt(2, Io);
			dbc.setInt(3, Addtime_year);
			rs = dbc.executeQuery();
			if (rs.next())
				totlemoney = rs.getInt(1);
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return totlemoney;
	}

	public DispPay() {
	}
}