update

liujie

2026-07-13 edccaca25b8b6bdb582c60cbd2983bc05a4e1ff3

update

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

@@ -17,6 +17,7 @@
import com.ruoyi.system.dto.SysEmployeeUnbindRequest;
import com.ruoyi.system.mapper.SysDoorDeviceMapper;
import com.ruoyi.system.service.ISysEmployeeService;
import com.ruoyi.system.vo.SysEmployeeVO;
import com.ruoyi.web.controller.tool.HikvisionAccessControlApi;
import com.ruoyi.web.controller.tool.MiddlePlatformDataQuery;
import org.apache.poi.ss.usermodel.Cell;
@@ -40,9 +41,11 @@
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 员工管理接口
@@ -177,7 +180,11 @@
    @PostMapping("/delete")
    public AjaxResult delete(@RequestBody SysEmployeeDeleteRequest request)
    {
        return toAjax(employeeService.delete(request, getUsername()));
        List<SysEmployeeVO> employees = loadDeleteEmployees(request);
        Map<String, Object> hikvisionDelete = deleteEmployeesFromHikvision(employees);
        AjaxResult ajax = toAjax(employeeService.delete(request, getUsername()));
        ajax.put("hikvisionDelete", hikvisionDelete);
        return ajax;
    }

    /**
@@ -232,6 +239,133 @@
    /**
     * 目的：中台查询异常时返回统一业务错误，避免异常栈直接暴露给前端。
     */
    private List<SysEmployeeVO> loadDeleteEmployees(SysEmployeeDeleteRequest request)
    {
        if (request == null || request.getEmployeeIds() == null || request.getEmployeeIds().isEmpty())
        {
            throw new ServiceException("请选择员工");
        }

        List<SysEmployeeVO> employees = new ArrayList<SysEmployeeVO>();
        Set<Long> uniqueEmployeeIds = new HashSet<Long>();
        for (Long employeeId : request.getEmployeeIds())
        {
            if (employeeId == null || employeeId <= 0)
            {
                throw new ServiceException("员工主键不能为空");
            }
            if (!uniqueEmployeeIds.add(employeeId))
            {
                continue;
            }
            SysEmployeeDetailRequest detailRequest = new SysEmployeeDetailRequest();
            detailRequest.setEmployeeId(employeeId);
            employees.add(employeeService.detail(detailRequest));
        }
        return employees;
    }

    private Map<String, Object> deleteEmployeesFromHikvision(List<SysEmployeeVO> employees)
    {
        Map<String, Object> result = new LinkedHashMap<String, Object>();
        result.put("employees", employees.size());

        List<SysDoorDevice> devices = doorDeviceMapper.selectEnabledHikvisionDevices();
        result.put("total", devices.size());
        if (devices.isEmpty())
        {
            result.put("status", "skipped");
            result.put("message", "未配置启用的海康门禁设备");
            return result;
        }

        String username = readHikvisionConfig("hikvision.access.username", "HIKVISION_ACCESS_USERNAME", "admin");
        String password = readHikvisionConfig("hikvision.access.password", "HIKVISION_ACCESS_PASSWORD", "");
        if (StringUtils.isEmpty(password))
        {
            result.put("status", "skipped");
            result.put("message", "未配置海康门禁设备密码");
            handleHikvisionDeleteFailure(result);
            return result;
        }

        List<Map<String, Object>> records = new ArrayList<Map<String, Object>>();
        int successCount = 0;
        int totalCount = devices.size() * employees.size();
        for (SysDoorDevice device : devices)
        {
            for (SysEmployeeVO employee : employees)
            {
                Map<String, Object> record = deleteEmployeeFromHikvisionDevice(device, employee, username, password);
                records.add(record);
                if (Boolean.TRUE.equals(record.get("success")))
                {
                    successCount++;
                }
            }
        }
        result.put("success", successCount);
        result.put("failed", totalCount - successCount);
        result.put("records", records);
        result.put("status", successCount == totalCount ? "success" : (successCount == 0 ? "failed" : "partial"));
        handleHikvisionDeleteFailure(result);
        return result;
    }

    private Map<String, Object> deleteEmployeeFromHikvisionDevice(SysDoorDevice device, SysEmployeeVO employee,
                                                                  String username, String password)
    {
        Map<String, Object> record = new LinkedHashMap<String, Object>();
        record.put("doorId", device.getDoorId());
        record.put("deviceName", device.getDeviceName());
        record.put("deviceId", device.getDeviceId());
        record.put("employeeId", employee.getEmployeeId());
        record.put("employeeNo", employee.getEmployeeNo());
        record.put("cardNo", employee.getCardNo());
        try
        {
            HikvisionAccessControlApi.DeviceConfig deviceConfig = HikvisionAccessControlApi.DeviceConfig
                    .http(device.getDeviceId(), username, password);
            deviceConfig.setPort(readHikvisionIntConfig("hikvision.access.port", "HIKVISION_ACCESS_PORT", 80));
            deviceConfig.setHttps(readHikvisionBooleanConfig("hikvision.access.https", "HIKVISION_ACCESS_HTTPS", false));

            HikvisionAccessControlApi.DeleteResult deleteResult = HikvisionAccessControlApi.deleteEmployee(
                    deviceConfig, buildHikvisionDeleteEmployee(employee));
            boolean success = isHikvisionDeleteSuccess(deleteResult);
            record.put("success", success);
            record.put("result", deleteResult);
            if (!success)
            {
                record.put("message", "海康门禁员工删除部分失败");
            }
        }
        catch (Exception e)
        {
            record.put("success", false);
            record.put("message", e.getMessage());
        }
        return record;
    }

    private HikvisionAccessControlApi.EmployeeCredential buildHikvisionDeleteEmployee(SysEmployeeVO employee)
    {
        return HikvisionAccessControlApi.EmployeeCredential.of(
                employee.getEmployeeNo(), employee.getEmployeeName(), employee.getCardNo());
    }

    private boolean isHikvisionDeleteSuccess(HikvisionAccessControlApi.DeleteResult result)
    {
        return result != null
                && isHikvisionResultSuccess(result.getUserInfo())
                && isHikvisionResultSuccess(result.getCard())
                && isHikvisionResultSuccess(result.getFace());
    }

    private boolean isHikvisionResultSuccess(HikvisionAccessControlApi.HikvisionResult result)
    {
        return result == null || result.isSuccess();
    }

    private Map<String, Object> issueEmployeeToHikvision(SysEmployeeAddRequest request)
    {
        Map<String, Object> result = new LinkedHashMap<String, Object>();
@@ -428,6 +562,18 @@
        }
    }

    private void handleHikvisionDeleteFailure(Map<String, Object> result)
    {
        if (!readHikvisionBooleanConfig("hikvision.access.fail-on-error", "HIKVISION_ACCESS_FAIL_ON_ERROR", false))
        {
            return;
        }
        if ("failed".equals(result.get("status")) || "partial".equals(result.get("status")))
        {
            throw new ServiceException("海康门禁员工删除失败");
        }
    }

    private AjaxResult middlePlatformError(IOException e)
    {
        return error("中台数据查询失败：" + e.getMessage());

 ruoyi-admin/src/main/java/com/ruoyi/web/controller/tool/HikvisionAccessControlApi.java

@@ -33,6 +33,8 @@
    private static final String USER_INFO_SET_UP_PATH = "/ISAPI/AccessControl/UserInfo/SetUp?format=json";
    private static final String CARD_INFO_SET_UP_PATH = "/ISAPI/AccessControl/CardInfo/SetUp?format=json";
    private static final String FACE_DATA_RECORD_PATH = "/ISAPI/Intelligent/FDLib/FaceDataRecord?format=json";
    private static final String USER_INFO_DELETE_PATH = "/ISAPI/AccessControl/UserInfo/Delete?format=json";
    private static final String CARD_INFO_DELETE_PATH = "/ISAPI/AccessControl/CardInfo/Delete?format=json";
    private static final MediaType JSON_MEDIA_TYPE = MediaType.parse("application/json; charset=utf-8");
    private static final MediaType JPEG_MEDIA_TYPE = MediaType.parse("image/jpeg");
    private static final MediaType PNG_MEDIA_TYPE = MediaType.parse("image/png");
@@ -66,6 +68,25 @@
            result.setFace(face);
            ensureSuccess(face, "绑定员工人脸失败");
        }
        return result;
    }

    /**
     * 删除门禁人员，并尽量清理绑定卡和人脸凭据。
     */
    public static DeleteResult deleteEmployee(DeviceConfig device, EmployeeCredential employee) throws IOException {
        Objects.requireNonNull(employee, "employee");
        requireText(employee.getEmployeeNo(), "employeeNo");

        DeleteResult result = new DeleteResult();
        if (isNotBlank(employee.getCardNo())) {
            result.setCard(deleteCard(device, employee.getCardNo()));
        }
        result.setFace(deleteFace(device, employee.getEmployeeNo()));

        HikvisionResult userInfo = deleteUserInfo(device, employee.getEmployeeNo());
        result.setUserInfo(userInfo);
        ensureSuccess(userInfo, "delete user info failed");
        return result;
    }

