Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r19412 -r19442 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 19412) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 19442) @@ -3140,6 +3140,7 @@ titleName = "应急处理器械次数"; addWorkQualityCollectionBean(list,titleName,sql,recAmountMap); // 普通器械清洗不合格率 + // 总数量 按照回收时科室不等于“手术一区、手术二区、手术三区”的器械包材料清洗总件数。并且器械包定义的器械包分组不等于“专科” String totalSql = "select " +dateQueryAdapter.dateConverAdapter3("rr.recyclingTime","mm") + " month,sum(ritem.amount*tdc.amount) from RecyclingRecord rr join RecyclingItem ritem on ritem.recyclingRecord_id = rr.id " + " join (select td.id tid,td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td " @@ -3163,6 +3164,7 @@ titleName = "普通器械清洗不合格率 "; addWorkQualityCollectionBean(list,titleName,sql,totalmap); // 专科器械清洗不合格率 + // 总数量 按照器械包定义的器械包分组等于“专科” 的器械材料清洗总件数 totalSql = "select " +dateQueryAdapter.dateConverAdapter3("cb.recyclingTime","mm") + " month,sum(cit.amount*tdc.amount) from ClassifyBasket cb " + " join ClassifyBasket_WashRecord cw on cb.id=cw.ClassifyBasket_ID join WashAndDisinfectRecord wr on cw.WashAndDisinfectRecord_ID=wr.id " @@ -3182,6 +3184,7 @@ titleName = "专科器械清洗不合格率"; addWorkQualityCollectionBean(list,titleName,sql,totalmap); // 手术器械清洗不合格率 + // 总数量：按照回收科室等于“手术一区、手术二区、手术三区”的器械材料清洗总件数 totalSql = "select " +dateQueryAdapter.dateConverAdapter3("rr.recyclingTime","mm") + " month,sum(ritem.amount*tdc.amount) from RecyclingRecord rr join RecyclingItem ritem on ritem.recyclingRecord_id = rr.id " + " join (select td.id tid,td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td where mi.tousse_id=td.id and td.forDisplay=1 group by td.id,td.name) tdc on tdc.tid=ritem.tousseDefinitionId " @@ -8833,86 +8836,137 @@ String month = year + "-" + monthFormart; - // 处理手术器械总件数 ,数据统计为申请时科室是手术室，并且包装类型不包含纸塑，外来器械也不包括在内。 -// String sql = " select sum(tdc.amount) from Invoice i,TousseInstance ti,TousseDefinition td,(select td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td " -// + " where mi.tousse_id=td.id and td.forDisplay=1 group by td.name) tdc " -// + " where i.id=ti.invoice_id and ti.tousseDefinition_id=td.id " -// + " and tdc.tdName = td.name " -// + " and ti.depart = '手术室' " -// + " and td.packageType != '纸塑' and td.tousseType != '外来器械包' and td.tousseType != '外来器械拆分小包' " -// + " and td.tousseType != '消毒物品' and td.tousseType != '敷料包' " -// + " and td.name not like '%电钻%' and td.name not like '%呼吸管道%' " -// + " and i.sendTime between " + startDay +" and "+endDay; - // 处理器械总件数 // 数据获取：不包含外来器械申请单、代理灭菌单、消毒物品、敷料包。包类型只针对器械包， // 所有包装类型都可以统计。申请科室没有限制。以回收为准。 // 数量统计为回收的器械包的材料数量 - String sql = "select sum(tdc.amount*ri.amount) from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " - + " join (select td.id tid,td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td where mi.tousse_id=td.id and td.forDisplay=1 group by td.id,td.name) tdc " - + " on tdc.tid=td.id" - + " left join invoicePlan ip on rr.recyclingApplication_id=ip.id" - + " where (ip.id is null or (ip.id is not null and ip.type != '" + InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION + "' and ip.type != '" + InvoicePlan.TYPE_PROXYDISINFECTION + "' and ip.type != '" +InvoicePlan.TYPE_DRESSING_APPLICATION_FROM+ "' and ip.type != '" +InvoicePlan.TYPE_DISINFECT_GOODS_APPLICATION_FORM+ "'))" - + " and td.tousseType != '" +TousseDefinition.PACKAGE_TYPE_DISINFECTION + "' and td.tousseType != '" + TousseDefinition.PACKAGE_TYPE_DRESSING+ "'" - + " and rr.recyclingTime between " + startDay +" and "+endDay; +// String sql = "select sum(tdc.amount*ri.amount) from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " +// + " join (select td.id tid,td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td where mi.tousse_id=td.id and td.forDisplay=1 group by td.id,td.name) tdc " +// + " on tdc.tid=td.id" +// + " left join invoicePlan ip on rr.recyclingApplication_id=ip.id" +// + " where (ip.id is null or (ip.id is not null and ip.type != '" + InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION + "' and ip.type != '" + InvoicePlan.TYPE_PROXYDISINFECTION + "' and ip.type != '" +InvoicePlan.TYPE_DRESSING_APPLICATION_FROM+ "' and ip.type != '" +InvoicePlan.TYPE_DISINFECT_GOODS_APPLICATION_FORM+ "'))" +// + " and td.tousseType != '" +TousseDefinition.PACKAGE_TYPE_DISINFECTION + "' and td.tousseType != '" + TousseDefinition.PACKAGE_TYPE_DRESSING+ "'" +// + " and rr.recyclingTime between " + startDay +" and "+endDay; + // Integer amount = getAmountBySql(sql); // String columnName = "处理器械总件数 "; // YearWorkloadBean bean = new YearWorkloadBean(i,month, 1, columnName, amount); - list.add(getYearWorkloadBean(i,month, 1, "处理器械总件数", sql)); +// list.add(getYearWorkloadBean(i,month, 1, "处理器械总件数", sql)); // 处理器械总包数 //数据获取：不包含外来器械申请单、代理灭菌单、消毒物品、敷料包。包类型只针对器械包， // 所有包装类型都可以统计。申请科室没有限制。数量统计为回收的器械包的包数 - sql = "select sum(ri.amount) from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " - + " left join invoicePlan ip on rr.recyclingApplication_id=ip.id" - + " where (ip.id is null or (ip.id is not null and ip.type != '" + InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION + "' and ip.type != '" + InvoicePlan.TYPE_PROXYDISINFECTION + "' and ip.type != '" +InvoicePlan.TYPE_DRESSING_APPLICATION_FROM+ "' and ip.type != '" +InvoicePlan.TYPE_DISINFECT_GOODS_APPLICATION_FORM+ "'))" - + " and td.tousseType != '" +TousseDefinition.PACKAGE_TYPE_DISINFECTION + "' and td.tousseType != '" + TousseDefinition.PACKAGE_TYPE_DRESSING+ "'" - + " and rr.recyclingTime between " + startDay +" and "+endDay; - list.add(getYearWorkloadBean(i,month, 2, "处理器械总包数 ", sql)); +// sql = "select sum(ri.amount) from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " +// + " left join invoicePlan ip on rr.recyclingApplication_id=ip.id" +// + " where (ip.id is null or (ip.id is not null and ip.type != '" + InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION + "' and ip.type != '" + InvoicePlan.TYPE_PROXYDISINFECTION + "' and ip.type != '" +InvoicePlan.TYPE_DRESSING_APPLICATION_FROM+ "' and ip.type != '" +InvoicePlan.TYPE_DISINFECT_GOODS_APPLICATION_FORM+ "'))" +// + " and td.tousseType != '" +TousseDefinition.PACKAGE_TYPE_DISINFECTION + "' and td.tousseType != '" + TousseDefinition.PACKAGE_TYPE_DRESSING+ "'" +// + " and rr.recyclingTime between " + startDay +" and "+endDay; + // 包装器械总包数。数据获取改成装配总包数，除了外来器械包其余所有的都统计在内，包含敷料、消毒物品、纸塑包 + String sql = "select count(*) from tousseInstance ti,tousseDefinition td " + + " where ti.tousseDefinition_id=td.id " + + " and td.tousseType != '" + TousseDefinition.PACKAGE_TYPE_FOREIGN + "'" + + " and td.tousseType != '" + TousseDefinition.PACKAGE_TYPE_SPLIT + "'" + + " and ti.operationTime between " + startDay +" and " + endDay; + list.add(getYearWorkloadBean(i,month, 2, "包装器械总包数", sql)); // 处理手术室器械总件数 申请科室为“手术室” 只统计包类型为“器械包” 统计回收器械包的材料数量 - sql = " select sum(tdc.amount*ri.amount) from RecyclingRecord rr,RecyclingItem ri,TousseDefinition td, " - + "(select td.id tid,td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td where mi.tousse_id=td.id and td.forDisplay=1 group by td.id,td.name) tdc " - + " where rr.id=ri.recyclingRecord_id and ri.tousseDefinitionId=td.id and td.id=tdc.tid and rr.depart='手术室' " - + " and (td.tousseType='器械包') " - + " and rr.recyclingTime between " + startDay +" and "+endDay; - list.add(getYearWorkloadBean(i,month, 3, "处理手术室器械总件数", sql)); +// sql = " select sum(tdc.amount*ri.amount) from RecyclingRecord rr,RecyclingItem ri,TousseDefinition td, " +// + "(select td.id tid,td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td where mi.tousse_id=td.id and td.forDisplay=1 group by td.id,td.name) tdc " +// + " where rr.id=ri.recyclingRecord_id and ri.tousseDefinitionId=td.id and td.id=tdc.tid and rr.depart='手术室' " +// + " and (td.tousseType='器械包') " +// + " and rr.recyclingTime between " + startDay +" and "+endDay; + // 回收科室等于手术室的器械清洗总件数，不包含外来器械 + sql = "select sum(ritem.amount*tdc.amount) from RecyclingRecord rr join RecyclingItem ritem on ritem.recyclingRecord_id = rr.id " + + " join (select td.id tid,td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td " + + " where mi.tousse_id=td.id and td.forDisplay=1 and( td.tousseType is null or td.tousseType != '" + TousseDefinition.PACKAGE_TYPE_FOREIGN + "') group by td.id,td.name) tdc on tdc.tid=ritem.tousseDefinitionId " + + " join ClassifyBasket_RecyclingRecord cr on rr.id=cr.RecyclingRecord_ID " + + " join ClassifyBasket cb on cr.ClassifyBasket_ID=cb.id join ClassifyBasket_WashRecord cw on cb.id=cw.ClassifyBasket_ID " + + " join WashAndDisinfectRecord wr on cw.WashAndDisinfectRecord_ID=wr.id " + + " where rr.depart is not null and rr.depart = '手术室' and wr.id is not null " + + " and ritem.tousseDefinitionId in (select cit.tousseDefinitionID from ClassifyBasket clb,ClassifiedItem cit where clb.id=cit.classifybasket_id and cb.id=clb.id) " + + " and rr.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (rr.recyclingTime between "+ startDay +" and " + endDay + ")"; + + Integer amountOpera = getAmountBySql(sql); + String columnNameOperta = "清洗手术室器械总件数"; + YearWorkloadBean beanOpera = new YearWorkloadBean(i,month, 4, columnNameOperta, amountOpera); + list.add(beanOpera); + +// list.add(getYearWorkloadBean(i,month, 3, "清洗手术室器械总件数", sql)); // 处理临床科室物品总件数，数据获取器械包申请时科室不等于手术室的 - String sql10 = " select sum(tdc.amount) from Invoice i,TousseInstance ti,TousseDefinition td,(select td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td " - + " where mi.tousse_id=td.id and td.forDisplay=1 group by td.name) tdc " - + " where i.id=ti.invoice_id and ti.tousseDefinition_id=td.id " - + " and tdc.tdName = td.name " - + " and (ti.depart is null or ti.depart != '手术室') " - + " and i.sendTime between " + startDay +" and "+endDay; + // 回收科室不等于“手术室”的器械清洗总件数 +// String sql10 = " select sum(tdc.amount) from Invoice i,TousseInstance ti,TousseDefinition td,(select td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td " +// + " where mi.tousse_id=td.id and td.forDisplay=1 group by td.name) tdc " +// + " where i.id=ti.invoice_id and ti.tousseDefinition_id=td.id " +// + " and tdc.tdName = td.name " +// + " and (ti.depart is null or ti.depart != '手术室') " +// + " and i.sendTime between " + startDay +" and "+endDay; + String sql10 = "select sum(ritem.amount*tdc.amount) from RecyclingRecord rr join RecyclingItem ritem on ritem.recyclingRecord_id = rr.id " + + " join (select td.id tid,td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td " + + " where mi.tousse_id=td.id and td.forDisplay=1 group by td.id,td.name) tdc on tdc.tid=ritem.tousseDefinitionId " + + " join ClassifyBasket_RecyclingRecord cr on rr.id=cr.RecyclingRecord_ID " + + " join ClassifyBasket cb on cr.ClassifyBasket_ID=cb.id join ClassifyBasket_WashRecord cw on cb.id=cw.ClassifyBasket_ID " + + " join WashAndDisinfectRecord wr on cw.WashAndDisinfectRecord_ID=wr.id " + + " where rr.depart != '手术室' and wr.id is not null " + + " and ritem.tousseDefinitionId in (select cit.tousseDefinitionID from ClassifyBasket clb,ClassifiedItem cit where clb.id=cit.classifybasket_id and cb.id=clb.id) " + + " and rr.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (rr.recyclingTime between "+ startDay +" and " + endDay + ")"; + Integer amount10 = getAmountBySql(sql10); - String columnName10 = "处理临床科室物品总件数"; + String columnName10 = "清洗临床科室物品总件数"; YearWorkloadBean bean10 = new YearWorkloadBean(i,month, 4, columnName10, amount10); list.add(bean10); + YearWorkloadBean materialTotal = new YearWorkloadBean(i,month, 1, "清洗器械总件数", amountOpera + amount10); + list.add(materialTotal); +// list.add(getYearWorkloadBean(i,month, 1, "处理器械总件数", sql)); //“处理外来手术器械总包件数”： //数据获取为：统计外来器械包回收的包数量，申请科室为“手术室” - sql = "select sum(ri.amount*tdc.amount) from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " +// sql = "select sum(ri.amount*tdc.amount) from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " +// + " join invoicePlan ip on rr.recyclingApplication_id=ip.id" +// + " join (select td.id tid,td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td where mi.tousse_id=td.id group by td.id,td.name) tdc " +// + " on tdc.tid=td.id" +// + " where ip.depart='手术室' and td.tousseType= '" + TousseDefinition.PACKAGE_TYPE_FOREIGN + "'" +// + " and rr.recyclingTime between " + startDay +" and "+endDay; + + sql = "select sum(ritem.amount*tdc.amount) from RecyclingRecord rr join RecyclingItem ritem on ritem.recyclingRecord_id = rr.id " + + " join (select td.id tid,td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td " + + " where mi.tousse_id=td.id and(td.tousseType = '" + TousseDefinition.PACKAGE_TYPE_FOREIGN + "') group by td.id,td.name) tdc on tdc.tid=ritem.tousseDefinitionId " + + " join ClassifyBasket_RecyclingRecord cr on rr.id=cr.RecyclingRecord_ID " + + " join ClassifyBasket cb on cr.ClassifyBasket_ID=cb.id join ClassifyBasket_WashRecord cw on cb.id=cw.ClassifyBasket_ID " + + " join WashAndDisinfectRecord wr on cw.WashAndDisinfectRecord_ID=wr.id " + " join invoicePlan ip on rr.recyclingApplication_id=ip.id" - + " join (select td.id tid,td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td where mi.tousse_id=td.id group by td.id,td.name) tdc " - + " on tdc.tid=td.id" - + " where rr.depart='手术室' and td.tousseType= '" + TousseDefinition.PACKAGE_TYPE_FOREIGN + "'" - + " and rr.recyclingTime between " + startDay +" and "+endDay; - list.add(getYearWorkloadBean(i,month, 5, "处理外来手术器械总件数", sql)); + + " where ip.depart is not null and ip.depart = '手术室' and wr.id is not null " + + " and ritem.tousseDefinitionId in (select cit.tousseDefinitionID from ClassifyBasket clb,ClassifiedItem cit where clb.id=cit.classifybasket_id and cb.id=clb.id) " + + " and rr.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (rr.recyclingTime between "+ startDay +" and " + endDay + ")"; + list.add(getYearWorkloadBean(i,month, 5, "清洗外来手术器械总件数", sql)); //“处理外来手术器械总包数”： - //数据获取为：统计外来器械包回收的包数量，申请科室为“手术室” + // 数据获取为：统计外来器械包回收的包数量，申请科室为“手术室” + // 包装外来手术器械总包数。数据获取：二次回收的外来器械不统计 sql = "select sum(ri.amount) from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " + " join invoicePlan ip on rr.recyclingApplication_id=ip.id " + " where rr.depart='手术室' and td.tousseType= '" + TousseDefinition.PACKAGE_TYPE_FOREIGN + "'" + + " and rr.id = (select max(rr1.id) from RecyclingRecord rr1,ForeignTousseApplication fta1 where rr1.recyclingApplication_id=fta1.id and fta1.id=ip.id) " + " and rr.recyclingTime between " + startDay +" and "+endDay; - list.add(getYearWorkloadBean(i,month, 6, "处理外来手术器械总包数", sql)); + list.add(getYearWorkloadBean(i,month, 6, "包装外来手术器械总包数", sql)); /****************************************灭菌炉***************************************/ //清洗机使用炉次”： //数据获取为：所有清洗机的运作次数 - sql = "select count(*) from WashAndDisinfectRecord wr " - + " where wr.startDate between " + startDay +" and "+endDay; + sql = "select count(*) from WashAndDisinfectRecord wr, Rinser r " + + " where r.id=wr.rinserId " + + " and r.name not like '%手工%' " + + " and wr.startDate between " + startDay +" and "+endDay; list.add(getYearWorkloadBean(i,month, 8, "清洗机使用炉次", sql)); + // 清洗消毒物品总件数：数据获取：清洗消毒物品类型材料的总件数 + sql = "select sum(wr.disinfectMaterialAmount) from WashAndDisinfectRecord wr , Rinser r " + + " where r.id=wr.rinserId " + + " and r.orgUnitCoding = '" + querySupplyRoom + "'" + + " and wr.startDate between " + startDay +" and "+endDay; + list.add(getYearWorkloadBean(i,month, 8, "清洗消毒物品总件数", sql)); + // 处理纸塑包总包数 String sql7 = " select count(*) from Invoice i,TousseInstance ti,TousseDefinition td " + " where i.id=ti.invoice_id and ti.tousseDefinition_id=td.id and ti.orgUnitCoding = '" + querySupplyRoom + "'" @@ -9012,6 +9066,10 @@ + startDay + " and " + endDay + " and sr.orgUnitCoding = '"+querySupplyRoom+"'" + " and s.ownGroup = '" + ownGroup + "'"; + if(ownGroup.contains("低温") || ownGroup.contains("高温")){ + // 如果是高温和低温组，灭菌炉名称不包含'4#' + sql3 += " and s.name not like '%4#%'"; + } String columnName3 = "灭菌器使用炉次 (" + ownGroup + ")"; Integer amount3 = getAmountBySql(sql3); YearWorkloadBean bean3 = new YearWorkloadBean(i,month, 7, columnName3, amount3);