Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/WashReportHelper.java =================================================================== diff -u -r35910 -r35911 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/WashReportHelper.java (.../WashReportHelper.java) (revision 35910) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/WashReportHelper.java (.../WashReportHelper.java) (revision 35911) @@ -22,8 +22,15 @@ import org.springframework.stereotype.Component; import com.forgon.databaseadapter.service.DateQueryAdapter; +import com.forgon.disinfectsystem.common.CssdUtils; +import com.forgon.disinfectsystem.entity.basedatamanager.materialdefinition.MaterialDefinition; +import com.forgon.disinfectsystem.entity.basedatamanager.rinser.Rinser; +import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseDefinition; +import com.forgon.disinfectsystem.entity.washanddisinfectmanager.washanddisinfectrecord.WashAndDisinfectRecord; +import com.forgon.disinfectsystem.entity.washanddisinfectmanager.washanddisinfectrecord.WashAndDisinfectRecordMaterial; import com.forgon.disinfectsystem.jasperreports.javabeansource.CleaningAreaWorkloadForUser; import com.forgon.disinfectsystem.jasperreports.javabeansource.MaterialTypeWorkloadReport; +import com.forgon.disinfectsystem.jasperreports.javabeansource.WashAndDisinfectRecordItemBean; import com.forgon.disinfectsystem.jasperreports.service.dataindex.DataIndex; import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams; import com.forgon.disinfectsystem.tousse.materialdefinition.service.MaterialDefinitionManager; @@ -40,7 +47,7 @@ * 清洗模块的一些报表(超过500行建议新建一个helper) * 清洗区人员工作量统计报表 * 清洗区材料类型工作量统计报表 - * + * 清洗情况报表 */ @Component public class WashReportHelper { @@ -382,4 +389,102 @@ materialTypeSecquenceMap.put("未设置", maxSecquence + 1); return materialTypeSecquenceMap; } + /** + * 清洗情况报表 + * @param startDate 开始日期 + * @param endDate 结束日期 + * @param querySupplyRoom 供应室编码 + * @param rinserName 清洗机名称 + * @param disinfectProgram 清洗程序 + * @param tousseName 器械包名称 + * @return + */ + public List getWashAndDisinfectRecordDataSource( + String startDate, String endDate, String querySupplyRoom, + String rinserName, String disinfectProgram, String tousseName) { + //查询条件 + String queryCondition = " where 1=1 "; + queryCondition += " and " + dateQueryAdapter.dateAreaSql("alias.startDate", startDate + " 00:00:00", endDate + " 23:59:59"); + if(StringUtils.isNotBlank(querySupplyRoom)){ + queryCondition += SqlUtils.get_InSql_Extra("alias.orgUnitCoding", querySupplyRoom); + } + if(StringUtils.isNotBlank(rinserName)){ + queryCondition += " and alias.rinserName='"+ rinserName +"'"; + } + if(StringUtils.isNotBlank(disinfectProgram)){ + queryCondition += " and alias.disinfectProgram='"+ disinfectProgram +"'"; + } + if(StringUtils.isNotBlank(tousseName)){ + queryCondition += " and alias.tousseName='"+ tousseName +"'"; + } + int dataSoureOfMaterialsCountOfToussesInReports = CssdUtils.getSystemSetConfigByNameInt("dataSoureOfMaterialsCountOfToussesInReports", 3); + String columnSql = null; + if(dataSoureOfMaterialsCountOfToussesInReports == 3){ + columnSql = "ci.materialAmount"; + }else{ + columnSql = "(select sum(mi.count) from MaterialInstance mi where mi.tousse_id=ci.tousseDefinitionID) "; + } + //查询语句 + String querySql = "select alias.startDate,alias.endDate,alias.rinserName,alias.disinfectProgram,alias.tousseName,alias.operator,sum(alias.tousseAmount) tousseAmount from (" + + " select r.orgUnitCoding,r.name rinserName," + + " wr.disinfectProgram disinfectProgram," + + " ci.tousseNameForMaterial tousseName," + + " (ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) tousseAmount," + + " wr.startDate,wr.endDate,wr.operator" + + " from WashAndDisinfectRecord wr join rinser r on wr.rinserId = r.id" + + " join ClassifyBasket_WashRecord cw on cw.WashAndDisinfectRecord_ID=wr.id" + + " join ClassifyBasket cb on cw.ClassifyBasket_ID=cb.id" + + " join ClassifiedItem ci on ci.classifybasket_id=cb.id" + + " where ci.itemType='"+ MaterialDefinition.TYPE_NAME +"'" + + + " union all" + + + " select r.orgUnitCoding,r.name rinserName," + + " wr.disinfectProgram disinfectProgram," + + " ci.name tousseName," + + " (ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) * " + + columnSql + + " tousseAmount,wr.startDate,wr.endDate,wr.operator " + + " from WashAndDisinfectRecord wr join rinser r on wr.rinserId = r.id" + + " join ClassifyBasket_WashRecord cw on cw.WashAndDisinfectRecord_ID=wr.id" + + " join ClassifyBasket cb on cw.ClassifyBasket_ID=cb.id" + + " join ClassifiedItem ci on ci.classifybasket_id=cb.id" + + " where ci.itemType in('"+ TousseDefinition.PACKAGE_TYPE_INSIDE +"','"+ TousseDefinition.PACKAGE_TYPE_DISINFECTION + "')" + + " union all select wr.orgUnitCoding,r.name rinserName, wr.disinfectProgram disinfectProgram,null tousseName,wrm.amount tousseAmount,wr.startDate,wr.endDate,wr.operator " + + " from "+ WashAndDisinfectRecord.class.getSimpleName() +" wr inner join WashRecord_WashMaterial wm on wm.WashAndDisinfectRecord_ID = wr.id " + + " inner join "+ WashAndDisinfectRecordMaterial.class.getSimpleName() +" wrm on wrm.id = wm.WashAndDisinfectMaterial_ID inner join "+ MaterialDefinition.class.getSimpleName() +" md on wrm.materialDefinition_id = md.id inner join "+ Rinser.class.getSimpleName() +" r on r.id=wr.rinserId) alias" + queryCondition + + " group by alias.startDate,alias.endDate,alias.rinserName,alias.disinfectProgram,alias.tousseName,alias.operator"; + ResultSet rs = null; + List dataList = new ArrayList(); + try { + rs = objectDao.executeSql(querySql); + WashAndDisinfectRecordItemBean bean = null; + while(rs.next()){ + bean = new WashAndDisinfectRecordItemBean(); + bean.setRinserName(rs.getString("rinserName")); + bean.setDisinfectProgram(rs.getString("disinfectProgram")); + bean.setTousseName(rs.getString("tousseName")); + bean.setOperator(rs.getString("operator")); + + Object tousseAmountObj = rs.getObject("tousseAmount"); + if(tousseAmountObj != null && tousseAmountObj instanceof Number){ + bean.setTousseAmount(((Number)tousseAmountObj).intValue()); + } + Date startDateObj = rs.getTimestamp("startDate"); + if(startDateObj != null){ + bean.setStartDate(ForgonDateUtils.safelyFormatDate(startDateObj, ForgonDateUtils.SIMPLEDATEFORMAT_YYYYMMDDHHMMSS, null)); + } + Date endDateObj = rs.getTimestamp("endDate"); + if(startDateObj != null){ + bean.setEndDate(ForgonDateUtils.safelyFormatDate(endDateObj, ForgonDateUtils.SIMPLEDATEFORMAT_YYYYMMDDHHMMSS, null)); + } + dataList.add(bean); + } + } catch (Exception e) { + e.printStackTrace(); + }finally{ + DatabaseUtil.closeResultSetAndStatement(rs); + } + return dataList; + } } Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r35909 -r35911 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35909) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35911) @@ -5085,90 +5085,7 @@ public List getWashAndDisinfectRecordDataSource( String startDate, String endDate, String querySupplyRoom, String rinserName, String disinfectProgram, String tousseName) { - //查询条件 - String queryCondition = " where 1=1 "; - queryCondition += " and " + dateQueryAdapter.dateAreaSql("alias.startDate", startDate + " 00:00:00", endDate + " 23:59:59"); - if(StringUtils.isNotBlank(querySupplyRoom)){ - queryCondition += SqlUtils.get_InSql_Extra("alias.orgUnitCoding", querySupplyRoom); - } - if(StringUtils.isNotBlank(rinserName)){ - queryCondition += " and alias.rinserName='"+ rinserName +"'"; - } - if(StringUtils.isNotBlank(disinfectProgram)){ - queryCondition += " and alias.disinfectProgram='"+ disinfectProgram +"'"; - } - if(StringUtils.isNotBlank(tousseName)){ - queryCondition += " and alias.tousseName='"+ tousseName +"'"; - } - int dataSoureOfMaterialsCountOfToussesInReports = CssdUtils.getSystemSetConfigByNameInt("dataSoureOfMaterialsCountOfToussesInReports", 3); - String columnSql = null; - if(dataSoureOfMaterialsCountOfToussesInReports == 3){ - columnSql = "ci.materialAmount"; - }else{ - columnSql = "(select sum(mi.count) from MaterialInstance mi where mi.tousse_id=ci.tousseDefinitionID) "; - } - //查询语句 - String querySql = "select alias.startDate,alias.endDate,alias.rinserName,alias.disinfectProgram,alias.tousseName,alias.operator,sum(alias.tousseAmount) tousseAmount from (" - + " select r.orgUnitCoding,r.name rinserName," - + " wr.disinfectProgram disinfectProgram," - + " ci.tousseNameForMaterial tousseName," - + " (ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) tousseAmount," - + " wr.startDate,wr.endDate,wr.operator" - + " from WashAndDisinfectRecord wr join rinser r on wr.rinserId = r.id" - + " join ClassifyBasket_WashRecord cw on cw.WashAndDisinfectRecord_ID=wr.id" - + " join ClassifyBasket cb on cw.ClassifyBasket_ID=cb.id" - + " join ClassifiedItem ci on ci.classifybasket_id=cb.id" - + " where ci.itemType='"+ MaterialDefinition.TYPE_NAME +"'" - - + " union all" - - + " select r.orgUnitCoding,r.name rinserName," - + " wr.disinfectProgram disinfectProgram," - + " ci.name tousseName," - + " (ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) * " - + columnSql - + " tousseAmount,wr.startDate,wr.endDate,wr.operator " - + " from WashAndDisinfectRecord wr join rinser r on wr.rinserId = r.id" - + " join ClassifyBasket_WashRecord cw on cw.WashAndDisinfectRecord_ID=wr.id" - + " join ClassifyBasket cb on cw.ClassifyBasket_ID=cb.id" - + " join ClassifiedItem ci on ci.classifybasket_id=cb.id" - + " where ci.itemType in('"+ TousseDefinition.PACKAGE_TYPE_INSIDE +"','"+ TousseDefinition.PACKAGE_TYPE_DISINFECTION + "')" - + " union all select wr.orgUnitCoding,r.name rinserName, wr.disinfectProgram disinfectProgram,null tousseName,wrm.amount tousseAmount,wr.startDate,wr.endDate,wr.operator " - + " from "+ WashAndDisinfectRecord.class.getSimpleName() +" wr inner join WashRecord_WashMaterial wm on wm.WashAndDisinfectRecord_ID = wr.id " - + " inner join "+ WashAndDisinfectRecordMaterial.class.getSimpleName() +" wrm on wrm.id = wm.WashAndDisinfectMaterial_ID inner join "+ MaterialDefinition.class.getSimpleName() +" md on wrm.materialDefinition_id = md.id inner join "+ Rinser.class.getSimpleName() +" r on r.id=wr.rinserId) alias" + queryCondition - + " group by alias.startDate,alias.endDate,alias.rinserName,alias.disinfectProgram,alias.tousseName,alias.operator"; - ResultSet rs = null; - List dataList = new ArrayList(); - try { - rs = objectDao.executeSql(querySql); - WashAndDisinfectRecordItemBean bean = null; - while(rs.next()){ - bean = new WashAndDisinfectRecordItemBean(); - bean.setRinserName(rs.getString("rinserName")); - bean.setDisinfectProgram(rs.getString("disinfectProgram")); - bean.setTousseName(rs.getString("tousseName")); - bean.setOperator(rs.getString("operator")); - - Object tousseAmountObj = rs.getObject("tousseAmount"); - if(tousseAmountObj != null && tousseAmountObj instanceof Number){ - bean.setTousseAmount(((Number)tousseAmountObj).intValue()); - } - Date startDateObj = rs.getTimestamp("startDate"); - if(startDateObj != null){ - bean.setStartDate(ForgonDateUtils.safelyFormatDate(startDateObj, ForgonDateUtils.SIMPLEDATEFORMAT_YYYYMMDDHHMMSS, null)); - } - Date endDateObj = rs.getTimestamp("endDate"); - if(startDateObj != null){ - bean.setEndDate(ForgonDateUtils.safelyFormatDate(endDateObj, ForgonDateUtils.SIMPLEDATEFORMAT_YYYYMMDDHHMMSS, null)); - } - dataList.add(bean); - } - } catch (Exception e) { - e.printStackTrace(); - }finally{ - DatabaseUtil.closeResultSetAndStatement(rs); - } - return dataList; + return washReportHelper.getWashAndDisinfectRecordDataSource(startDate, endDate, querySupplyRoom, rinserName, disinfectProgram, tousseName); } @Override