data-daq/service-daq/src/main/java/com/simuwang/daq/service/ValuationParseService.java

package com.simuwang.daq.service;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.StopWatch;
import cn.hutool.core.exceptions.ExceptionUtil;
import cn.hutool.core.util.StrUtil;
import com.simuwang.base.common.util.ValuationBusinessUtils;
import com.simuwang.base.common.util.ValuationDataUtils;
import com.simuwang.base.common.util.ValuationTableParseUtil;
import com.simuwang.base.mapper.*;
import com.simuwang.base.pojo.dos.*;
import com.simuwang.base.pojo.valuation.*;
import com.smppw.utils.BigDecimalUtils;
import com.smppw.utils.DateUtil;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.*;
import java.util.concurrent.*;
import java.util.stream.Collectors;

@Service
public class ValuationParseService {

    private static final Logger log = LoggerFactory.getLogger(ValuationParseService.class);

    private final ValuationMarketCodeMapper valuationMarketCodeMapper;
    private final PdfValuationRecordMapper pdfValuationRecordMapper;
    private final ThreadPoolTaskExecutor executor;


    public ValuationParseService(ValuationMarketCodeMapper valuationMarketCodeMapper, PdfValuationRecordMapper pdfValuationRecordMapper,
                                 @Qualifier("valuationExecutor") ThreadPoolTaskExecutor executor) {
        this.valuationMarketCodeMapper = valuationMarketCodeMapper;
        this.pdfValuationRecordMapper = pdfValuationRecordMapper;
        this.executor = executor;
    }

