package com.yeejoin.amos.boot.module.hygf.biz.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.map.MapBuilder;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.github.pagehelper.util.StringUtil;
import com.icbc.api.DefaultIcbcClient;
import com.icbc.api.IcbcApiException;
import com.icbc.api.IcbcConstants;
import com.icbc.api.request.JftApiPayFeewithholdQuerydetailRequestV1;
import com.icbc.api.response.JftApiPayFeewithholdQuerydetailResponseV1;
import com.yeejoin.amos.boot.biz.common.entity.BaseEntity;
import com.yeejoin.amos.boot.biz.common.excel.ExcelUtil;
import com.yeejoin.amos.boot.biz.common.utils.DateUtils;
import com.yeejoin.amos.boot.biz.common.utils.ExcelUtils;
import com.yeejoin.amos.boot.module.hygf.api.Enum.UploadStatusEnum;
import com.yeejoin.amos.boot.module.hygf.api.dto.*;
import com.yeejoin.amos.boot.module.hygf.api.entity.HistoryPeasantHousehold;
import com.yeejoin.amos.boot.module.hygf.api.entity.IcbcWithhold;
import com.yeejoin.amos.boot.module.hygf.api.entity.IcbcWithholdRecord;
import com.yeejoin.amos.boot.module.hygf.api.entity.PeasantHousehold;
import com.yeejoin.amos.boot.module.hygf.api.mapper.IcbcWithholdMapper;
import com.yeejoin.amos.boot.module.hygf.api.mapper.IcbcWithholdRecordMapper;
import com.yeejoin.amos.boot.module.hygf.api.service.IIcbcWithholdService;
import com.yeejoin.amos.boot.module.hygf.api.util.RSASignUtils;
import com.yeejoin.amos.boot.module.hygf.api.util.SFTPUtil;
import com.yeejoin.amos.boot.module.hygf.biz.vo.IcbcExcelVO;
import com.yeejoin.amos.component.feign.model.FeignClientResult;
import com.yeejoin.amos.component.robot.BadRequest;
import com.yeejoin.amos.feign.systemctl.Systemctl;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.ClassPathResource;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.multipart.MultipartFile;
import org.typroject.tyboot.core.rdbms.service.BaseService;
import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.typroject.tyboot.core.restful.utils.ResponseHelper;
import org.typroject.tyboot.core.restful.utils.ResponseModel;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URL;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.ParseException;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import static com.yeejoin.amos.boot.biz.common.utils.WordConverterUtils.fileToMultipartFile;
import static com.yeejoin.amos.boot.module.hygf.api.util.FileUtils.*;

/**
 *  聚富通代扣信息表服务实现类
 *
 * @author system_generator
 * @date 2024-12-02
 */
@Service
public class IcbcWithholdServiceImpl extends BaseService<IcbcWithholdDto,IcbcWithhold,IcbcWithholdMapper> implements IIcbcWithholdService {
    private static final Logger logger = LoggerFactory.getLogger(IcbcWithholdServiceImpl.class);
    @Value("${icbc.Withhold.projectId}")
    private String projectId;
    @Value("${icbc.Withhold.corpCis}")
    private String corpCis;
    @Value("${icbc.Withhold.partner.identification}")
    public  String partnerIdentification;
    @Value("${icbc.Withhold.outVendorId}")
    public  String outVendorId;
    @Value("${icbc.Withhold.sftpIp}")
    public  String sftpIp;
    @Value("${icbc.Withhold.sftpPort}")
    public  int sftpPort;
    @Autowired
    IcbcWithholdRecordMapper icbcWithholdRecordMapper;
    @Value("${icbc.Withhold.sftpUserName}")
    public  String sftpUserName;
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd");
    private static final AtomicInteger sequence = new AtomicInteger(0);
    private static String uploadPath = "/JftFeeWithhold/upload"; // 上传地址
    private static String downLoadPath = "/JftFeeWithhold/download"; // 下载地址
    private static String file ="";
    //private static String file ="amos-boot-system-jxiop/amos-boot-module-hygf-biz/src/main/resources/";
    private static LocalDate lastDate = LocalDate.now(); // 上次生成批次号的日期
    @Value("${urlHttp}")
    private  String urlPath;

    @Autowired
    private IcbcWithholdRecordServiceImpl icbcWithholdRecordService;

    /**
     * 分页查询
     */
    public Page<IcbcWithholdDto> queryForIcbcWithholdPage(Page<IcbcWithholdDto> page)   {
        return this.queryForPage(page, null, false);
    }

    /**
     * 列表查询 示例
     */
    public List<IcbcWithholdDto> queryForIcbcWithholdList()   {
        return this.queryForList("" , false);
    }


    /**
     * 生成文件
     *
     */

