3.25标签功能

zxl

2026-03-25 d1dfb6e35f38e27fd960dc3ad0130c8d0f5c39bb

3.25标签功能

 jyz-base-start/src/main/java/com/tievd/jyz/controller/ActivityController.java

@@ -47,6 +47,9 @@
  @Autowired
  IActOrgRefService actOrgRefService;
  
  @Autowired
  private com.tievd.jyz.mapper.OilRecordMapper oilRecordMapper;
  
  /**
   * 分页列表查询
   */
@@ -158,6 +161,41 @@
  }

  /**
   * 查看车辆加油频次
   */
  @GetMapping("/getVehicleFrequency")
  @Operation(description = "查看车辆加油频次")
  public Result<?> getVehicleFrequency(@RequestParam Integer actId) {
    Activity activity = activityService.getById(actId);
    if (activity == null) {
      return Result.error("活动不存在");
    }
    
    List<ActOrgRef> orgRefs = actOrgRefService.list(new LambdaQueryWrapper<ActOrgRef>().eq(ActOrgRef::getActId, actId));
    if (orgRefs == null || orgRefs.isEmpty()) {
      return Result.error("活动未关联机构");
    }
    
    List<String> orgCodes = new ArrayList<>();
    for (ActOrgRef ref : orgRefs) {
      orgCodes.add(ref.getOrgCode());
    }
    
    java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String startTimeStr = sdf.format(activity.getStartTime());
    String endTimeStr = sdf.format(activity.getEndTime());
    
    java.util.Map<String, Object> result = new java.util.HashMap<>();
    
    result.put("activity", activity);
    result.put("beforeActivity", oilRecordMapper.getVehicleFrequencyBeforeActivity(startTimeStr, orgCodes));
    result.put("duringActivity", oilRecordMapper.getVehicleFrequencyDuringActivity(startTimeStr, endTimeStr, orgCodes));
    result.put("afterActivity", oilRecordMapper.getVehicleFrequencyAfterActivity(endTimeStr, orgCodes));
    
    return Result.ok(result);
  }

  /**
   * 导出excel
   */
  @RequestMapping("/exportXls")

 jyz-base-start/src/main/java/com/tievd/jyz/controller/DepartLabelController.java

New file
@@ -0,0 +1,128 @@
package com.tievd.jyz.controller;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.tievd.cube.commons.annotations.AutoLog;
import com.tievd.cube.commons.base.CubeController;
import com.tievd.cube.commons.base.Result;
import com.tievd.jyz.entity.DepartLabel;
import com.tievd.jyz.service.IDepartLabelService;
import io.swagger.v3.oas.annotations.Operation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Map;

@Slf4j
@RestController
@RequestMapping("/jyz/departLabel")
public class DepartLabelController extends CubeController<DepartLabel, IDepartLabelService> {

    @Autowired
    private IDepartLabelService departLabelService;

    @GetMapping("/list")
    @Operation(summary = "分页查询机构标签")
    public Result<?> queryPageList(@RequestParam(required = false) String labelName,
                                    @RequestParam(required = false) String parentCode,
                                    @RequestParam(required = false) String parentId,
                                    @RequestParam(defaultValue = "1") Integer pageNo,
                                    @RequestParam(defaultValue = "10") Integer pageSize) {
        List<Map<String, Object>> list = departLabelService.queryDepartLabelList(labelName, parentCode, parentId);
        int total = list.size();
        int start = (pageNo - 1) * pageSize;
        int end = Math.min(start + pageSize, total);
        
        Page<Map<String, Object>> page = new Page<>(pageNo, pageSize, total);
        if (start < total) {
            page.setRecords(list.subList(start, end));
        } else {
            page.setRecords(Collections.emptyList());
        }
        
        return Result.ok(page);
    }

    @GetMapping("/listLabels")
    @Operation(summary = "获取所有标签名称")
    public Result<?> listLabels(@RequestParam(required = false) String parentCode,
                                  @RequestParam(required = false) String parentId) {
        List<String> labels = departLabelService.queryAllLabelNames(parentCode, parentId);
        return Result.ok(labels);
    }

