Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/InstrumentRepairReportHelper.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/InstrumentRepairReportHelper.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/InstrumentRepairReportHelper.java (revision 35781) @@ -0,0 +1,151 @@ +package com.forgon.disinfectsystem.jasperreports.util; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.text.DateFormat; +import java.util.ArrayList; +import java.util.Date; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.apache.commons.lang.StringUtils; +import org.springframework.beans.factory.annotation.Autowired; + +import com.forgon.databaseadapter.service.DateQueryAdapter; +import com.forgon.disinfectsystem.entity.basedatamanager.instrumentrepair.InstrumentRepair; +import com.forgon.disinfectsystem.entity.basedatamanager.instrumentrepair.InstrumentRepairGoods; +import com.forgon.disinfectsystem.entity.basedatamanager.materialdefinition.MaterialDefinition; +import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseDefinition; +import com.forgon.disinfectsystem.jasperreports.javabeansource.InstrumentRepairGoodsVo; +import com.forgon.disinfectsystem.jasperreports.javabeansource.InstrumentRepairVo; +import com.forgon.tools.date.DateTools; +import com.forgon.tools.db.DatabaseUtil; +import com.forgon.tools.db.InitDbConnection; +import com.forgon.tools.hibernate.ObjectDao; +import com.forgon.tools.util.SqlUtils; +/** + * 器械维修记录报表 + * @author zc.li 2023-05-04 + * + */ +public class InstrumentRepairReportHelper { + @Autowired + private ObjectDao objectDao; + @Autowired + private InitDbConnection dbConnection; + @Autowired + protected DateQueryAdapter dateQueryAdapter; + /** + * 处理器械维修记录报表数据 + * @param objectDao + * @param sql + * @return + */ + public List getInstrumentRepairReportData(String startTime, String endTime, String repairStatus, String registrantName + ,String materialName, String tousseName){ + ResultSet rs = null; + List instrumentRepairVos = new ArrayList(); + try { + String sql = getInstrumentRepairReportSql(startTime, endTime, repairStatus, registrantName, materialName, tousseName); + rs = objectDao.executeSql(sql); + InstrumentRepairVo instrumentRepairVo = null; + Map instrumentRepairVoMap = new HashMap(); + DateFormat sdf = DateTools.YMDHMFORMAT.get(); + while (rs.next()) { + Long id = rs.getLong("id"); + if(instrumentRepairVoMap.containsKey(id)){ + instrumentRepairVo = instrumentRepairVoMap.get(id); + }else{ + instrumentRepairVo = new InstrumentRepairVo(); + Date registerDateTime = rs.getTimestamp("registerDateTime"); + Date outHandoverDateTime = rs.getTimestamp("outHandoverDateTime"); + Date returnHandoverDateTime = rs.getTimestamp("returnHandoverDateTime"); + String repairStatusRs = rs.getString("repairStatus"); + String registrantNameRs = rs.getString("registrantName"); + String repairDesc = rs.getString("repairDesc"); + String remark = rs.getString("remark"); + if(registerDateTime != null){ + instrumentRepairVo.setRegisterDateTimeStr(sdf.format(registerDateTime)); + } + if(outHandoverDateTime != null){ + instrumentRepairVo.setOutHandoverDateTimeStr(sdf.format(outHandoverDateTime)); + } + if(returnHandoverDateTime != null){ + instrumentRepairVo.setReturnHandoverDateTimeStr(sdf.format(returnHandoverDateTime)); + } + instrumentRepairVo.setRepairStatus(repairStatusRs); + instrumentRepairVo.setRegistrantName(registrantNameRs); + instrumentRepairVo.setRepairDesc(repairDesc); + instrumentRepairVo.setRemark(remark); + instrumentRepairVoMap.put(id, instrumentRepairVo); + instrumentRepairVos.add(instrumentRepairVo); + } + InstrumentRepairGoodsVo goodsVo = new InstrumentRepairGoodsVo(); + goodsVo.setAmount(rs.getInt("amount")); + String materialNameRs = rs.getString("materialDefinitionName"); + String specification = rs.getString("specification"); + if(StringUtils.isNotBlank(specification)){ + materialNameRs += "[" + specification + "]"; + } + goodsVo.setMaterialName(materialNameRs); + + goodsVo.setTousseName(rs.getString("tousseName")); + instrumentRepairVo.getInstrumentRepairGoodsVos().add(goodsVo); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + return instrumentRepairVos; + } + /** + * 构建器械维修记录报表查询语句 + * @param startTime 查询登记开始时间 + * @param endTime 查询登记结束时间 + * @param repairStatus 维修状态 + * @param registrantName 登记人姓名 + * @param materialName 材料名称 可能包含规格 + * @param tousseName 包名称 + * @return + */ + private String getInstrumentRepairReportSql(String startTime, String endTime, String repairStatus, String registrantName + ,String materialName, String tousseName){ + String materialNameSQL = null; + if(StringUtils.isNotBlank(materialName)){ + if(materialName.contains("[") && materialName.contains("]") && materialName.lastIndexOf("]") > materialName.indexOf("[")){ + String connector = null; + if(dbConnection.isOracle()){ + connector = " || "; + }else{ + connector = " + "; + } + materialNameSQL = " and md.name "+ connector +" '[' "+ connector +" md.specification "+ connector +" ']' = '" + materialName + "'"; + }else{ + materialNameSQL = " and md.name = '" + materialName + "'"; + } + }else{ + materialNameSQL = ""; + } + return "select i.id,i.registerDateTime , i.repairStatus, i.registrantName " + + ", i.outHandoverDateTime ,i.returnHandoverDateTime ,i.repairDesc ,i.remark " + + ",g.amount ,md.name materialDefinitionName ,td.name tousseName,md.specification from " + + InstrumentRepair.class.getSimpleName() + +" i join " + + InstrumentRepairGoods.class.getSimpleName() + +" g on i.id=g.instrumentRepair_ID left join " + + TousseDefinition.class.getSimpleName() + +" td on td.id=g.toussedefinitionId left join " + + MaterialDefinition.class.getSimpleName() + +" md on md.id=g.materialDefinitionID where i.reviewStatus='" + + InstrumentRepair.REVIEW_STATUS_REVIEWED + +"' and registerDateTime " + + String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startTime),dateQueryAdapter.dateAdapter(endTime)) + + SqlUtils.getWhereSqlByfilterFieldAndStringValueAndSeparator("i.repairStatus", repairStatus, ",") + + SqlUtils.getWhereSqlByfilterFieldAndStringValue("i.registrantName", registrantName) + + materialNameSQL + + SqlUtils.getWhereSqlByfilterFieldAndStringValue("td.name", tousseName) + + "order by i.registerDateTime desc,i.id desc,md.id desc "; + } +} Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r35774 -r35781 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35774) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35781) @@ -174,6 +174,7 @@ import com.forgon.disinfectsystem.jasperreports.javabeansource.*; import com.forgon.disinfectsystem.jasperreports.service.dataindex.DataIndex; import com.forgon.disinfectsystem.jasperreports.service.dataindex.WorkQualityCollectionDataIndex; +import com.forgon.disinfectsystem.jasperreports.util.InstrumentRepairReportHelper; import com.forgon.disinfectsystem.packing.service.PackingManager; import com.forgon.disinfectsystem.qualitymonitoring.definition.service.QualityMonitoringDefinitionManager; import com.forgon.disinfectsystem.recyclingapplication.service.InvoicePlanManager; @@ -288,6 +289,8 @@ private OperationManager operationManager; private BrancheOfHospitalManager brancheOfHospitalManager; private PackingManager packingManager; + @Autowired + private InstrumentRepairReportHelper instrumentRepairReportHelper; public void setPackingManager(PackingManager packingManager) { this.packingManager = packingManager; } @@ -30995,108 +30998,6 @@ @Override public List getInstrumentRepairReportData(String startTime, String endTime, String repairStatus , String registrantName, String materialName, String tousseName) { - String sql = getInstrumentRepairReportSql(startTime, endTime, repairStatus, registrantName, materialName, tousseName); - ResultSet rs = null; - List instrumentRepairVos = new ArrayList(); - try { - rs = objectDao.executeSql(sql); - InstrumentRepairVo instrumentRepairVo = null; - Map instrumentRepairVoMap = new HashMap(); - SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm"); - while (rs.next()) { - Long id = rs.getLong("id"); - if(instrumentRepairVoMap.containsKey(id)){ - instrumentRepairVo = instrumentRepairVoMap.get(id); - }else{ - instrumentRepairVo = new InstrumentRepairVo(); - Date registerDateTime = rs.getTimestamp("registerDateTime"); - Date outHandoverDateTime = rs.getTimestamp("outHandoverDateTime"); - Date returnHandoverDateTime = rs.getTimestamp("returnHandoverDateTime"); - String repairStatusRs = rs.getString("repairStatus"); - String registrantNameRs = rs.getString("registrantName"); - String repairDesc = rs.getString("repairDesc"); - String remark = rs.getString("remark"); - if(registerDateTime != null){ - instrumentRepairVo.setRegisterDateTimeStr(sdf.format(registerDateTime)); - } - if(outHandoverDateTime != null){ - instrumentRepairVo.setOutHandoverDateTimeStr(sdf.format(outHandoverDateTime)); - } - if(returnHandoverDateTime != null){ - instrumentRepairVo.setReturnHandoverDateTimeStr(sdf.format(returnHandoverDateTime)); - } - instrumentRepairVo.setRepairStatus(repairStatusRs); - instrumentRepairVo.setRegistrantName(registrantNameRs); - instrumentRepairVo.setRepairDesc(repairDesc); - instrumentRepairVo.setRemark(remark); - instrumentRepairVoMap.put(id, instrumentRepairVo); - instrumentRepairVos.add(instrumentRepairVo); - } - InstrumentRepairGoodsVo goodsVo = new InstrumentRepairGoodsVo(); - goodsVo.setAmount(rs.getInt("amount")); - String materialNameRs = rs.getString("materialDefinitionName"); - String specification = rs.getString("specification"); - if(StringUtils.isNotBlank(specification)){ - materialNameRs += "[" + specification + "]"; - } - goodsVo.setMaterialName(materialNameRs); - - goodsVo.setTousseName(rs.getString("tousseName")); - instrumentRepairVo.getInstrumentRepairGoodsVos().add(goodsVo); - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - return instrumentRepairVos; + return instrumentRepairReportHelper.getInstrumentRepairReportData(startTime, endTime, repairStatus, registrantName, materialName, tousseName); } - /** - * 构建器械维修记录报表查询语句 - * @param startTime 查询登记开始时间 - * @param endTime 查询登记结束时间 - * @param repairStatus 维修状态 - * @param registrantName 登记人姓名 - * @param materialName 材料名称 可能包含规格 - * @param tousseName 包名称 - * @return - */ - private String getInstrumentRepairReportSql(String startTime, String endTime, String repairStatus, String registrantName - ,String materialName, String tousseName){ - String materialNameSQL = null; - if(StringUtils.isNotBlank(materialName)){ - if(materialName.contains("[") && materialName.contains("]") && materialName.lastIndexOf("]") > materialName.indexOf("[")){ - String connector = null; - if(dbConnection.isOracle()){ - connector = " || "; - }else{ - connector = " + "; - } - materialNameSQL = " and md.name "+ connector +" '[' "+ connector +" md.specification "+ connector +" ']' = '" + materialName + "'"; - }else{ - materialNameSQL = " and md.name = '" + materialName + "'"; - } - }else{ - materialNameSQL = ""; - } - return "select i.id,i.registerDateTime , i.repairStatus, i.registrantName " - + ", i.outHandoverDateTime ,i.returnHandoverDateTime ,i.repairDesc ,i.remark " - + ",g.amount ,md.name materialDefinitionName ,td.name tousseName,md.specification from " - + InstrumentRepair.class.getSimpleName() - +" i join " - + InstrumentRepairGoods.class.getSimpleName() - +" g on i.id=g.instrumentRepair_ID left join " - + TousseDefinition.class.getSimpleName() - +" td on td.id=g.toussedefinitionId left join " - + MaterialDefinition.class.getSimpleName() - +" md on md.id=g.materialDefinitionID where i.reviewStatus='" - + InstrumentRepair.REVIEW_STATUS_REVIEWED - +"' and registerDateTime " - + String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startTime),dateQueryAdapter.dateAdapter(endTime)) - + SqlUtils.getWhereSqlByfilterFieldAndStringValueAndSeparator("i.repairStatus", repairStatus, ",") - + SqlUtils.getWhereSqlByfilterFieldAndStringValue("i.registrantName", registrantName) - + materialNameSQL - + SqlUtils.getWhereSqlByfilterFieldAndStringValue("td.name", tousseName) - + "order by i.registerDateTime desc,i.id desc,md.id desc "; - } } Index: ssts-web/src/main/webapp/WEB-INF/spring/applicationContext-service.xml =================================================================== diff -u -r28623 -r35781 --- ssts-web/src/main/webapp/WEB-INF/spring/applicationContext-service.xml (.../applicationContext-service.xml) (revision 28623) +++ ssts-web/src/main/webapp/WEB-INF/spring/applicationContext-service.xml (.../applicationContext-service.xml) (revision 35781) @@ -776,4 +776,6 @@ + + \ No newline at end of file