Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r17425 -r17446 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 17425) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 17446) @@ -13528,7 +13528,7 @@ key = day + "&" + title + "&recycle"; TousseMorrowSendBean bean = new TousseMorrowSendBean(); bean.setRecycleDate(day); - bean.setColummTitle(title+"回收"); + bean.setColummTitle(title+"包数量"); bean.setColumnNum(columnNum++); bean.setValue("0"); beanMap.put(key, bean); @@ -13557,40 +13557,75 @@ bean2.setValue("0"); beanMap.put(key, bean2); } - + startDay = startDay + " 00:00:00"; + endDay = endDay + " 00:00:00"; String dataSql = dateQueryAdapter.dateToVarchar1("rr.recyclingTime"); + String packingDateSql = dateQueryAdapter.dateToVarchar1("pr.packTime"); // 获取回收数量的sql String recycleDepartSql = " select " + dataSql + " recycleTime, rr.depart depart,sum(ti.amount) amount " + " from recyclingRecord rr,TousseItem ti where rr.id = ti.recyclingRecord_id " - + " and ( " + dateQueryAdapter.strDateAreasql("rr.recyclingTime", startDay, endDay, false) + ")" + + " and ( " + dateQueryAdapter.dateAreaSql("rr.recyclingTime", startDay, endDay, false) + ")" + " and rr.depart in (" + whereDepart + ") " + " group by " + dataSql + " , rr.depart " ; + // 辅料包也算次日下送率，用当天装配，和是否当天发货算 + String packingDepartSql = " select " + packingDateSql + " packTime, ip.depart depart,count(*) " + + " from TousseInstance ti,TousseDefinition td,invoicePlan ip,PackingRecord pr " + + " where ti.invoicePlanId = ip.id and ti.packingRecord_id=pr.id and ti.tousseDefinition_id = td.id " + + " and td.tousseType = '" + TousseDefinition.PACKAGE_TYPE_DRESSING + "' " + + " and ip.depart in (" + whereDepart + ") " + + " and (" + dateQueryAdapter.dateAreaSql("pr.packTime", startDay, endDay, false) + ")" + + " group by " + packingDateSql + ", ip.depart "; + // 器械包分组数量 String recycleTousseGroupSql = " select " + dataSql + " ,tdi.tousseGroupName,sum(tItem.amount) from recyclingRecord rr,TousseItem tItem,TousseDefinition tdi " + " where rr.id = tItem.recyclingRecord_id and tItem.tousseName = tdi.name and tdi.forDisplay = 1 " - + " and ( " + dateQueryAdapter.strDateAreasql("rr.recyclingTime", startDay, endDay, false) + ")" + + " and ( " + dateQueryAdapter.dateAreaSql("rr.recyclingTime", startDay, endDay, false) + ")" + " and tdi.tousseGroupName in (" + whereTousseGroup + " )" + " group by " + dataSql + " , tdi.tousseGroupName"; + String packingTousseGroupSql = " select " + packingDateSql + " packTime, td.tousseGroupName groupName,count(*) " + + " from TousseInstance ti,TousseDefinition td,PackingRecord pr where ti.packingRecord_id=pr.id " + + " and ti.tousseDefinition_id = td.id " + + " and td.tousseType = '" + TousseDefinition.PACKAGE_TYPE_DRESSING + "' " + + " and td.tousseGroupName in (" + whereTousseGroup + ") " + + " and ( " + dateQueryAdapter.dateAreaSql("pr.packTime", startDay, endDay, false) + ")" + + " group by " + packingDateSql + ", td.tousseGroupName "; + + // 器械包分组和科室数量 String recycleAmountSql = " select " + dataSql + ",sum(tItem.amount) from recyclingRecord rr,TousseItem tItem,TousseDefinition tdi " - + " where rr.id = tItem.recyclingRecord_id and tItem.tousseName = tdi.name and tdi.forDisplay = 1 " - + " and ( " + dateQueryAdapter.strDateAreasql("rr.recyclingTime", startDay, endDay, false) + ")" - + " and ( tdi.tousseGroupName in (" + whereTousseGroup + ")" + " or rr.depart in (" + whereDepart + ") )" - + " group by " + dataSql ; + + " where rr.id = tItem.recyclingRecord_id and tItem.tousseName = tdi.name and tdi.forDisplay = 1 " + + " and ( " + dateQueryAdapter.dateAreaSql("rr.recyclingTime", startDay, endDay, false) + ")" + + " and ( tdi.tousseGroupName in (" + whereTousseGroup + ")" + " or rr.depart in (" + whereDepart + ") )" + + " group by " + dataSql ; + + String packingAmountSql = " select "+ packingDateSql + " packTime,count(*) " + + " from TousseInstance ti,TousseDefinition td,invoicePlan ip,PackingRecord pr where ti.invoicePlanId = ip.id and ti.packingRecord_id=pr.id " + + " and ti.tousseDefinition_id = td.id " + + " and td.tousseType = '" + TousseDefinition.PACKAGE_TYPE_DRESSING + "' " + + " and (ip.depart in (" +whereDepart + ") or td.tousseGroupName in (" + whereTousseGroup + "))" + + " and ( " + dateQueryAdapter.dateAreaSql("pr.packTime", startDay, endDay, false) + ")" + + " group by " + packingDateSql ; + getMorrowSendBean(recycleDepartSql,beanMap,"recycle",null); + getMorrowSendBean(packingDepartSql,beanMap,"recycle",null); getMorrowSendBean(recycleTousseGroupSql,beanMap,"recycle",null); + getMorrowSendBean(packingTousseGroupSql,beanMap,"recycle",null); getMorrowSendBean(recycleAmountSql,beanMap,"recycle","total"); + getMorrowSendBean(packingAmountSql,beanMap,"recycle","total"); String timeCompareSql = ""; + String packingTimeCompareSql = ""; String database = dbConnection.getDatabase(); if(DatabaseUtil.isOracle(database)){ // 按天算次日下送时间,只要不是当天回收当天发,都是次日下送. timeCompareSql = " trunc(iv.sendTime) - trunc(iv.recyclingTime) > 0 "; + packingTimeCompareSql = " trunc(iv.sendTime) - trunc(pr.packTime) > 0 "; // 按24小时算，两者相减的单位是24小时，> 1即大于24小时 // timeCompareSql = " trunc(iv.sendTime,'hh24') - trunc(iv.recyclingTime,'hh24') > 1"; }else{ // 按天算次日下送时间,只要不是当天回收当天发,都是次日下送. timeCompareSql = " DATEDIFF(DAY,iv.recyclingTime,iv.sendTime) > 0 "; + packingTimeCompareSql = " DATEDIFF(DAY,pr.packTime,iv.sendTime) > 0 "; // 按小时计算，单位是小时 超过24小时算次日下送 // timeCompareSql = " DATEDIFF(HOUR,iv.recyclingTime,iv.sendTime) >= 24 "; } @@ -13600,24 +13635,53 @@ + " where iv.id=ii.invoice_id " + " and " + timeCompareSql + " and iv.depart in (" + whereDepart + ")" - + " and ( " + dateQueryAdapter.strDateAreasql("iv.recyclingTime", startDay, endDay, false) + ")" + + " and ( " + dateQueryAdapter.dateAreaSql("iv.recyclingTime", startDay, endDay, false) + ")" + " group by " + dataSql + " ,iv.depart "; + + // 装配辅料包的次日下送数-科室 + String departPackingMorrowSendSql = " select " + packingDateSql + " day,iv.depart,count(*) from Invoice iv,TousseInstance ti,PackingRecord pr " + + " where iv.id=ti.invoice_id and ti.packingRecord_id = pr.id " + + " and "+ packingTimeCompareSql + + " and iv.depart in (" + whereDepart + ")" + + " and ( " + dateQueryAdapter.dateAreaSql("pr.packTime", startDay, endDay, false) + ")" + + " group by " + packingDateSql +" ,iv.depart"; + String tousseGrouptMorrowSendSql = "select " + dataSql +" day,tdi.tousseGroupName,sum(ii.amount) from Invoice iv,InvoiceItem ii,TousseDefinition tdi " + " where iv.id=ii.invoice_id and ii.tousseDefinitionId=tdi.id " + " and " + timeCompareSql + " and tdi.tousseGroupName in (" + whereTousseGroup + ")" - + " and ( " + dateQueryAdapter.strDateAreasql("iv.recyclingTime", startDay, endDay, false) + ")" + + " and ( " + dateQueryAdapter.dateAreaSql("iv.recyclingTime", startDay, endDay, false) + ")" + " group by " + dataSql + " ,tdi.tousseGroupName "; + // 装配辅料包的次日下送数-器械包分组 + String tousseGrouptPackingMorrowSendSql = " select " + packingDateSql + " day,td.tousseGroupName,count(*) from Invoice iv,TousseInstance ti,TousseDefinition td,PackingRecord pr " + + " where iv.id=ti.invoice_id and ti.packingRecord_id = pr.id and ti.tousseDefinition_id=td.id " + + " and "+ packingTimeCompareSql + + " and td.tousseGroupName in (" + whereTousseGroup + ")" + + " and ( " + dateQueryAdapter.dateAreaSql("pr.packTime", startDay, endDay, false) + ")" + + " group by " + packingDateSql +" ,td.tousseGroupName"; + String totalMorrowSendSql = "select " + dataSql +" day,sum(ii.amount) from Invoice iv,InvoiceItem ii,TousseDefinition tdi " + " where iv.id=ii.invoice_id and ii.tousseDefinitionId=tdi.id " + " and " + timeCompareSql + " and ( iv.depart in (" + whereDepart + ") or tdi.tousseGroupName in (" + whereTousseGroup + ") ) " - + " and ( " + dateQueryAdapter.strDateAreasql("iv.recyclingTime", startDay, endDay, false) + ")" + + " and ( " + dateQueryAdapter.dateAreaSql("iv.recyclingTime", startDay, endDay, false) + ")" + " group by " + dataSql ; + + // 装配辅料包的次日下送数-科室和器械包分组总数 + String totalPackingMorrowSendSql = " select " + packingDateSql + " day,count(*) from Invoice iv,TousseInstance ti,TousseDefinition td,PackingRecord pr " + + " where iv.id=ti.invoice_id and ti.packingRecord_id = pr.id and ti.tousseDefinition_id=td.id " + + " and "+ packingTimeCompareSql + + " and ( iv.depart in (" + whereDepart + ") or td.tousseGroupName in (" + whereTousseGroup + ") ) " + + " and ( " + dateQueryAdapter.dateAreaSql("pr.packTime", startDay, endDay, false) + ")" + + " group by " + packingDateSql ; + getMorrowSendBean(departMorrowSendSql,beanMap,"morrowSend",null); + getMorrowSendBean(departPackingMorrowSendSql,beanMap,"morrowSend",null); getMorrowSendBean(tousseGrouptMorrowSendSql,beanMap,"morrowSend",null); + getMorrowSendBean(tousseGrouptPackingMorrowSendSql,beanMap,"morrowSend",null); getMorrowSendBean(totalMorrowSendSql,beanMap,"morrowSend","total"); + getMorrowSendBean(totalPackingMorrowSendSql,beanMap,"morrowSend","total"); // 合计 sumAmount(days,titles,beanMap); // 计算不合格率 @@ -13705,7 +13769,7 @@ String key = totalKey + "&" + titile + "&recycle"; TousseMorrowSendBean bean = new TousseMorrowSendBean(); bean.setRecycleDate("合计"); - bean.setColummTitle(titile+"回收"); + bean.setColummTitle(titile+"包数量"); bean.setColumnNum(rColNum); bean.setValue(rAmountTotal + ""); beanMap.put(key, bean); @@ -13794,7 +13858,12 @@ String key = dateTime + "&" + total + "&" + type; if(beanMap.containsKey(key)){ TousseMorrowSendBean bean = beanMap.get(key); - bean.setValue(amount + ""); + String value = bean.getValue(); + int curAmount = 0; + if(StringUtils.isNumeric(value)){ + curAmount = Integer.valueOf(value); + } + bean.setValue(curAmount + amount + ""); beanMap.put(key, bean); } } @@ -13814,7 +13883,12 @@ String key = dateTime + "&" + title + "&" + type; if(beanMap.containsKey(key)){ TousseMorrowSendBean bean = beanMap.get(key); - bean.setValue(amount + ""); + String value = bean.getValue(); + int curAmount = 0; + if(StringUtils.isNumeric(value)){ + curAmount = Integer.valueOf(value); + } + bean.setValue(curAmount + amount + ""); beanMap.put(key, bean); } }