Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/TousseWorkLoadHelper.java =================================================================== diff -u -r36375 -r36649 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/TousseWorkLoadHelper.java (.../TousseWorkLoadHelper.java) (revision 36375) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/TousseWorkLoadHelper.java (.../TousseWorkLoadHelper.java) (revision 36649) @@ -1,10 +1,12 @@ package com.forgon.disinfectsystem.jasperreports.util; import java.sql.ResultSet; +import java.sql.SQLException; import java.text.Collator; import java.util.ArrayList; import java.util.Collections; import java.util.Comparator; +import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.LinkedHashMap; @@ -187,39 +189,39 @@ String applicationSql = String.format("select 'application' type,tl.tousseName,amount ,tl.tousseType"+ queryTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByPackageSQL("申请数量", params) + ") tl"); - String sql = applicationSql; + //String sql = applicationSql; querySqlList.add(applicationSql); //回收数量（按包统计） if("是".equals(isProxyDisinfection)){ params.extraQuery = " and rr.recyclingApplication_id in (select id from InvoicePlan where type='"+ InvoicePlan.TYPE_PROXYDISINFECTION +"') " + instrumentSetTypeSetSql; }else if("否".equals(isProxyDisinfection)){ params.extraQuery = " and (rr.recyclingApplication_id is null or rr.recyclingApplication_id not in (select id from InvoicePlan where type='"+ InvoicePlan.TYPE_PROXYDISINFECTION +"')) " + instrumentSetTypeSetSql; } - sql += " union all "; + //sql += " union all "; String recycSql = String.format("select 'recyc' type,tl.tousseName,amount ,tl.tousseType"+ queryTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByPackageSQL("回收数量", params) + ") tl"); - sql += recycSql; + //sql += recycSql; querySqlList.add(recycSql); //清点数量（按包统计） - sql += " union all "; + //sql += " union all "; String inventorySql = String.format("select 'inventory' type,tl.tousseName,amount ,tl.tousseType"+ queryTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByPackageSQL("清点数量", params) + ") tl"); - sql += inventorySql; + //sql += inventorySql; querySqlList.add(inventorySql); - sql += " union all "; + //sql += " union all "; int dataSoureOfMaterialsCountOfToussesInReports = CssdUtils.getSystemSetConfigByNameInt("dataSoureOfMaterialsCountOfToussesInReports", 3); //清点数量(按材料统计) String inventoryMaterialSql = String.format("select 'inventory-material' type,tl.tousseName,amount ,tl.tousseType"+ queryTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByMaterialSQL("清点数量", params, dataSoureOfMaterialsCountOfToussesInReports) + ") tl"); - sql += inventoryMaterialSql; + //sql += inventoryMaterialSql; querySqlList.add(inventoryMaterialSql); - sql += " union all "; + //sql += " union all "; //清洗数量（按包统计） if("是".equals(isProxyDisinfection)){ params.extraQuery = " and ci.invoicePlanID in (select id from InvoicePlan where type='"+ InvoicePlan.TYPE_PROXYDISINFECTION +"') " + instrumentSetTypeSetSql; @@ -232,22 +234,22 @@ String washSql = String.format("select 'wash' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType"+ queryMaxTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByPackageSQL("清洗数量", params) + ") tl group by tl.tousseType,tl.tousseName "); - sql += washSql; + //sql += washSql; querySqlList.add(washSql); params.extraSelectColumns1 = tdcTousseGroupNameSQL; params.extraSelectColumns2 = tdcTousseGroupNameSQL; params.extraSelectColumns3 = tdcTousseGroupNameSQL; params.extraSelectColumns4 = nullTousseGroupNameSQL; //清洗数量（按材料统计） - sql += " union all "; + //sql += " union all "; String washMaterialSql = String.format("select 'wash-material' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType"+ queryMaxTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + ") tl group by tl.tousseType,tl.tousseName "); params.extraSelectColumns1 = ""; params.extraSelectColumns2 = ""; params.extraSelectColumns3 = ""; params.extraSelectColumns4 = ""; - sql += washMaterialSql; + //sql += washMaterialSql; querySqlList.add(washMaterialSql); params.extraSelectColumns = maxTdcTousseGroupNameSQL; //清洗数量（按包统计） @@ -257,42 +259,42 @@ params.extraQuery = " and ti.proxyDisinfection_id is null " + instrumentSetTypeSetSql; } //装配数量（按包统计） - sql += " union all "; + //sql += " union all "; String packingSql = String.format("select 'packing' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType"+ queryMaxTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByPackageSQL("配包数量", params) + ") tl group by tl.tousseType,tl.tousseName "); - sql += packingSql; + //sql += packingSql; querySqlList.add(packingSql); if(statisticTousseWorkLoadIncludeDisposableGoodsAmountObj !=null){ params.includeDisposableGoodsInTousse = statisticTousseWorkLoadIncludeDisposableGoodsAmountObj.optBoolean("装配", true); }else{ params.includeDisposableGoodsInTousse = true; } //装配数量（按材料统计） - sql += " union all "; + //sql += " union all "; String packingMaterialSql = String.format("select 'packing-material' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType"+ queryMaxTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByMaterialSQL("配包数量", params, dataSoureOfMaterialsCountOfToussesInReports) + ") tl group by tl.tousseType,tl.tousseName "); - sql += packingMaterialSql; + //sql += packingMaterialSql; querySqlList.add(packingMaterialSql); if(queryPackagingAmount){ //打包数量（按包统计） - sql += " union all "; + //sql += " union all "; String packagingSql = String.format("select 'packaging' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType"+ queryMaxTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByPackageSQL("打包数量", params) + ") tl group by tl.tousseType,tl.tousseName "); - sql += packingSql; + //sql += packingSql; querySqlList.add(packagingSql); //打包数量（按材料统计） - sql += " union all "; + //sql += " union all "; String packagingMaterialSql = String.format("select 'packaging-material' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType"+ queryMaxTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByMaterialSQL("打包数量", params, dataSoureOfMaterialsCountOfToussesInReports) + ") tl group by tl.tousseType,tl.tousseName "); - sql += packingMaterialSql; + //sql += packingMaterialSql; querySqlList.add(packagingMaterialSql); } //审核数量（按包统计） - sql += " union all "; + //sql += " union all "; // TODO:以下的代码加了包定义中是否统计审核的工作量的判断，需要考虑该属性是否需要 // sql += String.format("select 'review' as type,t.tousseName,count(*),ti.tousseType from TousseInstance t,TousseDefinition ti " // +" where t.tousseDefinition_id=ti.id %s %s %s and t.orgUnitCoding = '%s' and t.reviewTime between %s and %s and t.foreignTousseApp_id is null " @@ -304,37 +306,37 @@ String reviewSql = String.format("select 'review' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType"+ queryMaxTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByPackageSQL("审核数量", params) + ") tl group by tl.tousseType,tl.tousseName "); - sql += reviewSql; + //sql += reviewSql; querySqlList.add(reviewSql); //灭菌数量（按包统计） - sql += " union all "; + //sql += " union all "; String sterilizationSql = String.format("select 'sterilization' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType"+ queryMaxTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByPackageSQL("灭菌数量", params) + ") tl group by tl.tousseType,tl.tousseName "); - sql += sterilizationSql; + //sql += sterilizationSql; querySqlList.add(sterilizationSql); if("是".equals(isProxyDisinfection)){ params.extraQuery = " and i.invoiceplan_id in (select id from InvoicePlan where type='"+ InvoicePlan.TYPE_PROXYDISINFECTION +"') " + instrumentSetTypeSetSql; }else if("否".equals(isProxyDisinfection)){ params.extraQuery = " and (i.invoiceplan_id is null or i.invoiceplan_id not in (select id from InvoicePlan where type='"+ InvoicePlan.TYPE_PROXYDISINFECTION +"')) " + instrumentSetTypeSetSql; } //发货数量（按包统计） - sql += " union all "; + //sql += " union all "; String invoiceSql = String.format("select 'invoice' type,tl.tousseName,sum(tl.amount) amount,tl.tousseType"+ queryMaxTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByPackageSQL("发货数量", params) + ") tl group by tl.tousseName,tl.tousseType "); - sql += invoiceSql; + //sql += invoiceSql; querySqlList.add(invoiceSql); if(queryUrgentAmount){ String urgentAmountSql = String.format("select 'invoiceUrgent' type,tl.tousseName,sum(tl.amount) amount,tl.tousseType"+ queryMaxTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByPackageSQL("发货中包含加急的包数量", params) + ") tl group by tl.tousseName,tl.tousseType "); - sql += " union all " + urgentAmountSql; + //sql += " union all " + urgentAmountSql; querySqlList.add(urgentAmountSql); } //发货数量（按材料数量统计） - sql += " union all "; + //sql += " union all "; if(statisticTousseWorkLoadIncludeDisposableGoodsAmountObj !=null){ params.includeDisposableGoodsInTousse = statisticTousseWorkLoadIncludeDisposableGoodsAmountObj.optBoolean("发货", false); }else{ @@ -345,7 +347,7 @@ String invoiceMaterialSql = String.format("select 'invoice-material' type,tl.tousseName,sum(tl.amount) amount,tl.tousseType"+ queryMaxTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByMaterialSQL("发货数量", params, dataSoureOfMaterialsCountOfToussesInReports) + ") tl group by tl.tousseName,tl.tousseType "); - sql += invoiceMaterialSql; + //sql += invoiceMaterialSql; querySqlList.add(invoiceMaterialSql); params.extraSelectColumns = maxTdcTousseGroupNameSQL; params.groupBySql = ""; @@ -355,41 +357,59 @@ params.extraQuery = " and ti.proxyDisinfection_id is null " + instrumentSetTypeSetSql; } //签收数量（按包统计） - sql += " union all "; + //sql += " union all "; String signedSql = String.format("select 'signed' type,tl.tousseName,sum(tl.amount) amount,tl.tousseType"+ queryMaxTousseGroupNameSql +" from (" +dataIndex.getWorkAmountByPackageSQL("签收数量", params) + ") tl group by tl.tousseName,tl.tousseType "); - sql += signedSql; + //sql += signedSql; querySqlList.add(signedSql); Map> typeOfChild = new LinkedHashMap>(); GoodsOption option = goodsOptionManager.getGoodsOption(GoodsOption.MODEL_TOUSSEWORKLOAD, departCoding); - logger.debug(" 器械包工作量统计报表后台查询sql...=" + sql); + //logger.debug(" 器械包工作量统计报表后台查询sql...=" + sql); //ResultSet result = objectDao.executeSql(sql); Set tousseGroupNames = null;//器械包分组组数 if(queryTousseGroupName){ tousseGroupNames = new HashSet(); } try { // 若用户自定义了需要查询的器械包，则根据自定义查询，否则查全部的器械包 - HashMap selectedTousseNameMap = new HashMap(); + Set selectedTousseNameMap = new HashSet(); if (option != null && StringUtils.isNotBlank(option.getValue())) { // 选择的包名称 String[] selectedGoodsNameArray = option.getValue().split(";"); if(DatabaseUtil.isPoIdValid(selectedGoodsNameArray[0])){ Set ids = SqlUtils.splitStringToSet(option.getValue(), ";", true); - List tds = objectDao.findByHql("select po from " + TousseDefinition.class.getSimpleName() + " po where " - + SqlUtils.getNonStringFieldInLargeCollectionsPredicate("po.id", ids)); - if(CollectionUtils.isNotEmpty(tds)){ - for (TousseDefinition td : tds) { - selectedTousseNameMap.put(td.getName(), - td.getName()); + ResultSet rs = null; + try { + rs = objectDao.executeSql("select name from " + TousseDefinition.class.getSimpleName() + " po where " + + SqlUtils.getNonStringFieldInLargeCollectionsPredicate("po.id", ids)); + while(rs.next()){ + selectedTousseNameMap.add(rs.getString(1)); } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); } + ResultSet rs2 = null; + try { + rs2 = objectDao.executeSql("select td.name from " + TousseDefinition.class.getSimpleName() + + " td join "+ TousseDefinition.class.getSimpleName() + +" tdc on tdc.id=td.parentID where td.tousseType='" + + TousseDefinition.PACKAGE_TYPE_SPLIT +"' and " + + SqlUtils.getNonStringFieldInLargeCollectionsPredicate("tdc.ancestorID", ids)); + while(rs2.next()){ + selectedTousseNameMap.add(rs2.getString(1)); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs2); + } }else{ for (String selectedGoodsName : selectedGoodsNameArray) { - selectedTousseNameMap.put(selectedGoodsName, - selectedGoodsName); + selectedTousseNameMap.add(selectedGoodsName); } } } @@ -399,20 +419,16 @@ // String tousseName = result.getString(2); // Integer amount = result.getInt(3); // String tousseType = result.getString(4); - List tousseWorkLoadList = queryTousseWorkLoadBySql(querySqlList); + List tousseWorkLoadList = queryTousseWorkLoadBySql(querySqlList,queryTousseGroupName); if(CollectionUtils.isEmpty(tousseWorkLoadList)){ return new ArrayList(); } + String sumQuery = "sumQuery"; 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)) { - formatedName = tousseNameAndTypeConvert(tousseName); - } HashMap nameOfChild = typeOfChild .get(tousseType); @@ -421,15 +437,12 @@ nameOfChild = new HashMap(); typeOfChild.put(tousseType, nameOfChild); } - // 如果设置了查询范围，但当前的包不在所选择的范围，则继续下一个包 // 特别注意：如果设置了包类型，则所有的材料都查不到了 - if (selectedTousseNameMap.size() > 0 - && selectedTousseNameMap.get(tousseName) == null - && selectedTousseNameMap.get(formatedName) == null) { + if(selectedTousseNameMap.size() > 0 && !selectedTousseNameMap.contains(tousseName)){ continue; } - if("sumQuery".equals(queryType)){ + if(sumQuery.equals(queryType)){ tousseName = "空"; } TousseWorkLoadChildVO child = nameOfChild.get(tousseName); @@ -489,21 +502,20 @@ * @param queryTousseWorkLoadSqlList 查询语句集合 * @return */ - private List queryTousseWorkLoadBySql(List queryTousseWorkLoadSqlList){ + private List queryTousseWorkLoadBySql(List queryTousseWorkLoadSqlList, boolean queryTousseGroupName){ List result = new ArrayList(); if(CollectionUtils.isNotEmpty(queryTousseWorkLoadSqlList)){ ResultSet rs = null; for(String querySql : queryTousseWorkLoadSqlList){ try{ rs = objectDao.executeSql(querySql); - boolean queryTousseGroupName = SqlUtils.isExistColumn(rs, "tousseGroupName"); while(rs.next()){ String type = rs.getString(1); String tousseName = rs.getString(2); Integer amount = rs.getInt(3); String tousseType = rs.getString(4); if(queryTousseGroupName){ - result.add(new Object[]{type, tousseName , amount , tousseType, rs.getString("tousseGroupName")}); + result.add(new Object[]{type, tousseName , amount , tousseType, rs.getString(5)}); }else{ result.add(new Object[]{type, tousseName , amount , tousseType}); } @@ -561,20 +573,4 @@ childVO.setInvoiceUrgent(childVO.getInvoiceUrgent() + amount); } } - /** - * 将外来器械拆分小包的名字和类型转换为不带部分的名字 - * @param tousseName 外来器械拆分小包的名字 - * @return 去掉了(部分n)后缀的名字 - */ - private String tousseNameAndTypeConvert(String tousseName){ - // 对于外来器械拆分小包，取包名称的前部分作比较 - String formatedTousseName = tousseName; - - int index = formatedTousseName.lastIndexOf("("); - if (index > 0) { - formatedTousseName = formatedTousseName.substring(0, index); - } - - return formatedTousseName; - } }