Account checking management is a module function point, account checking is not clear, that is: pull unclear, unclear. Manual screening would be extremely painful, aided by intelligent software analysis.

Functional design drawing

About CSV file parsing

I know what you’re talking about. Right. At present, we all use reconciliation business in our work, and collect data through XXL-job scheduling. Each new project business needs to connect with the third-party payment interface, so the account reconciliation is a thorny issue, the call writing method is various, each time we have to repeat the wheel, can we package a dependency package to provide research and development use? Then, write pig-go-pay-sdk. In order to complete urgent and important tasks, pig-java-pay-SDK dependencies are configured to send interface message requests in response to a JSON download address link via the packaged boss SDK file pig-Java-pay-SDK. The downloaded file is followed by a CSV file inside a ZIP file. CSV files can be opened with Excel or notepad. The content of the CSV file is as follows:

# name :[641787296] # account period:20200316]
#-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - business detail list -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --Order Number, serial number, amount (min), currency, type, date, time5A605CB39D1C420E9FE6B04C91D923E1,PIG2020031502163665,60.01,TRADE,20200316.185936
688H660BC69B45B3ADC39D3D435411E8,PIG2020031500193460,50.01,TRADE,20200316.221847
#-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - the business end of the detail list -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --# Total transactions:0A total of business transactions:0Total refund:0Pen, merchant refund total:0Generation time :[2020- 03- 20 16:50:37]
Copy the code

Read the number of lines normally, parse the matter. However. I want to learn open source OpencSV and Hutool. I use OpencSv5.1 to read CSV files and Hutool5.3.2 tools for use.

Code thinking

Fyi, CodeReview

Rely on the import

< the dependency > < groupId > cn. Pig4cloud < / groupId > < artifactId > pig - Java - pay - SDK < / artifactId > < version > 2.6.5 < / version > </dependency> <! -- https://mvnrepository.com/artifact/cn.hutool/hutool-all --> <dependency> <groupId>cn.hutool</groupId> < artifactId > hutool -all < / artifactId > < version > 5.2.3 requires < / version > < / dependency > <! -- https://mvnrepository.com/artifact/com.opencsv/opencsv --> <dependency> <groupId>com.opencsv</groupId> < artifactId > opencsv < / artifactId > < version > 5.1 < / version > < / dependency >Copy the code

Table structure design

Design two tables, amt_pay and amt_pay_detail. Table structure, skipped here.

Unit testing

TestSettleRequest

/** ** ** ** ** ** ** ** **@author Lucky
 * @dateThe 2020-03-17 11:13:33 * /
@SpringBootTest(classes = {PigPayApplication.class})
@RunWith(SpringRunner.class)
public class TestSettleRequest {
        @Test
	public void testSettle(a) throws IOException, CsvException {
            //1 request payment interface to obtain jsonStr (ignored)
    
            //2 Verify that the parsed JSON parameter is normal (ignored)
    
    	    //3 Parsing the content at the beginning and end of a CSV file
            analysisCsvToHeadAndTail(csvFilePath);
        
             //4 Parsing CSV file details (share)
            analysisCsvToBody(csvFilePath);
	
	}

	/** * 3 Parse the start and end of the CSV file hutool **@paramCsvFileStr file full path "/app/641787296_20200316.csv" */
	private AmtPayVo analysisCsvToHeadAndTail(String csvFileStr) {
		//String csvFile = "D:\\app\\641787296_20200316.csv";
		CsvReader reader = CsvUtil.getReader();
		// Read CSV data from a file
		CsvData data = reader.read(FileUtil.file(csvFileStr));
		List<CsvRow> rows = data.getRows();
		if (rows.size() <= 0) {
			return null;
		}
		/ / traverse line
		AmtPayVo amtPayVo = new AmtPayVo();
		for (CsvRow csvRow : rows) {
			// Note the use of non-"!" In order to get
			String rowStr = csvRow.getRawList().toString();
			if(! rowStr.contains("#") || rowStr.contains("# -") || rowStr.contains("# query details")) {continue;
			}
			//getRawList returns a List, each of which is a comma-separated part of the CSV cell.
			Console.log(csvRow.getRawList());
			amtPayVo.setRemark("Reconciliation Details Enquiry");
			buildAmtPayVo(amtPayVo, csvRow.getRawList());
		}
		Console.log(amtPayVo.toString());
		return amtPayVo;
	}

	/** * 4 Parse the CSV file details opencSV **@paramCsvFileStr file path "/app/641787296_20200316.csv" *@throws IOException
	 * @throws CsvException
	 */
	private List<AmtPayDetail> analysisCsvToBody(String csvFileStr) throws IOException, CsvException {
		// Create castobaen and csvreader object
		MyCSVReader csvReader = null;
		try {
			csvReader = new MyCSVReader(new FileReader(csvFileStr));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		//The default line to start reading.
		csvReader.skip(4);

		List<AmtPayDetail> amtPayDetailList = new CsvToBeanBuilder(csvReader)
				//.withFilter(new MyCsvToBeanFilter()) // Add a filter to filter out #
				.withType(AmtPayDetail.class)
				.withIgnoreQuotations(true)
				.withThrowExceptions(false) / / 1
				//.withSkipLines(4)
				.withIgnoreLeadingWhiteSpace(true)
				.build()
				.parse();

		if (amtPayDetailList.size() <= 0) {
			Console.log("Parsing CSV file details: No statement details data for the day");
			return null;
		}
		// print details of Bean object
		for (AmtPayDetail amtPayDetail : amtPayDetailList) {
			Console.log(amtPayDetail);
		}
		returnamtPayDetailList; }}Copy the code

AmtPayDetail

/** ** reconciliation details **@author Lucky
 * @dateThe 2020-03-17 11:13:33 * /
@Data
public class AmtPayDetail {
	private static final long serialVersionUID = 1L;


	/** * Merchant number provided by the platform */
	@CsvBindByName(column = Merchant No.)
	private String merchantOrderNo;

	/** ** serial number */
	@CsvBindByName(column = "Serial number")
	private String payOrderNo;

	/** * Amount (fen) unit: fen */
	@CsvBindByName(column = "Amount (cent)")
	private String tranAmt;

	/** ** transaction currency */
	@CsvBindByName(column = "Transaction currency")
	private String currencyCode;

	/** * Transaction type */
	@CsvBindByName(column = "Transaction Type")
	private String type;

	/** * Date in the following format: yyyyMMdd */
	@CsvBindByName(column = "Date")
	private String orderDate;

	/** * The time format is HHmmss */
	@CsvBindByName(column = "Time")
	private String orderTime;
}
Copy the code

Manual: opencsv.sourceforge.net/

Documents: opencsv.sourceforge.net/apidocs/ind…