medicalWaste-admin/src/main/java/com/sinata/web/controller/backend/MwCheckoutRecordController.java
@@ -108,7 +108,7 @@ /** * 运输记录详情 * * @param id * @param query * @return */ @ApiOperation("运输记录详情") medicalWaste-system/src/main/java/com/sinata/system/config/AutoColumnWidthStrategy.java
New file @@ -0,0 +1,49 @@ package com.sinata.system.config; import cn.idev.excel.metadata.Head; import cn.idev.excel.metadata.data.WriteCellData; import cn.idev.excel.write.metadata.holder.WriteSheetHolder; import cn.idev.excel.write.style.column.AbstractColumnWidthStyleStrategy; import org.apache.poi.ss.usermodel.Cell; import java.util.HashMap; import java.util.List; import java.util.Map; public class AutoColumnWidthStrategy extends AbstractColumnWidthStyleStrategy { private final Map<Integer, Map<Integer, Integer>> cache = new HashMap<>(); @Override protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { boolean needSetWidth = isHead || !cellDataList.isEmpty(); if (needSetWidth) { Map<Integer, Integer> maxWidthMap = cache.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>()); int columnWidth = calculateWidth(cellDataList, cell, isHead); if (columnWidth > 0) { columnWidth = Math.min(columnWidth, 255); // 最大支持255字符 if (columnWidth > maxWidthMap.getOrDefault(cell.getColumnIndex(), 0)) { maxWidthMap.put(cell.getColumnIndex(), columnWidth); writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256); } } } } // 计算单元格内容长度（按字节） private int calculateWidth(List<WriteCellData<?>> cellDataList, Cell cell, boolean isHead) { if (isHead) { return cell.getStringCellValue().getBytes().length; } else { WriteCellData<?> cellData = cellDataList.get(0); switch (cellData.getType()) { case STRING: return cellData.getStringValue().getBytes().length; case NUMBER: return cellData.getNumberValue().toString().getBytes().length; default: return -1; } } } } medicalWaste-system/src/main/java/com/sinata/system/domain/vo/MwCheckoutRecordExcelVO.java
@@ -1,5 +1,6 @@ package com.sinata.system.domain.vo; import cn.idev.excel.annotation.ExcelIgnore; import cn.idev.excel.annotation.ExcelProperty; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; @@ -20,38 +21,39 @@ private static final long serialVersionUID = -851971023455890457L; @ApiModelProperty("记录id") @ExcelIgnore private Long id; @ExcelProperty(value = "转运时间", index = 1) @ExcelProperty(value = "转运时间", index = 0) private Date checkoutTime; @ApiModelProperty("医院名称") @ExcelProperty(value = "医院名称", index = 2) @ExcelProperty(value = "医院名称", index = 1) private String hospitalName; @ApiModelProperty("箱数") @ExcelProperty(value = "箱数", index = 3) @ExcelProperty(value = "箱数", index = 2) private Integer boxNum; @ApiModelProperty("袋数") @ExcelProperty(value = "袋数", index = 4) @ExcelProperty(value = "袋数", index = 3) private Integer bagNum; @ApiModelProperty("总重量") @ExcelProperty(value = "重量", index = 5) @ExcelProperty(value = "重量", index = 4) private BigDecimal totalWeight; @ApiModelProperty("司机姓名") @ExcelProperty(value = "司机姓名", index = 6) @ExcelProperty(value = "司机姓名", index = 5) private String driverName; @ApiModelProperty("车牌号") @ExcelProperty(value = "车牌号", index = 7) @ExcelProperty(value = "车牌号", index = 6) private String licensePlateNumber; @ApiModelProperty("转运线路") @ExcelProperty(value = "转运路线", index = 8) @ExcelProperty(value = "转运路线", index = 7) private String routeName; } medicalWaste-system/src/main/java/com/sinata/system/service/biz/StaticsService.java
@@ -6,6 +6,7 @@ import com.sinata.common.core.domain.entity.SysDictData; import com.sinata.common.utils.CollUtils; import com.sinata.common.utils.DateUtils; import com.sinata.system.config.AutoColumnWidthStrategy; import com.sinata.system.domain.MwCollectRecord; import com.sinata.system.domain.MwWarningRecord; import com.sinata.system.domain.SysDepartment; @@ -19,8 +20,6 @@ import com.sinata.system.domain.vo.MwMedicalWasteBoxVO; import com.sinata.system.domain.vo.SysDictDataVO; import com.sinata.system.domain.vo.TransformVO; import com.sinata.system.enums.DepartmentEnum; import com.sinata.system.enums.MedicalWasteStatusEnum; import com.sinata.system.service.ISysDictDataService; import com.sinata.system.service.MwCheckoutRecordService; import com.sinata.system.service.MwCollectRecordService; @@ -82,8 +81,8 @@ } vo.setLegend(wasteTypeList.stream().map(SysDictDataVO::getDictLabel).collect(Collectors.toList())); vo.getLegend().add("小计"); List<MwCollectRecord> collectRecordList = mwCollectRecordService.lambdaQuery().eq(query.getReportType().equals(1), MwCollectRecord::getStatus, MedicalWasteStatusEnum.TEMPORARILY_STORED.getCode()) .ne(query.getReportType().equals(2), MwCollectRecord::getStatus, MedicalWasteStatusEnum.TEMPORARILY_STORED.getCode()) List<MwCollectRecord> collectRecordList = mwCollectRecordService.lambdaQuery()/*.eq(query.getReportType().equals(1), MwCollectRecord::getStatus, MedicalWasteStatusEnum.TEMPORARILY_STORED.getCode()) .ne(query.getReportType().equals(2), MwCollectRecord::getStatus, MedicalWasteStatusEnum.TEMPORARILY_STORED.getCode())*/ .eq(MwCollectRecord::getDepartmentId, query.getDepartmentId()) .between(query.getReportType().equals(1), MwCollectRecord::getCollectTime, query.getStartTime(), query.getEndTime()) .between(query.getReportType().equals(2), MwCollectRecord::getCheckoutTime, query.getStartTime(), query.getEndTime()) @@ -166,6 +165,7 @@ FastExcel.write(response.getOutputStream()) .head(head) .autoCloseStream(Boolean.TRUE) .registerWriteHandler(new AutoColumnWidthStrategy()) .sheet("医院报表") .doWrite(getHospitalReportStaticsData(query)); } @@ -225,40 +225,36 @@ vo.setDepartmentName(department.getDepartmentName()); } SysDepartment region = sysDepartmentService.getDepartmentByParentId(department.getParentId()); List<SysDepartment> hospitalList = sysDepartmentService.lambdaQuery().likeRight(SysDepartment::getTreeCode, region.getTreeCode()).eq(SysDepartment::getOrgType, DepartmentEnum.MEDICAL_INSTITUTION.getCode()).list(); if (CollUtils.isNotEmpty(hospitalList)) { List<TransformVO> checkoutRecordVOList = mwCheckoutRecordService.getCheckoutRecordList(query, region.getTreeCode()); if (CollUtils.isNotEmpty(checkoutRecordVOList)) { //查询医废类型 List<SysDictData> wasteTypeList = sysDictDataService.lambdaQuery().in(SysDictData::getDictCode, query.getWasteTypeCodeList()).orderByDesc(SysDictData::getDictCode).list(); if (CollUtils.isNotEmpty(wasteTypeList)) { vo.setLegend(wasteTypeList.stream().map(SysDictData::getDictLabel).collect(Collectors.toList())); vo.getLegend().add("小计"); List<DepartmentReportItemVO> list = new ArrayList<>(); for (TransformVO transformVO : checkoutRecordVOList) { BigDecimal totalCount = BigDecimal.ZERO; BigDecimal totalWeight = BigDecimal.ZERO; DepartmentReportItemVO departmentReportItemVO = new DepartmentReportItemVO(); departmentReportItemVO.setName(transformVO.getHospitalName()); departmentReportItemVO.setData(new ArrayList<>()); departmentReportItemVO.setDriverName(transformVO.getDriverName()); departmentReportItemVO.setHospitalSignature(ImageToBase64.convertImageToBase64(transformVO.getHospitalSignature())); departmentReportItemVO.setHandoverTime(transformVO.getCheckoutTime()); for (SysDictData sysDictData : wasteTypeList) { BigDecimal count = BigDecimal.valueOf(transformVO.getCollectRecordList().stream().filter(item -> item.getWasteType().equals(sysDictData.getDictCode())).count()); BigDecimal weight = transformVO.getCollectRecordList().stream().filter(item -> item.getWasteType().equals(sysDictData.getDictCode())).map(MwMedicalWasteBoxVO::getTotalWeight).reduce(BigDecimal.ZERO, BigDecimal::add); departmentReportItemVO.getData().add(count); departmentReportItemVO.getData().add(weight); totalCount = totalCount.add(count); totalWeight = totalWeight.add(weight); } departmentReportItemVO.getData().add(totalCount); departmentReportItemVO.getData().add(totalWeight); list.add(departmentReportItemVO); } vo.setList(list); //List<SysDepartment> hospitalList = sysDepartmentService.lambdaQuery().likeRight(SysDepartment::getTreeCode, region.getTreeCode()).eq(SysDepartment::getOrgType, DepartmentEnum.MEDICAL_INSTITUTION.getCode()).list(); List<TransformVO> checkoutRecordVOList = mwCheckoutRecordService.getCheckoutRecordList(query, region.getTreeCode()); //查询医废类型 List<SysDictData> wasteTypeList = sysDictDataService.lambdaQuery().in(SysDictData::getDictCode, query.getWasteTypeCodeList()).orderByDesc(SysDictData::getDictCode).list(); if (CollUtils.isNotEmpty(wasteTypeList)) { vo.setLegend(wasteTypeList.stream().map(SysDictData::getDictLabel).collect(Collectors.toList())); vo.getLegend().add("小计"); List<DepartmentReportItemVO> list = new ArrayList<>(); for (TransformVO transformVO : checkoutRecordVOList) { BigDecimal totalCount = BigDecimal.ZERO; BigDecimal totalWeight = BigDecimal.ZERO; DepartmentReportItemVO departmentReportItemVO = new DepartmentReportItemVO(); departmentReportItemVO.setName(transformVO.getHospitalName()); departmentReportItemVO.setData(new ArrayList<>()); departmentReportItemVO.setDriverName(transformVO.getDriverName()); departmentReportItemVO.setHospitalSignature(ImageToBase64.convertImageToBase64(transformVO.getHospitalSignature())); departmentReportItemVO.setHandoverTime(transformVO.getCheckoutTime()); for (SysDictData sysDictData : wasteTypeList) { BigDecimal count = BigDecimal.valueOf(transformVO.getCollectRecordList().stream().filter(item -> item.getWasteType().equals(sysDictData.getDictCode())).count()); BigDecimal weight = transformVO.getCollectRecordList().stream().filter(item -> item.getWasteType().equals(sysDictData.getDictCode())).map(MwMedicalWasteBoxVO::getTotalWeight).reduce(BigDecimal.ZERO, BigDecimal::add); departmentReportItemVO.getData().add(count); departmentReportItemVO.getData().add(weight); totalCount = totalCount.add(count); totalWeight = totalWeight.add(weight); } departmentReportItemVO.getData().add(totalCount); departmentReportItemVO.getData().add(totalWeight); list.add(departmentReportItemVO); } vo.setList(list); } return vo; } @@ -276,6 +272,7 @@ FastExcel.write(response.getOutputStream()) .head(head) .autoCloseStream(Boolean.TRUE) .registerWriteHandler(new AutoColumnWidthStrategy()) .sheet("转运联单") .doWrite(getTransformReportStaticsData(query)); } @@ -409,6 +406,7 @@ FastExcel.write(response.getOutputStream()) .head(head) .autoCloseStream(Boolean.TRUE) .registerWriteHandler(new AutoColumnWidthStrategy()) .sheet("处置报表") .doWrite(getDisposalReportStaticsData(query)); } @@ -533,6 +531,7 @@ FastExcel.write(response.getOutputStream()) .head(head) .autoCloseStream(Boolean.TRUE) .registerWriteHandler(new AutoColumnWidthStrategy()) .sheet("监管报表") .doWrite(getRegulationReportStaticsData(query)); } medicalWaste-system/src/main/java/com/sinata/system/service/impl/MwCheckoutRecordServiceImpl.java
@@ -9,6 +9,7 @@ import com.sinata.common.utils.CollUtils; import com.sinata.common.utils.DateUtils; import com.sinata.common.utils.StringUtils; import com.sinata.system.config.AutoColumnWidthStrategy; import com.sinata.system.domain.MwCheckoutRecord; import com.sinata.system.domain.dto.CollectTotalUpDto; import com.sinata.system.domain.query.CheckoutRecordQuery; @@ -85,7 +86,10 @@ // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode("转运记录", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); FastExcel.write(response.getOutputStream(), MwCheckoutRecordExcelVO.class).sheet("转运记录").doWrite(mwCheckoutRecordExcelVOS); FastExcel.write(response.getOutputStream(), MwCheckoutRecordExcelVO.class) .registerWriteHandler(new AutoColumnWidthStrategy()) .sheet("转运记录") .doWrite(mwCheckoutRecordExcelVOS); } @Override medicalWaste-system/src/main/java/com/sinata/system/service/impl/MwCollectRecordServiceImpl.java
@@ -5,6 +5,7 @@ import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.sinata.common.entity.PageDTO; import com.sinata.common.utils.StringUtils; import com.sinata.system.config.AutoColumnWidthStrategy; import com.sinata.system.domain.MedicalWasteStaticsVO; import com.sinata.system.domain.MwCollectRecord; import com.sinata.system.domain.dto.CollectCarTotalUpDto; @@ -27,7 +28,6 @@ import java.io.IOException; import java.net.URLEncoder; import java.time.LocalDate; import java.util.Collections; import java.util.List; import java.util.Objects; @@ -130,7 +130,9 @@ // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode("医废收集记录", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); FastExcel.write(response.getOutputStream(), MwCollectRecordVO.class).sheet("医废收集记录").doWrite(vo); FastExcel.write(response.getOutputStream(), MwCollectRecordVO.class) .registerWriteHandler(new AutoColumnWidthStrategy()) .sheet("医废收集记录").doWrite(vo); } /** medicalWaste-system/src/main/java/com/sinata/system/service/impl/MwMicroEquipmentRecordServiceImpl.java
@@ -12,6 +12,7 @@ import com.sinata.common.utils.DateUtils; import com.sinata.common.utils.SecurityUtils; import com.sinata.common.utils.StringUtils; import com.sinata.system.config.AutoColumnWidthStrategy; import com.sinata.system.domain.MwCollectRecord; import com.sinata.system.domain.MwMicroEquipment; import com.sinata.system.domain.MwMicroEquipmentRecord; @@ -118,7 +119,9 @@ // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode("微波设备使用记录", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); FastExcel.write(response.getOutputStream(), MwCollectRecordVO.class).sheet("微波设备使用记录").doWrite(list); FastExcel.write(response.getOutputStream(), MwCollectRecordVO.class) .registerWriteHandler(new AutoColumnWidthStrategy()) .sheet("微波设备使用记录").doWrite(list); } /** @@ -272,6 +275,7 @@ FastExcel.write(response.getOutputStream()) .head(head) .autoCloseStream(Boolean.TRUE) .registerWriteHandler(new AutoColumnWidthStrategy()) .sheet("处置分析报表") .doWrite(getStaticsReportData(query)); } medicalWaste-system/src/main/java/com/sinata/system/service/impl/MwStagingRoomServiceImpl.java
@@ -7,6 +7,7 @@ import com.sinata.common.exception.ServiceException; import com.sinata.common.utils.BeanUtils; import com.sinata.common.utils.StringUtils; import com.sinata.system.config.AutoColumnWidthStrategy; import com.sinata.system.domain.MwStagingRoom; import com.sinata.system.domain.dto.MwStagingRoomDTO; import com.sinata.system.domain.excel.MwCheckoutRecordExcel; @@ -136,7 +137,9 @@ // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode("暂存间入库记录", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); FastExcel.write(response.getOutputStream(), MwStorageRecordVO.class).sheet("暂存间入库记录").doWrite(list); FastExcel.write(response.getOutputStream(), MwStorageRecordVO.class) .registerWriteHandler(new AutoColumnWidthStrategy()) .sheet("暂存间入库记录").doWrite(list); } /** @@ -180,7 +183,10 @@ response.setCharacterEncoding("utf-8"); String fileName = URLEncoder.encode("暂存间出库记录", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); FastExcel.write(response.getOutputStream(), MwCheckoutRecordExcel.class).sheet("暂存间出库记录").doWrite(mwCheckoutRecordExcels); FastExcel.write(response.getOutputStream(), MwCheckoutRecordExcel.class) .registerWriteHandler(new AutoColumnWidthStrategy()) .sheet("暂存间出库记录") .doWrite(mwCheckoutRecordExcels); } @Override medicalWaste-system/src/main/java/com/sinata/system/service/impl/MwTransitRouteServiceImpl.java
@@ -5,11 +5,14 @@ import com.sinata.common.entity.PageDTO; import com.sinata.common.exception.ServiceException; import com.sinata.common.utils.BeanUtils; import com.sinata.common.utils.CollUtils; import com.sinata.common.utils.StringUtils; import com.sinata.system.domain.MwTransitRoute; import com.sinata.system.domain.MwTransitRouteCar; import com.sinata.system.domain.MwTransitRoutePoints; import com.sinata.system.domain.SysDepartment; import com.sinata.system.domain.dto.MwTransitRouteDTO; import com.sinata.system.domain.dto.MwTransitRoutePointsDTO; import com.sinata.system.domain.query.MwTransitRouteQuery; import com.sinata.system.domain.vo.MwTransitRouteVO; import com.sinata.system.mapper.MwTransitRouteMapper; @@ -83,6 +86,8 @@ @Override @Transactional(rollbackFor = Exception.class) public void add(MwTransitRouteDTO dto) { //校验医院是否有关联线路 validateHospitalUnique(dto); MwTransitRoute mwTransitRoute = BeanUtils.copyBean(dto, MwTransitRoute.class); mwTransitRoute.setHospitalQuantity(dto.getHostpitalList().size()); save(mwTransitRoute); @@ -100,6 +105,19 @@ mwTransitRouteCarService.saveBatch(mwTransitRouteCars); } private void validateHospitalUnique(MwTransitRouteDTO dto) { //医院id列表 List<Long> departmentIdList = dto.getHostpitalList().stream().map(MwTransitRoutePointsDTO::getDepartmentId).collect(Collectors.toList()); List<MwTransitRoutePoints> list = mwTransitRoutePointsService.lambdaQuery() .in(MwTransitRoutePoints::getDepartmentId, departmentIdList) .ne(Objects.nonNull(dto.getId()), MwTransitRoutePoints::getRouteId, dto.getId()) .list(); if (CollUtils.isNotEmpty(list)) { SysDepartment department = sysDepartmentService.getById(list.get(0).getDepartmentId()); throw new ServiceException(department.getDepartmentName() + "已关联转运线路！"); } } /** * 编辑 * @@ -112,6 +130,7 @@ if (Objects.isNull(dto.getId())) { throw new ServiceException("线路id不能为空"); } validateHospitalUnique(dto); MwTransitRoute mwTransitRoute = BeanUtils.copyBean(dto, MwTransitRoute.class); mwTransitRoute.setHospitalQuantity(dto.getHostpitalList().size()); updateById(mwTransitRoute); medicalWaste-system/src/main/java/com/sinata/system/service/impl/MwWarningRecordServiceImpl.java
@@ -6,6 +6,7 @@ import com.sinata.common.entity.PageDTO; import com.sinata.common.utils.SecurityUtils; import com.sinata.common.utils.StringUtils; import com.sinata.system.config.AutoColumnWidthStrategy; import com.sinata.system.domain.MwWarningRecord; import com.sinata.system.domain.query.MwWarningRecordQuery; import com.sinata.system.domain.vo.MwWarningRecordVO; @@ -75,6 +76,7 @@ response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); FastExcel.write(response.getOutputStream(), MwWarningRecordVO.class) .registerWriteHandler(new AutoColumnWidthStrategy()) .sheet("预警信息") .doWrite(list); } medicalWaste-system/src/main/resources/mapper/system/MwCheckoutRecordMapper.xml
@@ -46,18 +46,19 @@ SELECT mcr.ID, mcr.CHECKOUT_TIME, mcr.HOSPITAL_NAME, SUM(mcr.BOX_NUM) AS boxNum, SUM(mcr.BAG_NUM) AS bagNum, SUM(mcr.TOTAL_WEIGHT) AS totalWeight, mcr.BOX_NUM, mcr.BAG_NUM, mcr.TOTAL_WEIGHT, mcr.DEPARTMENT_ID, su.NICK_NAME AS driverName, mtc.LICENSE_PLATE_NUMBER mtc.LICENSE_PLATE_NUMBER, mtr.ROUTE_NAME FROM MW_CHECKOUT_RECORD mcr LEFT JOIN SYS_USER su ON mcr.DRIVER_ID = su.USER_ID LEFT JOIN MW_TRANSIT_CAR mtc ON mcr.CAR_ID = mtc.ID LEFT JOIN SYS_DEPARTMENT sd ON mcr.DEPARTMENT_ID = sd.ID LEFT JOIN SYS_USER su ON mcr.DRIVER_ID = su.USER_ID LEFT JOIN MW_TRANSIT_CAR MTC ON mcr.CAR_ID = mtc.ID LEFT JOIN MW_TRANSIT_ROUTE_POINTS mtrp ON mcr.DEPARTMENT_ID = mtrp.DEPARTMENT_ID LEFT JOIN MW_TRANSIT_ROUTE mtr ON mtrp.ROUTE_ID = mtr.ID LEFT JOIN SYS_DEPARTMENT sd ON mcr.DEPARTMENT_ID = sd.ID <where> mcr.DEL_FLAG = 0 <if test="treeCode != null and treeCode != ''"> @@ -74,216 +75,204 @@ SELECT mcr.ID, mcr.CHECKOUT_TIME, mcr.HOSPITAL_NAME, SUM(mcr.BOX_NUM) AS boxNum, SUM(mcr.BAG_NUM) AS bagNum, SUM(mcr.TOTAL_WEIGHT) AS totalWeight, mcr.BOX_NUM, mcr.BAG_NUM, mcr.TOTAL_WEIGHT, mcr.DEPARTMENT_ID, su.NICK_NAME AS driverName, mtc.LICENSE_PLATE_NUMBER mtc.LICENSE_PLATE_NUMBER, mtr.ROUTE_NAME FROM MW_CHECKOUT_RECORD mcr LEFT JOIN SYS_USER su ON mcr.DRIVER_ID = su.USER_ID LEFT JOIN MW_TRANSIT_CAR mtc ON mcr.CAR_ID = mtc.ID LEFT JOIN SYS_DEPARTMENT sd ON mcr.DEPARTMENT_ID = sd.ID LEFT JOIN SYS_USER su ON mcr.DRIVER_ID = su.USER_ID LEFT JOIN MW_TRANSIT_CAR MTC ON mcr.CAR_ID = mtc.ID LEFT JOIN MW_TRANSIT_ROUTE_POINTS mtrp ON mcr.DEPARTMENT_ID = mtrp.DEPARTMENT_ID LEFT JOIN MW_TRANSIT_ROUTE mtr ON mtrp.ROUTE_ID = mtr.ID LEFT JOIN SYS_DEPARTMENT sd ON mcr.DEPARTMENT_ID = sd.ID <where> mcr.ID = #{id} </where> GROUP BY mcr.ID </select> <select id="hospitalDetailPage" resultType="com.sinata.system.domain.vo.MwMedicalWasteBoxVO"> SELECT mcr2.BOX_ID AS id, mcr2.BOX_NUMBER, mcr2.WASTE_TYPE_STR, su.NICK_NAME AS COLLECT_USER_NAME, mcr1.BAG_NUM AS bagNum, mcr1.TOTAL_WEIGHT, (SELECT MAX(mcr_inner.BOX_TIME) FROM MW_COLLECT_RECORD mcr_inner WHERE mcr_inner.BOX_ID = mcr2.BOX_ID) AS BOX_TIME FROM MW_CHECKOUT_RECORD mcr1 LEFT JOIN MW_CHECKOUT_RECORD_ITEM mcri ON mcr1.ID = mcri.CHECKOUT_RECORD_ID LEFT JOIN MW_COLLECT_RECORD mcr2 ON mcri.COLLECT_RECORD_ID = mcr2.ID LEFT JOIN SYS_USER su ON mcr2.COLLECT_USER_ID = su.USER_ID LEFT JOIN SYS_DEPARTMENT sd ON su.DEPARTMENT_ID = sd.ID SELECT MCR.BOX_NUMBER, MCR.WASTE_TYPE_STR, COUNT(MCR.ID) AS BAG_NUM, SUM(MCR.WEIGHT) AS TOTAL_WEIGHT, SU.NICK_NAME AS COLLECT_USER_NAME, MAX(MCR.BOX_TIME) AS BOX_TIME FROM MW_CHECKOUT_RECORD_ITEM MCRI LEFT JOIN MW_COLLECT_RECORD MCR ON MCRI.COLLECT_RECORD_ID = MCR.ID LEFT JOIN SYS_USER SU ON MCR.COLLECT_USER_ID = SU.USER_ID <where> mcr1.DEL_FLAG = 0 MCR.DEL_FLAG = 0 <if test="departmentId != null and departmentId != ''"> AND mcr1.ID = #{departmentId} AND MCRI.CHECKOUT_RECORD_ID = #{departmentId} </if> </where> GROUP BY mcr2.BOX_ID ORDER BY mcr2.CREATE_TIME DESC GROUP BY MCR.BOX_NUMBER ORDER BY MCR.CREATE_TIME DESC </select> <select id="transitPageList" resultType="com.sinata.system.domain.vo.MwTransitRecordVO"> SELECT ID, CAR_ID, DEPARTMENT_NAME, LICENSE_PLATE_NUMBER, NICK_NAME, SUM(WEIGHT) AS weight, COUNT(COLLECT_RECORD_ID) AS bagNum, RECEIVE_TIME AS endTime, transitDate, STATUS, DEL_FLAG, MIN(BOX_TIME) AS startTime, COUNT(DISTINCT BOX_NUMBER) AS boxNum FROM (SELECT DISTINCT MTR.ID, MCR2.CAR_ID, SD.DEPARTMENT_NAME, MTC.LICENSE_PLATE_NUMBER, SU.NICK_NAME, MCR2.BOX_NUM, MCR.BOX_TIME, MCR2.TOTAL_WEIGHT, MDR.RECEIVE_TIME, MCR.STATUS, SELECT MTR.ID, MCR.CAR_ID, MAX(SD.DEPARTMENT_NAME) AS DEPARTMENT_NAME, MAX(MTC.LICENSE_PLATE_NUMBER) AS LICENSE_PLATE_NUMBER, MAX(SU.NICK_NAME) AS NICK_NAME, SUM(MCR.WEIGHT) AS weight, COUNT(DISTINCT MCR.ID) AS bagNum, MAX(MDR.RECEIVE_TIME) AS endTime, TRUNC(MCR2.CHECKOUT_TIME) AS transitDate, SD.TREE_CODE, MCR.ID AS COLLECT_RECORD_ID, MCR.WEIGHT, MCR2.DEL_FLAG, MCR.BOX_NUMBER MAX(MCR.STATUS) AS STATUS, MAX(MCR2.DEL_FLAG) AS DEL_FLAG, MIN(MCR.BOX_TIME) AS startTime, COUNT(DISTINCT MCR.BOX_NUMBER) AS boxNum FROM MW_CHECKOUT_RECORD MCR2 LEFT JOIN MW_CHECKOUT_RECORD_ITEM MCRI INNER JOIN MW_CHECKOUT_RECORD_ITEM MCRI ON MCRI.CHECKOUT_RECORD_ID = MCR2.ID LEFT JOIN MW_COLLECT_RECORD MCR AND MCR2.DEL_FLAG = 0 INNER JOIN MW_COLLECT_RECORD MCR ON MCR.ID = MCRI.COLLECT_RECORD_ID LEFT JOIN MW_DISPOSAL_RECORD_ITEM MDRI ON MDRI.COLLECT_RECORD_ID = MCRI.COLLECT_RECORD_ID LEFT JOIN MW_DISPOSAL_RECORD MDR ON MDR.ID = MDRI.DISPOSAL_RECORD_ID LEFT JOIN MW_TRANSIT_CAR MTC ON MCR.CAR_ID = MTC.ID LEFT JOIN SYS_USER SU ON MCR.DRIVER_ID = SU.USER_ID LEFT JOIN SYS_DEPARTMENT SD ON SD.ID = MDR.DEPARTMENT_ID LEFT JOIN (SELECT * FROM MW_TRANSIT_ROUTE GROUP BY DEPARTMENT_ID) MTR ON MTR.DEPARTMENT_ID = MDR.DEPARTMENT_ID) AS T LEFT JOIN MW_DISPOSAL_RECORD_ITEM MDRI ON MDRI.COLLECT_RECORD_ID = MCR.ID LEFT JOIN MW_DISPOSAL_RECORD MDR ON MDR.ID = MDRI.DISPOSAL_RECORD_ID LEFT JOIN MW_TRANSIT_CAR MTC ON MTC.ID = MCR.CAR_ID LEFT JOIN SYS_USER SU ON SU.USER_ID = MCR.DRIVER_ID LEFT JOIN SYS_DEPARTMENT SD ON SD.ID = MDR.DEPARTMENT_ID LEFT JOIN ( SELECT DEPARTMENT_ID, MIN(ID) AS ID FROM MW_TRANSIT_ROUTE GROUP BY DEPARTMENT_ID ) MTR ON MTR.DEPARTMENT_ID = SD.ID <where> T.DEL_FLAG = 0 <if test="treeCode != null and treeCode != null"> AND T.TREE_CODE LIKE CONCAT(#{treeCode},'%') <if test="treeCode != null"> AND SD.TREE_CODE LIKE CONCAT(#{treeCode},'%') </if> <if test="query.licensePlateNumber != null and query.licensePlateNumber != ''"> AND T.LICENSE_PLATE_NUMBER LIKE CONCAT('%',#{query.licensePlateNumber},'%') <if test="query.licensePlateNumber != null"> AND MTC.LICENSE_PLATE_NUMBER LIKE CONCAT('%',#{query.licensePlateNumber},'%') </if> <if test="query.status != null and (query.status == 3 or query.status == 4)"> AND T.STATUS in(3,4) AND MCR.STATUS IN (3,4) </if> <if test="query.startTimeStart != null and query.startTimeEnd != null"> AND T.BOX_TIME BETWEEN #{query.startTimeStart} AND #{query.startTimeEnd} AND MCR.BOX_TIME BETWEEN #{query.startTimeStart} AND #{query.startTimeEnd} </if> <if test="query.endTimeStart != null and query.endTimeEnd != null"> AND T.RECEIVE_TIME BETWEEN #{query.endTimeStart} AND #{query.endTimeEnd} AND MDR.RECEIVE_TIME BETWEEN #{query.endTimeStart} AND #{query.endTimeEnd} </if> </where> GROUP BY T.transitDate, T.ID, T.CAR_ID, T.NICK_NAME ORDER BY T.transitDate DESC GROUP BY TRUNC(MCR2.CHECKOUT_TIME), MTR.ID, MCR.CAR_ID, SU.NICK_NAME ORDER BY transitDate DESC </select> <select id="transitDetail" resultType="com.sinata.system.domain.vo.MwTransitRecordVO"> SELECT ID, CAR_ID, DEPARTMENT_NAME, LICENSE_PLATE_NUMBER, NICK_NAME, SUM(WEIGHT) AS weight, COUNT(COLLECT_RECORD_ID) AS bagNum, RECEIVE_TIME AS endTime, transitDate, STATUS FROM (SELECT DISTINCT MTR.ID, MCR2.CAR_ID, SD.DEPARTMENT_NAME, MTC.LICENSE_PLATE_NUMBER, SU.NICK_NAME, MCR2.BOX_NUM, MCR.BOX_TIME, MCR2.TOTAL_WEIGHT, MDR.RECEIVE_TIME, MCR.STATUS, SELECT MTR.ID, MCR.CAR_ID, MAX(SD.DEPARTMENT_NAME) AS DEPARTMENT_NAME, MAX(MTC.LICENSE_PLATE_NUMBER) AS LICENSE_PLATE_NUMBER, MAX(SU.NICK_NAME) AS NICK_NAME, SUM(MCR.WEIGHT) AS weight, COUNT(DISTINCT MCR.ID) AS bagNum, MAX(MDR.RECEIVE_TIME) AS endTime, TRUNC(MCR2.CHECKOUT_TIME) AS transitDate, SD.TREE_CODE, MCR.ID AS COLLECT_RECORD_ID, MCR.WEIGHT MAX(MCR.STATUS) AS STATUS, MAX(MCR2.DEL_FLAG) AS DEL_FLAG, MIN(MCR.BOX_TIME) AS startTime, COUNT(DISTINCT MCR.BOX_NUMBER) AS boxNum FROM MW_CHECKOUT_RECORD MCR2 LEFT JOIN MW_CHECKOUT_RECORD_ITEM MCRI INNER JOIN MW_CHECKOUT_RECORD_ITEM MCRI ON MCRI.CHECKOUT_RECORD_ID = MCR2.ID LEFT JOIN MW_COLLECT_RECORD MCR AND MCR2.DEL_FLAG = 0 INNER JOIN MW_COLLECT_RECORD MCR ON MCR.ID = MCRI.COLLECT_RECORD_ID LEFT JOIN MW_DISPOSAL_RECORD_ITEM MDRI ON MDRI.COLLECT_RECORD_ID = MCRI.COLLECT_RECORD_ID LEFT JOIN MW_DISPOSAL_RECORD MDR ON MDR.ID = MDRI.DISPOSAL_RECORD_ID LEFT JOIN MW_TRANSIT_CAR MTC ON MCR.CAR_ID = MTC.ID LEFT JOIN SYS_USER SU ON MCR.DRIVER_ID = SU.USER_ID LEFT JOIN SYS_DEPARTMENT SD ON SD.ID = MDR.DEPARTMENT_ID LEFT JOIN (SELECT * FROM MW_TRANSIT_ROUTE GROUP BY DEPARTMENT_ID) MTR ON MTR.DEPARTMENT_ID = MDR.DEPARTMENT_ID) AS T LEFT JOIN MW_DISPOSAL_RECORD_ITEM MDRI ON MDRI.COLLECT_RECORD_ID = MCR.ID LEFT JOIN MW_DISPOSAL_RECORD MDR ON MDR.ID = MDRI.DISPOSAL_RECORD_ID LEFT JOIN MW_TRANSIT_CAR MTC ON MTC.ID = MCR.CAR_ID LEFT JOIN SYS_USER SU ON SU.USER_ID = MCR.DRIVER_ID LEFT JOIN SYS_DEPARTMENT SD ON SD.ID = MDR.DEPARTMENT_ID LEFT JOIN ( SELECT DEPARTMENT_ID, MIN(ID) AS ID FROM MW_TRANSIT_ROUTE GROUP BY DEPARTMENT_ID ) MTR ON MTR.DEPARTMENT_ID = SD.ID <where> T.ID = #{query.id} AND T.CAR_ID = #{query.carId} AND T.transitDate = #{query.transitDate} MTR.ID = #{query.id} AND MCR.CAR_ID = #{query.carId} AND TRUNC(MCR2.CHECKOUT_TIME) = #{query.transitDate} </where> </select> <select id="transitDetailPageList" resultType="com.sinata.system.domain.vo.MwMedicalWasteBoxVO"> SELECT ID, CAR_ID, DEPARTMENT_NAME, LICENSE_PLATE_NUMBER, NICK_NAME, COUNT(COLLECT_RECORD_ID) AS bagNum, SUM(WEIGHT) AS totalWeight, RECEIVE_TIME AS endTime, transitDate, STATUS,CHECK_OUT_ID, HOSPITAL_NAME, BOX_NUMBER, WASTE_TYPE_STR FROM (SELECT DISTINCT MTR.ID, MCR2.CAR_ID, SD.DEPARTMENT_NAME, MTC.LICENSE_PLATE_NUMBER, SU.NICK_NAME, MCR.BOX_TIME, MDR.RECEIVE_TIME, MCR.STATUS, SELECT MTR.ID, MCR.CAR_ID, MAX(SD.DEPARTMENT_NAME) AS DEPARTMENT_NAME, MAX(MTC.LICENSE_PLATE_NUMBER) AS LICENSE_PLATE_NUMBER, MAX(SU.NICK_NAME) AS NICK_NAME, COUNT(DISTINCT MCR.ID) AS bagNum, SUM(MCR.WEIGHT) AS totalWeight, MAX(MDR.RECEIVE_TIME) AS endTime, TRUNC(MCR2.CHECKOUT_TIME) AS transitDate, SD.TREE_CODE, MAX(MCR.STATUS) AS STATUS, MCR2.ID AS CHECK_OUT_ID, MCR.ID AS COLLECT_RECORD_ID, MCR.WEIGHT, MCR.HOSPITAL_NAME, MCR.BOX_NUMBER, MCR.WASTE_TYPE_STR MAX(MCR.HOSPITAL_NAME) AS HOSPITAL_NAME, COUNT(DISTINCT MCR.BOX_NUMBER) AS BOX_NUMBER, MAX(MCR.WASTE_TYPE_STR) AS WASTE_TYPE_STR FROM MW_CHECKOUT_RECORD MCR2 LEFT JOIN MW_CHECKOUT_RECORD_ITEM MCRI INNER JOIN MW_CHECKOUT_RECORD_ITEM MCRI ON MCRI.CHECKOUT_RECORD_ID = MCR2.ID LEFT JOIN MW_COLLECT_RECORD MCR INNER JOIN MW_COLLECT_RECORD MCR ON MCR.ID = MCRI.COLLECT_RECORD_ID LEFT JOIN MW_DISPOSAL_RECORD_ITEM MDRI ON MDRI.COLLECT_RECORD_ID = MCRI.COLLECT_RECORD_ID LEFT JOIN MW_DISPOSAL_RECORD MDR ON MDR.ID = MDRI.DISPOSAL_RECORD_ID LEFT JOIN MW_TRANSIT_CAR MTC ON MCR.CAR_ID = MTC.ID LEFT JOIN SYS_USER SU ON MCR.DRIVER_ID = SU.USER_ID LEFT JOIN SYS_DEPARTMENT SD ON SD.ID = MDR.DEPARTMENT_ID LEFT JOIN (SELECT * FROM MW_TRANSIT_ROUTE GROUP BY DEPARTMENT_ID) MTR ON MTR.DEPARTMENT_ID = MDR.DEPARTMENT_ID) AS T LEFT JOIN MW_DISPOSAL_RECORD_ITEM MDRI ON MDRI.COLLECT_RECORD_ID = MCR.ID LEFT JOIN MW_DISPOSAL_RECORD MDR ON MDR.ID = MDRI.DISPOSAL_RECORD_ID LEFT JOIN MW_TRANSIT_CAR MTC ON MTC.ID = MCR.CAR_ID LEFT JOIN SYS_USER SU ON SU.USER_ID = MCR.DRIVER_ID LEFT JOIN SYS_DEPARTMENT SD ON SD.ID = MDR.DEPARTMENT_ID LEFT JOIN MW_TRANSIT_ROUTE MTR ON MTR.DEPARTMENT_ID = SD.ID <where> T.ID = #{query.id} AND T.CAR_ID = #{query.carId} AND T.transitDate = #{query.transitDate} MTR.ID = #{query.id} AND MCR.CAR_ID = #{query.carId} AND TRUNC(MCR2.CHECKOUT_TIME) = #{query.transitDate} </where> GROUP BY MTR.ID, MCR.CAR_ID, TRUNC(MCR2.CHECKOUT_TIME), MCR2.ID </select> <select id="hospitalTransitList" resultType="com.sinata.system.domain.vo.MwCheckoutRecordVO"> SELECT mcr.ID, mcr.CHECKOUT_TIME, mcr.HOSPITAL_NAME, SUM(mcr.BOX_NUM) AS boxNum, SUM(mcr.BAG_NUM) AS bagNum, SUM(mcr.TOTAL_WEIGHT) AS totalWeight, mcr.BOX_NUM, mcr.BAG_NUM, mcr.TOTAL_WEIGHT, mcr.DEPARTMENT_ID, su.NICK_NAME AS driverName, mtc.LICENSE_PLATE_NUMBER mtc.LICENSE_PLATE_NUMBER, mtr.ROUTE_NAME FROM MW_CHECKOUT_RECORD mcr LEFT JOIN SYS_USER su ON mcr.DRIVER_ID = su.USER_ID LEFT JOIN MW_TRANSIT_CAR mtc ON mcr.CAR_ID = mtc.ID LEFT JOIN SYS_DEPARTMENT sd ON mcr.DEPARTMENT_ID = sd.ID LEFT JOIN SYS_USER su ON mcr.DRIVER_ID = su.USER_ID LEFT JOIN MW_TRANSIT_CAR MTC ON mcr.CAR_ID = mtc.ID LEFT JOIN MW_TRANSIT_ROUTE_POINTS mtrp ON mcr.DEPARTMENT_ID = mtrp.DEPARTMENT_ID LEFT JOIN MW_TRANSIT_ROUTE mtr ON mtrp.ROUTE_ID = mtr.ID LEFT JOIN SYS_DEPARTMENT sd ON mcr.DEPARTMENT_ID = sd.ID <where> mcr.DEL_FLAG = 0 <if test="treeCode != null and treeCode != ''"> @@ -292,14 +281,9 @@ <if test="query.checkoutTimeStart != null and query.checkoutTimeEnd!=null"> AND mcr.CHECKOUT_TIME BETWEEN #{query.checkoutTimeStart} AND #{query.checkoutTimeEnd} </if> <if test="query.ids != null and ids.size() > 0"> AND mcr.ID IN <foreach collection="query.ids" item="id" separator="," open="(" close=")"> #{id} </foreach> </if> </where> GROUP BY mcr.DEPARTMENT_ID GROUP BY mcr.ID ORDER BY mcr.CHECKOUT_TIME DESC </select> <select id="totalUp" resultType="com.sinata.system.domain.dto.CollectTotalUpDto"> SELECT WASTE_TYPE as wasteType,WASTE_TYPE_STR as wasteTypeStr,COUNT(DISTINCT BOX_NUMBER ) as boxNum ,COUNT(1)as num,sum(WEIGHT) as weight from MEDICAL_WASTE.MW_COLLECT_RECORD @@ -384,8 +368,8 @@ LEFT JOIN MW_DISPOSAL_RECORD_ITEM MDRI ON MDRI.COLLECT_RECORD_ID = MCR.ID LEFT JOIN MW_DISPOSAL_RECORD MDR ON MDR.ID = MDRI.DISPOSAL_RECORD_ID <where> <if test="query.departmentId != null"> AND MDR.DEPARTMENT_ID = #{query.departmentId} <if test="treeCode != null and treeCode !=''"> AND SD.TREE_CODE LIKE CONCAT(#{treeCode},'%') </if> <if test="query.startTime != null and query.endTime != null"> AND MCR.CHECKOUT_TIME BETWEEN #{query.startTime} AND #{query.endTime} medicalWaste-system/src/main/resources/mapper/system/MwCollectRecordMapper.xml
@@ -148,7 +148,7 @@ LEFT JOIN MW_DISPOSAL_HANDLE_RECORD_ITEM mdhri ON mdhri.COLLECT_RECORD_ID= mcr.ID LEFT JOIN MW_DISPOSAL_HANDLE_RECORD mdhr ON mdhr.ID= mdhri.DISPOSAL_HANDLE_RECORD_ID LEFT JOIN SYS_USER su3 ON su3.USER_ID = mdr.RECEIVER_ID LEFT JOIN SYS_USER su4 ON su4.USER_ID = mdr.DISPOSAL_USER_ID LEFT JOIN SYS_USER su4 ON su4.USER_ID = mdhr.DISPOSAL_USER_ID LEFT JOIN MW_TRANSIT_CAR mtc ON mtc.ID = mcr2.CAR_ID <where> mcr.ID = #{id} @@ -346,7 +346,7 @@ <where> MCR.DEL_FLAG = 0 <if test="treeCode != null and treeCode != ''"> and SD.TREE_CODE = #{treeCode} and SD.TREE_CODE LIKE CONCAT(#{treeCode},'%') </if> <if test="query.startTime != null and query.endTime != null"> and MCR.COLLECT_TIME between #{query.startTime} and #{query.endTime}