    @AutoLog("机构标签-添加")
    @PostMapping("/add")
    @Operation(summary = "添加机构标签")
    public Result<?> add(@RequestBody DepartLabel departLabel) {
        LambdaQueryWrapper<DepartLabel> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(DepartLabel::getDepartId, departLabel.getDepartId());
        queryWrapper.eq(DepartLabel::getLabelName, departLabel.getLabelName());
        if (departLabelService.count(queryWrapper) > 0) {
            return Result.error("该机构已存在此标签");
        }
        departLabelService.save(departLabel);
        return Result.ok();
    }

    @AutoLog("机构标签-删除")
    @DeleteMapping("/delete")
    @Operation(summary = "删除机构标签")
    public Result<?> delete(@RequestBody Map<String, String> params) {
        String departId = params.get("departId");
        String labelName = params.get("labelName");
        LambdaQueryWrapper<DepartLabel> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(DepartLabel::getDepartId, departId);
        if (labelName != null && !labelName.isEmpty()) {
            queryWrapper.eq(DepartLabel::getLabelName, labelName);
        }
        departLabelService.remove(queryWrapper);
        return Result.ok();
    }

    @AutoLog("机构标签-更新")
    @PutMapping("/update")
    @Operation(summary = "更新机构标签")
    public Result<?> update(@RequestBody Map<String, String> params) {
        String departId = params.get("departId");
        String labelName = params.get("labelName");
        LambdaQueryWrapper<DepartLabel> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(DepartLabel::getDepartId, departId);
        List<DepartLabel> labels = departLabelService.list(queryWrapper);
        
        if (labels.isEmpty()) {
            return Result.ok();
        }
        
        departLabelService.remove(queryWrapper);
        
        if (labelName != null && !labelName.isEmpty()) {
            String[] labelArray = labelName.split(",");
            for (String label : labelArray) {
                if (!label.trim().isEmpty()) {
                    DepartLabel departLabel = new DepartLabel();
                    departLabel.setDepartId(departId);
                    departLabel.setLabelName(label.trim());
                    departLabelService.save(departLabel);
                }
            }
        }
        
        return Result.ok();
    }

    @PostMapping("/queryOrgOilCount")
    @Operation(summary = "查询站点加油数")
    public Result<?> queryOrgOilCount(@RequestBody Map<String, String> params) {
        String orgCode = params.get("orgCode");
        String startTime = params.get("startTime");
        String endTime = params.get("endTime");
        List<Map<String, Object>> oilCountList = departLabelService.queryOrgOilCount(orgCode, startTime, endTime);
        return Result.ok(oilCountList);
    }
}

 jyz-base-start/src/main/java/com/tievd/jyz/entity/DepartLabel.java

New file
@@ -0,0 +1,44 @@
package com.tievd.jyz.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.util.Date;

@Data
@Accessors(chain = true)
@TableName("t_depart_label")
@Schema(name = "DepartLabel", description = "机构标签关联表")
public class DepartLabel extends Model<DepartLabel> {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    @Schema(description = "机构ID")
    @TableField("depart_id")
    private String departId;

    @Schema(description = "标签名称")
    @TableField("label_name")
    private String labelName;

    @TableField("create_time")
    private Date createTime;

    @TableField("update_time")
    private Date updateTime;

    @Override
    public Serializable pkVal() {
        return this.id;
    }
}

 jyz-base-start/src/main/java/com/tievd/jyz/mapper/DepartLabelMapper.java

New file
@@ -0,0 +1,21 @@
package com.tievd.jyz.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tievd.jyz.entity.DepartLabel;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface DepartLabelMapper extends BaseMapper<DepartLabel> {

    List<Map<String, Object>> queryDepartLabelList(@Param("labelName") String labelName,
                                                     @Param("parentCode") String parentCode,
                                                     @Param("parentId") String parentId);

    List<String> queryAllLabelNames(@Param("parentCode") String parentCode, @Param("parentId") String parentId);

    List<Map<String, Object>> queryOrgOilCount(@Param("orgCode") String orgCode,
                                             @Param("startTime") String startTime,
                                             @Param("endTime") String endTime);
}

 jyz-base-start/src/main/java/com/tievd/jyz/mapper/OilRecordMapper.java

