Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/QualityMonitoringHelper.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/QualityMonitoringHelper.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/QualityMonitoringHelper.java (revision 35802) @@ -0,0 +1,1332 @@ +package com.forgon.disinfectsystem.jasperreports.util; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.HashMap; +import java.util.HashSet; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.Map.Entry; + +import net.sf.json.JSONObject; + +import org.apache.commons.collections4.CollectionUtils; +import org.apache.commons.collections4.MapUtils; +import org.apache.commons.lang.StringUtils; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.stereotype.Component; + +import com.forgon.Constants; +import com.forgon.directory.acegi.tools.AcegiHelper; +import com.forgon.disinfectsystem.basedatamanager.supplyroomconfig.service.SupplyRoomConfigManager; +import com.forgon.disinfectsystem.common.CssdUtils; +import com.forgon.disinfectsystem.entity.customform.formdefinition.FormDefinition; +import com.forgon.disinfectsystem.entity.customform.formdefinition.FormDefinitionItem; +import com.forgon.disinfectsystem.entity.qualitymonitoringmanager.qualitymonitoringconfig.QualityMonitoringDefinition; +import com.forgon.disinfectsystem.jasperreports.javabeansource.MonitoringItemBean; +import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams; +import com.forgon.tools.MathTools; +import com.forgon.tools.StrutsParamUtils; +import com.forgon.tools.db.DatabaseUtil; +import com.forgon.tools.format.ConvertNumber; +import com.forgon.tools.util.SqlUtils; +/** + * 质量监测报表 + * + */ +@Component +public class QualityMonitoringHelper extends ReportHelper{ + @Autowired + private ReportSqlUtil reportSqlUtil; + @Autowired + private CommonReportHelper commonReportHelper; + @Autowired + private SupplyRoomConfigManager supplyRoomConfigManager; + /** + * @param formName 表单名称 + * @param instrumentSetTypes 器械包种类 只查询具体种类 不查询种类下的子类 + * @param responsibilityPart 责任环节 + * @param recordRinseInformation 监测项是否录入清洗信息 + * @param scope 监测范围 + * @param requestParameters 报表需要的参数,可根据数据变更报表模板 + * @param filterAnswer 是否过滤掉空的answer 如果有FormDefinitionItem有必填项,可以过滤掉加快查询和显示效率 + * @return + */ + public List getQualityMonitoringDataSource(String instrumentSetTypes, String formName, String responsibilityPart, String recordRinseInformation, String scope, Map requestParameters, boolean filterAnswer, boolean isShowTousseMaerialSumAmount) { + List list = new ArrayList(); + String startDate = StrutsParamUtils.getPraramValue("startDate", ""); + String endDate = StrutsParamUtils.getPraramValue("endDate", ""); + + String querySupplyRoom = StrutsParamUtils.getPraramValue("querySupplyRoom", ""); + if(querySupplyRoom.contains("全部")){ + querySupplyRoom = ""; + } + String materialName = StrutsParamUtils.getPraramValue("materialName", ""); + String tousseName = StrutsParamUtils.getPraramValue("tousseName", ""); + String fdioValueSql = null;//查询fdio.Value时加上序号,解决报表排序问题 + String answerWhereSql = null; + if(dbConnection.isOracle()){ + if(filterAnswer){ + answerWhereSql = " fiItem.answer is not null and "; + }else{ + answerWhereSql = ""; + } + fdioValueSql = ",case when fdio.orderNumber<10 then '0' || fdio.orderNumber || 'P+' || fdio.value else fdio.orderNumber || 'P+' || fdio.value end value"; + }else{ + if(filterAnswer){ + answerWhereSql = " 1=1 " + SqlUtils.getIsNotNullSql(dbConnection, "fiItem.answer") + " and "; + }else{ + answerWhereSql = ""; + } + fdioValueSql = ",case when fdio.orderNumber<10 then '0' + CONVERT(varchar(10), fdio.orderNumber) + 'P+' + fdio.value else CONVERT(varchar(10), fdio.orderNumber) + 'P+' + fdio.value end value"; + } + String extraQueryForResponsibilityPart = null; + String extraJoinForResponsibilityPart = null; + Map> washAmountMap = null; + if(QualityMonitoringDefinition.QUALITYMONITORING_MODULE_NAME_RECYCLING.equals(responsibilityPart)){ + String recyclingTime = dateQueryAdapter.dateToVarchar1("rr.recyclingTime"); + extraQueryForResponsibilityPart = ",rr.depart,ur.operationRoom,ur.circuitNurse, rr.recyclingUser,"+ recyclingTime +" recyclingTime "; + extraJoinForResponsibilityPart = " left join TousseInstance ti on ti.id=qmi.scanTousseInstance_id " + + " left join RecyclingRecord rr on rr.id=ti.recyclingRecordId " + + " left join UseRecord ur on ur.id=ti.useRecord_id "; + }else if(QualityMonitoringDefinition.QUALITYMONITORING_MODULE_NAME_WASH.equals(responsibilityPart)){ + String wdrStartDate = dateQueryAdapter.dateToVarchar1("wdr.startDate"); + extraQueryForResponsibilityPart = ",wdr.disinfectIdentification,"+ wdrStartDate +" washStartDate,case when qmi.responsiblePerson is null or qmi.responsiblePerson='' then wdr.washPersonInCharge else qmi.responsiblePerson end washPersonInCharge "; + extraJoinForResponsibilityPart = " left join WashAndDisinfectRecord wdr on wdr.id=qmi.scanWashRecord_id "; + if(Constants.STR_YES.equals(recordRinseInformation)){ + ReportQueryParams params = new ReportQueryParams(); + int dataSoureOfMaterialsCountOfToussesInReports = CssdUtils.getSystemSetConfigByNameInt("dataSoureOfMaterialsCountOfToussesInReports", 3); + String sql = reportSqlUtil.getWashWorkloadSql(params, startDate + " 00:00:00", endDate + " 23:59:59", querySupplyRoom, dataSoureOfMaterialsCountOfToussesInReports, true, "yyyy-mm-dd HH24:MI:SS",null); + washAmountMap = commonReportHelper.getWashWorkloadAmountMap(sql, true); + } + }else if(QualityMonitoringDefinition.QUALITYMONITORING_MODULE_NAME_STERILIZATION.equals(responsibilityPart)){ + String wdrStartDate = dateQueryAdapter.dateToVarchar1("sr.startDate"); + extraQueryForResponsibilityPart = ",s.name,sr.frequency,sr.sterilizationUser,"+ wdrStartDate +" sterilizationStartDate,sr.amount steAmount "; + extraJoinForResponsibilityPart = " left join SterilizationRecord sr on sr.id=qmi.sterilizationRecordId " + + " left join Sterilizer s on s.id=sr.sterilizer_id "; + } + String monitoringType = StrutsParamUtils.getPraramValue("monitoringType", ""); + String extraQuery = ""; + String extraJoin = ""; + boolean showSterilizationInfo = false;//是否查询灭菌炉和炉次 + String qmdOrderNumberBySql = ""; + String tempOrderBySql = ""; + boolean materialAndTousse = false; + String materialAmountSql = ""; + String FORMNAMEOFADJ = "无菌包质量抽查登记表"; + if(FORMNAMEOFADJ.equals(formName)){//从关联的灭菌记录获取 + showSterilizationInfo = true; + requestParameters.put("dataType", FORMNAMEOFADJ); + extraQuery = ",sr.frequency,s.name sterilizerName"; + extraJoin = " join SterilizationRecord sr on sr.id=qmi.sterilizationRecordId join Sterilizer s on s.id=sr.sterilizer_id"; + }else if(QualityMonitoringDefinition.SCOPE_STERILIZATIONRECORD.equals(scope) && "定期监测".equals(monitoringType)){//因为没有关联灭菌记录，所以从QualityMonitoringInstance获取 + showSterilizationInfo = true; + extraQuery = ",qmi.sterilizerDate,qmi.sterilizerFrequency frequency,qmi.sterilizerName"; + requestParameters.put("jasperreportName", "routineMonitoringSteScopeReport.jasper"); + requestParameters.put("dataType", "监测范围为灭菌炉记录的定期监测数据"); + }else if(QualityMonitoringDefinition.SCOPE_MATERIAL_AND_TOUSSE.equals(scope) && "质量监测".equals(monitoringType)){ + requestParameters.put("jasperreportName", "routineMonitoringWashScopeReport.jasper"); + qmdOrderNumberBySql = " order by qmi.id desc,fiItem.id desc,qmd.orderNumber asc"; + tempOrderBySql = "order by temp.id desc,temp.orderNumber,temp.qmdOrderNumber asc"; + requestParameters.put("dataType", "监测范围为材料和器械包的质量监测数据"); + if(isShowTousseMaerialSumAmount){ + materialAmountSql = ",(select sum(count) from MaterialInstance join TousseDefinition td on td.id=tousse_id where td.id=qmd.tousseDefinitionId) materialAmount "; + } + materialAndTousse = true; + } + if (StringUtils.isNotBlank(startDate) && StringUtils.isNotBlank(endDate) + && StringUtils.isNotBlank(formName)) { + String joinTousseSql = ""; + String onlyQueryTousseSql = ""; + String tousseSql = ""; + if(StringUtils.isNotBlank(instrumentSetTypes)){ + Set instrumentSetTypeSet = SqlUtils.splitStringToSet(instrumentSetTypes, ","); + if(CollectionUtils.isNotEmpty(instrumentSetTypeSet)){ + tousseSql = reportSqlUtil.getInstrumentSetTypeSql(instrumentSetTypeSet); + joinTousseSql = " join TousseDefinition td on td.id=qmd.tousseDefinitionId "; + onlyQueryTousseSql = " and 1=2 "; + } + } + JSONObject amoutSumJson = new JSONObject(); + String whereSql = buildWhereSQL(startDate, endDate, querySupplyRoom, + formName, monitoringType); + String tousseNameSql = buildTousseNameMaterialNameWhereSql(materialName, tousseName); + String dataSql = dateQueryAdapter.dateToVarchar1("qmi.dateTime"); + String sql = " select * from (select " + dataSql + " dateStr," + + " qmd.tousseName toussName,qmd.material material,qmd.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg," + + " fdi.name fdiName, fdi.orderNumber orderNum"+ fdioValueSql +",fiov.id,fi.createUserName,fdio.orderNumber,qmd.orderNumber qmdOrderNumber,qmd.id qmdid " + + materialAmountSql + + extraQuery + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" + + " join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + + extraJoin + + joinTousseSql + + " join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " + + " and fiov.definitionItemOption_id=fdio.id " + + " where fdi.isReportDisplay='是' " + + tousseSql + + "and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql; + sql+= " union all select distinct " + dataSql + " dateStr," + + " qmd.tousseName toussName,qmd.material material,qmd.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg," + + " '' fdiName,null orderNum,'' value,null id,fi.createUserName,fdio.orderNumber ,qmd.orderNumber qmdOrderNumber,qmd.id qmdid " + + materialAmountSql + + extraQuery + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" + + " left join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + + extraJoin + + joinTousseSql + + " left join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " + + " and fiov.definitionItemOption_id=fdio.id " + + " where fdi.isReportDisplay='是' " + + tousseSql + + " and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql + + " and fiov.id is null and qmi.id not in(" + + " select qmi.id qmiId" + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" + + " join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + + extraJoin + + joinTousseSql + + " join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " + + " and fiov.definitionItemOption_id=fdio.id " + + " where fdi.isReportDisplay='是' " + + tousseSql + + "and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql + + "))temp " + tempOrderBySql; + addMonitoringItemBeanOptionRadioAndCHeck(list,sql,showSterilizationInfo, materialAndTousse); + // 没有录入材料和包，有配了多选的细则的项 + sql = " select " + dataSql + " dateStr," + + " qmi.id qmiId,qmi.positionMsg positionMsg," + + " fdi.name fdiName, fdi.orderNumber orderNum"+ fdioValueSql +",fiov.id,fi.createUserName " + + extraQuery + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" + + " join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id " + + " join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " + + extraJoin + + " and fiov.definitionItemOption_id=fdio.id " + + " where fdi.isReportDisplay='是' " + + onlyQueryTousseSql + + " and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + + " and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmd.id is null)"; + sql += " union all select distinct " + dataSql + " dateStr," + + " qmi.id qmiId,qmi.positionMsg positionMsg," + + " '' fdiName, null orderNum,'' value,null id,fi.createUserName " + + extraQuery + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" + + " join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id " + + " left join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " + + extraJoin + + " and fiov.definitionItemOption_id=fdio.id " + + " where fdi.isReportDisplay='是' " + + onlyQueryTousseSql + + " and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + + " and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmd.id is null)" + + " and fiov.id is null and qmi.id not in (" + + " select qmi.id qmiId " + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" + + " join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id " + + " join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " + + extraJoin + + " and fiov.definitionItemOption_id=fdio.id " + + " where fdi.isReportDisplay='是'" + + onlyQueryTousseSql + + " and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + + " and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmd.id is null)" + + ")"; + addMonitoringItemBeanOptionRadioAndCHeckWithoutMaterial(list,sql,showSterilizationInfo); + sql = " select " + dataSql + " dateStr," + + " qmd.tousseName toussName,qmd.material material,qmd.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg," + + " fdi.name fdiName, fdi.orderNumber orderNum,fiItem.answer amswer,fi.createUserName,qmd.orderNumber qmdOrderNumber,qmd.id qmdid " + + materialAmountSql + + extraQuery + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id " + + " join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + + extraJoin + + joinTousseSql + + " where "+ answerWhereSql +" (fdi.type != '" + FormDefinitionItem.TYPE_RADIO + "' and fdi.isReportDisplay='是' " + + tousseSql + + " and fdi.type!='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql + qmdOrderNumberBySql; + addMonitoringItemBeanOptionSingleResult(list,sql,showSterilizationInfo, materialAndTousse); + // 没有录入材料和包，单一的结果 + sql = " select " + dataSql + " dateStr," + + " qmi.id qmiId,qmi.positionMsg positionMsg," + + " fdi.name fdiName, fdi.orderNumber orderNum,fiItem.answer amswer,fi.createUserName " + + extraQuery + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id " + + extraJoin + + " where "+ answerWhereSql +" (fdi.type != '" + FormDefinitionItem.TYPE_RADIO + "' and fdi.type!='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + + " and fdi.isReportDisplay='是' " + + onlyQueryTousseSql + + "and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmd.id is null) "; + addMonitoringItemBeanOptionSingleResultWithoutMaterial(list,sql,showSterilizationInfo,monitoringType); + + if(FormDefinition.FOMRTYPE_QUALITYMONITORING.equals(monitoringType)){ + sql = " select " + dataSql + " dateStr," + + " qmd.tousseName toussName,qmd.material material,qmd.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg,fi.createUserName " + + materialAmountSql + + extraQuery + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id " + + " join FormDefinition fd on fi.formDefinition_id=fd.id join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + + " left join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id" + + extraJoin + + joinTousseSql + + " where qmi.id not in (select formInstance_id from FormInstanceItem) " + + " and qmi.type = '" + FormDefinition.FOMRTYPE_QUALITYMONITORING + "' " + + tousseSql + + " and " + whereSql + tousseNameSql + qmdOrderNumberBySql; + addMonitoringItemBeanQualityMonitoring(list,sql,showSterilizationInfo, materialAndTousse); + } + //是否无菌包质量抽查登记表,是则隐藏监测结果列 + Map isQualifiedAmount = new HashMap(); + Map isQualifiedMaterialAmount = new HashMap(); + if (!FORMNAMEOFADJ.equals(formName) && FormDefinition.FOMRTYPE_PERIODICMONITORING.equals(monitoringType)) { + sql = " select " + dataSql + " dateStr," + + " qmd.tousseName toussName,qmd.material material,qmd.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg,qmi.result,fi.createUserName " + + extraQuery + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + + extraJoin + + joinTousseSql + + " where qmi.type = '" + FormDefinition.FOMRTYPE_PERIODICMONITORING + "'" + " and " + whereSql + tousseNameSql + tousseSql; + addMonitoringItemBeanRountineMonitoring(list,sql,showSterilizationInfo,isQualifiedAmount,isQualifiedMaterialAmount); + } + if(StringUtils.isNotBlank(extraQueryForResponsibilityPart) && "质量监测".equals(monitoringType)){ + String sql2 = " select " + dataSql + " dateStr,qmi.id qmiId," + + "qmdf.recordRecycleInformation,qmdf.recordRinseInformation,qmdf.recordSterilizatioInformation " + + extraQuery + extraQueryForResponsibilityPart + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id " + + extraJoin + extraJoinForResponsibilityPart + + " where 1=1 and " + + whereSql; + addMonitoringItemBeanOption(list,sql2,showSterilizationInfo,washAmountMap,amoutSumJson,materialAndTousse); + } + // 条数 + Integer qmCount = 0; + // 材料或者包数量和 + Integer qmTousseAndMaterialAmount = 0; + if(materialAndTousse){ + sql = " select sum(t.bAmount) total from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join ( select qmi.id qid,sum(case when qmd.material is null or qmd.material='' then 0 else qmd.amount end) bAmount from " + + "QualityMonitoringInstance qmi join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + + joinTousseSql + + " where 1=1 " + + tousseNameSql + + tousseSql + + " group by qmi.id ) t on t.qid=qmi.id " + + " where " + whereSql; + Set readIds = new HashSet(); + for (MonitoringItemBean bean : list) { + Long id = bean.getQualityMonitoringGoodsId(); + if(!readIds.contains(id) && bean.getAmount() != null){ + readIds.add(id); + qmCount += bean.getAmount(); + } + } + }else{ + sql = " select count(0) count,sum(t.aAmount) total from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join ( select qmi.id qid,sum(qmd.amount) aAmount " + + "from QualityMonitoringInstance qmi join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + + joinTousseSql + + "where 1=1 " + + tousseNameSql + + tousseSql + + " group by qmi.id ) t on t.qid=qmi.id " + + " where " + whereSql; + } + ResultSet rs = objectDao.executeSql(sql); + try { + while (rs.next()) { + if(!materialAndTousse){ + qmCount = rs.getInt("count"); + } + qmTousseAndMaterialAmount = rs.getInt("total"); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + sql = "select distinct fdi.name from FormDefinition fd join FormDefinitionItem fdi on fdi.formDefinition_id=fd.id where fdi.type='数字'"; + ResultSet rs1 = null; + Set numNames = new HashSet(); + try { + rs1 = objectDao.executeSql(sql); + while (rs1.next()) { + numNames.add(rs1.getString("name")); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs1); + } + // 质量检测加数量汇总 + if ("质量监测".equals(monitoringType)) { + Integer totalAmount = 0; + Integer totalItem = 0; + totalAmount = qmTousseAndMaterialAmount; + totalItem = qmCount; + if (totalAmount > 0 || list.size() > 0) { + List> listMap = getBeanMap(list,numNames); + if( listMap != null && listMap.size() > 0){ + Map beanStatisticsMap = listMap.get(0); + if(beanStatisticsMap != null){ + for (Map.Entry entry : beanStatisticsMap.entrySet()){ + String key = entry.getKey(); + String[] arr = key.split("_"); + if(arr.length >=3){ + int _orderNumber = Integer.valueOf(arr[0]); + String _option = arr[1]; + String _questionName = arr[2]; + int anwserInt = entry.getValue(); + if(anwserInt == 0){ + continue; + } + MonitoringItemBean bean1 = new MonitoringItemBean(); + bean1.setGoodsName("合计"); + bean1.setDateTime("合计"); + bean1.setOrderNumber(_orderNumber); + bean1.setOption(_option); + bean1.setQuestionName(_questionName); + bean1.setAmount(totalItem); + bean1.setAnswer(anwserInt+""); + bean1.setPositionMsg(""); + list.add(bean1); + } + } + } + } + Integer tousseAmoutSum = amoutSumJson.optInt("tousseAmoutSum"); + Integer forgonAmoutSum = amoutSumJson.optInt("forgonAmoutSum"); + if(tousseAmoutSum != null && tousseAmoutSum > 0){ + MonitoringItemBean bean1 = new MonitoringItemBean(); + bean1.setGoodsName("合计"); + bean1.setDateTime("合计"); + bean1.setOrderNumber(15); + bean1.setOption("当天清洗件数"); + bean1.setQuestionName("清洗情况"); + bean1.setAmount(totalItem); + bean1.setPositionMsg(""); + bean1.setAnswer(tousseAmoutSum + ""); + list.add(bean1); + } + if(forgonAmoutSum != null && forgonAmoutSum > 0){ + MonitoringItemBean bean2 = new MonitoringItemBean(); + bean2.setGoodsName("合计"); + bean2.setDateTime("合计"); + bean2.setOrderNumber(15); + bean2.setOption("当天清洗外来器械件数"); + bean2.setQuestionName("清洗情况"); + bean2.setAmount(totalItem); + bean2.setPositionMsg(""); + bean2.setAnswer(forgonAmoutSum + ""); + list.add(bean2); + } + } + }else if("定期监测".equals(monitoringType)){ + Integer totalAmount = 0; + Integer orderNumber = 1; + String option = ""; + String questionName = ""; + totalAmount = qmTousseAndMaterialAmount; + if (list != null && list.size() > 0) { + orderNumber = list.get(0).getOrderNumber(); + option = list.get(0).getOption(); + questionName = list.get(0).getQuestionName(); + } + // 对每个bean 做统计 + List> listMap = getBeanMapByResult(list,numNames); + if( listMap != null && listMap.size() > 0){ + Map beanStatisticsMap = listMap.get(0); + if(beanStatisticsMap != null){ + for (Map.Entry entry : beanStatisticsMap.entrySet()){ + String key = entry.getKey(); + String[] arr = key.split("_"); + if(arr.length >=3){ + int _orderNumber = Integer.valueOf(arr[0]); + String _option = arr[1]; + String _questionName = arr[2]; + int anwserInt = entry.getValue(); + if(anwserInt == 0){ + continue; + } + MonitoringItemBean bean1 = new MonitoringItemBean(); + bean1.setGoodsName("合计"); + bean1.setDateTime("合计"); + bean1.setOrderNumber(_orderNumber); + bean1.setOption(_option); + bean1.setQuestionName(_questionName); + bean1.setAmount(totalAmount); + bean1.setAnswer(anwserInt+""); + bean1.setPositionMsg(""); + list.add(bean1); + } + } + } + if(totalAmount != null && totalAmount > 0){ + addQualifiedAmountInfo(isQualifiedAmount,isQualifiedMaterialAmount, list, orderNumber, option, questionName); + } + } + } + } + return list; + } + /** + * 创建质量监测报表查询条件的sql + * @param startDate + * @param endDate + * @param querySupplyRoom + * @param formName + * @param monitoringType + * @return + */ + private String buildWhereSQL(String startDate, String endDate, + String querySupplyRoom, String formName, String monitoringType) { + + String sql = " qmi.dateTime between " + + dateQueryAdapter.dateAdapter(startDate+" 00:00:00") + + " and " + "" + + dateQueryAdapter.dateAdapter(endDate+" 23:59:59") + + " and qmi.type = '" + monitoringType + + "' and qmi.name = '" + formName + "'" + + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom); + String departCoding = AcegiHelper.getLoginUser().getOrgUnitCodingFromSupplyRoomConfig(); + if(!supplyRoomConfigManager.isFirstOrSecondSupplyRoomOrgUnit(departCoding)){ + sql += SqlUtils.get_InSql_Extra("fi.orgUnitCoding", departCoding); + } + return sql; + } + /** + * 创建质量监测报表查询条件的sql + * @param materialName 材料名 + * @param tousseName 包名 + * @return + */ + private String buildTousseNameMaterialNameWhereSql(String materialName, String tousseName){ + String materialSql = ""; + String tousseSql = ""; + if( StringUtils.isNotBlank(materialName)){ + // 如果字符串中有"[",SqlServer需要转译 + if(materialName.contains("[") && dbConnection.isSqlServer()){ + int index = materialName.indexOf("["); + materialName = materialName.substring(0, index) + "\\" + materialName.substring(index, materialName.length()); + materialSql = " qmd.material like '%" + materialName + "%'" + " escape '\\' "; + }else{ + materialSql = " qmd.material like '%" + materialName + "%'"; + } + } + if( StringUtils.isNotBlank(tousseName)){ + // 如果字符串中有"[",SqlServer需要转译 + if(tousseName.contains("[") && dbConnection.isSqlServer()){ + int index = tousseName.indexOf("["); + tousseName = tousseName.substring(0, index) + "\\" + tousseName.substring(index, materialName.length()); + tousseSql = " qmd.tousseName like '%" + tousseName + "%'" + " escape '\\' "; + }else{ + tousseSql = " qmd.tousseName like '%" + tousseName + "%'"; + } + } + // 如果都有材料和器械包的名字，用or条件 + String tsql = ""; + if(StringUtils.isNotBlank(materialSql) && StringUtils.isNotBlank(tousseSql)){ + tsql = " and ( " + materialSql + " or " + tousseSql + " ) "; + }else if(StringUtils.isNotBlank(materialSql)){ + tsql = " and " + materialSql; + }else if(StringUtils.isNotBlank(tousseSql)){ + tsql = " and " + tousseSql; + } + return tsql; + } + /** + * 添加质量监测记录的bean 单选和多选的结果 + * @param list + * @param sql + */ + private void addMonitoringItemBeanOptionRadioAndCHeck(List list,String sql, boolean isCustomADJAndIsFormNameIsADJ, boolean washScope){ + if(StringUtils.isBlank(sql)){ + return ; + } + ResultSet rs = objectDao.executeSql(sql); + try { + Map qmiIdToLastTousseNameMap = new HashMap(); + Map qmiIdToLastTousseAmountMap = new HashMap(); + MonitoringItemBean lastMonitoringItemBean = null; + Set readedIds = new HashSet(); + while (rs.next()) { + String dataStr = null; + String tousseName = rs.getString("toussName"); + String materialName = rs.getString("material"); + Long qmiId = rs.getLong("qmiId"); + if(!readedIds.contains(qmiId)){ + readedIds.add(qmiId); + if(lastMonitoringItemBean != null){ + newMonitoringItem(list, lastMonitoringItemBean.getRegisterMaterialAmount(), + lastMonitoringItemBean.getAmount(), + lastMonitoringItemBean.getTousseName(), + lastMonitoringItemBean.getMaterialName(), + lastMonitoringItemBean.getQualityMonitoringInstanceId(), + lastMonitoringItemBean.getPositionMsg(), + lastMonitoringItemBean.getDateTime(), + lastMonitoringItemBean.getOrderNumber(), + lastMonitoringItemBean.getQuestionName(), + lastMonitoringItemBean.getOption(), + lastMonitoringItemBean.getAnswer(), + lastMonitoringItemBean.getFrequency(), + lastMonitoringItemBean.getSterilizerName(),isCustomADJAndIsFormNameIsADJ, + lastMonitoringItemBean.getCreateUserName(), + lastMonitoringItemBean.getQualityMonitoringGoodsId(), + lastMonitoringItemBean.getMaterialAmount()); + } + lastMonitoringItemBean = null; + } + int amount = rs.getInt("amount"); + String positionMsg = rs.getString("positionMsg"); + String questionName = rs.getString("fdiName"); + int orderNum = rs.getInt("orderNum"); + String optionStr = rs.getString("value"); + Long fiovId = rs.getLong("id"); + String createUserName = rs.getString("createUserName"); + String answer = (fiovId > 0) ? "√" : ""; + Integer frequency = null; + String sterilizerName = null; + if(isCustomADJAndIsFormNameIsADJ){ + frequency = ConvertNumber.getNumberIntValue(rs.getObject("frequency"), null); + sterilizerName = rs.getString("sterilizerName"); + if(SqlUtils.isExistColumn(rs, "sterilizerDate")){//监测范围为灭菌炉记录，日期要使用灭菌日期 + dataStr = rs.getString("sterilizerDate"); + } + } + if(dataStr == null){ + dataStr = rs.getString("dateStr"); + } + Long qmdid = rs.getLong("qmdid"); + Integer materialAmount = 0; + if(SqlUtils.isExistColumn(rs, "materialAmount")){ + materialAmount = rs.getInt("materialAmount"); + } + String tousseAmountKey = null; + if(washScope){ + int qmdOrderNumber = rs.getInt("qmdOrderNumber"); + if(StringUtils.isNotBlank(tousseName)){ + qmiIdToLastTousseNameMap.put(qmiId + "_" + qmdOrderNumber, tousseName); + qmiIdToLastTousseAmountMap.put(qmiId + "_" + qmdOrderNumber, amount); + } + if(StringUtils.isBlank(materialName)){ + if(lastMonitoringItemBean != null){ + newMonitoringItem(list, lastMonitoringItemBean.getRegisterMaterialAmount(), lastMonitoringItemBean.getAmount(), + lastMonitoringItemBean.getTousseName(), + lastMonitoringItemBean.getMaterialName(), + lastMonitoringItemBean.getQualityMonitoringInstanceId(), + lastMonitoringItemBean.getPositionMsg(), + lastMonitoringItemBean.getDateTime(), + lastMonitoringItemBean.getOrderNumber(), + lastMonitoringItemBean.getQuestionName(), + lastMonitoringItemBean.getOption(), + lastMonitoringItemBean.getAnswer(), + lastMonitoringItemBean.getFrequency(), + lastMonitoringItemBean.getSterilizerName(),isCustomADJAndIsFormNameIsADJ, + lastMonitoringItemBean.getCreateUserName(), + lastMonitoringItemBean.getQualityMonitoringGoodsId(), + lastMonitoringItemBean.getMaterialAmount()); + lastMonitoringItemBean = null; + } + lastMonitoringItemBean = new MonitoringItemBean(); + lastMonitoringItemBean.setAnswer(answer); + lastMonitoringItemBean.setDateTime(dataStr); + lastMonitoringItemBean.setSterilizerName(sterilizerName); + lastMonitoringItemBean.setCreateUserName(createUserName); + lastMonitoringItemBean.setFrequency(frequency); + lastMonitoringItemBean.setMaterialName(materialName); + if(StringUtils.isNotBlank(tousseName) && StringUtils.isNotBlank(materialName)){ + lastMonitoringItemBean.setRegisterMaterialAmount(amount); + }else if(StringUtils.isNotBlank(tousseName)){ + lastMonitoringItemBean.setAmount(amount); + }else if(StringUtils.isNotBlank(materialName)){ + lastMonitoringItemBean.setRegisterMaterialAmount(amount); + } + lastMonitoringItemBean.setTousseName(tousseName); + lastMonitoringItemBean.setQualityMonitoringInstanceId(qmiId); + lastMonitoringItemBean.setPositionMsg(positionMsg); + lastMonitoringItemBean.setOrderNumber(orderNum); + lastMonitoringItemBean.setQuestionName(questionName); + lastMonitoringItemBean.setOption(optionStr); + lastMonitoringItemBean.setQualityMonitoringGoodsId(qmdid); + lastMonitoringItemBean.setMaterialAmount(materialAmount); + continue; + } + lastMonitoringItemBean = null; + for (int i = 1; i < qmdOrderNumber; i++) { + if(qmiIdToLastTousseNameMap.containsKey(qmiId + "_" + (qmdOrderNumber - i))){ + tousseName = qmiIdToLastTousseNameMap.get(qmiId + "_" + (qmdOrderNumber - i)); + tousseAmountKey = qmiId + "_" + (qmdOrderNumber - i); + break; + } + } + } + Integer registerMaterialAmount = 0; + if(StringUtils.isNotBlank(tousseName) && StringUtils.isNotBlank(materialName)){ + registerMaterialAmount = amount; + if(tousseAmountKey != null){ + amount = qmiIdToLastTousseAmountMap.get(tousseAmountKey); + } + }else if(StringUtils.isNotBlank(materialName)){ + registerMaterialAmount = amount; + amount = 0; + } + newMonitoringItem(list, registerMaterialAmount,amount,tousseName,materialName,qmiId,positionMsg, + dataStr, orderNum, questionName,optionStr, answer,frequency,sterilizerName,isCustomADJAndIsFormNameIsADJ,createUserName, qmdid, materialAmount); + } + if(lastMonitoringItemBean != null){ + newMonitoringItem(list, lastMonitoringItemBean.getRegisterMaterialAmount(),lastMonitoringItemBean.getAmount(), + lastMonitoringItemBean.getTousseName(), + lastMonitoringItemBean.getMaterialName(), + lastMonitoringItemBean.getQualityMonitoringInstanceId(), + lastMonitoringItemBean.getPositionMsg(), + lastMonitoringItemBean.getDateTime(), + lastMonitoringItemBean.getOrderNumber(), + lastMonitoringItemBean.getQuestionName(), + lastMonitoringItemBean.getOption(), + lastMonitoringItemBean.getAnswer(), + lastMonitoringItemBean.getFrequency(), + lastMonitoringItemBean.getSterilizerName(),isCustomADJAndIsFormNameIsADJ, + lastMonitoringItemBean.getCreateUserName(), + lastMonitoringItemBean.getQualityMonitoringGoodsId(), + lastMonitoringItemBean.getMaterialAmount()); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + /** + * 添加质量监测记录的bean 没有包和材料时，单选和多选的结果 + * @param list + * @param sql + */ + private void addMonitoringItemBeanOptionRadioAndCHeckWithoutMaterial(List list,String sql, boolean addMonitoringItemBeanOptionSingleResult){ + if(StringUtils.isBlank(sql)){ + return ; + } + ResultSet rs = objectDao.executeSql(sql); + try { + while (rs.next()) { + String dataStr = null; + Long qmiId = rs.getLong("qmiId"); + String positionMsg = rs.getString("positionMsg"); + String questionName = rs.getString("fdiName"); + int orderNum = rs.getInt("orderNum"); + String optionStr = rs.getString("value"); + Long fiovId = rs.getLong("id"); + String createUserName = rs.getString("createUserName"); + String answer = (fiovId > 0) ? "√" : ""; + Integer frequency = null; + String sterilizerName = null; + if(addMonitoringItemBeanOptionSingleResult){ + frequency = ConvertNumber.getNumberIntValue(rs.getObject("frequency"), null); + sterilizerName = rs.getString("sterilizerName"); + if(SqlUtils.isExistColumn(rs, "sterilizerDate")){//监测范围为灭菌炉记录，日期要使用灭菌日期 + dataStr = rs.getString("sterilizerDate"); + } + } + if(dataStr == null){ + dataStr = rs.getString("dateStr"); + } + newMonitoringItem(list, 0, 0,"","",qmiId,positionMsg, + dataStr, orderNum, questionName,optionStr, answer, frequency, sterilizerName, addMonitoringItemBeanOptionSingleResult,createUserName, null, 0); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + /** + * 添加质量监测记录的bean 单一的结果 + * @param list + * @param sql + */ + private void addMonitoringItemBeanOptionSingleResult(List list,String sql, boolean addMonitoringItemBeanOptionSingleResult, boolean washScope){ + if(StringUtils.isBlank(sql)){ + return ; + } + ResultSet rs = null; + try { + rs = objectDao.executeSql(sql); + Map qmiIdToLastTousseNameMap = new HashMap(); + Map qmiIdToLastTousseAmountMap = new HashMap(); + MonitoringItemBean lastMonitoringItemBean = null; + Set readedIds = new HashSet(); + while (rs.next()) { + String dataStr = null; + String tousseName = rs.getString(2); + String materialName = rs.getString(3); + Long qmiId = rs.getLong(5); + if(!readedIds.contains(qmiId)){ + readedIds.add(qmiId); + if(lastMonitoringItemBean != null){ + newMonitoringItem(list, lastMonitoringItemBean.getRegisterMaterialAmount(),lastMonitoringItemBean.getAmount(), + lastMonitoringItemBean.getTousseName(), + lastMonitoringItemBean.getMaterialName(), + lastMonitoringItemBean.getQualityMonitoringInstanceId(), + lastMonitoringItemBean.getPositionMsg(), + lastMonitoringItemBean.getDateTime(), + lastMonitoringItemBean.getOrderNumber(), + lastMonitoringItemBean.getQuestionName(), + "-", + lastMonitoringItemBean.getAnswer(), + lastMonitoringItemBean.getFrequency(), + lastMonitoringItemBean.getSterilizerName(),addMonitoringItemBeanOptionSingleResult, + lastMonitoringItemBean.getCreateUserName(), + lastMonitoringItemBean.getQualityMonitoringGoodsId(), + lastMonitoringItemBean.getMaterialAmount()); + } + lastMonitoringItemBean = null; + } + int amount = rs.getInt(4); + + String positionMsg = rs.getString(6); + String questionName = rs.getString(7); + String createUserName = rs.getString("createUserName"); + int orderNum = rs.getInt(8); + String answer = rs.getString(9); + Integer frequency = null; + String sterilizerName = null; + if(addMonitoringItemBeanOptionSingleResult){ + frequency = ConvertNumber.getNumberIntValue(rs.getObject("frequency"), null); + sterilizerName = rs.getString("sterilizerName"); + if(SqlUtils.isExistColumn(rs, "sterilizerDate")){//监测范围为灭菌炉记录，日期要使用灭菌日期 + dataStr = rs.getString("sterilizerDate"); + } + } + if(dataStr == null){ + dataStr = rs.getString("dateStr"); + } + Long qmdid = rs.getLong("qmdid"); + int materialAmount = 0; + if(SqlUtils.isExistColumn(rs, "materialAmount")){ + materialAmount = rs.getInt("materialAmount"); + } + String tousseAmountKey = null; + if(washScope){ + int qmdOrderNumber = rs.getInt("qmdOrderNumber"); + if(StringUtils.isNotBlank(tousseName)){ + qmiIdToLastTousseNameMap.put(qmiId + "_" + qmdOrderNumber, tousseName); + qmiIdToLastTousseAmountMap.put(qmiId + "_" + qmdOrderNumber, amount); + } + if(StringUtils.isBlank(materialName)){ + if(lastMonitoringItemBean != null){ + newMonitoringItem(list, lastMonitoringItemBean.getRegisterMaterialAmount() + ,lastMonitoringItemBean.getAmount(), + lastMonitoringItemBean.getTousseName(), + lastMonitoringItemBean.getMaterialName(), + lastMonitoringItemBean.getQualityMonitoringInstanceId(), + lastMonitoringItemBean.getPositionMsg(), + lastMonitoringItemBean.getDateTime(), + lastMonitoringItemBean.getOrderNumber(), + lastMonitoringItemBean.getQuestionName(), + lastMonitoringItemBean.getOption(), + lastMonitoringItemBean.getAnswer(), + lastMonitoringItemBean.getFrequency(), + lastMonitoringItemBean.getSterilizerName(),addMonitoringItemBeanOptionSingleResult, + lastMonitoringItemBean.getCreateUserName(),lastMonitoringItemBean.getQualityMonitoringGoodsId(), + lastMonitoringItemBean.getMaterialAmount()); + lastMonitoringItemBean = null; + } + lastMonitoringItemBean = new MonitoringItemBean(); + lastMonitoringItemBean.setAnswer(answer); + lastMonitoringItemBean.setDateTime(dataStr); + lastMonitoringItemBean.setSterilizerName(sterilizerName); + lastMonitoringItemBean.setCreateUserName(createUserName); + lastMonitoringItemBean.setFrequency(frequency); + lastMonitoringItemBean.setMaterialName(materialName); + lastMonitoringItemBean.setTousseName(tousseName); + if(StringUtils.isNotBlank(tousseName) && StringUtils.isNotBlank(materialName)){ + lastMonitoringItemBean.setRegisterMaterialAmount(amount); + }else if(StringUtils.isNotBlank(tousseName)){ + lastMonitoringItemBean.setAmount(amount); + }else if(StringUtils.isNotBlank(materialName)){ + lastMonitoringItemBean.setRegisterMaterialAmount(amount); + } + lastMonitoringItemBean.setQualityMonitoringInstanceId(qmiId); + lastMonitoringItemBean.setPositionMsg(positionMsg); + lastMonitoringItemBean.setOrderNumber(orderNum); + lastMonitoringItemBean.setQuestionName(questionName); + lastMonitoringItemBean.setOption("-"); + lastMonitoringItemBean.setQualityMonitoringGoodsId(qmdid); + lastMonitoringItemBean.setMaterialAmount(materialAmount); + continue; + } + for (int i = 1; i < qmdOrderNumber; i++) { + if(qmiIdToLastTousseNameMap.containsKey(qmiId + "_" + (qmdOrderNumber - i))){ + tousseName = qmiIdToLastTousseNameMap.get(qmiId + "_" + (qmdOrderNumber - i)); + tousseAmountKey = qmiId + "_" + (qmdOrderNumber - i); + break; + } + } + } + lastMonitoringItemBean = null; + Integer registerMaterialAmount = 0; + if(StringUtils.isNotBlank(tousseName) && StringUtils.isNotBlank(materialName)){ + registerMaterialAmount = amount; + if(tousseAmountKey != null){ + amount = qmiIdToLastTousseAmountMap.get(tousseAmountKey); + } + }else if(StringUtils.isNotBlank(materialName)){ + registerMaterialAmount = amount; + amount = 0; + } + newMonitoringItem(list, registerMaterialAmount,amount,tousseName,materialName,qmiId,positionMsg, + dataStr, orderNum,questionName, "-", answer, frequency, sterilizerName, addMonitoringItemBeanOptionSingleResult,createUserName, qmdid, materialAmount); + } + if(lastMonitoringItemBean != null){ + newMonitoringItem(list, lastMonitoringItemBean.getRegisterMaterialAmount(),lastMonitoringItemBean.getAmount(), + lastMonitoringItemBean.getTousseName(), + lastMonitoringItemBean.getMaterialName(), + lastMonitoringItemBean.getQualityMonitoringInstanceId(), + lastMonitoringItemBean.getPositionMsg(), + lastMonitoringItemBean.getDateTime(), + lastMonitoringItemBean.getOrderNumber(), + lastMonitoringItemBean.getQuestionName(), + "-", + lastMonitoringItemBean.getAnswer(), + lastMonitoringItemBean.getFrequency(), + lastMonitoringItemBean.getSterilizerName(),addMonitoringItemBeanOptionSingleResult, + lastMonitoringItemBean.getCreateUserName(), + lastMonitoringItemBean.getQualityMonitoringGoodsId(), + lastMonitoringItemBean.getMaterialAmount()); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + private void addMonitoringItemBeanOptionSingleResultWithoutMaterial(List list,String sql, boolean addMonitoringItemBeanOptionSingleResultWithoutMaterial, String monitoringType){ + if(StringUtils.isBlank(sql)){ + return ; + } + ResultSet rs = objectDao.executeSql(sql); + try { + while (rs.next()) { + String dataStr = null; + Long qmiId = rs.getLong(2); + String positionMsg = rs.getString(3); + String questionName = rs.getString(4); + int orderNum = rs.getInt(5); + String answer = rs.getString(6); + String createUserName = rs.getString("createUserName"); + Integer frequency = null; + String sterilizerName = null; + if(addMonitoringItemBeanOptionSingleResultWithoutMaterial){ + frequency = ConvertNumber.getNumberIntValue(rs.getObject("frequency"), null); + sterilizerName = rs.getString("sterilizerName"); + if(SqlUtils.isExistColumn(rs, "sterilizerDate")){//监测范围为灭菌炉记录，日期要使用灭菌日期 + dataStr = rs.getString("sterilizerDate"); + } + } + if(dataStr == null){ + dataStr = rs.getString("dateStr"); + } + newMonitoringItem(list, 0 ,0,"","",qmiId,positionMsg, + dataStr, orderNum, "-",questionName, answer ,frequency ,sterilizerName, addMonitoringItemBeanOptionSingleResultWithoutMaterial,createUserName, null, 0); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + /** + * 添加质量监测记录的bean 没有监测细则的记录 + * @param list + * @param sql + */ + private void addMonitoringItemBeanQualityMonitoring(List list,String sql, boolean addMonitoringItemBeanQualityMonitoring,boolean washScope){ + if(StringUtils.isBlank(sql)){ + return ; + } + ResultSet rs = null; + try { + rs = objectDao.executeSql(sql); + Map qmiIdToLastTousseNameMap = new HashMap(); + Map qmiIdToLastTousseAmountMap = new HashMap(); + while (rs.next()) { + String dataStr = rs.getString(1); + String tousseName = rs.getString(2); + String materialName = rs.getString(3); + Long qmiId = rs.getLong(5); + int amount = rs.getInt(4); + Integer registerMaterialAmount = 0; + if(washScope){ + if(StringUtils.isNotBlank(tousseName)){ + qmiIdToLastTousseNameMap.put(qmiId, tousseName); + qmiIdToLastTousseAmountMap.put(qmiId, amount); + } + if(StringUtils.isBlank(materialName)){ + continue; + } + if(qmiIdToLastTousseNameMap.containsKey(qmiId)){ + tousseName = qmiIdToLastTousseNameMap.get(qmiId); + registerMaterialAmount = amount; + amount = qmiIdToLastTousseAmountMap.get(qmiId); + } + } + String positionMsg = rs.getString(6); + String createUserName = rs.getString("createUserName"); + Integer frequency = null; + String sterilizerName = null; + if(addMonitoringItemBeanQualityMonitoring){ + frequency = ConvertNumber.getNumberIntValue(rs.getObject("frequency"), null); + sterilizerName = rs.getString("sterilizerName"); + } + int materialAmount = 0; + if(SqlUtils.isExistColumn(rs, "materialAmount")){ + materialAmount = rs.getInt("materialAmount"); + } + newMonitoringItem(list,registerMaterialAmount, amount,tousseName,materialName,qmiId,positionMsg, + dataStr, 1, "-","-", "", frequency, sterilizerName, addMonitoringItemBeanQualityMonitoring,createUserName, null, materialAmount); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + /** + * 质量监测添加动态列 + * @param list + * @param sql + * @param isCustomADJAndIsFormNameIsADJ + * @param washAmountMap 清洗数量map + * @param amoutSumJson 当天清洗件数和当天外来器清洗件数json 格式{tousseAmoutSum":2,"forgonAmoutSum":3}; + */ + private void addMonitoringItemBeanOption(List list,String sql, boolean isCustomADJAndIsFormNameIsADJ, Map> washAmountMap,JSONObject amoutSumJson, boolean materialAndTousse){ + if(StringUtils.isBlank(sql)){ + return ; + } + ResultSet rs = null; + try { + Set readedSet = new HashSet(); + Integer tousseAmoutSum = 0; + Integer forgonAmoutSum = 0; + rs = objectDao.executeSql(sql); + List addList = new ArrayList();//本次要新增的bean + while (rs.next()) { + Long qmiId = rs.getLong("qmiId"); + for (MonitoringItemBean monitoringItemBean : list) { + if(monitoringItemBean == null){ + continue; + } + if(MathTools.equals(monitoringItemBean.getQualityMonitoringInstanceId(), qmiId)){ + String dataStr = rs.getString("dateStr"); + String recordRecycleInformation = rs.getString("recordRecycleInformation"); + String recordRinseInformation = rs.getString("recordRinseInformation"); + String recordSterilizatioInformation = rs.getString("recordSterilizatioInformation"); + if(SqlUtils.isExistColumn(rs, "sterilizationStartDate") && Constants.STR_YES.equals(recordSterilizatioInformation)){ + String name = rs.getString("name"); + if(StringUtils.isNotBlank(name)){ + Integer steFrequency = ConvertNumber.getNumberIntValue(rs.getObject("frequency"), null); + name = name + ",第" + steFrequency + "炉"; + } + if(StringUtils.isBlank(name)){ + name = ""; + } + copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 10, "灭菌情况", "灭菌批号",name, isCustomADJAndIsFormNameIsADJ); + String sterilizationUser = rs.getString("sterilizationUser"); + copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 10,"灭菌情况","灭菌员", sterilizationUser, isCustomADJAndIsFormNameIsADJ); + String startDate = rs.getString("sterilizationStartDate"); + copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 10, "灭菌情况","灭菌时间", startDate, isCustomADJAndIsFormNameIsADJ); + Integer steAmount = rs.getInt("steAmount"); + copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 10, "灭菌情况","当次炉次灭菌总包数", steAmount + "", isCustomADJAndIsFormNameIsADJ); + }else if(SqlUtils.isExistColumn(rs, "washStartDate") && Constants.STR_YES.equals(recordRinseInformation)){ + String startDate = null; + String disinfectIdentification = null; + disinfectIdentification = rs.getString("disinfectIdentification"); + copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "清洗情况","清洗机", disinfectIdentification, isCustomADJAndIsFormNameIsADJ); + startDate = rs.getString("washStartDate"); + copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "清洗情况","清洗开始时间", startDate, isCustomADJAndIsFormNameIsADJ); + Integer forgonAmount = 0; + Integer totalAmout = 0; + if(MapUtils.isNotEmpty(washAmountMap)){ + Map washAmountMapItem = washAmountMap.get(dataStr); + if(MapUtils.isNotEmpty(washAmountMapItem)){ + if(washAmountMapItem.containsKey("外来器械")){ + forgonAmount = washAmountMapItem.get("外来器械"); + } + totalAmout = washAmountMapItem.get("清洗总数"); + totalAmout = totalAmout - forgonAmount; + } + } + String washPersonInCharge = null; + if(SqlUtils.isExistColumn(rs, "washPersonInCharge")){ + washPersonInCharge = rs.getString("washPersonInCharge"); + copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "清洗情况","责任人", washPersonInCharge, isCustomADJAndIsFormNameIsADJ); + } + if(!readedSet.contains(dataStr)){ + readedSet.add(dataStr); + tousseAmoutSum += totalAmout; + forgonAmoutSum += forgonAmount; + copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "清洗情况","当天清洗件数", totalAmout + "", isCustomADJAndIsFormNameIsADJ); + copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "清洗情况","当天清洗外来器械件数", forgonAmount + "", isCustomADJAndIsFormNameIsADJ); + } + }else if(SqlUtils.isExistColumn(rs, "recyclingTime") && Constants.STR_YES.equals(recordRecycleInformation)){ + String depart = rs.getString("depart"); + copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "回收情况","回收科室", depart, isCustomADJAndIsFormNameIsADJ); + String operationRoom = rs.getString("operationRoom"); + copyPartPropertiesMonitoringItem(addList, monitoringItemBean,15, "回收情况","手术间", operationRoom, isCustomADJAndIsFormNameIsADJ); + String circuitNurse = rs.getString("circuitNurse"); + copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "回收情况","巡回护士", circuitNurse, isCustomADJAndIsFormNameIsADJ); + String recyclingUser = rs.getString("recyclingUser"); + copyPartPropertiesMonitoringItem(addList, monitoringItemBean,15, "回收情况","回收员", recyclingUser, isCustomADJAndIsFormNameIsADJ); + String recyclingTime = rs.getString("recyclingTime"); + copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "回收情况","回收时间", recyclingTime, isCustomADJAndIsFormNameIsADJ); + } + } + } + } + if(addList.size() > 0){ + list.addAll(addList); + } + amoutSumJson.put("tousseAmoutSum", tousseAmoutSum); + amoutSumJson.put("forgonAmoutSum", forgonAmoutSum); + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + /** + * 添加质量监测记录的bean 定期监测的记录 + * @param list + * @param sql + */ + private void addMonitoringItemBeanRountineMonitoring(List list,String sql,boolean addMonitoringItemBeanRountineMonitoring,Map isQualifiedAmount, Map isQualifiedMaterialAmount){ + if(StringUtils.isBlank(sql)){ + return ; + } + ResultSet rs = objectDao.executeSql(sql); + try { + Integer qualifiedAmount = 0; + Integer unQualifiedAmount = 0; + Integer qualifiedMaterialAmount = 0; + Integer unQualifiedMaterialAmount = 0; + while (rs.next()) { + String dataStr = rs.getString(1); + String tousseName = rs.getString(2); + String materialName = rs.getString(3); + int amount = rs.getInt(4); + Long qmiId = rs.getLong(5); + String positionMsg = rs.getString(6); + String result = rs.getString(7); + String createUserName = rs.getString("createUserName"); + Integer frequency = null; + String sterilizerName = null; + if(addMonitoringItemBeanRountineMonitoring){ + frequency = ConvertNumber.getNumberIntValue(rs.getObject("frequency"), null); + sterilizerName = rs.getString("sterilizerName"); + } + if(StringUtils.isNotBlank(tousseName) && StringUtils.isNotBlank(materialName)){ + if("合格".equals(result)){ + qualifiedMaterialAmount += amount; + }else if("不合格".equals(result)){ + unQualifiedMaterialAmount += amount; + } + }else if(StringUtils.isNotBlank(tousseName)){ + if("合格".equals(result)){ + qualifiedAmount += amount; + }else if("不合格".equals(result)){ + unQualifiedAmount += amount; + } + }else{ + if("合格".equals(result)){ + qualifiedMaterialAmount += amount; + }else if("不合格".equals(result)){ + unQualifiedMaterialAmount += amount; + } + } + newMonitoringItem(list, null,amount,tousseName,materialName,qmiId,positionMsg, + dataStr, 100, "-","监测结果", result, frequency, sterilizerName, addMonitoringItemBeanRountineMonitoring,createUserName, null, 0); + } + isQualifiedAmount.put("合格", qualifiedAmount); + isQualifiedAmount.put("不合格", unQualifiedAmount); + isQualifiedMaterialAmount.put("合格", qualifiedMaterialAmount); + isQualifiedMaterialAmount.put("不合格", unQualifiedMaterialAmount); + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + private List> getBeanMap(List beanList,Set numNames){ + if(beanList == null) + return null; + List> listMap = new ArrayList>(); + Map map = new HashMap(); + for(MonitoringItemBean mt : beanList){ + String questionName = mt.getQuestionName(); + if("清洗情况".equals(questionName) || "灭菌情况".equals(questionName) || "回收情况".equals(questionName)){ + continue; + } + String option = mt.getOption(); + String answer = mt.getAnswer(); + String key = "0_" + option + "_" + questionName; + // 统计项有值才添加 + if(StringUtils.isNotBlank(answer)){ + if(numNames.contains(questionName) || "-".equals(questionName) && numNames.contains(option)){ + if(StringUtils.isNotBlank(answer) && StringUtils.isNumeric(answer)){ + Integer amount = Integer.valueOf(answer); + if( !map.containsKey(key) ){ + map.put(key, amount); + }else{ + int value = map.get(key) + amount; + map.put(key, value); + } + } + }else{ + if( !map.containsKey(key) ){ + map.put(key, 1); + }else{ + int value = map.get(key) + 1; + map.put(key, value); + } + } + } + } + listMap.add(map); + return listMap; + } + /** + * 获取结果统计的map，结果为"√"才统计 + * @param beanList + * @return + */ + private List> getBeanMapByResult(List beanList, Set numNames){ + if(beanList == null) + return null; + List> listMap = new ArrayList>(); + Map map = new HashMap(); + for(MonitoringItemBean mt : beanList){ + String option = mt.getOption(); + String questionName = mt.getQuestionName(); + String answer = mt.getAnswer(); + String key = "0_" + option + "_" + questionName; + // 统计项有值才添加 + if("√".equals(answer)){ + if( !map.containsKey(key) ){ + map.put(key, 1); + }else{ + int value = map.get(key) + 1; + map.put(key, value); + } + //指定的元素也添加(比如数字) + }else if(numNames.contains(questionName) || "-".equals(questionName) && numNames.contains(option)){ + if(StringUtils.isNotBlank(answer) && StringUtils.isNumeric(answer)){ + Integer amount = Integer.valueOf(answer); + if( !map.containsKey(key) ){ + map.put(key, amount); + }else{ + int value = map.get(key) + amount; + map.put(key, value); + } + } + } + } + listMap.add(map); + return listMap; + } + /** + * 添加合格与不合格统计信息 + * @param isQualifiedAmount 合格与不合格的数据 + * @param list List + * @param orderNumber 排序号 + * @param option 监测参数 + * @param questionName 监测细则名称 + */ + private void addQualifiedAmountInfo(Map isQualifiedAmount,Map isQualifiedMaterialAmount, List list, Integer orderNumber, + String option, String questionName){ + for(Entry entry : isQualifiedAmount.entrySet()){ + String key = entry.getKey(); + MonitoringItemBean mib = new MonitoringItemBean(); + mib.setGoodsName(""); + mib.setDateTime(key); + mib.setOrderNumber(orderNumber); + mib.setOption(option); + mib.setQuestionName(questionName); + mib.setAmount(entry.getValue()); + mib.setRegisterMaterialAmount(isQualifiedMaterialAmount.get(key)); + mib.setPositionMsg(""); + list.add(mib); + } + } + private void newMonitoringItem(List list, + Integer registerMaterialAmount, Integer amount,String tousseName,String materialName, + Long qmiId,String positoinMsg,String dateStr, + Integer orderNum,String questionName,String option,String answer,Integer frequency, String sterilizerName, boolean isCustomADJAndIsFormNameIsADJ, String createUserName, Long qmdId, Integer materialAmount) { + MonitoringItemBean bean = new MonitoringItemBean(); + bean.setAmount(amount); + bean.setRegisterMaterialAmount(registerMaterialAmount); + bean.setDateTime(dateStr); + bean.setMaterialName(materialName); + bean.setTousseName(tousseName); + String goodsName = tousseName; + if (StringUtils.isBlank(goodsName)) { + goodsName = materialName; + } + bean.setGoodsName(goodsName + "%&" + qmiId); + bean.setQualityMonitoringInstanceId(qmiId); + bean.setQualityMonitoringGoodsId(qmdId); + bean.setPositionMsg(positoinMsg); + bean.setOrderNumber(orderNum); + bean.setOption(option);// 表头第三列 + bean.setQuestionName(questionName);// 表头第二列 + bean.setAnswer(answer); + if(isCustomADJAndIsFormNameIsADJ){ + bean.setFrequency(frequency); + bean.setSterilizerName(sterilizerName); + } + bean.setCreateUserName(createUserName); + bean.setMaterialAmount(materialAmount); + list.add(bean); + } + /** + * 复制部分属性 添加新的bean到List + * @param list + * @param monitoringItemBean + * @param orderNum 型号 + * @param questionName 监测细则名称 + * @param option 监测参数 + * @param answer 检测结果 + * @param isCustomADJAndIsFormNameIsADJ + */ + private void copyPartPropertiesMonitoringItem(List list,MonitoringItemBean monitoringItemBean, + Integer orderNum,String questionName,String option,String answer, boolean isCustomADJAndIsFormNameIsADJ) { + MonitoringItemBean bean = new MonitoringItemBean(); + bean.setAmount(monitoringItemBean.getAmount()); + bean.setDateTime(monitoringItemBean.getDateTime()); + bean.setMaterialName(monitoringItemBean.getMaterialName()); + bean.setTousseName(monitoringItemBean.getTousseName()); + bean.setGoodsName(monitoringItemBean.getGoodsName()); + bean.setPositionMsg(monitoringItemBean.getPositionMsg()); + bean.setOrderNumber(orderNum); + bean.setQualityMonitoringInstanceId(monitoringItemBean.getQualityMonitoringInstanceId()); + bean.setOption(option);// 表头第三列 + bean.setQuestionName(questionName);// 表头第二列 + bean.setAnswer(answer); + bean.setQualityMonitoringGoodsId(monitoringItemBean.getQualityMonitoringGoodsId()); + if(isCustomADJAndIsFormNameIsADJ){ + bean.setFrequency(monitoringItemBean.getFrequency()); + bean.setSterilizerName(monitoringItemBean.getSterilizerName()); + } + bean.setCreateUserName(monitoringItemBean.getCreateUserName()); + list.add(bean); + } +} Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/WashWorkloadReprotHelper.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/WashWorkloadReprotHelper.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/WashWorkloadReprotHelper.java (revision 35802) @@ -0,0 +1,408 @@ +package com.forgon.disinfectsystem.jasperreports.util; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.text.DecimalFormat; +import java.text.ParseException; +import java.text.SimpleDateFormat; +import java.util.ArrayList; +import java.util.Calendar; +import java.util.Date; +import java.util.HashMap; +import java.util.HashSet; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.Map.Entry; + +import org.apache.commons.collections4.CollectionUtils; +import org.apache.commons.collections4.MapUtils; +import org.apache.commons.lang.StringUtils; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.stereotype.Component; + +import com.forgon.disinfectsystem.common.CssdUtils; +import com.forgon.disinfectsystem.entity.basedatamanager.reportoption.GoodsOption; +import com.forgon.disinfectsystem.entity.becleanitem.ClassifiedItem; +import com.forgon.disinfectsystem.jasperreports.javabeansource.CrossTableBean; +import com.forgon.disinfectsystem.jasperreports.service.dataindex.DataIndex; +import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams; +import com.forgon.systemsetting.service.HttpOptionManager; +import com.forgon.tools.MathTools; +import com.forgon.tools.date.DateTools; +import com.forgon.tools.db.DatabaseUtil; + +/** + * 清洗区工作量及不合格率汇总表 + * + */ +@Component +public class WashWorkloadReprotHelper extends ReportHelper{ + @Autowired + private HttpOptionManager httpOptionManager; + @Autowired + private FormDefinitionHelper formDefinitionHelper; + @Autowired + private ReportSqlUtil reportSqlUtil; + @Autowired + private CommonReportHelper commonReportHelper; + /** + * 获取清洗区工作量及不合格率统计报表的数据 + * @param startMonth 开始时间 + * @param endMonth 结束时间 + * @param querySupplyRoom 供应室 + * @param queryDateType 查询时间类型 year or month or day + * @return + */ + public List getWashWorkloadList(String startTime, String endTime, String querySupplyRoom, String queryDateType) { + //查询的开始时间 + String startDateStr; + //查询的结束时间 + String endDateStr; + /** + * 数据查询的时间类型 queryDateType为年份是值为month 否则day + */ + String dateType; + /** + * 清洗数量map <时间,<类型,数量>> + */ + Map> washDateAmountMap; + /** + * 不合格数据map <时间,数量> + */ + Map unQualityAmountMap = new HashMap(); + /** + * 分组数据map <时间,<组名,数量>> + */ + Map> dateAmountMapOfDepartGroup = new HashMap>(); + /** + * 表格列头 + */ + Map titleColumnNumMap = new HashMap(); + /** + * 科室分组 + */ + Map> groupAndDepartMap = httpOptionManager.getGroupAndDepartMap(); + /** + * 最终返回的交叉表数据 + */ + List ctBeanList = new ArrayList(); + /** + * 多个方法中用到的对象 + */ + ReportQueryParams params = new ReportQueryParams(); + SimpleDateFormat sdfyyyyMMdd; + /** + * 开始时间 + */ + Date startDate = null; + /** + * 结束时间 + */ + Date endDate = null; + /** + * 日期差量 查询天为Calendar.MONTH 否则 Calendar.DAY_OF_MONTH + */ + Integer addDateType; + Calendar calendar = Calendar.getInstance();; + DecimalFormat decimalFormat = new DecimalFormat("0.0000"); + + + sdfyyyyMMdd = new SimpleDateFormat("yyyy-MM-dd"); + SimpleDateFormat sdfyyyyMMddHHmmss = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); + + params.querySupplyRoom = querySupplyRoom; + endDate = calendar.getTime(); + if("year".equals(queryDateType)){ + dateType = "month"; + startDateStr = startTime + "-01-01 00:00:00"; + try { + startDate = sdfyyyyMMddHHmmss.parse(startDateStr); + } catch (ParseException e) { + e.printStackTrace(); + } + calendar.setTime(startDate); + calendar.add(Calendar.YEAR, 1); + endDateStr = sdfyyyyMMdd.format(calendar.getTime()) + " 00:00:00"; + }else if("day".equals(queryDateType)){ + dateType = "day"; + startDateStr = startTime + " 00:00:00"; + endDateStr = endTime + " 23:59:59"; + }else{//按月份查询查询的是天 + dateType = "day"; + startDateStr = startTime + "-01 00:00:00"; + if(StringUtils.isBlank(endTime)){ + try { + startDate = sdfyyyyMMddHHmmss.parse(startDateStr); + } catch (ParseException e) { + e.printStackTrace(); + } + calendar.setTime(startDate); + calendar.add(Calendar.MONTH, 1); + endDateStr = sdfyyyyMMdd.format(calendar.getTime()) + " 00:00:00"; + }else{ + String[] dateArr = endTime.split("-"); + endDateStr = DateTools.getLastDayOfMonthByDate(dateArr[0], dateArr[1]) + " 23:59:59"; + } + } + if("month".equals(dateType)){ + addDateType = Calendar.MONTH; + params.monthlyStr = dateQueryAdapter.dateToVarchar2("qi.datetime"); + }else{ + addDateType = Calendar.DAY_OF_MONTH; + params.monthlyStr = dateQueryAdapter.dateToVarchar1("qi.datetime"); + } + try { + if(startDate == null){//按月份查询的 如果没传结束月份 前面已经获取过 + startDate = sdfyyyyMMddHHmmss.parse(startDateStr); + } + calendar.setTime(startDate); + endDate = sdfyyyyMMddHHmmss.parse(endDateStr); + } catch (ParseException e) { + e.printStackTrace(); + } + + int clounmNum = 0; + titleColumnNumMap.put("普通器械", clounmNum++); + titleColumnNumMap.put("手术器械", clounmNum++); + titleColumnNumMap.put("外来器械", clounmNum++); + titleColumnNumMap.put("消毒物品", clounmNum++); + titleColumnNumMap.put("外来器械二次清洗", clounmNum++); + if(MapUtils.isNotEmpty(groupAndDepartMap)){ + for(String key : groupAndDepartMap.keySet()){ + titleColumnNumMap.put(key, clounmNum++); + } + } + titleColumnNumMap.put("清洗总数", clounmNum++); + titleColumnNumMap.put("不合格数", clounmNum++); + titleColumnNumMap.put("不合格率", clounmNum++); + //查询清洗 + int dataSoureOfMaterialsCountOfToussesInReports = CssdUtils.getSystemSetConfigByNameInt("dataSoureOfMaterialsCountOfToussesInReports", 3); + String washWorkloadSql = reportSqlUtil.getWashWorkloadSql(params, startDateStr, endDateStr, querySupplyRoom, dataSoureOfMaterialsCountOfToussesInReports, true,"yyyy-mm-dd HH24:MI:SS",dateType); + washDateAmountMap = commonReportHelper.getWashWorkloadAmountMap(washWorkloadSql,true); + //查询不合格 + String[] washSumUnqualifiedSource = formDefinitionHelper.getModelSources(GoodsOption.MODEL_WASHUNQUALIFIEDSOURCE, null); + String unQualitySql = DataIndex.getQMAmountSqlByFormNames(washSumUnqualifiedSource, params, true); + ResultSet unQualityRs = null; + try { + unQualityRs = objectDao.executeSql(unQualitySql); + while(unQualityRs.next()){ + unQualityAmountMap.put(unQualityRs.getString("monthstr"), unQualityRs.getInt("amount")); + } + } catch (SQLException e) { + e.printStackTrace(); + } finally { + DatabaseUtil.closeResultSetAndStatement(unQualityRs); + } + //设置分组数据 + if(MapUtils.isNotEmpty(groupAndDepartMap)){ + Set allDeparts = new HashSet(); + Map> groupNameMap = new HashMap>(); + for(Entry> entry : groupAndDepartMap.entrySet()){ + String groupName = entry.getKey(); + List departList = entry.getValue(); + Set thisDeparts = new HashSet(); + if(CollectionUtils.isNotEmpty(departList)){ + allDeparts.addAll(departList); + thisDeparts.addAll(departList); + } + groupNameMap.put(groupName, thisDeparts); + } + if(allDeparts.size() > 0){ + String departs = StringUtils.join(allDeparts,"','"); + String departGroupSql = getDepartGroupSql(departs,startDateStr,endDateStr,querySupplyRoom,dateType); + ResultSet rs = null; + try { + rs = objectDao.executeSql(departGroupSql); + while(rs.next()){ + String queryDate = rs.getString("wrDate"); // 时间 + String orgUnitName = rs.getString("orgUnitName"); // 时间 + Integer amount = rs.getInt("amount"); // 数量 + Map groupNameAmountMap = null; + if(dateAmountMapOfDepartGroup.containsKey(queryDate)){ + groupNameAmountMap = dateAmountMapOfDepartGroup.get(queryDate); + }else{ + groupNameAmountMap = new HashMap(); + dateAmountMapOfDepartGroup.put(queryDate, groupNameAmountMap); + } + //找到科室对应的部门 增加数量 不排除科室属于多部门 + for(Entry> entry : groupNameMap.entrySet()){ + String groupName = entry.getKey(); + if(entry.getValue().contains(orgUnitName)){ + groupNameAmountMap.put(groupName, MathTools.add(groupNameAmountMap.get(groupName), amount).intValue()); + } + } + } + } catch (SQLException e) { + e.printStackTrace(); + } finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + } + //组合返回数据 + SimpleDateFormat sdf = null; + if("month".equals(dateType)){ + sdf = new SimpleDateFormat("yyyy-MM"); + }else{ + sdf = sdfyyyyMMdd; + } + while(endDate.after(startDate)){ + String thisStartDate = sdf.format(startDate); + Map thisWashAmountMap = washDateAmountMap.containsKey(thisStartDate)?washDateAmountMap.get(thisStartDate):new HashMap(); + Integer thisUnQualityAmount = unQualityAmountMap.get(thisStartDate); + if(thisWashAmountMap == null){ + thisWashAmountMap = new HashMap(); + } + String rate = "0.0000%"; + CrossTableBean ctBean = getCrossTableBean("普通器械",titleColumnNumMap,thisStartDate,thisWashAmountMap.get("普通器械"),null); + ctBeanList.add(ctBean); + CrossTableBean ctBean1 = getCrossTableBean("手术器械",titleColumnNumMap,thisStartDate,thisWashAmountMap.get("手术器械"),null); + ctBeanList.add(ctBean1); + CrossTableBean ctBean2 = getCrossTableBean("外来器械",titleColumnNumMap,thisStartDate,thisWashAmountMap.get("外来器械"),null); + ctBeanList.add(ctBean2); + CrossTableBean ctBean3 = getCrossTableBean("消毒物品",titleColumnNumMap,thisStartDate,thisWashAmountMap.get("消毒物品"),null); + ctBeanList.add(ctBean3); + CrossTableBean ctBean4 = getCrossTableBean("外来器械二次清洗",titleColumnNumMap,thisStartDate,thisWashAmountMap.get("外来器械二次清洗"),null); + ctBeanList.add(ctBean4); + CrossTableBean ctBean5 = getCrossTableBean("清洗总数",titleColumnNumMap,thisStartDate,thisWashAmountMap.get("清洗总数"),null); + ctBeanList.add(ctBean5); + CrossTableBean ctBean6 = getCrossTableBean("不合格数",titleColumnNumMap,thisStartDate,thisUnQualityAmount,null); + ctBeanList.add(ctBean6); + if(thisUnQualityAmount != null && thisUnQualityAmount > 0 && thisWashAmountMap != null && thisWashAmountMap.get("清洗总数") != null && thisWashAmountMap.get("清洗总数") > 0){ + rate = decimalFormat.format(100.00d*thisUnQualityAmount/thisWashAmountMap.get("清洗总数")) + "%"; + } + CrossTableBean ctBean7 = getCrossTableBean("不合格率",titleColumnNumMap,thisStartDate,0,rate); + ctBeanList.add(ctBean7); + if(MapUtils.isNotEmpty(groupAndDepartMap)){ + Map groupNameAmountMap = dateAmountMapOfDepartGroup.get(thisStartDate); + for (String groupName : groupAndDepartMap.keySet()) { + Integer amount = null; + if(MapUtils.isNotEmpty(groupNameAmountMap) && groupNameAmountMap.containsKey(groupName)){ + amount = groupNameAmountMap.get(groupName); + }else{ + amount = 0; + } + CrossTableBean ctBean8 = getCrossTableBean(groupName,titleColumnNumMap,thisStartDate + ,amount + ,null); + if(ctBean8 != null){ + ctBeanList.add(ctBean8); + } + } + } + calendar.setTime(startDate); + calendar.add(addDateType, 1); + startDate = calendar.getTime(); + } + // 对数据做合计 ctBeanList + Map totalMap = getSumMap(ctBeanList); + for(Entry entry : titleColumnNumMap.entrySet()){ + String title = entry.getKey(); + Integer columnNum = entry.getValue(); + Integer totalCount = totalMap.get(title); + CrossTableBean sumBean = new CrossTableBean(); + sumBean.setQueryDate("合计"); + sumBean.setColummTitle(title); + sumBean.setColumnNum(columnNum); + sumBean.setValueInteger(totalCount); + ctBeanList.add(sumBean); + } + Integer totalAmount = totalMap.get("清洗总数"); + Integer unQualitotalAmount = totalMap.get("不合格数"); + String rate = "0.0000%"; + if(totalAmount > 0 && unQualitotalAmount > 0){ + rate = decimalFormat.format(100.00d*unQualitotalAmount/totalAmount) + "%"; + } + CrossTableBean ctBean9 = getCrossTableBean("不合格率",titleColumnNumMap,"合计",0,rate); + if(ctBean9 != null){ + ctBeanList.add(ctBean9); + } + return ctBeanList; + } + /** + * 获取交叉报表的bean + * @param title + * @param titleColunmMap + * @param date + * @param valueInteger + * @param value + * @return + */ + private CrossTableBean getCrossTableBean(String title,Map titleColunmMap,String date,Integer valueInteger,String value){ + if(StringUtils.isBlank(title)){ + return null; + } + CrossTableBean ctBean = new CrossTableBean(); + ctBean.setColummTitle(title); + ctBean.setColumnNum(titleColunmMap.get(title)); + ctBean.setQueryDate(date); + // 有字符类型的值优先设置，否则设置数字类型的值 + if(StringUtils.isNotBlank(value)){ + ctBean.setValue(value); + }else{ + ctBean.setValueInteger(valueInteger); + } + return ctBean; + } + // 获取汇总的map + private Map getSumMap(List ctBeanList){ + Map sumMap = new HashMap(); + if(CollectionUtils.isNotEmpty(ctBeanList)){ + for(CrossTableBean ctBean : ctBeanList){ + String title = ctBean.getColummTitle(); + if(StringUtils.isNotBlank(title)){ + if(sumMap.containsKey(title)){ + Integer curAmount = sumMap.get(title); + Integer amount = (ctBean.getValueInteger() == null ? MathTools.ZERO_INTEGER : ctBean.getValueInteger()) + curAmount; + sumMap.put(title, amount); + }else{ + Integer amount = ctBean.getValueInteger() == null ? MathTools.ZERO_INTEGER : ctBean.getValueInteger(); + if(amount == null){ + sumMap.put(title, 0); + }else{ + sumMap.put(title, amount); + } + } + } + } + } + return sumMap; + } + private String getDepartGroupSql(String departs,String sartDate,String endDate,String querySupplyRoom,String dateType){ + String sql = ""; + String timeSql = null; + if("month".equals(dateType)){ + timeSql = dateQueryAdapter.dateToVarchar2("wr.startDate"); + }else{ + timeSql = dateQueryAdapter.dateToVarchar1("wr.startDate"); + } + sql = " select c.wrDate,c.orgUnitName,sum(c.mAmount) amount from " + + " (((select " + timeSql + " wrDate,ci.orgUnitName,sum(ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) mAmount " + + " from WashAndDisinfectRecord wr,ClassifyBasket_WashRecord cw,ClassifyBasket cb," + + " ClassifiedItem ci where wr.id=cw.WashAndDisinfectRecord_ID and cw.ClassifyBasket_ID=cb.id " + + " and cb.id=ci.classifybasket_id " + + " and ci.itemType='" + ClassifiedItem.TYPE_MATERIAL + "' " + + " and ci.orgUnitName in ('" + departs + "')" + + " and ( wr.startDate between "+dateQueryAdapter.dateConverAdapter2(sartDate,"yyyy-mm-dd HH24:MI:SS") + + " and "+dateQueryAdapter.dateConverAdapter2(endDate,"yyyy-mm-dd HH24:MI:SS") + ")" + + " and wr.orgUnitCoding = '"+querySupplyRoom+"'" + + " group by " + timeSql + " ,ci.orgUnitName)) " + + " union all " + + " (select " + timeSql + " wrDate,ci.orgUnitName, sum((ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end)*tdc.amount) tAmount " + + " from WashAndDisinfectRecord wr,ClassifyBasket_WashRecord cw,ClassifyBasket cb," + + " ClassifiedItem ci ,(select td.id tdID, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td " + + " where mi.tousse_id=td.id and td.forDisplay=1 group by td.id) tdc " + + " where wr.id=cw.WashAndDisinfectRecord_ID and cw.ClassifyBasket_ID=cb.id " + + " and cb.id=ci.classifybasket_id and ci.toussedefinition_id=tdc.tdID " + + " and ci.itemType != '" + ClassifiedItem.TYPE_MATERIAL + "' " + + " and ci.orgUnitName in ('" + departs + "')" + + " and ( wr.startDate between "+dateQueryAdapter.dateConverAdapter2(sartDate,"yyyy-mm-dd HH24:MI:SS") + + " and "+dateQueryAdapter.dateConverAdapter2(endDate,"yyyy-mm-dd HH24:MI:SS") + ")" + + " and wr.orgUnitCoding = '"+querySupplyRoom+"'" + + " group by " + timeSql + ",ci.orgUnitName) " + + " ) c " + + " group by c.wrDate,c.orgUnitName "; + return sql; + } +} Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r35801 -r35802 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35801) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35802) @@ -187,13 +187,16 @@ import com.forgon.disinfectsystem.jasperreports.util.MonthReportGroupBySterilizationModeHelper; 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.RealTimeBulletinBoardWorkloadHelper; import com.forgon.disinfectsystem.jasperreports.util.ReportSqlUtil; import com.forgon.disinfectsystem.jasperreports.util.StatisticalWorkloadHelper; import com.forgon.disinfectsystem.jasperreports.util.SteAmountEachBranchReportHelper; +import com.forgon.disinfectsystem.jasperreports.util.SterilizationWorkloadReportHelper; import com.forgon.disinfectsystem.jasperreports.util.SupplyRoomQualityQuotaHelper; import com.forgon.disinfectsystem.jasperreports.util.TousseWorkLoadDataForDLZXYYHelper; import com.forgon.disinfectsystem.jasperreports.util.TousseWorkLoadHelper; +import com.forgon.disinfectsystem.jasperreports.util.WashWorkloadReprotHelper; import com.forgon.disinfectsystem.jasperreports.util.YearWorkloadReportHelper; import com.forgon.disinfectsystem.jasperreports.util.YearWorkloadReportSpecialHelper; import com.forgon.disinfectsystem.packing.service.PackingManager; @@ -354,6 +357,12 @@ private DepartSatisfyReportHelper departSatisfyReportHelper; @Autowired private PackingUnqualifieReportHelper packingUnqualifieReportHelper; + @Autowired + private SterilizationWorkloadReportHelper sterilizationWorkloadReportHelper; + @Autowired + private WashWorkloadReprotHelper washWorkloadReprotHelper; + @Autowired + private QualityMonitoringHelper qualityMonitoringHelper; public void setPackingManager(PackingManager packingManager) { this.packingManager = packingManager; } @@ -799,524 +808,8 @@ @Override public List getDisinfectionFDSource( String startTime,String endTime, String querySupplyRoom, String queryDateType) { - // 数据是否有消毒物品数量 如果没有要隐藏列 - boolean hasDisinfection = false; - //灭菌数量 - String sterilizationSql; - //数量map<时间,<列头,数量>> - Map> amountMap = new HashMap>(); - //不合格数据查询sql - String unQualitySql; - //不合格数据map <时间,<表单名称,数量>> - Map> unQualityAmountMap = new HashMap>(); - //表格列头 - Map sourceSortMap = new HashMap(); - //最终返回的表数据 - List returnList = new ArrayList(); - - //查询的开始时间 - String startDateStr; - //查询的结束时间 - String endDateStr; - //开始时间 - Date startDate = null; - //结束时间 - Date endDate = null; - //日期差量 查询天为Calendar.MONTH 否则 Calendar.DAY_OF_MONTH - Integer addDateType; - //数据查询的时间类型 queryDateType为年份是值为month 否则day - String dateType; - SimpleDateFormat sdfyyyyMMdd = new SimpleDateFormat("yyyy-MM-dd"); - SimpleDateFormat sdfyyyyMMddHHmmss = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); - //不同查询时间类型的一些处理 - Calendar calendar = Calendar.getInstance(); - endDate = calendar.getTime(); - if("year".equals(queryDateType)){ - dateType = "month"; - startDateStr = startTime + "-01-01 00:00:00"; - try { - startDate = sdfyyyyMMddHHmmss.parse(startDateStr); - } catch (ParseException e) { - e.printStackTrace(); - } - calendar.setTime(startDate); - calendar.add(Calendar.YEAR, 1); - endDateStr = sdfyyyyMMdd.format(calendar.getTime()) + " 00:00:00"; - }else if("day".equals(queryDateType)){ - dateType = "day"; - startDateStr = startTime + " 00:00:00"; - endDateStr = endTime + " 23:59:59"; - }else{//按月份查询查询的是天 - dateType = "day"; - startDateStr = startTime + "-01 00:00:00"; - if(StringUtils.isBlank(endTime)){ - try { - startDate = sdfyyyyMMddHHmmss.parse(startDateStr); - } catch (ParseException e) { - e.printStackTrace(); - } - calendar.setTime(startDate); - calendar.add(Calendar.MONTH, 1); - endDateStr = sdfyyyyMMdd.format(calendar.getTime()) + " 00:00:00"; - }else{ - String[] dateArr = endTime.split("-"); - endDateStr = DateTools.getLastDayOfMonthByDate(dateArr[0], dateArr[1]) + " 23:59:59"; - } - } - if("month".equals(dateType)){ - addDateType = Calendar.MONTH; - }else{ - addDateType = Calendar.DAY_OF_MONTH; - } - try { - if(startDate == null){//按月份查询的 如果没传结束月份 前面已经获取过 - startDate = sdfyyyyMMddHHmmss.parse(startDateStr); - } - calendar.setTime(startDate); - endDate = sdfyyyyMMddHHmmss.parse(endDateStr); - } catch (ParseException e) { - e.printStackTrace(); - } - - - sourceSortMap.put("器械包", 1); - sourceSortMap.put("外来器械包", 2); - sourceSortMap.put("敷料包", 3); - sourceSortMap.put("代理灭菌包", 4); - sourceSortMap.put(TousseDefinition.PACKAGE_TYPE_DISINFECTION, 5); - sourceSortMap.put("灭菌炉数", 6); - sourceSortMap.put("灭菌总数", 7); - - getSterilizationAmountOfDisinfectionFD(amountMap, dateType, startDate, endDate, querySupplyRoom, hasDisinfection); - setUnQualityAmountMapOfDisinfectionFD(unQualityAmountMap, dateType, querySupplyRoom, startDate, endDate); - - SimpleDateFormat sdf = null; - if("month".equals(dateType)){ - sdf = new SimpleDateFormat("yyyy-MM"); - }else{ - sdf = sdfyyyyMMdd; - } - // 日期没数据的map 没数据也要在报表显示0 - Map noDataMap = new HashMap(); - noDataMap.put(TousseDefinition.PACKAGE_TYPE_INSIDE, 0); - noDataMap.put(TousseDefinition.PACKAGE_TYPE_FOREIGN, 0); - noDataMap.put(TousseDefinition.PACKAGE_TYPE_DRESSING, 0); - noDataMap.put(TousseDefinition.PACKAGE_TYPE_PROXY, 0); - noDataMap.put(TousseDefinition.PACKAGE_TYPE_DISINFECTION, 0); - noDataMap.put("灭菌炉数", 0); - noDataMap.put("灭菌总数", 0); - while(endDate.after(startDate)){ - String thisStartDate = sdf.format(startDate); - Integer amount = 0;//这个日期的灭菌总数 - Map typeAmoutMap = null; - if(amountMap.containsKey(thisStartDate)){ - typeAmoutMap = amountMap.get(thisStartDate); - amount = typeAmoutMap.get("灭菌总数"); - }else{ - typeAmoutMap = noDataMap; - } - Integer unqualifiedAmount = 0;//不合格总数 - double fractionDefective = 0.00;//不合格率 - if(unQualityAmountMap.containsKey(thisStartDate)){ - Map formNameAmountMap = unQualityAmountMap.get(thisStartDate); - unqualifiedAmount = formNameAmountMap.get("不合格总数"); - if (amount != null && amount != 0 && unqualifiedAmount != null && unqualifiedAmount != 0) { - fractionDefective = new BigDecimal(unqualifiedAmount).divide(new BigDecimal(amount),4,RoundingMode.HALF_UP).doubleValue(); - } - for(Entry entry : formNameAmountMap.entrySet()){ - String monitoringName = entry.getKey(); - if("不合格总数".equals(monitoringName)){ - continue; - } - DisinfectionFractionDefectiveBean bean = new DisinfectionFractionDefectiveBean(); - bean.setDate(thisStartDate); - bean.setResult(entry.getValue()); - bean.setTitle(monitoringName); - bean.setPercentage(fractionDefective); - bean.setTitleSort(15); - returnList.add(bean); - } - } - for(Entry entry : typeAmoutMap.entrySet()){ - String title = entry.getKey(); - DisinfectionFractionDefectiveBean bean = new DisinfectionFractionDefectiveBean(); - bean.setDate(thisStartDate); - bean.setResult(entry.getValue()); - bean.setTitle(title); - bean.setTitleSort(sourceSortMap.get(title)); - bean.setPercentage(fractionDefective); - returnList.add(bean); - } - calendar.setTime(startDate); - calendar.add(addDateType, 1); - startDate = calendar.getTime(); - } - // 月合计汇总行 - Map totalRowMap = new HashMap(); - Integer monthUnqualifiedAmount = 0; // 月不合格总数 - for (DisinfectionFractionDefectiveBean bean : returnList) { - String title = bean.getTitle(); - Integer result = bean.getResult(); - if (totalRowMap.get(title) == null) { - totalRowMap.put(title, result); - } else if (totalRowMap.get(title) != null) { - totalRowMap.put(title, - (result + totalRowMap.get(title))); - } - if (bean.getTitleSort() == 15) { - monthUnqualifiedAmount += result; - } - } - Integer monthTotalAmount = totalRowMap.get("灭菌总数"); // 月灭菌总数 - try { - for(Entry entry : totalRowMap.entrySet()){ - String title = entry.getKey(); - Integer result = entry.getValue(); - DisinfectionFractionDefectiveBean bean = new DisinfectionFractionDefectiveBean(); - bean.setDate("合计"); - bean.setResult(result); - bean.setTitle(title); - Integer sort = sourceSortMap.get(title); - if (sort == null) { - sort = 15; - } - bean.setTitleSort(sort); - Double fractionDefective = 0.00; - if(monthTotalAmount > 0 && monthUnqualifiedAmount > 0){ - fractionDefective = new BigDecimal(monthUnqualifiedAmount).divide(new BigDecimal(monthTotalAmount),4,RoundingMode.HALF_UP).doubleValue(); - } - bean.setPercentage(fractionDefective); - returnList.add(bean); - } - } catch (Exception e) { - } - if(!hasDisinfection){//没有消毒物品,不显示消毒物品列 - Iterator it = returnList.iterator(); - while(it.hasNext()){ - if(TousseDefinition.PACKAGE_TYPE_DISINFECTION.equals(it.next().getTitle())){ - it.remove(); - } - } - } - - return returnList; + return sterilizationWorkloadReportHelper.getDisinfectionFDSource(startTime, endTime, querySupplyRoom, queryDateType); } - /** - * 设置不合格数据源 - */ - private void setUnQualityAmountMapOfDisinfectionFD(Map> unQualityAmountMap, String dateType, String querySupplyRoom, Date startDate, Date endDate){ - String queryDateSQL = null; - if("month".equals(dateType)){ - queryDateSQL = dateQueryAdapter.dateToVarchar2("qi.datetime"); - }else{ - queryDateSQL = dateQueryAdapter.dateToVarchar1("qi.datetime"); - } - String unQualitySql = "select fd.formName,sum(qmd.amount) amount,"+ queryDateSQL +" monthstr from QualityMonitoringInstance qi ,FormInstance fi," - + "FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd " - + " where qi.id = fi.id and fi.formDefinition_id = fd.id and fd.id = qmdf.id " - + " and qi.id=qmd.qualityMonitoringInstance_id " - + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) - + " and qmdf.responsibilitypart = '灭菌管理' and fd.formType = '质量监测' " - + SqlUtils.get_LikeSql("fd.departcodes", - querySupplyRoom.split(",")) - + " and qi.datetime between " - + dateQueryAdapter.dateAdapter(startDate) - + " and " - + dateQueryAdapter.dateAdapter(endDate) - + " group by fd.formName," - + queryDateSQL; - ResultSet unQualityRs = null; - try { - unQualityRs = objectDao.executeSql(unQualitySql); - while(unQualityRs.next()){ - String monthstr = unQualityRs.getString("monthstr"); - Map formNameAmountMap = null; - if(unQualityAmountMap.containsKey(monthstr)){ - formNameAmountMap = unQualityAmountMap.get(monthstr); - }else{ - formNameAmountMap = new HashMap(); - unQualityAmountMap.put(monthstr, formNameAmountMap); - } - int amount = unQualityRs.getInt("amount"); - formNameAmountMap.put(unQualityRs.getString("formName"), amount); - formNameAmountMap.put("不合格总数", MathTools.add(amount, formNameAmountMap.get("不合格总数")).intValue()); - } - } catch (SQLException e) { - e.printStackTrace(); - } finally { - DatabaseUtil.closeResultSetAndStatement(unQualityRs); - } - } - private void getSterilizationAmountOfDisinfectionFD(Map> amountMap, String dateType, Date startDate, Date endDate, String querySupplyRoom, boolean hasDisinfection){ - String queryDateSQL = ""; - if("month".equals(dateType)){ - queryDateSQL = dateQueryAdapter.dateToVarchar2("po.endDate"); - }else{ - queryDateSQL = dateQueryAdapter.dateToVarchar1("po.endDate"); - } - ResultSet rs = null; - try { - String sterilizationSql = " select sum(po.tousseAmount) tousseAmount,sum(po.foreignTousseAmount) foreignTousseAmount" - + ",sum(po.dressingTousseAmount) dressingTousseAmount,sum(po.proxyTousseAmount) proxyTousseAmount" - + ",sum(po.disinfectionAmount) disinfectionAmount," - + queryDateSQL - +" dateStr,sum(po.amount) amount,count(po.id) srAmount from " - + SterilizationRecord.class.getSimpleName() - +" po where po.endDate between " - + dateQueryAdapter.dateAdapter(startDate) + " and " - + dateQueryAdapter.dateAdapter(endDate) - + SqlUtils.get_InSql_Extra("po.orgUnitCoding", querySupplyRoom) - + " and (po.status = '" - + SterilizationRecord.STERILIZATION_STATUS_END + "'" - + " or po.status = '" - + SterilizationRecord.STERILIZATION_STATUS_FAILURE - + "') group by " - + queryDateSQL; - rs = objectDao.executeSql(sterilizationSql); - while (rs.next()) { - int tousseAmount = rs.getInt("tousseAmount"); - int foreignTousseAmount = rs.getInt("foreignTousseAmount"); - int dressingTousseAmount = rs.getInt("dressingTousseAmount"); - int proxyTousseAmount = rs.getInt("proxyTousseAmount"); - int disinfectionAmount = rs.getInt("disinfectionAmount"); - if(!hasDisinfection && disinfectionAmount > 0){ - hasDisinfection = true; - } - String dateStr = rs.getString("dateStr"); - Map tousseTypeAmountMap = null; - if(amountMap.containsKey(dateStr)){ - tousseTypeAmountMap = amountMap.get(dateStr); - }else{ - tousseTypeAmountMap = new HashMap(); - amountMap.put(dateStr, tousseTypeAmountMap); - } - tousseTypeAmountMap.put(TousseDefinition.PACKAGE_TYPE_INSIDE, tousseAmount); - tousseTypeAmountMap.put(TousseDefinition.PACKAGE_TYPE_FOREIGN, foreignTousseAmount); - tousseTypeAmountMap.put(TousseDefinition.PACKAGE_TYPE_DRESSING, dressingTousseAmount); - tousseTypeAmountMap.put(TousseDefinition.PACKAGE_TYPE_PROXY, proxyTousseAmount); - tousseTypeAmountMap.put(TousseDefinition.PACKAGE_TYPE_DISINFECTION, disinfectionAmount); - tousseTypeAmountMap.put("灭菌炉数", rs.getInt("srAmount")); - tousseTypeAmountMap.put("灭菌总数", rs.getInt("amount")); - } - } catch (SQLException e) { - e.printStackTrace(); - } finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - } - /*//实时查包实例的方法获取灭菌区工作量及不合格率月报的数据，不从冗余字段获取数据 暂时不使用本方法 - private List getDisinfectionFDSource2( - String month,String departCoding){ - String sql = getSterileSqlSql(month, departCoding); - logger.debug("sql:"+sql); - ResultSet rs = objectDao.executeSql(sql); - Integer unqualifiedAmount = 0; - Map monitoringMap = new HashMap(); - Map> monthStrToResultSetMap = new HashMap>(); - // 排序 - Map sourceSortMap = new HashMap(); - try { - sourceSortMap.put("器械包", 1); - sourceSortMap.put("外来器械包", 2); - sourceSortMap.put("敷料包", 3); - sourceSortMap.put("代理灭菌包", 4); - sourceSortMap.put("灭菌炉数", 7); - sourceSortMap.put("灭菌总数", 8); - boolean hasDisinfection = false; - boolean hasOther = false; - while (rs.next()) { - String tousseType = rs.getString("tousseType"); - String monthstr = rs.getString("monthstr"); - Integer amount = rs.getInt("amount"); - int sterilizationAmount = rs.getInt("sterilizationAmount"); - DisinfectionFractionDefectiveResultResultVo vo = new DisinfectionFractionDefectiveResultResultVo(); - vo.setAmount(amount); - vo.setSterilizationAmount(sterilizationAmount); - vo.setTousseType(tousseType); - List vos = null; - if(monthStrToResultSetMap.containsKey(monthstr)){ - vos = monthStrToResultSetMap.get(monthstr); - }else{ - vos = new ArrayList(); - monthStrToResultSetMap.put(monthstr, vos); - } - vos.add(vo); - if(!hasDisinfection && TousseDefinition.PACKAGE_TYPE_DISINFECTION.equals(tousseType)){ - hasDisinfection = true; - sourceSortMap.put(TousseDefinition.PACKAGE_TYPE_DISINFECTION, 5); - } - if(!hasOther && !TousseDefinition.PACKAGE_TYPE_CUSTOM.equals(tousseType) - && !TousseDefinition.PACKAGE_TYPE_COMBO.equals(tousseType) - && !TousseDefinition.PACKAGE_TYPE_INSIDE.equals(tousseType) - && !TousseDefinition.PACKAGE_TYPE_FOREIGN.equals(tousseType) - && !TousseDefinition.PACKAGE_TYPE_SPLIT.equals(tousseType) - && !TousseDefinition.PACKAGE_TYPE_DRESSING.equals(tousseType) - && !TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY.equals(tousseType) - && !TousseDefinition.PACKAGE_TYPE_DISINFECTION.equals(tousseType)){ - sourceSortMap.put("其他", 6); - hasOther = true; - } - } - } catch (Exception e) { - e.printStackTrace(); - } finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - - List returnList = new ArrayList(); - if (StringUtils.isNotBlank(month)) { - List dateOfMonth = DisinfectionFractionDefectiveBean - .getDateOfMonth(month); - for (String queryDate : dateOfMonth) { - String startDate = queryDate + " 00:00:00"; - String endDate = queryDate + " 23:59:59"; - Integer tousseAmount = 0;// 内部包总数 - Integer foreignTousseAmount = 0;// 外来包总数 - Integer dressingTousseAmount = 0;// 敷料包总数 - Integer proxyTousseAmount = 0;// 代理灭菌包 - Integer amount = 0;// 总数 - Integer otherAmount = 0; - Integer disinfectionAmount = 0; - int sterilizationAmount = 0; - if(monthStrToResultSetMap.containsKey(queryDate)){ - for(DisinfectionFractionDefectiveResultResultVo vo : monthStrToResultSetMap.get(queryDate)){ - String tousseType = vo.getTousseType(); - Integer voAmount = vo.getAmount(); - if(voAmount == null){ - voAmount = 0; - } - if(TousseDefinition.PACKAGE_TYPE_CUSTOM.equals(tousseType) - || TousseDefinition.PACKAGE_TYPE_COMBO.equals(tousseType) - || TousseDefinition.PACKAGE_TYPE_INSIDE.equals(tousseType)){ - tousseAmount += voAmount; - }else if(TousseDefinition.PACKAGE_TYPE_FOREIGN.equals(tousseType) || - TousseDefinition.PACKAGE_TYPE_SPLIT.equals(tousseType)){ - foreignTousseAmount += voAmount; - }else if(TousseDefinition.PACKAGE_TYPE_DRESSING.equals(tousseType)){ - dressingTousseAmount += voAmount; - }else if(TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY.equals(tousseType)){ - proxyTousseAmount += voAmount; - }else if(TousseDefinition.PACKAGE_TYPE_DISINFECTION.equals(tousseType)){ - disinfectionAmount += voAmount; - }else{ - otherAmount += voAmount; - } - amount += voAmount; - } - } - // 数量 - Map sourceMap = new HashMap(); - for (String key : sourceSortMap.keySet()) { - if("器械包".equals(key)){ - sourceMap.put("器械包", tousseAmount); - }else if("外来器械包".equals(key)){ - sourceMap.put("外来器械包", foreignTousseAmount); - }else if("敷料包".equals(key)){ - sourceMap.put("敷料包", dressingTousseAmount); - }else if("代理灭菌包".equals(key)){ - sourceMap.put("代理灭菌包", proxyTousseAmount); - }else if("消毒物品".equals(key)){ - sourceMap.put("消毒物品", disinfectionAmount); - }else if("其他".equals(key)){ - sourceMap.put("其他", otherAmount); - }else if("灭菌总数".equals(key)){ - sourceMap.put("灭菌总数", amount); - }else if("灭菌炉数".equals(key)){ - sourceMap.put("灭菌炉数", sterilizationAmount); - } - } - Iterator iterator = sourceMap.keySet().iterator(); - //质量监测不合格数列 - double fractionDefective = addMonitoringDataToList( - departCoding, returnList, queryDate, startDate,endDate, amount); - // 各类型包数量 - while (iterator.hasNext()) { - String title = iterator.next(); - DisinfectionFractionDefectiveBean bean = new DisinfectionFractionDefectiveBean(); - bean.setDate(queryDate); - bean.setResult(sourceMap.get(title)); - bean.setTitle(title); - bean.setTitleSort(sourceSortMap.get(title)); - bean.setPercentage(fractionDefective); - returnList.add(bean); - } - } - // 月合计汇总行 - Map totalRowMap = new HashMap(); - Integer monthUnqualifiedAmount = 0; // 月不合格总数 - for (DisinfectionFractionDefectiveBean bean : returnList) { - String title = bean.getTitle(); - Integer result = bean.getResult(); - if (totalRowMap.get(title) == null) { - totalRowMap.put(title, result); - } else if (totalRowMap.get(title) != null) { - totalRowMap.put(title, - (result + totalRowMap.get(title))); - } - if (bean.getTitleSort() == 15) { - monthUnqualifiedAmount += result; - } - } - Integer monthTotalAmount = totalRowMap.get("灭菌总数"); // 月灭菌总数 - - Iterator iterator = totalRowMap.keySet().iterator(); - try { - while (iterator.hasNext()) { - String title = iterator.next(); - Integer result = totalRowMap.get(title); - DisinfectionFractionDefectiveBean bean = new DisinfectionFractionDefectiveBean(); - bean.setDate("合计"); - bean.setResult(result); - bean.setTitle(title); - Integer sort = sourceSortMap.get(title); - if (sort == null) { - sort = 15; - } - bean.setTitleSort(sort); - Double fractionDefective = 0.00; - if(monthTotalAmount > 0 && monthUnqualifiedAmount > 0){ - fractionDefective = new BigDecimal(monthUnqualifiedAmount).divide(new BigDecimal(monthTotalAmount),4,RoundingMode.HALF_UP).doubleValue(); - } - bean.setPercentage(fractionDefective); - returnList.add(bean); - } - } catch (Exception e) { - } - } - return returnList; - }*/ - /*private String getSterileSqlSql(String month,String departCoding){ - String startTime = month + "-01 00:00:00"; - String endTime = DateTools.getLastDayOfMonthByDate(month.split("-")[0], month.split("-")[1]) + " 23:59:59"; - //数据库计算字符串长度的函数名 - //int dataSoureOfMaterialsCountOfToussesInReports = CssdUtils.getSystemSetConfigByNameInt("dataSoureOfMaterialsCountOfToussesInReports", 3); - String sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); - ReportQueryParams params = new ReportQueryParams(); - params.betweenSql = " between " - + dateQueryAdapter.dateAdapter(startTime) + " and " - + dateQueryAdapter.dateAdapter(endTime); - params.querySupplyRoom = departCoding; - params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; - params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = ""; - params.tousseGroupSqlWithAliasOfTousseDefinitionIsTd = ""; - params.tousseTypeAndPackageSizeSql = ""; - params.sqlLengthFunctionName = sqlLengthFunctionName; - params.extraSelectColumns = ",count(distinct ti.sterilizationRecord_id) sterilizationAmount "; - params.groupBySql = ""; - params.selectTousseType = true; - String tiSterileEndTime = null; - if(dbConnection.isSqlServer()){ - tiSterileEndTime = dateQueryAdapter.dateConverAdapter(dateQueryAdapter.stringFieldToDate("ti.sterileEndTime"),"yyyy-MM-dd"); - } else if(dbConnection.isOracle()){ - tiSterileEndTime = dateQueryAdapter.dateConverAdapter(dateQueryAdapter.stringFieldToDate("ti.sterileEndTime"),"yyyy-MM-dd"); - } - params.monthlyStr = tiSterileEndTime; - params.tousseTypeAndPackageSizeSql = ""; - String sterileSql = String.format("select sum(tl.amount) amount,monthstr,tousseType,sum(sterilizationAmount) sterilizationAmount from (" - +dataIndex.getWorkAmountByPackageSQL("灭菌数量", params) - + ") tl group by monthstr,tousseType order by monthstr"); - return sterileSql; - }*/ @Override public double addMonitoringDataToList(String departCoding, List returnList, @@ -4155,193 +3648,9 @@ } return list; } - /** - * 获取清洗记录加载的sql - * @param params - * @param statDate 开始时间 - * @param endDate 结束时间 - * @param querySupplyRoom 科室编码 - * @param dataSoureOfMaterialsCountOfToussesInReports 报表材料数量显示方式 - * @param queryDateType 查询时间类型 月或天 - * @return - */ - private String getWashWorkloadSql(ReportQueryParams params, String statDate, String endDate, String querySupplyRoom, Integer dataSoureOfMaterialsCountOfToussesInReports, boolean groupByDay, String format, String queryDateType){ - String betweenSql = String.format(" between %s and %s ", - dateQueryAdapter.dateConverAdapter2(statDate, format), - dateQueryAdapter.dateConverAdapter2(endDate, format)); - params.betweenSql = betweenSql; - params.querySupplyRoom = querySupplyRoom; - String groupByDaySql = null; - String queryDaySql = null; - String endDaySql = null; - if(groupByDay){ - if("month".equals(queryDateType)){ - groupByDaySql = "," + dateQueryAdapter.dateToVarchar2("wr.endDate"); - }else{ - groupByDaySql = "," + dateQueryAdapter.dateToVarchar1("wr.endDate"); - } - queryDaySql = groupByDaySql + " endDate "; - endDaySql = ",rs.endDate "; - }else{ - groupByDaySql = ""; - queryDaySql = ""; - endDaySql = ""; - } - // 统计清洗项的itemType为材料的物品，包括拆包清洗的器械包以及外来器械包 - String sql = String - .format("select ci.itemType type,ci.orgUnitName,ci.isSencondWashForForeignTousse ,sum(ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) amount, td.tousseType " - + queryDaySql - + dataIndex.getWashMaterialAmountSqlFromItemTypeIsMaterial(params.extraJoinCondition) - + " and wr.endDate %s and wr.washMaterialAmount <> 0 %s group by ci.itemType,ci.orgUnitName,ci.isSencondWashForForeignTousse, td.tousseType " - + groupByDaySql, - params.betweenSql, - SqlUtils.get_InSql_Extra("wr.orgUnitCoding", params.querySupplyRoom)); - - // 统计清洗项的itemType为材料的物品，单独入清洗篮筐的器械，没有关联的器械包 - sql += " union all "; - sql += String - .format("select ci.itemType type,ci.orgUnitName,ci.isSencondWashForForeignTousse ,sum(ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) amount, td.tousseType " - + queryDaySql - + dataIndex.getWashMaterialAmountSqlFromItemTypeIsMaterialWithoutTousseDefinition(params.extraJoinCondition) - + " and wr.endDate %s and wr.washMaterialAmount <> 0 %s group by ci.itemType,ci.orgUnitName,ci.isSencondWashForForeignTousse, td.tousseType " - + groupByDaySql, - params.betweenSql, - SqlUtils.get_InSql_Extra("wr.orgUnitCoding", params.querySupplyRoom)); - - // 统计清洗项的itemType不为材料的物品，包括整包清洗的器械包 - sql += " union all "; - String columnSql = null; - String joinMaterialInstanceSql = null; - if(dataSoureOfMaterialsCountOfToussesInReports == 3){ - columnSql = "(ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end)*ci.materialAmount"; - joinMaterialInstanceSql = ""; - }else{ - columnSql = "(ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end)*mi.count"; - joinMaterialInstanceSql = "inner join MaterialInstance mi on mi.tousse_id = td.id"; - } - sql += String - .format("select ci.itemType type,ci.orgUnitName,ci.isSencondWashForForeignTousse ,sum(%s) amount, td.tousseType " - + queryDaySql - + dataIndex.getWashMaterialAmountSqlFromItemTypeIsNotMaterial(joinMaterialInstanceSql) - + " and wr.endDate %s and wr.washMaterialAmount <> 0 %s group by ci.itemType,ci.orgUnitName,ci.isSencondWashForForeignTousse, td.tousseType " - + groupByDaySql, - columnSql, - params.betweenSql, - SqlUtils.get_InSql_Extra("wr.orgUnitCoding", params.querySupplyRoom)); - // 判断是否需要统计单独清洗的材料的数量 - // 由于要查找清洗记录所属的科室的名称，所以连接了orgUnit表进行查询，没有调用DataIndex服务类的getWashMaterialAmountSqlFromTousseTypeIsMaterial方法 - sql += " union all "; - sql += String - .format("select md.type,org.name orgUnitName,0 isSencondWashForForeignTousse ,sum(wrm.amount) amount,'' tousseType " - + queryDaySql - + "from WashAndDisinfectRecord wr, WashRecord_WashMaterial wm,WashAndDisinfectRecordMaterial wrm,MaterialDefinition md, OrgUnit org " - + "where wm.WashAndDisinfectRecord_ID = wr.id and wrm.id = wm.WashAndDisinfectMaterial_ID and wrm.materialDefinition_id = md.id and org.orgUnitCoding = wr.orgUnitCoding " - + " and wr.endDate %s %s group by md.type,org.name" - + groupByDaySql, - params.betweenSql, SqlUtils.get_InSql_Extra( - "wr.orgUnitCoding", params.querySupplyRoom)); - sql = " select rs.type,rs.orgUnitName,rs.isSencondWashForForeignTousse,sum(rs.amount) amount, rs.tousseType "+ endDaySql + " from (" - + sql - + ")rs " - + " group by rs.type,rs.orgUnitName,rs.isSencondWashForForeignTousse,rs.tousseType" + endDaySql; - return sql; - } - /** - * 获取清洗数据 按日期分组查询和计算 - * @param sql - * @return - */ - public Map> getWashWorkloadAmountMap(String sql, boolean groupByDate){ - // 手术器械设置 - GoodsOption option = goodsOptionManager.getGoodsOption(GoodsOption.MODEL_SURGICALINSTRUMENTS_DEPT, null); - List groupDeparts = httpOptionManager.getDepartsFromDepartGroup(); - Integer totalAmount = 0; - ResultSet rs = null; - Map> washAmountMap = new HashMap>(); - try { - rs = objectDao.executeSql(sql); - while(rs.next()){ - String type = rs.getString("type"); - String orgUnitName = rs.getString("orgUnitName"); - boolean isSecWashForeignTousse = rs.getBoolean("isSencondWashForForeignTousse"); - Integer amount = rs.getInt("amount"); - String tousseType = rs.getString("tousseType"); - String endDate = rs.getString("endDate"); - String key = ""; - if(ClassifiedItem.TYPE_FOREIGN_TOUSSE.equals(tousseType)){ - // 外来器械包，看是否二次清洗 - if(isSecWashForeignTousse == false){ - key = "外来器械"; - }else{ - key = "外来器械二次清洗"; - } - }else if(ClassifiedItem.TYPE_DISINFECTION_GOODS.equals(type)){ - key = "消毒物品"; - - }else{ - // 不是外来器械，不是消毒物品，就是手术器械，或者普通器械，或者分组器械 - // 是手术器械 - if (StringUtils.isNotBlank(orgUnitName) && option != null && StringUtils.isNotBlank(option.getValue()) && option.getValue().indexOf(orgUnitName) != -1) { - key = "手术器械"; - }else if( !(CollectionUtils.isNotEmpty(groupDeparts) && groupDeparts.contains(orgUnitName)) ){ - // 也不是分组器械，是普通器械 - key = "普通器械"; - } - } - if(StringUtils.isNotBlank(endDate)){ - Map mapItem = null; - if(washAmountMap.containsKey(endDate)){ - mapItem = washAmountMap.get(endDate); - }else{ - mapItem = new HashMap(); - mapItem.put("清洗总数", 0); - washAmountMap.put(endDate, mapItem); - } - if(StringUtils.isNotBlank(key)){ - if(mapItem.containsKey(key)){ - Integer curAmount = mapItem.get(key); - mapItem.put(key, curAmount + amount); - }else{ - mapItem.put(key,amount); - } - } - mapItem.put("清洗总数", mapItem.get("清洗总数") + amount); - } - } - } catch (SQLException e) { - e.printStackTrace(); - } finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - return washAmountMap; - } - /** - * 获取交叉报表的bean - * @param title - * @param titleColunmMap - * @param date - * @param valueInteger - * @param value - * @return - */ - private CrossTableBean getCrossTableBean(String title,Map titleColunmMap,String date,Integer valueInteger,String value){ - if(StringUtils.isBlank(title)){ - return null; - } - CrossTableBean ctBean = new CrossTableBean(); - ctBean.setColummTitle(title); - ctBean.setColumnNum(titleColunmMap.get(title)); - ctBean.setQueryDate(date); - // 有字符类型的值优先设置，否则设置数字类型的值 - if(StringUtils.isNotBlank(value)){ - ctBean.setValue(value); - }else{ - ctBean.setValueInteger(valueInteger); - } - return ctBean; - } + @Override public List getYearWorkloadReportData(String year) throws ParseException, SQLException { @@ -4472,339 +3781,10 @@ return list; } @Override - public List getwashWorkloadList(String startTime, String endTime, String querySupplyRoom, String queryDateType) { - //查询的开始时间 - String startDateStr; - //查询的结束时间 - String endDateStr; - /** - * 数据查询的时间类型 queryDateType为年份是值为month 否则day - */ - String dateType; - /** - * 清洗数量map <时间,<类型,数量>> - */ - Map> washDateAmountMap; - /** - * 不合格数据map <时间,数量> - */ - Map unQualityAmountMap = new HashMap(); - /** - * 分组数据map <时间,<组名,数量>> - */ - Map> dateAmountMapOfDepartGroup = new HashMap>(); - /** - * 表格列头 - */ - Map titleColumnNumMap = new HashMap(); - /** - * 科室分组 - */ - Map> groupAndDepartMap = httpOptionManager.getGroupAndDepartMap(); - /** - * 最终返回的交叉表数据 - */ - List ctBeanList = new ArrayList(); - /** - * 多个方法中用到的对象 - */ - ReportQueryParams params = new ReportQueryParams(); - SimpleDateFormat sdfyyyyMMdd; - /** - * 开始时间 - */ - Date startDate = null; - /** - * 结束时间 - */ - Date endDate = null; - /** - * 日期差量 查询天为Calendar.MONTH 否则 Calendar.DAY_OF_MONTH - */ - Integer addDateType; - Calendar calendar = Calendar.getInstance();; - DecimalFormat decimalFormat = new DecimalFormat("0.0000"); - - - sdfyyyyMMdd = new SimpleDateFormat("yyyy-MM-dd"); - SimpleDateFormat sdfyyyyMMddHHmmss = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); - - params.querySupplyRoom = querySupplyRoom; - endDate = calendar.getTime(); - if("year".equals(queryDateType)){ - dateType = "month"; - startDateStr = startTime + "-01-01 00:00:00"; - try { - startDate = sdfyyyyMMddHHmmss.parse(startDateStr); - } catch (ParseException e) { - e.printStackTrace(); - } - calendar.setTime(startDate); - calendar.add(Calendar.YEAR, 1); - endDateStr = sdfyyyyMMdd.format(calendar.getTime()) + " 00:00:00"; - }else if("day".equals(queryDateType)){ - dateType = "day"; - startDateStr = startTime + " 00:00:00"; - endDateStr = endTime + " 23:59:59"; - }else{//按月份查询查询的是天 - dateType = "day"; - startDateStr = startTime + "-01 00:00:00"; - if(StringUtils.isBlank(endTime)){ - try { - startDate = sdfyyyyMMddHHmmss.parse(startDateStr); - } catch (ParseException e) { - e.printStackTrace(); - } - calendar.setTime(startDate); - calendar.add(Calendar.MONTH, 1); - endDateStr = sdfyyyyMMdd.format(calendar.getTime()) + " 00:00:00"; - }else{ - String[] dateArr = endTime.split("-"); - endDateStr = DateTools.getLastDayOfMonthByDate(dateArr[0], dateArr[1]) + " 23:59:59"; - } - } - if("month".equals(dateType)){ - addDateType = Calendar.MONTH; - params.monthlyStr = dateQueryAdapter.dateToVarchar2("qi.datetime"); - }else{ - addDateType = Calendar.DAY_OF_MONTH; - params.monthlyStr = dateQueryAdapter.dateToVarchar1("qi.datetime"); - } - try { - if(startDate == null){//按月份查询的 如果没传结束月份 前面已经获取过 - startDate = sdfyyyyMMddHHmmss.parse(startDateStr); - } - calendar.setTime(startDate); - endDate = sdfyyyyMMddHHmmss.parse(endDateStr); - } catch (ParseException e) { - e.printStackTrace(); - } - - int clounmNum = 0; - titleColumnNumMap.put("普通器械", clounmNum++); - titleColumnNumMap.put("手术器械", clounmNum++); - titleColumnNumMap.put("外来器械", clounmNum++); - titleColumnNumMap.put("消毒物品", clounmNum++); - titleColumnNumMap.put("外来器械二次清洗", clounmNum++); - if(MapUtils.isNotEmpty(groupAndDepartMap)){ - for(String key : groupAndDepartMap.keySet()){ - titleColumnNumMap.put(key, clounmNum++); - } - } - titleColumnNumMap.put("清洗总数", clounmNum++); - titleColumnNumMap.put("不合格数", clounmNum++); - titleColumnNumMap.put("不合格率", clounmNum++); - //查询清洗 - int dataSoureOfMaterialsCountOfToussesInReports = CssdUtils.getSystemSetConfigByNameInt("dataSoureOfMaterialsCountOfToussesInReports", 3); - String washWorkloadSql = getWashWorkloadSql(params, startDateStr, endDateStr, querySupplyRoom, dataSoureOfMaterialsCountOfToussesInReports, true,"yyyy-mm-dd HH24:MI:SS",dateType); - washDateAmountMap = getWashWorkloadAmountMap(washWorkloadSql,true); - //查询不合格 - String[] washSumUnqualifiedSource = formDefinitionHelper.getModelSources(GoodsOption.MODEL_WASHUNQUALIFIEDSOURCE, null); - String unQualitySql = DataIndex.getQMAmountSqlByFormNames(washSumUnqualifiedSource, params, true); - ResultSet unQualityRs = null; - try { - unQualityRs = objectDao.executeSql(unQualitySql); - while(unQualityRs.next()){ - unQualityAmountMap.put(unQualityRs.getString("monthstr"), unQualityRs.getInt("amount")); - } - } catch (SQLException e) { - e.printStackTrace(); - } finally { - DatabaseUtil.closeResultSetAndStatement(unQualityRs); - } - //设置分组数据 - if(MapUtils.isNotEmpty(groupAndDepartMap)){ - Set allDeparts = new HashSet(); - Map> groupNameMap = new HashMap>(); - for(Entry> entry : groupAndDepartMap.entrySet()){ - String groupName = entry.getKey(); - List departList = entry.getValue(); - Set thisDeparts = new HashSet(); - if(CollectionUtils.isNotEmpty(departList)){ - allDeparts.addAll(departList); - thisDeparts.addAll(departList); - } - groupNameMap.put(groupName, thisDeparts); - } - if(allDeparts.size() > 0){ - String departs = StringUtils.join(allDeparts,"','"); - String departGroupSql = getDepartGroupSql(departs,startDateStr,endDateStr,querySupplyRoom,dateType); - CrossTableBean ctBean = null; - ResultSet rs = null; - try { - rs = objectDao.executeSql(departGroupSql); - while(rs.next()){ - String queryDate = rs.getString("wrDate"); // 时间 - String orgUnitName = rs.getString("orgUnitName"); // 时间 - Integer amount = rs.getInt("amount"); // 数量 - Map groupNameAmountMap = null; - if(dateAmountMapOfDepartGroup.containsKey(queryDate)){ - groupNameAmountMap = dateAmountMapOfDepartGroup.get(queryDate); - }else{ - groupNameAmountMap = new HashMap(); - dateAmountMapOfDepartGroup.put(queryDate, groupNameAmountMap); - } - //找到科室对应的部门 增加数量 不排除科室属于多部门 - for(Entry> entry : groupNameMap.entrySet()){ - String groupName = entry.getKey(); - if(entry.getValue().contains(orgUnitName)){ - groupNameAmountMap.put(groupName, MathTools.add(groupNameAmountMap.get(groupName), amount).intValue()); - } - } - } - } catch (SQLException e) { - e.printStackTrace(); - } finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - } - } - //组合返回数据 - SimpleDateFormat sdf = null; - if("month".equals(dateType)){ - sdf = new SimpleDateFormat("yyyy-MM"); - }else{ - sdf = sdfyyyyMMdd; - } - while(endDate.after(startDate)){ - String thisStartDate = sdf.format(startDate); - Map thisWashAmountMap = washDateAmountMap.containsKey(thisStartDate)?washDateAmountMap.get(thisStartDate):new HashMap(); - Integer thisUnQualityAmount = unQualityAmountMap.get(thisStartDate); - if(thisWashAmountMap == null){ - thisWashAmountMap = new HashMap(); - } - String rate = "0.0000%"; - CrossTableBean ctBean = getCrossTableBean("普通器械",titleColumnNumMap,thisStartDate,thisWashAmountMap.get("普通器械"),null); - ctBeanList.add(ctBean); - CrossTableBean ctBean1 = getCrossTableBean("手术器械",titleColumnNumMap,thisStartDate,thisWashAmountMap.get("手术器械"),null); - ctBeanList.add(ctBean1); - CrossTableBean ctBean2 = getCrossTableBean("外来器械",titleColumnNumMap,thisStartDate,thisWashAmountMap.get("外来器械"),null); - ctBeanList.add(ctBean2); - CrossTableBean ctBean3 = getCrossTableBean("消毒物品",titleColumnNumMap,thisStartDate,thisWashAmountMap.get("消毒物品"),null); - ctBeanList.add(ctBean3); - CrossTableBean ctBean4 = getCrossTableBean("外来器械二次清洗",titleColumnNumMap,thisStartDate,thisWashAmountMap.get("外来器械二次清洗"),null); - ctBeanList.add(ctBean4); - CrossTableBean ctBean5 = getCrossTableBean("清洗总数",titleColumnNumMap,thisStartDate,thisWashAmountMap.get("清洗总数"),null); - ctBeanList.add(ctBean5); - CrossTableBean ctBean6 = getCrossTableBean("不合格数",titleColumnNumMap,thisStartDate,thisUnQualityAmount,null); - ctBeanList.add(ctBean6); - if(thisUnQualityAmount != null && thisUnQualityAmount > 0 && thisWashAmountMap != null && thisWashAmountMap.get("清洗总数") != null && thisWashAmountMap.get("清洗总数") > 0){ - rate = decimalFormat.format(100.00d*thisUnQualityAmount/thisWashAmountMap.get("清洗总数")) + "%"; - } - CrossTableBean ctBean7 = getCrossTableBean("不合格率",titleColumnNumMap,thisStartDate,0,rate); - ctBeanList.add(ctBean7); - if(MapUtils.isNotEmpty(groupAndDepartMap)){ - Map groupNameAmountMap = dateAmountMapOfDepartGroup.get(thisStartDate); - for (String groupName : groupAndDepartMap.keySet()) { - Integer amount = null; - if(MapUtils.isNotEmpty(groupNameAmountMap) && groupNameAmountMap.containsKey(groupName)){ - amount = groupNameAmountMap.get(groupName); - }else{ - amount = 0; - } - CrossTableBean ctBean8 = getCrossTableBean(groupName,titleColumnNumMap,thisStartDate - ,amount - ,null); - if(ctBean8 != null){ - ctBeanList.add(ctBean8); - } - } - } - calendar.setTime(startDate); - calendar.add(addDateType, 1); - startDate = calendar.getTime(); - } - // 对数据做合计 ctBeanList - Map totalMap = getSumMap(ctBeanList); - for(Entry entry : titleColumnNumMap.entrySet()){ - String title = entry.getKey(); - Integer columnNum = entry.getValue(); - Integer totalCount = totalMap.get(title); - CrossTableBean sumBean = new CrossTableBean(); - sumBean.setQueryDate("合计"); - sumBean.setColummTitle(title); - sumBean.setColumnNum(columnNum); - sumBean.setValueInteger(totalCount); - ctBeanList.add(sumBean); - } - Integer totalAmount = totalMap.get("清洗总数"); - Integer unQualitotalAmount = totalMap.get("不合格数"); - String rate = "0.0000%"; - if(totalAmount > 0 && unQualitotalAmount > 0){ - rate = decimalFormat.format(100.00d*unQualitotalAmount/totalAmount) + "%"; - } - CrossTableBean ctBean9 = getCrossTableBean("不合格率",titleColumnNumMap,"合计",0,rate); - if(ctBean9 != null){ - ctBeanList.add(ctBean9); - } - return ctBeanList; + public List getwashWorkloadList(String startTime, String endTime, String querySupplyRoom, String queryDateType) { + return washWorkloadReprotHelper.getWashWorkloadList(startTime, endTime, querySupplyRoom, queryDateType); } - // 获取汇总的map - private Map getSumMap(List ctBeanList){ - Map sumMap = new HashMap(); - if(CollectionUtils.isNotEmpty(ctBeanList)){ - for(CrossTableBean ctBean : ctBeanList){ - String title = ctBean.getColummTitle(); - if(StringUtils.isNotBlank(title)){ - if(sumMap.containsKey(title)){ - Integer curAmount = sumMap.get(title); - Integer amount = (ctBean.getValueInteger() == null ? 0 : ctBean.getValueInteger()) + curAmount; - if(amount == null){ - sumMap.put(title, 0); - }else{ - sumMap.put(title, amount); - } - }else{ - Integer amount = ctBean.getValueInteger() == null ? 0 : ctBean.getValueInteger(); - if(amount == null){ - sumMap.put(title, 0); - }else{ - sumMap.put(title, amount); - } - } - } - } - } - return sumMap; - } - private String getDepartGroupSql(String departs,String sartDate,String endDate,String querySupplyRoom,String dateType){ - String sql = ""; - String timeSql = null; - if("month".equals(dateType)){ - timeSql = dateQueryAdapter.dateToVarchar2("wr.startDate"); - }else{ - timeSql = dateQueryAdapter.dateToVarchar1("wr.startDate"); - } - sql = " select c.wrDate,c.orgUnitName,sum(c.mAmount) amount from " - + " (((select " + timeSql + " wrDate,ci.orgUnitName,sum(ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) mAmount " - + " from WashAndDisinfectRecord wr,ClassifyBasket_WashRecord cw,ClassifyBasket cb," - + " ClassifiedItem ci where wr.id=cw.WashAndDisinfectRecord_ID and cw.ClassifyBasket_ID=cb.id " - + " and cb.id=ci.classifybasket_id " - + " and ci.itemType='" + ClassifiedItem.TYPE_MATERIAL + "' " - + " and ci.orgUnitName in ('" + departs + "')" - + " and ( wr.startDate between "+dateQueryAdapter.dateConverAdapter2(sartDate,"yyyy-mm-dd HH24:MI:SS") - + " and "+dateQueryAdapter.dateConverAdapter2(endDate,"yyyy-mm-dd HH24:MI:SS") + ")" - + " and wr.orgUnitCoding = '"+querySupplyRoom+"'" - + " group by " + timeSql + " ,ci.orgUnitName)) " - + " union all " - + " (select " + timeSql + " wrDate,ci.orgUnitName, sum((ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end)*tdc.amount) tAmount " - + " from WashAndDisinfectRecord wr,ClassifyBasket_WashRecord cw,ClassifyBasket cb," - + " ClassifiedItem ci ,(select td.id tdID, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td " - + " where mi.tousse_id=td.id and td.forDisplay=1 group by td.id) tdc " - + " where wr.id=cw.WashAndDisinfectRecord_ID and cw.ClassifyBasket_ID=cb.id " - + " and cb.id=ci.classifybasket_id and ci.toussedefinition_id=tdc.tdID " - + " and ci.itemType != '" + ClassifiedItem.TYPE_MATERIAL + "' " - + " and ci.orgUnitName in ('" + departs + "')" - + " and ( wr.startDate between "+dateQueryAdapter.dateConverAdapter2(sartDate,"yyyy-mm-dd HH24:MI:SS") - + " and "+dateQueryAdapter.dateConverAdapter2(endDate,"yyyy-mm-dd HH24:MI:SS") + ")" - + " and wr.orgUnitCoding = '"+querySupplyRoom+"'" - + " group by " + timeSql + ",ci.orgUnitName) " - + " ) c " - + " group by c.wrDate,c.orgUnitName "; - return sql; - } - @Override public List getGodownEntryDetailStatistic( String title, String author, String orgUnit, String sheetId, @@ -8762,464 +7742,9 @@ // 质量监测报表 @Override public List getQualityMonitoringDataSource(String instrumentSetTypes, String formName, String responsibilityPart, String recordRinseInformation, String scope, Map requestParameters, boolean filterAnswer, boolean isShowTousseMaerialSumAmount) { - List list = new ArrayList(); - String startDate = StrutsParamUtils.getPraramValue("startDate", ""); - String endDate = StrutsParamUtils.getPraramValue("endDate", ""); - - String querySupplyRoom = StrutsParamUtils.getPraramValue("querySupplyRoom", ""); - if(querySupplyRoom.contains("全部")){ - querySupplyRoom = ""; - } - String materialName = StrutsParamUtils.getPraramValue("materialName", ""); - String tousseName = StrutsParamUtils.getPraramValue("tousseName", ""); - String fdioValueSql = null;//查询fdio.Value时加上序号,解决报表排序问题 - String answerWhereSql = null; - if(dbConnection.isOracle()){ - if(filterAnswer){ - answerWhereSql = " fiItem.answer is not null and "; - }else{ - answerWhereSql = ""; - } - fdioValueSql = ",case when fdio.orderNumber<10 then '0' || fdio.orderNumber || 'P+' || fdio.value else fdio.orderNumber || 'P+' || fdio.value end value"; - }else{ - if(filterAnswer){ - answerWhereSql = " 1=1 " + SqlUtils.getIsNotNullSql(dbConnection, "fiItem.answer") + " and "; - }else{ - answerWhereSql = ""; - } - fdioValueSql = ",case when fdio.orderNumber<10 then '0' + CONVERT(varchar(10), fdio.orderNumber) + 'P+' + fdio.value else CONVERT(varchar(10), fdio.orderNumber) + 'P+' + fdio.value end value"; - } - String extraQueryForResponsibilityPart = null; - String extraJoinForResponsibilityPart = null; - Map> washAmountMap = null; - if(QualityMonitoringDefinition.QUALITYMONITORING_MODULE_NAME_RECYCLING.equals(responsibilityPart)){ - String recyclingTime = dateQueryAdapter.dateToVarchar1("rr.recyclingTime"); - extraQueryForResponsibilityPart = ",rr.depart,ur.operationRoom,ur.circuitNurse, rr.recyclingUser,"+ recyclingTime +" recyclingTime "; - extraJoinForResponsibilityPart = " left join TousseInstance ti on ti.id=qmi.scanTousseInstance_id " - + " left join RecyclingRecord rr on rr.id=ti.recyclingRecordId " - + " left join UseRecord ur on ur.id=ti.useRecord_id "; - }else if(QualityMonitoringDefinition.QUALITYMONITORING_MODULE_NAME_WASH.equals(responsibilityPart)){ - String wdrStartDate = dateQueryAdapter.dateToVarchar1("wdr.startDate"); - extraQueryForResponsibilityPart = ",wdr.disinfectIdentification,"+ wdrStartDate +" washStartDate,case when qmi.responsiblePerson is null or qmi.responsiblePerson='' then wdr.washPersonInCharge else qmi.responsiblePerson end washPersonInCharge "; - extraJoinForResponsibilityPart = " left join WashAndDisinfectRecord wdr on wdr.id=qmi.scanWashRecord_id "; - if(Constants.STR_YES.equals(recordRinseInformation)){ - ReportQueryParams params = new ReportQueryParams(); - int dataSoureOfMaterialsCountOfToussesInReports = CssdUtils.getSystemSetConfigByNameInt("dataSoureOfMaterialsCountOfToussesInReports", 3); - String sql = getWashWorkloadSql(params, startDate + " 00:00:00", endDate + " 23:59:59", querySupplyRoom, dataSoureOfMaterialsCountOfToussesInReports, true, "yyyy-mm-dd HH24:MI:SS",null); - washAmountMap = getWashWorkloadAmountMap(sql, true); - } - }else if(QualityMonitoringDefinition.QUALITYMONITORING_MODULE_NAME_STERILIZATION.equals(responsibilityPart)){ - String wdrStartDate = dateQueryAdapter.dateToVarchar1("sr.startDate"); - extraQueryForResponsibilityPart = ",s.name,sr.frequency,sr.sterilizationUser,"+ wdrStartDate +" sterilizationStartDate,sr.amount steAmount "; - extraJoinForResponsibilityPart = " left join SterilizationRecord sr on sr.id=qmi.sterilizationRecordId " - + " left join Sterilizer s on s.id=sr.sterilizer_id "; - } - String monitoringType = StrutsParamUtils.getPraramValue("monitoringType", ""); - String extraQuery = ""; - String extraJoin = ""; - boolean showSterilizationInfo = false;//是否查询灭菌炉和炉次 - String qmdOrderNumberBySql = ""; - String tempOrderBySql = ""; - boolean materialAndTousse = false; - String materialAmountSql = ""; - if(FORMNAMEOFADJ.equals(formName)){//从关联的灭菌记录获取 - showSterilizationInfo = true; - requestParameters.put("dataType", FORMNAMEOFADJ); - extraQuery = ",sr.frequency,s.name sterilizerName"; - extraJoin = " join SterilizationRecord sr on sr.id=qmi.sterilizationRecordId join Sterilizer s on s.id=sr.sterilizer_id"; - }else if(QualityMonitoringDefinition.SCOPE_STERILIZATIONRECORD.equals(scope) && "定期监测".equals(monitoringType)){//因为没有关联灭菌记录，所以从QualityMonitoringInstance获取 - showSterilizationInfo = true; - extraQuery = ",qmi.sterilizerDate,qmi.sterilizerFrequency frequency,qmi.sterilizerName"; - requestParameters.put("jasperreportName", "routineMonitoringSteScopeReport.jasper"); - requestParameters.put("dataType", "监测范围为灭菌炉记录的定期监测数据"); - }else if(QualityMonitoringDefinition.SCOPE_MATERIAL_AND_TOUSSE.equals(scope) && "质量监测".equals(monitoringType)){ - requestParameters.put("jasperreportName", "routineMonitoringWashScopeReport.jasper"); - qmdOrderNumberBySql = " order by qmi.id desc,fiItem.id desc,qmd.orderNumber asc"; - tempOrderBySql = "order by temp.id desc,temp.orderNumber,temp.qmdOrderNumber asc"; - requestParameters.put("dataType", "监测范围为材料和器械包的质量监测数据"); - if(isShowTousseMaerialSumAmount){ - materialAmountSql = ",(select sum(count) from MaterialInstance join TousseDefinition td on td.id=tousse_id where td.id=qmd.tousseDefinitionId) materialAmount "; - } - materialAndTousse = true; - } - if (StringUtils.isNotBlank(startDate) && StringUtils.isNotBlank(endDate) - && StringUtils.isNotBlank(formName)) { - String joinTousseSql = ""; - String onlyQueryTousseSql = ""; - String tousseSql = ""; - if(StringUtils.isNotBlank(instrumentSetTypes)){ - Set instrumentSetTypeSet = SqlUtils.splitStringToSet(instrumentSetTypes, ","); - if(CollectionUtils.isNotEmpty(instrumentSetTypeSet)){ - tousseSql = reportSqlUtil.getInstrumentSetTypeSql(instrumentSetTypeSet); - joinTousseSql = " join TousseDefinition td on td.id=qmd.tousseDefinitionId "; - onlyQueryTousseSql = " and 1=2 "; - } - } - JSONObject amoutSumJson = new JSONObject(); - String whereSql = buildWhereSQL(startDate, endDate, querySupplyRoom, - formName, monitoringType); - String tousseNameSql = buildTousseNameMaterialNameWhereSql(materialName, tousseName); - String dataSql = dateQueryAdapter.dateToVarchar1("qmi.dateTime"); - String sql = " select * from (select " + dataSql + " dateStr," - + " qmd.tousseName toussName,qmd.material material,qmd.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg," - + " fdi.name fdiName, fdi.orderNumber orderNum"+ fdioValueSql +",fiov.id,fi.createUserName,fdio.orderNumber,qmd.orderNumber qmdOrderNumber,qmd.id qmdid " - + materialAmountSql - + extraQuery - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " - + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" - + " join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " - + extraJoin - + joinTousseSql - + " join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " - + " and fiov.definitionItemOption_id=fdio.id " - + " where fdi.isReportDisplay='是' " - + tousseSql - + "and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql; - sql+= " union all select distinct " + dataSql + " dateStr," - + " qmd.tousseName toussName,qmd.material material,qmd.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg," - + " '' fdiName,null orderNum,'' value,null id,fi.createUserName,fdio.orderNumber ,qmd.orderNumber qmdOrderNumber,qmd.id qmdid " - + materialAmountSql - + extraQuery - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " - + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" - + " left join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " - + extraJoin - + joinTousseSql - + " left join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " - + " and fiov.definitionItemOption_id=fdio.id " - + " where fdi.isReportDisplay='是' " - + tousseSql - + " and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql - + " and fiov.id is null and qmi.id not in(" - + " select qmi.id qmiId" - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " - + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" - + " join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " - + extraJoin - + joinTousseSql - + " join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " - + " and fiov.definitionItemOption_id=fdio.id " - + " where fdi.isReportDisplay='是' " - + tousseSql - + "and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql - + "))temp " + tempOrderBySql; - addMonitoringItemBeanOptionRadioAndCHeck(list,sql,showSterilizationInfo, materialAndTousse); - // 没有录入材料和包，有配了多选的细则的项 - sql = " select " + dataSql + " dateStr," - + " qmi.id qmiId,qmi.positionMsg positionMsg," - + " fdi.name fdiName, fdi.orderNumber orderNum"+ fdioValueSql +",fiov.id,fi.createUserName " - + extraQuery - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " - + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" - + " join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id " - + " join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " - + extraJoin - + " and fiov.definitionItemOption_id=fdio.id " - + " where fdi.isReportDisplay='是' " - + onlyQueryTousseSql - + " and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql - + " and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmd.id is null)"; - sql += " union all select distinct " + dataSql + " dateStr," - + " qmi.id qmiId,qmi.positionMsg positionMsg," - + " '' fdiName, null orderNum,'' value,null id,fi.createUserName " - + extraQuery - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " - + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" - + " join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id " - + " left join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " - + extraJoin - + " and fiov.definitionItemOption_id=fdio.id " - + " where fdi.isReportDisplay='是' " - + onlyQueryTousseSql - + " and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql - + " and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmd.id is null)" - + " and fiov.id is null and qmi.id not in (" - + " select qmi.id qmiId " - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " - + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" - + " join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id " - + " join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " - + extraJoin - + " and fiov.definitionItemOption_id=fdio.id " - + " where fdi.isReportDisplay='是'" - + onlyQueryTousseSql - + " and (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql - + " and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmd.id is null)" - + ")"; - addMonitoringItemBeanOptionRadioAndCHeckWithoutMaterial(list,sql,showSterilizationInfo); - sql = " select " + dataSql + " dateStr," - + " qmd.tousseName toussName,qmd.material material,qmd.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg," - + " fdi.name fdiName, fdi.orderNumber orderNum,fiItem.answer amswer,fi.createUserName,qmd.orderNumber qmdOrderNumber,qmd.id qmdid " - + materialAmountSql - + extraQuery - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " - + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id " - + " join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " - + extraJoin - + joinTousseSql - + " where "+ answerWhereSql +" (fdi.type != '" + FormDefinitionItem.TYPE_RADIO + "' and fdi.isReportDisplay='是' " - + tousseSql - + " and fdi.type!='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql + qmdOrderNumberBySql; - addMonitoringItemBeanOptionSingleResult(list,sql,showSterilizationInfo, materialAndTousse); - // 没有录入材料和包，单一的结果 - sql = " select " + dataSql + " dateStr," - + " qmi.id qmiId,qmi.positionMsg positionMsg," - + " fdi.name fdiName, fdi.orderNumber orderNum,fiItem.answer amswer,fi.createUserName " - + extraQuery - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " - + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id " - + extraJoin - + " where "+ answerWhereSql +" (fdi.type != '" + FormDefinitionItem.TYPE_RADIO + "' and fdi.type!='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql - + " and fdi.isReportDisplay='是' " - + onlyQueryTousseSql - + "and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmd.id is null) "; - addMonitoringItemBeanOptionSingleResultWithoutMaterial(list,sql,showSterilizationInfo,monitoringType); - - if(FormDefinition.FOMRTYPE_QUALITYMONITORING.equals(monitoringType)){ - sql = " select " + dataSql + " dateStr," - + " qmd.tousseName toussName,qmd.material material,qmd.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg,fi.createUserName " - + materialAmountSql - + extraQuery - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id " - + " join FormDefinition fd on fi.formDefinition_id=fd.id join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " - + " left join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id" - + extraJoin - + joinTousseSql - + " where qmi.id not in (select formInstance_id from FormInstanceItem) " - + " and qmi.type = '" + FormDefinition.FOMRTYPE_QUALITYMONITORING + "' " - + tousseSql - + " and " + whereSql + tousseNameSql + qmdOrderNumberBySql; - addMonitoringItemBeanQualityMonitoring(list,sql,showSterilizationInfo, materialAndTousse); - } - //是否无菌包质量抽查登记表,是则隐藏监测结果列 - Map isQualifiedAmount = new HashMap(); - Map isQualifiedMaterialAmount = new HashMap(); - if (!FORMNAMEOFADJ.equals(formName) && FormDefinition.FOMRTYPE_PERIODICMONITORING.equals(monitoringType)) { - sql = " select " + dataSql + " dateStr," - + " qmd.tousseName toussName,qmd.material material,qmd.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg,qmi.result,fi.createUserName " - + extraQuery - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " - + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " - + extraJoin - + joinTousseSql - + " where qmi.type = '" + FormDefinition.FOMRTYPE_PERIODICMONITORING + "'" + " and " + whereSql + tousseNameSql + tousseSql; - addMonitoringItemBeanRountineMonitoring(list,sql,showSterilizationInfo,isQualifiedAmount,isQualifiedMaterialAmount); - } - if(StringUtils.isNotBlank(extraQueryForResponsibilityPart) && "质量监测".equals(monitoringType)){ - String sql2 = " select " + dataSql + " dateStr,qmi.id qmiId," - + "qmdf.recordRecycleInformation,qmdf.recordRinseInformation,qmdf.recordSterilizatioInformation " - + extraQuery + extraQueryForResponsibilityPart - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " - + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id " - + extraJoin + extraJoinForResponsibilityPart - + " where 1=1 and " - + whereSql; - addMonitoringItemBeanOption(list,sql2,showSterilizationInfo,washAmountMap,amoutSumJson,materialAndTousse); - } - // 条数 - Integer qmCount = 0; - // 材料或者包数量和 - Integer qmTousseAndMaterialAmount = 0; - if(materialAndTousse){ - sql = " select sum(t.bAmount) total from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " - + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join ( select qmi.id qid,sum(case when qmd.material is null or qmd.material='' then 0 else qmd.amount end) bAmount from " - + "QualityMonitoringInstance qmi join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " - + joinTousseSql - + " where 1=1 " - + tousseNameSql - + tousseSql - + " group by qmi.id ) t on t.qid=qmi.id " - + " where " + whereSql; - Set readIds = new HashSet(); - for (MonitoringItemBean bean : list) { - Long id = bean.getQualityMonitoringGoodsId(); - if(!readIds.contains(id) && bean.getAmount() != null){ - readIds.add(id); - qmCount += bean.getAmount(); - } - } - }else{ - sql = " select count(0) count,sum(t.aAmount) total from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " - + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join ( select qmi.id qid,sum(qmd.amount) aAmount " - + "from QualityMonitoringInstance qmi join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " - + joinTousseSql - + "where 1=1 " - + tousseNameSql - + tousseSql - + " group by qmi.id ) t on t.qid=qmi.id " - + " where " + whereSql; - } - ResultSet rs = objectDao.executeSql(sql); - try { - while (rs.next()) { - if(!materialAndTousse){ - qmCount = rs.getInt("count"); - } - qmTousseAndMaterialAmount = rs.getInt("total"); - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - sql = "select distinct fdi.name from FormDefinition fd join FormDefinitionItem fdi on fdi.formDefinition_id=fd.id where fdi.type='数字'"; - ResultSet rs1 = null; - Set numNames = new HashSet(); - try { - rs1 = objectDao.executeSql(sql); - while (rs1.next()) { - numNames.add(rs1.getString("name")); - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(rs1); - } - // 质量检测加数量汇总 - if ("质量监测".equals(monitoringType)) { - Integer totalAmount = 0; - Integer totalItem = 0; - Integer orderNumber = 1; - String option = ""; - String questionName = ""; - totalAmount = qmTousseAndMaterialAmount; - totalItem = qmCount; - if (list != null && list.size() > 0) { - orderNumber = list.get(0).getOrderNumber(); - option = list.get(0).getOption(); - questionName = list.get(0).getQuestionName(); - } - if (totalAmount > 0 || list.size() > 0) { - List> listMap = getBeanMap(list,numNames); - if( listMap != null && listMap.size() > 0){ - Map beanStatisticsMap = listMap.get(0); - if(beanStatisticsMap != null){ - for (Map.Entry entry : beanStatisticsMap.entrySet()){ - String key = entry.getKey(); - String[] arr = key.split("_"); - if(arr.length >=3){ - int _orderNumber = Integer.valueOf(arr[0]); - String _option = arr[1]; - String _questionName = arr[2]; - int anwserInt = entry.getValue(); - if(anwserInt == 0){ - continue; - } - MonitoringItemBean bean1 = new MonitoringItemBean(); - bean1.setGoodsName("合计"); - bean1.setDateTime("合计"); - bean1.setOrderNumber(_orderNumber); - bean1.setOption(_option); - bean1.setQuestionName(_questionName); - bean1.setAmount(totalItem); - bean1.setAnswer(anwserInt+""); - bean1.setPositionMsg(""); - list.add(bean1); - } - } - } - } - Integer tousseAmoutSum = amoutSumJson.optInt("tousseAmoutSum"); - Integer forgonAmoutSum = amoutSumJson.optInt("forgonAmoutSum"); - if(tousseAmoutSum != null && tousseAmoutSum > 0){ - MonitoringItemBean bean1 = new MonitoringItemBean(); - bean1.setGoodsName("合计"); - bean1.setDateTime("合计"); - bean1.setOrderNumber(15); - bean1.setOption("当天清洗件数"); - bean1.setQuestionName("清洗情况"); - bean1.setAmount(totalItem); - bean1.setPositionMsg(""); - bean1.setAnswer(tousseAmoutSum + ""); - list.add(bean1); - } - if(forgonAmoutSum != null && forgonAmoutSum > 0){ - MonitoringItemBean bean2 = new MonitoringItemBean(); - bean2.setGoodsName("合计"); - bean2.setDateTime("合计"); - bean2.setOrderNumber(15); - bean2.setOption("当天清洗外来器械件数"); - bean2.setQuestionName("清洗情况"); - bean2.setAmount(totalItem); - bean2.setPositionMsg(""); - bean2.setAnswer(forgonAmoutSum + ""); - list.add(bean2); - } - } - }else if("定期监测".equals(monitoringType)){ - Integer totalAmount = 0; - Integer totalItem = 0; - Integer orderNumber = 1; - String option = ""; - String questionName = ""; - totalAmount = qmTousseAndMaterialAmount; - totalItem = qmCount; - if (list != null && list.size() > 0) { - orderNumber = list.get(0).getOrderNumber(); - option = list.get(0).getOption(); - questionName = list.get(0).getQuestionName(); - } - // 对每个bean 做统计 - List> listMap = getBeanMapByResult(list,numNames); - if( listMap != null && listMap.size() > 0){ - Map beanStatisticsMap = listMap.get(0); - if(beanStatisticsMap != null){ - for (Map.Entry entry : beanStatisticsMap.entrySet()){ - String key = entry.getKey(); - String[] arr = key.split("_"); - if(arr.length >=3){ - int _orderNumber = Integer.valueOf(arr[0]); - String _option = arr[1]; - String _questionName = arr[2]; - int anwserInt = entry.getValue(); - if(anwserInt == 0){ - continue; - } - MonitoringItemBean bean1 = new MonitoringItemBean(); - bean1.setGoodsName("合计"); - bean1.setDateTime("合计"); - bean1.setOrderNumber(_orderNumber); - bean1.setOption(_option); - bean1.setQuestionName(_questionName); - bean1.setAmount(totalAmount); - bean1.setAnswer(anwserInt+""); - bean1.setPositionMsg(""); - list.add(bean1); - } - } - } - if(totalAmount != null && totalAmount > 0){ - addQualifiedAmountInfo(isQualifiedAmount,isQualifiedMaterialAmount, list, orderNumber, option, questionName); - } - } - } - } - return list; + return qualityMonitoringHelper.getQualityMonitoringDataSource(instrumentSetTypes, formName, responsibilityPart, recordRinseInformation, scope, requestParameters, filterAnswer, isShowTousseMaerialSumAmount); } - /** - * 添加合格与不合格统计信息 - * @param isQualifiedAmount 合格与不合格的数据 - * @param list List - * @param orderNumber 排序号 - * @param option 监测参数 - * @param questionName 监测细则名称 - */ - private void addQualifiedAmountInfo(Map isQualifiedAmount,Map isQualifiedMaterialAmount, List list, Integer orderNumber, - String option, String questionName){ - for(Entry entry : isQualifiedAmount.entrySet()){ - String key = entry.getKey(); - MonitoringItemBean mib = new MonitoringItemBean(); - mib.setGoodsName(""); - mib.setDateTime(key); - mib.setOrderNumber(orderNumber); - mib.setOption(option); - mib.setQuestionName(questionName); - mib.setAmount(entry.getValue()); - mib.setRegisterMaterialAmount(isQualifiedMaterialAmount.get(key)); - mib.setPositionMsg(""); - list.add(mib); - } - } + // 获取质量监测统计项月报统计 @Override public List getQualityMonitoringInspectMonthParamet(String queryYear,String orgUnitCoding,String monitoringType,String inspectItem,String inspectDetail,String type,String responsibilityType){ @@ -9965,713 +8490,17 @@ } } } - private void newMonitoringItem(List list, - QualityMonitoringInstance qmInstance, String dateStr, - Integer orderNum,String questionName,String option,String answer) { - MonitoringItemBean bean = new MonitoringItemBean(); - bean.setAmount(qmInstance.getAmount()); - bean.setDateTime(dateStr); - bean.setMaterialName(qmInstance.getMaterial()); - bean.setTousseName(qmInstance.getTousseName()); - String goodsName = qmInstance.getTousseName(); - if (StringUtils.isBlank(goodsName)) { - goodsName = qmInstance.getMaterial(); - } - bean.setGoodsName(goodsName + "%&" + qmInstance.getId()); - bean.setQualityMonitoringInstanceId(qmInstance.getId()); - bean.setPositionMsg(qmInstance.getPositionMsg()); - bean.setOrderNumber(orderNum); - bean.setOption(option);// 表头第三列 - bean.setQuestionName(questionName);// 表头第二列 - bean.setAnswer(answer); - list.add(bean); - } - private void newMonitoringItem(List list, - Integer registerMaterialAmount, Integer amount,String tousseName,String materialName, - Long qmiId,String positoinMsg,String dateStr, - Integer orderNum,String questionName,String option,String answer,Integer frequency, String sterilizerName, boolean isCustomADJAndIsFormNameIsADJ, String createUserName, Long qmdId, Integer materialAmount) { - MonitoringItemBean bean = new MonitoringItemBean(); - bean.setAmount(amount); - bean.setRegisterMaterialAmount(registerMaterialAmount); - bean.setDateTime(dateStr); - bean.setMaterialName(materialName); - bean.setTousseName(tousseName); - String goodsName = tousseName; - if (StringUtils.isBlank(goodsName)) { - goodsName = materialName; - } - bean.setGoodsName(goodsName + "%&" + qmiId); - bean.setQualityMonitoringInstanceId(qmiId); - bean.setQualityMonitoringGoodsId(qmdId); - bean.setPositionMsg(positoinMsg); - bean.setOrderNumber(orderNum); - bean.setOption(option);// 表头第三列 - bean.setQuestionName(questionName);// 表头第二列 - bean.setAnswer(answer); - if(isCustomADJAndIsFormNameIsADJ){ - bean.setFrequency(frequency); - bean.setSterilizerName(sterilizerName); - } - bean.setCreateUserName(createUserName); - bean.setMaterialAmount(materialAmount); - list.add(bean); - } - /** - * 添加质量监测记录的bean 单选和多选的结果 - * @param list - * @param sql - */ - private void addMonitoringItemBeanOptionRadioAndCHeck(List list,String sql, boolean isCustomADJAndIsFormNameIsADJ, boolean washScope){ - if(StringUtils.isBlank(sql)){ - return ; - } - ResultSet rs = objectDao.executeSql(sql); - try { - Map qmiIdToLastTousseNameMap = new HashMap(); - Map qmiIdToLastTousseAmountMap = new HashMap(); - MonitoringItemBean lastMonitoringItemBean = null; - Set readedIds = new HashSet(); - while (rs.next()) { - String dataStr = null; - String tousseName = rs.getString("toussName"); - String materialName = rs.getString("material"); - Long qmiId = rs.getLong("qmiId"); - if(!readedIds.contains(qmiId)){ - readedIds.add(qmiId); - if(lastMonitoringItemBean != null){ - newMonitoringItem(list, lastMonitoringItemBean.getRegisterMaterialAmount(), - lastMonitoringItemBean.getAmount(), - lastMonitoringItemBean.getTousseName(), - lastMonitoringItemBean.getMaterialName(), - lastMonitoringItemBean.getQualityMonitoringInstanceId(), - lastMonitoringItemBean.getPositionMsg(), - lastMonitoringItemBean.getDateTime(), - lastMonitoringItemBean.getOrderNumber(), - lastMonitoringItemBean.getQuestionName(), - lastMonitoringItemBean.getOption(), - lastMonitoringItemBean.getAnswer(), - lastMonitoringItemBean.getFrequency(), - lastMonitoringItemBean.getSterilizerName(),isCustomADJAndIsFormNameIsADJ, - lastMonitoringItemBean.getCreateUserName(), - lastMonitoringItemBean.getQualityMonitoringGoodsId(), - lastMonitoringItemBean.getMaterialAmount()); - } - lastMonitoringItemBean = null; - } - int amount = rs.getInt("amount"); - String positionMsg = rs.getString("positionMsg"); - String questionName = rs.getString("fdiName"); - int orderNum = rs.getInt("orderNum"); - String optionStr = rs.getString("value"); - Long fiovId = rs.getLong("id"); - String createUserName = rs.getString("createUserName"); - String answer = (fiovId > 0) ? "√" : ""; - Integer frequency = null; - String sterilizerName = null; - if(isCustomADJAndIsFormNameIsADJ){ - frequency = ConvertNumber.getNumberIntValue(rs.getObject("frequency"), null); - sterilizerName = rs.getString("sterilizerName"); - if(SqlUtils.isExistColumn(rs, "sterilizerDate")){//监测范围为灭菌炉记录，日期要使用灭菌日期 - dataStr = rs.getString("sterilizerDate"); - } - } - if(dataStr == null){ - dataStr = rs.getString("dateStr"); - } - Long qmdid = rs.getLong("qmdid"); - Integer materialAmount = 0; - if(SqlUtils.isExistColumn(rs, "materialAmount")){ - materialAmount = rs.getInt("materialAmount"); - } - String tousseAmountKey = null; - if(washScope){ - int qmdOrderNumber = rs.getInt("qmdOrderNumber"); - if(StringUtils.isNotBlank(tousseName)){ - qmiIdToLastTousseNameMap.put(qmiId + "_" + qmdOrderNumber, tousseName); - qmiIdToLastTousseAmountMap.put(qmiId + "_" + qmdOrderNumber, amount); - } - if(StringUtils.isBlank(materialName)){ - if(lastMonitoringItemBean != null){ - newMonitoringItem(list, lastMonitoringItemBean.getRegisterMaterialAmount(), lastMonitoringItemBean.getAmount(), - lastMonitoringItemBean.getTousseName(), - lastMonitoringItemBean.getMaterialName(), - lastMonitoringItemBean.getQualityMonitoringInstanceId(), - lastMonitoringItemBean.getPositionMsg(), - lastMonitoringItemBean.getDateTime(), - lastMonitoringItemBean.getOrderNumber(), - lastMonitoringItemBean.getQuestionName(), - lastMonitoringItemBean.getOption(), - lastMonitoringItemBean.getAnswer(), - lastMonitoringItemBean.getFrequency(), - lastMonitoringItemBean.getSterilizerName(),isCustomADJAndIsFormNameIsADJ, - lastMonitoringItemBean.getCreateUserName(), - lastMonitoringItemBean.getQualityMonitoringGoodsId(), - lastMonitoringItemBean.getMaterialAmount()); - lastMonitoringItemBean = null; - } - lastMonitoringItemBean = new MonitoringItemBean(); - lastMonitoringItemBean.setAnswer(answer); - lastMonitoringItemBean.setDateTime(dataStr); - lastMonitoringItemBean.setSterilizerName(sterilizerName); - lastMonitoringItemBean.setCreateUserName(createUserName); - lastMonitoringItemBean.setFrequency(frequency); - lastMonitoringItemBean.setMaterialName(materialName); - if(StringUtils.isNotBlank(tousseName) && StringUtils.isNotBlank(materialName)){ - lastMonitoringItemBean.setRegisterMaterialAmount(amount); - }else if(StringUtils.isNotBlank(tousseName)){ - lastMonitoringItemBean.setAmount(amount); - }else if(StringUtils.isNotBlank(materialName)){ - lastMonitoringItemBean.setRegisterMaterialAmount(amount); - } - lastMonitoringItemBean.setTousseName(tousseName); - lastMonitoringItemBean.setQualityMonitoringInstanceId(qmiId); - lastMonitoringItemBean.setPositionMsg(positionMsg); - lastMonitoringItemBean.setOrderNumber(orderNum); - lastMonitoringItemBean.setQuestionName(questionName); - lastMonitoringItemBean.setOption(optionStr); - lastMonitoringItemBean.setQualityMonitoringGoodsId(qmdid); - lastMonitoringItemBean.setMaterialAmount(materialAmount); - continue; - } - lastMonitoringItemBean = null; - for (int i = 1; i < qmdOrderNumber; i++) { - if(qmiIdToLastTousseNameMap.containsKey(qmiId + "_" + (qmdOrderNumber - i))){ - tousseName = qmiIdToLastTousseNameMap.get(qmiId + "_" + (qmdOrderNumber - i)); - tousseAmountKey = qmiId + "_" + (qmdOrderNumber - i); - break; - } - } - } - Integer registerMaterialAmount = 0; - if(StringUtils.isNotBlank(tousseName) && StringUtils.isNotBlank(materialName)){ - registerMaterialAmount = amount; - if(tousseAmountKey != null){ - amount = qmiIdToLastTousseAmountMap.get(tousseAmountKey); - } - }else if(StringUtils.isNotBlank(materialName)){ - registerMaterialAmount = amount; - amount = 0; - } - newMonitoringItem(list, registerMaterialAmount,amount,tousseName,materialName,qmiId,positionMsg, - dataStr, orderNum, questionName,optionStr, answer,frequency,sterilizerName,isCustomADJAndIsFormNameIsADJ,createUserName, qmdid, materialAmount); - } - if(lastMonitoringItemBean != null){ - newMonitoringItem(list, lastMonitoringItemBean.getRegisterMaterialAmount(),lastMonitoringItemBean.getAmount(), - lastMonitoringItemBean.getTousseName(), - lastMonitoringItemBean.getMaterialName(), - lastMonitoringItemBean.getQualityMonitoringInstanceId(), - lastMonitoringItemBean.getPositionMsg(), - lastMonitoringItemBean.getDateTime(), - lastMonitoringItemBean.getOrderNumber(), - lastMonitoringItemBean.getQuestionName(), - lastMonitoringItemBean.getOption(), - lastMonitoringItemBean.getAnswer(), - lastMonitoringItemBean.getFrequency(), - lastMonitoringItemBean.getSterilizerName(),isCustomADJAndIsFormNameIsADJ, - lastMonitoringItemBean.getCreateUserName(), - lastMonitoringItemBean.getQualityMonitoringGoodsId(), - lastMonitoringItemBean.getMaterialAmount()); - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - } - /** - * 质量监测添加动态列 - * @param list - * @param sql - * @param isCustomADJAndIsFormNameIsADJ - * @param washAmountMap 清洗数量map - * @param amoutSumJson 当天清洗件数和当天外来器清洗件数json 格式{tousseAmoutSum":2,"forgonAmoutSum":3}; - */ - private void addMonitoringItemBeanOption(List list,String sql, boolean isCustomADJAndIsFormNameIsADJ, Map> washAmountMap,JSONObject amoutSumJson, boolean materialAndTousse){ - if(StringUtils.isBlank(sql)){ - return ; - } - ResultSet rs = null; - try { - Set readedSet = new HashSet(); - Integer tousseAmoutSum = 0; - Integer forgonAmoutSum = 0; - rs = objectDao.executeSql(sql); - Map monitoringItemBeanMap = null; - List addList = new ArrayList();//本次要新增的bean - while (rs.next()) { - Long qmiId = rs.getLong("qmiId"); - for (MonitoringItemBean monitoringItemBean : list) { - if(monitoringItemBean == null){ - continue; - } - if(MathTools.equals(monitoringItemBean.getQualityMonitoringInstanceId(), qmiId)){ - String dataStr = rs.getString("dateStr"); - String recordRecycleInformation = rs.getString("recordRecycleInformation"); - String recordRinseInformation = rs.getString("recordRinseInformation"); - String recordSterilizatioInformation = rs.getString("recordSterilizatioInformation"); - if(SqlUtils.isExistColumn(rs, "sterilizationStartDate") && Constants.STR_YES.equals(recordSterilizatioInformation)){ - String name = rs.getString("name"); - if(StringUtils.isNotBlank(name)){ - Integer steFrequency = ConvertNumber.getNumberIntValue(rs.getObject("frequency"), null); - name = name + ",第" + steFrequency + "炉"; - } - if(StringUtils.isBlank(name)){ - name = ""; - } - copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 10, "灭菌情况", "灭菌批号",name, isCustomADJAndIsFormNameIsADJ); - String sterilizationUser = rs.getString("sterilizationUser"); - copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 10,"灭菌情况","灭菌员", sterilizationUser, isCustomADJAndIsFormNameIsADJ); - String startDate = rs.getString("sterilizationStartDate"); - copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 10, "灭菌情况","灭菌时间", startDate, isCustomADJAndIsFormNameIsADJ); - Integer steAmount = rs.getInt("steAmount"); - copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 10, "灭菌情况","当次炉次灭菌总包数", steAmount + "", isCustomADJAndIsFormNameIsADJ); - }else if(SqlUtils.isExistColumn(rs, "washStartDate") && Constants.STR_YES.equals(recordRinseInformation)){ - String startDate = null; - String disinfectIdentification = null; - disinfectIdentification = rs.getString("disinfectIdentification"); - copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "清洗情况","清洗机", disinfectIdentification, isCustomADJAndIsFormNameIsADJ); - startDate = rs.getString("washStartDate"); - copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "清洗情况","清洗开始时间", startDate, isCustomADJAndIsFormNameIsADJ); - Integer forgonAmount = 0; - Integer totalAmout = 0; - if(MapUtils.isNotEmpty(washAmountMap)){ - Map washAmountMapItem = washAmountMap.get(dataStr); - if(MapUtils.isNotEmpty(washAmountMapItem)){ - if(washAmountMapItem.containsKey("外来器械")){ - forgonAmount = washAmountMapItem.get("外来器械"); - } - totalAmout = washAmountMapItem.get("清洗总数"); - totalAmout = totalAmout - forgonAmount; - } - } - String washPersonInCharge = null; - if(SqlUtils.isExistColumn(rs, "washPersonInCharge")){ - washPersonInCharge = rs.getString("washPersonInCharge"); - copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "清洗情况","责任人", washPersonInCharge, isCustomADJAndIsFormNameIsADJ); - } - if(!readedSet.contains(dataStr)){ - readedSet.add(dataStr); - tousseAmoutSum += totalAmout; - forgonAmoutSum += forgonAmount; - copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "清洗情况","当天清洗件数", totalAmout + "", isCustomADJAndIsFormNameIsADJ); - copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "清洗情况","当天清洗外来器械件数", forgonAmount + "", isCustomADJAndIsFormNameIsADJ); - } - }else if(SqlUtils.isExistColumn(rs, "recyclingTime") && Constants.STR_YES.equals(recordRecycleInformation)){ - String depart = rs.getString("depart"); - copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "回收情况","回收科室", depart, isCustomADJAndIsFormNameIsADJ); - String operationRoom = rs.getString("operationRoom"); - copyPartPropertiesMonitoringItem(addList, monitoringItemBean,15, "回收情况","手术间", operationRoom, isCustomADJAndIsFormNameIsADJ); - String circuitNurse = rs.getString("circuitNurse"); - copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "回收情况","巡回护士", circuitNurse, isCustomADJAndIsFormNameIsADJ); - String recyclingUser = rs.getString("recyclingUser"); - copyPartPropertiesMonitoringItem(addList, monitoringItemBean,15, "回收情况","回收员", recyclingUser, isCustomADJAndIsFormNameIsADJ); - String recyclingTime = rs.getString("recyclingTime"); - copyPartPropertiesMonitoringItem(addList, monitoringItemBean, 15, "回收情况","回收时间", recyclingTime, isCustomADJAndIsFormNameIsADJ); - } - } - } - } - if(addList.size() > 0){ - list.addAll(addList); - } - amoutSumJson.put("tousseAmoutSum", tousseAmoutSum); - amoutSumJson.put("forgonAmoutSum", forgonAmoutSum); - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - } - /** - * 复制部分属性 添加新的bean到List - * @param list - * @param monitoringItemBean - * @param orderNum 型号 - * @param questionName 监测细则名称 - * @param option 监测参数 - * @param answer 检测结果 - * @param isCustomADJAndIsFormNameIsADJ - */ - private void copyPartPropertiesMonitoringItem(List list,MonitoringItemBean monitoringItemBean, - Integer orderNum,String questionName,String option,String answer, boolean isCustomADJAndIsFormNameIsADJ) { - MonitoringItemBean bean = new MonitoringItemBean(); - bean.setAmount(monitoringItemBean.getAmount()); - bean.setDateTime(monitoringItemBean.getDateTime()); - bean.setMaterialName(monitoringItemBean.getMaterialName()); - bean.setTousseName(monitoringItemBean.getTousseName()); - bean.setGoodsName(monitoringItemBean.getGoodsName()); - bean.setPositionMsg(monitoringItemBean.getPositionMsg()); - bean.setOrderNumber(orderNum); - bean.setQualityMonitoringInstanceId(monitoringItemBean.getQualityMonitoringInstanceId()); - bean.setOption(option);// 表头第三列 - bean.setQuestionName(questionName);// 表头第二列 - bean.setAnswer(answer); - bean.setQualityMonitoringGoodsId(monitoringItemBean.getQualityMonitoringGoodsId()); - if(isCustomADJAndIsFormNameIsADJ){ - bean.setFrequency(monitoringItemBean.getFrequency()); - bean.setSterilizerName(monitoringItemBean.getSterilizerName()); - } - bean.setCreateUserName(monitoringItemBean.getCreateUserName()); - list.add(bean); - } - /** - * 添加质量监测记录的bean 没有包和材料时，单选和多选的结果 - * @param list - * @param sql - */ - private void addMonitoringItemBeanOptionRadioAndCHeckWithoutMaterial(List list,String sql, boolean addMonitoringItemBeanOptionSingleResult){ - if(StringUtils.isBlank(sql)){ - return ; - } - ResultSet rs = objectDao.executeSql(sql); - try { - while (rs.next()) { - String dataStr = null; - Long qmiId = rs.getLong("qmiId"); - String positionMsg = rs.getString("positionMsg"); - String questionName = rs.getString("fdiName"); - int orderNum = rs.getInt("orderNum"); - String optionStr = rs.getString("value"); - Long fiovId = rs.getLong("id"); - String createUserName = rs.getString("createUserName"); - String answer = (fiovId > 0) ? "√" : ""; - Integer frequency = null; - String sterilizerName = null; - if(addMonitoringItemBeanOptionSingleResult){ - frequency = ConvertNumber.getNumberIntValue(rs.getObject("frequency"), null); - sterilizerName = rs.getString("sterilizerName"); - if(SqlUtils.isExistColumn(rs, "sterilizerDate")){//监测范围为灭菌炉记录，日期要使用灭菌日期 - dataStr = rs.getString("sterilizerDate"); - } - } - if(dataStr == null){ - dataStr = rs.getString("dateStr"); - } - newMonitoringItem(list, 0, 0,"","",qmiId,positionMsg, - dataStr, orderNum, questionName,optionStr, answer, frequency, sterilizerName, addMonitoringItemBeanOptionSingleResult,createUserName, null, 0); - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - } - /** - * 添加质量监测记录的bean 单一的结果 - * @param list - * @param sql - */ - private void addMonitoringItemBeanOptionSingleResult(List list,String sql, boolean addMonitoringItemBeanOptionSingleResult, boolean washScope){ - if(StringUtils.isBlank(sql)){ - return ; - } - ResultSet rs = null; - try { - rs = objectDao.executeSql(sql); - Map qmiIdToLastTousseNameMap = new HashMap(); - Map qmiIdToLastTousseAmountMap = new HashMap(); - MonitoringItemBean lastMonitoringItemBean = null; - Set readedIds = new HashSet(); - while (rs.next()) { - String dataStr = null; - String tousseName = rs.getString(2); - String materialName = rs.getString(3); - Long qmiId = rs.getLong(5); - if(!readedIds.contains(qmiId)){ - readedIds.add(qmiId); - if(lastMonitoringItemBean != null){ - newMonitoringItem(list, lastMonitoringItemBean.getRegisterMaterialAmount(),lastMonitoringItemBean.getAmount(), - lastMonitoringItemBean.getTousseName(), - lastMonitoringItemBean.getMaterialName(), - lastMonitoringItemBean.getQualityMonitoringInstanceId(), - lastMonitoringItemBean.getPositionMsg(), - lastMonitoringItemBean.getDateTime(), - lastMonitoringItemBean.getOrderNumber(), - lastMonitoringItemBean.getQuestionName(), - "-", - lastMonitoringItemBean.getAnswer(), - lastMonitoringItemBean.getFrequency(), - lastMonitoringItemBean.getSterilizerName(),addMonitoringItemBeanOptionSingleResult, - lastMonitoringItemBean.getCreateUserName(), - lastMonitoringItemBean.getQualityMonitoringGoodsId(), - lastMonitoringItemBean.getMaterialAmount()); - } - lastMonitoringItemBean = null; - } - int amount = rs.getInt(4); - - String positionMsg = rs.getString(6); - String questionName = rs.getString(7); - String createUserName = rs.getString("createUserName"); - int orderNum = rs.getInt(8); - String answer = rs.getString(9); - Integer frequency = null; - String sterilizerName = null; - if(addMonitoringItemBeanOptionSingleResult){ - frequency = ConvertNumber.getNumberIntValue(rs.getObject("frequency"), null); - sterilizerName = rs.getString("sterilizerName"); - if(SqlUtils.isExistColumn(rs, "sterilizerDate")){//监测范围为灭菌炉记录，日期要使用灭菌日期 - dataStr = rs.getString("sterilizerDate"); - } - } - if(dataStr == null){ - dataStr = rs.getString("dateStr"); - } - Long qmdid = rs.getLong("qmdid"); - int materialAmount = 0; - if(SqlUtils.isExistColumn(rs, "materialAmount")){ - materialAmount = rs.getInt("materialAmount"); - } - String tousseAmountKey = null; - if(washScope){ - int qmdOrderNumber = rs.getInt("qmdOrderNumber"); - if(StringUtils.isNotBlank(tousseName)){ - qmiIdToLastTousseNameMap.put(qmiId + "_" + qmdOrderNumber, tousseName); - qmiIdToLastTousseAmountMap.put(qmiId + "_" + qmdOrderNumber, amount); - } - if(StringUtils.isBlank(materialName)){ - if(lastMonitoringItemBean != null){ - newMonitoringItem(list, lastMonitoringItemBean.getRegisterMaterialAmount() - ,lastMonitoringItemBean.getAmount(), - lastMonitoringItemBean.getTousseName(), - lastMonitoringItemBean.getMaterialName(), - lastMonitoringItemBean.getQualityMonitoringInstanceId(), - lastMonitoringItemBean.getPositionMsg(), - lastMonitoringItemBean.getDateTime(), - lastMonitoringItemBean.getOrderNumber(), - lastMonitoringItemBean.getQuestionName(), - lastMonitoringItemBean.getOption(), - lastMonitoringItemBean.getAnswer(), - lastMonitoringItemBean.getFrequency(), - lastMonitoringItemBean.getSterilizerName(),addMonitoringItemBeanOptionSingleResult, - lastMonitoringItemBean.getCreateUserName(),lastMonitoringItemBean.getQualityMonitoringGoodsId(), - lastMonitoringItemBean.getMaterialAmount()); - lastMonitoringItemBean = null; - } - lastMonitoringItemBean = new MonitoringItemBean(); - lastMonitoringItemBean.setAnswer(answer); - lastMonitoringItemBean.setDateTime(dataStr); - lastMonitoringItemBean.setSterilizerName(sterilizerName); - lastMonitoringItemBean.setCreateUserName(createUserName); - lastMonitoringItemBean.setFrequency(frequency); - lastMonitoringItemBean.setMaterialName(materialName); - lastMonitoringItemBean.setTousseName(tousseName); - if(StringUtils.isNotBlank(tousseName) && StringUtils.isNotBlank(materialName)){ - lastMonitoringItemBean.setRegisterMaterialAmount(amount); - }else if(StringUtils.isNotBlank(tousseName)){ - lastMonitoringItemBean.setAmount(amount); - }else if(StringUtils.isNotBlank(materialName)){ - lastMonitoringItemBean.setRegisterMaterialAmount(amount); - } - lastMonitoringItemBean.setQualityMonitoringInstanceId(qmiId); - lastMonitoringItemBean.setPositionMsg(positionMsg); - lastMonitoringItemBean.setOrderNumber(orderNum); - lastMonitoringItemBean.setQuestionName(questionName); - lastMonitoringItemBean.setOption("-"); - lastMonitoringItemBean.setQualityMonitoringGoodsId(qmdid); - lastMonitoringItemBean.setMaterialAmount(materialAmount); - continue; - } - for (int i = 1; i < qmdOrderNumber; i++) { - if(qmiIdToLastTousseNameMap.containsKey(qmiId + "_" + (qmdOrderNumber - i))){ - tousseName = qmiIdToLastTousseNameMap.get(qmiId + "_" + (qmdOrderNumber - i)); - tousseAmountKey = qmiId + "_" + (qmdOrderNumber - i); - break; - } - } - } - lastMonitoringItemBean = null; - Integer registerMaterialAmount = 0; - if(StringUtils.isNotBlank(tousseName) && StringUtils.isNotBlank(materialName)){ - registerMaterialAmount = amount; - if(tousseAmountKey != null){ - amount = qmiIdToLastTousseAmountMap.get(tousseAmountKey); - } - }else if(StringUtils.isNotBlank(materialName)){ - registerMaterialAmount = amount; - amount = 0; - } - newMonitoringItem(list, registerMaterialAmount,amount,tousseName,materialName,qmiId,positionMsg, - dataStr, orderNum,questionName, "-", answer, frequency, sterilizerName, addMonitoringItemBeanOptionSingleResult,createUserName, qmdid, materialAmount); - } - if(lastMonitoringItemBean != null){ - newMonitoringItem(list, lastMonitoringItemBean.getRegisterMaterialAmount(),lastMonitoringItemBean.getAmount(), - lastMonitoringItemBean.getTousseName(), - lastMonitoringItemBean.getMaterialName(), - lastMonitoringItemBean.getQualityMonitoringInstanceId(), - lastMonitoringItemBean.getPositionMsg(), - lastMonitoringItemBean.getDateTime(), - lastMonitoringItemBean.getOrderNumber(), - lastMonitoringItemBean.getQuestionName(), - "-", - lastMonitoringItemBean.getAnswer(), - lastMonitoringItemBean.getFrequency(), - lastMonitoringItemBean.getSterilizerName(),addMonitoringItemBeanOptionSingleResult, - lastMonitoringItemBean.getCreateUserName(), - lastMonitoringItemBean.getQualityMonitoringGoodsId(), - lastMonitoringItemBean.getMaterialAmount()); - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - } - private void addMonitoringItemBeanOptionSingleResultWithoutMaterial(List list,String sql, boolean addMonitoringItemBeanOptionSingleResultWithoutMaterial, String monitoringType){ - if(StringUtils.isBlank(sql)){ - return ; - } - ResultSet rs = objectDao.executeSql(sql); - try { - while (rs.next()) { - String dataStr = null; - Long qmiId = rs.getLong(2); - String positionMsg = rs.getString(3); - String questionName = rs.getString(4); - int orderNum = rs.getInt(5); - String answer = rs.getString(6); - String createUserName = rs.getString("createUserName"); - Integer frequency = null; - String sterilizerName = null; - if(addMonitoringItemBeanOptionSingleResultWithoutMaterial){ - frequency = ConvertNumber.getNumberIntValue(rs.getObject("frequency"), null); - sterilizerName = rs.getString("sterilizerName"); - if(SqlUtils.isExistColumn(rs, "sterilizerDate")){//监测范围为灭菌炉记录，日期要使用灭菌日期 - dataStr = rs.getString("sterilizerDate"); - } - } - if(dataStr == null){ - dataStr = rs.getString("dateStr"); - } - newMonitoringItem(list, 0 ,0,"","",qmiId,positionMsg, - dataStr, orderNum, "-",questionName, answer ,frequency ,sterilizerName, addMonitoringItemBeanOptionSingleResultWithoutMaterial,createUserName, null, 0); - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - } - /** - * 添加质量监测记录的bean 没有监测细则的记录 - * @param list - * @param sql - */ - private void addMonitoringItemBeanQualityMonitoring(List list,String sql, boolean addMonitoringItemBeanQualityMonitoring,boolean washScope){ - if(StringUtils.isBlank(sql)){ - return ; - } - ResultSet rs = null; - try { - rs = objectDao.executeSql(sql); - Map qmiIdToLastTousseNameMap = new HashMap(); - Map qmiIdToLastTousseAmountMap = new HashMap(); - while (rs.next()) { - String dataStr = rs.getString(1); - String tousseName = rs.getString(2); - String materialName = rs.getString(3); - Long qmiId = rs.getLong(5); - int amount = rs.getInt(4); - Integer registerMaterialAmount = 0; - if(washScope){ - if(StringUtils.isNotBlank(tousseName)){ - qmiIdToLastTousseNameMap.put(qmiId, tousseName); - qmiIdToLastTousseAmountMap.put(qmiId, amount); - } - if(StringUtils.isBlank(materialName)){ - continue; - } - if(qmiIdToLastTousseNameMap.containsKey(qmiId)){ - tousseName = qmiIdToLastTousseNameMap.get(qmiId); - registerMaterialAmount = amount; - amount = qmiIdToLastTousseAmountMap.get(qmiId); - } - } - String positionMsg = rs.getString(6); - String createUserName = rs.getString("createUserName"); - Integer frequency = null; - String sterilizerName = null; - if(addMonitoringItemBeanQualityMonitoring){ - frequency = ConvertNumber.getNumberIntValue(rs.getObject("frequency"), null); - sterilizerName = rs.getString("sterilizerName"); - } - int materialAmount = 0; - if(SqlUtils.isExistColumn(rs, "materialAmount")){ - materialAmount = rs.getInt("materialAmount"); - } - newMonitoringItem(list,registerMaterialAmount, amount,tousseName,materialName,qmiId,positionMsg, - dataStr, 1, "-","-", "", frequency, sterilizerName, addMonitoringItemBeanQualityMonitoring,createUserName, null, materialAmount); - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - } - /** - * 添加质量监测记录的bean 定期监测的记录 - * @param list - * @param sql - */ - private void addMonitoringItemBeanRountineMonitoring(List list,String sql,boolean addMonitoringItemBeanRountineMonitoring,Map isQualifiedAmount, Map isQualifiedMaterialAmount){ - if(StringUtils.isBlank(sql)){ - return ; - } - ResultSet rs = objectDao.executeSql(sql); - try { - Integer qualifiedAmount = 0; - Integer unQualifiedAmount = 0; - Integer qualifiedMaterialAmount = 0; - Integer unQualifiedMaterialAmount = 0; - while (rs.next()) { - String dataStr = rs.getString(1); - String tousseName = rs.getString(2); - String materialName = rs.getString(3); - int amount = rs.getInt(4); - Long qmiId = rs.getLong(5); - String positionMsg = rs.getString(6); - String result = rs.getString(7); - String createUserName = rs.getString("createUserName"); - Integer frequency = null; - String sterilizerName = null; - if(addMonitoringItemBeanRountineMonitoring){ - frequency = ConvertNumber.getNumberIntValue(rs.getObject("frequency"), null); - sterilizerName = rs.getString("sterilizerName"); - } - if(StringUtils.isNotBlank(tousseName) && StringUtils.isNotBlank(materialName)){ - if("合格".equals(result)){ - qualifiedMaterialAmount += amount; - }else if("不合格".equals(result)){ - unQualifiedMaterialAmount += amount; - } - }else if(StringUtils.isNotBlank(tousseName)){ - if("合格".equals(result)){ - qualifiedAmount += amount; - }else if("不合格".equals(result)){ - unQualifiedAmount += amount; - } - }else{ - if("合格".equals(result)){ - qualifiedMaterialAmount += amount; - }else if("不合格".equals(result)){ - unQualifiedMaterialAmount += amount; - } - } - newMonitoringItem(list, null,amount,tousseName,materialName,qmiId,positionMsg, - dataStr, 100, "-","监测结果", result, frequency, sterilizerName, addMonitoringItemBeanRountineMonitoring,createUserName, null, 0); - } - isQualifiedAmount.put("合格", qualifiedAmount); - isQualifiedAmount.put("不合格", unQualifiedAmount); - isQualifiedMaterialAmount.put("合格", qualifiedMaterialAmount); - isQualifiedMaterialAmount.put("不合格", unQualifiedMaterialAmount); - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - } + + + + + + + + + private String buildQuerySQL(String startDate, String endDate, String querySupplyRoom, String formName, String materialName, String tousseName, String monitoringType) { @@ -10718,152 +8547,14 @@ sql += " order by " + dateQueryAdapter.dateConverAdapter("po.dateTime","yyyy-MM-dd")+",po.tousseName,po.material"; return sql; } - /** - * 创建质量监测报表查询条件的sql - * @param materialName 材料名 - * @param tousseName 包名 - * @return - */ - private String buildTousseNameMaterialNameWhereSql(String materialName, String tousseName){ - String materialSql = ""; - String tousseSql = ""; - if( StringUtils.isNotBlank(materialName)){ - // 如果字符串中有"[",SqlServer需要转译 - if(materialName.contains("[") && dbConnection.isSqlServer()){ - int index = materialName.indexOf("["); - materialName = materialName.substring(0, index) + "\\" + materialName.substring(index, materialName.length()); - materialSql = " qmd.material like '%" + materialName + "%'" + " escape '\\' "; - }else{ - materialSql = " qmd.material like '%" + materialName + "%'"; - } - } - if( StringUtils.isNotBlank(tousseName)){ - // 如果字符串中有"[",SqlServer需要转译 - if(tousseName.contains("[") && dbConnection.isSqlServer()){ - int index = tousseName.indexOf("["); - tousseName = tousseName.substring(0, index) + "\\" + tousseName.substring(index, materialName.length()); - tousseSql = " qmd.tousseName like '%" + tousseName + "%'" + " escape '\\' "; - }else{ - tousseSql = " qmd.tousseName like '%" + tousseName + "%'"; - } - } - // 如果都有材料和器械包的名字，用or条件 - String tsql = ""; - if(StringUtils.isNotBlank(materialSql) && StringUtils.isNotBlank(tousseSql)){ - tsql = " and ( " + materialSql + " or " + tousseSql + " ) "; - }else if(StringUtils.isNotBlank(materialSql)){ - tsql = " and " + materialSql; - }else if(StringUtils.isNotBlank(tousseSql)){ - tsql = " and " + tousseSql; - } - return tsql; - } - /** - * 创建质量监测报表查询条件的sql - * @param startDate - * @param endDate - * @param querySupplyRoom - * @param formName - * @param monitoringType - * @return - */ - private String buildWhereSQL(String startDate, String endDate, - String querySupplyRoom, String formName, String monitoringType) { - - String sql = " qmi.dateTime between " - + dateQueryAdapter.dateAdapter(startDate+" 00:00:00") - + " and " + "" - + dateQueryAdapter.dateAdapter(endDate+" 23:59:59") - + " and qmi.type = '" + monitoringType - + "' and qmi.name = '" + formName + "'" - + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom); - String departCoding = AcegiHelper.getLoginUser().getOrgUnitCodingFromSupplyRoomConfig(); - if(!supplyRoomConfigManager.isFirstOrSecondSupplyRoomOrgUnit(departCoding)){ - sql += SqlUtils.get_InSql_Extra("fi.orgUnitCoding", departCoding); - } - return sql; - } + + - private List> getBeanMap(List beanList,Set numNames){ - if(beanList == null) - return null; - List> listMap = new ArrayList>(); - Map map = new HashMap(); - for(MonitoringItemBean mt : beanList){ - String questionName = mt.getQuestionName(); - if("清洗情况".equals(questionName) || "灭菌情况".equals(questionName) || "回收情况".equals(questionName)){ - continue; - } - String option = mt.getOption(); - String answer = mt.getAnswer(); - String key = "0_" + option + "_" + questionName; - // 统计项有值才添加 - if(StringUtils.isNotBlank(answer)){ - if(numNames.contains(questionName) || "-".equals(questionName) && numNames.contains(option)){ - if(StringUtils.isNotBlank(answer) && StringUtils.isNumeric(answer)){ - Integer amount = Integer.valueOf(answer); - if( !map.containsKey(key) ){ - map.put(key, amount); - }else{ - int value = map.get(key) + amount; - map.put(key, value); - } - } - }else{ - if( !map.containsKey(key) ){ - map.put(key, 1); - }else{ - int value = map.get(key) + 1; - map.put(key, value); - } - } - } - } - listMap.add(map); - return listMap; - } - /** - * 获取结果统计的map，结果为"√"才统计 - * @param beanList - * @return - */ - private List> getBeanMapByResult(List beanList, Set numNames){ - if(beanList == null) - return null; - List> listMap = new ArrayList>(); - Map map = new HashMap(); - for(MonitoringItemBean mt : beanList){ - String option = mt.getOption(); - String questionName = mt.getQuestionName(); - String answer = mt.getAnswer(); - String key = "0_" + option + "_" + questionName; - // 统计项有值才添加 - if("√".equals(answer)){ - if( !map.containsKey(key) ){ - map.put(key, 1); - }else{ - int value = map.get(key) + 1; - map.put(key, value); - } - //指定的元素也添加(比如数字) - }else if(numNames.contains(questionName) || "-".equals(questionName) && numNames.contains(option)){ - if(StringUtils.isNotBlank(answer) && StringUtils.isNumeric(answer)){ - Integer amount = Integer.valueOf(answer); - if( !map.containsKey(key) ){ - map.put(key, amount); - }else{ - int value = map.get(key) + amount; - map.put(key, value); - } - } - } - } - listMap.add(map); - return listMap; - } + + /** * 获取回收器械材料明细 Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/ReportSqlUtil.java =================================================================== diff -u -r35797 -r35802 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/ReportSqlUtil.java (.../ReportSqlUtil.java) (revision 35797) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/ReportSqlUtil.java (.../ReportSqlUtil.java) (revision 35802) @@ -1,17 +1,24 @@ package com.forgon.disinfectsystem.jasperreports.util; import java.util.Set; - +import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Component; - +import com.forgon.databaseadapter.service.DateQueryAdapter; import com.forgon.disinfectsystem.entity.invoicemanager.InvoicePlan; +import com.forgon.disinfectsystem.jasperreports.service.dataindex.DataIndex; +import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams; import com.forgon.tools.util.SqlUtils; /** * 一些多报表使用的sql工具 * 不要超过500行 */ @Component(value = "reportSqlUtil") public class ReportSqlUtil { + + @Autowired + protected DataIndex dataIndex; + @Autowired + private DateQueryAdapter dateQueryAdapter; /** * 获取代理灭菌包过滤科室分组的sql * @param field 过滤的字段 @@ -81,4 +88,98 @@ return " and td.ancestorid in (select istt.tousseDefinitionId from InstrumentSetType_TD istt " + "join instrumentSetType ist on ist.id=istt.instrumentSetTypeId where "+ SqlUtils.getNonStringFieldInLargeCollectionsPredicate("ist.id", instrumentSetTypes) +") "; } + + /** + * 获取清洗记录加载的sql + * @param params + * @param statDate 开始时间 + * @param endDate 结束时间 + * @param querySupplyRoom 科室编码 + * @param dataSoureOfMaterialsCountOfToussesInReports 报表材料数量显示方式 + * @param queryDateType 查询时间类型 月或天 + * @return + */ + public String getWashWorkloadSql(ReportQueryParams params, String statDate, String endDate, String querySupplyRoom, Integer dataSoureOfMaterialsCountOfToussesInReports, boolean groupByDay, String format, String queryDateType){ + String betweenSql = String.format(" between %s and %s ", + dateQueryAdapter.dateConverAdapter2(statDate, format), + dateQueryAdapter.dateConverAdapter2(endDate, format)); + params.betweenSql = betweenSql; + params.querySupplyRoom = querySupplyRoom; + String groupByDaySql = null; + String queryDaySql = null; + String endDaySql = null; + if(groupByDay){ + if("month".equals(queryDateType)){ + groupByDaySql = "," + dateQueryAdapter.dateToVarchar2("wr.endDate"); + }else{ + groupByDaySql = "," + dateQueryAdapter.dateToVarchar1("wr.endDate"); + } + queryDaySql = groupByDaySql + " endDate "; + endDaySql = ",rs.endDate "; + }else{ + groupByDaySql = ""; + queryDaySql = ""; + endDaySql = ""; + } + // 统计清洗项的itemType为材料的物品，包括拆包清洗的器械包以及外来器械包 + String sql = String + .format("select ci.itemType type,ci.orgUnitName,ci.isSencondWashForForeignTousse ,sum(ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) amount, td.tousseType " + + queryDaySql + + dataIndex.getWashMaterialAmountSqlFromItemTypeIsMaterial(params.extraJoinCondition) + + " and wr.endDate %s and wr.washMaterialAmount <> 0 %s group by ci.itemType,ci.orgUnitName,ci.isSencondWashForForeignTousse, td.tousseType " + + groupByDaySql, + params.betweenSql, + SqlUtils.get_InSql_Extra("wr.orgUnitCoding", params.querySupplyRoom)); + + // 统计清洗项的itemType为材料的物品，单独入清洗篮筐的器械，没有关联的器械包 + sql += " union all "; + sql += String + .format("select ci.itemType type,ci.orgUnitName,ci.isSencondWashForForeignTousse ,sum(ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) amount, td.tousseType " + + queryDaySql + + dataIndex.getWashMaterialAmountSqlFromItemTypeIsMaterialWithoutTousseDefinition(params.extraJoinCondition) + + " and wr.endDate %s and wr.washMaterialAmount <> 0 %s group by ci.itemType,ci.orgUnitName,ci.isSencondWashForForeignTousse, td.tousseType " + + groupByDaySql, + params.betweenSql, + SqlUtils.get_InSql_Extra("wr.orgUnitCoding", params.querySupplyRoom)); + + + // 统计清洗项的itemType不为材料的物品，包括整包清洗的器械包 + sql += " union all "; + String columnSql = null; + String joinMaterialInstanceSql = null; + if(dataSoureOfMaterialsCountOfToussesInReports == 3){ + columnSql = "(ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end)*ci.materialAmount"; + joinMaterialInstanceSql = ""; + }else{ + columnSql = "(ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end)*mi.count"; + joinMaterialInstanceSql = "inner join MaterialInstance mi on mi.tousse_id = td.id"; + } + sql += String + .format("select ci.itemType type,ci.orgUnitName,ci.isSencondWashForForeignTousse ,sum(%s) amount, td.tousseType " + + queryDaySql + + dataIndex.getWashMaterialAmountSqlFromItemTypeIsNotMaterial(joinMaterialInstanceSql) + + " and wr.endDate %s and wr.washMaterialAmount <> 0 %s group by ci.itemType,ci.orgUnitName,ci.isSencondWashForForeignTousse, td.tousseType " + + groupByDaySql, + columnSql, + params.betweenSql, + SqlUtils.get_InSql_Extra("wr.orgUnitCoding", params.querySupplyRoom)); + + // 判断是否需要统计单独清洗的材料的数量 + // 由于要查找清洗记录所属的科室的名称，所以连接了orgUnit表进行查询，没有调用DataIndex服务类的getWashMaterialAmountSqlFromTousseTypeIsMaterial方法 + sql += " union all "; + sql += String + .format("select md.type,org.name orgUnitName,0 isSencondWashForForeignTousse ,sum(wrm.amount) amount,'' tousseType " + + queryDaySql + + "from WashAndDisinfectRecord wr, WashRecord_WashMaterial wm,WashAndDisinfectRecordMaterial wrm,MaterialDefinition md, OrgUnit org " + + "where wm.WashAndDisinfectRecord_ID = wr.id and wrm.id = wm.WashAndDisinfectMaterial_ID and wrm.materialDefinition_id = md.id and org.orgUnitCoding = wr.orgUnitCoding " + + " and wr.endDate %s %s group by md.type,org.name" + + groupByDaySql, + params.betweenSql, SqlUtils.get_InSql_Extra( + "wr.orgUnitCoding", params.querySupplyRoom)); + sql = " select rs.type,rs.orgUnitName,rs.isSencondWashForForeignTousse,sum(rs.amount) amount, rs.tousseType "+ endDaySql + " from (" + + sql + + ")rs " + + " group by rs.type,rs.orgUnitName,rs.isSencondWashForForeignTousse,rs.tousseType" + endDaySql; + return sql; + } } Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/SterilizationWorkloadReportHelper.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/SterilizationWorkloadReportHelper.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/SterilizationWorkloadReportHelper.java (revision 35802) @@ -0,0 +1,345 @@ +package com.forgon.disinfectsystem.jasperreports.util; + +import java.math.BigDecimal; +import java.math.RoundingMode; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.text.ParseException; +import java.text.SimpleDateFormat; +import java.util.ArrayList; +import java.util.Calendar; +import java.util.Date; +import java.util.HashMap; +import java.util.Iterator; +import java.util.List; +import java.util.Map; +import java.util.Map.Entry; + +import org.apache.commons.lang.StringUtils; +import org.springframework.stereotype.Component; + +import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseDefinition; +import com.forgon.disinfectsystem.entity.sterilizationmanager.sterilizationrecord.SterilizationRecord; +import com.forgon.disinfectsystem.jasperreports.javabeansource.DisinfectionFractionDefectiveBean; +import com.forgon.tools.MathTools; +import com.forgon.tools.date.DateTools; +import com.forgon.tools.db.DatabaseUtil; +import com.forgon.tools.util.SqlUtils; +/** + * 灭菌区工作量及不合格率月报 + * + */ +@Component +public class SterilizationWorkloadReportHelper extends ReportHelper{ + /** + * 获取灭菌区工作量及不合格率月报数据 + * @param startTime 开始时间 + * @param endTime 结束时间 + * @param departCoding 供应室编码 + * @param queryDateType 查询时间类型 year or month or day + * @return + */ + public List getDisinfectionFDSource( + String startTime,String endTime, String querySupplyRoom, String queryDateType) { + // 数据是否有消毒物品数量 如果没有要隐藏列 + boolean hasDisinfection = false; + //数量map<时间,<列头,数量>> + Map> amountMap = new HashMap>(); + //不合格数据map <时间,<表单名称,数量>> + Map> unQualityAmountMap = new HashMap>(); + //表格列头 + Map sourceSortMap = new HashMap(); + //最终返回的表数据 + List returnList = new ArrayList(); + + //查询的开始时间 + String startDateStr; + //查询的结束时间 + String endDateStr; + //开始时间 + Date startDate = null; + //结束时间 + Date endDate = null; + //日期差量 查询天为Calendar.MONTH 否则 Calendar.DAY_OF_MONTH + Integer addDateType; + //数据查询的时间类型 queryDateType为年份是值为month 否则day + String dateType; + SimpleDateFormat sdfyyyyMMdd = new SimpleDateFormat("yyyy-MM-dd"); + SimpleDateFormat sdfyyyyMMddHHmmss = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); + //不同查询时间类型的一些处理 + Calendar calendar = Calendar.getInstance(); + endDate = calendar.getTime(); + if("year".equals(queryDateType)){ + dateType = "month"; + startDateStr = startTime + "-01-01 00:00:00"; + try { + startDate = sdfyyyyMMddHHmmss.parse(startDateStr); + } catch (ParseException e) { + e.printStackTrace(); + } + calendar.setTime(startDate); + calendar.add(Calendar.YEAR, 1); + endDateStr = sdfyyyyMMdd.format(calendar.getTime()) + " 00:00:00"; + }else if("day".equals(queryDateType)){ + dateType = "day"; + startDateStr = startTime + " 00:00:00"; + endDateStr = endTime + " 23:59:59"; + }else{//按月份查询查询的是天 + dateType = "day"; + startDateStr = startTime + "-01 00:00:00"; + if(StringUtils.isBlank(endTime)){ + try { + startDate = sdfyyyyMMddHHmmss.parse(startDateStr); + } catch (ParseException e) { + e.printStackTrace(); + } + calendar.setTime(startDate); + calendar.add(Calendar.MONTH, 1); + endDateStr = sdfyyyyMMdd.format(calendar.getTime()) + " 00:00:00"; + }else{ + String[] dateArr = endTime.split("-"); + endDateStr = DateTools.getLastDayOfMonthByDate(dateArr[0], dateArr[1]) + " 23:59:59"; + } + } + if("month".equals(dateType)){ + addDateType = Calendar.MONTH; + }else{ + addDateType = Calendar.DAY_OF_MONTH; + } + try { + if(startDate == null){//按月份查询的 如果没传结束月份 前面已经获取过 + startDate = sdfyyyyMMddHHmmss.parse(startDateStr); + } + calendar.setTime(startDate); + endDate = sdfyyyyMMddHHmmss.parse(endDateStr); + } catch (ParseException e) { + e.printStackTrace(); + } + + + sourceSortMap.put("器械包", 1); + sourceSortMap.put("外来器械包", 2); + sourceSortMap.put("敷料包", 3); + sourceSortMap.put("代理灭菌包", 4); + sourceSortMap.put(TousseDefinition.PACKAGE_TYPE_DISINFECTION, 5); + sourceSortMap.put("灭菌炉数", 6); + sourceSortMap.put("灭菌总数", 7); + + getSterilizationAmountOfDisinfectionFD(amountMap, dateType, startDate, endDate, querySupplyRoom, hasDisinfection); + setUnQualityAmountMapOfDisinfectionFD(unQualityAmountMap, dateType, querySupplyRoom, startDate, endDate); + + SimpleDateFormat sdf = null; + if("month".equals(dateType)){ + sdf = new SimpleDateFormat("yyyy-MM"); + }else{ + sdf = sdfyyyyMMdd; + } + // 日期没数据的map 没数据也要在报表显示0 + Map noDataMap = new HashMap(); + noDataMap.put(TousseDefinition.PACKAGE_TYPE_INSIDE, 0); + noDataMap.put(TousseDefinition.PACKAGE_TYPE_FOREIGN, 0); + noDataMap.put(TousseDefinition.PACKAGE_TYPE_DRESSING, 0); + noDataMap.put(TousseDefinition.PACKAGE_TYPE_PROXY, 0); + noDataMap.put(TousseDefinition.PACKAGE_TYPE_DISINFECTION, 0); + noDataMap.put("灭菌炉数", 0); + noDataMap.put("灭菌总数", 0); + while(endDate.after(startDate)){ + String thisStartDate = sdf.format(startDate); + Integer amount = 0;//这个日期的灭菌总数 + Map typeAmoutMap = null; + if(amountMap.containsKey(thisStartDate)){ + typeAmoutMap = amountMap.get(thisStartDate); + amount = typeAmoutMap.get("灭菌总数"); + }else{ + typeAmoutMap = noDataMap; + } + Integer unqualifiedAmount = 0;//不合格总数 + double fractionDefective = 0.00;//不合格率 + if(unQualityAmountMap.containsKey(thisStartDate)){ + Map formNameAmountMap = unQualityAmountMap.get(thisStartDate); + unqualifiedAmount = formNameAmountMap.get("不合格总数"); + if (amount != null && amount != 0 && unqualifiedAmount != null && unqualifiedAmount != 0) { + fractionDefective = new BigDecimal(unqualifiedAmount).divide(new BigDecimal(amount),4,RoundingMode.HALF_UP).doubleValue(); + } + for(Entry entry : formNameAmountMap.entrySet()){ + String monitoringName = entry.getKey(); + if("不合格总数".equals(monitoringName)){ + continue; + } + DisinfectionFractionDefectiveBean bean = new DisinfectionFractionDefectiveBean(); + bean.setDate(thisStartDate); + bean.setResult(entry.getValue()); + bean.setTitle(monitoringName); + bean.setPercentage(fractionDefective); + bean.setTitleSort(15); + returnList.add(bean); + } + } + for(Entry entry : typeAmoutMap.entrySet()){ + String title = entry.getKey(); + DisinfectionFractionDefectiveBean bean = new DisinfectionFractionDefectiveBean(); + bean.setDate(thisStartDate); + bean.setResult(entry.getValue()); + bean.setTitle(title); + bean.setTitleSort(sourceSortMap.get(title)); + bean.setPercentage(fractionDefective); + returnList.add(bean); + } + calendar.setTime(startDate); + calendar.add(addDateType, 1); + startDate = calendar.getTime(); + } + // 月合计汇总行 + Map totalRowMap = new HashMap(); + Integer monthUnqualifiedAmount = 0; // 月不合格总数 + for (DisinfectionFractionDefectiveBean bean : returnList) { + String title = bean.getTitle(); + Integer result = bean.getResult(); + if (totalRowMap.get(title) == null) { + totalRowMap.put(title, result); + } else if (totalRowMap.get(title) != null) { + totalRowMap.put(title, + (result + totalRowMap.get(title))); + } + if (bean.getTitleSort() == 15) { + monthUnqualifiedAmount += result; + } + } + Integer monthTotalAmount = totalRowMap.get("灭菌总数"); // 月灭菌总数 + try { + for(Entry entry : totalRowMap.entrySet()){ + String title = entry.getKey(); + Integer result = entry.getValue(); + DisinfectionFractionDefectiveBean bean = new DisinfectionFractionDefectiveBean(); + bean.setDate("合计"); + bean.setResult(result); + bean.setTitle(title); + Integer sort = sourceSortMap.get(title); + if (sort == null) { + sort = 15; + } + bean.setTitleSort(sort); + Double fractionDefective = 0.00; + if(monthTotalAmount > 0 && monthUnqualifiedAmount > 0){ + fractionDefective = new BigDecimal(monthUnqualifiedAmount).divide(new BigDecimal(monthTotalAmount),4,RoundingMode.HALF_UP).doubleValue(); + } + bean.setPercentage(fractionDefective); + returnList.add(bean); + } + } catch (Exception e) { + } + if(!hasDisinfection){//没有消毒物品,不显示消毒物品列 + Iterator it = returnList.iterator(); + while(it.hasNext()){ + if(TousseDefinition.PACKAGE_TYPE_DISINFECTION.equals(it.next().getTitle())){ + it.remove(); + } + } + } + + return returnList; + } + private void getSterilizationAmountOfDisinfectionFD(Map> amountMap, String dateType, Date startDate, Date endDate, String querySupplyRoom, boolean hasDisinfection){ + String queryDateSQL = ""; + if("month".equals(dateType)){ + queryDateSQL = dateQueryAdapter.dateToVarchar2("po.endDate"); + }else{ + queryDateSQL = dateQueryAdapter.dateToVarchar1("po.endDate"); + } + ResultSet rs = null; + try { + String sterilizationSql = " select sum(po.tousseAmount) tousseAmount,sum(po.foreignTousseAmount) foreignTousseAmount" + + ",sum(po.dressingTousseAmount) dressingTousseAmount,sum(po.proxyTousseAmount) proxyTousseAmount" + + ",sum(po.disinfectionAmount) disinfectionAmount," + + queryDateSQL + +" dateStr,sum(po.amount) amount,count(po.id) srAmount from " + + SterilizationRecord.class.getSimpleName() + +" po where po.endDate between " + + dateQueryAdapter.dateAdapter(startDate) + " and " + + dateQueryAdapter.dateAdapter(endDate) + + SqlUtils.get_InSql_Extra("po.orgUnitCoding", querySupplyRoom) + + " and (po.status = '" + + SterilizationRecord.STERILIZATION_STATUS_END + "'" + + " or po.status = '" + + SterilizationRecord.STERILIZATION_STATUS_FAILURE + + "') group by " + + queryDateSQL; + rs = objectDao.executeSql(sterilizationSql); + while (rs.next()) { + int tousseAmount = rs.getInt("tousseAmount"); + int foreignTousseAmount = rs.getInt("foreignTousseAmount"); + int dressingTousseAmount = rs.getInt("dressingTousseAmount"); + int proxyTousseAmount = rs.getInt("proxyTousseAmount"); + int disinfectionAmount = rs.getInt("disinfectionAmount"); + if(!hasDisinfection && disinfectionAmount > 0){ + hasDisinfection = true; + } + String dateStr = rs.getString("dateStr"); + Map tousseTypeAmountMap = null; + if(amountMap.containsKey(dateStr)){ + tousseTypeAmountMap = amountMap.get(dateStr); + }else{ + tousseTypeAmountMap = new HashMap(); + amountMap.put(dateStr, tousseTypeAmountMap); + } + tousseTypeAmountMap.put(TousseDefinition.PACKAGE_TYPE_INSIDE, tousseAmount); + tousseTypeAmountMap.put(TousseDefinition.PACKAGE_TYPE_FOREIGN, foreignTousseAmount); + tousseTypeAmountMap.put(TousseDefinition.PACKAGE_TYPE_DRESSING, dressingTousseAmount); + tousseTypeAmountMap.put(TousseDefinition.PACKAGE_TYPE_PROXY, proxyTousseAmount); + tousseTypeAmountMap.put(TousseDefinition.PACKAGE_TYPE_DISINFECTION, disinfectionAmount); + tousseTypeAmountMap.put("灭菌炉数", rs.getInt("srAmount")); + tousseTypeAmountMap.put("灭菌总数", rs.getInt("amount")); + } + } catch (SQLException e) { + e.printStackTrace(); + } finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + /** + * 设置不合格数据源 + */ + private void setUnQualityAmountMapOfDisinfectionFD(Map> unQualityAmountMap, String dateType, String querySupplyRoom, Date startDate, Date endDate){ + String queryDateSQL = null; + if("month".equals(dateType)){ + queryDateSQL = dateQueryAdapter.dateToVarchar2("qi.datetime"); + }else{ + queryDateSQL = dateQueryAdapter.dateToVarchar1("qi.datetime"); + } + String unQualitySql = "select fd.formName,sum(qmd.amount) amount,"+ queryDateSQL +" monthstr from QualityMonitoringInstance qi ,FormInstance fi," + + "FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd " + + " where qi.id = fi.id and fi.formDefinition_id = fd.id and fd.id = qmdf.id " + + " and qi.id=qmd.qualityMonitoringInstance_id " + + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + + " and qmdf.responsibilitypart = '灭菌管理' and fd.formType = '质量监测' " + + SqlUtils.get_LikeSql("fd.departcodes", + querySupplyRoom.split(",")) + + " and qi.datetime between " + + dateQueryAdapter.dateAdapter(startDate) + + " and " + + dateQueryAdapter.dateAdapter(endDate) + + " group by fd.formName," + + queryDateSQL; + ResultSet unQualityRs = null; + try { + unQualityRs = objectDao.executeSql(unQualitySql); + while(unQualityRs.next()){ + String monthstr = unQualityRs.getString("monthstr"); + Map formNameAmountMap = null; + if(unQualityAmountMap.containsKey(monthstr)){ + formNameAmountMap = unQualityAmountMap.get(monthstr); + }else{ + formNameAmountMap = new HashMap(); + unQualityAmountMap.put(monthstr, formNameAmountMap); + } + int amount = unQualityRs.getInt("amount"); + formNameAmountMap.put(unQualityRs.getString("formName"), amount); + formNameAmountMap.put("不合格总数", MathTools.add(amount, formNameAmountMap.get("不合格总数")).intValue()); + } + } catch (SQLException e) { + e.printStackTrace(); + } finally { + DatabaseUtil.closeResultSetAndStatement(unQualityRs); + } + } +} Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/CommonReportHelper.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/CommonReportHelper.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/CommonReportHelper.java (revision 35802) @@ -0,0 +1,100 @@ +package com.forgon.disinfectsystem.jasperreports.util; + +import java.sql.ResultSet; +import java.sql.SQLException; +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.reportoption.GoodsOptionManager; +import com.forgon.disinfectsystem.entity.basedatamanager.reportoption.GoodsOption; +import com.forgon.disinfectsystem.entity.becleanitem.ClassifiedItem; +import com.forgon.systemsetting.service.HttpOptionManager; +import com.forgon.tools.db.DatabaseUtil; +import com.forgon.tools.hibernate.ObjectDao; +/** + * 公共的报表帮助类 一些多个报表使用的东西 可以考虑些在这里 + * + */ +@Component +public class CommonReportHelper { + @Autowired + private GoodsOptionManager goodsOptionManager; + @Autowired + private HttpOptionManager httpOptionManager; + @Autowired + private ObjectDao objectDao; + /** + * 获取清洗数据 按日期分组查询和计算 + * @param sql + * @return + */ + public Map> getWashWorkloadAmountMap(String sql, boolean groupByDate){ + // 手术器械设置 + GoodsOption option = goodsOptionManager.getGoodsOption(GoodsOption.MODEL_SURGICALINSTRUMENTS_DEPT, null); + List groupDeparts = httpOptionManager.getDepartsFromDepartGroup(); + ResultSet rs = null; + Map> washAmountMap = new HashMap>(); + try { + rs = objectDao.executeSql(sql); + while(rs.next()){ + String type = rs.getString("type"); + String orgUnitName = rs.getString("orgUnitName"); + boolean isSecWashForeignTousse = rs.getBoolean("isSencondWashForForeignTousse"); + Integer amount = rs.getInt("amount"); + String tousseType = rs.getString("tousseType"); + String endDate = rs.getString("endDate"); + String key = ""; + if(ClassifiedItem.TYPE_FOREIGN_TOUSSE.equals(tousseType)){ + // 外来器械包，看是否二次清洗 + if(isSecWashForeignTousse == false){ + key = "外来器械"; + }else{ + key = "外来器械二次清洗"; + } + }else if(ClassifiedItem.TYPE_DISINFECTION_GOODS.equals(type)){ + key = "消毒物品"; + + }else{ + // 不是外来器械，不是消毒物品，就是手术器械，或者普通器械，或者分组器械 + // 是手术器械 + if (StringUtils.isNotBlank(orgUnitName) && option != null && StringUtils.isNotBlank(option.getValue()) && option.getValue().indexOf(orgUnitName) != -1) { + key = "手术器械"; + }else if( !(CollectionUtils.isNotEmpty(groupDeparts) && groupDeparts.contains(orgUnitName)) ){ + // 也不是分组器械，是普通器械 + key = "普通器械"; + } + } + if(StringUtils.isNotBlank(endDate)){ + Map mapItem = null; + if(washAmountMap.containsKey(endDate)){ + mapItem = washAmountMap.get(endDate); + }else{ + mapItem = new HashMap(); + mapItem.put("清洗总数", 0); + washAmountMap.put(endDate, mapItem); + } + if(StringUtils.isNotBlank(key)){ + if(mapItem.containsKey(key)){ + Integer curAmount = mapItem.get(key); + mapItem.put(key, curAmount + amount); + }else{ + mapItem.put(key,amount); + } + } + mapItem.put("清洗总数", mapItem.get("清洗总数") + amount); + } + } + } catch (SQLException e) { + e.printStackTrace(); + } finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + return washAmountMap; + } +}