点位导入

xuhy

2025-06-20 12aac2ade617f3bb4e40165176d009ab3fa15a52

点位导入

 ruoyi-admin/src/main/java/com/ruoyi/web/controller/api/TLocationController.java

@@ -1,29 +1,46 @@
package com.ruoyi.web.controller.api;


import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.ruoyi.common.annotation.Log;
import com.ruoyi.common.basic.PageInfo;
import com.ruoyi.common.core.domain.R;
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.core.exception.ServiceException;
import com.ruoyi.common.enums.BusinessType;
import com.ruoyi.common.utils.WebUtils;
import com.ruoyi.common.utils.bean.BeanUtils;
import com.ruoyi.system.dto.LocationDTO;
import com.ruoyi.system.importExcel.TLocationImportExcel;
import com.ruoyi.system.model.*;
import com.ruoyi.system.query.LocationListQuery;
import com.ruoyi.system.query.LocationListTaskQuery;
import com.ruoyi.system.service.*;
import com.ruoyi.system.utils.CustomerImportFailedData;
import com.ruoyi.system.vo.system.LocationListTaskVO;
import com.ruoyi.system.vo.system.LocationListVO;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

/**
@@ -34,6 +51,7 @@
 * @author xiaochen
 * @since 2025-05-28
 */
@Slf4j
@Api(tags = "点位管理")
@RestController
@RequestMapping("/t-location")
@@ -191,5 +209,70 @@
        locationService.removeBatchByIds(Arrays.asList(split));
        return R.ok();
    }

    @ApiOperation(value = "点位导入模板下载")
    @GetMapping("/import-template")
    public void importTemplate() {
        List<TLocationImportExcel> locationImportExcels = new ArrayList<>();
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), TLocationImportExcel.class, locationImportExcels);
        HttpServletResponse response = WebUtils.response();
        ServletOutputStream outputStream = null;
        try {
            String fileName = URLEncoder.encode("点位导入模板.xls", "utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("点位导入模板下载失败!");
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @Log(title = "点位导入", businessType = BusinessType.INSERT)
    @ApiOperation(value = "点位导入")
    @Transactional(rollbackFor = Exception.class,propagation = Propagation.REQUIRES_NEW)
    @PostMapping("/importLocation")
    public R<List<CustomerImportFailedData>> importLocation(@RequestPart("file") MultipartFile file) {
        ImportParams params = new ImportParams();
        params.setTitleRows(2); // 标题行数
        params.setHeadRows(1); //表头行数
        InputStream inputStream = null;
        List<CustomerImportFailedData> failedData = new ArrayList<>();
        List<TLocationImportExcel> locationExcelList;
        try {
            inputStream = file.getInputStream();
            locationExcelList = ExcelImportUtil.importExcel(inputStream,
                    TLocationImportExcel.class, params);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("点位导入失败:{}", e.getMessage());
            throw new ServiceException("点位导入失败!");
        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                throw new ServiceException(e.getMessage());
            }
        }
        if (CollectionUtils.isEmpty(locationExcelList)) {
            throw new ServiceException("点位数据为空!");
        }
        for (TLocationImportExcel locationExcel : locationExcelList) {
            System.err.println(locationExcel);
        }
//        locationService.saveOrUpdateBatch(saveOrUpdateList);

        return R.ok(failedData);
    }

}


 ruoyi-admin/src/main/java/com/ruoyi/web/controller/api/TTaskController.java

@@ -1,7 +1,16 @@
package com.ruoyi.web.controller.api;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDateTime;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.hutool.core.io.resource.ClassPathResource;
import com.ruoyi.common.core.domain.BaseModel;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.WebUtils;
import com.ruoyi.framework.web.service.TokenService;
import com.ruoyi.system.dto.TTaskAuditBatchDTO;
import com.ruoyi.system.dto.TTaskAuditDTO;
@@ -21,14 +30,15 @@
import com.ruoyi.system.vo.system.*;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.*;
import java.util.stream.Collectors;

