流水导入

zhangmei

2025-02-21 75efe9739dd7600882365903bd1859b0ee7fb7ad

流水导入

 pom.xml

@@ -87,24 +87,24 @@
            </dependency>

            <!-- excel工具 -->
<!--            <dependency>-->
<!--                <groupId>org.apache.poi</groupId>-->
<!--                <artifactId>poi-ooxml</artifactId>-->
<!--                <version>${poi.version}</version>-->
<!--            </dependency>-->
           <dependency>
               <groupId>org.apache.poi</groupId>
               <artifactId>poi-ooxml</artifactId>
               <version>${poi.version}</version>
           </dependency>


<!--            <dependency>-->
<!--                <groupId>org.apache.poi</groupId>-->
<!--                <artifactId>poi</artifactId>-->
<!--                <version>${poi.version}</version>-->
<!--            </dependency>-->
           <dependency>
               <groupId>org.apache.poi</groupId>
               <artifactId>poi</artifactId>
               <version>${poi.version}</version>
           </dependency>

<!--            <dependency>-->
<!--                <groupId>org.apache.poi</groupId>-->
<!--                <artifactId>poi-ooxml-schemas</artifactId>-->
<!--                <version>${poi.version}</version>-->
<!--            </dependency>-->
           <dependency>
               <groupId>org.apache.poi</groupId>
               <artifactId>poi-ooxml-schemas</artifactId>
               <version>${poi.version}</version>
           </dependency>

            <!-- velocity代码生成使用模板 -->
            <dependency>

 ruoyi-admin/src/main/java/com/ruoyi/web/controller/api/TBankFlowController.java

@@ -1,19 +1,41 @@
package com.ruoyi.web.controller.api;


import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.ruoyi.common.basic.PageInfo;
import com.ruoyi.common.core.domain.R;
import com.ruoyi.common.utils.WebUtils;
import com.ruoyi.system.export.ContractExport;
import com.ruoyi.system.importExcel.TBankFlowImportExcel;
import com.ruoyi.system.model.TBankFlow;
import com.ruoyi.system.query.TBankFlowQuery;
import com.ruoyi.system.service.TBankFlowService;
import com.ruoyi.system.vo.TBankFlowStatisticsVo;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * <p>
@@ -28,6 +50,7 @@
public class TBankFlowController {
    @Autowired
    private TBankFlowService flowService;

    /**
     * 获取流水列表
     */
@@ -40,8 +63,99 @@

    @ApiOperation(value = "根据支付方式统计流水金额")
    @PostMapping("/getPaymentStats")
    public R<TBankFlowStatisticsVo> getPaymentStats(@RequestBody TBankFlowQuery query){
    public R<TBankFlowStatisticsVo> getPaymentStats(@RequestBody TBankFlowQuery query) {
        return R.ok(flowService.getPaymentStats(query));
    }

    @PostMapping("importBankFlow")
    public void importElectronic(@RequestBody MultipartFile file) {
        try (
                InputStream is = file.getInputStream();
        ) {
            List<TBankFlowImportExcel> failList = new Vector<>();
            EasyExcel.read(is, TBankFlowImportExcel.class, new AnalysisEventListener<TBankFlowImportExcel>() {
                private final List<TBankFlowImportExcel> list = new ArrayList<>();
                final AtomicInteger all = new AtomicInteger();

                @Override
                public void invoke(TBankFlowImportExcel data, AnalysisContext context) {
                    all.addAndGet(1);
                    boolean isok = data.validate();
                    if (!isok) {
                        failList.add(data);
                        return;
                    }
                    list.add(data);
                    int size = list.size();
                    if (size >= 100) {
                        flowService.saveImportBatch(list, failList);
                        list.clear();
                    }
                }

                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    int size = list.size();
                    if (size > 0) {
                        flowService.saveImportBatch(list, failList);
                    }
                }
            }).sheet().doRead();
            // 导出导入结果
            //     ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
            //     HttpServletResponse response = servletRequestAttributes.getResponse();
            //     response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //     response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
            //     if (failList.size()>0) {
            //         EasyExcel.write(response.getOutputStream(), TBankFlowImportExcel.class).sheet("Sheet1").doWrite(failList);
            //     } else {
            //         TBankFlowImportExcel result = new TBankFlowImportExcel();
            //         result.setResult("全部成功");
            //         failList.add(result);
            //         EasyExcel.write(response.getOutputStream(), TBankFlowImportExcel.class).sheet("Sheet1").doWrite(failList);
            //     }


            if (failList.size()==0){
                TBankFlowImportExcel result = new TBankFlowImportExcel();
                result.setResult("全部成功");
                failList.add(result);
            }
            Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), TBankFlowImportExcel.class, failList);
            HttpServletResponse response = WebUtils.response();
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            ServletOutputStream outputStream = null;
            try {
                String fileName = URLEncoder.encode("银行流水返回结果.xls", "utf-8");
                response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                response.setHeader("Pragma", "no-cache");
                response.setHeader("Cache-Control", "no-cache");
                outputStream = response.getOutputStream();
                workbook.write(outputStream);
            } catch (IOException e) {
                e.printStackTrace();
                System.err.println("银行流水返回结果导出失败");
            } finally {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }






        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("银行流水返回结果导出失败");
        }
    }
}




 ruoyi-system/pom.xml