    public void fileGeneration(IcbcWithhold model) throws Exception {
        model.setUploadStatus(UploadStatusEnum.未上传.getName());
        int billNo = 0;
        int totalAmt = 0;
        //生成批次号 若已从保存生成取原有批次号
        String batchNo = StringUtil.isEmpty(model.getBatchNo())?checkBatcnNumber():model.getBatchNo();
        String loopField = "";
        //判断接口调用来源 不为空时说明是先保存后在列表按钮上生成 可直取冗余数据
        if (StringUtils.isNotEmpty(model.getIcbcRecordInfos())){
         model.setRecordDTOS(JSONArray.parseArray(model.getIcbcRecordInfos(),IcbcWithholdRecord.class));
        }
        //生成循环行数据
        for (IcbcWithholdRecord recordDTO : model.getRecordDTOS()) {
            WithholdLoopField withholdLoopField = new WithholdLoopField();
            withholdLoopField.setBillNo(++billNo);
            recordDTO.setBatchNo(batchNo);
            recordDTO.setProjectId(projectId);
            recordDTO.setContentText(model.getDesc());
            withholdLoopField.setBusiCode(recordDTO.getOutUserId());
            withholdLoopField.setBusiAcct(recordDTO.getMediumId());
            withholdLoopField.setBusiName(recordDTO.getCustName());
            withholdLoopField.setBillDate(DateUtils.getDateNowShortNumberN());
            withholdLoopField.setCaptAmount((int)(recordDTO.getPaymentAmount()*100));
            totalAmt += (int)(recordDTO.getPaymentAmount()*100);
            loopField += toEscapedString(withholdLoopField);
        }
        //生成第一行数据
        WithholdFirstLinkField firstLinkField = new WithholdFirstLinkField();
        firstLinkField.setTotalNum(model.getRecordDTOS().size());
        firstLinkField.setTotalAmt(totalAmt);
        firstLinkField.setBatchNo(batchNo);
        firstLinkField.setCorpCis(corpCis);
        firstLinkField.setProjectId(projectId);

        String firstLink = toEscapedString(firstLinkField);
        firstLink+=loopField;
        String fileName ="gxjr"+"-"+projectId+"-ENTRUST-"+batchNo;
        Path currentWorkingDir = Paths.get("");
        Path dirPath = currentWorkingDir.resolve(batchNo);
        System.out.println("Current working directory: " + System.getProperty("user.dir"));
        writeStringToFile(firstLink,dirPath.toString()+"/"+fileName+".bin");


        com.yeejoin.amos.boot.module.hygf.api.util.FileUtils.save2File(
                dirPath.toString()+"/"+fileName+".sign",
                RSASignUtils.signWithByte_New( com.yeejoin.amos.boot.module.hygf.api.util.FileUtils.toByteArray(dirPath.toString()+"/"+fileName+".bin"),
                        RSASignUtils .loadPrivateKey(file+"secretKey/pkcs8.pem")));

        //String sign = RSASignUtils.sign(firstLink, RSASignUtils.loadPrivateKey(file+"secretKey/pkcs8.pem"));
        //生成upload需上传的三个文件

        //writeStringToFile(sign,dirPath.toString()+"/"+fileName+".sign");
        String checkFile = createCheckFile(fileName,batchNo);
        writeStringToFile(checkFile,dirPath.toString()+"/"+fileName+".check");

        try (ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(dirPath.toString() + "/" + fileName + ".zip"))) {
            addFileToZip(dirPath.toString() + "/" + fileName + ".bin", fileName + ".bin", zos);
            addFileToZip(dirPath.toString() + "/" + fileName + ".sign", fileName + ".sign", zos);
            addFileToZip(dirPath.toString() + "/" + fileName + ".check", fileName + ".check", zos);

        MultipartFile multipartFile = convertZipToMultipartFile(new File(dirPath.toString() + "/" + fileName + ".zip"));
        FeignClientResult<Map<String, String>> result = Systemctl.fileStorageClient.updateCommonFile(multipartFile);
        if (result != null) {
            for (String url : result.getResult().keySet()) {
                model.setUploadFile(url);
            }
          }
        } catch (IOException e) {
            model.setUploadStatus(UploadStatusEnum.未生成.getName());
            e.printStackTrace();
            model.setIcbcRecordInfos(JSONObject.toJSONString(model.getRecordDTOS()));
            model.setBatchNo(batchNo);
            this.saveOrUpdate(model);
        } finally {
            cleanup(dirPath);
        }
        model.setIcbcRecordInfos(JSONObject.toJSONString(model.getRecordDTOS()));
        model.setBatchNo(batchNo);
        model.setUploadFileName(fileName);
        model.setProjectNumber(model.getRecordDTOS().get(0).getProjectId());
        model.getRecordDTOS().stream().forEach(e->e.setSequenceNbr(null));
        if (Objects.isNull(model.getSequenceNbr())){
            icbcWithholdRecordService.saveBatch(model.getRecordDTOS());
        }
        this.saveOrUpdate(model);

    }


    /**
     * 生成文件
     *
     */
    public void saveRecord(IcbcWithhold model) throws Exception {
        String batch = checkBatcnNumber();
        model.getRecordDTOS().forEach(e->{
            e.setSequenceNbr(null);
            e.setBatchNo(batch);
            e.setContentText(model.getDesc());
            e.setProjectId(projectId);
        });
        icbcWithholdRecordService.saveBatch(model.getRecordDTOS());
        model.setProjectNumber(projectId);
        model.setIcbcRecordInfos(JSONObject.toJSONString(model.getRecordDTOS()));
        model.setBatchNo(batch);
        model.setUploadStatus(UploadStatusEnum.未生成.getName());
        this.saveOrUpdate(model);
    }




    public String  createCheckFile(String filePath,String batchNo) throws Exception {
        long binSize = getFileSize(batchNo + "/" + filePath + ".bin");
        long signSize = getFileSize(batchNo + "/" + filePath + ".sign");

        Map<String, Object> build = MapBuilder.<String, Object>create().put("sourceFile", filePath + ".bin")
                .put("sourceFileSize", binSize).put("signFile",  filePath + ".sign").put("signFileSize", signSize).put("remark", "bin格式文件").build();
        Map<String, Object> fileInfo = MapBuilder.<String, Object>create().put("fileInfo", build).build();
        return   JSON.toJSONString(fileInfo);
    }


    public static String toEscapedString(Object obj) throws IllegalAccessException {
        StringBuilder sb = new StringBuilder();
        Class<?> clazz = obj.getClass();
        Field[] fields = clazz.getDeclaredFields();

        for (Field field : fields) {
            field.setAccessible(true); // 设置为可访问
            Object value = field.get(obj);
            if (value == null || (value instanceof String && ((String) value).isEmpty())) {
                sb.append((char) 27); // 使用 char(27) 作为分隔符
            } else {
                sb.append(value).append((char) 27); // 添加字段后跟分隔符
            }
        }
        sb.append('\r');
        sb.append('\n'); // 每条记录以回车结束
        return sb.toString();
    }


