Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/DataIndex.java =================================================================== diff -u -r25133 -r25134 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/DataIndex.java (.../DataIndex.java) (revision 25133) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/DataIndex.java (.../DataIndex.java) (revision 25134) @@ -99,8 +99,17 @@ return packageSizeSql; } - /** + * 将NULL的用户名转为系统用户 + * @param fieldName 要查询的用户名字段 + * @return + */ + public static String getNullUserNameQuery(String fieldName){ + String sql = String.format("case when (%s is null ) then '系统用户' else %s end ", fieldName, fieldName); + return sql; + } + + /** * 获取器械包类型查询的SQL * @param tousseTypes 器械包类型的字符串，用逗号分隔。如果是全部类型，则为空或者为"全部" * @return Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r25133 -r25134 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 25133) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 25134) @@ -9181,66 +9181,160 @@ if (i == 12) { endDay = (Integer.parseInt(year) + 1) + "-01-01 00:00:00"; } - startDay = dateQueryAdapter.dateAdapter(startDay); - endDay = dateQueryAdapter.dateAdapter(endDay); + + //查询的时间段 + String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDay),dateQueryAdapter.dateAdapter(endDay)); + //数据库计算字符串长度的函数名 + String sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); + + ReportQueryParams params = new ReportQueryParams(); + params.betweenSql = betweenSql; + params.querySupplyRoom = querySupplyRoom; + params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; + params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + params.tousseGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + params.tousseTypeAndPackageSizeSql = ""; + params.sqlLengthFunctionName = sqlLengthFunctionName; + + + +// startDay = dateQueryAdapter.dateAdapter(startDay); +// endDay = dateQueryAdapter.dateAdapter(endDay); + String month = year + "-" + monthFormart; - String sql = "select sum((case when generalMaterialAmount is null then 0 else generalMaterialAmount end) + (case when operateMaterialAmount is null then 0 else operateMaterialAmount end) + (case when foreignMaterialAmount is null then 0 else foreignMaterialAmount end)) as tousseAmount " - +",sum(case when foreignMaterialAmount is null then 0 else foreignMaterialAmount end) as foreignMaterialAmount from WashAndDisinfectRecord w " - +" where w.endDate between "+ startDay +" and "+endDay - +" and w.orgUnitCoding = '"+querySupplyRoom+"'"; - ResultSet rs = objectDao.executeSql(sql); - Integer amount = 0; - Integer amount2 = 0; + ResultSet rs = null; + + // 处理器械总件数 + String sql = String.format("select sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("清洗数量", params) + + ") tl "); + + Integer totalTousseAmount = 0; + rs = objectDao.executeSql(sql); if (rs != null) { try { while (rs.next()) { - amount = rs.getInt(1); - amount2 = rs.getInt(2); + totalTousseAmount = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); - } finally { + }finally { DatabaseUtil.closeResultSetAndStatement(rs); } } + + // 处理外来器械总件数 + params.tousseTypes = TousseDefinition.PACKAGE_TYPE_FOREIGN; + params.tousseTypeAndPackageSizeSql = DataIndex.getTousseTypesAndPackageSizesFilterSQL(TousseDefinition.PACKAGE_TYPE_FOREIGN, null); + sql = String.format("select sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("清洗数量", params) + + ") tl "); + + Integer totalForeignTousseAmount = 0; + rs = objectDao.executeSql(sql); + if (rs != null) { + try { + while (rs.next()) { + totalForeignTousseAmount = rs.getInt(1); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + // 清空参数 + params.tousseTypes = ""; + params.tousseTypeAndPackageSizeSql = ""; + String columnName1 = "处理器械总件数"; - YearWorkloadBean bean = new YearWorkloadBean(i,month, 1, columnName1, amount); + YearWorkloadBean bean = new YearWorkloadBean(i,month, 1, columnName1, totalTousseAmount); list.add(bean); String columnName2 = "处理外来手术器械总件数"; - YearWorkloadBean bean2 = new YearWorkloadBean(i,month, 2, columnName2, amount2); + YearWorkloadBean bean2 = new YearWorkloadBean(i,month, 2, columnName2, totalForeignTousseAmount); list.add(bean2); //消毒物品数量 - String disinfectionSql = String.format("select count(1) from TousseInstance ti join TousseDefinition td on td.id=ti.tousseDefinition_id " - + "where ti.toussefixedBarcode<>1 and ti.operationTime between %s and %s and td.tousseType='%s' and ti.orgUnitCoding='%s' ", - startDay, endDay, TousseDefinition.PACKAGE_TYPE_DISINFECTION, querySupplyRoom); - list.add(new YearWorkloadBean(i,month, 2, "消毒物品数量", objectDao.countBySql(disinfectionSql))); +// String disinfectionSql = String.format("select count(1) from TousseInstance ti join TousseDefinition td on td.id=ti.tousseDefinition_id " +// + "where ti.toussefixedBarcode<>1 and ti.operationTime between %s and %s and td.tousseType='%s' and ti.orgUnitCoding='%s' ", +// startDay, endDay, TousseDefinition.PACKAGE_TYPE_DISINFECTION, querySupplyRoom); + params.tousseTypeAndPackageSizeSql = DataIndex + .getTousseTypesAndPackageSizesFilterSQL( + TousseDefinition.PACKAGE_TYPE_DISINFECTION, null); - String sterilizaSql = " select sum(sr.amount),sum(sr.foreignTousseAmount) " - + " from SterilizationRecord sr where sr.status = '"+ SterilizationRecord.STERILIZATION_STATUS_END +"' " - + " and sr.orgUnitCoding = '"+querySupplyRoom+"'" - + " and sr.endDate between " + startDay +" and "+endDay; - ResultSet rs1 = objectDao.executeSql(sterilizaSql); + sql = String.format("select sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("发货数量", params) + + ") tl "); + + + Integer disinfectionAmount = 0; + rs = objectDao.executeSql(sql); + if (rs != null) { + try { + while (rs.next()) { + disinfectionAmount = rs.getInt(1); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + list.add(new YearWorkloadBean(i,month, 2, "消毒物品数量", disinfectionAmount)); + + + // 灭菌无菌包数 + params.tousseTypeAndPackageSizeSql = ""; + String sterileSql = String.format("select sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("灭菌数量", params) + + ") tl "); + Integer sterilizaAmount = 0; + rs = objectDao.executeSql(sterileSql); + if (rs != null) { + try { + while (rs.next()) { + sterilizaAmount = rs.getInt(1); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + + + // 灭菌外来手术器械包数 + params.tousseTypeAndPackageSizeSql = DataIndex + .getTousseTypesAndPackageSizesFilterSQL( + TousseDefinition.PACKAGE_TYPE_FOREIGN, null); + String sterileForeignSql = String.format("select sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("灭菌数量", params) + + ") tl "); + Integer sterilizaForeignAmount = 0; - if (rs1 != null) { + rs = objectDao.executeSql(sterileForeignSql); + if (rs != null) { try { - while (rs1.next()) { - sterilizaAmount = rs1.getInt(1); - sterilizaForeignAmount = rs1.getInt(2); + while (rs.next()) { + sterilizaForeignAmount = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); - } finally { - DatabaseUtil.closeResultSetAndStatement(rs1); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); } } + // 清空过滤条件 + params.tousseTypeAndPackageSizeSql = ""; + + String columnName3 = "灭菌无菌包数"; YearWorkloadBean bean3 = new YearWorkloadBean(i,month, 3, columnName3, sterilizaAmount); list.add(bean3); @@ -9249,6 +9343,10 @@ YearWorkloadBean bean4 = new YearWorkloadBean(i,month, 4, columnName4, sterilizaForeignAmount); list.add(bean4); + + startDay = dateQueryAdapter.dateAdapter(startDay); + endDay = dateQueryAdapter.dateAdapter(endDay); + //获取所有灭菌炉分组 int sequence = 4; List groupList = httpOptionManager.getHttpOptionTextById("sterilerGroup"); @@ -9278,26 +9376,33 @@ } } - 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+"'"; + - ResultSet rs4 = objectDao.executeSql(sql4); - Integer amount6 = 0; - if (rs4 != null) { + // 器械包发放数量 + params.includeTousses = true; + params.includeDisposableGoods = false; + + String tousseSendSql = String.format("select sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("发货数量", params) + + ") tl "); + + Integer tousseInvoiceSendAmount = 0; + rs = objectDao.executeSql(tousseSendSql); + if (rs != null) { try { - while (rs4.next()) { - amount6 = rs4.getInt(1); + while (rs.next()) { + tousseInvoiceSendAmount = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); }finally { - DatabaseUtil.closeResultSetAndStatement(rs4); + DatabaseUtil.closeResultSetAndStatement(rs); } } + String columnName6 = "器械包发放数量"; - YearWorkloadBean bean6 = new YearWorkloadBean(i,month, ++ sequence, columnName6, amount6); + YearWorkloadBean bean6 = new YearWorkloadBean(i,month, ++ sequence, columnName6, tousseInvoiceSendAmount); list.add(bean6); if(isAddEndoscopic == true){ String sql7 = " select sum(tdc.amount) from Invoice i,TousseInstance ti,TousseDefinition td,(select td.name tdName, sum(mi.count) amount from MaterialInstance mi,MaterialDefinition md, tousseDefinition td " Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java =================================================================== diff -u -r25133 -r25134 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java (.../ReportQueryParams.java) (revision 25133) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java (.../ReportQueryParams.java) (revision 25134) @@ -55,5 +55,10 @@ */ public boolean includeDisposableGoods = false; + /** + * 是否统计值为NULL的用户，默认不统计 + */ + public boolean includeNullUser = false; + }