/**
@@ -265,5 +275,98 @@
        return R.ok();
    }

    @Log(title = "任务记录导出", businessType = BusinessType.DELETE)
    @ApiOperation(value = "任务记录导出")
    @PostMapping(value = "/exportExcel")
    public void exportExcel(@RequestBody TaskListQuery query) {
        List<SysUser> sysUsers = sysUserService.selectAllList();
        List<TLocation> locationList = locationService.list();
        if (StringUtils.hasLength(query.getDeptName())){
            List<String> projectIds = projectDeptService.lambdaQuery().like(TProjectDept::getProjectName, query.getDeptName()).list()
                    .stream().map(TProjectDept::getId).collect(Collectors.toList());
            List<String> deptIds = deptService.lambdaQuery().like(TDept::getDeptName, query.getDeptName()).list()
                    .stream().map(TDept::getId).collect(Collectors.toList());
            projectIds.addAll(deptIds);
            if (projectIds.isEmpty()){
                projectIds.add("-1");
            }
            if (StringUtils.hasLength(query.getPhonenumber())){
                List<Long> patrolInspectorIds = sysUsers.stream().filter(sysUser ->
                        sysUser.getPhonenumber().equals(query.getPhonenumber())
                                && projectIds.contains(sysUser.getDeptId())
                ).map(SysUser::getUserId).collect(Collectors.toList());
                if (patrolInspectorIds.isEmpty()){
                    patrolInspectorIds.add(-1L);
                }
                query.setPatrolInspectorIds(patrolInspectorIds);
            }
        }
        if (StringUtils.hasLength(query.getPhonenumber())){
            List<Long> patrolInspectorIds = sysUsers.stream().filter(sysUser ->
                    sysUser.getPhonenumber().equals(query.getPhonenumber())
            ).map(SysUser::getUserId).collect(Collectors.toList());
            if (!query.getPatrolInspectorIds().isEmpty()){
                // 取交集
                patrolInspectorIds = patrolInspectorIds.stream().filter(query.getPatrolInspectorIds()::contains).collect(Collectors.toList());
            }
            query.setPatrolInspectorIds(patrolInspectorIds);
            if (patrolInspectorIds.isEmpty()){
                patrolInspectorIds.add(-1L);
            }
        }
        if (query.getClearStatus()!=null){
            List<String> collect = tTaskDetailService.lambdaQuery().eq(TTaskDetail::getClearStatus, query.getClearStatus())
                    .list().stream().distinct().map(TTaskDetail::getTaskId).collect(Collectors.toList());
            query.setTaskIds(collect);
            if (collect.isEmpty()){
                collect.add("-1");
            }
        }
        if (query.getLocationType()!=null){
            List<String> collect = locationList.stream().filter(e -> e.getLocationType().equals(query.getLocationType())).map(TLocation::getId)
                    .collect(Collectors.toList());
            if (collect.isEmpty()){
                collect.add("-1");
            }
            query.setLocationIds(collect);
        }
        if (StringUtils.hasLength(query.getLocationName())){
            List<String> collect = locationList.stream().filter(e -> e.getLocationName().contains(query.getLocationName())).map(TLocation::getId)
                    .collect(Collectors.toList());
            if (collect.isEmpty()){
                collect.add("-1");
            }else{
                collect = collect.stream().filter(query.getLocationIds()::contains).collect(Collectors.toList());
                if (collect.isEmpty()){
                    collect.add("-1");
                }
                query.setLocationIds(collect);
            }
        }
        List<TaskListVO> list = taskCleanerService.exportList(query);

        //1.获取excel模板
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), TaskListVO.class, list);
        HttpServletResponse response = WebUtils.response();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        ServletOutputStream outputStream = null;
        try {
            String fileName = URLEncoder.encode("任务记录列表.xls", "utf-8");
            response.setHeader("Content-dispodition", "attachment;filename=" + fileName);
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

}


 ruoyi-common/src/main/java/com/ruoyi/common/core/controller/BaseController.java

@@ -190,7 +190,7 @@
    /**
     * 获取登录部门id
     */
    public Long getDeptId()
    public String getDeptId()
    {
        return getLoginUser().getDeptId();
    }

 ruoyi-common/src/main/java/com/ruoyi/common/core/domain/model/LoginUser.java

