Examples with Equipment com.eservice.api.model.contract.Equipment used on opensource projects

Example 1 with Equipment

use of com.eservice.api.model.contract.Equipment in project sinsim by WilsonHu.

the class MachineOrderController method exportToSaleExcel.

@PostMapping("/exportToSaleExcel")
public Result exportToSaleExcel(Integer id, Integer contract_id, String order_num, String contract_num, String status, String sellman, String customer, String marketGroupName, // 这个是查询创建日期
String query_start_time, // 这个是查询创建日期
String query_finish_time, // 这个是查询审核日期
String queryStartTimeSign, // 这个是查询审核日期
String queryFinishTimeSign, String machine_name, String oderSignCurrentStep, String searchDepartment, @RequestParam(defaultValue = "true") Boolean is_fuzzy) {
    List<MachineOrderDetail> list = machineOrderService.selectOrder(id, contract_id, order_num, contract_num, status, sellman, customer, marketGroupName, query_start_time, query_finish_time, queryStartTimeSign, queryFinishTimeSign, machine_name, oderSignCurrentStep, searchDepartment, is_fuzzy);
    HSSFWorkbook wb = null;
    FileOutputStream out = null;
    String downloadPath = "";
    /*
                返回给docker外部下载
                    */
    String downloadPathForNginx = "";
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
    try {
        // 生成一个空的Excel文件
        wb = new HSSFWorkbook();
        Sheet sheet1 = wb.createSheet("销售报表");
        // 设置标题行格式
        HSSFCellStyle headcellstyle = wb.createCellStyle();
        HSSFFont headfont = wb.createFont();
        headfont.setFontHeightInPoints((short) 10);
        // 粗体显示
        headfont.setBold(true);
        headcellstyle.setFont(headfont);
        Row row;
        // 新创建一行，行号为row+1
        row = sheet1.createRow(0);
        int columnSum = 22;
        for (int c = 0; c < columnSum; c++) {
            // 列头
            // 创建一个单元格，列号为col+1
            row.createCell(c);
            sheet1.setColumnWidth(c, 4500);
            sheet1.getRow(0).getCell(c).setCellStyle(headcellstyle);
        }
        // 第一行为标题
        int columnX = 0;
        sheet1.getRow(0).getCell(columnX++).setCellValue("客户");
        sheet1.getRow(0).getCell(columnX++).setCellValue("国家");
        sheet1.getRow(0).getCell(columnX++).setCellValue("部门");
        sheet1.getRow(0).getCell(columnX++).setCellValue("合同号");
        sheet1.getRow(0).getCell(columnX++).setCellValue("订单号");
        // sheet1.getRow(0).getCell(3).setCellValue("铭牌号");
        sheet1.getRow(0).getCell(columnX++).setCellValue("机器信息");
        sheet1.getRow(0).getCell(columnX++).setCellValue("台数");
        sheet1.getRow(0).getCell(columnX++).setCellValue("单价");
        sheet1.getRow(0).getCell(columnX++).setCellValue("装置");
        sheet1.getRow(0).getCell(columnX++).setCellValue("装置数量");
        sheet1.getRow(0).getCell(columnX++).setCellValue("装置总价");
        sheet1.getRow(0).getCell(columnX++).setCellValue("优惠金额");
        sheet1.getRow(0).getCell(columnX++).setCellValue("订单总金额(美元)");
        sheet1.getRow(0).getCell(columnX++).setCellValue("订单总金额(人民币)");
        sheet1.getRow(0).getCell(columnX++).setCellValue("订单总金额(欧元)");
        // sheet1.getRow(0).getCell(columnX++).setCellValue("币种");
        sheet1.getRow(0).getCell(columnX++).setCellValue("销售员");
        // 销售费
        sheet1.getRow(0).getCell(columnX++).setCellValue("业务费");
        sheet1.getRow(0).getCell(columnX++).setCellValue("付款方式");
        sheet1.getRow(0).getCell(columnX++).setCellValue("毛利");
        sheet1.getRow(0).getCell(columnX++).setCellValue("订单类型");
        sheet1.getRow(0).getCell(columnX++).setCellValue("保修费");
        sheet1.getRow(0).getCell(columnX++).setCellValue("签核完成时间");
        DataFormat dataFormat = wb.createDataFormat();
        CellStyle cellStyle;
        HSSFCellStyle wrapStyle = wb.createCellStyle();
        wrapStyle.setWrapText(true);
        // 第二行开始，填入值
        // 各订单的装置数量-合计
        int allOrdersEquipmentCount = 0;
        // 各订单的装置总价-合计
        int allOrdersEquipemntAmountCount = 0;
        // 各订单的优惠金额-合计
        int allOrdersDiscountCount = 0;
        // 各订单订单总金额（美元）	合计
        int allOrdersTotalPriceCount_USD = 0;
        // 各订单订单总金额（人民币）合计
        int allOrdersTotalPriceCount_RMB = 0;
        // 各订单订单总金额（欧元）合计
        int allOrdersTotalPriceCount_EUR = 0;
        cellStyle = wb.createCellStyle();
        // 金额格式
        cellStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));
        for (int i = 0; i < list.size(); i++) {
            String dateStringSignFinish = "未完成签核";
            int r = i + 1;
            MachineOrderDetail mod = list.get(i);
            // 新创建一行
            row = sheet1.createRow(r);
            for (int c = 0; c < columnSum; c++) {
                // 创建列单元格
                row.createCell(c);
            }
            // 合同的内容也用到
            Contract contract = contractService.findById(list.get(i).getContractId());
            if (contract == null) {
                logger.error("异常，根据合同ID号，查找不到合同");
                return ResultGenerator.genFailResult("异常，根据合同ID号，查找不到合同");
            }
            // 签核也用到：签核完成时间
            List<OrderSign> orderSignList = orderSignService.getOrderSignListByOrderId(mod.getOrderSign().getOrderId());
            OrderSign orderSign = null;
            List<SignContentItem> signContentItemList = null;
            if (orderSignList.size() > 0) {
                // 订单的签核记录只有在新增合同-订单时，才创建订单的签核记录，所以这里 getOrderSignListByOrderId其实只返回一个签核记录
                orderSign = orderSignList.get(orderSignList.size() - 1);
                if (orderSign.getCurrentStep() == null) {
                    logger.warn("异常，orderSign.getCurrentStep()为null, 比如测试数据手动乱改动时可能出现");
                }
                if (orderSign.getCurrentStep() != null && orderSign.getCurrentStep().equals("签核完成")) {
                    // 如果签核完成，取最后一个角色的签核时间
                    signContentItemList = JSON.parseArray(orderSign.getSignContent(), SignContentItem.class);
                    // 注意订单签核没有enable开关
                    if (signContentItemList.get(signContentItemList.size() - 1).getDate() != null) {
                        dateStringSignFinish = formatter.format(signContentItemList.get(signContentItemList.size() - 1).getDate());
                    } else {
                        logger.warn("signContentItemList.get(signContentItemList.size()-1).getDate() 是 null，比如测试数据手动乱改动时可能出现");
                    }
                }
            }
            columnX = 0;
            // 客户
            sheet1.getRow(r).getCell(columnX).setCellValue(mod.getCustomer());
            sheet1.getRow(r).getCell(columnX++).setCellStyle(wrapStyle);
            // 国家
            sheet1.getRow(r).getCell(columnX++).setCellValue(mod.getCountry());
            // 部门
            sheet1.getRow(r).getCell(columnX++).setCellValue(contract.getMarketGroupName());
            // 合同号
            sheet1.getRow(r).getCell(columnX).setCellValue(mod.getContractNum());
            sheet1.getRow(r).getCell(columnX++).setCellStyle(wrapStyle);
            // 
            sheet1.getRow(r).getCell(columnX).setCellValue(mod.getOrderNum());
            sheet1.getRow(r).getCell(columnX++).setCellStyle(wrapStyle);
            // sheet1.getRow(r).getCell(3).setCellValue(mod.getNameplate());//
            MachineType mt = mod.getMachineType();
            if (mt != null) {
                // 机器信息
                String machineInfo = mt.getName() + "/" + mod.getNeedleNum() + "/" + mod.getHeadNum() + "/" + mod.getHeadDistance() + "/" + mod.getxDistance() + "/" + mod.getyDistance() + "/" + mod.getElectricTrim() + "/" + mod.getElectricPc();
                // 
                sheet1.getRow(r).getCell(columnX++).setCellValue(machineInfo);
            }
            // 
            sheet1.getRow(r).getCell(columnX++).setCellValue(mod.getMachineNum());
            // 
            sheet1.getRow(r).getCell(columnX).setCellValue(mod.getMachinePrice());
            sheet1.getRow(r).getCell(columnX++).setCellStyle(cellStyle);
            List<Equipment> epArray = JSON.parseArray(mod.getEquipment(), Equipment.class);
            String strEp = "";
            // 订单内  各种装置数量之和
            int equipmentCount = 0;
            int epPriceAmount = 0;
            for (Equipment itemEquipment : epArray) {
                strEp += itemEquipment.getName() + ":" + itemEquipment.getNumber() + "个" + "\r\n";
                epPriceAmount += itemEquipment.getPrice() * itemEquipment.getNumber();
                equipmentCount += itemEquipment.getNumber();
            }
            allOrdersEquipmentCount += equipmentCount;
            allOrdersEquipemntAmountCount += epPriceAmount;
            // sheet1.getRow(r).getCell(columnX).setCellStyle(wrapStyle);
            // 装置
            sheet1.getRow(r).getCell(columnX++).setCellValue(new HSSFRichTextString(strEp));
            // 装置数量
            sheet1.getRow(r).getCell(columnX++).setCellValue(equipmentCount);
            // 装置总价
            sheet1.getRow(r).getCell(columnX).setCellValue(epPriceAmount);
            sheet1.getRow(r).getCell(columnX++).setCellStyle(cellStyle);
            // 优惠金额
            sheet1.getRow(r).getCell(columnX).setCellValue(mod.getDiscounts());
            allOrdersDiscountCount += Integer.valueOf(mod.getDiscounts());
            Double totalAmount = Double.parseDouble(mod.getMachinePrice()) * mod.getMachineNum() + epPriceAmount * mod.getMachineNum() - // 优惠金额 （需求单总价格优惠金额）
            Double.parseDouble(mod.getOrderTotalDiscounts()) - // 每台的优惠金额
            Double.parseDouble(mod.getDiscounts()) * mod.getMachineNum();
            // - Double.parseDouble(mod.getIntermediaryPrice())*mod.getMachineNum();//每台的居间费用 和总价无关
            sheet1.getRow(r).getCell(columnX++).setCellStyle(cellStyle);
            if (mod.getCurrencyType().equals("美元")) {
                allOrdersTotalPriceCount_USD += totalAmount;
                // 总金额（美元）
                sheet1.getRow(r).getCell(columnX).setCellValue(totalAmount);
                sheet1.getRow(r).getCell(columnX++).setCellStyle(cellStyle);
                columnX++;
                // 
                columnX++;
            }
            if (mod.getCurrencyType().equals("人民币")) {
                allOrdersTotalPriceCount_RMB += totalAmount;
                columnX++;
                // 总金额（人民币）
                sheet1.getRow(r).getCell(columnX).setCellValue(totalAmount);
                sheet1.getRow(r).getCell(columnX++).setCellStyle(cellStyle);
                columnX++;
            }
            if (mod.getCurrencyType().equals("欧元")) {
                allOrdersTotalPriceCount_EUR += totalAmount;
                columnX++;
                columnX++;
                // 总金额（欧元）
                sheet1.getRow(r).getCell(columnX).setCellValue(totalAmount);
                sheet1.getRow(r).getCell(columnX++).setCellStyle(cellStyle);
            }
            // sheet1.getRow(r).getCell(columnX++).setCellValue(mod.getCurrencyType());//币种
            // 销售员
            sheet1.getRow(r).getCell(columnX++).setCellValue(mod.getSellman());
            // 销售费 业务费
            sheet1.getRow(r).getCell(columnX).setCellValue(mod.getBusinessExpense());
            sheet1.getRow(r).getCell(columnX++).setCellStyle(cellStyle);
            // 付款方式
            sheet1.getRow(r).getCell(columnX++).setCellValue(mod.getPayMethod());
            // 毛利率
            sheet1.getRow(r).getCell(columnX++).setCellValue(mod.getGrossProfit());
            // 订单类型
            sheet1.getRow(r).getCell(columnX++).setCellValue(mod.getOrderType());
            // 保修费
            sheet1.getRow(r).getCell(columnX++).setCellValue(mod.getWarrantyFee());
            // 签核完成时间
            sheet1.getRow(r).getCell(columnX++).setCellValue(dateStringSignFinish);
        }
        // 最后一行 汇总
        // 新创建一行
        Row rowSum = sheet1.createRow(list.size() + 1);
        for (int c = 0; c < columnSum; c++) {
            // 创建列单元格
            rowSum.createCell(c);
        }
        // 各订单的装置数量-合计
        rowSum.getCell(9).setCellValue(allOrdersEquipmentCount);
        // 各订单的装置总价 合计
        rowSum.getCell(10).setCellValue(allOrdersEquipemntAmountCount);
        rowSum.getCell(10).setCellStyle(cellStyle);
        // 优惠金额  合计
        rowSum.getCell(11).setCellValue(allOrdersDiscountCount);
        // 订单总金额（美元）	合计
        rowSum.getCell(12).setCellValue(allOrdersTotalPriceCount_USD);
        rowSum.getCell(12).setCellStyle(cellStyle);
        // 订单总金额（人民币）合计
        rowSum.getCell(13).setCellValue(allOrdersTotalPriceCount_RMB);
        rowSum.getCell(13).setCellStyle(cellStyle);
        // 订单总金额（欧元）合计
        rowSum.getCell(14).setCellValue(allOrdersTotalPriceCount_EUR);
        rowSum.getCell(14).setCellStyle(cellStyle);
        downloadPath = reportOutputPath + "销售报表" + ".xls";
        downloadPathForNginx = "/report/" + "销售报表" + ".xls";
        out = new FileOutputStream(downloadPath);
        wb.write(out);
        out.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    if ("".equals(downloadPath)) {
        return ResultGenerator.genFailResult("异常导出失败!");
    } else {
        return ResultGenerator.genSuccessResult(downloadPathForNginx);
    }
}

