1、各模块大屏运行情况查询修改

ZengTao

2025-09-04 30317b7c1d87231f09f77d39b3d7ab54c26844ed

1、各模块大屏运行情况查询修改

 hangzhoumesParent/moduleService/CacheGlassModule/src/main/resources/application-dev.yml

@@ -40,14 +40,4 @@
        endpoint: opc.tcp://10.153.19.150:49320
        security-policy: basic256sha256
        username: admin
        password: 1qaz2wsx3edc4rfv
kangaroohy:
  milo:
    enabled: true
    primary: default
    config:
      default:
        endpoint: opc.tcp://10.153.19.150:49320
        security-policy: basic256sha256
        username: admin
        password: 1qaz2wsx3edc4rfv

 hangzhoumesParent/moduleService/CacheGlassModule/src/main/resources/mapper/EdgStorageDeviceTaskHistoryMapper.xml

@@ -36,33 +36,47 @@
        </if>
    </select>
    <select id="queryRunTimes" resultType="com.mes.largenscreen.entity.RunTime">
        with edg_storage_cage_history_task_temp as (
            select distinct create_time from edg_storage_device_task_history  where task_type in (1,3) and
                create_time LIKE '%${days}%'
        )
        WITH edg_storage_cage_history_task_temp AS
                 (SELECT DISTINCT create_time
                  FROM edg_storage_device_task_history
                  WHERE task_type IN (1, 3) AND DATE ( create_time ) = '${days}' ),
            time_temp AS (
        SELECT
            date (create_time) AS day_time,
            MIN(create_time) AS start_timestamp,
            MAX(create_time) AS end_timestamp
        FROM
            edg_storage_device_task_history
        WHERE
            Date ( create_time ) = '${days}'
        GROUP BY
            date ( create_time )
            ),
            result_temp AS (
        SELECT
            date ( t1.create_time ) AS day_time,
            t1.create_time AS first_timestamp,
            t2.create_time AS second_timestamp,
            TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time) as diff_minutes
                ,(select min(create_time) from edg_storage_cage_history_task_temp) as start_timestamp
                ,(select max(create_time) from edg_storage_cage_history_task_temp) as end_timestamp
            TIMESTAMPDIFF( MINUTE, t1.create_time, t2.create_time ) AS diff_minutes
        FROM
            (SELECT
                 create_time,
                 LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
             FROM
                 edg_storage_cage_history_task_temp ) t1
                JOIN
            (SELECT
                 create_time,
                 LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
             FROM
                 edg_storage_cage_history_task_temp ) t2 ON t1.next_timestamp = t2.create_time

            ( SELECT create_time, LEAD( create_time ) OVER ( ORDER BY create_time ) AS next_timestamp FROM edg_storage_cage_history_task_temp ) t1
            JOIN ( SELECT create_time, LEAD( create_time ) OVER ( ORDER BY create_time ) AS next_timestamp FROM edg_storage_cage_history_task_temp ) t2
        ON t1.next_timestamp = t2.create_time
        WHERE
            TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time) between 6 and 500
        GROUP BY t1.create_time,t2.create_time
        ;
            TIMESTAMPDIFF( MINUTE
            , t1.create_time
            , t2.create_time ) BETWEEN 6
          AND 500
        GROUP BY
            t1.create_time,
            t2.create_time
            ),
            result AS (
        SELECT first_timestamp, second_timestamp, diff_minutes, start_timestamp, end_timestamp
        FROM time_temp t1 LEFT JOIN result_temp t2
        ON t1.day_time = t2.day_time )
        SELECT *
        FROM result
    </select>
    <select id="queryEdgDailyProduction" resultMap="baseMap">
        SELECT

 hangzhoumesParent/moduleService/CacheVerticalGlassModule/src/main/resources/mapper/BigStorageCageHistoryTaskMapper.xml

