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();
        }
    }
}