@@ -24,7 +24,7 @@
    /**
     * 部门ID
     */
    private Long deptId;
    private String deptId;

    /**
     * 用户唯一标识
@@ -81,7 +81,7 @@
        this.permissions = permissions;
    }

    public LoginUser(Long userId, Long deptId, SysUser user, Set<String> permissions)
    public LoginUser(Long userId, String deptId, SysUser user, Set<String> permissions)
    {
        this.userId = userId;
        this.deptId = deptId;
@@ -99,12 +99,12 @@
        this.userId = userId;
    }

    public Long getDeptId()
    public String getDeptId()
    {
        return deptId;
    }

    public void setDeptId(Long deptId)
    public void setDeptId(String deptId)
    {
        this.deptId = deptId;
    }

 ruoyi-common/src/main/java/com/ruoyi/common/utils/SecurityUtils.java

@@ -35,7 +35,7 @@
    /**
     * 获取部门ID
     **/
    public static Long getDeptId()
    public static String getDeptId()
    {
        try
        {

 ruoyi-framework/src/main/java/com/ruoyi/framework/web/service/SysLoginService.java

@@ -219,7 +219,7 @@
            throw new ServiceException("短信验证码错误");
        }
        AsyncManager.me().execute(AsyncFactory.recordLogininfor(username, Constants.LOGIN_SUCCESS, MessageUtils.message("user.login.success")));
        LoginUser loginUser = new LoginUser(user.getUserId(), Long.valueOf(user.getDeptId()), user, permissionService.getMenuPermission(user));
        LoginUser loginUser = new LoginUser(user.getUserId(), user.getDeptId(), user, permissionService.getMenuPermission(user));
        recordLoginInfo(loginUser.getUserId());
        // 生成token
        return loginUser;

 ruoyi-system/src/main/java/com/ruoyi/system/importExcel/TLocationImportExcel.java

New file
@@ -0,0 +1,26 @@
package com.ruoyi.system.importExcel;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;

@Data
@ApiModel(value = "点位导入excel")
public class TLocationImportExcel implements Serializable {

    @ApiModelProperty(value = "项目部编号")
    private String deptCode;
    @ApiModelProperty(value = "项目部名称")
    private String deptName;
    @ApiModelProperty(value = "基层管理员姓名  巡检员")
    private String nickName;
//    @ApiModelProperty(value = "保洁员部门编号")
//    private String nickName;
//    @ApiModelProperty(value = "基层管理员姓名  巡检员")
//    private String nickName;
//    @ApiModelProperty(value = "基层管理员姓名  巡检员")
//    private String nickName;

}

 ruoyi-system/src/main/java/com/ruoyi/system/mapper/TTaskMapper.java

@@ -26,4 +26,11 @@
    List<TaskListVO> pageList(@Param("query")TaskListQuery query, @Param("pageInfo")PageInfo<TaskListVO> pageInfo);

    List<TaskUserListVO> pageListUser(@Param("query")TaskUserListQuery query, @Param("pageInfo")PageInfo<TaskUserListVO> pageInfo);

    /**
     * 导出任务记录
     * @param query
     * @return
     */
    List<TaskListVO> exportList(@Param("query")TaskListQuery query);
}

 ruoyi-system/src/main/java/com/ruoyi/system/model/TTask.java

@@ -1,5 +1,6 @@
package com.ruoyi.system.model;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.baomidou.mybatisplus.annotation.*;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.ruoyi.common.core.domain.BaseModel;
@@ -34,6 +35,7 @@
    @TableField("project_id")
    private String projectId;

    @Excel(name = "任务状态", width = 20,orderNum = "8",replace = {"1_未执行","2_超时","3_待确认","4_待整改","5_整改完成","6_已完成"})
    @ApiModelProperty(value = "任务状态:1未执行、2超时、3待确认、4待整改、5整改完成、6已完成")
    @TableField("status")
    private Integer status;

 ruoyi-system/src/main/java/com/ruoyi/system/service/TTaskCleanService.java

@@ -8,6 +8,8 @@
import com.ruoyi.system.query.TaskListQuery;
import com.ruoyi.system.vo.system.TaskListVO;

import java.util.List;

/**
 * <p>
 * 任务记录 服务类
@@ -23,4 +25,11 @@

    PageInfo<TaskUserListVO> pageListUser(TaskUserListQuery query);

    /**
     * 任务记录导出
     *
     * @param query
     * @return
     */
    List<TaskListVO> exportList(TaskListQuery query);
}

 ruoyi-system/src/main/java/com/ruoyi/system/service/impl/TTaskCleanServiceImpl.java

@@ -4,6 +4,7 @@
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.common.basic.PageInfo;
import com.ruoyi.common.core.domain.BaseModel;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.system.applet.query.TaskUserListQuery;
import com.ruoyi.system.applet.vo.TaskUserListVO;
import com.ruoyi.system.mapper.TDeptMapper;
@@ -69,4 +70,26 @@
        pageInfo.setRecords(list);
        return pageInfo;
    }

    @Override
    public List<TaskListVO> exportList(TaskListQuery query) {
        List<TaskListVO> list = this.baseMapper.exportList(query);

        List<TTaskDetail> tTaskDetails = taskDetailMapper.selectList(new LambdaQueryWrapper<TTaskDetail>()
                .orderByDesc(BaseModel::getCreateTime)
                .in(TTaskDetail::getHandleType,Arrays.asList(1,4)));

        for (TaskListVO taskListVO : list) {
            TDept tDept = deptMapper.selectById(taskListVO.getPatrolInspectorDept());
            taskListVO.setDeptName(tDept.getDeptName());
            TTaskDetail tTaskDetail = tTaskDetails.stream().filter(e -> e.getTaskId().equals(taskListVO.getId())).findFirst().orElse(null);
            if (tTaskDetail!=null){
                taskListVO.setPicture(tTaskDetail.getPicture());
                taskListVO.setClearStatus(tTaskDetail.getClearStatus());
                taskListVO.setFinishTimeStr(DateUtils.localDateTimeToString(tTaskDetail.getCreateTime()));
                taskListVO.setRemark(tTaskDetail.getRemark());
            }
        }
        return list;
    }
}

 ruoyi-system/src/main/java/com/ruoyi/system/utils/CustomerImportFailedData.java

