Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/QualityMonitoringReportTaskGroupReportHelper.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/QualityMonitoringReportTaskGroupReportHelper.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/QualityMonitoringReportTaskGroupReportHelper.java (revision 35804) @@ -0,0 +1,385 @@ +package com.forgon.disinfectsystem.jasperreports.util; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.text.DecimalFormat; +import java.util.ArrayList; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.apache.commons.collections4.CollectionUtils; +import org.apache.commons.lang.StringUtils; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.stereotype.Component; + +import com.forgon.disinfectsystem.basedatamanager.supplyroomconfig.service.SupplyRoomConfigManager; +import com.forgon.disinfectsystem.common.CssdUtils; +import com.forgon.disinfectsystem.entity.basedatamanager.taskGroup.TaskGroup; +import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseDefinition; +import com.forgon.disinfectsystem.entity.customform.formdefinition.FormDefinition; +import com.forgon.disinfectsystem.jasperreports.javabeansource.QualityMonitoringReportTaskGroupVo; +import com.forgon.disinfectsystem.jasperreports.service.dataindex.DataIndex; +import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams; +import com.forgon.exception.SystemException; +import com.forgon.tools.db.DatabaseUtil; +import com.forgon.tools.util.SqlUtils; + +/** + * + * 各任务组质量监测汇总报表 + * + */ +@Component +public class QualityMonitoringReportTaskGroupReportHelper extends ReportHelper{ + @Autowired + private SupplyRoomConfigManager supplyRoomConfigManager; + /** + * 获取各任务组质量监测汇总报表数据 + * @param startTime 开始时间 + * @param endTime 结束时间 + * @return + */ + public List getQualityMonitoringReportTaskGroupReportDate( + String startTime, String endTime) { + List resultList = new ArrayList(); + String handleDepartCoding = supplyRoomConfigManager.getFirstSupplyRoomConfig().getOrgUnitCoding(); + if(StringUtils.isBlank(handleDepartCoding)){ + throw new SystemException("没有设置一级供应室"); + } + String field = "amount"; + ReportQueryParams params = new ReportQueryParams(objectDao); + params.betweenSql = " between " + dateQueryAdapter.dateAdapter(startTime) + + " and " + dateQueryAdapter.dateAdapter(endTime); + params.querySupplyRoom = handleDepartCoding;//大院供应室 + params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; + params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + params.tousseGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + params.tousseTypeAndPackageSizeSql = ""; + //params.sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); + int dataSoureOfMaterialsCountOfToussesInReports = CssdUtils.getSystemSetConfigByNameInt("dataSoureOfMaterialsCountOfToussesInReports", 3); + + // 处理外来器械总件数 + params.tousseTypes = TousseDefinition.PACKAGE_TYPE_FOREIGN; + params.tousseTypeAndPackageSizeSql = DataIndex.getTousseTypesAndPackageSizesFilterSQL(TousseDefinition.PACKAGE_TYPE_FOREIGN, null); + String forgonWashSql = String.format("select sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl "); + String extraJoin = ""; + DecimalFormat dft = new DecimalFormat("0.00"); + Long totalProMaterialAmount = objectDao.getALongNum(forgonWashSql, field); + Long qualityMonitoringMaterialAmount = objectDao.getALongNum(getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒", null," and qmg.tousseDefinitionId in (select id from TousseDefinition where tousseType in('外来器械包','外来器械拆分小包'))",extraJoin,"",""), field); + Long backWashMaterialAmount = objectDao.getALongNum(getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒", null," and qmg.tousseDefinitionId in (select id from TousseDefinition where tousseType in('外来器械包','外来器械拆分小包')) " + + "and fi.id in (select formInstance_id from FormInstanceItem where answer like '%重洗%') " + ,extraJoin,"",""), field); + addQualityMonitoringReportTaskGroupVo(resultList, "外来器械清洗" + , totalProMaterialAmount, 0L + , qualityMonitoringMaterialAmount + , 0L, backWashMaterialAmount + , getBackwashRate(dft, backWashMaterialAmount, totalProMaterialAmount), + totalProMaterialAmount - qualityMonitoringMaterialAmount, 0L + , getPassRate(dft, qualityMonitoringMaterialAmount, totalProMaterialAmount)); + + params.tousseTypes = ""; + params.tousseTypeAndPackageSizeSql= ""; + //临床器械清洗 器械包分组为【通用手术包】、【专科手术包】、【通用器械包】、【专科器械包】的清洗总件数 + params.extraQuery = " and tdc.tousseGroupName in('通用手术包','专科手术包','通用器械包','专科器械包') "; + String clinicalInstrumentsWashSql = String.format("select sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl "); + totalProMaterialAmount = objectDao.getALongNum(clinicalInstrumentsWashSql, field); + qualityMonitoringMaterialAmount = objectDao.getALongNum(getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒",null + , " and qmg.tousseDefinitionId in (select td1.id from TousseDefinition td1 join TousseDefinition tdc1 on tdc1.id=td1.ancestorID where tdc1.tousseGroupName in('通用手术包','专科手术包','通用器械包','专科器械包')) " + ,extraJoin,"",""), field); + backWashMaterialAmount = objectDao.getALongNum(getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒",null + , " and qmg.tousseDefinitionId in (select td1.id from TousseDefinition td1 join TousseDefinition tdc1 on tdc1.id=td1.ancestorID where tdc1.tousseGroupName in('通用手术包','专科手术包','通用器械包','专科器械包')) " + + " and fi.id in (select formInstance_id from FormInstanceItem where answer like '%重洗%') " ,extraJoin,"",""), field); + + addQualityMonitoringReportTaskGroupVo(resultList, "临床器械清洗" + , totalProMaterialAmount, 0L + , qualityMonitoringMaterialAmount + , 0L, backWashMaterialAmount + , getBackwashRate(dft, backWashMaterialAmount, totalProMaterialAmount) + , totalProMaterialAmount - qualityMonitoringMaterialAmount, 0L, + getPassRate(dft, qualityMonitoringMaterialAmount, totalProMaterialAmount)); + + //手术器械清洗 器械包分组为【麻一手术包】、【麻二手术包】的清洗总件数 + params.extraQuery = " and tdc.tousseGroupName in('麻一手术包','麻二手术包') "; + String surgicalInstrumentsWashSql = String.format("select sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl "); + totalProMaterialAmount = objectDao.getALongNum(surgicalInstrumentsWashSql, field); + qualityMonitoringMaterialAmount = objectDao.getALongNum(getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒",null + , " and qmg.tousseDefinitionId in (select td1.id from TousseDefinition td1 join TousseDefinition tdc1 on tdc1.id=td1.ancestorID where tdc1.tousseGroupName in('麻一手术包','麻二手术包')) ",extraJoin,"",""), field); + backWashMaterialAmount = objectDao.getALongNum(getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒",null + , " and qmg.tousseDefinitionId in (select td1.id from TousseDefinition td1 join TousseDefinition tdc1 on tdc1.id=td1.ancestorID where tdc1.tousseGroupName in('麻一手术包','麻二手术包')) " + + " and fi.id in (select formInstance_id from FormInstanceItem where answer like '%重洗%') ",extraJoin,"",""), field); + addQualityMonitoringReportTaskGroupVo(resultList, "手术器械清洗" + ,totalProMaterialAmount, 0L + , qualityMonitoringMaterialAmount + , 0L, backWashMaterialAmount + , getBackwashRate(dft, backWashMaterialAmount, totalProMaterialAmount) + , totalProMaterialAmount - qualityMonitoringMaterialAmount, 0L + , getPassRate(dft, qualityMonitoringMaterialAmount, totalProMaterialAmount)); + params.extraQuery = ""; + + //各任务组的装配总包数 + @SuppressWarnings("unchecked") + List taskGroups = objectDao.findByHql("select po from " + TaskGroup.class.getSimpleName() + " po where departCode='"+ handleDepartCoding +"' order by id asc"); + if(CollectionUtils.isNotEmpty(taskGroups)){ + Map taskGroupWashAmountMap = getTaskGroupWashAmountMap(params, dataSoureOfMaterialsCountOfToussesInReports); + extraJoin = " join TousseDefinition td on td.id=qmg.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID "; + String sql = getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒",null + , "",extraJoin,",tdc.taskGroup"," group by tdc.taskGroup"); + Map taskGroupQualityMonitoringAmountMap = getTaskGroupQualityMonitoringAmountMap(sql); + sql = getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒",null + , " and fi.id in (select formInstance_id from FormInstanceItem where answer like '%重洗%') " + ,extraJoin,",tdc.taskGroup"," group by tdc.taskGroup"); + Map backWashTaskGroupQualityMonitoringAmountMap = getTaskGroupQualityMonitoringAmountMap(sql); + for (TaskGroup taskGroup : taskGroups) { + String taskGroupName = taskGroup.getTaskGroupName(); + totalProMaterialAmount = taskGroupWashAmountMap.get(taskGroupName); + if(totalProMaterialAmount == null){ + totalProMaterialAmount = 0L; + } + qualityMonitoringMaterialAmount = taskGroupQualityMonitoringAmountMap.get(taskGroupName); + if(qualityMonitoringMaterialAmount == null){ + qualityMonitoringMaterialAmount = 0L; + } + backWashMaterialAmount = backWashTaskGroupQualityMonitoringAmountMap.get(taskGroupName); + if(backWashMaterialAmount == null){ + backWashMaterialAmount = 0L; + } + addQualityMonitoringReportTaskGroupVo(resultList, taskGroupName + "清洗" + , totalProMaterialAmount , 0L + , qualityMonitoringMaterialAmount + , 0L, backWashMaterialAmount + , getBackwashRate(dft, backWashMaterialAmount, totalProMaterialAmount) + , totalProMaterialAmount - qualityMonitoringMaterialAmount, 0L + , getPassRate(dft, qualityMonitoringMaterialAmount, totalProMaterialAmount)); + } + sql = getQualityMonitoringAmount(true, startTime, endTime, handleDepartCoding, "装配管理",null, "",extraJoin,",tdc.taskGroup"," group by tdc.taskGroup"); + Map taskGroupPackQualityMonitoringAmountMap = getTaskGroupQualityMonitoringAmountMap(sql); + sql = getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "装配管理",null, "",extraJoin,",tdc.taskGroup"," group by tdc.taskGroup"); + Map taskGroupPackQualityMonitoringMaterialAmountMap = getTaskGroupQualityMonitoringAmountMap(sql); + for (TaskGroup taskGroup : taskGroups) { + String taskGroupName = taskGroup.getTaskGroupName(); + Long qualityMonitoringTousseAmount = taskGroupPackQualityMonitoringAmountMap.get(taskGroupName); + if(qualityMonitoringTousseAmount == null){ + qualityMonitoringTousseAmount = 0L; + } + qualityMonitoringMaterialAmount = taskGroupPackQualityMonitoringMaterialAmountMap.get(taskGroupName); + if(qualityMonitoringMaterialAmount == null){ + qualityMonitoringMaterialAmount = 0L; + } + params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = " and tdc.taskGroup ='"+ taskGroupName +"' "; + sql = String.format("select sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByPackageSQL("配包数量", params) + + ") tl"); + + Long totalProTousseAmount = objectDao.getALongNum(sql, field); + + sql = String.format("select sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByMaterialSQL("配包数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl"); + totalProMaterialAmount = objectDao.getALongNum(sql, field); + + addQualityMonitoringReportTaskGroupVo(resultList, taskGroupName + "装配组" + , totalProMaterialAmount , totalProTousseAmount + , qualityMonitoringMaterialAmount + , qualityMonitoringTousseAmount, 0L + , "0.00%", totalProMaterialAmount - qualityMonitoringMaterialAmount + , totalProTousseAmount - qualityMonitoringTousseAmount + , getPassRate(dft, qualityMonitoringTousseAmount, totalProTousseAmount)); + } + } + params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + + //高温灭菌 + params.extraQuery = " and s.ownGroup like '%高温%' "; + params.extraJoinCondition = " join Sterilizer s on s.id=sr.sterilizer_id "; + String highTemperatureSterilizationSql = "select sum(tl.amount) amount from (" + + dataIndex.getWorkAmountByPackageSQL("灭菌数量", params) + +") tl "; + extraJoin = " join TousseInstance ti on qmg.tousseInstanceId=ti.id " + + " join SterilizationRecord sr on sr.id=ti.sterilizationRecord_id " + + " join Sterilizer s on s.id=sr.sterilizer_id "; + Long totalProTousseAmount = objectDao.getALongNum(highTemperatureSterilizationSql, field); + Long qualityMonitoringTousseAmount = objectDao.getALongNum(getQualityMonitoringAmount(true, startTime, endTime, handleDepartCoding, null, "灭菌不合格" + , params.extraQuery,extraJoin,"",""), "amount"); + addQualityMonitoringReportTaskGroupVo(resultList, "高温灭菌", 0L , totalProTousseAmount + , 0L + , qualityMonitoringTousseAmount, 0L + , "0.00%", 0L, totalProTousseAmount - qualityMonitoringTousseAmount + , getPassRate(dft, qualityMonitoringTousseAmount, totalProTousseAmount)); + //低温灭菌 + params.extraQuery = " and s.ownGroup like '%低温%' "; + String lowTemperatureSterilizationSql = "select sum(tl.amount) amount from (" + + dataIndex.getWorkAmountByPackageSQL("灭菌数量", params) + +") tl "; + totalProTousseAmount = objectDao.getALongNum(lowTemperatureSterilizationSql, field); + qualityMonitoringTousseAmount = objectDao.getALongNum(getQualityMonitoringAmount(true, startTime, endTime, handleDepartCoding, null, "灭菌不合格" + , params.extraQuery,extraJoin,"",""), "amount"); + addQualityMonitoringReportTaskGroupVo(resultList, "低温灭菌", 0L , totalProTousseAmount + , 0L + , qualityMonitoringTousseAmount, 0L + , "0.00%", 0L, totalProTousseAmount - qualityMonitoringTousseAmount + , getPassRate(dft, qualityMonitoringTousseAmount, totalProTousseAmount)); + return resultList; + } + /** + * 质量监测登记数 统计责任环节清洗消毒的质量监测记录中的材料件数，责任环节装配管理的质量监测记录中的包数，监测项为灭菌不合格的质量监测记录中的包数 + * @param calcTousse true:统计包 false:统计材料 + * @param startTime 开始时间 + * @param endTime 介绍时间 + * @param departmentCode 质量监测定义处理科室 + * @param responsibilityPart 责任环节 + * @param extraQuery 额外的查询条件 + * @return + */ + private String getQualityMonitoringAmount(boolean calcTousse, String startTime, String endTime, String departmentCode, String responsibilityPart, String formName, String extraQuery, String extraJoin, String extraQueryColumn, String groupBySql){ + String responsibilityPartSql = StringUtils.isNotBlank(responsibilityPart)?" and qmi.responsibilityPart='"+responsibilityPart+"' ":""; + String formNameSql = StringUtils.isNotBlank(formName)?" and fd.formName='"+formName+"' ":""; + String sql = "select sum(qmg.amount) amount " + + extraQueryColumn + + " from QualityMonitoringInstance qmi " + + "join FormInstance fi on fi.id=qmi.id " + + "join FormDefinition fd on fd.id=fi.formDefinition_id " + + "join QualityMonitoringDefinition qmdf on qmdf.id=fd.id " + + "join QualityMonitoringGoods qmg on qmg.qualityMonitoringInstance_id=fi.id " + + extraJoin + + "where " + (calcTousse ? " qmg.tousseName is not null " : " (qmg.tousseName is null or qmg.tousseName='') ") + + responsibilityPartSql + + formNameSql + + " and fd.formType = '" + FormDefinition.FOMRTYPE_QUALITYMONITORING+ "' " + + extraQuery + + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(departmentCode) + + " and " + dateQueryAdapter.dateAreaSql("qmi.datetime", startTime, endTime) + + groupBySql; + return sql; + } + /** + * 获取返洗率 + * @param dft DecimalFormat格式 + * @param backWashMaterialAmount 反洗件数 + * @param totalProMaterialAmount 总件数 + * @return + */ + private String getBackwashRate(DecimalFormat dft,Long backWashMaterialAmount, Long totalProMaterialAmount){ + if(totalProMaterialAmount == null || totalProMaterialAmount == 0){ + return "-"; + } + if(backWashMaterialAmount == null){ + backWashMaterialAmount = 0L; + } + return dft.format(100.00d*backWashMaterialAmount/totalProMaterialAmount) + "%"; + } + /** + * 获取合格率 + * @param dft DecimalFormat格式 + * @param qualityMonitoringAmount 监测包或件数 + * @param totalAmount 总包或件数 + * @return + */ + private String getPassRate(DecimalFormat dft,Long qualityMonitoringAmount, Long totalAmount){ + if(totalAmount == null || totalAmount == 0){ + return "-"; + } + if(qualityMonitoringAmount == null){ + qualityMonitoringAmount = 0L; + } + return dft.format(100.00d*(totalAmount - qualityMonitoringAmount)/totalAmount) + "%"; + } + /** + * 添加QualityMonitoringReportTaskGroupVo bean + * @param resultList vos + * @param monitoringObject 监测对象 + * @param qualityMonitoringAmount 质量监测登记数 + * @param totalProcessing 处理总数 + * @param totalProMaterialAmount 处理总件数 + * @param totalProTousseAmount 处理总包数 + * @param qualityMonitoringMaterialAmount 监测件数 + * @param qualityMonitoringTousseAmount 监测包数 + * @param backWashMaterialAmount 返洗件数 + * @param backwashRate 返洗率 + * @param qualifiedMaterialAmount 合格件数 + * @param qualifiedTousseAmount 合格包数 + * @param passRate 合格率 + */ + private void addQualityMonitoringReportTaskGroupVo(List resultList + , String monitoringObject, Long totalProMaterialAmount + , Long totalProTousseAmount,Long qualityMonitoringMaterialAmount, Long qualityMonitoringTousseAmount + , Long backWashMaterialAmount, String backwashRate, Long qualifiedMaterialAmount + , Long qualifiedTousseAmount, String passRate){ + QualityMonitoringReportTaskGroupVo bean = new QualityMonitoringReportTaskGroupVo(); + bean.setQualityMonitoringMaterialAmount(qualityMonitoringMaterialAmount); + bean.setQualityMonitoringTousseAmount(qualityMonitoringTousseAmount); + bean.setQualifiedMaterialAmount(qualifiedMaterialAmount); + bean.setTotalProMaterialAmount(totalProMaterialAmount); + bean.setBackWashMaterialAmount(backWashMaterialAmount); + bean.setQualifiedTousseAmount(qualifiedTousseAmount); + bean.setTotalProTousseAmount(totalProTousseAmount); + bean.setMonitoringObject(monitoringObject); + bean.setBackwashRate(backwashRate); + bean.setPassRate(passRate); + resultList.add(bean); + } + /** + * 获取任务组的工作量 + * @param sql + * @return + */ + private Map getTaskGroupQualityMonitoringAmountMap(String sql){ + ResultSet rs = null; + Map taskGroupQualityMonitoringAmountMap = new HashMap(); + try { + rs = objectDao.executeSql(sql); + while (rs.next()) { + Long amount = rs.getLong("amount"); + String taskGroup = rs.getString("taskGroup"); + if(StringUtils.isBlank(taskGroup)){ + taskGroup = ""; + } + taskGroupQualityMonitoringAmountMap.put(taskGroup, amount); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + return taskGroupQualityMonitoringAmountMap; + } + /** + * 获取各个任务组的清洗件数 + * @param params + * @param dataSoureOfMaterialsCountOfToussesInReports 配置项 + * @return + */ + private Map getTaskGroupWashAmountMap(ReportQueryParams params, int dataSoureOfMaterialsCountOfToussesInReports){ + params.extraSelectColumns= ",tdc.taskGroup"; + params.extraGroupBy = "group by tdc.taskGroup"; + String taskGroupWashSql = String.format("select sum(tl.amount) amount,taskGroup from (" + +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by taskGroup "); + params.extraSelectColumns= ""; + params.extraGroupBy = ""; + ResultSet rs = null; + Map taskGroupWashAmountMap = new HashMap(); + try { + rs = objectDao.executeSql(taskGroupWashSql); + while(rs.next()){ + String taskGroup = rs.getString("taskGroup"); + if(StringUtils.isBlank(taskGroup)){ + taskGroup = ""; + } + long amount = rs.getLong("amount"); + taskGroupWashAmountMap.put(taskGroup, amount); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + return taskGroupWashAmountMap; + } +} Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r35803 -r35804 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35803) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35804) @@ -188,6 +188,7 @@ import com.forgon.disinfectsystem.jasperreports.util.MonthReportHelper; import com.forgon.disinfectsystem.jasperreports.util.PackingUnqualifieReportHelper; import com.forgon.disinfectsystem.jasperreports.util.QualityMonitoringHelper; +import com.forgon.disinfectsystem.jasperreports.util.QualityMonitoringReportTaskGroupReportHelper; import com.forgon.disinfectsystem.jasperreports.util.RealTimeBulletinBoardWorkloadHelper; import com.forgon.disinfectsystem.jasperreports.util.ReportSqlUtil; import com.forgon.disinfectsystem.jasperreports.util.StatisticalWorkloadHelper; @@ -363,6 +364,8 @@ private WashWorkloadReprotHelper washWorkloadReprotHelper; @Autowired private QualityMonitoringHelper qualityMonitoringHelper; + @Autowired + private QualityMonitoringReportTaskGroupReportHelper qualityMonitoringReportTaskGroupReportHelper; public void setPackingManager(PackingManager packingManager) { this.packingManager = packingManager; } @@ -15241,352 +15244,16 @@ @Override public List getQualityMonitoringReportTaskGroupReportDate( String startTime, String endTime) { - List resultList = new ArrayList(); - String handleDepartCoding = supplyRoomConfigManager.getFirstSupplyRoomConfig().getOrgUnitCoding(); - if(StringUtils.isBlank(handleDepartCoding)){ - throw new SystemException("没有设置一级供应室"); - } - String field = "amount"; - ReportQueryParams params = new ReportQueryParams(objectDao); - params.betweenSql = " between " + dateQueryAdapter.dateAdapter(startTime) - + " and " + dateQueryAdapter.dateAdapter(endTime); - params.querySupplyRoom = handleDepartCoding;//大院供应室 - params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; - params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = ""; - params.tousseGroupSqlWithAliasOfTousseDefinitionIsTd = ""; - params.tousseTypeAndPackageSizeSql = ""; - //params.sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); - int dataSoureOfMaterialsCountOfToussesInReports = CssdUtils.getSystemSetConfigByNameInt("dataSoureOfMaterialsCountOfToussesInReports", 3); - - // 处理外来器械总件数 - params.tousseTypes = TousseDefinition.PACKAGE_TYPE_FOREIGN; - params.tousseTypeAndPackageSizeSql = DataIndex.getTousseTypesAndPackageSizesFilterSQL(TousseDefinition.PACKAGE_TYPE_FOREIGN, null); - String forgonWashSql = String.format("select sum(tl.amount) amount from (" - +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) - + ") tl "); - String extraJoin = ""; - DecimalFormat dft = new DecimalFormat("0.00"); - Long totalProMaterialAmount = objectDao.getALongNum(forgonWashSql, field); - Long qualityMonitoringMaterialAmount = objectDao.getALongNum(getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒", null," and qmg.tousseDefinitionId in (select id from TousseDefinition where tousseType in('外来器械包','外来器械拆分小包'))",extraJoin,"",""), field); - Long backWashMaterialAmount = objectDao.getALongNum(getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒", null," and qmg.tousseDefinitionId in (select id from TousseDefinition where tousseType in('外来器械包','外来器械拆分小包')) " - + "and fi.id in (select formInstance_id from FormInstanceItem where answer like '%重洗%') " - ,extraJoin,"",""), field); - addQualityMonitoringReportTaskGroupVo(resultList, "外来器械清洗" - , totalProMaterialAmount, 0L - , qualityMonitoringMaterialAmount - , 0L, backWashMaterialAmount - , getBackwashRate(dft, backWashMaterialAmount, totalProMaterialAmount), - totalProMaterialAmount - qualityMonitoringMaterialAmount, 0L - , getPassRate(dft, qualityMonitoringMaterialAmount, totalProMaterialAmount)); - - params.tousseTypes = ""; - params.tousseTypeAndPackageSizeSql= ""; - //临床器械清洗 器械包分组为【通用手术包】、【专科手术包】、【通用器械包】、【专科器械包】的清洗总件数 - params.extraQuery = " and tdc.tousseGroupName in('通用手术包','专科手术包','通用器械包','专科器械包') "; - String clinicalInstrumentsWashSql = String.format("select sum(tl.amount) amount from (" - +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) - + ") tl "); - totalProMaterialAmount = objectDao.getALongNum(clinicalInstrumentsWashSql, field); - qualityMonitoringMaterialAmount = objectDao.getALongNum(getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒",null - , " and qmg.tousseDefinitionId in (select td1.id from TousseDefinition td1 join TousseDefinition tdc1 on tdc1.id=td1.ancestorID where tdc1.tousseGroupName in('通用手术包','专科手术包','通用器械包','专科器械包')) " - ,extraJoin,"",""), field); - backWashMaterialAmount = objectDao.getALongNum(getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒",null - , " and qmg.tousseDefinitionId in (select td1.id from TousseDefinition td1 join TousseDefinition tdc1 on tdc1.id=td1.ancestorID where tdc1.tousseGroupName in('通用手术包','专科手术包','通用器械包','专科器械包')) " - + " and fi.id in (select formInstance_id from FormInstanceItem where answer like '%重洗%') " ,extraJoin,"",""), field); + return qualityMonitoringReportTaskGroupReportHelper.getQualityMonitoringReportTaskGroupReportDate(startTime, endTime); + } - addQualityMonitoringReportTaskGroupVo(resultList, "临床器械清洗" - , totalProMaterialAmount, 0L - , qualityMonitoringMaterialAmount - , 0L, backWashMaterialAmount - , getBackwashRate(dft, backWashMaterialAmount, totalProMaterialAmount) - , totalProMaterialAmount - qualityMonitoringMaterialAmount, 0L, - getPassRate(dft, qualityMonitoringMaterialAmount, totalProMaterialAmount)); - - //手术器械清洗 器械包分组为【麻一手术包】、【麻二手术包】的清洗总件数 - params.extraQuery = " and tdc.tousseGroupName in('麻一手术包','麻二手术包') "; - String surgicalInstrumentsWashSql = String.format("select sum(tl.amount) amount from (" - +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) - + ") tl "); - totalProMaterialAmount = objectDao.getALongNum(surgicalInstrumentsWashSql, field); - qualityMonitoringMaterialAmount = objectDao.getALongNum(getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒",null - , " and qmg.tousseDefinitionId in (select td1.id from TousseDefinition td1 join TousseDefinition tdc1 on tdc1.id=td1.ancestorID where tdc1.tousseGroupName in('麻一手术包','麻二手术包')) ",extraJoin,"",""), field); - backWashMaterialAmount = objectDao.getALongNum(getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒",null - , " and qmg.tousseDefinitionId in (select td1.id from TousseDefinition td1 join TousseDefinition tdc1 on tdc1.id=td1.ancestorID where tdc1.tousseGroupName in('麻一手术包','麻二手术包')) " - + " and fi.id in (select formInstance_id from FormInstanceItem where answer like '%重洗%') ",extraJoin,"",""), field); - addQualityMonitoringReportTaskGroupVo(resultList, "手术器械清洗" - ,totalProMaterialAmount, 0L - , qualityMonitoringMaterialAmount - , 0L, backWashMaterialAmount - , getBackwashRate(dft, backWashMaterialAmount, totalProMaterialAmount) - , totalProMaterialAmount - qualityMonitoringMaterialAmount, 0L - , getPassRate(dft, qualityMonitoringMaterialAmount, totalProMaterialAmount)); - params.extraQuery = ""; - //各任务组的装配总包数 - List taskGroups = objectDao.findByHql("select po from " + TaskGroup.class.getSimpleName() + " po where departCode='"+ handleDepartCoding +"' order by id asc"); - if(CollectionUtils.isNotEmpty(taskGroups)){ - Map taskGroupWashAmountMap = getTaskGroupWashAmountMap(params, dataSoureOfMaterialsCountOfToussesInReports); - extraJoin = " join TousseDefinition td on td.id=qmg.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID "; - String sql = getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒",null - , "",extraJoin,",tdc.taskGroup"," group by tdc.taskGroup"); - Map taskGroupQualityMonitoringAmountMap = getTaskGroupQualityMonitoringAmountMap(sql); - sql = getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "清洗消毒",null - , " and fi.id in (select formInstance_id from FormInstanceItem where answer like '%重洗%') " - ,extraJoin,",tdc.taskGroup"," group by tdc.taskGroup"); - Map backWashTaskGroupQualityMonitoringAmountMap = getTaskGroupQualityMonitoringAmountMap(sql); - for (TaskGroup taskGroup : taskGroups) { - String taskGroupName = taskGroup.getTaskGroupName(); - totalProMaterialAmount = taskGroupWashAmountMap.get(taskGroupName); - if(totalProMaterialAmount == null){ - totalProMaterialAmount = 0L; - } - qualityMonitoringMaterialAmount = taskGroupQualityMonitoringAmountMap.get(taskGroupName); - if(qualityMonitoringMaterialAmount == null){ - qualityMonitoringMaterialAmount = 0L; - } - backWashMaterialAmount = backWashTaskGroupQualityMonitoringAmountMap.get(taskGroupName); - if(backWashMaterialAmount == null){ - backWashMaterialAmount = 0L; - } - addQualityMonitoringReportTaskGroupVo(resultList, taskGroupName + "清洗" - , totalProMaterialAmount , 0L - , qualityMonitoringMaterialAmount - , 0L, backWashMaterialAmount - , getBackwashRate(dft, backWashMaterialAmount, totalProMaterialAmount) - , totalProMaterialAmount - qualityMonitoringMaterialAmount, 0L - , getPassRate(dft, qualityMonitoringMaterialAmount, totalProMaterialAmount)); - } - sql = getQualityMonitoringAmount(true, startTime, endTime, handleDepartCoding, "装配管理",null, "",extraJoin,",tdc.taskGroup"," group by tdc.taskGroup"); - Map taskGroupPackQualityMonitoringAmountMap = getTaskGroupQualityMonitoringAmountMap(sql); - sql = getQualityMonitoringAmount(false, startTime, endTime, handleDepartCoding, "装配管理",null, "",extraJoin,",tdc.taskGroup"," group by tdc.taskGroup"); - Map taskGroupPackQualityMonitoringMaterialAmountMap = getTaskGroupQualityMonitoringAmountMap(sql); - for (TaskGroup taskGroup : taskGroups) { - String taskGroupName = taskGroup.getTaskGroupName(); - Long qualityMonitoringTousseAmount = taskGroupPackQualityMonitoringAmountMap.get(taskGroupName); - if(qualityMonitoringTousseAmount == null){ - qualityMonitoringTousseAmount = 0L; - } - qualityMonitoringMaterialAmount = taskGroupPackQualityMonitoringMaterialAmountMap.get(taskGroupName); - if(qualityMonitoringMaterialAmount == null){ - qualityMonitoringMaterialAmount = 0L; - } - params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = " and tdc.taskGroup ='"+ taskGroupName +"' "; - sql = String.format("select sum(tl.amount) amount from (" - +dataIndex.getWorkAmountByPackageSQL("配包数量", params) - + ") tl"); - - Long totalProTousseAmount = objectDao.getALongNum(sql, field); - - sql = String.format("select sum(tl.amount) amount from (" - +dataIndex.getWorkAmountByMaterialSQL("配包数量", params, dataSoureOfMaterialsCountOfToussesInReports) - + ") tl"); - totalProMaterialAmount = objectDao.getALongNum(sql, field); - - addQualityMonitoringReportTaskGroupVo(resultList, taskGroupName + "装配组" - , totalProMaterialAmount , totalProTousseAmount - , qualityMonitoringMaterialAmount - , qualityMonitoringTousseAmount, 0L - , "0.00%", totalProMaterialAmount - qualityMonitoringMaterialAmount - , totalProTousseAmount - qualityMonitoringTousseAmount - , getPassRate(dft, qualityMonitoringTousseAmount, totalProTousseAmount)); - } - } - params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = ""; - - //高温灭菌 - params.extraQuery = " and s.ownGroup like '%高温%' "; - params.extraJoinCondition = " join Sterilizer s on s.id=sr.sterilizer_id "; - String highTemperatureSterilizationSql = "select sum(tl.amount) amount from (" - + dataIndex.getWorkAmountByPackageSQL("灭菌数量", params) - +") tl "; - extraJoin = " join TousseInstance ti on qmg.tousseInstanceId=ti.id " - + " join SterilizationRecord sr on sr.id=ti.sterilizationRecord_id " - + " join Sterilizer s on s.id=sr.sterilizer_id "; - Long totalProTousseAmount = objectDao.getALongNum(highTemperatureSterilizationSql, field); - Long qualityMonitoringTousseAmount = objectDao.getALongNum(getQualityMonitoringAmount(true, startTime, endTime, handleDepartCoding, null, "灭菌不合格" - , params.extraQuery,extraJoin,"",""), "amount"); - addQualityMonitoringReportTaskGroupVo(resultList, "高温灭菌", 0L , totalProTousseAmount - , 0L - , qualityMonitoringTousseAmount, 0L - , "0.00%", 0L, totalProTousseAmount - qualityMonitoringTousseAmount - , getPassRate(dft, qualityMonitoringTousseAmount, totalProTousseAmount)); - //低温灭菌 - params.extraQuery = " and s.ownGroup like '%低温%' "; - String lowTemperatureSterilizationSql = "select sum(tl.amount) amount from (" - + dataIndex.getWorkAmountByPackageSQL("灭菌数量", params) - +") tl "; - totalProTousseAmount = objectDao.getALongNum(lowTemperatureSterilizationSql, field); - qualityMonitoringTousseAmount = objectDao.getALongNum(getQualityMonitoringAmount(true, startTime, endTime, handleDepartCoding, null, "灭菌不合格" - , params.extraQuery,extraJoin,"",""), "amount"); - addQualityMonitoringReportTaskGroupVo(resultList, "低温灭菌", 0L , totalProTousseAmount - , 0L - , qualityMonitoringTousseAmount, 0L - , "0.00%", 0L, totalProTousseAmount - qualityMonitoringTousseAmount - , getPassRate(dft, qualityMonitoringTousseAmount, totalProTousseAmount)); - return resultList; - } - /** - * 添加QualityMonitoringReportTaskGroupVo bean - * @param resultList vos - * @param monitoringObject 监测对象 - * @param qualityMonitoringAmount 质量监测登记数 - * @param totalProcessing 处理总数 - * @param totalProMaterialAmount 处理总件数 - * @param totalProTousseAmount 处理总包数 - * @param qualityMonitoringMaterialAmount 监测件数 - * @param qualityMonitoringTousseAmount 监测包数 - * @param backWashMaterialAmount 返洗件数 - * @param backwashRate 返洗率 - * @param qualifiedMaterialAmount 合格件数 - * @param qualifiedTousseAmount 合格包数 - * @param passRate 合格率 - */ - private void addQualityMonitoringReportTaskGroupVo(List resultList - , String monitoringObject, Long totalProMaterialAmount - , Long totalProTousseAmount,Long qualityMonitoringMaterialAmount, Long qualityMonitoringTousseAmount - , Long backWashMaterialAmount, String backwashRate, Long qualifiedMaterialAmount - , Long qualifiedTousseAmount, String passRate){ - QualityMonitoringReportTaskGroupVo bean = new QualityMonitoringReportTaskGroupVo(); - bean.setQualityMonitoringMaterialAmount(qualityMonitoringMaterialAmount); - bean.setQualityMonitoringTousseAmount(qualityMonitoringTousseAmount); - bean.setQualifiedMaterialAmount(qualifiedMaterialAmount); - bean.setTotalProMaterialAmount(totalProMaterialAmount); - bean.setBackWashMaterialAmount(backWashMaterialAmount); - bean.setQualifiedTousseAmount(qualifiedTousseAmount); - bean.setTotalProTousseAmount(totalProTousseAmount); - bean.setMonitoringObject(monitoringObject); - bean.setBackwashRate(backwashRate); - bean.setPassRate(passRate); - resultList.add(bean); - } - /** - * 质量监测登记数 统计责任环节清洗消毒的质量监测记录中的材料件数，责任环节装配管理的质量监测记录中的包数，监测项为灭菌不合格的质量监测记录中的包数 - * @param calcTousse true:统计包 false:统计材料 - * @param startTime 开始时间 - * @param endTime 介绍时间 - * @param departmentCode 质量监测定义处理科室 - * @param responsibilityPart 责任环节 - * @param extraQuery 额外的查询条件 - * @return - */ - private String getQualityMonitoringAmount(boolean calcTousse, String startTime, String endTime, String departmentCode, String responsibilityPart, String formName, String extraQuery, String extraJoin, String extraQueryColumn, String groupBySql){ - String responsibilityPartSql = StringUtils.isNotBlank(responsibilityPart)?" and qmi.responsibilityPart='"+responsibilityPart+"' ":""; - String formNameSql = StringUtils.isNotBlank(formName)?" and fd.formName='"+formName+"' ":""; - String sql = "select sum(qmg.amount) amount " - + extraQueryColumn - + " from QualityMonitoringInstance qmi " - + "join FormInstance fi on fi.id=qmi.id " - + "join FormDefinition fd on fd.id=fi.formDefinition_id " - + "join QualityMonitoringDefinition qmdf on qmdf.id=fd.id " - + "join QualityMonitoringGoods qmg on qmg.qualityMonitoringInstance_id=fi.id " - + extraJoin - + "where " + (calcTousse ? " qmg.tousseName is not null " : " (qmg.tousseName is null or qmg.tousseName='') ") - + responsibilityPartSql - + formNameSql - + " and fd.formType = '" + FormDefinition.FOMRTYPE_QUALITYMONITORING+ "' " - + extraQuery - + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(departmentCode) - + " and " + dateQueryAdapter.dateAreaSql("qmi.datetime", startTime, endTime) - + groupBySql; - return sql; - } @Override public List getSteAmountEachBranchReportDate( String startTime, String endTime) { return steAmountEachBranchReportHelper.getSteAmountEachBranchReportDate(startTime, endTime); } - - /** - * 获取各个任务组的清洗件数 - * @param params - * @param dataSoureOfMaterialsCountOfToussesInReports 配置项 - * @return - */ - private Map getTaskGroupWashAmountMap(ReportQueryParams params, int dataSoureOfMaterialsCountOfToussesInReports){ - params.extraSelectColumns= ",tdc.taskGroup"; - params.extraGroupBy = "group by tdc.taskGroup"; - String taskGroupWashSql = String.format("select sum(tl.amount) amount,taskGroup from (" - +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) - + ") tl group by taskGroup "); - params.extraSelectColumns= ""; - params.extraGroupBy = ""; - ResultSet rs = null; - Map taskGroupWashAmountMap = new HashMap(); - try { - rs = objectDao.executeSql(taskGroupWashSql); - while(rs.next()){ - String taskGroup = rs.getString("taskGroup"); - if(StringUtils.isBlank(taskGroup)){ - taskGroup = ""; - } - long amount = rs.getLong("amount"); - taskGroupWashAmountMap.put(taskGroup, amount); - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - return taskGroupWashAmountMap; - } - /** - * 获取返洗率 - * @param dft DecimalFormat格式 - * @param backWashMaterialAmount 反洗件数 - * @param totalProMaterialAmount 总件数 - * @return - */ - private String getBackwashRate(DecimalFormat dft,Long backWashMaterialAmount, Long totalProMaterialAmount){ - if(totalProMaterialAmount == null || totalProMaterialAmount == 0){ - return "-"; - } - if(backWashMaterialAmount == null){ - backWashMaterialAmount = 0L; - } - return dft.format(100.00d*backWashMaterialAmount/totalProMaterialAmount) + "%"; - } - /** - * 获取合格率 - * @param dft DecimalFormat格式 - * @param qualityMonitoringAmount 监测包或件数 - * @param totalAmount 总包或件数 - * @return - */ - private String getPassRate(DecimalFormat dft,Long qualityMonitoringAmount, Long totalAmount){ - if(totalAmount == null || totalAmount == 0){ - return "-"; - } - if(qualityMonitoringAmount == null){ - qualityMonitoringAmount = 0L; - } - return dft.format(100.00d*(totalAmount - qualityMonitoringAmount)/totalAmount) + "%"; - } - /** - * 获取任务组的工作量 - * @param sql - * @return - */ - private Map getTaskGroupQualityMonitoringAmountMap(String sql){ - ResultSet rs = null; - Map taskGroupQualityMonitoringAmountMap = new HashMap(); - try { - rs = objectDao.executeSql(sql); - while (rs.next()) { - Long amount = rs.getLong("amount"); - String taskGroup = rs.getString("taskGroup"); - if(StringUtils.isBlank(taskGroup)){ - taskGroup = ""; - } - taskGroupQualityMonitoringAmountMap.put(taskGroup, amount); - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - return taskGroupQualityMonitoringAmountMap; - } @Override public List getCssdWorkloadReportForTangshanWorkersHospital(String tousseTypes, String taskGroup, String departGroupStr, String querySupplyRoom, String startDate, String endDate) { return cssdWorkloadReportForTangshanWorkersHospitalHelper.getCssdWorkloadReportForTangshanWorkersHospital(tousseTypes, taskGroup, departGroupStr, querySupplyRoom, startDate, endDate);