twin-service/ruoyi-admin/src/test/java/com/jjt/DataProcess.java

package com.jjt;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import com.ruoyi.RuoYiApplication;
import com.ruoyi.biz.service.IIotService;
import com.ruoyi.common.utils.DateUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.annotation.Resource;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * DataProcess$
 *
 * @author wukai
 * @date 2024/5/7 11:49
 */
@SpringBootTest(classes = RuoYiApplication.class, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class DataProcess {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private IIotService iotService;

    @Test
    void excel() {
        String code = "106";
        String table = "root.tl.suxi.knittings" + code + "_plc1";
        Workbook workbook = new XSSFWorkbook();
        for (int i = 21; i <= 26; i++) {
            String date = "2024-05-" + i;
            System.err.println(date);
            List<String[]> list = new ArrayList<>();
            for (int j = 1; j <= 12; j++) {
                List<String[]> pList = get(table, date, j);
                list.addAll(pList);
            }
            Sheet sheet = workbook.createSheet(date);
            toExcel(list, sheet);
        }
        try (FileOutputStream outputStream = new FileOutputStream("D:\\SYSTEM\\Desktop\\temp\\excel\\小经编机" + code + "号.xlsx")) {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public List<String[]> get(String table, String date, int period) {
        List<String[]> list = new ArrayList<>();
        LocalDate localDate = LocalDate.parse(date);
        LocalDateTime ldt = LocalDateTime.of(localDate, LocalTime.MIN);
        LocalDateTime start = ldt.plusHours(2 * period).minusHours(2);
        LocalDateTime end = start.plusHours(2);
        start = start.minusSeconds(1);
        Long startTime = start.toInstant(ZoneOffset.of("+8")).toEpochMilli();
        Long endTime = end.toInstant(ZoneOffset.of("+8")).toEpochMilli();
        String sql = "select Capacity_data_5,Capacity_data_6,Capacity_data_7,Capacity_data_8,Capacity_data_9,Capacity_data_10,Capacity_data_11,Capacity_data_12,Capacity_data_13,Capacity_data_14,Capacity_data_15,Capacity_data_16,Capacity_data_17,Capacity_data_18,Capacity_data_19" +
                " from %s where time>%s and time <=%s";
        sql = String.format(sql, table, startTime, endTime);
        iotService.query(sql);
        JSONObject jsonObject = iotService.query(sql);
        JSONObject data = jsonObject.getJSONObject("data");
        JSONArray values = data.getJSONArray("values");
        JSONArray timestamps = data.getJSONArray("timestamps");
        for (int i = 0; i < values.size(); i++) {
            String[] temp = new String[16];
            temp[0] = timestamps.getStr(i);
            JSONArray da = values.getJSONArray(i);
            for (int j = 1; j < temp.length; j++) {
                temp[j] = da.getStr(j - 1);
            }
            list.add(temp);
        }
        return list;
    }

    public void toExcel(List<String[]> list, Sheet sheet) {
        String[] names = {"时间", "GB1剩余时间", "GB2剩余时间", "GB3剩余时间", "GB4剩余时间", "GB5剩余时间", "GB1盘头剩余周长", "GB2盘头剩余周长", "GB3盘头剩余周长", "GB4盘头剩余周长", "GB5盘头剩余周长", "GB1盘头剩余圈数", "GB2盘头剩余圈数", "GB3盘头剩余圈数", "GB4盘头剩余圈数", "GB5盘头剩余圈数"};

        Row title = sheet.createRow(0);
        for (int i = 0; i < names.length; i++) {
            Cell cell = title.createCell(i);
            cell.setCellValue(names[i]);
        }
        AtomicInteger index = new AtomicInteger(1);
        list.forEach(d -> {
            Row row = sheet.createRow(index.get());
            Long time = Long.parseLong(d[0]);
            String date = DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD_HH_MM_SS, new Date(time));
            Cell cell = row.createCell(0);
            cell.setCellValue(date);
            for (int j = 1; j <= 10; j++) {
                cell = row.createCell(j);
                cell.setCellValue(Float.parseFloat(d[j]));

            }
            for (int j = 11; j < d.length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(Integer.parseInt(d[j]));
            }
            index.getAndIncrement();
        });
    }


    @Test
    void data() {
        String table = "root.tl.suxi.knittings98_plc1";
        String[] arr = {
                "time>2024-05-03T00:00:00 and time <=2024-05-03T02:00:00",
                "time>2024-05-03T02:00:00 and time <=2024-05-03T04:00:00",
                "time>2024-05-03T04:00:00 and time <=2024-05-03T06:00:00",
                "time>2024-05-03T06:00:00 and time <=2024-05-03T08:00:00",
                "time>2024-05-03T08:00:00 and time <=2024-05-03T10:00:00",
                "time>2024-05-03T10:00:00 and time <=2024-05-03T12:00:00",
                "time>2024-05-03T12:00:00 and time <=2024-05-03T14:00:00",
                "time>2024-05-03T14:00:00 and time <=2024-05-03T16:00:00",
                "time>2024-05-03T16:00:00 and time <=2024-05-03T18:00:00",
                "time>2024-05-03T18:00:00 and time <=2024-05-03T20:00:00",
                "time>2024-05-03T20:00:00 and time <=2024-05-03T22:00:00",
                "time>2024-05-03T22:00:00 and time <=2024-05-04T00:00:00",
                "time>2024-05-04T00:00:00 and time <=2024-05-04T02:00:00",
                "time>2024-05-04T02:00:00 and time <=2024-05-04T04:00:00",
                "time>2024-05-04T04:00:00 and time <=2024-05-04T06:00:00",
                "time>2024-05-04T06:00:00 and time <=2024-05-04T08:00:00",
                "time>2024-05-04T08:00:00 and time <=2024-05-04T10:00:00",
                "time>2024-05-04T10:00:00 and time <=2024-05-04T12:00:00",
                "time>2024-05-04T12:00:00 and time <=2024-05-04T14:00:00",
                "time>2024-05-04T14:00:00 and time <=2024-05-04T16:00:00",
                "time>2024-05-04T16:00:00 and time <=2024-05-04T18:00:00",
                "time>2024-05-04T18:00:00 and time <=2024-05-04T20:00:00",
                "time>2024-05-04T20:00:00 and time <=2024-05-04T22:00:00",
                "time>2024-05-04T22:00:00 and time <=2024-05-05T00:00:00"
        };

//        for (String where : arr) {
//            process(table, where);
//        }

        String where = "time>=2024-05-05T12:00:00 and time <=2024-05-05T14:00:00";
        process(table, where);
    }

    @Test
    void process(String table, String where) {
        String sql = "select Alarm_unit_1,Alarm_unit_2,Alarm_unit_3,Alarm_unit_4,Alarm_unit_5,Alarm_unit_6,Alarm_unit_7,Alarm_unit_8,Alarm_unit_9,Alarm_unit_10,Alarm_unit_11,Alarm_unit_12,Alarm_unit_13,Alarm_unit_14,Alarm_unit_15,Alarm_unit_16,Alarm_unit_17,Alarm_unit_18,Alarm_unit_19,Alarm_unit_20,Alarm_unit_21,Alarm_unit_22,Alarm_unit_23,Alarm_unit_24,Alarm_unit_25,Alarm_unit_26,Alarm_unit_27,Capacity_data_1,Capacity_data_2,Capacity_data_3,Capacity_data_4,Capacity_data_5,Capacity_data_6,Capacity_data_7,Capacity_data_8,Capacity_data_9,Capacity_data_10,Capacity_data_11,Capacity_data_12,Capacity_data_13,Capacity_data_14,Capacity_data_15,Capacity_data_16,Capacity_data_17,Capacity_data_18,Capacity_data_19,Capacity_data_20,Capacity_data_21,Capacity_data_22,Capacity_data_23,Capacity_data_24,Capacity_data_25,Capacity_data_26,Capacity_data_27,Capacity_data_28,Capacity_data_29,Capacity_data_30,Capacity_data_31,Capacity_data_32,Capacity_data_33,Capacity_data_34,Capacity_data_35,Capacity_data_36,Capacity_data_37,Capacity_data_38,Capacity_data_39,Capacity_data_40,Capacity_data_41,Capacity_data_42,Capacity_data_43,Capacity_data_44,Capacity_data_45,Capacity_data_46,Capacity_data_47,Capacity_data_48,Capacity_data_49,Capacity_data_50,Capacity_data_51,Capacity_data_52,Capacity_data_53,System_data_1,System_data_2,System_data_3,System_data_4,System_data_5,System_data_6,System_data_7,System_data_8,System_data_9,System_data_10,System_data_11,System_data_12,System_data_13,System_data_14,System_data_15,System_data_16,System_data_17,System_data_18,System_data_19,System_data_20,System_data_21,System_data_22,System_data_23,System_data_24,System_data_25,System_data_26,System_data_27,System_data_28,System_data_29,System_data_30,System_data_31,System_data_32,System_data_33,System_data_34,System_data_35,System_data_36,System_data_37,System_data_38,System_data_39,System_data_40,Formula_data_1,Formula_data_2,Formula_data_3,Formula_data_4,Formula_data_5,Formula_data_6,Formula_data_7,Formula_data_8,Formula_data_9,Formula_data_10,Formula_data_11,Formula_data_12,Formula_data_13,Formula_data_14,Formula_data_15,Formula_data_16,Formula_data_17,Formula_data_18,Formula_data_19,Formula_data_20,Formula_data_21,Formula_data_22,Formula_data_23,Formula_data_24,Formula_data_25,Formula_data_26,Formula_data_27,Formula_data_28,Formula_data_29,Formula_data_30,Formula_data_31,Formula_data_32,Formula_data_33,Formula_data_34,Formula_data_35" +
                " from " + table +
                " where " + where;
//        System.err.println(res);
        JSONObject jsonObject = iotService.query(sql);
        JSONObject data = jsonObject.getJSONObject("data");
        JSONArray array = data.getJSONArray("values");
        JSONArray columnNames = data.getJSONArray("columnNames");
        JSONArray timestamps = data.getJSONArray("timestamps");
        List list = new ArrayList<>();
        for (int i = 0; i < timestamps.size(); i++) {
            Object[] arr = new Object[columnNames.size() + 1];
            arr[0] = LocalDateTime.ofInstant(Instant.ofEpochMilli(timestamps.getLong(i)), ZoneId.systemDefault());
            JSONArray da = array.getJSONArray(i);
            for (int j = 0; j < da.size(); j++) {
                arr[j + 1] = da.get(j);
            }

            list.add(arr);
        }

        sql = "INSERT INTO TWIN_DATA(DATA_TIME,Alarm_unit_1,Alarm_unit_2,Alarm_unit_3,Alarm_unit_4,Alarm_unit_5,Alarm_unit_6,Alarm_unit_7,Alarm_unit_8,Alarm_unit_9,Alarm_unit_10,Alarm_unit_11,Alarm_unit_12,Alarm_unit_13,Alarm_unit_14,Alarm_unit_15,Alarm_unit_16,Alarm_unit_17,Alarm_unit_18,Alarm_unit_19,Alarm_unit_20,Alarm_unit_21,Alarm_unit_22,Alarm_unit_23,Alarm_unit_24,Alarm_unit_25,Alarm_unit_26,Alarm_unit_27,Capacity_data_1,Capacity_data_2,Capacity_data_3,Capacity_data_4,Capacity_data_5,Capacity_data_6,Capacity_data_7,Capacity_data_8,Capacity_data_9,Capacity_data_10,Capacity_data_11,Capacity_data_12,Capacity_data_13,Capacity_data_14,Capacity_data_15,Capacity_data_16,Capacity_data_17,Capacity_data_18,Capacity_data_19,Capacity_data_20,Capacity_data_21,Capacity_data_22,Capacity_data_23,Capacity_data_24,Capacity_data_25,Capacity_data_26,Capacity_data_27,Capacity_data_28,Capacity_data_29,Capacity_data_30,Capacity_data_31,Capacity_data_32,Capacity_data_33,Capacity_data_34,Capacity_data_35,Capacity_data_36,Capacity_data_37,Capacity_data_38,Capacity_data_39,Capacity_data_40,Capacity_data_41,Capacity_data_42,Capacity_data_43,Capacity_data_44,Capacity_data_45,Capacity_data_46,Capacity_data_47,Capacity_data_48,Capacity_data_49,Capacity_data_50,Capacity_data_51,Capacity_data_52,Capacity_data_53,System_data_1,System_data_2,System_data_3,System_data_4,System_data_5,System_data_6,System_data_7,System_data_8,System_data_9,System_data_10,System_data_11,System_data_12,System_data_13,System_data_14,System_data_15,System_data_16,System_data_17,System_data_18,System_data_19,System_data_20,System_data_21,System_data_22,System_data_23,System_data_24,System_data_25,System_data_26,System_data_27,System_data_28,System_data_29,System_data_30,System_data_31,System_data_32,System_data_33,System_data_34,System_data_35,System_data_36,System_data_37,System_data_38,System_data_39,System_data_40,Formula_data_1,Formula_data_2,Formula_data_3,Formula_data_4,Formula_data_5,Formula_data_6,Formula_data_7,Formula_data_8,Formula_data_9,Formula_data_10,Formula_data_11,Formula_data_12,Formula_data_13,Formula_data_14,Formula_data_15,Formula_data_16,Formula_data_17,Formula_data_18,Formula_data_19,Formula_data_20,Formula_data_21,Formula_data_22,Formula_data_23,Formula_data_24,Formula_data_25,Formula_data_26,Formula_data_27,Formula_data_28,Formula_data_29,Formula_data_30,Formula_data_31,Formula_data_32,Formula_data_33,Formula_data_34,Formula_data_35)" +
                " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.batchUpdate(sql, list);
    }
}