@@ -152,6 +173,53 @@
    }

    /**
     * 删除设备端人员基础信息。
     */
    public static HikvisionResult deleteUserInfo(DeviceConfig device, String employeeNo) throws IOException {
        requireDevice(device);
        requireText(employeeNo, "employeeNo");

        JSONObject employeeNoItem = new JSONObject(true);
        employeeNoItem.put("employeeNo", employeeNo);

        JSONObject userInfoDelCond = new JSONObject(true);
        userInfoDelCond.put("EmployeeNoList", singletonList(employeeNoItem));

        JSONObject body = new JSONObject(true);
        body.put("UserInfoDelCond", userInfoDelCond);
        return executeJson(device, "DELETE", USER_INFO_DELETE_PATH, body);
    }

    public static HikvisionResult deleteCard(DeviceConfig device, String cardNo) throws IOException {
        requireDevice(device);
        requireText(cardNo, "cardNo");

        JSONObject cardNoItem = new JSONObject(true);
        cardNoItem.put("cardNo", cardNo);

        JSONObject cardInfoDelCond = new JSONObject(true);
        cardInfoDelCond.put("CardNoList", singletonList(cardNoItem));

        JSONObject body = new JSONObject(true);
        body.put("CardInfoDelCond", cardInfoDelCond);
        return executeJson(device, "DELETE", CARD_INFO_DELETE_PATH, body);
    }

    public static HikvisionResult deleteFace(DeviceConfig device, String employeeNo) throws IOException {
        requireDevice(device);
        requireText(employeeNo, "employeeNo");

        JSONObject faceData = new JSONObject(true);
        faceData.put("faceLibType", "blackFD");
        faceData.put("FDID", "1");
        faceData.put("FPID", employeeNo);

        JSONObject body = new JSONObject(true);
        body.put("FaceDataRecord", faceData);
        return executeJson(device, "DELETE", FACE_DATA_RECORD_PATH, body);
    }

    /**
     * 通过图片 URL 下载人脸并绑定到员工。
     */
    public static HikvisionResult bindFaceByUrl(DeviceConfig device, String employeeNo, String faceImageUrl) throws IOException {
@@ -179,6 +247,8 @@
                .header("Accept", "application/json");
        if ("POST".equals(method)) {
            builder.post(requestBody);
        } else if ("DELETE".equals(method)) {
            builder.delete(requestBody);
        } else {
            builder.put(requestBody);
        }
@@ -668,6 +738,36 @@
        }
    }

    public static final class DeleteResult {
        private HikvisionResult userInfo;
        private HikvisionResult card;
        private HikvisionResult face;

        public HikvisionResult getUserInfo() {
            return userInfo;
        }

        public void setUserInfo(HikvisionResult userInfo) {
            this.userInfo = userInfo;
        }

        public HikvisionResult getCard() {
            return card;
        }

        public void setCard(HikvisionResult card) {
            this.card = card;
        }

        public HikvisionResult getFace() {
            return face;
        }

        public void setFace(HikvisionResult face) {
            this.face = face;
        }
    }

    public static final class IssueResult {
        private HikvisionResult userInfo;
        private HikvisionResult card;

 ruoyi-applet/pom.xml

@@ -71,6 +71,12 @@
<!--            </exclusions>-->
        </dependency>

        <!-- 系统模块-->
        <dependency>
            <groupId>com.ruoyi</groupId>
            <artifactId>ruoyi-system</artifactId>
        </dependency>

        <!-- 定时任务-->
        <dependency>
            <groupId>com.ruoyi</groupId>

 ruoyi-applet/src/main/java/com/ruoyi/web/controller/api/SysApiDashboardController.java

New file
@@ -0,0 +1,52 @@
package com.ruoyi.web.controller.api;

import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.system.dto.SysDashboardOverviewRequest;
import com.ruoyi.system.service.ISysDashboardService;
import com.ruoyi.system.vo.SysDashboardOverviewVO;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiResponse;
import io.swagger.annotations.ApiResponses;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * 首页数据总览接口
 *
 * @author ruoyi
 */
@RestController
@RequestMapping("/api/dashboard")
@Api(tags = "首页数据总览")
public class SysApiDashboardController extends BaseController
{
    @Autowired
    private ISysDashboardService dashboardService;

    /**
     * 查询首页数据总览
     */
    @ApiOperation(
            value = "查询首页数据总览",
            notes = "返回线上预约、现场登记、访客状态和进出场趋势统计数据",
            response = SysDashboardOverviewVO.class
    )
    @ApiResponses({
            @ApiResponse(
                    code = 200,
                    message = "成功",
                    response = AjaxResult.class,
                    responseContainer = "Data"
            )
    })
    @PostMapping("/overview")
    public AjaxResult<SysDashboardOverviewVO> overview(@RequestBody(required = false) SysDashboardOverviewRequest request)
    {
        return success(dashboardService.overview(request));
    }
}

 ruoyi-applet/src/main/java/com/ruoyi/web/controller/api/SysApiVisitorRecordController.java

@@ -83,7 +83,7 @@
    }


    @PostMapping("/add")
    @PostMapping("/add1")
    @ApiOperation(value = "身份证签离", notes = "身份证签离")
    public AjaxResult idCardOut(
            @RequestBody

 ruoyi-applet/src/main/java/com/ruoyi/web/controller/api/SysApiVisitorRecordManageController.java

New file
@@ -0,0 +1,78 @@
package com.ruoyi.web.controller.api;

import com.ruoyi.common.basic.PageInfo;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.system.dto.SysVisitorRecordDetailRequest;
import com.ruoyi.system.dto.SysVisitorRecordPageRequest;
import com.ruoyi.system.service.ISysVisitorRecordService;
import com.ruoyi.system.vo.SysVisitorRecordDetailVO;
import com.ruoyi.system.vo.SysVisitorRecordVO;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiResponse;
import io.swagger.annotations.ApiResponses;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * 管理侧访客记录接口
 *
 * @author ruoyi
 */
@RestController
@RequestMapping("/api/visitorRecord")
@Api(tags = "管理侧访客记录")
public class SysApiVisitorRecordManageController extends BaseController
{
    @Autowired
    private ISysVisitorRecordService visitorRecordService;

    /**
     * 分页查询访客记录
     */
    @ApiOperation(
            value = "分页查询访客记录",
            notes = "按访客、被访人、部门、访问区域、来访事由、有效期筛选访客记录",
            response = SysVisitorRecordVO.class,
            responseContainer = "Page"
    )
    @ApiResponses({
            @ApiResponse(
                    code = 200,
                    message = "成功",
                    response = AjaxResult.class,
                    responseContainer = "Page"
            )
    })
    @PostMapping("/page")
    public AjaxResult<PageInfo<SysVisitorRecordVO>> page(@RequestBody(required = false) SysVisitorRecordPageRequest request)
    {
        return success(visitorRecordService.pageList(request));
    }

    /**
     * 查询访客记录详情
     */
    @ApiOperation(
            value = "查询访客记录详情",
            notes = "返回访客信息、被访者信息、访问权限、签离记录和通行明细",
            response = SysVisitorRecordDetailVO.class
    )
    @ApiResponses({
            @ApiResponse(
                    code = 200,
                    message = "成功",
                    response = AjaxResult.class,
                    responseContainer = "Data"
            )
    })
    @PostMapping("/detail")
    public AjaxResult<SysVisitorRecordDetailVO> detail(@RequestBody SysVisitorRecordDetailRequest request)
    {
        return success(visitorRecordService.detail(request));
    }
}

 ruoyi-system/src/main/java/com/ruoyi/system/dto/SysDashboardOverviewRequest.java

@@ -1,6 +1,7 @@
package com.ruoyi.system.dto;

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

@@ -12,19 +13,20 @@
 * @author ruoyi
 */
@Data
@ApiModel(value = "SysDashboardOverviewRequest", description = "首页数据总览查询请求")
public class SysDashboardOverviewRequest
{
    /** 统计周期：TODAY今日 RECENT_7_DAYS最近7天 RECENT_30_DAYS最近30天 */
    @ApiModelProperty("统计周期：TODAY今日 RECENT_7_DAYS最近7天 RECENT_30_DAYS最近30天")
    @ApiModelProperty(value = "统计周期：TODAY今日，RECENT_7_DAYS最近7天，RECENT_30_DAYS最近30天；为空默认最近7天", example = "RECENT_7_DAYS")
    private String periodType;

    /** 自定义开始时间 */
    @ApiModelProperty("自定义开始时间")
    @ApiModelProperty(value = "自定义开始时间；beginTime 与 endTime 同时传入且 endTime 晚于 beginTime 时生效", example = "2025-06-01 00:00:00")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date beginTime;

    /** 自定义结束时间 */
    @ApiModelProperty("自定义结束时间")
    @ApiModelProperty(value = "自定义结束时间；统计区间为左闭右开，即包含 beginTime，不包含 endTime", example = "2025-07-01 00:00:00")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date endTime;
}

 ruoyi-system/src/main/java/com/ruoyi/system/dto/SysVisitorRecordDetailRequest.java

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

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

@@ -9,9 +10,10 @@
 * @author ruoyi
 */
@Data
@ApiModel(value = "SysVisitorRecordDetailRequest", description = "管理侧访客记录详情查询请求")
public class SysVisitorRecordDetailRequest
{
    /** 访客登记记录主键 */
    @ApiModelProperty("访客登记记录主键")
    @ApiModelProperty(value = "访客登记记录主键", required = true, example = "1")
    private Long recordId;
}

 ruoyi-system/src/main/java/com/ruoyi/system/dto/SysVisitorRecordPageRequest.java

@@ -2,6 +2,7 @@

import com.fasterxml.jackson.annotation.JsonFormat;
import com.ruoyi.common.core.domain.BasePage;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
@@ -15,43 +16,56 @@
 */
@Data
@EqualsAndHashCode(callSuper = true)
@ApiModel(value = "SysVisitorRecordPageRequest", description = "管理侧访客记录分页查询请求")
public class SysVisitorRecordPageRequest extends BasePage
{
    /** 被访人 */
    @ApiModelProperty("被访人")
    @ApiModelProperty(value = "被访人姓名，支持模糊查询", example = "李维")
    private String intervieweeName;

    /** 被访人关键字：姓名 */
    @ApiModelProperty(value = "被访人关键字，等同于被访人姓名模糊查询", example = "李")
    private String intervieweeKeyword;

    /** 被访人部门 */
    @ApiModelProperty("被访人部门")
    @ApiModelProperty(value = "被访人部门名称", example = "安全部")
    private String intervieweeDept;

    /** 访问区域ID */
    @ApiModelProperty("访问区域ID")
    @ApiModelProperty(value = "访问区域ID，按区域树节点筛选", example = "1001")
    private Long areaId;

    /** 访问区域关键字：区域/楼层 */
    @ApiModelProperty(value = "访问区域关键字，支持区域名称或楼层模糊查询", example = "A区域")
    private String visitAreaKeyword;

    /** 有效期开始时间 */
    @ApiModelProperty("有效期开始时间")
    @ApiModelProperty(value = "来访时间范围开始时间，筛选与有效期有交集的记录", example = "2025-06-01 00:00:00")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date validStartTime;

    /** 有效期结束时间 */
    @ApiModelProperty("有效期结束时间")
    @ApiModelProperty(value = "来访时间范围结束时间，筛选与有效期有交集的记录", example = "2025-06-30 23:59:59")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date validEndTime;

    /** 状态 */
    @ApiModelProperty("状态")
    @ApiModelProperty(value = "访客状态：VISITING来访中，SIGNED_OUT已签离，PENDING_CONFIRM逾期滞留，OVERDUE_AUTO_SIGNED_OUT超期自动，CANCELLED已取消", example = "VISITING")
    private String status;

    /** 访客 */
    @ApiModelProperty("访客")
    @ApiModelProperty(value = "访客姓名，支持模糊查询", example = "王丽白")
    private String visitorName;

    /** 访客关键字：姓名/证件号 */
    @ApiModelProperty(value = "访客关键字，支持访客姓名或证件号模糊查询", example = "510151")
    private String visitorKeyword;

    /** 来访事由 */
    @ApiModelProperty("来访事由")
    @ApiModelProperty(value = "来访事由", example = "商务洽谈")
    private String visitReason;

    /** 登记类型 */
    @ApiModelProperty("登记类型")
    @ApiModelProperty(value = "登记类型：APPOINTMENT预约登记，ONSITE现场登记", example = "APPOINTMENT")
    private String registerType;
}

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

@@ -10,6 +10,7 @@
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.Date;
import java.util.List;

/**
@@ -27,6 +28,10 @@

    public List<SysVisitorPassRecordVO> selectPassRecordsByRecordId(Long recordId);

    public int updateExpiredToAutoSignedOut(@Param("now") Date now);

    public int updateVisitingToOverdue(@Param("now") Date now);

    public int deleteByIds(@Param("recordIds") List<Long> recordIds, @Param("updateBy") String updateBy);

    public int insert(SysVisitorRecord record);

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

@@ -18,5 +18,7 @@

    public SysVisitorRecordDetailVO detail(SysVisitorRecordDetailRequest request);

    public int refreshVisitorStatus();

    public int delete(SysVisitorRecordDeleteRequest request, String username);
}

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

@@ -5,6 +5,7 @@
import com.ruoyi.system.dto.SysDashboardOverviewRequest;
import com.ruoyi.system.mapper.SysDashboardMapper;
import com.ruoyi.system.service.ISysDashboardService;
import com.ruoyi.system.service.ISysVisitorRecordService;
import com.ruoyi.system.vo.SysDashboardOverviewVO;
import com.ruoyi.system.vo.SysDashboardRegisterSummaryVO;
import com.ruoyi.system.vo.SysDashboardStatusSummaryVO;
@@ -44,9 +45,13 @@
    @Autowired
    private SysDashboardMapper dashboardMapper;

    @Autowired
    private ISysVisitorRecordService visitorRecordService;

    @Override
    public SysDashboardOverviewVO overview(SysDashboardOverviewRequest request)
    {
        visitorRecordService.refreshVisitorStatus();
        DateRange range = resolveDateRange(request);
        SysDashboardOverviewVO overview = new SysDashboardOverviewVO();
        overview.setPeriodType(range.getPeriodType());

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

@@ -2,6 +2,7 @@

import com.ruoyi.common.basic.PageInfo;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.system.dto.SysVisitorRecordDeleteRequest;
import com.ruoyi.system.dto.SysVisitorRecordDetailRequest;
@@ -12,8 +13,14 @@
import com.ruoyi.system.vo.SysVisitorRecordVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.Date;
import java.util.ArrayList;
import java.util.List;

/**
@@ -30,11 +37,15 @@
    @Override
    public PageInfo<SysVisitorRecordVO> pageList(SysVisitorRecordPageRequest request)
    {
        refreshVisitorStatus();
        SysVisitorRecordPageRequest query = request == null ? new SysVisitorRecordPageRequest() : request;
        query.setIntervieweeName(StringUtils.trim(query.getIntervieweeName()));
        query.setIntervieweeKeyword(StringUtils.trim(query.getIntervieweeKeyword()));
        query.setIntervieweeDept(StringUtils.trim(query.getIntervieweeDept()));
        query.setVisitAreaKeyword(StringUtils.trim(query.getVisitAreaKeyword()));
        query.setStatus(StringUtils.trim(query.getStatus()));
        query.setVisitorName(StringUtils.trim(query.getVisitorName()));
        query.setVisitorKeyword(StringUtils.trim(query.getVisitorKeyword()));
        query.setVisitReason(StringUtils.trim(query.getVisitReason()));
        query.setRegisterType(StringUtils.trim(query.getRegisterType()));
        PageInfo<SysVisitorRecordVO> pageInfo = new PageInfo<SysVisitorRecordVO>(query.getPageNum(), query.getPageSize());
@@ -46,6 +57,7 @@
    @Override
    public SysVisitorRecordDetailVO detail(SysVisitorRecordDetailRequest request)
    {
        refreshVisitorStatus();
        Long recordId = getRequiredRecordId(request == null ? null : request.getRecordId());
        SysVisitorRecordDetailVO detail = visitorRecordMapper.selectDetailById(recordId);
        if (detail == null)
@@ -53,7 +65,18 @@
            throw new ServiceException("访客登记记录不存在");
        }
        detail.setPassRecords(visitorRecordMapper.selectPassRecordsByRecordId(recordId));
        fillDetailDisplayFields(detail);
        return detail;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int refreshVisitorStatus()
    {
        Date now = new Date();
        int autoSignedOutRows = visitorRecordMapper.updateExpiredToAutoSignedOut(now);
        int overdueRows = visitorRecordMapper.updateVisitingToOverdue(now);
        return autoSignedOutRows + overdueRows;
    }

    @Override
@@ -71,6 +94,163 @@
        return visitorRecordMapper.deleteByIds(recordIds, username);
    }

    private void fillDetailDisplayFields(SysVisitorRecordDetailVO detail)
    {
        detail.setIdCardMask(maskIdCard(detail.getIdCard()));
        detail.setRegisterTypeName(registerTypeName(detail.getRegisterType()));
        detail.setStatusName(statusName(detail.getStatus()));
        detail.setReceiverVerifyMethodName(verifyMethodName(detail.getReceiverVerifyMethod()));
        detail.setSignVerifyMethodName(verifyMethodName(detail.getSignVerifyMethod()));
        detail.setVisitAreaNameList(splitNames(detail.getVisitAreaNames()));
        detail.setValidTimeText(validTimeText(detail.getValidStartTime(), detail.getValidEndTime()));
        detail.setRegisterCollectTime(detail.getRegisterTime());
        detail.setSignCollectTime(detail.getSignTime());
    }

    private String maskIdCard(String idCard)
    {
        if (StringUtils.isBlank(idCard) || idCard.length() <= 10)
        {
            return idCard;
        }
        return idCard.substring(0, 6) + "********" + idCard.substring(idCard.length() - 4);
    }

    private List<String> splitNames(String names)
    {
        List<String> result = new ArrayList<>();
        if (StringUtils.isBlank(names))
        {
            return result;
        }
        String[] splitNames = names.split(",");
        for (String name : splitNames)
        {
            String trimName = StringUtils.trim(name);
            if (StringUtils.isNotBlank(trimName))
            {
                result.add(trimName);
            }
        }
        return result;
    }

    private String registerTypeName(String registerType)
    {
        if ("APPOINTMENT".equals(registerType))
        {
            return "预约登记";
        }
        if ("ONSITE".equals(registerType))
        {
            return "现场登记";
        }
        return registerType;
    }

    private String statusName(String status)
    {
        if ("VISITING".equals(status))
        {
            return "来访中";
        }
        if ("SIGNED_OUT".equals(status))
        {
            return "已签离";
        }
        if ("PENDING_CONFIRM".equals(status))
        {
            return "逾期滞留";
        }
        if ("OVERDUE_AUTO_SIGNED_OUT".equals(status))
        {
            return "超期自动";
        }
        if ("CANCELLED".equals(status))
        {
            return "已取消";
        }
        return status;
    }

    private String verifyMethodName(String verifyMethod)
    {
        if (StringUtils.isBlank(verifyMethod) || StringUtils.containsAny(verifyMethod, "验证", "确认", "自动"))
        {
            return verifyMethod;
        }
        if ("FACE".equals(verifyMethod) || "FACE_VERIFY".equals(verifyMethod) || "FACE_RECOGNITION".equals(verifyMethod))
        {
            return "人脸验证";
        }
        if ("CARD".equals(verifyMethod) || "CARD_VERIFY".equals(verifyMethod))
        {
            return "刷卡验证";
        }
        if ("ID_CARD".equals(verifyMethod))
        {
            return "身份证验证";
        }
        if ("MANUAL".equals(verifyMethod))
        {
            return "人工确认";
        }
        if ("AUTO".equals(verifyMethod))
        {
            return "系统自动";
        }
        return verifyMethod;
    }

    private String validTimeText(Date validStartTime, Date validEndTime)
    {
        if (validStartTime == null || validEndTime == null)
        {
            return null;
        }
        LocalDateTime start = DateUtils.dateToLocalDateTime(validStartTime);
        LocalDateTime end = DateUtils.dateToLocalDateTime(validEndTime);
        LocalDate startDate = start.toLocalDate();
        LocalDate endDate = end.toLocalDate();
        if (startDate.equals(endDate))
        {
            return DateUtils.localDateToString(startDate) + " " + dayPartName(start.toLocalTime(), end.toLocalTime());
        }
        if (isDayStart(start.toLocalTime()) && isDayEnd(end.toLocalTime()))
        {
            return DateUtils.localDateToString(startDate) + " 至 " + DateUtils.localDateToString(endDate) + " 全天";
        }
        return DateUtils.parseDateToStr("yyyy-MM-dd HH:mm", validStartTime)
                + " 至 " + DateUtils.parseDateToStr("yyyy-MM-dd HH:mm", validEndTime);
    }

    private String dayPartName(LocalTime start, LocalTime end)
    {
        if (isDayStart(start) && isDayEnd(end))
        {
            return "全天";
        }
        if (start.getHour() < 12 && end.getHour() <= 12)
        {
            return "上午";
        }
        if (start.getHour() >= 12)
        {
            return "下午";
        }
        return "全天";
    }

    private boolean isDayStart(LocalTime time)
    {
        return time.getHour() == 0 && time.getMinute() == 0;
    }

    private boolean isDayEnd(LocalTime time)
    {
        return time.getHour() == 23 && time.getMinute() == 59;
    }

    private Long getRequiredRecordId(Long recordId)
    {
        if (recordId == null || recordId <= 0)

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

@@ -1,6 +1,8 @@
package com.ruoyi.system.vo;

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.Date;
@@ -11,28 +13,36 @@
 * @author ruoyi
 */
@Data
@ApiModel(value = "SysDashboardOverviewVO", description = "首页数据总览返回对象")
public class SysDashboardOverviewVO
{
    /** 当前统计周期 */
    @ApiModelProperty("当前统计周期：TODAY今日 RECENT_7_DAYS最近7天 RECENT_30_DAYS最近30天 CUSTOM自定义")
    private String periodType;

    /** 统计开始时间 */
    @ApiModelProperty("统计开始时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date beginTime;

    /** 统计结束时间 */
    @ApiModelProperty("统计结束时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date endTime;

    /** 线上预约统计 */
    @ApiModelProperty("线上预约板块统计")
    private SysDashboardRegisterSummaryVO onlineAppointment;

    /** 现场登记统计 */
    @ApiModelProperty("现场预约板块统计")
    private SysDashboardRegisterSummaryVO onsiteAppointment;

    /** 访客状态统计 */
    @ApiModelProperty("访客实时状态统计")
    private SysDashboardStatusSummaryVO statusSummary;

    /** 登记趋势 */
    @ApiModelProperty("进出场趋势统计")
    private SysDashboardTrendVO trend;
}

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

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

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

/**
@@ -8,11 +10,14 @@
 * @author ruoyi
 */
@Data
@ApiModel(value = "SysDashboardRegisterSummaryVO", description = "首页登记统计返回对象")
public class SysDashboardRegisterSummaryVO
{
    /** 登记数 */
    @ApiModelProperty("预约或登记数量")
    private Long registerCount;

    /** 来访车辆数 */
    @ApiModelProperty("来访车辆数量")
    private Long vehicleCount;
}

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

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

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

/**
@@ -8,14 +10,18 @@
 * @author ruoyi
 */
@Data
@ApiModel(value = "SysDashboardStatusSummaryVO", description = "首页访客状态统计返回对象")
public class SysDashboardStatusSummaryVO
{
    /** 来访中数量 */
    @ApiModelProperty("来访中数量")
    private Long visitingCount;

    /** 已签离数量 */
    @ApiModelProperty("已签离数量，包含正常签离和超期自动签离")
    private Long signedOutCount;

    /** 逾期滞留数量 */
    @ApiModelProperty("逾期滞留数量，超过有效期但尚未到当天23:59:59自动签离的访客")
    private Long overdueCount;
}

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

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

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

/**
@@ -8,20 +10,26 @@
 * @author ruoyi
 */
@Data
@ApiModel(value = "SysDashboardTrendDailyVO", description = "首页每日趋势查询结果")
public class SysDashboardTrendDailyVO
{
    /** 日期，格式 yyyy-MM-dd */
    @ApiModelProperty("日期，格式yyyy-MM-dd")
    private String trendDate;

    /** 日期标签，格式 MM-dd */
    @ApiModelProperty("日期标签，格式MM-dd")
    private String label;

    /** 现场登记数 */
    @ApiModelProperty("现场登记数")
    private Long onsiteCount;

    /** 预约登记数 */
    @ApiModelProperty("预约登记数")
    private Long appointmentCount;

    /** 来访车辆数 */
    @ApiModelProperty("来访车辆数")
    private Long vehicleCount;
}

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

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

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

import java.util.List;
@@ -10,17 +12,22 @@
 * @author ruoyi
 */
@Data
@ApiModel(value = "SysDashboardTrendVO", description = "首页趋势统计返回对象")
public class SysDashboardTrendVO
{
    /** 日期标签 */
    @ApiModelProperty("日期标签列表，格式MM-dd，与各趋势数组下标一一对应")
    private List<String> labels;

    /** 现场登记趋势 */
    @ApiModelProperty("现场登记趋势数组，与labels下标一一对应")
    private List<Long> onsite;

    /** 预约登记趋势 */
    @ApiModelProperty("预约登记趋势数组，与labels下标一一对应")
    private List<Long> appointment;

    /** 来访车辆趋势 */
    @ApiModelProperty("来访车辆趋势数组，与labels下标一一对应")
    private List<Long> vehicle;
}

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

@@ -1,6 +1,8 @@
package com.ruoyi.system.vo;

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.Date;
@@ -11,20 +13,35 @@
 * @author ruoyi
 */
@Data
@ApiModel(value = "SysVisitorPassRecordVO", description = "访客通行明细返回对象")
public class SysVisitorPassRecordVO
{
    /** 通行明细主键 */
    @ApiModelProperty("通行明细主键")
    private Long passId;

    /** 访客登记记录主键 */
    @ApiModelProperty("访客登记记录主键")
    private Long recordId;

    /** 通行类型：IN进场 OUT出场 */
    @ApiModelProperty("通行类型：IN进场 OUT出场")
    private String passType;

    /** 通行时间 */
    @ApiModelProperty("通行时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date passTime;

    /** 门禁设备名称 */
    @ApiModelProperty("门禁设备名称")
    private String doorDeviceName;

    /** 门禁设备编号 */
    @ApiModelProperty("门禁设备编号")
    private String doorDeviceCode;

    /** 门禁抓拍图片地址 */
    @ApiModelProperty("门禁抓拍图片地址")
    private String captureImageUrl;
}

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

@@ -1,6 +1,8 @@
package com.ruoyi.system.vo;

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.Date;
@@ -12,59 +14,148 @@
 * @author ruoyi
 */
@Data
@ApiModel(value = "SysVisitorRecordDetailVO", description = "管理侧访客记录详情返回对象")
public class SysVisitorRecordDetailVO
{
    /** 访客登记记录主键 */
    @ApiModelProperty("访客登记记录主键")
    private Long recordId;

    /** 登记编号 */
    @ApiModelProperty("登记编号")
    private String registerNo;

    /** 访客姓名 */
    @ApiModelProperty("访客姓名")
    private String visitorName;

    /** 访客证件号 */
    @ApiModelProperty("访客证件号")
    private String idCard;

    /** 脱敏后的访客证件号 */
    @ApiModelProperty("脱敏后的访客证件号")
    private String idCardMask;

    /** 登记类型：APPOINTMENT预约登记 ONSITE现场登记 */
    @ApiModelProperty("登记类型：APPOINTMENT预约登记 ONSITE现场登记")
    private String registerType;

    /** 登记类型名称 */
    @ApiModelProperty("登记类型名称")
    private String registerTypeName;

    /** 被访人 */
    @ApiModelProperty("被访人")
    private String intervieweeName;

    /** 被访人部门 */
    @ApiModelProperty("被访人部门")
    private String intervieweeDept;

    /** 登记接待人 */
    @ApiModelProperty("登记接待人")
    private String receiverName;

    /** 接待人验证方式编码 */
    @ApiModelProperty("接待人验证方式编码")
    private String receiverVerifyMethod;

    /** 接待人验证方式名称 */
    @ApiModelProperty("接待人验证方式名称")
    private String receiverVerifyMethodName;

    /** 访问区域ID，英文逗号分隔 */
    @ApiModelProperty("访问区域ID，英文逗号分隔")
    private String visitAreaIds;

    /** 访问区域名称，英文逗号分隔 */
    @ApiModelProperty("访问区域名称，英文逗号分隔")
    private String visitAreaNames;

    /** 访问区域名称列表 */
    @ApiModelProperty("访问区域名称列表")
    private List<String> visitAreaNameList;

    /** 车牌号 */
    @ApiModelProperty("车牌号")
    private String plateNo;

    /** 来访事由 */
    @ApiModelProperty("来访事由")
    private String visitReason;

    /** 有效期开始时间 */
    @ApiModelProperty("有效期开始时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date validStartTime;

    /** 有效期结束时间 */
    @ApiModelProperty("有效期结束时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date validEndTime;

    /** 访客状态：VISITING来访中 SIGNED_OUT已签离 PENDING_CONFIRM逾期滞留 OVERDUE_AUTO_SIGNED_OUT超期自动 CANCELLED已取消 */
    @ApiModelProperty("访客状态：VISITING来访中 SIGNED_OUT已签离 PENDING_CONFIRM逾期滞留 OVERDUE_AUTO_SIGNED_OUT超期自动 CANCELLED已取消")
    private String status;

    /** 访客状态名称 */
    @ApiModelProperty("访客状态名称")
    private String statusName;

    /** 访问有效期展示文本 */
    @ApiModelProperty("访问有效期展示文本")
    private String validTimeText;

    /** 登记时间 */
    @ApiModelProperty("登记时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date registerTime;

    /** 登记采集时间 */
    @ApiModelProperty("登记采集时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date registerCollectTime;

    /** 登记来源 */
    @ApiModelProperty("登记来源")
    private String registerSource;

    /** 登记访客人像地址 */
    @ApiModelProperty("登记访客人像地址")
    private String faceImageUrl;

    /** 签离时间 */
    @ApiModelProperty("签离时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date signTime;

    /** 签离接待人 */
    @ApiModelProperty("签离接待人")
    private String signReceiverName;

    /** 签离验证方式编码 */
    @ApiModelProperty("签离验证方式编码")
    private String signVerifyMethod;

    /** 签离验证方式名称 */
    @ApiModelProperty("签离验证方式名称")
    private String signVerifyMethodName;

    /** 签离人像地址 */
    @ApiModelProperty("签离人像地址")
    private String signImageUrl;

    /** 签离采集时间 */
    @ApiModelProperty("签离采集时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date signCollectTime;

    /** 备注 */
    @ApiModelProperty("备注")
    private String remark;

    /** 通行明细列表 */
    @ApiModelProperty("通行明细列表")
    private List<SysVisitorPassRecordVO> passRecords;
}

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

@@ -1,6 +1,8 @@
package com.ruoyi.system.vo;

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.Date;
@@ -11,36 +13,65 @@
 * @author ruoyi
 */
@Data
@ApiModel(value = "SysVisitorRecordVO", description = "管理侧访客记录列表返回对象")
public class SysVisitorRecordVO
{
    /** 访客登记记录主键 */
    @ApiModelProperty("访客登记记录主键")
    private Long recordId;

    /** 登记编号 */
    @ApiModelProperty("登记编号")
    private String registerNo;

    /** 访客姓名 */
    @ApiModelProperty("访客姓名")
    private String visitorName;

    /** 访客证件号 */
    @ApiModelProperty("访客证件号")
    private String idCard;

    /** 登记类型：APPOINTMENT预约登记 ONSITE现场登记 */
    @ApiModelProperty("登记类型：APPOINTMENT预约登记 ONSITE现场登记")
    private String registerType;

    /** 被访人 */
    @ApiModelProperty("被访人")
    private String intervieweeName;

    /** 被访人部门 */
    @ApiModelProperty("被访人部门")
    private String intervieweeDept;

    /** 访问区域名称，英文逗号分隔 */
    @ApiModelProperty("访问区域名称，英文逗号分隔")
    private String visitAreaNames;

    /** 车牌号 */
    @ApiModelProperty("车牌号")
    private String plateNo;

    /** 来访事由 */
    @ApiModelProperty("来访事由")
    private String visitReason;

    /** 有效期开始时间 */
    @ApiModelProperty("有效期开始时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date validStartTime;

    /** 有效期结束时间 */
    @ApiModelProperty("有效期结束时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date validEndTime;

    /** 访客状态：VISITING来访中 SIGNED_OUT已签离 PENDING_CONFIRM逾期滞留 OVERDUE_AUTO_SIGNED_OUT超期自动 CANCELLED已取消 */
    @ApiModelProperty("访客状态：VISITING来访中 SIGNED_OUT已签离 PENDING_CONFIRM逾期滞留 OVERDUE_AUTO_SIGNED_OUT超期自动 CANCELLED已取消")
    private String status;

    /** 登记时间 */
    @ApiModelProperty("登记时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date registerTime;
}

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

@@ -227,7 +227,10 @@

    <update id="deleteByIds">
        update t_employee
        set del_flag = '1',
        set enabled = 0,
            card_no = null,
            face_image = null,
            del_flag = '1',
            update_by = #{updateBy},
            update_time = sysdate()
        where del_flag = '0'

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

@@ -93,11 +93,17 @@
            <if test="query.intervieweeName != null and query.intervieweeName != ''">
                and interviewee_name like concat('%', #{query.intervieweeName}, '%')
            </if>
            <if test="query.intervieweeKeyword != null and query.intervieweeKeyword != ''">
                and interviewee_name like concat('%', #{query.intervieweeKeyword}, '%')
            </if>
            <if test="query.intervieweeDept != null and query.intervieweeDept != ''">
                and interviewee_dept = #{query.intervieweeDept}
            </if>
            <if test="query.areaId != null">
                and find_in_set(#{query.areaId}, visit_area_ids)
            </if>
            <if test="query.visitAreaKeyword != null and query.visitAreaKeyword != ''">
                and visit_area_names like concat('%', #{query.visitAreaKeyword}, '%')
            </if>
            <if test="query.validStartTime != null">
                and valid_end_time &gt;= #{query.validStartTime}
@@ -110,6 +116,12 @@
            </if>
            <if test="query.visitorName != null and query.visitorName != ''">
                and visitor_name like concat('%', #{query.visitorName}, '%')
            </if>
            <if test="query.visitorKeyword != null and query.visitorKeyword != ''">
                and (
                    visitor_name like concat('%', #{query.visitorKeyword}, '%')
                    or id_card like concat('%', #{query.visitorKeyword}, '%')
                )
            </if>
            <if test="query.visitReason != null and query.visitReason != ''">
                and visit_reason = #{query.visitReason}
@@ -141,6 +153,30 @@
        order by pass_time desc, pass_id desc
    </select>

    <update id="updateExpiredToAutoSignedOut">
        update t_visitor_record
        set status = 'OVERDUE_AUTO_SIGNED_OUT',
            sign_time = date_sub(date_add(date(valid_end_time), interval 1 day), interval 1 second),
            sign_verify_method = 'AUTO',
            update_by = 'system',
            update_time = sysdate()
        where del_flag = '0'
          and status in ('VISITING', 'PENDING_CONFIRM')
          and valid_end_time is not null
          and date_add(date(valid_end_time), interval 1 day) &lt;= #{now}
    </update>

    <update id="updateVisitingToOverdue">
        update t_visitor_record
        set status = 'PENDING_CONFIRM',
            update_time = sysdate()
        where del_flag = '0'
          and status = 'VISITING'
          and valid_end_time is not null
          and valid_end_time &lt; #{now}
          and date_add(date(valid_end_time), interval 1 day) &gt; #{now}
    </update>

    <update id="deleteByIds">
        update t_visitor_record
        set del_flag = '1',