@@ -44,30 +44,47 @@
        INNER join big_storage_out_temp on 1 = 1
    </select>
    <select id="queryRunTimes" resultType="com.mes.largenscreen.entity.RunTime">
        with big_storage_cage_history_task_temp as (
            select distinct create_time
            from big_storage_cage_history_task
            where task_type = 2
              and create_time LIKE '%${days}%'
        )
        SELECT t1.create_time                                                    AS first_timestamp,
               t2.create_time                                                    AS second_timestamp,
               TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time)             as diff_minutes
                ,
               (select min(create_time) from big_storage_cage_history_task_temp) as start_timestamp
                ,
               (select max(create_time) from big_storage_cage_history_task_temp) as end_timestamp
        FROM (SELECT create_time,
                     LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
              FROM big_storage_cage_history_task_temp) t1
                 JOIN
             (SELECT create_time,
                     LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
              FROM big_storage_cage_history_task_temp) t2 ON t1.next_timestamp = t2.create_time

        WHERE TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time) between 11 and 500
        GROUP BY t1.create_time, t2.create_time
        ;
        WITH big_storage_cage_history_task_temp AS
                 (SELECT DISTINCT create_time
                  FROM big_storage_cage_history_task
                  WHERE task_type = 2 AND DATE ( create_time ) = '${days}' ),
            time_temp AS (
        SELECT
            date (create_time) AS day_time,
            MIN(create_time) AS start_timestamp,
            MAX(create_time) AS end_timestamp
        FROM
            big_storage_cage_history_task
        WHERE
            Date ( create_time ) = '${days}'
        GROUP BY
            date ( create_time )
            ),
            result_temp AS (
        SELECT
            date ( t1.create_time ) AS day_time,
            t1.create_time AS first_timestamp,
            t2.create_time AS second_timestamp,
            TIMESTAMPDIFF( MINUTE, t1.create_time, t2.create_time ) AS diff_minutes
        FROM
            ( SELECT create_time, LEAD( create_time ) OVER ( ORDER BY create_time ) AS next_timestamp FROM big_storage_cage_history_task_temp ) t1
            JOIN ( SELECT create_time, LEAD( create_time ) OVER ( ORDER BY create_time ) AS next_timestamp FROM big_storage_cage_history_task_temp ) t2
        ON t1.next_timestamp = t2.create_time
        WHERE
            TIMESTAMPDIFF( MINUTE
            , t1.create_time
            , t2.create_time ) BETWEEN 11
          AND 500
        GROUP BY
            t1.create_time,
            t2.create_time
            ),
            result AS (
        SELECT first_timestamp, second_timestamp, diff_minutes, start_timestamp, end_timestamp
        FROM time_temp t1 LEFT JOIN result_temp t2
        ON t1.day_time = t2.day_time )
        SELECT *
        FROM result
    </select>

</mapper>

 hangzhoumesParent/moduleService/TemperingGlassModule/src/main/java/com/mes/job/PlcScreenTask.java

@@ -1,21 +1,20 @@
package com.mes.job;

import cn.hutool.json.JSONObject;
import com.mes.order.entity.Orders;
import com.mes.order.service.OrdersService;
import com.mes.pp.entity.OptimizeProject;
import com.mes.pp.entity.request.AwaitingRepair;
import com.mes.pp.entity.request.Reportingdamage;
import com.mes.pp.service.OptimizeProjectService;
import com.mes.pp.service.ReportingWorkService;
import com.mes.tools.WebSocketServer;
import com.mes.tools.WebSocketUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
@@ -31,6 +30,8 @@
    private OptimizeProjectService optimizeProjectService;
    @Autowired
    private OrdersService ordersService;
    @Resource
    private WebSocketUtils webSocketUtils;
    /**
     * fixedRate : 上一个调用开始后再次调用的延时（不用等待上一次调用完成）
     * fixedDelay : 上一个调用结束后再次调用的延时
@@ -70,17 +71,17 @@

        /*现场规划图显示*/


        ArrayList<WebSocketServer> sendwServer = WebSocketServer.sessionMap.get("screen");
        if (sendwServer != null) {
            for (WebSocketServer webserver : sendwServer) {
                if (webserver != null) {
                    webserver.sendMessage(jsonObject.toString());
                } else {
                    log.info("Home is closed");
                }
            }
        }
        webSocketUtils.sendToWeb("screen", jsonObject);
//        ArrayList<WebSocketServer> sendwServer = WebSocketServer.sessionMap.get("screen");
//        if (sendwServer != null) {
//            for (WebSocketServer webserver : sendwServer) {
//                if (webserver != null) {
//                    webserver.sendMessage(jsonObject.toString());
//                } else {
//                    log.info("Home is closed");
//                }
//            }
//        }
    }

}

 hangzhoumesParent/moduleService/hollowGlassModule/src/main/java/com/mes/job/PushMessageToIndex.java

