twin-service/ruoyi-admin/src/main/java/com/ruoyi/order/controller/OrderApiController.java

package com.ruoyi.order.controller;

import com.ruoyi.biz.domain.TwinCalcDay;
import com.ruoyi.biz.domain.TwinFormulaInfo;
import com.ruoyi.biz.service.ITwinCalcDayService;
import com.ruoyi.biz.service.ITwinFormulaInfoService;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.order.domain.MesStock;
import com.ruoyi.order.domain.TwinOrder;
import com.ruoyi.order.domain.TwinOrderDetail;
import com.ruoyi.order.domain.VmsStock;
import com.ruoyi.order.service.ITwinOrderService;
import com.ruoyi.order.utils.MssqlUtil;
import com.ruoyi.order.vo.BomVO;
import com.ruoyi.order.vo.SkuVO;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTDLbls;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

/**
 * @author ruoyi
 */
@Api("数据接口")
@RestController
@Slf4j
@RequestMapping()
public class OrderApiController extends BaseController {
    @Resource
    private ITwinOrderService orderService;
    @Resource
    private ITwinFormulaInfoService formulaInfoService;
    @Resource
    private ITwinCalcDayService dayService;
    private CellStyle p2;
    private CellStyle percentStyle;

    private CellStyle bk;

    @ApiOperation("查看当前库存")
    @GetMapping("/api/stock")
    @CrossOrigin(origins = "*")
    @ResponseBody
    public Map<String, Object> mesStock() {
        LocalDate localDate = LocalDate.now();
        return MssqlUtil.mesStock(localDate);
    }

    @ApiOperation("查看当前库存")
    @GetMapping("/api/mes-trend")
    @CrossOrigin(origins = "*")
    @ResponseBody
    public Map<String, MesStock> mesTrend() {
        LocalDate localDate = LocalDate.now();
        Map<String, MesStock> map = MssqlUtil.mesTrend(localDate);
        List<MesStock> mesList = new ArrayList<>(map.values());
        return map;
    }

