srdatasourcefactory.java

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

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

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

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

import net.sf.jasperreports.engine.JRDataSource;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import java.text.SimpleDateFormat;
import java.text.ParseException;

public class SRDataSourceFactory {

    /************************************************************************
     * 生成访问学者情况的数据源
     * @return JRDataSource
     ***********************************************************************/
    public static JRDataSource createILabVisitorScholarDS(String iLabBm) throws Exception {
        JRBeanCollectionDataSource ds = null;
        ArrayList beans = createILabVisitorScholarCollection(iLabBm);
        ds = new JRBeanCollectionDataSource(beans);
        return ds;
    }

    public static ArrayList createILabVisitorScholarCollection(String iLabBm) throws Exception {
        Connection conn = DBConnection.getConnection();
        ArrayList<ILabVisitorScholarBean> beans = new ArrayList<ILabVisitorScholarBean>();
        ILabVisitorScholarBean newBean;

        StringBuffer vsIndexQuery = new StringBuffer();
        vsIndexQuery.append("SELECT DISTINCT xxbh,fwrxm,jsdw,fwqssj,fwjzsj,gj,hzxmmc,cgjj ");
        vsIndexQuery.append("FROM ky_fwxzxxb,ky_kyjgryxxb ");
        vsIndexQuery.append("WHERE ky_fwxzxxb.fwrbh=ky_kyjgryxxb.rybh AND ky_kyjgryxxb.jgbm='"+iLabBm+"'");

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(vsIndexQuery.toString());
        while(rs.next()){
            newBean = new ILabVisitorScholarBean();
            newBean.setFwrxm(rs.getString(2));
            newBean.setJsdw(rs.getString(3));
            newBean.setFwqssj(rs.getTimestamp(4));
            newBean.setFwjzsj(rs.getTimestamp(5));
            newBean.setGj(rs.getString(6));
            newBean.setHzxmmc(rs.getString(7));
            newBean.setCgjj(rs.getString(8));
            beans.add(newBean);
        }

        DBConnection.releaseConnection(conn);
        return beans;
    }



    /************************************************************************
     * 生成论文索引情况情况的数据源
     * @return JRDataSource
     ***********************************************************************/
    public static JRDataSource createILabPaperIndexedDS(String iLabBm) throws Exception {
        JRBeanCollectionDataSource ds = null;
        ArrayList beans = createILabPaperIndexedCollection(iLabBm);
        ds = new JRBeanCollectionDataSource(beans);
        return ds;
    }

    public static ArrayList createILabPaperIndexedCollection(String iLabBm) throws Exception {
        Connection conn = DBConnection.getConnection();
        ArrayList<ILabPaperIndexedBean> beans = new ArrayList<ILabPaperIndexedBean>();
        ILabPaperIndexedBean newBean;

        StringBuffer ppIndexQuery = new StringBuffer();
        ppIndexQuery.append("SELECT DISTINCT lwbh,lwmc,lwzzxm,kwmc,bz ");
        ppIndexQuery.append("FROM ky_kjlwxxb,ky_kyjgryxxb ");
        ppIndexQuery.append("WHERE ky_kyjgryxxb.rybh=ky_kjlwxxb.lwzzbh AND ky_kyjgryxxb.jgbm='"+iLabBm+"' AND cjzwc=1 ");
        ppIndexQuery.append("ORDER BY lwbh");

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(ppIndexQuery.toString());
        while(rs.next()){
            newBean = new ILabPaperIndexedBean();
            newBean.setPaperName(rs.getString(2));
            newBean.setAuthor(rs.getString(3));
            newBean.setMagazineName(rs.getString(4));
            newBean.setIndexInfo(rs.getString(5));
            beans.add(newBean);
        }

        DBConnection.releaseConnection(conn);
        return beans;
    }


    /************************************************************************
     * 生成获奖项目情况情况的数据源
     * @return JRDataSource
     **********************************************************************/
    public static JRDataSource createILabAwardProjectDS(String iLabBm) throws Exception {
        JRBeanCollectionDataSource ds = null;
        ArrayList beans = createILabAwardProjectCollection(iLabBm);
        ds = new JRBeanCollectionDataSource(beans);
        return ds;
    }

