📄 srdatasourcefactory.java
字号:
indexStmt3.setString(1,String.valueOf(i));
indexRs = indexStmt3.executeQuery();
if( indexRs.next() ){
istpNumTemp = indexRs.getBigDecimal(1).intValue();
istpNum += istpNumTemp;
}
newBean.setSciPaperNum(sciNumTemp==0?"":String.valueOf(sciNumTemp));
newBean.setEiPaperNum(eiNumTemp==0?"":String.valueOf(eiNumTemp));
newBean.setIstpPaperNum(istpNumTemp==0?"":String.valueOf(istpNumTemp));
beans.add(newBean);
}
totalBean.setSpecialArticleNum(sAtcNum==0?"":String.valueOf(sAtcNum));
totalBean.setWriteArticleNum(wAtcNum==0?"":String.valueOf(wAtcNum));
totalBean.setTranslateArticleNum(tAtcNum==0?"":String.valueOf(tAtcNum));
totalBean.setNationPaperNum(nPprNum==0?"":String.valueOf(nPprNum));
totalBean.setProvincePaperNum(proPprNum==0?"":String.valueOf(proPprNum));
totalBean.setLocalPaperNum(locPprNum==0?"":String.valueOf(locPprNum));
totalBean.setOtherPaperNum(otrPprNum==0?"":String.valueOf(otrPprNum));
totalBean.setSciPaperNum(sciNum==0?"":String.valueOf(sciNum));
totalBean.setEiPaperNum(eiNum==0?"":String.valueOf(eiNum));
totalBean.setIstpPaperNum(istpNum==0?"":String.valueOf(istpNum));
beans.add(totalBean);
DBConnection.releaseConnection(conn);
return beans;
}
/////////////////////////////////////////////////////////////////////////
/************************************************************************
* 生成研究成果情况的数据源
* @return JRDataSource
***********************************************************************/
public static JRDataSource createILabResearchResultDS(String startYear,String endYear,String iLabBm) throws Exception {
JRBeanCollectionDataSource ds = null;
ArrayList beans = createILabResearchResultCollection(startYear,endYear,iLabBm);
ds = new JRBeanCollectionDataSource(beans);
return ds;
}
public static ArrayList createILabResearchResultCollection(String startYear,String endYear,String iLabBm) throws Exception {
Connection conn = DBConnection.getConnection();
ArrayList<ILabResearchResultBean> beans = new ArrayList<ILabResearchResultBean>();
ILabResearchResultBean newBean;
ILabResearchResultBean totalBean = new ILabResearchResultBean();
totalBean.setYear("合计");
//保存12个字段的合计值
int[] total = new int[12];
int sYear = Integer.parseInt(startYear);
int eYear = Integer.parseInt(endYear);
/*项目信息的查询--即鉴定成果的查询*/
StringBuffer resultQuery = new StringBuffer();
resultQuery.append("SELECT COUNT(xmbh) ");
resultQuery.append("FROM ky_kyxmxxb,ky_kyxtbmb,ky_kyjgryxxb ");
resultQuery.append("WHERE ky_kyjgryxxb.rybh=ky_kyxmxxb.xmrybh AND ky_kyjgryxxb.jgbm='"+iLabBm+"' ");
resultQuery.append("AND ky_kyxmxxb.xmlybm=ky_kyxtbmb.bm AND cjzwc=1 AND xmjtsj>=? and xmjtsj<=? AND ky_kyxtbmb.bz=?");
PreparedStatement rspStmt = conn.prepareStatement(resultQuery.toString());
//分别是国家级的,省部级和厅局级的RS
ResultSet nationalRs,provinceRs,directorRs;
int nationalNum,provinceNum,directorNum,totalNum;
/** 专利部分的查询
SELECT cgbh,sqsj
FROM ky_zscqxxb,ky_kyxtbmb
WHERE ky_zscqxxb.zllx=ky_kyxtbmb.bm AND cjzwc=1 AND cgnd='2005' AND ky_kyxtbmb.bmmc='发明专利'
**/
StringBuffer patentQueryApp = new StringBuffer();
patentQueryApp.append("SELECT DISTINCT cgbh,sqsj ");
patentQueryApp.append("FROM ky_zscqxxb,ky_kyxtbmb,ky_kyjgryxxb ");
patentQueryApp.append("WHERE ky_kyjgryxxb.rybh=ky_zscqxxb.zlrbh AND ky_kyjgryxxb.jgbm='"+iLabBm+"' ");
patentQueryApp.append("AND ky_zscqxxb.zllx=ky_kyxtbmb.bm AND cjzwc=1 AND cgnd=? AND ky_kyxtbmb.bm=? ");
PreparedStatement patentStmt = conn.prepareStatement(patentQueryApp.toString());
//分别是发明专利,实用新型和外观设计
ResultSet iRs,pRs,uiRs;
int iAppNum,iAuthNum,pAppNum,pAuthNum,uiAppNum,uiAuthNum,totalAppPatentNum,totalAuthPatentNum;
//共需进行eYear-sYear次叠代
for(int i = sYear; i <= eYear; i++){
newBean = new ILabResearchResultBean();
//设置年度
newBean.setYear(String.valueOf(i));
/** 专利部分 *************************/
iAppNum=0;iAuthNum=0;pAppNum=0;pAuthNum=0;
uiAppNum=0;uiAuthNum=0;totalAppPatentNum=0;totalAuthPatentNum=0;
patentStmt.setString(1,String.valueOf(i));
patentStmt.setString(2,"01401");//发明专利
iRs = patentStmt.executeQuery();
while(iRs.next()){
if( iRs.getTimestamp(2)==null ){
iAppNum++;
totalAppPatentNum++;
}
else{
iAuthNum++;
totalAuthPatentNum++;
}
}
patentStmt.setString(2,"01402");//实用新型
pRs = patentStmt.executeQuery();
while(pRs.next()){
if( pRs.getTimestamp(2)==null ){
pAppNum++;
totalAppPatentNum++;
}
else{
pAuthNum++;
totalAuthPatentNum++;
}
}
patentStmt.setString(2,"01403");//外观设计
uiRs= patentStmt.executeQuery();
while(uiRs.next()){
if( uiRs.getTimestamp(2)==null ){
uiAppNum++;
totalAppPatentNum++;
}
else{
uiAuthNum++;
totalAuthPatentNum++;
}
}
newBean.setInventPatentApp(iAppNum==0?"":String.valueOf(iAppNum));
newBean.setInventPatentAuth(iAuthNum==0?"":String.valueOf(iAuthNum));
newBean.setPracticalPatenApp(pAppNum==0?"":String.valueOf(pAppNum));
newBean.setPracticalPatenAuth(pAuthNum==0?"":String.valueOf(pAuthNum));
newBean.setUiPatentApp(uiAppNum==0?"":String.valueOf(uiAppNum));
newBean.setUiPatentAuth(uiAuthNum==0?"":String.valueOf(uiAuthNum));
newBean.setTotalPatenApp(totalAppPatentNum==0?"":String.valueOf(totalAppPatentNum));
newBean.setTotalPatenAuth(totalAuthPatentNum==0?"":String.valueOf(totalAuthPatentNum));
/*项目信息的查询--即鉴定成果的查询***************/
rspStmt.setString(1,String.valueOf(i)+"-1-1");
rspStmt.setString(2,String.valueOf(i)+"-12-31");
rspStmt.setString(3,"国家级");
nationalRs = rspStmt.executeQuery();
if( nationalRs.next() )
nationalNum = nationalRs.getBigDecimal(1).intValue();
rspStmt.setString(3,"省部级");
provinceRs = rspStmt.executeQuery();
if( provinceRs.next() )
provinceNum = provinceRs.getBigDecimal(1).intValue();
rspStmt.setString(3,"厅局级");
directorRs = rspStmt.executeQuery();
if( directorRs.next() )
directorNum = directorRs.getBigDecimal(1).intValue();
nationalNum = 0;
provinceNum = 0;
directorNum = 0;
totalNum = 0;
totalNum = nationalNum + provinceNum + directorNum;
newBean.setNationResult(nationalNum==0?"":String.valueOf(nationalNum));
newBean.setProvinceResult(provinceNum==0?"":String.valueOf(provinceNum));
newBean.setDirectorResult(directorNum==0?"":String.valueOf(directorNum));
newBean.setTotalResult(totalNum==0?"":String.valueOf(totalNum));
//计算总和
//int iAppNum,iAuthNum,pAppNum,pAuthNum,uiAppNum,uiAuthNum,totalAppPatentNum,totalAuthPatentNum;
total[0] += nationalNum;
total[1] += provinceNum;
total[2] += directorNum;
total[3] += totalNum;
total[4] += iAppNum;
total[5] += iAuthNum;
total[6] += pAppNum;
total[7] += pAuthNum;
total[8] += uiAppNum;
total[9] += uiAuthNum;
total[10]+= totalAppPatentNum;
total[11]+= totalAuthPatentNum;
beans.add(newBean);
}
totalBean.setNationResult(total[0]==0?"":String.valueOf(total[0]));
totalBean.setProvinceResult(total[1]==0?"":String.valueOf(total[1]));
totalBean.setDirectorResult(total[2]==0?"":String.valueOf(total[2]));
totalBean.setTotalResult(total[3]==0?"":String.valueOf(total[3]));
totalBean.setInventPatentApp(total[4]==0?"":String.valueOf(total[4]));
totalBean.setInventPatentAuth(total[5]==0?"":String.valueOf(total[5]));
totalBean.setPracticalPatenApp(total[6]==0?"":String.valueOf(total[6]));
totalBean.setPracticalPatenAuth(total[7]==0?"":String.valueOf(total[7]));
totalBean.setUiPatentApp(total[8]==0?"":String.valueOf(total[8]));
totalBean.setUiPatentAuth(total[9]==0?"":String.valueOf(total[9]));
totalBean.setTotalPatenApp(total[10]==0?"":String.valueOf(total[10]));
totalBean.setTotalPatenAuth(total[11]==0?"":String.valueOf(total[11]));
beans.add(totalBean);
DBConnection.releaseConnection(conn);
return beans;
}
/************************************************************************
* 生成学术交流情况的数据源
* @return JRDataSource
***********************************************************************/
public static JRDataSource createILabScienceCommDS(String startYear,String endYear,String iLabBm) throws Exception {
JRBeanCollectionDataSource ds = null;
ArrayList beans = createILabScienceCommCollection(startYear,endYear,iLabBm);
ds = new JRBeanCollectionDataSource(beans);
return ds;
}
public static ArrayList createILabScienceCommCollection(String startYear,String endYear,String iLabBm) throws Exception {
Connection conn = DBConnection.getConnection();
ArrayList<ILabScienceCommBean> beans = new ArrayList<ILabScienceCommBean>();
ILabScienceCommBean newBean;
ILabScienceCommBean totalBean = new ILabScienceCommBean();
totalBean.setYear("总计");
int attConHomeNum=0, attConAbroadNum = 0,acceptVisiAbroadNum=0,sendVisiAbroadNum=0;
int startYearNum = Integer.parseInt(startYear);
int endYearNum = Integer.parseInt(endYear);
//学术会议查询
//参加国内会议
String conQuery1 = "SELECT COUNT(hybh) FROM ky_xshyxxb,ky_kyjgryxxb WHERE ky_kyjgryxxb.rybh=ky_xshyxxb.cjrbh AND ky_kyjgryxxb.jgbm='"+iLabBm+"' AND hynd=? and hylx='1' ";
//参加国外会议
String conQuery2 = "SELECT COUNT(hybh) FROM ky_xshyxxb,ky_kyjgryxxb WHERE ky_kyjgryxxb.rybh=ky_xshyxxb.cjrbh AND ky_kyjgryxxb.jgbm='"+iLabBm+"' AND hynd=? and hylx='2'";
//访问学者查询
//接受访问学者
String visitorQuery1 = "SELECT COUNT(xxbh) FROM ky_fwxzxxb,ky_kyjgryxxb WHERE ky_kyjgryxxb.rybh=ky_fwxzxxb.fwrbh AND ky_kyjgryxxb.jgbm='"+iLabBm+"' AND fwqssj>=? and fwqssj<=? and fwlb='1'";
//派出访问学者
String visitorQuery2 = "SELECT COUNT(distinct xxbh) FROM ky_fwxzxxb,ky_kyjgryxxb WHERE ky_kyjgryxxb.rybh=ky_fwxzxxb.fwrbh AND ky_kyjgryxxb.jgbm='"+iLabBm+"' AND fwqssj>=? and fwqssj<=? and fwlb='2'";
PreparedStatement conpStmt1 = conn.prepareStatement(conQuery1);
PreparedStatement conpStmt2 = conn.prepareStatement(conQuery2);
PreparedStatement visipStmt1 = conn.prepareStatement(visitorQuery1);
PreparedStatement visipStmt2 = conn.prepareStatement(visitorQuery2);
ResultSet commRs;
for(int i = startYearNum; i <= endYearNum; i++){
newBean = new ILabScienceCommBean();
newBean.setYear(String.valueOf(i));
conpStmt1.setString(1,String.valueOf(i));
conpStmt2.setString(1,String.valueOf(i));
visipStmt1.setString(1,String.valueOf(i)+"-1-1");
visipStmt1.setString(2,String.valueOf(i)+"-12-31");
visipStmt2.setString(1,String.valueOf(i)+"-1-1");
visipStmt2.setString(2,String.valueOf(i)+"-12-31");
commRs = conpStmt1.executeQuery();
if( commRs.next() ){
newBean.setAttendConHome(commRs.getInt(1) == 0 ? "" : String.valueOf(commRs.getInt(1)));
attConHomeNum += commRs.getInt(1);
}
commRs = conpStmt2.executeQuery();
if( commRs.next() ){
newBean.setAttendConAbroad(commRs.getInt(1) == 0 ? "" : String.valueOf(commRs.getInt(1)));
attConAbroadNum += commRs.getInt(1);
}
commRs = visipStmt1.executeQuery();
if( commRs.next() ){
newBean.setAcceptVisitorAbroad(commRs.getInt(1) == 0 ? "" : String.valueOf(commRs.getInt(1)));
acceptVisiAbroadNum += commRs.getInt(1);
}
commRs = visipStmt2.executeQuery();
if( commRs.next() ){
newBean.setSendVisitorAbroad(commRs.getInt(1) == 0 ? "" : String.valueOf(commRs.getInt(1)));
sendVisiAbroadNum += commRs.getInt(1);
}
beans.add(newBean);
}
totalBean.setAttendConAbroad(attConAbroadNum==0?"":String.valueOf(attConAbroadNum));
totalBean.setAttendConHome(attConHomeNum==0?"":String.valueOf(attConHomeNum));
totalBean.setAcceptVisitorAbroad(acceptVisiAbroadNum==0?"":String.valueOf(acceptVisiAbroadNum));
totalBean.setSendVisitorAbroad(sendVisiAbroadNum==0?"":String.valueOf(sendVisiAbroadNum));
beans.add(totalBean);
DBConnection.releaseConnection(conn);
return beans;
}
/************************************************************************
* 生成出版著作情况的数据源--考虑了多个人一个著作的情况
* @return JRDataSource
***********************************************************************/
public static JRDataSource createILabPublishedArticleDS(String iLabBm) throws Exception {
JRBeanCollectionDataSource ds = null;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -