Index: forgon-core/src/main/java/com/forgon/directory/service/OrgUnitGroupManager.java =================================================================== diff -u -r33552 -r34352 --- forgon-core/src/main/java/com/forgon/directory/service/OrgUnitGroupManager.java (.../OrgUnitGroupManager.java) (revision 33552) +++ forgon-core/src/main/java/com/forgon/directory/service/OrgUnitGroupManager.java (.../OrgUnitGroupManager.java) (revision 34352) @@ -52,4 +52,9 @@ * @return */ public List loadOrgUnits(Long groupID, String groupType); + /** + * 获取手术器械科室分组设置指定所有科室名称 + * @return + */ + public String getOperatingRoomNames(); } Index: forgon-core/src/main/java/com/forgon/directory/service/OrgUnitGroupManagerImpl.java =================================================================== diff -u -r33552 -r34352 --- forgon-core/src/main/java/com/forgon/directory/service/OrgUnitGroupManagerImpl.java (.../OrgUnitGroupManagerImpl.java) (revision 33552) +++ forgon-core/src/main/java/com/forgon/directory/service/OrgUnitGroupManagerImpl.java (.../OrgUnitGroupManagerImpl.java) (revision 34352) @@ -287,5 +287,16 @@ groupID); return objectDao.findByHql(hql); } - + @Override + public String getOperatingRoomNames() { + List orgUnitList = loadOrgUnits(null, OrgUnitGroup.GROUP_TYPE_OPERATIONROOM_GROUP); + if(CollectionUtils.isEmpty(orgUnitList)){ + return ""; + } + Set orgUnitNameSet = new HashSet(); + for (OrgUnit orgUnit : orgUnitList) { + orgUnitNameSet.add(orgUnit.getName()); + } + return StringTools.join(orgUnitNameSet, ","); + } } Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfZsyy.java =================================================================== diff -u -r34199 -r34352 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfZsyy.java (.../CustomReportsOfZsyy.java) (revision 34199) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfZsyy.java (.../CustomReportsOfZsyy.java) (revision 34352) @@ -204,15 +204,15 @@ bean3.setTitle("应急处理器械次数 "); bean3.setRowNum(list.size()+1); list.add(bean3); - String operatingRoomCodes = getOperatingRoomCodes(); + String operatingRoomNames = orgUnitGroupManager.getOperatingRoomNames(); String notInDepartSql = ""; - if(StringUtils.isNotBlank(operatingRoomCodes)){ - String[] departArr = operatingRoomCodes.split(","); + if(StringUtils.isNotBlank(operatingRoomNames)){ + String[] departArr = operatingRoomNames.split(","); for (int i = 0; i < departArr.length; i++) { - notInDepartSql += " and rr.departCode != '" + departArr[i] + "' "; + notInDepartSql += " and ci.orgUnitName != '" + departArr[i] + "' "; } } - // 普通器械清洗不合格率 普通器械清洗不合格 + // 普通器械清洗不合格率 普通器械清洗不合格 按照回收时科室不等于“手术一区、手术二区、手术三区”，并且器械包定义的器械包分组不等于“专科”的器械包材料清洗总件数。 WorkQualityCollection bean4 = new WorkQualityCollection(); for (String key : startAndEndDays.keySet()){ Map innerMap = startAndEndDays.get(key); @@ -221,24 +221,26 @@ endDay = innerMap.get(keyOfInnerMap); } sql = getWorkUnQualityAmountContainSql("普通器械清洗不合格",startDay,endDay,querySupplyRoom); - totalSql = "select sum("+ columnSqlForBean4Or6 +") from RecyclingRecord rr join RecyclingItem ritem on ritem.recyclingRecord_id = rr.id " - + joinSqlForBean4 - + " join ClassifyBasket_RecyclingRecord cr on rr.id=cr.RecyclingRecord_ID " - + " join ClassifyBasket cb on cr.ClassifyBasket_ID=cb.id join ClassifyBasket_WashRecord cw on cb.id=cw.ClassifyBasket_ID " - + " join WashAndDisinfectRecord wr on cw.WashAndDisinfectRecord_ID=wr.id " - + " where rr.depart is not null " - //+ "and rr.depart != '手术一区' and rr.depart != '手术二区' and rr.depart != '手术三区' " - + notInDepartSql - + "and wr.id is not null " - + " and ritem.tousseDefinitionId in (select cit.tousseDefinitionID from ClassifyBasket clb,ClassifiedItem cit where clb.id=cit.classifybasket_id and cb.id=clb.id) " - + " and rr.orgUnitCoding = '"+querySupplyRoom+"'" - + " and (rr.recyclingTime between "+dateQueryAdapter.dateAdapter(startDay)+" and " + dateQueryAdapter.dateAdapter(endDay) + ")"; + ReportQueryParams params = new ReportQueryParams(); + String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDay),dateQueryAdapter.dateAdapter(endDay)); + params.betweenSql = betweenSql; + params.querySupplyRoom = querySupplyRoom; + params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; + params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = " and tdc.tousseGroupName<>'专科' " + notInDepartSql; + params.tousseGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + params.tousseTypeAndPackageSizeSql = ""; + //数据库计算字符串长度的函数名 + params.monthlyStr = dateQueryAdapter.dateConverAdapter3("wr.endDate","mm"); + // 处理器械总件数 + totalSql = String.format("select sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl "); setWorkQualityCollectionData(bean4,key,sql,totalSql); } bean4.setTitle("普通器械清洗不合格率"); bean4.setRowNum(list.size()+1); list.add(bean4); - // 专科器械清洗不合格率 专科器械清洗不合格 + // 专科器械清洗不合格率 专科器械清洗不合格 按照回收时科室不等于“手术一区、手术二区、手术三区”，并且器械包定义的器械包分组等于“专科” 的器械材料清洗总件数。 WorkQualityCollection bean5 = new WorkQualityCollection(); for (String key : startAndEndDays.keySet()){ Map innerMap = startAndEndDays.get(key); @@ -252,12 +254,10 @@ params.betweenSql = betweenSql; params.querySupplyRoom = querySupplyRoom; params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; - params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = " and tdc.tousseGroupName='专科' "; + params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = " and tdc.tousseGroupName='专科' " + notInDepartSql; params.tousseGroupSqlWithAliasOfTousseDefinitionIsTd = ""; params.tousseTypeAndPackageSizeSql = ""; //数据库计算字符串长度的函数名 - String sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); - //params.sqlLengthFunctionName = sqlLengthFunctionName; params.monthlyStr = dateQueryAdapter.dateConverAdapter3("wr.endDate","mm"); // 处理器械总件数 totalSql = String.format("select sum(tl.amount) amount from (" @@ -271,35 +271,41 @@ // 手术器械清洗不合格率 手术器械清洗不合格 WorkQualityCollection bean6 = new WorkQualityCollection(); String departSql = ""; - if(StringUtils.isNotBlank(operatingRoomCodes)){ - String[] departArr = operatingRoomCodes.split(","); + if(StringUtils.isNotBlank(operatingRoomNames)){ + String[] departArr = operatingRoomNames.split(","); departSql += " and ("; for (int i = 0; i < departArr.length; i++) { if(i == 0){ - departSql += " rr.departCode='" + departArr[i] + "' "; + departSql += " ci.orgUnitName='" + departArr[i] + "' "; }else{ - departSql += " or rr.departCode='" + departArr[i] + "' "; + departSql += " or ci.orgUnitName='" + departArr[i] + "' "; } } departSql += ") "; } + //按照回收科室等于“手术一区、手术二区、手术三区”的这3个手术科室的器械材料清洗总件数。目前手术科室在config.js中通过operatingRoomCodes进行配置。 for (String key : startAndEndDays.keySet()){ Map innerMap = startAndEndDays.get(key); for (String keyOfInnerMap : innerMap.keySet()) { startDay = keyOfInnerMap; endDay = innerMap.get(keyOfInnerMap); } sql = getWorkUnQualityAmountContainSql("手术器械清洗不合格",startDay,endDay,querySupplyRoom); - totalSql = "select sum("+ columnSqlForBean4Or6 +") from RecyclingRecord rr join RecyclingItem ritem on ritem.recyclingRecord_id = rr.id " - + joinSqlForBean6 - + " join ClassifyBasket_RecyclingRecord cr on rr.id=cr.RecyclingRecord_ID join ClassifyBasket cb on cr.ClassifyBasket_ID=cb.id join ClassifyBasket_WashRecord cw on cb.id=cw.ClassifyBasket_ID " - + " join WashAndDisinfectRecord wr on cw.WashAndDisinfectRecord_ID=wr.id " - + " where rr.depart is not null " - //+ " and (rr.depart = '手术一区' or rr.depart = '手术二区' or rr.depart = '手术三区') " - + departSql - + " and wr.id is not null " - + " and ritem.tousseDefinitionId in (select cit.tousseDefinitionID from ClassifyBasket clb,ClassifiedItem cit where clb.id=cit.classifybasket_id and cb.id=clb.id) " - + " and (rr.recyclingTime between "+dateQueryAdapter.dateAdapter(startDay)+" and " + dateQueryAdapter.dateAdapter(endDay) + ")"; + ReportQueryParams params = new ReportQueryParams(); + String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDay),dateQueryAdapter.dateAdapter(endDay)); + params.betweenSql = betweenSql; + params.querySupplyRoom = querySupplyRoom; + params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; + params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = departSql; + params.tousseGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + params.tousseTypeAndPackageSizeSql = ""; + //数据库计算字符串长度的函数名 + params.monthlyStr = dateQueryAdapter.dateConverAdapter3("wr.endDate","mm"); + // 处理器械总件数 + totalSql = String.format("select sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl "); + setWorkQualityCollectionData(bean6,key,sql,totalSql); } bean6.setTitle("手术器械清洗不合格率"); @@ -458,10 +464,10 @@ // 无菌物品发放不及时次数 无菌物品发放不及时 WorkQualityCollection bean17 = new WorkQualityCollection(); notInDepartSql = ""; - if(StringUtils.isNotBlank(operatingRoomCodes)){ - String[] departArr = operatingRoomCodes.split(","); + if(StringUtils.isNotBlank(operatingRoomNames)){ + String[] departArr = operatingRoomNames.split(","); for (int i = 0; i < departArr.length; i++) { - notInDepartSql += " and org.orgUnitCoding != '" + departArr[i] + "' "; + notInDepartSql += " and org.name != '" + departArr[i] + "' "; } } for (String key : startAndEndDays.keySet()){ @@ -572,48 +578,29 @@ + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "应急处理器械次数"; addWorkQualityCollectionBean(list,titleName,sql,recAmountMap); - String columnOneOrThree = null; - String joinOne = null; - //String columnTwo = null; - //String joinTwo = null; - String joinThree = null; - if (dataSoureOfMaterialsCountOfToussesInReports == 3) { - columnOneOrThree = "ritem.amount*ritem.materialAmount"; - joinOne = " join TousseDefinition td on td.id=ritem.tousseDefinitionId and td.forDisplay=1 and( td.tousseGroupName is null or td.tousseGroupName != '专科')"; - //columnTwo = "(cit.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end)*cit.materialAmount"; - //joinTwo = " join TousseDefinition td on td.id=cit.tousseDefinitionID and td.forDisplay=1 and td.tousseGroupName = '专科' "; - joinThree = ""; - } else { - columnOneOrThree = "ritem.amount*tdc.amount"; - joinOne = " join (select td.id tid, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td where mi.tousse_id=td.id and td.forDisplay=1 and( td.tousseGroupName is null or td.tousseGroupName != '专科') group by td.id) tdc on tdc.tid=ritem.tousseDefinitionId "; - //columnTwo = "(cit.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end)*tdc.amount"; - //joinTwo = " join (select td.id tid, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td where mi.tousse_id=td.id and td.forDisplay=1 and td.tousseGroupName = '专科' group by td.id) tdc on tdc.tid=cit.tousseDefinitionID "; - joinThree = " join (select td.id tid, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td where mi.tousse_id=td.id and td.forDisplay=1 group by td.id) tdc on tdc.tid=ritem.tousseDefinitionId "; - } - String operatingRoomCodes = getOperatingRoomCodes(); + String operatingRoomNames = orgUnitGroupManager.getOperatingRoomNames(); String notInDepartSql = ""; - if(StringUtils.isNotBlank(operatingRoomCodes)){ - String[] departArr = operatingRoomCodes.split(","); + if(StringUtils.isNotBlank(operatingRoomNames)){ + String[] departArr = operatingRoomNames.split(","); for (int i = 0; i < departArr.length; i++) { - notInDepartSql += " and rr.departCode != '" + departArr[i] + "' "; + notInDepartSql += " and ci.orgUnitName != '" + departArr[i] + "' "; } } + ReportQueryParams params = new ReportQueryParams(objectDao); + String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(queryYear),dateQueryAdapter.dateAdapter(nextYear)); + params.betweenSql = betweenSql; + params.querySupplyRoom = querySupplyRoom; + params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; + params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = " and (tdc.tousseGroupName is null or tdc.tousseGroupName<>'专科') " + notInDepartSql; + params.tousseGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + params.tousseTypeAndPackageSizeSql = ""; + params.monthlyStr = dateQueryAdapter.dateConverAdapter3("wr.endDate","mm"); // 普通器械清洗不合格率 // 总数量 按照回收时科室不等于“手术一区、手术二区、手术三区”的器械包材料清洗总件数。并且器械包定义的器械包分组不等于“专科” - String totalSqlOne = "select " +dateQueryAdapter.dateConverAdapter3("rr.recyclingTime","mm") - + " month,sum("+ columnOneOrThree +") from RecyclingRecord rr join RecyclingItem ritem on ritem.recyclingRecord_id = rr.id " - + joinOne - + " join ClassifyBasket_RecyclingRecord cr on rr.id=cr.RecyclingRecord_ID " - + " join ClassifyBasket cb on cr.ClassifyBasket_ID=cb.id join ClassifyBasket_WashRecord cw on cb.id=cw.ClassifyBasket_ID " - + " join WashAndDisinfectRecord wr on cw.WashAndDisinfectRecord_ID=wr.id " - + " where rr.depart is not null " - //+ " and rr.depart != '手术一区' and rr.depart != '手术二区' and rr.depart != '手术三区' " - + notInDepartSql - + " and wr.id is not null " - + " and ritem.tousseDefinitionId in (select cit.tousseDefinitionID from ClassifyBasket clb,ClassifiedItem cit where clb.id=cit.classifybasket_id and cb.id=clb.id) " - + " and rr.orgUnitCoding = '"+querySupplyRoom+"'" - + " and (rr.recyclingTime between "+dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" - + " group by " + dateQueryAdapter.dateConverAdapter3("rr.recyclingTime","mm"); + // 处理器械总件数 + String totalSqlOne = String.format("select monthstr,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by monthstr "); Map totalmap = getMapBySql(totalSqlOne); sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf ,QualityMonitoringGoods qmd" @@ -626,18 +613,8 @@ addWorkQualityCollectionBean(list,titleName,sql,totalmap); // 专科器械清洗不合格率 // 总数量 按照器械包定义的器械包分组等于“专科” 的器械材料清洗总件数 - ReportQueryParams params = new ReportQueryParams(objectDao); - String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(queryYear),dateQueryAdapter.dateAdapter(nextYear)); - params.betweenSql = betweenSql; - params.querySupplyRoom = querySupplyRoom; - params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; - params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = " and tdc.tousseGroupName='专科' "; - params.tousseGroupSqlWithAliasOfTousseDefinitionIsTd = ""; - params.tousseTypeAndPackageSizeSql = ""; - //数据库计算字符串长度的函数名 - String sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); - //params.sqlLengthFunctionName = sqlLengthFunctionName; - params.monthlyStr = dateQueryAdapter.dateConverAdapter3("wr.endDate","mm"); + params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = " and tdc.tousseGroupName='专科' " + notInDepartSql; + // 处理器械总件数 String totalSqlTwo = String.format("select monthstr,sum(tl.amount) amount from (" +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) @@ -657,30 +634,23 @@ String departSql = ""; - if(StringUtils.isNotBlank(operatingRoomCodes)){ - String[] departArr = operatingRoomCodes.split(","); + if(StringUtils.isNotBlank(operatingRoomNames)){ + String[] departArr = operatingRoomNames.split(","); departSql += " and ("; for (int i = 0; i < departArr.length; i++) { if(i == 0){ - departSql += " rr.departCode='" + departArr[i] + "' "; + departSql += " ci.orgUnitName='" + departArr[i] + "' "; }else{ - departSql += " or rr.departCode='" + departArr[i] + "' "; + departSql += " or ci.orgUnitName='" + departArr[i] + "' "; } } departSql += ") "; } - String totalSqlThree = "select " +dateQueryAdapter.dateConverAdapter3("rr.recyclingTime","mm") - + " month,sum("+ columnOneOrThree +") from RecyclingRecord rr join RecyclingItem ritem on ritem.recyclingRecord_id = rr.id " - + joinThree - + " join ClassifyBasket_RecyclingRecord cr on rr.id=cr.RecyclingRecord_ID join ClassifyBasket cb on cr.ClassifyBasket_ID=cb.id join ClassifyBasket_WashRecord cw on cb.id=cw.ClassifyBasket_ID " - + " join WashAndDisinfectRecord wr on cw.WashAndDisinfectRecord_ID=wr.id " - + " where rr.depart is not null " - //+ " and (rr.depart = '手术一区' or rr.depart = '手术二区' or rr.depart = '手术三区') " - + departSql - + " and wr.id is not null " - + " and ritem.tousseDefinitionId in (select cit.tousseDefinitionID from ClassifyBasket clb,ClassifiedItem cit where clb.id=cit.classifybasket_id and cb.id=clb.id) " - + " and (rr.recyclingTime between "+dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" - + " group by " + dateQueryAdapter.dateConverAdapter3("rr.recyclingTime","mm"); + params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = departSql; + // 处理器械总件数 + String totalSqlThree = String.format("select monthstr,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by monthstr "); totalmap = getMapBySql(totalSqlThree); sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf ,QualityMonitoringGoods qmd" @@ -793,10 +763,10 @@ addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); // 无菌物品发放不及时次数 notInDepartSql = ""; - if(StringUtils.isNotBlank(operatingRoomCodes)){ - String[] departArr = operatingRoomCodes.split(","); + if(StringUtils.isNotBlank(operatingRoomNames)){ + String[] departArr = operatingRoomNames.split(","); for (int i = 0; i < departArr.length; i++) { - notInDepartSql += " and org.orgUnitCoding != '" + departArr[i] + "' "; + notInDepartSql += " and org.name != '" + departArr[i] + "' "; } } String totalSql = "select " +dateQueryAdapter.dateConverAdapter3("iv.sendTime","mm")