Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r33513 -r33524 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 33513) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 33524) @@ -462,7 +462,7 @@ + SqlUtils.get_InSql_Extra("w.orgUnitCoding", departCoding) + " group by "+dateQueryAdapter.dateConverAdapter3("w.enddate","mm")+" order by "+dateQueryAdapter.dateConverAdapter3("w.enddate","mm"); - ReportQueryParams reportParams = new ReportQueryParams(); + ReportQueryParams reportParams = new ReportQueryParams(objectDao); reportParams.betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(queryYear), dateQueryAdapter.dateAdapter(nextYear)); @@ -889,7 +889,7 @@ String nextYear = DataIndex.getNextYear(year); - ReportQueryParams reportParams = new ReportQueryParams(); + ReportQueryParams reportParams = new ReportQueryParams(objectDao); reportParams.betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(queryYear), dateQueryAdapter.dateAdapter(nextYear)); @@ -1762,7 +1762,7 @@ // + dateQueryAdapter.dateAdapter(startDate) + " and " + dateQueryAdapter.dateAdapter(endDate)+" and t.orgUnitCoding='" // + deptCode + "' group by d.tousseType"; - ReportQueryParams params = new ReportQueryParams(); + ReportQueryParams params = new ReportQueryParams(objectDao); params.betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDate), dateQueryAdapter.dateAdapter(endDate)); @@ -7794,7 +7794,7 @@ //数据库计算字符串长度的函数名 String sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); - ReportQueryParams params = new ReportQueryParams(); + ReportQueryParams params = new ReportQueryParams(objectDao); params.betweenSql = betweenSql; params.querySupplyRoom = querySupplyRoom; params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; @@ -8266,340 +8266,299 @@ */ @Override public List getYearWorkloadReportSpecial(String year,String querySupplyRoom) { - List sterilerGroupList = httpOptionManager.getHttpOptionListById("sterilerGroup"); - List list = new ArrayList(); + Map yearMonthMap = new HashMap();//月份对应的年月map,用于报表显示 + String[] monthArr = null; + String mmOrdd = null; + String startDay = null; + String endDay = null; + mmOrdd = "mm"; + startDay = "'" + year + "-01-01 00:00:00'"; + String nextYear = dataIndex.getNextYear(year); + endDay = "'" + nextYear + " 00:00:00'"; + ReportQueryParams params = new ReportQueryParams(objectDao); + params.betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(year + "-01-01 00:00:00"),dateQueryAdapter.dateAdapter(nextYear + " 00:00:00")); + params.querySupplyRoom = querySupplyRoom; + params.sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); + params.selectTousseType = false; + params.selectTousseName = false; + params.isGroup = true; + String statisticTousseWorkLoadIncludeDisposableGoodsAmountStr = CssdUtils.getSystemSetConfigByName("statisticTousseWorkLoadIncludeDisposableGoodsAmount"); + JSONObject statisticTousseWorkLoadIncludeDisposableGoodsAmountObj = null; + if(StringUtils.isNotBlank(statisticTousseWorkLoadIncludeDisposableGoodsAmountStr)){ + statisticTousseWorkLoadIncludeDisposableGoodsAmountObj = JSONObject.fromObject(statisticTousseWorkLoadIncludeDisposableGoodsAmountStr); + } + params.sterilizationModeSqlWithAliasOfTousseDefinitionIsTd = ""; + params.packageTypeSqlWithAliasOfTousseDefinitionIsTd = ""; -// List groupList = new ArrayList(); -// String ownGroupSql = " select distinct ownGroup from Sterilizer "; -// ResultSet rs2 = objectDao.executeSql(ownGroupSql); -// try { -// while(rs2.next()){ -// groupList.add(rs2.getString(1)); -// } -// } catch (SQLException e1) { -// e1.printStackTrace(); -// } - - Map map = new HashMap(); + monthArr = new String[]{"01","02","03","04","05","06","07","08","09","10","11","12"};//月份,不同的数据库月份可能不一样 + for (int i = 0; i < monthArr.length; i++) { + yearMonthMap.put(monthArr[i], year + "-" + monthArr[i]); + } int dataSoureOfMaterialsCountOfToussesInReports = CssdUtils.getSystemSetConfigByNameInt("dataSoureOfMaterialsCountOfToussesInReports", 3); - String columnSql2Or10Or11 = null; - String joinMaterialInstanceSql2 = null;//数字对应sql后面的数字 如本条对应String sql2 - String joinMaterialInstanceSql10 = null; - String joinMaterialInstanceSql11 = null; - String columnSql12 = null; - String joinMaterialInstanceSql12 = null; - String joinConditionSql12 = null; - if(dataSoureOfMaterialsCountOfToussesInReports == 3){ - columnSql2Or10Or11 = "ritem.amount*ritem.materialAmount"; - - joinMaterialInstanceSql2 = String.format(" join %s td on td.id=ritem.tousseDefinitionId and ( td.tousseType is null or td.tousseType != '%s')", - TousseDefinition.class.getSimpleName(), - TousseDefinition.PACKAGE_TYPE_FOREIGN); - - joinMaterialInstanceSql10 = ""; - joinMaterialInstanceSql11 = String.format(" join %s td on td.id=ritem.tousseDefinitionId and td.tousseType = '%s'", - TousseDefinition.class.getSimpleName(), - TousseDefinition.PACKAGE_TYPE_FOREIGN); - - columnSql12 = "pr.amount*pr.materialAmount"; - joinMaterialInstanceSql12 = ""; - joinConditionSql12 = ""; - }else{ - columnSql2Or10Or11 = "ritem.amount*tdc.amount"; - - joinMaterialInstanceSql2 = String.format(" join (select td.id tid,sum(mi.count) amount from %s mi inner join %s td on td.id=mi.tousse_id and ( td.tousseType is null or td.tousseType != '%s') group by td.id ) tdc on tdc.tid=ritem.tousseDefinitionId ", - MaterialInstance.class.getSimpleName(), - TousseDefinition.class.getSimpleName(), - TousseDefinition.PACKAGE_TYPE_FOREIGN); - - joinMaterialInstanceSql10 = String.format(" join (select td.id tid,sum(mi.count) amount from %s mi inner join %s td on td.id=mi.tousse_id group by td.id ) tdc on tdc.tid=ritem.tousseDefinitionId ", - MaterialInstance.class.getSimpleName(), - TousseDefinition.class.getSimpleName()); - - joinMaterialInstanceSql11 = String.format(" join (select td.id tid,sum(mi.count) amount from %s mi inner join %s td on td.id=mi.tousse_id and td.tousseType = '%s' group by td.id ) tdc on tdc.tid=ritem.tousseDefinitionId ", - MaterialInstance.class.getSimpleName(), - TousseDefinition.class.getSimpleName(), - TousseDefinition.PACKAGE_TYPE_FOREIGN); - - columnSql12 = "pr.amount*tdc.amount"; - joinMaterialInstanceSql12 = String.format(",(select td.id tid, sum(mi.count) amount from %s mi, %s td where mi.tousse_id=td.id group by td.id) tdc ", - MaterialInstance.class.getSimpleName(), - TousseDefinition.class.getSimpleName()); - joinConditionSql12 = " and td.id = tdc.tid "; + + //清洗手术室器械总件数 + params.monthlyStr = dateQueryAdapter.dateConverAdapter3("wr.endDate","mm"); + params.applicationDepart="手术室"; + String sql = String.format("select sum(tl.amount) amount, monthstr from (" + +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by monthstr "); + Map operatingRoomWashMaterialAmountMap = dataIndex.getAmountAndDateBySql(sql); + //清洗临床科室物品总件数 回收科室不等于“手术室”的器械清洗总件数 + params.applicationDepart=""; + params.extraQuery = " and ci.orgUnitName!='手术室' "; + sql = String.format("select sum(tl.amount) amount, monthstr from (" + +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by monthstr "); + Map noOperatingRoomWashMaterialAmountMap = dataIndex.getAmountAndDateBySql(sql); + //清洗器械总件数 = 清洗临床科室物品总件数 + 清洗手术室器械总件数 + Map totalWashMaterialAmountMap = new HashMap(); + for (String key : operatingRoomWashMaterialAmountMap.keySet()) { + totalWashMaterialAmountMap.put(key, operatingRoomWashMaterialAmountMap.get(key)); } - for (int i = 1; i <= 12; i++) { - String monthFormart = i + ""; - if(i < 10){ - monthFormart = "0" + monthFormart; + for (String key : noOperatingRoomWashMaterialAmountMap.keySet()) { + if(totalWashMaterialAmountMap.containsKey(key)){ + totalWashMaterialAmountMap.put(key, MathTools.add(totalWashMaterialAmountMap.get(key), noOperatingRoomWashMaterialAmountMap.get(key)).intValue()); + }else{ + totalWashMaterialAmountMap.put(key, noOperatingRoomWashMaterialAmountMap.get(key)); } - String startDay = year + "-" + i + "-01 00:00:00"; - String endDay = year + "-" + (i + 1) + "-01 00:00:00"; - if (i == 12) { - endDay = (Integer.parseInt(year) + 1) + "-01-01 00:00:00"; - } - startDay = dateQueryAdapter.dateAdapter(startDay); - endDay = dateQueryAdapter.dateAdapter(endDay); - - String month = year + "-" + monthFormart; - - // 处理器械总件数 - // 数据获取：不包含外来器械申请单、代理灭菌单、消毒物品、敷料包。包类型只针对器械包， - // 所有包装类型都可以统计。申请科室没有限制。以回收为准。 - // 数量统计为回收的器械包的材料数量 -// String sql = "select sum(tdc.amount*ri.amount) from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " -// + " join (select td.id tid,td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td where mi.tousse_id=td.id and td.forDisplay=1 group by td.id,td.name) tdc " -// + " on tdc.tid=td.id" -// + " left join invoicePlan ip on rr.recyclingApplication_id=ip.id" -// + " where (ip.id is null or (ip.id is not null and ip.type != '" + InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION + "' and ip.type != '" + InvoicePlan.TYPE_PROXYDISINFECTION + "' and ip.type != '" +InvoicePlan.TYPE_DRESSING_APPLICATION_FROM+ "' and ip.type != '" +InvoicePlan.TYPE_DISINFECT_GOODS_APPLICATION_FORM+ "'))" -// + " and td.tousseType != '" +TousseDefinition.PACKAGE_TYPE_DISINFECTION + "' and td.tousseType != '" + TousseDefinition.PACKAGE_TYPE_DRESSING+ "'" -// + " and rr.recyclingTime between " + startDay +" and "+endDay; - -// Integer amount = getAmountBySql(sql); -// String columnName = "处理器械总件数 "; -// YearWorkloadBean bean = new YearWorkloadBean(i,month, 1, columnName, amount); -// list.add(getYearWorkloadBean(i,month, 1, "处理器械总件数", sql)); - // 处理器械总包数 - //数据获取：不包含外来器械申请单、代理灭菌单、消毒物品、敷料包。包类型只针对器械包， - // 所有包装类型都可以统计。申请科室没有限制。数量统计为回收的器械包的包数 -// sql = "select sum(ri.amount) from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " -// + " left join invoicePlan ip on rr.recyclingApplication_id=ip.id" -// + " where (ip.id is null or (ip.id is not null and ip.type != '" + InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION + "' and ip.type != '" + InvoicePlan.TYPE_PROXYDISINFECTION + "' and ip.type != '" +InvoicePlan.TYPE_DRESSING_APPLICATION_FROM+ "' and ip.type != '" +InvoicePlan.TYPE_DISINFECT_GOODS_APPLICATION_FORM+ "'))" -// + " and td.tousseType != '" +TousseDefinition.PACKAGE_TYPE_DISINFECTION + "' and td.tousseType != '" + TousseDefinition.PACKAGE_TYPE_DRESSING+ "'" -// + " and rr.recyclingTime between " + startDay +" and "+endDay; - // 包装器械总包数。数据获取改成装配总包数，除了外来器械包其余所有的都统计在内，包含敷料、消毒物品、纸塑包 - String sql = "select sum(pr.amount) from PackingRecord pr,tousseDefinition td " - + " where pr.tousseDefinitionId=td.id " - + " and td.tousseType != '" + TousseDefinition.PACKAGE_TYPE_FOREIGN + "'" - + " and td.tousseType != '" + TousseDefinition.PACKAGE_TYPE_SPLIT + "'" - + " and pr.orgUnitCoding='" + querySupplyRoom + "' " - + " and pr.packTime between " + startDay +" and " + endDay; - - list.add(getYearWorkloadBean(i,month, 2, "包装器械总包数", sql)); - // 处理手术室器械总件数 申请科室为“手术室” 只统计包类型为“器械包” 统计回收器械包的材料数量 -// sql = " select sum(tdc.amount*ri.amount) from RecyclingRecord rr,RecyclingItem ri,TousseDefinition td, " -// + "(select td.id tid,td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td where mi.tousse_id=td.id and td.forDisplay=1 group by td.id,td.name) tdc " -// + " where rr.id=ri.recyclingRecord_id and ri.tousseDefinitionId=td.id and td.id=tdc.tid and rr.depart='手术室' " -// + " and (td.tousseType='器械包') " -// + " and rr.recyclingTime between " + startDay +" and "+endDay; - // 回收科室等于手术室的器械清洗总件数，不包含外来器械 - String sql2 = "select sum("+ columnSql2Or10Or11 +") from RecyclingRecord rr join RecyclingItem ritem on ritem.recyclingRecord_id = rr.id " - + joinMaterialInstanceSql2 - + " join ClassifyBasket_RecyclingRecord cr on rr.id=cr.RecyclingRecord_ID " - + " join ClassifyBasket cb on cr.ClassifyBasket_ID=cb.id join ClassifyBasket_WashRecord cw on cb.id=cw.ClassifyBasket_ID " - + " join WashAndDisinfectRecord wr on cw.WashAndDisinfectRecord_ID=wr.id " - + " where rr.depart is not null and rr.depart = '手术室' and wr.id is not null " - + " and ritem.tousseDefinitionId in (select cit.tousseDefinitionID from ClassifyBasket clb,ClassifiedItem cit where clb.id=cit.classifybasket_id and cb.id=clb.id) " - + " and rr.orgUnitCoding = '"+querySupplyRoom+"'" - + " and (rr.recyclingTime between "+ startDay +" and " + endDay + ")"; - - Integer amountOpera = dataIndex.getAmountBySql(sql2); - String columnNameOperta = "清洗手术室器械总件数"; - YearWorkloadBean beanOpera = new YearWorkloadBean(i,month, 4, columnNameOperta, amountOpera); - list.add(beanOpera); - -// list.add(getYearWorkloadBean(i,month, 3, "清洗手术室器械总件数", sql)); - // 处理临床科室物品总件数，数据获取器械包申请时科室不等于手术室的 - // 回收科室不等于“手术室”的器械清洗总件数 -// String sql10 = " select sum(tdc.amount) from Invoice i,TousseInstance ti,TousseDefinition td,(select td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td " -// + " where mi.tousse_id=td.id and td.forDisplay=1 group by td.name) tdc " -// + " where i.id=ti.invoice_id and ti.tousseDefinition_id=td.id " -// + " and tdc.tdName = td.name " -// + " and (ti.depart is null or ti.depart != '手术室') " -// + " and i.sendTime between " + startDay +" and "+endDay; - String sql10 = "select sum("+ columnSql2Or10Or11 +") from RecyclingRecord rr join RecyclingItem ritem on ritem.recyclingRecord_id = rr.id " - + joinMaterialInstanceSql10 - + " join ClassifyBasket_RecyclingRecord cr on rr.id=cr.RecyclingRecord_ID " - + " join ClassifyBasket cb on cr.ClassifyBasket_ID=cb.id join ClassifyBasket_WashRecord cw on cb.id=cw.ClassifyBasket_ID " - + " join WashAndDisinfectRecord wr on cw.WashAndDisinfectRecord_ID=wr.id " - + " where rr.depart != '手术室' and wr.id is not null " - + " and ritem.tousseDefinitionId in (select cit.tousseDefinitionID from ClassifyBasket clb,ClassifiedItem cit where clb.id=cit.classifybasket_id and cb.id=clb.id) " - + " and rr.orgUnitCoding = '"+querySupplyRoom+"'" - + " and (rr.recyclingTime between "+ startDay +" and " + endDay + ")"; - - Integer amount10 = dataIndex.getAmountBySql(sql10); - String columnName10 = "清洗临床科室物品总件数"; - YearWorkloadBean bean10 = new YearWorkloadBean(i,month, 4, columnName10, amount10); - list.add(bean10); - - YearWorkloadBean materialTotal = new YearWorkloadBean(i,month, 1, "清洗器械总件数", amountOpera + amount10); - list.add(materialTotal); -// list.add(getYearWorkloadBean(i,month, 1, "处理器械总件数", sql)); - //“处理外来手术器械总包件数”： - //数据获取为：统计外来器械包回收的包数量，申请科室为“手术室” -// sql = "select sum(ri.amount*tdc.amount) from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " -// + " join invoicePlan ip on rr.recyclingApplication_id=ip.id" -// + " join (select td.id tid,td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td where mi.tousse_id=td.id group by td.id,td.name) tdc " -// + " on tdc.tid=td.id" -// + " where ip.depart='手术室' and td.tousseType= '" + TousseDefinition.PACKAGE_TYPE_FOREIGN + "'" -// + " and rr.recyclingTime between " + startDay +" and "+endDay; - - String sql11 = "select sum("+ columnSql2Or10Or11 +") from RecyclingRecord rr join RecyclingItem ritem on ritem.recyclingRecord_id = rr.id " - + joinMaterialInstanceSql11 - + " join ClassifyBasket_RecyclingRecord cr on rr.id=cr.RecyclingRecord_ID " - + " join ClassifyBasket cb on cr.ClassifyBasket_ID=cb.id join ClassifyBasket_WashRecord cw on cb.id=cw.ClassifyBasket_ID " - + " join WashAndDisinfectRecord wr on cw.WashAndDisinfectRecord_ID=wr.id " - + " join invoicePlan ip on rr.recyclingApplication_id=ip.id" - + " where ip.depart is not null and ip.depart = '手术室' and wr.id is not null " - + " and ritem.tousseDefinitionId in (select cit.tousseDefinitionID from ClassifyBasket clb,ClassifiedItem cit where clb.id=cit.classifybasket_id and cb.id=clb.id) " - + " and rr.orgUnitCoding = '"+querySupplyRoom+"'" - + " and (rr.recyclingTime between "+ startDay +" and " + endDay + ")"; - list.add(getYearWorkloadBean(i,month, 5, "清洗外来手术器械总件数", sql11)); - - //“处理外来手术器械总包数”： - // 数据获取为：统计外来器械包回收的包数量，申请科室为“手术室” - // 包装外来手术器械总包数。数据获取：二次回收的外来器械不统计 - sql = "select sum(ri.amount) from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " - + " join invoicePlan ip on rr.recyclingApplication_id=ip.id " - + " where rr.depart='手术室' and td.tousseType= '" + TousseDefinition.PACKAGE_TYPE_FOREIGN + "'" - + " and rr.id = (select max(rr1.id) from RecyclingRecord rr1,ForeignTousseApplication fta1 where rr1.recyclingApplication_id=fta1.id and fta1.id=ip.id) " - + " and rr.orgUnitCoding='" + querySupplyRoom + "' " - + " and rr.recyclingTime between " + startDay +" and "+endDay; - list.add(getYearWorkloadBean(i,month, 6, "包装外来手术器械总包数", sql)); - -/****************************************灭菌炉***************************************/ - - //清洗机使用炉次”： - //数据获取为：所有清洗机的运作次数 - sql = "select count(*) from WashAndDisinfectRecord wr, Rinser r " - + " where r.id=wr.rinserId " - + " and wr.disinfectIdentification not like '%手工%' " - + " and wr.orgUnitCoding='" + querySupplyRoom + "' " - + " and wr.startDate between " + startDay +" and "+endDay; - list.add(getYearWorkloadBean(i,month, 8, "清洗机使用炉次", sql)); - - // 清洗消毒物品总件数：数据获取：清洗消毒物品类型材料的总件数 - sql = "select sum(wr.disinfectMaterialAmount) from WashAndDisinfectRecord wr , Rinser r " - + " where r.id=wr.rinserId " - + " and r.orgUnitCoding = '" + querySupplyRoom + "'" - + " and wr.startDate between " + startDay +" and "+endDay; - list.add(getYearWorkloadBean(i,month, 8, "清洗消毒物品总件数", sql)); - - // 处理纸塑包总包数 - String sql7 = " select count(*) from Invoice i,TousseInstance ti,TousseDefinition td " - + " where i.id=ti.invoice_id and ti.tousseDefinition_id=td.id and ti.orgUnitCoding = '" + querySupplyRoom + "'" - + " and td.packageType = '纸塑' " - + " and i.sendTime between " + startDay +" and "+endDay; - Integer amount7 = dataIndex.getAmountBySql(sql7); - String columnName7 = "处理纸塑包总包数"; - YearWorkloadBean bean7 = new YearWorkloadBean(i,month, 9, columnName7, amount7); - list.add(bean7); - - //“处理敷料包总包数”：数据获取为： - //敷料包类型，统计敷料包的材料总数，按照装配数量为准 -// sql = "select count(*) from tousseInstance ti " -// + " where ti.operationTime between " + startDay +" and " + endDay; - - sql = "select sum(pr.amount) from PackingRecord pr,tousseDefinition td " - + " where pr.tousseDefinitionId=td.id " - + " and td.tousseType = '" + TousseDefinition.PACKAGE_TYPE_DRESSING + "'" - + " and pr.orgUnitCoding='" + querySupplyRoom + "' " - + " and pr.packTime between " + startDay +" and " + endDay; - list.add(getYearWorkloadBean(i,month, 10, "处理敷料包总包数", sql)); - - //“处理敷料包总件数”：数据获取为： - //敷料包类型，统计敷料包的材料总数，按照装配数量为准 - String sql12 = "select sum("+ columnSql12 +") from PackingRecord pr,TousseDefinition td" - + joinMaterialInstanceSql12 - + " where pr.tousseDefinitionId=td.id " - + joinConditionSql12 - + " and td.tousseType = '" +TousseDefinition.PACKAGE_TYPE_DRESSING +"'" - + " and pr.orgUnitCoding='" + querySupplyRoom + "' " - + " and pr.packTime between " + startDay +" and " + endDay; - list.add(getYearWorkloadBean(i,month, 11, "处理敷料包总件数", sql12)); - - // 处理消毒物品总包数 - String sql5 = " select count(*) from Invoice i,TousseInstance ti,TousseDefinition td " - + " where i.id=ti.invoice_id and ti.tousseDefinition_id=td.id and ti.orgUnitCoding = '" + querySupplyRoom + "'" - + " and td.tousseType = '消毒物品' " - + " and i.sendTime between " + startDay +" and "+endDay; - Integer amount5 = dataIndex.getAmountBySql(sql5); - String columnName5 = "处理消毒物品总包数"; - YearWorkloadBean bean5 = new YearWorkloadBean(i,month, 12, columnName5, amount5); - list.add(bean5); - - // “处理消毒包总包数”： - // 数据获取为：包名称为“消毒包”，按照发货数量统计 - sql = "select count(*) from Invoice i,TousseInstance ti,TousseDefinition td where i.id=ti.invoice_id and ti.tousseDefinition_id=td.id and td.name='消毒包'" - + " and ti.orgUnitCoding='" + querySupplyRoom + "' " - + " and i.sendTime between " + startDay +" and "+endDay; - list.add(getYearWorkloadBean(i,month, 13, "处理消毒包总包数 ", sql)); - - //“处理湿化瓶总包数”： - //数据获取为：包名称中包含“湿化瓶”字眼的器械包，按照回收数量统计 - sql = "select sum(ri.amount) from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " - + " where td.name like '%湿化瓶%'" - + " and rr.orgUnitCoding='" + querySupplyRoom + "' " - + " and rr.recyclingTime between " + startDay +" and "+endDay; - list.add(getYearWorkloadBean(i,month, 14, "处理湿化瓶总包数", sql)); - - // 处理呼吸机管道总包数，数据获取器械包名称包含呼吸机管道的统计 - String sql9 = " select count(*) from Invoice i,TousseInstance ti,TousseDefinition td " - + " where i.id=ti.invoice_id and ti.tousseDefinition_id=td.id and ti.orgUnitCoding = '" + querySupplyRoom + "'" - + " and td.name like '%呼吸机管道%' " - + " and i.sendTime between " + startDay +" and "+endDay; - Integer amount9 = dataIndex.getAmountBySql(sql9); - String columnName9 = "处理呼吸机管道总包数"; - YearWorkloadBean bean9 = new YearWorkloadBean(i,month, 15, columnName9, amount9); - list.add(bean9); - - // 处理电钻总个数，数据获取器械包名称包含电钻的统计 - String sql8 = " select count(*) from Invoice i,TousseInstance ti,TousseDefinition td " - + " where i.id=ti.invoice_id and ti.tousseDefinition_id=td.id and ti.orgUnitCoding = '" + querySupplyRoom + "'" - + " and td.name like '%电钻%' " - + " and i.sendTime between " + startDay +" and "+endDay; - Integer amount8 = dataIndex.getAmountBySql(sql8); - String columnName8 = "处理电钻总个数"; - YearWorkloadBean bean8 = new YearWorkloadBean(i,month, 16, columnName8, amount8); - list.add(bean8); - - - String sql4 = "select count(*) as invoiceAmount from invoice i,tousseinstance t " - + "where i.sendTime between " + startDay + " and " + endDay - + " and t.invoice_id = i.id" + " and i.orgUnitCoding = '"+querySupplyRoom+"'"; - Integer amount4 = dataIndex.getAmountBySql(sql4); - - String columnName4 = "器械包发放数量"; - YearWorkloadBean bean4 = new YearWorkloadBean(i,month, 17, columnName4, amount4); - list.add(bean4); - - /**********end***************/ - //“处理外来手术器械总包数”： - //数据获取为：统计外来器械包回收的包数量，申请科室为“手术室” -// sql = "select sum(ri.amount) from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " -// + " join invoicePlan ip on rr.recyclingApplication_id=ip.id" -// + " where rr.depart='手术室' and td.tousseType= '" + TousseDefinition.PACKAGE_TYPE_FOREIGN + "'" -// + " and rr.recyclingTime between " + startDay +" and "+endDay; -// list.add(getYearWorkloadBean(i,month, 5, "处理外来手术器械总包数", sql)); -// -// // 处理外来手术器械总件数 -// sql = " select sum(foreignMaterialAmount) as foreignMaterialAmount " -// +" from WashAndDisinfectRecord w " -// +" where w.endDate between "+ startDay +" and "+endDay -// +" and w.orgUnitCoding = '"+querySupplyRoom+"'"; -// list.add(getYearWorkloadBean(i,month, 9, "处理外来手术器械总件数", sql)); - //获取所有灭菌炉分组 - int sequence = 4; - List groupList = httpOptionManager.getHttpOptionTextById("sterilerGroup"); - if(CollectionUtils.isNotEmpty(groupList)){ - for (String ownGroup : groupList) { - String sql3 = "select count(*) from SterilizationRecord sr,Sterilizer s where " - + " sr.sterilizer_id = s.id and (sr.status = '灭菌完成' or sr.status = '灭菌失败') and sr.enddate between " - + startDay + " and " + endDay - + " and sr.orgUnitCoding = '"+querySupplyRoom+"'" - + " and s.ownGroup = '" + ownGroup + "'"; - if(ownGroup.contains("低温") || ownGroup.contains("高温")){ - // 如果是高温和低温组，灭菌炉名称不包含'4#' - sql3 += " and s.name not like '%4#%'"; + } + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + String yearMonth = yearMonthMap.get(month); + addYearWorkLoadBean(1, month, totalWashMaterialAmountMap, i, "清洗器械总件数", list, yearMonth); + addYearWorkLoadBean(3, month, noOperatingRoomWashMaterialAmountMap, i, "清洗临床科室物品总件数", list, yearMonth); + addYearWorkLoadBean(4, month, operatingRoomWashMaterialAmountMap, i, "清洗手术室器械总件数", list, yearMonth); + } + totalWashMaterialAmountMap = null; + noOperatingRoomWashMaterialAmountMap = null; + noOperatingRoomWashMaterialAmountMap = null; + //清洗外来手术器械总件数 + + params.extraQuery = " and ci.orgUnitName='手术室' "; + params.tousseTypeAndPackageSizeSql = " and td.tousseType='"+ TousseDefinition.PACKAGE_TYPE_FOREIGN +"' "; + sql = String.format("select sum(tl.amount) amount, monthstr from (" + +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by monthstr "); + Map foreignMaterialAmountMap = dataIndex.getAmountAndDateBySql(sql); + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + String yearMonth = yearMonthMap.get(month); + addYearWorkLoadBean(5, month, foreignMaterialAmountMap, i, "清洗外来手术器械总件数", list, yearMonth); + } + foreignMaterialAmountMap = null; + // 包装外来手术器械总包数 二次回收的外来器械不统计 + String dateStr = dateQueryAdapter.dateConverAdapter3("rr.recyclingTime","mm"); + sql = "select sum(ri.amount) amount,"+ dateStr +" monthstr from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " + + " join invoicePlan ip on rr.recyclingApplication_id=ip.id " + + " where td.tousseType= '" + TousseDefinition.PACKAGE_TYPE_FOREIGN + "'" + + " and rr.id = (select max(rr1.id) from RecyclingRecord rr1,ForeignTousseApplication fta1 where rr1.recyclingApplication_id=fta1.id and fta1.id=ip.id) " + + " and rr.orgUnitCoding='" + querySupplyRoom + "' " + + " and rr.recyclingTime between " + startDay +" and "+endDay + + " group by " + dateStr; + Map foreignAmountMap = dataIndex.getAmountAndDateBySql(sql); + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + String yearMonth = yearMonthMap.get(month); + addYearWorkLoadBean(20, month, foreignAmountMap, i, "包装外来手术器械总包数", list, yearMonth); + } + foreignAmountMap = null; + //处理湿化瓶总包数 数据获取为：包名称中包含“湿化瓶”字眼的器械包，按照回收数量统计 + sql = "select sum(ri.amount) amount,"+ dateStr +" monthstr from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " + + " where td.name like '%湿化瓶%'" + + " and rr.orgUnitCoding='" + querySupplyRoom + "' " + + " and rr.recyclingTime between " + startDay +" and "+endDay + + " group by " + dateStr; + Map nameLikeHumidificationBottleAmountMap = dataIndex.getAmountAndDateBySql(sql); + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + String yearMonth = yearMonthMap.get(month); + addYearWorkLoadBean(28, month, nameLikeHumidificationBottleAmountMap, i, "处理湿化瓶总包数", list, yearMonth); + } + nameLikeHumidificationBottleAmountMap = null; + dateStr = dateQueryAdapter.dateConverAdapter3("wr.startDate","mm"); + //清洗机使用炉次”： + //数据获取为：所有清洗机的运作次数 + sql = "select count(*) amount,"+ dateStr +" monthstr from WashAndDisinfectRecord wr, Rinser r " + + " where r.id=wr.rinserId " + + " and wr.disinfectIdentification not like '%手工%' " + + " and wr.orgUnitCoding='" + querySupplyRoom + "' " + + " and wr.startDate between " + startDay +" and "+endDay + + " group by " + dateStr; + Map rinserAmountMap = dataIndex.getAmountAndDateBySql(sql); + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + String yearMonth = yearMonthMap.get(month); + addYearWorkLoadBean(21, month, rinserAmountMap, i, "清洗机使用炉次", list, yearMonth); + } + rinserAmountMap = null; + // 清洗消毒物品总件数： 消毒物品清洗件数 + params.applicationDepart=""; + params.extraQuery = ""; + params.tousseTypeAndPackageSizeSql = " and td.tousseType='" + TousseDefinition.PACKAGE_TYPE_DISINFECTION + "' "; + sql = String.format("select sum(tl.amount) amount, monthstr from (" + +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by monthstr "); + Map washDisinfectMaterialAmountMap = dataIndex.getAmountAndDateBySql(sql); + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + String yearMonth = yearMonthMap.get(month); + addYearWorkLoadBean(22, month, washDisinfectMaterialAmountMap, i, "清洗消毒物品总件数", list, yearMonth); + } + washDisinfectMaterialAmountMap = null; + //处理敷料包总包数 统计敷料包的装配包数 + dateStr = dateQueryAdapter.dateConverAdapter3("pr.packTime","mm"); + String monthlyStr = dateQueryAdapter.dateConverAdapter3("ti.operationTime",mmOrdd); + params.monthlyStr = monthlyStr; + params.tousseTypeAndPackageSizeSql= " and td.tousseType = '" + TousseDefinition.PACKAGE_TYPE_DRESSING + "'"; + sql = String.format("select sum(tl.amount) amount, monthstr from (" + +dataIndex.getWorkAmountByPackageSQL("配包数量", params) + + ") tl group by monthstr "); + Map packDressingAmountMap = dataIndex.getAmountAndDateBySql(sql); + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + String yearMonth = yearMonthMap.get(month); + addYearWorkLoadBean(24, month, packDressingAmountMap, i, "处理敷料包总包数", list, yearMonth); + } + packDressingAmountMap = null; + // 处理敷料包总件数 敷料包类型，统计敷料包的装配件数 + params.extraSelectColumns = "," + params.monthlyStr + " monthStr "; + params.extraGroupBy = monthlyStr; + sql = String.format("select sum(tl.amount) amount, monthstr from (" + +dataIndex.getWorkAmountByMaterialSQL("配包数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by monthstr "); + Map packDressingMaterialAmountMap = dataIndex.getAmountAndDateBySql(sql); + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + String yearMonth = yearMonthMap.get(month); + addYearWorkLoadBean(25, month, packDressingMaterialAmountMap, i, "处理敷料包总件数", list, yearMonth); + } + //待灭菌物品包装总数。统计除了消毒物品以外的所有类型的包的包装总数量，按包计算。 + params.tousseTypeAndPackageSizeSql= ""; + params.extraQuery = " and td.tousseType<>'"+ TousseDefinition.PACKAGE_TYPE_DISINFECTION +"' "; + params.extraSelectColumns = ""; + params.extraGroupBy = ""; + params.tousseTypes = ""; + sql = String.format("select sum(tl.amount) amount, monthstr from (" + +dataIndex.getWorkAmountByPackageSQL("配包数量", params) + + ") tl group by monthstr "); + Map packingAmountMap = dataIndex.getAmountAndDateBySql(sql); + //构建list,主要是没数据的月份也需要有YearWorkloadBean对象返回,所以没有在resultset里添加 + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + String yearMonth = yearMonthMap.get(month); + addYearWorkLoadBean(2, month, packingAmountMap, i, "待灭菌物品包装总数", list, yearMonth); + } + packingAmountMap = null; + params.includeTousses = true; + params.includeDisposableGoods = false; + monthlyStr = dateQueryAdapter.dateConverAdapter3("i.sendTime",mmOrdd); + params.extraSelectColumns = "," + monthlyStr + " monthStr "; + params.extraGroupBy = monthlyStr; + // 处理消毒物品总包数 + params.tousseTypeAndPackageSizeSql = " and td.tousseType = '"+ TousseDefinition.PACKAGE_TYPE_DISINFECTION +"' "; + sql = "select sum(tl.amount) amount,monthStr from (" + +dataIndex.getWorkAmountByPackageSQL("发货数量", params) + + ") tl group by monthStr"; + Map disinfectionAmountMap = dataIndex.getAmountAndDateBySql(sql); + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + String yearMonth = yearMonthMap.get(month); + addYearWorkLoadBean(26, month, disinfectionAmountMap, i, "处理消毒物品总包数", list, yearMonth); + } + disinfectionAmountMap = null; + //处理呼吸机管道总包数，获取器械包名称包含呼吸机管道的发货包数量 + params.tousseTypeAndPackageSizeSql = ""; + params.extraWhereSql = " and td.name like '%呼吸机管道%' "; + sql = "select sum(tl.amount) amount,monthStr from (" + +dataIndex.getWorkAmountByPackageSQL("发货数量", params) + + ") tl group by monthStr"; + Map nameLikeVentilatorCuctAmountMap = dataIndex.getAmountAndDateBySql(sql); + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + String yearMonth = yearMonthMap.get(month); + addYearWorkLoadBean(29, month, nameLikeVentilatorCuctAmountMap, i, "处理呼吸机管道总包数", list, yearMonth); + } + nameLikeVentilatorCuctAmountMap = null; + // 处理电钻总个数，获取器械包名称包含电钻的发货包数 + params.extraWhereSql = " and td.name like '%电钻%' "; + sql = "select sum(tl.amount) amount,monthStr from (" + +dataIndex.getWorkAmountByPackageSQL("发货数量", params) + + ") tl group by monthStr"; + Map nameLikeElectricDrillAmountMap = dataIndex.getAmountAndDateBySql(sql); + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + String yearMonth = yearMonthMap.get(month); + addYearWorkLoadBean(30, month, nameLikeElectricDrillAmountMap, i, "处理电钻总个数", list, yearMonth); + } + nameLikeElectricDrillAmountMap = null; + //器械包发放数量 + params.extraWhereSql = ""; + sql = String.format("select sum(tl.amount) amount,monthStr from (" + +dataIndex.getWorkAmountByPackageSQL("发货数量", params) + + ") tl group by monthStr"); + Map sendAmountMap = dataIndex.getAmountAndDateBySql(sql); + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + String yearMonth = yearMonthMap.get(month); + addYearWorkLoadBean(31, month, sendAmountMap, i, "器械包发放数量", list, yearMonth); + } + sendAmountMap = null; + //处理消毒包总包数 数据获取为：包名称为“消毒包”，按照发货包数量统计 + params.extraWhereSql = " and td.name='消毒包' "; + sql = String.format("select sum(tl.amount) amount,monthStr from (" + +dataIndex.getWorkAmountByPackageSQL("发货数量", params) + + ") tl group by monthStr"); + Map nameIsDisinfectionBagAmountMap = dataIndex.getAmountAndDateBySql(sql); + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + String yearMonth = yearMonthMap.get(month); + addYearWorkLoadBean(27, month, nameIsDisinfectionBagAmountMap, i, "处理消毒包总包数", list, yearMonth); + } + nameIsDisinfectionBagAmountMap = null; + //处理纸塑包总包数 包装类型为纸塑的发货包数 + params.packageTypeSqlWithAliasOfTousseDefinitionIsTd = " and td.packageType='纸塑' "; + params.extraWhereSql = ""; + sql = String.format("select sum(tl.amount) amount,monthStr from (" + +dataIndex.getWorkAmountByPackageSQL("发货数量", params) + + ") tl group by monthStr"); + Map paperAmountMap = dataIndex.getAmountAndDateBySql(sql); + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + String yearMonth = yearMonthMap.get(month); + addYearWorkLoadBean(23, month, paperAmountMap, i, "处理纸塑包总包数", list, yearMonth); + } + paperAmountMap = null; + //获取所有灭菌炉分组 + int sequence = 6; + List groupList = httpOptionManager.getHttpOptionTextById("sterilerGroup"); + if(CollectionUtils.isNotEmpty(groupList)){ + dateStr = dateQueryAdapter.dateConverAdapter3("sr.enddate","mm"); + for (String ownGroup : groupList) { + sql = "select count(*) amount,"+ dateStr +" monthstr from SterilizationRecord sr,Sterilizer s where " + + " sr.sterilizer_id = s.id and (sr.status = '灭菌完成' or sr.status = '灭菌失败') and sr.enddate between " + + startDay + " and " + endDay + + " and sr.orgUnitCoding = '"+querySupplyRoom+"'" + + " and s.ownGroup = '" + ownGroup + "'"; + if(ownGroup.contains("低温") || ownGroup.contains("高温")){ + // 如果是高温和低温组，灭菌炉名称不包含'4#' + sql += " and s.name not like '%4#%'"; + } + sql += " group by " + dateStr; + String columnName3 = "灭菌器使用炉次 (" + ownGroup + ")"; + Map ownGroupAmountMap = dataIndex.getAmountAndDateBySql(sql); + for (int i = 0; i < monthArr.length; i++) { + String month = monthArr[i]; + Integer amount = 0; + if(ownGroupAmountMap.containsKey(month)){ + amount = ownGroupAmountMap.get(month); } - String columnName3 = "灭菌器使用炉次 (" + ownGroup + ")"; - Integer amount3 = dataIndex.getAmountBySql(sql3); - YearWorkloadBean bean3 = new YearWorkloadBean(i,month, ++ sequence, columnName3, amount3); - list.add(bean3); + YearWorkloadBean bean = new YearWorkloadBean(sequence,yearMonthMap.get(month), i, columnName3, amount); + list.add(bean); } + sequence++; } - } return list; } @@ -8686,7 +8645,7 @@ String tousseTypeAndPackageSizeSql = String.format(" and %s and %s ", tousseTypeSql, packageSizeSql); - ReportQueryParams params = new ReportQueryParams(); + ReportQueryParams params = new ReportQueryParams(objectDao); params.betweenSql = betweenSql; params.querySupplyRoom = querySupplyRoom; params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; @@ -12330,7 +12289,7 @@ String tousseTypeAndPackageSizeSql = String.format(" and %s and %s ", tousseTypeSql, packageSizeSql); - ReportQueryParams params = new ReportQueryParams(); + ReportQueryParams params = new ReportQueryParams(objectDao); params.betweenSql = betweenSql; params.querySupplyRoom = departCoding; params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd; @@ -12646,7 +12605,7 @@ */ private String getGoodsRiskLevelReportDataSql(String startTime, String endTime, String departCoding){ WorkQualityCollection bean = new WorkQualityCollection(); - ReportQueryParams reportParams = new ReportQueryParams(); + ReportQueryParams reportParams = new ReportQueryParams(objectDao); String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startTime),dateQueryAdapter.dateAdapter(endTime)); reportParams.betweenSql = betweenSql; reportParams.sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); @@ -18655,7 +18614,7 @@ List months = getMonthList(year); List departs = supplyRoomConfigManager.getSupplyRoomNameList(); - ReportQueryParams reportParams = new ReportQueryParams(); + ReportQueryParams reportParams = new ReportQueryParams(objectDao); reportParams.betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDay), dateQueryAdapter.dateAdapter(endDay)); Index: ssts-web/src/main/webapp/jasperRtp/yearWorkloadReportSpecial.jasper =================================================================== diff -u Binary files differ Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java =================================================================== diff -u -r33190 -r33524 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java (.../ReportQueryParams.java) (revision 33190) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java (.../ReportQueryParams.java) (revision 33524) @@ -5,6 +5,7 @@ import org.apache.commons.lang.StringUtils; import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseDefinition; +import com.forgon.tools.hibernate.ObjectDao; /** @@ -148,7 +149,9 @@ * @return */ public boolean getIsQueryCom(){ - if(StringUtils.isBlank(tousseTypes) || tousseTypes.indexOf(TousseDefinition.PACKAGE_TYPE_COMBO) != -1 || "全部".equals(tousseTypes)){ + if(!haveCombo){ + isQueryCom = false; + }else if(StringUtils.isBlank(tousseTypes) || tousseTypes.indexOf(TousseDefinition.PACKAGE_TYPE_COMBO) != -1 || "全部".equals(tousseTypes)){ isQueryCom = true; } return isQueryCom; @@ -167,6 +170,8 @@ * 只查询聚合包 */ public boolean isOnlyQueryComboTousse = false; + + public boolean haveCombo = true; /** * 器械件数是否按可拆数量计算(目前只有深圳三院的器械包工作量报表使用) */ @@ -182,5 +187,11 @@ } return isOnlyQueryComboTousse; } - + public ReportQueryParams(){} + public ReportQueryParams(ObjectDao objectDao){ + //包定义没有聚合包定义 查询聚合包时也不需要查询 节省资源 提高效率 + if(objectDao.countBySql("select count(*) from " + TousseDefinition.class.getSimpleName() +" where tousseType='"+ TousseDefinition.PACKAGE_TYPE_COMBO +"'") == 0){ + this.haveCombo = false; + } + } } Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java =================================================================== diff -u -r33303 -r33524 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 33303) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 33524) @@ -239,7 +239,7 @@ */ public String getWorkAmountByMaterialSQL(String workType, ReportQueryParams obj, Integer dataSoureOfMaterialsCountOfToussesInReports) { - String s = "sum(case td.tousseType when '消毒物品' then ti.statisticsAmount else 1 end)"; + String statisticsAmountSql = "sum(case td.tousseType when '消毒物品' then ti.statisticsAmount else 1 end)"; String amountSql = ""; String amountColumnSql = null; String joinMaterialInstanceSql = null; @@ -482,7 +482,8 @@ && StringUtils .isBlank(obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd) && StringUtils.isBlank(obj.sterilizationModeSqlWithAliasOfTousseDefinitionIsTd) - && StringUtils.isBlank(obj.packageTypeSqlWithAliasOfTousseDefinitionIsTd)) { + && StringUtils.isBlank(obj.packageTypeSqlWithAliasOfTousseDefinitionIsTd) + && !obj.extraQuery.contains("ci.")) { amountSql += " union all "; amountSql += String.format("select %s %s %s %s %s %s " + getWashMaterialAmountSqlFromTousseTypeIsMaterial(obj.extraJoinCondition) @@ -657,7 +658,7 @@ if (TousseDefinition.WORKLOAD_STATISTICAL_METHOD_PROPERTY.equals(workLoadStatisticalMethodOfDisinfectGoodsMaterial)){ amountSql = String - .format("select " + s + " amount %s %s %s " + .format("select " + statisticsAmountSql + " amount %s %s %s " + "%s from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + "inner join MaterialInstance mi on td.id = mi.tousse_id " + "where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and ti.operationTime %s %s %s and %s %s %s " @@ -685,14 +686,15 @@ amountSql = String .format("select sum(%s) amount %s %s %s " + "%s from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " - + " %s where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and ti.operationTime %s %s %s and %s %s %s " + + " %s where %s and ti.operationTime %s %s %s and %s %s %s " + "%s %s %s %s %s", amountColumnSql, obj.selectUserName?",case when (ti.operator is null) then '系统用户' else ti.operator end userName":"", obj.selectTousseType ? ",td.tousseType" : "", obj.selectTousseName ? ",td.name tousseName" : "", obj.extraSelectColumns, joinMaterialInstanceSql, + obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", obj.betweenSql, SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), SqlUtils.get_InSql_Extra("ti.depart", obj.applicationDepart), @@ -763,7 +765,7 @@ // 按属性统计 if (TousseDefinition.WORKLOAD_STATISTICAL_METHOD_PROPERTY.equals(workLoadStatisticalMethodOfDisinfectGoodsMaterial)){ amountSql = String - .format("select " + obj.monthlyStr + " monthstr, " + s + " amount " + .format("select " + obj.monthlyStr + " monthstr, " + statisticsAmountSql + " amount " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + "inner join MaterialInstance mi on td.id = mi.tousse_id " + "where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and ti.operationTime %s %s " @@ -783,12 +785,13 @@ amountSql = String .format("select " + obj.monthlyStr + " monthstr, sum(%s) amount " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " - + " %s where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and ti.operationTime %s %s " + + " %s where %s and ti.operationTime %s %s " + "%s %s %s %s " + "group by " + obj.monthlyStr, amountColumnSql, joinMaterialInstanceSql, + obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", obj.betweenSql, SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, @@ -953,11 +956,12 @@ amountSql = String .format("select sum(%s) amount ,ti.reviewer userName " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " - + " %s where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and ti.reviewTime %s %s " + + " %s where %s and ti.reviewTime %s %s " + "and ti.reviewer is not null and %s(ti.reviewer)<>0 %s %s %s %s " + "group by ti.reviewer", amountColumnSql, joinMaterialInstanceSql, + obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", obj.betweenSql, SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), obj.sqlLengthFunctionName, @@ -1002,11 +1006,12 @@ amountSql = String .format("select sum(%s) amount ,ti.sterilizationUser userName " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID join SterilizationRecord sr on sr.id=ti.sterilizationRecord_id " - + " %s where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and " + dateQueryAdapter.stringFieldToDate("ti.sterileEndTime") + " %s %s " + + " %s where %s and " + dateQueryAdapter.stringFieldToDate("ti.sterileEndTime") + " %s %s " + "and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 and ti.sterilizationRecord_id is not null %s %s %s %s " + "group by ti.sterilizationUser", amountColumnSql, joinMaterialInstanceSql, + obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", obj.betweenSql, SqlUtils.get_InSql_Extra("sr.orgUnitCoding", obj.querySupplyRoom), obj.sqlLengthFunctionName, @@ -1071,11 +1076,12 @@ amountSql += String .format(" union all select sum(%s) amount %s " + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " - + " %s where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and i.sendTime %s %s %s and %s %s %s " + + " %s where %s and i.sendTime %s %s %s and %s %s %s " + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s", queryDisposableGoodAmountSql, obj.extraSelectColumns, joinMaterialInstanceSql, + obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", obj.betweenSql, SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), SqlUtils.get_InSql_Extra("i.depart", obj.applicationDepart), @@ -1204,11 +1210,12 @@ amountSql = String .format("select sum(%s) amount ,i.assistantSender userName " + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " - + " %s where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and i.sendTime %s %s " + + " %s where %s and i.sendTime %s %s " + "and i.assistantSender is not null and %s(i.assistantSender)<>0 %s %s %s %s " + "group by i.assistantSender", amountColumnSql, joinMaterialInstanceSql, + obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", obj.betweenSql, SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), obj.sqlLengthFunctionName, @@ -1267,12 +1274,13 @@ amountSql = String .format("select sum(%s) amount ,i.personInCharge userName " + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " - + " %s where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and i.sendTime %s %s " + + " %s where %s and i.sendTime %s %s " + "and i.personInCharge is not null and %s(i.personInCharge)<>0 " + "%s %s %s %s " + "group by i.personInCharge", amountColumnSql, joinMaterialInstanceSql, + obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", obj.betweenSql, SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), obj.sqlLengthFunctionName, @@ -1516,7 +1524,7 @@ amountSql = String .format("select %s count(*) amount %s %s %s %s " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " - + "where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and ti.operationTime %s %s %s and %s %s %s " + + "where %s and ti.operationTime %s %s %s and %s %s %s " + "%s %s %s %s %s " + "%s ", getDatePeriodSelectSql(obj.monthlyStr), @@ -1525,6 +1533,7 @@ obj.selectTousseName ? selectTousseNameSql : "", obj.extraSelectColumns, obj.extraJoinCondition, + obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", obj.betweenSql, SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), SqlUtils.get_InSql_Extra("ti.depart", obj.applicationDepart), @@ -1639,12 +1648,13 @@ amountSql = String .format("select count(*) amount %s %s %s " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " - + "where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and ti.reviewTime %s %s %s and %s %s %s " + + "where %s and ti.reviewTime %s %s %s and %s %s %s " + "and ti.reviewer is not null and %s(ti.reviewer)<>0 %s %s %s %s " + " %s", obj.selectUserName ? ",ti.reviewer userName" : "", obj.selectTousseType ? ",td.tousseType" : "", obj.selectTousseName ? ",td.name tousseName" : "", + obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", obj.betweenSql, SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), SqlUtils.get_InSql_Extra("ti.depart", obj.applicationDepart), @@ -1708,7 +1718,7 @@ amountSql = String .format("select %s count(*) amount %s %s %s %s " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID join SterilizationRecord sr on sr.id=ti.sterilizationRecord_id %s " - + "where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and " + dateQueryAdapter.stringFieldToDate("ti.sterileEndTime") + " %s %s %s and %s %s %s " + + "where %s and " + dateQueryAdapter.stringFieldToDate("ti.sterileEndTime") + " %s %s %s and %s %s %s " + "and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 " + "and ti.sterilizationRecord_id is not null %s %s %s %s %s " + "%s ", @@ -1718,6 +1728,7 @@ obj.selectTousseName ? ",td.name tousseName " : "", obj.extraSelectColumns, obj.extraJoinCondition, + obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", obj.betweenSql, SqlUtils.get_InSql_Extra("sr.orgUnitCoding", obj.querySupplyRoom), SqlUtils.get_InSql_Extra("ti.depart", obj.applicationDepart), @@ -1785,14 +1796,15 @@ .format("select sum(ii.amount) amount %s %s %s %s " + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " + "inner join TousseDefinition td on td.id=ii.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID %s " - + "where ii.comboTousseInstanceId is null and i.sendTime %s %s %s and %s %s %s " + + "where %s and i.sendTime %s %s %s and %s %s %s " + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s %s " + " %s", obj.selectUserName ? ",i.sender userName" : "", obj.selectTousseType ? ",td.tousseType" : "", obj.selectTousseName ? ",td.name tousseName" : "", obj.extraSelectColumns, obj.extraJoinCondition, + obj.haveCombo?"ii.comboTousseInstanceId is null":"1=1", obj.betweenSql, SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), @@ -1915,10 +1927,11 @@ amountSql = String .format("select sum(ii.amount) amount %s " + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id inner join TousseDefinition td on td.id=ii.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID " - + "where ii.comboTousseInstanceId is null and i.sendTime %s %s " + + "where %s and i.sendTime %s %s " + "and i.assistantSender is not null and %s(i.assistantSender)<>0 %s %s %s %s " + "group by i.assistantSender ", obj.selectUserName ? ",i.assistantSender userName" : "", + obj.haveCombo?"ii.comboTousseInstanceId is null":"1=1", obj.betweenSql, SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), obj.sqlLengthFunctionName, @@ -1970,10 +1983,11 @@ amountSql = String .format("select count(*) amount, td.tousseType, td.name tousseName " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id=td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " - + "where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and ti.signedDate %s %s %s and %s %s %s " + + "where %s and ti.signedDate %s %s %s and %s %s %s " + "and ti.signedUser is not null and %s(ti.signedUser)<>0 %s %s %s %s %s " + "group by td.tousseType,td.name", obj.extraJoinCondition, + obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", obj.betweenSql, SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), SqlUtils.get_InSql_Extra("ti.depart", obj.applicationDepart), @@ -2726,6 +2740,29 @@ } /** + * 执行sql获取时间和数量， + * @param sql + * @return + */ + public Map getAmountAndDateBySql(String sql){ + if(StringUtils.isBlank(sql)){ + return new HashMap(); + } + Map resultMap = new HashMap(); + ResultSet rs = null; + try { + rs = objectDao.executeSql(sql); + while (rs.next()) { + resultMap.put(rs.getString("monthstr"), rs.getInt("amount")); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + return resultMap; + } + /** * 获取每日数据的map,sql的查询结果第一个必须是字符串，第二个是数量 * SQL查询要根据日进行分组 * Index: ssts-web/src/main/webapp/jasperRtp/yearWorkloadReportSpecial.jrxml =================================================================== diff -u --- ssts-web/src/main/webapp/jasperRtp/yearWorkloadReportSpecial.jrxml (revision 0) +++ ssts-web/src/main/webapp/jasperRtp/yearWorkloadReportSpecial.jrxml (revision 33524) @@ -0,0 +1,330 @@ + + + + + + + + + +