Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfZsyy.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfZsyy.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfZsyy.java (revision 25235) @@ -0,0 +1,851 @@ +package com.forgon.disinfectsystem.jasperreports.service; + +import java.math.BigDecimal; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.apache.commons.lang.StringUtils; +import org.apache.log4j.Logger; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.stereotype.Component; + +import com.forgon.databaseadapter.service.DateQueryAdapter; +import com.forgon.databaseadapter.service.SqlFunctionsAdapter; +import com.forgon.directory.service.OrgUnitManager; +import com.forgon.disinfectsystem.barcode.service.BarcodeManager; +import com.forgon.disinfectsystem.basedatamanager.reportoption.GoodsOptionManager; +import com.forgon.disinfectsystem.basedatamanager.rinser.service.RinserManager; +import com.forgon.disinfectsystem.basedatamanager.sterilizer.service.SterilizerManager; +import com.forgon.disinfectsystem.basedatamanager.supplyroomconfig.service.SupplyRoomConfigManager; +import com.forgon.disinfectsystem.basedatamanager.warehouse.service.WareHouseManager; +import com.forgon.disinfectsystem.customform.formdefinition.service.FormDefinitionManager; +import com.forgon.disinfectsystem.diposablegoods.service.DiposableGoodsManager; +import com.forgon.disinfectsystem.diposablegoods.service.GodownEntryItemManager; +import com.forgon.disinfectsystem.foreigntousseapplication.service.ForeignTousseApplicationManager; +import com.forgon.disinfectsystem.jasperreports.javabeansource.WorkQualityCollection; +import com.forgon.disinfectsystem.qualitymonitoring.definition.service.QualityMonitoringDefinitionManager; +import com.forgon.disinfectsystem.tousse.materialdefinition.service.MaterialDefinitionManager; +import com.forgon.disinfectsystem.tousse.materialmanager.service.MaterialCheckManager; +import com.forgon.disinfectsystem.tousse.materialmanager.service.MaterialEntryItemManager; +import com.forgon.disinfectsystem.tousse.toussedefinition.service.TousseDefinitionManager; +import com.forgon.disinfectsystem.tousse.toussedefinition.service.TousseInstanceManager; +import com.forgon.disinfectsystem.useRecord.service.UseRecordManager; +import com.forgon.systemsetting.service.HttpOptionManager; +import com.forgon.tools.db.DatabaseUtil; +import com.forgon.tools.db.InitDbConnection; +import com.forgon.tools.hibernate.ObjectDao; + +/** + * @author Terry + * 中大附一的个性化报表，迁移至到本类 + * 2018-12-24 + * + */ +@Component(value = "customReportsOfZsyy") +public class CustomReportsOfZsyy { + private Logger logger = Logger.getLogger(this.getClass()); + @Autowired + private ObjectDao objectDao; + @Autowired + private DateQueryAdapter dateQueryAdapter; + @Autowired + private SqlFunctionsAdapter sqlFunctionsAdapter; + @Autowired + private SupplyRoomConfigManager supplyRoomConfigManager; + @Autowired + private DiposableGoodsManager diposableGoodsManager; + @Autowired + private InitDbConnection dbConnection; + @Autowired + private TousseDefinitionManager tousseDefinitionManager; + @Autowired + private MaterialDefinitionManager materialDefinitionManager; + @Autowired + private UseRecordManager useRecordManager; + @Autowired + private TousseInstanceManager tousseInstanceManager; + @Autowired + private HttpOptionManager httpOptionManager; + + private static final String DIPOSABLEGOODS = "一次性物品"; + + @Autowired + private GoodsOptionManager goodsOptionManager; + @Autowired + private GodownEntryItemManager godownEntryItemManager; + @Autowired + private MaterialEntryItemManager materialEntryItemManager; + @Autowired + private WareHouseManager wareHouseManager; + @Autowired + private MaterialCheckManager materialCheckManager; + @Autowired + private OrgUnitManager orgUnitManager; + @Autowired + private ForeignTousseApplicationManager foreignTousseApplicationManager; + @Autowired + private QualityMonitoringDefinitionManager qualityMonitoringDefinitionManager; + @Autowired + private FormDefinitionManager formDefinitionManager; + @Autowired + private BarcodeManager barcodeManager; + @Autowired + private SterilizerManager sterilizerManager; + @Autowired + private RinserManager rinserManager; + + + public void getZSYYWorkQualityCollectionList(Map> startAndEndDays,List list,String querySupplyRoom){ + if(startAndEndDays == null || list == null){ + return ; + } + String startDay = null; + String endDay = null; + String sql = null; + String totalSql = null; + // 回收时发现器械缺损不配套例数 缺损不配套的数据显示 + 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); + totalSql = getTotalRecSql(startDay,endDay,querySupplyRoom); + 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); + totalSql = getTotalRecSql(startDay,endDay,querySupplyRoom); + 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); + totalSql = getTotalRecSql(startDay,endDay,querySupplyRoom); + 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); + totalSql = getTotalPackingSql(startDay,endDay,querySupplyRoom); + 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); + totalSql = getTotalPackingSql(startDay,endDay,querySupplyRoom); + 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); + totalSql = getTotalPackingSql(startDay,endDay,querySupplyRoom); + 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); + totalSql = getTotalPackingSql(startDay,endDay,querySupplyRoom); + 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); + totalSql = getTotalPackingSql(startDay,endDay,querySupplyRoom); + 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); + totalSql = getTotalPackingSql(startDay,endDay,querySupplyRoom); + 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); + totalSql = getTotalPackingSql(startDay,endDay,querySupplyRoom); + 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); + totalSql = getTotalPackingSql(startDay,endDay,querySupplyRoom); + 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); + totalSql = getTotalPackingSql(startDay,endDay,querySupplyRoom); + 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); + totalSql = getTotalPackingSql(startDay,endDay,querySupplyRoom); + 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); + totalSql = getTotalRecSql(startDay,endDay,querySupplyRoom); + 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); + totalSql = getTotalRecSql(startDay,endDay,querySupplyRoom); + setWorkQualityCollectionData(bean19,key,sql,totalSql); + } + bean19.setTitle("器械遗失例数"); + bean19.setRowNum(list.size()+1); + list.add(bean19); + } + + public List getZSYYWorkQualityCollectionListAllyear(String querySupplyRoom,String year ,Map recAmountMap, + Map washAmountMap,Map packingAmountMap){ + 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); + 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); + } + + // 获取工作量持续收集不合格的数据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 " + + " and fd.orgUnitCoding = '" + querySupplyRoom + "'" + + " and fd.formName like '%" + formName + "%'" + + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(startDay)+" and " + dateQueryAdapter.dateAdapter(endDay) + ")"; + return sql; + } + + // 获取工作量持续收集不合格的数据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; + } + + + + 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 getTotalRecSql(String startDay,String endDay,String querySupplyRoom){ + 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 " + + " where rr.orgUnitCoding = '" + querySupplyRoom + "'" + + " and rr.recyclingTime between " + dateQueryAdapter.dateAdapter(startDay) +" and " + dateQueryAdapter.dateAdapter(endDay); + return sql; + } + // 获取清洗总数sql + private String getTotalWashSql(String startDay,String endDay,String querySupplyRoom){ + String sql = "select sum(r.washmaterialamount) from washanddisinfectrecord r " + + "where r.orgUnitCoding = '"+querySupplyRoom+"' and r.endDate >= " + + dateQueryAdapter.dateAdapter(startDay) + + " and r.endDate <= " + + dateQueryAdapter.dateAdapter(endDay); + return sql; + } + // 获取装配总数sql + private String getTotalPackingSql(String startDay,String endDay,String querySupplyRoom){ + String sql = "select sum(p.amount) from packingrecord p" + + " where p.orgUnitCoding = '"+querySupplyRoom+"' and p.packTime >= " + + dateQueryAdapter.dateAdapter(startDay) + + " and p.packTime <= " + + dateQueryAdapter.dateAdapter(endDay); + return sql; + } + + /** + * 获取每个月数据的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; + } + +} + Index: ssts-web/src/main/webapp/jasperRtp/workQualityCollection.jasper =================================================================== diff -u -r17078 -r25235 Binary files differ Index: ssts-web/src/main/webapp/jasperRtp/workQualityCollection.jrxml =================================================================== diff -u -r17078 -r25235 --- ssts-web/src/main/webapp/jasperRtp/workQualityCollection.jrxml (.../workQualityCollection.jrxml) (revision 17078) +++ ssts-web/src/main/webapp/jasperRtp/workQualityCollection.jrxml (.../workQualityCollection.jrxml) (revision 25235) @@ -1,7 +1,7 @@ - +