statisticdsfactory.java

报表,关于ireport报表的一些源码(转)

package cn.edu.hlju.oa.kygl.statistic;

import java.util.*;
import java.sql.*;
import cn.edu.hlju.oa.kygl.db.DBConnection;

public class StatisticDSFactory {

    /**
     *
     * @param ryxm String
     * @param rybh String
     * @param fromClause String
     * @param whereClause String
     * @return String
     */
    private static String addSQLWarpper(String selectClause,//不用改的select语句
                                        String fromClause,  //不用改的from语句
                                        String whereClause, //不用改的where语句
                                        String ryTableInfo, //要和人事表进行连接的部分
                                        String ryxm,        //姓名--人事表
                                        String rsbh){       //人员编号--人事表
        if( ryxm==null || rsbh==null || ryxm.equals("") || rsbh.equals("") ){
            return "SELECT "+selectClause+" FROM "+fromClause+" WHERE "+whereClause;
        }else{
           String fromTemp  = ",rsda_jbxx ";
           String whereTemp = " AND ("+ryTableInfo+"=rsda_jbxx.rsbh AND rsda_jbxx.rsbh='"+rsbh+"' AND rsda_jbxx.xm='"+ryxm+"') ";
           return "SELECT "+selectClause+" FROM "+fromClause+fromTemp+" WHERE "+whereClause+whereTemp;
        }
    }