    @ApiOperation("导出白柸预测")
    @GetMapping("/api/export/order")
    @CrossOrigin(origins = "*")
    public void orderExport(String start, String end, HttpServletResponse response) {
        //查询订单
        List<TwinOrder> orderList = orderService.selectTwinOrderListByDate(start, end);
        //按订单ID分组
        Map<Long, TwinOrder> orderMap = orderList.stream().collect(Collectors.toMap(TwinOrder::getOderId, o -> o));
        List<TwinFormulaInfo> infos = formulaInfoService.selectTwinFormulaInfoList(new TwinFormulaInfo());
        LocalDateTime ldt = LocalDateTime.now();
        Date sd;
        if (ldt.getHour() > 7) {
            sd = DateUtils.toDate(LocalDate.now().minusDays(1));
        } else {
            sd = DateUtils.toDate(LocalDate.now().minusDays(2));
        }
        List<TwinCalcDay> dayList = dayService.selectTwinCalcDayListByTime(sd, sd);
        //按设备ID分组
        Map<Long, TwinCalcDay> dayMap = dayList.stream().collect(Collectors.toMap(TwinCalcDay::getDeviceId, o -> o));
        List<VmsStock> stocks = MssqlUtil.vmsStock();
        List<TwinOrderDetail> all = new ArrayList<>();
        orderList.forEach(to -> all.addAll(to.getTwinOrderDetailList()));
        //按规格分组
        Map<String, List<TwinOrderDetail>> allMap = all.stream().collect(Collectors.groupingBy(TwinOrderDetail::getBomSpec, LinkedHashMap::new, Collectors.toList()));
        List<BomVO> bomList = new ArrayList<>();
        List<SkuVO> skuList = new ArrayList<>();
        for (String spec : allMap.keySet()) {
            List<TwinOrderDetail> todList = allMap.get(spec);
            //匹配白柸布库存
            Float kc = 0f;
            VmsStock stock = matchStock(spec, stocks);
            if (stock != null) {
                kc = stock.getQty();
            }
            //匹配生产设备
            List<TwinFormulaInfo> matchFormula = matchFormula(spec, infos);
            BigDecimal cl = BigDecimal.ZERO;
            String deviceIds = "";
            for (TwinFormulaInfo match : matchFormula) {
                if (StringUtils.isNotEmpty(deviceIds)) {
                    deviceIds += ",";
                }
                deviceIds += match.getDeviceId();
                TwinCalcDay day = dayMap.get(match.getDeviceId());
                if (day != null) {
                    cl = cl.add(day.getWeight());
                }
            }

            cl = cl.multiply(BigDecimal.valueOf(1000));


            BomVO bom = new BomVO();
            bom.setDeviceIds(deviceIds);
            bom.setLast(cl.doubleValue());
            //统计订单数量
            long orders = todList.stream().map(TwinOrderDetail::getOderId).distinct().count();
            double totalWeight = todList.stream().mapToDouble(o -> o.getWeight().doubleValue()).sum();
            TwinOrderDetail tod = todList.get(0);
            bom.setBomCode(tod.getBomCode());
            bom.setBomName(tod.getBomName());
            bom.setBomSpec(spec);

            bom.setXql(totalWeight);
            bom.setKcl(kc);
            bom.setOrders(orders);
            double ycl = cl.multiply(BigDecimal.TEN).doubleValue();
            double cz = totalWeight - kc - ycl;
            bom.setYcl(ycl);
            bom.setCz(cz);
            bom.setCl(cz / totalWeight);

            SkuVO sku = new SkuVO();
            sku.setBomCode(tod.getBomCode());
            sku.setBomName(tod.getBomName());
            sku.setBomSpec(spec);
            if (stock != null) {
                sku.setSku(stock.getSku());
                sku.setDesc(stock.getDesc());
                sku.setNum(stock.getNum());
                sku.setQty(stock.getQty());
            } else {
                sku.setSku("");
                sku.setDesc("");
                sku.setNum(0);
                sku.setQty(0f);
            }
            sku.setXql(bom.getXql());
            List<TwinOrder> skuOrder = new ArrayList<>();
            //按订单分组
            Map<Long, List<TwinOrderDetail>> map = todList.stream().collect(Collectors.groupingBy(TwinOrderDetail::getOderId, LinkedHashMap::new, Collectors.toList()));
            for (Long oid : map.keySet()) {
                TwinOrder to = orderMap.get(oid);
                to.setTwinOrderDetailList(map.get(oid));
                skuOrder.add(to);
            }
            sku.setOrderList(skuOrder);
            bom.setOrderList(skuOrder);
            bomList.add(bom);
            skuList.add(sku);
        }

        try (InputStream inputStream = getClass().getClassLoader().getResourceAsStream("tpl/order.xlsx"); XSSFWorkbook wb = new XSSFWorkbook(inputStream); OutputStream outputStream = new BufferedOutputStream(response.getOutputStream())) {
            CreationHelper creationHelper = wb.getCreationHelper();
            percentStyle = wb.createCellStyle();
            percentStyle.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%"));
            percentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            p2 = wb.createCellStyle();
            p2.setDataFormat(wb.createDataFormat().getFormat("0.00"));
            p2.setVerticalAlignment(VerticalAlignment.CENTER);
            bk = wb.createCellStyle();
            bk.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
            bk.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            XSSFSheet sheet0 = wb.getSheetAt(0);
            Cell title = sheet0.getRow(0).getCell(0);
            title.setCellValue("白柸布靶向生产测算-10天（" + start + " 至 " + end + "）");

            sheet0(sheet0, bomList);
            sheet1(wb.getSheetAt(1), stocks);
            sheet2(wb.getSheetAt(2), skuList);
            sheet3(wb.getSheetAt(3), bomList);
            sheet4(wb.getSheetAt(4), infos);
            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("白柸布靶向预测" + start + "至" + end + ".xlsx", "UTF-8"));
            response.setContentType("application/octet-stream");
            wb.write(outputStream);
            outputStream.flush();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }


    /**
     * 总览
     *
     * @param sheet   sheet
     * @param bomList 物料列表
     */
    private void sheet0(XSSFSheet sheet, List<BomVO> bomList) {
        AtomicInteger num = new AtomicInteger(3);
        BomVO bom = new BomVO();
        bom.setOrders(0L);
        bom.setXql(0d);
        bom.setKcl(0f);
        bom.setYcl(0d);
        bom.setCz(0d);
        bom.setCl(0d);
        bomList.forEach(obj -> {
            Row row = sheet.createRow(num.getAndIncrement());
            Cell[] cells = new Cell[10];
            for (int i = 0; i < cells.length; i++) {
                cells[i] = row.createCell(i);
            }
            cells[0].setCellValue(obj.getBomCode());
            cells[1].setCellValue(obj.getBomName());
            cells[2].setCellValue(obj.getBomSpec());
            cells[3].setCellValue(obj.getOrders());
            cells[4].setCellValue(obj.getXql());
            cells[5].setCellValue(obj.getKcl());
            cells[7].setCellValue(obj.getYcl());
            cells[8].setCellValue(obj.getCz());
            cells[9].setCellValue(obj.getCl());
            cells[9].setCellStyle(percentStyle);
            bom.setOrders(bom.getOrders() + obj.getOrders());
            bom.setXql(bom.getXql() + obj.getXql());
            bom.setKcl(bom.getKcl() + obj.getKcl());
            bom.setYcl(bom.getYcl() + obj.getYcl());
            bom.setCz(bom.getCz() + obj.getCz());
            bom.setCl(bom.getCl() + obj.getCl());
        });
        Row row = sheet.createRow(num.getAndIncrement());
        Cell[] cells = new Cell[10];
        for (int i = 0; i < cells.length; i++) {
            cells[i] = row.createCell(i);
        }
        cells[0].setCellValue("合计");
//        cells[3].setCellValue(bom.getOrders());
        cells[4].setCellValue(bom.getXql());
        cells[5].setCellValue(bom.getKcl());
        cells[7].setCellValue(bom.getYcl());
        cells[8].setCellValue(bom.getCz());
        for (int i = 4; i < 9; i++) {
            cells[i].setCellStyle(p2);
        }
        cells[9].setCellValue(bom.getCl() / bomList.size());
        cells[9].setCellStyle(percentStyle);
        // 创建一个画布
        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        // 前四个默认0，[0,4]：从0列4行开始;[7,20]:到7列20行结束
        // 默认宽度(14-8)*12
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 11, 3, 17, 20);
        // 创建一个chart对象
        XSSFChart chart = drawing.createChart(anchor);
        // 标题
        chart.setTitleText("需求重量占比图");
        // 标题是否覆盖图表
        chart.setTitleOverlay(false);

        // 图例位置
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.TOP_RIGHT);

        XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(3, num.get() - 2, 2, 2));
        // 数据1，单元格范围位置[1, 0]到[1, 6]
        XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(3, num.get() - 2, 4, 4));
