package com.nova.sankuai.service.impl;
|
|
import cn.hutool.core.date.DateTime;
|
import cn.hutool.core.date.DateUtil;
|
import cn.hutool.core.io.IoUtil;
|
import cn.hutool.poi.excel.ExcelUtil;
|
import cn.hutool.poi.excel.ExcelWriter;
|
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
|
import com.baomidou.mybatisplus.core.metadata.IPage;
|
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
|
import com.nova.sankuai.domain.dto.*;
|
import com.nova.sankuai.domain.entity.ChannelInfo;
|
import com.nova.sankuai.domain.entity.ChannelLog;
|
import com.nova.sankuai.domain.entity.Customer;
|
import com.nova.sankuai.domain.entity.CustomerUser;
|
import com.nova.sankuai.domain.enums.SourceTypeEnum;
|
import com.nova.sankuai.domain.vo.*;
|
import com.nova.sankuai.domain.vo.channel.ChannelExportReportVo;
|
import com.nova.sankuai.domain.vo.channel.ChannelImportReportVo;
|
import com.nova.sankuai.domain.vo.channel.ChannelReportTotalVo;
|
import com.nova.sankuai.infra.config.CommonException;
|
import com.nova.sankuai.infra.mapper.*;
|
import com.nova.sankuai.infra.utils.PageRequest;
|
import com.nova.sankuai.infra.utils.PageUtil;
|
import com.nova.sankuai.security.UserDetail;
|
import com.nova.sankuai.service.CostConfigService;
|
import com.nova.sankuai.service.IChannelInfoService;
|
import com.nova.sankuai.service.IChannelLogService;
|
import com.nova.sankuai.service.ICustomerUserService;
|
import lombok.AllArgsConstructor;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.commons.lang.StringUtils;
|
import org.apache.poi.ss.usermodel.*;
|
import org.springframework.beans.BeanUtils;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
import org.springframework.util.CollectionUtils;
|
import org.springframework.util.ObjectUtils;
|
|
import javax.servlet.ServletOutputStream;
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.IOException;
|
import java.nio.charset.StandardCharsets;
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
/**
|
* @author weikangdi
|
*/
|
@Service
|
@AllArgsConstructor
|
@Slf4j
|
public class ChannelLogServiceImpl extends ServiceImpl<ChannelLogMapper, ChannelLog> implements IChannelLogService {
|
|
private final ChannelLogMapper channelLogMapper;
|
|
private final PageUtil pageUtil;
|
|
private final IChannelInfoService channelInfoService;
|
|
private final CustomerMapper customerMapper;
|
|
private final SysUserMapper sysUserMapper;
|
|
private final ICustomerUserService customerUserService;
|
|
private final LoanSupermarketMapper loanSupermarketMapper;
|
|
private final ChannelInfoMapper channelInfoMapper;
|
|
private final CostConfigService costConfigService;
|
|
@Override
|
public void createImportLog(Integer source, Integer status, Long customerId, String importObjects) {
|
ChannelInfo channelInfo = channelInfoService.lambdaQuery().eq(ChannelInfo::getCode, source).one();
|
ChannelLog channelLog = new ChannelLog();
|
channelLog.setSource(source);
|
channelLog.setName(channelInfo.getName());
|
channelLog.setType(ChannelLog.IMPORT);
|
channelLog.setStatus(status);
|
channelLog.setCustomerRecordId(customerId);
|
channelLog.setImportObjects(importObjects);
|
channelLog.setCreationDate(new Date());
|
if (!checkDuplicateLog(channelLog)) {
|
save(channelLog);
|
}
|
}
|
|
@Override
|
public void createExportLog(Integer source, Integer exportSource, Integer status, Long customerId, String result) {
|
ChannelInfo channelInfo = channelInfoService.lambdaQuery().eq(ChannelInfo::getCode, source).one();
|
ChannelLog channelLog = new ChannelLog();
|
channelLog.setSource(source);
|
channelLog.setName(channelInfo.getName());
|
channelLog.setType(ChannelLog.EXPORT);
|
channelLog.setExportSource(exportSource);
|
channelLog.setStatus(status);
|
channelLog.setCustomerRecordId(customerId);
|
channelLog.setResult(result);
|
channelLog.setCreationDate(new Date());
|
if (!checkDuplicateLog(channelLog)) {
|
save(channelLog);
|
}
|
}
|
|
@Override
|
@Transactional(rollbackFor = Exception.class)
|
public void createSupermarketLog(Long supermarketId, Integer source, Long customerId) {
|
ChannelLog channelLog = new ChannelLog();
|
Customer customer = customerMapper.selectById(customerId);
|
if (customer != null) {
|
channelLog.setSource(customer.getSourceChannel());
|
channelLog.setStatus(ChannelLog.SUCCESS);
|
} else {
|
channelLog.setSource(source);
|
channelLog.setStatus(ChannelLog.FAIL);
|
}
|
ChannelInfo channelInfo = channelInfoService.lambdaQuery().eq(ChannelInfo::getCode, customer.getSourceChannel()).one();
|
channelLog.setName(channelInfo.getName());
|
channelLog.setExportSource(SourceTypeEnum.LOAN_SUPERMARKET.getCode());
|
channelLog.setType(ChannelLog.EXPORT);
|
channelLog.setCustomerRecordId(customerId);
|
channelLog.setRecordId(supermarketId);
|
channelLog.setCreationDate(new Date());
|
if (!checkDuplicateLog(channelLog)) {
|
save(channelLog);
|
} else {
|
log.info("创建重复客户推送记录，原始数据：" + channelLog.toString());
|
}
|
}
|
|
@Override
|
public List<ChannelReportTotalVo> importTotal(ChannelReportDto reportDto) {
|
List<ChannelReportTotalVo> result = channelLogMapper.importTotal(reportDto.initDate());
|
result = result.stream().filter(i -> i.getSuccessCountSum() != 0).collect(Collectors.toList());
|
return result;
|
}
|
|
@Override
|
public IPage<ChannelImportReportVo> importDetail(ChannelReportDetailDto reportDto, PageRequest pageRequest) {
|
IPage<ChannelImportReportVo> page = channelLogMapper.importDetail(pageUtil.getPage(pageRequest), reportDto.initDate());
|
return page;
|
}
|
|
@Override
|
public List<ChannelReportTotalVo> exportTotal(ChannelReportDto reportDto) {
|
List<ChannelReportTotalVo> result = new ArrayList<>();
|
// List<ChannelReportTotalVo> common = channelLogMapper.exportTotal(reportDto.initDate());
|
// result.addAll(common);
|
// if (reportDto.getChannelCode() != null && !SourceTypeEnum.LOAN_SUPERMARKET.getCode().equals(reportDto.getChannelCode())) {
|
// return result;
|
// }
|
List<ChannelReportTotalVo> supermarket = channelLogMapper.exportSupermarketTotal(reportDto.initDate());
|
result.addAll(supermarket);
|
result = result.stream().filter(i -> i.getSuccessCountSum() != 0).collect(Collectors.toList());
|
return result;
|
}
|
|
@Override
|
public IPage<ChannelExportReportVo> exportDetail(ChannelReportDetailDto reportDto, PageRequest pageRequest) {
|
if (SourceTypeEnum.LOAN_SUPERMARKET.getCode().equals(reportDto.getChannelCode())) {
|
IPage<ChannelExportReportVo> page = channelLogMapper.exportLoanMarketDetail(pageUtil.getPage(pageRequest), reportDto.initDate());
|
return page;
|
}
|
IPage<ChannelExportReportVo> page = channelLogMapper.exportDetail(pageUtil.getPage(pageRequest), reportDto.initDate());
|
return page;
|
}
|
|
@Override
|
public List<ChannelInfo> channelList(Integer type) {
|
List<ChannelInfo> list = channelInfoService.lambdaQuery().eq(ChannelInfo::getStatus, 1)
|
.eq(ChannelInfo::getType, type).list();
|
return list;
|
}
|
|
private boolean checkDuplicateLog(ChannelLog channelLog) {
|
DateTime endOfDay = DateUtil.endOfDay(new Date());
|
DateTime beginOfDay = DateUtil.beginOfDay(new Date());
|
List<ChannelLog> list = channelLogMapper.selectDuplicateLogs(beginOfDay, endOfDay, channelLog);
|
if (list == null || list.isEmpty()) {
|
return false;
|
}
|
return true;
|
}
|
|
@Override
|
public void addJYDLoanMarketRecord(Long loanMarkId, UserDetail userDetail) {
|
CustomerUser customerUser = customerUserService.lambdaQuery().eq(CustomerUser::getPhone, userDetail.getPhone()).one();
|
ChannelLog channelLog = new ChannelLog();
|
channelLog.setSource(customerUser.getSourceChannel());
|
channelLog.setStatus(ChannelLog.SUCCESS);
|
ChannelInfo channelInfo = channelInfoService.lambdaQuery().eq(ChannelInfo::getCode, customerUser.getSourceChannel()).one();
|
Customer customer = customerMapper.getLatestCustomerRecord(userDetail.getPhone());
|
if (customer == null) {
|
throw new CommonException("客户历史记录为空");
|
}
|
channelLog.setExportSource(SourceTypeEnum.LOAN_SUPERMARKET.getCode());
|
channelLog.setName(channelInfo.getName());
|
channelLog.setType(ChannelLog.EXPORT);
|
channelLog.setCustomerRecordId(customer.getId());
|
channelLog.setRecordId(loanMarkId);
|
channelLog.setCreationDate(new Date());
|
if (!checkDuplicateLog(channelLog)) {
|
save(channelLog);
|
} else {
|
log.info("创建重复客户推送记录，原始数据：" + channelLog.toString());
|
}
|
}
|
|
@Override
|
public List<ChannelProportionListVo> getChannelProportion(ChannelProportionDto channelProportionDto) {
|
ChannelInfo channelInfos = channelInfoService.getOne(new LambdaQueryWrapper<ChannelInfo>()
|
.eq(ChannelInfo::getCode, channelProportionDto.getChCode()).eq(ChannelInfo::getStatus, 1));
|
if (ObjectUtils.isEmpty(channelInfos)) {
|
throw new CommonException("未查询到该渠道");
|
}
|
if (channelProportionDto.getEndDate() == null) {
|
throw new CommonException("查询时间不能为空");
|
}
|
|
DateTime startDate = DateUtil.beginOfDay(DateUtil.offsetDay(channelProportionDto.getEndDate(), -1));
|
DateTime endDate = DateUtil.endOfDay(channelProportionDto.getEndDate());
|
|
String startDayStr = DateUtil.format(startDate, "MM-dd");
|
String endDayStr = DateUtil.format(endDate, "MM-dd");
|
|
String startDayTemp = DateUtil.format(startDate, "yyyy-MM-dd");
|
String endDayTemp = DateUtil.format(endDate, "yyyy-MM-dd");
|
|
Integer chCode = channelProportionDto.getChCode();
|
String[] hourStr = {"00", "01", "02", "03", "04", "05", "06", "07", "08", "09", "10",
|
"11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23"};
|
List<ChannelProportionVo> channelProportion = channelLogMapper.getChannelProportion(startDate, endDate, chCode, startDayTemp, endDayTemp, hourStr);
|
ArrayList<ChannelProportionListVo> channelProportionListVos = new ArrayList<>();
|
if (ObjectUtils.isEmpty(channelProportion) || channelProportion.size() < 1) {
|
return new ArrayList<>();
|
}
|
for (String hour : hourStr) {
|
ChannelProportionListVo channelProportionListVo = new ChannelProportionListVo();
|
channelProportionListVo.setHHour(hour);
|
channelProportionListVo.setChName(channelInfos.getName());
|
channelProportionListVo.setTime(endDayStr);
|
channelProportionListVo.setLastTime(startDayStr);
|
ChannelProportionVo channelProportionVo1 = channelProportion.stream()
|
.filter(i -> startDayStr.equals(i.getHDay()) && hour.equals(i.getHHour())).findFirst().orElse(null);
|
if (channelProportionVo1 != null) {
|
channelProportionListVo.setLastVipSum(channelProportionVo1.getVipSum());
|
channelProportionListVo.setLastVipPropertion(channelProportionVo1.getVipPropertion());
|
channelProportionListVo.setLastUvSum(channelProportionVo1.getUvSum());
|
channelProportionListVo.setLastPkSum(channelProportionVo1.getPkSum());
|
channelProportionListVo.setLastPkPropertion(channelProportionVo1.getPkPropertion());
|
}
|
ChannelProportionVo channelProportionVo = channelProportion.stream()
|
.filter(i -> endDayStr.equals(i.getHDay()) && hour.equals(i.getHHour())).findFirst().orElse(null);
|
if (channelProportionVo != null) {
|
channelProportionListVo.setVipSum(channelProportionVo.getVipSum());
|
channelProportionListVo.setVipPropertion(channelProportionVo.getVipPropertion());
|
channelProportionListVo.setUvSum(channelProportionVo.getUvSum());
|
channelProportionListVo.setPkSum(channelProportionVo.getPkSum());
|
channelProportionListVo.setPkPropertion(channelProportionVo.getPkPropertion());
|
}
|
channelProportionListVos.add(channelProportionListVo);
|
}
|
return channelProportionListVos;
|
}
|
|
@Override
|
public List<LoanSupermarketStatisticsVo> getLoanSupermarketStatistics(LoanSupermarketStatisticsDto supermarketStatisticsDto) {
|
Long suId = supermarketStatisticsDto.getSuId();
|
if (supermarketStatisticsDto.getDate() == null) {
|
throw new CommonException("查询时间不能为空");
|
}
|
DateTime startDate = DateUtil.beginOfDay(supermarketStatisticsDto.getDate());
|
DateTime endDate = DateUtil.endOfDay(supermarketStatisticsDto.getDate());
|
Integer chCode = supermarketStatisticsDto.getChCode();
|
List<LoanSupermarketStatisticsVo> loanSupermarketStatistics = channelLogMapper.getLoanSupermarketStatistics(startDate, endDate, suId, chCode);
|
if (ObjectUtils.isEmpty(loanSupermarketStatistics) || loanSupermarketStatistics.size() < 1) {
|
return new ArrayList<>();
|
}
|
String[] hourStr = {"00", "01", "02", "03", "04", "05", "06", "07", "08", "09", "10",
|
"11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23"};
|
Map<String, List<LoanSupermarketStatisticsVo>> collect = loanSupermarketStatistics.stream().collect(Collectors.groupingBy(LoanSupermarketStatisticsVo::getHHour));
|
ArrayList<LoanSupermarketStatisticsVo> loanSupermarketStatisticsVos = new ArrayList<>();
|
for (String s : hourStr) {
|
if (ObjectUtils.isEmpty(collect.get(s)) || collect.get(s).size() < 1) {
|
LoanSupermarketStatisticsVo loanSupermarketStatisticsVo = new LoanSupermarketStatisticsVo();
|
loanSupermarketStatisticsVo.setHHour(s);
|
loanSupermarketStatisticsVos.add(loanSupermarketStatisticsVo);
|
} else {
|
loanSupermarketStatisticsVos.addAll(collect.get(s));
|
}
|
}
|
return loanSupermarketStatisticsVos;
|
}
|
|
@Override
|
public List<AutoStatisticsVo> getAutoStatistics(AutoStatisticsDto autoStatisticsDto) {
|
DateTime startDate = DateUtil.beginOfDay(autoStatisticsDto.getEndDate());
|
DateTime endDate = DateUtil.endOfDay(autoStatisticsDto.getEndDate());
|
List<AutoStatisticsVo> autoStatistics = channelLogMapper.getAutoStatistics(startDate, endDate);
|
if (ObjectUtils.isEmpty(autoStatistics) || autoStatistics.isEmpty()) {
|
return new ArrayList<>();
|
}
|
List<AutoStatisticsVo> newList = new ArrayList<>();
|
autoStatistics.forEach(it -> {
|
if (it.getVipAmount() != null) {
|
it.setVipAmount(it.getVipAmount() / 100);
|
}
|
if (it.getPkAmount() != null) {
|
it.setPkAmount(it.getPkAmount() / 100);
|
}
|
if (StringUtils.isNotEmpty(it.getChName())) {
|
it.setUv(0);
|
it.setVipAmountSum(null);
|
}
|
//数据修复
|
if (Objects.isNull(it.getSuCode())) {
|
log.error("联登报表{}渠道数据丢失", it.getChName());
|
channelLogMapper.dataRepair(it.getId());
|
ChannelInfo byId = channelInfoService.getById(it.getId());
|
it.setSuCode(byId.getAutoParentCode());
|
}
|
if (!checkIsNull(it)) {
|
newList.add(it);
|
}
|
});
|
Map<Integer, List<AutoStatisticsVo>> collect = newList.stream().sorted(Comparator.comparing(AutoStatisticsVo::getSuName).reversed()).collect(Collectors.groupingBy(AutoStatisticsVo::getSuCode));
|
List<AutoStatisticsVo> autoStatisticsVos = new ArrayList<>();
|
collect.keySet().stream().map(it -> {
|
List<AutoStatisticsVo> autoStatisticsVos1 = collect.get(it);
|
if (!ObjectUtils.isEmpty(autoStatisticsVos1) && !autoStatisticsVos1.isEmpty()) {
|
AutoStatisticsVo autoStatisticsVo = autoStatisticsVos1.get(0);
|
double sum = autoStatisticsVos1.stream().filter(i -> i.getVipAmount() != null).mapToDouble(AutoStatisticsVo::getVipAmount).sum();
|
autoStatisticsVos1.remove(autoStatisticsVo);
|
autoStatisticsVo.setVipAmountSum(sum);
|
autoStatisticsVos.add(autoStatisticsVo);
|
autoStatisticsVos.addAll(autoStatisticsVos1);
|
}
|
return it;
|
}).collect(Collectors.toList());
|
return autoStatisticsVos;
|
}
|
|
@Override
|
public void getChannelProportionExport(HttpServletResponse response, ChannelProportionDto channelProportionDto) {
|
List<ChannelProportionListVo> channelProportion = this.getChannelProportion(channelProportionDto);
|
ArrayList<ChannelProportionListExportVo> channelProportionListExportVos = new ArrayList<>();
|
for (ChannelProportionListVo channelProportionListVo : channelProportion) {
|
ChannelProportionListExportVo channelProportionListVo1 = new ChannelProportionListExportVo();
|
BeanUtils.copyProperties(channelProportionListVo, channelProportionListVo1);
|
channelProportionListExportVos.add(channelProportionListVo1);
|
}
|
for (int i = 0; i < channelProportion.size(); i++) {
|
channelProportionListExportVos.get(i).setId(i + 1);
|
}
|
// 通过工具类创建writer，默认创建xls格式
|
ExcelWriter writer = ExcelUtil.getWriter();
|
writer.passRows(3);
|
|
String lastTime = channelProportion.get(0).getLastTime();
|
lastTime = lastTime.replace("-", "");
|
String time = channelProportion.get(0).getTime();
|
time = time.replace("-", "");
|
// 自定义标题别名
|
writer.addHeaderAlias("id", "序号");
|
writer.addHeaderAlias("chName", "渠道");
|
writer.addHeaderAlias("hHour", "时间（每小时）");
|
|
writer.addHeaderAlias("lastUvSum", lastTime);
|
writer.addHeaderAlias("uvSum", time);
|
|
writer.addHeaderAlias("lastPkSum", "1");
|
writer.addHeaderAlias("pkSum", "2");
|
|
writer.addHeaderAlias("lastPkPropertion", "3");
|
writer.addHeaderAlias("pkPropertion", "4");
|
|
writer.addHeaderAlias("lastVipSum", "5");
|
writer.addHeaderAlias("vipSum", "6");
|
|
writer.addHeaderAlias("lastVipPropertion", "7");
|
writer.addHeaderAlias("vipPropertion", "8");
|
writer.setOnlyAlias(true);
|
// 合并单元格后的标题行，使用默认标题样式
|
CellStyle cellStyle = writer.getStyleSet().getCellStyle();
|
CellStyle cellStyle1 = writer.getStyleSet().getCellStyle();
|
cellStyle1.setWrapText(true);
|
//设置默认高度
|
writer.setDefaultRowHeight(40);
|
// 一次性写出内容，使用默认样式，强制输出标题
|
writer.write(channelProportionListExportVos, true);
|
//设置自适应列宽
|
setSizeColumn(writer.getSheet(), 12);
|
|
writer.merge(0, 1, 3, 4, "UV", cellStyle);
|
writer.merge(0, 1, 5, 6, "加速包购买数量", cellStyle);
|
writer.merge(0, 1, 7, 8, "加速包购买率（加速包购买数量/UV）", cellStyle1);
|
writer.merge(0, 1, 9, 10, "会员购买数量", cellStyle);
|
writer.merge(0, 1, 11, 12, "会员购买比例（会员购买数量/UV）", cellStyle1);
|
writer.merge(2, 3, 9, 9, lastTime, cellStyle);
|
writer.merge(2, 3, 10, 10, time, cellStyle);
|
writer.merge(2, 3, 3, 3, lastTime, cellStyle);
|
writer.merge(2, 3, 4, 4, time, cellStyle);
|
writer.merge(2, 3, 5, 5, lastTime, cellStyle);
|
writer.merge(2, 3, 6, 6, time, cellStyle);
|
writer.merge(2, 3, 7, 7, lastTime, cellStyle);
|
writer.merge(2, 3, 8, 8, time, cellStyle);
|
writer.merge(2, 3, 11, 11, lastTime, cellStyle);
|
writer.merge(2, 3, 12, 12, time, cellStyle);
|
writer.merge(2, 3, 1, 1, "渠道", cellStyle);
|
writer.merge(2, 3, 2, 2, "时间（每小时）", cellStyle);
|
writer.merge(2, 3, 0, 0, "序号", cellStyle);
|
|
//浏览器导出
|
response.setContentType("application/vnd.ms-excel;charset=utf-8");
|
ServletOutputStream out = null;
|
try {
|
// 设置请求头属性
|
response.setHeader("Content-Disposition", "attachment;filename=" + new String(("入口流量会员购买率" + ".xlsx").getBytes(), StandardCharsets.UTF_8));
|
out = response.getOutputStream();
|
// 写出到文件
|
writer.flush(out, true);
|
// 关闭writer，释放内存
|
writer.close();
|
// 此处记得关闭输出Servlet流
|
IoUtil.close(out);
|
} catch (IOException e) {
|
log.error(e.getMessage());
|
e.printStackTrace();
|
}
|
|
}
|
|
@Override
|
public void getLoanSupermarketStatisticsExport(HttpServletResponse response, LoanSupermarketStatisticsDto supermarketStatisticsDto) {
|
List<LoanSupermarketStatisticsVo> loanSupermarketStatistics = getLoanSupermarketStatistics(supermarketStatisticsDto);
|
ArrayList<LoanSupermarketStatisticsExportVo> loanSupermarketStatisticsExportVos = new ArrayList<>();
|
for (LoanSupermarketStatisticsVo loanSupermarketStatisticsVo : loanSupermarketStatistics) {
|
LoanSupermarketStatisticsExportVo loanSupermarketStatisticsExportVo = new LoanSupermarketStatisticsExportVo();
|
BeanUtils.copyProperties(loanSupermarketStatisticsVo, loanSupermarketStatisticsExportVo);
|
loanSupermarketStatisticsExportVos.add(loanSupermarketStatisticsExportVo);
|
}
|
String suName = "";
|
for (int i = 0; i < loanSupermarketStatisticsExportVos.size(); i++) {
|
loanSupermarketStatisticsExportVos.get(i).setId(i + 1);
|
if (!StringUtils.isEmpty(loanSupermarketStatisticsExportVos.get(i).getSuName()) && !"".equals(loanSupermarketStatisticsExportVos.get(i).getSuName())) {
|
suName = loanSupermarketStatisticsExportVos.get(i).getSuName();
|
}
|
}
|
|
String format = DateUtil.format(supermarketStatisticsDto.getDate(), "yyyy-MM-dd");
|
// 通过工具类创建writer，默认创建xls格式
|
ExcelWriter writer = ExcelUtil.getWriter();
|
writer.passRows(2);
|
|
// 自定义标题别名
|
writer.addHeaderAlias("id", "序号");
|
writer.addHeaderAlias("hHour", "时间（每小时）");
|
writer.addHeaderAlias("suName", "贷超产品");
|
writer.addHeaderAlias("uvSum", "UV");
|
writer.addHeaderAlias("chName", "渠道名称");
|
writer.setOnlyAlias(true);
|
|
// 合并单元格后的标题行，使用默认标题样式
|
CellStyle cellStyle = writer.getStyleSet().getCellStyle();
|
CellStyle cellStyle1 = writer.getStyleSet().getCellStyle();
|
cellStyle1.setWrapText(true);
|
//设置默认高度
|
writer.setDefaultRowHeight(40);
|
// 一次性写出内容，使用默认样式，强制输出标题
|
writer.write(loanSupermarketStatisticsExportVos, true);
|
//设置自适应列宽
|
setSizeColumn(writer.getSheet(), 4);
|
|
writer.merge(0, 1, 0, 1, "日期:" + format, cellStyle);
|
writer.merge(0, 1, 2, 3, "贷超产品:" + suName, cellStyle1);
|
|
//浏览器导出
|
webExport(response, writer);
|
}
|
|
@Override
|
public void autoStatisticsExport(HttpServletResponse response, AutoStatisticsDto autoStatisticsDto) {
|
List<AutoStatisticsVo> autoStatistics = getAutoStatistics(autoStatisticsDto);
|
String format = DateUtil.format(autoStatisticsDto.getEndDate(), "yyyy-MM-dd");
|
// 通过工具类创建writer，默认创建xls格式
|
ExcelWriter writer = ExcelUtil.getWriter();
|
writer.passRows(1);
|
|
// 自定义标题别名
|
writer.addHeaderAlias("hDay", "日期");
|
writer.addHeaderAlias("suName", "产品");
|
writer.addHeaderAlias("chName", "接入渠道");
|
writer.addHeaderAlias("uv", "UV");
|
writer.addHeaderAlias("inletFlowSum", "入口流量数");
|
writer.addHeaderAlias("pkAmount", "加速包金额");
|
writer.addHeaderAlias("vipAmount", "会员金额");
|
writer.addHeaderAlias("vipAmountSum", "会员金额合计");
|
writer.addHeaderAlias("vipPropertion", "会员入口流量比");
|
writer.setOnlyAlias(true);
|
|
// 合并单元格后的标题行，使用默认标题样式
|
CellStyle cellStyle = writer.getStyleSet().getCellStyle();
|
cellStyle.setWrapText(true);
|
//设置默认高度
|
writer.setDefaultRowHeight(40);
|
// 一次性写出内容，使用默认样式，强制输出标题
|
writer.write(autoStatistics, true);
|
//设置自适应列宽
|
setSizeColumn(writer.getSheet(), 8);
|
|
writer.merge(0, 0, 0, 1, "日期:" + format, cellStyle);
|
|
//浏览器导出
|
webExport(response, writer);
|
|
}
|
|
|
/**
|
* 判断数据内容是否为空
|
*
|
* @param autoStatisticsVo
|
* @return
|
*/
|
private boolean checkIsNull(AutoStatisticsVo autoStatisticsVo) {
|
//判断对象数据是否为空
|
if (Objects.isNull(autoStatisticsVo.getVipAmount()) && Objects.isNull(autoStatisticsVo.getPkAmount()) && Objects.isNull(autoStatisticsVo.getInletFlowSum()) && Objects.isNull(autoStatisticsVo.getVipAmountSum())) {
|
if (Objects.isNull(autoStatisticsVo.getUv()) || autoStatisticsVo.getUv() == 0) {
|
return true;
|
}
|
}
|
return false;
|
}
|
|
|
@Override
|
public List<WholeProcessStatisticsListVo> getWholeProcessStatistics(WholeProcessStatisticsDto wholeProcessStatisticsDto) {
|
if (wholeProcessStatisticsDto.getDate() == null) {
|
throw new CommonException("查询时间不能为空");
|
}
|
DateTime startDate = DateUtil.beginOfDay(wholeProcessStatisticsDto.getDate());
|
DateTime endDate = DateUtil.endOfDay(wholeProcessStatisticsDto.getDate());
|
Integer chCode = wholeProcessStatisticsDto.getChCode();
|
WholeProcessStatisticsVo resultDb = channelLogMapper.getWholeProcessStatistics(startDate, endDate, chCode);
|
List<WholeProcessStatisticsListVo> result = new ArrayList<>();
|
WholeProcessStatisticsListVo statisticsH5Vo = new WholeProcessStatisticsListVo();
|
WholeProcessStatisticsListVo statisticsAppVo = new WholeProcessStatisticsListVo();
|
statisticsH5Vo.setType("H5");
|
statisticsH5Vo.setDate(resultDb.getDate());
|
statisticsH5Vo.setLoginCount(resultDb.getH5LoginCount());
|
statisticsH5Vo.setDownloadCount(resultDb.getDownloadCount());
|
statisticsH5Vo.setBaseInfoCount(resultDb.getBaseInfoCount());
|
result.add(statisticsH5Vo);
|
BeanUtils.copyProperties(resultDb,statisticsAppVo);
|
statisticsAppVo.setType("APP");
|
statisticsAppVo.setDownloadCount(null);
|
statisticsAppVo.setLoginCount(resultDb.getAppLoginCount());
|
result.add(statisticsAppVo);
|
return result;
|
}
|
|
@Override
|
public List<InletFlowProportionListVo> getInletFlowProportion(InletFlowProportionDto inletFlowProportionDto) {
|
ChannelInfo channelInfos = channelInfoMapper.selectOne(new LambdaQueryWrapper<ChannelInfo>()
|
.eq(ChannelInfo::getCode, inletFlowProportionDto.getChCode()).eq(ChannelInfo::getStatus, 1));
|
if (ObjectUtils.isEmpty(channelInfos)) {
|
throw new CommonException("未查询到该渠道");
|
}
|
if (inletFlowProportionDto.getEndDate() == null) {
|
throw new CommonException("查询时间不能为空");
|
}
|
|
DateTime startDate = DateUtil.beginOfDay(DateUtil.offsetDay(inletFlowProportionDto.getEndDate(), -1));
|
DateTime endDate = DateUtil.endOfDay(inletFlowProportionDto.getEndDate());
|
|
String startDayStr = DateUtil.format(startDate, "MM-dd");
|
String endDayStr = DateUtil.format(endDate, "MM-dd");
|
|
String startDayTemp = DateUtil.format(startDate, "yyyy-MM-dd");
|
String endDayTemp = DateUtil.format(endDate, "yyyy-MM-dd");
|
|
Integer chCode = inletFlowProportionDto.getChCode();
|
String[] hourStr = {"00", "01", "02", "03", "04", "05", "06", "07", "08", "09", "10",
|
"11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23"};
|
List<InletFlowProportionVo> inletFlowProportionVos = customerMapper.getInletFlowProportion(startDate, endDate, chCode, startDayTemp, endDayTemp, hourStr);
|
ArrayList<InletFlowProportionListVo> inletFlowProportionListVos = new ArrayList<>();
|
|
if (ObjectUtils.isEmpty(inletFlowProportionVos) || inletFlowProportionVos.size() < 1) {
|
return new ArrayList<>();
|
}
|
for (String hour : hourStr) {
|
InletFlowProportionListVo channelProportionListVo = new InletFlowProportionListVo();
|
channelProportionListVo.setHHour(hour);
|
channelProportionListVo.setChName(channelInfos.getName());
|
channelProportionListVo.setTime(endDayStr);
|
channelProportionListVo.setLastTime(startDayStr);
|
InletFlowProportionVo inletFlowProportionVo = inletFlowProportionVos.stream()
|
.filter(i -> startDayStr.equals(i.getHDay()) && hour.equals(i.getHHour())).findFirst().orElse(null);
|
if (inletFlowProportionVo != null) {
|
channelProportionListVo.setLastVipSum(inletFlowProportionVo.getVipSum());
|
channelProportionListVo.setLastVipPropertion(inletFlowProportionVo.getVipPropertion());
|
channelProportionListVo.setLastUvSum(inletFlowProportionVo.getUvSum());
|
channelProportionListVo.setLastPkSum(inletFlowProportionVo.getPkSum());
|
channelProportionListVo.setLastPkPropertion(inletFlowProportionVo.getPkPropertion());
|
}
|
InletFlowProportionVo inletFlowProportionVo1 = inletFlowProportionVos.stream()
|
.filter(i -> endDayStr.equals(i.getHDay()) && hour.equals(i.getHHour())).findFirst().orElse(null);
|
if (inletFlowProportionVo1 != null) {
|
channelProportionListVo.setVipSum(inletFlowProportionVo1.getVipSum());
|
channelProportionListVo.setVipPropertion(inletFlowProportionVo1.getVipPropertion());
|
channelProportionListVo.setUvSum(inletFlowProportionVo1.getUvSum());
|
channelProportionListVo.setPkSum(inletFlowProportionVo1.getPkSum());
|
channelProportionListVo.setPkPropertion(inletFlowProportionVo1.getPkPropertion());
|
}
|
inletFlowProportionListVos.add(channelProportionListVo);
|
}
|
return inletFlowProportionListVos;
|
}
|
|
|
@Override
|
public void inletFlowProportionExport(HttpServletResponse response, InletFlowProportionDto inletFlowProportionDto) {
|
|
List<InletFlowProportionListVo> inletFlowProportion = getInletFlowProportion(inletFlowProportionDto);
|
List<InletFlowProportionListExportVo> inletFlowProportionListExportVos = new ArrayList<>();
|
for (InletFlowProportionListVo inletFlowProportionListVo : inletFlowProportion) {
|
InletFlowProportionListExportVo inletFlowProportionListExportVo = new InletFlowProportionListExportVo();
|
BeanUtils.copyProperties(inletFlowProportionListVo, inletFlowProportionListExportVo);
|
inletFlowProportionListExportVos.add(inletFlowProportionListExportVo);
|
}
|
for (int i = 0; i < inletFlowProportion.size(); i++) {
|
inletFlowProportionListExportVos.get(i).setId(i + 1);
|
}
|
// 通过工具类创建writer，默认创建xls格式
|
ExcelWriter writer = ExcelUtil.getWriter();
|
writer.passRows(3);
|
|
String lastTime = inletFlowProportion.get(0).getLastTime();
|
lastTime = lastTime.replace("-", "");
|
String time = inletFlowProportion.get(0).getTime();
|
time = time.replace("-", "");
|
// 自定义标题别名
|
writer.addHeaderAlias("id", "序号");
|
writer.addHeaderAlias("chName", "渠道");
|
writer.addHeaderAlias("hHour", "时间（每小时）");
|
|
writer.addHeaderAlias("lastUvSum", lastTime);
|
writer.addHeaderAlias("uvSum", time);
|
|
writer.addHeaderAlias("lastPkSum", "1");
|
writer.addHeaderAlias("pkSum", "2");
|
|
writer.addHeaderAlias("lastPkPropertion", "3");
|
writer.addHeaderAlias("pkPropertion", "4");
|
|
writer.addHeaderAlias("lastVipSum", "5");
|
writer.addHeaderAlias("vipSum", "6");
|
|
writer.addHeaderAlias("lastVipPropertion", "7");
|
writer.addHeaderAlias("vipPropertion", "8");
|
writer.setOnlyAlias(true);
|
// 合并单元格后的标题行，使用默认标题样式
|
CellStyle cellStyle = writer.getStyleSet().getCellStyle();
|
CellStyle cellStyle1 = writer.getStyleSet().getCellStyle();
|
cellStyle1.setWrapText(true);
|
//设置默认高度
|
writer.setDefaultRowHeight(40);
|
// 一次性写出内容，使用默认样式，强制输出标题
|
writer.write(inletFlowProportionListExportVos, true);
|
//设置自适应列宽
|
setSizeColumn(writer.getSheet(), 12);
|
|
writer.merge(0, 1, 3, 4, "入口流量数", cellStyle);
|
writer.merge(0, 1, 5, 6, "加速包购买数量", cellStyle);
|
writer.merge(0, 1, 7, 8, "加速包购买率（加速包购买数量/UV）", cellStyle1);
|
writer.merge(0, 1, 9, 10, "会员购买数量", cellStyle);
|
writer.merge(0, 1, 11, 12, "会员购买购买率（会员购买数量/UV）", cellStyle1);
|
writer.merge(2, 3, 9, 9, lastTime, cellStyle);
|
writer.merge(2, 3, 10, 10, time, cellStyle);
|
writer.merge(2, 3, 3, 3, lastTime, cellStyle);
|
writer.merge(2, 3, 4, 4, time, cellStyle);
|
writer.merge(2, 3, 5, 5, lastTime, cellStyle);
|
writer.merge(2, 3, 6, 6, time, cellStyle);
|
writer.merge(2, 3, 7, 7, lastTime, cellStyle);
|
writer.merge(2, 3, 8, 8, time, cellStyle);
|
writer.merge(2, 3, 11, 11, lastTime, cellStyle);
|
writer.merge(2, 3, 12, 12, time, cellStyle);
|
writer.merge(2, 3, 1, 1, "渠道", cellStyle);
|
writer.merge(2, 3, 2, 2, "时间（每小时）", cellStyle);
|
writer.merge(2, 3, 0, 0, "序号", cellStyle);
|
|
|
//浏览器导出
|
response.setContentType("application/vnd.ms-excel;charset=utf-8");
|
ServletOutputStream out = null;
|
try {
|
// 设置请求头属性
|
response.setHeader("Content-Disposition", "attachment;filename=" + new String(("入口流量会员购买率" + ".xlsx").getBytes(), StandardCharsets.UTF_8));
|
out = response.getOutputStream();
|
// 写出到文件
|
writer.flush(out, true);
|
// 关闭writer，释放内存
|
writer.close();
|
// 此处记得关闭输出Servlet流
|
IoUtil.close(out);
|
} catch (IOException e) {
|
log.error(e.getMessage());
|
e.printStackTrace();
|
}
|
}
|
|
|
@Override
|
public List<ChannelStatisticsVo> channelStatistics(ChannelStatisticsDto channelStatisticsDto) {
|
List<Integer> chCodes = channelStatisticsDto.getChCodes();
|
if (chCodes != null && chCodes.size() > 0) {
|
List<ChannelInfo> channelInfos = channelInfoMapper.selectList(new LambdaQueryWrapper<ChannelInfo>().in(ChannelInfo::getCode, chCodes));
|
if (CollectionUtils.isEmpty(channelInfos) || channelInfos.size() < channelStatisticsDto.getChCodes().size()) {
|
throw new CommonException("有未存在的渠道");
|
}
|
} else {
|
chCodes = null;
|
}
|
if (channelStatisticsDto.getDate() == null) {
|
throw new CommonException("查询时间不能为空");
|
}
|
DateTime startDate = DateUtil.beginOfDay(DateUtil.offsetDay(channelStatisticsDto.getDate(), -1));
|
DateTime endDate = DateUtil.endOfDay(channelStatisticsDto.getDate());
|
String startDayStr = DateUtil.format(startDate, "MM-dd");
|
String endDayStr = DateUtil.format(endDate, "MM-dd");
|
|
List<ChannelStatisticsListVo> channelStatisticsListVos = channelLogMapper.channelStatistics(startDate, endDate, chCodes);
|
if (CollectionUtils.isEmpty(channelStatisticsListVos)) {
|
return new ArrayList<>();
|
}
|
ArrayList<ChannelStatisticsVo> channelStatisticsVos = new ArrayList<>();
|
Map<String, List<ChannelStatisticsListVo>> startDayByChName = channelStatisticsListVos.stream().filter(i -> startDayStr.equals(i.getHDay())).collect(Collectors.groupingBy(ChannelStatisticsListVo::getChName));
|
Map<String, List<ChannelStatisticsListVo>> endDayByChName = channelStatisticsListVos.stream().filter(i -> endDayStr.equals(i.getHDay())).collect(Collectors.groupingBy(ChannelStatisticsListVo::getChName));
|
Map<String, List<ChannelStatisticsListVo>> collect = channelStatisticsListVos.stream().collect(Collectors.groupingBy(ChannelStatisticsListVo::getChName));
|
collect.keySet().stream().map(item -> {
|
ChannelStatisticsVo channelStatisticsVo = new ChannelStatisticsVo();
|
channelStatisticsVo.setChName(item);
|
channelStatisticsVo.setLastTime(startDayStr);
|
channelStatisticsVo.setTime(endDayStr);
|
List<ChannelStatisticsListVo> channelStatisticsListVos1 = startDayByChName.get(item);
|
if (!CollectionUtils.isEmpty(channelStatisticsListVos1) && !channelStatisticsListVos1.isEmpty()) {
|
channelStatisticsVo.setLastPkPropertion(channelStatisticsListVos1.get(0).getPkPropertion());
|
channelStatisticsVo.setLastPkSum(channelStatisticsListVos1.get(0).getPkSum());
|
channelStatisticsVo.setLastUvSum(channelStatisticsListVos1.get(0).getUvSum());
|
channelStatisticsVo.setLastVipSum(channelStatisticsListVos1.get(0).getVipSum());
|
channelStatisticsVo.setLastVipPropertion(channelStatisticsListVos1.get(0).getVipPropertion());
|
}
|
List<ChannelStatisticsListVo> channelStatisticsListVos2 = endDayByChName.get(item);
|
if (!CollectionUtils.isEmpty(channelStatisticsListVos2) && !channelStatisticsListVos2.isEmpty()) {
|
channelStatisticsVo.setPkPropertion(channelStatisticsListVos2.get(0).getPkPropertion());
|
channelStatisticsVo.setPkSum(channelStatisticsListVos2.get(0).getPkSum());
|
channelStatisticsVo.setUvSum(channelStatisticsListVos2.get(0).getUvSum());
|
channelStatisticsVo.setVipSum(channelStatisticsListVos2.get(0).getVipSum());
|
channelStatisticsVo.setVipPropertion(channelStatisticsListVos2.get(0).getVipPropertion());
|
}
|
channelStatisticsVos.add(channelStatisticsVo);
|
return item;
|
}).collect(Collectors.toList());
|
return channelStatisticsVos;
|
}
|
|
@Override
|
public List<ProfitAnalysisReportVo> profitAnalysisReport(ProfitAnalysisReportDto profitAnalysisReportDto) {
|
if (profitAnalysisReportDto.getDate() == null) {
|
profitAnalysisReportDto.setDate(new Date());
|
}
|
Integer chCode = profitAnalysisReportDto.getChCode();
|
if (chCode == null) {
|
return new ArrayList<>();
|
}
|
ChannelInfo channelInfo = channelInfoMapper.selectOne(new LambdaQueryWrapper<ChannelInfo>().in(ChannelInfo::getCode, chCode));
|
if (channelInfo == null) {
|
throw new CommonException("渠道不存在");
|
}
|
DateTime startDate = DateUtil.beginOfDay(profitAnalysisReportDto.getDate());
|
DateTime endDate = DateUtil.endOfDay(profitAnalysisReportDto.getDate());
|
ProfitAnalysisReportVo reportVo = new ProfitAnalysisReportVo();
|
BaseProfitAnalysisReportVo baseReportVo = channelLogMapper.getBaseProfitAnalysisReportVo(startDate, endDate, chCode);
|
reportVo.setChName(baseReportVo.getChName());
|
reportVo.setUvSum(baseReportVo.getUvSum());
|
reportVo.setLoanIncome(baseReportVo.getLoanIncome());
|
reportVo.setVipIncome(baseReportVo.getVipIncome());
|
reportVo.setPkIncome(baseReportVo.getPkIncome());
|
CityProfitAnalysisReportVo cityReportVo = channelLogMapper.getCityProfitAnalysisReportVo(startDate, endDate, chCode);
|
reportVo.setCollisionIncome(cityReportVo.getCollisionIncome());
|
reportVo.setCompanyIncome(cityReportVo.getCapitalIncome() + cityReportVo.getAuditIncome());
|
reportVo.setRegisterSum(cityReportVo.getRegisterSum());
|
Integer totalCost = baseReportVo.getUvCost() + cityReportVo.getRegisterCost();
|
reportVo.setTotalCost(totalCost);
|
Integer totalIncome = reportVo.getLoanIncome() + reportVo.getPkIncome() + reportVo.getVipIncome()
|
+ reportVo.getCollisionIncome() + reportVo.getCompanyIncome();
|
reportVo.setTotalIncome(totalIncome);
|
reportVo.setProfit(totalIncome - totalCost);
|
return Collections.singletonList(reportVo);
|
}
|
|
//设置自适应列宽
|
public static void setSizeColumn(Sheet sheet, int size) {
|
for (int columnNum = 0; columnNum <= size; columnNum++) {
|
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
|
for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
|
Row currentRow;
|
//当前行未被使用过
|
if (sheet.getRow(rowNum) == null) {
|
currentRow = sheet.createRow(rowNum);
|
} else {
|
currentRow = sheet.getRow(rowNum);
|
}
|
|
if (currentRow.getCell(columnNum) != null) {
|
Cell currentCell = currentRow.getCell(columnNum);
|
if (currentCell.getCellType() == CellType.STRING) {
|
int length = currentCell.getStringCellValue().getBytes().length;
|
if (columnWidth < length) {
|
columnWidth = length;
|
}
|
}
|
}
|
}
|
sheet.setColumnWidth(columnNum, columnWidth * 256);
|
}
|
}
|
|
//浏览器导出
|
public void webExport(HttpServletResponse response, ExcelWriter writer) {
|
response.setContentType("application/vnd.ms-excel;charset=utf-8");
|
ServletOutputStream out = null;
|
try {
|
// 设置请求头属性
|
response.setHeader("Content-Disposition", "attachment;filename=" + new String(("入口流量会员购买率" + ".xlsx").getBytes(), StandardCharsets.UTF_8));
|
out = response.getOutputStream();
|
// 写出到文件
|
writer.flush(out, true);
|
// 关闭writer，释放内存
|
writer.close();
|
// 此处记得关闭输出Servlet流
|
IoUtil.close(out);
|
} catch (IOException e) {
|
log.error(e.getMessage());
|
e.printStackTrace();
|
}
|
}
|
}
|