    public List<AssetsValuationResult.Record> parseValuationExcel(List<ValuationNeedParseParam> valuationNeedParseParams) {
        if (CollUtil.isEmpty(valuationNeedParseParams)) {
            return CollUtil.newArrayList();
        }
        StopWatch stopWatch = new StopWatch();
        stopWatch.start("create valuation executor");
        stopWatch.stop();
        log.info("create valuation executor cost -> {}", stopWatch.prettyPrint(TimeUnit.MILLISECONDS));
        List<AssetsValuationResult.Record> records = CollectionUtil.newArrayList();
        List<Future<ValuationResult>> futureList = CollectionUtil.newArrayList();
        List<String> marketCodeList = valuationMarketCodeMapper.queryMarketCode();
        try {
            for (ValuationNeedParseParam valuationNeedParseParam : valuationNeedParseParams) {
                ParseValuationInfo parseValuationInfo;
                AssetsValuationResult.Record record = new AssetsValuationResult.Record();
                PreAssetsValuationInfo<PreAssetsValuationBase> preAssetsValuationInfo = ValuationBusinessUtils.importFile(valuationNeedParseParam);
                PreAssetsValuationInfo<PreAssetsValuationBase>.Error error = preAssetsValuationInfo.getError();
                if (error != null) {
                    error = preAssetsValuationInfo.getError();
                    record.setExcelName(error.getExcelName()).setMsg(error.getMsg()).setRowNum(error.getRowNum()).setColumnNum(error.getCellNum())
                            .setSuccess(0);
                    records.add(record);
                } else {
                    AssetsValuationExcelInfo<AssetsValuationInfo> excelInfo = processDataV2(preAssetsValuationInfo);
                    // 邮件解析 -> 需要根据备案编码和基金名称去查询对应的基金id
                    if (StringUtils.isEmpty(valuationNeedParseParam.getFundId())) {
                        String headInfo = excelInfo.getExtInfo().getHeadInfo();
                        if (StringUtils.isNotEmpty(headInfo)) {
                            AssetsValuationDetails details = excelInfo.getExtInfo();
                            parseValuationInfo = parseRegisterNumAndFundName(headInfo);
                            record.setParseValuationInfo(parseValuationInfo);
                            log.info("表格:{},从表格中解析到的基金名称和备案编码:{}", valuationNeedParseParam.getOriginFileName(), parseValuationInfo.toString());

                            record.setNav(String.valueOf(details.getNav()));
                            record.setCumulativeNavWithdrawal(String.valueOf(details.getCumulativeNav()));
                            record.setExcelName(valuationNeedParseParam.getOriginFileName());
                            record.setDate(details.getValuationDate());
                            record.setExcelName(valuationNeedParseParam.getOriginFileName());
                            record.setDate(details.getValuationDate());
                            record.setSuccess(1);

                            String valuationDate = details.getValuationDate();
                            List<AssetsValuationInfo> data = excelInfo.getData();
                            if (CollUtil.isNotEmpty(data)) {
                                ValuationTableDO valuationTableDO = buildValuationTableDO(details, valuationNeedParseParam);
                                record.setValuationTableDO(valuationTableDO);

                                Future<ValuationResult> future = executor.submit(() -> {
                                    ValuationResult valuationResult = new ValuationResult();
                                    List<CmValuationTableAttribute> valuationTableAttributes = ValuationTableParseUtil.getAttrList(data);
                                    record.setValuationTableAttributeList(valuationTableAttributes);

                                    List<FundPositionDetailDO> fundPositionDetailDOList = ValuationTableParseUtil.parseDataNew(valuationTableAttributes, valuationTableDO, marketCodeList);
                                    record.setFundPositionDetailDOList(fundPositionDetailDOList);

                                    if (CollectionUtil.isNotEmpty(fundPositionDetailDOList)) {
                                        fundPositionDetailDOList.stream().filter(p -> "202".equals(p.getSecuritiesCode())).findFirst()
                                                .ifPresent(p -> valuationResult.setCumulativeNavWithdrawal(p.getMarketValue() != null ? String.valueOf(p.getMarketValue()) : null));
                                    }
                                    valuationResult.setValuationTableAttributes(valuationTableAttributes);
                                    valuationResult.setList(fundPositionDetailDOList);
                                    valuationResult.setRecord(record);
                                    valuationResult.setValuationDate(valuationDate);
                                    return valuationResult;
                                });
                                futureList.add(future);
                            }
                        }
                    }
                }
            }

            for (Future<ValuationResult> future : futureList) {
                try {
                    ValuationResult r = future.get(3600, TimeUnit.SECONDS);
                    AssetsValuationResult.Record record = r.getRecord();
                    record.setCumulativeNavWithdrawal(record.getCumulativeNavWithdrawal());
                    // 获取资产净值和资产份额
                    BigDecimal assetNet = r.getList().stream().filter(e -> StrUtil.isNotBlank(e.getSecuritiesCode()) && "112".equals(e.getSecuritiesCode()))
                            .findFirst().map(FundPositionDetailDO::getMarketValue).orElse(null);
                    BigDecimal assetShare = r.getList().stream().filter(e -> StrUtil.isNotBlank(e.getSecuritiesCode()) && "107".equals(e.getSecuritiesCode()))
                            .findFirst().map(FundPositionDetailDO::getMarketValue).orElse(null);
                    record.setAssetNet(assetNet != null ? String.valueOf(assetNet) : null);
                    record.setAssetShare(assetShare != null ? String.valueOf(assetShare) : null);
                    records.add(record);
                } catch (InterruptedException | ExecutionException | TimeoutException e) {
                    log.info("valuation excel upload future exec exception: {}", e.getMessage(), e);
                }
            }
        } catch (
                Exception exception) {
            log.error("parse valuation excel error ->{}", ExceptionUtil.stacktraceToString(exception));
        } finally {
            stopWatch.start("destruction valuation executor");
            stopWatch.stop();
            log.info("destruction valuation executor cost -> {}", stopWatch.prettyPrint(TimeUnit.MILLISECONDS));
        }

        //保存PDF估值表记录
        savePdfValuationRecord(valuationNeedParseParams, records);
        return records;
    }

