package cn.gistack.sm.intelligentCall.util;
|
|
import cn.gistack.sm.intelligentCall.vo.CallTaskResultVO;
|
import com.alibaba.excel.EasyExcel;
|
import com.alibaba.excel.ExcelWriter;
|
import com.alibaba.excel.support.ExcelTypeEnum;
|
import com.alibaba.excel.write.metadata.WriteSheet;
|
import com.alibaba.excel.write.metadata.fill.FillWrapper;
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.io.OutputStream;
|
import java.net.URLEncoder;
|
import java.util.HashMap;
|
import java.util.List;
|
|
/**
|
* excel工具类
|
*/
|
public class ExcelUtil {
|
|
/**
|
* 导出数据
|
* @param response
|
* @param callTaskResultVOS
|
* @param callTaskResult
|
* @throws Exception
|
*/
|
public static void generateExcel(HttpServletResponse response, List<CallTaskResultVO> callTaskResultVOS,CallTaskResultVO callTaskResult) throws Exception {
|
String fileName = callTaskResult.getStartTime() + "~" + callTaskResult.getEndTime() + "智能外呼统计情况表" + System.currentTimeMillis() + ".xlsx";
|
HashMap<String, Object> info = formatInfo(callTaskResultVOS,callTaskResult);
|
//获取模板(带公式的模板需要文件编辑之后才会显示正常数据)
|
InputStream inputStream = ExcelUtil.class.getClassLoader().getResourceAsStream("excel/xlsx/callStatisticTemplate.xlsx");
|
ExcelWriter excelWriter = EasyExcel.write(getOutputStream(fileName, response)).withTemplate(inputStream).excelType(ExcelTypeEnum.XLSX).build();
|
//设置公式生效
|
WriteSheet writeSheet = EasyExcel.writerSheet(0,"sheet").build();
|
// 这个传进去的 map 就是非表格数据，详见我定义的模板中图片位置上方那部分
|
excelWriter.fill(info, writeSheet);
|
excelWriter.fill(new FillWrapper("data", callTaskResultVOS), writeSheet);
|
excelWriter.finish();
|
}
|
|
/**
|
* 数据格式化处理
|
* @param callTaskResultVOS
|
* @param callTaskResult
|
* @return
|
*/
|
private static HashMap<String,Object> formatInfo(List<CallTaskResultVO> callTaskResultVOS,CallTaskResultVO callTaskResult) {
|
HashMap<String,Object> info = new HashMap<>();
|
info.put("startTime",callTaskResult.getStartTime());
|
info.put("endTime",callTaskResult.getEndTime());
|
int total = callTaskResultVOS.size();
|
int jtTotal = 0;
|
int wtTotal = 0;
|
int isNotMan = 0;
|
int isMan = 0;
|
int patrol = 0;
|
int noOver = 0;
|
int noDanger = 0;
|
String bl = "0";
|
|
//给总览加序号
|
for (int i = 0; i < callTaskResultVOS.size(); i++) {
|
CallTaskResultVO callResult = callTaskResultVOS.get(i);
|
callResult.setIndex(String.valueOf(i+1));
|
|
// 通话时长计算
|
if (!callResult.getCallDuration().equals("0")) {
|
int result = Integer.parseInt(callResult.getCallDuration());
|
// double h = Math.floor(result / 3600) < 10 ? '0' + Math.floor(result / 3600) : Math.floor(result / 3600)
|
double m = Math.floor((result / 60 % 60)) < 10 ? '0' + Math.floor((result / 60 % 60)) : Math.floor((result / 60 % 60));
|
double s = Math.floor((result % 60)) < 10 ? '0' + Math.floor((result % 60)) : Math.floor((result % 60));
|
StringBuilder builder = new StringBuilder();
|
builder.append(m).append(":").append(s);
|
callResult.setCallDuration(builder.toString());
|
}
|
|
// 是否巡查责任人 0：未知或空 1：不是巡查责任人 2：是巡查责任人
|
if (callResult.getIsMan().equals("0")) {
|
callResult.setIsMan("");
|
}
|
if (callResult.getIsMan().equals("1")) {
|
callResult.setIsMan("否");
|
}
|
if (callResult.getIsMan().equals("2")) {
|
callResult.setIsMan("是");
|
}
|
|
// 是否已巡查 0：未知或空 1：未巡查 2：已巡查
|
if (callResult.getIsPatrol().equals("0")) {
|
callResult.setIsPatrol("");
|
}
|
if (callResult.getIsPatrol().equals("1")) {
|
callResult.setIsPatrol("否");
|
}
|
if (callResult.getIsPatrol().equals("2")) {
|
callResult.setIsPatrol("是");
|
}
|
|
// 是否超汛限 0：未知或空 1：未超汛限 2：超汛限
|
if (callResult.getIsOver().equals("0")) {
|
callResult.setIsOver("");
|
}
|
if (callResult.getIsOver().equals("1")) {
|
callResult.setIsOver("否");
|
}
|
if (callResult.getIsOver().equals("2")) {
|
callResult.setIsOver("是");
|
}
|
|
// 是否发现险情 0：未知或空 1：未发现险情 2：发现险情
|
if (callResult.getIsDange().equals("0")) {
|
callResult.setIsDange("");
|
}
|
if (callResult.getIsDange().equals("1")) {
|
callResult.setIsDange("否");
|
}
|
if (callResult.getIsDange().equals("2")) {
|
callResult.setIsDange("是");
|
}
|
|
// 挂断方向 A机器 B用户
|
if (callResult.getCallHangupDirection().equals("A")) {
|
callResult.setCallHangupDirection("机器");
|
}
|
if (callResult.getCallHangupDirection().equals("B")) {
|
callResult.setCallHangupDirection("用户");
|
}
|
|
if (callResult.getCallResult().equals("200000") ||
|
callResult.getIsSmsFill().equals("已反馈")
|
){
|
if (callResult.getIsSmsFill().equals("已反馈")){
|
setCallResult(callResult);
|
}else {
|
callResult.setCallResult("接通");
|
}
|
jtTotal += 1;
|
}else {
|
wtTotal += 1;
|
setCallResult(callResult);
|
}
|
|
// 接通并是巡查责任人
|
if ((callResult.getCallResult().equals("接通") && callResult.getIsMan().equals("是")) ||
|
(callResult.getIsSmsFill().equals("已反馈") && callResult.getIsMan().equals("是"))
|
) {
|
isMan += 1;
|
}
|
|
// 接通并不是巡查责任人
|
if ((callResult.getCallResult().equals("接通") && callResult.getIsMan().equals("否")) ||
|
(callResult.getIsSmsFill().equals("已反馈") && callResult.getIsMan().equals("否"))
|
) {
|
isNotMan += 1;
|
}
|
|
// 接通并已巡查
|
if ((callResult.getCallResult().equals("接通") && callResult.getIsPatrol().equals("是") && callResult.getIsMan().equals("是") ) ||
|
(callResult.getIsSmsFill().equals("已反馈") && callResult.getIsPatrol().equals("是") && callResult.getIsMan().equals("是"))
|
) {
|
patrol += 1;
|
}
|
|
// 接通并不是超汛限
|
if ((callResult.getCallResult().equals("接通") && callResult.getIsOver().equals("否") && callResult.getIsMan().equals("是") ) ||
|
(callResult.getIsSmsFill().equals("已反馈") && callResult.getIsOver().equals("否") && callResult.getIsMan().equals("是"))
|
) {
|
noOver += 1;
|
}
|
|
// 接通并没有发现险情
|
if ((callResult.getCallResult().equals("接通") && callResult.getIsDange().equals("否") && callResult.getIsMan().equals("是") ) ||
|
(callResult.getIsSmsFill().equals("已反馈") && callResult.getIsDange().equals("否") && callResult.getIsMan().equals("是"))
|
) {
|
noDanger += 1;
|
}
|
}
|
|
if (total > 0) {
|
// 计算百分比
|
double number = (double) jtTotal/total;
|
// 格式化
|
String result = String.format("%.2f", number*100);
|
// 设置
|
bl = result;
|
String[] split = bl.split("\\.");
|
if (split.length==2){
|
if (split[1].equals("00")){
|
bl = split[0];
|
}
|
}
|
}
|
// 备注说明拼接
|
StringBuilder builder = new StringBuilder();
|
builder.append("统计：智能外呼系统于 ")
|
.append(callTaskResult.getStartTime()).append(" 至 ")
|
.append(callTaskResult.getEndTime()).append(" 以来对湖北省超汛限水库进行巡查责任人电话抽查其中湖北省进行了")
|
.append(total).append("人次的电话抽查，接通")
|
.append(jtTotal).append("个，未接通")
|
.append(wtTotal).append("个，实际触达率为")
|
.append(bl).append("%;有效通话中的")
|
.append(jtTotal).append("个水库，其中")
|
.append(isNotMan).append("人反馈不是巡查责任人；在巡查问答环节的")
|
.append(isMan).append("个责任人中，")
|
.append(patrol).append("人均已表示巡查，")
|
.append(noOver).append("人表示水位未超汛限水位，")
|
.append(noDanger).append("人表示未发现险情。");
|
// 设置说明
|
info.put("remark", builder.toString());
|
// 返回
|
return info;
|
}
|
|
/**
|
* 设置通话结果
|
* @param callResult
|
*/
|
private static void setCallResult(CallTaskResultVO callResult) {
|
if (callResult.getCallResult().equals("200001")) {
|
callResult.setCallResult("用户提前挂机未完整收听");
|
}
|
if (callResult.getCallResult().equals("200002")) {
|
callResult.setCallResult("占线");
|
}
|
if (callResult.getCallResult().equals("200003")) {
|
callResult.setCallResult("未接通");
|
}
|
if (callResult.getCallResult().equals("200004")) {
|
callResult.setCallResult("空号");
|
}
|
if (callResult.getCallResult().equals("200005")) {
|
callResult.setCallResult("拒接");
|
}
|
if (callResult.getCallResult().equals("200007")) {
|
callResult.setCallResult("无法接通/不在服务区");
|
}
|
if (callResult.getCallResult().equals("200010")) {
|
callResult.setCallResult("关机");
|
}
|
if (callResult.getCallResult().equals("200011")) {
|
callResult.setCallResult("停机");
|
}
|
if (callResult.getCallResult().equals("200100")) {
|
callResult.setCallResult("转接");
|
}
|
if (callResult.getCallResult().equals("200130")) {
|
callResult.setCallResult("其他（无法识别）");
|
}
|
}
|
|
/**
|
* 导出文件时为Writer生成OutputStream.
|
*
|
* @param fileName 文件名
|
* @param response response
|
* @return ""
|
*/
|
public static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
|
try {
|
fileName = URLEncoder.encode(fileName, "UTF-8");
|
response.setContentType("application/vnd.ms-excel");
|
response.setCharacterEncoding("utf8");
|
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
|
return response.getOutputStream();
|
} catch (IOException e) {
|
throw new Exception("导出excel表格失败!", e);
|
}
|
}
|
}
|
