Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r15652 -r15666 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 15652) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 15666) @@ -9473,183 +9473,9 @@ 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) { + public List getTousseDeliverDetailData(String startTime, String endTime,String departCoding) { //（cjr） startTime += " 00:00:00"; endTime += " 23:59:59"; @@ -9729,7 +9555,7 @@ "(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 != '材料' " + + "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") + @@ -9845,15 +9671,16 @@ DatabaseUtil.closeResultSetAndStatement(result5); } - //发货的数量（按器械包和材料都要算） + //发货的数量（按器械包算） 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" + "select ti.tousseName tousseName," + + "count(ti.tousseName) tousseAmount " + + "from Invoice i " + + "inner join TousseInstance ti on ti.invoice_id=i.id " + + "where 1=1 " + + "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)) ; @@ -9862,7 +9689,6 @@ while(result6.next()){ String tousseName = result6.getString(1); int tousseAmount = result6.getInt(2); - int materialAmount = result6.getInt(3); TousseDeliverDetailVO vo = voMap.get(tousseName); @@ -9872,7 +9698,6 @@ voMap.put(tousseName, vo); } vo.setTousseAmount(tousseAmount); - vo.setMaterialAmount(materialAmount); } } catch (SQLException e) { e.printStackTrace(); @@ -9881,6 +9706,43 @@ } + //发货的数量（按材料算） + String sql7 = + "select ti.tousseName tousseName," + + "sum(mi.count) materialAmount " + + "from Invoice i " + + "inner join TousseInstance ti on ti.invoice_id=i.id " + + "inner join TousseDefinition td on td.id=ti.tousseDefinition_id " + + "inner join MaterialInstance mi on mi.tousse_id=td.id " + + "where 1=1 " + + "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 result7 = objectDao.executeSql(sql7); + try { + while(result7.next()){ + String tousseName = result7.getString(1); + int materialAmount = result7.getInt(2); + + + TousseDeliverDetailVO vo = voMap.get(tousseName); + if(vo == null){ + vo = new TousseDeliverDetailVO(); + vo.setTousseName(tousseName); + voMap.put(tousseName, vo); + } + vo.setMaterialAmount(materialAmount); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(result7); + } + + List voList = new ArrayList(); int serialNum = 0; if(voMap.size() > 0){ @@ -9891,6 +9753,7 @@ voList.add(voMap.get(tousseName)); } } + return voList; }