    /**
     * 保存PDF估值表记录
     *
     * @param valuationNeedParseParams 估值表解析参数
     * @param records                  估值表解析结果
     */
    private void savePdfValuationRecord(List<ValuationNeedParseParam> valuationNeedParseParams, List<AssetsValuationResult.Record> records) {
        if (CollUtil.isEmpty(records)) {
            return;
        }
        List<AssetsValuationResult.Record> pdfRecordList = records.stream().filter(e -> e.getExcelName().endsWith("pdf")).collect(Collectors.toList());
        if (CollUtil.isEmpty(pdfRecordList)) {
            return;
        }
        List<PdfValuationRecordDO> pdfValuationRecordDoList = buildCmPdfValuationRecordDo(valuationNeedParseParams, pdfRecordList);
        if (CollUtil.isNotEmpty(pdfValuationRecordDoList)) {
            pdfValuationRecordMapper.batchInsert(pdfValuationRecordDoList);
        }
    }

    private List<PdfValuationRecordDO> buildCmPdfValuationRecordDo(List<ValuationNeedParseParam> valuationNeedParseParams,
                                                                   List<AssetsValuationResult.Record> pdfRecordList) {
        if (CollUtil.isEmpty(pdfRecordList)) {
            return CollUtil.newArrayList();
        }
        Map<String, ValuationNeedParseParam> needParseParamMap = valuationNeedParseParams.stream()
                .collect(Collectors.toMap(ValuationNeedParseParam::getOriginFileName, v -> v));
        return pdfRecordList.stream().map(e -> {
            PdfValuationRecordDO pdfValuationRecordDo = new PdfValuationRecordDO();
            ValuationNeedParseParam parseParam = needParseParamMap.get(e.getExcelName());
            if (parseParam != null) {
                pdfValuationRecordDo.setUploadDate(new Date());
                pdfValuationRecordDo.setExcelUrl(parseParam.getFile().getAbsolutePath());
                pdfValuationRecordDo.setPdfUrl(parseParam.getFileUrl());
                pdfValuationRecordDo.setFromEmail(parseParam.getFromEmail() == 1 ? 1 : 0);
            }
            pdfValuationRecordDo.setFundId(e.getFundId());
            pdfValuationRecordDo.setFileName(e.getExcelName());
            pdfValuationRecordDo.setIsSuccess(e.getSuccess());
            pdfValuationRecordDo.setReason(e.getMsg());
            pdfValuationRecordDo.setCreatorId(0);
            pdfValuationRecordDo.setUpdaterId(0);
            pdfValuationRecordDo.setCreateTime(new Date());
            pdfValuationRecordDo.setUpdateTime(new Date());
            pdfValuationRecordDo.setIsvalid(1);
            return pdfValuationRecordDo;
        }).collect(Collectors.toList());
    }

    /**
     * 解析备案编码和备案编码
     *
     * @param headInfo excel第一行和第二行的内容,中间以'@'符号分隔
     * @return 备案编码和备案编码
     */
    public ParseValuationInfo parseRegisterNumAndFundName(String headInfo) {
        ParseValuationInfo info = new ParseValuationInfo();
        if (StringUtils.isNotEmpty(headInfo)) {
            List<String> contentList = Arrays.stream(headInfo.split("@")).collect(Collectors.toList());
            for (String content : contentList) {
                String registerNumber = content.length() > 6 ? content.substring(0, 6) : null;
                if (StrUtil.isNotBlank(registerNumber) && !ValuationDataUtils.hasChinese(registerNumber, false)) {
                    info.setRegisterNumber(registerNumber);
                }
                if (content.contains("估值表") || content.contains("专用表")) {
                    String originRegisterNumber = info.getRegisterNumber();
                    String originFundName = info.getFundName();
                    if (content.contains("___")) {
                        List<String> collect = Arrays.stream(content.split("___")).collect(Collectors.toList());
                        if (collect.size() == 4 && content.contains("专用表")) {
                            info.setRegisterNumber(collect.get(1));
                            info.setFundName(collect.get(2));
                        } else {
                            if (CollUtil.isNotEmpty(collect) && StrUtil.isBlank(originRegisterNumber) && !ValuationDataUtils.hasChinese(registerNumber, false)) {
                                originRegisterNumber = collect.get(0);
                                info.setRegisterNumber(originRegisterNumber);
                            }
                            originFundName = collect.size() == 2 ? collect.get(0) : originFundName;
                            originFundName = collect.size() > 2 ? collect.get(1) : originFundName;
                            info.setFundName(originFundName);
                        }

                    } else if (content.contains("__")) {
                        List<String> collect = Arrays.stream(content.split("__")).collect(Collectors.toList());
                        if (collect.size() == 4 && content.contains("专用表")) {
                            info.setRegisterNumber(collect.get(1));
                            info.setFundName(collect.get(2));
                        } else {
                            if (CollUtil.isNotEmpty(collect) && StrUtil.isBlank(originRegisterNumber) && !ValuationDataUtils.hasChinese(registerNumber, false)) {
                                originRegisterNumber = collect.get(0);
                                info.setRegisterNumber(originRegisterNumber);
                            }
                            originFundName = collect.size() == 2 ? collect.get(0) : originFundName;
                            originFundName = collect.size() > 2 ? collect.get(1) : originFundName;
                            info.setFundName(originFundName);
                        }
                    } else if (content.contains("_")) {
                        List<String> collect = Arrays.stream(content.split("_")).collect(Collectors.toList());
                        if (collect.size() == 4 && content.contains("专用表")) {
                            info.setRegisterNumber(collect.get(1));
                            info.setFundName(collect.get(2));
                        } else {
                            if (CollUtil.isNotEmpty(collect) && StrUtil.isBlank(originRegisterNumber) && !ValuationDataUtils.hasChinese(registerNumber, false)) {
                                originRegisterNumber = collect.get(0);
                                info.setRegisterNumber(originRegisterNumber);
                            }
                            originFundName = collect.size() == 2 ? collect.get(0) : originFundName;
                            originFundName = collect.size() > 2 ? collect.get(1) : originFundName;
                            info.setFundName(originFundName);
                        }
                    }
                }
            }
        }
        //兼容格式: xxx估值表@基金名称 -> 只能识别到基金名称
        if (StrUtil.isBlank(info.getFundName()) && StrUtil.isBlank(info.getRegisterNumber())) {
            List<String> contentList = Arrays.stream(headInfo.split("@")).collect(Collectors.toList());
            if (CollUtil.isNotEmpty(contentList) && contentList.size() >= 2) {
                String fundName = !contentList.get(1).contains("_") ? contentList.get(1) : null;
                info.setFundName(fundName);
            }
        }

        // 兼容格式:编码编码+基金名称+日期+估值报表四级 -> SXT974同增FOF稳增1期私募证券投资基金2024年08月02日估值报表四级
        if (StrUtil.isBlank(info.getFundName()) && StrUtil.isNotBlank(info.getRegisterNumber())) {
            int index = StrUtil.indexOfIgnoreCase(headInfo, "私募证券投资基金");
            // 说明找到了
            if (index != -1) {
                String shortName = headInfo.substring(0, StrUtil.indexOfIgnoreCase(headInfo, "私募证券投资基金"));
                String fundName = shortName.replace(info.getRegisterNumber(), "") + "私募证券投资基金";
                info.setFundName(fundName);
            }
        }
        return info;
    }

    public ValuationTableDO buildValuationTableDO(AssetsValuationDetails details, ValuationNeedParseParam valuationNeedParseParam) {
        ValuationTableDO tableInfo = new ValuationTableDO();
        tableInfo.setValuationDate(DateUtil.StringToDate(details.getValuationDate()));
        tableInfo.setIsAnalyzed(0);
        tableInfo.setIsvalid(1);
        tableInfo.setCreateTime(DateTime.now());
        tableInfo.setUpdateTime(DateTime.now());
        tableInfo.setCreatorId(0);
        tableInfo.setUpdaterId(0);
        tableInfo.setTotalMarketValue(BigDecimalUtils.toBigDecimal(details.getTotalMarketValue()));
        tableInfo.setNetAssetsValue(BigDecimalUtils.toBigDecimal(details.getNetAssetsValue()));
        tableInfo.setIncrement(BigDecimalUtils.toBigDecimal(details.getIncrement()));
        tableInfo.setTitle(details.getTitle());
        tableInfo.setNav(BigDecimalUtils.toBigDecimal(details.getNav()));
        tableInfo.setFileUrl(valuationNeedParseParam.getFileUrl());
        tableInfo.setHeadInfo(details.getHeadInfo());
        tableInfo.setTailInfo(details.getTailInfo());
        tableInfo.setTableType(details.getType());
        tableInfo.setFromEmail(valuationNeedParseParam.getFromEmail());
        tableInfo.setOriginalFile(valuationNeedParseParam.getOriginFileName());
        tableInfo.setConvertedFile(valuationNeedParseParam.getFile().getName());
        return tableInfo;
    }

