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.biz.service.impl.BrokenYarnExportServiceImpl;
import com.ruoyi.biz.tools.Tools;
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 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.xddf.usermodel.chart.BarDirection;
import org.apache.poi.xssf.usermodel.XSSFSheet;
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 ITwinCalcStopService stopService;
    @Resource
    private ITwinCalcAlarmsService alarmsService;
    @Resource
    private ITwinDeviceService deviceService;
    @Resource
    private IIotService iotService;
    @Resource
    private AsyncServiceImpl asyncService;
    @Resource
    private BrokenYarnExportServiceImpl exportService;
    @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;
    @Value("${excel.brokenYarn}")
    private String brokenYarnExcelTemplate;

    @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(Integer day, 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()));
            LocalDateTime localDateTime = LocalDateTime.now();
            if (localDateTime.getHour() < 7) {
                //当天7点前，则是前天数据
                localDateTime = localDateTime.minusDays(2);
            } else {
                //7点后，就是昨日数据
                localDateTime = localDateTime.minusDays(1);
            }
            LocalDate localDate = localDateTime.toLocalDate();
            Date sd = Date.from(localDate.atStartOfDay(ZoneOffset.of("+8")).toInstant());
            List<TwinCalcDay> calcDayList = twinCalcDayService.selectTwinCalcDayListByTime(sd, sd);
            Map<Long, BigDecimal> decimalMap = calcDayList.stream().collect(Collectors.toMap(TwinCalcDay::getDeviceId, TwinCalcDay::getLength));
            try (FileInputStream inputStream = new FileInputStream(yarnExcelTemplate); XSSFWorkbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) {
                Sheet sheet = wb.getSheetAt(0);
                AtomicInteger rowNum = new AtomicInteger(2);
                CellStyle percentStyle = wb.createCellStyle();
                percentStyle.setDataFormat(wb.createDataFormat().getFormat("0.00%"));
                // 垂直居中
                percentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                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));
                    }
                }
                XSSFSheet sheet2 = wb.getSheetAt(1);
                wb.setSheetName(1, "未来产能预期（" + day + "天）");
                AtomicInteger rn = new AtomicInteger(2);
                //1.先按底纱分组
                Map<String, List<FormulaDetail>> map2 = list.stream().collect(Collectors.groupingBy(FormulaDetail::getYarnDd, LinkedHashMap::new, Collectors.toList()));
                for (String s : map2.keySet()) {
                    int sr = rn.get();
                    List<FormulaDetail> l12list = map2.get(s);
                    //2.再按规格分组
                    Map<Integer, List<FormulaDetail>> d1map = l12list.stream().collect(Collectors.groupingBy(FormulaDetail::getFormula_data_6, LinkedHashMap::new, Collectors.toList()));
                    for (Integer l3d : d1map.keySet()) {
                        int ssr = rn.get();
                        List<FormulaDetail> l3list = d1map.get(l3d);
                        //3.再按盘头根数和毛高分组
                        Map<String, List<FormulaDetail>> d2map = l3list.stream().collect(Collectors.groupingBy(FormulaDetail::getYarnPt, LinkedHashMap::new, Collectors.toList()));
                        for (String pt : d2map.keySet()) {
                            List<FormulaDetail> ptList = d2map.get(pt);
                            FormulaDetail detail = ptList.get(0);
                            String device = "";
                            BigDecimal length = BigDecimal.ZERO;
                            for (FormulaDetail dd : ptList) {
                                BigDecimal temp = decimalMap.get(dd.getDeviceId());
                                if (temp != null) {
                                    length = length.add(temp);
                                }
//                                device += dd.getDevice() + "/";
                            }
                            BigDecimal yLength = length.multiply(BigDecimal.valueOf(day));
                            Row row = sheet2.createRow(rn.get());
                            Cell[] cells = new Cell[12];
                            for (int i = 0; i < cells.length; i++) {
                                cells[i] = row.createCell(i);
                            }
                            cells[0].setCellValue(rn.get() - 1);
                            cells[1].setCellValue(detail.getFormula_data_4());
                            cells[2].setCellValue(detail.getFormula_data_5());
                            cells[3].setCellValue(detail.getFormula_data_6());
                            cells[4].setCellValue(detail.getFormula_data_10());
                            cells[5].setCellValue(detail.getFormula_data_11());
                            cells[6].setCellValue(detail.getFormula_data_12());
                            cells[7].setCellValue(detail.getFormula_data_15());
                            cells[8].setCellValue(length.doubleValue());
                            cells[8].setCellStyle(p2);
                            cells[9].setCellValue(yLength.doubleValue());
                            cells[9].setCellStyle(p2);
                            //规格占比
                            BigDecimal percent1 = BigDecimal.valueOf(ptList.size()).divide(BigDecimal.valueOf(list.size()), 4, RoundingMode.HALF_UP);
                            cells[10].setCellValue(percent1.doubleValue());
                            cells[10].setCellStyle(percentStyle);
                            //底纱占比
                            BigDecimal percent2 = BigDecimal.valueOf(l12list.size()).divide(BigDecimal.valueOf(list.size()), 4, RoundingMode.HALF_UP);
                            cells[11].setCellValue(percent2.doubleValue());
                            cells[11].setCellStyle(percentStyle);
                            rn.getAndIncrement();
                        }
                        if (d2map.size() > 1) {
                            int er = rn.get() - 1;
                            sheet2.addMergedRegion(new CellRangeAddress(ssr, er, 1, 1));
                            sheet2.addMergedRegion(new CellRangeAddress(ssr, er, 2, 2));
                            sheet2.addMergedRegion(new CellRangeAddress(ssr, er, 3, 3));
                        }
                    }
                    if (d1map.size() > 1) {
                        int er = rn.get() - 1;
                        sheet2.addMergedRegion(new CellRangeAddress(sr, er, 11, 11));
                    }
                }
                int endRn = rn.get() - 1;
                int[] pos = {13, 1, 20, endRn + 1};
                int[] xdata = {2, endRn, 0, 0};
                int[] data = {2, endRn, 10, 10};
                exportService.drawBar(sheet2, "规格占比", "规格序号", pos, xdata, data, BarDirection.COL);
                // 清空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("/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/broken-yarn")
    @CrossOrigin(origins = "*")
    public void brokenYarnExport(String date, HttpServletResponse response) throws ParseException {
        LocalDate localDate = LocalDate.parse(date);
        try (FileInputStream inputStream = new FileInputStream(brokenYarnExcelTemplate); XSSFWorkbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) {
            //1.基本信息表
            exportService.base(wb, localDate);
            //2.停机原因分析
            exportService.stop(wb, localDate);
            //4.断纱停机TOP排名
            exportService.top(wb, localDate);
            // 清空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("断纱分析日报" + localDate + ".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));
            CellStyle rightStyle = wb.createCellStyle();
            rightStyle.setAlignment(HorizontalAlignment.RIGHT);
            Sheet sheetAt = wb.getSheetAt(0);
            Row title = sheetAt.getRow(0);
            Cell cell = title.getCell(0);
            cell.setCellValue("停机原因分析（" + start + "至" + end + "）");
            for (int i = 0; i < stopStr.length; i++) {
                Row row = sheetAt.createRow(i + 2);
                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(Tools.convertHMS(stopTime[i]));
                cells[2].setCellStyle(rightStyle);
                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[5];
                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);
                Duration duration = Duration.between(stop.getStartTime().toInstant(), stop.getEndTime().toInstant());
                cells[4].setCellValue(Tools.convertHMS(duration.getSeconds()));
                cells[4].setCellStyle(rightStyle);
                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();
        }
    }
}