Examples with MachineOrderDetail com.eservice.api.model.machine_order.MachineOrderDetail used on opensource projects

Example 6 with MachineOrderDetail

use of com.eservice.api.model.machine_order.MachineOrderDetail 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);
    }
}