ycl-pojo/src/main/java/com/ycl/platform/domain/dto/ReportImportDTO.java
New file @@ -0,0 +1,19 @@ package com.ycl.platform.domain.dto; import annotation.Excel; import lombok.Data; /** * @author gonghl * @since 2024/8/23 上午 10:41 */ @Data public class ReportImportDTO { @Excel(name = "设备编码") private String serialNumber; @Excel(name = "点位名称") private String pointName; } ycl-pojo/src/main/java/com/ycl/platform/domain/entity/Report.java
@@ -33,8 +33,8 @@ private Integer peopleId; @ApiModelProperty("点位ID") @TableField("point_id") private Integer pointId; @TableField("serial_number") private String serialNumber; @TableField("report_type") private String reportType; @@ -74,4 +74,8 @@ /** 标识号：可用于查询历史审核记录 */ @TableField("identify") private String identify; /** 导入批次号 */ @TableField("import_batch_number") private String importBatchNumber; } ycl-pojo/src/main/java/com/ycl/platform/domain/form/ReportForm.java
@@ -1,21 +1,20 @@ package com.ycl.platform.domain.form; import com.fasterxml.jackson.annotation.JsonFormat; import com.ycl.system.domain.group.Update; import com.ycl.system.domain.group.Add; import com.ycl.platform.base.AbsForm; import com.ycl.platform.domain.entity.Report; import java.time.LocalDateTime; import java.util.Date; import org.springframework.beans.BeanUtils; import jakarta.validation.constraints.NotBlank; import jakarta.validation.constraints.NotNull; import org.springframework.lang.NonNull; import com.ycl.system.domain.group.Add; import com.ycl.system.domain.group.Update; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import jakarta.validation.constraints.NotBlank; import lombok.Data; import lombok.experimental.Accessors; import org.springframework.beans.BeanUtils; import org.springframework.lang.NonNull; import org.springframework.web.multipart.MultipartFile; import java.util.Date; /** * 报备表单 @@ -32,9 +31,11 @@ private Integer peopleId; @NotNull(message = "点位不能为空", groups = {Add.class, Update.class}) @ApiModelProperty("点位") private Integer pointId; private String pointId; @ApiModelProperty("导入点位") private MultipartFile importPointId; @ApiModelProperty("生效时间") @JsonFormat(pattern = "yyyy-MM-dd") ycl-pojo/src/main/java/com/ycl/platform/domain/query/ReportQuery.java
@@ -1,15 +1,12 @@ package com.ycl.platform.domain.query; import com.ycl.platform.base.AbsQuery; import java.util.Date; import java.util.List; import org.springframework.lang.NonNull; import jakarta.validation.constraints.NotBlank; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.Data; import lombok.experimental.Accessors; import java.util.List; /** * 报备查询 @@ -24,6 +21,8 @@ private String reportType; private String keyword; // private Date beginCreateTime; // // private Date endCreateTime; ycl-pojo/src/main/java/com/ycl/platform/domain/vo/ReportVO.java
@@ -4,16 +4,12 @@ import com.fasterxml.jackson.annotation.JsonFormat; import com.ycl.platform.base.AbsVo; import com.ycl.platform.domain.entity.Report; import java.util.Date; import java.util.List; import java.time.LocalDateTime; import org.springframework.lang.NonNull; import org.springframework.beans.BeanUtils; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.Data; import lombok.experimental.Accessors; import org.springframework.beans.BeanUtils; import org.springframework.lang.NonNull; import java.util.Date; /** * 报备展示 @@ -35,8 +31,9 @@ @Excel(name = "报备人员") private String peopleName; /** 点位ID */ private Integer pointId; /** 设备编码 */ private String serialNumber; @Excel(name = "点位") private String pointName;
@@ -1,28 +1,24 @@ package com.ycl.platform.controller; import com.ycl.platform.domain.entity.Report; import com.ycl.platform.domain.entity.TMonitor; import com.ycl.platform.domain.form.ReportAuditingForm; import com.ycl.platform.domain.vo.ReportVO; import com.ycl.platform.domain.vo.TMonitorVO; import com.ycl.system.domain.group.Update; import com.ycl.system.domain.group.Add; import com.ycl.utils.poi.ExcelUtil; import jakarta.servlet.http.HttpServletResponse; import org.springframework.security.access.prepost.PreAuthorize; import org.springframework.validation.annotation.Validated; import lombok.RequiredArgsConstructor; import java.util.List; import org.springframework.validation.annotation.Validated; import jakarta.validation.constraints.NotEmpty; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import com.ycl.platform.service.ReportService; import com.ycl.system.Result; import com.ycl.platform.domain.form.ReportForm; import com.ycl.platform.domain.query.ReportQuery; import com.ycl.platform.domain.vo.ReportVO; import com.ycl.platform.service.ReportService; import com.ycl.system.Result; import com.ycl.system.domain.group.Add; import com.ycl.system.domain.group.Update; import com.ycl.utils.poi.ExcelUtil; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import jakarta.servlet.http.HttpServletResponse; import jakarta.validation.constraints.NotEmpty; import lombok.RequiredArgsConstructor; import org.springframework.security.access.prepost.PreAuthorize; import org.springframework.validation.annotation.Validated; import org.springframework.web.bind.annotation.*; import java.util.List; /** * 报备 前端控制器 @@ -46,6 +42,20 @@ return reportService.add(form); } @PostMapping("/import") @ApiOperation(value = "导入", notes = "导入") @PreAuthorize("@ss.hasPermi('system:report:add')") public Result importData(ReportForm form) { return reportService.importData(form); } @PostMapping("/importTemplate") @ApiOperation(value = "导入模板", notes = "导入模板") @PreAuthorize("@ss.hasPermi('system:report:add')") public void importTemplate(HttpServletResponse response) { reportService.importTemplate(response); } @PutMapping @ApiOperation(value = "修改", notes = "修改") @PreAuthorize("@ss.hasPermi('system:report:edit')") ycl-server/src/main/java/com/ycl/platform/service/ReportService.java
@@ -1,12 +1,14 @@ package com.ycl.platform.service; import com.ycl.platform.domain.entity.Report; import com.baomidou.mybatisplus.extension.service.IService; import com.ycl.platform.domain.entity.Report; import com.ycl.platform.domain.form.ReportAuditingForm; import com.ycl.platform.domain.vo.ReportVO; import com.ycl.system.Result; import com.ycl.platform.domain.form.ReportForm; import com.ycl.platform.domain.query.ReportQuery; import com.ycl.platform.domain.vo.ReportVO; import com.ycl.system.Result; import jakarta.servlet.http.HttpServletResponse; import java.util.List; /** @@ -24,6 +26,20 @@ */ Result add(ReportForm form); /** * 导入模板 * @param response 结果 */ void importTemplate(HttpServletResponse response); /** * 导入 * @param form 数据 * @return 结果 */ Result importData(ReportForm form); /** * 修改 * @param form ycl-server/src/main/java/com/ycl/platform/service/impl/ReportServiceImpl.java
@@ -4,6 +4,7 @@ import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.ycl.platform.domain.dto.ReportImportDTO; import com.ycl.platform.domain.entity.Report; import com.ycl.platform.domain.entity.YwPeople; import com.ycl.platform.domain.entity.YwPoint; @@ -19,13 +20,18 @@ import com.ycl.platform.service.ReportService; import com.ycl.system.Result; import com.ycl.system.page.PageUtil; import com.ycl.utils.DateUtils; import com.ycl.utils.SecurityUtils; import com.ycl.utils.poi.ExcelUtil; import com.ycl.utils.uuid.IdUtils; import enumeration.general.ErrorTypeEnum; import jakarta.servlet.http.HttpServletResponse; import lombok.RequiredArgsConstructor; import lombok.SneakyThrows; import org.apache.commons.lang3.StringUtils; import org.springframework.beans.BeanUtils; import org.springframework.context.annotation.Bean; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import org.springframework.util.Assert; import org.springframework.util.CollectionUtils; @@ -56,11 +62,16 @@ */ @Override public Result add(ReportForm form) { if (Objects.isNull(form.getPointId())) { throw new RuntimeException("点位不能为空"); } Long userId = SecurityUtils.getUserId(); YwPeople people = new LambdaQueryChainWrapper<>(peopleMapper).eq(YwPeople::getUserId, userId).one(); form.setPeopleId(people.getId()); form.setUnitId(people.getBelongUnit()); Report entity = ReportForm.getEntityByForm(form, null); entity.setSerialNumber(form.getPointId()); entity.setErrorType(String.join(",", form.getErrorType())); entity.setStatus(0); entity.setIdentify(IdUtils.timeAddRandomNO(3)); Date now = new Date(); @@ -68,6 +79,46 @@ entity.setUpdateTime(now); if(baseMapper.insert(entity) > 0) { return Result.ok("添加成功"); } return Result.error("添加失败"); } @Override @SneakyThrows public void importTemplate(HttpServletResponse response) { ExcelUtil<ReportImportDTO> excelUtil = new ExcelUtil<>(ReportImportDTO.class); excelUtil.exportExcel(response, null, "报备导入模板"); } @Override @SneakyThrows @Transactional public Result importData(ReportForm form) { if (Objects.isNull(form.getImportPointId())) { throw new RuntimeException("点位不能为空"); } Long userId = SecurityUtils.getUserId(); YwPeople people = new LambdaQueryChainWrapper<>(peopleMapper).eq(YwPeople::getUserId, userId).one(); form.setPeopleId(people.getId()); form.setUnitId(people.getBelongUnit()); // 读取excel数据 ExcelUtil<ReportImportDTO> excelUtil = new ExcelUtil<>(ReportImportDTO.class); List<ReportImportDTO> list = excelUtil.importExcel(form.getImportPointId().getInputStream()); // 批量插入 ArrayList<Report> reports = new ArrayList<>(); long l = System.currentTimeMillis(); list.forEach( item -> { Report entity = ReportForm.getEntityByForm(form, null); entity.setImportBatchNumber(String.valueOf(l)); entity.setSerialNumber(item.getSerialNumber()); entity.setStatus(0); entity.setIdentify(IdUtils.timeAddRandomNO(3)); entity.setCreateTime(DateUtils.getNowDate()); entity.setUpdateTime(DateUtils.getNowDate()); reports.add(entity); }); if (saveBatch(reports)) { return Result.ok("导入成功"); } return Result.error("添加失败"); } @@ -138,6 +189,7 @@ public Result page(ReportQuery query) { IPage<ReportVO> page = PageUtil.getPage(query, ReportVO.class); baseMapper.page(page, query); page.getRecords().forEach(item -> item.setErrorType(ErrorTypeEnum.getEnumValue(item.getErrorType()))); return Result.ok().data(page).total(page.getTotal()); } @@ -193,7 +245,7 @@ .eq(StringUtils.isNotBlank(query.getReportType()), Report::getReportType, query.getReportType()) // .le(Objects.nonNull(query.getBeginCreateTime()), Report::getBeginCreateTime, query.getBeginCreateTime()) // .ge(Objects.nonNull(query.getEndCreateTime()), Report::getEndCreateTime, query.getEndCreateTime()) .in(!CollectionUtils.isEmpty(query.getPointIdList()), Report::getPointId, query.getPointIdList()) // .in(!CollectionUtils.isEmpty(query.getPointIdList()), Report::getPointId, query.getPointIdList()) .in(!CollectionUtils.isEmpty(query.getPeopleIdList()), Report::getPeopleId, query.getPeopleIdList()) .orderByDesc(Report::getCreateTime) .page(PageUtil.getPage(query, Report.class)); @@ -206,8 +258,8 @@ vo.setUnitName(unit.getUnitName()); YwPeople ywPeople = peopleMapper.selectById(vo.getPeopleId()); vo.setPeopleName(ywPeople.getYwPersonName()); YwPoint ywPoint = ywpointMapper.selectById(vo.getPointId()); vo.setPointName(ywPoint.getPointName()); // YwPoint ywPoint = ywpointMapper.selectById(vo.getPointId()); // vo.setPointName(ywPoint.getPointName()); return vo; } ) ycl-server/src/main/resources/mapper/zgyw/ReportMapper.xml
@@ -9,7 +9,7 @@ <result column="update_time" property="updateTime" /> <result column="unit_id" property="unitId" /> <result column="people_id" property="peopleId" /> <result column="point_id" property="pointId" /> <result column="serial_number" property="serialNumber" /> <result column="auditing_time" property="auditingTime" /> <result column="report_content" property="reportContent" /> <result column="report_materials" property="reportMaterials" /> @@ -23,13 +23,13 @@ t_report r LEFT JOIN t_yw_unit u ON r.unit_id = u.id and u.deleted = 0 LEFT JOIN t_yw_people p ON r.people_id = p.id and p.deleted = 0 LEFT JOIN t_yw_point pt ON r.point_id = pt.id and pt.deleted = 0 INNER JOIN ( LEFT JOIN t_yw_point pt ON r.serial_number = pt.serial_number and pt.deleted = 0 /* INNER JOIN ( SELECT identify,MAX(create_time) AS create_time FROM t_report WHERE deleted = 0 GROUP BY identify ) as rr ON r.create_time = rr.create_time ) as rr ON r.create_time = rr.create_time*/ WHERE r.deleted = 0 <if test="query.reportType != null and query.reportType != ''"> @@ -38,11 +38,8 @@ <if test="query.status != null"> AND r.status = #{query.status} </if> <if test="query.pointId != null and query.pointId != ''"> AND pt.point_name like concat('%', #{query.pointId}, '%') </if> <if test="query.peopleId != null and query.peopleId != ''"> AND p.yw_person_name like concat('%', #{query.peopleId}, '%') <if test="query.keyword != null and query.keyword != ''"> AND (pt.point_name like concat('%', #{query.pointId}, '%') OR p.yw_person_name like concat('%', #{query.peopleId}, '%')) </if> ORDER BY r.update_time DESC </select> @@ -54,7 +51,7 @@ t_report r LEFT JOIN t_yw_unit u ON r.unit_id = u.id and u.deleted = 0 LEFT JOIN t_yw_people p ON r.people_id = p.id and p.deleted = 0 LEFT JOIN t_yw_point pt ON r.point_id = pt.id and pt.deleted = 0 LEFT JOIN t_yw_point pt ON r.serial_number = pt.serial_number and pt.deleted = 0 INNER JOIN ( SELECT identify, create_time FROM t_report @@ -66,17 +63,17 @@ <select id="selectNumberList" resultType="java.lang.String"> SELECT yp.serial_number FROM t_report r LEFT JOIN t_yw_point yp ON r.point_id = yp.id LEFT JOIN t_yw_point yp ON r.serial_number = yp.serial_number WHERE r.status = #{status} AND #{date} between r.begin_create_time and r.end_create_time </select> <select id="checkPointReported" resultType="com.ycl.platform.domain.vo.ReportVO"> SELECT r.id, r.errorType r.id, r.error_type FROM t_report r INNER JOIN t_yw_point yo ON r.point_id = yo.id AND yo.deleted = 0 INNER JOIN t_yw_point yo ON r.serial_number = yo.serial_number AND yo.deleted = 0 INNER JOIN t_work_order wo ON yo.serial_number = wo.serial_number AND wo.serial_number = #{serialNumber} AND wo.deleted = 0 WHERE r.status = 1 ycl-server/src/main/resources/mapper/zgyw/TMonitorMapper.xml
@@ -367,10 +367,9 @@ COUNT(p2.id) AS errorNum, COUNT(p1.id) - COUNT(p2.id) AS normalNum FROM t_monitor LEFT JOIN t_yw_point p1 ON t_monitor.serial_number = p1.serial_number <if test="dataScope == 1"> AND p1.province_tag = 'province' </if> LEFT JOIN t_yw_point p2 ON t_monitor.serial_number = p2.serial_number AND p2.recovery = 1 <if test="dataScope == 1"> AND p2.province_tag = 'province' </if> LEFT JOIN t_yw_point p1 ON t_monitor.serial_number = p1.serial_number <if test="dataScope == 1"> AND p1.province_tag = 'province' </if><if test="deptId != null"> AND p1.dept_id = #{deptId} </if> LEFT JOIN t_yw_point p2 ON t_monitor.serial_number = p2.serial_number AND p2.recovery = 1 AND p1.id = p2.id <if test="dataScope == 1"> AND p2.province_tag = 'province' </if><if test="deptId != null"> AND p2.dept_id = #{deptId} </if> WHERE INSTR(camera_fun_type, 1) <if test="deptId != null"> AND p1.dept_id = #{deptId} AND p2.dept_id = #{deptId} </if> UNION ALL SELECT '车辆' as type, @@ -378,10 +377,9 @@ COUNT(p2.id) AS errorNum, COUNT(p1.id) - COUNT(p2.id) AS normalNum FROM t_monitor LEFT JOIN t_yw_point p1 ON t_monitor.serial_number = p1.serial_number <if test="dataScope == 1"> AND p1.province_tag = 'province' </if> LEFT JOIN t_yw_point p2 ON t_monitor.serial_number = p2.serial_number AND p2.recovery = 1 <if test="dataScope == 1"> AND p2.province_tag = 'province' </if> LEFT JOIN t_yw_point p1 ON t_monitor.serial_number = p1.serial_number <if test="dataScope == 1"> AND p1.province_tag = 'province' </if><if test="deptId != null"> AND p1.dept_id = #{deptId} </if> LEFT JOIN t_yw_point p2 ON t_monitor.serial_number = p2.serial_number AND p2.recovery = 1 AND p1.id = p2.id <if test="dataScope == 1"> AND p2.province_tag = 'province' </if><if test="deptId != null"> AND p2.dept_id = #{deptId} </if> WHERE INSTR(camera_fun_type, 2) <if test="deptId != null"> AND p1.dept_id = #{deptId} AND p2.dept_id = #{deptId} </if> UNION ALL SELECT '人脸' as type, @@ -389,10 +387,9 @@ COUNT(p2.id) AS errorNum, COUNT(p1.id) - COUNT(p2.id) AS normalNum FROM t_monitor LEFT JOIN t_yw_point p1 ON t_monitor.serial_number = p1.serial_number <if test="dataScope == 1"> AND p1.province_tag = 'province' </if> LEFT JOIN t_yw_point p2 ON t_monitor.serial_number = p2.serial_number AND p2.recovery = 1 <if test="dataScope == 1"> AND p2.province_tag = 'province' </if> LEFT JOIN t_yw_point p1 ON t_monitor.serial_number = p1.serial_number <if test="dataScope == 1"> AND p1.province_tag = 'province' </if><if test="deptId != null"> AND p1.dept_id = #{deptId} </if> LEFT JOIN t_yw_point p2 ON t_monitor.serial_number = p2.serial_number AND p2.recovery = 1 AND p1.id = p2.id <if test="dataScope == 1"> AND p2.province_tag = 'province' </if><if test="deptId != null"> AND p2.dept_id = #{deptId} </if> WHERE INSTR(camera_fun_type, 3) <if test="deptId != null"> AND p1.dept_id = #{deptId} AND p2.dept_id = #{deptId} </if> </select> <select id="monitorRate" resultType="com.ycl.platform.domain.vo.screen.MonitorRateVO"> @@ -409,7 +406,7 @@ FROM sys_dept d LEFT JOIN t_yw_point p ON p.dept_id = d.dept_id AND p.deleted = 0 <if test="dataScope == 1"> AND p.province_tag = 'province' </if> LEFT JOIN t_yw_point p2 ON p2.dept_id = d.dept_id AND p2.deleted = 0 AND p2.recovery = 1 <if test="dataScope == 1"> AND p2.province_tag = 'province' </if> LEFT JOIN t_yw_point p2 ON p2.dept_id = d.dept_id AND p2.deleted = 0 AND p2.recovery = 1 AND p.id = p2.id <if test="dataScope == 1"> AND p2.province_tag = 'province' </if> LEFT JOIN t_monitor m ON m.serial_number = p.serial_number AND INSTR(m.camera_fun_type, 1) LEFT JOIN t_monitor m2 ON m2.serial_number = p2.serial_number AND INSTR(m2.camera_fun_type, 1) WHERE d.del_flag = 0 AND d.area IS NOT NULL @@ -427,7 +424,7 @@ FROM sys_dept d LEFT JOIN t_yw_point p ON p.dept_id = d.dept_id AND p.deleted = 0 <if test="dataScope == 1"> AND p.province_tag = 'province' </if> LEFT JOIN t_yw_point p2 ON p2.dept_id = d.dept_id AND p2.deleted = 0 AND p2.recovery = 1 <if test="dataScope == 1"> AND p2.province_tag = 'province' </if> LEFT JOIN t_yw_point p2 ON p2.dept_id = d.dept_id AND p2.deleted = 0 AND p2.recovery = 1 AND p.id = p2.id <if test="dataScope == 1"> AND p2.province_tag = 'province' </if> LEFT JOIN t_monitor m ON m.serial_number = p.serial_number AND INSTR(m.camera_fun_type, 2) LEFT JOIN t_monitor m2 ON m2.serial_number = p2.serial_number AND INSTR(m2.camera_fun_type, 2) WHERE d.del_flag = 0 AND d.area IS NOT NULL @@ -445,7 +442,7 @@ FROM sys_dept d LEFT JOIN t_yw_point p ON p.dept_id = d.dept_id AND p.deleted = 0 <if test="dataScope == 1"> AND p.province_tag = 'province' </if> LEFT JOIN t_yw_point p2 ON p2.dept_id = d.dept_id AND p2.deleted = 0 AND p2.recovery = 1 <if test="dataScope == 1"> AND p2.province_tag = 'province' </if> LEFT JOIN t_yw_point p2 ON p2.dept_id = d.dept_id AND p2.deleted = 0 AND p2.recovery = 1 AND p.id = p2.id <if test="dataScope == 1"> AND p2.province_tag = 'province' </if> LEFT JOIN t_monitor m ON m.serial_number = p.serial_number AND INSTR(m.camera_fun_type, 3) LEFT JOIN t_monitor m2 ON m2.serial_number = p2.serial_number AND INSTR(m2.camera_fun_type, 3) WHERE d.del_flag = 0 AND d.area IS NOT NULL
