Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java =================================================================== diff -u -r33611 -r33770 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 33611) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 33770) @@ -355,7 +355,7 @@ .format("select sum(%s) amount %s %s %s " + "from RecyclingRecord rr inner join RecyclingItem ri on ri.recyclingRecord_id = rr.id inner join TousseDefinition td on ri.tousseDefinitionId = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + " %s where rr.recyclingTime %s %s %s and %s %s %s " - + "and rr.operator is not null and %s(rr.operator)<>0 %s %s %s %s %s", + + "and rr.operator is not null and %s(rr.operator)<>0 %s %s %s %s %s %s ", amountColumnSql, obj.selectUserName?",rr.operator userName":"", obj.selectTousseType?",td.tousseType":"", @@ -372,6 +372,7 @@ obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseTypeAndPackageSizeSql, + obj.extraQuery, groupBySql); break; @@ -487,7 +488,8 @@ .isBlank(obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd) && StringUtils.isBlank(obj.sterilizationModeSqlWithAliasOfTousseDefinitionIsTd) && StringUtils.isBlank(obj.packageTypeSqlWithAliasOfTousseDefinitionIsTd) - && !obj.extraQuery.contains("ci.")) { + && !obj.extraQuery.contains("ci.") + && !obj.extraQuery.contains("td.")) { amountSql += " union all "; amountSql += String.format("select %s %s %s %s %s %s " + getWashMaterialAmountSqlFromTousseTypeIsMaterial(obj.extraJoinCondition) @@ -656,7 +658,7 @@ .format("select sum(%s) amount %s %s %s " + "%s from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + " %s where %s and ti.operationTime %s %s %s and %s %s %s " - + "%s %s %s %s %s", + + "%s %s %s %s %s %s", amountColumnSql, obj.selectUserName?",case when (ti.operator is null) then '系统用户' else ti.operator end userName":"", obj.selectTousseType ? ",td.tousseType" : "", @@ -674,6 +676,7 @@ obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseTypeAndPackageSizeSql, + obj.extraQuery, groupBySql); } if(obj.getIsQueryCom()){ @@ -687,7 +690,7 @@ .format("select sum(%s) amount %s %s %s " + "%s from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + " %s where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseDefinitionId is not null and ti.comboTousseInstanceId is not null and ti.operationTime %s %s %s and %s %s %s " - + "%s %s %s %s", + + "%s %s %s %s %s ", amountColumnSql, obj.selectUserName?",case when (ti.operator is null) then '系统用户' else ti.operator end userName":"", obj.selectTousseType ? ",'"+TousseDefinition.PACKAGE_TYPE_COMBO + "' tousseType" : "", @@ -703,6 +706,7 @@ obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("tdc", "ti"), obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.extraQuery, groupBySql); } break; @@ -995,7 +999,7 @@ .format("select sum(%s) amount %s " + "from Invoice i inner join InvoiceItem ii on ii.invoice_id=i.id inner join TousseDefinition td on ii.tousseDefinitionId = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + " %s where i.sendTime %s %s %s and %s %s %s " - + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s %s", + + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s %s %s ", miCountSql, obj.extraSelectColumns, joinMaterialInstanceSql, @@ -1010,6 +1014,7 @@ obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseTypeAndPackageSizeSql, + obj.extraQuery, obj.groupBySql); String queryDisposableGoodAmountSql = ""; if(obj.includeDisposableGoodsInTousse){ @@ -1018,7 +1023,7 @@ .format(" union all select sum(%s) amount %s " + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + " %s where %s and i.sendTime %s %s %s and %s %s %s " - + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s", + + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s %s ", queryDisposableGoodAmountSql, obj.extraSelectColumns, joinMaterialInstanceSql, @@ -1033,6 +1038,7 @@ obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("tdc", "ti"), obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.extraQuery, obj.groupBySql); } } @@ -1055,7 +1061,7 @@ .format("select sum(%s) amount %s " + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + " %s where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is not null and i.sendTime %s %s %s and %s %s %s " - + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s", + + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s %s", miCountSql, obj.extraSelectColumns, joinMaterialInstanceSql, @@ -1069,6 +1075,7 @@ obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("tdc", "ti"), obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.extraQuery, obj.groupBySql); } @@ -1303,7 +1310,7 @@ .format("select sum(item.amount) amount %s %s " + "from InvoicePlan ip inner join TousseItem item on item.recyclingApplication_ID=ip.id inner join TousseDefinition td on td.id=item.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID " + "where ip.applicationTime %s %s %s and %s %s %s " - + "and item.tousseType<>'一次性物品' %s %s %s %s " + + "and item.tousseType<>'一次性物品' %s %s %s %s %s " + "group by td.tousseType,td.name ", obj.selectTousseType ? ",td.tousseType" : "", obj.selectTousseName ? ",td.name tousseName " : "", @@ -1316,7 +1323,8 @@ obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseTypeAndPackageSizeSql); + obj.tousseTypeAndPackageSizeSql, + obj.extraQuery); break; @@ -1339,7 +1347,7 @@ .format("select sum(ri.amount) amount %s %s %s %s" + "from RecyclingRecord rr inner join RecyclingItem ri on ri.recyclingRecord_id = rr.id inner join TousseDefinition td on ri.tousseDefinitionId=td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + "where rr.recyclingTime %s %s %s and %s %s %s " - + " %s %s %s %s %s ", + + " %s %s %s %s %s %s", obj.selectUserName ? ",rr.recyclingUser userName" : "", obj.selectTousseType ? ",td.tousseType" : "", obj.selectTousseName ? ",td.name tousseName " : "", @@ -1354,6 +1362,7 @@ obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseTypeAndPackageSizeSql, + obj.extraQuery, groupBySql); break; case "预回收数量": @@ -1392,7 +1401,7 @@ + "from RecyclingRecord rr inner join RecyclingItem ri on ri.recyclingRecord_id = rr.id " + "inner join TousseDefinition td on ri.tousseDefinitionId=td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " + "where rr.recyclingTime %s %s %s and %s %s %s " - + "and rr.operator is not null and %s(rr.operator)<>0 %s %s %s %s %s", + + "and rr.operator is not null and %s(rr.operator)<>0 %s %s %s %s %s %s", obj.selectUserName?",rr.operator userName":"", obj.selectTousseType ? ",td.tousseType" : "", obj.selectTousseName ? ",td.name tousseName" : "", @@ -1409,6 +1418,7 @@ obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseTypeAndPackageSizeSql, + obj.extraQuery, groupBySql); break; @@ -1418,7 +1428,7 @@ + "from WashAndDisinfectRecord wdr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td,TousseDefinition tdc " + "where cw.WashAndDisinfectRecord_ID = wdr.id and cb.id = cw.ClassifyBasket_ID and ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID and tdc.id=td.ancestorID " + "and ci.itemType = '材料' " - + "and wdr.endDate %s %s %s and %s %s %s and wdr.washMaterialAmount <> 0 %s %s %s %s " + + "and wdr.endDate %s %s %s and %s %s %s and wdr.washMaterialAmount <> 0 %s %s %s %s %s " + "group by ci.tousseDefinitionID,ci.recyclingRecordId", obj.selectUserName?",case when (min(cb.personInCharge) is null or min(cb.personInCharge) = '') then min(wdr.operator) else min(cb.personInCharge) end userName":"", obj.selectTousseType ? ",min(td.tousseType) tousseType" : "", @@ -1433,13 +1443,14 @@ obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseTypeAndPackageSizeSql); + obj.tousseTypeAndPackageSizeSql, + obj.extraQuery); amountSql += " union all "; amountSql += String.format("select (ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) amount %s %s %s " + "from WashAndDisinfectRecord wdr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td,TousseDefinition tdc " + "where cw.WashAndDisinfectRecord_ID = wdr.id and cb.id = cw.ClassifyBasket_ID and ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID and tdc.id=td.ancestorID " - + "and wdr.endDate %s %s %s and %s %s %s and ci.itemtype != '材料' and wdr.washMaterialAmount <> 0 %s %s %s %s ", + + "and wdr.endDate %s %s %s and %s %s %s and ci.itemtype != '材料' and wdr.washMaterialAmount <> 0 %s %s %s %s %s ", obj.selectUserName?",case when (cb.personInCharge is null or cb.personInCharge = '') then wdr.operator else cb.personInCharge end userName":"", obj.selectTousseType ? ",ci.itemType tousseType" : "", obj.selectTousseName ? ",td.name tousseName" : "", @@ -1452,7 +1463,8 @@ obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseTypeAndPackageSizeSql); + obj.tousseTypeAndPackageSizeSql, + obj.extraQuery); break; @@ -1590,7 +1602,7 @@ .format("select count(*) amount %s %s %s " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + "where %s and ti.reviewTime %s %s %s and %s %s %s " - + "and ti.reviewer is not null and %s(ti.reviewer)<>0 %s %s %s %s " + + "and ti.reviewer is not null and %s(ti.reviewer)<>0 %s %s %s %s %s " + " %s", obj.selectUserName ? ",ti.reviewer userName" : "", obj.selectTousseType ? ",td.tousseType" : "", @@ -1607,6 +1619,7 @@ obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseTypeAndPackageSizeSql, + obj.extraQuery, groupBySql); } if(obj.getIsQueryCom()){ @@ -1617,7 +1630,7 @@ .format("select count(*) amount %s %s %s " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null and ti.reviewTime %s %s %s and %s %s %s " - + "and ti.reviewer is not null and %s(ti.reviewer)<>0 %s %s %s " + + "and ti.reviewer is not null and %s(ti.reviewer)<>0 %s %s %s %s " + " %s ", obj.selectUserName ? ",ti.reviewer userName" : "", obj.selectTousseType ? ",td.tousseType" : "", @@ -1632,6 +1645,7 @@ obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("tdc", "ti"), obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.extraQuery, groupBySql); } break; @@ -1739,7 +1753,7 @@ + "inner join TousseDefinition td on td.id=ii.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID %s " + "where %s and i.sendTime %s %s %s and %s %s %s " + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s %s " - + " %s", + + " %s %s ", obj.selectUserName ? ",i.sender userName" : "", obj.selectTousseType ? ",td.tousseType" : "", obj.selectTousseName ? ",td.name tousseName" : "", @@ -1759,11 +1773,12 @@ obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseTypeAndPackageSizeSql, obj.extraWhereSql, + obj.extraQuery, groupBySql); } // 一次性物品的发货统计 - if (obj.includeDisposableGoods && StringUtils.isBlank(obj.sterilizationModeSqlWithAliasOfTousseDefinitionIsTd) && StringUtils.isBlank(obj.packageTypeSqlWithAliasOfTousseDefinitionIsTd)) { + if ((StringUtils.isBlank(obj.extraQuery) || obj.extraQuery != null && !obj.extraQuery.contains("td.")) && obj.includeDisposableGoods && StringUtils.isBlank(obj.sterilizationModeSqlWithAliasOfTousseDefinitionIsTd) && StringUtils.isBlank(obj.packageTypeSqlWithAliasOfTousseDefinitionIsTd)) { if (obj.includeTousses) { amountSql += " union all "; } @@ -1796,7 +1811,7 @@ + "inner join TousseDefinition td on td.id=ii.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID %s " + "inner join TousseInstance ti on ti.tousseDefinition_id=td.id " + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and ti.invoice_id = i.id and td.tousseType ='"+ TousseDefinition.PACKAGE_TYPE_COMBO +"' and i.sendTime %s %s %s and %s %s %s " - + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s" + + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s %s " + " %s", obj.selectUserName ? ",i.sender userName" : "", obj.selectTousseType ? ", '"+TousseDefinition.PACKAGE_TYPE_COMBO+"' tousseType" : "", @@ -1815,6 +1830,7 @@ obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("tdc", "ti"), obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, obj.extraWhereSql, + obj.extraQuery, groupBySql); } break; @@ -1925,7 +1941,7 @@ .format("select count(*) amount, td.tousseType, td.name tousseName " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id=td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " + "where %s and ti.signedDate %s %s %s and %s %s %s " - + "and ti.signedUser is not null and %s(ti.signedUser)<>0 %s %s %s %s %s " + + "and ti.signedUser is not null and %s(ti.signedUser)<>0 %s %s %s %s %s %s " + "group by td.tousseType,td.name", obj.extraJoinCondition, obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", @@ -1940,6 +1956,7 @@ obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseTypeAndPackageSizeSql, + obj.extraQuery, obj.extraWhereSql); } if(obj.getIsQueryCom()){ @@ -1950,7 +1967,7 @@ .format("select count(*) amount, td.tousseType, td.name tousseName " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id=td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null and ti.signedDate %s %s %s and %s %s %s " - + "and ti.signedUser is not null and %s(ti.signedUser)<>0 %s %s %s %s" + + "and ti.signedUser is not null and %s(ti.signedUser)<>0 %s %s %s %s %s " + "group by td.tousseType,td.name ", obj.extraJoinCondition, obj.betweenSql, @@ -1963,6 +1980,7 @@ obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("tdc", "ti"), obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.extraQuery, obj.extraWhereSql); } break; Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r33769 -r33770 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 33769) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 33770) @@ -13183,7 +13183,7 @@ } @Override - public List getTousseWorkLoadData(String startTime, + public List getTousseWorkLoadData(String instrumentSetTypes, String startTime, String endTime, String departCoding, String tousseTypes, String operator, String isDisableIDCard, String taskGroup, String tousseGroup, boolean showAsTousseSplitAmount, String sterilizationMode, String packageType, String applicationDepart, String groupIds, String queryType) { long begin = System.currentTimeMillis(); @@ -13286,6 +13286,10 @@ String tousseGroupFalseSqlCondition = ""; List querySqlList = new ArrayList(); + Set instrumentSetTypeSet = SqlUtils.splitStringToSet(instrumentSetTypes, ","); + if(CollectionUtils.isNotEmpty(instrumentSetTypeSet)){ + params.extraQuery= getInstrumentSetTypeSql(instrumentSetTypeSet); + } //申请数量（按包统计） String applicationSql = String.format("select 'application' type,tl.tousseName,amount ,tl.tousseType from (" +dataIndex.getWorkAmountByPackageSQL("申请数量", params) @@ -16714,7 +16718,7 @@ } // 质量监测报表 @Override - public List getQualityMonitoringDataSource(String formName, String responsibilityPart, String recordRinseInformation, String scope, Map requestParameters, boolean filterAnswer, boolean isShowTousseMaerialSumAmount) { + public List getQualityMonitoringDataSource(String instrumentSetTypes, String formName, String responsibilityPart, String recordRinseInformation, String scope, Map requestParameters, boolean filterAnswer, boolean isShowTousseMaerialSumAmount) { List list = new ArrayList(); String startDate = StrutsParamUtils.getPraramValue("startDate", ""); String endDate = StrutsParamUtils.getPraramValue("endDate", ""); @@ -16797,6 +16801,17 @@ } if (StringUtils.isNotBlank(startDate) && StringUtils.isNotBlank(endDate) && StringUtils.isNotBlank(formName)) { + String joinTousseSql = ""; + String onlyQueryTousseSql = ""; + String tousseSql = ""; + if(StringUtils.isNotBlank(instrumentSetTypes)){ + Set instrumentSetTypeSet = SqlUtils.splitStringToSet(instrumentSetTypes, ","); + if(CollectionUtils.isNotEmpty(instrumentSetTypeSet)){ + tousseSql = getInstrumentSetTypeSql(instrumentSetTypeSet); + joinTousseSql = " join TousseDefinition td on td.id=qmd.tousseDefinitionId "; + onlyQueryTousseSql = " and 1=2 "; + } + } JSONObject amoutSumJson = new JSONObject(); String whereSql = buildWhereSQL(startDate, endDate, querySupplyRoom, formName, monitoringType); @@ -16811,9 +16826,12 @@ + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" + " join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + extraJoin + + joinTousseSql + " join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " + " and fiov.definitionItemOption_id=fdio.id " - + " where fdi.isReportDisplay='是' and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql; + + " where fdi.isReportDisplay='是' " + + tousseSql + + "and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql; sql+= " union all select distinct " + dataSql + " dateStr," + " qmd.tousseName toussName,qmd.material material,qmd.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg," + " '' fdiName,null orderNum,'' value,null id,fi.createUserName,fdio.orderNumber ,qmd.orderNumber qmdOrderNumber,qmd.id qmdid " @@ -16823,18 +16841,24 @@ + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" + " left join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + extraJoin + + joinTousseSql + " left join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " + " and fiov.definitionItemOption_id=fdio.id " - + " where fdi.isReportDisplay='是' and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql + + " where fdi.isReportDisplay='是' " + + tousseSql + + " and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql + " and fiov.id is null and qmi.id not in(" + " select qmi.id qmiId" + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" + " join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + extraJoin + + joinTousseSql + " join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " + " and fiov.definitionItemOption_id=fdio.id " - + " where fdi.isReportDisplay='是' and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql + + " where fdi.isReportDisplay='是' " + + tousseSql + + "and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql + "))temp " + tempOrderBySql; addMonitoringItemBeanOptionRadioAndCHeck(list,sql,showSterilizationInfo, materialAndTousse); // 没有录入材料和包，有配了多选的细则的项 @@ -16848,7 +16872,9 @@ + " join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " + extraJoin + " and fiov.definitionItemOption_id=fdio.id " - + " where fdi.isReportDisplay='是' and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + + " where fdi.isReportDisplay='是' " + + onlyQueryTousseSql + + " and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + " and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmd.id is null)"; sql += " union all select distinct " + dataSql + " dateStr," + " qmi.id qmiId,qmi.positionMsg positionMsg," @@ -16860,7 +16886,9 @@ + " left join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " + extraJoin + " and fiov.definitionItemOption_id=fdio.id " - + " where fdi.isReportDisplay='是' and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + + " where fdi.isReportDisplay='是' " + + onlyQueryTousseSql + + " and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + " and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmd.id is null)" + " and fiov.id is null and qmi.id not in (" + " select qmi.id qmiId " @@ -16870,7 +16898,9 @@ + " join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " + extraJoin + " and fiov.definitionItemOption_id=fdio.id " - + " where fdi.isReportDisplay='是' and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + + " where fdi.isReportDisplay='是'" + + onlyQueryTousseSql + + " and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + " and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmd.id is null)" + ")"; addMonitoringItemBeanOptionRadioAndCHeckWithoutMaterial(list,sql,showSterilizationInfo); @@ -16883,7 +16913,10 @@ + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id " + " join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + extraJoin - + " where "+ answerWhereSql +" (fdi.type != '" + FormDefinitionItem.TYPE_RADIO + "' and fdi.isReportDisplay='是' and fdi.type!='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql + qmdOrderNumberBySql; + + joinTousseSql + + " where "+ answerWhereSql +" (fdi.type != '" + FormDefinitionItem.TYPE_RADIO + "' and fdi.isReportDisplay='是' " + + tousseSql + + " and fdi.type!='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql + qmdOrderNumberBySql; addMonitoringItemBeanOptionSingleResult(list,sql,showSterilizationInfo, materialAndTousse); // 没有录入材料和包，单一的结果 sql = " select " + dataSql + " dateStr," @@ -16894,7 +16927,9 @@ + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id " + extraJoin + " where "+ answerWhereSql +" (fdi.type != '" + FormDefinitionItem.TYPE_RADIO + "' and fdi.type!='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql - + " and fdi.isReportDisplay='是' and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmd.id is null) "; + + " and fdi.isReportDisplay='是' " + + onlyQueryTousseSql + + "and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmd.id is null) "; addMonitoringItemBeanOptionSingleResultWithoutMaterial(list,sql,showSterilizationInfo,monitoringType); if(FormDefinition.FOMRTYPE_QUALITYMONITORING.equals(monitoringType)){ @@ -16906,8 +16941,10 @@ + " join FormDefinition fd on fi.formDefinition_id=fd.id join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + " left join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id" + extraJoin + + joinTousseSql + " where qmi.id not in (select formInstance_id from FormInstanceItem) " + " and qmi.type = '" + FormDefinition.FOMRTYPE_QUALITYMONITORING + "' " + + tousseSql + " and " + whereSql + tousseNameSql + qmdOrderNumberBySql; addMonitoringItemBeanQualityMonitoring(list,sql,showSterilizationInfo, materialAndTousse); } @@ -16921,7 +16958,8 @@ + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + extraJoin - + " where qmi.type = '" + FormDefinition.FOMRTYPE_PERIODICMONITORING + "'" + " and " + whereSql + tousseNameSql; + + joinTousseSql + + " where qmi.type = '" + FormDefinition.FOMRTYPE_PERIODICMONITORING + "'" + " and " + whereSql + tousseNameSql + tousseSql; addMonitoringItemBeanRountineMonitoring(list,sql,showSterilizationInfo,isQualifiedAmount,isQualifiedMaterialAmount); } if(StringUtils.isNotBlank(extraQueryForResponsibilityPart) && "质量监测".equals(monitoringType)){ @@ -16941,8 +16979,12 @@ Integer qmTousseAndMaterialAmount = 0; if(materialAndTousse){ sql = " select sum(t.bAmount) total from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " - + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join ( select qmi.id qid,sum(case when qmd.material is null or qmd.material='' then 0 else qmd.amount end) bAmount from QualityMonitoringInstance qmi,QualityMonitoringGoods qmd where qmi.id=qmd.qualityMonitoringInstance_id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join ( select qmi.id qid,sum(case when qmd.material is null or qmd.material='' then 0 else qmd.amount end) bAmount from " + + "QualityMonitoringInstance qmi join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + + joinTousseSql + + " where 1=1 " + tousseNameSql + + tousseSql + " group by qmi.id ) t on t.qid=qmi.id " + " where " + whereSql; Set readIds = new HashSet(); @@ -16955,8 +16997,12 @@ } }else{ sql = " select count(0) count,sum(t.aAmount) total from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " - + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join ( select qmi.id qid,sum(qmd.amount) aAmount from QualityMonitoringInstance qmi,QualityMonitoringGoods qmd where qmi.id=qmd.qualityMonitoringInstance_id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join ( select qmi.id qid,sum(qmd.amount) aAmount " + + "from QualityMonitoringInstance qmi join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + + joinTousseSql + + "where 1=1 " + tousseNameSql + + tousseSql + " group by qmi.id ) t on t.qid=qmi.id " + " where " + whereSql; } @@ -24767,7 +24813,8 @@ modeType = "recordSterilizatioInformation"; } JSONObject dynamicColumnObj = queryFormDefinitionDynamicColumnInfo(formName,modeType); - List monitoringItemBeans = getQualityMonitoringDataSource(formName, responsibilityPart, recordRinseInformation, scope, requestParameters,dynamicColumnObj.optBoolean("haveRequirement"), isShowTousseMaerialSumAmount); + String instrumentSetTypes = StrutsParamUtils.getPraramValue("instrumentSetTypes", ""); + List monitoringItemBeans = getQualityMonitoringDataSource(instrumentSetTypes, formName, responsibilityPart, recordRinseInformation, scope, requestParameters,dynamicColumnObj.optBoolean("haveRequirement"), isShowTousseMaerialSumAmount); if(!CollectionUtils.isEmpty(monitoringItemBeans)){ //获取最新的属性 Map formDefinitionItemNameMap = getFormDefinitionOrderInfo(formName); @@ -26441,4 +26488,38 @@ } return vos; } + /** + * 获取器械包种类过滤 + * @param instrumentSetType 器械包种类 + * @param instrumentSetTypes 器械包种类 不为空时忽略instrumentSetType + * @param inOrNotIN in 或者 not in + * @return + */ + private String getInstrumentSetTypeSql(Set instrumentSetTypes){ + return " and td.ancestorid in (select istt.tousseDefinitionId from InstrumentSetType_TD istt " + + "join instrumentSetType ist on ist.id=istt.instrumentSetTypeId where "+ SqlUtils.getNonStringFieldInLargeCollectionsPredicate("ist.id", instrumentSetTypes) +") "; + } + public List getBetweenDayList(String startTime, String endTime) { + // 返回的日期集合 + List days = new ArrayList(); + SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); + try { + Date start = dateFormat.parse(startTime); + Date end = dateFormat.parse(endTime); + + Calendar tempStart = Calendar.getInstance(); + tempStart.setTime(start); + Calendar tempEnd = Calendar.getInstance(); + tempEnd.setTime(end); + tempEnd.add(Calendar.DATE, +1);// 日期加1(包含结束) + while (tempStart.before(tempEnd)) { + days.add(dateFormat.format(tempStart.getTime())); + tempStart.add(Calendar.DAY_OF_YEAR, 1); + } + + } catch (ParseException e) { + e.printStackTrace(); + } + return days; + } } Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/action/JasperreportsAction.java =================================================================== diff -u -r33725 -r33770 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/action/JasperreportsAction.java (.../JasperreportsAction.java) (revision 33725) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/action/JasperreportsAction.java (.../JasperreportsAction.java) (revision 33770) @@ -491,6 +491,7 @@ parametMap.put("title", "质量监测表"); } Long inspectItemId = StrutsParamUtils.getPraramLongValue("inspectItemId", 0L); + String instrumentSetTypes = StrutsParamUtils.getPraramValue("instrumentSetTypes", ""); String responsibilityPart = null; String recordRinseInformation = null; String scope = null; @@ -500,7 +501,7 @@ responsibilityPart = q.getResponsibilityPart(); recordRinseInformation = q.getRecordRinseInformation(); } - return jasperReportManager.getQualityMonitoringDataSource(formName, responsibilityPart, recordRinseInformation, scope, requestParameters,false, false); + return jasperReportManager.getQualityMonitoringDataSource(instrumentSetTypes, formName, responsibilityPart, recordRinseInformation, scope, requestParameters,false, false); }else if(reportName.equals("recycleMaterialDetail")){//回收器械材料明细报表 String startTime = StrutsParamUtils.getPraramValue("startTime", ""); String endTime = StrutsParamUtils.getPraramValue("endTime", ""); @@ -1532,6 +1533,7 @@ return jasperReportManager.getTousseDeliverDetailData(startDay,endDay,departCoding); } }else if(reportName.equals("tousseWorkLoad")){ //器械包工作量统计报表 + String instrumentSetTypes = StrutsParamUtils.getPraramValue("instrumentSetTypes", ""); String startDay = StrutsParamUtils.getPraramValue("startDay", ""); String endDay = StrutsParamUtils.getPraramValue("endDay", ""); String departCoding = StrutsParamUtils.getPraramValue("departCoding", ""); @@ -1562,7 +1564,7 @@ parametMap.put("remark", ""); } if(StringUtils.isNotBlank(startDay) && StringUtils.isNotBlank(endDay)){ - return jasperReportManager.getTousseWorkLoadData(startDay,endDay,departCoding,tousseTypes,operator,isDisableIDCard,taskGroup,tousseGroupName,showAsTousseSplitAmount,sterilizationMode,packageType,applicationDepart, groupIds, queryType); + return jasperReportManager.getTousseWorkLoadData(instrumentSetTypes, startDay,endDay,departCoding,tousseTypes,operator,isDisableIDCard,taskGroup,tousseGroupName,showAsTousseSplitAmount,sterilizationMode,packageType,applicationDepart, groupIds, queryType); } }else if("goodsRiskLevelReport".equals(reportName)){ String startTime = StrutsParamUtils.getPraramValue("startDay", ""); Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManager.java =================================================================== diff -u -r33725 -r33770 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManager.java (.../JasperReportManager.java) (revision 33725) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManager.java (.../JasperReportManager.java) (revision 33770) @@ -466,6 +466,7 @@ public List getSterilizingStoveUseCountDetailData(String startTime,String endTime,String depart,String departCoding,String sterilizationPurpose, String sterilizerNameGroup, String sterilizerName, String sterilisation); /** * 获取器械包工作量统计报表的数据 + * @param instrumentSetTypes 器械包种类 只查询具体种类 不查询种类下的子类 * @param startTime 开始时间格式如：2017-01-01 * @param endTime 结束时间格式如：2017-01-01 * @param departCoding 供应室编码 @@ -482,7 +483,7 @@ * @return */ public List getTousseWorkLoadData( - String startTime,String endTime, + String instrumentSetTypes, String startTime,String endTime, String departCoding,String tousseTypes, String operator, String isDisableIDCard, String taskGroup, String tousseGroup, boolean showAsTousseSplitAmount, String sterilizationMode, String packageType, String applicationDepart, String groupIds, String querytype); /** @@ -679,14 +680,15 @@ public List getFormInstanceData(String startDate,String endDate,String departCoding,String formDefinitionId,Map requestParameters); /** * @param formName 表单名称 + * @param instrumentSetTypes 器械包种类 只查询具体种类 不查询种类下的子类 * @param responsibilityPart 责任环节 * @param recordRinseInformation 监测项是否录入清洗信息 * @param scope 监测范围 * @param requestParameters 报表需要的参数,可根据数据变更报表模板 * @param filterAnswer 是否过滤掉空的answer 如果有FormDefinitionItem有必填项,可以过滤掉加快查询和显示效率 * @return */ - public List getQualityMonitoringDataSource(String formName, String responsibilityPart, String recordRinseInformation, String scope, Map requestParameters, boolean filterAnswer, boolean isShowTousseMaerialSumAmount); + public List getQualityMonitoringDataSource(String instrumentSetTypes, String formName, String responsibilityPart, String recordRinseInformation, String scope, Map requestParameters, boolean filterAnswer, boolean isShowTousseMaerialSumAmount); /** * 获取丢失报损类型月报数据 * @param queryYear