@@ -106,4 +106,10 @@
    List<StatDataTableVo> statFanByPosition(DataStatisReqVo param);
    
    List<StatDataTableVo> statBarByModel(DataStatisReqVo param);
    
    Map getVehicleFrequencyBeforeActivity(@Param("startTime") String startTime, @Param("orgCodes") List<String> orgCodes);
    
    Map getVehicleFrequencyDuringActivity(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("orgCodes") List<String> orgCodes);
    
    Map getVehicleFrequencyAfterActivity(@Param("endTime") String endTime, @Param("orgCodes") List<String> orgCodes);
}

 jyz-base-start/src/main/java/com/tievd/jyz/service/IDepartLabelService.java

New file
@@ -0,0 +1,18 @@
package com.tievd.jyz.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.tievd.jyz.entity.DepartLabel;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;
import java.util.Map;

public interface IDepartLabelService extends IService<DepartLabel> {

    List<Map<String, Object>> queryDepartLabelList(String labelName, String parentCode, String parentId);

    List<String> queryAllLabelNames(String parentCode,
                                    String parentId);

    List<Map<String, Object>> queryOrgOilCount(String orgCode, String startTime, String endTime);
}

 jyz-base-start/src/main/java/com/tievd/jyz/service/impl/DepartLabelServiceImpl.java

