水质数据查询

zhanzhiqin

2022-01-04 3801fe6ab221830fc803c632c14cb592c4619177

水质数据查询

 dao/src/main/java/com/sandu/ximon/dao/mapper/WaterQualityDataMapper.java

@@ -9,8 +9,7 @@

@Mapper
public interface WaterQualityDataMapper extends BaseMapper<WaterQualityData> {
    List<WaterQualityDataBo> listWaterQualityDataByKeyword(String keyword);
    List<Long> listWaterQualityDataId(String keyword, Long clientId);

    List<WaterQualityDataBo> listWaterQualityData();

    List<WaterQualityDataBo> listWaterQualityDataByIds(List<Long> WaterQualityDataIdList);
}

 dao/src/main/resources/mapper/WaterQualityDataMapper.xml

@@ -14,57 +14,44 @@
        <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
    </resultMap>
    <select id="listWaterQualityDataByKeyword" resultType="com.sandu.ximon.dao.bo.WaterQualityDataBo"
            parameterType="java.lang.String">
            SELECT
                t1.*, t2.pole_id,
                t3.pole_name
            FROM
                water_quality_data t1
            JOIN (
                SELECT
                    MAX(t1.id) a1,
                    t1.water_quality_ph,
                    t2.pole_id,
                    t3.pole_name
                FROM
                    water_quality_data t1
                LEFT JOIN pole_binding t2 ON t1.water_quality_equipment_code = t2.device_code
                AND t2.device_type = 4
                LEFT JOIN pole t3 ON t3.id = t2.pole_id
                WHERE
                    t3.pole_name LIKE  CONCAT(CONCAT('%', #{keyword}), '%')
                GROUP BY
                    t1.water_quality_equipment_code
                    LIMIT 0,10
                ) t ON t.a1 = t1.id
                LEFT JOIN pole_binding t2 ON t1.water_quality_equipment_code = t2.device_code
                AND t2.device_type = 4
                LEFT JOIN pole t3 ON t3.id = t2.pole_id

    <select id="listWaterQualityDataByIds" resultType="com.sandu.ximon.dao.bo.WaterQualityDataBo">
        SELECT
        t1.*, t2.pole_id,
        t3.pole_name
        FROM
        water_quality_data t1
        LEFT JOIN pole_binding t2 ON t1.water_quality_equipment_code = t2.device_code
        AND t2.device_type = 4
        LEFT JOIN pole t3 ON t3.id = t2.pole_id
        <where>
            t1.id IN
            <foreach collection="WaterQualityDataIdList" open="(" close=")" item="airId" separator=",">
                #{airId}
            </foreach>
        </where>
    </select>

    <select id="listWaterQualityData" resultType="com.sandu.ximon.dao.bo.WaterQualityDataBo"
            parameterType="java.lang.String">
            SELECT
                t1.*, t2.pole_id,
                t3.pole_name
            FROM
                water_quality_data t1
            JOIN (
                SELECT
                    MAX(t1.id) a1
                FROM
                    water_quality_data t1
                LEFT JOIN pole_binding t2 ON t1.water_quality_equipment_code = t2.device_code
                AND t2.device_type = 4
                LEFT JOIN pole t3 ON t3.id = t2.pole_id
                GROUP BY
                    t1.water_quality_equipment_code
                    LIMIT 0,10
                ) t ON t.a1 = t1.id
                LEFT JOIN pole_binding t2 ON t1.water_quality_equipment_code = t2.device_code
                AND t2.device_type = 4
                LEFT JOIN pole t3 ON t3.id = t2.pole_id
    <select id="listWaterQualityDataId" resultType="java.lang.Long">
        SELECT
        MAX(t1.id) AS water_quality_data_id
        FROM
        water_quality_data t1
        <if test="keyword != null and keyword != ''">
            LEFT JOIN pole_binding t2 ON t1.water_quality_equipment_code = t2.device_code
            AND t2.device_type = 4
            LEFT JOIN pole t3 ON t3.id = t2.pole_id
        </if>
        <where>
            <if test="keyword != null and keyword != ''">
                AND t3.pole_name LIKE CONCAT(CONCAT('%', #{keyword}), '%')
            </if>
            <if test="clientId != null">
                AND t3.client_id = #{clientId}
            </if>
        </where>
        GROUP BY
        t1.water_quality_equipment_code
    </select>

</mapper>

 ximon-admin/src/main/java/com/sandu/ximon/admin/controller/WaterQualityDataController.java

@@ -1,11 +1,11 @@
package com.sandu.ximon.admin.controller;

import com.sandu.common.domain.ResponseVO;
import com.sandu.common.object.BaseConditionVO;
import com.sandu.common.util.ResponseUtil;
import com.sandu.ximon.admin.dto.WaterQualityDataDto;
import com.sandu.ximon.admin.service.WaterQualityDataService;
import com.sandu.ximon.dao.bo.WaterQualityDataBo;
import com.sandu.ximon.dao.domain.WaterQualityData;
import lombok.AllArgsConstructor;
import org.springframework.web.bind.annotation.*;

@@ -20,21 +20,15 @@
public class WaterQualityDataController {
    private final WaterQualityDataService waterQualityDataService;

    @GetMapping
    public ResponseVO<Object> listWaterQualityData() {
        List<WaterQualityDataBo> list = waterQualityDataService.listWaterQualityData();
        return ResponseUtil.success(list);
    }

    /**
     * 模糊查询
     * 水质数据查询
     *
     * @return
     */
    @GetMapping("/listWaterQualityDataByKeyword/{keyword}")
    public ResponseVO<Object> listWaterQualityDataByKeyword(@PathVariable String keyword) {
        List<WaterQualityDataBo> list = waterQualityDataService.listWaterQualityDataByKeyword(keyword);
        return ResponseUtil.success(list);
    @GetMapping("/listWaterQualityData")
    public ResponseVO<Object> listWaterQualityDataByKeyword(BaseConditionVO baseConditionVO, @RequestParam(required = false) String keyword) {
        List<WaterQualityDataBo> list = waterQualityDataService.listWaterQualityDataByKeyword(baseConditionVO, keyword);
        return ResponseUtil.successPage(list);
    }

    @PostMapping("/getWaterQualityDataInfo/{waterQualityEquipmentCode}")

 ximon-admin/src/main/java/com/sandu/ximon/admin/service/WaterQualityDataService.java

@@ -1,16 +1,17 @@
package com.sandu.ximon.admin.service;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.http.HttpUtil;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.github.pagehelper.PageHelper;
import com.sandu.common.execption.BusinessException;
import com.sandu.common.object.BaseConditionVO;
import com.sandu.common.service.impl.BaseServiceImpl;
import com.sandu.ximon.admin.dto.WaterQualityDataDto;
import com.sandu.ximon.admin.security.SecurityUtils;
import com.sandu.ximon.dao.bo.WaterQualityDataBo;
import com.sandu.ximon.dao.domain.WaterQualityData;
import com.sandu.ximon.dao.mapper.WaterQualityDataMapper;
import com.sandu.ximon.dao.mapper.WaterQualityEquipmentMapper;
import lombok.AllArgsConstructor;
import org.springframework.stereotype.Service;

@@ -25,21 +26,17 @@
    private WaterQualityDataMapper waterQualityDataMapper;

    /**
     * 查询全部水质设备数据
     */
    public List<WaterQualityDataBo> listWaterQualityData() {
        return waterQualityDataMapper.listWaterQualityData();
    }

    /**
     * 模糊查询
     */
    public List<WaterQualityDataBo> listWaterQualityDataByKeyword(String keyword) {
        if (keyword == null || keyword.trim().length() == 0) {
            return listWaterQualityData();
        } else {
            return waterQualityDataMapper.listWaterQualityDataByKeyword(keyword);
    public List<WaterQualityDataBo> listWaterQualityDataByKeyword(BaseConditionVO baseConditionVO, String keyword) {
        Long clientId = SecurityUtils.getClientId();
        PageHelper.startPage(baseConditionVO.getPageNo(), baseConditionVO.getPageSize());
        List<Long> waterQualityDataId = waterQualityDataMapper.listWaterQualityDataId(keyword, clientId);
        if (CollUtil.isEmpty(waterQualityDataId)) {
            return CollUtil.newArrayList();
        }
        List<WaterQualityDataBo> airEquipmentBos = waterQualityDataMapper.listWaterQualityDataByIds(waterQualityDataId);
        return airEquipmentBos;
    }

    /**