twin-service/ruoyi-admin/src/main/java/com/ruoyi/biz/controller/ApiController.java

package com.ruoyi.biz.controller;

import com.ruoyi.biz.domain.*;
import com.ruoyi.biz.service.*;
import com.ruoyi.biz.service.impl.AsyncServiceImpl;
import com.ruoyi.common.constant.Constants;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.R;
import com.ruoyi.common.utils.CacheUtils;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.system.service.ISysConfigService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.text.ParseException;
import java.time.*;
import java.util.*;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;

/**
 * swagger 用户测试方法
 *
 * @author ruoyi
 */
@Api("数据接口")
@RestController
@Slf4j
@RequestMapping("/api/")
public class ApiController extends BaseController {
    @Resource
    private ITwinCalcDayService twinCalcDayService;
    @Resource
    private ITwinCalc2hrService calc2hrService;
    @Resource
    private ITwinRecordAlarmsService recordAlarmsService;
    @Resource
    private ITwinCalcStopService stopService;
    @Resource
    private ITwinCalcAlarmsService alarmsService;
    @Resource
    private ITwinDeviceService deviceService;
    @Resource
    private IIotService iotService;
    @Resource
    private AsyncServiceImpl asyncService;
    @Resource
    private ITwinPanHeadInfoService panHeadInfoService;
    @Resource
    private ISysConfigService configService;
    @Value("${excel.total}")
    private String totalExcelTemplate;
    @Value("${excel.formula}")
    private String formulaExcelTemplate;
    @Value("${excel.gram}")
    private String gramExcelTemplate;
    @Value("${excel.stops}")
    private String stopsExcelTemplate;
    @Value("${excel.stopsType}")
    private String stopsTypeExcelTemplate;
    @Value("${excel.alarms}")
    private String alarmsExcelTemplate;
    @Value("${excel.yarn}")
    private String yarnExcelTemplate;

    @ApiOperation("在线设备")
    @GetMapping("/device/online")
    @CrossOrigin(origins = "*")
    public R<List<String>> online() {
        TwinDevice searchDevice = new TwinDevice();
        searchDevice.setOnline("1");
        List<TwinDevice> list = deviceService.selectTwinDeviceList(searchDevice);
        List<String> devices = new ArrayList<>();
        list.forEach(d -> devices.add(d.getDeviceCode()));
        return R.ok(devices);
    }