@@ -51,6 +51,12 @@
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>
        <!--集成EasyExcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>


<!--        <dependency>-->

 ruoyi-system/src/main/java/com/ruoyi/system/importExcel/TBankFlowImportExcel.java

New file
@@ -0,0 +1,57 @@
package com.ruoyi.system.importExcel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.ruoyi.common.utils.DateUtils;
import io.swagger.annotations.ApiModel;
import lombok.Data;
import org.apache.commons.lang3.StringUtils;

import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;

/**
 * @author 64502
 */
@Data
@ApiModel(value="银行流水导入Excel")
public class TBankFlowImportExcel implements Serializable {


    @ExcelProperty("银行流水号")
    private String bankSerialNumber;

    @ExcelProperty("流水金额")
    private BigDecimal flowMoney;

    @ExcelProperty("支付时间")
    private String payTime;

    @ExcelProperty("付款人")
    private String payer;

    @ExcelProperty("导入结果")
    private String result;

    public boolean validate() {
        if (StringUtils.isEmpty(bankSerialNumber)){
            result = "银行流水号不能为空";
            return false;
        }
        if (StringUtils.isEmpty(payTime)){
            result = "支付时间不能为空";
            return false;
        }
        if (flowMoney == null){
            result = "流水金额不能为空";
            return false;
        }
        return true;
    }


}

 ruoyi-system/src/main/java/com/ruoyi/system/service/TBankFlowService.java

@@ -2,6 +2,7 @@

import com.baomidou.mybatisplus.extension.service.IService;
import com.ruoyi.common.basic.PageInfo;
import com.ruoyi.system.importExcel.TBankFlowImportExcel;
import com.ruoyi.system.model.TBankFlow;
import com.ruoyi.system.query.TBankFlowQuery;
import com.ruoyi.system.vo.TBankFlowStatisticsVo;
@@ -21,8 +22,9 @@
    PageInfo<TBankFlow> pageList(TBankFlowQuery query);
    List<TBankFlow> makeQuery(TBankFlowQuery query);
    TBankFlowStatisticsVo getPaymentStats( TBankFlowQuery query);

    List<TBankFlow> searchByBankSerialNumber(String bankSerialNumber);

    void saveImportBatch(List<TBankFlowImportExcel> list, List<TBankFlowImportExcel> failList);
    TBankFlow findUniq(String bankSerialNumber);

}

 ruoyi-system/src/main/java/com/ruoyi/system/service/TFlowManagementService.java

@@ -22,5 +22,4 @@
    TFlowManagementStatisticsVo getPaymentStats(TFlowManagementQuery req);



}

 ruoyi-system/src/main/java/com/ruoyi/system/service/impl/TBankFlowServiceImpl.java