//    public  synchronized String generateBatchNumber() {
//        LocalDate currentDate = LocalDate.now();
//        // 如果是新的一天，重置序列号
//        if (!currentDate.equals(lastDate)) {
//            sequence = 0;
//            lastDate = currentDate;
//        }
//        // 生成8位日期
//        String datePart = currentDate.format(DATE_FORMATTER);
//        // 生成5位序列号
//        String sequencePart = String.format("%05d", sequence++);
//        // 拼接成完整的批次号
//        return  checkBatcnNumber(sequencePart,datePart,partnerIdentification);
//    }

    public String checkBatcnNumber()throws Exception{
        LocalDate currentDate = LocalDate.now();
        String datePart = currentDate.format(DATE_FORMATTER);
        if (!currentDate.equals(lastDate)) {
            sequence.set(0); // 重置序列号从0开始
            lastDate = currentDate;
        }
        // 定义序列号的最大值，这里设为五位数的最大值99999
        int maxSequence = 99999;

        // 设置最大尝试次数，防止无限循环
        int maxAttempts = 10000;

        // 构建批次号前缀，由日期部分和合作伙伴识别码组成
        String batchNoPrefix = datePart + partnerIdentification;

        // 使用 AtomicInteger 来管理序列号，保证线程安全
        AtomicInteger attemptCounter = new AtomicInteger(0);

        // 尝试生成唯一批次号，最多进行 maxAttempts 次尝试
        while (attemptCounter.incrementAndGet() <= maxAttempts) {
            // 获取当前序列号，并原子性地递增计数器
            int currentSequence = sequence.getAndIncrement();

            // 如果序列号超过了设定的最大值，则重置计数器
            if (currentSequence > maxSequence) {
                sequence.set(0); // 重置序列号从0开始
                currentSequence = sequence.getAndIncrement(); // 再次获取新的序列号
            }

            // 格式化序列号为固定长度的字符串，不足的部分用零填充
            String sequencePart = String.format("%05d", currentSequence);

            // 组合完整的批次号
            String batchNo = batchNoPrefix + sequencePart;

            // 查询数据库以确认该批次号是否已经被占用
            LambdaQueryWrapper<IcbcWithhold> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(IcbcWithhold::getBatchNo, batchNo);
            List<IcbcWithhold> existingRecord = this.getBaseMapper().selectList(wrapper);

            // 如果没有找到重复记录，则说明此批次号可用，直接返回
            if (CollectionUtil.isEmpty(existingRecord)) {
                return batchNo; // 当找到唯一批次号时返回
            }
        }
        // 如果经过多次尝试后仍然未能生成唯一的批次号，则抛出异常
        throw new Exception(" 如果经过多次尝试后仍然未能生成唯一的批次号");
    }


    public static void writeStringToFile(String content, String filename) throws IOException {
        createDirectoryIfNotExists(filename);
        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(filename), "GBK"))) {
            writer.write(content);
        }
    }
    /**
     * 确保文件所在的目录存在，如果不存在则创建。
     *
     * @param filename 包含完整路径的文件名
     * @throws IOException 如果创建目录失败
     */
    public static void createDirectoryIfNotExists(String filename) throws IOException {
        File file = new File(filename);
        File parentDir = file.getParentFile();
        if (parentDir != null && !parentDir.exists()) {
            if (!parentDir.mkdirs()) {
                throw new IOException("Failed to create directory: " + parentDir.getAbsolutePath());
            }
        }
    }

    public static long getFileSize(String filePath) throws Exception {
        Path path = Paths.get(filePath);
        return Files.size(path);
    }


    public  void downloadAndSaveZipFile(String fileUrl, Path savePath) {
        try (InputStream in = new URL(urlPath+"/"+fileUrl).openStream()) {
            String fileName = fileUrl.substring(fileUrl.lastIndexOf('/') + 1);
            // String fileName = savePath.toString();
            if (!fileName.toLowerCase().endsWith(".zip")) {
                throw new IllegalArgumentException("The provided URL does not point to a ZIP file.");
            }
            Path zipPath = savePath.resolve(fileName);
            FileUtils.copyInputStreamToFile(in, zipPath.toFile());
            System.out.println("ZIP file downloaded and saved to: " + zipPath);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        }
    }

   public void downLoadicbcFile(String batchNo,Long sequenceNbr,HttpServletResponse response) throws Exception {
        IcbcWithhold model = this.getBaseMapper().selectById(sequenceNbr);
        if (StringUtils.isNotEmpty(model.getReceiptFile())){
            this.downloadAndSaveZipFile(model.getReceiptFile(),batchNo,"回盘文件",response);
        }else {

            String path = new ClassPathResource(file+"secretKey/登录-gxjrid_rsa").getPath();
            SFTPUtil sftp = new SFTPUtil(sftpIp, sftpPort,
                    sftpUserName,
                    path, null);
            logger.info(String.valueOf(new StringBuffer().append("服务器地址: ")
                    .append(sftp.getHostName()).append(" 端口：")
                    .append(sftp.getPort()).append("用户名：")
                    .append(sftp.getUserName()).append("密钥文件:")
                    .append(sftp.getPriKeyFile())));
            sftp.priKeyConnect();
            Path currentWorkingDir = Paths.get("");
            Path dirPath = currentWorkingDir.resolve(batchNo);

            try {
                if (!Files.exists(dirPath)) {
                    Files.createDirectories(dirPath);
                    System.out.println("Directory created: " + dirPath);
                } else {
                    System.out.println("Directory already exists: " + dirPath);
                }
            } catch (IOException e) {
                e.printStackTrace();
                System.err.println("Failed to create directory: " + dirPath);
            }
            System.out.println("Current working directory: " + System.getProperty("user.dir"));
            if (sftp.isExist(downLoadPath)) {
                sftp.downloadAndZipFiles(downLoadPath,batchNo,dirPath.toString()+"/"+batchNo+".zip",true);
                logger.info("文件下载成功");
            }
            try {
                MultipartFile multipartFile = fileToMultipartFile(new File(dirPath.toString() + "/" + batchNo + ".zip"));
                FeignClientResult<Map<String, String>> result = Systemctl.fileStorageClient.updateCommonFile(multipartFile);
                if (result != null) {
                    for (String url : result.getResult().keySet()) {
                        model.setReceiptFile(url);
                    }
                    this.saveOrUpdate(model);
                    this.downloadAndSaveZipFile(model.getReceiptFile(),batchNo,"回盘文件",response);
                }
            } catch ( Exception e) {
                logger.error("平台上传文件失败");
                e.printStackTrace();
            } finally {
                cleanup(dirPath);
            }
        }

    }


    @Scheduled(cron = "${withholdReceiptFileCron}")
      void setReceiptFile() throws Exception {

        String path = new ClassPathResource(file+"secretKey/登录-gxjrid_rsa").getPath();
        SFTPUtil sftp = new SFTPUtil(sftpIp, sftpPort,
                sftpUserName,
                path, null);
        logger.info(String.valueOf(new StringBuffer().append("服务器地址: ")
                .append(sftp.getHostName()).append(" 端口：")
                .append(sftp.getPort()).append("用户名：")
                .append(sftp.getUserName()).append("密钥文件:")
                .append(sftp.getPriKeyFile())));
        sftp.priKeyConnect();

        //获取所有已上传但代扣状态为空的数据
        LambdaQueryWrapper<IcbcWithhold> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(IcbcWithhold::getWithholdStatus,UploadStatusEnum.成功.getName()).or().eq(IcbcWithhold::getWithholdStatus,UploadStatusEnum.失败.getName());
        wrapper.isNull(IcbcWithhold::getReceiptFile);
        List<IcbcWithhold> icbcWithholds = this.getBaseMapper().selectList(wrapper);
        boolean flag = false;
        Iterator<IcbcWithhold> iterator = icbcWithholds.iterator();
        while (iterator.hasNext()){
            IcbcWithhold model = iterator.next();
            Path currentWorkingDir = Paths.get("");
            Path dirPath = currentWorkingDir.resolve(model.getBatchNo());
            //没有下一个元素的时候关闭连接
            if (!iterator.hasNext()){
                flag = true;
            }
                try {
                if (!Files.exists(dirPath)) {
                    Files.createDirectories(dirPath);
                    System.out.println("Directory created: " + dirPath);
                } else {
                    System.out.println("Directory already exists: " + dirPath);
                }
            } catch (IOException e) {
                e.printStackTrace();
                System.err.println("Failed to create directory: " + dirPath);
            }
            System.out.println("Current working directory: " + System.getProperty("user.dir"));
            if (sftp.isExist(downLoadPath)) {
                sftp.downloadAndZipFiles(downLoadPath,model.getBatchNo(),dirPath.toString()+"/"+model.getBatchNo()+".zip",flag);
                logger.info("文件下载成功");
            }
            try {
                MultipartFile multipartFile = fileToMultipartFile(new File(dirPath.toString() + "/" + model.getBatchNo() + ".zip"));
                FeignClientResult<Map<String, String>> result = Systemctl.fileStorageClient.updateCommonFile(multipartFile);
                if (result != null) {
                    for (String url : result.getResult().keySet()) {
                        model.setReceiptFile(url);
                    }
                    this.saveOrUpdate(model);
                }
            } catch ( Exception e) {
                logger.error("平台上传文件失败");
                e.printStackTrace();
            } finally {
                cleanup(dirPath);
            }
        }



    }

    @Scheduled(cron = "${withholdStatusCron}")
    public void updateWithholdStatus() throws Exception {
        //获取所有已上传但代扣状态为空的数据
        LambdaQueryWrapper<IcbcWithhold> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(IcbcWithhold::getUploadStatus,UploadStatusEnum.已上传.getName());
        wrapper.isNull(IcbcWithhold::getWithholdStatus);
        List<IcbcWithhold> icbcWithholds = this.getBaseMapper().selectList(wrapper);
        String path = new ClassPathResource(file+"secretKey/登录-gxjrid_rsa").getPath();

        SFTPUtil sftp = new SFTPUtil(sftpIp, sftpPort,
                sftpUserName,
                path, null);
        logger.info(String.valueOf(new StringBuffer().append("服务器地址: ")
                .append(sftp.getHostName()).append(" 端口：")
                .append(sftp.getPort()).append("用户名：")
                .append(sftp.getUserName()).append("密钥文件:")
                .append(sftp.getPriKeyFile())));
        sftp.priKeyConnect();
        if (sftp.isExist(uploadPath)) {
            //修改上传失败的状态
            List<Map<String, Object>> allFailureFilesContentAsJson = sftp.getAllFailureFilesContentAsJson(uploadPath);
            allFailureFilesContentAsJson.forEach(e->{
                icbcWithholds.forEach(i->{
                    if (e.get("fileName").toString().contains(i.getBatchNo())){
                        IcbcWithhold icbcWithhold = new IcbcWithhold();
                        BeanUtil.copyProperties(e,icbcWithhold);
                        icbcWithhold.setUploadErrorDesc(JSON.toJSONString(e));
                        icbcWithhold.setUploadStatus(UploadStatusEnum.失败.getName());
                        this.saveOrUpdate(icbcWithhold);
                    }
                });
            });
            //修改上传成功的状态
            List<String> successFilesContentAsJson = sftp.getAllSuccessFilesContentAsJson(uploadPath);
            if (CollectionUtil.isNotEmpty(successFilesContentAsJson)){
                LambdaUpdateWrapper<IcbcWithhold> updateWrapper = new LambdaUpdateWrapper<>();
                updateWrapper.in(IcbcWithhold::getBatchNo,successFilesContentAsJson);
                updateWrapper.set(IcbcWithhold::getUploadStatus,UploadStatusEnum.成功.getName());
                updateWrapper.set(IcbcWithhold::getWithholdStatus,UploadStatusEnum.代扣处理中.getName());
                this.getBaseMapper().update(null,updateWrapper);
            }
        }
        this.upIcbcWithholdRecordWithholdStatus();

        if (sftp.isExist(downLoadPath)) {
            //获取回盘文件扣款状态
            List<ResultLinkField> files = sftp.getAllResultFilesContentAsJson(downLoadPath);
            if (CollectionUtil.isNotEmpty(files)){
                Map<String, List<ResultLinkField>> collect = files.stream().collect(Collectors.groupingBy(ResultLinkField::getBatchNo));
                for (String batchNo : collect.keySet()) {
                    LambdaQueryWrapper<IcbcWithhold> queryWrapper = new LambdaQueryWrapper<>();
                    queryWrapper.eq(IcbcWithhold::getBatchNo,batchNo);
                    IcbcWithhold model = this.getBaseMapper().selectOne(queryWrapper);
                    List<IcbcWithholdRecord> icbcWithholdRecords = JSONArray.parseArray(model.getIcbcRecordInfos(), IcbcWithholdRecord.class);
                    Boolean isSuccess = true;
                    List<ResultLinkField> resultLinkFields = collect.get(batchNo);
                    //若失败 组装失败农户姓名及原因
                    String err ="";
                    for (ResultLinkField resultLinkField : resultLinkFields) {
                        if (resultLinkField.getStatus().equals("1")){
                            err  = err+ icbcWithholdRecords.get(Integer.valueOf(resultLinkField.getBillNo())-1).getCustName() + ":" + resultLinkField.getErrMsg() + ";";
                            isSuccess = false;
                        }
                    }
                    LambdaUpdateWrapper<IcbcWithhold> updateWrapper = new LambdaUpdateWrapper<>();
                    if (!isSuccess) {
                        updateWrapper.set(IcbcWithhold::getPaymentErrorDesc,err);
                        updateWrapper.set(IcbcWithhold::getWithholdStatus,UploadStatusEnum.失败.getName());
                    }else {
                        updateWrapper.set(IcbcWithhold::getWithholdStatus,UploadStatusEnum.成功.getName());
                    }
                    updateWrapper.eq(IcbcWithhold::getBatchNo,batchNo);
                    this.getBaseMapper().update(null,updateWrapper);
                }
            }

        }
    }




    /**
     *
     * @param fileUrl 文件url地址
     * @param batchNo 批次号
     */
    @Async
    public  void sftpUploadAndUnzip(String fileUrl,String batchNo,String uploader ) {
        Path currentWorkingDir = Paths.get("");
        Path dirPath = currentWorkingDir.resolve(batchNo);
        try {
            String path = new ClassPathResource(file+"secretKey/登录-gxjrid_rsa").getPath();
            System.out.println("aaaaaaaaaaaaaaaa"+path);
            SFTPUtil sftp = new SFTPUtil(sftpIp, sftpPort,
                    sftpUserName,
                    path, null);
            logger.info(String.valueOf(new StringBuffer().append("服务器地址: ")
                    .append(sftp.getHostName()).append(" 端口：")
                    .append(sftp.getPort()).append("用户名：")
                    .append(sftp.getUserName()).append("密钥文件:")
                    .append(sftp.getPriKeyFile())));
            sftp.priKeyConnect();
            // 服务器文件存放路径
            downloadAndSaveZipFile(fileUrl,dirPath);
            if (sftp.isExist(uploadPath)) {
          //      sftp.uploadAndUnzip(uploadPath, "testupload.zip", dirPath.toString()+ "/" + fileUrl.substring(fileUrl.lastIndexOf('/') + 1));
                  sftp.unzipAndUpload(uploadPath, dirPath.toString()+ "/" + fileUrl.substring(fileUrl.lastIndexOf('/') + 1));
                logger.info("上传并解压成功");
                //修改代扣信息表状态
                LambdaUpdateWrapper<IcbcWithhold> updateWrapper = new LambdaUpdateWrapper<>();
                updateWrapper.eq(IcbcWithhold::getBatchNo,batchNo);
                updateWrapper.set(IcbcWithhold::getUploadStatus,UploadStatusEnum.已上传.getName());
                updateWrapper.set(IcbcWithhold::getUploader,uploader);
                updateWrapper.set(IcbcWithhold::getUploadTime,new Date());
                this.getBaseMapper().update(null,updateWrapper);
            }
        } catch (Exception e) {
            logger.error("异常信息：" + e.getMessage());
        }finally {
            cleanup(dirPath);
        }
    }
   public void removeDataById(Long sequenceNbr,String batchNo) {

       LambdaUpdateWrapper<IcbcWithhold>  icbcWithholdUp = new LambdaUpdateWrapper<>();
       icbcWithholdUp.set(BaseEntity::getIsDelete,"1");
       icbcWithholdUp.eq(BaseEntity::getSequenceNbr,sequenceNbr);
       this.getBaseMapper().update(null ,icbcWithholdUp);



       LambdaUpdateWrapper<IcbcWithholdRecord>  icbcWithholdRecordUp = new LambdaUpdateWrapper<>();
       icbcWithholdRecordUp.set(BaseEntity::getIsDelete,"1");
       icbcWithholdRecordUp.eq(IcbcWithholdRecord::getBatchNo,batchNo);
       icbcWithholdRecordService.getBaseMapper().update(null ,icbcWithholdRecordUp);

    }


    public Page<IcbcWithholdDto> queryForIcbcWithholdPage(int current, int size, String uploader, String uploadStartTime,  String uploadEndTime, String uploadStatus, String confirmator, String confirmationStartTime,  String confirmationEndTime,String desc,String withholdStatus) {
        Page<IcbcWithholdDto> page = new Page<>();
        PageHelper.startPage(current,size);
        List<IcbcWithholdDto> icbcWithholdDtos = this.getBaseMapper().queryForIcbcWithholdPage(uploader,uploadStartTime,uploadEndTime,uploadStatus,confirmator,confirmationStartTime,confirmationEndTime,desc,withholdStatus);
        PageInfo<IcbcWithholdDto> pageInfo = new PageInfo<>(icbcWithholdDtos);

        page.setSize(pageInfo.getSize());
        page.setCurrent(pageInfo.getPageNum());
        page.setTotal(pageInfo.getTotal());
        page.setRecords(icbcWithholdDtos);
        return page;
    }

    public void upIcbcWithholdRecordWithholdStatus() {

        LambdaQueryWrapper<IcbcWithholdRecord> wrapper = new LambdaQueryWrapper<>();
        wrapper.isNull(IcbcWithholdRecord::getWithholdStatus).or().eq(IcbcWithholdRecord::getWithholdStatus,"处理中");
        Set<String> batchNos = icbcWithholdRecordService.getBaseMapper().selectList(wrapper).stream()
                .map(IcbcWithholdRecord::getBatchNo)
                .collect(Collectors.toSet());;
        String APP_ID = "11000000000000028870";
        String MY_PRIVATE_KEY = "MIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQDAlcIeANdqipul3/qAIRlknSacHiFCMzLzUJisGcr9ipm3p2rir8WDsac2MxgtUt+f89SGNoWyjv4q0/QAKQQTd5U3KuGAksCJLBGVibuFe7G7YGYVezUksjvocvp8GIinCIkzq67KL2SOpGXAu3s+282rx8AxdTZA/EhXQvbORbWz5+DamyY+wS7Maa8KmIOb6WZPtiXgENZxDHAafrqW8Gt1BnVfJNf5yS5J9Wl+LcR1EPvi5iH8dSIdn1ZMVupoREjV/DvItSogVehsqTRZWuekAo2xH9YEli1UMG/l3deViRn/A4VmPPzdv0xXpn/yO+OTjWez+KmSmJfAZXBvAgMBAAECggEABRYviWFWTz8X+1qeIDI/nHO2KFt3X2tAxkJztd/8h5PYmYw3e4NPATp5Ayp4UgIlW/ExxNW91EiImBL/F939eJIpA8sDJ8U4hqb+U+fOZyksOZnDOIAHmE+I24vl588yFM9Z6F55gGeeDVJ0SZHqIG/nz8i339aLt02yj3N6V1peQugBP6L9arcD+gVu4F70whkqW+lHBK/WzX1OazqEx3Ip175jqzi9/0vO/h/kqnGGXuMi2VeuAPsa+d0a6lf/FScxqCt3t6dCkJ5DPA1MCj81dPi5ZfCM/vE0N8I6LEV+RyC7bb4YVO/PoicFjb2j8vfLvldOUrsvkNH51dZusQKBgQDpMHacOBpIuVzEFfNtS9SgA96cUV6NCSmHoQppJ/p9xKzY4DpcqoOCIBT4WCvVe6PeN5mrjzt8Q5gJWxiq0tVgIF9k8GjLmuiJ2v4Qh8aUmlRwn0T0DYiX/Y7uzX+pRlkpoKrSktlTOT9vmGwZDGDk0h2+H3hZ3YQEucfA6bhHmQKBgQDTbHrbr1WsUR9lrdLaGi/Uphpl3BXLuCnJXav3yIZOktO5V68MfPZXLP7aaNtbK3n0YHD+Uv8wpHBBSWQYjVYlGdvlniA/W1pr73GZJECFfS1BmmFC4GG3E92D5IkmrcPlUuz8XxKrlwHnfW1F0MoDvhp930vS0tS6u+WYTt9dRwKBgCeEQPVkRIACeYf5OFFTQmsDfNv8pgs8fD8xuTPsxHQ/uhLenMVLWBHbIfKb7oG0/CYSQgZitW/vfHpJZ7q7E9HAaqoOW5P1YmbKJ7fhanOQW7LiKqs5B+bJ30j0piendkCpq4kXvaBu2SMuL1NnV5wvRz8K0jhYY6DxYrp8YPAxAoGAZxWTaZ25tgTvvBHeprzx6Ur7wAJpFiU7KpVjjbLV2WW5mbro/LvJGIQ11qQdn/w4wDBtp3MsPblPimWQSnBPOlO7Zd+NdZbDJbFfv/1vACcic8Qj/AmPW0ZyUSaSwKskwqGGLx7j6Yn9QbNkHhBJDz4XiJvhSm/FjS6kKXj7a20CgYEArnEiPmL5g1Ca/qKK9ql8Q6P9gipIfGGpaFu23y0trbcFpVn8Oos/ic0Jgw4Xiz+rqvb6bW2V6lqBJ+9/kdn0i7zlZxKNpYsW2xFgi04OU3d5HkGL16Y1rpqMYYEhjFaOIn7J8P046UYR99yaybgQd18TE6oFrX8OL5uY8M4ge4Y=";
        String icbcPulicKey = "MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCMpjaWjngB4E3ATh+G1DVAmQnIpiPEFAEDqRfNGAVvvH35yDetqewKi0l7OEceTMN1C6NPym3zStvSoQayjYV+eIcZERkx31KhtFu9clZKgRTyPjdKMIth/wBtPKjL/5+PYalLdomM4ONthrPgnkN4x4R0+D4+EBpXo8gNiAFsNwIDAQAB";
        String AES_Key = "nuCVNzIxOTHZWv8YjEeYQA==";
        DefaultIcbcClient client = new DefaultIcbcClient(APP_ID,
                IcbcConstants.SIGN_TYPE_RSA2, MY_PRIVATE_KEY, IcbcConstants.CHARSET_UTF8,
                IcbcConstants.FORMAT_JSON,
                icbcPulicKey, IcbcConstants.ENCRYPT_TYPE_AES, AES_Key, null, null);
        JftApiPayFeewithholdQuerydetailRequestV1 request = new
                JftApiPayFeewithholdQuerydetailRequestV1();
        String host = "https://gw.open.icbc.com.cn";
        request.setServiceUrl(host + "/api/jft/api/pay/feewithhold/querydetail/V1");
        JftApiPayFeewithholdQuerydetailRequestV1.JftApiPayFeewithholdQuerydetailRequestV1Biz bizContent = new
                JftApiPayFeewithholdQuerydetailRequestV1.JftApiPayFeewithholdQuerydetailRequestV1Biz();
        bizContent.setAppId(APP_ID);//平台商户标识
        bizContent.setOutVendorId("gxjr");//子商户编号
        bizContent.setProjectId("PJ140014023565102203");//缴费项目编号
    //   bizContent.setBatchNo("20241216JO00400005");//批次号
        bizContent.setCorpCis("211590000183323");
   //     bizContent.setBillNo("1");//序号
        bizContent.setTrxDate(DateUtils.getDateNowShortStr());//交易日期
   //    bizContent.setBusiCode("14948822");
        bizContent.setCurPage("1");//查询页码
        request.setBizContent(bizContent);
        JftApiPayFeewithholdQuerydetailResponseV1 response;
        try {
            response = client.execute(request, System.currentTimeMillis() + "");
            if (response.isSuccess()) {
                List<JftApiPayFeewithholdQuerydetailResponseV1.TradeInfo> tradeList = response.getTradeList();
                List<JftApiPayFeewithholdQuerydetailResponseV1.TradeInfo> filterList = tradeList.stream().filter(e -> batchNos.contains(e.getBatchNo())).collect(Collectors.toList());
                filterList.forEach(e->{
                    LambdaUpdateWrapper<IcbcWithholdRecord> updateWrapper = new LambdaUpdateWrapper<>();
                    updateWrapper.eq(IcbcWithholdRecord::getBatchNo,e.getBatchNo());
                    updateWrapper.eq(IcbcWithholdRecord::getOutUserId,e.getBusiCode());
                    updateWrapper.set(IcbcWithholdRecord::getWithholdStatus,UploadStatusEnum.getNodeByCode(e.getStatus()).getName());
                    updateWrapper.set(IcbcWithholdRecord::getWithholdTime,e.getTrxTime());
                    updateWrapper.set(IcbcWithholdRecord::getPaymentTime,e.getTrxTime());
                    icbcWithholdRecordService.getBaseMapper().update(null,updateWrapper);
                });
            } else {
                System.out.println("ReturnCode:" + response.getReturnCode());
                System.out.println("ReturnMsg:" + response.getReturnMsg());
            }
        } catch (IcbcApiException e) {
            e.printStackTrace();
        }
    }



    public void exportData(HttpServletResponse response, IcbcWithhold icbcWithhold ){

        List<HygfIcbcWithholdRecordExportDTO> datas = new ArrayList<>();
        IcbcWithholdRecordDto dto = new IcbcWithholdRecordDto();
        if (StringUtils.isNotEmpty(icbcWithhold.getBatchNo())){
            dto.setBatchNo(icbcWithhold.getBatchNo());
        }
        if (StringUtils.isNotEmpty(icbcWithhold.getIcbcId())){
            dto.setAmosUserId(icbcWithhold.getIcbcId());
        }
        List<IcbcWithholdRecordDto>  dtos = icbcWithholdRecordMapper.exportData(dto);

        for (IcbcWithholdRecordDto icbcWithholdRecordDto : dtos) {
            HygfIcbcWithholdRecordExportDTO exportDTO = new HygfIcbcWithholdRecordExportDTO();
            BeanUtil.copyProperties(icbcWithholdRecordDto,exportDTO);
            exportDTO.setDesc(icbcWithhold.getDesc());
            exportDTO.setUploader(icbcWithhold.getConfirmator());
            datas.add(exportDTO);
        }

        if (CollectionUtil.isNotEmpty(datas)){
            ExcelUtil.createTemplate(response,icbcWithhold.getBatchNo()+"导出明细",icbcWithhold.getBatchNo()+"人员信息",datas,HygfIcbcWithholdRecordExportDTO.class,null,false);
        }
    }




    public void downloadAndSaveZipFile(String fileUrl,String batchNo,String type, HttpServletResponse response) throws Exception {
        Path currentWorkingDir = Paths.get("");
        Path dirPath = currentWorkingDir.resolve(batchNo);
        downloadAndSaveZipFile(fileUrl,dirPath);
        response.setContentType("application/zip");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode( batchNo +"-"+type+".zip", "UTF-8"));
        unzipAndUpload(dirPath.toString()+ "/" + fileUrl.substring(fileUrl.lastIndexOf('/') + 1),response);
    }

    public void exportIcbcExcel(IcbcExcelDto icbcExcelDto, HttpServletResponse response) {
        List<String> sequenceNbrList = icbcExcelDto.getSequenceNbrList();
        List<IcbcWithholdDto> icbcWithholdDtoList;
        //如果为空导出全部
        if(CollectionUtil.isEmpty(sequenceNbrList)){
            icbcWithholdDtoList = this.getBaseMapper().queryForIcbcWithhold(icbcExcelDto);
        }else {
            icbcWithholdDtoList = this.getBaseMapper().queryForIcbcWithholdBySequenceNbrs(sequenceNbrList);
        }
        List<IcbcExcelVO> excelVOList = new ArrayList<>();
        if(CollectionUtil.isNotEmpty(icbcWithholdDtoList)){
            Integer index=1;
            for (IcbcWithholdDto icbcWithholdDto : icbcWithholdDtoList) {
                IcbcExcelVO icbcExcelVO = BeanUtil.copyProperties(icbcWithholdDto, IcbcExcelVO.class);
                try {
                    icbcExcelVO.setConfirmationTime(DateUtils.dateFormat(icbcWithholdDto.getConfirmationTime(), DateUtils.DATE_TIME_PATTERN));
                    icbcExcelVO.setUploadTime(DateUtils.dateFormat(icbcWithholdDto.getUploadTime(), DateUtils.DATE_TIME_PATTERN));
                } catch (ParseException e) {
                    log.error(e.getMessage(),e);
                }
                icbcExcelVO.setIndex(index++);
                excelVOList.add(icbcExcelVO);
            }
        }
        try {
            ExcelUtils.exportExcelDefaultGroundColor(excelVOList, null, "批扣管理", IcbcExcelVO.class, "批扣管理" + DateUtils.dateFormat(new Date(), "YYYY-MM-dd-HH-mm") + ".xlsx", response);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            throw new BadRequest("导出失败,失败原因[" + e.getMessage() + "]");
        }
    }

}