disppay.java

一个财务管理系统

/**
*  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();
                    dbc.prepareStatement("SELECT * FROM pay order by id desc");
                    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.setInout(rs.getInt("inout"));
                            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.setInout(rs.getInt("inout"));
                                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.setInout(rs.getInt("inout"));
                                    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.setInout(rs.getInt("inout"));
                                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 totleinout_month() {
                 DBConnect dbc = null;
                    int totlemoney = 0;
                          try{
                              dbc  = new DBConnect();
                              dbc.prepareStatement("select sum(money) from pay where classid=? and inout=? and year(addtime)=? and month(addtime)=?");
                              dbc.setInt(1,Classid);
                              dbc.setInt(2,Inout);
                              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 totleinout_year() {
                DBConnect dbc = null;
                   int totlemoney = 0;
                         try{
                             dbc  = new DBConnect();
                             dbc.prepareStatement("select sum(money) from pay where classid=? and inout=? and year(addtime)=?");
                             dbc.setInt(1,Classid);
                             dbc.setInt(2,Inout);
                             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() {
    }
}