statisticdsfactory.java

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

        String query = query = addSQLWarpper("count(hjbh)",
               isILab?"ky_kyxtbmb,ky_kyhjxxb,ky_kyjgryxxb":"ky_kyxtbmb,ky_kyhjxxb",
               isILab?"(ky_kyhjxxb.hjzbh=ky_kyjgryxxb.rybh AND ky_kyjgryxxb.jgbm='"+iLabBm+"') AND "+commonPart:commonPart,
               "ky_kyhjxxb.hjzbh",
               ryxm,rsbh);
        PreparedStatement stmt = conn.prepareStatement(query.toString());

        int index = 0;
        while( bzRs.next() ){
            String bm = bzRs.getString(1);//获得编码
            String mc = bzRs.getString(2);//获得名称

            //因为Bean第一位是年度位,所以index从1开始
            index++;
            for(int i = 0; i < beans.size(); i++){
                //填入查询参数
                stmt.setString(1,bm);
                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 createWholeResultDS(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='014' AND bm>'00000' ORDER BY bm");
        PreparedStatement bmmcStmt = conn.prepareStatement(bmmcQueryBuf.toString());
        ResultSet bzRs = bmmcStmt.executeQuery();

        //query.append("SELECT count(cgbh) ");
        //query.append("FROM ky_kyxtbmb,ky_zscqxxb ");
        //query.append("WHERE ky_kyxtbmb.bm=ky_zscqxxb.zllx AND ky_kyxtbmb.bm=? AND cjzwc=1 AND ky_zscqxxb.sqsj_1>=? and ky_zscqxxb.sqsj_1<=? ");
        String commonPart = "(ky_kyxtbmb.bm=ky_zscqxxb.zllx AND ky_kyxtbmb.bm=?) AND cjzwc=1 AND ky_zscqxxb.sqsj_1>=? and ky_zscqxxb.sqsj_1<=?";
        String query = query = addSQLWarpper("count(cgbh)",
               isILab?"ky_kyxtbmb,ky_zscqxxb,ky_kyjgryxxb":"ky_kyxtbmb,ky_zscqxxb",
               isILab?"(ky_zscqxxb.zlrbh=ky_kyjgryxxb.rybh AND ky_kyjgryxxb.jgbm='"+iLabBm+"') AND "+commonPart:commonPart,
               "ky_zscqxxb.zlrbh",
               ryxm,rsbh);

        PreparedStatement stmt = conn.prepareStatement(query.toString());

        int index = 0;
        while( bzRs.next() ){
            String bm = bzRs.getString(1);//获得编码
            String mc = bzRs.getString(2);//获得名称

            //因为Bean第一位是年度位,所以index从1开始
            index++;
            for(int i = 0; i < beans.size(); i++){
                //填入查询参数
                stmt.setString(1,bm);
                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 createWholeArticleDS(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='009' AND bm>'00000' ORDER BY bm");
        PreparedStatement bmmcStmt = conn.prepareStatement(bmmcQueryBuf.toString());
        ResultSet bzRs = bmmcStmt.executeQuery();

        //query.append("SELECT count(zzbh) ");
        //query.append("FROM ky_kyxtbmb,ky_zzxxb ");
        //query.append("WHERE ky_kyxtbmb.bm=ky_zzxxb.zzlb AND ky_kyxtbmb.bm=? AND cjzwc=1 AND ky_zzxxb.cbsj>=? and ky_zzxxb.cbsj<=? ");
        String commonPart = "ky_kyxtbmb.bm=ky_zzxxb.zzlb AND ky_kyxtbmb.bm=? AND cjzwc=1 AND ky_zzxxb.cbsj>=? and ky_zzxxb.cbsj<=?";
        String query = query = addSQLWarpper("count(zzbh)",
               isILab?"ky_kyxtbmb,ky_zzxxb,ky_kyjgryxxb":"ky_kyxtbmb,ky_zzxxb",
               isILab?"(ky_zzxxb.zzbh_1=ky_kyjgryxxb.rybh AND ky_kyjgryxxb.jgbm='"+iLabBm+"') AND "+commonPart:commonPart,
               "ky_zzxxb.zzbh_1",
               ryxm,rsbh);
        PreparedStatement stmt = conn.prepareStatement(query.toString());

        int index = 0;
        while( bzRs.next() ){
            String bm = bzRs.getString(1);//获得编码
            String mc = bzRs.getString(2);//获得名称

            //因为Bean第一位是年度位,所以index从1开始
            index++;
            for(int i = 0; i < beans.size(); i++){
                //填入查询参数
                stmt.setString(1,bm);
                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 createWholePaperDS(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='008' AND bm>'00000' ORDER BY bm");
        PreparedStatement bmmcStmt = conn.prepareStatement(bmmcQueryBuf.toString());
        ResultSet bzRs = bmmcStmt.executeQuery();

        //query.append("SELECT count(lwbh) ");
        //query.append("FROM ky_kyxtbmb,ky_kjlwxxb ");
        //query.append("WHERE ky_kyxtbmb.bm=ky_kjlwxxb.kwjb AND ky_kyxtbmb.bm=? AND cjzwc=1 AND ky_kjlwxxb.kwnd=? ");
        String commonPart = "ky_kyxtbmb.bm=ky_kjlwxxb.kwjb AND ky_kyxtbmb.bm=? AND cjzwc=1 AND ky_kjlwxxb.kwnd=?";
        String query = addSQLWarpper("count(lwbh)",
               isILab?"ky_kyxtbmb,ky_kjlwxxb,ky_kyjgryxxb":"ky_kyxtbmb,ky_kjlwxxb",
               isILab?"(ky_kjlwxxb.lwzzbh=ky_kyjgryxxb.rybh AND ky_kyjgryxxb.jgbm='"+iLabBm+"') AND "+commonPart:commonPart,
               "ky_kjlwxxb.lwzzbh",
               ryxm,rsbh);
        PreparedStatement stmt = conn.prepareStatement(query.toString());

        int index = 0;
        while( bzRs.next() ){
            String bm = bzRs.getString(1);//获得编码
            String mc = bzRs.getString(2);//获得名称

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

        return beans;
    }


}