/**
 * Copyright (c) 2018-2099, Chill Zhuang 庄骞 (bladejava@qq.com).
 * <p>
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * <p>
 * http://www.apache.org/licenses/LICENSE-2.0
 * <p>
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.xiaoniu.subsystem.business.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.xiaoniu.common.core.constant.Constants;
import com.xiaoniu.common.core.exception.ServiceException;
import com.xiaoniu.common.core.utils.BeanUtil;
import com.xiaoniu.common.core.utils.DateUtils;
import com.xiaoniu.common.core.utils.StringUtils;
import com.xiaoniu.common.core.utils.uuid.IdUtils;
import com.xiaoniu.subsystem.business.domain.*;
import com.xiaoniu.subsystem.business.domain.dto.DemandDTO;
import com.xiaoniu.subsystem.business.domain.dto.ExpenseDTO;
import com.xiaoniu.subsystem.business.domain.dto.ProfileSaveDTO;
import com.xiaoniu.subsystem.business.domain.vo.ProfileVO;
import com.xiaoniu.subsystem.business.mapper.*;
import com.xiaoniu.subsystem.business.service.IProfileService;
import com.xiaoniu.subsystem.file.controller.SysFileController;
import com.xiaoniu.subsystem.file.service.ISysFileService;
import com.xiaoniu.subsystem.file.utils.WordTableReader;
import com.xiaoniu.subsystem.strategy.FileUtil;
import com.xiaoniu.subsystem.strategy.UnZipStrategy;
import com.xiaoniu.subsystem.strategy.bean.FileObj;
import com.xiaoniu.subsystem.strategy.factory.UnZipStrategyFactory;
import com.xiaoniu.subsystem.system.utils.MysqlDataBackupUtil;
import jakarta.annotation.Resource;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.FileCopyUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.math.BigDecimal;
import java.net.URL;
import java.net.URLConnection;
import java.util.*;
import java.util.stream.Collectors;

import static com.xiaoniu.common.security.utils.SecurityUtils.getUnitId;
import static com.xiaoniu.common.security.utils.SecurityUtils.getUserId;

/**
 * 资料管理表 服务实现类
 *
 * @author sst
 * @since 2024-11-26
 */
@Slf4j
@Service
public class ProfileServiceImpl extends ServiceImpl<ProfileMapper, Profile> implements IProfileService {

    @Resource
    private ISysFileService fileService;
    @Resource
    private ProfileFileMapper profileFileMapper;
    @Resource
    private ExpenseMapper expenseMapper;
    @Resource
    private DemandMapper demandMapper;
    @Resource
    private ProjectPersonnelInfoMapper personnelInfoMapper;
    @Resource
    private BudgetMapper budgetMapper;

    /**
     * 资源映射路径 前缀
     */
    @Value("${file.prefix}")
    private String localFilePrefix;

    /**
     * 上传文件存储在本地的根路径
     */
    @Value("${file.path}")
    private String localFilePath;

    public static void main(String[] args) {
        String fileName = "结算单.docx";
        String url = "E:\\java\\共享\\资料\\结业资料\\结业考试成绩登记表.docx";
        String[] split = fileName.split("\\.");
        // int index = fileName.lastIndexOf(".");
        ProfileFile tbProfileFile = new ProfileFile();
        tbProfileFile.setFileName(split[0]);
        tbProfileFile.setType(split[1]);
        tbProfileFile.setUrl(url);
        ProfileSaveDTO expenseDTO = new ProfileSaveDTO();
        expenseDTO.setFlightName(null);
        expenseDTO.setFlightId(null);
        expenseDTO.setFlightCode(null);
        expenseDTO.setTrainingUnitId(null);
        expenseDTO.setTrainingUnitName(null);
        expenseDTO.setYear(null);
        expenseDTO.setTrainingTime(null);
        try {
            WordTableReader reader = new WordTableReader();
            List<XWPFTable> tables = reader.readTables(tbProfileFile.getUrl());
            for (XWPFTable table : tables) {
                List<XWPFTableRow> rows = table.getRows();
                for (int j = 0; j < rows.size(); j++) {
                    List<XWPFTableCell> cells = rows.get(j).getTableCells();
                    System.out.println((j) + "." + cells.toString());
                    for (int i = 0; i < cells.size(); i++) {
                        // 处理每个单元格的数据
                        String string = cells.get(i).getText();
                        System.out.println((i) + "." + string);
                    }
                }
            }
        } catch (IOException e) {
            throw new ServiceException(e.getMessage());
        }
    }

