package pwc.taxtech.atms.common; import java.math.BigDecimal; import java.util.Date; import org.apache.commons.io.FileUtils; import org.apache.commons.lang3.StringUtils; import org.apache.commons.lang3.math.NumberUtils; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.*; import org.junit.Test; import org.springframework.beans.factory.annotation.Autowired; import pwc.taxtech.atms.CommonIT; import pwc.taxtech.atms.entity.StandardAccount; import pwc.taxtech.atms.invoice.InputInvoiceDetailMapper; import pwc.taxtech.atms.invoice.InputInvoiceMapper; import pwc.taxtech.atms.invoice.OutputInvoiceDetailMapper; import pwc.taxtech.atms.invoice.OutputInvoiceMapper; import pwc.taxtech.atms.service.impl.DistributedIdService; import pwc.taxtech.atms.vat.entity.*; import javax.annotation.Resource; import java.io.File; import java.io.IOException; import java.nio.charset.StandardCharsets; import java.util.List; import java.util.Optional; public class DataInitTest extends CommonIT { @Resource private InputInvoiceMapper inputInvoiceMapper; @Resource private InputInvoiceDetailMapper inputInvoiceDetailMapper; @Resource private OutputInvoiceMapper outputInvoiceMapper; @Resource private OutputInvoiceDetailMapper outputInvoiceDetailMapper; @Resource private DistributedIdService distributedIdService; @Test public void insertCashFlows() { for(int i=0;i<100;i++){ CashFlow account = new CashFlow(); account.setId(distributedIdService.nextId()); account.setDate(new Date()); account.setSource("我来自哪"+i); account.setPeriod(i); account.setStatus("这都是啥"+i); account.setLedgerId("账套ID"+i); account.setLedgerName("账套名称"+i); account.setLedgerCurrencyCode("账套币种"+i); account.setEntityCode("机构编码"+i); account.setEntityName("机构名称"+i); account.setCategory("主体性质"+i); account.setFrequency("频度"+i); account.setItemName("项目中文名称"+i); account.setItemName2("项目英文名称"+i); account.setPeriodAmt(BigDecimal.valueOf(i)); account.setYtdAmt(BigDecimal.valueOf(i)); account.setCreateTime(new Date()); account.setUpdateTime(new Date()); account.setCreateBy("系统"); account.setUpdateBy("系统"); cashFlowMapper.insert(account); } } @Test public void tt() { StandardAccount account = new StandardAccount(); account.setId(CommonUtils.getUUID()); account.setCode("2221200103"); account.setName("代扣代缴增值税"); account.setFullName("应交税费-应交增值税-进项税额-代扣代缴增值税"); account.setParentCode("22212001"); account.setAcctProp(2); account.setAcctLevel(4); account.setDirection(1); account.setIsLeaf(true); account.setRuleType(2); account.setIsActive(true); account.setEnglishName("应交税费-应交增值税-进项税额-代扣代缴增值税"); account.setIndustryId("0"); standardAccountMapper.insertSelective(account); } @Test public void initStandardAccount() { try { List<String> lines = FileUtils.readLines(new File("C:\\Users\\Eddie Wu\\Desktop\\隆基科目表(最终版)_1106.csv"), StandardCharsets.UTF_8); lines.remove(0); String[] parent1 = null; String[] parent2 = null; String[] parent3 = null; String[] parent4 = null; for (String line : lines) { if (StringUtils.isBlank(line)) { continue; } String[] cols = line.split(","); String name = StringUtils.substringAfterLast(cols[7], "-"); StandardAccount account = new StandardAccount(); account.setId(CommonUtils.getUUID()); account.setCode(cols[6]); account.setName(StringUtils.isBlank(name) ? cols[7] : name); if (StringUtils.isNotBlank(cols[0])) { parent1 = cols; } else if (StringUtils.isNotBlank(cols[1])) { parent2 = cols; account.setParentCode(parent1[6]); } else if (StringUtils.isNotBlank(cols[2])) { parent3 = cols; account.setParentCode(parent2[6]); } else if (StringUtils.isNotBlank(cols[3])) { parent4 = cols; account.setParentCode(parent3[6]); } account.setFullName(cols[7]); account.setAcctProp(Integer.valueOf(cols[9])); int acctLevel = 1; if (StringUtils.isNotBlank(cols[1])) { acctLevel = 2; } else if (StringUtils.isNotBlank(cols[2])) { acctLevel = 3; } else if (StringUtils.isNotBlank(cols[3])) { acctLevel = 4; } else if (StringUtils.isNotBlank(cols[4])) { acctLevel = 5; } account.setAcctLevel(acctLevel); account.setDirection(Integer.valueOf(cols[10]) == 1 ? 1 : -1); account.setIsLeaf(acctLevel != 1); account.setRuleType(2); account.setIsActive(true); account.setEnglishName(cols[8]); account.setIndustryId("0"); standardAccountMapper.insertSelective(account); } } catch (IOException e) { e.printStackTrace(); } } @Test public void initInput() throws Exception { Workbook workbook = WorkbookFactory.create(new File("C:\\Users\\Eddie Wu\\Desktop\\导入\\进销项/导入模板_进项主表_绿能_201809.xlsx")); Sheet sheet = workbook.getSheetAt(0); for (int r = 1; r <= sheet.getLastRowNum(); r++) { try { for (int c = 0; c <= sheet.getRow(r).getLastCellNum(); c++) { Cell cell = sheet.getRow(r).getCell(c); if (null != cell) { cell.setCellType(CellType.STRING); } } InputInvoice invoice = new InputInvoice(); invoice.setID(CommonUtils.getUUID()); invoice.setFPDM(sheet.getRow(r).getCell(1).getStringCellValue()); invoice.setFPHM(sheet.getRow(r).getCell(2).getStringCellValue()); invoice.setKPRQ(sheet.getRow(r).getCell(3).getStringCellValue()); invoice.setGFSH(sheet.getRow(r).getCell(4).getStringCellValue()); invoice.setGFMC(sheet.getRow(r).getCell(5).getStringCellValue()); invoice.setXFSH(sheet.getRow(r).getCell(8).getStringCellValue()); invoice.setXFMC(sheet.getRow(r).getCell(9).getStringCellValue()); invoice.setFPLX(sheet.getRow(r).getCell(13).getStringCellValue()); invoice.setHJJE(sheet.getRow(r).getCell(16).getStringCellValue()); invoice.setHJSE(sheet.getRow(r).getCell(17).getStringCellValue()); invoice.setJSHJ(sheet.getRow(r).getCell(18).getStringCellValue()); invoice.setFPZT(sheet.getRow(r).getCell(27).getStringCellValue()); invoice.setCYZT(sheet.getRow(r).getCell(28).getStringCellValue()); invoice.setRZZT(sheet.getRow(r).getCell(29).getStringCellValue()); invoice.setRZSQ(StringUtils.replace(sheet.getRow(r).getCell(30).getStringCellValue(), "-", "")); invoice.setRZSJ(sheet.getRow(r).getCell(31).getStringCellValue()); invoice.setRZJG(sheet.getRow(r).getCell(32).getStringCellValue()); invoice.setCJSJ(sheet.getRow(r).getCell(34).getStringCellValue()); invoice.setLRRQ(sheet.getRow(r).getCell(38).getStringCellValue()); InputInvoiceExample example = new InputInvoiceExample(); example.createCriteria().andFPDMEqualTo(invoice.getFPDM()).andFPHMEqualTo(invoice.getFPHM()); if (inputInvoiceMapper.selectByExample(example).size() > 0) { inputInvoiceMapper.updateByExampleSelective(invoice, example); } else { inputInvoiceMapper.insertSelective(invoice); } } catch (Exception e) { e.printStackTrace(); } } System.out.println("end"); } @Test public void initInputDetail() throws Exception { Workbook workbook = WorkbookFactory.create(new File("C:\\Users\\Eddie Wu\\Desktop\\导入\\进销项/导入模板_进项明细_绿能_201809.xls")); Sheet sheet = workbook.getSheetAt(0); for (int r = 1; r <= sheet.getLastRowNum(); r++) { try { for (int c = 0; c <= sheet.getRow(r).getLastCellNum(); c++) { Cell cell = sheet.getRow(r).getCell(c); if (null != cell) { cell.setCellType(CellType.STRING); } } InputInvoiceDetail detail = new InputInvoiceDetail(); detail.setID(CommonUtils.getUUID()); detail.setFPDM(sheet.getRow(r).getCell(1).getStringCellValue()); detail.setFPHM(sheet.getRow(r).getCell(2).getStringCellValue()); detail.setSPMC(sheet.getRow(r).getCell(3).getStringCellValue()); detail.setDW(sheet.getRow(r).getCell(5).getStringCellValue()); detail.setDJ(sheet.getRow(r).getCell(6).getStringCellValue()); detail.setSPSL(sheet.getRow(r).getCell(7).getStringCellValue()); detail.setJE(sheet.getRow(r).getCell(8).getStringCellValue()); detail.setSLV(String.valueOf(Math.round(Double.valueOf(sheet.getRow(r).getCell(9).getStringCellValue())))); detail.setSE(sheet.getRow(r).getCell(10).getStringCellValue()); // InputInvoiceExample example = new InputInvoiceExample(); // example.createCriteria().andFPDMEqualTo(detail.getFPDM()).andFPHMEqualTo(detail.getFPHM()); // Optional<InputInvoice> invoiceOptional = inputInvoiceMapper.selectByExample(example).stream().findFirst(); // if (!invoiceOptional.isPresent()) { // continue; // } // detail.setFPID(); InputInvoiceDetailExample example = new InputInvoiceDetailExample(); example.createCriteria().andFPDMEqualTo(detail.getFPDM()).andFPHMEqualTo(detail.getFPHM()); if (inputInvoiceDetailMapper.selectByExample(example).size() > 0) { inputInvoiceDetailMapper.updateByExampleSelective(detail, example); } else { inputInvoiceDetailMapper.insertSelective(detail); } // inputInvoiceDetailMapper.insertSelective(detail); } catch (Exception e) { e.printStackTrace(); } } System.out.println("end"); } @Test public void initOutput() throws Exception { // todo 导入前检查开票日期,给的文档日期经常不一样 Workbook workbook = WorkbookFactory.create(new File("C:\\Users\\Eddie Wu\\Desktop\\导入\\进销项/导入模板_销项主表_绿能_201809.xlsx")); Sheet sheet = workbook.getSheetAt(0); for (int r = 1; r <= sheet.getLastRowNum(); r++) { try { for (int c = 0; c <= sheet.getRow(r).getLastCellNum(); c++) { Cell cell = sheet.getRow(r).getCell(c); if (null != cell) { cell.setCellType(CellType.STRING); } } OutputInvoice outputInvoice = new OutputInvoice(); outputInvoice.setID(CommonUtils.getUUID()); outputInvoice.setFPQQLSH(sheet.getRow(r).getCell(1).getStringCellValue()); outputInvoice.setFPDM(sheet.getRow(r).getCell(2).getStringCellValue()); outputInvoice.setFPHM(sheet.getRow(r).getCell(3).getStringCellValue()); // outputInvoice.setKPRQ(sheet.getRow(r).getCell(4).getStringCellValue() + " 00:00:00"); outputInvoice.setKPRQ(sheet.getRow(r).getCell(4).getStringCellValue()); String s = "000" + sheet.getRow(r).getCell(5).getStringCellValue(); outputInvoice.setFPLXDM(s.substring(s.length() - 3, s.length())); outputInvoice.setGFMC(sheet.getRow(r).getCell(9).getStringCellValue()); outputInvoice.setGFSH(sheet.getRow(r).getCell(10).getStringCellValue()); outputInvoice.setXFSH(sheet.getRow(r).getCell(13).getStringCellValue()); outputInvoice.setXFMC(sheet.getRow(r).getCell(14).getStringCellValue()); outputInvoice.setFPLX(sheet.getRow(r).getCell(23).getStringCellValue()); outputInvoice.setKPZT(sheet.getRow(r).getCell(24).getStringCellValue()); outputInvoice.setFPZT(sheet.getRow(r).getCell(25).getStringCellValue()); outputInvoice.setHJJE(sheet.getRow(r).getCell(33).getStringCellValue()); outputInvoice.setHJSE(sheet.getRow(r).getCell(34).getStringCellValue()); outputInvoice.setJSHJ(sheet.getRow(r).getCell(35).getStringCellValue()); // outputInvoice.setSLV(sheet.getRow(r).getCell(42).getStringCellValue()); // outputInvoice.setHTBH(sheet.getRow(r).getCell(54).getStringCellValue()); OutputInvoiceExample example = new OutputInvoiceExample(); example.createCriteria().andFPDMEqualTo(outputInvoice.getFPDM()).andFPHMEqualTo(outputInvoice.getFPHM()); //有重复数据 先删除 outputInvoiceMapper.deleteByExample(example); if (outputInvoiceMapper.selectByExample(example).size() > 0) { outputInvoiceMapper.updateByExampleSelective(outputInvoice, example); } else { outputInvoiceMapper.insertSelective(outputInvoice); } } catch (Exception e) { e.printStackTrace(); } } System.out.println("end"); } @Test public void initOutputDetail() throws Exception { Workbook workbook = WorkbookFactory.create(new File("C:\\Users\\Eddie Wu\\Desktop\\导入\\进销项/导入模板_销项明细_乐叶_201807.xlsx")); Sheet sheet = workbook.getSheetAt(0); String lsh = ""; for (int r = 1; r <= sheet.getLastRowNum(); r++) { try { for (int c = 0; c <= sheet.getRow(r).getLastCellNum(); c++) { Cell cell = sheet.getRow(r).getCell(c); if (null != cell) { cell.setCellType(CellType.STRING); } } OutputInvoiceDetail detail = new OutputInvoiceDetail(); detail.setID(CommonUtils.getUUID()); detail.setFPQQLSH(sheet.getRow(r).getCell(1).getStringCellValue()); detail.setSPMC(sheet.getRow(r).getCell(2).getStringCellValue()); detail.setGGXH(sheet.getRow(r).getCell(3).getStringCellValue()); detail.setDW(sheet.getRow(r).getCell(4).getStringCellValue()); detail.setDJ(sheet.getRow(r).getCell(5).getStringCellValue()); detail.setSL(sheet.getRow(r).getCell(6).getStringCellValue()); detail.setJE(sheet.getRow(r).getCell(7).getStringCellValue()); detail.setSLV(sheet.getRow(r).getCell(8).getStringCellValue()); detail.setSE(sheet.getRow(r).getCell(9).getStringCellValue()); detail.setMXXH(NumberUtils.createBigDecimal(sheet.getRow(r).getCell(15).getStringCellValue())); detail.setFPHXZ(sheet.getRow(r).getCell(16).getStringCellValue()); OutputInvoiceDetailExample example = new OutputInvoiceDetailExample(); example.createCriteria().andFPQQLSHEqualTo(detail.getFPQQLSH()); if (!StringUtils.equals(lsh, detail.getFPQQLSH())) { outputInvoiceDetailMapper.deleteByExample(example); lsh = detail.getFPQQLSH(); } outputInvoiceDetailMapper.insertSelective(detail); } catch (Exception e) { e.printStackTrace(); } } System.out.println("end"); } public static void main(String[] args) { System.out.println(CommonUtils.getUUID()); try { Workbook workbook = WorkbookFactory.create(new File("C:\\Users\\Eddie Wu\\Desktop\\导入/导入模板_销项主表_绿能_201809(1).xlsx")); Sheet sheet = workbook.getSheetAt(0); for (int r = 1; r <= sheet.getLastRowNum(); r++) { for (int c = 0; c <= sheet.getRow(r).getLastCellNum(); c++) { Cell cell = sheet.getRow(r).getCell(c); if (null != cell) { cell.setCellType(CellType.STRING); } } OutputInvoiceDetail detail = new OutputInvoiceDetail(); detail.setID(CommonUtils.getUUID()); detail.setSPMC(sheet.getRow(r).getCell(2).getStringCellValue()); detail.setDW(sheet.getRow(r).getCell(4).getStringCellValue()); detail.setDJ(sheet.getRow(r).getCell(5).getStringCellValue()); detail.setSL(sheet.getRow(r).getCell(6).getStringCellValue()); detail.setJE(sheet.getRow(r).getCell(7).getStringCellValue()); detail.setSLV(sheet.getRow(r).getCell(8).getStringCellValue()); detail.setSE(sheet.getRow(r).getCell(9).getStringCellValue()); detail.setMXXH(NumberUtils.createBigDecimal(sheet.getRow(r).getCell(10).getStringCellValue())); } sheet.getFirstRowNum(); } catch (IOException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } } }