# M3RowServiceImpl.java

    package cloud.wconnect.finance.service.pl.impl;

    import cloud.wconnect.commonlib.component.BeanMapper;
    import cloud.wconnect.commonlib.cons.BaseErrorCodeEnum;
    import cloud.wconnect.commonlib.cons.CommonErrorI18nConstant;
    import cloud.wconnect.commonlib.exception.BadRequestException;
    import cloud.wconnect.commonlib.exception.ForbiddenException;
    import cloud.wconnect.commonlib.pojo.dto.ListPagedDTO;
    import cloud.wconnect.commonlib.util.I18nMessageUtils;
    import cloud.wconnect.finance.cons.pl.PlErrorI18nConstant;
    import cloud.wconnect.finance.cons.pl.RowCategoryEnum;
    import cloud.wconnect.finance.entity.pl.M3Account;
    import cloud.wconnect.finance.entity.pl.M3PlLayout;
    import cloud.wconnect.finance.entity.pl.M3Row;
    import cloud.wconnect.finance.entity.pl.M3Variable;
    import cloud.wconnect.finance.mapper.pl.M3RowMapper;
    import cloud.wconnect.finance.pojo.dto.pl.M3RowDTO;
    import cloud.wconnect.finance.pojo.dto.pl.LayoutElement;
    import cloud.wconnect.finance.pojo.dto.pl.RowFormulaDTO;
    import cloud.wconnect.finance.pojo.dto.pl.UploadResult;
    import cloud.wconnect.finance.pojo.query.pl.RowQuery;
    import cloud.wconnect.finance.service.pl.*;
    import cloud.wconnect.finance.service.pl.parseLayout.FormulaParser;
    import cloud.wconnect.finance.service.pl.parseLayout.LayoutParser;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
    import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    import com.google.common.collect.Lists;
    import org.apache.commons.collections.CollectionUtils;
    import org.apache.commons.lang3.StringUtils;
    import org.checkerframework.checker.nullness.qual.Nullable;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.annotation.Lazy;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    import org.springframework.web.multipart.MultipartFile;

    import java.io.InputStream;
    import java.text.MessageFormat;
    import java.util.*;
    import java.util.regex.Matcher;
    import java.util.regex.Pattern;
    import java.util.stream.Collectors;

    /**
     * <p>
     * 服务实现类
     * </p>
     *
     * @author lichen
     * @since 2025-09-25
     */
    @Service
    @DS("finance")
    public class M3RowServiceImpl extends ServiceImpl<M3RowMapper, M3Row> implements IM3RowService {

        @Autowired
        private IM3VariableService m3VariableService;
        @Autowired
        private I18nMessageUtils i18nMessageUtils;
        @Autowired
        @Lazy
        private IM3PlLayoutService m3PlLayoutService;
        @Autowired
        private BeanMapper beanMapper;
        @Autowired
        private PlCommonService plCommonService;
        @Autowired
        private IM3AccountService m3AccountService;

        /**
         * 上传M3 Row文件
         *
         * @param tenantId   租户id
         * @param propertyId 资产id
         * @param file       文件
         * @return cloud.wconnect.finance.pojo.dto.pl.UploadResult
         * @author lichen
         * @date 2025/9/25
         */
        @Override
        @Transactional(rollbackFor = Exception.class)
        public UploadResult upload(Integer tenantId, int propertyId, MultipartFile file) {
            List<LayoutElement> elements;
            //解析文件
            try (InputStream inputStream = file.getInputStream()) {
                elements = LayoutParser.parseLayoutFile(inputStream);
            } catch (Exception e) {
                //Layout.xls file parse error
                throw new BadRequestException(PlErrorI18nConstant.LAYOUT_FILE_PARSE_ERROR,
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "Layout.xls file parse error", e);
            }
            if (CollectionUtils.isEmpty(elements)) {
                //Layout.xls file is empty
                throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate("Layout.xls"),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "Layout.xls file is empty");
            }

            //处理 groups and sub-total 公式
            FormulaParser.parse(elements);

            //LayoutElement -> M3 Row entity
            List<M3Row> m3RowList = beanMapper.mapList(elements, M3Row.class, (element, entity) -> {
                entity.setTenantId(tenantId);
                entity.setPropertyId(propertyId);
                entity.setRowNumber(element.getRowId());
                entity.setRowCategory(element.getType());
                entity.setRowName(element.getName());
                entity.setAccountNumber(element.getGlCode());
            });

            //拓扑排序，用于校验是否有循环依赖
            List<M3Row> topologySortList = plCommonService.topologySort(m3RowList, Collections.emptyList());
            //校验公式中使用的Row在Layout.xls中是否都存在
            HashSet<String> allVariables = checkFormulaUsedRowExist(topologySortList);

            //获取所有的RowNumber
            Set<String> rowNumbers = m3RowList.stream().map(M3Row::getRowNumber).collect(Collectors.toSet());
            //获取资产配置过的M3 Row
            List<M3Row> existM3RowList = listByPropertyIdOrderById(propertyId);

            //非第一次上传时，从本次删除的M3 row中筛选出被Layout配置使用的M3 Row
            List<String> usedRowNames = new ArrayList<>();
            //非第一次上传时，Layout配置中type异常的rowNumber集合
            List<String> layoutConfigTypeAbnormalRowNumbers = new ArrayList<>();
            if (CollectionUtils.isNotEmpty(existM3RowList)) {
                //获取Layout配置中引用的RowNumber集合
                Set<String> referenceRowNumbers = m3PlLayoutService.findReferenceRowNumber(propertyId);

                //本次更新Layout.xls文件后，删除的M3 Row
                Set<String> delM3RowNumbers = existM3RowList.stream().map(M3Row::getRowNumber)
                        .filter(item -> !rowNumbers.contains(item))
                        .collect(Collectors.toSet());
                //从本次删除的M3 row中筛选出被Layout配置使用的M3 Row
                Set<String> usedRowNumbers = referenceRowNumbers.stream().filter(delM3RowNumbers::contains).collect(Collectors.toSet());
                //获取Row名称
                usedRowNames = existM3RowList.stream().filter(item -> usedRowNumbers.contains(item.getRowNumber()))
                        .map(M3Row::getRowName).toList();

                //获取本次导入Layout.xls中M3 是否可以计算
                Map<String, Boolean> row2CanCalculation = m3RowList.stream().collect(Collectors.toMap(M3Row::getRowNumber,
                        (item) -> RowCategoryEnum.needCalculation(item.getRowCategory())));
                referenceRowNumbers.forEach(rowNumber -> {
                    Boolean canCalculation = row2CanCalculation.get(rowNumber);
                    if (Objects.nonNull(canCalculation) && !canCalculation) {
                        // Layout.xls 中配置的RowNumber类型异常
                        layoutConfigTypeAbnormalRowNumbers.add(rowNumber);
                    }
                });
            }

            //查询资产预置的变量集合
            List<M3Variable> m3Variables = m3VariableService.listByPropertyId(propertyId);
            List<String> variableNames = m3Variables.stream().map(M3Variable::getName).toList();
            //过滤出本次导入Layout.xls中新增的变量
            List<String> insertVariables = allVariables.stream().filter(item -> !variableNames.contains(item)).toList();

            /*
             * item类型的Row Name用Account Name替换
             */
            Map<String, String> accountNumber2Name = m3AccountService.listByPropertyId(propertyId)
                    .stream().collect(Collectors.toMap(M3Account::getAccountNumber, M3Account::getAccountName));
            m3RowList.forEach(item -> {
                if (item.getRowCategory().equalsIgnoreCase(RowCategoryEnum.ITEM.getValue())) {
                    //判断accountNumber是否是数字
                    if (!StringUtils.isNumeric(item.getAccountNumber())) {
                        String[] inputs = extractLastNumber(item.getRowName());
                        item.setRowName(inputs[0]);
                        item.setAccountNumber(inputs[1]);
                    }

                    //替换RowName
                    String accountName = accountNumber2Name.get(item.getAccountNumber());
                    if (StringUtils.isNotBlank(accountName)) {
                        item.setRowName(accountName);
                    }
                } else if (item.getRowCategory().equalsIgnoreCase(RowCategoryEnum.ITEM_SUBTOTAL.getValue())) {
                    //Item Subtotal类型的AccountNumber置为空
                    item.setAccountNumber("");
                }
            });

            //先删除Row
            remove(new LambdaQueryWrapper<M3Row>().eq(M3Row::getPropertyId, propertyId));

            //保存导入的Row集合
            if (CollectionUtils.isNotEmpty(m3RowList)) {
                // 分批处理，每300条记录一批
                int batchSize = 300;
                for (int i = 0; i < m3RowList.size(); i += batchSize) {
                    int endIndex = Math.min(i + batchSize, m3RowList.size());
                    List<M3Row> batch = m3RowList.subList(i, endIndex);
                    baseMapper.insertBatch(batch);
                }
            }

            //新增变量
            ArrayList<M3Variable> insertBatchM3Variables = Lists.newArrayListWithCapacity(insertVariables.size());
            for (String insertVariable : insertVariables) {
                M3Variable entity = new M3Variable();
                entity.setTenantId(tenantId);
                entity.setPropertyId(propertyId);
                entity.setName(insertVariable);
                entity.setValue("");
                entity.setType(M3Variable.TYPE_FORMULA);
                entity.setUpdateLog("[]");
                insertBatchM3Variables.add(entity);
            }
            m3VariableService.saveBatch(insertBatchM3Variables);

            //更新{[Rooms Rented]}变量的值
            m3VariableService.updateRoomsRentedValue(propertyId);

            return new UploadResult(usedRowNames, CollectionUtils.isNotEmpty(insertVariables), layoutConfigTypeAbnormalRowNumbers);
        }

        /**
         * 校验公式中使用的Row在Layout.xls中是否都存在
         *
         * @param orderList 排序后的元素列表
         * @return HashSet 变量集合
         * @author lichen
         * @date 2025/9/25
         */
        private HashSet<String> checkFormulaUsedRowExist(List<M3Row> orderList) {
            HashSet<String> allRowNumbers = new HashSet<>();
            HashSet<String> variables = new HashSet<>();
            for (M3Row m3Row : orderList) {
                allRowNumbers.add(m3Row.getRowNumber());

                String formula = m3Row.getFormula();
                if (formula != null && !formula.trim().isEmpty()) {
                    Matcher matcher = PlCommonService.CURLY_BRACES_PATTERN.matcher(formula);
                    while (matcher.find()) {
                        String dependency = matcher.group(1).trim();

                        //正则匹配 数字:数字
                        if (plCommonService.isValidRowFormat(dependency) && !allRowNumbers.contains(dependency)) {
                            //公式中使用的Row在Layout.xls中不存在
                            throw new BadRequestException(PlErrorI18nConstant.FORMULA_USED_ROW_NOT_EXIST_IN_LAYOUT_FILE,
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The Row used in the formula does not exist in Layout.xls");
                        }

                        //正则匹配 [xxx]
                        if (plCommonService.isValidBracketFormat(dependency)) {
                            variables.add("{" + dependency + "}");
                        }
                    }
                }
            }
            return variables;
        }

        /**
         * 根据资产id查询M3 Row
         *
         * @param propertyId 资产id
         * @return java.util.List<cloud.wconnect.finance.entity.pl.M3Row>
         * @author lichen
         * @date 2025/9/25
         */
        @Override
        public List<M3Row> listByPropertyIdOrderById(int propertyId) {
            return list(new LambdaQueryWrapper<M3Row>().eq(M3Row::getPropertyId, propertyId)
                    .orderByAsc(M3Row::getId));
        }

        /**
         * 标记Item类型的Row Account不存在
         *
         * @param propertyId       资产id
         * @param deleteM3Accounts 删除的Account数据
         * @return void
         * @author lichen
         * @date 2025/9/25
         */
        @Override
        public void markItemRowAccountNotExist(Integer propertyId, List<M3Account> deleteM3Accounts) {
            if (CollectionUtils.isEmpty(deleteM3Accounts)) return;

            //查询指定资产、item类型、指定accountNumber的M3 Row
            List<M3Row> updateList = list(new LambdaQueryWrapper<M3Row>().eq(M3Row::getPropertyId, propertyId)
                    .eq(M3Row::getRowCategory, RowCategoryEnum.ITEM.getValue())
                    .in(M3Row::getAccountNumber, deleteM3Accounts.stream().map(M3Account::getAccountNumber).toList()));

            if (CollectionUtils.isEmpty(updateList)) return;

            List<Integer> ids = updateList.stream().map(M3Row::getId).toList();
            //accountNumber设置为null
            baseMapper.updateAccountNumberIsNull(ids);
        }

        /**
         * 分页查询
         *
         * @param propertyId 资产id
         * @param query      查询参数
         * @return cloud.wconnect.commonlib.pojo.dto.ListPagedDTO<cloud.wconnect.finance.entity.pl.M3Row>
         * @author lichen
         * @date 2025/9/25
         */
        @Override
        public ListPagedDTO<M3Row> pageList(int propertyId, RowQuery query) {
            LambdaQueryWrapper<M3Row> lambdaQueryWrapper = new LambdaQueryWrapper<M3Row>().eq(M3Row::getPropertyId, propertyId)
                    .eq(StringUtils.isNotBlank(query.getRowNumber()), M3Row::getRowNumber, query.getRowNumber())
                    .in(CollectionUtils.isNotEmpty(query.getRowCategoryList()), M3Row::getRowCategory, query.getRowCategoryList())
                    .like(StringUtils.isNotBlank(query.getRowName()), M3Row::getRowName, query.getRowName())
                    .like(StringUtils.isNotBlank(query.getAccountNumber()), M3Row::getAccountNumber, query.getAccountNumber())
                    .orderByAsc(M3Row::getId);
            Page<M3Row> page = page(new Page<>(query.getPage(), query.getSize()), lambdaQueryWrapper);
            if (page.getTotal() == 0) {
                return new ListPagedDTO<>();
            }
            return new ListPagedDTO<>(page.getTotal(), page.getRecords());
        }

        /**
         * 获取Row公式
         *
         * @param propertyId 资产id
         * @param rowNumber  Row编号
         * @return cloud.wconnect.finance.pojo.dto.pl.RowFormulaDTO
         * @author lichen
         * @date 2025/9/25
         */
        @Override
        public RowFormulaDTO getRowFormula(int propertyId, String rowNumber) {
            //校验Row是否存在
            M3Row m3Row = checkExist(rowNumber, propertyId);
            //校验归属
            checkBelong(m3Row, propertyId);

            RowFormulaDTO rowFormulaDTO = new RowFormulaDTO();
            rowFormulaDTO.setRowNumber(m3Row.getRowNumber());
            rowFormulaDTO.setFormula(m3Row.getFormula());
            rowFormulaDTO.setList(Collections.emptyList());

            ArrayList<String> categoryList = Lists.newArrayList(RowCategoryEnum.FORMULA.getValue(),
                    RowCategoryEnum.GROUP_SUBTOTAL.getValue(), RowCategoryEnum.ITEM_SUBTOTAL.getValue());
            if (categoryList.contains(m3Row.getRowCategory())) {
                //查询公式中使用的Row编号
                List<String> calculationFactorList = plCommonService.parseFormulaUsedRowNumber(m3Row.getFormula());
                if (CollectionUtils.isNotEmpty(calculationFactorList)) {
                    //查询计算因子的信息
                    List<M3Row> m3Rows = listByRowNumber(propertyId, calculationFactorList);
                    List<M3RowDTO> m3RowDTOS = beanMapper.mapList(m3Rows, M3RowDTO.class);
                    rowFormulaDTO.setList(m3RowDTOS);
                }
            }
            return rowFormulaDTO;
        }

        /**
         * 根据Row编号查询
         *
         * @param propertyId    资产id
         * @param rowNumberList Row编号集合
         * @return java.util.List<cloud.wconnect.finance.entity.pl.M3Row>
         * @author lichen
         * @date 2025/9/25
         */
        @Override
        public List<M3Row> listByRowNumber(int propertyId, List<String> rowNumberList) {
            if (CollectionUtils.isEmpty(rowNumberList)) {
                return Collections.emptyList();
            }
            return list(new LambdaQueryWrapper<M3Row>().eq(M3Row::getPropertyId, propertyId)
                    .in(M3Row::getRowNumber, rowNumberList));
        }

        /**
         * 检查Row是否存在
         *
         * @param rowNumber  Row编号
         * @param propertyId 资产id
         * @return cloud.wconnect.finance.entity.pl.M3Row
         * @author lichen
         * @date 2025/10/2
         */
        @Override
        public M3Row checkExist(String rowNumber, int propertyId) {
            //查询M3 Row
            M3Row m3Row = getOne(new LambdaQueryWrapper<M3Row>()
                    .eq(M3Row::getPropertyId, propertyId)
                    .eq(M3Row::getRowNumber, rowNumber));
            //校验M3 Row是否存在
            if (Objects.isNull(m3Row)) {
                throw new BadRequestException(CommonErrorI18nConstant.DATA_NOT_FOUND,
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "M3 row is not found");
            }
            return m3Row;
        }

        /**
         * 检查Row是否存在
         *
         * @param rowNumbers Row编号集合
         * @param propertyId 资产id
         * @return java.util.List<cloud.wconnect.finance.entity.pl.M3Row>
         * @author lichen
         * @date 2026/3/23
         */
        @Override
        public List<M3Row> checkExist(List<String> rowNumbers, int propertyId) {
            if (CollectionUtils.isEmpty(rowNumbers)) return Collections.emptyList();
            //查询M3 Row
            List<M3Row> m3Rows = list(new LambdaQueryWrapper<M3Row>().eq(M3Row::getPropertyId, propertyId)
                    .in(M3Row::getRowNumber, rowNumbers));
            if (CollectionUtils.isEmpty(m3Rows) || m3Rows.size() != rowNumbers.size()) {
                throw new BadRequestException(CommonErrorI18nConstant.DATA_NOT_FOUND,
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "M3 row is not found");
            }
            return m3Rows;
        }

        /**
         * 检查Row所属资产
         *
         * @param m3Row      Row数据
         * @param propertyId 资产id
         * @return void
         * @author lichen
         * @date 2025/10/2
         */
        @Override
        public void checkBelong(M3Row m3Row, int propertyId) {
            //校验M3Row是否属于当前资产
            if (m3Row.getPropertyId() != propertyId) {
                throw new ForbiddenException(BaseErrorCodeEnum.DATA_FORBIDDEN.getI18nCode(),
                        BaseErrorCodeEnum.DATA_FORBIDDEN.getCode(),
                        MessageFormat.format("m3Row not belong currentPropertyId, m3RowNumber:{0},belongPropertyId:{1},currentPropertyId:{2}",
                                m3Row.getRowNumber(), m3Row.getPropertyId(), propertyId));
            }
        }

        /**
         * 检查Row所属资产
         *
         * @param m3RowList  Row数据集合
         * @param propertyId 资产id
         * @author lichen
         * @date 2026/3/23
         */
        @Override
        public void checkBelong(List<M3Row> m3RowList, int propertyId) {
            List<String> m3RowNumbers = m3RowList.stream()
                    .filter(m3Row -> m3Row.getPropertyId() != propertyId)
                    .map(M3Row::getRowNumber).toList();
            //校验M3Row是否属于当前资产
            if (CollectionUtils.isNotEmpty(m3RowNumbers)) {
                throw new ForbiddenException(BaseErrorCodeEnum.DATA_FORBIDDEN.getI18nCode(),
                        BaseErrorCodeEnum.DATA_FORBIDDEN.getCode(),
                        MessageFormat.format("m3Row not belong currentPropertyId, m3RowNumbers:{0},currentPropertyId:{1}",
                                m3RowNumbers, propertyId));
            }
        }

        /**
         * 根据AccountNumber查询M3Row
         *
         * @param propertyId        资产id
         * @param accountNumberList AccountNumber集合
         * @return java.util.List<cloud.wconnect.finance.entity.pl.M3Row>
         * @author lichen
         * @date 2025/10/2
         */
        @Override
        public List<M3Row> listByAccountNumber(int propertyId, List<String> accountNumberList) {
            if (CollectionUtils.isEmpty(accountNumberList)) return Collections.emptyList();
            return list(new LambdaQueryWrapper<M3Row>().eq(M3Row::getPropertyId, propertyId)
                    .eq(M3Row::getRowCategory, RowCategoryEnum.ITEM.getValue())
                    .in(M3Row::getAccountNumber, accountNumberList));
        }

        /**
         * 提取字符串中的最后一个数字
         *
         * @param input 输入字符串
         * @return java.lang.String[]
         * @author lichen
         * @date 2025/10/3
         */
        private String[] extractLastNumber(String input) {
            /*
             * 处理一下格式字符串
             * Travel Expenses (Training Related) -6465 （有空格）
             * Travel Expenses (Training Related)-6465 （无空格）
             * Electricity Usage (kw/h) (9310) （括号包围数字）
             * Electricity Usage (kw/h)(9310) （无空格且括号包围数字）
             */

            // 统一处理多种格式的正则表达式
            // (.*?) - 非贪婪匹配任意字符，用于获取描述部分
            // \\s* - 匹配零个或多个空白字符（可选）
            // [\\s\(]* - 匹配零个或多个空白字符或左括号
            // (-?\\d+) - 捕获可选的负号和一个或多个数字
            // [\)]* - 匹配零个或多个右括号
            // $ - 锚定到字符串末尾
            String regex = "(.*?)\\s*[\\s\\(]*(-?\\d+)[\\)]*$";
            Pattern pattern = Pattern.compile(regex);
            Matcher matcher = pattern.matcher(input.trim());

            if (matcher.find()) {
                String description = matcher.group(1).trim();
                String number = matcher.group(2);
                // 如果是负数，去掉负号
                if (number.startsWith("-")) {
                    number = number.substring(1);
                }
                return new String[]{description, number};
            }
            // 如果不匹配，返回原始字符串和空值
            return new String[]{input, ""};
        }

    }