    public static ArrayList createILabAwardProjectCollection(String iLabBm) throws Exception {
        Connection conn = DBConnection.getConnection();
        ArrayList<ILabAwardProjectBean> beans = new ArrayList<ILabAwardProjectBean>();
        ILabAwardProjectBean newBean;

        String awardQuery = "SELECT DISTINCT hjbh,jljb,jldj,cgmc,jxmc,hjzxm,hjsj "+
                            "FROM ky_kyhjxxb,ky_kyjgryxxb "+
                            "WHERE ky_kyjgryxxb.rybh=ky_kyhjxxb.hjzbh AND ky_kyjgryxxb.jgbm='"+iLabBm+"' AND cjzwc=1 "+
                            "ORDER BY hjnd";
        ResultSet awardRs;
        Statement awardStmt = conn.createStatement();

        String bmNameQuery = "SELECT bmmc FROM ky_kyxtbmb WHERE bm=?";
        PreparedStatement bmStmt = conn.prepareStatement(bmNameQuery);
        ResultSet bmRs;

        awardRs = awardStmt.executeQuery(awardQuery);
        while( awardRs.next() ){
            String jljb = awardRs.getString(2);//获得奖励等级编号
            String jldj = awardRs.getString(3);//获得奖励级别的编号

            bmStmt.setString(1,jljb);
            bmRs = bmStmt.executeQuery();
            if( bmRs.next() )
                jljb = bmRs.getString(1);

            bmStmt.setString(1,jldj);
            bmRs = bmStmt.executeQuery();
            if( bmRs.next() )
                jldj = bmRs.getString(1);

            newBean = new ILabAwardProjectBean();
            newBean.setProjectName(awardRs.getString(4));
            newBean.setAwardingCompany(awardRs.getString(5));
            newBean.setAwardType(jljb);
            newBean.setAwardRank(jldj);
            newBean.setFirstPerson(awardRs.getString(6));
            newBean.setAwardingTime(GlobalUtil.ymdTimestampFormater(awardRs.getTimestamp(7)));
            beans.add(newBean);
        }
        DBConnection.releaseConnection(conn);
        return beans;
    }


    /************************************************************************
     * 生成专著,论文情况的数据源
     * @return JRDataSource
     ***********************************************************************/
    public static JRDataSource createILabArticlePaperDS(String startYear,String endYear,String iLabBm) throws Exception {
        JRBeanCollectionDataSource ds = null;
        ArrayList beans = createILabArticlePaperCollection(startYear,endYear,iLabBm);
        ds = new JRBeanCollectionDataSource(beans);
        return ds;
    }

    public static ArrayList createILabArticlePaperCollection(String startYear,String endYear,String iLabBm) throws Exception {
        Connection conn = DBConnection.getConnection();
        ArrayList<ILabArticlePaperBean> beans = new ArrayList<ILabArticlePaperBean>();
        ILabArticlePaperBean newBean;

        //起始年份
        int sYear = Integer.parseInt(startYear);
        //结束年份
        int eYear = Integer.parseInt(endYear);

        ILabArticlePaperBean totalBean = new ILabArticlePaperBean();
        totalBean.setYear("合计");
        //合计所用计数器
        int sAtcNum = 0,wAtcNum   = 0,tAtcNum =0;
        int nPprNum = 0,proPprNum = 0,locPprNum = 0,otrPprNum = 0;
        int sciNum  = 0,eiNum     = 0,istpNum   = 0;

        //暂时计数的字段
        int sAtcNumTemp = 0,wAtcNumTemp = 0,tAtcNumTemp =0;
        int nPprNumTemp = 0,proPprNumTemp = 0,locPprNumTemp = 0,otrPprNumTemp = 0;
        int sciNumTemp  = 0,eiNumTemp     = 0,istpNumTemp   = 0;

        //著作信息
        StringBuffer articleQuery = new StringBuffer();
        articleQuery.append("SELECT count(zzbh) ");
        articleQuery.append("FROM ky_zzxxb,ky_kyxtbmb,ky_kyjgryxxb ");
        articleQuery.append("WHERE ky_zzxxb.zzbh_1=ky_kyjgryxxb.rybh AND ky_kyjgryxxb.jgbm='"+iLabBm+"' AND ky_zzxxb.zzlb=ky_kyxtbmb.bm AND cjzwc=1 AND cbsj>=? AND cbsj<=? AND bm=?");
        PreparedStatement articleStmt = conn.prepareStatement(articleQuery.toString());
        ResultSet articleSet;

        //论文信息
        StringBuffer papereQuery = new StringBuffer();
        papereQuery.append("SELECT COUNT(lwbh) ");
        papereQuery.append("FROM ky_kjlwxxb,ky_kyxtbmb,ky_kyjgryxxb ");
        papereQuery.append("WHERE ky_kyjgryxxb.rybh=ky_kjlwxxb.lwzzbh AND ky_kyjgryxxb.jgbm='"+iLabBm+"' AND ky_kjlwxxb.kwjb=ky_kyxtbmb.bm AND cjzwc='1' AND kwnd=? AND ky_kyxtbmb.bm=?");
        PreparedStatement paperStmt = conn.prepareStatement(papereQuery.toString());
        ResultSet paperSet;

        //检索论文信息
        //注意!这里的这个表里的参加者位次是字符型
        String indexQuery1 = "SELECT COUNT(lwbh) FROM ky_kjlwxxb,ky_kyjgryxxb WHERE ky_kyjgryxxb.rybh=ky_kjlwxxb.lwzzbh AND ky_kyjgryxxb.jgbm='"+iLabBm+"' AND cjzwc='1' AND SCI='1'  AND kwnd=?";
        String indexQuery2 = "SELECT COUNT(lwbh) FROM ky_kjlwxxb,ky_kyjgryxxb WHERE ky_kyjgryxxb.rybh=ky_kjlwxxb.lwzzbh AND ky_kyjgryxxb.jgbm='"+iLabBm+"' AND cjzwc='1' AND EI='1'   AND kwnd=? ";
        String indexQuery3 = "SELECT COUNT(lwbh) FROM ky_kjlwxxb,ky_kyjgryxxb WHERE ky_kyjgryxxb.rybh=ky_kjlwxxb.lwzzbh AND ky_kyjgryxxb.jgbm='"+iLabBm+"' AND cjzwc='1' AND ISTP='1' AND kwnd=?";
        PreparedStatement indexStmt1 = conn.prepareStatement(indexQuery1);
        PreparedStatement indexStmt2 = conn.prepareStatement(indexQuery2);
        PreparedStatement indexStmt3 = conn.prepareStatement(indexQuery3);
        ResultSet indexRs;

        //需要叠代 eYear-sYear+1次
        for(int i = sYear; i <= eYear; i++){
            newBean = new ILabArticlePaperBean();
            newBean.setYear(String.valueOf(i));

            sAtcNumTemp = 0;wAtcNumTemp = 0;tAtcNumTemp =0;
            nPprNumTemp = 0;proPprNumTemp=0;locPprNumTemp =0;otrPprNumTemp = 0;
            sciNumTemp  = 0;eiNumTemp    =0;istpNumTemp   =0;

            /** 出版著作部分 **/
            //对于专著部分作特殊处理
            articleStmt.setString(1,String.valueOf(i)+"-1-1");
            articleStmt.setString(2,String.valueOf(i)+"-12-31");
            articleStmt.setString(3,"00901");
            //专著
            articleSet = articleStmt.executeQuery();
            if( articleSet.next() ){
                sAtcNumTemp = articleSet.getBigDecimal(1).intValue();
                //sAtcNum    += sAtcNumTemp;
            }
            articleStmt.setString(3,"00902");
            articleSet = articleStmt.executeQuery();
            if( articleSet.next() ){
                sAtcNumTemp += articleSet.getBigDecimal(1).intValue();
                sAtcNum     += sAtcNumTemp;
            }
            //编著
            articleStmt.setString(3,"00903");
            articleSet = articleStmt.executeQuery();
            if( articleSet.next() ){
                wAtcNumTemp = articleSet.getBigDecimal(1).intValue();
                wAtcNum    += wAtcNumTemp;
            }
            //译著
            articleStmt.setString(3,"00904");
            articleSet = articleStmt.executeQuery();
            if( articleSet.next() ){
                tAtcNumTemp = articleSet.getBigDecimal(1).intValue();
                tAtcNum    += tAtcNumTemp;
            }
            newBean.setSpecialArticleNum(sAtcNumTemp==0?"":String.valueOf(sAtcNumTemp));
            newBean.setWriteArticleNum(wAtcNumTemp==0?"":String.valueOf(wAtcNumTemp));
            newBean.setTranslateArticleNum(tAtcNumTemp==0?"":String.valueOf(tAtcNumTemp));

            /** 论文部分 **/
            paperStmt.setString(1,String.valueOf(i));
            paperStmt.setString(2,"08001");//国家级固定为00801
            paperSet = paperStmt.executeQuery();
            if( paperSet.next() ){
                nPprNumTemp +=  paperSet.getBigDecimal(1).intValue();
                nPprNum     += nPprNumTemp;
            }
            paperStmt.setString(2,"08002");//省级固定为00802
            paperSet = paperStmt.executeQuery();
            if( paperSet.next() ){
                proPprNumTemp +=  paperSet.getBigDecimal(1).intValue();
                proPprNum     += proPprNumTemp;
            }
            paperStmt.setString(2,"08003");//地方级固定为00803
            paperSet = paperStmt.executeQuery();
            if( paperSet.next() ){
                locPprNumTemp +=  paperSet.getBigDecimal(1).intValue();
                locPprNum     += locPprNumTemp;
            }
            paperStmt.setString(2,"08004");//其他级别固定为00804
            paperSet = paperStmt.executeQuery();
            if( paperSet.next() ){
                otrPprNumTemp +=  paperSet.getBigDecimal(1).intValue();
                otrPprNum     +=  otrPprNumTemp;
            }
            newBean.setNationPaperNum(nPprNumTemp==0?"":String.valueOf(nPprNumTemp));
            newBean.setProvincePaperNum(proPprNumTemp==0?"":String.valueOf(proPprNumTemp));
            newBean.setLocalPaperNum(locPprNumTemp==0?"":String.valueOf(locPprNumTemp));
            newBean.setOtherPaperNum(otrPprNumTemp==0?"":String.valueOf(otrPprNumTemp));

            /** 索引论文情况 **/
            indexStmt1.setString(1,String.valueOf(i));
            indexRs = indexStmt1.executeQuery();
            if( indexRs.next() ){
                sciNumTemp = indexRs.getBigDecimal(1).intValue();
                sciNum += sciNumTemp;
            }
            indexStmt2.setString(1,String.valueOf(i));
            indexRs = indexStmt2.executeQuery();
            if( indexRs.next() ){
                eiNumTemp = indexRs.getBigDecimal(1).intValue();
                eiNum += eiNumTemp;
            }