    public AssetsValuationExcelInfo<AssetsValuationInfo> processDataV2(PreAssetsValuationInfo<PreAssetsValuationBase> info) {
        AssetsValuationExcelInfo<AssetsValuationInfo> avInfo = new AssetsValuationExcelInfo<>();

        AssetsValuationDetails extInfo = new AssetsValuationDetails();
        extInfo.setTailInfo(list2String(info.getFooter()));
        extInfo.setHeadInfo(list2String(info.getHeader().getHeaderList()));
        extInfo.setOriginalfile(info.getExcelOriginName());
        extInfo.setConvertedFile(info.getExcelNewName());
        extInfo.setFileUrl(info.getSavePath());
        extInfo.setTitle(info.getHeader().getTitle());
        extInfo.setType(info.getType().getId());
        extInfo.setNav(info.getHeader().getUnitNetValue());
        extInfo.setUserId(ValuationDataUtils.string2Int(info.getUserId()));
        extInfo.setValuationDate(new Date(info.getHeader().getValuationDate().getTime()));

        List<PreAssetsValuationBase> lBases = info.getData();
        ValuationDataUtils.removeUnderLine(lBases);
        List<AssetsValuationInfo> list = new ArrayList<>(lBases.size());

        //现在业务需要保存基金的总资产  总资产  资产净值  直接从解析结果获取
        Double totalMarketValue = null;
        // 总资产净值
        Double netAssetMarketValue = null;
        // 基金估值增值
        Double increment = null;
        // 资产份额
        Double assetShare = null;

        for (int i = 0; i < lBases.size(); i++) {

            PreAssetsValuationBase d = lBases.get(i);

            if (d.getSubjectCode() == null || d.getSubjectCode().isEmpty()) {
                continue;
            }

            String originalSubjectCode = d.getOriginalSubjectCode();
            if (ValuationDataUtils.checkMarketValue(originalSubjectCode)) {
                totalMarketValue = ValuationDataUtils.string2Double(d.getMarketValue());
            }
            if (ValuationDataUtils.checkAssetShare(originalSubjectCode)) {
                assetShare = ValuationDataUtils.string2Double(d.getMarketValue());
            }
            if (ValuationDataUtils.checkNetAssetsValue(originalSubjectCode)) {
                netAssetMarketValue = ValuationDataUtils.string2Double(d.getMarketValue());
                increment = ValuationDataUtils.string2Double(d.getValueAdded());
            }

            AssetsValuationInfo data = new AssetsValuationInfo();
            data.setUserId(ValuationDataUtils.string2Int(info.getUserId()));
            data.setSecuritiesAmount(ValuationDataUtils.string2Double(d.getAmount()));
            data.setHaltInfo(d.getSuspensionInfo());
            data.setIncrement(ValuationDataUtils.string2Double(d.getValueAdded()));
            data.setMarketValue(ValuationDataUtils.string2Double(d.getMarketValue()));
            data.setMarketValueRatio(ValuationDataUtils.string2Double(d.getRatioOfMarketValueToNetWorth()));
            data.setNetCost(ValuationDataUtils.string2Double(d.getCost()));
            data.setNetCostRatio(ValuationDataUtils.string2Double(d.getRatioOfCostToNetWorth()));
            data.setSubjectCode(d.getSubjectCode());
            data.setSecuritiesCode((d.getSecurtiesCode()));
            // 对于期权，如果以DR、XR开头，则剔除这两个字母
            if (data.getSubjectName() != null && (data.getSubjectName().startsWith("DR") || data.getSubjectName().startsWith("XR"))) {
                String subjectName = data.getSubjectName().replace("DR", "");
                data.setSubjectName(subjectName.replace("XR", ""));
            } else {
                data.setSubjectName(d.getSubjectName());
            }
            data.setUnitCost(ValuationDataUtils.string2Double(d.getUnitCost()));

            //寻找累计单位净值
            if (null == extInfo.getCumulativeNav()) {
                if (StringUtils.isNotEmpty(d.getSubjectCode()) && d.getSubjectCode().contains("累计单位净值")) {
                    if (StringUtils.isNotEmpty(d.getSubjectName()) && ValuationDataUtils.isNumber(d.getSubjectName())) {
                        extInfo.setCumulativeNav(Double.valueOf(d.getSubjectName()));
                    }
                }
            }

            switch (d.getType()) {
                case GeneralTemplate:
                    PreAssetsValuationGeneralTemplate general = (PreAssetsValuationGeneralTemplate) d;
                    data.setOriginalSubjectCode(general.getOriginalSubjectCode());
                    data.setMarketPrice(ValuationDataUtils.string2Double(general.getMarketPrice()));
                    data.setCurrency(general.getCurrency());
                    data.setExchangeRate(general.getExchangeRate());
                    data.setOriCurrencyCost(ValuationDataUtils.string2Double(general.getOriCurrencyCost()));
                    data.setOriCurrencyMarketValue(ValuationDataUtils.string2Double(general.getOriCurrencyMarketValue()));
                    data.setOriCurrencyValueAdded(ValuationDataUtils.string2Double(general.getOriCurrencyValueAdded()));
                    data.setSubjectLevel(general.getSubjectLevel());
                    data.setIssuer(general.getIssuer());
                    data.setVProjectCode(general.getvProjectCode());
                    data.setVProjectName(general.getvProjectName());
                    data.setCostRatioAsset(general.getCostRatioAsset());
                    data.setMvRatioAsset(general.getMvRatioAsset());
                    data.setSpotPrice(general.getSpotPrice());
                    data.setRemark(general.getRemark());
                    data.setRightsInterestsInfo(general.getRightAndInterestInfo());
                    data.setAssumingCost(general.getAssumingCost());
                    data.setHoldingState(general.getHoldingState());

                    if (data.getSubjectName() != null && ValuationDataUtils.isValid(data.getSubjectCode())) {
                        data.setSubjectCode(data.getSubjectCode().replace(".", ""));
                        if (ValuationDataUtils.hasChinese(data.getSubjectCode(), false)) {
                            String code = data.getSubjectCode().replace(data.getSubjectName(), "");
                            code = ValuationDataUtils.getSubjectCode(code);
                            if (code.length() >= 4) {
                                data.setSubjectCode(code);
                            } else {
                                data.setSecType(100);
                            }
                        }
                    }
                    break;
                default:
                    break;
            }
            list.add(data);
        }
        extInfo.setTotalMarketValue(totalMarketValue);
        extInfo.setAssetShare(assetShare);
        extInfo.setNetAssetsValue(netAssetMarketValue);
        extInfo.setIncrement(increment);
        avInfo.setExtInfo(extInfo);
        avInfo.setData(list);
        return avInfo;
    }

    private static String list2String(List<String> list) {
        if (CollUtil.isEmpty(list)) {
            return null;
        }
        return String.join("@", list);
    }
}