Java-代码段-合计两个表中的数据汇总写入到另一个表-删除脏数据-判断新增和更新-没变更的数据不更新-最近几天数据是否有变化数据
/**
* 考虑脏数据行执行删除
* 考虑数据没有变更不执行更新
* 判断出 新增的 编辑的
* 支持先查询最近几天的数据有没有变化
* where in list 值处理
* 自动统计相关方企业某一合同下违规事项，包括违规次数，扣分总计等。
* 来自安元 四川中烟 proj包
* 扣分记录：http://172.168.1.81:28000/space-sichuanzhongyananqua/app/!/information/xiangguanfangkoufenj xiangguanfangkoufenj
* 扣分统计: http://172.168.1.81:28000/space-sichuanzhongyananqua/app/!/information/xiangguanfangkoufent xiangguanfangkoufent
*
*/
@Override
public String generateDeductionStatistics(String qiyebianma, String hetongbianhao, Integer lastDay) {
// 注意入参 是 且 的关系
log.info("{}入参,qiyebianma：{}, hetongbianhao:{}, lastDay:{}", LOG_TAG_DEDUCTIONSTATISTICS, qiyebianma, hetongbianhao, lastDay);
List<String> xiangguanfangqiyebiaList = null;
List<String> hetongbianhaoList = null;
if (lastDay != null) {
String sqlQueryChangeData = String.format("SELECT `xiangguanfangqiyebia`,`hetongbianhao` from ent_sichuanzhongyananqua_datapool_xiangguanfangkoufenj where (`created_at` > DATE_SUB(now(), INTERVAL %d DAY) or `last_modified` > DATE_SUB(now(), INTERVAL %d DAY) )", lastDay, lastDay);
if (StringUtils.isNotBlank(qiyebianma)) {
sqlQueryChangeData += String.format(" and `xiangguanfangqiyebia` = '%s'", qiyebianma);
}
if (StringUtils.isNotBlank(hetongbianhao)) {
sqlQueryChangeData += String.format(" and `hetongbianhao` = '%s'", hetongbianhao);
}
sqlQueryChangeData += " GROUP BY `xiangguanfangqiyebia`,`hetongbianhao` ";
List<Map<String, Object>> maps = execSql(getEntId(), sqlQueryChangeData);
if (maps.isEmpty()) {
String result = String.format("%s中止，最近%d天没有变化的违规扣分记录", LOG_TAG_DEDUCTIONSTATISTICS, lastDay);
log.info(result);
return result;
}
xiangguanfangqiyebiaList = getWhereInValues(maps, "xiangguanfangqiyebia");
hetongbianhaoList = getWhereInValues(maps, "hetongbianhao");
}
xiangguanfangqiyebiaList = addParamToList(qiyebianma, xiangguanfangqiyebiaList);
hetongbianhaoList = addParamToList(hetongbianhao, hetongbianhaoList);
StringBuilder sql = new StringBuilder("select `xiangguanfangqiyemin`,`xiangguanfangqiyebia`,`hetongmingcheng`,`hetongbianhao`,count(`id`) as violationCount, sum(`weiguikoufen`) totalDeductionPoints from ent_sichuanzhongyananqua_datapool_xiangguanfangkoufenj where `disable` <> 1 ");
appendWhereInValues(xiangguanfangqiyebiaList, sql, " and `xiangguanfangqiyebia` in (");
appendWhereInValues(hetongbianhaoList, sql, " and `hetongbianhao` in (");
sql.append(" GROUP BY `xiangguanfangqiyebia`,`hetongbianhao`");
List<Map<String, Object>> maps = execSql(getEntId(), sql.toString());
if (maps.isEmpty()) {
String result = String.format("%s中止，查询违规扣分记录为空", LOG_TAG_DEDUCTIONSTATISTICS);
log.info(result);
return result;
}
setUniqueTag(maps, "xiangguanfangqiyebia", "hetongbianhao");
xiangguanfangqiyebiaList = getWhereInValues(maps, "xiangguanfangqiyebia");
hetongbianhaoList = getWhereInValues(maps, "hetongbianhao");
if (xiangguanfangqiyebiaList.isEmpty() || hetongbianhaoList.isEmpty()) {
String result = String.format("%s中止，查询企业编号或合同编号为空", LOG_TAG_DEDUCTIONSTATISTICS);
log.info(result);
return result;
}
AtomicLong changeCount = new AtomicLong();
synchronized (ParamFieldLockUtils.getLock("generateDeductionStatistics", "1")) {
StringBuilder sqlQueryExists = new StringBuilder("select `id`,`qiyemingcheng`,`qiyebianma`,`hetongmingcheng`,`hetongbianma`,`weiguicishu`,`koufenheji` from ent_sichuanzhongyananqua_datapool_xiangguanfangkoufent where `disable` <> 1 ");
appendWhereInValues(xiangguanfangqiyebiaList, sqlQueryExists, " and `qiyebianma` in (");
appendWhereInValues(hetongbianhaoList, sqlQueryExists, " and `hetongbianma` in (");
List<Map<String, Object>> deductionStatisticsList = execSql(getEntId(), sqlQueryExists.toString());
setUniqueTag(deductionStatisticsList, "qiyebianma", "hetongbianma");
ImmutableListMultimap<String, Map<String, Object>> deductionStatisticsListMap = Multimaps.index(deductionStatisticsList, y -> y != null ? y.get("uniqueTag").toString() : null);
List<String> dbUniqueTagList = deductionStatisticsList.stream().map(y -> y.get("uniqueTag").toString()).collect(Collectors.toList());
log.info("dbUniqueTagList:{}", dbUniqueTagList);
//新增的
List<Map<String, Object>> insertList = maps.stream().filter(item -> !dbUniqueTagList.contains(item.get("uniqueTag").toString())).collect(Collectors.toList());
log.info("insertList:{}", insertList);
//要编辑的
List<Map<String, Object>> updateList = maps.stream().filter(item -> dbUniqueTagList.contains(item.get("uniqueTag").toString())).collect(Collectors.toList());
log.info("updateList:{}", updateList); // 这个还要去关联id
String adminUser = getAdminUser();
if (!insertList.isEmpty()) {
for (Map<String, Object> map : insertList) {
try {
Map<String, Object> data = getInsertOrUpdateMapMap(map, null);
DatacenterSaveRecordRequest request = new DatacenterSaveRecordRequest();
request.setEntId(getEntId());
request.setUserId(adminUser);
request.setTableId("xiangguanfangkoufent");
request.setMethod(StringUtils.join("info/xiangguanfangkoufent"));//appid]
request.setData(data);
request.setDebug(true);
log.info("{}，准备进行新增：data：{}", LOG_TAG_DEDUCTIONSTATISTICS, JSON.toJSONString(request.getData()));
QpaasHttpResponse qpaasHttpResponse = paasTemplate.getClient(getEntId()).doAction(request);
log.info("{}，新增返回：{},data:{}", LOG_TAG_DEDUCTIONSTATISTICS, qpaasHttpResponse.getMessage(), qpaasHttpResponse.getData());
changeCount.getAndIncrement();
} catch (Exception e) {
log.error("{}，新增出现异常：{},data:{},异常：{}", LOG_TAG_DEDUCTIONSTATISTICS, JSON.toJSONString(map), e);
}
}
}
if (!updateList.isEmpty()) {
//脏数据，顺便删除
List<Integer> deleteIdList = Lists.newArrayList();
for (Map<String, Object> map : updateList) {
try {
String uniqueTag = map.get("uniqueTag").toString();
ImmutableList<Map<String, Object>> dbListMap = deductionStatisticsListMap.get(uniqueTag);
if (CollectionUtils.isEmpty(dbListMap)) {
//理论上不为空
continue;
}
int size = dbListMap.size();
if (size > 1) {
//理论上只有一条数据
for (int index = 1; index < size - 1; index++) {
Map<String, Object> getDeleteId = dbListMap.get(index);
Integer deleteId = Integer.valueOf(getDeleteId.get("id").toString());
deleteIdList.add(deleteId);
}
}
Map<String, Object> dbUpdateOne = dbListMap.get(0);
Map<String, Object> data = getInsertOrUpdateMapMap(map, dbUpdateOne);
log.info("{}，更新数据跳过，当前没有没有更新：uniqueTag：{}", LOG_TAG_DEDUCTIONSTATISTICS, uniqueTag);
if(data == null){
continue;
}
DataflowUpdateInstanceRequest request = new DataflowUpdateInstanceRequest();
request.setAppId("xiangguanfangkoufent");
request.setData(data);
request.setEntId(getEntId());
request.setTableId("xiangguanfangkoufent");
request.setUserId(adminUser);
request.setRecordId(String.valueOf(Integer.valueOf(dbUpdateOne.get("id").toString())));
request.setDebug(true);
log.info("{}，准备进行更新：data：{}", LOG_TAG_DEDUCTIONSTATISTICS, JSON.toJSONString(request.getData()));
QpaasHttpResponse<DataflowInstanceResultDTO> response = paasTemplate.getClient(getEntId()).doAction(request);
boolean success = response.getSuccess();
if (success) {
DataflowInstanceResultDTO result = response.getData();
changeCount.getAndIncrement();
log.info("{}，更新扣分统计，成功，result：{}", LOG_TAG_DEDUCTIONSTATISTICS, JSON.toJSONString(result));
} else {
String message = response.getMessage();
log.info("{}，更新扣分统计，失败，result：{}", LOG_TAG_DEDUCTIONSTATISTICS, JSON.toJSONString(message));
}
} catch (Exception e) {
log.error("{}，更新扣分统计，出现异常，map：{},异常：{}", LOG_TAG_DEDUCTIONSTATISTICS, JSON.toJSONString(map), e);
}
}
if (CollectionUtils.isNotEmpty(deleteIdList)) {
DataflowDeleteMasterRequest.DatacenterDeleteExtendInfoVO datacenterDeleteExtendInfoVO = new DataflowDeleteMasterRequest.DatacenterDeleteExtendInfoVO();
DataflowDeleteMasterRequest request = new DataflowDeleteMasterRequest();
request.setAppId("xiangguanfangkoufent");//设置应用id
request.setEntId(getEntId());//设置企业id
request.setRecordIds(JSON.toJSONString(deleteIdList));//删除记录ID集合删除记录ID集合(List<Integer> JSON字符串)
request.setExtendInfo(datacenterDeleteExtendInfoVO);//设置扩展信息
request.setUserId(adminUser);//设置用户id
request.setDebug(true);
log.info("{}，删除扣分统计脏数据，deleteIdList：{}", LOG_TAG_DEDUCTIONSTATISTICS, JSON.toJSONString(deleteIdList));
try {
QpaasHttpResponse<Long> response = paasTemplate.getClient(getEntId()).doAction(request);
changeCount.getAndAdd(deleteIdList.size());
} catch (Exception e) {
log.error("{}，删除扣分统计脏数据出现异常，deleteIdList：{},异常:{}", LOG_TAG_DEDUCTIONSTATISTICS, JSON.toJSONString(deleteIdList), e);
}
}
}
}
String result = String.format("%s 完成，共影响行数%d条", LOG_TAG_DEDUCTIONSTATISTICS, changeCount.get());
log.info(result);
return result;
}
private List<String> addParamToList(String param, List<String> sqlParamValueList) {
if (StringUtils.isNotBlank(param)) {
param = "'" + param + "'";
if (sqlParamValueList == null) {
sqlParamValueList = Lists.newArrayList(param);
} else {
if (!param.contains(param)) {
sqlParamValueList.add(param);
}
}
}
return sqlParamValueList;
}
private void setUniqueTag(List<Map<String, Object>> maps, String qiyebianma, String hetongbianhao) {
for (Map<String, Object> map : maps) {
String uniqueTag = map.get(qiyebianma) + "#" + map.get(hetongbianhao);
map.put("uniqueTag", uniqueTag);
}
}
private void appendWhereInValues(List<String> valueList, StringBuilder sql, String s) {
if (CollectionUtils.isNotEmpty(valueList)) {
sql.append(s);
String values = StringUtils.join(valueList, ",");
sql.append(values);
sql.append(") ");
}
}
/**
* 提前处理好where in value 上的值的引号
* @param maps
* @param key
* @return
*/
@NotNull
private List<String> getWhereInValues(List<Map<String, Object>> maps, String key) {
return maps.stream().map(y -> "'" + y.getOrDefault(key, "").toString() + "'").filter(s -> !"''".equals(s)).distinct().collect(Collectors.toList());
}
private Map<String, Object> getInsertOrUpdateMapMap(Map<String, Object> map, Map<String, Object> dbOldOne) {
if (dbOldOne != null) {
boolean isHasChange = false;
isHasChange = isHasChange(map, dbOldOne, isHasChange, "qiyemingcheng", "xiangguanfangqiyemin");
isHasChange = isHasChange(map, dbOldOne, isHasChange, "qiyebianma", "xiangguanfangqiyebia");
isHasChange = isHasChange(map, dbOldOne, isHasChange, "hetongmingcheng", "hetongmingcheng");
isHasChange = isHasChange(map, dbOldOne, isHasChange, "hetongbianma", "hetongbianhao");
isHasChange = isHasChange(map, dbOldOne, isHasChange, "weiguicishu", "violationCount");
isHasChange = isHasChange(map, dbOldOne, isHasChange, "koufenheji", "totalDeductionPoints");
if(!isHasChange){
return null;
}
}
Map<String, Object> data = new HashMap<>();
data.put("qiyemingcheng", map.getOrDefault("xiangguanfangqiyemin", "").toString());
data.put("qiyebianma", map.getOrDefault("xiangguanfangqiyebia", "").toString());
data.put("hetongmingcheng", map.getOrDefault("hetongmingcheng", "").toString());