New file
@@ -0,0 +1,200 @@
package com.tievd.jyz.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.tievd.jyz.entity.DepartLabel;
import com.tievd.jyz.mapper.DepartLabelMapper;
import com.tievd.jyz.service.IDepartLabelService;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class DepartLabelServiceImpl extends ServiceImpl<DepartLabelMapper, DepartLabel> implements IDepartLabelService {

    @Override
    public List<Map<String, Object>> queryDepartLabelList(String labelName, String parentCode, String parentId) {
        return baseMapper.queryDepartLabelList(labelName, parentCode, parentId);
    }

    @Override
    public List<String> queryAllLabelNames(String parentCode, String parentId) {
        return baseMapper.queryAllLabelNames(parentCode, parentId);
    }

    @Override
    public List<Map<String, Object>> queryOrgOilCount(String orgCode, String startTime, String endTime) {
        List<Map<String, Object>> oilCountList = baseMapper.queryOrgOilCount(orgCode, startTime, endTime);
        
        System.out.println("=== queryOrgOilCount 开始 ===");
        System.out.println("orgCode: " + orgCode);
        System.out.println("startTime: " + startTime);
        System.out.println("endTime: " + endTime);
        System.out.println("oilCountList size: " + (oilCountList != null ? oilCountList.size() : 0));
        
        if (oilCountList == null || oilCountList.isEmpty()) {
            return new ArrayList<>();
        }
        
        Map<String, String> orgCodeToIdMap = new HashMap<>();
        Map<String, String> idToParentIdMap = new HashMap<>();
        Map<String, Integer> idOilCountMap = new HashMap<>();
        Map<String, Integer> idCarCountMap = new HashMap<>();
        Map<String, Integer> idStationCountMap = new HashMap<>();
        Map<String, Integer> idOilVolumeMap = new HashMap<>();
        
        for (Map<String, Object> item : oilCountList) {
            String orgCodeStr = (String) item.get("org_code");
            String departId = (String) item.get("depart_id");
            
            Object oilCountObj = item.get("oilCount");
            Integer oilCount = null;
            if (oilCountObj instanceof Integer) {
                oilCount = (Integer) oilCountObj;
            } else if (oilCountObj instanceof Number) {
                oilCount = ((Number) oilCountObj).intValue();
            } else if (oilCountObj instanceof String) {
                oilCount = Integer.parseInt((String) oilCountObj);
            }
            
            Object carCountObj = item.get("carCount");
            Integer carCount = null;
            if (carCountObj instanceof Integer) {
                carCount = (Integer) carCountObj;
            } else if (carCountObj instanceof Number) {
                carCount = ((Number) carCountObj).intValue();
            } else if (carCountObj instanceof String) {
                carCount = Integer.parseInt((String) carCountObj);
            }
            
            Object stationCountObj = item.get("stationCount");
            Integer stationCount = null;
            if (stationCountObj instanceof Integer) {
                stationCount = (Integer) stationCountObj;
            } else if (stationCountObj instanceof Number) {
                stationCount = ((Number) stationCountObj).intValue();
            } else if (stationCountObj instanceof String) {
                stationCount = Integer.parseInt((String) stationCountObj);
            }
            
            Object oilVolumeObj = item.get("oilVolume");
            Integer oilVolume = null;
            if (oilVolumeObj instanceof Integer) {
                oilVolume = (Integer) oilVolumeObj;
            } else if (oilVolumeObj instanceof Number) {
                oilVolume = ((Number) oilVolumeObj).intValue();
            } else if (oilVolumeObj instanceof String) {
                oilVolume = Integer.parseInt((String) oilVolumeObj);
            }
            
            if (departId != null) {
                orgCodeToIdMap.put(orgCodeStr, departId);
                if (oilCount != null) {
                    idOilCountMap.put(departId, oilCount);
                }
                if (carCount != null) {
                    idCarCountMap.put(departId, carCount);
                }
                if (stationCount != null) {
                    idStationCountMap.put(departId, stationCount);
                }
                if (oilVolume != null) {
                    idOilVolumeMap.put(departId, oilVolume);
                }
            }
        }
        
        List<Map<String, Object>> departList = baseMapper.queryDepartLabelList(null, orgCode, null);
        System.out.println("departList size: " + (departList != null ? departList.size() : 0));
        
        for (Map<String, Object> depart : departList) {
            String departId = (String) depart.get("id");
            Object parentIdObj = depart.get("parent_id");
            System.out.println("departId: " + departId + ", parentId: " + parentIdObj);
            if (parentIdObj != null) {
                String parentIdStr = parentIdObj.toString();
                if (parentIdStr != null && !parentIdStr.isEmpty()) {
                    String[] parentIds = parentIdStr.split(",");
                    for (String pid : parentIds) {
                        if (!pid.trim().isEmpty()) {
                            idToParentIdMap.put(departId, pid.trim());
                            System.out.println("  -> 父子关系: " + departId + " -> " + pid.trim());
                        }
                    }
                }
            }
        }
        
        System.out.println("idToParentIdMap size: " + idToParentIdMap.size());
        System.out.println("idOilCountMap: " + idOilCountMap);
        System.out.println("idCarCountMap: " + idCarCountMap);
        System.out.println("idStationCountMap: " + idStationCountMap);
        System.out.println("idOilVolumeMap: " + idOilVolumeMap);
        
        Map<String, Integer> finalOilCountMap = new HashMap<>(idOilCountMap);
        Map<String, Integer> finalCarCountMap = new HashMap<>(idCarCountMap);
        Map<String, Integer> finalStationCountMap = new HashMap<>(idStationCountMap);
        Map<String, Integer> finalOilVolumeMap = new HashMap<>(idOilVolumeMap);
        
        for (Map.Entry<String, String> entry : idToParentIdMap.entrySet()) {
            String childId = entry.getKey();
            String parentIdStr = entry.getValue();
            if (parentIdStr != null && !parentIdStr.isEmpty()) {
                Integer childOilCount = idOilCountMap.get(childId);
                if (childOilCount != null) {
                    finalOilCountMap.merge(parentIdStr, childOilCount, Integer::sum);
                    System.out.println("累加加油数: 父节点 " + parentIdStr + " += 子节点 " + childId + " 的 " + childOilCount);
                }
                Integer childCarCount = idCarCountMap.get(childId);
                if (childCarCount != null) {
                    finalCarCountMap.merge(parentIdStr, childCarCount, Integer::sum);
                    System.out.println("累加车流量: 父节点 " + parentIdStr + " += 子节点 " + childId + " 的 " + childCarCount);
                }
                Integer childStationCount = idStationCountMap.get(childId);
                if (childStationCount != null) {
                    finalStationCountMap.merge(parentIdStr, childStationCount, Integer::sum);
                    System.out.println("累加进站数: 父节点 " + parentIdStr + " += 子节点 " + childId + " 的 " + childStationCount);
                }
                Integer childOilVolume = idOilVolumeMap.get(childId);
                if (childOilVolume != null) {
                    finalOilVolumeMap.merge(parentIdStr, childOilVolume, Integer::sum);
                    System.out.println("累加油品销量: 父节点 " + parentIdStr + " += 子节点 " + childId + " 的 " + childOilVolume);
                }
            }
        }
        
        System.out.println("finalOilCountMap: " + finalOilCountMap);
        System.out.println("finalCarCountMap: " + finalCarCountMap);
        System.out.println("finalStationCountMap: " + finalStationCountMap);
        System.out.println("finalOilVolumeMap: " + finalOilVolumeMap);
        
        for (Map<String, Object> item : oilCountList) {
            String departId = (String) item.get("depart_id");
            Integer finalOilCount = finalOilCountMap.get(departId);
            if (finalOilCount != null) {
                item.put("oilCount", finalOilCount);
                System.out.println("更新 " + item.get("depart_name") + " 的加油数: " + finalOilCount);
            }
            Integer finalCarCount = finalCarCountMap.get(departId);
            if (finalCarCount != null) {
                item.put("carCount", finalCarCount);
                System.out.println("更新 " + item.get("depart_name") + " 的车流量: " + finalCarCount);
            }
            Integer finalStationCount = finalStationCountMap.get(departId);
            if (finalStationCount != null) {
                item.put("stationCount", finalStationCount);
                System.out.println("更新 " + item.get("depart_name") + " 的进站数: " + finalStationCount);
            }
            Integer finalOilVolume = finalOilVolumeMap.get(departId);
            if (finalOilVolume != null) {
                item.put("oilVolume", finalOilVolume);
                System.out.println("更新 " + item.get("depart_name") + " 的油品销量: " + finalOilVolume);
            }
        }
        
        System.out.println("=== queryOrgOilCount 结束 ===");
        return oilCountList;
    }
}

 jyz-base-start/src/main/resources/xml/DepartLabelMapper.xml