Example 2 with Equipment

use of com.eservice.api.model.contract.Equipment in project sinsim by WilsonHu.

the class MachineOrderController method exportToFinaceExcel.

@PostMapping("/exportToFinaceExcel")
public Result exportToFinaceExcel(Integer id, Integer contract_id, String order_num, String contract_num, String status, String sellman, String customer, String marketGroupName, // 这个是查询创建日期
String query_start_time, // 这个是查询创建日期
String query_finish_time, // 这个是查询审核日期
String queryStartTimeSign, // 这个是查询审核日期
String queryFinishTimeSign, String machine_name, // 订单签核的当前步骤
String oderSignCurrentStep, String searchDepartment, @RequestParam(defaultValue = "true") Boolean is_fuzzy) {
    List<MachineOrderDetail> list = machineOrderService.selectOrder(id, contract_id, order_num, contract_num, status, sellman, customer, marketGroupName, query_start_time, query_finish_time, queryStartTimeSign, queryFinishTimeSign, machine_name, oderSignCurrentStep, searchDepartment, is_fuzzy);
    HSSFWorkbook wb = null;
    FileOutputStream out = null;
    String downloadPath = "";
    /*
            返回给docker外部下载
                */
    String downloadPathForNginx = "";
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
    String dateString;
    try {
        // 生成一个空的Excel文件
        wb = new HSSFWorkbook();
        Sheet sheet1 = wb.createSheet("账务报表");
        // 设置标题行格式
        HSSFCellStyle headcellstyle = wb.createCellStyle();
        HSSFFont headfont = wb.createFont();
        headfont.setFontHeightInPoints((short) 10);
        // 粗体显示
        headfont.setBold(true);
        headcellstyle.setFont(headfont);
        Row row;
        // 新创建一行，行号为row+1
        row = sheet1.createRow(0);
        int columnSum = 35;
        for (int c = 0; c < columnSum; c++) {
            // 列头
            // 创建一个单元格，列号为col+1
            row.createCell(c);
            sheet1.setColumnWidth(c, 4500);
            sheet1.getRow(0).getCell(c).setCellStyle(headcellstyle);
        }
        // 第一行为标题
        sheet1.getRow(0).getCell(0).setCellValue("客户");
        sheet1.getRow(0).getCell(1).setCellValue("合同号");
        sheet1.getRow(0).getCell(2).setCellValue("订单号");
        // sheet1.getRow(0).getCell(3).setCellValue("铭牌号");
        sheet1.getRow(0).getCell(3).setCellValue("机器信息");
        sheet1.getRow(0).getCell(4).setCellValue("台数");
        sheet1.getRow(0).getCell(5).setCellValue("单价");
        sheet1.getRow(0).getCell(6).setCellValue("装置");
        sheet1.getRow(0).getCell(7).setCellValue("装置总价");
        sheet1.getRow(0).getCell(8).setCellValue("优惠金额");
        sheet1.getRow(0).getCell(9).setCellValue("订单总价");
        sheet1.getRow(0).getCell(10).setCellValue("币种");
        sheet1.getRow(0).getCell(11).setCellValue("销售员");
        // 销售费
        sheet1.getRow(0).getCell(12).setCellValue("业务费");
        sheet1.getRow(0).getCell(13).setCellValue("保修费");
        sheet1.getRow(0).getCell(14).setCellValue("保修人员");
        sheet1.getRow(0).getCell(15).setCellValue("付款方式");
        sheet1.getRow(0).getCell(16).setCellValue("定金率");
        sheet1.getRow(0).getCell(17).setCellValue("毛利");
        sheet1.getRow(0).getCell(18).setCellValue("包装方式");
        sheet1.getRow(0).getCell(19).setCellValue("机架长度");
        sheet1.getRow(0).getCell(20).setCellValue("针数");
        sheet1.getRow(0).getCell(21).setCellValue("头数");
        sheet1.getRow(0).getCell(22).setCellValue("头距");
        sheet1.getRow(0).getCell(23).setCellValue("X行程");
        sheet1.getRow(0).getCell(24).setCellValue("Y行程");
        sheet1.getRow(0).getCell(25).setCellValue("电脑");
        sheet1.getRow(0).getCell(26).setCellValue("剪线方式");
        sheet1.getRow(0).getCell(27).setCellValue("换色方式");
        sheet1.getRow(0).getCell(28).setCellValue("加油系统");
        sheet1.getRow(0).getCell(29).setCellValue("夹线器");
        sheet1.getRow(0).getCell(30).setCellValue("跳跃方式");
        sheet1.getRow(0).getCell(31).setCellValue("旋梭");
        sheet1.getRow(0).getCell(32).setCellValue("面线夹持");
        sheet1.getRow(0).getCell(33).setCellValue("订单类型");
        sheet1.getRow(0).getCell(34).setCellValue("填表日期");
        DataFormat dataFormat = wb.createDataFormat();
        CellStyle cellStyle;
        HSSFCellStyle wrapStyle = wb.createCellStyle();
        wrapStyle.setWrapText(true);
        // 第二行开始，填入值
        for (int i = 0; i < list.size(); i++) {
            int r = i + 1;
            MachineOrderDetail mod = list.get(i);
            OrderDetail od = mod.getOrderDetail();
            // 新创建一行
            row = sheet1.createRow(r);
            for (int c = 0; c < columnSum; c++) {
                // 创建列单元格
                row.createCell(c);
            }
            // 客户
            sheet1.getRow(r).getCell(0).setCellValue(mod.getCustomer());
            // 合同号
            sheet1.getRow(r).getCell(1).setCellValue(mod.getContractNum());
            // 
            sheet1.getRow(r).getCell(2).setCellValue(mod.getOrderNum());
            sheet1.getRow(r).getCell(0).setCellStyle(wrapStyle);
            sheet1.getRow(r).getCell(1).setCellStyle(wrapStyle);
            sheet1.getRow(r).getCell(2).setCellStyle(wrapStyle);
            // sheet1.getRow(r).getCell(3).setCellValue(mod.getNameplate());//
            MachineType mt = mod.getMachineType();
            if (mt != null) {
                // 机器信息
                String machineInfo = mt.getName() + "/" + mod.getNeedleNum() + "/" + mod.getHeadNum() + "/" + mod.getHeadDistance() + "/" + mod.getxDistance() + "/" + mod.getyDistance() + "/" + mod.getElectricTrim() + "/" + mod.getElectricPc();
                // 
                sheet1.getRow(r).getCell(3).setCellValue(machineInfo);
            }
            // 
            sheet1.getRow(r).getCell(4).setCellValue(mod.getMachineNum());
            // 
            sheet1.getRow(r).getCell(5).setCellValue(mod.getMachinePrice());
            cellStyle = wb.createCellStyle();
            // 金额格式
            cellStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));
            sheet1.getRow(r).getCell(5).setCellStyle(cellStyle);
            List<Equipment> epArray = JSON.parseArray(mod.getEquipment(), Equipment.class);
            String strEp = "";
            int epAmount = 0;
            for (Equipment itemEquipment : epArray) {
                strEp += itemEquipment.getName() + ":" + itemEquipment.getNumber() + "个" + "\r\n";
                epAmount += itemEquipment.getPrice() * itemEquipment.getNumber();
            }
            sheet1.getRow(r).getCell(6).setCellStyle(wrapStyle);
            // 装置
            sheet1.getRow(r).getCell(6).setCellValue(new HSSFRichTextString(strEp));
            // 装置金额
            sheet1.getRow(r).getCell(7).setCellValue(epAmount);
            sheet1.getRow(r).getCell(7).setCellStyle(cellStyle);
            // 优惠金额
            sheet1.getRow(r).getCell(8).setCellValue(mod.getDiscounts());
            Double totalAmount = Double.parseDouble(mod.getMachinePrice()) * mod.getMachineNum() + epAmount - Double.parseDouble(mod.getDiscounts());
            sheet1.getRow(r).getCell(8).setCellStyle(cellStyle);
            // 总金额
            sheet1.getRow(r).getCell(9).setCellValue(totalAmount);
            sheet1.getRow(r).getCell(9).setCellStyle(cellStyle);
            // 币种
            sheet1.getRow(r).getCell(10).setCellValue(mod.getCurrencyType());
            // 销售员
            sheet1.getRow(r).getCell(11).setCellValue(mod.getSellman());
            // 销售费
            sheet1.getRow(r).getCell(12).setCellValue(mod.getBusinessExpense());
            // 保修费
            sheet1.getRow(r).getCell(13).setCellValue(mod.getWarrantyFee());
            // 保修人员
            sheet1.getRow(r).getCell(14).setCellValue(mod.getMaintainPerson());
            // 付款方式
            sheet1.getRow(r).getCell(15).setCellValue(mod.getPayMethod());
            // sheet1.getRow(r).getCell(16).setCellValue(0);//定金率 先空着
            // 改为要从成本核算员的意见中抽取 毛利率
            // 毛利率
            sheet1.getRow(r).getCell(17).setCellValue(mod.getGrossProfit());
            // 包装方式
            sheet1.getRow(r).getCell(18).setCellValue(mod.getPackageMethod());
            // 订单中关于技术部的意见里长度：***，显示在财务报表中，显示方式为【机架长度】一栏
            // 机架长度
            sheet1.getRow(r).getCell(19).setCellValue(mod.getMachineFrameLength());
            // 针数
            sheet1.getRow(r).getCell(20).setCellValue(mod.getNeedleNum());
            // 头数
            sheet1.getRow(r).getCell(21).setCellValue(mod.getHeadNum());
            // 头距
            sheet1.getRow(r).getCell(22).setCellValue(mod.getHeadDistance());
            // X行程
            sheet1.getRow(r).getCell(23).setCellValue(mod.getxDistance());
            // Y行程
            sheet1.getRow(r).getCell(24).setCellValue(mod.getyDistance());
            // 电脑
            sheet1.getRow(r).getCell(25).setCellValue(od.getElectricPc());
            // 剪线方式
            sheet1.getRow(r).getCell(26).setCellValue(od.getElectricTrim());
            // 换色方式
            sheet1.getRow(r).getCell(27).setCellValue(od.getColorChangeMode());
            // 加油系统
            sheet1.getRow(r).getCell(28).setCellValue(od.getElectricOil());
            // 夹线器
            sheet1.getRow(r).getCell(29).setCellValue(od.getAxleSplit());
            // 跳跃方式
            sheet1.getRow(r).getCell(30).setCellValue(od.getAxleJump());
            // 旋梭
            sheet1.getRow(r).getCell(31).setCellValue(od.getAxleHook());
            // 面线夹持
            sheet1.getRow(r).getCell(32).setCellValue(od.getAxleUpperThread());
            // 订单类型
            sheet1.getRow(r).getCell(33).setCellValue(mod.getOrderType());
            dateString = formatter.format(mod.getCreateTime());
            // 填表日期
            sheet1.getRow(r).getCell(34).setCellValue(dateString);
        }
        downloadPath = reportOutputPath + "账务报表" + ".xls";
        downloadPathForNginx = "/report/" + "账务报表" + ".xls";
        out = new FileOutputStream(downloadPath);
        wb.write(out);
        out.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    if ("".equals(downloadPath)) {
        return ResultGenerator.genFailResult("异常导出失败!");
    } else {
        return ResultGenerator.genSuccessResult(downloadPathForNginx);
    }
}