@@ -148,9 +148,9 @@


    public void queryDataSource1(JSONObject jsonObject) throws Exception {
        S7DataZKDLPOne s7DataZKDLPOne = s7SerializerZKDLPOne.read(S7DataZKDLPOne.class);
        S7DataZKDLPTwo s7DataZKDLPTwo = s7SerializerZKDLPTwo.read(S7DataZKDLPTwo.class);
        S7DataZKExtra s7DataZKExtra = s7SerializerZKQ2.read(S7DataZKExtra.class);
//        S7DataZKDLPOne s7DataZKDLPOne = s7SerializerZKDLPOne.read(S7DataZKDLPOne.class);
//        S7DataZKDLPTwo s7DataZKDLPTwo = s7SerializerZKDLPTwo.read(S7DataZKDLPTwo.class);
//        S7DataZKExtra s7DataZKExtra = s7SerializerZKQ2.read(S7DataZKExtra.class);
        List<Double> carPostion = new ArrayList<>();
        carPostion.add(0.25);
        carPostion.add(0.5);
@@ -158,150 +158,150 @@
        //界面展示笼子信息
        jsonObject.append("bigStorageCageInfos", hollowBigStorageCageDetailsService.queryHollowbigStorageCageDetail());

        try {
            //进片任务数据
            List<BigStorageTaskVO> inTaskList = new ArrayList();
            String fromOpcUa = s7DataZKDLPOne.getFrom1().toString();
            for (int i = 0; i < 6; i++) {
                String requestWord = "";
                String stateWord = "";
                String targetSlotWord = "";
                switch (i) {
                    case 0:
                        requestWord = s7DataZKDLPOne.getId1();
                        stateWord = s7DataZKDLPOne.getState1().toString();
                        targetSlotWord = s7DataZKDLPOne.getTo1().toString();
                        break;
                    case 1:
                        requestWord = s7DataZKDLPOne.getId2();
                        stateWord = s7DataZKDLPOne.getState2().toString();
                        targetSlotWord = s7DataZKDLPOne.getTo2().toString();
                        break;
                    case 2:
                        requestWord = s7DataZKDLPOne.getId3();
                        stateWord = s7DataZKDLPOne.getState3().toString();
                        targetSlotWord = s7DataZKDLPOne.getTo3().toString();
                        break;
                    case 3:
                        requestWord = s7DataZKDLPOne.getId4();
                        stateWord = s7DataZKDLPOne.getState4().toString();
                        targetSlotWord = s7DataZKDLPOne.getTo4().toString();
                        break;
                    case 4:
                        requestWord = s7DataZKDLPOne.getId5();
                        stateWord = s7DataZKDLPOne.getState5().toString();
                        targetSlotWord = s7DataZKDLPOne.getTo5().toString();
                        break;
                    case 5:
                        requestWord = s7DataZKDLPOne.getId6();
                        stateWord = s7DataZKDLPOne.getState6().toString();
                        targetSlotWord = s7DataZKDLPOne.getTo6().toString();
                        break;
                }
                if (StringUtils.isNotEmpty(requestWord)) {
                    BigStorageTaskVO task = new BigStorageTaskVO();
                    task.setGlassId(requestWord);
                    int isExistCount = hollowBigStorageCageDetailsService.count(new LambdaQueryWrapper<HollowBigStorageCageDetails>().in(HollowBigStorageCageDetails::getGlassId, task.getGlassId())
                            .in(HollowBigStorageCageDetails::getState, Const.GLASS_STATE_IN_ALL));
                    if (isExistCount > 0) {
                        task.setIsSame(1);
                    } else {
                        task.setIsSame(0);
                    }
                    task.setStartSlot(Integer.parseInt(fromOpcUa));
                    task.setTargetSlot(Integer.parseInt(targetSlotWord));
                    task.setTaskState(Integer.parseInt(stateWord));
                    inTaskList.add(task);
                    continue;
                }
            }
            jsonObject.append("bigStorageCageDetailsFeedTask", inTaskList);
        } catch (Exception e) {
            //todo:不做任务处理
        }
        try {
            //进片联机
            Boolean inkageEntity = s7DataZKDLPOne.getMesControl();
            jsonObject.append("inkageEntity", inkageEntity);
            //进片请求
            String requestEntity = s7DataZKDLPOne.getRequestMes().toString();
            jsonObject.append("requestEntity", requestEntity);
            //启动命令
            String mesReplyEntity = s7DataZKDLPOne.getMesReply().toString();
            jsonObject.append("mesReplyEntity", mesReplyEntity);
            //出片联机
            String outInkageEntity = s7DataZKDLPTwo.getMesControl().toString();
            jsonObject.append("outInkageEntity", outInkageEntity);
            //出片请求
            String outRequestEntity = s7DataZKDLPTwo.getRequestMes().toString();
            jsonObject.append("outInkageEntity", outInkageEntity);
            //930空闲信号
            jsonObject.append("freeOneRequestEntity", CMJ1ModbusTcp.readUInt16(42027 - offset));
            //931空闲信号
            jsonObject.append("freeTwoRequestEntity", s7DataZKExtra.getIsFree());
            //932空闲信号
            jsonObject.append("freeThreeRequestEntity", s7DataZKExtra.getIsFree());
        } catch (Exception e) {
            //todo:不做任务处理
        }

        //出片任务数据
        List<BigStorageCageTask> outTaskList = new ArrayList<>();
        for (int i = 0; i < 6; i++) {

            BigStorageCageTask bigStorageCageTask = new BigStorageCageTask();
            String glassId = "";
            Integer startSlot = 0;
            Integer targetSlot = 0;
            Integer taskState = 0;
            switch (i) {
                case 0:
                    glassId = s7DataZKDLPTwo.getId1();
                    startSlot = s7DataZKDLPTwo.getFrom1();
                    targetSlot = s7DataZKDLPTwo.getTo1();
                    taskState = s7DataZKDLPTwo.getState1();
                    ;
                    break;
                case 1:
                    glassId = s7DataZKDLPTwo.getId2();
                    startSlot = s7DataZKDLPTwo.getFrom2();
                    targetSlot = s7DataZKDLPTwo.getTo2();
                    taskState = s7DataZKDLPTwo.getState2();
                    break;
                case 2:
                    glassId = s7DataZKDLPTwo.getId3();
                    startSlot = s7DataZKDLPTwo.getFrom3();
                    targetSlot = s7DataZKDLPTwo.getTo3();
                    taskState = s7DataZKDLPTwo.getState3();
                    break;
                case 3:
                    glassId = s7DataZKDLPTwo.getId4();
                    startSlot = s7DataZKDLPTwo.getFrom4();
                    targetSlot = s7DataZKDLPTwo.getTo4();
                    taskState = s7DataZKDLPTwo.getState4();
                    break;
                case 4:
                    glassId = s7DataZKDLPTwo.getId5();
                    startSlot = s7DataZKDLPTwo.getFrom5();
                    targetSlot = s7DataZKDLPTwo.getTo5();
                    taskState = s7DataZKDLPTwo.getState5();
                    break;
                case 5:
                    glassId = s7DataZKDLPTwo.getId6();
                    startSlot = s7DataZKDLPTwo.getFrom6();
                    targetSlot = s7DataZKDLPTwo.getTo6();
                    taskState = s7DataZKDLPTwo.getState6();
                    break;
            }
            if (StringUtils.isNotEmpty(glassId)) {
                bigStorageCageTask.setGlassId(glassId);
                bigStorageCageTask.setStartSlot(startSlot);
                bigStorageCageTask.setTargetSlot(targetSlot);
                bigStorageCageTask.setTaskState(taskState);
                outTaskList.add(bigStorageCageTask);
            }
        }
        jsonObject.append("bigStorageCageDetailsOutTask", outTaskList);
//        try {
//            //进片任务数据
//            List<BigStorageTaskVO> inTaskList = new ArrayList();
//            String fromOpcUa = s7DataZKDLPOne.getFrom1().toString();
//            for (int i = 0; i < 6; i++) {
//                String requestWord = "";
//                String stateWord = "";
//                String targetSlotWord = "";
//                switch (i) {
//                    case 0:
//                        requestWord = s7DataZKDLPOne.getId1();
//                        stateWord = s7DataZKDLPOne.getState1().toString();
//                        targetSlotWord = s7DataZKDLPOne.getTo1().toString();
//                        break;
//                    case 1:
//                        requestWord = s7DataZKDLPOne.getId2();
//                        stateWord = s7DataZKDLPOne.getState2().toString();
//                        targetSlotWord = s7DataZKDLPOne.getTo2().toString();
//                        break;
//                    case 2:
//                        requestWord = s7DataZKDLPOne.getId3();
//                        stateWord = s7DataZKDLPOne.getState3().toString();
//                        targetSlotWord = s7DataZKDLPOne.getTo3().toString();
//                        break;
//                    case 3:
//                        requestWord = s7DataZKDLPOne.getId4();
//                        stateWord = s7DataZKDLPOne.getState4().toString();
//                        targetSlotWord = s7DataZKDLPOne.getTo4().toString();
//                        break;
//                    case 4:
//                        requestWord = s7DataZKDLPOne.getId5();
//                        stateWord = s7DataZKDLPOne.getState5().toString();
//                        targetSlotWord = s7DataZKDLPOne.getTo5().toString();
//                        break;
//                    case 5:
//                        requestWord = s7DataZKDLPOne.getId6();
//                        stateWord = s7DataZKDLPOne.getState6().toString();
//                        targetSlotWord = s7DataZKDLPOne.getTo6().toString();
//                        break;
//                }
//                if (StringUtils.isNotEmpty(requestWord)) {
//                    BigStorageTaskVO task = new BigStorageTaskVO();
//                    task.setGlassId(requestWord);
//                    int isExistCount = hollowBigStorageCageDetailsService.count(new LambdaQueryWrapper<HollowBigStorageCageDetails>().in(HollowBigStorageCageDetails::getGlassId, task.getGlassId())
//                            .in(HollowBigStorageCageDetails::getState, Const.GLASS_STATE_IN_ALL));
//                    if (isExistCount > 0) {
//                        task.setIsSame(1);
//                    } else {
//                        task.setIsSame(0);
//                    }
//                    task.setStartSlot(Integer.parseInt(fromOpcUa));
//                    task.setTargetSlot(Integer.parseInt(targetSlotWord));
//                    task.setTaskState(Integer.parseInt(stateWord));
//                    inTaskList.add(task);
//                    continue;
//                }
//            }
//            jsonObject.append("bigStorageCageDetailsFeedTask", inTaskList);
//        } catch (Exception e) {
//            //todo:不做任务处理
//        }
//        try {
//            //进片联机
//            Boolean inkageEntity = s7DataZKDLPOne.getMesControl();
//            jsonObject.append("inkageEntity", inkageEntity);
//            //进片请求
//            String requestEntity = s7DataZKDLPOne.getRequestMes().toString();
//            jsonObject.append("requestEntity", requestEntity);
//            //启动命令
//            String mesReplyEntity = s7DataZKDLPOne.getMesReply().toString();
//            jsonObject.append("mesReplyEntity", mesReplyEntity);
//            //出片联机
//            String outInkageEntity = s7DataZKDLPTwo.getMesControl().toString();
//            jsonObject.append("outInkageEntity", outInkageEntity);
//            //出片请求
//            String outRequestEntity = s7DataZKDLPTwo.getRequestMes().toString();
//            jsonObject.append("outInkageEntity", outInkageEntity);
//            //930空闲信号
//            jsonObject.append("freeOneRequestEntity", CMJ1ModbusTcp.readUInt16(42027 - offset));
//            //931空闲信号
//            jsonObject.append("freeTwoRequestEntity", s7DataZKExtra.getIsFree());
//            //932空闲信号
//            jsonObject.append("freeThreeRequestEntity", s7DataZKExtra.getIsFree());
//        } catch (Exception e) {
//            //todo:不做任务处理
//        }
//
//        //出片任务数据
//        List<BigStorageCageTask> outTaskList = new ArrayList<>();
//        for (int i = 0; i < 6; i++) {
//
//            BigStorageCageTask bigStorageCageTask = new BigStorageCageTask();
//            String glassId = "";
//            Integer startSlot = 0;
//            Integer targetSlot = 0;
//            Integer taskState = 0;
//            switch (i) {
//                case 0:
//                    glassId = s7DataZKDLPTwo.getId1();
//                    startSlot = s7DataZKDLPTwo.getFrom1();
//                    targetSlot = s7DataZKDLPTwo.getTo1();
//                    taskState = s7DataZKDLPTwo.getState1();
//                    ;
//                    break;
//                case 1:
//                    glassId = s7DataZKDLPTwo.getId2();
//                    startSlot = s7DataZKDLPTwo.getFrom2();
//                    targetSlot = s7DataZKDLPTwo.getTo2();
//                    taskState = s7DataZKDLPTwo.getState2();
//                    break;
//                case 2:
//                    glassId = s7DataZKDLPTwo.getId3();
//                    startSlot = s7DataZKDLPTwo.getFrom3();
//                    targetSlot = s7DataZKDLPTwo.getTo3();
//                    taskState = s7DataZKDLPTwo.getState3();
//                    break;
//                case 3:
//                    glassId = s7DataZKDLPTwo.getId4();
//                    startSlot = s7DataZKDLPTwo.getFrom4();
//                    targetSlot = s7DataZKDLPTwo.getTo4();
//                    taskState = s7DataZKDLPTwo.getState4();
//                    break;
//                case 4:
//                    glassId = s7DataZKDLPTwo.getId5();
//                    startSlot = s7DataZKDLPTwo.getFrom5();
//                    targetSlot = s7DataZKDLPTwo.getTo5();
//                    taskState = s7DataZKDLPTwo.getState5();
//                    break;
//                case 5:
//                    glassId = s7DataZKDLPTwo.getId6();
//                    startSlot = s7DataZKDLPTwo.getFrom6();
//                    targetSlot = s7DataZKDLPTwo.getTo6();
//                    taskState = s7DataZKDLPTwo.getState6();
//                    break;
//            }
//            if (StringUtils.isNotEmpty(glassId)) {
//                bigStorageCageTask.setGlassId(glassId);
//                bigStorageCageTask.setStartSlot(startSlot);
//                bigStorageCageTask.setTargetSlot(targetSlot);
//                bigStorageCageTask.setTaskState(taskState);
//                outTaskList.add(bigStorageCageTask);
//            }
//        }
//        jsonObject.append("bigStorageCageDetailsOutTask", outTaskList);

        //调度开关
        boolean dispatchHollowSwitch = false;

 hangzhoumesParent/moduleService/hollowGlassModule/src/main/resources/mapper/HollowBigStorageCageHistoryTaskMapper.xml

@@ -48,29 +48,46 @@
        inner join hollow_out_two_temp on 1 = 1
    </select>
    <select id="queryRunTimes" resultType="com.mes.largenscreen.entity.RunTime">
        with big_storage_cage_history_task_temp as (
            select distinct create_time from hollow_big_storage_cage_history_task  where task_type = 5 and
                create_time  LIKE '%${days}%'
        )
        WITH big_storage_cage_history_task_temp AS
                 (SELECT DISTINCT create_time
                  FROM hollow_big_storage_cage_history_task
                  WHERE task_type = 5 AND DATE ( create_time ) = '2025-06-23' ),
            time_temp AS (
        SELECT
            date (create_time) AS day_time,
            MIN(create_time) AS start_timestamp,
            MAX(create_time) AS end_timestamp
        FROM
            hollow_big_storage_cage_history_task
        WHERE
            Date ( create_time ) = '2025-06-23'
        GROUP BY
            date ( create_time )
            ),
            result_temp AS (
        SELECT
            date ( t1.create_time ) AS day_time,
            t1.create_time AS first_timestamp,
            t2.create_time AS second_timestamp,
            TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time) as diff_minutes,
               (select min(create_time) from big_storage_cage_history_task_temp) as start_timestamp
                ,(select max(create_time) from big_storage_cage_history_task_temp) as end_timestamp
            TIMESTAMPDIFF( MINUTE, t1.create_time, t2.create_time ) AS diff_minutes
        FROM
            (SELECT
                 create_time,
                 LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
             FROM
                 big_storage_cage_history_task_temp ) t1
                JOIN
            (SELECT
                 create_time,
                 LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
             FROM
                 big_storage_cage_history_task_temp ) t2 ON t1.next_timestamp = t2.create_time
            ( SELECT create_time, LEAD( create_time ) OVER ( ORDER BY create_time ) AS next_timestamp FROM big_storage_cage_history_task_temp ) t1
            JOIN ( SELECT create_time, LEAD( create_time ) OVER ( ORDER BY create_time ) AS next_timestamp FROM big_storage_cage_history_task_temp ) t2
        ON t1.next_timestamp = t2.create_time
        WHERE
            TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time) between 11 and 500;
            TIMESTAMPDIFF( MINUTE
            , t1.create_time
            , t2.create_time ) BETWEEN 11
          AND 500
        GROUP BY
            t1.create_time,
            t2.create_time
            ),
            result AS (
        SELECT first_timestamp, second_timestamp, diff_minutes, start_timestamp, end_timestamp
        FROM time_temp t1 LEFT JOIN result_temp t2
        ON t1.day_time = t2.day_time )
        SELECT *
        FROM result
    </select>
</mapper>