New file
@@ -0,0 +1,117 @@
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.tievd.jyz.mapper.DepartLabelMapper">

    <select id="queryDepartLabelList" resultType="java.util.Map">
        SELECT 
            s.id,
            s.id as depart_id,
            s.parent_id,
            COALESCE(GROUP_CONCAT(DISTINCT d.label_name SEPARATOR ','), '') as label_name,
            s.depart_name,
            s.org_code,
            s.create_time
        FROM sys_depart s
        LEFT JOIN t_depart_label d ON s.id = d.depart_id
        WHERE s.del_flag = 0
        <if test="parentCode != null and parentCode != ''">
            AND s.org_code LIKE CONCAT(#{parentCode}, '%')
        </if>
        <if test="parentId != null and parentId != ''">
            AND (s.id = #{parentId} OR FIND_IN_SET(#{parentId}, s.parent_id))
        </if>
        GROUP BY s.id, s.depart_name, s.org_code, s.create_time
        <if test="labelName != null and labelName != ''">
            HAVING FIND_IN_SET(#{labelName}, GROUP_CONCAT(DISTINCT d.label_name SEPARATOR ','))
        </if>
        ORDER BY s.id
    </select>

    <select id="queryAllLabelNames" resultType="java.lang.String">
        SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(label_name, ',', n.n), ',', -1) as label_name
        FROM t_label
        CROSS JOIN (
            SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
            SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL 
            SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
        ) n
        WHERE CHAR_LENGTH(label_name) - CHAR_LENGTH(REPLACE(label_name, ',', '')) >= n.n - 1
        AND label_type = '站点标签'
        ORDER BY label_name
    </select>

    <select id="queryOrgOilCount" resultType="java.util.Map">
        SELECT 
            d.id as depart_id,
            d.org_code,
            d.depart_name,
            COALESCE(oil.oil_count, 0) as oilCount,
            COALESCE(traffic.car_count, 0) as carCount,
            COALESCE(station.station_count, 0) as stationCount,
            COALESCE(volume.oil_volume, 0) as oilVolume
        FROM sys_depart d
        LEFT JOIN (
            SELECT 
                org_code,
                COUNT(IF(behavior=1, 1, NULL)) as oil_count
            FROM t_oil_record
            <where>
                <if test="startTime != null and startTime != ''">
                    AND start_time &gt;= #{startTime}
                </if>
                <if test="endTime != null and endTime != ''">
                    AND start_time &lt; #{endTime}
                </if>
            </where>
            GROUP BY org_code
        ) oil ON d.org_code = oil.org_code
        LEFT JOIN (
            SELECT 
                org_code,
                SUM(car_count) as car_count
            FROM t_traffic_flow
            <where>
                <if test="startTime != null and startTime != ''">
                    AND capture_time &gt;= #{startTime}
                </if>
                <if test="endTime != null and endTime != ''">
                    AND capture_time &lt; #{endTime}
                </if>
            </where>
            GROUP BY org_code
        ) traffic ON d.org_code = traffic.org_code
        LEFT JOIN (
            SELECT 
                org_code,
                COUNT(1) as station_count
            FROM t_oil_record
            <where>
                <if test="startTime != null and startTime != ''">
                    AND start_time &gt;= #{startTime}
                </if>
                <if test="endTime != null and endTime != ''">
                    AND start_time &lt; #{endTime}
                </if>
            </where>
            GROUP BY org_code
        ) station ON d.org_code = station.org_code
        LEFT JOIN (
            SELECT 
                org_code,
                SUM(oil_volume) as oil_volume
            FROM t_oil_record
            <where>
                <if test="startTime != null and startTime != ''">
                    AND start_time &gt;= #{startTime}
                </if>
                <if test="endTime != null and endTime != ''">
                    AND start_time &lt; #{endTime}
                </if>
            </where>
            GROUP BY org_code
        ) volume ON d.org_code = volume.org_code
        WHERE d.org_code LIKE CONCAT(#{orgCode}, '%')
        GROUP BY d.id, d.org_code, d.depart_name
    </select>

</mapper>

 jyz-base-start/src/main/resources/xml/OilRecordMapper.xml

@@ -52,7 +52,7 @@
        select
        oil_position oilPosition, count(1) oilCount,  sum(oil_volume) OilVolume
        from t_oil_record
        where org_code =#{orgCode}
        where org_code like concat(#{orgCode}, '%')
        <if test="dateMonth != null and dateMonth != ''">and DATE_FORMAT(start_time, '%Y-%m')=#{dateMonth}</if>
        GROUP BY oil_position
    </select>
@@ -220,4 +220,43 @@
          GROUP BY model_code
        ) f on m.model_code = f.model_code
    </select>

    <select id="getVehicleFrequencyBeforeActivity" resultType="java.util.Map">
        SELECT 
            COUNT(DISTINCT license_num) as totalVehicles,
            COUNT(*) as totalOilRecords,
            AVG(oil_volume) as avgOilVolume,
            SUM(oil_volume) as totalOilVolume
        FROM t_oil_record
        WHERE start_time &lt; #{startTime}
        <foreach collection="orgCodes" item="orgCode" open="AND (" separator=" OR " close=")">
            org_code LIKE concat(#{orgCode}, '%')
        </foreach>
    </select>

    <select id="getVehicleFrequencyDuringActivity" resultType="java.util.Map">
        SELECT 
            COUNT(DISTINCT license_num) as totalVehicles,
            COUNT(*) as totalOilRecords,
            AVG(oil_volume) as avgOilVolume,
            SUM(oil_volume) as totalOilVolume
        FROM t_oil_record
        WHERE start_time >= #{startTime} AND start_time &lt;= #{endTime}
        <foreach collection="orgCodes" item="orgCode" open="AND (" separator=" OR " close=")">
            org_code LIKE concat(#{orgCode}, '%')
        </foreach>
    </select>

    <select id="getVehicleFrequencyAfterActivity" resultType="java.util.Map">
        SELECT 
            COUNT(DISTINCT license_num) as totalVehicles,
            COUNT(*) as totalOilRecords,
            AVG(oil_volume) as avgOilVolume,
            SUM(oil_volume) as totalOilVolume
        FROM t_oil_record
        WHERE start_time > #{endTime}
        <foreach collection="orgCodes" item="orgCode" open="AND (" separator=" OR " close=")">
            org_code LIKE concat(#{orgCode}, '%')
        </foreach>
    </select>
</mapper>