    //整体科研信息
    public static ArrayList createWholeDS(int startYear,int endYear,String ryxm,String rsbh,boolean isILab,String iLabBm)
    throws SQLException {
        ArrayList<WholeBean> beans = new ArrayList<WholeBean>();
        WholeBean newBean;
        Connection conn = DBConnection.getConnection();

        String projectQuery = addSQLWarpper("count(xmbh)",
                                            isILab?"ky_kyxmxxb,ky_kyjgryxxb":"ky_kyxmxxb",
                                            isILab?"(ky_kyxmxxb.xmrybh=ky_kyjgryxxb.rybh AND ky_kyjgryxxb.jgbm='"+iLabBm+"') AND cjzwc=1 AND xmkssj>=? AND xmkssj<=?":"cjzwc=1 AND xmkssj>=? AND xmkssj<=?",
                                            "ky_kyxmxxb.xmrybh",
                                            ryxm,rsbh);
        //System.out.println("项目查询语句:"+projectQuery);

        //String awardQuery   = "SELECT count(hjbh) FROM ky_kyhjxxb WHERE cjzwc=1 AND hjsj>=? AND hjsj<=?";
        String awardQuery = addSQLWarpper("count(hjbh)",
                                            isILab?"ky_kyhjxxb,ky_kyjgryxxb":"ky_kyhjxxb",
                                            isILab?"(ky_kyhjxxb.hjzbh=ky_kyjgryxxb.rybh AND ky_kyjgryxxb.jgbm='"+iLabBm+"') AND cjzwc=1 AND hjsj>=? AND hjsj<=?":"cjzwc=1 AND hjsj>=? AND hjsj<=?",
                                            "ky_kyhjxxb.hjzbh",
                                            ryxm,rsbh);
        //System.out.println("获奖查询语句:"+awardQuery);

        //String resultQuery  = "SELECT count(cgbh) FROM ky_zscqxxb WHERE cjzwc=1 AND sqsj_1>=? AND sqsj_1<=?";
        String resultQuery = addSQLWarpper("count(cgbh)",
                                            isILab?"ky_zscqxxb,ky_kyjgryxxb":"ky_zscqxxb",
                                            isILab?"(ky_zscqxxb.zlrbh=ky_kyjgryxxb.rybh AND ky_kyjgryxxb.jgbm='"+iLabBm+"') AND cjzwc=1 AND sqsj_1>=? AND sqsj_1<=?":"cjzwc=1 AND sqsj_1>=? AND sqsj_1<=?",
                                            "ky_zscqxxb.zlrbh",
                                            ryxm,rsbh);
        //System.out.println("成果/专利查询语句:"+resultQuery);

        //String articleQuery = "SELECT count(zzbh) FROM ky_zzxxb   WHERE cjzwc=1 AND cbsj>=? AND cbsj<=?";
        String articleQuery = addSQLWarpper("count(zzbh)",
                                            isILab?"ky_zzxxb,ky_kyjgryxxb":"ky_zzxxb",
                                            isILab?"(ky_zzxxb.zzbh_1=ky_kyjgryxxb.rybh AND ky_kyjgryxxb.jgbm='"+iLabBm+"') AND cjzwc=1 AND cbsj>=? AND cbsj<=?":"cjzwc=1 AND cbsj>=? AND cbsj<=?",
                                            "ky_zzxxb.zzbh_1",
                                            ryxm,rsbh);
        //System.out.println("著作查询语句:"+articleQuery);

        //String paperQuery   = "SELECT count(lwbh) FROM ky_kjlwxxb WHERE cjzwc=1 AND kwnd=?";
        String paperQuery = addSQLWarpper("count(lwbh)",
                                          isILab?"ky_kjlwxxb,ky_kyjgryxxb":"ky_kjlwxxb",
                                          isILab?"(ky_kjlwxxb.lwzzbh=ky_kyjgryxxb.rybh AND ky_kyjgryxxb.jgbm='"+iLabBm+"') AND cjzwc=1 AND kwnd=?":"cjzwc=1 AND kwnd=?",
                                          "ky_kjlwxxb.lwzzbh",
                                          ryxm,rsbh);
        //System.out.println("论文查询语句:"+paperQuery);

        PreparedStatement pjStmt = conn.prepareStatement(projectQuery);
        PreparedStatement adStmt = conn.prepareStatement(awardQuery);
        PreparedStatement rsStmt = conn.prepareStatement(resultQuery);
        PreparedStatement atStmt = conn.prepareStatement(articleQuery);
        PreparedStatement ppStmt = conn.prepareStatement(paperQuery);
        ResultSet comRs;

        int pjTl=0,adTl=0,rsTl=0,atTl=0,ppTl=0;

        for(int i = startYear; i <= endYear; i++){

            newBean = new WholeBean();

            pjStmt.setString(1,String.valueOf(i)+"-1-1");
            pjStmt.setString(2,String.valueOf(i)+"-12-31");
            comRs = pjStmt.executeQuery();
            if( comRs.next() )
                newBean.setProject(comRs.getBigDecimal(1).intValue());

            adStmt.setString(1,String.valueOf(i)+"-1-1");
            adStmt.setString(2,String.valueOf(i)+"-12-31");
            comRs = adStmt.executeQuery();
            if( comRs.next() )
                newBean.setAward(comRs.getBigDecimal(1).intValue());

            rsStmt.setString(1,String.valueOf(i)+"-1-1");
            rsStmt.setString(2,String.valueOf(i)+"-12-31");
            comRs = rsStmt.executeQuery();
            if( comRs.next() )
                newBean.setResult(comRs.getBigDecimal(1).intValue());

            atStmt.setString(1,String.valueOf(i)+"-1-1");
            atStmt.setString(2,String.valueOf(i)+"-12-31");
            comRs = atStmt.executeQuery();
            if( comRs.next() )
                newBean.setArticle(comRs.getBigDecimal(1).intValue());

            ppStmt.setString(1,String.valueOf(i));
            comRs = ppStmt.executeQuery();
            if( comRs.next() )
                newBean.setPaper(comRs.getBigDecimal(1).intValue());

            beans.add(newBean);
        }

        DBConnection.releaseConnection(conn);
        return beans;
    }


    //整体的项目信息
    public static ArrayList createWholeProjectDS(int startYear,int endYear,String ryxm,String rsbh,boolean isILab,String iLabBm)
    throws SQLException {
        //事先已经知道到底有多少年的了
        ArrayList<GeneralStatBean> beans = new ArrayList<GeneralStatBean>();
        for(int i = 0; i < endYear-startYear+1 ; i++){
            GeneralStatBean bean = new GeneralStatBean();
            beans.add(bean);
        }

        //获得数据库连接
        Connection conn = DBConnection.getConnection();

        StringBuffer bmmcQueryBuf = new StringBuffer();
        bmmcQueryBuf.append("SELECT bz FROM ky_kyxtbmb WHERE bmfl='007' AND bm>'00000' GROUP BY bz ORDER BY bm");
        PreparedStatement bmmcStmt = conn.prepareStatement(bmmcQueryBuf.toString());
        ResultSet bzRs = bmmcStmt.executeQuery();

        //query.append("SELECT count(xmbh) ");
        //query.append("FROM ky_kyxtbmb,ky_kyxmxxb ");
        //query.append("WHERE ky_kyxtbmb.bm=ky_kyxmxxb.xmlybm AND ky_kyxtbmb.bz=? AND cjzwc=1 AND ky_kyxmxxb.xmkssj>=? and ky_kyxmxxb.xmkssj<=? ");
        String query = addSQLWarpper("count(xmbh)",
               isILab?"ky_kyxmxxb,ky_kyxtbmb,ky_kyjgryxxb":"ky_kyxmxxb,ky_kyxtbmb",
               isILab?"(ky_kyxmxxb.xmrybh=ky_kyjgryxxb.rybh AND ky_kyjgryxxb.jgbm='"+iLabBm+"') AND (ky_kyxtbmb.bm=ky_kyxmxxb.xmlybm AND ky_kyxtbmb.bz=?) AND cjzwc=1 AND xmkssj>=? AND xmkssj<=?":"(ky_kyxtbmb.bm=ky_kyxmxxb.xmlybm AND ky_kyxtbmb.bz=?) AND cjzwc=1 AND xmkssj>=? AND xmkssj<=?",
               "ky_kyxmxxb.xmrybh",
               ryxm,rsbh);
        PreparedStatement stmt = conn.prepareStatement(query.toString());

        int index = 0;
        while( bzRs.next() ){
            String xmlymc = bzRs.getString(1);
            //因为Bean第一位是年度位,所以index从1开始
            index++;
            for(int i = 0; i < beans.size(); i++){
                //填入查询参数
                stmt.setString(1,xmlymc);
                stmt.setString(2,String.valueOf(i+startYear)+"-1-1");
                stmt.setString(3,String.valueOf(i+startYear)+"-12-31");
                //将查询结果填入
                GeneralStatBean temp = beans.get(i);
                ResultSet rs = stmt.executeQuery();
                if( rs.next() ){
                    temp.addVal(rs.getBigDecimal(1).intValue());
                }else
                    temp.addVal(0);
            }
        }

        return beans;
    }


    //整体科研获奖信息
    public static ArrayList createWholeAwardDS(int startYear,int endYear,String ryxm,String rsbh,boolean isILab,String iLabBm)
    throws SQLException {
       //事先已经知道到底有多少年的了
       ArrayList<GeneralStatBean> beans = new ArrayList<GeneralStatBean>();
       for(int i = 0; i < endYear-startYear+1 ; i++){
           GeneralStatBean bean = new GeneralStatBean();
           beans.add(bean);
       }

       //获得数据库连接
        Connection conn = DBConnection.getConnection();

        StringBuffer bmmcQueryBuf = new StringBuffer();
        bmmcQueryBuf.append("SELECT bm,bmmc FROM ky_kyxtbmb WHERE bmfl='011' AND bm>'00000' ORDER BY bm");
        PreparedStatement bmmcStmt = conn.prepareStatement(bmmcQueryBuf.toString());
        ResultSet bzRs = bmmcStmt.executeQuery();

        //query.append("SELECT count(hjbh) ");
        //query.append("FROM ky_kyxtbmb,ky_kyhjxxb ");
        //query.append("WHERE ky_kyxtbmb.bm=ky_kyhjxxb.jldj AND ky_kyxtbmb.bm=? AND cjzwc=1 AND ky_kyhjxxb.hjsj>=? and ky_kyhjxxb.hjsj<=? ");
        String commonPart = "(ky_kyxtbmb.bm=ky_kyhjxxb.jldj AND ky_kyxtbmb.bm=?) AND cjzwc=1 AND ky_kyhjxxb.hjsj>=? and ky_kyhjxxb.hjsj<=?";