package common.service.impl; import common.model.*; import common.repository.*; import common.service.DmpIncomeService; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.joda.time.DateTime; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.util.StopWatch; import org.springframework.util.StringUtils; import org.springframework.web.multipart.MultipartFile; import util.ResultModel; import java.io.IOException; import java.io.InputStream; import java.math.BigDecimal; import java.text.DateFormat; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.*; import java.util.concurrent.CompletableFuture; import java.util.concurrent.ExecutorService; import java.util.concurrent.Executors; import java.util.stream.Collectors; import java.util.stream.Stream; @Service public class DmpIncomeServiceImpl implements DmpIncomeService { Logger logger = LoggerFactory.getLogger(DmpIncomeServiceImpl.class); @Autowired DmpIncomeRepository dmpIncomeRepository; @Autowired ContractRepository contractRepository; @Autowired TradeTypeRepsitory tradeTypeRepsitory; @Autowired SalesRepository salesRepository; @Autowired PackageBaseRepository packageBaseRepository; @Autowired BarrioCityRepository barrioCityRepository; @Autowired ContractBodyRepository contractBodyRepository; public static final Map<String, String> SHEET_NAMES = new HashMap(); private static final Map<String, String> CONTRACT_TYPE_NAME = new HashMap(); private static final Map<String, String> BUSINESS_TYPE_NAME = new HashMap(); private static final Map<String, String> SETAGREEMENT_TYPE_NAME = new HashMap(); static { SHEET_NAMES.put("tkio", "TrackingIO"); SHEET_NAMES.put("dmp", "DMP"); SHEET_NAMES.put("fake", "防作弊卫士"); SHEET_NAMES.put("adi", "ADI"); SHEET_NAMES.put("ads", "ADS"); SHEET_NAMES.put("abtest", "ABTEST"); SHEET_NAMES.put("cas", "CAS"); CONTRACT_TYPE_NAME.put("首次签约", "0"); CONTRACT_TYPE_NAME.put("续约", "1"); CONTRACT_TYPE_NAME.put("补充协议", "2"); BUSINESS_TYPE_NAME.put("VIP", "1");//业务类型 1 VIP 2 共管 BUSINESS_TYPE_NAME.put("共管", "2"); BUSINESS_TYPE_NAME.put("前置机", "3"); SETAGREEMENT_TYPE_NAME.put("普通协议", "1");//协议类型 1 普通协议 2 框架协议 SETAGREEMENT_TYPE_NAME.put("框架协议", "2"); } @Override public List<DmpIncome> listByCode(String contractCode) { List<DmpIncome> dmpIncomes = dmpIncomeRepository.findByContractCode(contractCode); return dmpIncomes; } @Override public List<DmpIncome> listByDs(String start, String end) { List<DmpIncome> dmpIncomes = dmpIncomeRepository.findByContractDs(start, end); if (dmpIncomes.isEmpty()) { return dmpIncomes; } List<String> codes = dmpIncomes.stream().map(v -> v.getContractCode()).collect(Collectors.toList()); List<Object[]> contracts = contractRepository.findByDmpContractCode(codes); Map<String, Object[]> names = contracts.stream().collect(Collectors.toMap(v -> v[0] + "", v -> v, (v1, v2) -> v1)); Map<String, String> cBodyMap = contractBodyRepository.findAllDis() .stream().collect(Collectors.toMap(ContractBody::getCode, ContractBody::getName, (v1, v2) -> v1)); for (DmpIncome dmpIncome : dmpIncomes) { Object[] nameItem = names.get(dmpIncome.getContractCode()); //my_body_name, customer_body, customer_short, business_type, agreement_type if (nameItem != null) { dmpIncome.setMyBodyName(nameItem[2] + ""); dmpIncome.setCustomerBody(nameItem[3] + ""); dmpIncome.setCustomerShort(nameItem[4] + ""); dmpIncome.setBusinessType(nameItem[5] + ""); dmpIncome.setAgreementType(nameItem[6] + ""); if (StringUtils.isEmpty(dmpIncome.getMyBodyName()) || dmpIncome.getMyBodyName().equals("null")) { dmpIncome.setMyBodyName(cBodyMap.get(nameItem[1])); } } } return dmpIncomes; } @Override public HSSFWorkbook exportIncomeList(String startDate, String endDate, String bodyCode, String serchName) { List<DmpIncome> dmpIncomes = this.listByDs(startDate, endDate); dmpIncomes = dmpIncomes.stream() .filter(v -> StringUtils.isEmpty(bodyCode) || "all".equals(bodyCode) || bodyCode.equals(v.getMyBodyName())) .filter(v -> StringUtils.isEmpty(serchName) || (v.getContractCode().contains(serchName) || v.getCustomerBody().contains(serchName) || v.getMyBodyName().contains(serchName))) .collect(Collectors.toList()); DecimalFormat df = new DecimalFormat("##,##0.00"); //创建工作薄对象 HSSFWorkbook workbook = new HSSFWorkbook();//这里也可以设置sheet的Name //创建工作表对象 HSSFSheet sheet = workbook.createSheet(); //创建工作表的行 HSSFRow row = sheet.createRow(0); //表头 String[] title = "我方签约主体,收入月份,签约方,客户简称,合同编号,业务类型,结算周期,系统结算,按月结算,税率,确认收入".split(","); for (int i = 0; i < title.length; i++) { row.createCell(i).setCellValue(title[i]); } String[] bussinesName = new String[]{"", "VIP", "共管", "前置机"}; for (int j = 0; j < dmpIncomes.size(); j++) { HSSFRow rowBody = sheet.createRow(j + 1); DmpIncome income = dmpIncomes.get(j); String bussinesTypeName = income.getBusinessType() == null ? "" : bussinesName[Integer.parseInt(income.getBusinessType())]; String[] dataItem = new String[]{ income.getMyBodyName(), income.getIncomeMonth(), income.getCustomerBody(), income.getCustomerShort(), income.getContractCode(), bussinesTypeName, income.getPeriod(), income.getSysSettlement(), income.getMonthSettlement(), income.getTaxRate(), income.getConfirmSettlement() }; for (int w = 0; w < dataItem.length; w++) { rowBody.createCell(w).setCellValue(dataItem[w]); } } return workbook; } @Override public DmpIncome update(DmpIncome dmpIncome) { if (dmpIncome.getId() == null) { return null; } DmpIncome dbItem = dmpIncomeRepository.findOne(dmpIncome.getId()); dbItem.setIncomeMonth(dmpIncome.getIncomeMonth()); dbItem.setPeriod(dmpIncome.getPeriod()); dbItem.setSysSettlement(dmpIncome.getSysSettlement()); dbItem.setMonthSettlement(dmpIncome.getMonthSettlement()); dbItem.setTaxRate(dmpIncome.getTaxRate()); dbItem.setConfirmSettlement(dmpIncome.getConfirmSettlement()); dmpIncomeRepository.save(dbItem); return dbItem; } @Override public Long delete(DmpIncome dmpIncome) { if (dmpIncome.getId() == null) { return null; } dmpIncomeRepository.delete(dmpIncome.getId()); return dmpIncome.getId(); } @Override public ResultModel uploadFile(MultipartFile file, String platform, User loginAccount) { Workbook workbook = getWorkbook(file); if (workbook == null) { return ResultModel.ERROR("获取上传文件错误"); } int rowNumber = checkSheetTitle(workbook, platform + "_income"); if (rowNumber == -1) { return ResultModel.ERROR("模板表头错误"); } else if (rowNumber <= 1) { return ResultModel.ERROR("文件为空"); } Sheet sheet = workbook.getSheetAt(0); DecimalFormat df = new DecimalFormat("##,##0.00"); StringBuffer erroMessage = new StringBuffer(); StopWatch stopWatch = new StopWatch(); stopWatch.start(); Map<String, String> incomeMap = new HashMap<>(); List<DmpIncome> dmpIncomeList = Stream.iterate(1, n -> n + 1).limit(rowNumber - 1).map(index -> { DmpIncome income = saveDmpIncomeItem(sheet, index, loginAccount); String key = income.getIncomeMonth() + income.getContractCode(); if (incomeMap.containsKey(key)) { return null; } incomeMap.put(key, "1"); return income; }).filter(in -> in != null).collect(Collectors.toList()); if (dmpIncomeList.isEmpty()) { return ResultModel.OK(); } ExecutorService executorService = Executors.newFixedThreadPool(30); final int[] indexLine = {0}; CompletableFuture[] futures = dmpIncomeList.stream().map(income -> CompletableFuture.runAsync( () -> { DmpIncome one = dmpIncomeRepository.findByCodeMonth(income.getContractCode(), income.getIncomeMonth()); if (one != null) { // 同一合同同一月份不重复保存 return; } indexLine[0] = income.getIndex(); if ("erro".equals(income.getIncomeMonth())) { erroMessage.append(income.getIndex()).append(" 行 收入月份错误").append(";\n"); } else { income.setConfirmSettlement(new BigDecimal(income.getConfirmSettlement()).setScale(2, BigDecimal.ROUND_HALF_UP).toString()); dmpIncomeRepository.save(income); } }, executorService) .exceptionally((t) -> { erroMessage.append(indexLine[0]).append(" 行 错误").append(t.getMessage()).append(";\n"); logger.error("the line " + indexLine[0], t); return null; }) ).toArray(size -> new CompletableFuture[size]); CompletableFuture.allOf(futures).join(); executorService.shutdownNow(); stopWatch.stop(); logger.info("dmp income upload {} line data use all {}s ", rowNumber - 1, stopWatch.getTotalTimeSeconds()); if (erroMessage.length() > 0) { return ResultModel.ERROR(erroMessage.toString()); } return ResultModel.OK(); } @Override public ResultModel contractUploadFile(MultipartFile file, String platform, User loginAccount) { Workbook workbook = getWorkbook(file); if (workbook == null) { return ResultModel.ERROR("获取上传文件错误"); } Sheet sheet = workbook.getSheetAt(0); String sheetName = sheet.getSheetName(); if (!sheetName.equalsIgnoreCase(SHEET_NAMES.get(platform))) { return ResultModel.ERROR("请上传对应项目模板处理的数据"); } int rowNumber = checkSheetTitle(workbook, platform); if (rowNumber == -1) { return ResultModel.ERROR("模板表头错误"); } else if (rowNumber <= 1) { return ResultModel.ERROR("文件为空"); } List<Contract> allContracts = new ArrayList<>(); Map<String, Long> tradTypeMap = tradeTypeRepsitory.findAll() .stream().collect(Collectors.toMap(TradeType::getName, TradeType::getId)); Map<String, Long> salseMap = salesRepository.findSaleByStatus(0) .stream().collect(Collectors.toMap(Sales::getName, Sales::getId)); Map<Long, String> barrioCityMap = barrioCityRepository.findAll() .stream().collect(Collectors.toMap(BarrioCity::getId, BarrioCity::getName)); Map<String, String> cBodyMap = contractBodyRepository.findAllDis() .stream().collect(Collectors.toMap(ContractBody::getName, ContractBody::getCode, (v1, v2) -> v1)); for (int i = 1; i < rowNumber; i++) { Row rowItem = sheet.getRow(i); if (rowItem == null) { break; } Contract contract = new Contract(); contract.setPlatform(platform); contract.setProduct(platform); contract.setStatus("normal"); contract.setType("main"); contract.setCreateAccount(loginAccount.getId()); contract.setCreateName(loginAccount.getName()); contract.setDs(DateTime.now().toString("yyyy-MM-dd")); String sysErro = null; try { //填充数据 fillDataByRow(contract, rowItem); } catch (Exception e) { logger.error("the line " + i, e); sysErro = e.getMessage(); } if (sysErro != null) { return erroValueTip(i, sysErro); } //校验数据有效性 if ("erro".equals(contract.getStartDate()) || "erro".equals(contract.getEndDate())) { return erroValueTip(i, "开始时间或结束时间"); } if (!tradTypeMap.containsKey(contract.getTradeName())) { return erroValueTip(i, "行业分类"); } else { contract.setTradeType(Integer.parseInt(tradTypeMap.get(contract.getTradeName()) + "")); } if (!cBodyMap.containsKey(contract.getMyBodyName())) { return erroValueTip(i, "我方签约主体"); } else { contract.setMyBodyCode(cBodyMap.get(contract.getMyBodyName())); } if (StringUtils.isEmpty(contract.getCustomerBody())) { return erroValueTip(i, "客户签约主体"); } if (!barrioCityMap.containsKey(contract.getBarrioId())) { return erroValueTip(i, "行政区域"); } else { contract.setBarrioName(barrioCityMap.get(contract.getBarrioId())); } if (!salseMap.containsKey(contract.getSaleName())) { return erroValueTip(i, "签约销售"); } else { contract.setSale(salseMap.get(contract.getSaleName())); } /*if (StringUtils.isEmpty(contract.getEmail())) { return erroValueTip(i, "客户主账号"); }*/ if (StringUtils.isEmpty(contract.getContractCode())) { return erroValueTip(i, "合同编号"); } else { String[] codeArr = contract.getContractCode().split("-"); if (codeArr.length == 4) { contract.setCodeNum(Integer.parseInt(codeArr[3])); } } if (!CONTRACT_TYPE_NAME.containsKey(contract.getContractType()) && !"续签".equals(contract.getContractType())) { return erroValueTip(i, "签约类型"); } else { contract.setContractType(CONTRACT_TYPE_NAME.get(contract.getContractType())); if ("续签".equals(contract.getContractType())) { contract.setContractType("1"); } } if (!BUSINESS_TYPE_NAME.containsKey(contract.getBusinessTypeName())) { return erroValueTip(i, "业务类型"); } else { contract.setBusinessType(Integer.parseInt(BUSINESS_TYPE_NAME.get(contract.getBusinessTypeName()))); } if (!SETAGREEMENT_TYPE_NAME.containsKey(contract.getAgreementTypeName())) { return erroValueTip(i, "协议类型"); } else { contract.setAgreementType(Integer.parseInt(SETAGREEMENT_TYPE_NAME.get(contract.getAgreementTypeName()))); } allContracts.add(contract); } if (allContracts.isEmpty()) { return ResultModel.ERROR("上传内容为空"); } //allContracts.stream().map(v->v.getRelationCode()).collect(Collectors.toList()) CompletableFuture.runAsync(() -> { StopWatch stopWatch = new StopWatch(); stopWatch.start(); ExecutorService executorService = Executors.newFixedThreadPool(35); CompletableFuture[] futures = allContracts.stream() .map(contract -> CompletableFuture.runAsync(() -> { if (!StringUtils.isEmpty(contract.getRelationCode())) { List<Long> idList = contractRepository.findIdByCode(contract.getRelationCode()); if (!idList.isEmpty()) { contract.setRelationContract(idList.get(0)); } else { contract.setRelationContract(-1L); } } else { contract.setRelationContract(-1L); } if (contract.getRelationContract() < 0) { contract.setRelationCode(null); } //删除已经重复的记录 contractRepository.deleteByCode(contract.getContractCode(), platform); contract.setCreateTime(new Date()); contractRepository.save(contract); }, executorService).exceptionally((t) -> null) ).toArray(size -> new CompletableFuture[size]); CompletableFuture.allOf(futures).join(); executorService.shutdownNow(); stopWatch.stop(); logger.info("{} contract upload {} line data use all {}s ", platform, allContracts.size(), stopWatch.getTotalTimeSeconds()); }); return ResultModel.OK(); } private int checkSheetTitle(Workbook workbook, String platform) { String sheetTitle = null; String code = "\t"; if ("dmp".equals(platform)) { //dmp 合同 sheetTitle = "我方签约主体\t客户签约主体\t客户简称\t第三方签约主体\t行政区域\t隶属集团\t行业分类\t合同开始日期\t合同结束日期\t签约销售\t客户主账号\t合同编号\t签约类型\t业务类型\t协议类型\t合同金额\t关联合同编号"; } else if ("dmp_income".equals(platform)) { //dmp 收入 sheetTitle = "合同编号\t收入月份\t结算周期\t系统结算\t按月结算\t税率\t确认收入"; } if (sheetTitle == null) { return -1; } Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); int sheLength = sheetTitle.split("\t").length; StringBuffer titleUp = new StringBuffer(); for (int i = 0; i < sheLength; i++) { if (i > 0) titleUp.append(code); titleUp.append(row.getCell(i)); } if (!titleUp.toString().equals(sheetTitle)) { return -1; } //总行数 sheLength = sheet.getLastRowNum() + 1; return sheLength; } private Workbook getWorkbook(MultipartFile excelfile) { InputStream stream = null; Workbook workbook = null; try { stream = excelfile.getInputStream(); workbook = WorkbookFactory.create(stream); } catch (Exception e) { logger.error("", e); } finally { if (stream != null) { try { stream.close(); } catch (IOException e) { logger.error("", e); } } } return workbook; } private DmpIncome saveDmpIncomeItem(Sheet sheet, int index, User user) { //logger.info(" line index {}", index); Row rowItem = sheet.getRow(index); //合同编号 收入月份 结算周期 系统结算 按月结算 税率 确认收入 DmpIncome income = new DmpIncome(); income.setIndex(index); income.setContractCode(getCellStringValue(rowItem, 0)); income.setIncomeMonth(getCellDateValue(rowItem, 1, "yyyy-MM")); income.setPeriod(getCellStringValue(rowItem, 2)); income.setSysSettlement(getCellStringValue(rowItem, 3)); income.setMonthSettlement(getCellStringValue(rowItem, 4)); income.setTaxRate(getCellStringValue(rowItem, 5)); income.setConfirmSettlement(getCellStringValue(rowItem, 6)); // 时间 上传人 income.setCreateTime(new Date()); income.setUploadUser(user.getName()); if (StringUtils.isEmpty(income.getContractCode())) { return null; } Double drate = StringUtils.isEmpty(income.getTaxRate()) ? 0 : Double.parseDouble(income.getTaxRate()); drate = new BigDecimal(drate * 100).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); income.setTaxRate(drate + "%"); if (StringUtils.isEmpty(income.getSysSettlement())) { income.setSysSettlement("0"); } return income; } private String getCellDateValue(Row row, int index) { return getCellDateValue(row, index, null); } private String getCellDateValue(Row row, int index, String fm) { Cell cell = row.getCell(index); if (cell == null) { return "erro"; } String dfmate = cell.getCellStyle().getDataFormatString(); if (!"yyyy/mm;@".equals(dfmate) && !"m/d/yy".equals(dfmate) && !"yy/m/d".equals(dfmate) && !"mm/dd/yy".equals(dfmate) && !"dd-mmm-yy".equals(dfmate) && !"yyyy/m/d".equals(dfmate) && !"yyyy/m/d;@".equals(dfmate) && !"yyyy\"年\"m\"月\";@".equals(dfmate)) { return "erro"; } if (fm == null) { fm = "yyyy-MM-dd"; } if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { // 用于转化为日期格式 Date d = cell.getDateCellValue(); DateFormat formater = new SimpleDateFormat(fm); return formater.format(d); } return cell.toString(); } private String getCellStringValue(Row row, int index) { Cell cell = row.getCell(index); return cell == null ? "" : cell.toString().trim(); } private String getCellStringValue(Row row, int index, String defaulValue) { Cell cell = row.getCell(index); return cell == null ? defaulValue : cell.toString(); } private void fillDataByRow(Contract contract, Row rowItem) { //我方签约主体 客户签约主体 客户简称 contract.setMyBodyName(getCellStringValue(rowItem, 0)); contract.setCustomerBody(getCellStringValue(rowItem, 1)); contract.setCustomerShort(getCellStringValue(rowItem, 2)); // 第三方签约主体 行政区域 隶属集团 行业分类 contract.setCustomerThird(getCellStringValue(rowItem, 3)); contract.setBarrioId(new BigDecimal(getCellStringValue(rowItem, 4, "0")).longValue()); contract.setBelongGroup(getCellStringValue(rowItem, 5)); contract.setTradeName(getCellStringValue(rowItem, 6)); // 合同开始日期 合同结束日期 签约销售 客户主账号 contract.setStartDate(getCellDateValue(rowItem, 7)); contract.setEndDate(getCellDateValue(rowItem, 8)); contract.setSaleName(getCellStringValue(rowItem, 9)); contract.setEmail(getCellStringValue(rowItem, 10)); // 合同编号 签约类型 业务类型 协议类型 合同金额 关联合同编号 contract.setContractCode(getCellStringValue(rowItem, 11)); contract.setContractType(getCellStringValue(rowItem, 12)); contract.setBusinessTypeName(getCellStringValue(rowItem, 13)); contract.setAgreementTypeName(getCellStringValue(rowItem, 14)); String money = getCellStringValue(rowItem, 15, "0"); if (money == null || StringUtils.isEmpty(money.trim())) { money = "0"; } contract.setMoney(Double.parseDouble(money)); contract.setRelationCode(getCellStringValue(rowItem, 16)); } private ResultModel erroValueTip(int line, String title) { return ResultModel.ERROR("第" + line + "行,[" + title + "]错误"); } }