Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java =================================================================== diff -u -r26557 -r26622 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 26557) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 26622) @@ -1355,7 +1355,7 @@ + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " + "inner join TousseDefinition td on td.id=ii.tousseDefinitionId " + "inner join TousseInstance ti on ti.tousseDefinition_id=td.id " - + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and td.tousseType ='聚合包' and i.sendTime %s %s " + + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and ti.invoice_id = i.id and td.tousseType ='"+ TousseDefinition.PACKAGE_TYPE_COMBO +"' and i.sendTime %s %s " + "and i.sender is not null and %s(i.sender)<>0 %s %s %s " + "group by i.sender,td.tousseType,td.name ", TousseDefinition.PACKAGE_TYPE_COMBO, @@ -1436,7 +1436,7 @@ .format("select sum(ii.amount) amount ,i.assistantSender userName,'%s' tousseType, td.name tousseName " + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id inner join TousseDefinition td on td.id=ii.tousseDefinitionId " + "inner join TousseInstance ti on ti.tousseDefinition_id=td.id " - + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and td.tousseType ='聚合包' and i.sendTime %s %s " + + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and td.tousseType ='"+ TousseDefinition.PACKAGE_TYPE_COMBO +"' and i.sendTime %s %s " + "and i.assistantSender is not null and %s(i.assistantSender)<>0 %s %s %s " + "group by i.assistantSender,td.tousseType,td.name ", TousseDefinition.PACKAGE_TYPE_COMBO, Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r26611 -r26622 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 26611) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 26622) @@ -7936,52 +7936,62 @@ String taskGroupFalseSqlCondition = ""; String tousseGroupFalseSqlCondition = ""; - + List querySqlList = new ArrayList(); //申请数量（按包统计） - String sql = String.format("select 'application' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + String applicationSql = String.format("select 'application' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" +dataIndex.getWorkAmountByPackageSQL("申请数量", params) + ") tl group by tl.tousseType,tl.tousseName "); + String sql = applicationSql; + querySqlList.add(applicationSql); //回收数量（按包统计） sql += " union all "; - sql += String.format("select 'recyc' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + String recycSql = String.format("select 'recyc' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" +dataIndex.getWorkAmountByPackageSQL("回收数量", params) + ") tl group by tl.tousseType,tl.tousseName "); + sql += recycSql; + querySqlList.add(recycSql); //清点数量（按包统计） sql += " union all "; - sql += String.format("select 'inventory' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + String inventorySql = String.format("select 'inventory' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" +dataIndex.getWorkAmountByPackageSQL("清点数量", params) + ") tl group by tl.tousseType,tl.tousseName "); + sql += inventorySql; + querySqlList.add(inventorySql); sql += " union all "; - //清洗数量（按包统计） - sql += String.format("select 'wash' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + String washSql = String.format("select 'wash' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" +dataIndex.getWorkAmountByPackageSQL("清洗数量", params) + ") tl group by tl.tousseType,tl.tousseName "); + sql += washSql; + querySqlList.add(washSql); - //清洗数量（按材料统计） sql += " union all "; - sql += String.format("select 'wash-material' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + String washMaterialSql = String.format("select 'wash-material' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params) + ") tl group by tl.tousseType,tl.tousseName "); + sql += washMaterialSql; + querySqlList.add(washMaterialSql); - - //装配数量（按包统计） sql += " union all "; - sql += String.format("select 'packing' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + String packingSql = String.format("select 'packing' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" +dataIndex.getWorkAmountByPackageSQL("配包数量", params) + ") tl group by tl.tousseType,tl.tousseName "); + sql += packingSql; + querySqlList.add(packingSql); //装配数量（按材料统计） sql += " union all "; - sql += String.format("select 'packing-material' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + String packingMaterialSql = String.format("select 'packing-material' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" +dataIndex.getWorkAmountByMaterialSQL("配包数量", params) + ") tl group by tl.tousseType,tl.tousseName "); + sql += packingMaterialSql; + querySqlList.add(packingMaterialSql); //审核数量（按包统计） sql += " union all "; @@ -7993,36 +8003,41 @@ // isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi,taskGroupSqlWithAliasOfTousseDefinitionIsTi,tousseGroupSqlWithAliasOfTousseDefinitionIsTi, // departCoding,startDateSql,endDateSql,Constants.STR_YES,Constants.STR_YES,getAndSql("t.reviewer", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTi); - sql += String.format("select 'review' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + String reviewSql = String.format("select 'review' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" +dataIndex.getWorkAmountByPackageSQL("审核数量", params) + ") tl group by tl.tousseType,tl.tousseName "); + sql += reviewSql; + querySqlList.add(reviewSql); - //灭菌数量（按包统计） sql += " union all "; - - sql += String.format("select 'sterilization' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + String sterilizationSql = String.format("select 'sterilization' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" +dataIndex.getWorkAmountByPackageSQL("灭菌数量", params) + ") tl group by tl.tousseType,tl.tousseName "); + sql += sterilizationSql; + querySqlList.add(sterilizationSql); //发货数量（按包统计） sql += " union all "; - String temp = String.format("select 'invoice' type,tl.tousseName,sum(tl.amount) amount,tl.tousseType from (" + String invoiceSql = String.format("select 'invoice' type,tl.tousseName,sum(tl.amount) amount,tl.tousseType from (" +dataIndex.getWorkAmountByPackageSQL("发货数量", params) + ") tl group by tl.tousseName,tl.tousseType "); - sql += temp; + sql += invoiceSql; + querySqlList.add(invoiceSql); //签收数量（按包统计） sql += " union all "; - sql += String.format("select 'signed' type,tl.tousseName,sum(tl.amount) amount,tl.tousseType from (" + String signedSql = String.format("select 'signed' type,tl.tousseName,sum(tl.amount) amount,tl.tousseType from (" +dataIndex.getWorkAmountByPackageSQL("签收数量", params) + ") tl group by tl.tousseName,tl.tousseType "); + sql += signedSql; + querySqlList.add(signedSql); Map> typeOfChild = new HashMap>(); GoodsOption option = goodsOptionManager.getGoodsOption(GoodsOption.MODEL_TOUSSEWORKLOAD, departCoding); logger.debug("getTousseWorkLoadData sql...=" + sql); - ResultSet result = objectDao.executeSql(sql); + //ResultSet result = objectDao.executeSql(sql); try { // 若用户自定义了需要查询的器械包，则根据自定义查询，否则查全部的器械包 HashMap selectedTousseNameMap = new HashMap(); @@ -8036,12 +8051,20 @@ } } - while (result.next()) { - String type = result.getString(1); - String tousseName = result.getString(2); - Integer amount = result.getInt(3); - String tousseType = result.getString(4); - +// while (result.next()) { +// String type = result.getString(1); +// String tousseName = result.getString(2); +// Integer amount = result.getInt(3); +// String tousseType = result.getString(4); + List tousseWorkLoadList = queryTousseWorkLoadBySql(querySqlList); + if(CollectionUtils.isEmpty(tousseWorkLoadList)){ + return new ArrayList(); + } + for(Object[] tousseWorkLoadObjectArray : tousseWorkLoadList){ + String type = (String)tousseWorkLoadObjectArray[0]; + String tousseName = (String)tousseWorkLoadObjectArray[1]; + Integer amount = (Integer)tousseWorkLoadObjectArray[2]; + String tousseType = (String)tousseWorkLoadObjectArray[3]; // 对于外来器械拆分小包，取包名称的前部分作比较 String formatedName = tousseName; if (TousseDefinition.PACKAGE_TYPE_SPLIT.equals(tousseType)) { @@ -8073,10 +8096,10 @@ setVoAmount(type, amount, child); } - } catch (SQLException e) { + } catch (Exception e) { e.printStackTrace(); }finally { - DatabaseUtil.closeResultSetAndStatement(result); + //DatabaseUtil.closeResultSetAndStatement(result); } List list = new ArrayList(); @@ -8100,8 +8123,40 @@ logger.debug("器械包工作量统计报表后台查询时间：" + (System.currentTimeMillis() - begin) + "毫秒"); return list; } - + /** + * 根据查询语句集合查询结果 + * @param queryTousseWorkLoadSqlList 查询语句集合 + * @return + */ + private List queryTousseWorkLoadBySql(List queryTousseWorkLoadSqlList){ + List result = null; + if(CollectionUtils.isNotEmpty(queryTousseWorkLoadSqlList)){ + ResultSet rs = null; + for(String querySql : queryTousseWorkLoadSqlList){ + try{ + rs = objectDao.executeSql(querySql); + while(rs.next()){ + String type = rs.getString(1); + String tousseName = rs.getString(2); + Integer amount = rs.getInt(3); + String tousseType = rs.getString(4); + if(result == null){ + result = new ArrayList(); + } + result.add(new Object[]{type, tousseName , amount , tousseType}); + } + }catch(Exception e){ + e.printStackTrace(); + }finally{ + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + } + return result; + } + + /** * 将外来器械拆分小包的名字和类型转换为不带部分的名字 * @param tousseName 外来器械拆分小包的名字 * @return 去掉了(部分n)后缀的名字