@@ -1,16 +1,30 @@
package com.ruoyi.system.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.Query;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.common.basic.PageInfo;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.system.importExcel.TBankFlowImportExcel;
import com.ruoyi.system.mapper.TBankFlowMapper;
import com.ruoyi.system.model.TBankFlow;
import com.ruoyi.system.model.TFlowManagement;
import com.ruoyi.system.query.TBankFlowQuery;
import com.ruoyi.system.query.TFlowManagementQuery;
import com.ruoyi.system.service.TBankFlowService;
import com.ruoyi.system.service.TFlowManagementService;
import com.ruoyi.system.vo.TBankFlowStatisticsVo;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.List;

/**
@@ -23,6 +37,8 @@
 */
@Service
public class TBankFlowServiceImpl extends ServiceImpl<TBankFlowMapper, TBankFlow> implements TBankFlowService {
    @Autowired
    TFlowManagementService flowManagementService;
    @Override
    public PageInfo<TBankFlow> pageList(TBankFlowQuery query) {
        PageInfo<TBankFlow> pageInfo = new PageInfo<>(query.getPageNum(), query.getPageSize());
@@ -61,4 +77,67 @@
        queryWrapper.like(StringUtils.isNotEmpty(bankSerialNumber),TBankFlow::getBankSerialNumber,bankSerialNumber);
        return this.baseMapper.selectList(queryWrapper);
    }

    @Override
    public void saveImportBatch(List<TBankFlowImportExcel> list, List<TBankFlowImportExcel> failList) {
        for (TBankFlowImportExcel row : list) {
            try {
                TBankFlow preexist = findUniq(row.getBankSerialNumber());
                if (preexist != null){
                    row.setResult("流水号已存在"+row.getBankSerialNumber());
                    failList.add(row);
                    continue;
                }
                TBankFlow flow = new TBankFlow();
                BeanUtils.copyProperties(row,flow);
                try {
                    LocalDateTime parse = LocalDateTime.parse(row.getPayTime(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                    flow.setPayTime(parse);
                }catch (DateTimeParseException e){
                    row.setResult("支付时间格式不正确"+row.getPayTime());
                    failList.add(row);
                    continue;
                }
                TFlowManagementQuery query = new TFlowManagementQuery();
                query.setBankSerialNumber(row.getBankSerialNumber());
                List<TFlowManagement> list1 = flowManagementService.makeQuery(query);
                for (TFlowManagement tFlowManagement : list1) {
                    // 如果相同流水号金额相同 ：抵扣金额为流水金额，剩余金额为0，状态为正常
                    if (row.getFlowMoney().compareTo(tFlowManagement.getFlowMoney()) == 0){
                        flow.setDeductionMoney(tFlowManagement.getFlowMoney());
                        flow.setRemainingMoney(BigDecimal.ZERO);
                        flow.setFlowStatus(1);
                        this.baseMapper.insert(flow);
                        break;
                    }else {
                        // 流水号相同，金额不同：已抵扣金额为系统流水金额，剩余金额 = 银行流水金额-系统流水金额，状态为异常
                        flow.setDeductionMoney(tFlowManagement.getFlowMoney());
                        flow.setRemainingMoney(row.getFlowMoney().divide(tFlowManagement.getFlowMoney(),2, RoundingMode.HALF_DOWN));
                        flow.setFlowStatus(2);
                        this.baseMapper.insert(flow);
                        break;
                    }
                }
                flow.setFlowStatus(2);
                this.baseMapper.insert(flow);
            }catch (Exception e){
                log.error("导入银行流水信息异常",e);
                row.setResult("导入异常："+e.getMessage());
                failList.add(row);
            }
        }
    }

    /**
     * 根据流水号查找唯一流水
     * @param bankSerialNumber
     * @return
     */

    @Override
    public TBankFlow findUniq(String bankSerialNumber) {
        LambdaQueryWrapper<TBankFlow> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(StringUtils.isNotEmpty(bankSerialNumber),TBankFlow::getBankSerialNumber,bankSerialNumber);
        return this.baseMapper.selectOne(queryWrapper);
    }
}

 ruoyi-system/src/main/java/com/ruoyi/system/service/impl/TFlowManagementServiceImpl.java

@@ -50,4 +50,5 @@
    public TFlowManagementStatisticsVo getPaymentStats(TFlowManagementQuery query) {
        return this.baseMapper.getPaymentStats(query);
    }

}