    @ApiOperation("首页统计数据")
    @GetMapping("/index")
    @CrossOrigin(origins = "*")
    public R<IndexData> index() {
        Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_CALC);
        if (d != null) {
            IndexData indexData = (IndexData) d;
            return R.ok(indexData);
        } else {
            return R.fail();
        }
    }

    @ApiOperation("首页告警数据")
    @GetMapping("/alarm")
    @CrossOrigin(origins = "*")
    public R<IndexData> alarm() {
        Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_ALARM);
        if (d != null) {
            IndexData indexData = (IndexData) d;
            return R.ok(indexData);
        } else {
            return R.fail();
        }
    }

    @ApiOperation("设备具体数据")
    @GetMapping("/query/{device}")
    @CrossOrigin(origins = "*")
    public R<Map> query(@PathVariable String device, String week) {
        TwinDevice info = deviceService.selectTwinDeviceByCode(device);
        if (info == null) {
            return R.fail("该设备未接入");
        }
        iotService.getToken();
        Future<Map<String, Object>> future = asyncService.currData(info);
        Map<String, Object> result = new HashMap<>(16);
        try {
            result.put("data", future.get());
        } catch (InterruptedException e) {
            throw new RuntimeException(e);
        } catch (ExecutionException e) {
            throw new RuntimeException(e);
        }
        if (week != null) {
            /*
             *获取前面7天的数据
             */
            LocalDateTime ldt = LocalDateTime.now();
            if (ldt.getHour() < 7) {
                //7点前是属于前一生产天
                ldt = ldt.minusDays(1);
            }
            LocalDate localDate = ldt.toLocalDate().minusDays(7);
            Date date = Date.from(localDate.atStartOfDay(ZoneOffset.of("+8")).toInstant());
            List<TwinCalcDay> list = twinCalcDayService.selectTwinCalcDayListByTime(date);
            List<Map<String, Object>> alarms = new ArrayList<>();
            Map<Date, List<TwinCalcDay>> stopDeviceGroup = list.stream().collect(Collectors.groupingBy(TwinCalcDay::getTime, LinkedHashMap::new, Collectors.toList()));
            for (Map.Entry<Date, List<TwinCalcDay>> entry : stopDeviceGroup.entrySet()) {
                Map<String, Object> temp = new HashMap<>(16);
                TwinCalcDay day = new TwinCalcDay(entry.getKey());
                List<TwinCalcDay> days = entry.getValue();
                day.calcDays(days);
                temp.put("time", DateUtils.parseDateToStr(entry.getKey()));
                temp.put("value", day.getAlarm());
                alarms.add(temp);
            }
            result.put("alarms", alarms);
        }
        return R.ok(result);
    }

    @ApiOperation("送经量")
    @GetMapping("/export/warp-run-in")
    @CrossOrigin(origins = "*")
    public void warpRunInExport(HttpServletResponse response) {
        Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_WARP_RUN_IN);
        if (d != null) {
            List<WarpRunIn> list = (List<WarpRunIn>) d;
            try (Workbook wb = new XSSFWorkbook(); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) {
                Sheet sheet = wb.createSheet("送经量明细");
                sheet.setDefaultColumnWidth(15);
                CellStyle style = wb.createCellStyle();
                style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
                Row titleRow = sheet.createRow(0);
                Cell titleCell = titleRow.createCell(0);
                titleCell.setCellValue("设备名");
                titleCell = titleRow.createCell(1);
                titleCell.setCellValue("L1梳栉送经量");
                titleCell = titleRow.createCell(2);
                titleCell.setCellValue("L2梳栉送经量");
                titleCell = titleRow.createCell(3);
                titleCell.setCellValue("L3梳栉送经量");
                titleCell = titleRow.createCell(4);
                titleCell.setCellValue("L4梳栉送经量");
                titleCell = titleRow.createCell(5);
                titleCell.setCellValue("L5梳栉送经量");
                titleCell = titleRow.createCell(6);
                titleCell.setCellValue("牵拉密度");
                titleCell = titleRow.createCell(7);
                titleCell.setCellValue("卷曲张力系数");
                AtomicInteger rowNum = new AtomicInteger(1);
                list.forEach(warp -> {
                    Row row = sheet.createRow(rowNum.get());
                    Cell[] cells = new Cell[8];
                    for (int i = 0; i < cells.length; i++) {
                        cells[i] = row.createCell(i);
                    }
                    warp.setCells(cells, style);
                    rowNum.getAndIncrement();
                });

                // 清空response
                response.reset();
                // 设置response的Header
                response.setCharacterEncoding("UTF-8");
                //Content-Disposition的作用：告知浏览器以何种方式显示响应返回的文件，用浏览器打开还是以附件的形式下载到本地保存
                //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3"
                // filename表示文件的默认名称，因为网络传输只支持URL编码的相关支付，因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称
                response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("送经量明细导出" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8"));
                response.setContentType("application/octet-stream");
                wb.write(outputStream);
                outputStream.flush();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }

    @ApiOperation("纱线规格")
    @GetMapping("/export/yarn-specifications")
    @CrossOrigin(origins = "*")
    public void yarnSpecificationsExport(HttpServletResponse response) {
        Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_FORMULA_DETAIL);
        if (d != null) {
            List<FormulaDetail> list = (List<FormulaDetail>) d;
            list.sort(Comparator.comparing(FormulaDetail::getFormula_data_4).thenComparing(FormulaDetail::getFormula_data_5).thenComparing(FormulaDetail::getFormula_data_6)
                    .thenComparing(FormulaDetail::getFormula_data_7).thenComparing(FormulaDetail::getFormula_data_8).thenComparing(FormulaDetail::getFormula_data_9)
                    .thenComparing(FormulaDetail::getFormula_data_15));
            Map<String, List<FormulaDetail>> map = list.stream().collect(Collectors.groupingBy(FormulaDetail::getYarnD, LinkedHashMap::new, Collectors.toList()));

            try (FileInputStream inputStream = new FileInputStream(yarnExcelTemplate); Workbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) {
                Sheet sheet = wb.getSheetAt(0);
                AtomicInteger rowNum = new AtomicInteger(2);
                CellStyle p2 = wb.createCellStyle();
                p2.setDataFormat(wb.createDataFormat().getFormat("0.00"));
                for (String s : map.keySet()) {
                    int sr = rowNum.get();
                    List<FormulaDetail> fList = map.get(s);
                    for (FormulaDetail detail : fList) {
                        Row row = sheet.createRow(rowNum.get());
                        Cell[] cells = new Cell[39];
                        for (int i = 0; i < cells.length; i++) {
                            cells[i] = row.createCell(i);
                        }
                        detail.setYarnCells(cells, p2);
                        rowNum.getAndIncrement();
                    }
                    if (fList.size() > 1) {
                        int er = sr + fList.size() - 1;
                        sheet.addMergedRegion(new CellRangeAddress(sr, er, 0, 0));
                        sheet.addMergedRegion(new CellRangeAddress(sr, er, 1, 1));
                        sheet.addMergedRegion(new CellRangeAddress(sr, er, 2, 2));
                    }
                }
                // 清空response
                response.reset();
                // 设置response的Header
                response.setCharacterEncoding("UTF-8");
                //Content-Disposition的作用：告知浏览器以何种方式显示响应返回的文件，用浏览器打开还是以附件的形式下载到本地保存
                //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3"
                // filename表示文件的默认名称，因为网络传输只支持URL编码的相关支付，因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称
                response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("纱线规格导出" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8"));
                response.setContentType("application/octet-stream");
                wb.write(outputStream);
                outputStream.flush();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }

    private void combo(Sheet sheet, AtomicInteger rowNum, CellStyle p2, Map<String, List<FormulaDetail>> map, boolean flag) {


    }


    @ApiOperation("平方米克重")
    @GetMapping("/gram-mass/total")
    @CrossOrigin(origins = "*")
    public R<List<GramMass>> gramMass() {
        Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_GRAM_MASS);
        if (d != null) {
            List<GramMass> list = (List<GramMass>) d;
            return R.ok(list);
        } else {
            return R.fail();
        }
    }

    @ApiOperation("平方米克重详情")
    @GetMapping("/gram-mass/detail/{gramMass}")
    @CrossOrigin(origins = "*")
    public R<List<GramMassDetail>> gramMassDetail(@PathVariable Float gramMass) {
        Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_GRAM_MASS_DETAIL);
        if (d != null) {
            List<GramMassDetail> list = (List<GramMassDetail>) d;
            list = list.stream().filter(detail -> detail.getGramMass().equals(gramMass)).collect(Collectors.toList());
            return R.ok(list);
        } else {
            return R.fail();
        }
    }

    @ApiOperation("平方米克重导出")
    @GetMapping("/export/gram-mass")
    @CrossOrigin(origins = "*")
    public void gramMassExport(HttpServletResponse response) {
        Object total = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_GRAM_MASS);
        Object detail = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_GRAM_MASS_DETAIL);
        if (total != null && detail != null) {
            List<GramMass> list = (List<GramMass>) total;
            List<GramMassDetail> details = (List<GramMassDetail>) detail;
            try (FileInputStream inputStream = new FileInputStream(gramExcelTemplate); Workbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) {
                Sheet sheet = wb.getSheetAt(0);

                CreationHelper creationHelper = wb.getCreationHelper();
                CellStyle percentStyle = wb.createCellStyle();
                percentStyle.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%"));
                CellStyle p2 = wb.createCellStyle();
                p2.setDataFormat(wb.createDataFormat().getFormat("0.00"));
                AtomicInteger rowNum = new AtomicInteger(1);
                list.forEach(gram -> {
                    Row row = sheet.createRow(rowNum.get());
                    Cell[] cells = new Cell[3];
                    for (int i = 0; i < cells.length; i++) {
                        cells[i] = row.createCell(i);
                    }
                    cells[0].setCellValue(gram.getGramMass());
                    cells[0].setCellStyle(p2);
                    cells[1].setCellValue(gram.getNum());
                    cells[2].setCellValue(gram.getPercent());
                    cells[2].setCellStyle(percentStyle);
                    rowNum.getAndIncrement();
                    Sheet ns = wb.cloneSheet(1);
                    wb.setSheetName(wb.getSheetIndex(ns), gram.getGramMass() + "克明细");
                    AtomicInteger detailRowNum = new AtomicInteger(2);
                    details.stream().filter(d -> d.getGramMass().equals(gram.getGramMass())).forEach(d -> {
                        Row dRow = ns.createRow(detailRowNum.get());
                        Cell[] dCells = new Cell[24];
                        for (int i = 0; i < dCells.length; i++) {
                            dCells[i] = dRow.createCell(i);
                        }
                        dCells[2].setCellStyle(p2);
                        d.setCells(dCells);
                        detailRowNum.getAndIncrement();
                    });
                });
                wb.removeSheetAt(1);
                // 清空response
                response.reset();
                // 设置response的Header
                response.setCharacterEncoding("UTF-8");
                //Content-Disposition的作用：告知浏览器以何种方式显示响应返回的文件，用浏览器打开还是以附件的形式下载到本地保存
                //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3"
                // filename表示文件的默认名称，因为网络传输只支持URL编码的相关支付，因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称
                response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("平方米克重明细导出" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8"));
                response.setContentType("application/octet-stream");
                wb.write(outputStream);
                outputStream.flush();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }

    @ApiOperation("配方统计")
    @GetMapping("/formula/total")
    @CrossOrigin(origins = "*")
    public R<List<FormulaTotal>> formulaTotal() {
        Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_FORMULA_TOTAL);
        if (d != null) {
            List<FormulaTotal> list = (List<FormulaTotal>) d;
            return R.ok(list);
        } else {
            return R.fail();
        }
    }

    @ApiOperation("配方详情")
    @GetMapping("/formula/detail/{height}")
    @CrossOrigin(origins = "*")
    public R<List<FormulaDetail>> formulaDetail(@PathVariable Float height) {
        Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_FORMULA_DETAIL);
        if (d != null) {
            List<FormulaDetail> list = (List<FormulaDetail>) d;
            list = list.stream().filter(detail -> detail.getFormula_data_15().equals(height)).collect(Collectors.toList());
            return R.ok(list);
        } else {
            return R.fail();
        }
    }

    @ApiOperation("配方详情导出")
    @GetMapping("/formula/export/{height}")
    @CrossOrigin(origins = "*")
    public void formulaExport(@PathVariable Float height, HttpServletResponse response) {
        Object d = CacheUtils.get(Constants.IOT_TOKEN, Constants.INDEX_FORMULA_DETAIL);
        if (d != null) {
            List<FormulaDetail> list = (List<FormulaDetail>) d;
            list = list.stream().filter(detail -> detail.getFormula_data_15().equals(height)).collect(Collectors.toList());
            try (FileInputStream inputStream = new FileInputStream(formulaExcelTemplate); Workbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) {
                Sheet sheet = wb.getSheetAt(0);
                AtomicInteger rowNum = new AtomicInteger(2);

                list.forEach(detail -> {
                    Row row = sheet.createRow(rowNum.get());
                    Cell[] cells = new Cell[26];
                    for (int i = 0; i < cells.length; i++) {
                        cells[i] = row.createCell(i);
                    }
                    detail.setCells(cells);
                    rowNum.getAndIncrement();
                });

                // 清空response
                response.reset();
                // 设置response的Header
                response.setCharacterEncoding("UTF-8");
                //Content-Disposition的作用：告知浏览器以何种方式显示响应返回的文件，用浏览器打开还是以附件的形式下载到本地保存
                //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3"
                // filename表示文件的默认名称，因为网络传输只支持URL编码的相关支付，因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称
                response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("毛高" + height + "设备明细导出" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8"));
                response.setContentType("application/octet-stream");
                wb.write(outputStream);
                outputStream.flush();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }

    @ApiOperation("导出产量数据")
    @GetMapping("/export/production")
    @CrossOrigin(origins = "*")
    public void productionExport(String start, String end, HttpServletResponse response) {
        LocalDate localDate = LocalDate.parse(start);
        Date sd = Date.from(localDate.atStartOfDay(ZoneOffset.of("+8")).toInstant());
        localDate = LocalDate.parse(end);
        Date ed = Date.from(localDate.atStartOfDay(ZoneOffset.of("+8")).toInstant());
        List<TwinCalcDay> list = twinCalcDayService.selectTwinCalcDayListByTime(sd, ed);
        try (FileInputStream inputStream = new FileInputStream(totalExcelTemplate); Workbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) {
            CreationHelper creationHelper = wb.getCreationHelper();
            CellStyle percentStyle = wb.createCellStyle();
            percentStyle.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%"));
            CellStyle p2 = wb.createCellStyle();
            p2.setDataFormat(wb.createDataFormat().getFormat("0.00"));

            Map<Date, List<TwinCalcDay>> dayGroup = list.stream().collect(Collectors.groupingBy(TwinCalcDay::getTime, LinkedHashMap::new, Collectors.toList()));
            for (Map.Entry<Date, List<TwinCalcDay>> entry : dayGroup.entrySet()) {
                Sheet sheet = wb.cloneSheet(0);
                wb.setSheetName(wb.getSheetIndex(sheet), DateUtils.parseDateToStr("yyyy-MM-dd", entry.getKey()));
                List<TwinCalcDay> days = entry.getValue();
                AtomicInteger rowNum = new AtomicInteger(2);
                days.forEach(day -> {
                    Row row = sheet.createRow(rowNum.get());
                    Cell[] cells = new Cell[25];
                    for (int i = 0; i < cells.length; i++) {
                        cells[i] = row.createCell(i);
                    }
                    day.setCells(cells);
                    rowNum.getAndIncrement();
                });
            }

            wb.removeSheetAt(0);
            // 清空response
            response.reset();
            // 设置response的Header
            response.setCharacterEncoding("UTF-8");
            //Content-Disposition的作用：告知浏览器以何种方式显示响应返回的文件，用浏览器打开还是以附件的形式下载到本地保存
            //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3"
            // filename表示文件的默认名称，因为网络传输只支持URL编码的相关支付，因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称
            response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("最近7天产量统计" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8"));
            response.setContentType("application/octet-stream");
            wb.write(outputStream);
            outputStream.flush();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

    @ApiOperation("导出停机数据")
    @GetMapping("/export/stops")
    @CrossOrigin(origins = "*")
    public void stopsExport(String start, String end, HttpServletResponse response) throws ParseException {
        Date sTime = DateUtils.parseDate(start, DateUtils.YYYY_MM_DD_HH_MM_SS);
        Date eTime = DateUtils.parseDate(end, DateUtils.YYYY_MM_DD_HH_MM_SS);
        List<TwinCalcStop> list = stopService.selectTwinCalcStopListByDate(sTime, eTime);
        Map<Integer, List<TwinCalcStop>> stopDeviceGroup = list.stream().collect(Collectors.groupingBy(TwinCalcStop::getStopType, LinkedHashMap::new, Collectors.toList()));
        //1停经片停机，2-CCD停机(相机号+断纱/故障)，3-人工停机，4-断电停机，5-设备故障停机，6-落布米数达到停机，7-盘头剩余圈数达到停机
        String[] stopStr = {"停经片停机", "CCD停机(相机号+断纱/故障)", "人工停机", "断电停机", "设备故障停机", "落布米数达到停机", "盘头剩余圈数达到停机"};
        //停机次数
        Integer[] stopNum = new Integer[7];
        Integer totalNum = 0;
        //停机时间
        Long[] stopTime = new Long[7];
        Arrays.fill(stopTime, 0L);
        AtomicReference<Long> totalTime = new AtomicReference<>(0L);
        Map<Long, TwinDevice> deviceMap = deviceService.deviceMap();
        for (Map.Entry<Integer, List<TwinCalcStop>> entry : stopDeviceGroup.entrySet()) {
            Integer stopType = entry.getKey();
            int pos = stopType - 1;
            List<TwinCalcStop> stops = entry.getValue();
            stopNum[pos] = stops.size();
            totalNum += stops.size();
            stops.forEach(stop -> {
                // 调整停机时间
                if (stop.getEndTime().compareTo(eTime) > 0) {
                    stop.setEndTime(eTime);
                }
                //调整开机时间
                if (stop.getStartTime().compareTo(sTime) < 0) {
                    stop.setStartTime(sTime);
                }
                long t = (stop.getEndTime().getTime() - stop.getStartTime().getTime()) / 1000;
                stopTime[pos] += t;
                totalTime.updateAndGet(v -> v + t);
            });
        }
        try (FileInputStream inputStream = new FileInputStream(stopsExcelTemplate); Workbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) {
            CreationHelper creationHelper = wb.getCreationHelper();
            CellStyle percentStyle = wb.createCellStyle();
            percentStyle.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%"));
            CellStyle timeStyle = wb.createCellStyle();
            timeStyle.setDataFormat(creationHelper.createDataFormat().getFormat(DateUtils.YYYY_MM_DD_HH_MM_SS));
            Sheet sheetAt = wb.getSheetAt(0);
            for (int i = 0; i < stopStr.length; i++) {
                Row row = sheetAt.createRow(i + 1);
                Cell[] cells = new Cell[5];
                for (int j = 0; j < cells.length; j++) {
                    cells[j] = row.createCell(j);
                }
                cells[0].setCellValue(stopStr[i]);
                cells[1].setCellValue(stopNum[i]);
                cells[2].setCellValue(stopTime[i]);
                float percentNum = BigDecimal.valueOf(stopNum[i]).divide(BigDecimal.valueOf(totalNum), 4, RoundingMode.HALF_UP).floatValue();
                float percentTimes = BigDecimal.valueOf(stopTime[i]).divide(BigDecimal.valueOf(totalTime.get()), 4, RoundingMode.HALF_UP).floatValue();
                cells[3].setCellValue(percentNum);
                cells[3].setCellStyle(percentStyle);
                cells[4].setCellValue(percentTimes);
                cells[4].setCellStyle(percentStyle);
            }

            Sheet sheet = wb.getSheetAt(1);
            AtomicInteger rowNum = new AtomicInteger(1);
            list.forEach(stop -> {
                Row row = sheet.createRow(rowNum.get());
                Cell[] cells = new Cell[4];
                for (int j = 0; j < cells.length; j++) {
                    cells[j] = row.createCell(j);
                }

                cells[0].setCellValue(deviceMap.get(stop.getDeviceId()).getDeviceName());
                cells[1].setCellValue(stopStr[stop.getStopType() - 1]);
                cells[2].setCellValue(stop.getStartTime());
                cells[2].setCellStyle(timeStyle);
                cells[3].setCellValue(stop.getEndTime());
                cells[3].setCellStyle(timeStyle);
                rowNum.getAndIncrement();
            });

            // 清空response
            response.reset();
            // 设置response的Header
            response.setCharacterEncoding("UTF-8");
            //Content-Disposition的作用：告知浏览器以何种方式显示响应返回的文件，用浏览器打开还是以附件的形式下载到本地保存
            //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3"
            // filename表示文件的默认名称，因为网络传输只支持URL编码的相关支付，因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称
            response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("停机分析" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8"));
            response.setContentType("application/octet-stream");
            wb.write(outputStream);
            outputStream.flush();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

    @ApiOperation("停机分类导出")
    @GetMapping("/export/stops/type/{type}")
    @CrossOrigin(origins = "*")
    public void stopsTypeExport(@PathVariable Integer type, String start, String end, Integer st, Integer et, HttpServletResponse response) {
        LocalDate s = LocalDate.parse(start);
        LocalDate e = LocalDate.parse(end);
        List<TwinCalcStop> list = new ArrayList<>();
        do {
            LocalDateTime ldtS = LocalDateTime.of(s, LocalTime.MIN).plusHours(st);
            LocalDateTime ldtE = LocalDateTime.of(s, LocalTime.MIN).plusHours(et);
            Date date = Date.from(ldtS.toLocalDate().atStartOfDay(ZoneOffset.of("+8")).toInstant());
            Date sTime = Date.from(ldtS.atZone(ZoneId.systemDefault()).toInstant());
            Date eTime = Date.from(ldtE.atZone(ZoneId.systemDefault()).toInstant());
            List<TwinCalcStop> stopList = stopService.selectTwinCalcStopListByDate(sTime, eTime);
            stopList.forEach(stop -> {
                stop.setDataDate(date);
            });
            list.addAll(stopList);
            s = s.plusDays(1);
        } while (!s.isAfter(e));

        List<TwinCalcStop> stopList = list.stream().filter(stop -> stop.getStopType().equals(type)).collect(Collectors.toList());
        Map<Long, TwinDevice> deviceMap = deviceService.deviceMap();
        Map<Date, List<TwinCalcStop>> stopDateGroup = stopList.stream().collect(Collectors.groupingBy(TwinCalcStop::getDataDate, LinkedHashMap::new, Collectors.toList()));
        //1停经片停机，2-CCD停机(相机号+断纱/故障)，3-人工停机，4-断电停机，5-设备故障停机，6-落布米数达到停机，7-盘头剩余圈数达到停机
        String[] stopStr = {"断纱", "断纱", "人工停机", "断电停机", "设备故障停机", "下卷", "叫料"};

        try (FileInputStream inputStream = new FileInputStream(stopsTypeExcelTemplate); Workbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) {
            CreationHelper creationHelper = wb.getCreationHelper();
            CellStyle percentStyle = wb.createCellStyle();
            percentStyle.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%"));
            CellStyle timeStyle = wb.createCellStyle();
            timeStyle.setDataFormat(creationHelper.createDataFormat().getFormat(DateUtils.YYYY_MM_DD_HH_MM_SS));
            CellStyle dateStyle = wb.createCellStyle();
            dateStyle.setDataFormat(creationHelper.createDataFormat().getFormat(DateUtils.YYYY_MM_DD));
            CellStyle p2 = wb.createCellStyle();
            p2.setDataFormat(wb.createDataFormat().getFormat("0.00"));
            Sheet sheetAt = wb.getSheetAt(0);
            int rowNum = 1;
            AtomicInteger rs = new AtomicInteger(1);
            for (Map.Entry<Date, List<TwinCalcStop>> entry : stopDateGroup.entrySet()) {
                List<TwinCalcStop> stops = entry.getValue();
                Row row = sheetAt.createRow(rowNum);
                Cell[] cells = new Cell[7];
                for (int j = 0; j < cells.length; j++) {
                    cells[j] = row.createCell(j);
                }
                cells[0].setCellValue(entry.getKey());
                cells[0].setCellStyle(dateStyle);
                cells[1].setCellValue(st + "-" + et);
                Map<Long, List<TwinCalcStop>> stopDeviceGroup = stops.stream().collect(Collectors.groupingBy(TwinCalcStop::getDeviceId, LinkedHashMap::new, Collectors.toList()));
                cells[2].setCellValue(stopDeviceGroup.size());
                int num = stops.size();
                cells[3].setCellValue(num);
                //0.最小，1.最大,2.总时间
                final long[] time = {999999L, 0, 0};
                Sheet sheet = wb.getSheetAt(1);
                stops.forEach(stop -> {
                    long t = (stop.getEndTime().getTime() - stop.getStartTime().getTime()) / 1000;
                    if (t < time[0]) {
                        time[0] = t;
                    }
                    if (t > time[1]) {
                        time[1] = t;
                    }
                    time[2] += t;

                    Row r1 = sheet.createRow(rs.get());
                    Cell[] cs = new Cell[6];
                    for (int j = 0; j < cs.length; j++) {
                        cs[j] = r1.createCell(j);
                    }
                    cs[0].setCellValue(entry.getKey());
                    cs[0].setCellStyle(dateStyle);
                    cs[1].setCellValue(st + "-" + et);
                    cs[2].setCellValue(deviceMap.get(stop.getDeviceId()).getDeviceName());
                    cs[3].setCellValue(stopStr[stop.getStopType() - 1]);
                    cs[4].setCellValue(stop.getStartTime());
                    cs[4].setCellStyle(timeStyle);
                    cs[5].setCellValue(stop.getEndTime());
                    cs[5].setCellStyle(timeStyle);
                    rs.getAndIncrement();
                });

                cells[4].setCellValue(time[1]);
                cells[5].setCellValue(time[0]);
                float avg = BigDecimal.valueOf(time[2]).divide(BigDecimal.valueOf(num), 2, RoundingMode.HALF_UP).floatValue();
                cells[6].setCellValue(avg);
                cells[6].setCellStyle(p2);
                rowNum++;
            }

            // 清空response
            response.reset();
            // 设置response的Header
            response.setCharacterEncoding("UTF-8");
            //Content-Disposition的作用：告知浏览器以何种方式显示响应返回的文件，用浏览器打开还是以附件的形式下载到本地保存
            //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3"
            // filename表示文件的默认名称，因为网络传输只支持URL编码的相关支付，因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称
            response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(stopStr[type - 1] + "分析" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8"));
            response.setContentType("application/octet-stream");
            wb.write(outputStream);
            outputStream.flush();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

    @ApiOperation("告警统计导出")
    @GetMapping("/export/alarms")
    @CrossOrigin(origins = "*")
    public void alarmsExport(String start, String end, HttpServletResponse response) throws ParseException {
        Date sTime = DateUtils.parseDate(start, DateUtils.YYYY_MM_DD_HH_MM_SS);
        Date eTime = DateUtils.parseDate(end, DateUtils.YYYY_MM_DD_HH_MM_SS);
        List<TwinCalcAlarms> list = alarmsService.selectTwinCalcAlarmsListByDate(sTime, eTime);
        list.sort((Comparator.comparing(TwinCalcAlarms::getDeviceId)));
        Map<Long, TwinDevice> deviceMap = deviceService.deviceMap();
        Map<Long, List<TwinCalcAlarms>> alarmsGroup = list.stream().collect(Collectors.groupingBy(TwinCalcAlarms::getDeviceId, LinkedHashMap::new, Collectors.toList()));
        String[] alarmStr = {"GB1伺服通讯故障", "GB2伺服通讯故障", "GB3伺服通讯故障", "GB4伺服通讯故障", "GB5伺服通讯故障", "牵引伺服通讯故障", "卷曲伺服通讯故障", "断纱故障", "电源故障", "联动未完全啮合", "米长到达", "GB1剩余圈数报警", "GB2剩余圈数报警", "GB3剩余圈数报警", "GB4剩余圈数报警", "GB5剩余圈数报警", "主轴变频掉线", "主轴变频故障", "GB1伺服故障", "GB2伺服故障", "GB3伺服故障", "GB4伺服故障", "GB5伺服故障", "牵引伺服故障", "卷曲伺服故障", "拍照自停", "CCD位置报警信息显示"};
        try (FileInputStream inputStream = new FileInputStream(alarmsExcelTemplate); Workbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) {
            CreationHelper creationHelper = wb.getCreationHelper();
            CellStyle percentStyle = wb.createCellStyle();
            percentStyle.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%"));
            CellStyle timeStyle = wb.createCellStyle();
            timeStyle.setDataFormat(creationHelper.createDataFormat().getFormat(DateUtils.YYYY_MM_DD_HH_MM_SS));
            Sheet sheetAt = wb.getSheetAt(0);
            int rowNum = 2;
            for (Map.Entry<Long, List<TwinCalcAlarms>> entry : alarmsGroup.entrySet()) {
                List<TwinCalcAlarms> alarms = entry.getValue();
                Row row = sheetAt.createRow(rowNum);
                Cell[] cells = new Cell[29];
                for (int j = 0; j < cells.length; j++) {
                    cells[j] = row.createCell(j);
                }
                cells[0].setCellValue(deviceMap.get(entry.getKey()).getDeviceName());
                cells[1].setCellValue(alarms.size());
                Map<Integer, Long> temp = alarms.stream().collect(Collectors.groupingBy(TwinCalcAlarms::getAlarmType, Collectors.counting()));
                for (Integer v : temp.keySet()) {
                    cells[v + 1].setCellValue(temp.get(v));
                }
                rowNum++;
            }

            Sheet sheet = wb.getSheetAt(1);
            AtomicInteger rn = new AtomicInteger(1);
            list.forEach(alarms -> {
                Row row = sheet.createRow(rn.get());
                Cell[] cells = new Cell[3];
                for (int j = 0; j < cells.length; j++) {
                    cells[j] = row.createCell(j);
                }

                cells[0].setCellValue(deviceMap.get(alarms.getDeviceId()).getDeviceName());
                cells[1].setCellValue(alarmStr[alarms.getAlarmType() - 1]);
                cells[2].setCellValue(alarms.getStartTime());
                cells[2].setCellStyle(timeStyle);
                rn.getAndIncrement();
            });

            // 清空response
            response.reset();
            // 设置response的Header
            response.setCharacterEncoding("UTF-8");
            //Content-Disposition的作用：告知浏览器以何种方式显示响应返回的文件，用浏览器打开还是以附件的形式下载到本地保存
            //attachment表示以附件方式下载 inline表示在线打开 "Content-Disposition: inline; filename=文件名.mp3"
            // filename表示文件的默认名称，因为网络传输只支持URL编码的相关支付，因此需要将文件名URL编码后进行传输,前端收到后需要反编码才能获取到真正的名称
            response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("告警统计" + DateUtils.dateTimeNow() + ".xlsx", "UTF-8"));
            response.setContentType("application/octet-stream");
            wb.write(outputStream);
            outputStream.flush();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }
}