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.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneOffset;
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;

    public static void main(String[] args) {
        String date = "2024-06-25";
        LocalDate localDate = LocalDate.parse(date);
        //获取当天0点
        LocalDateTime ldt = LocalDateTime.of(localDate, LocalTime.MIN);
        LocalDateTime aTime = ldt.plusHours(7);
        //获取7点-19点为A班
        int times = 1;
        do {
            System.err.println("A班\t"+aTime);
            aTime = aTime.plusHours(1);
        } while (times++ < 12);
        LocalDateTime bTime=ldt.plusHours(19);
        //获取19点-第二天6点为B班
        times = 1;
        do {
            System.err.println("B班\t"+bTime);
            bTime = bTime.plusHours(1);
        } while (times++ < 12);
    }

    @Test
    void test() {
        String code = "135";
        String table = "root.tl.suxi.knittings" + code + "_plc1";
        Workbook workbook = new XSSFWorkbook();
        String date = "2025-02-24";
        System.err.println(date);
        List<String[]> list = new ArrayList<>();
        for (int j = 7; j < 19; j++) {
            List<String[]> pList = getNew(table, date, j);
            list.addAll(pList);
        }
//        String date="2024-06-25";
//        for (int j = 0; j < 7; j++) {
//            List<String[]> pList = getNew(table, date, j);
//            list.addAll(pList);
//        }
        Sheet sheet = workbook.createSheet("A班");
        toExcelNew(list, sheet);
        try (FileOutputStream outputStream = new FileOutputStream("D:\\SYSTEM\\Desktop\\temp\\excel\\小经编机" + code + "号+" + date + "-明细.xlsx")) {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    void excel() {
        String code = "3";
        String table = "root.tl.suxi.knittings" + code + "_plc1";
        Workbook workbook = new XSSFWorkbook();
        for (int i = 21; i <= 26; i++) {
            String date = "2024-06-" + 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();
        });
    }

    public List<String[]> getNew(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(period);
        LocalDateTime end = start.plusHours(1);
        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_2,Capacity_data_33" +
                " 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[3];
            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 toExcelNew(List<String[]> list, Sheet sheet) {
        String[] names = {"时间", "织造米数", "功率-W"};

        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 <= 2; j++) {
                cell = row.createCell(j);
                cell.setCellValue(Float.parseFloat(d[j]));

            }
            index.getAndIncrement();
        });
    }
}