    @Override
    public IPage<ProfileVO> selectProfilePage(IPage<ProfileVO> page, ProfileVO profile) {
        return page.setRecords(baseMapper.selectProfilePage(page, profile));
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void importProFile(MultipartFile multipartFile, ProfileSaveDTO dto) {
        if (StringUtils.isBlank(dto.getType())) {
            throw new ServiceException("请选择导入类型");
        }
        if (ObjectUtils.isEmpty(multipartFile)) {
            throw new ServiceException("文件不能为空");
        }
        File file = new File(multipartFile.getOriginalFilename());
        List<String> zipTypeList = new ArrayList<>();
        zipTypeList.add("zip");
        zipTypeList.add("tar");
        zipTypeList.add("rar");
        zipTypeList.add("7z");
        String fileType = FileUtil.getFileType(multipartFile.getOriginalFilename());
        if (!zipTypeList.contains(fileType)) {
            throw new ServiceException("上传的文件格式不对，请检查");
        }
        String url = "";
        try {
            // 上传
            url = fileService.uploadFile(multipartFile);
            try (FileOutputStream fos = new FileOutputStream(file)) {
                FileCopyUtils.copy(multipartFile.getInputStream(), fos);
            }
        } catch (Exception e) {
            log.error("文件处理失败");
        }
        String fileName = file.getName();
        int index = fileName.lastIndexOf(".");
        // 获取文件大小
        String fileSize = FileUtil.formatFileSize(file.length());
        Profile profile = new Profile();
        BeanUtil.copy(dto, profile);
        profile.setProfileName(fileName.substring(0, index));
        profile.setFileSize(fileSize);
        profile.setUrl(url);
        profile.setCreateDept(getUnitId());
        profile.setCreateUser(getUserId());
        profile.setCreateTime(new Date());
        Profile one = baseMapper.selectOne(new LambdaQueryWrapper<Profile>()
                // .eq(Profile::getProfileCode, dto.getProfileCode())
                .eq(Profile::getType, dto.getType())
                .eq(Profile::getYear, dto.getYear())
                .eq(Profile::getFlightId, dto.getFlightId())
                .eq(Profile::getTrainingTime, dto.getTrainingTime()));
        if (null != one) {
            profile.setProfileId(one.getProfileId());
            profile.setUpdateTime(new Date());
            if (MysqlDataBackupUtil.MysqlPathSystem) {
                String file1 = localFilePath + one.getUrl().replaceAll(localFilePrefix, "");
                MysqlDataBackupUtil.deleteFiles(file1);
            }
        } else {
            profile.setCreateTime(new Date());
            profile.setStatus(Constants.ONE);
            profile.setIsDeleted(Constants.ZERO);
        }
        baseMapper.insertOrUpdate(profile);
        String suffix = fileName.substring(index + 1);
        UnZipStrategy unZipStrategy = UnZipStrategyFactory.generate(suffix);
        try {
            List<FileObj> analysis = unZipStrategy.analysis(new FileInputStream(fileName));
            log.info("文件数量为{}", analysis.size());
            // 处理资料
            processingData(analysis, profile);
            MysqlDataBackupUtil.deleteFiles(file.getName());
        } catch (FileNotFoundException e) {
            log.error("压缩包格式不正确！！！");
        }
    }

    /**
     * 处理资料
     *
     * @param analysis 文件
     */
    public void processingData(List<FileObj> analysis, Profile profile) {
        List<ProfileFile> tbProfileFiles = new ArrayList<>();
        for (FileObj fileObj : analysis) {
            String url = "";
            String fileName = fileObj.getDocName();
            String[] split = fileName.split("\\.");
            ProfileFile tbProfileFile = new ProfileFile();
            tbProfileFile.setProfileId(profile.getProfileId());
            tbProfileFile.setFileName(split[0]);
            tbProfileFile.setType(split[1]);
            if (StringUtils.isNotNull(fileObj.getBytes())) {
                try (InputStream inputStream = new ByteArrayInputStream(fileObj.getBytes());) {
                    // 将字节数组转换为 InputStream
                    MultipartFile multipartFile = FileUtil.inputStreamConvertMultipartFile(inputStream, IdUtils.fastSimpleUUID() + "." + fileObj.getSuffix());
                    if (Objects.requireNonNull(multipartFile.getOriginalFilename()).toLowerCase().endsWith(".doc")) {
                        multipartFile = SysFileController.convertToDocx(multipartFile);
                        tbProfileFile.setType("docx");
                    }
                    url = fileService.uploadFile(multipartFile);
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }
            }
            tbProfileFile.setUrl(url);
            tbProfileFiles.add(tbProfileFile);
        }
        if (!CollectionUtils.isEmpty(tbProfileFiles)) {
            List<ProfileFile> profileFiles = profileFileMapper.selectList(new LambdaQueryWrapper<ProfileFile>()
                    .eq(ProfileFile::getProfileId, profile.getProfileId()));
            if (CollectionUtils.isEmpty(profileFiles)) {
                profileFileMapper.insertBatch(tbProfileFiles);
                tbProfileFiles.forEach(t -> extracted(t, profile));
            } else {
                Map<String, ProfileFile> map = profileFiles.stream().collect(Collectors.toMap(ProfileFile::getFileName, e -> e));
                tbProfileFiles.forEach(t -> {
                    ProfileFile profileFile = map.get(t.getFileName());
                    if (null != profileFile) {
                        if (MysqlDataBackupUtil.MysqlPathSystem) {
                            String file1 = localFilePath + profileFile.getUrl().replaceAll(localFilePrefix, "");
                            MysqlDataBackupUtil.deleteFiles(file1);
                        }
                        t.setProfileFileId(profileFile.getProfileFileId());
                        profileFileMapper.updateById(t);
                    } else {
                        profileFileMapper.insert(t);
                    }
                    extracted(t, profile);
                });
            }
        }
    }


    private void extracted(ProfileFile tbProfileFile, Profile dto) {
        if (!StringUtils.equals(tbProfileFile.getType(), "doc")
                && !StringUtils.equals(tbProfileFile.getType(), "docx")) {
            return;
        }
        try {
            if (StringUtils.equals(dto.getType(), "1")) {
                extracted1(tbProfileFile, dto);
            }
            if (StringUtils.equals(dto.getType(), "0")) {
                extracted2(tbProfileFile, dto);
            }
        } catch (IOException e) {
            throw new ServiceException(e.getMessage());
        }
    }

    private void extracted2(ProfileFile tbProfileFile, Profile dto) throws IOException {
        List<XWPFTable> tables = getXwpfTables(tbProfileFile);
        List<ProjectPersonnelInfo> list = new ArrayList<>();
        for (XWPFTable table : tables) {
            List<XWPFTableRow> rows = table.getRows();
            for (int j = 0; j < rows.size(); j++) {
                List<XWPFTableCell> cells = rows.get(j).getTableCells();
                if (StringUtils.equals(tbProfileFile.getFileName(), "入学摸底考试成绩登记表")) {
                    list.add(extractedIntoThe(cells, j));
                }
                if (StringUtils.equals(tbProfileFile.getFileName(), "结业考试成绩登记表")) {
                    list.add(extractedEnd(cells, j));
                }
                if (StringUtils.equals(tbProfileFile.getFileName(), "入学摸底考试试卷")) {

                }
                if (StringUtils.equals(tbProfileFile.getFileName(), "结业考试试卷（理论+实操）")) {

                }
            }
        }
        if (CollectionUtils.isEmpty(list)) {
            savePersonnelInfo(dto, list);
        }
    }


    private void extracted1(ProfileFile tbProfileFile, Profile dto) throws IOException {
        List<XWPFTable> tables = getXwpfTables(tbProfileFile);
        if (StringUtils.equals(tbProfileFile.getFileName(), "结算单")) {
            ExpenseDTO expenseDTO = new ExpenseDTO();
            for (XWPFTable table : tables) {
                List<XWPFTableRow> rows = table.getRows();
                for (int j = 0; j < rows.size(); j++) {
                    List<XWPFTableCell> cells = rows.get(j).getTableCells();
                    extracted(cells, expenseDTO,j);
                }
            }
            expenseDTO.setSettleAccountUrl(tbProfileFile.getUrl());
            saveExpense(dto, expenseDTO);
        }
        if (StringUtils.equals(tbProfileFile.getFileName(), "费用明细")) {
            ExpenseDTO expenseDTO = new ExpenseDTO();
            for (XWPFTable table : tables) {
                List<XWPFTableRow> rows = table.getRows();
                for (int j = 0; j < rows.size(); j++) {
                    List<XWPFTableCell> cells = rows.get(j).getTableCells();
                    extractedDetail(cells, expenseDTO, j);
                }
            }
            expenseDTO.setDetailUrl(tbProfileFile.getUrl());
            saveExpense(dto, expenseDTO);
        }
        if (StringUtils.equals(tbProfileFile.getFileName(), "预算表")) {
            // DemandDTO demandDTO = new DemandDTO();
            // for (XWPFTable table : tables) {
            //     List<XWPFTableRow> rows = table.getRows();
            //     for (int j = 0; j < rows.size(); j++) {
            //         List<XWPFTableCell> cells = rows.get(j).getTableCells();
            //         extractedDetail(cells, expenseDTO, j);
            //     }
            // }
            // demandDTO.setDemandId(tbProfileFile.getUrl());
            // saveExpense(dto, expenseDTO);
        }
    }

    private void saveExpense(Profile dto, ExpenseDTO expenseDTO) {
        expenseDTO.setFlightName(dto.getFlightName());
        expenseDTO.setFlightId(dto.getFlightId());
        expenseDTO.setFlightCode(dto.getFlightCode());
        expenseDTO.setTrainingUnitId(dto.getTrainingUnitId());
        expenseDTO.setTrainingUnitName(dto.getTrainingUnitName());
        expenseDTO.setYear(dto.getYear());
        expenseDTO.setTrainingTime(dto.getTrainingTime());
        Budget budget = budgetMapper.selectOne(new LambdaQueryWrapper<Budget>()
                .eq(Budget::getTrainingTime, dto.getTrainingTime())
                .eq(Budget::getYear, dto.getYear())
                .eq(Budget::getFlightName, dto.getFlightName())
                .eq(Budget::getFlightCode, dto.getFlightCode())
                .eq(Budget::getFlightId, dto.getFlightId()));
        if (null != budget) {
            expenseDTO.setOrganBudgetedAmount(StringUtils.isNotBlank(budget.getBudgetedAmount()) ?
                    budget.getBudgetedAmount() : "0");
        }else {
            expenseDTO.setOrganBudgetedAmount("0");
        }
        Expense expense = expenseMapper.selectOne(new LambdaQueryWrapper<Expense>()
                .eq(Expense::getTrainingTime, dto.getTrainingTime())
                .eq(Expense::getYear, dto.getYear())
                .eq(Expense::getFlightName, dto.getFlightName())
                .eq(Expense::getFlightCode, dto.getFlightCode())
                .eq(Expense::getFlightId, dto.getFlightId()));
        if (null == expense) {
            expenseMapper.insert(expenseDTO);
        } else {
            expenseMapper.updateById(expenseDTO.setExpenseId(expense.getExpenseId()));
        }
    }

    private void savePersonnelInfo(Profile dto, List<ProjectPersonnelInfo> list) {
        for (ProjectPersonnelInfo info : list) {
            ProjectPersonnelInfo demand = personnelInfoMapper.selectOne(new LambdaQueryWrapper<ProjectPersonnelInfo>()
                    .eq(ProjectPersonnelInfo::getTrainingTime, dto.getTrainingTime())
                    .eq(ProjectPersonnelInfo::getUserName, info.getUserName())
                    .eq(ProjectPersonnelInfo::getSendingUnitName, info.getSendingUnitName())
                    .eq(ProjectPersonnelInfo::getYear, dto.getYear())
                    .eq(ProjectPersonnelInfo::getFlightName, dto.getFlightName())
                    .eq(ProjectPersonnelInfo::getFlightId, dto.getFlightId()));
            if (null != demand) {
                info.setFlightName(dto.getFlightName());
                info.setFlightId(dto.getFlightId());
                info.setTrainingUnitId(dto.getTrainingUnitId());
                info.setTrainingUnitName(dto.getTrainingUnitName());
                info.setYear(dto.getYear());
                info.setTrainingTime(dto.getTrainingTime());
                info.setUpdateTime(new Date());
                info.setTrainingTime(dto.getTrainingTime());
                info.setUserId(demand.getUserId());
            }
        }
        personnelInfoMapper.updateById(list);
    }

    private void saveDemand(Profile dto, DemandDTO demandDTO) {
        demandDTO.setFlightName(dto.getFlightName());
        demandDTO.setFlightId(dto.getFlightId());
        demandDTO.setFlightCode(dto.getFlightCode());
        demandDTO.setTrainingUnitId(dto.getTrainingUnitId());
        demandDTO.setTrainingUnitName(dto.getTrainingUnitName());
        demandDTO.setYear(dto.getYear());
        demandDTO.setTrainingTime(dto.getTrainingTime());
        Demand demand = demandMapper.selectOne(new LambdaQueryWrapper<Demand>()
                .eq(Demand::getTrainingTime, dto.getTrainingTime())
                .eq(Demand::getYear, dto.getYear())
                .eq(Demand::getFlightName, dto.getFlightName())
                .eq(Demand::getFlightCode, dto.getFlightCode())
                .eq(Demand::getFlightId, dto.getFlightId()));
        if (null == demand) {
            demandMapper.insert(demandDTO);
        } else {
            demandMapper.updateById(demandDTO.setDemandId(demand.getDemandId()));
        }
    }

    private List<XWPFTable> getXwpfTables(ProfileFile tbProfileFile) throws IOException {
        WordTableReader reader = new WordTableReader();
        List<XWPFTable> tables;
        if (MysqlDataBackupUtil.MysqlPathSystem) {
            String file = localFilePath + tbProfileFile.getUrl().replaceAll(localFilePrefix, "");
            // String file = tbProfileFile.getUrl();
            tables = reader.readTables(file);
        } else {
            URL url = new URL(tbProfileFile.getUrl());
            URLConnection con = url.openConnection();
            con.setConnectTimeout(20 * 1000);
            // 通过输入流读取文件内容
            InputStream inStream = con.getInputStream();
            tables = reader.readTables(inStream);
        }
        return tables;
    }

    private ProjectPersonnelInfo extractedIntoThe(List<XWPFTableCell> cells,int j) {
        ProjectPersonnelInfo expenseDTO = new ProjectPersonnelInfo();
        for (int i = 0; i < cells.size(); i++) {
            // 处理每个单元格的数据
            String string = cells.get(i).getText();
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, String.valueOf(j))) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text)) {
                    expenseDTO.setUserName(text);
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1)) {
                    expenseDTO.setSendingUnitName(text1);
                }
                String text2 = cells.get(i + 5).getText();
                if (StringUtils.isNotBlank(text2)) {
                    expenseDTO.setAdmissionScore(text2);
                }
            }
        }
        return expenseDTO;
    }

    private ProjectPersonnelInfo extractedEnd(List<XWPFTableCell> cells, int j) {
        ProjectPersonnelInfo expenseDTO = new ProjectPersonnelInfo();
        for (int i = 0; i < cells.size(); i++) {
            String string = cells.get(i).getText();
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, String.valueOf(j))) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text)) {
                    expenseDTO.setUserName(text);
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1)) {
                    expenseDTO.setSendingUnitName(text1);
                }
                String text2 = cells.get(i + 3).getText();
                if (StringUtils.isNotBlank(text2)) {
                    expenseDTO.setTheoreticalAchievement(text2);
                }
                String text3 = cells.get(i + 4).getText();
                if (StringUtils.isNotBlank(text3)) {
                    expenseDTO.setOverallPracticalScore(text3);
                }
                String text4 = cells.get(i + 5).getText();
                if (StringUtils.isNotBlank(text4)) {
                    expenseDTO.setTotalScore(text4);
                    BigDecimal v = BigDecimal.valueOf(Double.parseDouble(text4));
                    expenseDTO.setEvaluationOrder(v.compareTo(BigDecimal.valueOf(60)) < 0 ? "不及格" :
                            (v.compareTo(BigDecimal.valueOf(60)) >= 0 && v.compareTo(BigDecimal.valueOf(80)) < 0) ? "及格" :
                                    (v.compareTo(BigDecimal.valueOf(80)) >= 0 && v.compareTo(BigDecimal.valueOf(90)) < 0) ? "良" :
                                            v.compareTo(BigDecimal.valueOf(90)) >= 0 ? "优" : "未知");

                }
            }
        }
        return expenseDTO;
    }

    private void extracted(List<XWPFTableCell> cells, ExpenseDTO expenseDTO, int j) {
        for (int i = 0; i < cells.size(); i++) {
            // 处理每个单元格的数据
            String string = cells.get(i).getText();
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "起止时间")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "培训天数")) {
                    String[] split = text.split("-");
                    if (split[1].contains("/")) {
                        split[1] = split[1].replaceAll("/", "-");
                    }
                    if (split[1].contains("年")) {
                        split[1] = split[1].replaceAll("年", "-")
                                .replaceAll("月", "-")
                                .replaceAll("日", "-");
                    }
                    expenseDTO.setEndTime(DateUtils.dateTime(DateUtils.YYYY_MM_DD, split[1]));
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "培训天数")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "计划参训人数")) {
                    expenseDTO.setTrainingDays(text);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "计划参训人数")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "培训地点")) {
                    expenseDTO.setPlannedNumber(text);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "培训地点")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "培训机构名称")) {
                    expenseDTO.setTrainingSite(text);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "培训机构名称")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "培训机构性质")) {
                    expenseDTO.setTrainingUnitName(text);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "培训机构性质")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "实际参训人数")) {
                    expenseDTO.setTrainingUnitNature(text);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "实际参训人数")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "限额标准")) {
                    expenseDTO.setParticipantsNumber(text);
                }
            }

            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "限额标准")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "预算金额")) {
                    expenseDTO.setQuotaStandard(text);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "预算金额")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "住宿费")) {
                    // expenseDTO.setOrganBudgetedAmount(text);
                    expenseDTO.setTrainingBudgetedAmount(text);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "住宿费")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && StringUtils.equals(text, "标准间数量")) {
                    String text1 = cells.get(i + 2).getText();
                    if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "单价")) {
                        expenseDTO.setRoomsNumber(text1);
                        String text2 = cells.get(i + 3).getText();
                        if (StringUtils.isNotBlank(text2) && StringUtils.equals(text2, "单价")) {
                            String text3 = cells.get(i + 4).getText();
                            if (StringUtils.isNotBlank(text3) && !StringUtils.equals(text3, "金额")) {
                                expenseDTO.setRoomsUnitPrice(text3);
                                String text4 = cells.get(i + 5).getText();
                                if (StringUtils.isNotBlank(text4) && StringUtils.equals(text4, "金额")) {
                                    String text5 = cells.get(i + 6).getText();
                                    if (StringUtils.isNotBlank(text5) && !StringUtils.equals(text5, "伙食费")) {
                                        expenseDTO.setAccommodationCost(text5);
                                    }
                                }
                            } else if (StringUtils.isNotBlank(text3) && StringUtils.equals(text3, "金额")) {
                                String text4 = cells.get(i + 5).getText();
                                if (StringUtils.isNotBlank(text4) && !StringUtils.equals(text4, "伙食费")) {
                                    expenseDTO.setAccommodationCost(text4);
                                }
                            }
                        }
                    } else if (StringUtils.isNotBlank(text1) && StringUtils.equals(text1, "单价")) {
                        String text2 = cells.get(i + 3).getText();
                        if (StringUtils.isNotBlank(text2) && !StringUtils.equals(text2, "金额")) {
                            expenseDTO.setRoomsUnitPrice(text2);
                            String text3 = cells.get(i + 4).getText();
                            if (StringUtils.isNotBlank(text3) && StringUtils.equals(text3, "金额")) {
                                String text4 = cells.get(i + 5).getText();
                                if (StringUtils.isNotBlank(text4) && !StringUtils.equals(text4, "伙食费")) {
                                    expenseDTO.setAccommodationCost(text4);
                                }
                            }
                        } else if (StringUtils.isNotBlank(text2) && StringUtils.equals(text2, "金额")) {
                            String text3 = cells.get(i + 4).getText();
                            if (StringUtils.isNotBlank(text3) && !StringUtils.equals(text3, "伙食费")) {
                                expenseDTO.setAccommodationCost(text3);
                            }
                        }
                    }
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "伙食费")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && StringUtils.equals(text, "培训天数")) {
                    String text1 = cells.get(i + 2).getText();
                    if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "单价")) {
                        expenseDTO.setTrainingDays(text1);
                        String text2 = cells.get(i + 3).getText();
                        if (StringUtils.isNotBlank(text2) && StringUtils.equals(text2, "单价")) {
                            String text3 = cells.get(i + 4).getText();
                            if (StringUtils.isNotBlank(text3) && !StringUtils.equals(text3, "金额")) {
                                expenseDTO.setMealsUnitPrice(text3);
                                String text4 = cells.get(i + 5).getText();
                                if (StringUtils.isNotBlank(text4) && StringUtils.equals(text4, "金额")) {
                                    String text5 = cells.get(i + 6).getText();
                                    if (StringUtils.isNotBlank(text5) && !StringUtils.equals(text5, "场地费")) {
                                        expenseDTO.setBoardMoney(text5);
                                    }
                                }
                            } else if (StringUtils.isNotBlank(text3) && StringUtils.equals(text3, "金额")) {
                                String text4 = cells.get(i + 5).getText();
                                if (StringUtils.isNotBlank(text4) && !StringUtils.equals(text4, "场地费")) {
                                    expenseDTO.setBoardMoney(text4);
                                }
                            }
                        }
                    } else if (StringUtils.isNotBlank(text1) && StringUtils.equals(text1, "单价")) {
                        String text2 = cells.get(i + 3).getText();
                        if (StringUtils.isNotBlank(text2) && !StringUtils.equals(text2, "金额")) {
                            expenseDTO.setMealsUnitPrice(text2);
                            String text3 = cells.get(i + 4).getText();
                            if (StringUtils.isNotBlank(text3) && StringUtils.equals(text3, "金额")) {
                                String text4 = cells.get(i + 5).getText();
                                if (StringUtils.isNotBlank(text4) && !StringUtils.equals(text4, "场地费")) {
                                    expenseDTO.setBoardMoney(text4);
                                }
                            }
                        } else if (StringUtils.isNotBlank(text2) && StringUtils.equals(text2, "金额")) {
                            String text3 = cells.get(i + 4).getText();
                            if (StringUtils.isNotBlank(text3) && !StringUtils.equals(text3, "场地费")) {
                                expenseDTO.setBoardMoney(text3);
                            }
                        }
                    }
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "场地费")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && StringUtils.equals(text, "会议室")) {
                    String text1 = cells.get(i + 2).getText();
                    if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "单价")) {
                        expenseDTO.setConferenceRoomNumber(text1);
                        String text2 = cells.get(i + 3).getText();
                        if (StringUtils.isNotBlank(text2) && StringUtils.equals(text2, "单价")) {
                            String text3 = cells.get(i + 4).getText();
                            if (StringUtils.isNotBlank(text3) && !StringUtils.equals(text3, "金额")) {
                                expenseDTO.setConferenceRoomUnitPrice(text3);
                                String text4 = cells.get(i + 5).getText();
                                if (StringUtils.isNotBlank(text4) && StringUtils.equals(text4, "金额")) {
                                    String text5 = cells.get(i + 6).getText();
                                    if (StringUtils.isNotBlank(text5) && !StringUtils.equals(text5, "场地费")) {
                                        expenseDTO.setConferenceRoom(text5);
                                    }
                                }
                            } else if (StringUtils.isNotBlank(text3) && StringUtils.equals(text3, "金额")) {
                                String text4 = cells.get(i + 5).getText();
                                if (StringUtils.isNotBlank(text4) && !StringUtils.equals(text4, "场地费")) {
                                    expenseDTO.setConferenceRoom(text4);
                                }
                            }
                        }
                    } else if (StringUtils.isNotBlank(text1) && StringUtils.equals(text1, "单价")) {
                        String text2 = cells.get(i + 3).getText();
                        if (StringUtils.isNotBlank(text2) && !StringUtils.equals(text2, "金额")) {
                            expenseDTO.setConferenceRoomUnitPrice(text2);
                            String text3 = cells.get(i + 4).getText();
                            if (StringUtils.isNotBlank(text3) && StringUtils.equals(text3, "金额")) {
                                String text4 = cells.get(i + 5).getText();
                                if (StringUtils.isNotBlank(text4) && !StringUtils.equals(text4, "场地费")) {
                                    expenseDTO.setConferenceRoom(text4);
                                }
                            }
                        } else if (StringUtils.isNotBlank(text2) && StringUtils.equals(text2, "金额")) {
                            String text3 = cells.get(i + 4).getText();
                            if (StringUtils.isNotBlank(text3) && !StringUtils.equals(text3, "场地费")) {
                                expenseDTO.setConferenceRoom(text3);
                            }
                        }
                    }
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "生产车间")) {
                String text1 = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "单价")) {
                    expenseDTO.setWorkshopNumber(text1);
                    String text2 = cells.get(i + 2).getText();
                    if (StringUtils.isNotBlank(text2) && StringUtils.equals(text2, "单价")) {
                        String text3 = cells.get(i + 3).getText();
                        if (StringUtils.isNotBlank(text3) && !StringUtils.equals(text3, "金额")) {
                            expenseDTO.setWorkshopUnitPrice(text3);
                            String text4 = cells.get(i + 4).getText();
                            if (StringUtils.isNotBlank(text4) && StringUtils.equals(text4, "金额")) {
                                String text5 = cells.get(i + 5).getText();
                                if (StringUtils.isNotBlank(text5) && !StringUtils.equals(text5, "其他费用")) {
                                    expenseDTO.setProductionWorkshop(text5);
                                }
                            }
                        } else if (StringUtils.isNotBlank(text3) && StringUtils.equals(text3, "金额")) {
                            String text4 = cells.get(i + 4).getText();
                            if (StringUtils.isNotBlank(text4) && !StringUtils.equals(text4, "其他费用")) {
                                expenseDTO.setProductionWorkshop(text4);
                            }
                        }
                    }
                } else if (StringUtils.isNotBlank(text1) && StringUtils.equals(text1, "单价")) {
                    String text2 = cells.get(i + 2).getText();
                    if (StringUtils.isNotBlank(text2) && !StringUtils.equals(text2, "金额")) {
                        expenseDTO.setWorkshopUnitPrice(text2);
                        String text3 = cells.get(i + 3).getText();
                        if (StringUtils.isNotBlank(text3) && StringUtils.equals(text3, "金额")) {
                            String text4 = cells.get(i + 4).getText();
                            if (StringUtils.isNotBlank(text4) && !StringUtils.equals(text4, "其他费用")) {
                                expenseDTO.setProductionWorkshop(text4);
                            }
                        }
                    } else if (StringUtils.isNotBlank(text2) && StringUtils.equals(text2, "金额")) {
                        String text3 = cells.get(i + 3).getText();
                        if (StringUtils.isNotBlank(text3) && !StringUtils.equals(text3, "其他费用")) {
                            expenseDTO.setProductionWorkshop(text3);
                        }
                    }
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "其他费用")) {
                String text1 = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "金额")) {
                    String str1 = text1.replaceAll("：", ":");
                    String[] split = str1.split("2.交通费:");
                    expenseDTO.setTrainingMaterialsFee(split[0].replaceAll("1.培训资料费:", ""));
                    String str2 = split[1];
                    String[] split1 = str2.split("3.讲课费:");
                    expenseDTO.setTransportatioExpense(split1[0]);
                    String str3 = split1[1];
                    String[] split2 = str3.split("4.保障管理费:");
                    expenseDTO.setLectureFee(split2[0]);
                    expenseDTO.setSupportManagementFee(split2[1]);
                    String text2 = cells.get(i + 2).getText();
                    if (StringUtils.isNotBlank(text2) && StringUtils.equals(text2, "金额")) {
                        String text3 = cells.get(i + 3).getText();
                        if (StringUtils.isNotBlank(text3) && !StringUtils.equals(text3, "合计")) {
                            expenseDTO.setOtherExpenses(text3);
                        }
                    }
                } else if (StringUtils.isNotBlank(text1) && StringUtils.equals(text1, "金额")) {
                    String text2 = cells.get(i + 2).getText();
                    if (StringUtils.isNotBlank(text2) && !StringUtils.equals(text2, "合计")) {
                        expenseDTO.setOtherExpenses(text2);
                    }
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "小写")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "大写")) {
                    expenseDTO.setTotal(BigDecimal.valueOf(Double.parseDouble(text)));
                    expenseDTO.setSettlementAmount(text);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "说明")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text)) {
                    expenseDTO.setRemark(text);
                }
            }
        }
    }

    private void extractedDetail(List<XWPFTableCell> cells, ExpenseDTO expenseDTO, int j) {
        for (int i = 0; i < cells.size(); i++) {
            // 处理每个单元格的数据
            String string = cells.get(i).getText();
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "标准间住宿费")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "小  计")) {
                    expenseDTO.setAccommodationCostDetail(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "小  计")) {
                    expenseDTO.setAccommodationCostRemark(text1);
                }
            }
            if (2 == j && StringUtils.isNotBlank(string) && StringUtils.equals(string, "小  计")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "伙食费")) {
                    expenseDTO.setAccommodationCostDetailSubtotal(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text, "伙食费")) {
                    expenseDTO.setAccommodationCostRemarkSubtotal(text1);
                }
            }

            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "餐 费")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "小  计")) {
                    expenseDTO.setBoardMoneyDetail(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "小  计")) {
                    expenseDTO.setBoardMoneyRemark(text1);
                }
            }
            if (4 == j && StringUtils.isNotBlank(string) && StringUtils.equals(string, "小  计")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "场地费")) {
                    expenseDTO.setBoardMoneyDetailSubtotal(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text, "场地费")) {
                    expenseDTO.setBoardMoneyRemarkSubtotal(text1);
                }
            }

            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "餐 费")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "小  计")) {
                    expenseDTO.setBoardMoneyDetail(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "小  计")) {
                    expenseDTO.setBoardMoneyRemark(text1);
                }
            }
            if (4 == j && StringUtils.isNotBlank(string) && StringUtils.equals(string, "小  计")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "场地费")) {
                    expenseDTO.setBoardMoneyDetailSubtotal(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text, "场地费")) {
                    expenseDTO.setBoardMoneyRemarkSubtotal(text1);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "车  间")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "会议室")) {
                    expenseDTO.setProductionWorkshopDetail(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "会议室")) {
                    expenseDTO.setProductionWorkshopRemark(text1);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "会议室")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "小  计")) {
                    expenseDTO.setConferenceRoomDetail(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "小  计")) {
                    expenseDTO.setConferenceRoomRemark(text1);
                }
            }
            if (7 == j && StringUtils.isNotBlank(string) && StringUtils.equals(string, "小  计")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "其他费用")) {
                    expenseDTO.setVenueCostDetailSubtotal(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text, "其他费用")) {
                    expenseDTO.setVenueCostRemarkSubtotal(text1);
                }
            }

            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "课酬费")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "培训资料费")) {
                    expenseDTO.setLectureFeeDetail(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "培训资料费")) {
                    expenseDTO.setLectureFeeRemark(text1);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "培训学员证")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "培训手册")) {
                    expenseDTO.setStudentCardFeeDetail(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "培训手册")) {
                    expenseDTO.setStudentCardFeeRemark(text1);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "培训手册")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "培训资料")) {
                    expenseDTO.setManualCostDetail(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "培训资料")) {
                    expenseDTO.setManualCostRemark(text1);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "培训资料")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "交通费")) {
                    expenseDTO.setTrainingMaterialsFeeDetail(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "交通费")) {
                    expenseDTO.setTrainingMaterialsFeeRemark(text1);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "交通费")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "保障管理费")) {
                    expenseDTO.setTransportatioExpenseDetail(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "保障管理费")) {
                    expenseDTO.setTransportatioExpenseRemark(text1);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "保障管理费")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "小  计")) {
                    expenseDTO.setSupportManagementFeeDetail(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text1, "小  计")) {
                    expenseDTO.setSupportManagementFeeRemark(text1);
                }
            }
            if (14 == j && StringUtils.isNotBlank(string) && StringUtils.equals(string, "小  计")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text) && !StringUtils.equals(text, "合计")) {
                    expenseDTO.setOtherExpensesDetailSubtotal(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1) && !StringUtils.equals(text, "合计")) {
                    expenseDTO.setOtherExpensesRemarkSubtotal(text1);
                }
            }
            if (StringUtils.isNotBlank(string) && StringUtils.equals(string, "合计")) {
                String text = cells.get(i + 1).getText();
                if (StringUtils.isNotBlank(text)) {
                    expenseDTO.setTotalDetail(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                String text1 = cells.get(i + 2).getText();
                if (StringUtils.isNotBlank(text1)) {
                    expenseDTO.setTotalDetailRemark(text1);
                }
            }
        }
    }
}
