Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/TousseDeliverDetailVO.java =================================================================== diff -u -r12602 -r15111 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/TousseDeliverDetailVO.java (.../TousseDeliverDetailVO.java) (revision 12602) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/TousseDeliverDetailVO.java (.../TousseDeliverDetailVO.java) (revision 15111) @@ -7,6 +7,8 @@ private Integer recyclingAmount = 0;//回收数量 + private Integer washAndDisinfectAmount = 0;//清洗 数量（是指材料数量） + private Integer packingAmount = 0;//装配数量 private Integer reviewAmount = 0;//审核数量 @@ -89,4 +91,12 @@ this.sendOutAmount = sendOutAmount; } + public Integer getWashAndDisinfectAmount() { + return washAndDisinfectAmount; + } + + public void setWashAndDisinfectAmount(Integer washAndDisinfectAmount) { + this.washAndDisinfectAmount = washAndDisinfectAmount; + } + } Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r15069 -r15111 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 15069) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 15111) @@ -9269,167 +9269,432 @@ return map; } + /** + * 这个方法只在getTousseDeliverDetailData内被调用，解决数据库的in()函数内的参数个数有限制 + * + */ + private String createInFilterSql(String filterName, String[] inFilterArray) { + String inFilter = " having "; + if (inFilterArray == null) { + return ""; + } + + int inFilterArraySize = inFilterArray.length; + for (int i = 0; i < inFilterArraySize; i ++) { + inFilter += filterName + " in(" + inFilterArray[i] + ") or "; + } + inFilter = inFilter.substring(0, inFilter.length() - 4); + return inFilter; + } + +// @Override +// public List getTousseDeliverDetailData(String startTime, String endTime,String departCoding) { +// startTime += " 00:00:00"; +// endTime += " 23:59:59"; +// +// Map optionMap = new HashMap(); +// GoodsOption option = goodsOptionManager.getGoodsOption(GoodsOption.MODEL_TOUSSEDELIVERDETAIL, departCoding); +// if(option != null && StringUtils.isNotBlank(option.getValue())){ +// for(String goodsName: option.getValue().split(";")){ +// optionMap.put(goodsName, 1); +// } +// } +// Map voMap = new HashMap(); +// +// //回收 +// String sql2 = "select i.tousseName,sum(i.amount) from RecyclingRecord r,TousseItem i" +// + " where r.id = i.recyclingRecord_id and r.orgUnitCoding = '"+departCoding +// + "' and r.recyclingTime between "+dateQueryAdapter.dateConverAdapter2(startTime,"yyyy-mm-dd HH24:MI:SS") +// + " and "+dateQueryAdapter.dateConverAdapter2(endTime,"yyyy-mm-dd HH24:MI:SS") +// + " group by i.tousseName "; +// +// ResultSet result2 = objectDao.executeSql(sql2); +// try { +// while(result2.next()){ +// String tousseName = result2.getString(1); +// tousseName = CssdUtils.filterDisinfectGoodsName(tousseName); +// if(optionMap.size() == 0 || optionMap.get(tousseName) != null){ +// int tousseAmount = result2.getInt(2); +// TousseDeliverDetailVO vo = voMap.get(tousseName); +// if(vo == null){ +// vo = new TousseDeliverDetailVO(); +// voMap.put(tousseName, vo); +// } +// vo.setTousseName(tousseName); +// vo.setRecyclingAmount(tousseAmount); +// } +// } +// } catch (SQLException e) { +// e.printStackTrace(); +// }finally { +// DatabaseUtil.closeResultSetAndStatement(result2); +// } +// +// //装配 +// String sql3 = "select i.tousseName,count(*) from PackingRecord r,tousseInstance i" +// + " where r.id = i.packingRecord_id and r.orgUnitCoding = '"+departCoding +// + "' and r.packTime between "+dateQueryAdapter.dateConverAdapter2(startTime,"yyyy-mm-dd HH24:MI:SS") +// + " and "+dateQueryAdapter.dateConverAdapter2(endTime,"yyyy-mm-dd HH24:MI:SS") +// + " group by i.tousseName "; +// +// ResultSet result3 = objectDao.executeSql(sql3); +// try { +// while(result3.next()){ +// String tousseName = result3.getString(1); +// tousseName = CssdUtils.filterDisinfectGoodsName(tousseName); +// if(optionMap.size() == 0 || optionMap.get(tousseName) != null){ +// int tousseAmount = result3.getInt(2); +// TousseDeliverDetailVO vo = voMap.get(tousseName); +// if(vo == null){ +// vo = new TousseDeliverDetailVO(); +// voMap.put(tousseName, vo); +// } +// vo.setTousseName(tousseName); +// vo.setPackingAmount(tousseAmount); +// } +// } +// } catch (SQLException e) { +// e.printStackTrace(); +// }finally { +// DatabaseUtil.closeResultSetAndStatement(result3); +// } +// +// //审核 +// String sql4 = "select i.tousseName,count(*) from tousseInstance i" +// + " where i.orgUnitCoding = '"+departCoding +// + "' and i.reviewTime between "+dateQueryAdapter.dateConverAdapter2(startTime,"yyyy-mm-dd HH24:MI:SS") +// + " and "+dateQueryAdapter.dateConverAdapter2(endTime,"yyyy-mm-dd HH24:MI:SS") +// + " group by i.tousseName "; +// +// ResultSet result4 = objectDao.executeSql(sql4); +// try { +// while(result4.next()){ +// String tousseName = result4.getString(1); +// tousseName = CssdUtils.filterDisinfectGoodsName(tousseName); +// if(optionMap.size() == 0 || optionMap.get(tousseName) != null){ +// int tousseAmount = result4.getInt(2); +// TousseDeliverDetailVO vo = voMap.get(tousseName); +// if(vo == null){ +// vo = new TousseDeliverDetailVO(); +// voMap.put(tousseName, vo); +// } +// vo.setTousseName(tousseName); +// vo.setReviewAmount(tousseAmount); +// } +// } +// } catch (SQLException e) { +// e.printStackTrace(); +// } finally { +// DatabaseUtil.closeResultSetAndStatement(result4); +// } +// +// //灭菌 +// String sql5 = "select i.tousseName,count(*) from SterilizationRecord r, tousseInstance i" +// + " where r.id = i.sterilizationRecord_id and i.orgUnitCoding = '"+departCoding +// + "' and r.endDate between "+dateQueryAdapter.dateConverAdapter2(startTime,"yyyy-mm-dd HH24:MI:SS") +// + " and "+dateQueryAdapter.dateConverAdapter2(endTime,"yyyy-mm-dd HH24:MI:SS") +// + " group by i.tousseName "; +// +// ResultSet result5 = objectDao.executeSql(sql5); +// try { +// while(result5.next()){ +// String tousseName = result5.getString(1); +// tousseName = CssdUtils.filterDisinfectGoodsName(tousseName); +// if(optionMap.size() == 0 || optionMap.get(tousseName) != null){ +// int tousseAmount = result5.getInt(2); +// TousseDeliverDetailVO vo = voMap.get(tousseName); +// if(vo == null){ +// vo = new TousseDeliverDetailVO(); +// voMap.put(tousseName, vo); +// } +// vo.setTousseName(tousseName); +// vo.setSterilizationAmount(tousseAmount); +// } +// } +// } catch (SQLException e) { +// e.printStackTrace(); +// } finally { +// DatabaseUtil.closeResultSetAndStatement(result5); +// } +// +// //发货 +// String sql = " select ti.tousseName,count(*) from invoice i,TousseInstance ti" +// + " where i.id = ti.invoice_id and i.orgUnitCoding = '" + departCoding +// + "' and i.sendTime between "+dateQueryAdapter.dateConverAdapter2(startTime,"yyyy-mm-dd HH24:MI:SS") +// + " and "+dateQueryAdapter.dateConverAdapter2(endTime,"yyyy-mm-dd HH24:MI:SS") +// + " group by ti.tousseName "; +// +// ResultSet result = objectDao.executeSql(sql); +// Map map = new HashMap(); +// try { +// while(result.next()){ +// String tousseName = result.getString(1); +// if(optionMap.size() == 0 || optionMap.get(tousseName) != null){ +// int tousseAmount = result.getInt(2); +// +// TousseDeliverDetailVO vo = voMap.get(tousseName); +// if(vo == null){ +// vo = new TousseDeliverDetailVO(); +// voMap.put(tousseName, vo); +// } +// vo.setTousseName(tousseName); +// vo.setTousseAmount(tousseAmount); +// Integer materialAmount = getMaterialAmountByTousse(tousseName,map); +// vo.setMaterialAmount(materialAmount*tousseAmount); +// } +// } +// } catch (SQLException e) { +// e.printStackTrace(); +// }finally { +// DatabaseUtil.closeResultSetAndStatement(result); +// } +// List voList = new ArrayList(); +// int serialNum = 0; +// if(voMap.size() > 0){ +// for (String tousseName : voMap.keySet()) { +// serialNum++; +// TousseDeliverDetailVO vo = voMap.get(tousseName); +// vo.setSerialNumber(serialNum); +// voList.add(voMap.get(tousseName)); +// } +// } +// return voList; +// } + + //（陈家儒改） @Override public List getTousseDeliverDetailData(String startTime, String endTime,String departCoding) { startTime += " 00:00:00"; endTime += " 23:59:59"; - Map optionMap = new HashMap(); GoodsOption option = goodsOptionManager.getGoodsOption(GoodsOption.MODEL_TOUSSEDELIVERDETAIL, departCoding); - if(option != null && StringUtils.isNotBlank(option.getValue())){ - for(String goodsName: option.getValue().split(";")){ - optionMap.put(goodsName, 1); + + String[] optionSqlArray = new String[0]; + Map voMap = new HashMap(); + + if(option != null && StringUtils.isNotBlank(option.getValue())) { + + String[] valueArray = option.getValue().split(";"); + int valueArraySize = valueArray.length; + int optionSqlArraySize = (int) Math.ceil(valueArraySize/1000f); + optionSqlArray = new String[optionSqlArraySize]; + + + for (int i = 0; i < optionSqlArraySize; i ++) { + int startIndex = i * 1000; + int endIndex = (i == (optionSqlArraySize - 1) ? valueArraySize : (i + 1) * 1000); + String optionSql = ""; + for (int j = startIndex; j < endIndex; j ++) { + String tousseName = valueArray[j]; + optionSql += "'" + tousseName + "',"; + } + optionSql = optionSql.substring(0, optionSql.length() - 1); + optionSqlArray[i] = optionSql; } + } - Map voMap = new HashMap(); - //回收 - String sql2 = "select i.tousseName,sum(i.amount) from RecyclingRecord r,TousseItem i" + + + //回收的数量（按器械包算） + String sql1 = "select i.tousseName,sum(i.amount) " + + "from RecyclingRecord r,TousseItem i" + " where r.id = i.recyclingRecord_id and r.orgUnitCoding = '"+departCoding + "' and r.recyclingTime between "+dateQueryAdapter.dateConverAdapter2(startTime,"yyyy-mm-dd HH24:MI:SS") + " and "+dateQueryAdapter.dateConverAdapter2(endTime,"yyyy-mm-dd HH24:MI:SS") - + " group by i.tousseName "; + + " group by i.tousseName" + + (optionSqlArray.length == 0 ? " having i.tousseName in('')" : createInFilterSql("i.tousseName", optionSqlArray)) + ; + ResultSet result1 = objectDao.executeSql(sql1); + try { + while(result1.next()){ + String tousseName = result1.getString(1); + int tousseAmount = result1.getInt(2); + + TousseDeliverDetailVO vo = voMap.get(tousseName); + if(vo == null){ + vo = new TousseDeliverDetailVO(); + vo.setTousseName(tousseName); + voMap.put(tousseName, vo); + } + vo.setRecyclingAmount(tousseAmount); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(result1); + } + + + //清洗的数量（按材料算） + String sql2 = + "select t.tousseName, sum(t.materialCount) materialCount " + + "from (" + + "(select ci.tousseNameForMaterial tousseName, sum(ci.amount) materialCount " + + "from washAndDisinfectRecord wr, classifyBasket_washRecord cw, classifyBasket cb, classifiedItem ci " + + "where wr.id = cw.washanddisinfectrecord_id and cw.classifybasket_id = cb.id and ci.classifybasket_id = cb.id and ci.itemType = '材料' " + + "and wr.orgunitcoding='" + departCoding + "' "+ + "and wr.endDate between " + dateQueryAdapter.dateConverAdapter2(startTime, "yyyy-mm-dd HH24:MI:SS") + + " and " + dateQueryAdapter.dateConverAdapter2(endTime, "yyyy-mm-dd HH24:MI:SS") + + " group by ci.tousseNameForMaterial)" + + + " union all " + + + "(select ci.name tousseName, sum(ci.amount*mi.count) materialCount " + + "from washAndDisinfectRecord wr, classifyBasket_washRecord cw, classifyBasket cb, classifiedItem ci, tousseDefinition td, materialInstance mi " + + "where cw.washanddisinfectrecord_id = wr.id and cw.classifybasket_id = cb.id and ci.classifybasket_id = cb.id and ci.tousseDefinition_id = td.id and mi.tousse_id = td.id and ci.itemType != '材料' " + + "and wr.orgunitcoding='" + departCoding + "' "+ + "and wr.endDate between " + dateQueryAdapter.dateConverAdapter2(startTime, "yyyy-mm-dd HH24:MI:SS") + + " and " + dateQueryAdapter.dateConverAdapter2(endTime, "yyyy-mm-dd HH24:MI:SS") + + " group by ci.name)" + + ") t " + + "group by t.tousseName" + + (optionSqlArray.length == 0 ? " having t.tousseName in('')" : createInFilterSql("t.tousseName", optionSqlArray)) + ; + + + ResultSet result2 = objectDao.executeSql(sql2); try { while(result2.next()){ String tousseName = result2.getString(1); - tousseName = CssdUtils.filterDisinfectGoodsName(tousseName); - if(optionMap.size() == 0 || optionMap.get(tousseName) != null){ - int tousseAmount = result2.getInt(2); - TousseDeliverDetailVO vo = voMap.get(tousseName); - if(vo == null){ - vo = new TousseDeliverDetailVO(); - voMap.put(tousseName, vo); - } + int materialCount = result2.getInt(2); + + TousseDeliverDetailVO vo = voMap.get(tousseName); + if(vo == null){ + vo = new TousseDeliverDetailVO(); vo.setTousseName(tousseName); - vo.setRecyclingAmount(tousseAmount); + voMap.put(tousseName, vo); } + vo.setWashAndDisinfectAmount(materialCount); } } catch (SQLException e) { e.printStackTrace(); }finally { DatabaseUtil.closeResultSetAndStatement(result2); } - //装配 - String sql3 = "select i.tousseName,count(*) from PackingRecord r,tousseInstance i" + + + //装配的数量（按器械包算） + String sql3 = "select i.tousseName,count(*) tousseAmount from PackingRecord r,tousseInstance i" + " where r.id = i.packingRecord_id and r.orgUnitCoding = '"+departCoding + "' and r.packTime between "+dateQueryAdapter.dateConverAdapter2(startTime,"yyyy-mm-dd HH24:MI:SS") + " and "+dateQueryAdapter.dateConverAdapter2(endTime,"yyyy-mm-dd HH24:MI:SS") - + " group by i.tousseName "; + + " group by i.tousseName" + + (optionSqlArray.length == 0 ? " having i.tousseName in('')" : createInFilterSql("i.tousseName", optionSqlArray)) + ; ResultSet result3 = objectDao.executeSql(sql3); try { while(result3.next()){ String tousseName = result3.getString(1); - tousseName = CssdUtils.filterDisinfectGoodsName(tousseName); - if(optionMap.size() == 0 || optionMap.get(tousseName) != null){ - int tousseAmount = result3.getInt(2); - TousseDeliverDetailVO vo = voMap.get(tousseName); - if(vo == null){ - vo = new TousseDeliverDetailVO(); - voMap.put(tousseName, vo); - } + int tousseAmount = result3.getInt(2); + + TousseDeliverDetailVO vo = voMap.get(tousseName); + if(vo == null){ + vo = new TousseDeliverDetailVO(); vo.setTousseName(tousseName); - vo.setPackingAmount(tousseAmount); + voMap.put(tousseName, vo); } + vo.setPackingAmount(tousseAmount); } } catch (SQLException e) { e.printStackTrace(); }finally { DatabaseUtil.closeResultSetAndStatement(result3); } - //审核 + //审核的数量（按器械包算） String sql4 = "select i.tousseName,count(*) from tousseInstance i" + " where i.orgUnitCoding = '"+departCoding + "' and i.reviewTime between "+dateQueryAdapter.dateConverAdapter2(startTime,"yyyy-mm-dd HH24:MI:SS") + " and "+dateQueryAdapter.dateConverAdapter2(endTime,"yyyy-mm-dd HH24:MI:SS") - + " group by i.tousseName "; + + " group by i.tousseName" + + (optionSqlArray.length == 0 ? " having i.tousseName in('')" : createInFilterSql("i.tousseName", optionSqlArray)) + ; ResultSet result4 = objectDao.executeSql(sql4); try { while(result4.next()){ String tousseName = result4.getString(1); - tousseName = CssdUtils.filterDisinfectGoodsName(tousseName); - if(optionMap.size() == 0 || optionMap.get(tousseName) != null){ - int tousseAmount = result4.getInt(2); - TousseDeliverDetailVO vo = voMap.get(tousseName); - if(vo == null){ - vo = new TousseDeliverDetailVO(); - voMap.put(tousseName, vo); - } + int tousseAmount = result4.getInt(2); + + TousseDeliverDetailVO vo = voMap.get(tousseName); + if(vo == null){ + vo = new TousseDeliverDetailVO(); vo.setTousseName(tousseName); - vo.setReviewAmount(tousseAmount); + voMap.put(tousseName, vo); } + vo.setReviewAmount(tousseAmount); } } catch (SQLException e) { e.printStackTrace(); } finally { DatabaseUtil.closeResultSetAndStatement(result4); } - //灭菌 + //灭菌的数量（按器械包算） String sql5 = "select i.tousseName,count(*) from SterilizationRecord r, tousseInstance i" + " where r.id = i.sterilizationRecord_id and i.orgUnitCoding = '"+departCoding + "' and r.endDate between "+dateQueryAdapter.dateConverAdapter2(startTime,"yyyy-mm-dd HH24:MI:SS") + " and "+dateQueryAdapter.dateConverAdapter2(endTime,"yyyy-mm-dd HH24:MI:SS") - + " group by i.tousseName "; + + " group by i.tousseName" + + (optionSqlArray.length == 0 ? " having i.tousseName in('')" : createInFilterSql("i.tousseName", optionSqlArray)) + ; ResultSet result5 = objectDao.executeSql(sql5); try { while(result5.next()){ String tousseName = result5.getString(1); - tousseName = CssdUtils.filterDisinfectGoodsName(tousseName); - if(optionMap.size() == 0 || optionMap.get(tousseName) != null){ - int tousseAmount = result5.getInt(2); - TousseDeliverDetailVO vo = voMap.get(tousseName); - if(vo == null){ - vo = new TousseDeliverDetailVO(); - voMap.put(tousseName, vo); - } - vo.setTousseName(tousseName); - vo.setSterilizationAmount(tousseAmount); - } + int tousseAmount = result5.getInt(2); + + TousseDeliverDetailVO vo = voMap.get(tousseName); + vo.setSterilizationAmount(tousseAmount); } } catch (SQLException e) { e.printStackTrace(); } finally { DatabaseUtil.closeResultSetAndStatement(result5); } - //发货 - String sql = " select ti.tousseName,count(*) from invoice i,TousseInstance ti" - + " where i.id = ti.invoice_id and i.orgUnitCoding = '" + departCoding - + "' and i.sendTime between "+dateQueryAdapter.dateConverAdapter2(startTime,"yyyy-mm-dd HH24:MI:SS") - + " and "+dateQueryAdapter.dateConverAdapter2(endTime,"yyyy-mm-dd HH24:MI:SS") - + " group by ti.tousseName "; + //发货的数量（按器械包和材料都要算） + String sql6 = + "select ti.tousseName, count(ti.tousseName) tousseAmount, sum(mi.count) materialAmount " + + "from invoice i, TousseInstance ti, TousseDefinition td, MaterialInstance mi " + + "where i.id = ti.invoice_id and ti.toussedefinition_id = td.id and td.id = mi.tousse_id " + + "and i.orgUnitCoding = '" + departCoding + "' " + + "and i.sendTime between " + dateQueryAdapter.dateConverAdapter2(startTime, "yyyy-mm-dd HH24:MI:SS") + + " and " + dateQueryAdapter.dateConverAdapter2(endTime, "yyyy-mm-dd HH24:MI:SS") + + " group by ti.tousseName" + + (optionSqlArray.length == 0 ? " having ti.tousseName in('')" : createInFilterSql("ti.tousseName", optionSqlArray)) + ; - ResultSet result = objectDao.executeSql(sql); - Map map = new HashMap(); + ResultSet result6 = objectDao.executeSql(sql6); try { - while(result.next()){ - String tousseName = result.getString(1); - if(optionMap.size() == 0 || optionMap.get(tousseName) != null){ - int tousseAmount = result.getInt(2); - - TousseDeliverDetailVO vo = voMap.get(tousseName); - if(vo == null){ - vo = new TousseDeliverDetailVO(); - voMap.put(tousseName, vo); - } + while(result6.next()){ + String tousseName = result6.getString(1); + int tousseAmount = result6.getInt(2); + int materialAmount = result6.getInt(3); + + + TousseDeliverDetailVO vo = voMap.get(tousseName); + if(vo == null){ + vo = new TousseDeliverDetailVO(); vo.setTousseName(tousseName); - vo.setTousseAmount(tousseAmount); - Integer materialAmount = getMaterialAmountByTousse(tousseName,map); - vo.setMaterialAmount(materialAmount*tousseAmount); + voMap.put(tousseName, vo); } + vo.setTousseAmount(tousseAmount); + vo.setMaterialAmount(materialAmount); } } catch (SQLException e) { e.printStackTrace(); }finally { - DatabaseUtil.closeResultSetAndStatement(result); + DatabaseUtil.closeResultSetAndStatement(result6); } + + List voList = new ArrayList(); int serialNum = 0; if(voMap.size() > 0){ Index: ssts-web/src/main/webapp/jasperRtp/tousseDeliverDetailReport.jrxml =================================================================== diff -u -r13201 -r15111 --- ssts-web/src/main/webapp/jasperRtp/tousseDeliverDetailReport.jrxml (.../tousseDeliverDetailReport.jrxml) (revision 13201) +++ ssts-web/src/main/webapp/jasperRtp/tousseDeliverDetailReport.jrxml (.../tousseDeliverDetailReport.jrxml) (revision 15111) @@ -1,6 +1,6 @@ - - + +