es使用教程（v7）
`package com.rpqb.business.process.service.impl;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson2.JSONObject;
import com.alibaba.fastjson2.JSONReader;
import com.rpqb.business.domain.App2LoanOrderInfo;
import com.rpqb.business.domain.LoanUser;
import com.rpqb.business.mapper.App2LoanOrderInfoMapper;
import com.rpqb.business.mapper.LoanUserMapper;
import com.rpqb.business.process.constants.ProcessConstants;
import com.rpqb.business.process.entity.ExportLoanPaymentPlan;
import com.rpqb.business.process.entity.LoanPaymentPlan;
import com.rpqb.business.process.entity.OverdueStat;
import com.rpqb.business.process.entity.RiskData;
import com.rpqb.business.process.service.IProcessActiveQueryService;
import com.rpqb.business.process.service.IProcessBusinessCreatePaymentPlanService;
import com.rpqb.common.constant.SysConstants;
import com.rpqb.common.enums.DataSourceTypeByDB;
import com.rpqb.common.utils.CommonUtil;
import com.rpqb.common.utils.DateUtils;
import com.rpqb.common.utils.SecretUtil;
import com.rpqb.common.utils.StringUtils;
import com.rpqb.common.utils.poi.ExcelUtil;
import com.rpqb.framework.datasource.DynamicDataSourceContextHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.elasticsearch.action.bulk.BulkRequest;
import org.elasticsearch.action.bulk.BulkResponse;
import org.elasticsearch.action.index.IndexRequest;
import org.elasticsearch.action.search.SearchRequest;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.client.RequestOptions;
import org.elasticsearch.client.RestHighLevelClient;
import org.elasticsearch.index.query.*;
import org.elasticsearch.index.reindex.BulkByScrollResponse;
import org.elasticsearch.index.reindex.DeleteByQueryRequest;
import org.elasticsearch.index.reindex.UpdateByQueryRequest;
import org.elasticsearch.script.Script;
import org.elasticsearch.search.SearchHit;
import org.elasticsearch.search.aggregations.Aggregation;
import org.elasticsearch.search.aggregations.AggregationBuilders;
import org.elasticsearch.search.aggregations.bucket.terms.Terms;
import org.elasticsearch.search.aggregations.bucket.terms.TermsAggregationBuilder;
import org.elasticsearch.search.aggregations.metrics.CardinalityAggregationBuilder;
import org.elasticsearch.search.aggregations.metrics.ParsedCardinality;
import org.elasticsearch.search.builder.SearchSourceBuilder;
import org.elasticsearch.search.collapse.CollapseBuilder;
import org.elasticsearch.search.sort.SortOrder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;
@Service
@Slf4j
public class ProcessActiveQueryService {
/**
* 删除es中的还款计划
* @param loanOrderInfo
*/
private void deletePaymentPlansByOrder(App2LoanOrderInfo loanOrderInfo) {
DeleteByQueryRequest deleteByQueryRequest = new DeleteByQueryRequest(REPAYMENT_PLAN_INDEX_NAME);
//构建查询条件
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
TermQueryBuilder termQueryBuilder = QueryBuilders.termQuery("order_no", loanOrderInfo.getAppOrderNo());
boolQueryBuilder.must().add(termQueryBuilder);
deleteByQueryRequest.setQuery(boolQueryBuilder);
try {
BulkByScrollResponse bulkByScrollResponse = restHighLevelClient.deleteByQuery(deleteByQueryRequest, RequestOptions.DEFAULT);
if (bulkByScrollResponse.getTotal() > 0) {
log.info("【删除ES中还款计划】成功, 订单={}", loanOrderInfo.getAppOrderNo());
}
} catch (IOException e) {
log.error("【删除ES中还款计划】失败, 订单={}, 异常={}", loanOrderInfo.getAppOrderNo(), e);
}
}
/**
* 修改es中的还款计划
* @param paymentPlanList
*/
public void updatePaymentPlans(List<LoanPaymentPlan> paymentPlanList) {
paymentPlanList.stream().forEach(a -> {
UpdateByQueryRequest updateByQueryRequest = new UpdateByQueryRequest(REPAYMENT_PLAN_INDEX_NAME);
//构建查询条件
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
TermQueryBuilder termQueryBuilder = QueryBuilders.termQuery("order_no", a.getOrderNo());
boolQueryBuilder.must().add(termQueryBuilder);
TermQueryBuilder indexNoTermQuery = QueryBuilders.termQuery("index_no", a.getIndexNo());
boolQueryBuilder.must().add(indexNoTermQuery);
updateByQueryRequest.setQuery(boolQueryBuilder);
//构建修改的字段
StringBuilder script = new StringBuilder()
.append("ctx._source['repay_amount']='").append(a.getRepayAmount())
.append("';ctx._source['repay_principal']='").append(a.getRepayPrincipal())
.append("';ctx._source['already_repay_amount']='").append(a.getAlreadyRepayAmount())
.append("';ctx._source['already_repay_principal']='").append(a.getAlreadyRepayPrincipal())
.append("';ctx._source['already_repay_interest']='").append(a.getAlreadyRepayInterest())
script.append("';ctx._source['actual_loan_give_time']='").append(a.getActualLoanGiveTime())
.append("';ctx._source['loan_give_money']='").append(a.getLoanGiveMoney())
.append("';ctx._source['office_loan_status']='").append(a.getOfficeLoanStatus())
.append("';ctx._source['update_time']='").append(a.getUpdateTime())
.append("'").append(";");
updateByQueryRequest.setScript(new Script(script.toString()));
try {
BulkByScrollResponse bulkByScrollResponse = restHighLevelClient.updateByQuery(updateByQueryRequest, RequestOptions.DEFAULT);
if (bulkByScrollResponse.getTotal() > 0) {
log.info("【修改ES中还款计划】保存成功, 订单={}, 期数={}", a.getOrderNo(), a.getIndexNo());
}
} catch (IOException e) {
log.error("【修改ES中还款计划】保存失败, 订单={}, 期数={}, 异常={}", a.getOrderNo(), a.getIndexNo(), e);
}
});
}
/**
* 保存还款计划到es
* @param paymentPlanList
*/
public boolean insertPaymentPlans(List<LoanPaymentPlan> paymentPlanList, List<LoanPaymentPlan> historyPlanList, String orderNo, String brand) {
BulkRequest bulkRequest = new BulkRequest();
List<Map<String, Object>> overdueUserRecordList = new ArrayList<>();
paymentPlanList.stream().forEach(a -> {
IndexRequest indexRequest = new IndexRequest(REPAYMENT_PLAN_INDEX_NAME);
Map<String, Object> params = new HashMap<>();
params.put("brand", brand);
params.put("uuid", a.getUuid());
params.put("loan_office_order", a.getLoanOfficeOrder());
params.put("loan_office_step", a.getLoanOfficeStep());
params.put("loan_office_status", a.getLoanOfficeStatus());
indexRequest.source(params);
bulkRequest.add(indexRequest);
});
//批量保存
try {
BulkResponse bulkResponse = restHighLevelClient.bulk(bulkRequest, RequestOptions.DEFAULT);
if (!bulkResponse.hasFailures()) {
log.info("【添加还款计划到ES】保存成功, 订单={}, 品牌={}", orderNo, brand);
return true;
} else {
for (int i = 0; i < bulkResponse.getItems().length; i++) {
log.info("【添加还款计划到ES】执行完成, 订单={}, 品牌={}, result={}", orderNo, brand, bulkResponse.getItems()[i].getFailureMessage());
}
}
} catch (IOException e) {
log.error("【添加还款计划到ES】保存失败, 订单={}, 品牌={}, 异常={}", orderNo, brand, e);
}
return false;
}
/**
* 根据订单号查询还款计划
* @param loanOrderInfo
* @return
*/
public List<LoanPaymentPlan> queryPaymentPlansFromEs(App2LoanOrderInfo loanOrderInfo) {
List<LoanPaymentPlan> planList = new ArrayList<>();
SearchRequest searchRequest = new SearchRequest();
searchRequest.indices(REPAYMENT_PLAN_INDEX_NAME);
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
//构建查询条件
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
TermQueryBuilder termQueryBuilder = QueryBuilders.termQuery("order_no", loanOrderInfo.getAppOrderNo());
boolQueryBuilder.must().add(termQueryBuilder);
searchSourceBuilder.query(boolQueryBuilder);
searchRequest.source(searchSourceBuilder);
try {
SearchResponse searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
for (SearchHit hit : searchResponse.getHits().getHits()) {
LoanPaymentPlan loanPaymentPlan = JSONObject.parseObject(hit.getSourceAsString(), LoanPaymentPlan.class, JSONReader.Feature.SupportSmartMatch);
planList.add(loanPaymentPlan);
}
} catch (IOException e) {
e.printStackTrace();
log.error("【queryPaymentPlansFromEs】根据订单号查询还款计划失败={}", e);
}
return planList;
}
/**
* 风控贷后数据排重key
*/
public static final String RISK_DATA_REPETITION_KEY = "risk_data_repetition_key";
/**
*
* @param isOverDue 是否逾期 0否 1是
* @param size 数量
*/
public String selectRiskData(String isOverDue, int size, String productKey) {
FileOutputStream fileOutputStream = null;
ExcelWriter excelWriter = null;
String today = DateUtils.getDate();
try {
String fileName = new StringBuilder("风控贷后数据_").append(SysConstants.ZERO.equals(isOverDue) ? "无逾期_" : "逾期_").append(today).append(".xlsx").toString();
fileOutputStream = new FileOutputStream(ExcelUtil.getAbsoluteFile(fileName));
excelWriter = EasyExcel.write(fileOutputStream, RiskData.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet("风控贷后数据").build();
SearchRequest searchRequest = new SearchRequest();
searchRequest.indices(REPAYMENT_PLAN_INDEX_NAME);
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
RangeQueryBuilder rangeQuery = QueryBuilders.rangeQuery("loan_give_date");
rangeQuery.lte(today);
boolQueryBuilder.must().add(rangeQuery);
if(StringUtils.isNotBlank(productKey)) {
TermQueryBuilder termQueryBuilder = QueryBuilders.termQuery("loan_office_flag", productKey);
boolQueryBuilder.must().add(termQueryBuilder);
}
searchSourceBuilder.query(boolQueryBuilder);
searchRequest.source(searchSourceBuilder);
searchSourceBuilder.sort("_id", SortOrder.ASC);
searchSourceBuilder.size(5000);
//查询数据
SearchResponse searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
SearchHit[] hits = searchResponse.getHits().getHits();
List<RiskData> resultList = new ArrayList<>();
int count = 0;
while (hits.length > 0) {
for (SearchHit hit : hits) {
LoanPaymentPlan loanPaymentPlan = JSONObject.parseObject(hit.getSourceAsString(), LoanPaymentPlan.class, JSONReader.Feature.SupportSmartMatch);
//处理过则直接跳过
if(redisTemplate.opsForHash().hasKey(RISK_DATA_REPETITION_KEY, loanPaymentPlan.getOrderNo())) {
continue;
}
//判断是否符合条件
if(!filterRiskData(loanPaymentPlan, isOverDue)) {
continue;
}
redisTemplate.opsForHash().put(RISK_DATA_REPETITION_KEY, loanPaymentPlan.getOrderNo(), SysConstants.ZERO);
redisTemplate.expire(RISK_DATA_REPETITION_KEY, 1, TimeUnit.DAYS);
log.info("【符合条件的贷后数据】是否逾期={}, orderNo={}", isOverDue, loanPaymentPlan.getOrderNo());
App2LoanOrderInfo app2LoanOrderInfo = app2LoanOrderInfoMapper.selectApp2LoanOrderInfoByAppOrderNo(loanPaymentPlan.getOrderNo());
RiskData riskData = new RiskData();
riskData.setName(SecretUtil.MD5(loanPaymentPlan.getLoanName()));
riskData.setIdCard(loanPaymentPlan.getIdCardMd5());
riskData.setMobile(loanPaymentPlan.getLoanMobileMd5());
riskData.setApplyTime(DateUtils.dateTime(app2LoanOrderInfo.getCreateTime()));
resultList.add(riskData);
count++;
if(count == size) {
hits = new SearchHit[]{};
break;
}
}
excelWriter.write(resultList, writeSheet);
if(hits.length > 0) {
//根据上次查询结果的最后一个 查询后面的数据
SearchHit lastHit = hits[hits.length - 1];
searchSourceBuilder.searchAfter(lastHit.getSortValues());
searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
hits = searchResponse.getHits().getHits();
}
resultList.clear();
}
excelWriter.finish();
fileOutputStream.flush();
redisTemplate.delete(RISK_DATA_REPETITION_KEY);
log.info("【查询贷后数据】成功，fileName={}", fileName);
return fileName;
} catch (Exception e) {
log.error("【查询贷后数据】失败={}", e);
} finally {
try {
if(fileOutputStream != null) {
fileOutputStream.close();
}
if(excelWriter != null) {
excelWriter.close();
}
} catch (IOException e) {
throw new RuntimeException(e);
}
}
return null;
}
/**
* 根据订单号查询还款计划，判断该订单是否符合条件
* @param loanPaymentPlan
* @return true 符合 false不符合
*/
private boolean filterRiskData(LoanPaymentPlan loanPaymentPlan, String isOverDue) {
boolean result = false;
SearchRequest searchRequest = new SearchRequest();
searchRequest.indices(REPAYMENT_PLAN_INDEX_NAME);
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
TermQueryBuilder orderNoQueryBuilder = QueryBuilders.termQuery("order_no", loanPaymentPlan.getOrderNo());
boolQueryBuilder.must().add(orderNoQueryBuilder);
searchSourceBuilder.query(boolQueryBuilder);
searchRequest.source(searchSourceBuilder);
searchSourceBuilder.sort("index_no", SortOrder.ASC);
//查询数据
try {
SearchResponse searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
SearchHit[] hits = searchResponse.getHits().getHits();
int count = 0;
for (SearchHit hit : hits) {
LoanPaymentPlan paymentPlan = JSONObject.parseObject(hit.getSourceAsString(), LoanPaymentPlan.class, JSONReader.Feature.SupportSmartMatch);
if(!ProcessConstants.ZERO.equals(paymentPlan.getOfficeLoanStatus())) {
count++;
}
//判断是否逾期
if(ProcessConstants.THREE.equals(paymentPlan.getOfficeLoanStatus())) {
result = true;
}
}
//还款4个月以上 并且符合条件
boolean flag = count >= 4 && (SysConstants.ZERO.equals(isOverDue) ? !result : result);
return flag;
} catch (Exception e) {
log.error("【根据订单号查询是否逾期】失败={}", e);
return false;
}
}
/**
* 查询未还款的订单 保存到中间表
* @param date
*/
public void executeQueryUnpaidOrder(String date) {
if(StringUtils.isBlank(date)) {
LocalDateTime localDateTime = LocalDateTime.now().minusDays(1);
date = localDateTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
}
String finalDate = date;
//分品牌查询数据
SysConstants.BRAND_LIST.stream().forEach(brand -> {
SearchRequest searchRequest = new SearchRequest();
searchRequest.indices(REPAYMENT_PLAN_INDEX_NAME);
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
//构建时间查询条件
RangeQueryBuilder rangeQuery = QueryBuilders.rangeQuery("loan_give_date");
rangeQuery.lte(finalDate);
boolQueryBuilder.must().add(rangeQuery);
//查询状态为0 待还款、3 已逾期、5 部分还款
TermsQueryBuilder termQueryBuilder = QueryBuilders.termsQuery("office_loan_status", SysConstants.ZERO, SysConstants.THREE, SysConstants.FIVE);
boolQueryBuilder.must().add(termQueryBuilder);
TermQueryBuilder brandTermQueryBuilder = QueryBuilders.termQuery("brand", brand);
boolQueryBuilder.must().add(brandTermQueryBuilder);
//根据订单号去重
CollapseBuilder collapseBuilder = new CollapseBuilder("order_no");
CardinalityAggregationBuilder aggregationBuilder = AggregationBuilders.cardinality("orderNo").field("order_no");
searchSourceBuilder.aggregation(aggregationBuilder);
searchSourceBuilder.query(boolQueryBuilder).collapse(collapseBuilder).size(10000).trackTotalHits(true);
searchRequest.source(searchSourceBuilder);
try {
//查询数据
SearchResponse searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
SearchHit[] hits = searchResponse.getHits().getHits();
List<String> orderNoList = new ArrayList<>();
for (SearchHit hit : hits) {
LoanPaymentPlan loanPaymentPlan = JSONObject.parseObject(hit.getSourceAsString(), LoanPaymentPlan.class, JSONReader.Feature.SupportSmartMatch);
orderNoList.add(loanPaymentPlan.getOrderNo());
}
//保存未还款订单数据
saveUnpaidOrderByBrand(orderNoList, brand, finalDate);
} catch (Exception e) {
log.error("【查询未还款订单】失败", e);
}
});
}
/**
* 保存未还款订单中间表数据
* @param orderNoList
* @param brand
*/
private void saveUnpaidOrderByBrand(List<String> orderNoList, String brand, String date) {
//清空历史未还款订单重新插入
deleteUnpaidOrderByBrand(brand, date);
if(orderNoList != null && orderNoList.size() > 0) {
BulkRequest bulkRequest = new BulkRequest();
orderNoList.stream().forEach(orderNo -> {
IndexRequest indexRequest = new IndexRequest(UNPAID_REPAYMENT_PLAN_INDEX_NAME);
Map<String, Object> params = new HashMap<>();
params.put("brand", brand);
params.put("orderNo", orderNo);
indexRequest.source(params);
bulkRequest.add(indexRequest);
});
try {
BulkResponse bulk = restHighLevelClient.bulk(bulkRequest, RequestOptions.DEFAULT);
if(!bulk.hasFailures()) {
log.info("【保存未还款订单中间表数据】成功，品牌={}, 日期={}", brand, date);
}
} catch (IOException e) {
log.error("【保存未还款订单中间表数据】失败，品牌={}, 日期={}", brand, date, e);
}
}
}
/**
* 删除未还款订单中间表数据
* @param brand
*/
private void deleteUnpaidOrderByBrand(String brand, String date) {
DeleteByQueryRequest deleteByQueryRequest = new DeleteByQueryRequest(UNPAID_REPAYMENT_PLAN_INDEX_NAME);
//构建查询条件
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
TermQueryBuilder termQueryBuilder = QueryBuilders.termQuery("brand", brand);
boolQueryBuilder.must().add(termQueryBuilder);
deleteByQueryRequest.setQuery(boolQueryBuilder);
try {
BulkByScrollResponse bulkByScrollResponse = restHighLevelClient.deleteByQuery(deleteByQueryRequest, RequestOptions.DEFAULT);
if (bulkByScrollResponse.getTotal() > 0) {
log.info("【删除未还款订单中间表数据】成功，品牌={}, 日期={}", brand, date);
}
} catch (IOException e) {
log.error("【删除未还款订单中间表数据】失败，品牌={}, 日期={}", brand, date, e);
}
}
/**
* 每天定时查询前一天未还款的计划
* @param
*/
public void executeQueryUnpaidRepaymentPlans() {
//分品牌查询数据
SysConstants.BRAND_LIST.stream().forEach(brand -> {
//查询未还款订单中间表
SearchRequest searchRequest = new SearchRequest();
searchRequest.indices(UNPAID_REPAYMENT_PLAN_INDEX_NAME);
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
TermQueryBuilder termQueryBuilder = QueryBuilders.termQuery("brand", brand);
boolQueryBuilder.must().add(termQueryBuilder);
searchSourceBuilder.query(boolQueryBuilder).sort("_id", SortOrder.ASC).size(5000);
searchRequest.source(searchSourceBuilder);
try {
//查询数据
SearchResponse searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
SearchHit[] hits = searchResponse.getHits().getHits();
List<String> orderNoList = new ArrayList<>();
while (hits.length > 0) {
for (SearchHit hit : hits) {
JSONObject jsonObject = JSONObject.parseObject(hit.getSourceAsString());
orderNoList.add(jsonObject.getString("orderNo"));
}
DataSourceTypeByDB dataSourceTypeByDB = Arrays.stream(DataSourceTypeByDB.values()).filter(d -> d.name().toLowerCase().equals(brand)).findFirst().get();
DynamicDataSourceContextHolder.setDataSourceType(dataSourceTypeByDB.getName());
//查询最新的还款计划
List<App2LoanOrderInfo> orderInfoList = app2LoanOrderInfoMapper.selectOrderInfoListByOrderNos(orderNoList);
DynamicDataSourceContextHolder.clearDataSourceType();
if (orderInfoList != null && orderInfoList.size() > 0) {
orderInfoList.stream().forEach(o -> {
List<LoanPaymentPlan> planList = queryRepaymentPlan(o, brand);
if (planList != null) {
savePaymentPlansToEs(o, planList, brand);
}
});
}
orderNoList.clear();
//根据上次查询结果的最后一个 查询后面的数据
SearchHit lastHit = hits[hits.length - 1];
searchSourceBuilder.searchAfter(lastHit.getSortValues());
searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
hits = searchResponse.getHits().getHits();
}
} catch (IOException e) {
log.error("定时查询前一天待还款的计划出现异常={}", e);
}
});
}
/**
* 放款成功的订单拉取还款计划
* @param
*/
public boolean queryLoanSuccessOrderPlans(String dbName, String appOrderNo, String applyStatus) {
log.info("【放款成功订单拉取还款计划】appOrderNo={}, applyStatus={}，dbName={}", appOrderNo, applyStatus, dbName);
//切换数据源 查询订单信息
DataSourceTypeByDB dataSourceTypeByDB = DataSourceTypeByDB.getDataSourceType(dbName);
DynamicDataSourceContextHolder.setDataSourceType(dataSourceTypeByDB.getName());
App2LoanOrderInfo orderInfo = app2LoanOrderInfoMapper.selectApp2LoanOrderInfoByAppOrderNo(appOrderNo);
DynamicDataSourceContextHolder.clearDataSourceType();
if(ObjectUtils.isEmpty(orderInfo)) {
log.error("【放款成功订单拉取还款计划】未查询到订单，appOrderNo={}, 数据源={}", appOrderNo, dataSourceTypeByDB.getName());
return false;
}
//查询还款计划
List<LoanPaymentPlan> planList = queryRepaymentPlan(orderInfo, dataSourceTypeByDB.name().toLowerCase(Locale.ROOT));
if(planList != null) {
return savePaymentPlansToEs(orderInfo, planList, dataSourceTypeByDB.name().toLowerCase(Locale.ROOT));
}
return false;
}
/**
* 保存用户逾期记录
* @param overdueUserRecordList
* @param orderNo
* @param brand
*/
private void saveOverdueUserRecord(List<Map<String, Object>> overdueUserRecordList, String orderNo, String brand) {
//先删除后新增
DeleteByQueryRequest deleteByQueryRequest = new DeleteByQueryRequest(OVERDUE_USER_RECORD_INDEX_NAME);
BoolQueryBuilder delBoolQueryBuilder = new BoolQueryBuilder();
//设置查询条件
TermQueryBuilder orderNoTermQueryBuilder = QueryBuilders.termQuery("order_no", orderNo);
delBoolQueryBuilder.must().add(orderNoTermQueryBuilder);
TermQueryBuilder brandTermQueryBuilder = QueryBuilders.termQuery("brand", brand);
delBoolQueryBuilder.must().add(brandTermQueryBuilder);
deleteByQueryRequest.setQuery(delBoolQueryBuilder);
try {
BulkByScrollResponse delResponse = restHighLevelClient.deleteByQuery(deleteByQueryRequest, RequestOptions.DEFAULT);
if(delResponse.getTotal() > 0) {
log.info("【删除用户逾期记录】成功，orderNo={}，brand={}", orderNo, brand);
}
} catch (IOException e) {
log.error("【删除用户逾期记录】失败，orderNo={}，brand={}", orderNo, brand, e);
}
//新增
BulkRequest bulkRequest = new BulkRequest();
overdueUserRecordList.stream().forEach(r -> {
IndexRequest indexRequest = new IndexRequest(OVERDUE_USER_RECORD_INDEX_NAME);
indexRequest.source(r);
bulkRequest.add(indexRequest);
});
try {
BulkResponse bulkResponse = restHighLevelClient.bulk(bulkRequest, RequestOptions.DEFAULT);
if(!bulkResponse.hasFailures()) {
log.info("【保存用户逾期记录】成功，orderNo={}，brand={}", orderNo, brand);
}
} catch (IOException e) {
log.error("【保存用户逾期记录】失败，orderNo={}，brand={}", orderNo, brand, e);
}
}
/**
* PD应还单量天数
*/
private static final int[] OVERDUE_PD_DAY = new int[] {0, 1, 7, 15, 30};
/**
* 查询逾期单量
* @param overdueStat
* @param overdueStatList
* @param day
*/
private void selectOverdueNum(OverdueStat overdueStat, List<OverdueStat> overdueStatList, int day) {
//查询逾期已结清记录
SearchRequest searchRequest = new SearchRequest();
searchRequest.indices(REPAYMENT_PLAN_INDEX_NAME);
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
//设置查询条件 条件一：还款计划状态是“部分结清” 且 系统当前时间 > 应还款日期 + day
BoolQueryBuilder boolQueryBuilder1 = new BoolQueryBuilder();
//应还日期
RangeQueryBuilder dateRangeQuery = QueryBuilders.rangeQuery("loan_give_date");
dateRangeQuery.gte(overdueStat.getBeginTime());
dateRangeQuery.lte(overdueStat.getEndTime());
boolQueryBuilder1.must().add(dateRangeQuery);
//产品
TermQueryBuilder productTermQueryBuilder = QueryBuilders.termQuery("loan_office_flag", overdueStat.getLoanOfficeFlag());
boolQueryBuilder1.must().add(productTermQueryBuilder);
//还款状态 还款计划状态是“部分结清”
TermQueryBuilder statusQueryBuilder = QueryBuilders.termQuery("app_loan_status", 5);
boolQueryBuilder1.must().add(statusQueryBuilder);
//首期
TermQueryBuilder indexQueryBuilder = QueryBuilders.termQuery("index_no", 1);
boolQueryBuilder1.must().add(indexQueryBuilder);
//系统当前时间 - 应还款日期 > day
Map<String, Object> params = new HashMap<>();
params.put("num", System.currentTimeMillis());
Script script2 = new Script(Script.DEFAULT_SCRIPT_TYPE, "painless", "(params.num - doc['loan_give_date'].value.toInstant().toEpochMilli()) / 1000 > " + (day == 0 ? 0 :(day + 1) * 24 * 60 * 60), params);
ScriptQueryBuilder scriptQueryBuilder2 = new ScriptQueryBuilder(script2);
boolQueryBuilder1.must().add(scriptQueryBuilder2);
//条件二：还款计划状态是“逾期结清”且实际还款日期 > 应还款日期 + day
BoolQueryBuilder boolQueryBuilder2 = new BoolQueryBuilder();
//还款计划状态是“逾期结清”
TermQueryBuilder appLoanStatusQueryBuilder = QueryBuilders.termQuery("app_loan_status", 4);
boolQueryBuilder2.must().add(appLoanStatusQueryBuilder);
Script script = new Script("if(doc['actual_loan_give_date'] != null && doc['actual_loan_give_date'].value != null){" +
" return (doc['actual_loan_give_date'].value.toInstant().toEpochMilli() - doc['loan_give_date'].value.toInstant().toEpochMilli()) / 1000 > " + (day == 0 ? 0 :(day + 1) * 24 * 60 * 60) +
" }");
ScriptQueryBuilder scriptQueryBuilder = new ScriptQueryBuilder(script);
boolQueryBuilder2.must().add(scriptQueryBuilder);
boolQueryBuilder2.must().add(productTermQueryBuilder);
boolQueryBuilder2.must().add(dateRangeQuery);
boolQueryBuilder2.must().add(indexQueryBuilder);
//条件三：还款计划状态是“逾期” 且 系统当前时间 > 应还款日期 + day
BoolQueryBuilder boolQueryBuilder3 = new BoolQueryBuilder();
//还款状态 还款计划状态是“逾期”或还款计划状态是“部分结清”
TermQueryBuilder statusQueryBuilder3 = QueryBuilders.termQuery("app_loan_status", 3);
boolQueryBuilder3.must().add(statusQueryBuilder3);
boolQueryBuilder3.must().add(scriptQueryBuilder2);
boolQueryBuilder3.must().add(productTermQueryBuilder);
boolQueryBuilder3.must().add(dateRangeQuery);
boolQueryBuilder3.must().add(indexQueryBuilder);
//当产品名称选择“易得花”并且选择“过滤掉权益卡未结清未而逾期用户”时，条件三增加还款金额为0的条件
if(ProcessConstants.FEN_QI_YI_PRODUCT_KEY.equals(overdueStat.getLoanOfficeFlag()) && SysConstants.ONE.equals(overdueStat.getFilterFlag())) {
TermQueryBuilder amountTermQueryBuilder = QueryBuilders.termQuery("already_repay_amount", "0");
boolQueryBuilder3.must().add(amountTermQueryBuilder);
}
//品牌
if(StringUtils.isNotBlank(overdueStat.getBrand())) {
TermQueryBuilder brandTermQueryBuilder = QueryBuilders.termQuery("brand", overdueStat.getBrand());
boolQueryBuilder1.must().add(brandTermQueryBuilder);
boolQueryBuilder2.must().add(brandTermQueryBuilder);
boolQueryBuilder3.must().add(brandTermQueryBuilder);
}
//boolQueryBuilder.should().add(boolQueryBuilder1);
boolQueryBuilder.should().add(boolQueryBuilder2);
boolQueryBuilder.should().add(boolQueryBuilder3);
//按日期分组统计
TermsAggregationBuilder aggregationBuilder = AggregationBuilders.terms("countAgg").field("loan_give_date").size(500);
CardinalityAggregationBuilder cardinalityAggregationBuilder = AggregationBuilders.cardinality("countAgg1").field("order_no");
aggregationBuilder.subAggregation(cardinalityAggregationBuilder);
searchSourceBuilder.aggregation(aggregationBuilder);
searchSourceBuilder.query(boolQueryBuilder).size(5000);
searchRequest.source(searchSourceBuilder);
try {
//分组统计数量
SearchResponse response = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
Terms countAgg = response.getAggregations().get("countAgg");
for (Terms.Bucket bucket : countAgg.getBuckets()) {
overdueStatList.stream().forEach(o -> {
if(StringUtils.equals(bucket.getKeyAsString(), o.getLoanGiveDate())) {
Aggregation aggregation = bucket.getAggregations().asList().get(0);
int count = (int) ((ParsedCardinality) aggregation).getValue();
// int count = Math.toIntExact(bucket.getDocCount());
switch (day) {
case 0:
o.setPd0PayableNum(count);
break;
case 1:
o.setPd1PayableNum(count);
break;
case 7:
o.setPd7PayableNum(count);
break;
case 15:
o.setPd15PayableNum(count);
break;
case 30:
o.setPd30PayableNum(count);
break;
}
}
});
}
} catch (Exception e) {
log.error("【贷后逾期报表】查询PD{}未还单量失败", day, e);
}
}
/**
* 查询应还款单量
* @param overdueStat
* @return
*/
private List<OverdueStat> selectPayableNum(OverdueStat overdueStat) {
//查询逾期已结清记录
SearchRequest searchRequest = new SearchRequest();
searchRequest.indices(REPAYMENT_PLAN_INDEX_NAME);
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
//设置应还日期查询条件
RangeQueryBuilder rangeQuery = QueryBuilders.rangeQuery("loan_give_date");
rangeQuery.gte(overdueStat.getBeginTime());
rangeQuery.lte(overdueStat.getEndTime());
boolQueryBuilder.must().add(rangeQuery);
TermQueryBuilder termQueryBuilder = QueryBuilders.termQuery("loan_office_flag", overdueStat.getLoanOfficeFlag());
boolQueryBuilder.must().add(termQueryBuilder);
//首期
TermQueryBuilder indexQueryBuilder = QueryBuilders.termQuery("index_no", 1);
boolQueryBuilder.must().add(indexQueryBuilder);
//品牌
if(StringUtils.isNotBlank(overdueStat.getBrand())) {
TermQueryBuilder brandTermQueryBuilder = QueryBuilders.termQuery("brand", overdueStat.getBrand());
boolQueryBuilder.must().add(brandTermQueryBuilder);
}
//按日期分组统计
TermsAggregationBuilder aggregationBuilder = AggregationBuilders.terms("countAgg").field("loan_give_date").size(500);
CardinalityAggregationBuilder cardinalityAggregationBuilder = AggregationBuilders.cardinality("countAgg1").field("order_no");
aggregationBuilder.subAggregation(cardinalityAggregationBuilder);
searchSourceBuilder.aggregation(aggregationBuilder);
searchSourceBuilder.query(boolQueryBuilder).size(5000);
searchRequest.source(searchSourceBuilder);
List<OverdueStat> resultList = new ArrayList<>();
try {
//分组统计数量
SearchResponse response = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
Terms countAgg = response.getAggregations().get("countAgg");
for (Terms.Bucket bucket : countAgg.getBuckets()) {
OverdueStat stat = new OverdueStat();
stat.setLoanGiveDate(bucket.getKeyAsString());
Aggregation aggregation = bucket.getAggregations().asList().get(0);
int count = (int) ((ParsedCardinality) aggregation).getValue();
stat.setPayableNum(count);
resultList.add(stat);
}
} catch (Exception e) {
log.error("【贷后逾期报表】查询应还款单量失败", e);
}
return resultList;
}
/**
* 计算比例
* @param divisor 除数
* @param dividend 被除数
* @return
*/
private String calculateRate(Integer divisor, Integer dividend) {
return dividend == 0 ? SysConstants.ZERO : new BigDecimal(divisor).multiply(BigDecimal.valueOf(100)).divide(new BigDecimal(dividend), 2, BigDecimal.ROUND_HALF_DOWN).toString();
}
/**
* 同步历史逾期用户记录
*/
public void executeHistoryOverdueRecord() {
//查询逾期已结清记录
SearchRequest searchRequest = new SearchRequest();
searchRequest.indices(REPAYMENT_PLAN_INDEX_NAME);
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
//设置查询条件 还款状态=4-逾期已结清,3-已逾期
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
TermsQueryBuilder termsQueryBuilder = QueryBuilders.termsQuery("app_loan_status", ProcessConstants.FORE, ProcessConstants.THREE);
boolQueryBuilder.must().add(termsQueryBuilder);
searchSourceBuilder.query(boolQueryBuilder).sort("_id", SortOrder.ASC).size(5000);
searchRequest.source(searchSourceBuilder);
try {
List<Map<String, Object>> overdueUserRecordList = new ArrayList<>();
SearchResponse searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
SearchHit[] hits = searchResponse.getHits().getHits();
if(hits.length == 0) {
return;
}
//分页遍历查询
while (hits.length > 0) {
for (SearchHit hit : hits) {
LoanPaymentPlan loanPaymentPlan = JSONObject.parseObject(hit.getSourceAsString(), LoanPaymentPlan.class, JSONReader.Feature.SupportSmartMatch);
Map<String, Object> map = new HashMap<>();
map.put("brand", loanPaymentPlan.getBrand());
map.put("user_id", loanPaymentPlan.getUserId());
map.put("loan_name", loanPaymentPlan.getLoanName());
map.put("loan_mobile_mask", loanPaymentPlan.getLoanMobileMask());
map.put("loan_mobile_md5", loanPaymentPlan.getLoanMobileMd5());
overdueUserRecordList.add(map);
}
//保存逾期记录
BulkRequest bulkRequest = new BulkRequest();
overdueUserRecordList.stream().forEach(r -> {
IndexRequest indexRequest = new IndexRequest(OVERDUE_USER_RECORD_INDEX_NAME);
indexRequest.source(r);
bulkRequest.add(indexRequest);
});
//批量保存
BulkResponse bulkResponse = restHighLevelClient.bulk(bulkRequest, RequestOptions.DEFAULT);
overdueUserRecordList.clear();
//根据上次查询结果的最后一个 查询后面的数据
SearchHit lastHit = hits[hits.length - 1];
searchSourceBuilder.searchAfter(lastHit.getSortValues());
searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
hits = searchResponse.getHits().getHits();
}
log.info("【同步历史逾期用户记录】成功");
} catch (IOException e) {
log.error("【同步历史逾期用户记录】出现异常", e);
}
}
/**
*
* @param beginTime
* @param endTime
* @param productKey
* @return
*/
public String exportRepaymentPlans(String beginTime, String endTime, String productKey, String loanStatus, String brand) {
FileOutputStream fileOutputStream = null;
ExcelWriter excelWriter = null;
try {
String fileName = new StringBuilder(StringUtils.isNotBlank(productKey) ? productKey : "").append("还款计划").append(beginTime).append("_").append(endTime).append(".xlsx").toString();
fileOutputStream = new FileOutputStream(ExcelUtil.getAbsoluteFile(fileName));
excelWriter = EasyExcel.write(fileOutputStream, ExportLoanPaymentPlan.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet("还款计划").build();
SearchRequest searchRequest = new SearchRequest();
searchRequest.indices(REPAYMENT_PLAN_INDEX_NAME);
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
RangeQueryBuilder rangeQuery = QueryBuilders.rangeQuery("loan_time");
rangeQuery.gte(DateUtils.StringToTimestamp(beginTime + SysConstants.BEGIN_TIME_SUFFIX));
rangeQuery.lte(DateUtils.StringToTimestamp(endTime + SysConstants.END_TIME_SUFFIX));
boolQueryBuilder.must().add(rangeQuery);
if(StringUtils.isNotBlank(productKey)) {
TermQueryBuilder termQueryBuilder = QueryBuilders.termQuery("loan_office_flag", productKey);
boolQueryBuilder.must().add(termQueryBuilder);
}
if(StringUtils.isNotBlank(loanStatus)) {
TermQueryBuilder termQueryBuilder = QueryBuilders.termQuery("app_loan_status", loanStatus);
boolQueryBuilder.must().add(termQueryBuilder);
}
if(StringUtils.isNotBlank(brand)) {
TermQueryBuilder termQueryBuilder = QueryBuilders.termQuery("brand", brand);
boolQueryBuilder.must().add(termQueryBuilder);
}
searchSourceBuilder.query(boolQueryBuilder);
searchRequest.source(searchSourceBuilder);
searchSourceBuilder.sort("_id", SortOrder.ASC);
searchSourceBuilder.size(5000);
//查询数据
SearchResponse searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
SearchHit[] hits = searchResponse.getHits().getHits();
List<ExportLoanPaymentPlan> resultList = new ArrayList<>();
while (hits.length > 0) {
for (SearchHit hit : hits) {
LoanPaymentPlan loanPaymentPlan = JSONObject.parseObject(hit.getSourceAsString(), LoanPaymentPlan.class, JSONReader.Feature.SupportSmartMatch);
ExportLoanPaymentPlan exportLoanPaymentPlan = new ExportLoanPaymentPlan();
exportLoanPaymentPlan.setUserId(loanPaymentPlan.getUserId());
exportLoanPaymentPlan.setLoanName(loanPaymentPlan.getLoanName());
exportLoanPaymentPlan.setLoanGiveDate(loanPaymentPlan.getLoanGiveDate());
if(StringUtils.isNotBlank(loanPaymentPlan.getLoanTime())) {
String result2 = simpleDateFormat.format(new Date(Long.parseLong(loanPaymentPlan.getLoanTime()) * 1000));
exportLoanPaymentPlan.setLoanTime(result2);
}
exportLoanPaymentPlan.setAppLoanStatus(loanPaymentPlan.getAppLoanStatus());
resultList.add(exportLoanPaymentPlan);
}
excelWriter.write(resultList, writeSheet);
if(hits.length > 0) {
//根据上次查询结果的最后一个 查询后面的数据
SearchHit lastHit = hits[hits.length - 1];
searchSourceBuilder.searchAfter(lastHit.getSortValues());
searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
hits = searchResponse.getHits().getHits();
}
resultList.clear();
}
excelWriter.finish();
fileOutputStream.flush();
log.info("【导出还款计划数据】成功，fileName={}", fileName);
return fileName;
} catch (Exception e) {
log.error("【导出还款计划数据】失败={}", e);
} finally {
try {
if(fileOutputStream != null) {
fileOutputStream.close();
}
if(excelWriter != null) {
excelWriter.close();
}
} catch (IOException e) {
throw new RuntimeException(e);
}
}
return null;
}
public void fixData(String beginTime, String endTime, String brand) {
SearchRequest searchRequest = new SearchRequest();
searchRequest.indices(REPAYMENT_PLAN_INDEX_NAME);
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
RangeQueryBuilder rangeQuery = QueryBuilders.rangeQuery("create_day");
rangeQuery.gte(beginTime);
rangeQuery.lte(endTime);
boolQueryBuilder.must().add(rangeQuery);
TermQueryBuilder termQueryBuilder = QueryBuilders.termQuery("brand", brand);
boolQueryBuilder.must().add(termQueryBuilder);
searchSourceBuilder.query(boolQueryBuilder).size(5000);
searchRequest.source(searchSourceBuilder);
SearchResponse searchResponse = null;
try {
searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
} catch (IOException e) {
throw new RuntimeException(e);
}
SearchHit[] hits = searchResponse.getHits().getHits();
List<String> orderNoList = new ArrayList<>();
for (SearchHit hit : hits) {
LoanPaymentPlan loanPaymentPlan = JSONObject.parseObject(hit.getSourceAsString(), LoanPaymentPlan.class, JSONReader.Feature.SupportSmartMatch);
orderNoList.add(loanPaymentPlan.getOrderNo());
}
}
}
`
浙公网安备 33010602011771号