//         XDDFChartData data = chart.createData(ChartTypes.PIE3D, null, null);
        XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
        // 设置为可变颜色
        data.setVaryColors(true);
        // 图表加载数据
        data.addSeries(countries, area);

        // 绘制
        chart.plot(data);
        CTDLbls dLbls = chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).addNewDLbls();
        dLbls.addNewShowVal().setVal(false);
        dLbls.addNewShowLegendKey().setVal(false);
        dLbls.addNewShowCatName().setVal(true);
        // 类别名称
        dLbls.addNewShowSerName().setVal(false);
        dLbls.addNewShowPercent().setVal(true);
        // 百分比
        dLbls.addNewShowLeaderLines().setVal(true);
        // 引导线
        dLbls.setSeparator("\n");
        // 分隔符为分行符

    }

    /**
     * 白柸布全量
     *
     * @param sheet  sheet
     * @param stocks 库存
     */
    private void sheet1(Sheet sheet, List<VmsStock> stocks) {
        AtomicInteger num = new AtomicInteger(2);
        stocks.forEach(stock -> {
            Row row = sheet.createRow(num.getAndIncrement());
            Cell[] cells = new Cell[6];
            for (int i = 0; i < cells.length; i++) {
                cells[i] = row.createCell(i);
            }
            cells[0].setCellValue(stock.getSku());
            cells[1].setCellValue(stock.getDesc());
            cells[2].setCellValue(stock.getNum());
            cells[3].setCellValue(stock.getQty());
            cells[3].setCellStyle(p2);
//            cells[4].setCellValue();
//            cells[5].setCellValue();
        });
    }

    /**
     * 白柸库存匹配
     *
     * @param sheet   sheet
     * @param skuList 库存
     */
    private void sheet2(Sheet sheet, List<SkuVO> skuList) {
        AtomicInteger num = new AtomicInteger(3);
        skuList.forEach(sku -> {
            int sr = num.get();
            Row row = sheet.createRow(num.getAndIncrement());
            Cell[] cells = new Cell[18];
            for (int i = 0; i < cells.length; i++) {
                cells[i] = row.createCell(i);
            }
            cells[0].setCellValue(sku.getSku());
            cells[1].setCellValue(sku.getDesc());
            cells[2].setCellValue(sku.getNum());
            cells[3].setCellValue(sku.getQty());
            cells[4].setCellValue(sku.getXql());
            cells[3].setCellStyle(p2);
            cells[4].setCellStyle(p2);
            cells[5].setCellValue(sku.getBomCode());
            cells[6].setCellValue(sku.getBomName());
            cells[7].setCellValue(sku.getBomSpec());
            boolean out = false;
            for (TwinOrder order : sku.getOrderList()) {
                if (out) {
                    row = sheet.createRow(num.getAndIncrement());
                    cells = new Cell[18];
                    for (int i = 0; i < cells.length; i++) {
                        cells[i] = row.createCell(i);
                    }
                }
                cells[8].setCellValue(DateUtils.parseDateToStr(order.getOrderDate()));
                cells[9].setCellValue(order.getOrderCode());
                cells[10].setCellValue(order.getCustomerCode());
                cells[11].setCellValue(order.getCustomerName());
                boolean in = false;
                int ssr = num.get() - 1;
                for (TwinOrderDetail detail : order.getTwinOrderDetailList()) {
                    if (in) {
                        row = sheet.createRow(num.getAndIncrement());
                        cells = new Cell[18];
                        for (int i = 0; i < cells.length; i++) {
                            cells[i] = row.createCell(i);
                        }
                    }
                    cells[12].setCellValue(detail.getColor());
                    cells[13].setCellValue(detail.getPrice().doubleValue());
                    cells[14].setCellValue(detail.getTotalPrice().doubleValue());
                    cells[15].setCellValue(detail.getUnit());
                    cells[16].setCellValue(detail.getNum().doubleValue());
                    cells[17].setCellValue(detail.getWeight().doubleValue());
                    in = true;
                }
                out = true;
                int eer = num.get() - 1;
                for (int i = 8; i < 12; i++) {
                    sheet.addMergedRegion(new CellRangeAddress(ssr, eer, i, i));
                }
            }
            int er = num.get() - 1;
            for (int i = 0; i < 8; i++) {
                sheet.addMergedRegion(new CellRangeAddress(sr, er, i, i));
            }
        });
    }

    /**
     * 经编机规格分布
     *
     * @param sheet   sheet
     * @param bomList 库存
     */
    private void sheet3(Sheet sheet, List<BomVO> bomList) {
        AtomicInteger num = new AtomicInteger(3);
        bomList.forEach(obj -> {
            int sr = num.get();
            Row row = sheet.createRow(num.getAndIncrement());
            Cell[] cells = new Cell[18];
            for (int i = 0; i < cells.length; i++) {
                cells[i] = row.createCell(i);
            }
            cells[0].setCellValue(obj.getBomCode());
            cells[1].setCellValue(obj.getBomName());
            cells[2].setCellValue(obj.getBomSpec());
            cells[3].setCellValue(obj.getXql());
            cells[4].setCellValue(obj.getDeviceIds());
            cells[5].setCellValue(obj.getLast());
            cells[6].setCellValue(obj.getYcl());

            boolean out = false;
            for (TwinOrder order : obj.getOrderList()) {
                if (out) {
                    row = sheet.createRow(num.getAndIncrement());
                    cells = new Cell[18];
                    for (int i = 0; i < cells.length; i++) {
                        cells[i] = row.createCell(i);
                    }
                }
                Cell xql = cells[7];
                cells[8].setCellValue(DateUtils.parseDateToStr(order.getOrderDate()));
                cells[9].setCellValue(order.getOrderCode());
                cells[10].setCellValue(order.getCustomerCode());
                cells[11].setCellValue(order.getCustomerName());
                boolean in = false;
                int ssr = num.get() - 1;
                BigDecimal ow = BigDecimal.ZERO;
                for (TwinOrderDetail detail : order.getTwinOrderDetailList()) {
                    if (in) {
                        row = sheet.createRow(num.getAndIncrement());
                        cells = new Cell[18];
                        for (int i = 0; i < cells.length; i++) {
                            cells[i] = row.createCell(i);
                        }
                    }
                    cells[12].setCellValue(detail.getColor());
                    cells[13].setCellValue(detail.getPrice().doubleValue());
                    cells[14].setCellValue(detail.getTotalPrice().doubleValue());
                    cells[15].setCellValue(detail.getUnit());
                    cells[16].setCellValue(detail.getNum().doubleValue());
                    cells[17].setCellValue(detail.getWeight().doubleValue());
                    ow = ow.add(detail.getWeight());
                    in = true;
                }
                xql.setCellValue(ow.doubleValue());
                xql.setCellStyle(p2);
                out = true;
                int eer = num.get() - 1;
                for (int i = 7; i < 12; i++) {
                    sheet.addMergedRegion(new CellRangeAddress(ssr, eer, i, i));
                }
            }
            int er = num.get() - 1;
            for (int i = 0; i < 7; i++) {
                sheet.addMergedRegion(new CellRangeAddress(sr, er, i, i));
            }
        });
    }


    /**
     * 白柸布库存--全量
     *
     * @param sheet sheet
     * @param infos 库存
     */
    private void sheet4(Sheet sheet, List<TwinFormulaInfo> infos) {
        AtomicInteger num = new AtomicInteger(3);
        infos.sort(Comparator.comparing(TwinFormulaInfo::getFd18).reversed().thenComparing(TwinFormulaInfo::getFd16).thenComparing(TwinFormulaInfo::getFd17).thenComparing(TwinFormulaInfo::getFd3));
        infos.forEach(info -> {
            Row row = sheet.createRow(num.getAndIncrement());
            Cell[] cells = new Cell[35];
            for (int i = 0; i < cells.length; i++) {
                cells[i] = row.createCell(i);
            }
            cells[0].setCellValue(info.getFd18());
            cells[1].setCellValue(info.getFd16());
            cells[2].setCellValue(info.getFd17());
            cells[3].setCellValue(info.getFd3());
            cells[4].setCellValue(info.getFd15());
            cells[5].setCellValue(info.getFd4());
            cells[6].setCellValue(info.getFd5());
            cells[7].setCellValue(info.getFd6());
            cells[8].setCellValue(info.getFd2());
            cells[9].setCellValue(info.getFd1());
            cells[10].setCellValue(info.getFd13());
            cells[11].setCellValue(info.getFd14());
            cells[12].setCellValue(info.getFd24());
            cells[13].setCellValue(info.getFd25());

            cells[14].setCellValue(info.getCd10());
            cells[15].setCellValue(info.getCd11());
            cells[16].setCellValue(info.getCd12());
            cells[17].setCellValue(info.getCd13());
            cells[18].setCellValue(info.getCd14());
            cells[19].setCellValue(info.getCd15());
            cells[20].setCellValue(info.getCd16());
            cells[21].setCellValue(info.getCd17());
            cells[22].setCellValue(info.getCd18());
            cells[23].setCellValue(info.getCd19());

            cells[24].setCellValue(info.getFd19());
            cells[25].setCellValue(info.getFd20());
            cells[26].setCellValue(info.getFd21());
            cells[27].setCellValue(info.getFd22());
            cells[28].setCellValue(info.getFd23());

            cells[29].setCellValue(info.getFd7());
            cells[30].setCellValue(info.getFd8());
            cells[31].setCellValue(info.getFd9());
            cells[32].setCellValue(info.getFd10());
            cells[33].setCellValue(info.getFd11());
            cells[34].setCellValue(info.getFd12());
        });
    }

    /**
     * 匹配库存
     *
     * @param spec   规格
     * @param stocks 库存
     * @return 结果
     */
    private VmsStock matchStock(String spec, List<VmsStock> stocks) {
        //按重量分组
        Map<Integer, List<VmsStock>> stockMap = stocks.stream().collect(Collectors.groupingBy(VmsStock::getWeight, LinkedHashMap::new, Collectors.toList()));
        if (spec != null) {
            String[] tmp = spec.split("\\*");
            if (tmp.length == 3) {
                Float length = Float.parseFloat(tmp[0]);
                Float width = Float.parseFloat(tmp[1]);
                int weight = (int) (Float.parseFloat(tmp[2]) * 1000);
                List<VmsStock> tempList = stockMap.get(weight);
                if (tempList != null) {
                    for (VmsStock stock : tempList) {
                        if (length > stock.getLength() || width > stock.getLength()) {
                            return stock;
                        }
                    }
                }
            }
        }

        return null;
    }

    /**
     * 匹配产线
     *
     * @param spec 规格
     * @param list 产线
     * @return 结果
     */
    private List<TwinFormulaInfo> matchFormula(String spec, List<TwinFormulaInfo> list) {
        List<TwinFormulaInfo> result = new ArrayList<>();
        //按重量分组
        Map<Float, List<TwinFormulaInfo>> formulaMap = list.stream().collect(Collectors.groupingBy(TwinFormulaInfo::getFd18, LinkedHashMap::new, Collectors.toList()));
        if (spec != null) {
            String[] tmp = spec.split("\\*");
            if (tmp.length == 3) {
                Float length = Float.parseFloat(tmp[0]);
                Float width = Float.parseFloat(tmp[1]);
                Float weight = Float.parseFloat(tmp[2]);
                List<TwinFormulaInfo> infos = formulaMap.get(weight);
                if (infos != null) {
                    for (TwinFormulaInfo info : infos) {
                        if (length.equals(info.getFd16()) && width.equals(info.getFd17()) ||
                                length.equals(info.getFd17()) && width.equals(info.getFd16())) {
                            result.add(info);
                        }
                    }
                }
            }
        }
        return result;
    }
}