Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/InstrumentSetDemandReportHelper.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/InstrumentSetDemandReportHelper.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/InstrumentSetDemandReportHelper.java (revision 41494) @@ -0,0 +1,376 @@ +package com.forgon.disinfectsystem.jasperreports.util; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.Collections; +import java.util.HashMap; +import java.util.HashSet; +import java.util.LinkedHashMap; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.stream.Collectors; + +import org.apache.commons.collections.MapUtils; +import org.apache.commons.collections4.CollectionUtils; +import org.apache.commons.lang.StringUtils; +import org.apache.log4j.Logger; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.stereotype.Component; + +import com.forgon.Constants; +import com.forgon.databaseadapter.service.DateQueryAdapter; +import com.forgon.directory.model.BrancheOfHospital; +import com.forgon.directory.service.BrancheOfHospitalManager; +import com.forgon.disinfectsystem.jasperreports.javabeansource.InstrumentSetDemandReportVo; +import com.forgon.disinfectsystem.jasperreports.service.dataindex.DataIndex; +import com.forgon.tools.hibernate.ObjectDao; +import com.forgon.tools.util.SqlUtils; +/** + * 手术器械包需求量分析报表 + */ +@Component +public class InstrumentSetDemandReportHelper { + private static final Logger logger = Logger.getLogger(InstrumentSetDemandReportHelper.class); + @Autowired + private DateQueryAdapter dateQueryAdapter; + @Autowired + private ObjectDao objectDao; + @Autowired + private DataIndex dataIndex; + @Autowired + private BrancheOfHospitalManager brancheOfHospitalManager; + /** + * 手术器械包需求量分析报表 + */ + public List getInstrumentSetDemandReportData(Map requestParameters, Map parametMap) { + // 1. 参数处理 + String brancheOfHospitalIds = requestParameters.get("brancheOfHospitalIds"); + Set brancheOfHospitalIdSet = getBrancheOfHospitalIds(brancheOfHospitalIds); + boolean filterBrancheOfHospital = CollectionUtils.isNotEmpty(brancheOfHospitalIdSet); + + Map departToBrancheOfHospitalMap = filterBrancheOfHospital?brancheOfHospitalManager.getDepartToBrancheOfHospitalMap(SqlUtils.splitStringToSet(brancheOfHospitalIds, Constants.IDS_SEPARATOR)):null; + + + // 日期处理 + String startDate = requestParameters.get("startDate"); + parametMap.put("title", "手术器械包需求量分析报表("+ startDate +")"); + startDate = StringUtils.isNotBlank(startDate) ? startDate + " 00:00:00" : null; + String endDate = getEndDate(requestParameters); + if(StringUtils.isBlank(startDate) || StringUtils.isBlank(endDate)){ + return null; + } + + // 2. 构建查询SQL + String sql = buildQuerySql(startDate, endDate, requestParameters, brancheOfHospitalIdSet, filterBrancheOfHospital); + + // 3. 执行查询并处理结果 + Map> operationNameMap = new LinkedHashMap<>(); + Map amountMap = new HashMap<>(); + Map> ipIdMap = new HashMap<>(); + String onlyQueryGap = requestParameters.get("onlyQueryGap"); + executeQueryAndProcessResults(sql, operationNameMap, amountMap, ipIdMap, departToBrancheOfHospitalMap); + + // 4. 构建结果列表并排序 + List resultList = buildVoList(operationNameMap, amountMap, ipIdMap, parametMap, onlyQueryGap); + + return sortResultList(resultList); + } + + /** + * 获取院区ID集合 + */ + private Set getBrancheOfHospitalIds(String brancheOfHospitalIds) { + return StringUtils.isNotBlank(brancheOfHospitalIds) ? + SqlUtils.splitLongToSet(brancheOfHospitalIds, Constants.IDS_SEPARATOR, false) : + Collections.emptySet(); + } + + /** + * 获取结束日期 + */ + private String getEndDate(Map requestParameters) { + String endDate = requestParameters.get("endDate"); + return StringUtils.isNotBlank(endDate) ? endDate + " 23:59:59" : null; + } + + /** + * 构建查询SQL + */ + private String buildQuerySql(String startDate, String endDate, Map requestParameters, + Set brancheOfHospitalIdSet, boolean filterBrancheOfHospital) { + + String queryDepart = requestParameters.get("depart"); + String queryTousseName = requestParameters.get("tousseName"); + SqlUtils.checkInputParam(queryDepart); + SqlUtils.checkInputParam(queryTousseName); + StringBuilder sql = new StringBuilder(); + sql.append("SELECT CONVERT(DATE, o.operationTime) operationTime, ip.depart, ti.tousseName, ") + .append("o.operationName, ti.amount, ip.id ipId ") + .append("FROM OperationReservation o ") + .append("JOIN invoicePlan ip ON ip.id = o.id ") + .append("JOIN TousseItem ti ON ti.recyclingApplication_ID = ip.id ") + .append("WHERE o.operationTime ") + .append(String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDate),dateQueryAdapter.dateAdapter(endDate))) + .append("AND o.operationTime IS NOT NULL "); + + // 添加部门条件 + if (StringUtils.isNotBlank(queryDepart)) { + sql.append("AND ip.depart = '"+ queryDepart +"' "); + } + + // 添加器械包名称条件 + if (StringUtils.isNotBlank(queryTousseName)) { + sql.append("AND ti.tousseName = '"+ queryTousseName +"' "); + } + + // 添加院区过滤条件 + if (filterBrancheOfHospital && CollectionUtils.isNotEmpty(brancheOfHospitalIdSet)) { + sql.append("AND EXISTS (") + .append("SELECT 1 FROM BrancheOfHospital bh ") + .append("JOIN OrgUnitGroup oug ON bh.orgUnitGroupId = oug.id ") + .append("JOIN Org_OrgGroup oog ON oog.orgGroupId = oug.id ") + .append("JOIN OrgUnit ou ON ou.id = oog.orgUnitId ") + .append("WHERE ou.name = ip.depart ") + .append(SqlUtils.getInLongListSql("bh.id", brancheOfHospitalIdSet)) + .append(") "); + } + + sql.append("ORDER BY o.operationTime DESC"); + + return sql.toString(); + } + + /** + * 执行查询并处理结果 + */ + private void executeQueryAndProcessResults(String sql, Map> operationNameMap, + Map amountMap, Map> ipIdMap, + Map departToBrancheOfHospitalMap) { + + try (ResultSet rs = objectDao.executeSql(sql)) { + while (rs.next()) { + processSingleRow(rs, operationNameMap, amountMap, ipIdMap, departToBrancheOfHospitalMap); + } + } catch (Exception e) { + e.printStackTrace(); + logger.error("执行器械包需求报表查询失败: " + e.getMessage(), e); + throw new RuntimeException("查询器械包需求报表数据失败", e); + } + } + + /** + * 处理单行结果 + */ + private void processSingleRow(ResultSet rs, Map> operationNameMap, + Map amountMap, Map> ipIdMap, + Map departToBrancheOfHospitalMap) throws SQLException { + + String operationTime = rs.getString("operationTime"); + String depart = rs.getString("depart"); + BrancheOfHospital brancheOfHospital = StringUtils.isNotBlank(depart) && MapUtils.isNotEmpty(departToBrancheOfHospitalMap) ? + departToBrancheOfHospitalMap.get(depart) : null; + String brancheOfHospitalName = brancheOfHospital != null ? brancheOfHospital.getName() : StringUtils.EMPTY; + String tousseName = rs.getString("tousseName"); + String operationName = rs.getString("operationName"); + int amount = rs.getInt("amount"); + Long ipId = rs.getLong("ipId"); + + String groupKey = buildGroupKey(operationTime, depart, brancheOfHospitalName, tousseName); + + // 处理operationName排重 + operationNameMap.computeIfAbsent(groupKey, k -> new HashSet<>()).add(operationName); + + // 汇总amount + amountMap.merge(groupKey, amount, Integer::sum); + + // 处理ipId排重计数 + ipIdMap.computeIfAbsent(groupKey, k -> new HashSet<>()).add(ipId); + } + + /** + * 构建分组键 + */ + private String buildGroupKey(String operationTime, String depart, String brancheOfHospitalName, String tousseName) { + return String.join("|", + StringUtils.defaultString(operationTime, StringUtils.EMPTY), + StringUtils.defaultString(depart, StringUtils.EMPTY), + StringUtils.defaultString(brancheOfHospitalName, StringUtils.EMPTY), + StringUtils.defaultString(tousseName, StringUtils.EMPTY)); + } + +/* *//** + * 构建结果列表 + *//* + private List> buildResultList(Map> operationNameMap, + Map amountMap, + Map> ipIdMap, + Map parametMap) { + + List resultList = new ArrayList<>(); + Integer totalTotalAmount = 0; + Integer totalStockAmount = 0; + Integer totalExpectedGapAmount = 0; + for (String key : operationNameMap.keySet()) { + String[] keyParts = key.split("\\|", -1); + + Map resultMap = new HashMap<>(); + resultMap.put("operationTime", keyParts[0]); + resultMap.put("depart", keyParts[1]); + resultMap.put("brancheOfHospitalName", keyParts[2]); + resultMap.put("tousseName", keyParts[3]); + + // 拼接排重后的operationName + Set operationNames = operationNameMap.get(key); + resultMap.put("operationNames", String.join(", ", operationNames)); + + // 汇总的amount + Integer totalAmount = amountMap.getOrDefault(key, 0); + totalTotalAmount += totalAmount; + resultMap.put("totalAmount", totalAmount); + + // ipId数量（排重后） + resultMap.put("ipCount", ipIdMap.get(key).size()); + + // 库存和缺口计算 + Integer stockAmount = getStockAmount(keyParts[1], keyParts[3]); + totalStockAmount += stockAmount; + resultMap.put("stockAmount", stockAmount); + + if (totalAmount != null && stockAmount != null && totalAmount > stockAmount) { + resultMap.put("expectedGapAmount", totalAmount - stockAmount); + resultMap.put("isUrgent", Constants.STR_YES); + totalExpectedGapAmount += totalAmount - stockAmount; + } else { + resultMap.put("expectedGapAmount", 0); + resultMap.put("isUrgent", Constants.STR_NO); + } + + resultList.add(resultMap); + } + parametMap.put("totalTotalAmount", totalTotalAmount); + parametMap.put("totalStockAmount", totalStockAmount); + parametMap.put("totalExpectedGapAmount", totalExpectedGapAmount); + return resultList; + }*/ + /** + * 构建VO列表 + */ + private List buildVoList(Map> operationNameMap, + Map amountMap, + Map> ipIdMap, + Map parametMap, + String onlyQueryGap) { + + List resultList = new ArrayList<>(); + Integer totalTotalAmount = 0; + Integer totalStockAmount = 0; + Integer totalExpectedGapAmount = 0; + + for (String key : operationNameMap.keySet()) { + String[] keyParts = key.split("\\|", -1); + + // 创建VO对象 + InstrumentSetDemandReportVo vo = new InstrumentSetDemandReportVo(); + vo.setOperationTime(keyParts[0]); + vo.setDepart(keyParts[1]); + vo.setBrancheOfHospitalName(keyParts[2]); + vo.setTousseName(keyParts[3]); + + // 拼接排重后的operationName + Set operationNames = operationNameMap.get(key); + vo.setOperationNames(String.join(", ", operationNames)); + + // 汇总的amount + Integer totalAmount = amountMap.getOrDefault(key, 0); + + vo.setTotalAmount(totalAmount); + + // ipId数量（排重后） + vo.setIpCount(ipIdMap.get(key).size()); + + // 库存和缺口计算 + Integer stockAmount = getStockAmount(keyParts[1], keyParts[3]); + + vo.setStockAmount(stockAmount); + + // 计算缺口和紧急状态 + if (totalAmount != null && stockAmount != null && totalAmount > stockAmount) { + Integer expectedGapAmount = totalAmount - stockAmount; + vo.setExpectedGapAmount(expectedGapAmount); + vo.setIsUrgent(Constants.STR_YES); + totalExpectedGapAmount += expectedGapAmount; + } else { + if(Constants.STR_YES.equals(onlyQueryGap)){ + continue; + } + vo.setExpectedGapAmount(0); + vo.setIsUrgent(Constants.STR_NO); + } + totalTotalAmount += totalAmount; + totalStockAmount += stockAmount; + resultList.add(vo); + } + + // 设置总数统计 + parametMap.put("totalTotalAmount", totalTotalAmount); + parametMap.put("totalStockAmount", totalStockAmount); + parametMap.put("totalExpectedGapAmount", totalExpectedGapAmount); + + return resultList; + } + /** + * 获取库存数量（添加参数化查询） + */ + private Integer getStockAmount(String departName, String tousseName) { + // 使用参数化查询避免SQL注入 + String sql = "SELECT SUM(amount) FROM GoodsStock WHERE 1=1 AND amount > 0 AND goodsType = '器械包' " + + "AND tousseDefinitionId IS NOT NULL " + + "AND orgUnitName = '"+ departName +"' AND name = '"+ tousseName +"' "; + + try { + // 这里需要根据你的objectDao实现参数化查询 + return objectDao.countBySql(sql); + } catch (Exception e) { + logger.error("查询库存数量失败 - depart: {"+ departName +"}, tousseName: {"+ tousseName +"}, error: {"+ e.getMessage() +"}"); + return 0; + } + } + + /** + * 排序结果列表 + * 排序规则：按缺口数量降序，缺口相同时按总需求数量降序 + * + * @param resultList 待排序的结果列表 + * @return 排序后的VO列表 + */ + private List sortResultList(List resultList) { + return resultList.stream() + .sorted((vo1, vo2) -> { + // 按预计缺口数量降序排序（缺口大的排前面） + Integer expectedGapAmount1 = vo1.getExpectedGapAmount(); + Integer expectedGapAmount2 = vo2.getExpectedGapAmount(); + + // 处理null值情况 + if (expectedGapAmount1 == null) expectedGapAmount1 = 0; + if (expectedGapAmount2 == null) expectedGapAmount2 = 0; + + int gapCompare = expectedGapAmount2.compareTo(expectedGapAmount1); + if (gapCompare != 0) { + return gapCompare; + } + + // 缺口数量相同时，按总需求数量降序排序（需求大的排前面） + Integer totalAmount1 = vo1.getTotalAmount(); + Integer totalAmount2 = vo2.getTotalAmount(); + + // 处理null值情况 + if (totalAmount1 == null) totalAmount1 = 0; + if (totalAmount2 == null) totalAmount2 = 0; + + return totalAmount2.compareTo(totalAmount1); + }) + .collect(Collectors.toList()); + } +} Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/action/JasperreportsAction.java =================================================================== diff -u -r41224 -r41494 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/action/JasperreportsAction.java (.../JasperreportsAction.java) (revision 41224) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/action/JasperreportsAction.java (.../JasperreportsAction.java) (revision 41494) @@ -106,6 +106,7 @@ import com.forgon.disinfectsystem.jasperreports.util.DeviceUptimeStatisticsReportHelper; import com.forgon.disinfectsystem.jasperreports.util.ForeignTousseReportHelper; import com.forgon.disinfectsystem.jasperreports.util.InstrumentRepairReportHelper; +import com.forgon.disinfectsystem.jasperreports.util.InstrumentSetDemandReportHelper; import com.forgon.disinfectsystem.jasperreports.util.LineGraphReportOfWorkloadByTimePeriodHelper; import com.forgon.disinfectsystem.jasperreports.util.MonthReportHelper; import com.forgon.disinfectsystem.jasperreports.util.OperationTousseOperationRepartHelper; @@ -224,6 +225,11 @@ TousseWorkLoadHelper tousseWorkLoadHelper) { this.tousseWorkLoadHelper = tousseWorkLoadHelper; } + private InstrumentSetDemandReportHelper instrumentSetDemandReportHelper; + public void setInstrumentSetDemandReportHelper( + InstrumentSetDemandReportHelper instrumentSetDemandReportHelper) { + this.instrumentSetDemandReportHelper = instrumentSetDemandReportHelper; + } private DeviceMaintenanceCusDataSourceHelper deviceMaintenanceCusDataSourceHelper; public void setDeviceMaintenanceCusDataSourceHelper( DeviceMaintenanceCusDataSourceHelper deviceMaintenanceCusDataSourceHelper) { @@ -2061,6 +2067,8 @@ if(StringUtils.isNotBlank(startDay) && StringUtils.isNotBlank(endDay)){ return tousseWorkLoadHelper.getTousseWorkLoadData(customTimePeriodId, instrumentSetTypes, startDay,endDay,departCoding,tousseTypes,operator,isDisableIDCard,taskGroup,tousseGroupName,showAsTousseSplitAmount,sterilizationMode,packageType,applicationDepart, groupIds, queryType, isProxyDisinfection,parametMap); } + }else if("instrumentSetDemandReport".equals(reportName)){ + return instrumentSetDemandReportHelper.getInstrumentSetDemandReportData(requestParameters, parametMap); }else if("departPackingDetailSummaryReport".equals(reportName)){ return departPackingDetailSummaryReportHelper.getDepartPackingDetailSummaryReportDate(requestParameters, parametMap); }else if("cssdNursingQualityControlSummaryReport".equals(reportName)){ Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/InstrumentSetDemandReportVo.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/InstrumentSetDemandReportVo.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/InstrumentSetDemandReportVo.java (revision 41494) @@ -0,0 +1,178 @@ +package com.forgon.disinfectsystem.jasperreports.javabeansource; +/** + * 手术器械包需求量分析报表 + */ +public class InstrumentSetDemandReportVo { + + /** + * 日期 + * 手术时间 + * 格式：YYYY-MM-DD + * 来源：OperationReservation.operationTime + */ + private String operationTime; + + /** + * 科室名称 + * 来源：invoicePlan.depart + */ + private String depart; + + /** + * 院区 + * 来源：BrancheOfHospital.name + * 通过部门与院区关联关系获取 + */ + private String brancheOfHospitalName; + + /** + * 器械包名称 + * 来源：TousseItem.tousseName + */ + private String tousseName; + + /** + * 手术名称列表（排重后拼接） + * 多个手术名称用逗号分隔 + * 来源：OperationReservation.operationName + * 处理逻辑：相同分组键下的手术名称去重后拼接 + */ + private String operationNames; + + /** + * 手术例数(台) + * 统计相同分组键下的不同手术单ID数量 + * 来源：invoicePlan.id + */ + private Integer ipCount; + + /** + * 需求总包数 + * 相同分组键下的器械包需求数量总和 + * 来源：TousseItem.amount + * 计算方式：按operationTime+depart+tousseName分组求和 + */ + private Integer totalAmount; + + /** + * 当前可用库存 + * 当前该科室该器械包的可用库存数量 + * 来源：GoodsStock.amount + * 查询条件：goodsType='器械包', amount>0, orgUnitName=depart, name=tousseName + */ + private Integer stockAmount; + + /** + * 预计缺口数量 + * 当总需求数量大于库存数量时的差额 + * 计算方式：totalAmount > stockAmount ? totalAmount - stockAmount : 0 + */ + private Integer expectedGapAmount; + + /** + * 是否加急 + * 标识该器械包是否存在库存缺口 + * 取值："是" / "否" + * 判断条件：expectedGapAmount > 0 ? "是" : "否" + */ + private String isUrgent; + + public InstrumentSetDemandReportVo() { + } + + public InstrumentSetDemandReportVo(String operationTime, String depart, String brancheOfHospitalName, + String tousseName, String operationNames, Integer ipCount, + Integer totalAmount, Integer stockAmount, Integer expectedGapAmount, + String isUrgent) { + this.operationTime = operationTime; + this.depart = depart; + this.brancheOfHospitalName = brancheOfHospitalName; + this.tousseName = tousseName; + this.operationNames = operationNames; + this.ipCount = ipCount; + this.totalAmount = totalAmount; + this.stockAmount = stockAmount; + this.expectedGapAmount = expectedGapAmount; + this.isUrgent = isUrgent; + } + + public String getOperationTime() { + return operationTime; + } + + public void setOperationTime(String operationTime) { + this.operationTime = operationTime; + } + + public String getDepart() { + return depart; + } + + public void setDepart(String depart) { + this.depart = depart; + } + + public String getBrancheOfHospitalName() { + return brancheOfHospitalName; + } + + public void setBrancheOfHospitalName(String brancheOfHospitalName) { + this.brancheOfHospitalName = brancheOfHospitalName; + } + + public String getTousseName() { + return tousseName; + } + + public void setTousseName(String tousseName) { + this.tousseName = tousseName; + } + + public String getOperationNames() { + return operationNames; + } + + public void setOperationNames(String operationNames) { + this.operationNames = operationNames; + } + + public Integer getIpCount() { + return ipCount; + } + + public void setIpCount(Integer ipCount) { + this.ipCount = ipCount; + } + + public Integer getTotalAmount() { + return totalAmount; + } + + public void setTotalAmount(Integer totalAmount) { + this.totalAmount = totalAmount; + } + + public Integer getStockAmount() { + return stockAmount; + } + + public void setStockAmount(Integer stockAmount) { + this.stockAmount = stockAmount; + } + + public Integer getExpectedGapAmount() { + return expectedGapAmount; + } + + public void setExpectedGapAmount(Integer expectedGapAmount) { + this.expectedGapAmount = expectedGapAmount; + } + + public String getIsUrgent() { + return isUrgent; + } + + public void setIsUrgent(String isUrgent) { + this.isUrgent = isUrgent; + } +} Index: ssts-web/src/main/webapp/jasperRtp/instrumentSetDemandReport.jrxml =================================================================== diff -u --- ssts-web/src/main/webapp/jasperRtp/instrumentSetDemandReport.jrxml (revision 0) +++ ssts-web/src/main/webapp/jasperRtp/instrumentSetDemandReport.jrxml (revision 41494) @@ -0,0 +1,301 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <band height="30" splitType="Stretch"> + <textField> + <reportElement uuid="4dd1a765-4207-4079-8cf6-8b2dc4d91f3b" x="0" y="0" width="1520" height="30"/> + <textElement textAlignment="Center" verticalAlignment="Middle"> + <font size="14" isBold="true"/> + </textElement> + <textFieldExpression><![CDATA[$P{title}]]></textFieldExpression> + </textField> + </band> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Index: ssts-web/src/main/webapp/jasperRtp/instrumentSetDemandReport.jasper =================================================================== diff -u Binary files differ