New file
@@ -0,0 +1,20 @@
package com.ruoyi.system.utils;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * @author xiaochen
 * @date 2025/5/30
 */
@Data
@ApiModel(value = "点位导入失败原因")
public class CustomerImportFailedData {

    @ApiModelProperty(value = "行号")
    private Integer rowNum;

    @ApiModelProperty(value = "失败原因")
    private String failedReason;
}

 ruoyi-system/src/main/java/com/ruoyi/system/vo/system/TaskListVO.java

@@ -1,5 +1,7 @@
package com.ruoyi.system.vo.system;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.baomidou.mybatisplus.annotation.TableField;
import com.ruoyi.system.model.TTask;
import com.ruoyi.system.model.TTemplate;
import io.swagger.annotations.ApiModel;
@@ -10,19 +12,31 @@
@ApiModel(value = "任务记录列表分页VO")
public class TaskListVO extends TTask {

    @Excel(name = "点位名称", width = 20,orderNum = "1")
    @ApiModelProperty(value = "点位名称")
    private String locationName;
    @Excel(name = "点位类型", width = 20,orderNum = "2")
    @ApiModelProperty(value = "点位类型名称")
    private String locationTypeName;
    @Excel(name = "所属项目部/部门", width = 20,orderNum = "3")
    @ApiModelProperty(value = "部门名称")
    private String deptName;
    @Excel(name = "巡查员", width = 20,orderNum = "4")
    @ApiModelProperty("巡查员名称")
    private String patrolInspectorName;
    @Excel(name = "联系电话", width = 20,orderNum = "5")
    @ApiModelProperty("电话")
    private String phonenumber;
    @ApiModelProperty("图片 多个逗号拼接")
    private String picture;
    @Excel(name = "清洁情况", width = 20,orderNum = "6",replace = {"1_合格","2_不合格"})
    @ApiModelProperty(value = "清洁情况 1合格2不合格")
    private Integer clearStatus;
    @Excel(name = "备注说明", width = 20,orderNum = "7")
    @ApiModelProperty(value = "备注")
    private String remark;
    @Excel(name = "完成日期", width = 20,orderNum = "9")
    @ApiModelProperty(value = "完成时间")
    private String finishTimeStr;

}

 ruoyi-system/src/main/resources/mapper/system/TTaskMapper.xml

@@ -110,5 +110,40 @@
            and (t1.implement_time between #{query.startTime} and #{query.endTime})
        </if>
    </select>
    <select id="exportList" resultType="com.ruoyi.system.vo.system.TaskListVO">
        select t1.*,t2.location_name as locationName,t3.location_name as locationTypeName,
        t4.nick_name as patrolInspectorName,
        t4.phonenumber as phonenumber
        from t_task t1
        left join t_location t2 on t1.location_id = t2.id
        left join t_location_type t3 on t2.location_type = t3.id
        left join sys_user t4 on t1.patrol_inspector = t4.user_id
        where 1=1
        and t1.`disabled` = ${@com.ruoyi.common.enums.DisabledEnum@NO.getCode()}
        <if test="query.patrolInspectorIds != null and query.patrolInspectorIds.size()>0">
            AND t4.user_id IN
            <foreach collection="query.patrolInspectorIds" separator="," item="id" open="(" close=")">
                #{id}
            </foreach>
        </if>
        <if test="query.taskIds != null and query.taskIds.size()>0">
            AND t1.id IN
            <foreach collection="query.taskIds" separator="," item="id" open="(" close=")">
                #{id}
            </foreach>
        </if>
        <if test="query.locationIds != null and query.locationIds.size()>0">
            AND t1.location_id IN
            <foreach collection="query.locationIds" separator="," item="id" open="(" close=")">
                #{id}
            </foreach>
        </if>
        <if test="query.status != null">
            and t1.status = #{query.status}
        </if>
        <if test="query.startTime != null and startTime != ''">
            and (t1.implement_time between #{query.startTime} and #{query.endTime})
        </if>
    </select>

</mapper>