Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r24728 -r24732 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 24728) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 24732) @@ -12257,7 +12257,8 @@ String startDateSql = dateQueryAdapter.dateConverAdapter2(startTime,dateFormatter); String endDateSql = dateQueryAdapter.dateConverAdapter2(endTime,dateFormatter); - String tousseTypeSql = ""; + String tousseTypeSqlWithAliasOfTousseDefinitionIsTd = ""; + String tousseTypeSqlWithAliasOfTousseDefinitionIsTi = ""; boolean includeForeignTousse = false; //需要查询的器械包类型是否包含“外来器械包” if (StringTools.isNotBlank(tousseTypes) && !tousseTypes.contains("全部")) { String[] tousseTypeArr = tousseTypes.split(","); @@ -12269,7 +12270,8 @@ } tousseTypeList.add(temp); } - tousseTypeSql = " and " + SqlUtils.getStringFieldInLargeCollectionsPredicate("ti.tousseType", tousseTypeList); + tousseTypeSqlWithAliasOfTousseDefinitionIsTd = " and " + SqlUtils.getStringFieldInLargeCollectionsPredicate("td.tousseType", tousseTypeList); + tousseTypeSqlWithAliasOfTousseDefinitionIsTi = " and " + SqlUtils.getStringFieldInLargeCollectionsPredicate("ti.tousseType", tousseTypeList); } else { includeForeignTousse = true; } @@ -12282,37 +12284,45 @@ SupplyRoomConfig systemParamsObj = supplyRoomConfigManager.getSystemParamsObj(); - //是否禁用标识牌的过滤 - String isDisableIDCardSql = ""; - String isDisableIDCardSql2 = ""; + //是否禁用标识牌的过滤(分别用于器械包定义别名为td及ti的过滤条件) + String isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; + String isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi = ""; + //假条件 + String isDisableIDCardFalseSqlCondition = ""; if (StringTools.isNotBlank(isDisableIDCard)) { if (StringTools.equals(isDisableIDCard, Constants.STR_NO)) { - isDisableIDCardSql = String.format(" and td.isDisableIDCard='%s' ", Constants.STR_NO); - isDisableIDCardSql2 = " and 1=2 "; + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = String.format(" and td.isDisableIDCard='%s' ", Constants.STR_NO); + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi = String.format(" and ti.isDisableIDCard='%s' ", Constants.STR_NO); + isDisableIDCardFalseSqlCondition = " and 1=2 "; } else { - isDisableIDCardSql = String.format(" and (td.isDisableIDCard='%s' or td.isDisableIDCard='' or td.isDisableIDCard is null) ", Constants.STR_YES); + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = String.format(" and (td.isDisableIDCard='%s' or td.isDisableIDCard='' or td.isDisableIDCard is null) ", Constants.STR_YES); + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi = String.format(" and (ti.isDisableIDCard='%s' or ti.isDisableIDCard='' or ti.isDisableIDCard is null) ", Constants.STR_YES); } } //任务组的过滤 - String taskGroupSql = ""; - String taskGroupSql2 = ""; + String taskGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + String taskGroupSqlWithAliasOfTousseDefinitionIsTi = ""; + String taskGroupFalseSqlCondition = ""; if (StringTools.isNotBlank(taskGroup)) { - taskGroupSql = String.format(" and td.taskGroup='%s' ", taskGroup); + taskGroupSqlWithAliasOfTousseDefinitionIsTd = String.format(" and td.taskGroup='%s' ", taskGroup); + taskGroupSqlWithAliasOfTousseDefinitionIsTi = String.format(" and ti.taskGroup='%s' ", taskGroup); SupplyRoomConfig supplyRoomConfig = supplyRoomConfigManager.getSystemParamsObj(); //外来器械包默认处理科室及任务组 JSONObject foreignTousseHandleDepartAndTaskGroupJsonobject = supplyRoomConfig.getOneForeignTousseHandleDepartAndTaskGroupByDepartCode(AcegiHelper.getCurrentOrgUnitCode()); if (foreignTousseHandleDepartAndTaskGroupJsonobject == null || !StringTools.equals(taskGroup, foreignTousseHandleDepartAndTaskGroupJsonobject.optString("taskGroup"))) { - taskGroupSql2 = " and 1=2 "; + taskGroupFalseSqlCondition = " and 1=2 "; } } //器械包分组的过滤 - String tousseGroupSql = ""; - String tousseGroupSql2 = ""; + String tousseGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + String tousseGroupSqlWithAliasOfTousseDefinitionIsTi = ""; + String tousseGroupFalseSqlCondition = ""; if (StringTools.isNotBlank(tousseGroup)) { - tousseGroupSql = String.format(" and td.tousseGroupName='%s' ", tousseGroup); - tousseGroupSql2 = " and 1=2 "; + tousseGroupSqlWithAliasOfTousseDefinitionIsTd = String.format(" and td.tousseGroupName='%s' ", tousseGroup); + tousseGroupSqlWithAliasOfTousseDefinitionIsTi = String.format(" and ti.tousseGroupName='%s' ", tousseGroup); + tousseGroupFalseSqlCondition = " and 1=2 "; } @@ -12321,24 +12331,24 @@ +" where ti.recyclingApplication_ID=ip.id and td.id=ti.tousseDefinitionId %s %s %s " + "and ip.handleDepartCoding = '%s' and ip.applicationTime between %s and %s and ti.tousseType<>'一次性物品' %s " + "%s group by ti.tousseType,ti.tousseName ", - isDisableIDCardSql,taskGroupSql,tousseGroupSql, - departCoding,startDateSql,endDateSql,tousseTypeSql,getAndSql("ip.applicant", operator)); + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd,taskGroupSqlWithAliasOfTousseDefinitionIsTd,tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + departCoding,startDateSql,endDateSql,tousseTypeSqlWithAliasOfTousseDefinitionIsTd,getAndSql("ip.applicant", operator)); //回收数量（按包统计） sql += " union all "; sql += String.format("select 'recyc' as type,ti.tousseName,sum(ti.amount),ti.tousseType from recyclingRecord rr,RecyclingItem ti,TousseDefinition td " +" where rr.id = ti.recyclingRecord_id and td.id=ti.tousseDefinitionId %s %s %s and rr.orgUnitCoding = '%s' and rr.recyclingTime between %s and %s " + "%s %s group by ti.tousseType,ti.tousseName ", - isDisableIDCardSql,taskGroupSql,tousseGroupSql, - departCoding,startDateSql,endDateSql,getAndSql("rr.recyclingUser", operator),tousseTypeSql); + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd,taskGroupSqlWithAliasOfTousseDefinitionIsTd,tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + departCoding,startDateSql,endDateSql,getAndSql("rr.recyclingUser", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTd); //清点数量（按包统计） sql += " union all "; sql += String.format("select 'inventory' as type,ti.tousseName,sum(ti.amount),ti.tousseType from recyclingRecord rr,RecyclingItem ti,TousseDefinition td " +" where rr.id = ti.recyclingRecord_id and td.id=ti.tousseDefinitionId %s %s %s and rr.orgUnitCoding = '%s' and rr.recyclingTime between %s and %s " + "%s %s group by ti.tousseType,ti.tousseName ", - isDisableIDCardSql,taskGroupSql,tousseGroupSql, - departCoding,startDateSql,endDateSql,getAndSql("rr.operator", operator),tousseTypeSql); + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd,taskGroupSqlWithAliasOfTousseDefinitionIsTd,tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + departCoding,startDateSql,endDateSql,getAndSql("rr.operator", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTd); //清洗数量（按包统计） sql += " union all "; @@ -12357,9 +12367,9 @@ + "where wdr.id = cw.WashAndDisinfectRecord_ID and cw.ClassifyBasket_ID = cb.id and cb.id = ci.classifybasket_id and ci.tousseDefinitionID=td.id and ci.itemType = '材料' " + "%s %s %s and wdr.orgUnitCoding = '%s' and wdr.endDate between %s and %s and wdr.washMaterialAmount<>0 group by ci.tousseDefinitionID,ci.recyclingRecordId" + ") ti where 1=1 %s %s group by ti.tousseType,ti.tousseName ", - isDisableIDCardSql,taskGroupSql,tousseGroupSql,departCoding,startDateSql,endDateSql, - isDisableIDCardSql,taskGroupSql,tousseGroupSql,departCoding,startDateSql,endDateSql, - getAndSql("ti.userName", operator),tousseTypeSql); + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd,taskGroupSqlWithAliasOfTousseDefinitionIsTd,tousseGroupSqlWithAliasOfTousseDefinitionIsTd,departCoding,startDateSql,endDateSql, + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd,taskGroupSqlWithAliasOfTousseDefinitionIsTd,tousseGroupSqlWithAliasOfTousseDefinitionIsTd,departCoding,startDateSql,endDateSql, + getAndSql("ti.userName", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTd); //清洗数量（按材料统计） sql += " union all "; @@ -12377,68 +12387,71 @@ + "where wdr.id = cw.WashAndDisinfectRecord_ID and cw.ClassifyBasket_ID = cb.id and cb.id = ci.classifybasket_id and ci.tousseDefinitionID=td.id and ci.itemType = '"+ TousseDefinition.PACKAGE_TYPE_INSIDE +"' " + " %s %s %s and wdr.orgUnitCoding = '%s' and wdr.endDate between %s and %s and wdr.washMaterialAmount<>0 " + ") ti where 1=1 %s %s group by ti.tousseType,ti.tousseName ", - isDisableIDCardSql,taskGroupSql,tousseGroupSql,departCoding,startDateSql,endDateSql, - isDisableIDCardSql,taskGroupSql,tousseGroupSql,departCoding,startDateSql,endDateSql, - getAndSql("ti.userName", operator),tousseTypeSql); + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd,taskGroupSqlWithAliasOfTousseDefinitionIsTd,tousseGroupSqlWithAliasOfTousseDefinitionIsTd,departCoding,startDateSql,endDateSql, + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd,taskGroupSqlWithAliasOfTousseDefinitionIsTd,tousseGroupSqlWithAliasOfTousseDefinitionIsTd,departCoding,startDateSql,endDateSql, + getAndSql("ti.userName", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTd); //装配数量（按包统计） sql += " union all "; sql += String.format("select 'packing' as type,ti.name tousseName,sum(pr.amount),ti.tousseType from PackingRecord pr,TousseDefinition ti " +" where pr.tousseDefinitionId=ti.id %s %s %s and pr.orgUnitCoding = '%s' and pr.packTime between %s and %s " + "%s %s group by ti.tousseType,ti.name ", - isDisableIDCardSql,String.format(" and ti.taskGroup='%s' ", taskGroup),tousseGroupSql, - departCoding,startDateSql,endDateSql,getAndSql("pr.packer", operator),tousseTypeSql); + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi,taskGroupSqlWithAliasOfTousseDefinitionIsTi,tousseGroupSqlWithAliasOfTousseDefinitionIsTi, + departCoding,startDateSql,endDateSql,getAndSql("pr.packer", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTi); //审核数量（按包统计） sql += " union all "; sql += String.format("select 'review' as type,t.tousseName,count(*),ti.tousseType from TousseInstance t,TousseDefinition ti " +" where t.tousseDefinition_id=ti.id %s %s %s and t.orgUnitCoding = '%s' and t.reviewTime between %s and %s and t.foreignTousseApp_id is null " + "and (ti.statisticsReviewWorkload='%s' or ti.statisticsReviewWorkload is null or ti.statisticsReviewWorkload='') and ti.isReview='%s' " + "%s %s group by ti.tousseType,t.tousseName ", - isDisableIDCardSql,String.format(" and ti.taskGroup='%s' ", taskGroup),tousseGroupSql, - departCoding,startDateSql,endDateSql,Constants.STR_YES,Constants.STR_YES,getAndSql("t.reviewer", operator),tousseTypeSql); + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi,taskGroupSqlWithAliasOfTousseDefinitionIsTi,tousseGroupSqlWithAliasOfTousseDefinitionIsTi, + departCoding,startDateSql,endDateSql,Constants.STR_YES,Constants.STR_YES,getAndSql("t.reviewer", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTi); if (includeForeignTousse) { sql += " union all "; sql += String.format("select 'review' as type,fta.tousseName,count(*),'外来器械包' from TousseInstance t,ForeignTousseApplication fta " +" where t.foreignTousseApp_id=fta.id %s %s %s and t.orgUnitCoding = '%s' and t.reviewTime between %s and %s " + "%s group by fta.tousseName ", - isDisableIDCardSql2,taskGroupSql2,tousseGroupSql2, + isDisableIDCardFalseSqlCondition,taskGroupFalseSqlCondition,tousseGroupFalseSqlCondition, departCoding,startDateSql,endDateSql,getAndSql("t.reviewer", operator)); } //灭菌数量（按包统计） sql += " union all "; - sql += String.format("select 'sterilization' as type,tis.tousseName,count(*),ti.tousseType from SterilizationRecord sr,sterilization_tousseInstance st,TousseInstance tis,TousseDefinition ti " + sql += String.format("select 'sterilization' as type,tis.tousseName,count(*),ti.tousseType from SterilizationRecord sr," + + "sterilization_tousseInstance st,TousseInstance tis,TousseDefinition ti " +" where sr.id = st.sterilizationRecord_id and st.tousseInstance_id = tis.id and tis.tousseDefinition_id=ti.id " +" %s %s %s and sr.orgUnitCoding = '%s' and sr.endDate between %s and %s and tis.foreignTousseApp_id is null " + "%s %s group by ti.tousseType,tis.tousseName ", - isDisableIDCardSql,String.format(" and ti.taskGroup='%s' ", taskGroup),tousseGroupSql, - departCoding,startDateSql,endDateSql,getAndSql("sr.sterilizationUser", operator),tousseTypeSql); + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi,taskGroupSqlWithAliasOfTousseDefinitionIsTi,tousseGroupSqlWithAliasOfTousseDefinitionIsTi, + departCoding,startDateSql,endDateSql,getAndSql("sr.sterilizationUser", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTi); if (includeForeignTousse) { sql += " union all "; - sql += String.format("select 'sterilization' as type,fta.tousseName,count(*),'外来器械包' from SterilizationRecord sr,sterilization_tousseInstance st,TousseInstance ti,ForeignTousseApplication fta " + sql += String.format("select 'sterilization' as type,fta.tousseName,count(*),'外来器械包' from SterilizationRecord sr," + + "sterilization_tousseInstance st,TousseInstance ti,ForeignTousseApplication fta " +" where sr.id = st.sterilizationRecord_id and st.tousseInstance_id = ti.id and ti.foreignTousseApp_id=fta.id " +" %s %s %s and sr.orgUnitCoding = '%s' and sr.endDate between %s and %s " + "%s group by fta.tousseName ", - isDisableIDCardSql2,taskGroupSql2,tousseGroupSql2, + isDisableIDCardFalseSqlCondition,taskGroupFalseSqlCondition,tousseGroupFalseSqlCondition, departCoding,startDateSql,endDateSql,getAndSql("sr.sterilizationUser", operator)); } sql += " union all "; - sql += String.format("select 'sterilization' as type,tis.tousseName,count(*),ti.tousseType from SterilizationRecord sr,sterilization_reviewed sre,ReviewedBasket rb,TousseInstance tis,TousseDefinition ti " + sql += String.format("select 'sterilization' as type,tis.tousseName,count(*),ti.tousseType from SterilizationRecord sr," + + "sterilization_reviewed sre,ReviewedBasket rb,TousseInstance tis,TousseDefinition ti " +" where sr.id = sre.sterilizationRecord_id and sre.reviewedBasket_id = rb.id and rb.id = tis.reviewBasket_ID and tis.tousseDefinition_id=ti.id " +" %s %s %s and sr.orgUnitCoding = '%s' and sr.endDate between %s and %s and tis.foreignTousseApp_id is null " + "%s %s group by ti.tousseType,tis.tousseName", - isDisableIDCardSql,String.format(" and ti.taskGroup='%s' ", taskGroup),tousseGroupSql, - departCoding,startDateSql,endDateSql,getAndSql("sr.sterilizationUser", operator),tousseTypeSql); + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi,taskGroupSqlWithAliasOfTousseDefinitionIsTi,tousseGroupSqlWithAliasOfTousseDefinitionIsTi, + departCoding,startDateSql,endDateSql,getAndSql("sr.sterilizationUser", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTi); if (includeForeignTousse) { sql += " union all "; sql += String.format("select 'sterilization' as type,fta.tousseName,count(*),'外来器械包' from SterilizationRecord sr,sterilization_reviewed sre,ReviewedBasket rb,TousseInstance ti,ForeignTousseApplication fta " +" where sr.id = sre.sterilizationRecord_id and sre.reviewedBasket_id = rb.id and rb.id = ti.reviewBasket_ID and ti.foreignTousseApp_id=fta.id " +" %s %s %s and sr.orgUnitCoding = '%s' and sr.endDate between %s and %s " + "%s group by fta.tousseName ", - isDisableIDCardSql2,taskGroupSql2,tousseGroupSql2, + isDisableIDCardFalseSqlCondition,taskGroupFalseSqlCondition,tousseGroupFalseSqlCondition, departCoding,startDateSql,endDateSql,getAndSql("sr.sterilizationUser", operator)); } @@ -12449,15 +12462,15 @@ + "where 1=1 %s %s %s and i.orgUnitCoding = '%s' and ti.diposable='否' and i.sendTime between %s and %s " + "and not exists(select 1 from ForeignTousseApplication fta where fta.id=i.invoicePlan_ID) " + "%s %s group by ti.tousseType,ti.tousseName ", - isDisableIDCardSql,taskGroupSql,tousseGroupSql, - departCoding,startDateSql,endDateSql,getAndSql("i.sender", operator),tousseTypeSql); + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd,taskGroupSqlWithAliasOfTousseDefinitionIsTd,tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + departCoding,startDateSql,endDateSql,getAndSql("i.sender", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTd); if (includeForeignTousse) { sql += " union all "; sql += String.format("select 'invoice' type,fta.tousseName,sum(ii.amount) amount,'外来器械包' tousseType from invoice i join InvoiceItem ii on ii.invoice_id=i.id " + "join ForeignTousseApplication fta on fta.id=i.invoicePlan_ID " +" where 1=1 %s %s %s and i.orgUnitCoding = '%s' and i.sendTime between %s and %s " + "%s group by fta.tousseName", - isDisableIDCardSql2,taskGroupSql2,tousseGroupSql2, + isDisableIDCardFalseSqlCondition,taskGroupFalseSqlCondition,tousseGroupFalseSqlCondition, departCoding,startDateSql,endDateSql,getAndSql("i.sender", operator)); } @@ -12466,14 +12479,14 @@ sql += String.format("select 'signed' as type,tis.tousseName,count(*),ti.tousseType from TousseInstance tis,TousseDefinition ti " +" where tis.tousseDefinition_id=ti.id %s %s %s and tis.orgUnitCoding = '%s' and %s between %s and %s and tis.foreignTousseApp_id is null " + "%s %s group by ti.tousseType,tis.tousseName ", - isDisableIDCardSql,String.format(" and ti.taskGroup='%s' ", taskGroup),tousseGroupSql, - departCoding,signedTimeString,startDateSql,endDateSql,getAndSql("tis.signedUser", operator),tousseTypeSql); + isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi,taskGroupSqlWithAliasOfTousseDefinitionIsTi,tousseGroupSqlWithAliasOfTousseDefinitionIsTi, + departCoding,signedTimeString,startDateSql,endDateSql,getAndSql("tis.signedUser", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTi); if (includeForeignTousse) { sql += " union all "; sql += String.format("select 'signed' as type,fta.tousseName,count(*),'外来器械包' from TousseInstance tis,ForeignTousseApplication fta " +" where tis.foreignTousseApp_id=fta.id %s %s %s " +" and tis.orgUnitCoding = '%s' and %s between %s and %s %s group by fta.tousseName ", - isDisableIDCardSql2,taskGroupSql2,tousseGroupSql2, + isDisableIDCardFalseSqlCondition,taskGroupFalseSqlCondition,tousseGroupFalseSqlCondition, departCoding,signedTimeString,startDateSql,endDateSql,getAndSql("tis.signedUser", operator)); }