Example 3 with Equipment

use of com.eservice.api.model.contract.Equipment in project sinsim by WilsonHu.

the class ContractController method buildContractExcel.

/**
 * 根据 contract_id，创建EXCEL表格，“合同评审单”+“客户需求单” 等sheet。
 * 具体内容来自 contract, contract_sign,machine_order,order_detail
 * Update: 总经理，销售，财务之外的用户，生成的excel里不显示金额信息.
 *
 * @param contractId
 * @return
 */
@PostMapping("/buildContractExcel")
public Result buildContractExcel(@RequestParam Integer contractId, @RequestParam String account) {
    InputStream fs = null;
    POIFSFileSystem pfs = null;
    HSSFWorkbook wb = null;
    FileOutputStream out = null;
    String downloadPath = "";
    /*
        返回给docker外部下载
         */
    String downloadPathForNginx = "";
    int focusLine = 0;
    /**
     * 某订单总价 = 机器价格*台数 + 装置价格*台数 - 优惠总价
     */
    // Integer totalPriceOfOrder = 0;
    Double totalPriceOfOrder = 0.0;
    /**
     * 某订单的机器总价
     */
    // Integer machineOrderSum = 0;
    Double machineOrderSum = 0.0;
    /**
     * 合同总价 = 各订单总价之和
     */
    // Integer totalPriceOfContract = 0;
    Double totalPriceOfContract = 0.0;
    // 只有总经理，销售，财务等用户，生成的excel里才显示金额信息. '6','7','9','14','15'
    Boolean displayPrice = commonService.isDisplayPrice(account);
    try {
        ClassPathResource resource = new ClassPathResource("empty_contract.xls");
        fs = resource.getInputStream();
        pfs = new POIFSFileSystem(fs);
        wb = new HSSFWorkbook(pfs);
        HSSFFont fontSlim = wb.createFont();
        fontSlim.setBold(false);
        HSSFCellStyle cellStyleSlim = wb.createCellStyle();
        cellStyleSlim.setFont(fontSlim);
        cellStyleSlim.setBorderBottom(BorderStyle.THIN);
        cellStyleSlim.setBorderLeft(BorderStyle.THIN);
        cellStyleSlim.setBorderTop(BorderStyle.THIN);
        cellStyleSlim.setBorderRight(BorderStyle.THIN);
        cellStyleSlim.setAlignment(HorizontalAlignment.CENTER);
        cellStyleSlim.setVerticalAlignment(VerticalAlignment.CENTER);
        Contract contract = contractService.findById(contractId);
        if (contract == null) {
            return ResultGenerator.genFailResult("contractID not exist!");
        }
        // 一个合同可能对应多个需求单
        List<Integer> machineOrderIdList = new ArrayList<Integer>();
        List<Integer> validMachineOrderIdList = new ArrayList<Integer>();
        MachineOrder mo;
        Condition tempCondition = new Condition(MachineOrder.class);
        tempCondition.createCriteria().andCondition("contract_id = ", contractId).andCondition("valid = ", 1);
        List<MachineOrder> validOrderList = machineOrderService.findByCondition(tempCondition);
        for (int i = 0; i < validOrderList.size(); i++) {
            mo = validOrderList.get(i);
            // (已改的单，是废弃的单，不用再显示在excel里,已拆的单,因为是有效的，所以保留着)
            if (mo.getValid().intValue() == 1) {
                machineOrderIdList.add(mo.getId());
                if (mo.getStatus().intValue() != Constant.ORDER_CHANGED.intValue()) {
                    validMachineOrderIdList.add(mo.getId());
                }
            }
        }
        MachineOrderDetail machineOrderDetail;
        // 需求单签核,一个需求单对应0个或多个签核
        List<OrderSign> orderSignList;
        // 读取了模板内所有sheet1内容
        HSSFSheet sheet1 = wb.getSheetAt(0);
        // 在相应的单元格进行赋值(A2)
        HSSFCell cell = sheet1.getRow(1).getCell((short) 0);
        cell.setCellValue(new HSSFRichTextString("合 同 号：" + contract.getContractNum()));
        // D2
        cell = sheet1.getRow(1).getCell((short) 3);
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat formatter2 = new SimpleDateFormat("yyyy/MM/dd");
        String dateString = formatter.format(contract.getCreateTime());
        HSSFCellStyle style = cell.getCellStyle();
        style.setWrapText(true);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString(dateString));
        // B3
        cell = sheet1.getRow(2).getCell((short) 1);
        cell.setCellValue(new HSSFRichTextString(contract.getCustomerName()));
        // N个需求单，插入N行（机器）,不包括已改单的需求单
        Integer validMachineOrderCount = validMachineOrderIdList.size();
        insertRow(wb, sheet1, 5, validMachineOrderCount);
        if (isDebug) {
            System.out.println("======== insert Rows validMachineOrderCount: " + validMachineOrderCount);
        }
        /**
         * 记录各个订单的装置数量
         */
        int[] equipmentNumArr = new int[validMachineOrderCount];
        for (int i = 0; i < validMachineOrderCount; i++) {
            machineOrderDetail = machineOrderService.getOrderAllDetail(validMachineOrderIdList.get(i));
            // 计算在合同sheet用到装置价格信息，
            JSONArray jsonArray = JSON.parseArray(machineOrderDetail.getEquipment());
            Integer equipmentCount = 0;
            if (null != jsonArray) {
                // 该需求单的X个装置，插入X + 3行 (3是优惠/居间/需求单小计)
                equipmentCount = jsonArray.size();
                insertRow(wb, sheet1, 5, equipmentCount + 3);
                equipmentNumArr[i] = equipmentCount;
                if (isDebug) {
                    System.out.println("======== insert Rows: " + (equipmentCount + 3) + "for equipments");
                }
            }
        }
        String machineInfo = "";
        for (int i = 0; i < validMachineOrderCount; i++) {
            totalPriceOfOrder = 0.0;
            machineOrderSum = 0.0;
            machineOrderDetail = machineOrderService.getOrderAllDetail(validMachineOrderIdList.get(i));
            focusLine = 5 + i + getLinesSum(equipmentNumArr, i);
            // A5,,...订单号
            cell = sheet1.getRow(focusLine).getCell((short) 0);
            cell.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderNum()));
            // B5,...机型详细信息：机型/针数/头数/头距/x行程/y行程/剪线方式/电脑
            cell = sheet1.getRow(focusLine).getCell((short) 1);
            machineInfo = machineOrderDetail.getMachineType().getName() + "/" + machineOrderDetail.getNeedleNum() + "/" + machineOrderDetail.getHeadNum() + "/" + machineOrderDetail.getHeadDistance() + "/" + machineOrderDetail.getxDistance() + "/" + machineOrderDetail.getyDistance() + "/" + machineOrderDetail.getOrderDetail().getElectricTrim() + "/" + machineOrderDetail.getOrderDetail().getElectricPc();
            cell.setCellValue(new HSSFRichTextString(machineInfo));
            // C5,,...数量
            cell = sheet1.getRow(focusLine).getCell((short) 2);
            cell.setCellValue(new HSSFRichTextString(machineOrderDetail.getMachineNum().toString()));
            // D5,,...单价
            cell = sheet1.getRow(focusLine).getCell((short) 3);
            if (displayPrice) {
                cell.setCellValue(new HSSFRichTextString(machineOrderDetail.getMachinePrice()));
            } else {
                cell.setCellValue(new HSSFRichTextString("/"));
            }
            // E5,,..机器总价
            cell = sheet1.getRow(focusLine).getCell((short) 4);
            machineOrderSum = Double.parseDouble(machineOrderDetail.getMachinePrice()) * machineOrderDetail.getMachineNum();
            totalPriceOfOrder += machineOrderSum;
            if (displayPrice) {
                cell.setCellValue(machineOrderSum);
            } else {
                cell.setCellValue(new HSSFRichTextString("/"));
            }
            if (isDebug) {
                System.out.println("===order: " + machineOrderDetail.getOrderNum() + " inserted machine @" + focusLine);
            }
            /**
             * 该订单的各个装置
             */
            JSONArray jsonArray = JSON.parseArray(machineOrderDetail.getEquipment());
            Integer equipmentCount = 0;
            // int orderEquipmentTotal = 0;
            double orderEquipmentTotal = 0;
            if (null != jsonArray) {
                focusLine = 6 + i + getLinesSum(equipmentNumArr, i);
                equipmentCount = jsonArray.size();
                for (int j = 0; j < equipmentCount; j++) {
                    Equipment eq = JSON.parseObject((String) jsonArray.get(j).toString(), Equipment.class);
                    /**
                     * 前端传进来异常数据时的处理
                     * 如果某数据为空 就都不写了。
                     */
                    if (eq.getName() == null) {
                        logger.error("异常数据，装置名称为空");
                        break;
                    }
                    if (eq.getNumber() == null) {
                        logger.error("异常数据，装置数量为空");
                        break;
                    }
                    if (eq.getPrice() == null) {
                        logger.error("异常数据，装置价格为空");
                        break;
                    }
                    if (eq.getType() == null) {
                        logger.error("异常数据，装置类型为空");
                        break;
                    }
                    cell = sheet1.getRow(focusLine + j).getCell((short) 1);
                    cell.setCellValue(new HSSFRichTextString(eq.getName() + (eq.getType() != null && !"".equals(eq.getType()) ? "(" + eq.getType() + ")" : "")));
                    cell.setCellStyle(cellStyleSlim);
                    /**
                     * 这里的数量是：每台机器的装置数*机器台数
                     */
                    cell = sheet1.getRow(focusLine + j).getCell((short) 2);
                    cell.setCellValue(eq.getNumber() * machineOrderDetail.getMachineNum());
                    cell.setCellStyle(cellStyleSlim);
                    cell = sheet1.getRow(focusLine + j).getCell((short) 3);
                    if (displayPrice) {
                        cell.setCellValue(new HSSFRichTextString(eq.getPrice().toString()));
                    } else {
                        cell.setCellValue(new HSSFRichTextString("/"));
                    }
                    cell.setCellStyle(cellStyleSlim);
                    /**
                     * 订单内该种装置的总价
                     */
                    cell = sheet1.getRow(focusLine + j).getCell((short) 4);
                    // int eqSum = eq.getNumber() * eq.getPrice() * machineOrderDetail.getMachineNum();
                    Double eqSum = eq.getNumber() * eq.getPrice() * machineOrderDetail.getMachineNum();
                    orderEquipmentTotal = orderEquipmentTotal + eqSum;
                    totalPriceOfOrder += eqSum;
                    if (displayPrice) {
                        // cell.setCellValue(new HSSFRichTextString((Integer.toString(eqSum))));
                        cell.setCellValue(new HSSFRichTextString((eqSum.toString())));
                    } else {
                        cell.setCellValue(new HSSFRichTextString("/"));
                    }
                }
                focusLine += equipmentCount;
            }
            /**
             * 该订单的优惠
             */
            if (isDebug) {
                System.out.println("========order: " + machineOrderDetail.getOrderNum() + " inserted 优惠 @" + focusLine);
            }
            cell = sheet1.getRow(focusLine).getCell((short) 0);
            cell.setCellValue(new HSSFRichTextString("优惠金额:"));
            cell = sheet1.getRow(focusLine).getCell((short) 4);
            /**
             * 该订单的 总优惠 = 优惠金额/台 * 台数 +  优惠金额 (用于抹零等)
             * 既有 优惠金额/台，又有优惠金额 。类似折上折的意思
             */
            // Integer sumOfDiscounts =Integer.parseInt(machineOrderDetail.getDiscounts()) * machineOrderDetail.getMachineNum() + Integer.parseInt(machineOrderDetail.getOrderTotalDiscounts());
            Double sumOfDiscounts = Double.parseDouble(machineOrderDetail.getDiscounts()) * machineOrderDetail.getMachineNum() + Double.parseDouble(machineOrderDetail.getOrderTotalDiscounts());
            if (displayPrice) {
                cell.setCellValue(new HSSFRichTextString(sumOfDiscounts.toString()));
            } else {
                cell.setCellValue(new HSSFRichTextString("/"));
            }
            totalPriceOfOrder -= sumOfDiscounts;
            if (isDebug) {
                System.out.println("========order: " + machineOrderDetail.getOrderNum() + "  old totalPriceOfContract:" + totalPriceOfContract);
            }
            totalPriceOfContract += totalPriceOfOrder;
            if (isDebug) {
                System.out.println("========order: " + machineOrderDetail.getOrderNum() + " now totalPriceOfContract:" + totalPriceOfContract + "by added:  " + totalPriceOfOrder);
            }
            focusLine++;
            cell = sheet1.getRow(focusLine).getCell((short) 0);
            cell.setCellValue(new HSSFRichTextString("居间费用"));
            // 居间费用的台数
            cell = sheet1.getRow(focusLine).getCell((short) 2);
            if (displayPrice) {
                cell.setCellValue(machineOrderDetail.getMachineNum());
            } else {
                cell.setCellValue(new HSSFRichTextString("/"));
            }
            // 居间费用/台 (单价)
            cell = sheet1.getRow(focusLine).getCell((short) 3);
            if (displayPrice) {
                cell.setCellValue(new HSSFRichTextString(machineOrderDetail.getIntermediaryPrice()));
            } else {
                cell.setCellValue(new HSSFRichTextString("/"));
            }
            // 居间费用总计,不计入订单总价
            cell = sheet1.getRow(focusLine).getCell((short) 4);
            if (displayPrice) {
                // Integer sumOfIntermediary =Integer.parseInt(machineOrderDetail.getIntermediaryPrice()) * machineOrderDetail.getMachineNum();
                Double sumOfIntermediary = Double.parseDouble(machineOrderDetail.getIntermediaryPrice()) * machineOrderDetail.getMachineNum();
                cell.setCellValue(new HSSFRichTextString(sumOfIntermediary.toString()));
            } else {
                cell.setCellValue(new HSSFRichTextString("/"));
            }
            if (isDebug) {
                System.out.println("========order: " + machineOrderDetail.getOrderNum() + " inserted 居间 @" + focusLine);
            }
            /**
             * 需求单小计
             */
            focusLine++;
            cell = sheet1.getRow(focusLine).getCell((short) 0);
            cell.setCellValue(new HSSFRichTextString("小 计"));
            cell = sheet1.getRow(focusLine).getCell((short) 2);
            cell.setCellValue(new HSSFRichTextString("/"));
            cell = sheet1.getRow(focusLine).getCell((short) 3);
            cell.setCellValue(new HSSFRichTextString("/"));
            // 需求单小计
            cell = sheet1.getRow(focusLine).getCell((short) 4);
            if (displayPrice) {
                // Integer orderTotalPrice = machineOrderSum + orderEquipmentTotal - sumOfDiscounts;
                Double orderTotalPrice = machineOrderSum + orderEquipmentTotal - sumOfDiscounts;
                cell.setCellValue(new HSSFRichTextString(orderTotalPrice.toString()));
            } else {
                cell.setCellValue(new HSSFRichTextString("/"));
            }
            if (isDebug) {
                System.out.println("========order: " + machineOrderDetail.getOrderNum() + " inserted 小计 @" + focusLine);
            }
        }
        // end of order
        // 删除需求单行的空白的多余一行
        sheet1.shiftRows(focusLine, sheet1.getLastRowNum(), -1);
        // 合同总计
        focusLine++;
        if (isDebug) {
            System.out.println("===============合同总价 @" + focusLine + " line, focusLine is: " + focusLine);
        }
        cell = sheet1.getRow(focusLine++).getCell((short) 4);
        if (displayPrice) {
            cell.setCellValue(new HSSFRichTextString(totalPriceOfContract.toString()));
        } else {
            cell.setCellValue(new HSSFRichTextString("/"));
        }
        // 付款方式
        cell = sheet1.getRow(focusLine++).getCell((short) 1);
        if (displayPrice) {
            cell.setCellValue(new HSSFRichTextString(contract.getPayMethod()));
        } else {
            cell.setCellValue(new HSSFRichTextString("/"));
        }
        // 币种
        cell = sheet1.getRow(focusLine++).getCell((short) 1);
        cell.setCellValue(new HSSFRichTextString(contract.getCurrencyType()));
        // 合同交货日期
        String dateTimeString = formatter.format(contract.getContractShipDate());
        cell = sheet1.getRow(focusLine++).getCell((short) 1);
        cell.setCellValue(new HSSFRichTextString(dateTimeString));
        // 备注
        cell = sheet1.getRow(focusLine++).getCell((short) 0);
        if (displayPrice) {
            cell.setCellValue(new HSSFRichTextString(contract.getMark()));
        } else {
            cell.setCellValue(new HSSFRichTextString("/"));
        }
        // 销售员
        focusLine = focusLine + 6;
        cell = sheet1.getRow(focusLine++).getCell((short) 1);
        cell.setCellValue(new HSSFRichTextString(contract.getSellman()));
        // 一个合同对应多个签核 TODO:多个签核时如何选择，contractSignService.detailByContractId 可能要改。
        ContractSign contractSign;
        // 合同审核信息，来自 contract_sign
        contractSign = contractSignService.detailByContractId(contractId.toString());
        SignContentItem signContentItem;
        List<SignContentItem> signContentItemList = JSON.parseArray(contractSign.getSignContent(), SignContentItem.class);
        OrderSign orderSign = null;
        /**
         *  根据签核内容 signContentItemList，动态填入表格。
         *  signType为 “合同签核”的，都按顺序填入表格
         */
        // 合同的N个签核，插入N行
        Integer contractSignCount = signContentItemList.size();
        insertRow(wb, sheet1, focusLine, contractSignCount);
        for (int k = 0; k < contractSignCount; k++) {
            /**
             * 合同签核的： 角色（部门）/人/时间/意见
             */
            // 1.签核角色（部门）
            int roleId = signContentItemList.get(k).getRoleId();
            // 根据roleId返回角色（部门）
            String roleName = roleService.findById(roleId).getRoleName();
            cell = sheet1.getRow(focusLine).getCell((short) 0);
            cell.setCellValue(new HSSFRichTextString(roleName));
            // 2.签核人
            cell = sheet1.getRow(focusLine).getCell((short) 1);
            cell.setCellValue(new HSSFRichTextString(signContentItemList.get(k).getUser()));
            // 3.签核时间
            cell = sheet1.getRow(focusLine).getCell((short) 2);
            if (null != signContentItemList.get(k).getDate()) {
                cell.setCellValue(new HSSFRichTextString(formatter2.format(signContentItemList.get(k).getDate())));
            }
            cell = sheet1.getRow(focusLine).getCell((short) 3);
            cell.setCellValue(new HSSFRichTextString("意见"));
            // 4.签核意见
            cell = sheet1.getRow(focusLine++).getCell((short) 4);
            if (displayPrice) {
                cell.setCellValue(new HSSFRichTextString(signContentItemList.get(k).getComment()));
            } else {
                /**
                 * 财务(财务会计，财务经理，成本核算员)的意见，仅特定人员可见
                 */
                if (// || signContentItemList.get(k).getRoleId().equals(9)     //销售员
                // 成本核算员
                signContentItemList.get(k).getRoleId().equals(13) || // 财务经理
                signContentItemList.get(k).getRoleId().equals(14) || // 财务会计
                signContentItemList.get(k).getRoleId().equals(15))
                    cell.setCellValue(new HSSFRichTextString("/"));
            }
        }
        // 删除最后多余一行
        sheet1.shiftRows(focusLine + 1, sheet1.getLastRowNum(), -1);
        Integer machineOrderCount = machineOrderIdList.size();
        // 根据实际需求单数量，动态复制生成新的sheet;
        for (int i = 0; i < machineOrderCount - 1; i++) {
            // clone已经包含copy+paste
            wb.cloneSheet(1);
        }
        // 调整sheet位置
        Integer sheetCount = wb.getNumberOfSheets();
        wb.setSheetOrder("Sheet3", sheetCount - 1);
        // sheet2，sheet3...,第1,2,...个需求单
        for (int i = 0; i < machineOrderCount; i++) {
            totalPriceOfOrder = 0.0;
            machineOrderSum = 0.0;
            machineOrderDetail = machineOrderService.getOrderAllDetail(machineOrderIdList.get(i));
            // 把sheet名称改为订单的编号
            wb.setSheetName(i + 1, machineOrderDetail.getOrderNum().replaceAll("/", "-"));
            HSSFSheet sheetX = wb.getSheetAt(1 + i);
            // 在相应的单元格进行赋值
            // B2
            HSSFCell cell2 = sheetX.getRow(1).getCell((short) 1);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getSellman()));
            // D2
            cell2 = sheetX.getRow(1).getCell((short) 3);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getMaintainPerson()));
            // F2
            cell2 = sheetX.getRow(1).getCell((short) 5);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderNum()));
            // I2
            cell2 = sheetX.getRow(1).getCell((short) 8);
            cell2.setCellValue(new HSSFRichTextString(contract.getContractNum()));
            // C3
            cell2 = sheetX.getRow(2).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getCustomer()));
            // E3
            cell2 = sheetX.getRow(2).getCell((short) 4);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getBrand()));
            // g3
            cell2 = sheetX.getRow(2).getCell((short) 6);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getMachineType().getName()));
            // I3 机头款式
            cell2 = sheetX.getRow(2).getCell((short) 8);
            if (machineOrderDetail.getMachineHeadStyle() != null) {
                // /新旧订单兼容。旧订单没有这项
                cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getMachineHeadStyle()));
            }
            // C4
            cell2 = sheetX.getRow(3).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getNeedleNum() + "/" + machineOrderDetail.getHeadNum().toString()));
            // E4
            cell2 = sheetX.getRow(3).getCell((short) 4);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getHeadDistance().toString()));
            // H4
            cell2 = sheetX.getRow(3).getCell((short) 7);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getxDistance()));
            // H5
            cell2 = sheetX.getRow(4).getCell((short) 7);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getyDistance()));
            // D6 毛巾机头
            cell2 = sheetX.getRow(5).getCell((short) 3);
            if (machineOrderDetail.getOrderDetail().getSpecialTowelHead() != null) {
                cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getSpecialTowelHead()));
            }
            // F6 毛巾色数
            cell2 = sheetX.getRow(5).getCell((short) 5);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getSpecialTowelColor()));
            // H6 毛巾机针
            cell2 = sheetX.getRow(5).getCell((short) 7);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getSpecialTowelNeedle()));
            // K6 主电机
            cell2 = sheetX.getRow(5).getCell((short) 10);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getSpecialTowelMotor()));
            // D7
            cell2 = sheetX.getRow(6).getCell((short) 3);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getSpecialTapingHead()));
            // H7
            // C8 电控型号（电脑）
            cell2 = sheetX.getRow(7).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getElectricPc()));
            // D8
            cell2 = sheetX.getRow(7).getCell((short) 3);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getElectricLanguage()));
            // F8
            cell2 = sheetX.getRow(7).getCell((short) 5);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getElectricMotor()));
            // I8
            cell2 = sheetX.getRow(7).getCell((short) 8);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getElectricMotorXy()));
            // C9
            cell2 = sheetX.getRow(8).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getElectricTrim()));
            // F9
            cell2 = sheetX.getRow(8).getCell((short) 5);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getElectricPower()));
            // I9 改为了 换色方式
            cell2 = sheetX.getRow(8).getCell((short) 8);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getColorChangeMode()));
            // C10
            cell2 = sheetX.getRow(9).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getElectricOil()));
            // C11
            cell2 = sheetX.getRow(10).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getAxleSplit()));
            // F11
            cell2 = sheetX.getRow(10).getCell((short) 5);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getAxlePanel()));
            // i11
            cell2 = sheetX.getRow(10).getCell((short) 8);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getAxleNeedle()));
            cell2 = sheetX.getRow(10).getCell((short) 10);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getAxleNeedleType()));
            // C12
            cell2 = sheetX.getRow(11).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getAxleHook()));
            // f12 取消
            // cell2 = sheetX.getRow(11).getCell((short) 5);
            // cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getAxleDownCheck()));
            // i12 取消
            // cell2 = sheetX.getRow(11).getCell((short) 8);
            // cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getAxleHook()));
            // C13
            cell2 = sheetX.getRow(12).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getAxleJump()));
            // F13
            cell2 = sheetX.getRow(12).getCell((short) 5);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getAxleUpperThread()));
            // C14
            cell2 = sheetX.getRow(13).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getAxleAddition()));
            // C15
            cell2 = sheetX.getRow(14).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getFrameworkColor()));
            // f15 机脚类型
            cell2 = sheetX.getRow(14).getCell((short) 5);
            if (machineOrderDetail.getOrderDetail().getFrameworkMachineFootType() != null) {
                cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getFrameworkMachineFootType()));
            }
            // G15
            // h15 台板支撑
            cell2 = sheetX.getRow(14).getCell((short) 7);
            if (machineOrderDetail.getOrderDetail().getFrameworkPlatenSupport() != null) {
                cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getFrameworkPlatenSupport()));
            }
            // k15
            cell2 = sheetX.getRow(14).getCell((short) 10);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getFrameworkRing()));
            // C16 电脑托架
            cell2 = sheetX.getRow(15).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getFrameworkBracket()));
            // f16
            cell2 = sheetX.getRow(15).getCell((short) 5);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getFrameworkStop()));
            // H16 台板颜色
            cell2 = sheetX.getRow(15).getCell((short) 7);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getFrameworkPlatenColor()));
            // K16
            cell2 = sheetX.getRow(15).getCell((short) 10);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getFrameworkPoleHeight()));
            // i16 取消
            // cell2 = sheetX.getRow(15).getCell((short) 8);
            // cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getFrameworkLight()));
            // C17 X驱动类型
            cell2 = sheetX.getRow(16).getCell((short) 2);
            if (machineOrderDetail.getOrderDetail().getDriverXType() != null) {
                cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getDriverXType()));
            }
            // f17 y驱动类型
            cell2 = sheetX.getRow(16).getCell((short) 5);
            if (machineOrderDetail.getOrderDetail().getDriverYType() != null) {
                cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getDriverYType()));
            }
            // H17 框架类型 (原先的驱动方式)
            cell2 = sheetX.getRow(16).getCell((short) 7);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getDriverMethod()));
            // F18
            cell2 = sheetX.getRow(17).getCell((short) 5);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getDriverReelHole()));
            // F19
            cell2 = sheetX.getRow(18).getCell((short) 5);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getDriverReel()));
            // C18
            cell2 = sheetX.getRow(17).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getDriverHorizonNum().toString()));
            // C19
            cell2 = sheetX.getRow(18).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderDetail().getDriverVerticalNum().toString()));
            // b20 包装方式：
            cell2 = sheetX.getRow(19).getCell((short) 1);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getPackageMethod()));
            // d20 线架拆除：
            cell2 = sheetX.getRow(19).getCell((short) 3);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getWrapStandRemove()));
            // F20
            cell2 = sheetX.getRow(19).getCell((short) 5);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getPackageMark()));
            // C21
            cell2 = sheetX.getRow(20).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getWrapMachine()));
            // F21
            cell2 = sheetX.getRow(20).getCell((short) 5);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getWrapMachineChange()));
            // C22，23 ... N 装置名称
            String str = machineOrderDetail.getEquipment();
            JSONArray jsonArray = JSON.parseArray(str);
            Integer equipmentCount = 0;
            if (null != jsonArray) {
                // 该需求单的N个装置，插入N行
                equipmentCount = jsonArray.size();
                insertRow2(wb, sheetX, 22, equipmentCount - 1);
                if (isDebug) {
                    System.out.println("========order: " + machineOrderDetail.getOrderNum() + " inserted " + equipmentCount + " line");
                }
                for (int j = 0; j < equipmentCount; j++) {
                    Equipment eq = JSON.parseObject((String) jsonArray.get(j).toString(), Equipment.class);
                    /**
                     * 前端传进来异常数据时的处理
                     * 如果某数据为空 就都不写了。
                     */
                    if (eq.getName() == null) {
                        logger.error("异常数据，装置名称为空");
                        break;
                    }
                    if (eq.getNumber() == null) {
                        logger.error("异常数据，装置数量为空");
                        break;
                    }
                    if (eq.getPrice() == null) {
                        logger.error("异常数据，装置价格为空");
                        break;
                    }
                    if (eq.getType() == null) {
                        logger.error("异常数据，装置类型为空");
                        break;
                    }
                    cell2 = sheetX.getRow(22 + j).getCell((short) 0);
                    cell2.setCellValue(new HSSFRichTextString(Integer.toString(j + 1)));
                    cell2 = sheetX.getRow(22 + j).getCell((short) 1);
                    cell2.setCellValue(new HSSFRichTextString(eq.getName() + (eq.getType() != null && !"".equals(eq.getType()) ? "(" + eq.getType() + ")" : "")));
                    cell2 = sheetX.getRow(22 + j).getCell((short) 2);
                    /**
                     * 这里的数量是：每台机器的装置数*机器台数
                     */
                    cell2.setCellValue(eq.getNumber() * machineOrderDetail.getMachineNum());
                    // 单价
                    cell2 = sheetX.getRow(22 + j).getCell((short) 3);
                    if (displayPrice) {
                        cell2.setCellValue(new HSSFRichTextString(eq.getPrice().toString()));
                    } else {
                        cell2.setCellValue(new HSSFRichTextString("/"));
                    }
                    // 总价
                    cell2 = sheetX.getRow(22 + j).getCell((short) 4);
                    // int eqSum = eq.getNumber() * eq.getPrice() * machineOrderDetail.getMachineNum();
                    Double eqSum = eq.getNumber() * eq.getPrice() * machineOrderDetail.getMachineNum();
                    totalPriceOfOrder += eqSum;
                    if (displayPrice) {
                        cell2.setCellValue(new HSSFRichTextString(eqSum.toString()));
                    } else {
                        cell2.setCellValue(new HSSFRichTextString("/"));
                    }
                }
            } else {
                if (isDebug) {
                    System.out.println("========order: " + machineOrderDetail.getOrderNum() + " inserted 000 line");
                }
            }
            // 装置end
            /**
             *  空表格里是斜线，不用显示
             */
            // //优惠价格/台
            // cell2 = sheetX.getRow(22 + equipmentCount).getCell((short) 3);
            // if (displayPrice) {
            // cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getDiscounts()));
            // } else {
            // cell2.setCellValue(new HSSFRichTextString("/"));
            // }
            /**
             * 优惠总计
             * 该订单的 总优惠 = 优惠金额/台 * 台数 +  优惠金额 (用于抹零等)
             * 既有 优惠金额/台，又有优惠金额 。类似折上折的意思
             */
            cell2 = sheetX.getRow(22 + equipmentCount).getCell((short) 4);
            if (displayPrice) {
                // Integer sumOfDiscounts =Integer.parseInt(machineOrderDetail.getDiscounts()) * machineOrderDetail.getMachineNum() + Integer.parseInt(machineOrderDetail.getOrderTotalDiscounts());
                /**
                 * 用 xx + yy 的形式展示具体的值，如果存在折上折时，可以清楚看到具体情况：  xx(即优惠金额/台 * 台数) + yy（即优惠金额）
                 */
                // Integer sumOfDiscounts =Integer.parseInt(machineOrderDetail.getDiscounts()) * machineOrderDetail.getMachineNum();
                Double sumOfDiscounts = Double.parseDouble(machineOrderDetail.getDiscounts()) * machineOrderDetail.getMachineNum();
                cell2.setCellValue(new HSSFRichTextString(sumOfDiscounts.toString()) + " + " + machineOrderDetail.getOrderTotalDiscounts());
                totalPriceOfOrder -= (sumOfDiscounts + Double.parseDouble(machineOrderDetail.getOrderTotalDiscounts()));
            } else {
                cell2.setCellValue(new HSSFRichTextString("/"));
            }
            // 居间费用的台数
            cell2 = sheetX.getRow(23 + equipmentCount).getCell((short) 2);
            if (displayPrice) {
                cell2.setCellValue(machineOrderDetail.getMachineNum());
            } else {
                cell2.setCellValue(new HSSFRichTextString("/"));
            }
            // 居间费用/台
            cell2 = sheetX.getRow(23 + equipmentCount).getCell((short) 3);
            if (displayPrice) {
                cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getIntermediaryPrice()));
            } else {
                cell2.setCellValue(new HSSFRichTextString("/"));
            }
            // 居间费用总计  不计入订单总价
            cell2 = sheetX.getRow(23 + equipmentCount).getCell((short) 4);
            if (displayPrice) {
                Double sumOfIntermediary = Double.parseDouble(machineOrderDetail.getIntermediaryPrice()) * machineOrderDetail.getMachineNum();
                cell2.setCellValue(new HSSFRichTextString(sumOfIntermediary.toString()));
            } else {
                cell2.setCellValue(new HSSFRichTextString("/"));
            }
            // 订机数量
            cell2 = sheetX.getRow(24 + equipmentCount).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getMachineNum().toString()));
            // 机器单价
            cell2 = sheetX.getRow(24 + equipmentCount).getCell((short) 3);
            if (displayPrice) {
                cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getMachinePrice()));
            } else {
                cell2.setCellValue(new HSSFRichTextString("/"));
            }
            // 机器总价
            machineOrderSum = Double.parseDouble(machineOrderDetail.getMachinePrice()) * machineOrderDetail.getMachineNum();
            cell2 = sheetX.getRow(24 + equipmentCount).getCell((short) 4);
            if (displayPrice) {
                cell2.setCellValue(machineOrderSum);
            } else {
                cell2.setCellValue(new HSSFRichTextString("/"));
            }
            // 需求单总价
            totalPriceOfOrder += machineOrderSum;
            cell2 = sheetX.getRow(25 + equipmentCount).getCell((short) 4);
            if (displayPrice) {
                cell2.setCellValue(totalPriceOfOrder);
            } else {
                cell2.setCellValue(new HSSFRichTextString("/"));
            }
            // 合同的交货日期
            cell2 = sheetX.getRow(26 + equipmentCount).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(formatter2.format(contract.getContractShipDate())));
            // 计划发货日期
            cell2 = sheetX.getRow(27 + equipmentCount).getCell((short) 2);
            cell2.setCellValue(new HSSFRichTextString(formatter2.format(machineOrderDetail.getPlanShipDate())));
            // 备注
            cell2 = sheetX.getRow(28 + equipmentCount).getCell((short) 0);
            cell2.setCellValue(new HSSFRichTextString(machineOrderDetail.getMark()));
            // 判断改单还是拆单
            if (machineOrderDetail.getOriginalOrderId() != null && machineOrderDetail.getOriginalOrderId() != 0) {
                int status = machineOrderService.findById(machineOrderDetail.getOriginalOrderId()).getStatus();
                if (status == Constant.ORDER_CHANGED.intValue()) {
                    Condition condition = new Condition(OrderChangeRecord.class);
                    condition.createCriteria().andCondition("order_id = ", machineOrderDetail.getOriginalOrderId());
                    List<OrderChangeRecord> list = orderChangeRecordService.findByCondition(condition);
                    if (list != null && list.size() == 1) {
                        cell2 = sheetX.getRow(38 + equipmentCount).getCell((short) 2);
                        cell2.setCellValue(new HSSFRichTextString(list.get(0).getChangeReason()));
                    }
                } else if (status == Constant.ORDER_SPLITED.intValue()) {
                    // 拆单
                    Condition condition = new Condition(OrderSplitRecord.class);
                    condition.createCriteria().andCondition("order_id = ", machineOrderDetail.getId());
                    List<OrderSplitRecord> list = orderSplitRecordService.findByCondition(condition);
                    if (list != null && list.size() == 1) {
                        cell2 = sheetX.getRow(39 + equipmentCount).getCell((short) 2);
                        cell2.setCellValue(new HSSFRichTextString(list.get(0).getSplitReason()));
                    }
                }
            }
            /**
             *  需求单审核信息，来自 order_sign， 具体有几个签核步骤，可以动态填入表格
             */
            orderSignList = orderSignService.getOrderSignListByOrderId(machineOrderIdList.get(i));
            if (orderSignList.size() > 0) {
                // 取最后一次的签核，后续看是否需要根据时间来取最新
                orderSign = orderSignList.get(orderSignList.size() - 1);
                signContentItemList = JSON.parseArray(orderSign.getSignContent(), SignContentItem.class);
                // 需求单的N个签核，插入N行
                Integer orderSignCount = signContentItemList.size();
                insertRow2(wb, sheetX, 41 + equipmentCount, orderSignCount);
                for (int k = 0; k < orderSignCount; k++) {
                    /**
                     * 需求单签核的： 角色（部门）/人/时间/意见
                     */
                    // 1.签核角色（部门）
                    int roleId = signContentItemList.get(k).getRoleId();
                    // 根据roleId返回角色（部门）
                    String roleName = roleService.findById(roleId).getRoleName();
                    cell = sheetX.getRow(41 + equipmentCount + k).getCell((short) 0);
                    cell.setCellValue(new HSSFRichTextString(roleName));
                    // 2.签核人
                    cell = sheetX.getRow(41 + equipmentCount + k).getCell((short) 1);
                    cell.setCellValue(new HSSFRichTextString(signContentItemList.get(k).getUser()));
                    // 3.签核时间
                    cell = sheetX.getRow(41 + equipmentCount + k).getCell((short) 2);
                    if (null != signContentItemList.get(k).getDate()) {
                        cell.setCellValue(new HSSFRichTextString(formatter2.format(signContentItemList.get(k).getDate())));
                    }
                    cell = sheetX.getRow(41 + equipmentCount + k).getCell((short) 3);
                    cell.setCellValue(new HSSFRichTextString("意见"));
                    // 4.签核意见, 隐藏 成本核算员、财务会计、财务经理，这三个角色的的审批意见
                    cell = sheetX.getRow(41 + equipmentCount + k).getCell((short) 4);
                    if (roleName.equals("成本核算员") || roleName.equals("财务会计") || roleName.equals("财务经理")) {
                        if (displayPrice) {
                            cell.setCellValue(new HSSFRichTextString(signContentItemList.get(k).getComment()));
                        } else if (!signContentItemList.get(k).getComment().isEmpty()) {
                            cell.setCellValue(new HSSFRichTextString("/"));
                        }
                    } else {
                        cell.setCellValue(new HSSFRichTextString(signContentItemList.get(k).getComment()));
                    }
                    // 合并单元格
                    sheetX.addMergedRegion(new CellRangeAddress(41 + equipmentCount + k, 41 + equipmentCount + k, 4, 10));
                }
                // 最后删除多余一行
                sheetX.shiftRows(41 + equipmentCount + orderSignCount + 1, sheetX.getLastRowNum(), -1);
                // 订单类型、
                cell = sheetX.getRow(41 + equipmentCount + orderSignCount).getCell((short) 1);
                cell.setCellValue(new HSSFRichTextString(machineOrderDetail.getOrderType()));
                // 业务费、
                cell = sheetX.getRow(41 + equipmentCount + orderSignCount).getCell((short) 3);
                if (displayPrice) {
                    cell.setCellValue(new HSSFRichTextString(machineOrderDetail.getBusinessExpense()));
                } else {
                    cell.setCellValue(new HSSFRichTextString("/"));
                }
                // 保修费
                cell = sheetX.getRow(41 + equipmentCount + orderSignCount).getCell((short) 5);
                if (displayPrice) {
                    cell.setCellValue(new HSSFRichTextString(machineOrderDetail.getWarrantyFee()));
                } else {
                    cell.setCellValue(new HSSFRichTextString("/"));
                }
            }
        }
        // 修改模板内容导出新模板,生成路径供前端下载
        downloadPath = contractOutputDir + contract.getContractNum().replaceAll("/", "-") + ".xls";
        downloadPathForNginx = "/excel/" + contract.getContractNum().replaceAll("/", "-") + ".xls";
        out = new FileOutputStream(downloadPath);
        wb.write(out);
        out.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            fs.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    if ("".equals(downloadPath)) {
        return ResultGenerator.genFailResult("生成合同文件失败!");
    } else {
        return ResultGenerator.genSuccessResult(downloadPathForNginx);
    }
}