Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/WorkQualityCollectionDataIndex.java =================================================================== diff -u -r25875 -r26203 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/WorkQualityCollectionDataIndex.java (.../WorkQualityCollectionDataIndex.java) (revision 25875) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/WorkQualityCollectionDataIndex.java (.../WorkQualityCollectionDataIndex.java) (revision 26203) @@ -206,6 +206,20 @@ return map; } /** + * 获取清洗数量（按材料数量统计）的map，按月份进行分组 + * @param reportParams 查询参数，包括时间范围，按照回收时科室名称不等于麻醉手术科的总数计算，xjjqzyy定制的工作质量持续收集统计报表使用 + * @return + */ + public Map getWashAmountByMaterialMapForXjjqzyy(ReportQueryParams reportParams){ + Map map = new HashMap(); + + reportParams.monthlyStr = dateQueryAdapter.dateConverAdapter3("wr.endDate","mm"); + String sql = dataIndex.getWorkAmountByMaterialSQL("加回收科室过滤的清洗数量", + reportParams); + map = getMonthAmountMapBySql(sql); + return map; + } + /** * 获取装配数量的map，12个月的数据 * @param querySupplyRoom * @param year Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfXjjqzyy.java =================================================================== diff -u -r25808 -r26203 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfXjjqzyy.java (.../CustomReportsOfXjjqzyy.java) (revision 25808) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfXjjqzyy.java (.../CustomReportsOfXjjqzyy.java) (revision 26203) @@ -1,12 +1,19 @@ package com.forgon.disinfectsystem.jasperreports.service; +import java.math.BigDecimal; import java.sql.ResultSet; import java.sql.SQLException; +import java.text.ParseException; +import java.util.ArrayList; +import java.util.HashMap; import java.util.LinkedList; import java.util.List; +import java.util.Map; + import org.apache.commons.lang.StringUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Component; + import com.forgon.Constants; import com.forgon.databaseadapter.service.DateQueryAdapter; import com.forgon.disinfectsystem.entity.basedatamanager.materialinstance.MaterialInstance; @@ -17,19 +24,33 @@ import com.forgon.disinfectsystem.entity.tousseitem.TousseItem; import com.forgon.disinfectsystem.jasperreports.javabeansource.ForeignTousseApplicationVO; import com.forgon.disinfectsystem.jasperreports.javabeansource.TousseItemVO; +import com.forgon.disinfectsystem.jasperreports.javabeansource.WorkQualityCollection; +import com.forgon.disinfectsystem.jasperreports.service.dataindex.DataIndex; +import com.forgon.disinfectsystem.jasperreports.service.dataindex.WorkQualityCollectionDataIndex; +import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams; import com.forgon.tools.db.DatabaseUtil; +import com.forgon.tools.db.InitDbConnection; import com.forgon.tools.hibernate.ObjectDao; import com.forgon.tools.string.StringTools; +import com.forgon.tools.util.SqlUtils; @Component(value = "customReportsOfXjjqzyy") -public class CustomReportsOfXjjqzyy { +public class CustomReportsOfXjjqzyy implements WorkQualityCollectionReport{ @Autowired private ObjectDao objectDao; @Autowired private DateQueryAdapter dateQueryAdapter; + @Autowired + private DataIndex dataIndex; + + @Autowired + private WorkQualityCollectionDataIndex workQualityCollectionDataIndex; + + @Autowired + private InitDbConnection dbConnection; /** * 获取xjjqzyy定制的外来器械申请报表数据 * @param startDay 申请开始时间 @@ -233,5 +254,1124 @@ } return list; } + @Override + public List getWorkQualityCollectionList( + String year, String type, String querySupplyRoom, String month, + Map params) throws ParseException, SQLException { + List list = new ArrayList(); + + // 工作量持续统计报表 全年数据的处理 + if("allYear".equals(type)){ + list = getWorkQualityCollectionListAllyear(year,querySupplyRoom,params); + return list; + } + Map> startAndEndDays = null; + if (StringUtils.isNotBlank(year) && StringUtils.isNotBlank(type)) { + startAndEndDays = dataIndex.getStartAndEndDay(year, type, null); + } else { + startAndEndDays = dataIndex.getStartAndEndDay(null, null, month); + } + getWorkQualityCollectionListZsyy(startAndEndDays, list, querySupplyRoom); + return list; + } + /** + * 中大附一的工作质量持续收集统计报表的实现 + * @param startAndEndDays + * @param list + * @param querySupplyRoom + */ + private void getWorkQualityCollectionListZsyy(Map> startAndEndDays,List list,String querySupplyRoom){ + if(startAndEndDays == null || list == null){ + return ; + } + String startDay = null; + String endDay = null; + String sql = null; + String totalSql = null; + ReportQueryParams params = new ReportQueryParams(); + //数据库计算字符串长度的函数名 + String sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); + params.sqlLengthFunctionName = sqlLengthFunctionName; + params.querySupplyRoom = querySupplyRoom; + // 回收时发现器械缺损不配套例数 缺损不配套的数据显示 + WorkQualityCollection bean1 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountContainSql("缺损不配套",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalRecSql(params); + setWorkQualityCollectionData(bean1,key,sql,totalSql); + } + bean1.setTitle("回收时发现器械缺损不配套例数"); + bean1.setRowNum(list.size()+1); + list.add(bean1); + // 回收时发现器械种类数量不相符例数 种类数据不相符 + WorkQualityCollection bean2 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountContainSql("种类数量不相符",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalRecSql(params); + setWorkQualityCollectionData(bean2,key,sql,totalSql); + } + bean2.setTitle("回收时发现器械种类数量不相符例数"); + bean2.setRowNum(list.size()+1); + list.add(bean2); + // 应急处理器械次数 应急处理 + WorkQualityCollection bean3 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountContainSql("应急处理",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalRecSql(params); + setWorkQualityCollectionData(bean3,key,sql,totalSql); + } + bean3.setTitle("应急处理器械次数 "); + bean3.setRowNum(list.size()+1); + list.add(bean3); + // 普通器械清洗不合格率 普通器械清洗不合格 + WorkQualityCollection bean4 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountContainSql("普通器械清洗不合格",startDay,endDay,querySupplyRoom); + totalSql = "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.tousseGroupName is null or td.tousseGroupName != '专科') 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 rr.depart != '手术二区' 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 "+dateQueryAdapter.dateAdapter(startDay)+" and " + dateQueryAdapter.dateAdapter(endDay) + ")"; + setWorkQualityCollectionData(bean4,key,sql,totalSql); + } + bean4.setTitle("普通器械清洗不合格率"); + bean4.setRowNum(list.size()+1); + list.add(bean4); + // 专科器械清洗不合格率 专科器械清洗不合格 + WorkQualityCollection bean5 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountContainSql("专科器械清洗不合格",startDay,endDay,querySupplyRoom); + totalSql = "select 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 " + + " join ClassifiedItem cit on cb.id=cit.classifybasket_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.tousseGroupName = '专科' group by td.id,td.name) tdc on tdc.tid=cit.tousseDefinitionID" + + " where wr.id is not null " + + " and (cb.recyclingTime between "+dateQueryAdapter.dateAdapter(startDay)+" and " + dateQueryAdapter.dateAdapter(endDay) + ")"; + setWorkQualityCollectionData(bean5,key,sql,totalSql); + } + bean5.setTitle("专科器械清洗不合格率"); + bean5.setRowNum(list.size()+1); + list.add(bean5); + // 手术器械清洗不合格率 手术器械清洗不合格 + WorkQualityCollection bean6 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountContainSql("手术器械清洗不合格",startDay,endDay,querySupplyRoom); + totalSql = "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 is not null and (rr.depart = '手术一区' or rr.depart = '手术二区' or 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.recyclingTime between "+dateQueryAdapter.dateAdapter(startDay)+" and " + dateQueryAdapter.dateAdapter(endDay) + ")"; + setWorkQualityCollectionData(bean6,key,sql,totalSql); + } + bean6.setTitle("手术器械清洗不合格率"); + bean6.setRowNum(list.size()+1); + list.add(bean6); + // 无菌包内器械数量不符例数 无菌包内器械数量不符 + WorkQualityCollection bean7 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("无菌包内器械数量不符",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalPackingSql(params); + setWorkQualityCollectionData(bean7,key,sql,totalSql); + } + bean7.setTitle("无菌包内器械数量不符例数"); + bean7.setRowNum(list.size()+1); + list.add(bean7); + // 无菌包内器械功能不全例数 无菌包内器械功能不全 + WorkQualityCollection bean8 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("无菌包内器械功能不全",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalPackingSql(params); + setWorkQualityCollectionData(bean8,key,sql,totalSql); + } + bean8.setTitle("无菌包内器械功能不全例数"); + bean8.setRowNum(list.size()+1); + list.add(bean8); + // 无菌包内器械种类错误例数 无菌包内器械种类错误 + WorkQualityCollection bean9 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("无菌包内器械种类错误",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalPackingSql(params); + setWorkQualityCollectionData(bean9,key,sql,totalSql); + } + bean9.setTitle("无菌包内器械种类错误例数"); + bean9.setRowNum(list.size()+1); + list.add(bean9); + // 包内指示卡欠缺例数 包内指示卡欠缺例数 + WorkQualityCollection bean10 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("包内指示卡欠缺例数",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalPackingSql(params); + setWorkQualityCollectionData(bean10,key,sql,totalSql); + } + bean10.setTitle("包内指示卡欠缺例数"); + bean10.setRowNum(list.size()+1); + list.add(bean10); + // 灭菌包包装密闭性能不合格例数 灭菌包包装密闭性能不合格 + WorkQualityCollection bean11 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("灭菌包包装密闭性能不合格",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalPackingSql(params); + setWorkQualityCollectionData(bean11,key,sql,totalSql); + } + bean11.setTitle("灭菌包包装密闭性能不合格例数"); + bean11.setRowNum(list.size()+1); + list.add(bean11); + // 包外明显污迹例数 污迹 + WorkQualityCollection bean12 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountContainSql("污迹",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalPackingSql(params); + setWorkQualityCollectionData(bean12,key,sql,totalSql); + } + bean12.setTitle("包外明显污迹例数"); + bean12.setRowNum(list.size()+1); + list.add(bean12); + // 无菌包标识不正确例数 无菌包标识不正确 + WorkQualityCollection bean13 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("无菌包标识不正确",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalPackingSql(params); + setWorkQualityCollectionData(bean13,key,sql,totalSql); + } + bean13.setTitle("无菌包标识不正确例数"); + bean13.setRowNum(list.size()+1); + list.add(bean13); + // 灭菌方式选择不正确例数 灭菌方式选择不正确 + WorkQualityCollection bean14 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("灭菌方式选择不正确",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalPackingSql(params); + setWorkQualityCollectionData(bean14,key,sql,totalSql); + } + bean14.setTitle("灭菌方式选择不正确例数"); + bean14.setRowNum(list.size()+1); + list.add(bean14); + // 湿包例数 湿包 + WorkQualityCollection bean15 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("湿包",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalPackingSql(params); + setWorkQualityCollectionData(bean15,key,sql,totalSql); + } + bean15.setTitle("湿包例数"); + bean15.setRowNum(list.size()+1); + list.add(bean15); + // 包内化学指示卡变色不合格例数 包内化学指示卡变色不合格 + WorkQualityCollection bean16 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("包内化学指示卡变色不合格",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalPackingSql(params); + setWorkQualityCollectionData(bean16,key,sql,totalSql); + } + bean16.setTitle("包内化学指示卡变色不合格例数"); + bean16.setRowNum(list.size()+1); + list.add(bean16); + // 无菌物品发放不及时次数 无菌物品发放不及时 + WorkQualityCollection bean17 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("无菌物品发放不及时",startDay,endDay,querySupplyRoom); + totalSql = "select sum(it.amount) from invoice iv,InvoiceItem it,OrgUnit org where iv.id=it.invoiceID and iv.orgUnitCoding=org.orgUnitCoding " + + " and org.name != '手术一区' and org.name != '手术二区' and org.name != '手术三区' " + + " and (iv.sendTime between "+dateQueryAdapter.dateAdapter(startDay)+" and " + dateQueryAdapter.dateAdapter(endDay) + ")"; + setWorkQualityCollectionData(bean17,key,sql,totalSql); + } + bean17.setTitle("无菌物品发放不及时次数"); + bean17.setRowNum(list.size()+1); + list.add(bean17); + // 器械损坏例数 器械损坏 + WorkQualityCollection bean18 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("器械损坏",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalRecSql(params); + setWorkQualityCollectionData(bean18,key,sql,totalSql); + } + bean18.setTitle("器械损坏例数"); + bean18.setRowNum(list.size()+1); + list.add(bean18); + // 器械遗失例数 器械遗失 + WorkQualityCollection bean19 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("器械遗失",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalRecSql(params); + setWorkQualityCollectionData(bean19,key,sql,totalSql); + } + bean19.setTitle("器械遗失例数"); + bean19.setRowNum(list.size()+1); + list.add(bean19); + // 器械包装不合格率 + WorkQualityCollection bean20 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("器械包装不合格",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalPackingSql(params); + setWorkQualityCollectionData(bean20,key,sql,totalSql); + } + bean20.setTitle("器械包装不合格率"); + bean20.setRowNum(list.size()+1); + list.add(bean20); + // 湿包发生率 + WorkQualityCollection bean21 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("湿包登记表",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalPackingSql(params); + setWorkQualityCollectionData(bean21,key,sql,totalSql); + } + bean21.setTitle("湿包发生率"); + bean21.setRowNum(list.size()+1); + list.add(bean21); + // 灭菌失败率 + WorkQualityCollection bean22 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("器械灭菌不合格",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalPackingSql(params); + setWorkQualityCollectionData(bean22,key,sql,totalSql); + } + bean22.setTitle("灭菌失败率"); + bean22.setRowNum(list.size()+1); + list.add(bean22); + // 无菌包内器械功能不全数 + WorkQualityCollection bean23 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("器械功能不全",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalPackingSql(params); + setWorkQualityCollectionData(bean23,key,sql,totalSql); + } + bean23.setTitle("无菌包内器械功能不全数"); + bean23.setRowNum(list.size()+1); + list.add(bean23); + // 器械包标签不合格 + WorkQualityCollection bean24 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("器械包标签不合格",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalPackingSql(params); + setWorkQualityCollectionData(bean24,key,sql,totalSql); + } + bean24.setTitle("器械包标签不合格"); + bean24.setRowNum(list.size()+1); + list.add(bean24); + // 器械包发货不合格 + WorkQualityCollection bean25 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + sql = getWorkUnQualityAmountEqualSql("器械包发货不合格",startDay,endDay,querySupplyRoom); + setBetweenSql(params, startDay, endDay); + totalSql = getTotalInvoiceSql(params); + setWorkQualityCollectionData(bean25,key,sql,totalSql); + } + bean25.setTitle("器械包发货不合格"); + bean25.setRowNum(list.size()+1); + list.add(bean25); + // 临床器械清洗不合格率 + WorkQualityCollection bean26 = new WorkQualityCollection(); + params.extraQuery = " and rr.departCode != '2213' "; + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + setBetweenSql(params, startDay, endDay); + sql = getWorkUnQualityAmountEqualSql("临床器械清洗不合格",startDay,endDay,querySupplyRoom); + totalSql = getTotalWashSql(params, "加回收科室过滤的清洗数量"); + setWorkQualityCollectionData(bean26,key,sql,totalSql); + } + bean26.setTitle("临床器械清洗不合格率"); + bean26.setRowNum(list.size()+1); + list.add(bean26); + // 手术器械清洗不合格率 + WorkQualityCollection bean27 = new WorkQualityCollection(); + params.extraQuery = " and rr.departCode = '2213' "; + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + setBetweenSql(params, startDay, endDay); + sql = getWorkUnQualityAmountEqualSql("手术器械清洗不合格",startDay,endDay,querySupplyRoom); + totalSql = getTotalWashSql(params,"加回收科室过滤的清洗数量"); + setWorkQualityCollectionData(bean27,key,sql,totalSql); + } + bean27.setTitle("手术器械清洗不合格率"); + bean27.setRowNum(list.size()+1); + list.add(bean27); + params.extraQuery = ""; + params.tousseTypeAndPackageSizeSql = String.format(" and (td.tousseType = '%s' or td.tousseType = '%s')", TousseDefinition.PACKAGE_TYPE_FOREIGN,TousseDefinition.PACKAGE_TYPE_SPLIT); + // 外来器械清洗不合格率 + WorkQualityCollection bean28 = new WorkQualityCollection(); + for (String key : startAndEndDays.keySet()){ + Map innerMap = startAndEndDays.get(key); + for (String keyOfInnerMap : innerMap.keySet()) { + startDay = keyOfInnerMap; + endDay = innerMap.get(keyOfInnerMap); + } + setBetweenSql(params, startDay, endDay); + sql = getWorkUnQualityAmountEqualSql("外来医疗器械清洗不合格",startDay,endDay,querySupplyRoom); + totalSql = getTotalWashSql(params,"清洗数量"); + setWorkQualityCollectionData(bean28,key,sql,totalSql); + } + bean28.setTitle("外来器械清洗不合格率"); + bean28.setRowNum(list.size()+1); + list.add(bean28); + } + private void setWorkQualityCollectionData(WorkQualityCollection bean,String key,String subSql,String totalSql){ + Integer amount = 0; + ResultSet rs = objectDao.executeSql(subSql); + try { + while (rs.next()) { + amount = rs.getInt(1); + } + } catch (Exception e) { + e.printStackTrace(); + } finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + ResultSet rs2 = objectDao.executeSql(totalSql); + try { + while (rs2.next()) { + Integer washAmount = rs2.getInt(1); + if (washAmount != null && washAmount > 0) { + double percentage = new BigDecimal(amount).divide( + new BigDecimal(washAmount), 4, + BigDecimal.ROUND_HALF_UP).doubleValue(); + percentage = new BigDecimal(percentage).multiply( + new BigDecimal(100)).doubleValue(); + DataIndex.setWorkQualityBeanPercentage(bean, key, percentage); + } + DataIndex.setWorkQualityBeanAmount(bean, key, washAmount); + if(amount == null){ + DataIndex.setWorkQualityBeanUnQualityAmount(bean, key, 0); + }else { + DataIndex.setWorkQualityBeanUnQualityAmount(bean, key, amount); + } + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs2); + } + } + // 获取工作量持续收集不合格的数据sql 等于监测项名字 + private String getWorkUnQualityAmountEqualSql(String formName,String startDay,String endDay,String querySupplyRoom){ + String sql =" select sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.orgUnitCoding = '" + querySupplyRoom + "'" + + " and fd.formName = '" + formName + "'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(startDay)+" and " + dateQueryAdapter.dateAdapter(endDay) + ")"; + return sql; + } + + // 获取回收总数sql + private String getTotalRecSql(ReportQueryParams params){ + String sql = "select sum(tl.amount) from (" + + dataIndex.getWorkAmountByMaterialSQL("回收数量", + params) + ") tl "; + return sql; + } + // 获取装配总数sql + private String getTotalPackingSql(ReportQueryParams params){ + String sql = "select sum(tl.amount) from (" + + dataIndex.getWorkAmountByPackageSQL("配包数量", + params) + ") tl "; + return sql; + } + //获取发货总数sql + private String getTotalInvoiceSql(ReportQueryParams params){ + String sql = "select sum(tl.amount) from (" + + dataIndex.getWorkAmountByPackageSQL("发货数量", + params) + ") tl "; + return sql; + } + /** + * 获取清洗总数的sql xjjqzyy使用 + * @param startDay + * @param endDay + * @param querySupplyRoom + * @param params + * @param workType + * @return + */ + private String getTotalWashSql(ReportQueryParams params, String workType){ + String sql = "select sum(tl.amount) from (" + + dataIndex.getWorkAmountByMaterialSQL(workType, + params) + ") tl "; + return sql; + } + // 获取工作量持续收集不合格的数据sql 包含监测项名字 + private String getWorkUnQualityAmountContainSql(String formName,String startDay,String endDay,String querySupplyRoom){ + String sql =" select sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + SqlUtils.get_InSql_Extra("fd.orgUnitCoding", querySupplyRoom) + + " and fd.formName like '%" + formName + "%'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(startDay)+" and " + dateQueryAdapter.dateAdapter(endDay) + ")"; + return sql; + } + @Override + public List getWorkQualityCollectionListAllyear( + String year, String querySupplyRoom, Map params) + throws ParseException, SQLException { + List list = new ArrayList(); + if(params == null){ + return list; + } + + String queryYear = year + "-01-01 00:00:00"; + String nextYear = dataIndex.getNextYear(year) + " 00:00:00"; + + //查询的时间段 + String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(queryYear),dateQueryAdapter.dateAdapter(nextYear)); + //数据库计算字符串长度的函数名 + String sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); + + ReportQueryParams reportParams = new ReportQueryParams(); + reportParams.betweenSql = betweenSql; + reportParams.querySupplyRoom = querySupplyRoom; + reportParams.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; + reportParams.taskGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + reportParams.tousseGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + reportParams.tousseTypeAndPackageSizeSql = ""; + reportParams.sqlLengthFunctionName = sqlLengthFunctionName; + + + // 回收总数的Map + Map recAmountMap = workQualityCollectionDataIndex.getRecyclingAmountMap(reportParams); + // 清洗总数的Map + //Map washAmountMap = workQualityCollectionDataIndex.getWashAmountByMaterialMap(reportParams); + reportParams.extraQuery = " and rr.departCode != '2213' "; + //回收时科室名称不等于麻醉手术科的清洗总数 + Map noEqualsDepartCodequwashAmountMap = workQualityCollectionDataIndex.getWashAmountByMaterialMapForXjjqzyy(reportParams); + //回收时科室名称等于麻醉手术科的清洗总数 + reportParams.extraQuery = " and rr.departCode = '2213' "; + Map equalsDepartCodequwashAmountMap = workQualityCollectionDataIndex.getWashAmountByMaterialMapForXjjqzyy(reportParams); + //外来器械清洗总数 + reportParams.extraQuery = ""; + reportParams.tousseTypeAndPackageSizeSql = String.format(" and (td.tousseType = '%s' or td.tousseType = '%s')", TousseDefinition.PACKAGE_TYPE_FOREIGN,TousseDefinition.PACKAGE_TYPE_SPLIT); + Map foreignQuwashAmountMap = workQualityCollectionDataIndex.getWashAmountByMaterialMap(reportParams); + reportParams.tousseTypeAndPackageSizeSql = ""; + // 装配总数的Map + Map packAmountMap = workQualityCollectionDataIndex.getPackAmountMap(reportParams); + + + // 装配消毒物品总数的Map + reportParams.tousseTypes = TousseDefinition.PACKAGE_TYPE_DISINFECTION; + reportParams.tousseTypeAndPackageSizeSql = DataIndex + .getTousseTypesAndPackageSizesFilterSQL( + TousseDefinition.PACKAGE_TYPE_DISINFECTION, null); + + //Map packDisinfectAmountMap = workQualityCollectionDataIndex.getPackAmountMap(reportParams); + + reportParams.tousseTypes = ""; + reportParams.tousseTypeAndPackageSizeSql = ""; + + + // 灭菌总数的Map +// Map sterilizationAmountMap = getSterilizationAmountMap(reportParams); + // 灭菌记录条数的Map + //Map sterilizationRecordAmountMap = workQualityCollectionDataIndex.getSterilizationRecordAmountMap(reportParams); + // 发货数量的Map + Map invoiceAmountMap = workQualityCollectionDataIndex.getInvoiceAmountMap(reportParams,""); + + // TODO:此处手术室的名称为硬编码，要修改才行 + // 手术室发货数量的Map + //Map invoiceOperateAmountMap = workQualityCollectionDataIndex.getInvoiceAmountMap(reportParams,"手术室"); + // 中大附一的特殊配置 + //String isXjjqzyyWorkQualityCollectionStr = params.get("isXjjqzyyWorkQualityCollection"); + return getWorkQualityCollectionListAllyearZsyy(querySupplyRoom,year,recAmountMap,null,packAmountMap,invoiceAmountMap,noEqualsDepartCodequwashAmountMap, equalsDepartCodequwashAmountMap, foreignQuwashAmountMap); + } + /** + * 中大附一的工作质量持续收集统计报表实现（按年度） + * @param querySupplyRoom + * @param year + * @param recAmountMap + * @param washAmountMap + * @param packingAmountMap + * @return + */ + private List getWorkQualityCollectionListAllyearZsyy(String querySupplyRoom,String year ,Map recAmountMap, + Map washAmountMap,Map packingAmountMap,Map invoiceAmountMap,Map noEqualsDepartCodequwashAmountMap, + Map equalsDepartCodequwashAmountMap, Map foreignQuwashAmountMap){ + List list = new ArrayList(); + String queryYear = year + "-01-01 00:00:00"; + String nextYear = DataIndex.getNextYear(year) + " 00:00:00"; + //回收时发现器械缺损不配套例数 + String sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName like '%缺损不配套%'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + String titleName = "回收时发现器械缺损不配套例数"; + addWorkQualityCollectionBean(list,titleName,sql,recAmountMap); + // 回收时发现器械种类数量不相符例数 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName like '%种类数量不相符%'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "回收时发现器械种类数量不相符例数"; + addWorkQualityCollectionBean(list,titleName,sql,recAmountMap); + // 应急处理器械次数 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName like '%应急处理%'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + 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 " + + " where mi.tousse_id=td.id and td.forDisplay=1 and( td.tousseGroupName is null or td.tousseGroupName != '专科') 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 rr.depart != '手术二区' 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 "+dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("rr.recyclingTime","mm"); + Map totalmap = getMapBySql(totalSql); + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName like '%普通器械清洗不合格%'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + 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 " + + " join ClassifiedItem cit on cb.id=cit.classifybasket_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.tousseGroupName = '专科' group by td.id,td.name) tdc on tdc.tid=cit.tousseDefinitionID" + + " where wr.id is not null " + + " and (cb.recyclingTime between "+dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("cb.recyclingTime","mm"); + totalmap = getMapBySql(totalSql); + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName like '%专科器械清洗不合格%'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + 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 " + + " 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 = '手术一区' or rr.depart = '手术二区' or 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.recyclingTime between "+dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("rr.recyclingTime","mm"); + totalmap = getMapBySql(totalSql); + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName like '%手术器械清洗不合格%'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "手术器械清洗不合格率"; + addWorkQualityCollectionBean(list,titleName,sql,totalmap); + // 无菌包内器械数量不符例数 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '无菌包内器械数量不符'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "无菌包内器械数量不符例数"; + addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); + // 无菌包内器械功能不全例数 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '无菌包内器械功能不全'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "菌包内器械功能不全例数"; + addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); + // 无菌包内器械种类错误例数 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '无菌包内器械种类错误'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "无菌包内器械种类错误例数"; + addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); + // 包内指示卡欠缺例数 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '包内指示卡欠缺例数'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "包内指示卡欠缺例数"; + addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); + // 灭菌包包装密闭性能不合格例数 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '灭菌包包装密闭性能不合格'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "灭菌包包装密闭性能不合格例数"; + addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); + // 包外明显污迹例数 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName like '%污迹%'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "包外明显污迹例数"; + addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); + // 无菌包标识不正确例数 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '无菌包标识不正确'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "无菌包标识不正确例数"; + addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); + // 灭菌方式选择不正确例数 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '灭菌方式选择不正确'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "灭菌方式选择不正确例数"; + addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); + // 湿包例数 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '湿包'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "湿包例数"; + addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); + // 包内化学指示卡变色不合格例数 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '包内化学指示卡变色不合格'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "包内化学指示卡变色不合格例数"; + addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); + // 无菌物品发放不及时次数 + totalSql = "select " +dateQueryAdapter.dateConverAdapter3("iv.sendTime","mm") + + " month, sum(it.amount) from invoice iv,InvoiceItem it,OrgUnit org where iv.id=it.invoiceID and iv.orgUnitCoding=org.orgUnitCoding " + + " and org.name != '手术一区' and org.name != '手术二区' and org.name != '手术三区' " + + " and (iv.sendTime between "+dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("iv.sendTime","mm"); + totalmap = getMapBySql(totalSql); + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '无菌物品发放不及时'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "无菌物品发放不及时次数"; + addWorkQualityCollectionBean(list,titleName,sql,totalmap); + // 器械损坏例数 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '器械损坏'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "器械损坏例数"; + addWorkQualityCollectionBean(list,titleName,sql,recAmountMap); + // 器械遗失例数 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '器械遗失'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "器械遗失例数"; + addWorkQualityCollectionBean(list,titleName,sql,recAmountMap); + // 器械包装不合格率 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '器械包装不合格'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "器械包装不合格率"; + addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); + // 湿包发生率 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '湿包登记表'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "湿包发生率"; + addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); + // 灭菌失败率 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '器械灭菌不合格'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "灭菌失败率"; + addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); + // 无菌包内器械功能不全数 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '器械功能不全'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "无菌包内器械功能不全数"; + addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); + // 器械包标签不合格 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '器械包标签不合格'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "器械包标签不合格"; + addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); + // 器械包发货不合格 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '器械包发货不合格'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "器械包发货不合格"; + addWorkQualityCollectionBean(list,titleName,sql,invoiceAmountMap); + // 临床器械清洗不合格率 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '临床器械清洗不合格'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "临床器械清洗不合格率"; + addWorkQualityCollectionBean(list,titleName,sql,noEqualsDepartCodequwashAmountMap); + // 手术器械清洗不合格率 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '手术器械清洗不合格'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "手术器械清洗不合格率"; + addWorkQualityCollectionBean(list,titleName,sql,equalsDepartCodequwashAmountMap); + // 外来器械清洗不合格率 + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" + + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " and fd.formName = '外来医疗器械清洗不合格'" + + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); + titleName = "外来器械清洗不合格率"; + addWorkQualityCollectionBean(list,titleName,sql,foreignQuwashAmountMap); + return list; + } + private void addWorkQualityCollectionBean(List list,String titileName,String sql,Map totalMap){ + if(list == null || StringUtils.isBlank(sql) || totalMap == null ){ + return ; + } + WorkQualityCollection bean = new WorkQualityCollection(); + ResultSet result = objectDao.executeSql(sql); + bean.setRowNum(list.size()+1); + bean.setTitle(titileName); + String key = ""; + Integer amount = 0; + Map map = new HashMap(); + try { + while (result.next()) { + key = result.getString(1); + // 月份以0开头的，去掉0 + if(key.length() > 1 && Integer.valueOf(key) < 10){ + key = Integer.valueOf(key).toString(); + } + amount = result.getInt(2); + map.put(key,amount); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(result); + } + // 记录已经添加的key + List keyList = new ArrayList(); + // 遍历结果集 设置百分比 + if(totalMap != null){ + for(Map.Entry entry:totalMap.entrySet()){ + key = entry.getKey(); + amount = map.get(key); + if(!keyList.contains(key)){ + keyList.add(key); + } + // 总数 + int amount2 = entry.getValue(); + if(amount2 > 0 && amount != null){ + double percentage = new BigDecimal(amount).divide( + new BigDecimal(amount2), 4, BigDecimal.ROUND_HALF_UP) + .doubleValue(); + percentage = new BigDecimal(percentage).multiply( + new BigDecimal(100)).doubleValue(); + DataIndex.setWorkQualityBeanPercentage(bean, key, percentage); + } + DataIndex.setWorkQualityBeanAmount(bean, key, amount2); + if(amount == null){ + DataIndex.setWorkQualityBeanUnQualityAmount(bean, key, 0); + }else { + DataIndex.setWorkQualityBeanUnQualityAmount(bean, key, amount); + } + } + } + // 添加不合格但没总数的数据 + if(map != null){ + for(Map.Entry entry:map.entrySet()){ + key = entry.getKey(); + if(keyList.contains(key)){ + continue; + } + amount = map.get(key); + if(amount == null){ + DataIndex.setWorkQualityBeanUnQualityAmount(bean, key, 0); + }else { + DataIndex.setWorkQualityBeanUnQualityAmount(bean, key, amount); + } + } + } + list.add(bean); + } + /** + * 获取每个月数据的map,sql的查询结果第一个必须是字符串，第二个是数量 + * @param sql + * @return + */ + private Map getMapBySql(String sql){ + Map map = new HashMap(); + if(StringUtils.isBlank(sql)){ + return map; + } + ResultSet result2 = objectDao.executeSql(sql); + try{ + while(result2.next()){ + String key = result2.getString(1); + // 月份以0开头的，去掉0 + if(key.length() > 1 && Integer.valueOf(key) < 10){ + key = Integer.valueOf(key).toString(); + } + map.put(key, result2.getInt(2)); + } + }catch(SQLException e){ + e.printStackTrace(); + }finally{ + DatabaseUtil.closeResultSetAndStatement(result2); + } + return map; + } + private void setBetweenSql(ReportQueryParams params, String startDay, String endDay){ + String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDay),dateQueryAdapter.dateAdapter(endDay)); + params.betweenSql = betweenSql; + } + } Index: ssts-web/src/main/webapp/disinfectsystem/config/xjjqzyy/config.js =================================================================== diff -u -r26132 -r26203 --- ssts-web/src/main/webapp/disinfectsystem/config/xjjqzyy/config.js (.../config.js) (revision 26132) +++ ssts-web/src/main/webapp/disinfectsystem/config/xjjqzyy/config.js (.../config.js) (revision 26203) @@ -201,6 +201,9 @@ //是否按包显示消毒物品,默认false按材料统计.true:按包统计,不统计非整包申请的消毒物品 showDisinfectionByPackage : false, //外来器械包拆包显示检查人字段,true显示,false不显示 - showInspectorInSplitForeignTousseDialog:true - + showInspectorInSplitForeignTousseDialog:true, + //新疆定制的工作质量持续收集统计报表 + isXjjqzyyWorkQualityCollection : true, + isHidePartOfWorkQualityCollection : true + } \ No newline at end of file Index: ssts-web/src/main/webapp/disinfectsystem/reportforms/workQualityCollectionView.js =================================================================== diff -u -r25918 -r26203 --- ssts-web/src/main/webapp/disinfectsystem/reportforms/workQualityCollectionView.js (.../workQualityCollectionView.js) (revision 25918) +++ ssts-web/src/main/webapp/disinfectsystem/reportforms/workQualityCollectionView.js (.../workQualityCollectionView.js) (revision 26203) @@ -116,7 +116,10 @@ if(sstsConfig.hasOwnProperty('isZSYYWorkQualityCollection') && sstsConfig.isZSYYWorkQualityCollection){ isZSYYWorkQualityCollection = true; } - + var isXjjqzyyWorkQualityCollection = false; + if(sstsConfig.hasOwnProperty('isXjjqzyyWorkQualityCollection') && sstsConfig.isXjjqzyyWorkQualityCollection){ + isXjjqzyyWorkQualityCollection = true; + } // 全年 var jasperReportName = "workQualityCollection.jasper"; if(type == 'split'){ @@ -140,6 +143,7 @@ window.open(WWWROOT + "/jasperreports/jasperreportsAction!createReportFromJavaBeanSource.do?jasperreportName=" + jasperReportName +"&reportName=workQualityCollection&querySupplyRoom="+departCoding +"&isZSYYWorkQualityCollection=" + isZSYYWorkQualityCollection + + "&isXjjqzyyWorkQualityCollection=" + isXjjqzyyWorkQualityCollection +"&year="+year+"&type="+type+"&isHidePartOfWorkQualityCollection="+hidePartOfWorkQualityCollection,'thisIframe','_self'); } var sign = true; Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/action/JasperreportsAction.java =================================================================== diff -u -r26066 -r26203 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/action/JasperreportsAction.java (.../JasperreportsAction.java) (revision 26066) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/action/JasperreportsAction.java (.../JasperreportsAction.java) (revision 26203) @@ -558,14 +558,18 @@ String isZSYYWorkQualityCollectionStr = params.get("isZSYYWorkQualityCollection"); // 判断是否为东莞市妇幼的持续收集统计报表 String isDgsfyWorkQualityCollectionStr = params.get("isDgsfyWorkQualityCollection"); - + // 判断是否为xjjqzyy的持续收集统计报表 + String isXjjqzyyWorkQualityCollectionStr = params.get("isXjjqzyyWorkQualityCollection"); if("true".equals(isZSYYWorkQualityCollectionStr)){ return customReportsOfZsyy.getWorkQualityCollectionList(year, type,querySupplyRoom ,null,params); } else if ("true".equals(isDgsfyWorkQualityCollectionStr)){ return customReportsOfDgsfy.getWorkQualityCollectionList(year, type,querySupplyRoom ,null,params); + }else if("true".equals(isXjjqzyyWorkQualityCollectionStr)){ + return customReportsOfXjjqzyy.getWorkQualityCollectionList(year, + type,querySupplyRoom ,null,params); } else{ return workQualityCollectionReport.getWorkQualityCollectionList(year, Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java =================================================================== diff -u -r26087 -r26203 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 26087) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 26203) @@ -430,7 +430,112 @@ } } break; - + case "加回收科室过滤的清洗数量"://xjjqzyy定制的工作质量持续收集统计报表使用 + String selectUserNameSqlXjjqzyy = ",case when (cb.personInCharge is null or cb.personInCharge = '') then wr.operator else cb.personInCharge end userName "; + String selectTousseTypeSqlXjjqzyy = ",td.tousseType tousseType "; + String selectTousseNameSqlXjjqzyy = ",ci.tousseNameForMaterial tousseName "; + + + // 统计清洗项的itemType为材料的物品，包括拆包清洗的器械包以及外来器械包 + amountSql += String + .format("select %s %s %s %s %s %s " + + getWashMaterialAmountSqlFromItemTypeIsMaterial(" inner join RecyclingRecord rr on rr.recyclingApplication_id=ci.invoicePlanID ") + + " and wr.endDate %s %s and wr.washMaterialAmount <> 0 %s %s %s %s %s " + + "%s %s ", + getDatePeriodSelectSql(obj.monthlyStr), + getAmountSelectSql(obj, "ci.amount"), + obj.selectUserName ? selectUserNameSqlXjjqzyy : "", + obj.selectTousseType ? selectTousseTypeSqlXjjqzyy : "", + obj.selectTousseName ? selectTousseNameSqlXjjqzyy : "", + obj.extraSelectColumns, + obj.betweenSql, + SqlUtils.get_InSql_Extra("wr.orgUnitCoding", obj.querySupplyRoom), + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql, + obj.extraQuery, + getGroupByDatePeriodSql(obj.monthlyStr), + obj.extraGroupBy); + + + // 统计清洗项的itemType为材料的物品，器械是单独添加进清洗篮筐，没有关联的包定义 + amountSql += " union all "; + amountSql += String + .format("select %s %s %s %s %s %s " + + getWashMaterialAmountSqlFromItemTypeIsMaterialWithoutTousseDefinition(" inner join RecyclingRecord rr on rr.recyclingApplication_id=ci.invoicePlanID ") + + " and wr.endDate %s %s and wr.washMaterialAmount <> 0 %s " + + "%s %s ", + getDatePeriodSelectSql(obj.monthlyStr), + getAmountSelectSql(obj, "ci.amount"), + obj.selectUserName ? selectUserNameSqlXjjqzyy : "", + obj.selectTousseType ? ",'材料' tousseType " : "", + obj.selectTousseName ? selectTousseNameSqlXjjqzyy : "", + obj.extraSelectColumns, + obj.betweenSql, + SqlUtils.get_InSql_Extra("wr.orgUnitCoding", obj.querySupplyRoom), + obj.extraQuery, + getGroupByDatePeriodSql(obj.monthlyStr), + obj.extraGroupBy); + + // 统计清洗项的itemType不为材料的物品，包括整包清洗的器械包 + amountSql += " union all "; + amountSql += String + .format("select %s %s %s %s %s %s " + + getWashMaterialAmountSqlFromItemTypeIsNotMaterial(" inner join RecyclingRecord rr on rr.recyclingApplication_id=ci.invoicePlanID ") + + " and wr.endDate %s %s and wr.washMaterialAmount <> 0 %s %s %s %s %s " + + "%s %s ", + getDatePeriodSelectSql(obj.monthlyStr), + getAmountSelectSql(obj, "ci.amount*mi.count"), + obj.selectUserName ? selectUserNameSqlXjjqzyy : "", + obj.selectTousseType ? selectTousseTypeSqlXjjqzyy : "", + obj.selectTousseName ? ",ci.name tousseName " : "", + obj.extraSelectColumns, + obj.betweenSql, + SqlUtils.get_InSql_Extra("wr.orgUnitCoding", obj.querySupplyRoom), + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql, + obj.extraQuery, + getGroupByDatePeriodSql(obj.monthlyStr), + obj.extraGroupBy); + + // 判断是否需要统计单独清洗的器械的数量，该器械没有装入清洗篮筐 + if ("全部".equals(obj.tousseTypes) + || StringUtils.isBlank(obj.tousseTypes) + || obj.tousseTypes.indexOf("材料") >= 0) { + + if (StringUtils + .isBlank(obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd) + && StringUtils + .isBlank(obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd) + && StringUtils + .isBlank(obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd)) { + amountSql += " union all "; + amountSql += String.format("select %s %s %s %s %s %s " + + getWashMaterialAmountSqlFromTousseTypeIsMaterial(" inner join ClassifyBasket_WashRecord cw on cw.WashAndDisinfectRecord_ID = wr.id " + + " inner join ClassifyBasket cb on cb.id = cw.ClassifyBasket_ID " + + " inner join ClassifiedItem ci on ci.classifybasket_id = cb.id " + + " inner join RecyclingRecord rr on rr.recyclingApplication_id=ci.invoicePlanID " + ) + + "and wr.endDate %s %s %s " + + "%s %s ", + getDatePeriodSelectSql(obj.monthlyStr), + getAmountSelectSql(obj, "wrm.amount"), + obj.selectUserName ? ",wr.operator userName " : "", + obj.selectTousseType ? ",'材料' tousseType " : "", + obj.selectTousseName ? ",md.name tousseName " : "", + obj.extraSelectColumns, + obj.betweenSql, + SqlUtils.get_InSql_Extra("wr.orgUnitCoding", obj.querySupplyRoom), + obj.extraQuery, + getGroupByDatePeriodSql(obj.monthlyStr), + obj.extraGroupBy + ); + } + } + break; case "配包数量": JSONObject sstsConfig = CssdUtils.getWebConfigInfo();