srdatasourcefactory.java

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

        ArrayList beans = createILabPublishedArticleCollection(iLabBm);
        ds = new JRBeanCollectionDataSource(beans);
        return ds;
    }

    public static ArrayList createILabPublishedArticleCollection(String iLabBm) throws Exception {
        //获得数据库连接
        Connection conn = DBConnection.getConnection();
        ArrayList<ILabArticleBean> beans = new ArrayList<ILabArticleBean>();

        String query1 = "SELECT zzbh,cjzwc,zzmc,zzxm,cbs,cbsj FROM ky_zzxxb,ky_kyjgryxxb WHERE ky_kyjgryxxb.rybh=ky_zzxxb.zzbh_1 AND ky_kyjgryxxb.jgbm='"+iLabBm+"' ORDER BY zzbh,cjzwc";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query1);
        ILabArticleBean bean = new ILabArticleBean();

        while(rs.next()){
            String zzmc = rs.getString(3);//著作名称
            int cjzwc = rs.getInt(2);//参加者位次

            //对于同一本书的作者不需要新建
            if( !zzmc.equals(bean.getArticleName()) ){
                bean = new ILabArticleBean();
                bean.setArticleName(zzmc);
                //参加者位次最高的人也是负责人
                if( cjzwc==1 )
                    bean.setPrincipal(rs.getString(4));
                bean.setAuthor(rs.getString(4));
                bean.setPublishingCompany(rs.getString(5));
                bean.setPublishTime(rs.getTimestamp(6));
                beans.add(bean);
            }else
                bean.setAuthor(bean.getAuthor()+" "+rs.getString(4));
        }

        DBConnection.releaseConnection(conn);
        return beans;
    }


    /*****************************************************************
     * 获得省部级以上项目信息
     * @return JRDataSource
     * @throws SQLException
     ******************************************************************/
    public static JRDataSource createILabProvincPlusProjectDS(String iLabBm) throws SQLException, ParseException {
        JRBeanCollectionDataSource ds = null;
        ArrayList beans = createILabProvincPlusProjectCollection(iLabBm);
        ds = new JRBeanCollectionDataSource(beans);
        return ds;
    }

    public static ArrayList createILabProvincPlusProjectCollection(String iLabBm) throws SQLException, ParseException {
        //获得数据库连接
        Connection conn = DBConnection.getConnection();

        //System.out.println(query.toString());
        ArrayList beans = new ArrayList();//用于保存Bean的数组

        ILabProvincePlusProjectBean newBean;
        /**
          SELECT xmmc, xmly, xmryxm, xmjf, xmkssj, xmjzsj,cjzwc,ky_kyxtbmb.bz
         FROM sr_database.ky_kyxmxxb, sr_database.ky_kyxtbmb
         WHERE ky_kyxmxxb.xmlybm = ky_kyxtbmb.bm and cjzwc=1 and (ky_kyxtbmb.bz='国家级' OR ky_kyxtbmb.bz='省部级')
         */
        StringBuffer query = new StringBuffer();
        query.append("SELECT DISTINCT xmbh,xmmc, xmly, xmryxm, xmjf, xmkssj, xmjzsj,cjzwc,ky_kyxtbmb.bz ");
        query.append("FROM ky_kyxmxxb, ky_kyxtbmb,ky_kyjgryxxb ");
        query.append("WHERE ky_kyjgryxxb.rybh=ky_kyxmxxb.xmrybh AND ky_kyjgryxxb.jgbm='"+iLabBm+"' AND ky_kyxmxxb.xmlybm=ky_kyxtbmb.bm and cjzwc=1 and (ky_kyxtbmb.bz='国家级' OR ky_kyxtbmb.bz='省部级') ");
        query.append("ORDER BY ky_kyxmxxb.xmkssj");
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query.toString());
        while( rs.next() ){
             newBean = new ILabProvincePlusProjectBean();
             newBean.setProjectName(rs.getString(2));
             newBean.setProjectOrigen(rs.getString(3));
             newBean.setCommander(rs.getString(4));
             float money = rs.getFloat(5);
             if( money > 0 )
                 newBean.setMoney(String.valueOf(money));
             Timestamp startTime = rs.getTimestamp(6);
             Timestamp endTime   = rs.getTimestamp(7);
             newBean.setStartEndTime(GlobalUtil.ymTimestampFormater(startTime)+"-"+GlobalUtil.ymTimestampFormater(endTime));
             beans.add(newBean);
        }
        DBConnection.releaseConnection(conn);
        return beans;
    }



    /**************************************************************
     * 获得实验室人员承担项目情况(5年-10年)的数据源
     * @param startYear 开始年限
     * @param xmlybm      项目来源的数组，数组内含项目来源的的编码(char5)
     * @return
     * @throws SQLException
     ****************************************************************/
    public static JRDataSource createILabReseacherProjectsDS(int startYear,int yearLimit,String iLabBm) throws SQLException, ParseException {
        JRBeanCollectionDataSource ds = null;
        ArrayList beans = createILabReseacherProjectsCollection(startYear,yearLimit,iLabBm);
        ds = new JRBeanCollectionDataSource(beans);
        return ds;
    }

    /**
     * 提供五年的数据信息
     * @param startYear
     * @param xmlybm
     * @return
     * @throws SQLException
     */
    public static ArrayList createILabReseacherProjectsCollection(int startYear,int totalYear,String iLabBm) throws SQLException, ParseException {
    //获得数据库连接
    Connection conn = DBConnection.getConnection();

    //System.out.println(query.toString());
    ArrayList beans = new ArrayList();//用于保存Bean的数组

    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();

    /**  开始统计查询
       select ky_kyxtbmb.bz as xmlymc,ky_kyxmxxb.xmnd,count(xmbh) as xms,sum(xmjf) as jf,cjzwc
       from sr_database.ky_kyxtbmb,sr_database.ky_kyxmxxb
       where ky_kyxtbmb.bm=ky_kyxmxxb.xmlybm and ky_kyxtbmb.bz='国家级' and cjzwc=1
       group by ky_kyxmxxb.xmnd
       having ky_kyxmxxb.xmnd>='1995' and ky_kyxmxxb.xmnd<='2005'
       order by ky_kyxmxxb.xmnd
     */
    StringBuffer query = new StringBuffer();
    query.append("SELECT ky_kyxtbmb.bz as xmlymc,ky_kyxmxxb.xmkssj,count(xmbh) as xms,sum(xmjf) as jf,cjzwc ");
    query.append("FROM ky_kyxtbmb,ky_kyxmxxb,ky_kyjgryxxb ");
    query.append("WHERE ky_kyjgryxxb.rybh=ky_kyxmxxb.xmrybh AND ky_kyjgryxxb.jgbm='"+iLabBm+"' AND ky_kyxtbmb.bm=ky_kyxmxxb.xmlybm AND ky_kyxtbmb.bz=? AND cjzwc=1 ");
    query.append("GROUP BY ky_kyxmxxb.xmbh ");
    query.append("HAVING ky_kyxmxxb.xmkssj>=? and ky_kyxmxxb.xmkssj<=? ");
    query.append("ORDER BY ky_kyxmxxb.xmkssj");

    PreparedStatement stmt = conn.prepareStatement(query.toString());
    ILabReseacherProjectsBean totalBean = new ILabReseacherProjectsBean();//用于合计字段

    while( bzRs.next() ){
        String xmlymc = bzRs.getString(1);
        ILabReseacherProjectsBean newBean = new ILabReseacherProjectsBean();
        //填入查询参数
        stmt.setString(1,xmlymc);
        stmt.setString(2,String.valueOf(startYear)+"-1-1");
        stmt.setString(3,String.valueOf(startYear+totalYear-1)+"-12-31");
        //将查询结果填入
        ResultSet rs = stmt.executeQuery();

        //设置项目来源的中文名称

        newBean.setXmly(bzRs.getString(1));

        while(rs.next()){
            Timestamp xmkssj = rs.getTimestamp(2);//项目开始时间
            int year = Integer.parseInt(GlobalUtil.yTimestampFormater(xmkssj));//项目开始时间
            ILabReseacherProjectsAdd(newBean,rs,year-startYear);
            ILabReseacherProjectsTotal(newBean,totalBean,year-startYear,year);
        }
        beans.add(newBean);
    }

    totalBean.setXmly("合计");
    beans.add(totalBean);
    DBConnection.releaseConnection(conn);
    return beans;
  }

  /**
   * 为Bean添加统计值
   * @param bean
   * @param rs
   * @param yearNum
   */
  public static void ILabReseacherProjectsTotal(ILabReseacherProjectsBean bean,ILabReseacherProjectsBean totalBean,int yearDiff,int yearNum){
      try{
          switch(yearDiff){
          case 0:
              totalBean.setProjectCount1(bean.getProjectCount1()+totalBean.getProjectCount1());
              totalBean.setMoney1(bean.getMoney1()+totalBean.getMoney1());
              break;
          case 1:
              totalBean.setProjectCount2(bean.getProjectCount2()+totalBean.getProjectCount2());
              totalBean.setMoney2(bean.getMoney2()+totalBean.getMoney2());
              break;
          case 2:
              totalBean.setProjectCount3(bean.getProjectCount3()+totalBean.getProjectCount3());
              totalBean.setMoney3(bean.getMoney3()+totalBean.getMoney3());
              break;
          case 3:
              totalBean.setProjectCount4(bean.getProjectCount4()+totalBean.getProjectCount4());
              totalBean.setMoney4(bean.getMoney4()+totalBean.getMoney4());
              break;
          case 4:
              totalBean.setProjectCount5(bean.getProjectCount5()+totalBean.getProjectCount5());
              totalBean.setMoney5(bean.getMoney5()+totalBean.getMoney5());
              break;
          case 5:
              totalBean.setProjectCount6(bean.getProjectCount6()+totalBean.getProjectCount6());
              totalBean.setMoney6(bean.getMoney6()+totalBean.getMoney6());
              break;
          case 6:
              totalBean.setProjectCount7(bean.getProjectCount7()+totalBean.getProjectCount7());
              totalBean.setMoney7(bean.getMoney7()+totalBean.getMoney7());
              break;
          case 7:
              totalBean.setProjectCount8(bean.getProjectCount8()+totalBean.getProjectCount8());
              totalBean.setMoney8(bean.getMoney8()+totalBean.getMoney8());
              break;
          case 8:
              totalBean.setProjectCount9(bean.getProjectCount9()+totalBean.getProjectCount9());
              totalBean.setMoney9(bean.getMoney9()+totalBean.getMoney9());
              break;
          case 9:
              totalBean.setProjectCount10(bean.getProjectCount10()+totalBean.getProjectCount10());
              totalBean.setMoney10(bean.getMoney10()+totalBean.getMoney10());
              break;
          }
      }catch(Exception e){
          e.printStackTrace();
      }

  }

  /**
   * 为Bean的相应位置添加数据
   * @param bean
   * @param rs
   * @param yearDiff
   */
  public static void ILabReseacherProjectsAdd(ILabReseacherProjectsBean bean,ResultSet rs,int yearDiff){
      try{
          switch(yearDiff){
          case 0:
              bean.setProjectCount1(rs.getInt(3));
              bean.setMoney1((rs.getBigDecimal(4).floatValue()));
              break;
          case 1:
              bean.setProjectCount2(rs.getInt(3));
              bean.setMoney2((rs.getBigDecimal(4).floatValue()));
              break;
          case 2:
              bean.setProjectCount3(rs.getInt(3));
              bean.setMoney3((rs.getBigDecimal(4).floatValue()));
              break;
          case 3:
              bean.setProjectCount4(rs.getInt(3));
              bean.setMoney4((rs.getBigDecimal(4).floatValue()));
              break;
          case 4:
              bean.setProjectCount5(rs.getInt(3));
              bean.setMoney5((rs.getBigDecimal(4).floatValue()));
              break;
          case 5:
              bean.setProjectCount6(rs.getInt(3));
              bean.setMoney6((rs.getBigDecimal(4).floatValue()));
              break;
          case 6:
              bean.setProjectCount7(rs.getInt(3));
              bean.setMoney7((rs.getBigDecimal(4).floatValue()));
              break;
          case 7:
              bean.setProjectCount8(rs.getInt(3));
              bean.setMoney8((rs.getBigDecimal(4).floatValue()));
              break;
          case 8:
              bean.setProjectCount9(rs.getInt(3));
              bean.setMoney9((rs.getBigDecimal(4).floatValue()));
              break;
          case 9:
              bean.setProjectCount10(rs.getInt(3));
              bean.setMoney10((rs.getBigDecimal(4).floatValue()));
              break;
          }
      }catch(Exception e){
          e.printStackTrace();
      }

  }

  //Just for test
  public static void main(String[] s) throws SQLException, FileNotFoundException, JRException, ParseException {
      SimpleDateFormat format = new SimpleDateFormat("yyyy.MM");
      /*java.util.Date newDate = (java.util.Date) format.parse("2002-11-02");
      Calendar calendar = Calendar.getInstance();
      calendar.setTime(newDate);
      int year = calendar.get(Calendar.YEAR);
      int month = calendar.get(Calendar.MONTH);
      System.out.println("year="+year)*/;

      Timestamp time = new Timestamp(System.currentTimeMillis());
      System.out.println(format.format(time));
  }
}